January 23, 2009 at 10:52 am
*I apologize for the duplication, I posted this in the 'Replication' forum as well, but was not sure if that was the right location.*
Hello,
Facts: I have a large SQL 2000 table of sales leads. Each lead has an associated Sales Associate field and a Sales Manager field (the managers manage the associates). Sales Managers are constantly changing. The database is not transactional – the data in the table is populated and distributed every night.
Here are the steps I need to accomplish:
1.Create an Access database for each Sales Manager listed.
2.Copy all the leads for each Sales Manager in the SQL 2000 table into the Sales Manager’s Access Database.
Thank you for your time!
January 23, 2009 at 10:56 am
DTS might be able to do something like that.
But I have to ask, why Access databases? Why not give the sales managers some sort of access to the SQL database?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 23, 2009 at 10:59 am
Thank you for your response, those are great questions.
The simple answer is because I need the information downloaded to the Manager's laptops in the mornings. They go on the road and do not always have access to the network. Having the information stored locally on the laptop gets around that problem.
January 23, 2009 at 2:27 pm
Have you thought about using replication, and setting up SQL Express on the laptops? It's going to be easier than constantly creating and synching Access databases.
If you have to go with Access, you have to, and DTS is going to be your best bet on that one. I know how I'd do that in SSIS, but I'm not as good with DTS as I am with SSIS. Even so, I'm sure it can be made to do that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 26, 2009 at 2:32 pm
I could give it a try in SSIS if you have an idea. I have not used it at all, but I am willing to try anything at this point...
January 26, 2009 at 4:00 pm
You really should be looking at using SQL Server Express and replication. That would be the better option all around and would be my recommendation.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 26, 2009 at 4:04 pm
Hi Jeffrey,
Thank you for the suggestion, unfortunately at this time it is not an option.
Think of it as an exercise...
How to break a table up into smaller tables in Access databases based of values in field in the table.
January 26, 2009 at 4:08 pm
Why is using SQL Server Express not an option?
January 26, 2009 at 4:14 pm
Well, I am not familiar with DTS and only have limited knowledge and capability with SSIS. I still think using SQL Server Express is your best option and have to wonder why it is not available as an option.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 26, 2009 at 4:19 pm
It would require the software to be installed on the user's computer correct?
January 26, 2009 at 4:27 pm
Both solutions require that software be installed on the users systems. Either SQL Server Express or Access. The difference between the two solutions is simple, who does the replication. You through SSIS (DTS) or SQL Server (Replication).
January 26, 2009 at 4:31 pm
Therein lies the problem, Access is standard on the profiles of my users. SQL express is not. I have to operate within the existing framework of my users' computers. Otherwise I would be stuck in my large organization's bureaucracy and it could take years to get my program out.
January 26, 2009 at 4:54 pm
If part of that is cost, remember that SQL Server Express is free. Using replication reduces the amount of "plumbing work" you need to develop to put the data on the users laptops using Access. If you are using Access, what do you do if the users laptop is turned off or not currently attached to the network when the data is ready to be sent? You can easily handle this with Replication.
I would definitely look a little more into this before diving into the process you are currently contemplating. You never know, if you present using SQL Server Express and Replication to your management, and the differences between the two methodoligies, you make get it out sooner than you think.
January 26, 2009 at 4:56 pm
I'll definitely keep that in mind and try to pursue that avenue.
But if it did have to be Access, how would you go about doing it?
January 26, 2009 at 5:02 pm
Very carefully.
Using SSIS, the process would be encapsulated in a FOR EACH container (not exactly sure how i would configure) and within that I'd have the necessary "code" to connect to each Access database, truncate the existing data (if needed), select the correct data from the source table and push the data to the Access database table.
That is a 30,000 foot overview of what I'd do. the details, I have to leave that to you at the moment. You need to handle the various errors that can occur, such as the laptop isn't there, or your process doesn't have permissions to access the laptop, etc.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply