Solved

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

Posted on 2004-08-25
10
674 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
  • 5
  • 4
10 Comments
 
LVL 34

Assisted Solution

by:arbert
arbert earned 250 total points
Comment Utility
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
Comment Utility
change the print to EXEC of course :)
0
 

Author Comment

by:acgetchell
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
"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
Comment Utility
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:acgetchell
Comment Utility
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
Comment Utility
So is student_ID defined as a number in oracle????
0
 

Author Comment

by:acgetchell
Comment Utility
It's a varchar2(11)
0
 
LVL 1

Accepted Solution

by:
amulya_333 earned 100 total points
Comment Utility
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
Comment Utility
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

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 setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now