none
BCP Invalid object name '#A'

    Question

  • Not sure what's going on here...

    I've been building a number of stored procedures to extract data into CSV files formatted for our fundraising database to import.  These stored procedures are triggered by a BCP command that's scheduled with a SQL job.

    Previously, this has worked fine with earlier stored procedures but I've hit a problem with the BCP command for the latest job I'm working on.

    The new stored procedures work fine when executed from SSMS.  However, when I execute the same stored procedures via BCP I get the following error:

    SQLState = S0002, NativeError = 208
    Error = [Microsoft][SQL Server Native Client 10.0][SQL Server]Invalid object name '#A'.

    The command syntax I am using is the same as I've used successfully before and is as follows:

    bcp "exec SERVER.DATABASE.dbo.STOREDPROC" queryout C:\EXTRACT\FILENAME.csv -c -T

    I have no idea why this isn't working and equally no idea where the Invalid object name '#A' came from in the error.  I've seen the invalid object name before but that's always named the object in question.

    I've Google'd for the error but cannot find anything that fits the problem I'm getting.

    I'm hoping it's something straightforward...

    Any help much appreciated!

    Thanks

    Michael

    Tuesday, March 05, 2013 5:37 PM

Answers

  • Micheal,

    I'm not sure exactly what you're doing, but when dealing with temporary table (hence the #), they can only be accessed from the SESSION that created them. If inside the stored procedure, something is called ro done that would create a temp table in any other session, this would fail as the object doesn't exist in that session's scope.

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by oduk.Michael Wednesday, March 06, 2013 12:24 PM
    Tuesday, March 05, 2013 7:15 PM
  • This happens because BCP needs to figure out beforehand what columns your procedure will return. To this end, BCP in SQL 2008 runs the procedure preceded by the command SET FMTONLY ON. In this mode, statements are not executed but SQL Server only returns metadata. Since statements are not executed the temp table is not created, and whence the failure.

    SET FMTONLY ON is an awful hack, and SQL 2012 has a new method ot finding metadata. However, that method as well wil fail on a temp table.

    If performance permits, replace the temp table with a table variable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by oduk.Michael Wednesday, March 06, 2013 12:26 PM
    Tuesday, March 05, 2013 11:14 PM
  • Hi Sean,

    Thanks for that, you've pointed me on the right track.

    I'd thought I'd used BCP in the past with temp tables but having checked back in older code this is the only one that uses temp tables.  Stupidly, I've given them cryptic short names like "#A" and in doing so completely forgot that I actually named them that!

    Note to self - give temp tables better names in future...

    I've switched the code to use table variables instead and they work just fine with BCP.

    Thanks again,

    Michael

    Wednesday, March 06, 2013 12:23 PM

All replies

  • Try it with syntax from the following thread (don't use 4-part naming, use the -S parameter):

    bcp "select * from dbname.dbo.tablename" queryout "D:\Temp\SampleFile.txt" -T -c -S "Server"

    http://social.msdn.microsoft.com/Forums/br/transactsql/thread/0ca4153f-2a16-4bca-8458-874c1bf7e06d


    Kalman Toth Database & OLAP Architect
    Paperback / Kindle: Windows Azure SQL Database Programming & Design



    Tuesday, March 05, 2013 6:41 PM
  • Micheal,

    I'm not sure exactly what you're doing, but when dealing with temporary table (hence the #), they can only be accessed from the SESSION that created them. If inside the stored procedure, something is called ro done that would create a temp table in any other session, this would fail as the object doesn't exist in that session's scope.

    -Sean


    Sean Gallardy | Blog | Twitter

    • Marked as answer by oduk.Michael Wednesday, March 06, 2013 12:24 PM
    Tuesday, March 05, 2013 7:15 PM
  • What do you get if you go to a command prompt and run:

    sqlcmd -S {servername} -Q "exec DATABASE.dbo.STOREDPROC"

    Tuesday, March 05, 2013 9:33 PM
  • This happens because BCP needs to figure out beforehand what columns your procedure will return. To this end, BCP in SQL 2008 runs the procedure preceded by the command SET FMTONLY ON. In this mode, statements are not executed but SQL Server only returns metadata. Since statements are not executed the temp table is not created, and whence the failure.

    SET FMTONLY ON is an awful hack, and SQL 2012 has a new method ot finding metadata. However, that method as well wil fail on a temp table.

    If performance permits, replace the temp table with a table variable.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by oduk.Michael Wednesday, March 06, 2013 12:26 PM
    Tuesday, March 05, 2013 11:14 PM
  • Hi Sean,

    Thanks for that, you've pointed me on the right track.

    I'd thought I'd used BCP in the past with temp tables but having checked back in older code this is the only one that uses temp tables.  Stupidly, I've given them cryptic short names like "#A" and in doing so completely forgot that I actually named them that!

    Note to self - give temp tables better names in future...

    I've switched the code to use table variables instead and they work just fine with BCP.

    Thanks again,

    Michael

    Wednesday, March 06, 2013 12:23 PM