SP Output Parameter Issue (across ODBC Connection)

• Flipz

  SSC Veteran

  Points: 223

  More actions

  August 12, 2013 at 3:04 am

  #281042

  Hello,

  I am having an issue retrieving a SP's Output parameters over an ODBC connection (Driver: SQL Server Native Client 10).

  When the SP is not attempting any inserts/deletions or updates, the Output params are returned across the ODBC connection fine. However, as soon as I add in a simple update statement (updating one field in one record), it is suddenly unable to retrieve the Output params.

  I have tried this on other SPs (with different update statements) and the same is happening? HOWEVER, when I capture the ODBC call to the SP in 'SQL Server Profiler' and run in the Management Studio then it returns the Output params fine which is why I am thinking it is an ODBC issue/restriction? All accounts used are SysAdmin so cannot see any permission issues? I have also removed all triggers on the tables being updated incase these were interfering at all?

  Will paste the SP script/call if required, but any pointers first would be greatly appreciated, 🙂

• Johan Bijnens

  SSC Guru

  Points: 134873

  More actions

  August 12, 2013 at 3:38 am

  #1640490

  1) centrainly not a good practise to use sysadmin privileges in regular aps.

  (even for testing ! The sooner you encouter a security issue the better !!)

  2) are you getting an ODBC error or just faulty data in the output fields?

  3) can you post the sproc DDL ?

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution 😀

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me