locked
Case Sensitivity in sp_describe_undeclared_parameters RRS feed

  • Question

  • 
    
    
    
    
    
    

    We recently upgraded one of our databases to 2012 Compatibility Mode, and are now getting an error from sp_describe_undeclared_parameters when running an SSIS package that one of the parameters isn't valid.

    We've tracked it down to the parameter in the command in the package not having the same case as the parameter in the stored procedure.  This obviously wasn't an issue prior to 2012, and we've been able to reproduce it with other stored procedures and parameters.

    Both the server and database have collations of SQL_Latin1_General_CP1_CI_AS, so I'm not sure where this case sensitivity is coming from...

    This is easily reproduced for us with the following queries:

    create proc test @test1 varchar(128) as

    select * from sys.objects where [name] = @test1

    exec sp_describe_undeclared_parameters N'exec test @test1 = @P1' --runs successfully

    exec sp_describe_undeclared_parameters N'exec test @Test1 = @P1' --invalid parameter

    exec test @test1 = 'test' --Runs successfully

    exec test @Test1 = 'test --Runs successfully

    
    Thursday, July 25, 2013 2:31 PM

Answers

All replies

  • Hello Chris,

    I tested it on our SQL Server 2012 with build 11.0.3000, also SQL_Latin1_General_CP1_CI_AS collation in new created database as well as in an old database we migrated from SQL Server 2005; and it works without any error messages. Which build / patch level are you using?


    Olaf Helper

    Blog Xing

    Thursday, July 25, 2013 2:39 PM
  • Ok, after some further tests I get the same error; strange that the first test was successfull.

    Unfortunately the sp_describe_undeclared_parameters is a XP, so we can't look up the source code and in the docu there is not a case senisitve behaviour mentioned.


    Olaf Helper

    Blog Xing

    Thursday, July 25, 2013 2:52 PM
  • Hello Chris,

    It seems it occurs only when you assign a value for the parameter; without a value it works:

    exec sp_describe_undeclared_parameters N'exec dbo.test @Test1'

    and in the result set you get different case:


    Olaf Helper

    Blog Xing

    Thursday, July 25, 2013 3:04 PM
  • Thanks Olaf, that looks to be the case...

    To make that work in our situation, since sp_describe_undeclared_parameters is being called automatically because of the OLEDB Command task in SSIS (I assume because it's treating the SQL as dynamic SQL and needs to do some sort of validation), the only way for us to fix it would still be to go in and change the packages to not explicitly list the parameter names (and just have values).  That may be best to avoid problems in the future, we'll have to discuss that...

    Everything I'm seeing so far though, it appears that despite not mentioning it in the documentation like you mentioned, it is indeed case sensitive.  So we're probably looking at some testing and changing things no matter what.

    Thursday, July 25, 2013 3:43 PM
    • Proposed as answer by Candy_Zhou Friday, July 26, 2013 7:17 AM
    • Marked as answer by Elvis Long Monday, August 5, 2013 11:33 AM
    Thursday, July 25, 2013 4:05 PM