Robb Hill
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.procna me '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.
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.
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.
ASKER
forgetting the view part of this...I cannot get this select statement to run.
I run the following:
select
*
from openquery(local,'[linkedse rvername]. 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,'[linkedse rver].data basename.d bo.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.
I run the following:
select
*
from openquery(local,'[linkedse
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,'[linkedse
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 :)
2 ' makes 1 ' inside :)
select
*
from openquery(local,'[linkedservername].dbname.dbo.procname ''2017-03-01''')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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
Use a global temp table in your stored proc e.g ## instead of #. Alter and execute to update your stored proc
Open in new window
then us this on your app as
Open in new window