Search

Monday, April 29, 2013

Verifying Foreign Keys in MS SQL Server

Today I was checking one Demo database and surprised to see that foreign key columns contained some values which were not stored in the referenced tables. The foreign key constraints were there, but they had been deactivated with the WITH NOCHECK clause. 

The below script verify the foreign key constraint:


DECLARE @TableName sysname, @ForeignKey sysname, @String NVARCHAR(1000)

DECLARE cur CURSOR FOR
SELECT t.name, fk.name
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id
ORDER BY t.name, fk.name

OPEN cur 
FETCH cur INTO @TableName, @ForeignKey

WHILE @@FETCH_STATUS = 0 BEGIN
SET @String = 'ALTER TABLE ' + @TableName + 
' WITH CHECK CHECK CONSTRAINT ' + @ForeignKey
PRINT @String
BEGIN TRY
EXEC (@String)
END TRY
BEGIN CATCH
PRINT 'Conflict in ' + @TableName + N'.' + @ForeignKey
DBCC CHECKCONSTRAINTS (@TableName)
END CATCH
FETCH cur INTO @TableName, @ForeignKey
END

CLOSE cur
DEALLOCATE cur

SELECT t.name, fk.name, fk.is_disabled, fk.is_not_trusted
FROM sys.foreign_keys fk
INNER JOIN sys.objects t ON t.object_id = fk.parent_object_id
WHERE fk.is_not_trusted = 1
ORDER BY t.name, fk.name


The script tries to enable each foreign key constraint it finds in the database using the WITH CHECK CHECK CONSTRAINT clause. 
If the check fails, it calls DBCC CHECKCONSTRAINTS which returns the set
of recordings violating the foreign key constraint. 

The result of the script execution shows you which tables and records need to be cleaned up. 
Run the script repeatedly after data cleanup until a single empty result set is returned.






Tuesday, April 23, 2013

The database cannot be opened because it is version 706. This server supports version 661 and earlier


Today I was trying to Attach a database in SQL server 2008 R2.

I got below error while attaching database:


This is because the data file I was trying to attach is from SQL server 2012. I was trying to attach this in SQL Server 2008. The only solution to this problem is to generate script from SQL server 2012 and than run that script in SQL server 2008.

Monday, April 22, 2013

Get the list of parameters for Stored Procedure

You can get the list of parameters for Stored procedure in two ways:

Solution 1


SELECT * FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_NAME = 'SP_TempProduct'

Solution 2  

SELECT * FROM SysColumns WHERE ID = OBJECT_ID('SP_TempProduct')  

Monday, April 15, 2013

Cannot insert an explicit value into a timestamp column. Use INSERT with a colimn list to exclude the timestamp column, or insert a DEFAULT into the timestamp column

You will get below error while inserting data into table with TimeStamp column:

Cannot insert an explicit value into a timestamp column. Use INSERT with a colimn list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.

The main reason for this error is timestamp data type works in the same way as identity field. You don’t need to pass the value explicitly for this fields, SQL server automatically insert the value based on the system data and time.

If you need to insert a formatted date or time then you need to use datatime data type or change the SQL query.

Monday, April 8, 2013

Error Message 3241

You may get below error message on your screen during SQL server database restore:

“An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)"
Additional information:
The media family on device 'DataBkp.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)”

This generally happens due to many reasons but the most common reason is version mismatch between backup & restore SQL server (To perform backup & restore operation of your SQL server database, you will have required two SQL servers with same version or higher version). You can't restore a backup that was taken from higher version of SQL server to lower version of SQL server. You can not restore a data backup taken on SQL Server 2008 to SQL Server 2005. To solve this error you have to check the SQL Server version of both SQL Server. Run below command to check version: 

Select @@version;
Go


If this command verifies that backup taken SQL server version is higher than restore SQL server version then you have two choice: 

either 
upgrade restore SQL server 
or 
restore backup on the higher version of SQL server.

Alternate Solution: You can also resolve SQL server error message 3241 by performing following steps:

  1. Create a blank database in SQL server 2005
  2. Generate the script using 'Generate Script Wizard'
  3. Now create structure of your database
  4. Finally use Import/Export data wizard to import your data.

Monday, April 1, 2013

SQL script to find Foreign key dependencies

Today I was looking for the code to find all Foreign Keys in my table and it’s reference Table. I used the below code t oget that:

SELECT CAST(F.Name AS VarChar(255)) AS [Foreign Key Name], 
CAST(C.Name AS VarChar(255)) AS [Foreign Table], 
CAST(FC.Name AS VarChar(255)) AS [Foreign Column], 
CAST(p.Name AS VarChar(255)) AS [Primary Table], 
CAST(RC.Name AS VarChar(255)) AS [Primary Column] 
FROM 
sysobjects AS F INNER JOIN 
sysobjects AS C ON F.Parent_obj = C.ID 
INNER JOIN sysreferences AS R ON F.ID = R.ConstID 
INNER JOIN sysobjects AS P ON R.RKeyID = p.ID 
INNER JOIN syscolumns AS RC ON R.RKeyID = RC.ID AND R.RKey1 = RC.ColID 
INNER JOIN syscolumns AS FC ON R.FKeyID = FC.ID AND R.FKey1 = FC.ColID 
LEFT JOIN syscolumns AS RC2 ON R.RKeyID = RC2.ID AND R.RKey2 = RC.ColID LEFT JOIN syscolumns FC2 ON R.FKeyID = FC2.ID AND R.FKey2 = FC.ColID 
WHERE F.Type = 'F' 
ORDER BY CAST(P.Name AS VarChar(255))