Posts
1145
Comments
890
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: http://www.sql-server-performance.com/sql_server_maintenance.asp

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 <old server name>" 

  • Run this stored procedure: "sp_addserver <new server name>, 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 (@objname sysname, @includeID bit)
AS
-- Currently the proc ignores Identity columns (by checking syscolumns.status & 128 (0x80)
SET NOCOUNT ON

DECLARE @first bit,
@objid int,
@colid int,
@quote varchar(4),
@colname sysname,
@txt varchar(250),
@select varchar(8000),
@sqltxt varchar(8000)

SET @quote = ''''

SELECT @objid = id
FROM sysobjects
WHERE name = @objname

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 = @objid
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 @sqltxt = 'INSERT [' + @objname + '] ('

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

SET @select = 'SELECT '
WHILE (@colid IS NOT NULL) BEGIN

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

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

IF @first = 0
SELECT @sqltxt = @sqltxt + ', ' + @colname,
@select = @select + '+' + @quote + ',' + @quote + '+' + @txt
ELSE
SELECT @sqltxt = @sqltxt + ' ' + @colname,
@select = @txt

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

END

SELECT @sqltxt = @sqltxt + ') VALUES (' + @quote + '+',
@select = @select + '+' + @quote + ')' + @quote + ' FROM [' + @objname + ']'

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

EXEC( 'SELECT ' + @quote + @sqltxt + @select)

if (@includeID = 1) begin
select 'SET IDENTITY_INSERT [' + @objname + '] 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:

http://www.codeproject.com/gen/design/DudeWheresMyBusinessLogic.asp

posted @ Friday, July 08, 2005 2:15 PM | Feedback (1)
The Future of Application Design

If you've ever been on a project that uses this methodology, I feel for you:

http://weblogs.asp.net/alex_papadimoulis/archive/2005/05/05/405747.aspx

posted @ Friday, July 08, 2005 2:14 PM | Feedback (1)
Column Definition Stored Procedure

From: http://pscode.com/vb/scripts/ShowCode.asp?txtCodeId=549&lngWId=5

This can help you avoid a lot of typing.  It lists out standard “Insert Into“, “Values“, etc. text when you pass in a table name, like 'exec sp__ColDefinition MyTable'.

---------------------------------------- -- ---------------------------------------- --------------------

use master
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__ColDefinition]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp__ColDefinition]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_SizePrecScale]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_SizePrecScale]
GO

CREATE function fn_SizePrecScale(
@Type varchar(50),
@Length int,
@Prec int,
@Scale int)
returns varchar(100)
as
begin
DECLARE @RC varchar(100)

IF @Type in ('smalldatetime','datatime','text','image','bit','ntext','uniqueidentifier','bigint','int','smallint','tinyint','money','smallmoney')
SET @RC = @Type
ELSE IF @Type in('decimal','numeric')
SET @RC = @Type + '(' + cast(@Length as varchar) + ') [' + cast(@Prec as varchar) + ',' + cast(@Scale as varchar) + ']'
ELSE
SET @RC = @Type + '(' + cast(@Length as varchar) + ')'

RETURN(@RC)
end

GO
CREATE PROC dbo.sp__ColDefinition
@ObjectName sysname
AS
BEGIN
DECLARE @xtype char(2)

SELECT @xtype = xtype FROM sysobjects WHERE name = @ObjectName

IF @xtype not in('U','FN','V','P')
begin
RAISERROR ('L''objet n''existe pas dans le catalogue', 16, 1)
RETURN
end


IF @xtype in ('U','V')
SELECT
[DECLARE] = '@' + C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ',',
C.name + ',' AS [INSERT INTO],
'@' + C.name + ',' AS [VALUES],
C.name + ' = @' + C.name + ',' AS [SET],
'@' + C.name + ' = ' + C.name + ',' AS [SELECT]
from sysobjects O, syscolumns C, systypes T
where O.id = C.id and C.xtype = T.xtype
and O.name = @ObjectName and T.xtype = T.xusertype
order by C.colorder
ELSE
SELECT
[DECLARE] = C.name + ' ' + dbo.fn_SizePrecScale(T.name,C.length,C.xprec,C.xscale) + ','
from sysobjects O, syscolumns C, systypes T
where O.id = C.id and C.xtype = T.xtype
and O.name = @ObjectName and T.xtype = T.xusertype
order by C.colorder
END

posted @ Friday, July 08, 2005 1:55 PM | Feedback (1)
This post depresses the hell out of me.....

In a happy sort of way.  If you like 'happily ever after' stories (well, happily ever after for right now, anyways)....

http://nslog.com/archives/2004/06/09/a_crush_realized.php

posted @ Tuesday, July 05, 2005 10:02 PM | Feedback (3)