September 15, 2009 at 2:51 am
It's possible that this is down to the ANSI options the query is executed with. Not sure how you influence these in SSIS, but you can change them in SSMS from the Query/Query Options menu. Try running the query in SSMS with different settings for ANSI_NULLS, CONCAT_NULL_YIELDS_NULL etc and see if that makes a difference.
Can you spot anything significant about the rows inserted by one script, but not the other.
Try adding SELECT @@rowcount after each step to identify exactly where the differences are being introduced.
September 15, 2009 at 8:37 am
Thanks for your view Ian. How would I be able to use the @@rowcount in SSIS?
I changed the SSIS package to execute the t-sql as a stored procedure. This still isn't working. Another thing I picked up is that if I run the SSIS package in Visual Studio the results are the same as if I was running the code in SSMS (in other words correct). If I export that package to the server and execute it in a job the results are incorrect. I can't understand why.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
September 15, 2009 at 10:00 am
This is just a shot in the dark, but are you sure you're pointing to the same server environment? I once deleted my config file for a package and my package ran anyway, picking up the test values that were in my variables.
September 16, 2009 at 1:06 am
I'm not sure how to get at @@rowcount in SSIS (don't have an SSI environment available at the moment).
Try putting different SET statements at the top of your procedure and running it in SSMS to see if one of them will repeat the behaviour of your SSIS environment.
September 16, 2009 at 1:54 am
Ok, I checked and the package and t-sql code is pointing to the correct environment. I think I'm going to declare a variable in the stored procedure and assign rowcounts to it. Then I'll push the values into a table and compare the differences.
Thanks for all your help. This one's gonna take a while as I have to fix a different issue on production at the moment. Will let you know of the results later.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
September 16, 2009 at 5:39 am
Okay, now I am officially stumped :blink: .
I copied the package to a different server. Created a new job and executed it. On this new server the results returned are correct, on the production server different(incorrect).
Note: I did a backup of the database on the production server and restored it on the new server. Then copied the package over, created the job in SQL Server Agent and started it.
So, my thinking is that it could be a setting or option in the setup of the production sql server. But then again I am a noob at playing DBA so I'm not sure.
Any ideas, anyone?
HHHHHHEEEEEEEEEEELLLLLLLLLPPPPP
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
September 16, 2009 at 5:48 am
As I hinted in my earlier posts, it sounds as though you have different ANSI settings.
In SSMS, right click the server and click properties. On the Connections page, you can set the default connection options. Are they different on the servers concerned?
September 16, 2009 at 6:56 am
Unfortunately not. I checked both servers and the connection settings are exactly the same. All "Default Connection Options" unticked. I've also compared the remaining settings between the 2 servers and can't find any differences.
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
September 16, 2009 at 7:03 am
Damn!
In one of your earlier posts, yoa said
I think I'm going to declare a variable in the stored procedure and assign rowcounts to it. Then I'll push the values into a table and compare the differences.
Did you do this?
I think the only way to get to the bottom of this is to decompose the steps, run them in the good and the bad environment and see at what point the results diverge.
September 16, 2009 at 7:31 am
Ian, you are going to be so upset with me man!
I did do the rowcount inserts. I rolled out the change to the production environment. After the package had run I opened up the table to see the results of the rowcount inserts and the table WAS EMPTY! That threw me off totally.
Then I went and checked the job in SQL Server agent and found that the package being executed was:
[SERVERNAME1\SERVERNAME1]\csn_BESTNUMBER
when instead it should have been:
[SERVERNAME1\SERVERNAME1]\cns_BESTNUMBER
D'OH!
csn_BESTNUMBER shouldn't even be there, its a spelling error in the package name. I kept on overlooking the spelling error. I feel like such a idiot (actually had something worse there, but thought better of it)...
Sorry for wasting your time
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
September 16, 2009 at 7:52 am
No worries... glad you got there in the end.:-)
Viewing 11 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply