Posts
832
Comments
691
Trackbacks
1
July 2005 Blog Posts
T-SQL: Triggers and Dirty Reads

Someone was troubleshooting a production issue and asked me “If a trigger updates the row that is being inserted, is it possible to see the row before the trigger completes its task?”  Since I didn't know, and couldn't find a clear answer on Google (I hate it when that happens), I tested it out.  I created a test table with an identity column and a value column.  Then I created a trigger that waited for one minute when fired (using WAITFOR '00:01:00'), and then incremented the inserted value.

Sure enough, when trying to do a normal select right after the insert happens, it is blocked.  But, if you add the with (nolock) hint to the select statement, you can see the original insert.  So, it behaves as one would expect in situations where the isolation level is set to READ UNCOMMITTED.

 

posted @ Thursday, July 21, 2005 12:25 PM | Feedback (0)
T-SQL: Force a database offline

alter database {DATABASE name} set offline with rollback immediate

posted @ Wednesday, July 20, 2005 9:57 AM | Feedback (1)
How to rename a SQL Server

From:

Useful if you need to replace/upgrade a machine, and you also need to rename it.

Here's how to rename a server running SQL Server:

  • Rename the physical server by going to the "Identification" tab of the "Network" properties window (available from Control Panel) if you are running Windows NT 4.0. If you are running Windows 2000, go to the "Network Identification" tab of the "Computer" Properties windows (available from Control Panel). 

  • Reboot the server. 

  • Run the SQL Server setup program. At some point early in the installation you will be prompted to "Upgrade" SQL Server. Answer "yes" to this question. After a very short period of time, you will be prompted that the installation is complete.

  • Reboot the server.

  • Run this stored procedure: "sp_dropserver

  • Run this stored procedure: "sp_addserver , local"

posted @ Monday, July 18, 2005 10:28 AM | Feedback (1)
Create Insert Stored Procedure

Almost as an aside, this script was posted at:

http://scottonwriting.net/sowBlog/posts/2801.aspx

It allows you to recreate a table's data.  This would be a good way to script out lookup tables and add them to a create database script.  Very cool.

*************************************************************************

--Stored Procedure to Create Insert commands from table of data
--At the moment dates need to be edited.

go

CREATE PROCEDURE usp_CreateInsert ( sysname, bit)
AS
-- Currently the proc ignores Identity columns (by checking syscolumns.status & 128 (0x80)
SET NOCOUNT ON

DECLARE bit,
int,
int,
varchar(4),
sysname,
@txt varchar(250),
varchar(8000),
varchar(8000)

SET = ''''

SELECT = id
FROM sysobjects
WHERE name =

CREATE TABLE #sphelptab
(
col_name char (30) NULL,
col_type char (30) NULL,
col_type_val int NULL,
col_len int NULL,
col_prec char (5) NULL,
col_scale char (5) NULL,
col_status int NULL,
colid int NULL
)
INSERT
INTO #sphelptab
SELECT c.name,
t.name,
c.type,
c.length,
CONVERT(CHAR(5),c.prec),
CONVERT(CHAR(5),c.scale),
c.status,
c.colid
FROM syscolumns c,
systypes t
WHERE c.id =
AND c.usertype *= t.usertype

--
-- Don't display precision and scale for datatypes
-- which they not applicable.
--

UPDATE #sphelptab
SET col_prec = '',
col_scale = ''
WHERE col_type IN
(SELECT name FROM systypes WHERE type NOT IN
(38,48,52,55,56,59,60,62,63,106,108,109,110,122))
--
-- Insert script
--
SELECT = 'INSERT [' + + '] ('

if ( = 0) begin
SELECT = MIN(colid),
= 1
FROM #sphelptab
WHERE col_status & 128 <> 128
end
else begin
SELECT = MIN(colid),
= 1
FROM #sphelptab
end

SET = 'SELECT '
WHILE ( IS NOT NULL) BEGIN

SELECT = RTRIM(col_name),
@txt = CASE
WHEN col_type_val IN (37,39,47) THEN
' ISNULL(' + +++ +'+'+ RTRIM(col_name) +'+'+

+++ + ',''NULL'')'
ELSE ' ISNULL(CAST('+RTRIM(col_name)+ ' AS VARCHAR),''NULL'')'
END
FROM #sphelptab
WHERE colid =

IF = 0
SELECT = + ', ' + ,
= + '+' + + ',' + + '+' + @txt
ELSE
SELECT = + ' ' + ,
= @txt

SELECT = MIN(colid),
= 0
FROM #sphelptab
WHERE col_status & 128 <> 128
AND colid >

END

SELECT = + ') VALUES (' + + '+',
= + '+' + + ')' + + ' FROM [' + + ']'

if ( = 1) begin
select 'SET IDENTITY_INSERT [' + + '] ON'
select 'GO'
end

EXEC( 'SELECT ' + + + )

if ( = 1) begin
select 'SET IDENTITY_INSERT [' + + '] OFF'
select 'GO'
end

GO

posted @ Monday, July 11, 2005 9:01 AM | Feedback (0)
Removing Business Logic from Stored Procedures

This is a bit extreme, but if you want to 'go all the way' in moving business logic out of your stored procedures, this explains one way of going about it: