Category Archives: SQL Server

SQL Server – Managing huge number of Server connections

My daily work involves connecting to several number of database servers and this involves me to remember server name or IP, port number, User Id and Passwords of those servers.  Number of servers I connect is more than 20 and due to this huge number I tend to forget the IP address, password etc. I can use the option of remember password, which will remember and list all the required server details with user id and password.

But the problem starts once you need to connect to a particular database server using more than one User Id. Once you use 2nd user id, SQL Server Management Studio fails to remember any of the passwords for that server.

So what is the solution?

SQL Server Management Studio has an option to manage server details, i.e. Registered Servers. By registering the server connection in the Registered Servers component of SQL Server Management Studio you can save the connection information for servers that are accessed frequently.
This option can be found under View menu.

A separate window will appear on the left hand side of the SSMS along with Object explorer. On right-clicking on Local Server Groups, option to register new server or to create new server group will appear. Creating Server group is optional but it will help in managing large number of server connections by logical grouping.

When Server Registration is opted, below window will appear. Opt for remember password option to remember the server connection.

In the above window you will find another tab which can be used to customize the connection further.

  1. Default database can be set here, so that every time you connect to a particular server from the registered server option, it will by default connect to the default database.
  2. Choose custom color for each server connections, so that one can easily differentiate between each connection.

SQL Server – Select, Update and Select in a Single statement

I came across a scenario where I had to optimize the stored procedure which was lengthy and using multiple locks to do the required operation. Objective of the stored procedure is to allocate slots based on some conditions.   Operations involved:

  1. Select few top rows based on slot status and some other conditions.
  2. Update its status as allocated.
  3. Return allocated slot ids to the user.

Below is the dress downed version of actual lengthy code.

SELECT TOP 3 * INTO #tempslot FROM dbo.ExamSlot IES WITH (NOLOCK)
WHERE IES.slot_status = 0
ORDER BY 1 DESC


UPDATE dbo.ExamSlot
SET slot_status = 1
FROM dbo.ExamSlot ES WITH (ROWLOCK)
INNER JOIN #tempslot TS ON ES.slot_id = TS.slot_id


SELECT ES.slot_id FROM dbo.ExamSlot ES WITH (ROWLOCK)
INNER JOIN #tempslot TS ON ES.slot_id = TS.slot_id

Below is the single statement to achieve above objective.

UPDATE SLT
SET SLT.slot_status =0
OUTPUT INSERTED.slot_id,
FROM (SELECT TOP 3 * FROM dbo.ExamSlot IES WITH (HOLDLOCK)
WHERE IES.slot_status = 1
ORDER BY 1 DESC) AS SLT

SQL – Generate INSERT statements

Here is a nice piece of stored procedure I found in www.sqlteam.com which can be used to generate INSERT statements for a table which has data. This is very helpful while distributing the application DB which requires some default information.

This script uses curser, so performance might be an issue in case of table with very large amount of data. There are a lot of commercial tools available to generate INSERT scripts. But those who don’t want to install some third party application for this task, this script will be an easy alternate option.


create proc generate_inserts @table varchar(20)
--Generate inserts for table @table
AS
declare @cols varchar(1000)
declare @col varchar(50)

set @cols=''

declare colcur
cursor for
select column_name
from information_schema.columns
where table_name=@table

open colcur

fetch next from colcur into @col

while @@fetch_status=0
begin
select @cols = @cols + ‘, ‘ + @col

fetch next from colcur into @col
end

close colcur
deallocate colcur

select @cols = substring(@cols, 3, datalength(@cols))

–select @cols

declare @sql varchar(4000)
declare @colname varchar(100),
@coltype varchar(30)

select @sql = ‘select replace(”insert ‘ + @table + ‘ (‘ + @cols + ‘) ‘

select @sql = @sql + ‘values (”’

declare ccur
cursor for
select column_name, data_type
from information_schema.columns
where table_name=@table

open ccur

fetch from ccur into @colname, @coltype

while @@fetch_status=0
begin
if @coltype in (‘varchar’, ‘char’, ‘datetime’)
select @sql=@sql + ”””

select @sql=@sql + ‘ + coalesce(convert(varchar, ‘ + @colname + ‘), ”null”) + ‘

if @coltype in (‘varchar’, ‘char’, ‘datetime’)
select @sql=@sql + ”””
select @sql = @sql + ”’, ”’

fetch from ccur into @colname, @coltype
end

close ccur
deallocate ccur

select @sql=substring(@sql, 1, datalength(@sql)-3)

select @sql=@sql + ‘)”, ”””null”””, ”null”) from ‘ + @table

 

exec (@sql)