Fill Table Using a SP

I am trying to create a temporary table and then run a SP and have the value
s
stored in this table. The problem is that the SP does'nt always return a
value for every column so i get a error saying that the column name or
definition do not match. How can I get around this problem? I have tried
setting default values but got the same results. Here is the code I am tryin
g
to use, the problem is in the #TabTemp table:
ALTER Procedure GetLotReport
As
--Create temp table inorder to crosstab
Create table #PushTemp (LotID Char(15), [Dyed Yards] int, [Detected Yards]
int, Total dec(10,3), FaultCause Char(15))
--Fill table with data
Insert into #PushTemp exec spcsql.dbo.sp_Buttonpushes '8/15/05', '8/21/05'
--Create Temp table to hold crosstab results
Create Table #tabtemp (LotID Char(7), [Broken Filament] Char(15) Default 0,
[Dye Grease] Char(10) Default 0, [Dye Spots] Char(9) Default 0, [Dye Streaks
]
Char(11) Default 0, [Finish Spots] Char(12) Default 0, [Floats] Char(6)
Default 0, [Fuzzy Edge] Char(9) Default 0, [Knots] Char(5) Default 0, [Loose
Edge] Char(10) Default 0, [Mispick] Char(7) Default 0, [No Fault] Char(8)
Default 0, [Not Listed] Char(10) Default 0, [Repair Broken Filament] Char(20
)
Default 0, [Run Out] Char(7) Default 0, [Sewings] Char(7) Default 0, [Tight
Loose Ends] Char(16) Default 0, [Water Spots] Char(11) Default 0, [Wavy Edge
]
Char(9) Default 0, [Weave Grease] Char(12) Default 0)
--Crosstab the table
insert into #tabtemp exec sp_JRMCrossTab
'#PushTemp',
'LotID',
'FaultCause',
'Total',
NULL,
'SUM'
Select Distinct t.LotID, p.[Dyed Yards], p.[Detected Yards], t.[Broken
Filament],
t.[Dye Grease], t.[Dye Spots], t.[Dye Streaks], t.[Finish Spots], t.[Fuzzy
Edge],
t.[Floats], t.[Fuzzy Edge], t.[Knots], t.[Loose Edge], t.[Mispick], t.[No
Fault], t.[Not Listed], t.[Repair Broken Filament], t.[Run Out], t.[Sewings],
t.[Tight Loose Ends], t.[Water Spots], t.[Wavy Edge], t.[Weave Grease]
From #tabtemp t inner join #pushtemp p
on t.LotID = p.LotIDSome sample:
CREATE Procedure test
(
@.test1 varchar(40),
@.test2 varchar(40)
)
AS
BEGIN
Select 1
END
create table #temptable
(
IDCol int
)
INSERt INTO #temptable
EXEC('test ''1'',''2''')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"A.B." wrote:

> I am trying to create a temporary table and then run a SP and have the val
ues
> stored in this table. The problem is that the SP does'nt always return a
> value for every column so i get a error saying that the column name or
> definition do not match. How can I get around this problem? I have tried
> setting default values but got the same results. Here is the code I am try
ing
> to use, the problem is in the #TabTemp table:
> ALTER Procedure GetLotReport
> As
> --Create temp table inorder to crosstab
> Create table #PushTemp (LotID Char(15), [Dyed Yards] int, [Detected Yards]
> int, Total dec(10,3), FaultCause Char(15))
> --Fill table with data
> Insert into #PushTemp exec spcsql.dbo.sp_Buttonpushes '8/15/05', '8/21/05'
> --Create Temp table to hold crosstab results
> Create Table #tabtemp (LotID Char(7), [Broken Filament] Char(15) Default 0
,
> [Dye Grease] Char(10) Default 0, [Dye Spots] Char(9) Default 0, [Dye Strea
ks]
> Char(11) Default 0, [Finish Spots] Char(12) Default 0, [Floats] Char(6)
> Default 0, [Fuzzy Edge] Char(9) Default 0, [Knots] Char(5) Default 0, [Loose
> Edge] Char(10) Default 0, [Mispick] Char(7) Default 0, [No Fault] Char(8)
> Default 0, [Not Listed] Char(10) Default 0, [Repair Broken Filament] Char(
20)
> Default 0, [Run Out] Char(7) Default 0, [Sewings] Char(7) Default 0, [Tight
> Loose Ends] Char(16) Default 0, [Water Spots] Char(11) Default 0, [Wavy Ed
ge]
> Char(9) Default 0, [Weave Grease] Char(12) Default 0)
> --Crosstab the table
> insert into #tabtemp exec sp_JRMCrossTab
> '#PushTemp',
> 'LotID',
> 'FaultCause',
> 'Total',
> NULL,
> 'SUM'
> Select Distinct t.LotID, p.[Dyed Yards], p.[Detected Yards], t.[Broken
> Filament],
> t.[Dye Grease], t.[Dye Spots], t.[Dye Streaks], t.[Finish Spots], t.[Fuzzy
> Edge],
> t.[Floats], t.[Fuzzy Edge], t.[Knots], t.[Loose Edge], t.[Mispick], t.[No
> Fault], t.[Not Listed], t.[Repair Broken Filament], t.[Run Out], t.[Sewings],
> t.[Tight Loose Ends], t.[Water Spots], t.[Wavy Edge], t.[Weave Grease]
> From #tabtemp t inner join #pushtemp p
> on t.LotID = p.LotID|||I am not sure what you are doing here can you please explain.
Adam
"Jens Sü?meyer" wrote:
> Some sample:
>
> CREATE Procedure test
> (
> @.test1 varchar(40),
> @.test2 varchar(40)
> )
> AS
> BEGIN
> Select 1
> END
>
> create table #temptable
> (
> IDCol int
> )
> INSERt INTO #temptable
> EXEC('test ''1'',''2''')
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "A.B." wrote:
>|||On Mon, 22 Aug 2005 07:34:02 -0700, A.B. wrote:

>I am trying to create a temporary table and then run a SP and have the valu
es
>stored in this table. The problem is that the SP does'nt always return a
>value for every column so i get a error saying that the column name or
>definition do not match. How can I get around this problem?
(snip)
>insert into #tabtemp exec sp_JRMCrossTab
> '#PushTemp',
> 'LotID',
> 'FaultCause',
> 'Total',
> NULL,
> 'SUM'
Hi A.B.,
Change this to
INSERT INTO #tabtemp (column1, column2, column3, ..., columnN)
EXEC sp_JRMCrossTab
'#PushTemp',
'LotID',
'FaultCause',
'Total',
NULL,
'SUM'
Also, give your procedure a name without "sp_' as the first three
characters. The sp_ prefix is reserved for system stored procedures.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

File Manipulation from MS SQL Server

I am working on an application which uses DTS to move data into temporary tables. I would like to be able to rename/relocate the source file in order to maintain a historical reference. The process which creates the source file is not flexible at all. Is there a way to manipulate the file's name and/or relocate the file by using SQL Server.

Thanks in advance!

Daniel
Austin, TexasTry looking into xp_cmdshell stored proc in BOL. This will allow you to use DOS commands from a TSQL script. We had to use this stored proc to rename delimited files we created from a DTS to be exported to a marketing company. Works pretty good.

HTH

DMW|||Hi DMW,

Thank you very much! I will give this a try.

Sincerely,

Daniel
Austin, Texas|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(RowNum int IDENTITY(1,1), Data varchar(8000))
GO

INSERT INTO myTable99(Data) EXEC master..xp_cmdshell 'Dir C:\*.*'

SELECT * FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||I use sp_oaxxx with FileSystemObject, because xp_cmdshell will require for a user to have privileges on the target file system.