最近在做公司平台,遇到下面的问题:
sql语句在程序和sqlserver的新建查询窗口中可以正确的执行,但是在视图中就出错,sql语句和错误如下:
sql语句:
select u.UserID AS CountTodayAccountID, u.UserID,u.UserName,
(SELECT COUNT( c.CustomerID) FROM dbo.Customer AS c where c.UserID=u.UserID
AND DATEDIFF(DAY, c.CreateDate, GETDATE()) = 0 AND c.CustomerState = 4) AS TrialAccounts /*今日试用*/
,
(SELECT COUNT(Amount)
FROM [172.20.67.236].ShtxFinance.dbo.AccountantFinal af
WHERE af.MID=u.UserAccount and (DATEDIFF(DAY, CreateDate, GETDATE()) = 0)
and IsChecked=1 ) as SuccefulAccount /*今日成单*/
,
(SELECT sum(Amount)
FROM [172.20.67.236].ShtxFinance.dbo.AccountantFinal af
WHERE af.MID=u.UserAccount and (DATEDIFF(DAY, CreateDate, GETDATE()) = 0)
and IsChecked=1
) as CollectionAccount/*今日到款认账*/
,
(SELECT COUNT(ID)
FROM dbo.TracingContent tc inner join Customer cut on cut.CustomerID=tc.CustomerID
where(DATEDIFF(DAY, tc.CreateDate, GETDATE()) = 0) and cut.UserID=u.UserID )
as TelAccount/*今日电话量*/
from VUserRole as u where u.RoleName='业务员' and u.Status=1
通过查资料,终于找到了解决方法。
解决方法:
建立与跨服务器的数据库的连接:sp_addlinkedserver
EXEC sp_addlinkedserver
@server='DBVIP',--被访问的服务器别名
@srvproduct='',
@provider='SQLOLEDB',
@datasrc="172.20.67.236" --要访问的服务器
EXEC sp_addlinkedsrvlogin
'DBVIP',--被访问的服务器别名
'false',
NULL,
'sa',--帐号
'shtx' --密码
测试一下:
select * from DBVIP.ShtxFinance.dbo.AccountantFinal
正确执行,结果如下:
在视图中再把上面的sql语句粘进去,注意把服务器名称换成连接时设置的别名,如下
保存,运行,OK了。