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 2008 Forums
 Transact-SQL (2008)
 Schedule Job

Author  Topic 

fedupjohn
Starting Member

6 Posts

Posted - 2013-12-04 : 07:27:12
I am extracting records from a dBase.dbf file from a restaurant POS and inserting them into a table

The following script works perfect manually.
My test DBF has about 7000 records in it and it extracts and inserts them all.
I am trying to schedule it as a job to run daily after the restaurant closes.

I have tried every which way to make it happen, I know I'm missing something that's I am posting this for the experts and asking for help.

INSERT INTO [My_Database].[dbo].[My_Table]
([Name]
,[Member_No]
,[Company]
,[Contact]
,[Address1]
,[Address2]
,[City]
,[State]
,[Zone]
,[Full_Phone]
,[Ext]
,[Fax]
,[Email]
,[Last_Order])
select
NAME,
MEMBER_NO,
COMPANY,
CONTACT,
ADDRESS1,
ADDRESS2,
CITY,
[STATE],
ZONE,
FULLPHONE,
Fax,
EXT,
EMAIL,
LAST_ORDER
from [Linked_Server]...[dbf server]

This all takes place on the restuarant's local host.

Thanks
John



John Hornak

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-04 : 07:37:25
refer to link below on how to schedule a job

http://technet.microsoft.com/en-us/library/ms191439%28v=sql.105%29.aspx


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-04 : 07:49:02
While scheduling the job, make sure the permissions are correct. i.e. job has permission to access both servers.

djj
Go to Top of Page

fedupjohn
Starting Member

6 Posts

Posted - 2013-12-04 : 08:29:57
Your right, it's failing to load in the link to the dbf folder.
Thanks

quote:
Originally posted by djj55

While scheduling the job, make sure the permissions are correct. i.e. job has permission to access both servers.

djj



John Hornak
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-12-04 : 09:18:03
Glad I could help.

djj
Go to Top of Page

fedupjohn
Starting Member

6 Posts

Posted - 2013-12-08 : 14:02:18
quote:
While scheduling the job, make sure the permissions are correct. i.e. job has permission to access both servers.

Unfortunately the other "Server" is a file folder [F:\Data]with DBF files in it. I have spent the last 2 days trying every security setting there is to no avail. All this is on a Desktop Local Host. If you have any ideas I am open to suggestions.

John Hornak
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-09 : 01:46:54
this is how i did in a similar scenario

http://visakhm.blogspot.in/2013/08/importing-dbf-files-to-ssis.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

fedupjohn
Starting Member

6 Posts

Posted - 2013-12-15 : 09:12:10
Thanks for the help. I ended up creating a package using BIDS and that did the trick. Your link did the job.

John Hornak
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-12-16 : 04:04:20
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
   

- Advertisement -