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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-02-28 : 08:19:31
|
| Anthony Ozogu writes "How do you code a stored procedure to pull a data from a table from a MS Access database format in a different dirctory, wipe out the table contents in the SQL server and then populate it with the data pulled from the MS Access database? Thank you." |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-02-28 : 11:41:15
|
| create proc test BEGINtruncate table northwind..employeesinsert into northwind..employeesselect * from openrowset('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Employees) ENDDidn't test this, but it should work.-ChadEdited by - chadmat on 02/28/2002 11:42:12 |
 |
|
|
efelito
Constraint Violating Yak Guru
478 Posts |
Posted - 2002-02-28 : 11:43:14
|
| This is fairly simple to do with a DTS package. Create a data source for the Access Database and one for the SQL Server. Create a SQL task to truncate the SQL table and add a transformation to copy the data from Access to SQL Server. Schedule the package and enjoy.Jeff BanschbachConsultant, MCDBA.... Doh. Missed that part about wanting an SP and got beat to the post! I need another Pepsi.Edited by - efelito on 02/28/2002 11:44:37 |
|
|
|
aozogu
Starting Member
4 Posts |
Posted - 2002-03-05 : 17:13:26
|
| Server: Msg 7399, Level 16, State 1, Procedure TEST, Line 6OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' reported an error. [OLE/DB provider returned message: Could not find installable ISAM.]I get this error message when I run the code belowUSE S8FREGGOCREATE PROCEDURE dbo.TEST ASTRUNCATE TABLE S8FREG..S8RegistrantsINSERT INTO S8FREG..S8Registrants SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','C:\ACCESS\S8FREG.MDB',S8Registrants)GO |
|
|
|
|
|
|
MS/Sql Strored procedure