BCP through CLR

While giving a training last week, I had some time to play around with SQL Server. And what’s better than exploring something new and something that might be interesting for some of my customers. So because one of my customers is doing a lot of bcp ins and outs through Stored Procedures in SQL 2000 using XP_cmdshell I decided to write a little demo to do a bulk copy of data using CLR, giving them another reason to upgrade their SQL Servers. So let’s get started.

Before I can start I need, of course, a test environment. So I create a BulkIn and a BulkOut database. To get some data into it I just use the Import/Export Wizard to copy the Person.Person table from database Adventureworks2008R2 into the BulkOut.dbo.Person table. The dbo.Person table in database BulkIn is created with the following script:

USE [BulkIn]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Person](
 [BusinessEntityID] [int] NOT NULL,
 [Title] [nvarchar](8) NULL,
 [FirstName] [nvarchar](50) NOT NULL,
 [MiddleName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NOT NULL,
 [Suffix] [nvarchar](10) NULL
) ON [PRIMARY]
GO

Because I’m going to use the CLR I need to enable it at server level and because my bulk copy normally will not stay on my instance I will create an Assembly with Permission Level External and need to mark the database as Trustworthy:

USE master
GO

SP_CONFIGURE ‘clr enabled’, 1
RECONFIGURE
GO

ALTER DATABASE BulkOut SET TRUSTWORTHY ON
GO

 Now it’s time to open Visual Studio to do some programming. I choose to create a Visual Basic SQL CLR Database Project because I don’t want @chrissie1 to write one of these post again. And to be honest my last programming experience was with VB6 a long time ago so VB.NET is easier for me. When I create the project I must  choose .NET Framework 3.5 otherwise I will get errors when Itry to deploy my assembly to SQL Server:

Next thing I need to  do is to set up a database reference and I choose to put it on the BulkOut database. So when I deploy the package later it will be generated in the BulkOut database:

As I already mentioned the Permission Level needs to be External because in normal circumstances I will not stay on my SQL Server Instance. This is done in the Database tab of the Properties screen of the Project:

Last thing to do is adding a new Stored Procedure to my Project before I finally can start writing some code:

The Stored Procedure is added and I can start writing code after the ‘Add your code here comment. First thing to do is to build up the connectionstrings to the database so I can easily reference them further on:

        Dim srcConnStr As String = “Data Source = (local);Integrated Security = true; Initial Catalog = bulkout”
        Dim dstConnStr As String = “Data Source = (local);Integrated Security = true; Initial Catalog = bulkin”

The logic says I need to open a connection to the source database:

        Using SrcConn As SqlConnection = New SqlConnection(srcConnStr)
            SrcConn.Open()

Next I’m going to read the data from my source table using a SqlDataReader:

            Dim cmdSrcData As SqlCommand = New SqlCommand(“SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix FROM Person;”, SrcConn)
            Dim srcReader As SqlDataReader = cmdSrcData.ExecuteReader

To be able to write to my destination database I first need a connection:

            Using dstConn As SqlConnection = New SqlConnection(dstConnStr)
                dstConn.Open()

And finally I can start the BulkCopy of my data:

                Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(dstConn)
                    bulkCopy.DestinationTableName = “Person”
                    bulkCopy.WriteToServer(srcReader)

I finish with closing all the using blocks, but the datareader needs to be closed explicitly:

                    srcReader.Close()
                End Using
            End Using
        End Using
    End Sub

 This is all the coding I need. Now I can deploy the assembly to SQL Server using Visual Studio. When the deploy succeeds I can find the spd_BulkCopy Stored Procedure in my BulkOut database and when I start it like a normally start a Stored Procedure it starts executing and after the message: The command(s) completed successfully I can find all my data in the Person table in database BulkIn.

The TRUNCATE DATABASE script

Last week I saw a post on Twitter from Wesley Backelant (blog|twitter) about somebody emptying a database and then shrinking the files to keep the database size acceptable. We were having a good laugh about a new ‘Empty’ button or a ‘Restore Database with SCHEMA_ONLY) command in SQL in Denali but I had to admit that I occasionally get the request to copy a database from a non-production environment to the production environment and remove all the data. I should be able to do it by scripting out all the possible objects in the database but often when I execute the scripts I’m having problems with tables with a FOREIGN KEY reference to tables that are not created yet, views or stored procedures missing some basetables…

So I want to see how hard it is to just truncate all the tables in a database. Let’s find out.

To get started I create a database to play around with:

CREATE DATABASE EmptyDB
GO

Next I add 2 simple tables:

USE EmptyDB
GO

CREATE TABLE NoFK1 (
 NFK1_ID int IDENTITY (1,1),
 Data char (5)
 )
GO
 
CREATE TABLE NoFK2 (
 NFK2_ID int IDENTITY (1,1),
 Data char (5)
 )
GO

Without any data I cannot prove that I can truncate the tables:

INSERT INTO NoFK1
 VALUES (‘abcde’)
INSERT INTO NoFK2
 VALUES (‘fghij’)
GO 25

Let’s see that the data is actually there:

SELECT * FROM NoFK1
 UNION
 SELECT * FROM NoFK2
GO

So let’s start scripting the truncate of our tables, I use a table variable to hold the fully qualified name of the tables and will loop trough it to truncate each table:

WARNING: EXECUTING THE CODE BELOW WILL RESULT IN DATA LOSS

DECLARE @EmptyTables table (
 ET_ID int IDENTITY (1,1),
 TableName varchar (100)
 )

INSERT INTO @EmptyTables
 SELECT ‘[‘ + TABLE_SCHEMA + ‘]’ + ‘.’ + ‘[‘ + TABLE_NAME + ‘]’
 FROM INFORMATION_SCHEMA.TABLES
 
DECLARE @i int
DECLARE @c int
DECLARE @Table varchar (100)
DECLARE @TruncateString varchar (125)

SET @i = 1
SELECT @c = COUNT(*) FROM @EmptyTables
WHILE @i <= @c
 BEGIN
  SELECT @Table = Tablename
   FROM @EmptyTables ET
   WHERE @i = ET.ET_ID
   
  SET @TruncateString = ‘TRUNCATE TABLE ‘ + @Table
  EXEC (@TruncateString)
  
  SET @i = @i + 1
 END
GO

And when I use the previous select again I get no rows back. But this was too easy, looking in BOL I see that the TRUNCATE TABLE statement is allergic for FOREIGN KEY constraints. This asks for further testings. So in my existing database I add 2 new tables, 1 with a primary key and 1 referencing this primary key:

CREATE TABLE FKParent (
 FKP_ID int IDENTITY (1,1)
  PRIMARY KEY,
 Data char (5)
 )
GO
 
CREATE TABLE FKChild (
 FKC_ID  int
  CONSTRAINT FK_FKChild_FKParent FOREIGN KEY (FKC_ID)
  REFERENCES FKParent(FKP_ID),
 Data char (5)
 )
GO

Off course I check if the foreign key is working (executing the code should end in an error):

INSERT INTO FKChild
 VALUES (1,’pqrst’)
GO

And here I go again filling up my tables:

INSERT INTO NoFK1
 VALUES (‘abcde’)
INSERT INTO NoFK2
 VALUES (‘fghij’)
 INSERT INTO FKParent
 VALUES (‘klmno’)
INSERT INTO FKChild
 VALUES (@@identity,’pqrst’)
GO 25

Off course a little check if there is data in the tables:

SELECT * FROM NoFK1
 UNION
 SELECT * FROM NoFK2
 UNION
 SELECT * FROM FKParent
 UNION
 SELECT * FROM FKChild
GO

If I execute the previous script to empty the tables I get a nice error message saying that a TRUNCATE on table FKParent was not possible because of the FOREIGN KEY constraint. The good news is BOL is right but my script will be a little more complicated. First I’ll need to get the necessary information about my existing FOREIGN KEY’s so I can drop them and, because I want my database only emptied, recreate them.  But first a little test to see how smart SQL Server is. I just disable the FOREIGN KEY, TRUNCATE the FKCHild table first to make sure there is no referencing data left and then try to TRUNCATE the FKParent table:

ALTER TABLE FKChild NOCHECK CONSTRAINT FK_FKChild_FKParent
GO
TRUNCATE TABLE FKChild
TRUNCATE TABLE FKParent

But alas, the TRUNCATE statement fails  again. I will have to put a script around the TRUNCATE script that drops and recreates the FOREIGN KEY’s. My first step is to create a temporary table the hold the FOREIGN KEY information. I use a temporary table instead of a table variable to keep control when the table is dropped. A table variable will disappear after the batch completes so if I don’t run the complete script in 1 batch it’s possible that all my FOREIGN KEY’s are dropped and I don’t have the necessary information anymore to recreate them:

CREATE TABLE #FK_Info (
 FK_ID int IDENTITY (1,1),
 FK_Name varchar (120),
 CS_Name varchar (50),
 CT_Name varchar (50),
 PS_Name varchar (50),
 PT_Name varchar (50),
 CC_Name varchar (50),
 PC_Name varchar (50)
 )
GO

Now I’ve got the temporary table in place I can start filling it up with the details of my FOREIGN KEY’s:

INSERT INTO #FK_Info
 SELECT fk.name,
  SCHEMA_NAME(fk.schema_id),
  OBJECT_NAME(fk.parent_object_id),
  SCHEMA_NAME(o.schema_id),
  OBJECT_NAME(fk.referenced_object_id),
  cc.name,
  cp.name
 FROM sys.foreign_keys fk
 INNER JOIN sys.foreign_key_columns fkc
  ON fk.object_id = fkc.constraint_object_id
 INNER JOIN sys.columns cc
  ON fkc.parent_object_id = cc.object_id
   AND fkc.parent_column_id = cc.column_id
 INNER JOIN sys.columns cp
  ON fkc.referenced_object_id = cp.object_id
   AND fkc.referenced_column_id = cp.column_id
 INNER JOIN sys.objects o
  ON fk.referenced_object_id = o.object_id
GO

And in the third step I can drop the FOREIGN KEY’s using this script:

DECLARE @idrop int
DECLARE @cdrop int
DECLARE @CS varchar (50)
DECLARE @CT varchar (50)
DECLARE @FK varchar (50)
DECLARE @dropstring varchar (400)

SET @idrop = 1
SELECT @cdrop = COUNT(*) from #FK_Info

WHILE @idrop <= @cdrop
 BEGIN
  SELECT @CS = CS_Name,
   @CT = CT_Name,
   @FK = FK_Name
   FROM #FK_Info
   WHERE FK_ID = @idrop
  SET @dropstring = ‘ALTER TABLE [‘ + @CS + ‘].[‘ + @CT + ‘] DROP CONSTRAINT [‘ + @FK + ‘]’
  EXEC (@dropstring)
  
  SET @idrop = @idrop +1
 END
GO

Finally I can execute the above TRUNCATE script and get rid of all my data. The only thing I have to do is put the FOREIGN KEY’s back in place:

DECLARE @icreate int
DECLARE @ccreate int
DECLARE @CS varchar (50)
DECLARE @CT varchar (50)
DECLARE @FK varchar (50)
DECLARE @CC varchar (50)
DECLARE @PS varchar (50)
DECLARE @PT varchar (50)
DECLARE @PC varchar (50)
DECLARE @createstring varchar (600)

SET @icreate = 1
SELECT @ccreate = COUNT(*) from #FK_Info

WHILE @icreate <= @ccreate
 BEGIN
  SELECT @CS = CS_Name,
   @CT = CT_Name,
   @FK = FK_Name,
   @CC = CC_Name,
   @PS = PS_Name,
   @PT = PT_Name,
   @PC = PC_Name
   FROM #FK_Info
   WHERE FK_ID = @icreate
  SET @createstring = ‘ALTER TABLE [‘ + @CS + ‘].[‘ + @CT + ‘] ADD CONSTRAINT [‘ + @FK + ‘] FOREIGN KEY([‘ + @CC + ‘])
   REFERENCES [‘ + @PS + ‘].[‘ + @PT + ‘]([‘ + @PC + ‘])’
  EXEC (@createstring)
  
  SET @icreate= @icreate +1
 END
GO

Doing a proper job, I need to cleanup the mess:

DROP TABLE #FK_Info

And finally I have an empty database ready to be filled with new test or production data. For the moment this script does what I need to do but it will not work if I have INDEXED VIEWS in my database. Maybe something for a later blog post? 

%d bloggers like this: