Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using a variable in a pass through query from SQL server to an Oracle server

Posted on 2004-08-25
10
Medium Priority
?
691 Views
Last Modified: 2012-06-27
I've looked at:

http://www.sqljunkies.com/Article/BA89A311-B3AE-4803-BABA-64FCF1E1F8F7.scuk

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_oa-oz_78z8.asp

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=37662

http://www.experts-exchange.com/Databases/Q_20841210.html

With no working results so far.

I have a stored procedure defined in SQL server that takes a variable, @studentID, and uses a pass through query to query an Oracle database, e.g.:

DECLARE @studentID nvarchar(9)
SET @studentID = '123456789'

SELECT *
FROM OPENQUERY(ORACLE_SERVER, 'SELECT *

FROM
      StudentIDTable ID,
      StudentNameTable Name,
      StudentLoginTable Login,
      StudentProgramTable Program,
      StudentProgramCodeTable_CODE Code

WHERE
      STUDENT_ID = ''@studentID''
      AND Login.Identifier = ID.Identifier
      AND Name.Identifier = ID.Identifier
      AND Program.Identifier=ID.Identifier  
      AND Code.AcademicProgram=Program.AcademicProgram
      AND Program.Major=''MAJR_CODE_1'''
)

This returns 0 results, whereas replacing the @studentID directly in the WHERE clause does return a result. I've also tried it using this format:

DECLARE @TSQL nvarchar(4000), @studentID nvarchar(9)

SET @studentID = '123456789'
SET @TSQL='SELECT * FROM OPENQUERY(ISODS_PROD, ''SELECT DISTINCT
      Name.ADDRESS_NAME, ID.STUDENT_ID, Login.LOGIN_ID,
      Program.AcademicProgram, Code.ACAD_DISCIPLINE_IAIS_NAME

FROM
      StudentIDTable ID,
      StudentNameTable Name,
      StudentLoginTable Login,
      StudentProgramTable Program,
      StudentProgramCodeTable_CODE Code

WHERE
      STUDENT_ID = ''''+@studentID+''''
      AND Login.Identifier = ID.Identifier
      AND Name.Identifier = ID.Identifier
      AND Program.Identifier=ID.Identifier  
      AND Code.AcademicProgram=Program.AcademicProgram
      AND Program.Major=''''MAJR_CODE_1'''''')'

EXEC (@TSQL)

Again, no results. I've also permuted the number of '' around the variable with no luck. Anyone have the correct syntax?

Thanks!
0
Comment
Question by:acgetchell
[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
  • 4
10 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 1000 total points
ID: 11897239
Try this:

DECLARE @TSQL nvarchar(4000), @studentID nvarchar(9)

SET @studentID = '123456789'
SET @TSQL='SELECT * FROM OPENQUERY(ISODS_PROD, ''SELECT DISTINCT
     Name.ADDRESS_NAME, ID.STUDENT_ID, Login.LOGIN_ID,
     Program.AcademicProgram, Code.ACAD_DISCIPLINE_IAIS_NAME

FROM
     StudentIDTable ID,
     StudentNameTable Name,
     StudentLoginTable Login,
     StudentProgramTable Program,
     StudentProgramCodeTable_CODE Code

WHERE
     STUDENT_ID = ' + char(39) + @studentID+ char(39) + '     AND Login.Identifier = ID.Identifier
     AND Name.Identifier = ID.Identifier
     AND Program.Identifier=ID.Identifier  
     AND Code.AcademicProgram=Program.AcademicProgram
     AND Program.Major=''''MAJR_CODE_1'''''')'

print (@TSQL)
0
 
LVL 34

Expert Comment

by:arbert
ID: 11897243
change the print to EXEC of course :)
0
 

Author Comment

by:acgetchell
ID: 11897469
Thanks, but this doesn't work. As written above, I get syntax errors. Once 4 ' are included each side, the query returns 0 records:

DECLARE @TSQL nvarchar(4000), @studentID nvarchar(9)

SET @studentID = '123456789'
SET @TSQL='SELECT * FROM OPENQUERY(ISODS_PROD, ''SELECT DISTINCT
     Name.ADDRESS_NAME, ID.STUDENT_ID, Login.LOGIN_ID,
     Program.AcademicProgram, Code.ACAD_DISCIPLINE_IAIS_NAME

FROM
     StudentIDTable ID,
     StudentNameTable Name,
     StudentLoginTable Login,
     StudentProgramTable Program,
     StudentProgramCodeTable_CODE Code

WHERE
     STUDENT_ID = ''''+char(39)+@studentID+char(39)''''
     AND Login.Identifier = ID.Identifier
     AND Name.Identifier = ID.Identifier
     AND Program.Identifier=ID.Identifier  
     AND Code.AcademicProgram=Program.AcademicProgram
     AND Program.Major=''''MAJR_CODE_1'''''')'

EXEC (@TSQL)
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 34

Expert Comment

by:arbert
ID: 11897505
"As written above, I get syntax errors. Once 4 '"

Hmm, you didn't write that above and the example you posted passed @studentID as a literal...

What is your quoted identifier setting?
0
 
LVL 34

Expert Comment

by:arbert
ID: 11897551
What do you get with this (will oracle support double quotes around your alpha columns?):

set quoted_identifier off
DECLARE @TSQL nvarchar(4000), @studentID nvarchar(9)

SET @studentID = '123456789'
SET @TSQL='SELECT * FROM OPENQUERY(ISODS_PROD, ''SELECT DISTINCT
     Name.ADDRESS_NAME, ID.STUDENT_ID, Login.LOGIN_ID,
     Program.AcademicProgram, Code.ACAD_DISCIPLINE_IAIS_NAME

FROM
     StudentIDTable ID,
     StudentNameTable Name,
     StudentLoginTable Login,
     StudentProgramTable Program,
     StudentProgramCodeTable_CODE Code

WHERE
     STUDENT_ID = "' +@studentID+ '"
     AND Login.Identifier = ID.Identifier
     AND Name.Identifier = ID.Identifier
     AND Program.Identifier=ID.Identifier  
     AND Code.AcademicProgram=Program.AcademicProgram
     AND Program.Major="MAJR_CODE_1")'

print (@TSQL)
0
 

Author Comment

by:acgetchell
ID: 11897721
Well, SQL Query Analyzer packages the sproc as:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

<Code above>

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

If I change QUOTED_IDENTIFIER OFF, I still get a syntax error with double quotes enclosed by quotes.

However, If I then use single quotes, ie

SET QUOTED_IDENTIFIER OFF

DECLARE @TSQL nvarchar(4000), @studentID nvarchar(9)

SET @studentID = '123456789'
SET @TSQL='SELECT * FROM OPENQUERY(ORACLE_SERVER, ''SELECT DISTINCT
      Name.ADDRESS_NAME, ID.STUDENT_ID, Login.LOGIN_ID,
      Program.ACAD_PROGRAM_CODE, Code.ACAD_DISCIPLINE_IAIS_NAME

FROM
      STUDENT_ID_V ID,
      PERSON.STUDENT_NAME_V Name,
      PERSON.STUDENT_LOGIN_ID_V Login,
      STUDENT.STUDENT_ACAD_PROGRAM Program,
      CODE.ACAD_PROGRAM_CODE Code

WHERE
      STUDENT_ID = '+@studentID+'
      AND Login.PERSON_WH_ID = ID.PERSON_WH_ID
      AND Name.PERSON_WH_ID = ID.PERSON_WH_ID
      AND Program.PERSON_WH_ID=ID.PERSON_WH_ID  
      AND Code.ACAD_PROGRAM_CODE=Program.ACAD_PROGRAM_CODE
      AND Program.MAJOR_SOURCE_CODE=''''MAJR_CODE_1'''''')'

EXEC (@TSQL)

I get an error message, not a syntax error:

Server: Msg 7330, Level 16, State 2, Line 1
Could not fetch a row from OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-01722: invalid number
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IRowset::GetNextRows returned 0x80040e07].



0
 
LVL 34

Expert Comment

by:arbert
ID: 11898195
So is student_ID defined as a number in oracle????
0
 

Author Comment

by:acgetchell
ID: 11898331
It's a varchar2(11)
0
 
LVL 1

Accepted Solution

by:
amulya_333 earned 400 total points
ID: 11899849
STUDENT_ID = ' + char(39) + @studentID+ char(39) + '  AND Login.Identifier = ID.Identifier
AND Name.Identifier = ID.Identifier
     AND Program.Identifier=ID.Identifier  
     AND Code.AcademicProgram=Program.AcademicProgram
     AND Program.Major='+char(39)+'MAJR_CODE_1'+char(39)+')'

I would like to tell you that any thing between ' and ' is constant so please watch out for extra spaces in the query when you print.
0
 

Author Comment

by:acgetchell
ID: 11905931
Well, finally got it to work. The correct answer ended up using triple quotes plus the char(39) for single quotes. I increased the point total since two of you contributed the correct idea, and I'll give the original total to arbert and the extra points to amulya. Hope that's fair for everyone involved.

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

719 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