Server or Databse Setting

Question

Server or Databse Setting

HBhagat

Hall of Fame

Points: 3341
More actions
August 21, 2009 at 9:15 am

#72409

Hi All,
I have 2 servers..
BB -Live
KK- warm
I want to insert data executing a procedure. There are some procedures which uses temporary tables.
In Live if i execute the procedure to insert the data into a table it happens without any problem. But the same thing if I do it KK it fails with the error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name '#temp_able'
Any help please..
I need to test a lot of procedures but due to this I am not able to do it...
I am using openquery to execute the procedure in warm server as if i try :-
Insert into Temp_table
exec kk.dbame.dbo.procname 1,1,1
it gives a total different error..
Can anybody please help..

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 1

A table that starts with # is a local temporary table. It exists on that server only, and must be created. If you created it on primary, it doesn't exist on the secondary unless you create it there.

HBhagat Hall of Fame Points: 3341 More actions · Answer 2

But how come i do not have the error for the same in Live... and i get the error in warm server!!!

i am doing excatly the same process in both the server.. but live is working and the dev is failing..

HBhagat Hall of Fame Points: 3341 More actions · Answer 3

Also... a few days back both were not working and then my live and dev started working..

Now at this moment my dev is only not working..

Exactly what I am doing is as follows:

Use live_test_unit

insert into test_unit1

select * from openquery(BB , 'exec BB.dbname.dbo.procedure_name '1' , '1' , '1'')

The above query enters record into the table. But the same thing is failing in dev with the Msg 1864..

Steve Jones - SSC Editor SSC Guru Points: 736479 More actions · Answer 4

You need to post all the code. In the first post you have an error for the '#temp_able'. In the last post, you don't show that.

something is different between the servers.

HBhagat Hall of Fame Points: 3341 More actions · Answer 5

Hi,

My base part of the procedure is only this..

If that wont work then I will not go further with other procedures...

I have been asked to make test plans for the procedure . I have to compare the data what each server return.

So for this.. in Live server I have made a database live_test_unit and in dev server as dev_test_unit

The procedures where there is no temp tables in the procedures.. do not have any problems.

Once there is a usage of temporay tables in the procedures I am getting this problem

Use live_test_unit

insert into test_unit1

select * from openquery(BB , 'exec BB.dbname.dbo.procedure_name '1' , '1' , '1'')

The above query insert rows into the table without any error.

Use dev_test_unit

insert into test_unit1

select * from openquery(KK , 'exec KK.dbname.dbo.procedure_name '1' , '1' , '1'')

The above gives me the error 208. as invalid object name for the temporary table used in the procedure..

If the above query inserts row into the table then I will start with other procedures..

I hope this helps

HBhagat Hall of Fame Points: 3341 More actions · Answer 6

Hi,

The problem got resolved now..

in the openquery i have added SET FNTONLY OFF and now it working fine..

Thanks for the help:-)