May 21, 2012 at 4:31 pm
Hello, I'm trying to drop a table if exists then create it as part of a dynamic query but it keeps returning syntax error msg: Msg 102, Level 15, State 1, Line 1 Incorrect syntax near 'tmp05212012MemBuyers'. I think the problem may be when I use the if exists drop table statement because if I don't use it and run the scrip it creates the table successfully. I have to create the table like this because if needs to include the present date when the job runs. Both examples are below, first the section that creates the table succesfully, then the crapped up section. Any light as to where the syntax/errors are will be greatly appreciated.
section 1:
-- drop table tmp05212012InfoReq
Declare @SQLExec2 varchar(250),
@BackName2 varchar(8)
Set @BackName2 = replace(convert(varchar,getdate(),101),'/','')
-- select @BackName
Set @SQLExec2 = 'Select * Into tmp' + @BackName2 + 'InfoReq From #InfoReq'
Exec (@SQLExec2)
Section 2:
Declare @SQLExec varchar(1000),
@BackName varchar(8)
Set @BackName = replace(convert(varchar,getdate(),101),'/','')
-- select @BackName
Set @SQLExec ='if exists (select * from dbo.sysobjects where id = object_id(N' + ' tmp' + @BackName + 'MemBuyers' +
') and OBJECTPROPERTY(id, N' + ' IsUserTable' + ') = 1)' + ' drop table dbo.tmp' + @BackName + 'MemBuyers' +
' Select * Into dbo.tmp' + @BackName + 'MemBuyers From #membbuyers'
Exec (@SQLExec)
May 21, 2012 at 9:25 pm
Take the Exec out and print the SQL statement instead. You will see that the string is missing a bunch of apostrophes around items.
This should be about right.
Declare @SQLExec varchar(1000),
@BackName varchar(8)
Set @BackName = replace(convert(varchar,getdate(),101),'/','')
-- select @BackName
Set @SQLExec = 'if exists (select * from dbo.sysobjects where id = object_id(N''tmp' + @BackName + 'MemBuyers'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)' + ' drop table dbo.tmp' + @BackName + 'MemBuyers;' +
' Select * Into dbo.tmp' + @BackName + 'MemBuyers From #membbuyers'
print (@SQLExec)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 22, 2012 at 7:05 am
Thanks, Matt. It works! I thought it was somewhere in the placing of the quote marks. FYI, it works with "exec" vs "print".
Regards,
John
May 22, 2012 at 7:35 am
latingntlman (5/22/2012)
Thanks, Matt. It works! I thought it was somewhere in the placing of the quote marks. FYI, it works with "exec" vs "print".Regards,
John
If PRINT isn't working, take off the parens.
May 22, 2012 at 2:34 pm
Hey Matt, what if the table is in a different database? For example: DATABASE2.
Where would it fall within the syntax:
Declare @SQLExec varchar(1000),
@BackName varchar(8)
Set @BackName = replace(convert(varchar,getdate(),101),'/','')
-- select @BackName
Set @SQLExec = 'if exists (select * from dbo.sysobjects where id = object_id(N''tmp' + @BackName + 'MemBuyers'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)' + ' drop table dbo.tmp' + @BackName + 'MemBuyers;' +
' Select * Into dbo.tmp' + @BackName + 'MemBuyers From #membbuyers'
print (@SQLExec)
Regards,
John
May 22, 2012 at 2:48 pm
Put a "use" clause in the dynamic statement.
Something like:
Declare @SQLExec varchar(1000),
@BackName varchar(8),
@dbname varchar(100)
Set @BackName = replace(convert(varchar,getdate(),101),'/','')
Set @dbname = 'Database2'
-- select @BackName
Set @SQLExec= 'use ['+@dbname+'];'
Set @SQLExec = @SQLExec+'if exists (select * from dbo.sysobjects where id = object_id(N''tmp' + @BackName + 'MemBuyers'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)' + ' drop table dbo.tmp' + @BackName + 'MemBuyers;' +
' Select * Into dbo.tmp' + @BackName + 'MemBuyers From #membbuyers'
print (@SQLExec)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 23, 2012 at 7:46 am
Thanks, Matt. that works just fine.
Regards,
John
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply