How to insert PDFs into a SQL Server DB from T-Sql and command shell

Hello Readers,

I thought I would write a few blogs this week for once.  I learned something new the other day at work and I thought I would share it with everyone.  The other day I had to somehow import a bunch of pdfs from a web server into our new sql server database.  After doing some googling and getting my knowledge up on command shell within T-Sql I found it was pretty simple.  Below are the steps I did to make it happen!

I ran into a few issues with the pdf being on the web server so to make it easier I copied the file from the server to the sql server on a share for a temp location until the file was imported into the database.

Step 1.  Let's set up our temp tables just let the rest of my blog entries.

--remove temp table
IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#myfiles'))DROP TABLE #myfiles

IF EXISTS(SELECT * FROM tempdb.dbo.sysobjects WHERE ID = OBJECT_ID(N'tempdb..#BinaryFile') ) DROP TABLE #BinaryFile

--Create temp table
CREATE TABLE #myfiles (MyID INT IDENTITY(1,1) PRIMARY KEY, FileName VARCHAR(2000))
CREATE TABLE #BinaryFile(FileID UNIQUEIDENTIFIER,filename VARCHAR(50),Team VARCHAR(50),BinaryFile VARBINARY(MAX))

Step 2.  Let's get a directory Listing of all the files in the share.  With the use of command shell we can easily do this.

INSERT INTO #myfiles (FileName )
EXECUTE MASTER.dbo.xp_cmdshell 'DIR "\\yourserver\share" /A-D /B'

Step 3. Let's create some variables.  These will help in the cursor I wrote to grab the data.

DECLARE @FileName VARCHAR(50) --the pdf name variable
DECLARE @Team VARCHAR(20)    --the team name
DECLARE @FilePath VARCHAR(200)   --file path location for pdf on web server
DECLARE @SqlPath VARCHAR(200)    --file path location for pdf on sql server
DECLARE @FileShare VARCHAR(200)  --share path for pdf on webserver
DECLARE @SqlShare VARCHAR(200)   --share path for pdf sql server
DECLARE @SqlCopyOutput INT       --to see if the file success copied to sql server
DECLARE @string VARCHAR(1000) --String for dynamic sql command

SET @FileShare = '\\server\share\'
SET @SqlShare ='\\server\share\'

Step 4.  The Cursor.  I wrote a cursor that will loop through all the files.  It will first move them from the web server to the sql server share.  After that it will insert them into the database.  If it was a successful insert it will then remove them from the sql server share. I had to use a few different commands such as command shell, t-sql, dynamic sql,  and open row set.  Here is the entire cursor.  It is pretty straight forward.  As you can see I keep with my baseball theme.  I am going to insert the Milwaukee Brewers and Minnesota Twins baseball schedule for 2013.

DECLARE Pdf_Cursor CURSOR FOR
SELECT Pdf.Team,Pdf.FileName
FROM( SELECT FileName ,
      SUBSTRING(FileName,6,((( CHARINDEX('2013.', filename) ) +(LEN(FileName)-18)) )) AS Team
     FROM #myfiles
     WHERE  FileName LIKE '2013%'
    ) Pdf
OPEN pdf_Cursor 
FETCH NEXT FROM pdf_cursor INTO @Team, @FileName
WHILE @@FETCH_STATUS = 0
   BEGIN
       SET @FilePath = @FileShare + @FileName
       SET @SqlPath = @SqlShare + @FileName

       --Move file from webserver to sql server so it will insert into di_media.
       SET @TransferFileString = 'copy ' +@FilePath+ ' ' + @SqlPath
       EXEC MASTER..xp_cmdshell @TransferFileString

       --making sure the file copied over to the sql serve before  inserting into di_media
       EXEC MASTER..xp_Fileexist @SqlPath ,@SqlCopyOutput OUTPUT

       IF @SqlCopyOutput != 1
           SELECT 'Fail to move to share', @FileName
       ELSE
           BEGIN
            SET @string = ' INSERT  INTO #BinaryFile( FileID,FILENAME,Team,BinaryFile)'
            SET @string = @string + 'SELECT  NEWID(),'  + CHAR(39) +@FileName  + CHAR(39) +',' + CHAR(39) + @Team + CHAR(39) +',BulkColumn FROM Openrowset(Bulk ' + CHAR(39) + @SqlPath + CHAR(39) +', SINGLE_BLOB) AS blob'
              
               EXEC(@string)

               --checking to see if pdf made it in the db before deleting from sql server.
               IF EXISTS(SELECT * FROM #BinaryFile WHERE filename = @FileName)
                       BEGIN
                           SET @string = 'Del ' + @SqlPath
                           EXEC xp_cmdshell @string
                           SET @string = ''
                       END              
               ELSE
                   SELECT 'Failed to insert into db', @Team
           END
      
       --clearing out path
       SET @FilePath = ''
       SET @SqlPath = ''
       SET @TransferFileString = ''

       FETCH NEXT FROM pdf_Cursor INTO @Team, @FileName
   END

CLOSE pdf_Cursor
DEALLOCATE pdf_cursor


Step 5.  To query the table.  Looks quickly the temp table to insure the data did make it.  As we can see it was a success!

SELECT * FROM #BinaryFile

The last thing I want to talk about the open row set I used to insert the actually pdf into the database.  This is the key part to the entire thing.  Here is the entire command printed out with out using dynamic sql.

INSERT  INTO #BinaryFile( FileID,FILENAME,Team,BinaryFile)
SELECT  NEWID(),'2013.BrewersSchedule.pdf','Brewers',BulkColumn 
FROM OPENROWSET(BULK '\\serever\share\2013.BrewersSchedule.pdf', SINGLE_BLOB) AS blob


I just learned how to turn this scripting logic into an SSIS package.  Later in another blog entry I will show how you can do this using SSIS.  It might be more useful in the SSIS package.

Dynamic SQL with Output parameters

Hey All!

So today in my post I would like to write about dynamic SQL in a store procedure with output parameter.  I have recently learned all about dynamic SQL.  Dynamic SQL is pretty neat, but I am sure we all know the blesses and curses of Dynamic SQL.  It can be pretty powerful.  The other day I had to create a store procedure to output various columns based on a bit flag as well as a show the record count.  In this blog I will show how I did this using a baseball example of data.

First thing is first let's get all the data set up first.  This little script will create all the temp tables with data for you.


--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Players') ) DROP TABLE #Players

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Roster') ) DROP TABLE #Roster

IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#sp_SearchRoster') ) DROP PROC #sp_SearchRoster

--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))
CREATE TABLE #Players(PlayerID INT,FirstName VARCHAR(20),LastName VARCHAR(20))
CREATE TABLE #Roster(TeamID INT, PlayerID INT)


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'

INSERT INTO #Players( PlayerID, FirstName, LastName )
   SELECT 100,'Hank','Aaron'
   UNION ALL
   SELECT 101, 'Robin','Yount'
   UNION ALL
   SELECT 102,'Ernie','Banks'
   UNION ALL
   SELECT 103,'Billy','Williams'
   UNION ALL
   SELECT 104, 'Kirby','Puckett'
   UNION ALL
   SELECT 105, 'Harmon','Killebrew'
   UNION ALL
   SELECT 106, 'Ty','Cobb'
   UNION ALL
   SELECT 107, 'Al','Kaline'
   
INSERT INTO #Roster( TeamID, PlayerID )
   SELECT 100,100
   UNION ALL
   SELECT 100,101
   UNION ALL
   SELECT 200,102
   UNION ALL
   SELECT 200,103
   UNION ALL
   SELECT 300,104
   UNION ALL
   SELECT 300, 105
   UNION ALL
   SELECT 400, 106
   UNION ALL
   SELECT 400, 107
  

I am sure there are several ways to write a dynamic SQL store procedure.  I found this way a pretty good way.  I would normal write some conditions for the part of when joining tables, but I got a lazy in this blog needless to say. Below is the store procedure.  I am not going to explain the store procedure that much since I have commented within it and it seems pretty start forward if you have ever read dynamic SQL before.

--Create store procedureSET NOCOUNT ONGOCREATE PROC #sp_SearchRoster
   (
       @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @Team VARCHAR(500),
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @RecordCount INT OUTPUT
   )AS BEGIN
   /*Variables*/
   DECLARE @SqlStringCount      NVARCHAR(MAX),
           @SqlStringColumns   VARCHAR(8000),
           @ParmDefinition     NVARCHAR(500),
           @Where              VARCHAR(8000),
           @From               VARCHAR(8000)
          
          
   /*Creating the select statement*/
   SET @SqlStringColumns =N'Select distinct r.teamid,'
   IF @FirstNameOut = 1 SET @SqlStringColumns = @SqlStringColumns + ' p.firstname,'
   IF @LastNameOut      = 1  SET @SqlStringColumns = @SqlStringColumns + ' p.lastname,'  
   IF @TeamNameOut      = 1 SET @SqlStringColumns = @SqlStringColumns + ' t.team,'
   IF @TeamFoundedOut   = 1 SET @SqlStringColumns = @SqlStringColumns + ' datepart(year,t.Founded) as Founded,'
   SET @SqlStringColumns = LEFT(@SqlStringColumns,LEN(@SqlStringColumns)-1)--HACK TO REMOVE TRAILING COMMA
  
   /*Creating From/join statement*/
   SET @From = ' from #Roster r '
   SET @From = @From + ' inner join #Teams t on t.teamid = r.teamid '
   SET @From = @From + ' inner join #players p on r.PlayerID = p.PlayerID '

   /*Creating the where clause*/
   SET @Where = ' where 1=1 '
   
    IF @Team IS NOT NULL SET @Where = @Where + ' and t.team in (' + CHAR(39) +REPLACE(@team,',', ''',''')+CHAR(39)  + ') '
   
    IF @TeamFoundedDateBegin IS NOT NULL AND @TeamFoundedDateEnd IS NOT NULL
       SET @Where = @where + ' and t.founded between ' + CHAR(39) + CONVERT(VARCHAR(10),@TeamFoundedDateBegin, 101) + CHAR(39)+ ' and '+ CHAR(39) + CONVERT(VARCHAR(10), @TeamFoundedDateEnd, 101) + CHAR(39)
  

   /********************************/
   /*Creating the Count SQL String*/
   SET @ParmDefinition = N'@RecordCountOUT int OUTPUT'
   SET @SqlStringCount = 'Select @RecordCountOUT = count(*) '
   SET @SqlStringCount = @SqlStringCount + @From --adding from clause
   SET @SqlStringCount = @SqlStringCount + @Where --adding from where
   EXECUTE sp_executesql @SqlStringCount, @ParmDefinition, @RecordCountOUT = @RecordCount OUTPUT --Executing the sql string to output the record
   /*********************************/   /**********************************/
   /*Creating the Columns SQL String*/
   SET @SqlStringColumns = @SqlStringColumns + @From --adding from clause
   SET @SqlStringColumns = @SqlStringColumns + @Where --adding from where
   EXEC(@SqlStringColumns) --Executing the Sql
   /********************************/
   END
GO


Let's first find all the teams that were founded in 1894.

DECLARE    @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @Team VARCHAR(500),
       @return_value INT,
       @RecordCount INT

EXEC   @return_value =  #sp_SearchRoster
       @FirstNameOut = 0,
       @LastNameOut =0,
       @TeamNameOut=1,
       @TeamFoundedOut  = 1,
       @TeamFoundedDateBegin = '1894',
       @TeamFoundedDateEnd ='1894',
       @team    =NULL,
       @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS N'@RecordCount'

Here are the results.

Let's say we want to just see all the players in the database.  It's simple just turn on and off some flags.

GO
DECLARE    @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @Team VARCHAR(500),
       @return_value INT,
       @RecordCount INT

EXEC   @return_value =  #sp_SearchRoster
       @FirstNameOut = 1,
       @LastNameOut =1,
       @TeamNameOut=0,
       @TeamFoundedOut  = 0,
       @TeamFoundedDateBegin = NULL,
       @TeamFoundedDateEnd =NULL,
       @team    =NULL,
       @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS N'@RecordCount'GO

Here is our result set.

Let's say we only want to Milwaukee Brewers and Minnesota Twins players instead of them.


GO
DECLARE    @FirstNameOut BIT,
       @LastNameOut BIT,
       @TeamNameOut BIT,
       @TeamFoundedOut BIT,
       @TeamFoundedDateBegin DATE,
       @TeamFoundedDateEnd DATE,
       @Team VARCHAR(500),
       @return_value INT,
       @RecordCount INT

EXEC   @return_value =  #sp_SearchRoster
       @FirstNameOut = 1,
       @LastNameOut =1,
       @TeamNameOut=1,
       @TeamFoundedOut  = 0,
       @TeamFoundedDateBegin = NULL,
       @TeamFoundedDateEnd =NULL,
       @team    ='Milwaukee Brewers,Minnesota Twins',
       @RecordCount = @RecordCount OUTPUT

SELECT @RecordCount AS N'@RecordCount'GO

The result set shown

How do you trouble shoot if your query is failing?   The way I like to do this is using the print command before I execute the SQL string.  If you write PRINT @SqlStringColumns it will output the following for you so you know what string is being executed

SELECT DISTINCT r.teamid ,
         p.firstname ,
         p.lastname ,
         t.team
FROM #Roster r
INNER JOIN #Teams t ON t.teamid = r.teamid
INNER JOIN #players p ON r.PlayerID = p.PlayerID
WHERE 1 = 1 AND t.team IN ( 'Milwaukee Brewers', 'Minnesota Twins' )

As you can see its pretty simple and you can really show and hide columns on the fly.  Now that I have given you the basic go ahead and have fun with Dynamic SQL!

Till next time,

Robert

A basic SQL Cursor

Hey followers or first time readers,

I thought this blog I would write a simple cursor.  I have been using them a lot lately and thought I might share a simple.  I have used them in past blogs but wanted an entry just about a cursor.

Let's get our temp table set up like my standard blog goes.

--Drop Table/store procedure
IF EXISTS ( SELECT  * FROM tempdb.dbo.sysobjects WHERE   ID = OBJECT_ID(N'tempdb..#Teams') ) DROP TABLE #Teams


--Create Table
CREATE TABLE #Teams(TeamID INT,Team VARCHAR(20),Founded DATE,City VARCHAR(30),State CHAR(2))


--Insert Values into Table
INSERT INTO  #Teams(TeamID,Team,Founded,City,State)
    SELECT  100,'Milwaukee Brewers','1970','Milwaukee','WI'
    UNION ALL
    SELECT 200,'Chicago Cubs','1870','chicago','IL'
    UNION ALL
    SELECT 300,'Minnesota Twins','1894','Minneapolis','MN'
    UNION ALL
    SELECT  400,'Detroit Tigers','1894','Detroit','MI'


Before we create the cursor let's set up a few variables.

--Setting up variables
DECLARE @Team VARCHAR(20),
    @Founded DATE,
    @City VARCHAR(30),
    @STATE CHAR(2)

Now we can define and cursor and loop through all the values in the temp table.  This is pretty simple and basic.

--Declare the cursor
DECLARE Team_Cursor CURSOR FOR
SELECT Team, Founded, City, State 
FROM #Teams--Opening cursor
OPEN Team_Cursor 
FETCH NEXT FROM Team_Cursor INTO @team, @Founded, @city, @State 
WHILE @@FETCH_STATUS = 0
   BEGIN
       --Outputting the current record in the cursor
        SELECT @team AS '@team', 
                DATEPART(YEAR,@Founded) AS '@Founded', 
                @city AS '@city', 
                @State AS '@State' 

       FETCH NEXT FROM Team_Cursor INTO @team, @Founded, @city, @State
   END

CLOSE Team_Cursor --closing the cursor
DEALLOCATE Team_Cursor --deallocate the cursor


Let's see what this looks like when its outputted.






Hope you learned how to write a simple cursor in this example.

-Robert