Re: bcp queryout with stored proc
Tibor Karaszi
The problem is that BCP tries to figure out the column structure before actually executing the SQL
code. Do a profiler trace and you'll see that it first executes the proc like:
SET FMTONLY ON EXEC pubs..p3 SET FMTONLY OFF
And for above, nothing is actually executed = no temp table created.
One thing you can do, is to fool BCP adding SET FMTONLY OFF, as in below (from my BAT file):
BCP "SET FMTONLY OFF EXEC pubs..p3" queryout authors.txt /c /T
Note that for above, your code is actually executed twice! Run Profiler and you will see.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Clint Colefax" <ClintC...@discussions.microsoft.com> wrote in message
news:DE648155-E37D-415D...@microsoft.com...
> I am trying to bcp out the resultset from a stored procedure to a file. This
> sp is a report which builds a result set into a temporary table and then
> returns the contents of this table as the result set of the sp.
> When I specify this sp as the source for bcp, i get an error stating that
> the temp table doesn't exist.
> From this I assume that temp tables cannot be used with bcp even though this
> temp table is in an sp. Is this correct.
> Does anyone know any work around?
>
> Thank you
> Clint Colefax
alekas
twice (withing the same transaction). I think in your SP you assuming that
BCP is calling you just once - so you are creating temp table and MOVING data
into it (and if there is no data available you just not creating/populating
that temp table at all).
Basically, you need to be able to distinguish the first call from BCP from
the second one. In first call you can just return an empty recordset (but
with actual field names/types) - BCP needs this information to be able to
verify the fields against format file (stupid, but that's how it is). In the
second BCP call you need to actually return your report's recordset.
If you unable to find a solution on how to distinguish first BCP call from
the second one - let me know - I know how to do it.
Alex
Tibor Karaszi
> the second one - let me know - I know how to do it.
That would be extremely difficult as BCP does a SET FMTONLY command before calling the proc the first time.
This mean that nothing is actually executed, SQL Server only return the result set as it would be executed.
Since nothing is executed, the temp table isn't created, SQL Server cannot know how that SELECT against the
non-existing temp table would look like. And you can't distinguish anything, as your code isn't executed. See
my other post on how you can handle this.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alekas" <ale...@discussions.microsoft.com> wrote in message
news:759DA088-AA28-4D4B...@microsoft.com...
alekas
Unfortunately you are not right. Lets say - you are calling the stored
procedure which is returning a recordset back to BCP. Lets say this stored
procedure updates some table in order to return report (recordset) only once
(so the second call to this SP will return an empty recordset). Check to see
if your SP works in SQL query analyzer (execute SP once - get the recordset;
execute it second time - get an empty recordset). Now update that table as it
was before your first call and try to call such SP from BCP - you will
receive an empty recordset. Try it yourself - you will be surprised.
If you want (or you don't believe me) - I can give you an example tomorrow
(I'm home now - I don't have SQL server installation here).
Regards,
Alex
Alex
Tibor Karaszi
I did a trace using profiler, and here what is happening:
BCP execute the procedure *three* times. First using SET FMTONLY ON. This is probably where it goes
wrong if the procedure creates a temptable.
Then two regular executions. Two! Go figure. :-)
So, if you want to handle the problem with procedure creating temptables, you need to SET FMTONLY
OFF when calling the procedure from BCP. This will, however, mean that the procedure is actually
executed three times. Here's the procedure etc I used to test this:
USE tempdb
GO
DROP TABLE t
GO
CREATE TABLE t (C1 int identity)
GO
DROP PROC p
GO
CREATE PROC p AS
INSERT t DEFAULT VALUES
SELECT * FROM t
And here's my bat file (try both with and without FMTONLY OFF):
bcp "SET FMTONLY OFF EXEC tempdb..p" queryout a.txt /T /c
type a.txt
pause
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"alekas" <ale...@discussions.microsoft.com> wrote in message
news:5910F79A-8A16-4B8B...@microsoft.com...
alekas
And then a question: why to execute SP with SET FMTONLY ON for the first
time? My guess is - to avoid actual execution (which can take a long time)
for field/format file validation. Then why to execute it 2 more times???
I think it should be executed only once - the first record returned must me
validated against format file and then BCP should just rollback transaction
if format file is invalid with the returned recordset...
Well, as usually with Microsoft - good idea and bad implementation (at least
from the developers point of view, not from the managers, who bought such a
"fast and reliable - much faster/better than any other DB" product :)
Alex
sam
try this at the end of your stored procedure, it works for me,
-- write out the table to the text file
exec master..xp_cmdshell 'bcp "SELECT * FROM DatabaseName.dbo.MyTable"
queryout FileName.txt -c -T -S(Local)'
Hope that helps
:)