Category Archives: SQL

SQL How to empty all tables in a database

You might need to purge all the data in all the tables in a particular database. For this we can use the following script.

USE <database name>
DECLARE @TableName AS VARCHAR(MAX)
DECLARE table_cursor CURSOR
FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME LIKE '%_Partition%'
OPEN table_cursor
FETCH NEXT FROM table_cursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @SQLText AS NVARCHAR(4000)
SET @SQLText = 'TRUNCATE TABLE ' + @TableName
EXEC sp_executeSQL @SQLText
FETCH NEXT FROM table_cursor INTO @TableName
END
CLOSE table_cursor
DEALLOCATE table_cursor

(73)

Fix: Rule Not clustered or the cluster service is up and online error during update of standalone SQL server

When updating your SQL server (Standalone) with a Cumulative Update (CU) you might check the error in the Rule Check Result.

Rule "Not clustered or the cluster service is up and online." failed.

The machine is clustered, but the cluster is not online or cannot be access from one of its nodes. To continue determine why the cluster is not online and rerun setup instead of rerunning the rule since the rule can no longer detect a cluster environment correctly.

This is a very strange message, especially since the SQL server is a stand alone server and there is no cluster. After a restart of the server and re-trying the update, the problems still persists.

After a lot of investigation, this issue is nothing related to SQL, but WMI. This can be verified by opening the WMI Management using the command WMIMGMT.MSC, right-click on WMI Control (Local) and click on Properties.

If your entries show Invalid Class, as the above screenshot, we need to re-compile the MOF files using the following command from a command prompt as Administrator.

mofcomp C:\Windows\System32\WBEM\cimwin32.mof

The result should be as below

After this, if you rerun the WMIMGMT.MSC and the same process, the WMI should look like this.

You can now re-run the SQL update and it will update with no issues, thus solving the problem permanently.

(3096)

Fix: SharePoint error Cannot generate SSPI context for SQL Server

When having a SharePoint setup you might get the following error in your event viewer.

Event ID 5586 – Unknown SQL Exception 0 occurred. Additional error information from SQL Server is included below. The target principal name is incorrect.  Cannot generate SSPI context.

The main culprit could be the lack of an SPN record. To do this, log into your SQL server as a domain administrator. Launch a command prompt as Administrator and type the following.

setspn -L Domain\UserName

Note: Replace Domain\UserName with the SharePoint user you are using for the services. If you are getting the ‘Cannot generate SSPI context’ you should not see an entry for the SQL server in the command we just executed i.e. you SharePoint farm service user.  Run the command below to add the SPN record for the SQL server

setspn -A MSSQLSvc/SQLServerName.Domain.com:1433 Domain\UserName

Note: Replace SQLServerName.Domain.com with the full FQDN name of your SQL Server. If you are using SQL AlwaysOn Availability or cluster, please enter the full FQDN of the SQL listener. Also change the Domain\Username with the SharePoint service user.

Once executed, run the first command we executed and make sure that you have the SQL SPN record for the username.

This should fix the Event ID 5586

(7159)

Fix: Cannot find server certificate with thumbprint while restoring SQL database

When restoring a database you might get the below error.

System.Data.SqlClient.SQLError: Cannot find server certificate with thumbprint

This is because the database was encrypted with Transparent Data Encryption (TDE) and you will not be able to restore it until you get the Certificate, the Private key and the password from the supplier of the database.

After you collect the required items above, open a new SQL query as the server admin on the database master.

First we need to create the master cerificate on the server by using

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<MyComplexPassword>'
GO

Now that the master certificate has been installed you will need to install the certificate provided by the owner of the database

CREATE CERTIFICATE MyServerCert
FROM FILE = 'C:\TDECert\Cert.cer'
WITH PRIVATE KEY (FILE = 'C:\TDECert\key.pvk',
DECRYPTION BY PASSWORD = '<PasswordProvidedByTheSupplier>');

Once this is done and executed you will be allowed to restore the database.

(27892)

Fix: BEGIN TRY does not work with BULK INSERT on SQL

I have created a stored procedure that does a BULK IMPORT. I wanted to add a bit of fault checking and added a BEGIN TRY and a BEGIN CATCH so that it sends an email with the error message if the T-SQL fails. The problem is that the BEGIN CATCH was being skipped and the procedure stops processing with the below error message.

Msg 4860, Level 16, State 1, Line 2
Cannot bulk load. The file "C:\temp\fileimport.txt" does not exist.

To fix the issue what I did is to add the BULK INSERT command in a variable and execute it. This way, the BEGIN CATCH is not ignored or skipped.

Fix below

BEGIN TRY
DECLARE @bulkimport varchar(1000)
SET @bulkimport = 'BULK INSERT mytable FROM ''C:\temp\fileimport.txt''
WITH (FIELDTERMINATOR = ''*'', ROWTERMINATOR = ''\n'')'
EXECUTE (@bulkimport)
END TRY

BEGIN CATCH
SELECT error_message()
END CATCH

(814)

How to: Execute DOS commands from T-SQL

Sometimes you might have the need to execute a copy, delete or run a batch file from a stored procedure. This can be done by using the xp_cmdshell and execute your command line.

This can be done as below

EXECUTE master..xp_cmdshell 'copy c:\test.txt c:\test1.txt'

You can run it using parameters as below

DECLARE @sql VARCHAR(200)
SET @sql = 'copy c:\test.txt c:\test1.txt'
EXECUTE master..xp_cmdshell @sql

If you are running the xp_cmdshell and get the error "SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server." Follow the code below to enable the feature.

EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO

(4689)

Fix: The backup set holds a backup of a database other than the existing

When restoring a database from an SQL backup .bak, one usually creates a database and then selects the restore function. As soon as you try to restore the database you get the error saying “The backup set holds a backup of a database other than the existing“. This is because it fails to read the files from the restore and matching them to the newly created files.

The error says:

Restore failed for Server ‘SQLSRV01’. (Microsoft.SqlServer.SmoExtended)
Additional Information
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The backup set hold a backup of a database other an the existing ‘test_restore’ database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)

To solve this one should do the following:

– Don’t create an empty database and restore the .bak file on to it.
– Use ‘Restore Database’ option accessible by right clicking the “Databases” branch of the SQL Server Management Studio and provide the database name while providing the source to restore.

This should allow you to restore the database with no error and fix the error 3154.

(12681)

Fix: The In-row data page count for object is incorrect when running integrity check on SQL

When running an integrity check on your databases, you may get the following error on some databases.

Description: Executing the query "DBCC CHECKDB(N'mydb')  WITH NO_INFOMSGS  " failed with the following error: "The In-row data myrow page count for object "my_table", index ID 0, partition ID 52794353778688, alloc unit ID 52794353778688 (type In-row data) is incorrect.

This is due to the fact that the database in question was imported from another or older version of your current SQL server. This can be fixed by running the below command on the databases in question.

DBCC UPDATEUSAGE (dbname)

dbname = The name of your database

When the database is indeed one which is failing the integrity check you will see the below sample message.

DBCC UPDATEUSAGE: Usage counts updated for table 'mytable' (index 'mytable', partition 1):
DATA pages (In-row Data): changed from (2) to (1) pages.
USED pages (In-row Data): changed from (25) to (24) pages.
RSVD pages (In-row Data): changed from (41) to (40) pages

If the database doesn’t need to update it’s usage you will simply get the message below.

DBCC execution completed.

Once you have executed the update-usage on all databases which are failing, run the integrity check again and swish, it works.

 

 

(5174)

Fix: SQL Server Database Services feature state failed during upgrade

During an SQL version upgrade I have encountered that during the checkup, you will get the error stating that Database Services feature state failed. This can be fixed by finding the registry entry below and changing as follows.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationState.

Change all values from 2 to 1 for all Four items in the right pane.

Re-Run the rule on the installation. Now it will be solved and through.

(3724)