Re: Variable server names

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Rick (rick0726@xxxxxxxxxxx) writes:
THANK YOU. I can't use a regular '#' temp table because the proc is
being run by BCP in order to send its output to a text file, and BCP
can't see '#' temp tables.

Well, you can use INSERT-EXEC to get data to the temp table, and
then copy the data to a table variable. Or, hum, maybe not, because BCP
is likely to get an error on the temp table.

Then again, this plainly bizarre kludge may work for you:

DECLARE @fmtonlyon bit
SELECT @fmtonlyon = 0
IF 1 = 0 SELECT @fmtonlyon = 1
SET FMTONLY OFF
CREATE TABLE #temp (...)
IF @fmtonlyon = 1 SET FMTONLY ON

And then you use the temp table in the INSERT-EXEC, still BCP will be
happy.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • Re: Variable server names
    ... a table variable and a temp table. ... Works fine on its own but BCP just doesn't like seeing a # ... Links for SQL Server Books Online: ...
    (microsoft.public.sqlserver.programming)
  • Re: Variable server names
    ... the processing on the temp table, then did the INSERT-EXEC into the ... being run by BCP in order to send its output to a text file, ...     SET FMTONLY OFF ... SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx ...
    (microsoft.public.sqlserver.programming)
  • Re: How to Create Local Temporary Table
    ... As Daniel pointed out, many times, it is locking that forces one to look at temp tables and Oracle does not share this problem. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... If it places the group's minds at ease DB2 supports neither VARRAY nor local temps. ...
    (comp.databases.oracle.server)
  • Re: Indented Bill of Materials
    ... but eventually I will be calling SQL ... > have no parent are inserted in a temp table, ... > execution of the WHILE statement, the last executed statement is the SET ... > In the first iteration, @lev will be increased to 1. ...
    (microsoft.public.sqlserver.programming)
  • Re: help: Timeout expired. The timeout period elapsed prior to completion of the operation or the se
    ... Actually we are putting records in a temp table which qualify according ... code fails very much even before transaction starts. ... unless really needed pls dont use lock hints as SQL ...
    (microsoft.public.sqlserver.server)