sp_refreshview

Question

sp_refreshview

Naz Manek

SSC Journeyman

Points: 77
More actions
July 7, 2005 at 8:51 am

#86749

Hi
I'm using sp_refreshview as follows:
exec sp_refreshview MyView
MyView is a simple view as
CREATE VIEW MyView AS
select top 1 * from openquery (MyLink, 'select top 1 * from ch_test..v_pf')
If I execute the select by itself, I get data, but using the sp_refreshview, I get the following:
Server: Msg 7391, Level 16, State 1, Procedure X_locB_CURR, Line 11
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].
Can anyone point me in the right direction.
Thanks
Naz

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 1

There are a few causes to this but the most likely is MS DTC isn't running on one of the two SQL Servers. Can you verify that they are? Also, what operating system/operating system service pack?

K. Brian Kelley
@kbriankelley

Naz Manek SSC Journeyman Points: 77 More actions · Answer 2

Thanks for the fast response.

I have 2 servers, one in Switzerland (local) and the other in Jersey. Both are running DTC. Both servers are running SQL2000 on W2K.

Incidentally, I also have a test server locally, between the 2 local servers it works without a problem. Could this be a firewall issue, although I can connect to the Jersey server.

Thanks in advance for your help.

Cheers

Naz

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 3

It may be. How are you connecting to the SQL Server, Windows authentication or SQL Server login? How is the linked server connection specified to connect?

K. Brian Kelley
@kbriankelley

Naz Manek SSC Journeyman Points: 77 More actions · Answer 4

The linked server is setup to use "security context" and I supply a user name and password. As I mentioned, when I execute a simple select using the linked server it works. Reading about the error I have, I found an article that states port 135 should be opened. Could this be the case?

In Jersey, port 1433 had to be authorised for the server here in order to gain access. Does the DTC use port 135 instead?

Again, thanks for the response

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 5

Are you connecting to the local server using Windows authentication? If so, it'll attempt a Windows authentication connection to the other server. In that case, port 135 (and some others) need to be opened. However, before you go down that path, that exposes your SQL Servers on the internet to Windows based attacks that normally get blocked at the firewall. What may be better is to establish particular SQL Server logins on the remote server for users that need to make that linked server connection across and then set up those users' local security logins to use those SQL Server logins. For instance, you might set up YourDomain\YourAccount to map to a SQL Server login YourAccount on the remote server.

K. Brian Kelley
@kbriankelley

Naz Manek SSC Journeyman Points: 77 More actions · Answer 6

Hi

All connections to any server is based on a given SQL login. I have a login on the remote server which I use to connect using the SQL tools. The linked server is also set up to use the same account since we only need one user to have access. I'm able to access various authorised tables, views and SPs.

Again thanks for your help.

Cheers

K. Brian Kelley SSC Guru Points: 114704 More actions · Answer 7

Didn't forget about you. I'm looking at the following Microsoft KB article:

INFO: Configuring Microsoft Distributed Transaction Coordinator (DTC) to Work Through a Firewall (250367)

It does look like DTC requires port 135 (ugh) because it uses RPC calls. So even though you're using SQL Server logins, it does appear you're going to have to make that sort of connection. With this being the case, you may want to set up access lists within your firewalls to ensure that 135 can only be utilized between the two sites and also manually configure a port for DTC to use rather than a randomly selected one. That'll help minimize your exposure.

K. Brian Kelley
@kbriankelley

Naz Manek SSC Journeyman Points: 77 More actions · Answer 8

Hi

Thanks for the info.

Just to recap, the server now has SP4 installed for SQL. Port 135 is open and according to MS, we also need to change the registry as below :

Ports : REG_MULTI-SZ : 5000-5020

PortsInternetAvailable : REG_SZ : Y

UseInternetPorts : REG_SZ : Y

This is done and the firewall configured for the above ports.

I still have the same problem:-(

Now I'm thinking, does the user we have which logs on to the server have enough privileges? Obviously, we can't have a Windows user due to security. I would have thought that if the DTC needed to use any Windows system resources, MSSQL would handle that for the logged in user internally. I'm I barking up the wrong tree?

I have run out of ideas now.

Thanks