Solved

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

Posted on 2004-08-25
10
688 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
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 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

740 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