How to access separate database from SQL Server stored procedure

Question

I am trying to run the below code but run into the error:

Multi-part Identifier could not be bound

I think it is due to trying to access a database table from a separate database but it is on the same server. Any ideas?

SELECT DISTINCT
    @ActiveStudents2 = COUNT([ActivityHistory].[dbo].[tblActivityCounts].[id])
FROM  
    dbo.tblSchools 
INNER JOIN 
    dbo.tblStudentSchool ON dbo.tblSchools.schoolid = dbo.tblStudentSchool.schoolid 
INNER JOIN 
   dbo.tblStudentPersonal ON dbo.tblStudentSchool.id = dbo.tblStudentPersonal.id
WHERE 
     dbo.tblStudentSchool.schoolid IN (@tempschoolid) 
     AND tblStudentSchool.graduationyear IN (SELECT Items 
                                             FROM FN_Split(@gradyears, ',')) 
     AND ([ActivityHistory].[dbo].[tblActivityCounts].[datetimechanged] >= @datefrom 
     AND [ActivityHistory].[dbo].[tblActivityCounts].[datetimechanged] <= @dateto)

The error occurs when I try to access the tblActivityCounts table in the Activity History database which is a separate database. I even try running this as the sa and it doesn't work. There aren't any spelling errors. Any help is appreciated. Thank you!

This is a syntax error, you don't seem to be joining on [ActivityHistory].[dbo].[tblActivityCounts]. — Alex
– Alex, Commented Apr 9, 2015 at 17:40
Not sure what your splitter looks like but given the name I have a feeling it is the one with a for loop in it. Take a look at this article for a better splitter. sqlperformance.com/2012/07/t-sql-queries/split-strings — Sean Lange
– Sean Lange, Commented Apr 9, 2015 at 17:56

Aleem · Accepted Answer · 2015-04-09 17:41:08Z

2

You are not joining on ActivityHistory table. That's why the query doesn't know from where to access tblActivityCounts table.

answered Apr 9, 2015 at 17:41

Aleem

925 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

BAReese · Accepted Answer · 2015-04-09 20:59:14Z

As Aleem said, you're not joining in the other table. I'd also recommend taking advantage of table aliases to make your code just a bit more readable... Makes it much easier to figure out what you were doing when you come back to it later. Depending on how tblActivityCounts relates to the other tables, you would do something like this:

SELECT      @ActiveStudents2 = COUNT(ActivityHistory.dbo.tblActivityCounts.id)
FROM        dbo.tblSchools as Schools
INNER JOIN  dbo.tblStudentSchool as Students
    ON      Schools.schoolid = Students.schoolid
INNER JOIN  dbo.tblStudentPersonal as Personel
    ON      Students.id = Personel.id
INNER JOIN  ActivityHistory.dbo.tblActivityCounts as Activity
    ON      Activity.studentid = Students.id -- Depending on how tblActivityCounts relates to the other tables
WHERE       Students.schoolid IN (@tempschoolid)
    AND     tblStudentSchool.graduationyear IN (SELECT Items FROM FN_Split(@gradyears, ',')) 
    AND     Activity.datetimechanged >= @datefrom 
    AND     Activity.datetimechanged <= @dateto

SouravA · Accepted Answer · 2015-04-10 10:13:32Z

Your query is wrong. This line:

SELECT DISTINCT
    @ActiveStudents2 = 
    COUNT([ActivityHistory].[dbo].[tblActivityCounts].[id])

Will retrieve all Ids from the tblActivityCounts table and count them, but has no reference in the rest of your query. You have to JOIN with this table and reference it to make your count. Also, I would recommend you use aliases.

Something like this:

SELECT DISTINCT
    @ActiveStudents2 = COUNT(ah.[id])
FROM dbo.tblSchools s
INNER JOIN dbo.tblStudentSchool ss ON s.schoolid = ss.schoolid 
INNER JOIN dbo.tblStudentPersonal sp ON s.id = sp.id
INNER JOIN [ActivityHistory].[dbo].[tblActivityCounts] ah ON s.acIdentifier = ah.acIdentifier
WHERE ss.schoolid IN (@tempschoolid) 
     AND ss.graduationyear IN (SELECT Items 
                               FROM FN_Split(@gradyears, ',')) 
                                AND ([ActivityHistory].[dbo].[tblActivityCounts].[datetimechanged] >= @datefrom 
                                AND [ActivityHistory].[dbo].[tblActivityCounts].[datetimechanged] <= @dateto)

Collectives™ on Stack Overflow

How to access separate database from SQL Server stored procedure

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related