Sql server versions list

From wikipedia… a useful table with the version of SQL server

QL Server Release History

Version	Year	Release Name	Codename
1.0 (OS/2)	1989	SQL Server 1.0 (16bit)	-
1.1 (OS/2)	1991	SQL Server 1.1 (16bit)	-
4.21 (WinNT)	1993	SQL Server 4.21	SQLNT
6.0	1995	SQL Server 6.0	SQL95
6.5	1996	SQL Server 6.5	Hydra
7.0	1998	SQL Server 7.0	Sphinx
-	1999	SQL Server 7.0 OLAP Tools	Plato
8.0	2000	SQL Server 2000	Shiloh
8.0	2003	SQL Server 2000 64-bit Edition	Liberty
9.0	2005	SQL Server 2005	Yukon
10.0	2008	SQL Server 2008	Katmai
10.25	2010	SQL Azure	Matrix (aka CloudDB)
10.5	2010	SQL Server 2008 R2	Kilimanjaro (aka KJ)
11.0		SQL Server 2012	Denali

SQL server BCP in a nutshell

BCP is a sql server utility used for importing and exporting huge quantity of data from a sql server table

You can use BCP without installing sql server, just install Microsoft SQL Server 2008 Command Line Utilities at:

http://www.microsoft.com/downloads/en/details.aspx?FamilyID=b33d2c78-1059-4ce2-b80d-2343c099bcb4

You can Export data from command line:

bcp mydbname.dbo.largetable  out c:\yourtable.csv /U sa /P mypassw /S servername /c

And Import data:

bcp mydbname.dbo.largetable  in c:\yourtable.csv /U sa /P mypassw /S servername /c

For a table with 60 fields the rate import is 10000 records/sec… but this is only a test value depending on the hardware.

You can also export queries using bcp (from msdn):

bcp "SELECT FirstName, LastName FROM AdventureWorks2008R2.Person.Person ORDER BY LastName, Firstname" queryout Contacts.txt -c –T

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!

 

Hot to recover unused space from SQL server free edition (MSDE,Express)

If you have a SQL server free edition and it isn’t working with the message:

CREATE/ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 2048 MB per database.

Unfortunately the ”dbcc cleantable “ does not help,  it cleans the table unused space BUT the shrink operation does  not decrease the database size

You have another option:  reorganize the indexes and shrink the db: it works giving you some hours of new life, just the time to install SQL2008 express R2 with 8GB limit

Follow this steps:

1. Open Sql Server Management Studio
2. Run sp_helpdb ‘YourDb’ and save the results
3. Analyze the tables with the bigger size, use this script:
   http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/121/determing-sql-server-table-size.aspx
4. Change in the script the final query :
• from:
  SELECT *
  FROM #TempTable
• To :
  SELECT *
  FROM #TempTable
  order by cast(replace(IndexSize,' KB','') as int) desc
5. Run the query and identify the worst tables
6. Open the Tables, Index and click on “Reorganize all”
7. Shrink the DB
8. Run sp_helpdb ‘YourDb’ and compare the results with the old one
9. Install the new SQL server and try to migrate the databases, but this is another story…..
   

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

Confrontare due DB

SQL Delta : confronta la struttura ed i dati di un database con un click!

Il programma è mooolto utile, sul sito http://www.sqldelta.com/ è disponibile la funzione trial da 15gg completamente funzionante!

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)