Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 544
  • Last Modified:

Creating a Script to schedule a data dump from SQL Server 2005 to Access

Hi there,
I have a db in SQL Server 2005 and would like to dump all the data from one table on to an Access db on the same computer. This job would be running twice, @ 8 AM/PM. What would be the best procedure to do this? Create a Job? Package in SSIS? I'm not that familiar with SSIS...
Also, the destination table has different field names than the source table, so mapping would probably come into play.
I'm a little comfortable with the insert statement, it's the scripting from SQL Server to Access is what I'm having trouble with...

Thanks for your input,
Classic
0
Classic1
Asked:
Classic1
  • 2
  • 2
1 Solution
 
RiteshShahCommented:
you can create one linked server and create one job which will dump your data from SQL Server to linked access table. here is one example to create linked server with Access

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html
0
 
Classic1Database AdministratorAuthor Commented:
Hi there,
Thanks for the link, the sp_addlinkedserver worked well! I was able to execute a select statement within SQL...

Now is there a chance I can do an insert statement to the linked Access db? I tried doing this, but I got this error: (This was executed in the actual db, not in the master environment.

Msg 215, Level 16, State 1, Line 1
Parameters supplied for object 'AutoSolLink...AuditTrail' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

I've attached the code as well..

Much appreciated,
Classic
Insert into [AutoSolLink]...AuditTrail ('Data1', 'Data2')
Select ADR.Reg_000, ADR.Reg_001
from MercuryAuditDRecords ADR

Open in new window

0
 
RiteshShahCommented:
run this

Insert into [AutoSolLink]...AuditTrail (Data1, Data2)
Select ADR.Reg_000, ADR.Reg_001
from MercuryAuditDRecords ADR

Open in new window

0
 
Classic1Database AdministratorAuthor Commented:
Thank you very much! I was able to insert the data successfully...much appreciated,
Classic
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now