OPEN QUERY: - LINKED SERVERS

[Richard Ade]

I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query

************************************************** *********************8
CREATE PROCEDURE qse_check_label_projectid
@projectid char(18)
AS

Select * from openquery(MRTEST32,'SELECT
Project_id
FROM
DBO.MRT_PROJECT
WHERE
Project_id = @PROJECTID &#39
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!

[Carly]

Say I had a linked server called “A” to the database “PUBS”
There are 3 ways to retrieve this information.
The 4 dotted notation.
select * from A.PUBS.DBO.AUTHORS where au_id=’172-32-1176’
The OpenQuery
select * from openquery(A,'select * from authors where au_id=''172-32-1176''&#39
The third, The third is one of the above created as a view so
Select * from ViewB where au_id=’172-32-1176’

I personally like the view option, Once created you treat it like a normal table.

So how does this translate into a stored procedure.
Watch out for the single to double quotes in the OpenQuery statement.

CREATE PROCEDURE authortest @ID varchar(30) AS
declare @tmp varchar(255)
Print "Normal Select"
select * from a.pubs.dbo.authors where au_id=@id
Print "View select"
select * from viewB where au_id=@id
Print "OpenQuery Select"
set @tmp="select * from openquery(a,'select * from authors where au_id=''"+@ID+"''&#39"
exec (@tmp)



------------
Richard Ade at 1/11/00 4:53:32 PM

I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query

************************************************** *********************8
CREATE PROCEDURE qse_check_label_projectid
@projectid char(18)
AS

Select * from openquery(MRTEST32,'SELECT
Project_id
FROM
DBO.MRT_PROJECT
WHERE
Project_id = @PROJECTID &#39
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!

[RICHARD ADE]

Thanks for the explanation, Carly
My next question is that, is how do you execute a stored procedure in a linked server(sybase) from within your open query function!


------------
Carly at 1/12/00 3:36:30 AM

Say I had a linked server called “A” to the database “PUBS”
There are 3 ways to retrieve this information.
The 4 dotted notation.
select * from A.PUBS.DBO.AUTHORS where au_id=’172-32-1176’
The OpenQuery
select * from openquery(A,'select * from authors where au_id=''172-32-1176''&#39
The third, The third is one of the above created as a view so
Select * from ViewB where au_id=’172-32-1176’

I personally like the view option, Once created you treat it like a normal table.

So how does this translate into a stored procedure.
Watch out for the single to double quotes in the OpenQuery statement.

CREATE PROCEDURE authortest @ID varchar(30) AS
declare @tmp varchar(255)
Print "Normal Select"
select * from a.pubs.dbo.authors where au_id=@id
Print "View select"
select * from viewB where au_id=@id
Print "OpenQuery Select"
set @tmp="select * from openquery(a,'select * from authors where au_id=''"+@ID+"''&#39"
exec (@tmp)



------------
Richard Ade at 1/11/00 4:53:32 PM

I have successfully connected to a sybase 11 database and have successfully run a couple of open query statements against this database, I have now placed this open query in a stored procedure and it works well, but when I want to pass parameter variables has part of my open query it does not like it. It asks me to declare the variable which is a parameter of the stored procedure. Is there any way I can pass in a variable value has part of my query

************************************************** *********************8
CREATE PROCEDURE qse_check_label_projectid
@projectid char(18)
AS

Select * from openquery(MRTEST32,'SELECT
Project_id
FROM
DBO.MRT_PROJECT
WHERE
Project_id = @PROJECTID &#39
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!