February 23, 2005 at 1:29 pm
Hi All,
Here's what I'm trying to do:
Read data from a remote, hosted (source) SQL Server and load it into a hosted (target) SQL Server. (Hosted means that the SQL Server belongs to a web hosting company, so I only have limited access to the servers. So this code will be called from a web page.)
Both SQL Server's are SQL Server 2000.
DTS is not an option, because the target server DTS packages (on the hosted SQL Server) are available to all database users and I don't have the rights to create a scheduled job on that server.
This is a shortened version of the query with variables expanded and disguised IP and login information.
INSERT INTO tablename ( columns ... )
SELECT columns ...
FROM OPENROWSET('SQLOLEDB'
, 'Provider=SQLOLEDB;Persist Security Info=False;
Network Library=DBMSSOCN;Data Source=1.1.1.1,1433;
Initial Catalog=dbname;User ID=user;Password=pw;
Connect Timeout=5;Pooling=False'
, 'SELECT * FROM tablename')
WHERE columnname LIKE '%Members%'
When I run the SQL statement in Query Analyzer against a local SQL Server on my network, the code works flawlessly.
I've tested the connection string from a hosted site running ASP and the connection string is able to access the remote SQL Server, but in that case, I am doing a simple SELECT statement in the ASP code and not the OPENROWSET statement.
I have no problems connecting to the remote server in either Enterprise Manager or Query Analyzer.
I can create a linked server from my local server to the remote server, but I don't have permissions to do this on the hosted server.
If I connect from a replica of the hosted SQL Server database on my local server in Query Analyzer and execute the code with the OPENROWSET statement in it, I get this error:
-------------------------------------------------------------------------
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: Invalid authorization specification]
[OLE/DB provider returned message: Invalid connection string attribute]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
IDBInitialize::Initialize returned 0x80004005: ].
-------------------------------------------------------------------------
I have tried removing each connection string attribute from the OPENROWSET statement, but it reports the same error.
If I change the connection string to connect to a replica of the remote database on my local server, the statement works. It also works in the web scripting page when I connect to a replica of the remote
database on my own server using the OPENROWSET syntax.
The issue is reading data from a remote SQL Server using an OPENROWSET connection string. The plan is for a once-a-day, scheduled task to perform updates from a remote SQL Server to the target site's SQL Server. I don't have access to the remote SQL Server or the remote site.
It seems to me that this is exactly what OPENROWSET is intended for -- when you can't create a linked server.
Has anyone done something similar successfully?
--
Richard Michael
http://www.mainstreetcentral.com/
Where your local community and the Internet meet!
February 23, 2005 at 4:30 pm
It worked for me sometime ago.
My test indicates connection string may be the problem.
Try simplify it with
FROM OPENROWSET('SQLOLEDB','1.1.1.1,1433';'user';'pw','SELECT * FROM dbname..tablename')
February 24, 2005 at 8:05 am
Make sure that the DisAllowAdHocAccess registry setting is set to 0 (zero) for whichever OLE DB Provider that you are using.
From Microsoft web....
"... with the DisallowAdHocAccess property set to 1 for a specific OLE DB provider, you must use a pre-defined linked server setup for the specific OLE DB provider; you can no longer pass in an ad hoc connection string that references that provider to the OPENROWSET or the OPENDATASOURCE function. "
A.J.
DBA with an attitude
February 24, 2005 at 1:54 pm
I was not aware of the DisallowAdHocAccess property, but I don't believe it is set because I can connect using ASP with a connection string.
Also, I don't have access to the server to check server settings.
February 24, 2005 at 2:01 pm
Truly amazing.
Separating the three main components of the connection string into individually quoted strings separated by semi-colons works.
I was pretty sure I had tried that in earlier attempts, but I was going through so many combinations, I no longer remember?
Thanks for putting a fresh set of eyes on this.
I'm still a little curious, however, why the alternative syntax of using a complete connection strings fails.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply