multi part identifier could not be bound

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

SQL Server 2005 Forums

Transact-SQL (2005)

multi part identifier could not be bound

Author

Topic

sharonmtowler
Starting Member

12 Posts

Posted - 2010-04-30 : 12:29:51

i have a procedure which is stored on one database and i want it to pull data from another. i can run the query fine on the storage database but when i try to create to sp i get error message MULTI PART IDENTIFIER COULD NOT BE BOUND
I put the from SERVER.DATABASE.dbo.TABLE is there something missing?

ALTER PROCEDURE [dbo].[USR_CR_CUSTBYRISKBYTYPE]
(@STARTDATE INT,
@ENDDATE INT)
AS

SELECT distinct
c1.id as cust_id,
c1.name,
c1.country,
activityhist.tranno,
activityhist.bookdate,
activityhist.baseamt,
c1.riskclass,
customer.type as customertype,
custtype = CASE WHEN customer.type is null then 'None'
else customer.type END,
c1.closed,
c1.status,
activityhist.recvpay,
received_paid = CASE WHEN activityhist.recvpay = 1 then 'Received'
when activityhist.recvpay = 2 then'Paid'
END,
dbo.Account.Type as accttype,
dbo.AccountType.Code,
dbo.AccountType.Name as at_name
FROM
PRIMEDEV.PBSA.dbo.Customer c1
INNER JOIN
PRIMEDEV.PBSA.dbo.Country ON
dbo.c1.Country = dbo.Country.Code
INNER JOIN
PRIMEDEV.PBSA.dbo.ActivityHist ON
dbo.c1.Id = dbo.ActivityHist.Cust
INNER JOIN
PRIMEDEV.PBSA.dbo.Account
INNER JOIN
PRIMEDEV.PBSA.dbo.AccountType ON
dbo.Account.Type = dbo.AccountType.Code ON
dbo.ActivityHist.Account = dbo.Account.Id
where activityhist.bookdate BETWEEN @startDate AND @endDate

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-30 : 13:45:10

Did you create PRIMEDEV as a linked server?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sharonmtowler
Starting Member

12 Posts

Posted - 2010-04-30 : 14:03:46

not sure about that, i am not the dba, just write the code is that what needs to be done to for this to work? i really just need to syntax or the procedure for the linked server

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-30 : 14:20:16

The DBA has to create the linked server.

But are you sure you need to use the four-part naming convention? Does the PBSA exist on a separate server or on the same server where the stored procedure exists? If it's the same server, then you only need the three-part naming convention: DbName.dbo.TableName.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sharonmtowler
Starting Member

12 Posts

Posted - 2010-04-30 : 15:03:55

its the same server and i got it to work, by qualifying every table and field. it compiles correctly with no errors. i added the db_name() to the sp to see if it is actually retrieving from the db i want, but it displays where it is running from, does that mean i need the linked server. how do you set that up. i am now becoming the temporary dba here.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-04-30 : 15:06:23

You do not need a linked server for this since the database is on the same server.

The stored procedure executes only in the database where you created it. DB_NAME() just shows you what you are connected to.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources