March 5, 2009 at 8:31 am
Hi,
I’m trying to build a table dynamically by concatenating a string.
Set @v_exec_str = ‘create table … (col_1, col_2, …)’
Open raw_Cur
Fetch raw_Cur into @some_pattern
While …
Begin
select @next_col = col
from…
where col = @some_pattern
set @v_exec_str = @v_exec_str + @next_col
end
PRINT @v_exec_str
--exec @v_exec_str
As a result by the end of the cursor @v_exec_str has absolutely working ‘create table’ code, which fails with error
Msg 203, Level 16, State 2, Line 99
The name 'create table APSS_2008_RAW (col_1, col_2, …)’ is not a valid identifier.
If I just copy, paste and run this the very same statement in a query editor, it does build the table.
What could be the problem?
March 5, 2009 at 8:40 am
Hi
It seems that some parts of your statement are not shown correctly, but I think I know the problem.
Use:
EXECUTE sp_executesql @v_exec_str
Instead of
EXEC @v_exec_str
Greets
Flo
How to Post Data/Code to get the best Help How to Post Performance Problems
March 5, 2009 at 8:58 am
Could we see the fully generated string please? Preferably, one generated with a print statement right before statement 99 where the error occurs.
However, I'm pretty sure Florian's right. "Create Table" is not a stored proc that can be the subject of an EXEC. Simple proof:
declare @sql as nvarchar(1000)
set @sql = 'create table test (col1 int)'
execute sp_executeSQL @sql
drop table test
exec @sql
drop table test
To use sp_executeSQL, you should make sure that the variable containing the code to be executed is ntext, nchar, or nvarchar.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 5, 2009 at 10:07 am
Use parentheses
exec ( @v_exec_str )
Derek
March 5, 2009 at 10:50 am
DOH!! :w00t:
That's what I get for having gotten away from using EXEC.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 5, 2009 at 11:47 am
Bob Hovious (3/5/2009)
DOH!! :w00t:That's what I get for having gotten away from using EXEC.
*affirmative*
😉
How to Post Data/Code to get the best Help How to Post Performance Problems
March 6, 2009 at 12:44 pm
Thanks a lot, guys! It worked.
Derek's solution worked out more comfortable for me 'cause I didn't have to go change all the varchars to nvarchars (that's just too much to do even with search-n-replace) 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply