Is this Possible?
-
Are you able to export data from a temp table using bcp? For example I have a table called #Customers and I wish to bcp the data into a text file. This is all happening in a Stored Procedure. I have my code below but I keep getting an error of;
SQLState = 01000, NativeError = 2701
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'tempdb' ignored, referencing object in tempdb.
SQLState = 01000, NativeError = 2701
Warning = [Microsoft][ODBC SQL Server Driver][SQL Server]Database name 'tempdb' ignored, referencing object in tempdb.
SQLState = S0002, NativeError = 208
Error = [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name '#Customers'.
Below is my code.
SELECT CustomerID, CompanyName
INTO #Customers
FROM Customers
DECLARE @BCP VARCHAR(400), @FileName VARCHAR(200), @Table VARCHAR(200)
SET @FileName = 'C:\Data\Customers.txt'
SET @Table = 'tempdb' + '.' + 'dbo' + '.' + '#Customers'
SET @BCP = 'bcp "' + @Table + '" out "' + @FileName + '" -q -c -T'
PRINT @BCP
EXEC Master.dbo.xp_cmdshell @BCP
-
Have you tried this without referencing tempdb?
Good Hunting!AJ Ahrens
webmaster@kritter.net -
1. You cannaot refer to a temptable in tempdb by prefixing tempdb.dbo. (If you check tempdb sysobjects you will see #table name will be different like #tablename_________12121)
2. To get fixed length file as data file use format files. Make field terminator blank(4th column).
8.0
4
1 SQLCHAR 0 32 "" 1 MyID SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 32 "" 2 myModel SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 24 "" 3 myDate SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 1 "\r\n" 4 MyFlag SQL_Latin1_General_CP1_CI_AS
Regards,
gova -
I think the main problem here is the approach. Just create a view and use it to export the data with bcp
Just my $0.02
* Noel -
from SQL 2000 BOL - CREATE TABLE:
Temporary Tables
You can create local and global temporary tables. Local temporary tables are visible only in the current session; global temporary tables are visible to all sessions.
Prefix local temporary table names with single number sign (#table_name), and prefix global temporary table names with a double number sign (##table_name).
BCP is going to create a new connection, and your temporary table is not visible to it. If you create a global temp table using the double number sign (##table_name) you can then access the table if you BCP uses the same credentials you are.
A sure fire way to make sure that you can bcp the table out is create an actual table in temp db (SELECT CustomerID, CompanyName
INTO tempdb.dbo.Customers
FROM Customers) then just add a DROP TABLE tempdb.dbo.Customers command after you BCP.
Or
you can have stored proc 1 call bcp, with the command to run the select statement directly (or another sproc containing the select statement). Then you wont need temp tables at all!Julian Kuiters
juliankuiters.id.au -
Why use a temp table or view? Just use the bcp query out like this
bcp.exe "select CustomerID, CompanyName From master..Customers" queryout C:\Data\Customers.txt -S %isqlserver% -U %isqluser% -P %password% -c >>C:\DATA\LOG\customet_bcp.log
Place this within a *.bat file.
JerseyMo--
-
a temptable is valid only for the session it is created. I menas that in separte session you can refrence under the same name the temptables.
If you absolutly want a temptable to be exported, then you can use a global temporary table (like ##TempTable) this is readable by everybody
Bye
Gabor
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply