Re: Bcp and temp tables

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/09/04 

Date: Thu, 9 Sep 2004 18:11:20 +0200

I got a different error (##A already exists). Changing it to a local temptable worked on my machine,
though...

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

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

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Aaron [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message 
news:u1bqjYolEHA.2764@TK2MSFTNGP11.phx.gbl...
>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: ... bcp "SET FMTONLY OFF EXEC pubs.dbo.Kristoffer" queryout ... without SET NOCOUNT ON with the same results. ... > Tibor Karaszi, SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Simple Stored Procedure funktioniert mal und funktioniert mal nich
    ... Deine PROC gibt keinen RETURN-Value zurück sondern ... > Ein Return-Value in einer SP ist immer vom Datentyp int ... > Eine Return-Variable sollte in einer SP immer deklariert sein. ... > SET NOCOUNT OFF ...
    (microsoft.public.de.sqlserver)
  • Re: Simple Stored Procedure funktioniert mal und funktioniert mal nich
    ... Ein Return-Value in einer SP ist immer vom Datentyp int ... Eine Return-Variable sollte in einer SP immer deklariert sein. ... CREATE PROC dbo.myProc ... SET NOCOUNT OFF ...
    (microsoft.public.de.sqlserver)
  • Re: Stop DTS execution
    ... Include SET NOCOUNT ON as the first statement in your proc: ... CREATE PROC StorProc ... >>SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Help - stored procedure not returning a recordset
    ... > Remember to SET NOCOUNT OFF at the end of the proc too ... The setting only lasts for the scope of the proc (or the proc that ... I always SET NOCOUNT ON and out of n stored procedures I have, ...
    (microsoft.public.inetserver.asp.db)