December 7, 2012 at 7:12 am
The situation:
A view : ( vwSource_Galaxy1Reporting_dbo_Companies )
SELECT CompanyUniqueID, CompanyID, Name, Abbr, FOPMask, Serial, LastSerial, RecordVersion, LastUpdate, LastUpdatedBy
FROM Galaxy_LDTORReporting.dbo.Companies
A stored proc that looks at the view:
if OBJECT_ID('tempdb..#company') is not null drop table #company
SELECT
CompanyID,
CompanyCode= Abbr ,
[CompanyName]= LTRIM(RTRIM([Name]))
INTO #Company
FROM vwSource_Galaxy1Reporting_dbo_Companies
TRUNCATE TABLE DIM_Company
INSERT INTO DIM_Company ([CompanyID] ,[CompanyCode],[CompanyName])
SELECT [CompanyID] ,[CompanyCode],[CompanyName] FROM #Company
DROP TABLE #Company
(I didn't write these)
Now. If you run the SP, in SSMS in a query window, all is fine.
If it's run from a job, the results are unpredictable. Sometimes there's be either one of two other sets of values returned.
The job is set up to look at the right database.
I don't understand why running the SP from a job could produce different results from running it in SMS.
It's SQL Server 2K8R2 SP1
DBA (Dogsbody with Bad Attitude)
December 7, 2012 at 8:55 am
December 7, 2012 at 9:00 am
Get rid of the conditional drop of the temp table.
Add error handling.
Could it be that sometimes a calling batch has created a temp table with the same name which is confusing things - the SP could get the wrong temp table structure in the plan.
Truncate and insert should probably be in a transaction.
Could be that something else is accessing the tables at the same time?
For this sort of thing I usually do a merge and record the inserted/updated times.
Cursors never.
DTS - only when needed and never to control.
December 7, 2012 at 10:19 am
Ok, a bit more info has come to light.
Evidently the database that the view references is restored each night, and transaction logs are rolled on every hour.
(Why not use replication? I dunno.)
So, it appears that if the views are recompiled (or rather the code behind them) it works.
Is this due to cacheing?
DBA (Dogsbody with Bad Attitude)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply