BCP results
-
Ed Wagner (7/28/2014)
I'm lucky that we're still running 2008. 😛 Thanks for sharing the question.+1
For me, the file got created ONLY when I tried creating the table (not temp table)
and used the db_name suffix
file contained 7 rows
OTHER WISE IT THREW AN ERROR "INVALID OBJECT NAME"
-
I realized that #Employee would not be available in the context of the bcp command, but had to do some digging to predict whether that would result in an empty file or no file.
I guess I was lucky that the first link I found was apparently for an older version. 🙂
I then tried running it and found that I had to make a lot of code changes to even get it to work. I had to fix the inconsistent upper- and lower-case use (not everyone uses case insensitive collations, you know!), and then had to add the -S option to bcp (not everyone is running a default instance, you know!).
And then I also had to create a folder Exports in the C: root before I could finally run the script.
It's a bit sad that the author explicitly called out one assumption (xp_cmdshell enabled), but failed to mention all the above.
Missing the behavior change is obviously another issue with the question, so I will not pound on that.
However, to conclude on a positive note - I am happy that this question and the discussion here have made me aware of how bcp works (and has changed its behavior) when a query fails.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Roland C (7/27/2014)
I am sorry, but I don't agree 🙂I executed the script in SSMS (SQL Server 2012). It returned "10 row(s) affected", the csv file was created, but was empty. If there is any explanation...
The "10 row(s) affected" is from the INSERT INTO statement. (I have SSMS set to default to using SET NOCOUNT ON, so I did not get that message, but I would get it if I change SET NOCOUNT back to OFF).
The empty file has already been explained in the rest of the discussion.
Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/ -
Thank you for post, really nice one.
I got it right for this reason, but I am not sure or I have not yet checked in the SQL if this is the way it is.
From my experience files and folder creation APIs I used in the windows app, I have and always known that
- creation of the file with just a mention of a drive-letter - TRUE
- creation of the folder with the drive letter mention - TRUE
- creation of the folder and file is the same folder, if the folder does not exist - FALSE
- creation of the file in the existing folder - TRUE
As there was no "Exports" folder in the C drive - I assumed that (based on the 3rd point above) it might/will fail and among the choices available 3rd seemed appropriate.
I have read the other comments here and few claim/say that 3rd point (above) is not true. This is where I need to focus in depth. And I executed the script to check, folder and file were NOT created and I changed the path in the script to one of the existing folder then it created with 0 rows (and this is other side of the story temp #table and global temp##table or permanent table), using global temp it works properly.
This is the output sequence from when I changed the folder path to existing folder
NULL
Starting copy...
NULL
7 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total : 483 Average : (14.49 rows per sec.)
NULL
And it fails, if the same path is used as mentioned in the script as the "Exports" directory may or may not exist. This is the output sequence from when I used the folder path as given in the script.
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 11.0]Unable to open BCP host data-file
NULL
Just wanted to check... did the "Exports" folder was really created? and then the file inside or you changed the path to suit your environment.?
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Hugo Kornelis (7/28/2014)
... And then I also had to create a folder Exports in the C: root before I could finally run the script.Thanks, Hugo, this explains everything
Hugo Kornelis (7/28/2014)
However, to conclude on a positive note - I am happy that this question and the discussion here have made me aware of how bcp works (and has changed its behavior) when a query fails.+1 (followed by infinite zeros)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart. -
Hugo Kornelis (7/28/2014)
The "10 row(s) affected" is from the INSERT INTO statement. (I have SSMS set to default to using SET NOCOUNT ON, so I did not get that message, but I would get it if I change SET NOCOUNT back to OFF).The empty file has already been explained in the rest of the discussion.
Thank you, Hugo, for the answer. I did not realize, as it was the last message (I did not got any error message) that it referred at the first lines of the script !
-
m mcdonald (7/28/2014)
Sean Pearce (7/28/2014)
Your question is version specific. From SQL Server 2012 the file will be created.Thanks Sean for providing a much better explanation
Using 2012, I had empty file
+1
+1 Very good question and discussion. Cleared up some of my confusion. Thanks.
Everything is awesome! -
Hugo Kornelis (7/28/2014)
I realized that #Employee would not be available in the context of the bcp command, but had to do some digging to predict whether that would result in an empty file or no file.I guess I was lucky that the first link I found was apparently for an older version. 🙂
I then tried running it and found that I had to make a lot of code changes to even get it to work. I had to fix the inconsistent upper- and lower-case use (not everyone uses case insensitive collations, you know!), and then had to add the -S option to bcp (not everyone is running a default instance, you know!).
And then I also had to create a folder Exports in the C: root before I could finally run the script.
It's a bit sad that the author explicitly called out one assumption (xp_cmdshell enabled), but failed to mention all the above.
Missing the behavior change is obviously another issue with the question, so I will not pound on that.
However, to conclude on a positive note - I am happy that this question and the discussion here have made me aware of how bcp works (and has changed its behavior) when a query fails.
Thanks, Hugo, I agree with you. Regardless of the disagreement with the question design, this is a great question to prompt review of how bcp works.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
Nice question. I didn't know about the different behaviour between versions.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP -
If anyone is wondering, you can use a global temporary table
##Employee
to get a file with the 7 records.
I have used this as a quick way to dump the results of a query to a file.
Regards,
Phil
-
Hugo Kornelis (7/28/2014)
...I then tried running it and found that I had to make a lot of code changes to even get it to work. I had to fix the inconsistent upper- and lower-case use (not everyone uses case insensitive collations, you know!), and then had to add the -S option to bcp (not everyone is running a default instance, you know!).And then I also had to create a folder Exports in the C: root before I could finally run the script.
It's a bit sad that the author explicitly called out one assumption (xp_cmdshell enabled), but failed to mention all the above.
Thanks for saying it so I could simply copy and paste...I too ran into the -S and missing folder issues.
Yes, a silver lining, learning about a difference in bcp behavior from version to version. It surely is a pity that xp_cmdshell had to be a part of the learning process, and yet with no mention that xp_cmdshell is disabled by default in the product due to pervasive security concerns, the question and the presumed path many took in trying to run the code could only promote the idea that this technique is a safe and secure way to get data out of SQL Server, which it surely is not. Leave xp_cmdshell alone and pickup SSIS or invoke bcp.exe from an application server's command-line instead of from within a T-SQL process.
Thanks for posting the question.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Koen Verbeeck (7/29/2014)
Nice question. I didn't know about the different behaviour between versions.+1 Good to know.
-
Ed Wagner (7/28/2014)
I'm lucky that we're still running 2008. 😛 Thanks for sharing the question.Really that helped. Overall a good question. 🙂
-
I executed the script on SQL Server 2008. It returned "10 row(s) affected", the csv file created but no records in the file
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply