donthiel
asked on
SQL Server distributed transaction to Oracle 'Select" syntax question
Usually I work with Oracle but on this occasion I have to enter the dark world of SQL Server. I have a SQL Server 2K db linked to an Oracle 9i db. I'm writing a stored procedure on SQL Server to selet data from SQL Server and perform inserts into Oracle. The code to do the inserts (not shown here) works fine. Therefore the links work as expected.
What I am having a problem with is using a value from SQL Server as part of a 'select' statement against Oracle. I'm wanting to use the variable "@Product" as part of a select against Oracle to get the value of "@pds_flag".
CREATE PROCEDURE sp_test_dist AS
DECLARE @Product varchar (20)
DECLARE @pds_flag varchar(1)
DECLARE src_cursor CURSOR LOCAL FOR
Select Product
From SQL_Server_Table
OPEN src_cursor
--Get the first record
FETCH NEXT FROM src_cursor INTO @Product
SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN DISTRIBUTED TRANSACTION
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @pds_flag=pd
FROM OPENQUERY(ORACLE_DB, 'SELECT pds_flag AS pd FROM oracle_table
WHERE product = ' + Convert(varchar(20),@Produ
-- do inserts into other oracle table
FETCH NEXT FROM src_cursor INTO @Product
END
COMMIT TRAN
CLOSE src_cursor
DEALLOCATE src_cursor
RETURN 0
GO
What I am having a problem with is using a value from SQL Server as part of a 'select' statement against Oracle. I'm wanting to use the variable "@Product" as part of a select against Oracle to get the value of "@pds_flag".
CREATE PROCEDURE sp_test_dist AS
DECLARE @Product varchar (20)
DECLARE @pds_flag varchar(1)
DECLARE src_cursor CURSOR LOCAL FOR
Select Product
From SQL_Server_Table
OPEN src_cursor
--Get the first record
FETCH NEXT FROM src_cursor INTO @Product
SET XACT_ABORT ON
SET NOCOUNT ON
BEGIN DISTRIBUTED TRANSACTION
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @pds_flag=pd
FROM OPENQUERY(ORACLE_DB, 'SELECT pds_flag AS pd FROM oracle_table
WHERE product = ' + Convert(varchar(20),@Produ
-- do inserts into other oracle table
FETCH NEXT FROM src_cursor INTO @Product
END
COMMIT TRAN
CLOSE src_cursor
DEALLOCATE src_cursor
RETURN 0
GO
The above will result in all the records being brought back from oracle and the "where" being applied on the SQL Server side--you will have to use dynamic SQL to pass the parm to openquery....
"you will have to use dynamic SQL to pass the parm to openquery...." (or create a linked server)
ASKER
When I try
SELECT @pds_flag=pd
FROM OPENQUERY(ORACLE_DB, 'SELECT pds_flag AS pd FROM oracle_table ')
WHERE product = @Product
I get the error: Error 207 Invalid column name 'product'
'product' is, of course, a column in oracle_table.
You aren't select PRODUCT from the openquery:
SELECT @pds_flag=pd
FROM OPENQUERY(ORACLE_DB, 'SELECT product,pds_flag AS pd FROM oracle_table ')
WHERE product = @Product
Of course, the above still isn't using Dynamic SQL like I suggested earlier--your criteria won't be passed to Oracle the way you have it...
SELECT @pds_flag=pd
FROM OPENQUERY(ORACLE_DB, 'SELECT product,pds_flag AS pd FROM oracle_table ')
WHERE product = @Product
Of course, the above still isn't using Dynamic SQL like I suggested earlier--your criteria won't be passed to Oracle the way you have it...
ASKER
I've spent most of the day researching "quoting" on msdn with out finding any definitive guidelines on where to use a single quote or where to use a double quote. Every example is different. Anyway, here is the latest version of the offending statement. It compiles and runs but the value of @pds_flag never changes. Oh, and at run time there is an "incorrect syntax" error on the line with the 'WHERE' clause.
SET @pds_flag = 'x'
SELECT @TmpSQL =
" SELECT " + @pds_flag + "=pd
FROM OPENQUERY(PEARS, 'SELECT pds_flag, item_number AS pd, item_number FROM product')
WHERE item_number =" + @product_name + ""
EXEC (@TmpSQL)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
After all that grief, the acutal answer was:
SELECT @pds_flag=pds_flag FROM PEARS..owner.PRODUCT Where item_number = @product_name
however, 'arbert' stuck with me so I am giving him the points.
"After all that grief, the acutal answer was:
SELECT @pds_flag=pds_flag FROM PEARS..owner.PRODUCT Where item_number = @product_name"
Well ya, that's the linked server syntax--I thought you wanted to use openquery--that was your original question...
SELECT @pds_flag=pds_flag FROM PEARS..owner.PRODUCT Where item_number = @product_name"
Well ya, that's the linked server syntax--I thought you wanted to use openquery--that was your original question...
SELECT @pds_flag=pd
FROM OPENQUERY(ORACLE_DB, 'SELECT pds_flag AS pd FROM oracle_table ')
WHERE product = @Product