Creating SPs using non-existant database object

Question

Creating SPs using non-existant database object

Joe Wagner

Ten Centuries

Points: 1394
More actions
August 19, 2004 at 7:08 pm

#108812

This got buried in another thread I started, and I wanted to give it a little more attention:
Is there a way to create a stored procedure that references a table that does
not currently exist? It should also be WITHOUT dynamic SQL.
For example:
SELECT * FROM
[linked-server].[Finance].[dbo].[ChartOfAccounts]
This will produce an error when I try to create/save the SP,if the linked server doesn't exist, or the ChartOfAccounts table doesn't exist in the Finance database, etc, etc.
When I deploy the SP, all of the specifics for the linked-server may not be
set up.
Is there a way to have the objects checked at run-time, rather than at
CREATE PROCEDURE time?
I still expect to get errors at run time if the objects haven't been properly configured. It's driving me nuts that I can't even save the SP if all the pieces are not in place.
Thanks.
August 23, 2004 at 8:00 am

This was removed by the editor as SPAM

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

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

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

SQL should let you create a proc that references a table that doesn't exist, but it will not let you do so if you're referencing a linked server that doesn't exist.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 2

select * from sysservers

You can always add linked servers like this where they exist as linked servers in your development environment.

sp_addlinkedserver @server = 'PrimarySvr' ,
@srvproduct = '', @provider = 'SQLOLEDB' ,
@datasrc =  '%SvrName%'
go 
sp_addlinkedserver @server = 'SecondarySvr' ,
@srvproduct = '', @provider = 'SQLOLEDB' ,
@datasrc =  '%SvrName%'
go

Then develop with fully qualified names (FQN)

in all your procedures as

[SecondarySvr].[Finance].[dbo].[ChartOfAccounts]

and

[PrimarySvr].[Acctg].[dbo].[GL_Entry]

Then to take it to production you would always add the linked servers at install.

Just throwing out my $0.02.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.

Joe Wagner Ten Centuries Points: 1394 More actions · Answer 3

Jim,

I just found that technique over the weekend, and implemented it today. Works great, as you know.

Do you know if there are any performance penalties for defining the linked server this way, as opposed to specifying @srvproduct = 'SQL Server' and leaving off @provider? Or does is boil down to the same thing?

Thanks.

Jim P. SSCrazy Eights Points: 8725 More actions · Answer 4

Actually, I came up with the idea in response to your question. I haven't tried it in production, but I can't really foresee a performance hit.

I'd look at execution plans and try some more complicated transactions. That's really the only way to know.

----------------
Jim P.

A little bit of this and a little byte of that can cause bloatware.