Solved

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

Posted on 2004-08-25
10
690 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 250 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 100 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

630 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