cannot bind multipart identifier in linked DB

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cannot bind multipart identifier in linked DB

Author

Topic

percy2009
Starting Member

2 Posts

Posted - 2009-10-21 : 18:42:34

Hi chaps,

I’m struggling with a TSQL based stored procedure on a SQL2K5 server that needs to retrieve demographic data from an Oracle9 Server.
I’m not a developer (work in IT Support) but need to sort this sp for a medical system about to go live. I have created a linked server to the Oracle box which works fine & I can pull of individual queries, the problem is that the data needs to be pulled from a combination of three tables on the oracle box, two of the fields that need to be returned are also conditional, extract of Oracle data dictionary below:-

Table :silver.number_ids
Name Null? Type
----------------------------------------- -------- ------------------
NUMBER_ID VARCHAR2(10)
NUM_ID_TYPE VARCHAR2(4)
RM_PATIENT_NO NUMBER(8)

Table: silver.patients
Name Null? Type
----------------------------------------- -------- ------------------
RM_PATIENT_NO NUMBER(8)
DATE_OF_BIRTH DATE

Table: silver.surname_ids
Name Null? Type
----------------------------------------- -------- ------------------
RM_PATIENT_NO NUMBER(8)
SURNAME_TYPE VARCHAR2(2)
SURNAME_ID VARCHAR2(35)
NAME1 VARCHAR2(20)
NAME2 VARCHAR2(20)

The called procedure accepts an arguments of “HospitalNumber” and/or “NHS Number” (& optionally Surname, Forename & DOB fields).

The value of SILVER.NUMBER_IDS.NUMBER_ID equates to HospitalNumber if SILVER.NUMBER_IDS.NUM_ID_TYPE = “W”,
However,
The value of SILVER.NUMBER_IDS.NUMBER_ID equates to “NHS Number” if SILVER.NUMBER_IDS.NUM_ID_TYPE = “NHS”,

I am getting following error: “The multi-part identifier "SILVER.SURNAME_IDS.RM_PATIENT_NO" could not be bound.” with the following code (I have replaced the record number with ##### to maintain confidentiality):

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[spCancerRegistry]
-- Add the parameters for the stored procedure here
@HospitalNumber varchar (10),
@NHSNumber varchar (10),
@Forename varchar (35),
@Surname varchar(35),
@DateOfBirth datetime

AS
BEGIN
SET NOCOUNT OFF;

IF(@HospitalNumber is not NULL)
BEGIN

SELECT RM_PATIENT_NO, SURNAME_ID FROM OPENQUERY(ICSRPT, 'select * FROM SILVER.SURNAME_IDS') where SILVER.SURNAME_IDS.RM_PATIENT_NO = ######;
END
END

I have tried inner joins etc - but suspect there's limitations when talking to a linked Oracle Database.
cheers

Percy2009

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-10-21 : 18:51:11

Is it just a matter of a ) ?

SELECT RM_PATIENT_NO, SURNAME_ID FROM OPENQUERY(ICSRPT, 'select * FROM SILVER.SURNAME_IDS') where SILVER.SURNAME_IDS.RM_PATIENT_NO = ######);

Jim

Everyday I learn something that somebody else already knew

percy2009
Starting Member

2 Posts

Posted - 2009-10-22 : 11:35:03

quote:
Originally posted by jimf

Is it just a matter of a ) ?

SELECT RM_PATIENT_NO, SURNAME_ID FROM OPENQUERY(ICSRPT, 'select * FROM SILVER.SURNAME_IDS') where SILVER.SURNAME_IDS.RM_PATIENT_NO = ######);

Jim

Everyday I learn something that somebody else already knew

Hi Jim, the extra bracket will throw it out of syntax.

Percy2009

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources