remidian

Can't find what you're looking for? Use of one of the search websites below …

Home → Archive by category "mssql"

Category Archives: mssql

I am an Oracle DBA (So you know the perspective of this category about that other rdbms)… On my current site we are now in the process of becoming responsible for over +20 SQL Servers, I’ll post anything I come around that I think is usefull.

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,

  • / mssql
  • # 2167
  • JAN, 30 2008

osql switches

Some osql switches.

Specify non default tcp ports 

C:\>osql -Stcp:<hostname>,<portname>[\<instance_name>] -E
1>

See http://support.microsoft.com/kb/313295

Remove SQL Server database from single-user mode

Braindump of a session where I had to remove a SQL Server database from single-user mode.

execute sp_dboption
1> exec sp_dboption 'testdb01', 'single user', 'FALSE';
2> go
Msg 5064, Level 16, State 1, Server REMIDIAN01, Line 1
Changes to the state or options of database 'testdb01' cannot be made at this
time. The database is in single-user mode, and a user is currently connected to
it.
Msg 5069, Level 16, State 1, Server REMIDIAN01, Line 1
ALTER DATABASE statement failed.
sp_dboption command failed.

Now I can use TSQL below to find all the database sesions connected to this database.

TSQL script to retrieve client sessions per database. 
select d.name, d.dbid, spid, login_time, nt_domain, nt_username, loginame
  from sysprocesses p inner join sysdatabases d on p.dbid = d.dbid
 where d.name = 'testdb01'
go

Kill the particular session(s) with the ;kill’ command. 

1> kill 51
2> go
1>

Now I can ‘remove’ the database from Sinlge user mode. 

1> exec sp_dboption 'testdb01', 'single user', 'FALSE'
2> go
1>

Microsoft SQL Server 2005 JDBC Driver (oracle GRID)

Microsoft SQL Server 2005 JDBC Driver java test program.

Enterprise Manager Grid Control System Monitoring Plug-in for Microsoft SQL Server

I have been busy deploying Microsoft SQL Server in our Oracle Enterpise GRID Control using the ‘Oracle Enterprise Manager Grid Control System Monitoring Plug-in for Microsoft SQL Server’ http://www.oracle.com/technology/products/oem/extensions/plugin-ms_sql.html. The final step in the configuration is the configuration of your SQL Server target in the GRID Control ‘configure target’ page for that SQL Server. One of the fields required is ‘JDBC URL’ (and ‘JDBC Driver’). The Microsoft SQL Server 2005 JDBC Driver comes with two sample programs in sqljdbc_1.2\enu\help\samples\connections (connectDS.java and connectURL.java). Since one of the prerequisities I read on metalink is ‘make sure remote JDBC connections with your SQL Server work’ I left the Oracle GRID console and tried to get this sample program to connect to my SQL Server succesfully.

It took my quit some time to

Create, drop and rename a database

Create, rename and drop a database in sql server …

Create database

See syntax below for the CREATE DATABASE statement.

Create NORTHWIND database
1> use master
2> go
1> create database [NORTHWIND]
2>     on ( name        = N'NORTHWIND_dat',
3>          filename    = N'C:\\mssql\\data\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.mdf' ,
4>          size        = 10,
5>          filegrowth  = 1,
6>          maxsize     = 1024
7>        )
8> log on ( name        = N'NORTHWIND_log',
9>          filename    = N'C:\\mssql\\tlog\\iDEFAULT\\NORTHWIND\\NORTHWIND_01.ldf' ,
10>

  • / T-SQL
  • # 1048
  • NOV, 15 2006

Control Structures

Control structures syntax; IF THEN ELSE, CASE, WHILE .

IF THEN ELSE

The IF THEN ELSE syntax in TSQL is kinda weird if you ask me … I forget them ‘all the time’, created examples below for my reference.

IF THEN
if 1 = 1
begin 
  print 'true'
end
 
go
IF THEN – short notation
if 1 = 1
  print 'true'
 
go
IF THEN ELSE
if 1 = 1
begin
  print 'true'
end
 
else  

begin
  print 'false'
end
 
go
IF THEN ELSE – short notation
if 1 = 1
  print 'true'
else  
  print 'false'

go
Multiple IF THEN ELSE blocks
if 1 = 1
begin
  print '1 = 1'
end
else  
if 1 = 2
  begin
    print '1 = 2'
  end
else
if 1 = 3
begin
  print '1 = 3'
end
 
go

CASE

TSQL has the CASE control structure …

declare @i int, @r int
 
set

Use filters to keep your traces meaningfull

Say you have an sql trace for the event ‘TSQL SQL:StmtCompleted’ you are, most probably, NOT interested in the sql statements executed through the SQL Server Agent for instance. Filters below remove most of the unwanted ‘internal’ tsql.

The contents of your tracefile will become more meanigfull and not unimportant small …

Filter out ‘internal’ sql server proceses

Filter on ‘ApplicationName’
-- --
-- ApplicationName
 
-- SQL Profiler sessions
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
 
-- SQL executed by the SQL Agent
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLAgent%'
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQLDMO_1%'
Filter on ‘LoginName’
-- --
-- LoginName
 
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'remivisser'
exec sp_trace_setfilter @TraceID, 11, 0, 7, N'<username>'

  • / T-SQL
  • # 7043
  • NOV, 3 2006

cursors in tsql

Cursors in tsql, it can be done …

Simple cursor loop

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

  • / T-SQL
  • # 2155
  • NOV, 1 2006

return variables from dynamic sql – sp_executesql

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>

  • / T-SQL
  • # 808
  • SEP, 18 2006

environment

Getting around in the mssql environment

Whoami

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

Date Time functions

Date time functions (frustrations) in T-SQL.

Current date

Functions getdate and getutcdate display the current date. Both functions accept zero arguments.

1> print getdate()
2> print getutcdate()
3> go
Sep 18 2006  8:25PM
Sep 18 2006  6:25PM

Formatting

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).

I wrote the T-SQL code below to get me the

MSSQL cheat sheet for the Oracle minded

A translation of oracle dba_* tables/v$* views/functions to their respective mssql counterparts.

dba_* tables

Oracle Table/View MSSQL Table/View Note dba_data_files (and v_$logfile) <database>.dbo.sysfiles Contains datafiles AND logfiles dba_objects <database>.dbo.sysobjects dba_users master.dbo.sysxlogins (T) master.dbo.syslogins (V) <database>.dbo.sysusers Containts users that belong to <database>. Subset of users in master.dbo.sysxlogins

SQL Trace Reference

Display/Alter running traces or an alternative way to shutdown your server (when ‘c2 audit mode’ is enabled).

Display running traces

When ‘c2 audit mode’ is enabled you will see a trace running at all times. Whenever the server can’t write to it’s trace file it will shut itself down. (You need to restart with the -f switch to fix your problem).

Example below first displays all running traces and next aborts the trace ‘facilitating’ the ‘c2 audit mode’ option. The following functions are used, ::fn_trace_getinfo (displays information about runing traces) and sp_trace_setstatus (used to alter the status of a trace). 

1> exec sp_configure "c2 audit mode"
2> go
 name                                minimum     maximum

sp_configure

Display or change global configuration settings for your mssql server through sp_configure.

Show (advanced) configuration options

To display the current configuration settings execute dbo.sp_configure without arguments. 

C:\>osql -E
1> exec dbo.sp_configure
2> go
 name                                minimum     maximum     config_value run_value
 ----------------------------------- ----------- ----------- ------------ -----------
 allow updates                                 0           1            0           0
 Cross DB Ownership Chaining