Sql server: cannot resolve collation conflict for equal to operation.

Sometimes you may encounter a “collation problem” when you compare two colums from different table or database.

As MSDN says “Collations let users sort and compare strings according to their own conventions”, but they can be a really pain for developers.

The simplest way for comparing two columns with different collation without hard writing specific collation codes is to convert the two columns to the database default collation.

If this sql script gives you the error “cannot resolve collation conflict for equal to operation”

select * from people,city 
where people.citycode = city.citycode

You can transform the select adding collation instructions:

select * from people,city 
where people.citycode COLLATE DATABASE_DEFAULT  = city.citycode COLLATE DATABASE_DEFAULT 

Hope it helps!

 

How to get columns type from Query

Sometimes I need to find info about columns in a table using only TSQL, here is a little query :

SELECT 
syscolumns.name AS ColName, 
systypes.name AS ColType, 
syscolumns.length AS ColSize,
syscolumns.isnullable,
systypes.collation
FROM sysobjects INNER JOIN syscolumns 
ON sysobjects.id = syscolumns.id 
INNER JOIN systypes 
ON dbo.syscolumns.xtype = dbo.systypes.xtype 
WHERE 
sysobjects.xtype='U' 
AND sysobjects.name='banners'

And here is the result, banners is a DotNetNuke table:

Disable the message "Rows processed" in Sql Query Analyzer

Here is how to disable the annoying message "Rows processed: 1" displayed when you execute queries using Sql Query Analyzer:


Th command is :
SET NOCOUNT ON
go


Simple?

Howto Create a SQL Conditional View

Here is a script on how to create a conditional view based on a parameter

Sometimes things seems simple, this instruction is valid:

if @condition = '1' then

begin

Create Table...

end

Here is the equivalent instruction, where Table is substituted by View:

IF EXISTS (

SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[vwTest]') AND OBJECTPROPERTY(id, N'IsView') = 1 )

BEGIN

CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE

END

This command fails with the error:

Messagge 156, livello 15, stato 1, riga 4

Incorrect syntax near the keyword  'VIEW'.

The alternative is to use the “exec” command:

exec N'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'

But here is the sad message:

Messagge 102, livello 15, stato 1, riga 1

Incorrect syntax near 'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'.

The solution is to use the stored procedure sp_executesql:

sp_executesql N'CREATE VIEW [dbo].[vwTest] AS SELECT * from MYTABLE'

Don’t forget , if you are passing the command using a parameter, to define it as ntext/nchar/nvarchar, here is the strange message if you don’t use this types:

Incorrect syntax near ...

Hope it helps!

Database size via query

Here are some usefull Sql commands for space verification on a database:

-- DB space

exec sp_spaceused

--space for a table

exec sp_spaceused 'tablename'

-- databases list

exec sp_databases

-- SQL version

Select @@version

-- All sql server version infos

exec xp_msver

Sql server datetime format

Sometimes is useful to get on SQL server clean date time values, without the hh:mm:ss, here are the examples starting from a simple GetDate().

For example you may need the date of the first day of the week or of the first day of the month.Here is how to get these values:

select getdate() 
today date with hour
Output: 2008-02-14 17:31:13.727

select DATEADD(dd, DATEDIFF(d,0,getdate()), 0)
today
Starting:2008-02-14 17:31:13.727
Output: 2008-02-14 00:00:00.000

select DATEADD(dd, DATEDIFF(d,-1,getdate()), 0)
tomorrow
Starting:2008-02-14 17:31:13.727
Output: 2008-02-15 00:00:00.000

select DATEADD(dd, DATEDIFF(d,2,getdate()), 0)
yesterday
Starting:2008-02-14 17:31:13.727
Output: 2008-02-12 00:00:00.000

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)this month
Starting:2008-02-14 17:31:13.727
Output: 2008-02-01 00:00:00.000

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)monday
Starting:2008-02-14 17:31:13.727
Output: 2008-02-11 00:00:00.000

select dateadd(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0))
last week
Starting:2008-02-14 17:31:13.727
Output: 2008-02-04 00:00:00.000

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
this year
Starting:2008-02-14 17:31:13.727
Output: 2008-01-01 00:00:00.000

select dateadd(m,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
1 month ago
Starting:2008-02-14 17:31:13.727
Output: 2008-01-01 00:00:00.000

select dateadd(m,-6,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
6 months ago
Starting:2008-02-14 17:31:13.727
Output: 2007-08-01 00:00:00.000

Hope it helps,

Matteo

Sql server kill active connections

Here is a good TSQL query that eliminates all the active connections.

It's usefull when your code forgets to close the connection object.


SET NOCOUNT ON
DECLARE @spid INT,
@cnt INT,
@sql VARCHAR(255) ,
@dbname varchar(50)

set @dbname = 'mydbname'

SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID

PRINT 'Starting to KILL '+RTRIM(@cnt)+' processes.'

WHILE @spid IS NOT NULL
BEGIN
PRINT 'About to KILL '+RTRIM(@spid)
SET @sql = 'KILL '+RTRIM(@spid)
EXEC(@sql)
SELECT @spid = MIN(spid), @cnt = COUNT(*)
FROM master..sysprocesses
WHERE dbid = DB_ID(@dbname)
AND spid != @@SPID
PRINT RTRIM(@cnt)+' processes remain.'
END



Found at: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=890339&SiteID=1

Property IsLocked is not available - La proprietà IsLocked non è disponibile

Sometimes after rebooting the machine with sql server 2005 standars installed I cannot connect using the sa user and I receive an error when I try to manage the user:

"La proprietà IsLocked non è disponibile per Account di accesso 'sa'"

Or, in english:

Property IsLocked is not available for Login '[sa]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)

The only solution is to unlock the sa user using the command:

alter login sa

with password = 'yourpwd' unlock,

check_policy = off,

check_expiration = off

After this all works ok.

For details look at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1673143&SiteID=1

Hope it helps,

Matteo

Sql Server DateTime Format

A volte su SQL server può essere necessario ricavare alcune date “pulite” dall’orario partendo da un semplice GetDate().

Ad esempio può servire la data del primo giorno della settimana, o del primo giorno del mese ecc.

Ecco un modo abbastanza elegante per ricavare questi dati:

select getdate() --ora iniziale
2008-02-14 17:31:13.727

select DATEADD(dd, DATEDIFF(d,0,getdate()), 0) --oggi
Output: 2008-02-14 00:00:00.000

select DATEADD(dd, DATEDIFF(d,-1,getdate()), 0) --domani
Output: 2008-02-15 00:00:00.000

select DATEADD(dd, DATEDIFF(d,2,getdate()), 0) --ieri
Output: 2008-02-12 00:00:00.000

select DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) --mese corr
Output: 2008-02-01 00:00:00.000

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) --lunedì
Output: 2008-02-11 00:00:00.000

select dateadd(wk,-1,DATEADD(wk, DATEDIFF(wk,0,getdate()),0)) --settimana scorsa
Output: 2008-02-04 00:00:00.000

select DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) --quest'anno
Output: 2008-01-01 00:00:00.000

select dateadd(m,-1,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) --1 mese fa
Output: 2008-01-01 00:00:00.000

select dateadd(m,-6,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)) --6 mesi fa
Output: 2007-08-01 00:00:00.000

Spero sia utile,

Matteo

Little question on sql... Null or not null?

Executing this TSQL code, what will be the result? "True or False"  or  "Not defined?"

if 'a' <> null or 'a' = null 
    print 'true or false!'
else 
    print 'not defined...!'

Answer: the first condition is neither true nor false, is… undefined.
Don’t compare columns using the ‘= null’ or ‘<> null’, pay attention, the results should be very different from what you expect.
You need to use ‘IS NULL’ and ‘IS NOT NULL’, these are the correct operators for managing null values in Sql server.

if null <> null or null = null 
    print 'true or false!'
else 
     print 'not defined...!'

Same story for the above statement, the result is… “Not defined!”

Matteo

">">Site Feed

HOWTO update or delete on SQL server a limited number of rows, like TOP condition

If you need to update a limited number of rows on a sql table using a "top"  like condition, here is a solution:

SET ROWCOUNT 100

-- update

SET ROWCOUNT 100

update table1 set column1 = getdate()

-- only 100 rows will be updated

-- delete

SET ROWCOUNT 10

delete from table1

-- only 10 rows will be deleted

Allineare due DB via batch file

Ecco uno script batch che utilizzo spesso per mantenere allineato il database locale con il database sul server di sviluppo.
E' necessario avere i permessi per accedere via file system al database remoto ed una user sql amministrativa.
Ecco i passaggi:

1. collego via net use al sql server
2. killo le connessioni attive sul db che deve essere copiato
3. eseguo lo shrink del db e lo metto offline
4. copio i file mdf ed ldf in locale
5. metto online il db
   

[+/-] Mostra i file

copydb.bat:
rem killo in processi in uso sul db che deve essere copiato, lo setto offline, copio i file e lo rimetto online
echo on
echo utilizzo:copydb.bat NOMESERVER NOMEDB SQLUSER SQLPASSWORD DOMINIO USER SQLPATH LOCALPATH
echo esempio:"copydb.bat" sqlserver1 pubs sa password domain01 myusername "\\sqlserver1\e$\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf" "\\sqlserver1\e$\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf" "c:\sqllocal"

set NOMESERVER=%1
set NOMEDB=%2
set SQLUSER=%3
set SQLPASSWORD=%4
set DOMINIO=%5
set USER=%6
set SQLMDFPATH=%7
set SQLLDFPATH=%8
set LOCALPATH=%9

net use \\%NOMESERVER% /user:%DOMINIO%\%USER%

isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -i "db_kill_connections.sql"
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "DBCC SHRINKDATABASE (%NOMEDB%,10)"
isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "alter database %NOMEDB% set offline"

copy %SQLMDFPATH% %LOCALPATH% /Y
copy %SQLLDFPATH% %LOCALPATH% /Y

isql -S %NOMESERVER% -U %SQLUSER% -P %SQLPASSWORD% -d master -Q "alter database %NOMEDB% set online"

pause


db_kill_connections.sql:

DECLARE @spid int
-- Declare a cursor For process records that concern with MyDatabase.
DECLARE sysprocesses_cursor SCROLL CURSOR FOR
SELECT spid
FROM master..sysprocesses
WHERE dbid = db_id('Pubs')

OPEN sysprocesses_cursor

FETCH NEXT FROM sysprocesses_cursor
INTO @spid
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN -- Clear up processes.
print 'kill @spid'
print @spid
EXEC ('KILL ' + @spid)
FETCH NEXT FROM sysprocesses_cursor INTO @spidEND
CLOSE sysprocesses_cursor
DEALLOCATE sysprocesses_cursor

SQL2000: gestire input variabile su Stored Procedure

Ecco una bellissima funzione fornita dal mio amico Gibyx:
Data una stringa contenente un numero variabile di valori, es:
"1;2;3;4;5;6;" oppure "AD;ER;FG;GG;HH;JJ"
restituisce una tabella che può essere utilizzata in sub query /join/etc..

[+/-] CREATE FUNCTION dbo.SplitIntList....

CREATE FUNCTION dbo.SplitIntList
(
@PropertyIDValues NVARCHAR(1024)
)
RETURNS @ResultTable TABLE(IDValue int)
AS
BEGIN
DECLARE @SingleColumn NVARCHAR(12)
DECLARE @SplitChar CHAR(1)
DECLARE @LENInputString INT
DECLARE @x INT
DECLARE @y INT

SET @SplitChar = ';'
IF (RIGHT(@PropertyIDValues, 1) <> @SplitChar)
BEGIN
 SET @PropertyIDValues = @PropertyIDValues + @SplitChar
END
SET @LENInputString = LEN(@PropertyIDValues)
SET @x = 0
WHILE (@x < @LENInputString)        BEGIN     SET @SingleColumn = SUBSTRING(@PropertyIDValues, @x, 1)     IF ((@SingleColumn <> @SplitChar) AND (@PropertyIDValues <> ' '))
   BEGIN
    SET @y = @x + 1
    WHILE ((@y < @LENInputString + 1) AND (SUBSTRING(@PropertyIDValues, @y, 1) <> @SplitChar))
     BEGIN
      SET @SingleColumn = @SingleColumn + SUBSTRING(@PropertyIDValues, @y, 1)
      SET @y = @y + 1
     END
    SET @x = @y
   END
 IF ((@SingleColumn <> @SplitChar) AND (@PropertyIDValues <> ' '))
  BEGIN
   SET @SingleColumn = LTRIM(RTRIM(@SingleColumn))
   INSERT INTO @ResultTable (IDValue) VALUES (CAST(@SingleColumn AS INT))
  END
 SET @x = @x + 1
END
RETURN
END

ESEMPI:
select * from splitIntList('1;10;11;12;13;') where idvalue >10
IDValue
-----------
11
12
13
(3 row(s) affected)

select * from splitStringList('GP;HB;TE;TC;MX;') where idvalue like 'G%'
IDValue
----------
GP
(1 row(s) affected)