Category: Errors and Exceptions

Errors and Exceptions, SQL, SQL Server

Exception: “Invalid parameter 1 specified for datediff.” in SQL Server

Problem Statement:

One of the view creation scripts in our deployment failed continuously with the below exception:

Invalid parameter 1 specified for datediff.
Incorrect syntax near the keyword ‘AS’.

The root cause of this error is as below.

FLOOR((DATEDIFF("W",ml.column1,ML.column2)+1)/7) AS aliasname

Solution:

FLOOR((DATEDIFF(W,ml.column1,ML.column2)+1)/7) AS aliasname

The change is that the datepart has been modified to remove the double quote.

DBA Utilities, Errors and Exceptions, SQL

Error Exception: Msg 3183, Level 16, State 2, Line 1 RESTORE detected an error on page (0:0) in database “dbname” as read from the backup set.

Recently we encountered the below issue while restoring a database. And we also observed the database has gone into “restoring” mode.

Msg 3183, Level 16, State 2, Line 1
RESTORE detected an error on page (0:0) in database “dbname” as read from the backup set.

The message is clear that there is something wrong on the backup set. There could be many issues related, but most likely reason would be the backup file would have got file corrupted.

You would likely to take a fresh backup and try with restore and in most cases it works good. So, the take away is “we should always validate our backups”. This is exactly what Paul S Randal explains with a survey and more details as here.

See Also

How to recover restoring database in SQL Server

How to recover “Recover Pending” database in SQL Server

If you enjoyed this blog post, feel free to share it with your friends!

Errors and Exceptions, SQL

Error Message: “The database could not be exclusively locked to perform the operation.” in SQL Server

Recently we encountered an error message as below while doing a rename database. So, let us look at the steps that we can use to overcome the issue with this post.

The database could not be exclusively locked to perform the operation.

We were trying to rename a database in one of our lower environment as below and ended up with the error message.

ALTER DATABASE dbname MODIFY NAME = dbname_new

Steps to resolve

  1. Take the database to single_user mode
  2. Rename the database
  3. Take the renamed database to multi_user mode

Script

ALTER DATABASE dbname SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE dbname MODIFY NAME = dbname_new
GO
ALTER DATABASE dbname_new SET MULTI_USER WITH ROLLBACK IMMEDIATE

If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

Warning Message in SQL Server: “Database name ‘tempdb’ ignored, referencing object in tempdb.”

Today, let us quickly try to understand an error message as below.

“Database name ‘tempdb’ ignored, referencing object in tempdb.”.

Many of us would have got this message (I do prefer to call this as a warning message, not error message), however, we would not have noticed as it is not causing any fatal results. With this post, I would like to share my thoughts and like to have your views on this as well.

Let us first see when would you get this message?

use tempdb
GO
create Table #Temp_Table(Col1 int)
GO
Select * From tempdb..#Temp_Table

Now, interesting thing, when the query is changed a bit as below, the message is vanished.

Somehow, I was not convinced the above method as a solution, because that was actually against my understanding of 4 part naming convention (servername.databasename.schemaname.objectname). So, we further tried to test with few more combinations to understand how it works internally.

A simple query as below showed #Temp_Table is created with dbo schema.

As next step, tried to create a schema and a new object is created. If you look at the schema of the object, we can clearly see that it is associated with dbo schema not the new schema. That means, the objects created in tempdb database is always creating in dbo, not in any other schema. In fact, the schema part is actually ignoring for temp tables.

create schema testschema
GO
create Table testschema.#Temp_Table10(Col1 int)

Points to ponder

There is NO need of specifying schema for objects created in tempdb database.

Even we specify a schema name, SQL Server simply ignores the schema part.

If you enjoyed this blog post, please share it with your friends!

Errors and Exceptions, SQL

Error Message : The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query.
Error Message:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

As specified in the error message, its a rare event or exception. The important message from the exception is that “Please simplify the query”. When the query is complex, the relational engine would not be able to create a query plan due to various reasons like the environment resource limitation, reduced capacity etc. and end up with the above exception.

The case I observed is with a dynamic query execution. I do not exactly going to give the query, however tries to provide a more like scenario. I wanted to create a dynamic query with an IN condition where the list of items are separated with a comma like below. 

declare @sql varchar(max)
Declare @values varchar(max)='val1','val2','val3'......'valhugenumber'
Select @sql = 'Select * from ' + @tablename + ' where ' + @columnname + ' in ('+@values+')'
Exec(@sql)


When executing this in one of environments (lower), the error is produced, however, the other interesting thing is its not happening in few other environments(eg. Production). As mentioned above, that is because it depends on the resources involved in various environment matters in producing the query plan.

Resolution:

From the analysis, the issue is associated with long (really long) list of values and when its parsing, the relational engine is not able to create a plan for the query execution. To resolve the issue, the query has been modified not to use the value list variable, instead populated those values into a temp table and then use the temp table as below. 

declare @sql varchar(max)
create table #temp (valuelist varchar(100))
Insert into #temp values('val1'),('val2'),('val3'),......('valhugenumber')
Select @sql = 'Select * from ' + @tablename + ' where ' + @columnname + ' in (Select valuelist from #temp)'
Exec(@sql)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!