June 19, 2006 at 4:43 am
Hi
I have moved an application from one server_1 to Server_2
there is a job now on Server_2 that needs to access a view in a database on Server_1
--Recreate first clients table---
drop table tc_first_clients
select * into tc_first_clients from fop.dbo.vw_first_clients
Where fop is database on Server_1
This matter is further complicated as the view brings in data from another database on server_1
CREATE VIEW dbo.vw_First_Clients
AS
SELECT LEFT(AccountNumber, 1) + '.' + SUBSTRING(AccountNumber, 2, 4) AS CLIENTCODE, COUNT(ClientRef) AS LPCOUNT
FROM Main1st.dbo.CLIENTS CLIENTS
GROUP BY LEFT(AccountNumber, 1) + '.' + SUBSTRING(AccountNumber, 2, 4)
where main1st database is on server 1
Could some one advise how this might be possible
There is a SA password on Server_1 but not on Server_2
Any assistence would be appreciated
June 19, 2006 at 11:45 am
You need to implement a linked server. Check out BOL.
Also, it is a VERY bad idea to have server with no SA password.
June 20, 2006 at 8:16 am
It was not my idea to have no SA password on the server, it became my inheritance and because of access issues it was decided not to change
I ran the sp_addlinkedserver TC_2 command
and get the ansi error
I used
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
select * into tc_first_clients
from tc_2.fop.dbo.vw_first_clients
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
as a test command
and receive the following error
Server: Msg 7405, Level 16, State 1, Line 2
Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query.
Also what is BOL I apologise in advance if this is a stupid question and self evident
June 20, 2006 at 9:25 am
BOL = Books OnLine aka SQL help
The message is telling you exactly what to do.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
Also, my advice if you don't want to be fired when someone hacks your system or reads sensitive info is to change the SA password. Also, SQL2005 does not allow a blank SA password so you will not be upgrading until it is changed.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply