I'm trying desperately to execute two statements in a single expression variable. I'm using the expression as a variable because this is intended as a template for other extractions and I want to minimize the customization. Basically this is my code: *"Truncate Table xxx.dbo."+@LoadTableName +" GO " +"Truncate Table xxx.dbo."+@LoadTableName +"_Rejects" +" GO"* The expression evaluates as: *Truncate Table xxx.dbo.TableName GO Truncate Table xxx.dbo.TableName_Rejects GO* I think it's the fact that it's all jumbled up on one line that is the problem. I'm pretty sure GO needs to be isolated for it to execute. However, I can't for the life of me figure out how to force the expression to carriage return when a variable is used. When no variable is used it's a no-brainer but the moment you include a variable it compacts. Any advice is greatly appreciated!
copy and paste this into the expression window of the variable, and see how it goes. If you look at the variables proerties, in the Expression property you should be able to see two little squares before and after each go statement "Truncate Table xxx.dbo."+@LoadTableName +" GO " +"Truncate Table xxx.dbo."+@LoadTableName +"_Rejects" +" GO"
You can try passing the characters for "New Line" = CHAR(13) + CHAR(10): DECLARE @Command varchar(255), @LoadTableName varchar(255)='TestTable' SELECT @Command='Truncate Table xxx.dbo.'+@LoadTableName +' ; ' + CHAR(13) + CHAR(10) +'Truncate Table xxx.dbo.'+@LoadTableName +'_Rejects' +' ;' PRINT @Command IIRC, `GO` is a special command that may not be interpretted correctly if sent to SQL Server in anything other than SSMS. Separate commands with a semicolon. *EDIT: added char(10) to script and description, fingers were faster than my brain!*
Thanks for all the answers! I tested both the semicolon suggestion and the solution that Daniel Ross put forward. Both ideas work great. It looks like the GO command works fine as long as it is isolated on its own line but the semicolons are even easier because line formatting is irrelevant. Cheers!