Create a Comma Delimited CSV File from Any Table/View with Debugging

Question

Post reply

Create a Comma Delimited CSV File from Any Table/View with Debugging

mrpaulandrew

Mr or Mrs. 500

Points: 552
More actions
August 12, 2013 at 3:31 pm

#281840

Comments posted to this topic are about the item Create a Comma Delimited CSV File from Any Table/View with Debugging

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

cranem451 SSC Rookie Points: 36 More actions · Answer 1

SQL Server 2012

using Database "AdventureWorks2012"

Table "DatabaseLog"

This is from the Message screen in SSMS:

Add column names to temp table as data:

INSERT INTO ##DatabaseLog

( [DatabaseLogID],[PostTime],[DatabaseUser],

[Event],[Schema],[Object],[TSQL],[XmlEvent],[Order] )

VALUES

( 'DatabaseLogID','PostTime','DatabaseUser','Event',

'Schema','Object','TSQL','XmlEvent', '1' )

------------------------------------------

Msg 544, Level 16, State 1, Line 2

Cannot insert explicit value for identity column in table

'##DatabaseLog' when IDENTITY_INSERT is set to OFF.

Column "[DatabaseLogID]" is type INT

Identity Specification is "YES"

If I add:

SET IDENTITY_INSERT dbo.DatabaseLog ON

Just above

EXEC(@SQL)

Which is approx. 5 lines below

PRINT 'Add column names to temp table as data:'

I get the message below:

------------------------------------------

Msg 544, Level 16, State 1, Line 2

Cannot insert explicit value for identity column in table '##DatabaseLog' when IDENTITY_INSERT is set to OFF.

A Table with a Column that contains an Identity Specification will NOT work with this Procedure.

The code that assembles the Fields needs to check if the Field is an "identity column" and use NULL as the value.

Marc Crane

cranem@addendum.com

mrpaulandrew Mr or Mrs. 500 Points: 552 More actions · Answer 2

Good point! Generally I've been dumping out views of data where identity values hadn't been included so didn't spot this issue.

Thanks for the feedback

Paul

mrpaulandrew Mr or Mrs. 500 Points: 552 More actions · Answer 3

Hi

Just submitted an updated version of the script to address to identity issue.

Thanks

prakashr.r7 SSCrazy Points: 2828 More actions · Answer 4

Hi Paul,

This is really great. I could use this procedure. It would save me lot of time. Thank you for your wonder full work...

Sector7G Ten Centuries Points: 1040 More actions · Answer 5

I tried this, and got an error saying I had to enable xp_cmdshell. So I Googled, and found this code to enable it:

EXEC sp_configure 'show advanced options', 1

GO

-- To update the currently configured value for advanced options.

RECONFIGURE

GO

-- To enable the feature.

EXEC sp_configure 'xp_cmdshell', 1

GO

-- To update the currently configured value for this feature.

RECONFIGURE

GO

Now when I run it, I receive these messages telling me the file was created.

Your file has been created:
C:\Users\STHOMAS\Documents\MSL_03122013091747.csv

But when I look in the folder, the file is not there. Is anyone else getting a message that indicates the file was created, but don't see the actual file?

Additional note: I ran it again with debugging turned on. It still says the file was created, but it also shows this additional info:

SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]Named Pipes Provider: Could not open a connection to SQL Server [2].
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Server Native Client 10.0]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
SQLState = S1T00, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Login timeout expired

mike.johnson-1129283 SSC Rookie Points: 25 More actions · Answer 6

How would you modify your code to allow for a Where Clause to be passed to the Stored Procedure?

mrpaulandrew Mr or Mrs. 500 Points: 552 More actions · Answer 7

Hi Sector7, yes, I've made a few assumptions about configuration and access rights with this and haven't coded for every possible error.

mrpaulandrew Mr or Mrs. 500 Points: 552 More actions · Answer 8

Hi mike.johnson, I suggest you create a view for your query with whatever WHERE clause is required then execute the stored procedure calling the view. Thanks

cranem451 SSC Rookie Points: 36 More actions · Answer 9

To Grasshopper......

I had a trailing backslash in the file path so I added the code below:

/* Remove the last character if it is a "back slash"

the trailing "back slash" will be concatenated later*/

IF RIGHT(@DestinationLocation,1) = '\'

BEGIN

SET @DestinationLocation = LEFT(@DestinationLocation,(LEN(@DestinationLocation)-1))

END

/* I added the code above just before the section below*/

/*------------------------------------------------------------

Stage 1: Create temp table for export data.

-------------------------------------------------------------*/

/* I added the code above just before the section above*/

I also had to add

"-S WIN7X64_2008RG\MSSQLSERVER1"

to get it to actually create the file.

I also had to specify @TextQualifier = '"'

( thats ' " ' ) a single quote, a double quote, a single quote

to actually get data in the file.

--Example of the Server added to the command string...

SET @Cmd = 'bcp "SELECT ' + @FileFields + ' FROM ##' + @SourceObjectName

+ ' ORDER BY [Order]" queryout "' + @DestinationLocation + '\' + @FileName

+ '" -S WIN7X64_2008RG\MSSQLSERVER1 -T -c -t,'

Replace "WIN7X64_2008RG\MSSQLSERVER1" with your "instance\sql server name".

KSplitt Newbie Points: 9 More actions · Answer 10

This looks great, thanks for writing the article. I do have a possibly (likely) stupid question, does this automatically escape breaking characters? Or do you need to do that in a view first?

Paul_clarke Valued Member Points: 51 More actions · Answer 11

We've had quite a few code posts on this theme of splitting out csv files etc. Having seen the huge increase in speed that can be obtained by the use of tally tables (I've had jobs which can split long xml strings some 30 times faster using tally tabkes). Why doesn't this code use a tally table to do the split? Several authors on this site have posted excellent examples of this method in use.

wordracr SSC Enthusiast Points: 176 More actions · Answer 12

I ran this on a sql2008 db (compat level 100), but it gives an error (below). Does the script need an update?

Exec [ExportObjectToCommaDelimitedCSV] 'dbo', 'my_table_name', 'C:\temp\tblexport.txt', null, 0

Msg 16950, Level 16, State 2, Procedure ExportObjectToCommaDelimitedCSV, Line 185

The variable '@Cursor' does not currently have a cursor allocated to it.

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 13

akljfhnlaflkj

SSC Guru

Points: 76202

May 5, 2015 at 7:20 am

#1795597

Thanks for the script. I'll give it a try.

Konstantin Taranov SSCertifiable Points: 6498 More actions · Answer 14

Hello.

For own needs I use similar script: https://github.com/ktaranov/sqlserver-kit/blob/master/Strored%20Procedure/usp_bcpTableUnload.sql

Comment Execute statements in catch block or install error log and print procedure:

https://github.com/ktaranov/sqlserver-kit/blob/master/Strored%20Procedure/usp_PrintError.sql

https://github.com/ktaranov/sqlserver-kit/blob/master/Strored%20Procedure/usp_LogError.sql