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.
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.
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!
Problem Statement:
When trying to migrate SQL Server 2008 R2 to SQL Server 2016, we identified an issue with the following error:
Could not load assembly ‘Microsoft.SqlServer.Connectioninfo, Version=10.0.0.0, Culture=netural, PublicKeyToken=89845dcd8080cc91’ or one
Of its dependencies. The system cannot find the file specified.
As a knee jerk reaction, we initially thought it was an issue with SQL Server 2016 installation and need to install the Feature Pack for SQL Server 2016. Once applied the feature Pack, we again tested and found the same issue unfortunately(but really fortunate one!!!).
Analyzing further(I mean, reading the error message clearly, It is found the version 10.0.0 that represents Microsoft® SQL Server® 2008.
As we do not have SQL Server 2008 installed in the environment and the code(.NET application) has some reference to the version mentioned, this was not able to find the dlls in the specified path(C:\windows\assemblies\).
To proceed with our testing, we installed SQL Server 2008 R2 Feature pack and resumed our testing. Finally, it worked like a treat!!!
The learning is we always need to go to the facts(error message) than blindly presume things!!!
please find the below information for various version feature pack references:
Microsoft® SQL Server® 2016 Feature Pack (Version: 13.0.1601.0)
https://www.microsoft.com/en-us/download/details.aspx?id=52676
Microsoft® SQL Server® 2014 SP2 Feature Pack (Version: 12.0.5000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=53164
Microsoft® SQL Server® 2014 Feature Pack (Version: 12.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=42295
Microsoft® SQL Server® 2012 SP1 Feature Pack (Version: 11.0.0.0)
http://www.microsoft.com/en-us/download/details.aspx?id=35580
Microsoft® SQL Server® 2012 Feature Pack (Version: 11.0.2100.60)
https://www.microsoft.com/en-us/download/details.aspx?id=29065
Microsoft® SQL Server® 2008 R2 SP2 Feature Pack (Version: 10.50.4000.0)
https://www.microsoft.com/en-us/download/details.aspx?id=30440
Microsoft® SQL Server® 2008 R2 SP1 Feature Pack (Version: 10.50.2500.0)
http://www.microsoft.com/en-us/download/details.aspx?id=26728
Microsoft® SQL Server® 2008 R2 Feature Pack (Version: 10.50.1600.1)
https://www.microsoft.com/en-us/download/details.aspx?id=16978
Microsoft SQL Server 2008 Service Pack 4 Feature Pack (Version: 10.0.6000.29)
https://www.microsoft.com/en-in/download/details.aspx?id=44277
Microsoft SQL Server 2008 Service Pack 3 Feature Pack (Version: 10.00.5500.00)
https://www.microsoft.com/en-us/download/details.aspx?id=27596
Microsoft SQL Server 2008 Service Pack 2 Feature Pack (Version: 10.00.4000.00)
https://www.microsoft.com/en-us/download/details.aspx?id=6375
SQL Server 2008 Service Pack 1 (Version: 10.00.2531.00)
https://www.microsoft.com/en-in/download/details.aspx?id=20302
SQLZealots 