Executing a SP on remote SQL Server and OPENROWSET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

Executing a SP on remote SQL Server and OPENROWSET

Author

Topic

ArviL
Starting Member

25 Posts

Posted - 2010-11-08 : 07:06:12

HI

I have 2 different servers.
1) SQL Server 2005 (64 bit), let's name it Sql
2) SQL Server 2005 Express (32 bit), let's name it SqlExpress

On SqlExpress I have a stored procedure sp_TestQuery in database SqlExpressDB, which queries data from some dbf-table (appropriate ODBC driver is installed, Allow Inprocess = True)
The script is somethink like:
---
USE SqlExpressDB

DECLARE @Employees TABLE(
tabn varchar(15),
forename varchar(50),
surname varchar(50)
)

INSERT INTO @Employees (
tabn,
forename,
surname
)
SELECT * FROM OPENROWSET(
'MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\Dataserver\SourcePath\;
SourceType=DBF;
Exclusive = No;
Collate=Machine;
NULL = NO;
DELETED = YES;
BACKGROUNDFETCH=NO
',
'SELECT tabn, forename, surname FROM SourceTable')
...
---

I want to execute this SP from server Sql (really I want to execute it as a job, but currently I test the script as stored procedure).
I have a user LinkedServerUser created in SqlExpress. LinkedServerUser has datareader, datawriter and public permissions for database SqlExpressDB.
In server Sql I have defined a linked server SqlExpress (Data Access = True; Rpc = True; RpsOut = True; Login = LinkedServerUser).
For SqlExpress, Ad Hoc Remote queries are enabled. For registry key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstanceName\Providers and subsequent MSDASQL key the DWORD's DisallowAdhocAccess=0 are added.

For testing, I creted a stored procedure in server SQL. The script is like:
---
USE Sql

Exec [SqlExpress].SqlExpressDB.dbo.sp_TestQuery;
---

The stored procedure returns an error:
'Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
...
The OLE DB provider "MSDASQL" for linked server "(null)" reported an error. The provider did not give any information about the error.'

When executed directly from SqlExpress, then the procedure sp_TestQuery executes normally.
When I execute from Sql a stored procedure in SqlExpress, which doesn't query data from external source, then it executes also normally.

Any ideas?
Thanks in advance!

ArviL
Starting Member

25 Posts

Posted - 2010-11-08 : 08:03:21

Sorry, but sp_TestQuery is executed as LinkedServerUser - and LinkedServerUser queries the remote database SourceDB too. But LinkesServerUser isn't a domain user, it is for internal use in SqlExpress only. So I think I have to create some special domain user which has access to SourceDB and use it instead of LinkedServerUser.

ArviL
Starting Member

25 Posts

Posted - 2010-11-08 : 08:31:19

I replaced security context for linked server SqlExpress with logins current one - as I logged in with my domain administrator account, the linked server SqlExpress must have all needed rights. When executing the stored procedure, I got the same error.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources