I'm trying to BCP out a temporary table (this is actually occuring in an
application and with the connection still open that "owns" the temporary
table, the application launches BCP.EXE to move the data to the file system).

The command line is of the form:
bcp "##temptablename...." out "c:\path\tempfilename.bin" -n -S server -q
-a 65535 -T

In the 8.0 version of the utility there is no problem, the BCP out operation
succeeds. But in the 9.0 version I get a series of errors:

SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user
'username'.
SQLState = IM006, NativeError = 0
Error = [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
SQLState = 42000, NativeError = 4060
Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database
"##temptablename..." requested by the login. The login failed.
SQLState = IM006, NativeError = 0
Error = [Microsoft][SQL Native Client]Packet size change not supported by
server, default used

That last error goes away if I take out the "-a 65535" command line option
(although the documentation shows this still as a valid option).

Any and all on this issue would be most appreciated!

-- TB

Hmmm... it seems that explicitly specifying "tempdb" makes BCP v9.0 work...

bcp "tempdb.##temptablename..." out "output file path" -n -S server -q -a
65535 -T

None of the other errors show up. Clarification on what is happening here
would be appreciated.

-- TB

"Thomas W. Brown" wrote:

> I'm trying to BCP out a temporary table (this is actually occuring in an
> application and with the connection still open that "owns" the temporary
> table, the application launches BCP.EXE to move the data to the file system).
>
> The command line is of the form:
> bcp "##temptablename...." out "c:\path\tempfilename.bin" -n -S server -q
> -a 65535 -T
>
> In the 8.0 version of the utility there is no problem, the BCP out operation
> succeeds. But in the 9.0 version I get a series of errors:
>
> SQLState = 28000, NativeError = 18456
> Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user
> 'username'.
> SQLState = IM006, NativeError = 0
> Error = [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
> SQLState = 42000, NativeError = 4060
> Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database
> "##temptablename..." requested by the login. The login failed.
> SQLState = IM006, NativeError = 0
> Error = [Microsoft][SQL Native Client]Packet size change not supported by
> server, default used
>
> That last error goes away if I take out the "-a 65535" command line option
> (although the documentation shows this still as a valid option).
>
> Any and all on this issue would be most appreciated!
>
> -- TB
>

"Thomas W. Brown" <thomas_w_brown@countrywide.NOSPAM.com> wrote in message
news:C431A804-70F2-4395-A176-299DEA86FACA@microsoft.com...
> Hmmm... it seems that explicitly specifying "tempdb" makes BCP v9.0
> work...
>
> bcp "tempdb.##temptablename..." out "output file path" -n -S server -q -a
> 65535 -T
>
> None of the other errors show up. Clarification on what is happening here
> would be appreciated.
>
> -- TB
>
> "Thomas W. Brown" wrote:
>
>> I'm trying to BCP out a temporary table (this is actually occuring in an
>> application and with the connection still open that "owns" the temporary
>> table, the application launches BCP.EXE to move the data to the file
>> system).
>>
>> The command line is of the form:
>> bcp "##temptablename...." out "c:\path\tempfilename.bin" -n -S
>> server -q
>> -a 65535 -T
>>
>> In the 8.0 version of the utility there is no problem, the BCP out
>> operation
>> succeeds. But in the 9.0 version I get a series of errors:
>>
>> SQLState = 28000, NativeError = 18456
>> Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user
>> 'username'.
>> SQLState = IM006, NativeError = 0
>> Error = [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
>> failed
>> SQLState = 42000, NativeError = 4060
>> Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database
>> "##temptablename..." requested by the login. The login failed.
>> SQLState = IM006, NativeError = 0
>> Error = [Microsoft][SQL Native Client]Packet size change not supported by
>> server, default used
>>
>> That last error goes away if I take out the "-a 65535" command line
>> option
>> (although the documentation shows this still as a valid option).
>>
>> Any and all on this issue would be most appreciated!
>>
>> -- TB

Hi

My tests indicate that the -q parameter causes this to fail. BOL indicates
that if you use quotes it is a query or under the -q information an entire
three part table or view name and should be used if the name has a space in
it. Being a global temporary table any database name should work if the user
has permissions and you don't have to specify the schema.

The following work:

bcp tempdb.##temptbl out "tempfilename.bin" -n -S (Local) -q -a 65535 -T

bcp "tempdb.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T

bcp "##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T

bcp ##temptbl out "tempfilename.bin" -n -S (Local) -a 65535 -T

bcp "tempdb.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T -q

bcp "master.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T -q

bcp "tempdb.dbo.##temptbl" out "tempfilename.bin" -n -S (Local) -a
65535 -T -q

bcp "tempdb.dbo.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T

bcp tempdb.dbo.##temptbl out "tempfilename.bin" -n -S (Local) -a 65535 -T

John

"John Bell" wrote:
>
> "Thomas W. Brown" <thomas_w_brown@countrywide.NOSPAM.com> wrote in message
> news:C431A804-70F2-4395-A176-299DEA86FACA@microsoft.com...
> > Hmmm... it seems that explicitly specifying "tempdb" makes BCP v9.0
> > work...
> >
> > bcp "tempdb.##temptablename..." out "output file path" -n -S server -q -a
> > 65535 -T
> >
> > None of the other errors show up. Clarification on what is happening here
> > would be appreciated.
> >
> > -- TB
> >
> > "Thomas W. Brown" wrote:
> >
> >> I'm trying to BCP out a temporary table (this is actually occuring in an
> >> application and with the connection still open that "owns" the temporary
> >> table, the application launches BCP.EXE to move the data to the file
> >> system).
> >>
> >> The command line is of the form:
> >> bcp "##temptablename...." out "c:\path\tempfilename.bin" -n -S
> >> server -q
> >> -a 65535 -T
> >>
> >> In the 8.0 version of the utility there is no problem, the BCP out
> >> operation
> >> succeeds. But in the 9.0 version I get a series of errors:
> >>
> >> SQLState = 28000, NativeError = 18456
> >> Error = [Microsoft][SQL Native Client][SQL Server]Login failed for user
> >> 'username'.
> >> SQLState = IM006, NativeError = 0
> >> Error = [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr
> >> failed
> >> SQLState = 42000, NativeError = 4060
> >> Error = [Microsoft][SQL Native Client][SQL Server]Cannot open database
> >> "##temptablename..." requested by the login. The login failed.
> >> SQLState = IM006, NativeError = 0
> >> Error = [Microsoft][SQL Native Client]Packet size change not supported by
> >> server, default used
> >>
> >> That last error goes away if I take out the "-a 65535" command line
> >> option
> >> (although the documentation shows this still as a valid option).
> >>
> >> Any and all on this issue would be most appreciated!
> >>
> >> -- TB
>
> Hi
>
> My tests indicate that the -q parameter causes this to fail. BOL indicates
> that if you use quotes it is a query or under the -q information an entire
> three part table or view name and should be used if the name has a space in
> it. Being a global temporary table any database name should work if the user
> has permissions and you don't have to specify the schema.
>
> The following work:
>
> bcp tempdb.##temptbl out "tempfilename.bin" -n -S (Local) -q -a 65535 -T
>
> bcp "tempdb.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T
>
> bcp "##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T
>
> bcp ##temptbl out "tempfilename.bin" -n -S (Local) -a 65535 -T
>
> bcp "tempdb.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T -q
>
> bcp "master.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T -q
>
> bcp "tempdb.dbo.##temptbl" out "tempfilename.bin" -n -S (Local) -a
> 65535 -T -q
>
> bcp "tempdb.dbo.##temptbl" out "tempfilename.bin" -n -S (Local) -a 65535 -T
>
> bcp tempdb.dbo.##temptbl out "tempfilename.bin" -n -S (Local) -a 65535 -T
>
> John
>

Excellent, that appears to be the problem..., somewhat of a regression in
BCP.EXE since this behavior was not present in v8.0. But I can code around
this.

Just FYI..., the reason the -q is there is because the BCP invocation is
being done programmatically and the name of the table is being supplied to my
routine. Since I don't know, a priori, whether the table name may need to be
quoted and, until now, I didn't want to add the complexity of scanning for
need-to-quote'ness of the table name so I put the -q there just in case.

The simplest solution, right now, seems to be to look for the leading '#'
prefix on the table name and, if present, prepend the "tempdb." specification
which allows BCP to work with -q as usual.

-- TB