Link to home
Start Free TrialLog in
Avatar of Robb Hill
Robb HillFlag for United States of America

asked on

Linked Server - SP with Param to VIew

I have been provided a Stored Proc  from another server...that I can call over a linked server.  This is my only access to this data.

With that being said I need to have a view on my side of the server...as I have an application that depends on this data that is pulled from the stored proc to be in this view.

How in the world do I do this.

Here is how I execute the stored proc over the linked.

exec [linkedservername].dbname.dbo.procname '2017-03-01';


I need to be able to take the data that is returned..and create a view...and I really need to be able to select from this data as I need to make adjustments to it before the view is called.


Is this possible?

I was looking at Openquery...but am not having any luck.


If there is a way to do this..the view would need to refresh upon being called...but the view has to be in place for the application to work.
Avatar of HainKurt
HainKurt
Flag of Canada image

what about this

create view dbo.MyView
as
select
*
from openquery(local,'[linkedservername].dbname.dbo.procname '''2017-03-01'''')

Open in new window


then us this on your app as

select * from MyView

Open in new window

Avatar of Robb Hill

ASKER

forgetting the view part of this...I cannot get this select statement to run.

I run the following:

select
*
from openquery(local,'[linkedservername].dbname.dbo.procname '''2017-03-01'''')


I changed out linkedservername with the name of the linkedserver, I left local as is, I then put the db name.dbo.proc and the param.

It is erroring out becuase of the tick marks.

select
*
from openquery(local,'[linkedserver].databasename.dbo.proc '''2017-03-01'''')

It sais
Incorrect syntax near '2017'.

If I adjust the parameter to ''2017-03-01''')

then it sais

Could not find server 'local' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.
pay attention to '

2 ' makes 1 ' inside :)


select
*
from openquery(local,'[linkedservername].dbname.dbo.procname ''2017-03-01''')

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Ok I had gotten this before previous to me posting...I get this:

Invalid object name '#Changed'.



I think this is a temp table defined in the stored proc..but I do not have full access to how the stored proc is written..

Not sure why this would matter...it should either call it or not..right?
I guess you need some permissions on linked server...
or you should

Use a global temp table in your stored proc e.g ## instead of #. Alter and execute to update your stored proc