passing string literal to stored proc from execute sql task

  • 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?

  • 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
  • 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

  • This was removed by the editor as SPAM

  • 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 6 days, 13 hours ago by  Thom A.

    Thom~

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

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

     

    Capture

  • 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

     

  • 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

  • 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.

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

  • 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?

     

  • 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.

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

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