May 27, 2009 at 9:21 am
I have to create a DTS package to send data to a client on a nightly basis. The data has to be taken from two applications. One is Sql Server 2005 and the other is Access. How do I link the two so that I can pull data from both using a stored procedure? Is this the ideal method? The data in both databases changes every day, so importing doesn't seem like a good solution. I've tried using sp_addlinkedserver and sp_addlinkedsrvlogin, but I've had no sucess with them (these commands seem to work, but I'm not sure how to proceed from there).
May 27, 2009 at 9:35 am
Once you have the working linked server connection, you just need to reference the objects by using the four-part naming convention. For e.g.
SELECT * FROM MyAccessServer...SomeTable
[/code]
--Ramesh
May 27, 2009 at 9:38 am
I have a very similar problem at the moment.
What I'm planning to do is add the linkedserver, then use OPENROWSET to select some data from the access database into a temporary table, then join on that to my Sql Server table.
Something like
INSERT INTO #Temp (col1, col2)
SELECT accessCol1, accessCol2
FROM OPENROWSET (.. {connection string data}... )
if I remember correctly.
I'll have to dive into BOL for the correct syntax.
I'll post the working version when I get it right.
May 27, 2009 at 9:40 am
Tom Brown (5/27/2009)
I have a very similar problem at the moment.What I'm planning to do is add the linkedserver, then use OPENROWSET to select some data from the access database into a temporary table, then join on that to my Sql Server table.
Something like
INSERT INTO #Temp (col1, col2)
SELECT accessCol1, accessCol2
FROM OPENROWSET (.. {connection string data}... )
if I remember correctly.
I'll have to dive into BOL for the correct syntax.
I'll post the working version when I get it right.
See above. You don't need to use OPENROWSET, just the four-part naming convention shown.
May 27, 2009 at 10:20 am
SQL Server (and the Access db) are sitting on Windows Server 2008 (64 bit), and when I try to select from the linked server I get the following error message:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" has not been registered
A little googling tells me that this is a known issue for 64 bit OS's, but I thought that the MSDASQL was bundled with 2008? Am I missing something? MSDASQL is listed as a provider on that machine, so I really don't understand what's going on.
May 27, 2009 at 2:12 pm
Heres what I had to do to get an OPENROWSET query working in the end. (no need for the linked server)
exec sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO
SELECT LQ.[ORGROLE], LQ.[INACTIVE]
FROM
OPENROWSET (N'Microsoft.Jet.OLEDB.4.0',
N'C:\Temp\lqos_dataextract_20090210.mdb';N'admin';'',
[LQOS34_REF_ORGROLES]) AS LQ
If there is no Jet driver for 64-bit then this method won't work either.
With visual studio 2008, you can target an application to a 32-bit CPU - could there be an option like that for Win2008 Server?
May 28, 2009 at 7:30 am
Mark Harley (5/27/2009)
I have to create a DTS package to send data to a client on a nightly basis. The data has to be taken from two applications. One is Sql Server 2005 and the other is Access. How do I link the two so that I can pull data from both using a stored procedure? Is this the ideal method? The data in both databases changes every day, so importing doesn't seem like a good solution. I've tried using sp_addlinkedserver and sp_addlinkedsrvlogin, but I've had no sucess with them (these commands seem to work, but I'm not sure how to proceed from there).
Sorry if I'm missing something here, but you can import from MS-Access using DTS directly. So; import into SQL from Access using DTS, transform if you need to and export the results from SQL and, for good measure, why not send some emails, too? All in one DTS.
Where's the problem?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply