Solved

SQL Server distributed transaction to Oracle 'Select" syntax question

Posted on 2004-08-04
9
667 Views
Last Modified: 2008-03-10
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),@Product) ''           <---- SQL Server gas a syntax error with this line
     
     -- 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
0
Comment
Question by:donthiel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 11719430
try

   SELECT @pds_flag=pd
          FROM OPENQUERY(ORACLE_DB, 'SELECT pds_flag AS pd  FROM oracle_table ')
               WHERE product = @Product
0
 
LVL 34

Expert Comment

by:arbert
ID: 11719461
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....
0
 
LVL 34

Expert Comment

by:arbert
ID: 11719490
"you will have to use dynamic SQL to pass the parm to openquery...."  (or create a linked server)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:donthiel
ID: 11737026

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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11741749
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...
0
 

Author Comment

by:donthiel
ID: 11777521

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)

0
 
LVL 34

Accepted Solution

by:
arbert earned 50 total points
ID: 11777612
This worked for me:

  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 =' + char(39) +  @product_name +  char(39)


Whether you use single or double quotes depends on several things.  In SQL, it depends on your Quoted Identifier setting, and since you're using openquery, it will depend on your backend database some as well.

Brerr
0
 

Author Comment

by:donthiel
ID: 11786992


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.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11787040
"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...
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question