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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-12-17 : 09:04:36
|
| David writes "Dear Team, I am trying to install some Stored Procedures on a set of remote machines, the stored proc references the host server, but because the install is not done on line, it falls over because of the remote machine reference. BOL states that "a syntactically correct stored procedure that references objects which do not exist can be created successfully, "This is true if the objects referred to are local. ie It can be seen in a statement like Create Procedure TestProc as Select * from NoTable Which executes fine creating the stored proc, however if full naming procedures are followedie Create Prcedure TestProc as Select * from HostServer.Northwind.dbo.NoTableThe following error message is returned:'Server: Msg 7314, Level 16, State 1, Procedure TestCompile, Line 4OLE DB provider 'HostServer' does not contain table '"northwind"."dbo"."NoTable"'. The table either does not exist or the current user does not have permissions on that table.' The Stored Proc is therefore not created. I do not wish to have to get the remote Servers (they are reps laptops) on-line in order to install this proc. The proc only needs to execute when they dial-up for replication, but will be installed from a CD when they are off-line. Is there a solution or workaround to this?Thanks in AdvanceDavid Droogleever" |
|
|
ToddV
Posting Yak Master
218 Posts |
Posted - 2001-12-17 : 09:38:48
|
| Maybe OPENQUERY or OPENROWSET will meet your needs |
 |
|
|
DavidD
Yak Posting Veteran
73 Posts |
Posted - 2001-12-17 : 21:01:56
|
| Thanks for the reply ToddV, but unfortunately SQL Server still tries to parse the Stored Proc, and will not create it if it cannot see the other db. What I really need is a no parse technique for creating the Stored proc - if such a technique exists??? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2001-12-17 : 22:32:28
|
| You could try creating a dummy database on the laptop to resolve the tables. Use an odbc datasource for the linked server to point at the local machine. Instal the SP then change the lnked server to point at the remote one.The linked server changes could be part of the SP installation script.Don't know whether this would work but might be worth a try.==========================================Cursors are useful if you don't know sql.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
Creation of Stored procs referencing full file names