Post reply

Sproc returns different number of records...

  • October 31, 2008 at 2:02 pm

    #131959

    Hi Folks,

    My problem seems simple but I must be missing something.

    I have a stored procedure that is used to return a record of one day's accounting activity for a merchant.

    When I run the sproc in SSMS I get a certain number of rows.

    When I run the exact same sproc in the SSIS package using the exact same parameter values I am rewarded with about 2/3s of the rows I get through SSMS.

    I have adjusted the Defaultbuffer size to the 100 MB max. I have left the DefaultBufferMaxRows as it is at 10000.

    I don't see anything in the logging that is generated to tell me that the record set is truncated in any way.

    Can someone please point me in a productive direction to resolve this connundrum?

    Any assistance is appreciated.

    Thank you.

    Richard L. Dawson

    Richard L. Dawson
    Microsoft Sql Server DBA/Data Architect

    I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

  • October 31, 2008 at 2:11 pm

    #892576

    I don't know if this is related to your issue or not, but I had same issue sometime back.

    My production server was on Latin1_general_BIN collation and when I run a select statement with some " where " parameter it use to return me certain number of rows but when I run it from a SSIS package (from a different server, which collation is set to AS_CI) it would return me different number of rows.

    When contacting MS, they told that sorting is different in different collation and run the package from _BIN collate server and see the result. Interestingly, the number of rows returned are same in both studio and SSIS package.

    Give it a try.

    SQL DBA.

  • October 31, 2008 at 2:38 pm

    #892596

    Thanks Sanjay,

    I have seen some strange things with collations too.

    Unfortunately, I am running both the package and the sproc against the same sql server database. So, collation differences aren't likely.

    Thanks again.

    Richard

    Richard L. Dawson
    Microsoft Sql Server DBA/Data Architect

    I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

Viewing 3 posts - 1 through 2 (of 2 total)

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