OPENDATASOURCE Question
-
I'm using opendatasource to centrally table-drive a number of stored procedures. The SPs on remote servers use tables in a 'DBA' database (all using specific SQL logins with access to specific tables, etc...) to receive "parameter" data to to update runtime information.
On the remote servers I used for test this worked great:
select * from OPENDATASOURCE('SQLOLEDB', 'Data source=MyServer;User ID=xyz;Password=xyz).MyTargetDb.dbo.MyTargetTable
also to modify runtime data:
INSERT INTO
OPENDATASOURCE('SQLOLEDB', 'Data source=MyServer;User ID=xyz;Password=xyz).MyTargetDb.dbo.MyTargetTable
(SQLServer) Values (CONVERT(varchar(255), SERVERPROPERTY('servername')))
When I did some further testing on some other servers I get the following error:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: The provider did not give any information about the error.].
Both SQL Servers "appear" to be configured identically. The error appears instantly (not a timeout issue - though I did configure the 'remote login timeout' to infinite for testing):
sp_configure 'remote login timeout', 0
go
reconfigure with override
go
I was able to manually and programatically add a linked server to the SQL Server (not what I want to do long-term).
=======================
Does anyone know of some SQLOLEDB version issue or some other configuration setting that would allow the use of opendatasource on some servers and not on others?
Thanks!
Glenn
-
As I mentioned... I did successfully try a linked server. However, my preference is to use the opendatasource. One reason is for using the opendatasource and/or openrowset (imho) is that linked servers are not easily transportable should a database be moved to another server. Also, opendatasource and openrowset allow the SP code to encapsulate the remote connection information.
So: any suggestions for getting opendatasource to work???
-
I used to get that error.
it was fixed after i ran this:
sp_configure 'remote query timeout', 0
go
reconfigure with override
go
Alex S -
Alex's method will probably work for you, as it's the other timeout that usually is behind this, while you've set the first one already.
If not, you might try setting trace flag 7300 (and 3604 if needed for your situation) and then reporting back the new error you get. Standard trace flag usage warnings apply.
-
Well - I had already tried the 'remote login time' (in the original post) - which didn't work. I just tried Alex's 'remote query timeout' - which also did not work (same errror). In conjunction with trying Alex's suggestion I did do the dbcc traceon (7300, 3604). Unfortunately this didn't provide any other information aside from the original error message (which is virtually useless for diagnosing the problem).
btw... the server on which I'm trying the opendatasource is SQL 2000 SP4.
Any further suggestions continue to be very appreciatede!!!
Glenn
-
Glenn is there more than 1 instance of SQL or an instance of other RDBMS on the box?
Alex S -
The configuration for "target SQL Server" (the server having the DBA dataqbase/tables being read/updated remotely) is: 1 instance of SQL Server 2000 and 1 instance of SQL 2005.
I've been able to successfully access the target server/database from:
- sql 2000 clustered instance
- sql 2000 single instance
- sql 2000 virtual (single instance)
- sql 2005 virtual (single instance)
- sql 2005 w/ multiple instances
It does not appear to be linked to the SQL server configuration (though I'm still wondering about the SQLOLEDB).
Also I've versified that this problem is not linked to the Registry entry: DisallowAdHocAccess (as the sql servers that work with opendatasource have the same DWORD settings as those that don't word with opendatasource).
This continues to be a real mystery... If I didn't have sooo many SQL servers to deal with I'd go ahead and create linked servers but really - using the opendatasource is the most convenient/effecient way to go (if it works).
Thanks again!
Glenn
-
Did you check that MSDTC is "ON" on the server where it fails ?
* Noel -
Noel - thanks for the suggestion.
The Distributed Transaction Coordinator service is set for Auto-start on all of the SQL Servers and I did verify that the service is currently running on those servers that are failing with the opendatasource.
Glenn
-
I am not only asking for the service to be "ON" but also accessible from the network
http://support.microsoft.com/kb/817064
* Noel -
I would assume that since the majority of servers (both 2000 ans 2003) can access the target server (Windows 2003 SP1/SQL 2000 SP4) via opendatasource that DTC is accessible on the network.
That being said... I did read through the article that you supplied and verified that DTC is configured as specified for network access.
Thanks
-
The reason I asked is that when you use OPENDATASOURCE or OPENROWSET to "read" (SELECT statement only) you *don't* start a distributed transaction. Which is *not* the case when you invoke DML (INSERT,UPDATE,DELETE)!!!
Now that you have verified the configuration my next "guess" is that there is a firewall or router in between the servers which may be blocking MSDTC access.
Can you confirm that you can "READ" *AND* not "WRITE" ?
* Noel -
By the way If that is the case use the tool in the kb below to verify you can go through
http://support.microsoft.com/kb/306843
I have to leave now. I'll be back tomorrow, keep us informed of your results
* Noel
Viewing 15 posts - 1 through 15 (of 38 total)
You must be logged in to reply to this topic. Login to reply