sidestep view/funct​ion limitation​s

Problem: need to create an object that can be used in a select from statement but due to limitations such as not being allowed to use variables in views or use side effecting operators in functions no object can be easily created.

Solution:

Create a stored procedure which returns the required dataset
Ensure a self referencing linked server exists on the server
Create view as a select * from openquery(SERVER,’exec stored_proc_name’)

in some cases you may need to modify the openquery statement

if your proc uses dml use the nocount on clause
if your proc uses temp table use the set fmtonly on clause

eg: Create view as a select * from openquery(SERVER,’SET NOCOUNT ON;SET FMTONLY OFF;exec stored_proc_name’)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: