Link to home
Start Free TrialLog in
Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL View not working

I have this view which connects to another database on another Server: -
SELECT        CustomerAccountNumber, AnalysisCode12 AS PCode
FROM            [DB1\SAGE200].ABC_LIVE.dbo.SLCustomerAccount AS SLC

This works fine, now I want to connect to another instance on DB1 the instance is 'DEF_Live': -
SELECT        CustomerAccountNumber, AnalysisCode12 AS PCode
FROM            [DB1\SAGE2002].DEF_LIVE.dbo.SLCustomerAccount AS SLC

This does not work or parse, anyone know why? Permissions?
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Don't know why it wouldn't parse, but as far as executing, the remote instance name is different:

FROM            [DB1\SAGE2002].DEF_LIVE.dbo.SLCustomerAccount AS SLC
Avatar of HKFuey

ASKER

Hi Scot, Yes there are 2 instances on that server.
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HKFuey

ASKER

OK, I tried adding that server as a linked server and I get 'Login Failed for user sa'. Even though I can connect with SSMS using the same credentials.
Avatar of HKFuey

ASKER

I can now add the linked server but the query still does not work, if I look at permissions to the Db I'm querying 'sa' is not there. (It does not allow me to add 'sa' either.