September 15, 2009 at 1:00 am
Hi,
I'm not sure if I should post this under SSIS or T-SQL, so, if a mod decides the other forum is better then please feel free to move this post.
I am having trouble with the t-sql code below. It is running in a SSIS package, if I copy and paste this code into SSMS (from SSIS) then I get different results inserted into the cns_bestnumberexportarchive table. Executing the code in SSMS will insert the correct number of records which is much lower than when it is executed by SSIS.
Does anyone have a clue as to why this is happening.
DECLARE @ATTEMPTS INT
--
--
SELECT @ATTEMPTS = CONVERT(INT, cDATA)
FROM cns_SYSConfig
WHERE cVARIABLE = 'NumberAttempts'
--
--
DECLARE @MAXBNADATE DATETIME
SELECT @MAXBNADATE = MAX(DateInserted) FROM cns_BestNumberArchive
--
--
DECLARE @DTS DATETIME
SET @DTS = @MAXBNADATE
--
--
PRINT '@DTS = ' + convert(varchar(20), @DTS)
PRINT '@MAXBNADATE = ' + convert(varchar(20), @maxbnadate)
--
--
IF OBJECT_ID('TEMPDB..#NUMBERDETAILS') IS NOT NULL
DROP TABLE #NUMBERDETAILS
--
--
--
SELECT *
INTO #NUMBERDETAILS
FROM (
SELECT DISTINCT
ND.ID10,
ND.PhoneNum,
ND.PhoneNumType,
ND.OriginalSource,
ND.SourceId
FROM cns_NumberDetail ND
INNER JOIN cns_BestNumberArchive BNA ON ND.ID10 = BNA.ID10
AND ND.PhoneNum = BNA.WORK
AND ND.PhoneNumType = 1
WHERE BNA.DateInserted = @MAXBNADATE
UNION ALL
SELECT DISTINCT
ND.ID10,
ND.PhoneNum,
ND.PhoneNumType,
ND.OriginalSource,
ND.SourceId
FROM cns_NumberDetail ND
INNER JOIN cns_BestNumberArchive BNA ON ND.ID10 = BNA.ID10
AND ND.PhoneNum = BNA.CELL
AND ND.PhoneNumType = 2
WHERE BNA.DateInserted = @MAXBNADATE
UNION ALL
SELECT DISTINCT
ND.ID10,
ND.PhoneNum,
ND.PhoneNumType,
ND.OriginalSource,
ND.SourceId
FROM cns_NumberDetail ND
INNER JOIN cns_BestNumberArchive BNA ON ND.ID10 = BNA.ID10
AND ND.PhoneNum = BNA.HOME
AND ND.PhoneNumType = 3
WHERE BNA.DateInserted = @MAXBNADATE
) A
--
--
CREATE NONCLUSTERED INDEX CIS ON #NUMBERDETAILS
(
ID10 ASC,
PHONENUMTYPE ASC,
PHONENUM ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
--
--
IF OBJECT_ID('TEMPDB..#DMCIS') IS NOT NULL
DROP TABLE #DMCIS
--
--
SELECT DISTINCT
CONVERT(BIGINT, CIS) [CIS]
INTO #DMCIS
FROM (
SELECT DISTINCT
CIS
FROM cns_DiallerImportArchive
WHERE DataSourceID = 8
AND Valid = 1
AND CONVERT(DATETIME,CONVERT(VARCHAR,DateInserted,111)) = (
SELECT CONVERT(DATETIME,CONVERT(VARCHAR,MAX(DateInserted),111))
FROM cns_DiallerImportArchive
WHERE DataSourceID = 8
)
UNION
SELECT DISTINCT
CIS
FROM cns_DiallerImportArchive
WHERE DataSourceID = 11
AND Valid = 1
AND CONVERT(DATETIME,CONVERT(VARCHAR,DateInserted,111)) = (
SELECT CONVERT(DATETIME,CONVERT(VARCHAR,MAX(DateInserted),111))
FROM cns_DiallerImportArchive
WHERE DataSourceID = 11
)
) A
--
--
IF OBJECT_ID('TEMPDB..#PRODCOUNT') IS NOT NULL
DROP TABLE #PRODCOUNT;
WITH CTE_PROD AS (
SELECT
A.CIS,
C.DESCRIPTION,
COUNT(1)VOL
FROM #DMCIS A
LEFT JOIN (SELECT AccountNumber, AccountType, CIS FROM cns_AccountDetails WHERE InCollections = 1 AND AccountType IN (1,2,3)) B ON A.CIS = B.CIS
LEFT JOIN cns_AccountType C ON C.AccountTypeID = B.AccountType
GROUP BY A.CIS, C.DESCRIPTION
)
SELECT
CIS,
ISNULL(CA,0)CA,
ISNULL(SA,0)SA,
ISNULL(HL,0)HL
INTO #PRODCOUNT
FROM CTE_PROD
PIVOT (
SUM(VOL) FOR DESCRIPTION IN ([CA], [SA], [HL])
) AS A
--
--
CREATE NONCLUSTERED INDEX CIS ON #PRODCOUNT
(
CIS ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
--
--
IF OBJECT_ID('TEMPDB..#PREVBESTNUM') IS NOT NULL
DROP TABLE #PREVBESTNUM
--
--
SELECT A.*
INTO #PREVBESTNUM
FROM cns_BestNumberExportArchive A
INNER JOIN #DMCIS B ON A.CIS = B.CIS
INNER JOIN (SELECT CIS, MAX(DATESENT)[DATESENT] FROM cns_BestNumberExportArchive WHERE SYSTEMID = 1 GROUP BY CIS) C
ON A.CIS = C.CIS AND A.DATESENT = C.DATESENT
WHERE A.SYSTEMID = 1
--
--
print 'dm prevbestnum insert above'
--INSERT THE NEW(NOT PREVIOUSLY SENT) DATA INTO THE ARCHIVE TABLE
--
--
CREATE NONCLUSTERED INDEX CIS ON #PREVBESTNUM
(
CIS ASC,
PHONENUMTYPE ASC,
PHONENUM ASC
)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
--
--
INSERT INTO cns_BestNumberExportArchive
SELECT DISTINCT
DateSent = @DTS,
SystemId = 1,
CIS = CIS.CIS,
PhoneNum = A.PhoneNum,
PhoneNumType = A.PhoneNumType,
SystemSourceId = A.OriginalSource,
SourceId = A.SourceId,
CA = B.CA,
SA = B.SA,
HL = B.HL,
Card = 0
FROM #DMCIS CIS
LEFT JOIN cns_BestNumberArchive BNA ON CIS.CIS = BNA.CIS
LEFT JOIN #NUMBERDETAILS A ON A.ID10 = BNA.ID10
LEFT JOIN #PRODCOUNT B ON B.CIS = BNA.CIS
LEFT JOIN #PREVBESTNUM D ON D.CIS = CIS.CIS
AND ISNULL(D.PhoneNum, 0) = ISNULL(A.PhoneNum, 0)
AND ISNULL(D.PhoneNumType, 0) = ISNULL(A.PhoneNumType, 0)
WHERE D.CIS IS NULL
--
--
print 'dm insert above'
----------------------------------------------------------------------------------------------
Struggling to find the question?
http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
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 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply