April 30, 2012 at 1:09 pm
Hello All!
I have a Linked Server that connects to an Oracle database
I wanted to create a stored procedure that I can pass a value to and retrieve the record associated with that value.
I was successful in creating the SP but when I pass the value to it, I only see “command completed successfully”.
If I run the following code, it works fine:
declare @MyString varchar(8000), @TicketNumber varchar(50)
set @TicketNumber = 'CHG000001358922'
set @MyString = 'select *
from TKTMART.ARS_CM_TICKET
where CHANGE_ID_ = ''' + @TicketNumber + ''''
set @MyString = N'select * from openquery
(AOTS, ''' + REPLACE(@MyString, '''', '''''') + '''
)'
EXEC (@MyString)
But if I run this – it doesn’t work.
CREATE PROCEDURE dbo.spGetCMTicket
@TicketNumber varchar(50)
AS
SET NOCOUNT ON;
declare @MyString varchar(8000)
set @MyString = 'select *
from TKTMART.ARS_CM_TICKET
where CHANGE_ID_ = ''' + @TicketNumber + ''''
set @MyString = N'select * from openquery
(AOTS, ''' + REPLACE(@MyString, '''', '''''') + '''
)'
execute spGetCMTicket N'CHG000001358922'
The SP that I'm creating will not reside on the Oracle Linked Server.
Has anybody had any luck passing a parameter to a stored procedure that retrieves records from a linked server?
Please let me know if you need any additional information or further clarification.
Thank You,
Ronnie
April 30, 2012 at 2:07 pm
I do not see EXEC(@MyString) in your proc.
Also, to clarify, make sure you have a GO after your proc definition when compiling to make sure you do not include anything you did not intend to include in your proc definition...like a call to the proc for example (will see recursive issues).
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 1, 2012 at 2:42 pm
I'm not guite sure what you're referring to when you say that you don't see EXEC(@MyString) in the proc.
Are you saying that it should be there and that's why I'm not getting my output or are you saying that it needs to be in another location in my proc?
Thanks,
May 1, 2012 at 2:53 pm
Ronnie Jones (5/1/2012)
Are you saying that it should be there and that's why I'm not getting my output or are you saying that it needs to be in another location in my proc?
Yes, that's what I'm saying. If your proc is defined as this (reformatted and added GO, but same code as above):
CREATE PROCEDURE dbo.spGetCMTicket
@TicketNumber VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyString VARCHAR(8000)
SET @MyString = 'select *
from TKTMART.ARS_CM_TICKET
where CHANGE_ID_ = ''' + @TicketNumber + ''''
SET @MyString = N'select * from openquery (AOTS, ''' + REPLACE(@MyString, '''', '''''') + ''')'
END
GO
Then the SELECT is never being executed.
Let us rid ourselves of a use of OPENQUERY (use EXEC...AT) and add some protection from SQL injection (use QUOTENAME), try it like this:
CREATE PROCEDURE dbo.spGetCMTicket
(
@TicketNumber VARCHAR(50)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @MyString VARCHAR(MAX);
SET @MyString = 'select * from TKTMART.ARS_CM_TICKET where CHANGE_ID_ = ' + QUOTENAME(@TicketNumber, '''');
EXEC(@MyString) AT [AOTS];
END
GO
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply