Author Archives: Amit Banerjee

Customizing the TokenPerm in Yukon SP3!!

Posted on by
I had written about the TokenPerm cache store in a previous blog post of mine. The trace flags mentioned in the KB Articles helped in throttling the cache to prevent the cache size from growing out-of-proportions. Recently, a new Trace Flag (-T4621) was added SQL Server 2005 Service Pack 3 (KB959823) to customize the size of the Token Perm cache. Instead of hard coding the quota for Token Perm with the earlier trace flags, you can use the formula to specify the Registry value:
 
Quota = 1,024 * 2 * ( <Number of Distinct Logins> + <Total Number of Users in each database> )
 
Remember to turn off the other trace flags for Token Perm issues that you had activated on your systems previously. You cannot enable trace flag 4621 together with trace flag 4618. When trace flag 4621 and trace flag 4618 are enabled together, trace flag 4618 takes precedence. The reason this trace flag was introduced was to prevent detrimental effect to systems where constant flushing of the Token Perm cache would affect the performance rather than helping it.

Script for printing out ASCII characters

Posted on by
I had recently written a script to convert a String to it’s equivalent in ASCII using the Sample code given in Books Online. Thought I would put it up so that other people could use it as well.
 
<<SCRIPT>>
 

SET TEXTSIZE 0 

SET NOCOUNT ON 

--Declare the Local Variables 

DECLARE @position int, @string varchar(256),@count int,@rownum int,@output varchar(8000) 

-- Initialize the Local Variables used to keep track of the current position and offsets 

SET @position = 1 

SET @count = 1 

-- Get the data that you want to convert to ASCII 

select [name],ROW_NUMBER() OVER(ORDER BY [name]) as ROWNUM 

into #tmp_tbl 

from sys.syslogins 

select @rownum = max(rownum) from #tmp_tbl 

-- Begin WHILE loop to convert the String to ASCII 

while (@rownum >= @count) 

begin 

select @string = [name] from #tmp_tbl where rownum = @count 

print 'Converting:'+@string 

WHILE @position <= DATALENGTH(@string) 

BEGIN 

SET @output = @output+space(1)+CAST(ASCII(SUBSTRING(@string, @position, 1)) as CHAR(4)) + '|' + CHAR(ASCII(SUBSTRING(@string, @position, 1))) 

SET @position = @position + 1 

END 

set @position = 1 

set @count = @count + 1 

print @output 

set @output = '' 

end 

-- Drop the Temporary Table created above 

drop table #tmp_tbl 

SET NOCOUNT OFF

 

<</SCRIPT>
 
A sample output of the above script would be:
Converting: tester 

116 |t 101 |e 115 |s 116 |t 101 |e 114 |r 

 

How to find startup parameters for SQL Server 2005 using WMI

Posted on by

SQL Server startup parameters -d, -l, -e which store the information about the master database data file, log file and ERRORLOG locations respectively are stored under the registry key: 
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\MSSQLServer\Parameters

However, if you need to find out the startup parameters without using the regedit snap-in (default and any extra ones added), you can use the following script:

 <<SCRIPT>>

strComputer = "."
 

Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\Microsoft\SqlServer\ComputerManagement")

 

Set colItems = objWMIService.ExecQuery( _

 

"SELECT * FROM SqlServiceAdvancedProperty WHERE SqlServiceType = 1 and PropertyName = 'STARTUPPARAMETERS'",,48)

 

For Each objItem in colItems

 

Wscript.Echo "ServiceName: " & objItem.ServiceName

 

Wscript.Echo "PropertyName: " & objItem.PropertyName

 

Wscript.Echo "PropertyStrValue: " & objItem.PropertyStrValue

 

Next

 

 <</SCRIPT>

Cannot modify table column in SQL Server 2008 using Table Designer

Posted on by

There is a new option added in SQL Server 2008 for modifying tables while using the Table Designed in Management Studio which will prevent saving any changes to a table that needs a drop/re-create of the table. The error that you would see in the Management Studio is:

TITLE: Microsoft SQL Server Management Studio
——————————

User canceled out of save dialog
(MS Visual Database Tools)

If you want to modify a table property through the UI which requires a table recreation, then you need to change the following options in Management Studio:

Tools ->Options-> Designers -> Table and Database Designers -> uncheck the option "Prevent saving changes that require table re-creation"

How to script out indexes for a database using DMO

Posted on by

Create the following stored procedure in your database: 

CREATE PROCEDURE [dbo].[proc_genscript]
 

@ServerName varchar(30),
 

@DBName varchar(30),
 

@ObjectName varchar(50),
 

@ObjectType varchar(10),
 

@TableName varchar(50),
 

@ScriptFile varchar(255)
 

AS
 

DECLARE @CmdStr varchar(255)
 

DECLARE @object int
 

DECLARE @hr int
 

SET NOCOUNT ON
 

SET @CmdStr = 'Connect('+@ServerName+')'
 

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
 

EXEC @hr = sp_OASetProperty @object, 'LoginSecure', TRUE
 

EXEC @hr = sp_OAMethod @object,@CmdStr
 

SET @CmdStr =
 

CASE @ObjectType
 

WHEN 'Database' THEN 'Databases("'
 

WHEN 'Procedure' THEN 'Databases("' + @DBName + '").StoredProcedures("'
 

WHEN 'View' THEN 'Databases("' + @DBName + '").Views("'
 

WHEN 'Table' THEN 'Databases("' + @DBName + '").Tables("'
 

WHEN 'Index' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Indexes("'
 

WHEN 'Trigger' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Triggers("'
 

WHEN 'Key' THEN 'Databases("' + @DBName + '").Tables("' + @TableName +
 

'").Keys("'
 

WHEN 'Check' THEN 'Databases("' + @DBName + '").Tables("' +
 

@TableName + '").Checks("'
 

WHEN 'Job' THEN 'Jobserver.Jobs("'
 

END
 

SET @CmdStr = @CmdStr + @ObjectName + '").Script(5,"' + @ScriptFile + '")'
 

EXEC @hr = sp_OAMethod @object, @CmdStr
 

EXEC @hr = sp_OADestroy @object




After the above stored procedure is created, you can create the commands for generating the index creation scripts. 


The script below will generate the CREATE script for the Primary Key for the table tbl_tran_repl (Key name = PK_tbl_tran_repl) and store it in the file PK_tbl_tran_repl.sql under the C:\Database folder. 


Example: 


DECLARE @return_value int
 

EXEC @return_value = [dbo].[proc_genscript]
 

@ServerName = '<SQL Server Name>',
 

@DBName = '<Database Name>',
 

@ObjectName = 'PK_tbl_tran_repl',
 

@ObjectType = 'KEY',
 

@TableName = 'tbl_tran_repl',
 

@ScriptFile = 'C:\Database\PK_tbl_tran_repl.sql'
 

SELECT 'Return Value ' = @return_value




The original script is obtained from Database Journal.