0

I am setting up a SQL Server database and have been given access to call a stored procedure in a remote server to help populate some tables.

I created a linked server as SSIS in Visual Studio 2008 and SQL Server Management Studio 2008 seem to not like EXEC statements in their SSIS packages for remote servers.

So having successfully set up linked server I try to execute my stored procedure with the following

Select * from OPENQUERY(LinkedServerName, 'exec  storedProc paramValue')

But get the following error

Msg 208, Level 16, State 1, Procedure storedProc, Line 49
Invalid object name '#tmp_bl'.

I have tried to Select my Linked server but no luck.

Does anyone know what the exec statement should be so I can put it into a local SSIS package?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user1417337
  • 75
  • 1
  • 4
  • 11
  • 1
    Can you explain why your SSIS package doesn't just connect directly to the second server? Why does it connect to the first server and then use a linked server? – Pondlife May 25 '12 at 12:19
  • There is some problem in storeproc sp, check for '#tmp_bl' text in that sp. Share the sp. – Romil Kumar Jain May 25 '12 at 12:28

1 Answers1

0
Msg 208, Level 16, State 1, Procedure storedProc, 
Line 49 Invalid object name '#tmp_bl'.

Looks like the SP you are calling, in turn tries to access a temp table named #tmp_bl.

So, when you try to run your SSIS package from design (BIDS) it validates the package and couldn't found temp table; so errors.

Solution: Create the temp table first (using SSMS/Sqlcmd) and then try to run the package; like

create table #tmp_tbl ....

If you are trying to run the package from command prompt using dtexec utility then make the delayvalidation property of the package control (control which actually executing your temp table; either DataFlow task or SQLTask) to True (DelayValidation = True). Which, will not validate to see whether temp table exist or not while validating your entire package.

Rahul
  • 76,197
  • 13
  • 71
  • 125
  • hey guys, i sorted the issue, it was a config issue with my MS SQL management Studio - I needed to go into LinkedServer -> providers-> OraOLEDB.oracle Double click on this and it shows you the options – check the ‘Allow inprocess’ option. – user1417337 Jul 24 '12 at 17:32
  • @user1417337: you could write an answer to your own question, that way you might help someone else with a similar problem! – Josien Jul 26 '12 at 13:30
  • But why not connect directly to the server from SSIS? Why use a linked server at all? You're overcomplicating your process. – Nick.Mc Mar 09 '16 at 06:45