SQL – Exporting single table to XML using – bcp queryout

SQL to XML

This is a quick and dirty but functional introduction to the export of a single database table to XML using Microsoft SQL 2012 Managment Studio.

The initial problem was to export and save a SQL-log to XML.
The logging is based on PHP/MFC code-logs, error-logs and communication-logs. Due to the high amount of logging, the table would eventually overflow even if the BIGINT datatype was used.

The problem is solved by exporting daily logs to an sufficiently big harddrive in XML format, and delete the table content and reseed the Identity coulumn after performing the export.

Task 1 : Export only the Table containing the Log in XML format
Task 2 : Delete table content
Task 2 : Reseed the Table Identity

This was not entirely straightforward, and it took me 1 full day searching the ever more insane Google-spagetti to find the following information:

Task 1 :

Using the bcp command is fairly straightforward when you know how to do it.

The simple way to test this, is to open the Powershell, and test the commands. Write bcp and press enter to get the initial format. My command looks like the one below:

bcp "SELECT * FROM Mydatabase.dbo.Tablename FOR XML AUTO" queryout "E:\test.xml" -c -T -S "MYSERVER/TESTSERVER"

Remember to use Your own database name, and server name.

Pitfalls I detected are the following:

  1. 1. double quotes in the SQL statement and the filename!
  2. 2. If you forget to use the -S “MYSERVER/TESTSERVER” several errors might occur ( SQLState = 08001, NativeError = 2 )

bcp5

bcp4

Before the command is inserted into a SQL Query the following code should be executed in a Query to enable the xp_cmdshell.

EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

Task 1/2/3 – implemented as T-SQL:

Below the bcp command, is inserted into a T-SQL Query, and in the end of the code the deletion and reseeding the table is executed.

The following code is tested :

-- SQL Single table backup (task 1)
-- Remember to extchange the @table variable and database name in the statement
DECLARE @path VARCHAR(256) -- path for backup files DECLARE @fileDate VARCHAR(20) -- used for file name
DECLARE @stateMent VARCHAR(100)
SET @path = '"E:\' 
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(20),GETDATE(),108),':','') 
DECLARE @table VARCHAR(128)
DECLARE @file VARCHAR(255)
DECLARE @cmd VARCHAR(512)
SET @table = 'Mydatabase_dbo_Tablename'-- Table Name which you want to backup
SET @statement = '"SELECT * FROM Mydatabase.dbo.Tablename FOR XML AUTO, BINARY BASE64"' 
SET @file = @path + @table + '_' + @fileDate + '.xml"'
SET @cmd = 'bcp ' + @statement + ' queryout ' + @file + ' -c -T -S "MYSERVER\TESTSERVER"'
PRINT @cmd
EXEC master..xp_cmdshell@cmd

-- Delete and Reseed the database after performing the backup (task 2 & 3)
DELETE FROM Mydatabase.dbo.LTablename
DBCC CHECKIDENT('Mydatabase.dbo.Tablename', RESEED, 0)

The pitfall here is the same as above:

  1. 1. double quotes in the SQL statement and the filename, and the @cmd variable
  2. 2. If you forget to use the -S “MYSERVER/TESTSERVER” several errors might occur ( SQLState = 08001, NativeError = 2 )

 

Finally we can automate this as a daily task, by inserting the Query into a SQL Job.