Re: Bcp and temp tables

From: Aaron [SQL Server MVP] (ten.xoc_at_dnartreb.noraa)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 11:59:24 -0400

I get a different error now.

SQLState = S1010, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Function sequence error

Here is the proc:

USE pubs
GO

CREATE PROCEDURE dbo.Kristoffer
AS
BEGIN
 SET NOCOUNT ON
 CREATE TABLE ##A
 (
  id INT
 )
 INSERT ##A SELECT 1
 SELECT * FROM ##A
END
GO

Now from a command line:

bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout
c:\K.txt -SLOCALHOST -T -c

The file was created, but it is empty, and I get the error mentioned above.

To verify that it was actually finding the proc, I used SET FMTONLY ON and
it yielded the same "Invalid object name '##A'" error. Tried with and
without SET NOCOUNT ON with the same results. 

-- 
http://www.aspfaq.com/
(Reverse address to reply.)
"Tibor Karaszi" <tibor_please.no.email_karaszi@hotmail.nomail.com> wrote in
message news:OA1#LRolEHA.2968@TK2MSFTNGP14.phx.gbl...
> Try:
>
> bcp "SET FMTOLNY OFF exec MyDatabase.._AAA" queryout
aaa.txt -SMyServer -T -c
>
> Note that your procedure will now be executed three times. Normally, using
queryout, only two
> executions are performed. Use Profiler to see what happens.
>
>
> -- 
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>
> "Kristoffer Persson" <hidden> wrote in message
news:OBEl%235nlEHA.1656@TK2MSFTNGP09.phx.gbl...
> > Can someone please tell me what's wrong?
> >
> > I get the error "Invalid object name '##AAA" when using BCP like this
> >
> > bcp "exec MyDatabase.._AAA" queryout aaa.txt -SMyServer -T -c
> >
> > where _AAA is a stored procedure that looks like this (in MyDatabase)
> >
> > CREATE PROCEDURE dbo._AAA
> > AS
> > BEGIN
> > if object_id (N'tempdb..##AAA') is not null
> >  drop table ##AAA
> >
> > create table ##AAA
> > (
> >  a int NOT NULL,
> >  b char(4)  NOT NULL,
> > )
> >
> > insert ##AAA
> > select
> >  a = 1000,
> >  b = '2000'
> >
> > select *
> > from ##AAA
> > END
> > GO
> >
> > I'm lost...
> >
> > - Kristoffer -
> >
> >
>
>

Relevant Pages

  • Re: Bcp and temp tables
    ... > Here is the proc: ... > SET NOCOUNT ON ... > id INT ... > bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout ...
    (microsoft.public.sqlserver.programming)
  • Re: Item Cannot be Found in Collection
    ... The "SET NOCOUNT ON" suggestion is not relevant for Access databases. ... setting that can only be performed in SQL Server. ... > I tried Set NOCOUNT = ON and I got a syntax error. ...
    (microsoft.public.scripting.vbscript)
  • Re: Another Concurrency Issue!!!!
    ... > allocating in blocks, using tables with identities, etc. ... > Columnist, SQL Server Professional ... I have a proc which returns a value after some DML operations on ... >> TIA ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL 2005
    ... The SQL Server implementation is not just Stored Procs, ... it works very tightly in proc with the SQL Server Engine(most ... Now with all that I have said T-SQL is not going away and is still a needed ... >> the CLR is now supported in stored procedures and triggers. ...
    (borland.public.delphi.non-technical)
  • Re: Performance Problem Using ADO and Stored Procs
    ... >I have a stored proc that executes in < 15 seconds through Query Analyzer. ... If I execute this proc ... > of CPU activity on the server hosting SQL server for the SQL server ... > Private Function RunProc(vntDB As Variant, strProcName As String, ...
    (microsoft.public.data.ado)