Go Premium for a chance to win a PS4. Enter to Win

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
?
693 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 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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

782 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