Reset an identity column of a table in SQL Server

This script came in handy when I was trying to help my client move from development to production server.

DBCC CHECKIDENT(table_name, RESEED, 0).

The table_name is the parameter that you would need to change per your table name. Also, this may be a good candidate to have a front end for the administrator to do maintenance in the future. Although, if you have a DBA he would rather prefer to execute this script from SQL management studio.

Stored Procedure to send email using sp_send_dbmail

This stored procedure was used to send email using the system stored procedure sp_send_dbmail. The client email system was Lotus 6.5 and the script was installed/test with SQL 2005.

This code can be downloaded from this google document site.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_Early_Buyer_Email]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'
--usp_Early_Buyer_Email ''0000037579'', ''06/01/2007'', ''SRI'', ''RADIO_14''

CREATE PROCEDURE [dbo].[usp_Early_Buyer_Email]
@purchase_order_no varchar(10),
@purchase_order_dt datetime,
@user_id varchar(10),
@device_id varchar(10)
AS
/****************************************************************************
HJ Consultant Srinivasan Prasanna

Client Company ABC

Proc Name usp_Early_Buyer_Email
Purpose This SP will be used to send out emails for Early PO''s
Date Created 5/23/2007
Version 1.0
Date Changed
******************************************************************************/
declare @sql varchar(5000)
declare @BUYER_ID varchar(100)
declare @cc_email varchar(100)
declare @msg varchar(1000)
declare @po_dt varchar(10)
declare @today varchar(10)

SET NOCOUNT ON
-- convert the datetime into varchar because the concatenation needs to email this message
set @po_dt = convert(varchar(10),@purchase_order_dt,101)
set @today = convert(varchar(10),getdate(),101)
-- this is hard coded value per the client''s request
set @cc_email = ''srinivasan@prasanna.com''
-- create temp table and drop it immediately
create table #temp
(colA varchar(100))

select @sql = ''insert into #temp select * from OPENQUERY (PeopleSoft, ''''SELECT BUYER_ID FROM PS_PO_HDR WHERE PO_ID = '''''''''' + @purchase_order_no + '''''''''''''')''
exec(@sql)

-- this means there is a buyer name in the database

if (select count(*) from #temp) > 0
begin
select @BUYER_ID = colA from #temp
end

-- drop the temp table
drop table #temp

select @BUYER_ID = email_id from t_buyer_email

where BUYER_ID = @BUYER_ID

select @msg = ''The Receipt date for this PO Number - '' + @purchase_order_no + '' is :
'' + @po_dt + '' . The user '' + @user_id + '' tried to receive this PO Number using
the device '' + @device_id +'' on '' + @today

EXEC msdb.dbo.sp_send_dbmail
@profile_name = ''HJ'',
@recipients = @BUYER_ID ,
@copy_recipients = @cc_email,
@body = @msg,
@importance = ''High'',
@subject = ''Early date PO - The PO cannot be received'' ;

-- at this point the email is complete. Now create a log for the PO which was not received on time.

insert into t_email_log
(buyer_email_id, cc_email_id, po_id, po_date, device_id, user_id, dt_timestamp)
select @BUYER_ID, @cc_email, @purchase_order_no, @purchase_order_dt, @device_id, @user_id, getdate()

END
GO

Linked server from SQL 2005 to Oracle 9.2

Recently I implemented a supply chain solution (Data Collection Advantage - If you need more information on this product, go this website ). In this project, since the client was a public traded company here in the US, they had strict auditing rules for the data movement/transactions and had to adhere to SOX (Sarabanes Oxley rules). Their ERP was PeopleSoft. I used the following script to link the Oracle 9.2 database to SQL Server 2005:
This code can also be downloaded from this google document site.

Tested on SQL Server 2005.
/****** Object: LinkedServer [PeopleSoft] Script Date: 10/02/2007 16:14:37 ******/
/****************************************************************************
HJ Consultant Srinivasan Prasanna
Client Company ABC
Proc Name usp_linked_server
Purpose This SP will be used to create linked server
Date Created 5/14/2007
Version 1.0
Date Changed
******************************************************************************/

EXEC master.dbo.sp_addlinkedserver @server = N'PeopleSoft', @srvproduct=N'Oracle 9.2', @provider=N'MSDAORA', @datasrc=N'Test8'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PeopleSoft', @optname=N'use remote collation', @optvalue=N'true'

/****** Object: LinkedServer [PeopleSoft] Script Date: 10/02/2007 16:14:37 ******/


Please feel free to comment on this code.

Code Formatter

I found a code formatter tool to pretty up some of my TSQL code. Specially, this tool makes my sql code look more legible and readable.

http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Please let me know your thoughts if you tried this tool or any other tool like this.

SET QUOTED_IDENTIFIER ON

SET QUOTED_IDENTIFIER ON:
When the set quoted identifier is on that means you can use a query like:
select * from "table name with space" this is important if you do not want to use the bracketed identifiers which is select * from [table name with space]

Purpose of this blog

This blog will exclusively feature some cool code snippets and any technical related article that has helped me in my professional career. I hope you will enjoy these and please feel free to comment if you find any errors or if the code does not work for you.