scheduling a data extract- stumped 1

[Ken011]

hello experts:

We have 2 remote servers. One hosts a sql server database and the other is an application server. They are both within the same firewall.

We would like to set up that extracts data from the sql server database to an Access data ( from remote server to another).

We would like this to happen 4 times a day (at 8:05Am, 1:05PM, 5:05PM and 1:05AM).

I am really stumped as to how to get started on this.

I would truly appreciate your assistance.

For some reason, when I tried to do this with dts, it would not work for me because I could not figure out how to schedule the extract 4 times a day using dts.

Thanks very much in advance.

 

[onpnt]

Set up a linked server entry to the access file.
Set up a DTS/SSIS (depending on SQL version)
Write a query to push the data to the linked server or use the import/export wizard to do this.
Add schedules matching your times to run daily

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[Ken011]

thanks for your quick response.

 

[onpnt]

No problem.

Just a little more detail....

Use the Jet provider to add the link to access.
The easiest way you can do that creation of the object is

Read sp_addlinkedserver (Transact-SQL)

EXEC sp_addlinkedserver 
	@server = N'LINKNAME', 
	@provider = N'Microsoft.Jet.OLEDB.4.0', 
	@srvproduct = N'OLE DB Provider for Jet',
	@datasrc = N'C:\db1.mdb'
GO

By default MS Access as Admin and the user account with no password. Unless you've setup workgroup security (or whatever it's called) your credentials for the login to the link will be

Read sp_addlinkedsrvlogin (Transact-SQL)

EXEC sp_addlinkedsrvlogin 
	@rmtsrvname = N'LINKNAME',
	@useself = N'TRUE',
	@locallogin = NULL, 
	@rmtuser = N'Admin', 
	@rmtpassword = NULL
GO

After that you can test things with

EXEC sp_tables_ex N'LINKNAME'
GO

This will return a list of tables on that linked server.

When you query on a access linked server the reference will be as
SELECT * FROM LINKNAME...TableName

Leave the database.owner out.

If the extract is straight forward you can create a proc and just execute that from a job. Otherwise you need to get into a DTS or SSIS package. You will schedule the runs in the job through the agent and can do as many times each day at you like. Just add a new schedule for each time




_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[Crowley16]

just a note:

I have found that at times, adding a linked server to access dbs can be quite tricky because the server refuses to see the access database, not really sure why, however I've managed to get around the problem by using different access drivers...

--------------------
Procrastinate Now!

 

[Ken011]

Thanks so much, onpnt.

It is hard to schedule 4 different times using dts. We have sql 2000.

 

[onpnt]

No problem.

Scheduling should really be the easy part of this. You do not schedule directly in the DTS. Once you finish creating your DTS package and then create your job all you need to do is right click the job and go to the schedules tab. You can then add as many scheduled times as you want

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[Ken011]

onpnt,

I have successfully set up the linkedserver, tested it and all works well.

What I would like, and I am struggling so far, is to setup the data extract to occur 4 times a day as I indicated earlier.

So far, I am not having any luck with the trigger I created.

Can you please spare a moment to help,please?

here is the trigger:

CREATE TRIGGER trExtractCal_UpdateToOtherServer ON ExtractCalendar AFTER INSERT
AS
    DELETE    CalExtract...ExtractCal
    INSERT    CalExtract...ExtractCal (ExtractDateTime,JuvenileInitials,FileNumber,caseNumber,HearingDateTime,HearingType,JudgeName,CourtRoom,CourtOfficer,AttorneyName)
    SELECT    ExtractDateTime,Jv,FileNumber,caseNumber,HearingDateTime,HearingType,JudgeName,CourtRoom,CourtOfficer,AttorneyName  FROM inserted
GO

 

[onpnt]

When did the trigger come into the picture?

Are you trying to schedule that as the script task?

_{____________ signature below ______________
You are a amateur developer until you realize all your code sucks.
Jeff Atwood}

 

[Ken011]

hi onpnt,

sorry for late response. I have been on the field
all week.

well, trigger came into the picture because as shamefule as it might sound, I could not figure out a way to use stored proc or query.

However, today, I came up with a stored proc and this is how it looks like:

--drop procedure transfercal
CREATE PROCEDURE TransferCal
AS
    DELETE FROM OPENQUERY(CalExtract,'SELECT * FROM extrCal') 
    INSERT INTO OPENQUERY (CalExtract, 'SELECT ExtractDate,Jitials,File,caseNum,HDate,Type,JudgeName,CRoom,O,Att FROM ExtrCal') SELECT ExtractDateTime,JuvenileInitials,FileNumber,caseNumber,HearingDateTime,HearingType,JudgeName,CourtRoom,CourtOfficer,AttorneyName FROM  ExtrCal 

--    INSERT    CalExtract... ExtrCal (ExtractDate,Jitials,File,caseNum,HDate,Type,JudgeName,CRoom,O,Att)
--    SELECT      FROM extrCal
GO

--EXEC TransferCal

When I manually execute this stored proc, it works but when I attempt to schedule a job to run it, I get the following error:

Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.

 

[AlexCuse]

Hi Ken,

A stored proc will work fine for you in this case.

In Enterprise Mangler open up the 'Management' folder and click the little + sign next the the 'SQL Server Agent' Icon.

Then, right click on 'Jobs', and select 'New Job'.

Give your job a name and description, then click on the 'Steps' tab.

Click 'new', give it a name, and ensure that in the 'type' drop down that Transact-SQL Script is selected.

In the command area, type 'execute dbName.dbo.usp_MySP' (replacing your names of course).

Then, once your step is added, you can go to the schedule tab, click 'change' button, and set up your recurring schedule as you see fit.

Hope this helps,

Alex


Ignorance of certain subjects is a great part of wisdom

 

[Ken011]

hi Alex,

I thank you for your response but I think you missed a part of it.

I have already done those steps you enumerated, however, I got the error I posted.

 

[AlexCuse]

Sorry about that Ken :-(

I think you might need to look at the permissions on the Login used by you to run the proc through query analyzer and permissions on the login used by the SQL Server agent. Sounds like something does not match up.

If you can't get it working, maybe a DTS package is the answer, as they seem to play a little more nicely with access.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Ken011]

hi Alex,

no problem at all. I truly appreciate the help.

dts package is what onpnt has suggested from the beginning but I am not sure how to use it with the code I posted. If you know how and would be kind enough to assist, I would truly appreciate it.

I have been stumped on this now for days.

 

[AlexCuse]

Hm, let me see. I don't have SQL 2000 installed on my PC at home, but I believe somewhere there is a tool called the 'import/export wizard'. As you don't seem to be doing any complex transformations, I think that a DTS package going this route would suffice.

You can run a query (on your access connection) to delete the data from the access db using an 'Execute SQL Task', and then set up whats' called a 'Data Pump Task' between your SQL and access connections. SOurce will be the select portion of your insert statement, destination will be the table in your access db.

For a ton of great info on DTS, check out this site. It has been invaluable to me:

http://www.sqldts.com

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Ken011]

Thanks again but I still have the permission issue.

I attempted to create a package.

Then I am asked to provide that will use that connection. So, I entered the same sql server permission that I have.
This permission was good enough to allow me permission to the database I am using but when I attempted to exec the query I am using, it failed because of permissions.

My question then is what account do I need for the linkedserver, is it the sql server account or the account for the remote server where the MS Access that I linked to SQL Server resides?

 

[AlexCuse]

You are getting way out of my element here. Are you trying to use OPENQUERY on the linked server in your DTS package? Because you shouldn't need to do that. If you have tried using the data pump, then I think you need to have a talk with your dba about what your account and sql server agent account has permission to do (agent account may not be able to use linked servers or distributed queries, or maybe it can't even access your DB).

I think you are starting to wander into the territory of forum962 , you may get a better informed response there.

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Ken011]

Thanks Alex for your time and assistance.

It was my understand that once I set up the linked server successfully, that the query and setting it up in dts package is the easy part.

So far, to me, that has been the troublesome part.

Onpnt, if you are weighing in on this, can you please assist?

I used openquery because I did a research and found a thread where someone was having the same
permission issue that I am and he was instructed to use openquery. He did and his problem was solved.

I tried openquery without the package but it didn't work.

If the dts package can work with just regular query such as Insert into...select from... then someone please let me know. This is getting very, very uncomfortable.

 

[AlexCuse]

dts package would work with a select query, and use the 'data pump task' to insert the select query results into your table. It is not purely T-SQL.

Openquery may work sometimes, but not always depending on setup. Some dba's will disallow that option. Have you tried the import/export wizard yet?

Ignorance of certain subjects is a great part of wisdom

 

[Ken011]

After your last thread last night, I decided to try the export wizard option.

It seemed like it was going to work till 3/4 quarters before the task was completed, I got an error that said, "invalid pointer".

I have not had time to research what that means.

I want to point out here while using the export wizard, I selected the "Use Query" option. This way, I can select only the column names that I need.