January 14, 2014 at 3:55 pm
We have a SQL 2008 R2 box and run SSRS in native mode.
Last night we had several reports run. Out of six reports that use a table called ZZ_OmniDGPsrc five executed without issue. However, one failed to execute with the typical "error occurred during report processing" garbage message from SSRS. Luckily I have verbose logging enabled and I got
Query execution failed for dataset 'BRANCHES'. ---> System.Data.SqlClient.SqlException: Invalid object name 'ZZ_OmniDGPsrc'.
The reports are scheduled at 8:30pm, 8:45pm, 9:15pm, 9:30pm, and 10:00pm.
All the reports except the 9:30pm ran without incident.
I've been through the execution logs on the report server, and there are no entries for the missing report.
I've looked through the queries executed just before and after 9:30pm and none of them drop or remove the ZZ_ table.
There is a stored procedure that creates the ZZ_ table and the last time it was run was 5:01pm.
Also the ZZ_ table properties report it was created on 1/14/2014 12:00 PM.
How can I find out the existence/status of the table for the last two days? I can't run a trace, as it happened in the past.
If I can't find out why that table was not there, is it possible to make SSRS wait and try again if it doesn't exist at run time?
January 14, 2014 at 4:14 pm
The other possibility is that the error message is correct and the report server got a connection to the sql server but somehow didn't put the connection in the correct database, as though a 'use database xxx' had failed or the user's default database had changed or was unavailable when the connection was made. In either of those cases, there should be another error message somewhere farther up the trail.
January 15, 2014 at 12:24 pm
Thank you for your reply. I haven't seen anything further up the trail; but I'll keep looking. I ran a Sql Profiler trace last night; but of course the error didn't happen again. I'll let you know if I find anything worth noting out.
January 15, 2014 at 5:03 pm
Any create or drop of an object should be in the default trace.
Do you have the default trace file(s) for the time in question?
Not sure if rename of an object is in there.
What is the create and last modify date of the table in sys.objects?
January 16, 2014 at 4:39 am
asheppardwork (1/14/2014)
There is a stored procedure that creates the ZZ_ table and the last time it was run was 5:01pm.
If there's a process creating the table, something must be dropping it?
What permissions does the report have coming into the database for this table, read\write db roles or explicitly granted permissions?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2014 at 8:20 am
I apologize for the lack of clarity. The Stored Proc. that creates the table, drops it at the start. I scanned all the queries run around the time that the table was not available and did not see the SP run; so it remains a mystery why it wasn't available. I've only seen that message before when the SP was scheduled to kick-off and a report was run during its execution before it had a chance to re-create the table.
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ZZ_OmniDGPsrc]') AND type in (N'U'))
DROP TABLE [dbo].[ZZ_OmniDGPsrc]
... [does work]...
CREATE TABLE dbo.ZZ_OmniDGPsrc
(ROWID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
PKGUID UNIQUEIDENTIFIER NOT NULL,
CUSTNAME CHAR(65) NOT NULL,
SLPRSNID VARCHAR(15) NULL,
XTNDPRCE NUMERIC(15) NULL,
SOPTYPE SMALLINT NOT NULL,
DOCDATE DATETIME NOT NULL,
NONINVEN SMALLINT NOT NULL,
SOPNUMBE CHAR(21) NOT NULL,
ITEMDESC CHAR(101) NOT NULL,
EXTDCOST NUMERIC(19,5) NOT NULL,
VOIDSTTS SMALLINT NOT NULL,
CUSTNMBR CHAR(15) NOT NULL,
CUSTCLAS CHAR(15) NOT NULL,
MRKDNAMT NUMERIC(19,5) NOT NULL,
QUANTITY NUMERIC (19,5) NOT NULL,
QTYFULFI NUMERIC (19,5) NOT NULL,
ITEMNMBR CHAR(31) NOT NULL,
Branch VARCHAR(15) NOT NULL,
SLPRSNFN CHAR(15) NOT NULL,
SPRSNSLN VARCHAR(61) NULL,
INACTIVE TINYINT NOT NULL,
ExPx NUMERIC(21,6) NULL,
Cost NUMERIC(20,5) NULL,
GrossProfit DECIMAL (14,6) NULL,
HasSplit INT NOT NULL
)
SET IDENTITY_INSERT ZZ_OmniDGPsrc ON
... [inserts records from temp tables created and returns the table]
January 16, 2014 at 10:55 am
asheppardwork (1/16/2014)
I apologize for the lack of clarity. The Stored Proc. that creates the table, drops it at the start. I scanned all the queries run around the time that the table was not available and did not see the SP run; so it remains a mystery why it wasn't available. I've only seen that message before when the SP was scheduled to kick-off and a report was run during its execution before it had a chance to re-create the table.
IF EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[ZZ_OmniDGPsrc]') AND type in (N'U'))
DROP TABLE [dbo].[ZZ_OmniDGPsrc]
... [does work]...
CREATE TABLE dbo.ZZ_OmniDGPsrc
(ROWID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL,
PKGUID UNIQUEIDENTIFIER NOT NULL,
CUSTNAME CHAR(65) NOT NULL,
SLPRSNID VARCHAR(15) NULL,
XTNDPRCE NUMERIC(15) NULL,
SOPTYPE SMALLINT NOT NULL,
DOCDATE DATETIME NOT NULL,
NONINVEN SMALLINT NOT NULL,
SOPNUMBE CHAR(21) NOT NULL,
ITEMDESC CHAR(101) NOT NULL,
EXTDCOST NUMERIC(19,5) NOT NULL,
VOIDSTTS SMALLINT NOT NULL,
CUSTNMBR CHAR(15) NOT NULL,
CUSTCLAS CHAR(15) NOT NULL,
MRKDNAMT NUMERIC(19,5) NOT NULL,
QUANTITY NUMERIC (19,5) NOT NULL,
QTYFULFI NUMERIC (19,5) NOT NULL,
ITEMNMBR CHAR(31) NOT NULL,
Branch VARCHAR(15) NOT NULL,
SLPRSNFN CHAR(15) NOT NULL,
SPRSNSLN VARCHAR(61) NULL,
INACTIVE TINYINT NOT NULL,
ExPx NUMERIC(21,6) NULL,
Cost NUMERIC(20,5) NULL,
GrossProfit DECIMAL (14,6) NULL,
HasSplit INT NOT NULL
)
SET IDENTITY_INSERT ZZ_OmniDGPsrc ON
... [inserts records from temp tables created and returns the table]
What about the permissions how are these generated?
Why drop it just to create it again?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 5, 2014 at 12:43 pm
I apologize for the long delay in replying. I actually started a new job yesterday and have spent the last two weeks prepping the old one for my departure, since they did not have a plan to replace me, or hire any dba at all.
I found the issue was resolved by adjusting the locks during the stored procedures. Since the likelihood of dirty reads was really minimal; I felt there was no real reason in making everything wait for locks to work. Especially since the data was created at scheduled intervals before the report processing.
However, to answer your question all the SPs were executed using the sa login for testing and then a admin user who had all but the most powerful sa abilities.
So far so good, and if the continue to have issues; they have my number and I will probably be back here or enlisting a more senior expert to contract out to help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply