CODEWITHSUNDEEP
sqlsql-serverxmlbcp
Nirav Joshi
Nirav Joshi

Reputation: 79

SQL Server 2005 BCP xml data error invalid object name temp table

I am using SQL Server 2005 SP4. I have a stored procedure which gets data from many tables and converts it into XML and then send this XML data to output variable of the stored procedure.

Now I want to load this XML data to file in the filesystem with specific location; for this I am using the BCP utility with the following command:

BCP "exec master.[dbo].[SPFileCreationInstanceOnlyXML]" QUERYOUT "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\PerformanceAnalyticsXML\LEOSQL2K5Apr112014128PM.xml" -c -t -T -SLEO\SQL2K5

Now this command gives me an error message when I am trying to execute from command prompt.

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

I am unable to figure it out why it is reference to the temporary table. While my code for the stored procedure which I am executing from BCP is as following

ALTER procedure [dbo].[SPFileCreationInstanceOnlyXML]
as 
begin
    DECLARE @Result XML
    exec [dbo].[BCPTableLineXML] @Result OUT

    SELECT @Result AS FinalOutput
end

The most interesting thing about this I have same stored procedures on SQL Server 2008 R2 which works like a charm in other server only this one with SQL Server 2005 is giving me this error message.

The code for BCPTabelineXML is as following the code inside BCPTableXML is as following.

ALTER  procedure [dbo].[BCPPerformanceBaseLineXML]
@ALLRet xml OUTPUT

as 
begin
DECLARE @ALL xml

--SQL Server Version and Server Name
DECLARE @SQLTraceFlag NVARCHAR(MAX)

create table ##traceinfo(flag varchar(20),Status varchar(10),Global varchar(10),Session varchar(10))

INSERT INTO ##traceinfo EXECUTE ('DBCC TRACESTATUS(-1)')

--select * from ##traceinfo

SET @SQLTraceFlag =
(
select * from
(
select * from ##traceinfo
) as SQLTraceFlag
for xml auto

);
--select @SQLTraceFlag
drop table ##traceinfo
set  @ALL = CONVERT(XML
ISNULL(@SQLTraceFlag,'')) 


set @ALLRet=@ALL;

end

Thanks in advance for your help and valuable time.

Cheers, Nic

Upvotes: 1

Views: 847

Answers (1)

Nirav Joshi
Nirav Joshi

Reputation: 79

Problem is solved using table variable instead of Temporary table. –

Upvotes: 1

Related Questions