August 12, 2013 at 3:31 pm
Comments posted to this topic are about the item Create a Comma Delimited CSV File from Any Table/View with Debugging
August 25, 2013 at 3:04 pm
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
August 27, 2013 at 6:40 am
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
November 25, 2013 at 10:12 am
Hi
Just submitted an updated version of the script to address to identity issue.
Thanks
December 3, 2013 at 5:52 am
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...
December 3, 2013 at 7:24 am
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
December 3, 2013 at 7:34 am
How would you modify your code to allow for a Where Clause to be passed to the Stored Procedure?
December 3, 2013 at 11:53 am
Hi Sector7, yes, I've made a few assumptions about configuration and access rights with this and haven't coded for every possible error.
December 3, 2013 at 11:55 am
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
December 5, 2013 at 1:03 am
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".
May 4, 2015 at 8:31 am
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?
May 4, 2015 at 2:23 pm
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.
May 4, 2015 at 2:31 pm
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.
May 5, 2015 at 7:20 am
Thanks for the script. I'll give it a try.
May 6, 2015 at 6:22 am
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
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply