Transact-SQL is central to the use of Microsoft SQL Server. All applications that communicate with SQL Server do so by sending Transact-SQL statements to the server, regardless of an application’s user interface. Transact-SQL Reference
Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking connections.
I decided the try to restore the database with T-SQL, with T-SQL the restore completed successfully! Another advantage of using T-SQL is that T-SQL gave more logging. This logging shows me that the BAK file (delivered from a third party) is not a SQL Server 2005 backup but presumably a SQL Server 2000 database backup …
DECLARE
@BackupFile varchar(8000),
@sql varchar(8000)
SET @BackupFile = 'E:\DB2000.BAK'
SET @sql = 'RESTORE DATABASE ADB01 FROM DISK = ''' + @backupfile + ''' WITH FILE = 1,
MOVE N''DB2000_dat'' TO N''E:\mssql\data\ADB01\ADB01_01.mdf'',
MOVE N''DB2000_log'' TO N''F:\mssql\tlog\ADB01\ADB01_01.ldf'',
NOUNLOAD,
Example below is the most straight forward example of walking through a CURSOR with a WHILE loop in tsql. The cursor is declared, opened and and a FETCH NEXT is done while @@fetch_status equals 0.
The behaviour of a FETCH [ NEXT ] call is that it returns the value(s) for the column(s) specified in the DECLARE CURSOR as a single-row result set.
Basic cursor example with FETCH returning row as single-row result set
1> declare c1 cursor
2> for
3> select left( name, 32) as name,
4> cast( dbid as varchar(10)) as dbid
5> from master.dbo.sysdatabases
6> where dbid < 5
7>
8> open c1
9> fetch next from c1
10>
11> -- while @@fetch_status != -1 and @@fetch_status
Returing values into tsql variables from a dynamic sql statement using sp_executesql stored procedure.
Scenario
The example below displays the number of objects per database. The following steps are involved.
open a cursor for the select * from master.dbo.sysdatabases statement (cursor is a while loop),
inside the cursor a dynamic sql statement is defined ,
at last the stored procedure sp_executesql is used to retrieve the output of this defined sql statement into tsql variables .
sp_execute example to return values into variables
1> declare @s_dtb sysname
2> declare @s_sql nvarchar(2000)
3> declare @i_obj int
4> declare c1 cursor
5> for
6> select name
7> from master.dbo.sysdatabases
8>
9>
10> open c1
11> fetch c1 into @s_dtb
12>
13> while @@fetch_status >= 0
14> begin
15>
16> -- construct sql statement
17> set @s_sql = 'select @count = count(*)
18>
In MSSQL (2000) there’s not an easy way to see in which database you are. Script below creates a view ‘whoami’ that gives you the information;
create view dbo.whoami
as
select @@spid as '@@spid',
cast( user as varchar(12)) as 'user',
cast( p.dbid as varchar(6)) + ':' + cast( d.name as varchar(14)) as 'database',
cast( p.loginame as varchar(16)) loginame,
cast( p.hostname as varchar(16)) as 'hostname',
cast( p.nt_username as varchar(16)) nt_username
from master.dbo.sysprocesses p left join master.dbo.sysdatabases d
on d.dbid = p.dbid
where spid = @@spid
go
grant select on dbo.whoami to public
go
1> select * from whoami
2> go
@@spid user database
To format the display of your date you need to convert it to a varchar (like the to_char function in oracle).
So to display 18 september as 25-09-2006 I must convert it the Italian?? way, as shown below.
1> print convert( varchar(32), getdate(), 105)
2> go
18-09-2006
1> print convert( varchar(32), getdate(), 5)
2> go
18-09-06
Advanced(?) formatting
My requirement is simple: get the date displayed as ‘yyyymmdd_hhmiss’. And yes I want leading zeros for the first nine monts/minutes. So ’09′ for ‘September’ and not ’9′. How hard can that be … Well, I am missing something bigtime OR this basic functionality is simply missing from sqlserver T-SQL (version 2000).
Restore SQL Server database with TSQL
Recently I had troubles restoring a database in my SQL Server 2005 instance (SP3). I used the ‘wizard’ in the SQL Server Enterprise manager but after I got the message ‘restore completed successfully’ the database hung in ‘Restoring’ state, blocking connections.
I decided the try to restore the database with T-SQL, with T-SQL the restore completed successfully! Another advantage of using T-SQL is that T-SQL gave more logging. This logging shows me that the BAK file (delivered from a third party) is not a SQL Server 2005 backup but presumably a SQL Server 2000 database backup …
DECLARE @BackupFile varchar(8000), @sql varchar(8000) SET @BackupFile = 'E:\DB2000.BAK' SET @sql = 'RESTORE DATABASE ADB01 FROM DISK = ''' + @backupfile + ''' WITH FILE = 1, MOVE N''DB2000_dat'' TO N''E:\mssql\data\ADB01\ADB01_01.mdf'', MOVE N''DB2000_log'' TO N''F:\mssql\tlog\ADB01\ADB01_01.ldf'', NOUNLOAD,Read more →