Linked Servers - SQL Server Forums

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

Linked Servers

Author

Topic

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-10-09 : 11:46:25

Hi all , have a tricky problem that i cant seem to resolve so any support greatly appreciated :

Originally i had server fsm016 ,with a db called ccmdb1 , and a same server with a database called supportdeskdev , i had an sql script that pulled data from both databases , this worked fine, see script below .

I have had to change the setup so now my database ccmdb1 is on a new server called uk0qw0004, and my database that was supportdeskdev has a new name called HOTHLive and now lives on a seperate server called UK0qnt003.

I decided to create a linked server on uk0qnt003 to point to uk0qw0004 and create an alias called uk0qw0004alias, i amended my script to add the server name etc to the script , but it now fails with the following errors ( there is more lines of the same )

Any thoughts as to what i have missed ??

Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "UK0QW0004ALIAS.ccmdb1.dbo.agent.agent_id" could not be bound.
Msg 4104, Level 16, State 1, Line 20
The multi-part identifier "UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.agent_id" could not be bound.
Msg 4104, Level 16, State 1, Line 22

NEW SCRIPT not working :

Select
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(UK0QW0004ALIAS.ccmdb1.dbo.agent.agent_name) as abc,UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from,
SUM(UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS,

(SELECT COUNT(HOTHLive.HOTHLiveUser.F0004_SUPEVENT.REFERENCE)
FROM HOTHLive.HOTHLiveUser.F0004_SUPEVENT

WHERE

HOTHLive.HOTHLiveUser.F0004_SUPEVENT.OPENDATE = UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from and HOTHLive.HOTHLiveUser.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY HOTHLive.HOTHLiveUser.F0004_SUPEVENT.OPENDATE) as HOTH

FROM UK0QW0004ALIAS.ccmdb1.dbo.agent INNER JOIN UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur
ON UK0QW0004ALIAS.ccmdb1.dbo.agent.agent_id = UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.agent_id

where UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from ='2014-01-01'and UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.agent_grp_id='16'

GROUP BY datepart(day,UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from), UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from
HAVING (sum(UK0QW0004ALIAS.ccmdb1.dbo.acd_grp_conv_call_cnt + UK0QW0004ALIAS.ccmdb1.dbo.acd_dir_conv_call_cnt)) <>'0'

) A

OLD SCRIPT THAT WORKED :

Select
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From (SELECT COUNT(ccmdb1.dbo.agent.agent_name) as abc,agent_activity_cur.date_from,
SUM(agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS,

(SELECT COUNT(supportdeskdev.suppdeskdev.F0004_SUPEVENT.REFERENCE)
FROM supportdeskdev.suppdeskdev.F0004_SUPEVENT

WHERE

supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE = agent_activity_cur.date_from and supportdeskdev.suppdeskdev.F0004_SUPEVENT.CUSTOMERREF <>'Premium'

GROUP BY supportdeskdev.suppdeskdev.F0004_SUPEVENT.OPENDATE) as HOTH

FROM agent INNER JOIN agent_activity_cur
ON ccmdb1.dbo.agent.agent_id = agent_activity_cur.agent_id

where agent_activity_cur.date_from =${ds} and agent_activity_cur.agent_grp_id='16'

GROUP BY datepart(day,agent_activity_cur.date_from), agent_activity_cur.date_from
HAVING (sum(acd_grp_conv_call_cnt + acd_dir_conv_call_cnt)) <>'0'

) A

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-10-09 : 12:44:25

What is the security context used within the linked server definition (Mapped logins, current user login, specific login)? When you click the "Test Connection" option of the linked server, does it succeed? What is the security context when you get the error?

No amount of belief makes something a fact. -James Randi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-09 : 12:58:47

The problem is here:
SELECT COUNT(HOTHLive.HOTHLiveUser.F0004_SUPEVENT.REFERENCE)
FROM HOTHLive.HOTHLiveUser.F0004_SUPEVENT
WHERE HOTHLive.HOTHLiveUser.F0004_SUPEVENT.OPENDATE = UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from and HOTHLive.HOTHLiveUser.F0004_SUPEVENT.CUSTOMERREF <>'Premium'
GROUP BY HOTHLive.HOTHLiveUser.F0004_SUPEVENT.OPENDATE

You can't refer to UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur in there since it's not included in the query.

I've formatted your code to make it readable for others to assist:


Select 
abc
,date_from
,ACDCALLS
,HOTH
,Cast((HOTH/cast(ACDCALLS as Numeric(6,3)))*100 as Int) as Percentage
From 
(
	SELECT 
		COUNT(UK0QW0004ALIAS.ccmdb1.dbo.agent.agent_name) as abc,
		UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from,
		SUM(UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.acd_grp_conv_call_cnt) AS ACDCALLS, 
	(
		SELECT COUNT(HOTHLive.HOTHLiveUser.F0004_SUPEVENT.REFERENCE) 
		FROM HOTHLive.HOTHLiveUser.F0004_SUPEVENT
		WHERE HOTHLive.HOTHLiveUser.F0004_SUPEVENT.OPENDATE = UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from and HOTHLive.HOTHLiveUser.F0004_SUPEVENT.CUSTOMERREF <>'Premium'
		GROUP BY HOTHLive.HOTHLiveUser.F0004_SUPEVENT.OPENDATE
	) as HOTH
	FROM UK0QW0004ALIAS.ccmdb1.dbo.agent 
	INNER JOIN UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur
	ON UK0QW0004ALIAS.ccmdb1.dbo.agent.agent_id = UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.agent_id
	where UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from ='2014-01-01'and UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.agent_grp_id='16'
	GROUP BY datepart(day,UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from), UK0QW0004ALIAS.ccmdb1.dbo.agent_activity_cur.date_from
	HAVING (sum(UK0QW0004ALIAS.ccmdb1.dbo.acd_grp_conv_call_cnt + UK0QW0004ALIAS.ccmdb1.dbo.acd_dir_conv_call_cnt)) <>'0'
) A

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

emailuser
Yak Posting Veteran

74 Posts

Posted - 2014-10-10 : 04:01:02

Hi , when i do a testy connection it works fine , also if i do a baasic select * from UK0QW0004ALIAS.ccmdb1.dbo.agent.agent_name i do get data back , i am running all the scripts from the management console on my server UK0QNt003 ...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-10 : 12:37:49

Like I said, the problem is a code issue and I showed exactly which part is the problem. You can't refer to the table in that specific portion of the code since you haven't joined to it. Add a join in there.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources