quotation syntax

Question

Post reply

quotation syntax

smknox

Hall of Fame

Points: 3275
More actions
February 25, 2006 at 12:53 pm

#113417

Really need help here:
I am trying to use the following to print a script that would insert records. I am okay up to the point I am trying to list columns to be included (@cols_to_include). In this example, I am using a single column (ipsil), but also need syntax for multiple columns. This was given to me but I think is originally from SQLserverCentral by Vyas Kondreddi. I can post the SP if needed, but just trying to pass parameters to the SP.
EXEC master.dbo.xp_cmdshell 'osql -S. -E -dTest -Q"EXEC sp_generate_inserts ''OccRadiology'',''Fact'',''@debug_mode=1'',''@cols_To_Include="''''Ipsil''''"'' -w8000 -h-1 -o c:/Artifex/inserts.sql'
This generates an error of unclosed ' or "error near '-' and the error resolves if I remove the @cols_To_Include

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andrew Barbieri SSC Rookie Points: 25 More actions · Answer 1

Hi, try removing the double quotes and use single quotes.

Example -

EXEC master.dbo.xp_cmdshell 'osql -S. -E -dTest -Q"EXEC sp_generate_inserts 'OccRadiology','Fact',@debug_mode=1,@cols_To_Include='Ipsil' -w8000 -h-1 -o c:/Artifex/inserts.sql'

The double-quote character shouldn't be used for passing strings into char,nvarchar,etc.

vadba SSChampion Points: 11133 More actions · Answer 2

I'd do something like like this (remove the PRINT after you're satisfied that the format is correct):

DECLARE @cmd varchar(8000)

SET @cmd = 'osql -S. -E -dTest -Q "EXEC sp_generate_inserts ''OccRadiology'',''Fact'',@debug_mode=''1'',@cols_To_Include=''Ipsil'' -w8000 -h-1 -o c:/Artifex/inserts.sql'

PRINT @cmd

EXEC master.dbo.xp_cmdshell @cmd

Whenever your dealing with embedded quotes, you should always store the result to a variable and then print it out to verify you formatted it correctly. It's just too easy to make mistakes without doing so.

smknox Hall of Fame Points: 3275 More actions · Answer 3

okay, it looks like the above solved the issue with the @col_to_include parameter.

Now I have this error: Line 1: Incorrect syntax near '-'.

There are many dashes. How do I find what it's complaining about?

Thanks,

Sam

smknox Hall of Fame Points: 3275 More actions · Answer 4

here is the statement printed with print @cmd:

osql -S. -E -dRestructureTest -Q"EXEC sp_generate_inserts 'OccRadiology','Fact',@debug_mode=1,@cols_To_Include='Ipsil','Contra' -w8000 -h-1 -o c:/Artifex/inserts.sql

Sam

vadba SSChampion Points: 11133 More actions · Answer 5

The problem is again with @cols_To_Include: @cols_To_Include='Ipsil','Contra' is incorrect due to the quotation marks. What format does the sp_generate_inserts stored procedure expect for the @cols_To_Include parameter?

I would think it would be Ipsil,Contra. That is, when @cols_To_Include is parsed, there aren't any quotes in the string, just commas to delimit the column names. If that is the case, try this:

DECLARE @cmd varchar(8000)

SET @cmd = 'osql -S. -E -dTest -Q "EXEC sp_generate_inserts ''OccRadiology'',''Fact'',@debug_mode=''1'',@cols_To_Include=''Ipsil,Contra'' -w8000 -h-1 -o c:/Artifex/inserts.sql'

PRINT @cmd

smknox Hall of Fame Points: 3275 More actions · Answer 6

mkeast, thanks for bearing with me. I'm a newbie and truly baffled by this. This statement executes properly from QA:

EXEC sp_generate_inserts OccRadiology, Fact, @debug_mode=1, @cols_to_include ="'AilmentID','Ipsil','ContraB'"

debug_mode is bit type (so do I need the quotes around it?)

in the comments of the sp, it states that it wants @cols_to_include (varchar) to be in this form: " ' col1 ' , ' col2' "

thanks so much,

Sam

vadba SSChampion Points: 11133 More actions · Answer 7

Wow..this is a lot harder than I expected. Let's give it another try.

I think the problem is that when you run xp_cmdshell, the command processor also parses the line - but handles quotes differently than SQL Server. To get the double quotes to work, use the backslash escape character, like this:

DECLARE @cmd varchar(8000)

SET @cmd = 'osql -S. -E -dTEST -Q "EXEC sp_generate_inserts OccRadiology, Fact, @debug_mode=1, @cols_To_Include = \"''AilmentID'',''Ipsil'',''ContraB''\"" -w8000 -h-1 -r -o c:\Artifex\inserts.sql'

PRINT @cmd

EXEC master..xp_cmdshell @cmd

I'm still puzzled as to why double quotes work and single quotes do not when working with the @col_to_include parameter. If they did, we wouldn't have this problem.