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

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

  • 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

  • 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

  • Hi

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

    Thanks

  • 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...

  • 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

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

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

  • 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

  • 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".

  • 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?

  • 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.

  • 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.

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

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

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