How to Find Currently Running Query In SQL Server

Following script find out which are the queries running currently on your server.

SELECT sqltext.TEXT,
req.session_id,
req.status,req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

SQL Server stored procedure to generate random passwords

The following stored procedure creates strings of random characters based on four parameters that configure the result.

1. LEN - specifies the length of the result (required)
2. MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
3. RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
4. EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

Here is the stored procedure:

create proc [dbo].uspRandChars
    @len int=6,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go

To use the stored procedure issue commands such as the following.

declare @newpwd varchar(20)


-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd


-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd


-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd


-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd

Here is sample output from the above commands:

Next Steps

• Unsure what ASCII codes to use, refer to this list of ASCII codes
• See where this procedure can be used in your application. Some examples include users inviting their friends or generating a new password by request or as a temporary replacement.
• Determine your environment's security policy including password length and valid characters.
• Implement your password policy in the parameters.

SQL Server Column Level Encryption Example using Symmetric Keys

http://www.mssqltips.com/sqlservertip/2431/sql-server-column-level-encryption-example-using-symmetric-keys/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20111121

Problem

Financial firms have sensitive data including credit card numbers, social security numbers, financial history, etc.  Based on the sensitivity of the data, it needs to be secured and protected from unauthorized access. With data stored in tables, you have a few options to protect data.  First, you can protect the data using views. Second, we can also assign column level permissions to users.  Are there any other options available?  Can I use database encryption? Can I encrypt only a column in my table or do I need to encrypt the whole database? Check out this tip to learn more about column level encryption.

Solution

An important option to be considered during restricting unauthorized access to data is to encrypt data so that even if somebody was able to reach to data it is not discernable by a simple query. Since critical information like credit card numbers are stored in column or two, it does not make sense to encrypt the complete database or database files. In this tip I will walk through the processes of encrypting a column in a table which contains credit card information of customers of XYZ company by using SQL Server symmetric key encryption.  SQL Server has an encryption hierarchy that needs to be followed in order to support the encryption capabilities. We will follow the same hierarchy in the subsequent steps of this tip.

Step 1 - Create a sample SQL Server table

Let’s use an example where we create the dbo.Customer_data table which contains credit card details for customers. Our task is to protect this data by encrypting the column, which contains the credit card number. I will populate it will some sample data as shown below.

USE encrypt_test;
GO
-- Create Table
CREATE TABLE dbo.Customer_data
(Customer_id int constraint Pkey3 Primary Key NOT NULL,
Customer_Name varchar(100) NOT NULL,
Credit_card_number varchar(25) NOT NULL)
-- Populate Table
INSERT INTO dbo.Customer_data 
VALUES (74112,'MSSQLTips2','2147-4574-8475')
GO
INSERT INTO dbo.Customer_data 
VALUES (74113,'MSSQLTips3','4574-8475-2147')
GO
INSERT INTO dbo.Customer_data 
VALUES (74114,'MSSQLTips4','2147-8475-4574')
GO
INSERT INTO dbo.Customer_data 
VALUES (74115,'MSSQLTips5','2157-1544-8875')
GO
-- Verify data
SELECT * 
FROM dbo.Customer_data
GO

Step 2 - SQL Server Service Master Key

The Service Master Key is the root of the SQL Server encryption hierarchy. It is created during the instance creation. Confirm it's existence using the query below. If it does not exist we need to manually create it. Read more about service master key here.

USE master;
GO
SELECT *
FROM sys.symmetric_keys
WHERE name = '##MS_ServiceMasterKey##';
GO

Step 3 - SQL Server Database Master Key

The next step is to create a database master key. This is accomplished using the CREATE MASTER KEY method. The "encrypt by password" argument is required and defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data encryption. It is important that you keep the encryption password in a safe place and/or keep backups of your SQL Server Database Master Key.

-- Create database Key
USE encrypt_test;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password123';
GO

Step 4 - Create a Self Signed SQL Server Certificate:

The next step is to create a self-signed certificate that is protected by the database master key. A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. An optional argument when creating a certificate is ENCRYPTION BY PASSWORD. This argument defines a password protection method of the certificate's private key. In our creation of the certificate we have chosen to not include this argument; by doing so we are specifying that the certificate is to be protected by the database master key. Read more on about SQL Server certificates.

-- Create self signed certificate
USE encrypt_test;
GO
CREATE CERTIFICATE Certificate1
WITH SUBJECT = 'Protect Data';
GO

Step 5 - SQL Server Symmetric Key

A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database. Read more about SQL Server Symmetric Keys.

-- Create symmetric Key
USE encrypt_test;
GO
CREATE SYMMETRIC KEY SymmetricKey1 
 WITH ALGORITHM = AES_128 
 ENCRYPTION BY CERTIFICATE Certificate1;
GO

Step 6 - Schema changes

An Encrypted column can only be of datatype varbinary and since the column we want to encrypt is of datatype varchar, we have to create a new column and populate it with encrypted values.

USE encrypt_test;
GO
ALTER TABLE Customer_data 
ADD Credit_card_number_encrypt varbinary(MAX) NULL
GO

Step 7 - Encrypting the newly created column

To encrypt the data we will use the encryptbykey command. Below is a sample code which can be used to encrypt the data. Please note that symmetric key needs to opened before we can encrypt data and be sure you manually close the key else it will remain open for the current session.

-- Populating encrypted data into new column
USE encrypt_test;
GO
-- Opens the symmetric key for use
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE Customer_data
SET Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM dbo.Customer_data;
GO
-- Closes the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Below is an example of the encrypted data.

Step 8 - Remove old column

To finalize this process, let's remove the old column so that the table is left only with the encrypted data.

USE encrypt_test;
GO
ALTER TABLE Customer_data
DROP COLUMN Credit_card_number;
GO

Step 9 - Reading the SQL Server Encrypted Data

Let's take a look at an example of reading data using the decrypt by key option. As we indicated before, make sure you open and close symmetric key as shown earlier. Read more about the decrypt by key option.

USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
-- Now list the original ID, the encrypted ID 
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;
 
 -- Close the symmetric key
CLOSE SYMMETRIC KEY SymmetricKey1;
GO

Here are the results from the query:

Step 10 - Adding Records to the Table

Below is the sample code to insert values into the newly created encrypted column.

USE encrypt_test;
GO
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
-- Performs the update of the record
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (25665, 'mssqltips4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'4545-58478-1245') ) );    
GO

Below are the results from the table after the insert statement.

Step 11 - Accessing the Encrypted Data

All the read access users will see the encrypted values while they do a select on table. A user need to have permission to symmetric key and certificate to decrypt data, if they still try to decrypt then they will receive null for encrypted values. However they do not receive any errors. In the below sample code I am running select in context of a user 'test' which has only read access on DB.

Execute as user='test'
GO
SELECT Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number',
CONVERT(varchar, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM dbo.Customer_data;

Below you can see from the image below, the test user is not able to access the encrypted data.

Step 12 - Grant Permissions to the Encrypted Data

Permissions can be granted to a set of users to decrypt and read data using the commands below.

GRANT VIEW DEFINITION ON SYMMETRIC KEY::SymmetricKey1 TO test; 
GO
GRANT VIEW DEFINITION ON Certificate::Certificate1 TO test;
GO

Next Steps

• In the above steps we have learned how to encrypt a data column. The same concept can be used to encrypt employee salaries, social security numbers, customer phone numbers, etc.  So check out your data to determine what should be encrypted.
• Keep in mind implementation of column level encryption needs schema modification.
• Reading from an encrypted column is resource intensive and lowers the overall performance of database, hence that should be considered as well.
• The element of data that is encrypted remains in that state, even when recalled into memory.
• Read more Encryption Tips.

Getting file details

If you need to find out the attributes of a particular file, then try out spFileDetails:

/****** Object:  StoredProcedure [dbo].[spFileDetails]    Script Date: 03/28/2007 15:28:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spFileDetails]
@Filename VARCHAR(100)

/*
spFileDetails 'c:\autoexec.bat'
*/
AS
DECLARE @hr INT,         --the HRESULT returned from 
       @objFileSystem INT,              --the FileSystem object
       @objFile INT,            --the File object
       @ErrorObject INT,        --the error object
       @ErrorMessage VARCHAR(255),--the potential error message
       @Path VARCHAR(100),--
       @ShortPath VARCHAR(100),
       @Type VARCHAR(100),
       @DateCreated datetime,
       @DateLastAccessed datetime,
       @DateLastModified datetime,
       @Attributes INT,
       @size INT



SET nocount ON

SELECT @hr=0,@errorMessage='opening the file system object '
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
                                       @objFileSystem OUT
IF @hr=0 SELECT @errorMessage='accessing the file '''
                                       +@Filename+'''',
       @ErrorObject=@objFileSystem
IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem,
         'GetFile',  @objFile out,@Filename
IF @hr=0 
       SELECT @errorMessage='getting the attributes of '''
                                       +@Filename+'''',
       @ErrorObject=@objFile
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Path', @path OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'ShortPath', @ShortPath OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Type', @Type OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateCreated', @DateCreated OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateLastAccessed', @DateLastAccessed OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateLastModified', @DateLastModified OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Attributes', @Attributes OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'size', @size OUT


IF @hr<>0
       BEGIN
       DECLARE 
               @Source VARCHAR(255),
               @Description VARCHAR(255),
               @Helpfile VARCHAR(255),
               @HelpID INT
       
       EXECUTE sp_OAGetErrorInfo  @errorObject, 
               @source output,@Description output,
                               @Helpfile output,@HelpID output

       SELECT @ErrorMessage='Error whilst '
                               +@Errormessage+', '
                               +@Description
       RAISERROR (@ErrorMessage,16,1)
       END
EXEC sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile
SELECT [Path]=  @Path,
       [ShortPath]=    @ShortPath,
       [Type]= @Type,
       [DateCreated]=  @DateCreated ,
       [DateLastAccessed]=     @DateLastAccessed,
       [DateLastModified]=     @DateLastModified,
       [Attributes]=   @Attributes,
       [size]= @size
RETURN @hr

Execute spFileDetails 'c:\autoexec.bat'

Writing out a file

No problem – just create spWriteStringToFile and try:

alter PROCEDURE spWriteStringToFile
 (
@String Varchar(max), --8000 in SQL Server 2000
@Path VARCHAR(255),
@Filename VARCHAR(100)

--
)
AS
DECLARE  @objFileSystem int
        ,@objTextStream int,
  @objErrorObject int,
  @strErrorMessage Varchar(1000),
     @Command varchar(1000),
     @hr int,
  @fileAndPath varchar(80)

set nocount on

select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT

Select @FileAndPath=@path+'\'+@filename
if @HR=0 Select @objErrorObject=@objFileSystem , @strErrorMessage='Creating file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod   @objFileSystem   , 'CreateTextFile'
 , @objTextStream OUT, @FileAndPath,2,True

if @HR=0 Select @objErrorObject=@objTextStream, 
 @strErrorMessage='writing to the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Write', Null, @String

if @HR=0 Select @objErrorObject=@objTextStream, @strErrorMessage='closing the file "'+@FileAndPath+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'

if @hr<>0
 begin
 Declare 
  @Source varchar(255),
  @Description Varchar(255),
  @Helpfile Varchar(255),
  @HelpID int
 
 EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
  @source output,@Description output,@Helpfile output,@HelpID output
 Select @strErrorMessage='Error whilst '
   +coalesce(@strErrorMessage,'doing something')
   +', '+coalesce(@Description,'')
 raiserror (@strErrorMessage,16,1)
 end
EXECUTE  sp_OADestroy @objTextStream
EXECUTE sp_OADestroy @objTextStream 

execute spWriteStringToFile 'This article describes how to fully access the
local filesystem from SQL Server. It shows a
way of reading and writing data to file, and
accessing the details of the server's
filesystem using OLE Automation to access
the filesystem object'
, 'MyPath','MyFileName'

Reading a file into a SQL Server data type

This is all very well, but how about something that reads a file in one gulp into a varchar or XML datatype? Perhaps you need to extract data from HTML, XHTML or some other format. Create the ufsReadfileAsString procedure and try something like…

ALTER FUNCTION [dbo].[ufsReadfileAsString]
(
@Path VARCHAR(255),
@Filename VARCHAR(100)
)
RETURNS 
 Varchar(max)
AS
BEGIN

DECLARE  @objFileSystem int
        ,@objTextStream int,
  @objErrorObject int,
  @strErrorMessage Varchar(1000),
     @Command varchar(1000),
  @Chunk Varchar(8000),
  @String varchar(max),
     @hr int,
  @YesOrNo int

Select @String=''
select @strErrorMessage='opening the File System Object'
EXECUTE @hr = sp_OACreate  'Scripting.FileSystemObject' , @objFileSystem OUT


if @HR=0 Select @objErrorObject=@objFileSystem, @strErrorMessage='Opening file "'+@path+'\'+@filename+'"',@command=@path+'\'+@filename

if @HR=0 execute @hr = sp_OAMethod   @objFileSystem  , 'OpenTextFile'
 , @objTextStream OUT, @command,1,false,0--for reading, FormatASCII

WHILE @hr=0
 BEGIN
 if @HR=0 Select @objErrorObject=@objTextStream, 
  @strErrorMessage='finding out if there is more to read in "'+@filename+'"'
 if @HR=0 execute @hr = sp_OAGetProperty @objTextStream, 'AtEndOfStream', @YesOrNo OUTPUT

 IF @YesOrNo<>0  break
 if @HR=0 Select @objErrorObject=@objTextStream, 
  @strErrorMessage='reading from the output file "'+@filename+'"'
 if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Read', @chunk OUTPUT,4000
 SELECT @String=@string+@chunk
 end
if @HR=0 Select @objErrorObject=@objTextStream, 
 @strErrorMessage='closing the output file "'+@filename+'"'
if @HR=0 execute @hr = sp_OAMethod  @objTextStream, 'Close'


if @hr<>0
 begin
 Declare 
  @Source varchar(255),
  @Description Varchar(255),
  @Helpfile Varchar(255),
  @HelpID int
 
 EXECUTE sp_OAGetErrorInfo  @objErrorObject, 
  @source output,@Description output,@Helpfile output,@HelpID output
 Select @strErrorMessage='Error whilst '
   +coalesce(@strErrorMessage,'doing something')
   +', '+coalesce(@Description,'')
 select @String=@strErrorMessage
 end
EXECUTE  sp_OADestroy @objTextStream
 -- Fill the table variable with the rows for your result set
 
 RETURN @string
END 

Select dbo.ufsReadfileAsString ('MyPath','MyFileName')

STUFF

Stuff is another TSql Function which is used to delete a specified length of characters within a string and replace with another set of characters. The general syntax of STUFF is as below :

STUFF(character_expression1, start, length, character_expression2)Character_Expression1 represents the string in which the stuff is to be applied. start indicates the starting position of the character in character_expression1, length is the length of characters which need to be replaced. character_expression2 is the string that will be replaced to the start position.

Let us take an example :


EG:-

select STUFF((
SELECT DISTINCT TOP 100 PERCENT
                                ',' + t2.team_name
                        FROM    team_master AS t2
                        ORDER BY ',' + t2.team_name
                        FOR XML PATH('')
  ), 1, 1, '')

Time delay SQL injections

The following excerpt, courtesy of Wiley Publishing, is from Chapter 22 of the book "The Database Hacker's Handbook: Defending Database Servers" written by David Litchfield, Chris Anley, John Heasman and Bill Grindlay. Click for the complete book excerpt series or purchase the book.




Time delays
The previous examples of SQL injection techniques assumed that the client can view the error messages returned by the backend database server; however, often the Web server is set up so that error messages are not returned. In this case an attacker may suspect that the Web application is vulnerable to SQL injection but be unable to view any useful information because of its configuration. A method used here to extract the data is known as time delay SQL injection, and works on the basis that true or false queries can be answered by the amount of time a request takes to complete. The statement waitfor used with the delay argument causes

SQL Server to pause for the specified period of time:

waitfor delay '0:0:5'

This will pause the query for five seconds.

This feature can be leveraged to reveal information, such as whether the Web application's connection to the database is made as a system administrator:

if (is_srvrolemember('sysadmin') > 0) waitfor delay '0:0:5'

This will cause the query to pause if true, or return immediately if false. At the very lowest level, all data stored in the database is just a binary series of ones and zeros. This means that any data in the database can be extracted using a sequence of true/false questions. For example, the query

if (ascii(substring(@string, @byte, 1)) & (power(2, @bit))) > 0 waitfor
delay '0:0:5'

will trigger a delay only if the bit (@bit) of byte (@byte) in the string (@string) is set to 1. To retrieve the current database name from the server, execute

declare @string varchar(8192) select @string = db_name() if
(ascii(substring(@string, 1, 1)) & (power(2, 0))) > 0 waitfor delay
'0:0:5'

This will delay if the first bit of the first byte of the current database name is set to 1. The second bit of the first byte can then be queried:

declare @string varchar(8192) select @string = db_name() if
(ascii(substring(@string, 1, 1)) & (power(2, 1))) > 0 waitfor delay
'0:0:5'

and so on, building up the entire string. Obviously using this method, this would be a time-consuming process, mainly because of the five-second delay per set bit. It is not necessary, however, to run these queries sequentially or in any particular order. A small program, known as a harness, can be used to form the URLs to request with the necessary injected SQL to build the required string. Multiple requests can then be made to the server in multiple threads, and the harness program can then wait for the requests to return and build the string as they do.

Step-by-Step SQL Mirroring the ASPState Database

 

Main ::http://www.codeproject.com/KB/database/sqlmirroring.aspx

 

Introduction

In this article, I'm going to cover:

• Setting up the ASPState database in a mirrored environment
• Configuring an ASP.NET web-application to use the mirrored ASPState database
• How to do a manual failover (for maintenance scenarios)
• How to do a forced failover (when the database server unexpectedly goes boom)

Background

It's fairly common for businesses to want to provide some high availability for their SQL Server databases, and one option is to have two SQL Server databases on separate machines with a SQL Server database mirrored. Microsoft provides mirroring out of the box in SQL Server 2005 and SQL Server 2008, and is a much cheaper alternative than going down the clustering/failover route, but does provide some protection. In mirroring, there is always one Principal database which serves the requests, and a standby Mirror that is always synchronizing. If the Principal database goes down, then the Mirror can be forced to become the Principal, and will then serve the requests. Once the original Principal is available again, it will become the new Mirror.
Setting up a mirrored database is not straightforward as it should be. Although there is a wizard, there are a number of steps that must be performed before a database can be mirrored, and also a number of "gotchas" which prevent it from working. This I found, to my own pain and frustration, over a number of days. Although Microsoft provides a large amount of documentation on the subject, a lot of it may not be relevant, and it's not easy to work out exactly what needs to be done. I needed a step-by-step guide to setting up a basic mirrored database, and couldn't find one, so here's my attempt at providing this to help other people in the future.
I'm going to use the ASP Session database as the example, since this is a common requirement, but this guide is good for any database you may have. I've used the same steps for other company databases. This is also good for SQL Server 2005, although I performed these steps on a SQL Server 2008 Standard Edition.
As soon as a mirrored database server is introduced, the ASP State can no longer use the State Server or the In-Memory Model, but must be configured to use the ASPState SQL Server state database. Since this database is mirrored, the user can move from one web server to another during a session, and the session state will be maintained between the servers.

Step 1- Installing the ASP Session Database

This step is omitted for a normal database.
First things first, we need to install the ASPState database on the Principal server.
Navigate to the C:\Windows\Microsoft.NET\Framework\v2.0.50727 folder and type the following command:

Collapse

aspnet_regsql.exe -ssadd -sstype:p -S [myserver] -U [mylogin] -P [mypassword]

The parameters are case sensitive. This will create the ASPState database on the specified server. You could use the parameter -E instead of the -U and -P parameters to use the current credentials.
If the -sstype:p parameter is not specified, then by default, the ASP.NET sessions will be put into the TempDB database and not the ASPState database. This confused me for a while. This is fine for a normal non-mirrored environment because the ASP sessions will be cleared if the server is restarted. But, this is not fine for the mirrored environment, because we want to mirror the ASP session data itself, not just the Stored Procedures! Also, mirroring is not possible on the TempDB database. The -sstype:p parameter makes ASP.NET install the session tables into the ASPState database, and they'll be persisted if the server is restarted. This is exactly the behaviour we want for a mirrored environment.

Step 2 - Installing the Database on the Mirrored Server

Start at this step for a normal database.
In order to get the database onto the mirrored server, we do a full backup of the ASPState (or the database you are mirroring) on the Principal server, followed by a backup of the Transaction Log.

• Perform a full backup of the database on the Principal server.
• Perform a Transaction Log backup on the Principal server.
• Copy the backup file to the Mirror.
• Important: Do a restore of the full backup into a new step, but before doing the restore, go to Options, then ensure you check the No Recovery option! This is vital!
• Perform another restore of the Transaction Log, also with the No Recovery option. (This is important, otherwise you'll get an error when starting the mirror - See Gotchas section for explanation).

You'll notice that the database on the Mirror server now is marked as "Restoring..." and can't be accessed. This is normal and expected! This confused me for quite some time, thinking that it was incorrect.
The Mirror is always in a permanent Restoring state to prevent users accessing the database, but will be receiving synchronization data. If the database fails over to the Mirror, then it will become an active database and the old Principal will go into the Recovering state.

Step 3 - Setting the SQL Server Service Impersonation

By default, and in most installations, the SQL Server Service in the Services applet runs as the Local System account. However, for mirroring to work, this needs to be changed to a local user. The Local System account does not have access to the network resources, so is unable to communicate with the mirrored server through the endpoint. It's vital that this step is completed, since I spent many an hour wondering why the mirroring wasn't working.

• Create a local user on both the Principal and the Mirror server with the same username and password. For example, "sqluser".
• Edit the SQL Server Service and change the Logon to this user.
• Do the same for the SQL Server Agent service.
• Change the SQL Server Agent service to be Automatic.
• Re-start the SQL Server Service and then the SQL Agent service.
• Do this on both the Principal and the Mirror!

It's important that the SQL Agent is also running. Because:

1. it runs automated backup jobs and
2. it expires the sessions in ASP

If you find that ASP.NET sessions are not being expired in the ASPState database, then it's because the SQL Agent service is not running.
Sometimes, you may find that the SQL Agent does not start. This can be resolved by re-starting the SQL Server Service and then the SQL Agent again.
Create a SQL Login on both SQL Servers for this user you created.

Step 4 - Setting Up the Mirror

Now, it's time to actually setup the mirror! Go to the Database Properties on the ASPState database (or your database), and choose the Mirroring tab.
If the Mirror tab does not appear in SQL Server 2008, then re-run the setup and ensure you've ticked the Complete SQL Tools options.

• Click "Configure Security"
• Click Next on the wizard
• Choose whether you want a Witness server or not, (this article does not cover Witness servers) and click Next
• In the Principal Server Instance stage, leave everything as its default (you can't change anything anyway)

In the Mirror Server Instance stage, choose your Mirror server from the dropdown and click Connect to provide the credentials. Click Next.

• In the next dialog about Service Accounts, leave these blank (you only need to fill them in if the servers are in a domain or in trusted domains)
• Click Next and Finish
• Click "Do not start mirroring"
• Enter in the FQDN of the servers if you want, but this is not necessary (as long as it will resolve)
• Click Start Mirroring (if you do not have a FQDN entered, then a warning will appear, but you can ignore it)
• The mirror should then start, and within moments, the Status should be "synchronized: the databases are fully synchronized"

So, you should now have a working mirror! Perform a manual failover to test it. Follow the instructions below in "Doing a manual failover".
Here's what a working mirror setup looks like on the Principal:
And, here's what it looks like on the Mirror:

Doing a Manual Failover

If you need to take a box down for maintenance, then you can perform a manual failover. There are two methods - using the SQL Enterprise Manager, and using T-SQL. I'll explain both here, but you can choose depending on your situation.

Using the SQL Enterprise Manager

On the Principal server, right-click on the database and choose Mirror. Then, you'll see a Failover button. Click this, and you'll get a message about the failover swapping the roles. Click Yes, and within about 10 seconds, the roles will be swapped. If you do a refresh of the databases on both servers, you'll see the Principal is now marked as Restoring... and the old mirror has become the new Principal.

Using T-SQL

Here is the T-SQL to do the same as above. You can ignore the SET SAFETY lines if your mirror is using Synchronous mode. You can check the mode being used in the Mirror Properties (right-click Database, Mirror).

Collapse

--Run on principal
USE master
GO

ALTER DATABASE dbName SET SAFETY FULL
GO
ALTER DATABASE dbName SET PARTNER FAILOVER
GO
--Run on new principal
USE master
GO

ALTER DATABASE dbName SET SAFETY OFF
GO

Doing a Forced Failover

If your Principal goes Boom! and you have an unexpected outage, then you'll need to do a forced failover. This means the Mirror server is forced to become the Principal. There is a slight risk of data loss when this takes place, but if the Principal server is down, then what choice have you got? Obviously, you only want to do this forced failover when the principal is unexpectedly unavailable. In all other situations, you should do a normal manual failover.

Doing a Forced Failover using T-SQL

Collapse

--Run on mirror if principal isn't available
USE
master
GO

ALTER DATABASE dbName SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS
GO

For example, in a production environment, you'll have a monitoring system which checks for the availability of the Principal at regular intervals. If it detects that the database is unavailable, then it could run a VBScript or a USQL command line to execute this T-SQL on the mirror database. If you have a witness server, then this will be done automatically.

Step 5 - Configuring the ASP.NET Application to Use the Mirrored Database

The final step is to configure your ASP.NET application to use the mirrored ASPState SQL Server database (or your database). If you are using .NET 2.0 or higher, use the following steps:

• In the web.config file, change any existing <sessionState key to the following (or if it doesn't exist, add it):
  
  
  Collapse

  <sessionState mode="SQLServer" allowCustomSqlDatabase="true"
     sqlConnectionString="data source=[PRINCIPALSERVER];
     failover partner=[MIRRORSERVER];initial catalog=ASPState;user id=[DBUSER];
     password=[DBPWD];network=dbmssocn;" cookieless="false" timeout="180" />

• data source = [PRINCIPALSERVER] - For data source, add the IP address or name of the Principal server. This is a required parameter.
• failover partner = [MIRRORPARTNER] - Specify the name of the Mirror server. This is a required parameter.
• initial catalog = ASPState - or specify the name of your database if doing a custom database. This is a required parameter.
• allowCustomSqlDatabase = "true" - This is a required parameter.

By configuring your application to use these settings, when the application makes a request to the database and the Principal server is not available, .NET will automatically send the request to the mirrored server. This should happen transparently so your website user should not notice any outage.
If you're using .NET 1.1, this Failover Partner is not present, so you'll need to roll your own code to transfer the client. This could be a simple try...catch around the database connection, and in the catch, it retries using the mirrored server.
In a unexpected outage scenario, the following happens:

• The Principal server goes down.
• The Witness server or your monitoring service detects the Principal database in unavailable.
• The Witness server or your monitoring service forces the failover to your mirrored server, which becomes the new Principal.
• ADO.NET detects that the principal server is down, and retries automatically with the mirrored server.

Important: If you have two or more web servers, you'll need to copy the above SessionState entry on to each of the web servers.
You'll also need to ensure you have the same validation key / decryption key on each of the web servers. If not, then the session data will not be able to be read if created on a different server. This is necessary if mirroring the ASPState database.

Collapse

<machineKey
 validationKey="D581FCFD1xxxxxxxxxxxxxx16FEEBB4C56Axxxxxxxxxxxxxxxxxxxxxxxxxx"
 decryptionKey="55B44F83Cxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
 validation="SHA1" decryption="AES"
 />

Gotchas

You may get the following error messages and gotchas when playing with your mirror:
When attempting to do a FORCED FAILOVER with the SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS TSQL, you get the following error:

Collapse

"Cannot alter database <db>because the database is not in the correct state 
to become the principal" 

You'll be able to perform a manual failover fine, but forced failovers will cause the error. The mirror will NOT become the principal if it detects that the principal is already running. This may happen if you are testing a forced failover with code or a script and don't stop the SQL instance on the principal. If you stop the service on the principal, you can then perform a forced failover.
When setting up the mirror, you get the following error message when attempting to start the mirror:

Collapse

"Remote copy of <db>has not been rolled forward to a point in time 
that is encompassed in the local copy of the database. Error: 1402" 

This will occur if you have not done a transaction log backup on the Principal and restored it (no recovery) on the Mirror. You MUST do this step, or this error will occur.

Mirroring a 2^nd Database

If you want to mirror more than one database on the same servers, this is possible. You need to repeat Step 2 for the next database, and then Step 4 (just click Next, Next, Finish, etc). The databases will all use the same endpoint port.

Stopping the Mirror

If you want to stop and remove the mirror for a database, follow these steps:

On the Principal

1. Right-click, Mirror > Remove Mirroring
2. Refresh the database view (Principal should now show as normal database).
   The db is now a normal database.
3. Delete the database

On the Mirror Itself

1. Will currently be saying "Restoring..."
2. Right, click and Delete the database or Take Offline and then delete.
   If the Delete is not showing, or you don't have control, Refresh the database view or reopen SQL Enterprise Mangler.
   Or to turn back to a normal database without deleting: Do another Restore Database but change the Options to be With Recovery (top option)

Conclusion

If you've been following the steps in this article, you should have now successfully setup the ASPState database as a mirrored database (or your own database), providing your web users with a decent amount of availability and protection against outage.
I hope the article has been of interest and of some help to someone. Any questions, drop a comment, and I'll try to answer it, or update the article if areas are not clear. Thanks for reading!

Notes

You can have more than one database mirrored on the same set of servers. But, of course, the more you have, the worse the performance.

SQL SERVER – 2005 – OUTPUT Clause

         In SQl Server OUTPUT Clause is just like a Triggers, Expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement.

INSERT statement



CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))

DECLARE @TmpTable TABLE (ID INT, TEXTVal VARCHAR(100))

INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (1,'FirstVal')
INSERT TestTable (ID, TEXTVal)
OUTPUT Inserted.ID, Inserted.TEXTVal INTO @TmpTable
VALUES (2,'SecondVal')

SELECT * FROM @TmpTable
SELECT * FROM TestTable

UPDATE STATEMENT 








CREATE TABLE TestTable (ID INT, TEXTVal VARCHAR(100))


CREATE TABLE BAK_TestTable (Old_ID INT, Old_TEXTVal VARCHAR(100),New_ID INT, New_TEXTVal VARCHAR(100),Date DATETIME)

update TestTable set id=6 ,textval='Dinesh'
output deleted.ID,deleted.textval,inserted.id,inserted.textval,getdate() into BAK_TestTable
where id=2 







DELETE STATEMENT 

DELETE FROM  TestTable 

OUTPUT Deleted.* 

Where id=6

Note: An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back. The result should not be used if any error occurs when you run the statement

Find ANSII Values

Here’s a simple query that lists the ASCII Value of each character in your string in SQL Server

DECLARE @counter int;
DECLARE @colString varchar(Max)
 set @colString='THE ‘SINGUR';
Set @counter=1;

WHILE @counter <= DATALENGTH(@colString)
   BEGIN
   SELECT CHAR(ASCII(SUBSTRING(@colString, @counter, 1))) as [Character],
   ASCII(SUBSTRING(@colString, @counter, 1)) as [ASCIIValue]
    
   SET @counter = @counter + 1
   END
GO

Automatic SQL Server Backup

       In case, the database files are located in the following directory:
C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA
As you may already know, you cannot backup the *.MDF and *.LDF database files directly because they are attached to the SQL Server. You could detach them, back them up, and then re-attach them but then your database will be offline for a period of time.
To solve this problem, we first need to create a SQL file that can be used later to automate our database backup. Here’s how we can do that:

CREATE AN SQL FILE

1. Login to Microsoft SQL Server Management Studio.
2. Right click on your database from the “Databases” node.
3. Select “Tasks” -> “Back up…”
4. You are now presented with the “Back up Database” window. In this window you can specify your backup settings. At a minimum, you need to create a destination for your backup. Click the “Add” button and specify a full path name for your database backup in the “File name” field. You will need to use this path name later, so let’s take note of it.
5. You can choose any other backup options that are important to you. Over on the left hand side is “Options”. I would click on that and decide whether you want to use “append to an existing backup set” or “overwrite all existing backup sets”.
6. Once you are finished with your settings, you need to create an SQL file which will be used later for the automated database backup. To create that SQL file, click on the arrow next to “Script” at the top of the window. There will be an option for “Script action to File”. Go ahead and select that option. You will then be able to save your SQL Server Script File. Take note of where you have saved this file as well.

CREATE A BATCH FILE

Now that we have created the SQL file, we need to create a batch file that can be automatically executed by the Task Scheduler. To create this file, do the following:

1. Open Notepad and enter the following:
   sqlcmd -S .\SQLEXPRESS -i "C:\Users\Administrator\Documents\Backup.sql"
   Of course, substitute the pathname with the pathname of where you saved your SQL file.
2. Save this file and take note of where you saved it.
3. Now, using Windows Explorer, go to the directory of where you created this file and rename it to have a .bat filename extension instead of .txt.

SET BATCH FILE TO RUN IN WINDOWS TASK SCHEDULER

We are now ready to add the batch file to the Task Scheduler. Rather than using the Task Scheduler GUI, I prefer to do it on the command line. You can open a command prompt as Administrator or if you are already running as Administrator, you can use Start -> Run. Either way, enter the following:
schtasks /create /sc Daily /st 03:30:00 /tn "MyTask" /tr "cmd /c C:\Users\Administrator\Desktop\Backup.bat"
Again, substitute the pathname above with that of your batch file.
Also, set the time specified to something that is desirable for you. In the above example, we run this task at 3:30 AM.

SET SECUREBACKUP TO BACKUP YOUR DATABASE

The time specified in the Task Scheduler step above is important because you need have SecureBackup automatically backup the database file after the task is complete. Since the backup took place at 3:30, you can setup SecureBackup to run the backup at say, 4:00 AM. This way you will have the most recent backup.
From within the SecureBackup software, you need to create or edit an existing  Backup Job and include the location of the backup file that you chose in step 4 of “CREATE AN SQL FILE” above. If the file isn’t listed and you can’t select it from within SecureBackup, it’s because Windows Task Scheduler has not run your batch file yet. To run the batch file, simply double click on it from within Windows Explorer. It may take anywhere from 10 seconds to, possibly, a few hours depending on how large your database is. Once complete, you will have your database backup file and can select that file from within SecureBackup. You will also be able to schedule the Backup Job around how long it took for that batch file to complete.
You now have an automated backup solution for your Microsoft SQL database complete with a secure online storage solution. I hope this post was helpful to you.

Built-in Functions - String Functions

--Main link
--http://sqlserverpedia.com/wiki/Built-in_Functions_-_String_Functions#STUFF_Function



String functions let you extract various portions of character strings, change the case of strings, concatenate and reverse strings and perform many other types of manipulations. All built-in string functions are deterministic with the exception of CHARINDEX and PATINDEX.
String manipulations are an inherent part of any programming language. In transactional systems you might have to format the string accepted on a user-interface screen; in reporting situations you might need to concatenate or compare strings. Transact-SQL includes numerous functions that are fairly simple to use.

Contents

[hide]

• 1 LEFT Function
• 2 RIGHT Function
• 3 LTRIM and RTRIM Functions
• 4 SUBSTRING Function
• 5 REVERSE Function
• 6 CHARINDEX and PATINDEX Function
• 7 REPLACE Function
• 8 STUFF Function
• 9 LEN Function
• 10 REPLICATE Function
• 11 SPACE Function
• 12 UPPER and LOWER Functions
• 13 ASCII Function
• 14 UNICODE Function
• 15 CHAR Function
• 16 NCHAR Function
• 17 QUOTENAME Function
• 18 STR Function
• 19 SOUNDEX and DIFFERENCE Functions
• 20 More SQL Server Functions 
 

More SQL Server Functions

• SQL Server 2005 String Functions - like LTRIM, REPLACE, CHARINDEX, STUFF
• SQL Server 2005 Cursor Functions - like @@FETCH, CURSOR_STATUS
• SQL Server 2005 Date & Time Functions - like DATEPART, DATEDIFF, GETUTCDATE
• SQL Server 2005 Mathematical Functions - like RAND, CEILING, FLOOR
• SQL Server 2005 Text and Image Functions - like PATINDEX(), TEXTPTR()
• SQL Server 2005 System Functions - like COALESCE, ISNUMERIC, ERROR_MESSAGE
• SQL Server 2005 Configuration Functions - like @@MAX_PRECISION, @@SERVERNAME
• SQL Server 2000 Functions - if you can only develop against SQL Server 2000, this page contains the list of functions supported by 2000.

Dynamic Cross-Tabs/Pivot Tables

---main link

---http://www.sqlteam.com/article/dynamic-cross-tabs-pivot-tables

IMHO, the best feature of MS Access is the TRANSFORM statement, used to create cross-tabs/pivot tables. It does all of the work of dynamically generating the cross-tabulation and the summary calculations. T-SQL unfortunately doesn't have this statement, so you're stuck using complicated SQL commands, expensive 3rd party products, or exotic OLAP to make pivot tables...or you can use the following procedure to dynamically create them!

I got the idea from this question, asking how to "undo" a pivot table, and then I started working on how to create them in T-SQL. There are numerous ways of doing pivot tables, and this site has several examples (and lots of other cool stuff). The standard method uses a CASE statement, with one CASE for each pivot value (the column headings created by cross-tabbing the pivot column). The greatest shortcoming is finding a way to handle an unknown or changing number of pivot values. Obviously you have to know these values beforehand, and you must add a CASE for each new, distinct value inserted into the pivot column. The code listed below will do all of the work for you:

CREATE PROCEDURE crosstab 
@select varchar(8000),
@sumfunc varchar(100), 
@pivot varchar(100), 
@table varchar(100) 
AS

DECLARE @sql varchar(8000), @delim varchar(1)
SET NOCOUNT ON
SET ANSI_WARNINGS OFF

EXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE 1=2')
EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' 
+ @pivot + ' Is Not Null')

SELECT @sql='',  @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )

SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date', data_type) ) 
WHEN 0 THEN '' ELSE '''' END 
FROM tempdb.information_schema.columns 
WHERE table_name='##pivot' AND column_name='pivot'

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' + 
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN ' 
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM ##pivot

DROP TABLE ##pivot

SELECT @sql=left(@sql, len(@sql)-1)
SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql + ' ')

EXEC (@select)
SET ANSI_WARNINGS ON

Yeah, I know, you want to know how it works :) I won't go into detail on the mechanics of the code. Let's just say that if you can follow it, feel free to play with it; if not, DON'T TOUCH IT! The syntax for the procedure call is below, followed by a description of each parameter:

	1	2	3	4
EXECUTE crosstab	SELECT statement,	summary calculation,	pivot column,	table name

1. The SELECT statement can be anything, as long as it has proper syntax and includes a GROUP BY clause. You can use JOINs, but if you use table aliases you should include the alias in the summary calculation expression (2).
2. The summary calculation must have an aggregate function like SUM(), AVG(), MIN(), MAX(), etc. You'd have to modify the code if you want to use DISTINCT with these functions. COUNT(*) won't work, you have to COUNT on a column.
3. The pivot column must be in the table (4). You can use an expression for the pivot column (a+b, LEFT(FirstName,3), etc.) as long as it can be derived from the table listed in (4). A cross-tab heading will be created for each distinct value in the pivot colum/expression.
4. This table can be any table in your database, or another database if you use the full naming syntax (database.owner.table). Tables in a linked server may also work, but I haven't tested this. It's possible that a derived table (nested SELECT) can work, but I haven't tested this either. You would need to enclose the SELECT statement in parentheses, and use a table alias outside these parentheses, like this: '(SELECT LastName FROM myTable) AS Surnames'

I'll list some cross-tab settings and the results. Here's two you can run in the pubs database:

EXECUTE crosstab 'select title from titles inner join sales on (sales.title_id=titles.title_id) 
group by title', 'sum(qty)','stor_id','stores'

title	6380	7066	7067	7131	7896	8042
But Is It User Friendly?						30
Computer Phobic AND Non-Phobic Individuals: Behavior Variations				20
Cooking with Computers: Surreptitious Balance Sheets						25
Emotional Security: A New Algorithm				25
Fifty Years in Buckingham Palace Kitchens			20
Is Anger the Enemy?	3	75	10	20
Life Without Fear				25
Onions, Leeks, and Garlic: Cooking Secrets of the Mediterranean			40
Prolonged Data Deprivation: Four Case Studies				15
Secrets of Silicon Valley		50
Silicon Valley Gastronomic Treats					10
Straight Talk About Computers					15
Sushi, Anyone?			20
The Busy Executive's Database Guide	5					10
The Gourmet Microwave				25		15
You Can Combat Computer Stress!					35

EXECUTE crosstab 'select pub_name, count(qty) as orders, sum(qty) as total 
from sales inner join titles on (sales.title_id=titles.title_id) 
right join publishers on (publishers.pub_id=titles.pub_id) 
group by pub_name', 'sum(qty)','type','titles'

pub_name	orders	total	business	mod_cook	popular_comp	psychology	trad_cook	UNDECIDED
Algodata Infosystems	6	135	55		80
Binnet & Hardley	7	150		50		20	80
Five Lakes Publishing	0
GGG&G	0
Lucerne Publishing	0
New Moon Books	8	208	35			173
Ramona Publishers	0
Scootney Books	0

Here's one that will run in Northwind:

EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders 
ON (Employees.EmployeeID=Orders.EmployeeID) 
GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

LastName	1996	1997	1998
Buchanan	11	18	13
Leverling	18	71	38
Fuller	16	41	39
Peacock	31	81	44
Callahan	19	54	31
Suyama	15	33	19
King	11	36	25
Dodsworth	5	19	19
Davolio	26	55	42

Some things to look out for:

• This procedure builds a SQL statement from your parameters, inserts the CASE statements to handle the cross-tabbing, and then executes it. The size of this statement is maxed out at 8000 characters, so there's a limit on the size and complexity of the SELECT statement and the number of pivoted values you can have.
• This procedure uses a global temporary table, and will cause an error if two sessions execute it at the same time. (thanks for catching this Garth!) I will have a remedy soon and will post the fixed code in this article.
• Date values can be pivoted, but remember that the timestamp portion will be included, so you should CONVERT() them in the pivot expression. NULLs can't be pivoted, you must use IsNull() to replace them with a value. I have not tested nvarchar or nchar data types, and the code may need to be modified to work with them. Nor have I tried ntext or text types, but I'm sure they can't be pivoted anyway.
• You can use a HAVING clause, or include WITH CUBE or WITH ROLLUP in the SELECT statement and it will return the appropriate summary rows for each group.
• You can even ORDER BY cross-tabbed columns, something you can't do with the TRANSFORM statement in Access.

I'm spending some time working on enhancements to this procedure (for a follow-up article), to allow things like subqueries, multiple pivoting columns, non-aggregate values (e.g. - show the store with the highest sales each month, pivoted by book type). I would love to hear from anyone who has suggestions or solutions on how to improve this code.

Generate BCP, Bulk Insert code with TSQL

main like ---

http://blogs.msdn.com/b/patrickgallucci/archive/2007/08/10/generate-bcp-bulk-insert-code-with-tsql.aspx

This script will generate tsql code to bcp and bulk load data for all tables in a given DB.

SET NOCOUNT ON

GO

DECLARE @path nvarchar(2000), @batchsize nvarchar(40), 

@format nvarchar(40), @serverinstance nvarchar(200), 

@security nvarchar(800)

SET @path = 'C:\Temp\';

SET @batchsize = '1000000' -- COMMIT EVERY n RECORDS

SET @serverinstance = 'PGALLUCC-M7' --SQL Server \ Instance name

SET @security = ' -T ' -- -T (trusted), -Uloginid -Ploginpassword

--GENERATE CONSTRAINT NO CHECK

PRINT '--NO CHECK CONSTRAINTS'

SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' NOCHECK CONSTRAINT ' 

+  QUOTENAME( CONSTRAINT_NAME )

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 

--DISABLE TRIGGERS

PRINT '--DISABLE TRIGGERS'

SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' DISABLE TRIGGER ALL'

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

--BCP-OUT TABLES

PRINT '--BCP OUT TABLES '

SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 

+ '.' + QUOTENAME( TABLE_NAME ) + '" out "' + @path + '' + TABLE_NAME + '.dat" -q -b"' 

+ @batchsize + '" -e"' + @path + '' + TABLE_NAME + '.err" -n -CRAW -o"' + @path + '' 

+ TABLE_NAME + '.out"  -S"' + @serverinstance + '" ' + @security + ''

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

-- CREATE NON-XML FORMAT FILE

PRINT '--NON-XML FORMAT FILE'

SELECT  'bcp "' + QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) + '.' 

+ QUOTENAME( TABLE_NAME ) + '" format nul -n -CRAW -f "' + @path + '' 

+ TABLE_NAME + '.fmt"  --S"' + @serverinstance + '" ' + @security + ''

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

-- CREATE XML FORMAT FILE

PRINT '--XML FORMAT FILE'

SELECT  'bcp "' +QUOTENAME( TABLE_CATALOG ) + '.' + QUOTENAME( TABLE_SCHEMA ) 

+ '.' + QUOTENAME( TABLE_NAME ) + '" format nul -x -n -CRAW -f "' 

+ @path + '' + TABLE_NAME + '.xml"  -S"' + @serverinstance + '" ' + @security + ''

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

--TRUNCATE TABLE

PRINT '--TRUNCATE TABLE'

SELECT 'TRUNCATE TABLE ' +QUOTENAME( TABLE_NAME ) + ' 

GO '

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

--BULK INSERT

PRINT '--BULK INSERT'

SELECT DISTINCT 'BULK INSERT ' + QUOTENAME(TABLE_CATALOG) + '.' 

+ QUOTENAME( TABLE_SCHEMA ) + '.' + QUOTENAME( TABLE_NAME ) + ' 

FROM ''' + @path + '' + TABLE_NAME + '.Dat'' 

WITH (FORMATFILE = ''' + @path + '' + TABLE_NAME + '.FMT'',

BATCHSIZE = ' + @batchsize + ',

ERRORFILE = ''' + @path + 'BI_' + TABLE_NAME + '.ERR'', 

TABLOCK);

GO ' 

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' 

--OPENROWSET

PRINT '--OPENROWSET'

SELECT DISTINCT 'INSERT INTO ' + QUOTENAME(TABLE_CATALOG) + '.' 

+ QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' 

SELECT *

FROM  OPENROWSET(BULK  ''' + @path + '' + TABLE_NAME + '.Dat'',

FORMATFILE=''' + @path + '' + TABLE_NAME + '.Xml''

) as t1 ;

GO ' 

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

--GENERATE CONSTRAINT CHECK CONSTRAINT TO VERIFY DATA AFTER LOAD

PRINT '--CHECK CONSTRAINT'

SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' CHECK CONSTRAINT ' 

+  QUOTENAME( CONSTRAINT_NAME ) 

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

--ENABLE TRIGGERS

PRINT '--ENABLE TRIGGERS'

SELECT 'ALTER TABLE ' + QUOTENAME( TABLE_NAME ) + ' ENABLE TRIGGER ALL'

FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Technorati Tags: bcp, bulk insert, openrowset, information_schema