Solved

MS SQL Server Management Studio 2005 and MS Access 2003

Posted on 2011-09-13
14
224 Views
Last Modified: 2013-11-27
I am fairly new to using Servers --my problem is that I have 3 linked tables in an MS Access 2003 dbase.  I need to create 1 table in SQL Server dbase from these 3 linked tables.  The 3 linked tables are from an external Server database in which I have read only access. This 1 combined table must be updated from the 3 linked tables to my group's Server dbase. How do I go about doing this?
0
Comment
Question by:rfitzh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
14 Comments
 
LVL 4

Expert Comment

by:grogman
ID: 36530861
I have done this before by creating a job in SQL Server Agent that queries the tables in the MS Access DB, and imports the results into a table in SQL. Depending on how the data changes (for example, if existing records are often updated/modified), you might even consider having the job drop and then recreate the SQL table before importing the query data. If the changes are only additional records, you could bypass the drop/create steps, and just import the new records through the query and add the new data to the existing table. Set the job to run on a schedule that works for you.
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 500 total points
ID: 36530866
You can do it from Access, by linking to the combined table and then running the appropriate append queries.

You can also do it from Management Studio with TSQL statements.  You just need to link to the read-only servers (in Management Studio these are call linked servers)  or use the OpenRowSet statement to access the source tables.
0
 

Author Comment

by:rfitzh
ID: 36531337
Thanks for your responses.  

The tables are constantly being updated and I need to capture the updates. These tables are huge. I am using Management Studio 2005.

I understand that stored procedure is the way to go. How do I go about doing this?
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36531854
There are many ways to approach the job, depending on your requirements.  One of the most critical is how current your combined table needs to be and another is how much activity you have.  

On one end of the spectrum, you could create a view that logically combines the tables (or relevant parts of the tables) on the fly.  On the other end, technologies like replication are popular because they automatically take care of some of the insideous details that may not be obvious when you first try to roll-your-own.

Another important dynamic is whether you want to pull the data from your server or push the data from the others.  Each approach has it's pros/cons.  For example, if you want to pull the data, then it's no trivial matter to recognize a new record, much less an update or delete.

If you want to push the data, then the triggers are convenient for recognizing the changes, but those must be deployed on the other servers, which may/may not be possible.

You also have to factor in the implications of primary/unique key collisions.
oversimplified illustration of a stored proc.  But, I caution you the task is likely a couple orders of magintude more complicated than this suggests:

1. Define your linked servers to the 3 data sources
2. Write a proc that runs on a regular schedule to "sync-up" with the combined table.

Create Proc Sync-up
as
Insert into TableC
   Select * from Table1 where pk not in (select pk from TableC)
   Union all select * from table2 where pk not in (select pk from TableC)
   Union all select * from table3 where pk not in (select pk from TableC)

Delete from TableC
   where PK not in
         (Select PK from Table1
   Union all select PK from table2
   Union all select PK from table3
         )

Update TableC
    from TableC inner join Table1 on TableC.PK = Table1.PK
    set TableC.fld1 = Table1.fld1
        ,TableC.fld2 = Table1.fld2
 where TableC.fld1 <> Table1.fld1
      or  TableC.fld2 <> Table1.fld2


Update TableC
    from TableC inner join Table2 on TableC.PK = Table2.PK
    set TableC.fld1 = Table2.fld1
        ,TableC.fld2 = Table2.fld2
 where TableC.fld1 <> Table2.fld1
      or  TableC.fld2 <> Table2.fld2


Update TableC
    from TableC inner join Table3  on TableC.PK = Table3.PK
    set TableC.fld1 = Table3.fld1
        ,TableC.fld2 = Table3.fld2
 where TableC.fld1 <> Table3.fld1
      or  TableC.fld2 <> Table1.fld2







 
       






 

   


0
 

Author Comment

by:rfitzh
ID: 36538308
OK, on item 2 as you stated: Write a proc that runs on a regular schedule to "sync-up" with the combined table. --That should be doable.

I have decided to import the tables from the external Server into the Server database that I have write permissions.  Below is my query used in the SQL Server Import Export Wizard which I am saving as an SSIS package:
SELECT [IncidentNbr]
      ,[MailDate]
      ,[RecordNbr]
      ,[Description]
      ,[RevisedBy]
      ,[RevisedDate]
      ,[RevisedTime]
      ,[MailBy]
FROM [dbo].[IM_DescriptionLog]
WHERE [MailDate] is Not Null or
            [MailDate] <> Null

UNION ALL
SELECT [IncidentNbr]
      ,[MailDate]
      ,[RecordNbr]
      ,[Description]
      ,[RevisedBy]
      ,[RevisedDate]
      ,[RevisedTime]
      ,[MailBy]
FROM [dbo].[IM_DescriptionLog]
WHERE [MailDate] is Not Null or
            [MailDate] <> Null
UNION ALL
SELECT [IncidentNbr]
      ,[MailDate]
      ,[RecordNbr]
      ,[Description]
      ,[RevisedBy]
      ,[RevisedDate]
      ,[RevisedTime]
      ,[MailBy]
FROM [dbo].[IM_DescriptionLog]
WHERE [MailDate] is Not Null or
            [MailDate] <> Null;  

Once the package is saved I will try and see how to get to item 2 as you indicated.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36539879
Unioning those 3 tables is a good idea. However, I notice that you union the same table 3 times, which I do not think is right.

Also, it is not really necessary to import the three tables to union them. You can union them during the import to create a single table. I propose putting the Union statement in a view, which you can subsequently reference in your sync procedure. Something like this:

Create view CombinedDescriptionLog
as
Select ...  FROM [Server1][dbo].[IM_DescriptionLog]
union all
Select ...  FROM [Server2][dbo].[IM_DescriptionLog]
union all
Select ...  FROM [Server2][dbo].[IM_DescriptionLog]
go


Then, you can reference the view in your SSIS import or even skip the imort and reference it in the syncronization proc:

Insert into TableC
   Select * from CombinedDescriptionLog where pk not in (select pk from TableC)

Delete from TableC
   where PK not in
         (Select PK from CombinedDescriptionLog         )

Update TableC
    from TableC inner join CombinedDescriptionLog CL on TableC.PK = CL.PK
       

    set TableC.fld1 = CL.fld1
        ,TableC.fld2 = CL.fld2
 where TableC.fld1 <> CL.fld1
      or  TableC.fld2 <> CL.fld2










0
 
LVL 42

Expert Comment

by:dqmq
ID: 36539917
Also, it occurs to me that if you are not changing the data locally, then really you may not need to import or sync anything at all.  Just reference the view as if it were a local table.  It will draw data from the remote tables and merge the results into a local view.  Up-to-date at all times with no synchronization effort on your part.

The drawbacks:
  You do not really have a local copy, so if one of the remote db's is down, your application won't work.
  Access to the data will be slower because it is over the network

The advantages:
   As simple as it gets
   No sync required
 
0
 

Author Comment

by:rfitzh
ID: 36542358
I like the view idea because it is simple.  I will try that first.  --Not able to work on it today, but all options you presented seems viable.  --Will work on and once complete will award the points.

Thanks dgmg
0
 

Author Comment

by:rfitzh
ID: 36542517
One further note. --the 3 tables are from different regions and they all have the same name and fields.  The databases all have the same name except the initials of the regions at the end of it.  So it is correct as stated in the Import/Export query option.

Thanks,
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36544396
My concern was about the SQL you provided for the SSIS package. Unless I'm missing something, it doesn't identify the region!  So, whatever region it defaults to, you will pull that table 3 times.  

Your FROM clause needs to look something like:

 FROM [servername].[dbo].[IM_DescriptionLog]


[servername] is what distinquishes the region.
0
 

Author Comment

by:rfitzh
ID: 36557592
Yes, you are correct.  The 3 tables are from 3 different databases.  The only difference is the Region code initials attached to the name of each dbase. The Import/Export Wizard does not allow for the name of the dbases in the query (the dbase selection is already made on the previous menu).  Again you are correct I need to include the name of the dbase in my query if I am including all 3 tables.

It seems that I will need 3 SSIS packages and then combine into 1 table after that point. I am not sure how that is done but I will be working on it first thing tomorrow a.m (Eastern Standard Time) --Will update on status.
0
 

Author Comment

by:rfitzh
ID: 36709313
Sorry, for late response working on a number of projects.

Ok, I have now created the SSIS package via SQL Server Business Intelligence Developement Studio. I used one query to pull the data from each region's database and saved as 1 SSIS package. Now, I need to complete by setting up the job to run this package daily.  This is where I need some help.  Do I need to use the SQL Server Agent to do this or can this be done without the Server Agent?  How do I go about doing this?

Thanks
0
 

Assisted Solution

by:rfitzh
rfitzh earned 0 total points
ID: 36967731
Resolved as follows:
1)  Created SSIS package via SQL Server Business Intelligence Developement Studio. I used same query (3 times - 1 time each region) to pull the data from each region's database and saved as 1 SSIS package;

2)  Set up job via 'Scheduled Task'  to run overnight from my machine

Will award points now - the above solution from dgmg is acceptable if using SQL BI Dev Studio and the SQL Server Agent option.

Thanks,
0
 

Author Closing Comment

by:rfitzh
ID: 36991214
Solution was partially correct as the SQL Server Agent option is not available on my department's Server; also the problem involved 3 different databases for retrieving the data.  This necessitated using SQL Server Business Intelligence Development to set up the SSIS Package and Scheduled Tasks to set up the job to run overnight.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question