bcp queryout fails in sql 2005

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

bcp queryout fails in sql 2005

Author

Topic

harikris5
Starting Member

1 Post

Posted - 2007-10-24 : 06:39:54

bcp queryout fails in sql 2005 with the message

"SQLState = 42S02, NativeError = 208
Error = [Microsoft][SQL Native Client][SQL Server]Invalid object name '#temptrial'."

Let me go in detail here:
1. Create a Table And Insert Values
create table dbo.bcptesting
(
empname varchar(250),
employeeno int
)

insert into dbo.bcptesting
select 'aaaa',11111
union
select 'bbbb',22222
union
select 'cccc',33333

2. Create a stored proc to get some result

alter procedure hkbcpsql2005problem
as begin

create table #temptrial
(
empname varchar(250),
employeeno int
)
insert #temptrial
select * from dbo.bcptesting

select * from #temptrial
end

3. Now I want the output of sp in dat file so doing a bcp query out in sql 2005

bcp "exec <dbname>.dbo.hkbcpsql2005problem" queryout hktest.dat -S<Servername> -T -c

I get a an error as given at the top .
Any idea on how to avoid ??
Thanks in advance !

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-24 : 06:46:37

don't use a temp table. temp table only exists within a session. Your bcp connects to the SQL Server in a separate session. It will not be able to access that temp table created in the stored procedure

You can either use a permanent table or use bcp queryout

KH
[spoiler]Time is always against us[/spoiler]

erbabu
Starting Member

1 Post

Posted - 2007-10-25 : 23:43:05

but hari is using 'bcp queryout' only even then he is getting this problem. Infact I am also facing similar problem. If we are having any temp table inside SP, bcp queryout is not working out correctly. Any alternatives?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-10-25 : 23:44:44

Can try with global temp table if possible.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-25 : 23:57:51

quote:
Originally posted by erbabu

but hari is using 'bcp queryout' only even then he is getting this problem. Infact I am also facing similar problem. If we are having any temp table inside SP, bcp queryout is not working out correctly. Any alternatives?

His problem is not the queryout but the temp table.

What i mean with queryout is to select direct from the base table and not from a temp table

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources