Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
New Learner
Starting Member
3 Posts |
 Posted - 2009-03-03 : 07:27:35
|
| Hi, I have a source SQL table where data is present and has 'InsertedDateTime' as one of the parameters. I have a destination table (on another server) and a Stored porcdure that runs a Linked Server Query from the Source Table between 2 InsertedDateTime values and inserts select fields into the destination table. This stored procedure is executed every 5 minutes and runs 10 min behind the source so that I do not miss out on data while its being inserted. For e.g. - when the stored proc is executed at 0810 hrs it pulls data from 0800 to 0805 hrs. Exec SP '0800' '0805' (Time 0810)I find something amusing. When the job runs, occasionally I observe that all the data is not loaded. Data (count) is either 0 or partial in the destination table. Other times the counts match. I run the Stored proc for the same date timings later manually, I get the expected output set and insert count matches with the source. I have no clue of why this happens occasionally. I am using Queries with NOLOCK. Please do advice. Thanks in advance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-03-03 : 08:01:56
|
Can we see the code?
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-03 : 11:02:41
|
| are you using transaction while data is inserted to source?it may be that those data didnt get commited when procedure was executing |
|
|
|
New Learner
Starting Member
3 Posts |
Posted - 2009-03-04 : 06:08:47
|
| Hi, Below is the Stored Proc - CREATE PROCEDURE [dbo].[USP_ExtractInfo] @FromInsertedDateTime DATETIME, @ToInsertedDateTime DATETIMEASSET NOCOUNT ON; SELECT LSV.[InsertedDateTime] AS [InsertedDateTime] ,LSV.[FIELD1] As [FIELD1] ,LSV.[FIELD2] AS [FIELD2] ,LSV.[FIELD3] AS [FIELD3] ,LSV.[FIELD4] AS [FIELD4] ,CAST(IT.[FIELD2A] AS NVARCHAR(10)) AS [FIELD2A] ,CAST(IT.[FIELD2B] AS NVARCHAR(2)) AS [FIELD2B]FROM [LINKED_SERVER].[VIEW] LSV WITH(NOLOCK)LEFT OUTER JOIN [INTERNAL_TABLE] IT WITH (NOLOCK)On IT.Id = LSV.[No]WHERE [InsertedDateTime] >= @FromInsertedDateTime AND [InsertedDateTime] < @ToInsertedDateTime |
|
|
|
New Learner
Starting Member
3 Posts |
Posted - 2009-03-04 : 06:16:47
|
| I am calling this particular Stored Procedure in SSIS. The SSIS package is in Transaction mode. I have identified that the source system does a committ after every 30 seconds of inserts and the issue here is the Stored proc is running 10 min late after the source. For E.g. source would be having Data between 08:00 and 08:09 at around 0810 hrs. But at 0810 hrs, i invoke the Stored proc with parameters 0800 and 0810. The INSERTED DATE TIME is a field in the source with Getutcdate(), so in case the data in the source has an INSERTED DATE TIME of 0804 hours, that is ony after insertions. This occurence is not pretty often, but causes an issue as the data is not picked later as the parameters have already updated. I have created another Stored proc that gets count(*) of both the tables in between the respective times (This stored proc I run after 15 min) and it gives me mismatches occured. And thus I am able to identify the occurence of the issue. Hope i made some sense?? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-04 : 08:59:51
|
| yup. that do make sense. |
|
|
|
|
|
|
|
|
Stored Proc not providing the right outputs