Tag Archives: SQL

This database file is not compatible with the current instance of SQL Server

This error occurred to me in Visual Studio 2013 while create connection to local AdventureWorks 2012 mdf file.

This database file is not compatible with the current instance of SQL Server. To resolve this issue, you must upgrade the database file by creating a new data connection, or you must modify the existing connection to this database file. For more information, see http://go.microsoft.com/fwlink/?LinkId=235986

In order to upgrade the database, just follow steps described under the link above:

To upgrade a database file to use LocalDB

  1. In Server Explorer, choose the Connect to Database button.
  2. In the Add Connection dialog box, specify the following information:
    • Data Source: Microsoft SQL Server (SqlClient)
    • Server Name: (LocalDB)\v11.0
    • Attach a database file: Path, where Path is the physical path of the primary .mdf file.
    • Logical Name: Name, where Name is the name that you want to use with the file
  3. Choose the OK button.
  4. When prompted, choose the Yes button to upgrade the file

The database is upgraded, attached to the LocalDB database engine, and no longer compatible with SQL Server 2008 Express.

SQL Server For Each Row Next

SELECT 
    RowNum = ROW_NUMBER() OVER(ORDER BY CustomerID)
    ,*
INTO #Customers
FROM SalesLT.Customer

DECLARE @MaxRownum INT
SET @MaxRownum = (SELECT MAX(RowNum) FROM #Customers)

DECLARE @Iter INT
SET @Iter = (SELECT MIN(RowNum) FROM #Customers)

WHILE @Iter <= @MaxRownum
BEGIN
    SELECT *
    FROM #Customers
    WHERE RowNum = @Iter
    
    -- run your operation here
    
    SET @Iter = @Iter + 1
END

DROP TABLE #Customers

via SQL SERVER PLANET

Error 0xc004706b: failed validation and returned validation status “VS_ISBROKEN”. (SQL Server Import and Export Wizard)

It happens during exporting the data, while source table has identity columns and destination one as well.

Step 1: Edit Mappings

01

Step 2: Check Enable identity insert

02

The reason why I got this error was because my destination table had the IDENTITY property set on ID column, which was not allowing do any inserts using the export wizard. This is because identity column values are automatically generated.

Enabling the Enable Identity Insert option allows values to be inserted in the identity field and desired values are moved from source database to destination table.

SQL Find all references to a table

You can use multiple approaches using SQL query like one below.
For me the fastest way to use SQL Search from Red Gate and from Objects filter select ‘Tables’

SELECT  obj.name AS FK_NAME,
    sch.name AS [schema_name],
    tab1.name AS [table],
    col1.name AS [column],
    tab2.name AS [referenced_table],
    col2.name AS [referenced_column]
FROM sys.foreign_key_columns fkc
INNER JOIN sys.objects obj
    ON obj.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tab1
    ON tab1.object_id = fkc.parent_object_id
INNER JOIN sys.schemas sch
    ON tab1.schema_id = sch.schema_id
INNER JOIN sys.columns col1
    ON col1.column_id = parent_column_id AND col1.object_id = tab1.object_id
INNER JOIN sys.tables tab2
    ON tab2.object_id = fkc.referenced_object_id
INNER JOIN sys.columns col2
    ON col2.column_id = referenced_column_id AND col2.object_id = tab2.object_id
    
    ORDER BY col2.name 

via Stack Overflow

sql server – Can foreign key constraints be temporarily disabled using T-SQL?

-- Disable all table constraints
ALTER TABLE MyTable NOCHECK CONSTRAINT ALL

-- Enable all table constraints
ALTER TABLE MyTable CHECK CONSTRAINT ALL

-- Disable single constraint
ALTER TABLE MyTable NOCHECK CONSTRAINT MyConstraint

-- Enable single constraint
ALTER TABLE MyTable CHECK CONSTRAINT MyConstraint

via Stack Overflow

SQL – Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation

Either change table collation or update query on comparisons using COLLATE DATABASE_DEFAULT

Before (failing)

INSERT INTO     geo.Codes (TheCode, CityID)
SELECT 		d.TheCode, d.CityID
FROM 		#tmp d
LEFT JOIN 	geo.Codes p 
ON 		d.TheCode = p.TheCode 
WHERE 		p.TheCode IS NULL

After (using COLLATE DATABASE_DEFAULT)

INSERT INTO geo.Codes (TheCode, CityID)
SELECT 		d.TheCode, d.CityID
FROM 		#tmp d
LEFT JOIN 	geo.Codes p 
ON 		d.TheCode COLLATE DATABASE_DEFAULT = p.TheCode COLLATE DATABASE_DEFAULT
WHERE 		p.TheCode IS NULL

SQL Cannot resolve the collation conflict between Latin1_General_CI_AS and SQL_Latin1_General_CP1_CI_AS in the equal to operation.

For example query

SELECT *
FROM dbo.Dealers d
JOIN dbo.Warehouses w
ON d.Id = w.DealershipId
JOIN dbo.Locations l
ON w.PostalCode = l.PostalCode

results in error

Msg 468, Level 16, State 9, Line 6
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

The solution is to use COLLATE in the line where it fails. In my case it was PostalCode field
Updated query:

SELECT *
FROM dbo.Dealers d
JOIN dbo.Warehouses w
ON d.Id = w.DealershipId
JOIN dbo.Locations l
ON w.PostalCode COLLATE DATABASE_DEFAULT = l.PostalCode COLLATE DATABASE_DEFAULT

More complex example, where COLLATE had to be used not only in basic comparison

SELECT Name FROM dbo.DealerProfiles DP
WHERE NAME COLLATE DATABASE_DEFAULT NOT IN 
(
	SELECT DealerName COLLATE DATABASE_DEFAULT
	FROM GEO.dbo.Dealers GD
	WHERE DP.Name COLLATE DATABASE_DEFAULT = gd.DealerName COLLATE DATABASE_DEFAULT
)

Enable remote connections in SQL Server 2008

Basically few things:

  • SSMS -> server properties -> Connections
    Allow remote connections to this server checked
  • SSMS -> server properties -> Security
    SQL Server and Windows Authentication mode checked
  • SQL Server Configuration Manager -> SQL Server Network Configuration TCP/IP enabled
  • TCP/IP -> Properties -> IP Addresses
    Every IP needs to have 1433 (default one) port set
  • sometimes Firewall needs extra inbound rule for 1433 port
    (Click on Advanced Settings on the left hand side and you should see the Windows Firewall with Advanced Security. Select the Inboud Rules on the left hand side and click on New Rule… on the right hand side.)

Woulktrhough here

Start or Stop MS SQL Server using a shortcut

  • Create a new shortcut.
  • When it asks you to browse or specify the location of what you want the shortcut to point to, enter the following

    cmd /c net start MSSQLSERVER 

    or

    cmd /c net stop MSSQLSERVER 
  • Click next, and call the shortcut whatever you want.

Important: MSSQLSERVER is your SQL instance name
Thanks to Just geeks

If you are running Windows 7, you might want to set running shortcuts as Administrator.

  • right click on the shortcut
  • in ‘Shortcut’ click ‘Advanced’ button
  • check ‘Run as administrator’ option

    shortcut