October 22, 2007 at 10:08 am
I am having problems getting results from a stored procedure into a textfile using bcp. The last statement in my stored procedure is
select *
from @results
@results is declared as a table variable. I can execute the procedure in query analyzer and I get the expected resultset:
exec database..spTest
However, if I run:
exec master..xp_cmdshell 'bcp "exec database..spTest" queryout c:\datafile.txt -c -SServerName -Usa'
I get:
Password:
NULL
Starting copy...
NULL
0 rows copied.
Network packet size (bytes): 4096
Can I do this using a table variable? Seems to work find if I change the last line in my stored procedure to select * from an actual table. Seems like I've done this before using a table variable, though. Right now I am just trying to get this to work locally; eventually I need to get this working by calling the stored procedure on a linked server and get the results in a textfile locally. Any help or suggestions are greatly appreciated.
Thanks,
Steve
October 22, 2007 at 4:05 pm
Is password missing in you bcp call or you just removed it befor posting?
bcp message shows you forgot to pass sa password to the Server.
_____________
Code for TallyGenerator
October 23, 2007 at 7:26 am
Thanks for the reply. Password for my local sa account is null. Connection works fine, in fact it creates a blank output file. If I change the last line in the stored procedure to select * from table (where table is an actual table and not a table variable then bcp works fine.)
I ended up changing the stored procedure to get it to work. Instead of populating a table variable with the results of the select, I just put the sql into a variable and made this the last line of the stored procedure:
exec sp_executesql @sql
I'm just confused as why selecting from the table variable did not bcp the result set, when a result set was clearly there.
Thanks again for you help.
- Steve
October 24, 2007 at 3:11 am
Dunno why it didn't work..
Though I agree that the output (with 'password' promt) looks suspicious.
Couldn't replicate it. Worked both on 2k and 2.5k, no problems at all.
The one difference perhaps, I used trusted connection (-T) instead of -Usa
/Kenneth
October 24, 2007 at 3:22 am
October 24, 2007 at 5:14 am
It's bcp thing.
I've got similar problem recently.
My reporting procedure created and populated table #Result.
Last statement was
SELECT * FROM #RESULT
Depending on parameters (mainly @ReportNo) table #Report had different columns.
Everything worked fine while we returned resultset to UI.
But when we tried to use it for bcp I faced strange problem: no matter which report was called only 2 columns were returned.
After long struggle I figured out that bcp builds recordset based on initial compilation.
Only objects existing or being referenced at the moment are considered.
SP with temp tables is being recompiled every time temp table is created or dropped.
But it does not affect bcp result.
I guess bcp takes most pessimistic case to exclude any chance of failure.
In my case there were no reports with less than 2 columns. That's why bcp reserved only 2 columns for the result.
During compilation your @Table does not exist. bcp cannot get any information about it before code is executed. But execution begins after compilation is completed - too late for bcp to change anything. In its understanding no data to be returned.
P.S. It's only my speculations.
Based on the actual behaviour I witnessed and some knowledge about SQL Server mechanics.
No guarantee it's exactly as I described.
But it's as much sense as I could make from it.
_____________
Code for TallyGenerator
October 24, 2007 at 7:35 am
Thanks everyone for all your help and suggestions. I'll need to do a bit of research on bcp to see if I can understand what is going on. Thanks again!
Steve
October 24, 2007 at 7:53 am
thanx for your feedbac. I guess, the link that I gave to you should be of great help related to the tasks whats are you looking for.
October 24, 2007 at 11:32 pm
I use procedure which finally selects from a table variable. This procedure is used in BCP command.
When i Execute the BCP command i get an error saying : BCP hostfile must atleast contain one column"
When i execute the procedure in SQL query analyser it gives me output. Also when the table variable is replaced by a permanent table BCP works. Problem is only with the table variable. Any idea why this error?
October 25, 2007 at 12:09 am
please find similar kind of problems faced and the answers to the same. Try to implement the same.
forums.microsoft.com/MSDN/ShowPost.aspx?PostID=535939&SiteID=1
support.microsoft.com/kb/940376
http://www.microsoft.com/technet/prodtechnol/sql/70/proddocs/diag/part3/75528c16.mspx
October 25, 2007 at 2:11 am
gayathri (10/24/2007)
I use procedure which finally selects from a table variable. This procedure is used in BCP command.When i Execute the BCP command i get an error saying : BCP hostfile must atleast contain one column"
When i execute the procedure in SQL query analyser it gives me output. Also when the table variable is replaced by a permanent table BCP works. Problem is only with the table variable. Any idea why this error?
Can you read?
Probably not very well.
Otherwise you would notice the answer right above your question.
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply