-
OPEN QUERY: - LINKED SERVERS
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 '
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!
-
OPEN QUERY: - LINKED SERVERS (reply)
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'''
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+"'''"
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 '
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!
-
OPEN QUERY: - LINKED SERVERS (reply)
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'''
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+"'''"
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 '
************************************************** ***************************
Please I believe there should be a way to pass a value through this open query function!
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|