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
 Transact-SQL (2005)
 Executing a pass-trough query on remote server

Author  Topic 

ArviL
Starting Member

25 Posts

Posted - 2012-09-14 : 02:31:48
Hi

I need to mirror some dbf-tables on MS SQL 2005 Server (64b). As 64b SQL Server doesn't have a Foxpro ODBC driver, we installed a 32b MS SQL Express Server for this purpouse only, and defined an according Linked Server on 64b SQL Server.

On SQL Express Server following query works properly:

SELECT * FROM OPENROWSET(
'MSDASQL',
'Driver=Microsoft Visual FoxPro Driver;
SourceDB=\\SourceServer\SourcePath\;
SourceType=DBF;
Exclusive = No;
Collate=Machine;
NULL = NO;
DELETED = YES;
BACKGROUNDFETCH=NO
',
'SELECT tabn, isikukood, eesnimi, perenimi, allyksus, ametikoht, meile_tool, lahkunud FROM kaader')

(I couldn't use a linked server for query, because if I remember correctly, through linked server I could query dbf-tables only exclusively, so only MSDASQL was an option)

The problem is, that SQL Qxpress Server doesn't have Jobs feature, so I can't use automation there. I hoped that calling the SQL Express stored procedure or even better a Table function from 64b SQL Server will be a solution, but so long I can't get it to work.

I can call a stored procedure from 32b Server, when the procedure operates with data on 32b Server only, but any attempt to run a pass-through query there results in error:
Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".

I also tried "automation" on SQL Express server using windos Task Sheduler and command:
sqlcmd -s .\SQLExpress -i c:\expressmaint.sql –d "MyDB" -E
, but I get the error:
"Login failed for user Domain\UserName"
(UserName has administrator rights on domain, and is creator for MyDB)

Having some bright ideas here anybody?
Thanks in advance!

Arvi Laanemets
   

- Advertisement -