Archive for the ‘Sql’ Category

Msg 468, Level 16, State 9, Procedure XXXXXX_Select_XXXXXXXXXXX_FILTERED, Line 11
Cannot resolve the collation conflict between “Finnish_Swedish_CI_AS” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

If your unlucky and need to be join tables or database which have different collation set then the above message is frequent. Luckily the fix isn’t tooo hard


--Throws Collation Error assuming table a column is set as Swedish or something similar
Select *
From TableA
INNER JOIN TABLEB ON a.Name = b.Name


--Work.. With collation force to latin (same as table b)
Select *
From TableA
INNER JOIN TABLEB ON a.Name  COLLATE SQL_latin1_general_CP1_ci_as = b.Name

Some interesting thing are exiting Ms SQL server (http://msdn.microsoft.com/en-us/library/ms143729.aspx)

The first one of note is:
Two part column name not supported
While mildly amusing that sql can be writting like this all Microsoft are really telling us is that this no longer supported:

select databasename.dbo.tablea.columnA
from databaseName.dbo.tableA

The work around (really really really lose term in this case here) is to simply alias the tables


select myAlias.columnA
from databaseName.dbo.tableA myAlias

IDENTITY_INSERT not working

Posted: 2013-03-01 in Sql
SET IDENTITY_INSERT tableA ON
insert into tableA

select *
from anotherdb..tableA
SET IDENTITY_INSERT tableA OFF

Msg 8101, Level 16, State 1, Line 5

An explicit value for the identity column in table ‘tablename’ can only be specified when a column list is used and IDENTITY_INSERT is ON.

Simple even with identity insert on you still have to list the columns

 insert into tableA (a , b, c)
 select a , b, c from anotherdb..tableA

(more…)

Reset seed on a database table

Posted: 2013-03-01 in Sql

DBCC CHECKIDENT(tablename,RESEED, 0)

 

Simple way to cleanse/delete all data in all tables in a database fast.

If you have constraints you might have to run it several times.

 


-- Truncate all tables
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL
BEGIN
 SELECT @SQL = 'TRUNCATE TABLE [dbo].[' + RTRIM(@name) +']'
 EXEC (@SQL)
 PRINT 'Truncated Table: ' + @name
 SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

Cheers

Choco

 

Made a schema change, added a column, want to check all stored procedures and views that they don’t have syntax or compile errors?

Try the below script its big but it works!!!

The output are the SP’s and Views that failed syntax or compile checks. Add this to a unit test and you’ll save yourself lots of run time headaches.

This is my  most precious script so please give me credit if you use it (even if you steal it to add a tweak or two and claim as your own)

 

BTW This script uses  FMTONLY which only work with SQL server 2008 R2 or below, you’ll need to upgrade it to sp_describe_first_result to run it in 2012

(more…)

Copies data from one database to another.
Assumes the tables schema are the same. Use the Management studio Generate scripts to create the tables, constraints, stats, and indexes.

The script is deliberately written in 3 parts to help ease of customization

Handles IDENTITY INSERT tables and constraints (although uses simple brute for with max of x attempts which isn’t so elegant)

(more…)