Re: bcp error when queryout is stored procedure

BCP tries to get how the result set will look like in order to generate the file format correctly.
It uses SET FMTONLY ON for this. You can try adding SET FMTONLY OFF in the beginning of he "query
(proc)" you will execute so that the temp table will actually be created, but be aware that the proc
then will be executed twice!

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/


"vygandas" <vygandas@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:424A4746-2C0C-4D00-B529-82FE4856E371@xxxxxxxxxxxxxxxx
>I posted previous code just as an example, what I think is incorrect BCP
> behavior. Applications (including bcp) should not be aware how stored
> procedure produces result set.
>
> Real stored procedure is very large and it populates temporary table through
> its all execution. I will consider use of global or "permanent" tables to
> work around this, but it will not be trivial, because the stored procedure
> will be executed in multiple processes at same time.
>
> Does anybody know how I can submit bug fix (improvement) request to Microsoft?
>
> Thank you for your responses!
>
> Vygandas
> MCDBA, MCSD
>


.

Relevant Pages

  • Re: Auditing SP Execute.
    ... As long as the users only have execute permissions on the proc i.e. they ... This assumes that the app doesn't use a single service ... > the stored procedure from enterprise manager or query analyser (others ...
    (microsoft.public.sqlserver.security)
  • Re: BCP
    ... You sould execute BCP from a client, not from within a stored procedure. ... sometimes it creates the .csv file. ...
    (microsoft.public.sqlserver.programming)
  • Re: CURSOR problem
    ... each row in the result set of the proc. ... the proc into a temp table and then run the cursor against it. ... getAccountsData is a stored procedure. ... I cant execute ...
    (microsoft.public.sqlserver.programming)
  • output variable error
    ... I have a stored procedure with an output varcharvariable, being set to the value of a database field that is of type varchar. ... When I execute the query I get the following message: ... CREATE PROC sp_ptname ...
    (microsoft.public.sqlserver.programming)
  • BCP OUT
    ... I'm executing a web application that calls a stored procedure and ... inside the sp I need to execute a BCP OUT with the xp_cmdshell. ... Do anyone knows how can I avoid of making this security problem? ...
    (microsoft.public.sqlserver)