SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Schedule Job
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fedupjohn
Starting Member

USA
6 Posts

Posted - 12/04/2013 :  07:27:12  Show Profile  Reply with Quote
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)

Singapore
17642 Posts

Posted - 12/04/2013 :  07:37:25  Show Profile  Reply with Quote
refer to link below on how to schedule a job

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


KH
Time is always against us

Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 12/04/2013 :  07:49:02  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 12/04/2013 :  08:29:57  Show Profile  Reply with Quote
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

USA
337 Posts

Posted - 12/04/2013 :  09:18:03  Show Profile  Reply with Quote
Glad I could help.

djj
Go to Top of Page

fedupjohn
Starting Member

USA
6 Posts

Posted - 12/08/2013 :  14:02:18  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/09/2013 :  01:46:54  Show Profile  Reply with Quote
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

USA
6 Posts

Posted - 12/15/2013 :  09:12:10  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 12/16/2013 :  04:04:20  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000