Friday, August 1, 2008

"The operation has timed out" while creating a snapshot (SSRS)

I've deployed a rather big report project with several subreports and a huge main one. That main report had two possible values for a parameter, and, depending on them, could run from 1 to 6 minutes. Well, for a customer 6 minutes was unacceptable wait time, but then the data was not supposed to be updated often, so a snapshot option looked good in this case.
In the report Properties window I opened the Execution tab and chose "Render this report from an execution snapshot" option. Then I've configured a schedule to have snapshots created daily, made sure that the "Create a snapshot of the report when this page is saved" option is checked (which is a default state), and clicked OK to save the properties...

NB! As I've mentioned, there were only two possible values for the only parameter of the main report. To run a report with parameters from a snapshot you need to have a separate version of your report with default values for each set of parameters. So, I actually had TWO reports instead of one, corresponding to two (hard-coded as a default for each version) possible values of a parameter. If you cannot allow yourself such a luxury, you need to turn to a cashing option instead...

So, I had to perform above mentioned properties setup twice. However, when I did that for the second value of a parameter (with which the report was supposed to run for 6 minutes), after just about 2 minutes of execution I got an error: "The operation has timed out"! Obviously, that was pertaining to the operation of the properties saving, because the report execution timeout was set up to unlimited time. After checking this issue on Internet I found out that other people have been fixing it by changing timeout value in system parameters... oh well, it was too much not only for me, but for our sysadmin as well. He tried several fixings, but the result was the same. After one of those fruitless attempts I looked at the project online and found that all the components are up and running except this big report, of which I got the following information:
"The selected report is not ready for viewing. The report is still being rendered or a report snapshot is not available. (rsReportNotReady)". That looked as a dead-end situation.

But then, in a due time of 6 minutes the report became available online, and has been available since then with no delay! Obviously, in spite of an error message, behind the scenes the snapshot was continuing to create!

CONCLUSION: don't panic upon seeing "The operation has timed out" error message while creating an initial snapshot of a long-running report. It could well be that in a due time the snapshot is successfully created. I killed a couple of hours (so did our sysadmin) to resolve the timeout problem, while in fact it had no impact on the resulting performance.

Wednesday, July 16, 2008

Query to look for text in procedures

This is a query that I use every day! Say, you've changed a datatype of a field, or renamed it, and now you need to change it everywhere where it's been called from procedures. Or you have hardcoded something (or someone before you did it), and now you need to change it or, better, get rid of it. Anyway, you will have to look for some set of symbols in all your procedures. There are, actually, quite a few samples of how to do this on the Internet, this is just one of them:


USE [YourDataBaseName]
GO
-- create temporary Result table to keep names and text of the procedures in the database :
CREATE TABLE #Result
(TextField varchar(max), ProcName varchar(100))


-- create temporary ProcName table with the names of all the procedures in the database:
CREATE TABLE #ProcList
(ID int IDENTITY, ProcName varchar(100))


-- populate the ProcName table with the procedure names:
INSERT #ProcList SELECT [name] from sys.procedures


-- get the number of procedures (to be used in the loop below):
DECLARE @NumberOfProcs int
SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures


-- loop to populate the Result table:
DECLARE @i INT
SET @i = 1
DECLARE @ProcName varchar(100)
DECLARE @SQL varchar(2000)
WHILE @i <= @NumberOfProcs
BEGIN
SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i

-- and ProcName like '%proc name%'
SET @SQL = 'INSERT INTO #Result (TextField) EXEC sp_helptext ' + @ProcName
EXEC (@SQL)
UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
SET @i = @i + 1
END


-- look for a string you need [your string] in the Result table
SELECT * FROM #Result WHERE TextField LIKE '%[your string]%'


-- clean up
DROP TABLE #Result
DROP TABLE #ProcList


--
Additional observations.
1. The general idea is to use the sp_text procedure.
2. Now, Pinal Dave, who runs a very useful blog on SQL server, has something much shorter for this (and guess what, it works just the same, even faster :) ):

USE [YouDatabaseName]
GO
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%[your string]%'

Tuesday, July 15, 2008

Error message "String or binary data would be truncated"

It is Msg 8152, and it's usually followed by "The statement has been terminated" message. It's actually very easy, still, I learned to get scared of it after, like, 5-th (10-th?) time I had it.
--
What happened. Nothing really bad: you just tried to populate a field with a string longer than the field's designed length. It's good to know: maybe you'd reconsider such an update.
--
Workaround. It you, however, want to stick a long string into a short field, just issues a statement: SET ANSI_WARNINGS OFF. You'll be able to commit updates without further interruption: extra symbols will be silently truncated.
--
Additional observation. One of the web articles explaining this error had a comment: "Profiler is handy in troubleshooting this error. " Never had a change to find out what this means... (feels like it's worth trying)

Saturday, July 12, 2008

Error message "The row value(s)... alter multiple rows"

The full text of the error message is following:

"No row was not updated.
The data in row [number] was not committed.
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows ([number] rows)."
--
How it happened. I received this error message (and, indeed, was not able to commit any updates) when I was trying to change a value through the 'OPEN TABLE' option of SSMS. I saw that I am able to change values in some rows, but other give me this message. How could I alter multiple rows if, while working directly in the open table, you can only update 1 value in 1 row at a time? And, of course, T-SQL statement was allowing to update any values with no error messages.
--
Explanation. It could be, indeed, that you have several rows with all the same values. One of the cases - you've recently dropped an identity field. Another case - you've just made changes to a row that turned it into a duplicate of another row. It is allowed, however, this row immediately turns into a read-only (it has a lighter grey color than other rows), and now, if you try to update its duplicate - you get this error message.
Workaround. There are cases when you want to make some hand-made changes directly in the table. If the situation of a non-uniqueness could occur, create a new identity field - then do whatever updates you need - then drop the new identity field.
--
Additional observations. The same can happened if there are constrains that tie a table with other tables, although I did not investigate this case too far.