| Author |
Topic |
|
ustreddog
Starting Member
10 Posts |
 Posted - 2010-01-27 : 07:41:15
|
| I need to write a number of store procedures that select some records from some tables and export the result set automatically to an access file. Is there any simple way to do it? |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-27 : 07:43:59
|
| How many Store procedures? |
 |
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 07:44:43
|
| around 75 |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-27 : 07:45:44
|
| That's not hard. |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 07:50:12
|
| how?? is there any sql statement like SELECT * FROM tableA OUTPUT "C:\abc.mdb"?? (I'm using SQL 2000) |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2010-01-27 : 07:58:29
|
| Have you tried Bcp or Export/Import wizard in SQL Server? |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 08:03:13
|
| I have tried BCP but it only generate CSV that cannot carry the data type to the access.I need to make it done using store procedures for the operators to run, that's why I can't simply did it using Export/ Import wizard |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-27 : 08:03:45
|
| Also try openrowsetMadhivananFailing to plan is Planning to fail |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 08:12:08
|
| I have used the following,insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'C:\abc.mdb;', 'SELECT * FROM tableA') select * from tableAand got this error,OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ] |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2010-01-27 : 08:17:40
|
| Make sure the MDB file exists in the Server's directoryMadhivananFailing to plan is Planning to fail |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 08:19:42
|
| C:\abc.mdb in the script means C: drive in the SQL Server machine, right? |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 08:28:28
|
| I checked there is the abc.mdb in the SQL server machine C drive. |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 08:49:00
|
| Anyone have any idea on it???? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-01-27 : 09:00:22
|
| "C:\abc.mdb in the script means C: drive in the SQL Server machine, right?"YesBut I don't know the answer other than that. You could perhaps make a Linked Server to your Access DB, and then you could just reference the Access tables with FROM MyAccessLink.SomeDatabaseName.SomeUser.SomeTable style syntax. No idea if this is possible, but I have used Linked Servers to communicate with Oracle and other databases that have ODBC drivers, so I presume Access would be possible too. If the FROM MyAccessLink.SomeDatabaseName.SomeUser.SomeTable synta doesn;t work you could also use OPENQUERY(LinkedServerName, 'SELECT ... qyery ...') |
|
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 12:40:59
|
| Can I do it using OPENROWSET? how can I test the connection to the mdb in the drive in the SQL server via 'Microsoft.Jet.OLEDB.4.0' using the Query Analyzer in my local?? |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
ustreddog
Starting Member
10 Posts |
Posted - 2010-01-27 : 20:50:59
|
| It's the client's request. I cannot control it. |
|
|
|
|
Export result to access