passing string literal to stored proc from execute sql task

Question

passing string literal to stored proc from execute sql task

jonathan.matt

Say Hey Kid

Points: 693
More actions
August 4, 2022 at 8:55 pm

#4072677

SQL Server 2017
I am having trouble with an execute sql task.
The sql statement is
exec Integration_RetrieveMessageToProcess ?, ?, Billed
I have tried surrounding Billed with no single quotes, single single quotes, double single quotes and putting Billed into a variable and passing as a parameter. Every time the emitted sql has incorrect single quotes as seen by profiler. Here is an example:
exec sp_executesql N'exec Integration_RetrieveMessageToProcess @P1, @P2, Billed''',N'@P1 float,@P2 float',525,1
Any idea how to do this?
August 5, 2022 at 1:16 pm

This was removed by the editor as SPAM

Viewing 12 posts - 1 through 11 (of 11 total)

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

Ed B Hall of Fame Points: 3666 More actions · Answer 1

Does it work when you run the procedure from SSMS? What error do get when it fails from SSIS? If the procedure has three parameters and the third one is a varchar it should work with single quotes. What does the procedure do with that parameter?

Have you tried it like this? It should be the same as using single quotes with the literal. If that doesn't work, what happens when you run that code in SSMS and replace the question marks with numbers?

DECLARE @v1 FLOAT = ?,
        @v2 FLOAT = ?,
        @v3 VARCHAR(20) = 'Billed'

EXEC  Integration_RetrieveMessageToProcess @v1, @v2, @v3

Thom A SSC Guru Points: 99130 More actions · Answer 2

Using single quotes around the literally should be all your need:

EXEC dbo.Integration_RetrieveMessageToProcess ?, ?, 'Billed';

If you do try this, as Ed asked, what error do you get? Don't tell us about what you see in Profiler, tell us what error(s) you are getting? ARe they SSIS errors or T-SQL errors?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thom A SSC Guru Points: 99130 More actions · Answer 3

dark404 wrote:

Pass a list of strings to a stored procedure SELECT. User.fields_i_want. FROM User. WHERE. User_id IN (SELECT User_id FROM Group_member WHERE Group_id IN (@Search_param)) ORDER BY User_id ASC. EXEC My_user_sp ''Group1', 'Group2', 'Group3''

There are, if I am honest, multiple problems with that SQL. USER is a reserved keyword, IN might as well be =, and the OP is trying to pass three parameters, not one with delimited values... The query seems completely unrelated to the problem, and I suspect that with your signature, you're actually a bot account, so others please just ignore their bad advise.

This reply was modified 2 years, 3 months ago by Thom A.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jonathan.matt Say Hey Kid Points: 693 More actions · Answer 4

Thank you for your responses. Here is the error using single quotes. The sql generated using single quotes from Profiler is above.

Capture

jonathan.matt Say Hey Kid Points: 693 More actions · Answer 5

oh i forgot to mention that of course the sql generated does not run in SSMS but if i just run

EXEC dbo.Integration_RetrieveMessageToProcess 525, 1, 'Billed'

in SSMS it runs

Thom A SSC Guru Points: 99130 More actions · Answer 6

Have you tried as shown in the above? Are you sure it's the EXEC that's actually failing and not the statement(s) inside the procedure?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

jonathan.matt Say Hey Kid Points: 693 More actions · Answer 7

Thanks again everyone. Thom A: I have tried putting the string into a variable and passing all three parameters as variables with same error.

Because testing this in its native production environment is so daunting (requires business users' involvement) I created a test package that runs against a test stored proc and it runs fine! The only difference is that in production the package is called from a SQL Agent job.

The test proc is identical to the prod version so its not coming from the proc.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 8

how is the packaging being executed? and are those parameters to the SP also parameters to the execution of the package?

Ed B Hall of Fame Points: 3666 More actions · Answer 9

jonathan.matt wrote:

The only difference is that in production the package is called from a SQL Agent job.

Are you deploying the project every time you update the string format in the execute sql task? Sorry of that's a stupid question.

It sounds like the job is passing in the same syntax whether you use single quotes, double quotes or no quotes, which suggests your change may not be taking effect. What happens if you change the value of the string to "invoiced" or anything else, do you see that change reflected in the profiler?

jonathan.matt Say Hey Kid Points: 693 More actions · Answer 10

Well, I am a bit worried about myself. There is a second SQL task that indeed had an unclosed single quote. I was so tunneled visioned on what I was certain was the source of the problem that I missed this. Thank you everyone and I am going to eat my humble pie now.