October 18, 2001 at 7:53 am
With the following stored procedure, MyProc, I would like to dynamically create a table which name is passed as a parameter and
fill it with the result of a call to xp_cmdshell. But if I execute MyProc, I will get an empty table.
What's wrong with it and how can I correct it?
Create Procedure MyProc
@TableName VarChar(100)
AS
Declare @sql VarChar(1000)
Declare @cmd VarChar(1000)
SELECT @sql = 'Create Table ' + @TableName + '('
SELECT @sql = @sql + 'Data VarChar(10))'
Exec (@SQL)
SELECT @sql = 'INSERT ' +
SELECT @cmd = 'testCMD hello'
Exec ( @sql)
Exec xp_cmdshell @cmd
October 18, 2001 at 10:03 am
I gotta ask - why would you do this??
Couple things wrong. Your insert is incomplete and you don't need to use xp_cmdshell, just another exec.
Andy
October 29, 2001 at 5:16 pm
Agree with Andy Warren - you might want to do it with a global temp table though for a bcp / xp_sendmail
anyway - something like this should work.
Whenever you're building something that deals with dynamic sql try replacing the exec's with print statements - you'll soon see what's wrong and if not can execute the statements produced.
Declare @sql VarChar(1000)
SELECT @sql = 'Create Table ' + @TableName + '('
SELECT @sql = @sql + 'Data VarChar(10))'
Exec (@SQL)
SELECT @sql = 'INSERT ' + @TableName + ' (Data) select ''hello'''
Exec (@SQL)
Cursors never.
DTS - only when needed and never to control.
October 30, 2001 at 12:46 pm
I'd agree to also use a temp table or a staging table. Personally I dislike temp tables, there have been issues in the past with them.
Steve Jones
October 30, 2001 at 1:04 pm
October 30, 2001 at 1:47 pm
Some memory leaks (v6.5). In 7 there were some problems in stored procs with the creation of a temp table within code. Seemed to be fine if created at the beginning.
Steve Jones
October 30, 2001 at 2:31 pm
The v7 problems are probably the locking issues where if a temp table was created in one sp and used in a called one then it was fine in a single user system but if run from several spids the first was ok, the second got an error and further spids didn't error but failed to run.
This was fixed in sp2 along with other concurrency problems.
There is a big difference in v7 over 6.5 where you now can't create two temp tables on the same spid with the same name - it doesn't error but gets confused.
They use resources and lock system tables so probably aren't a good idea for a lot of users but otherwise can simplify code a lot.
Cursors never.
DTS - only when needed and never to control.
October 30, 2001 at 2:35 pm
I dont use them heavily, but I do use them, never had an issue so far.
Andy
October 30, 2001 at 3:00 pm
College had an interesting one recently
update #tbl
set fld = 'asd'
from #tbl left outer join #tbl2 on #tbl.fld1 = #tbl.fld2 and #tbl2.fld3 = 'zxcv'
where #tbl.fld4 = ''
It should have updated every row but updated batches of rows instead.
Taking out the fld3, fld4 or turning left outer into inner fixed it.
It looked like it was missing pages so I rebuilt the indexes and that fixed it but it came back again later.
Changed the code to be two updates on an inner join but was a bit worrying.
Cursors never.
DTS - only when needed and never to control.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply