Solved

MS SQL Server Management Studio 2005 and MS Access 2003

Posted on 2011-09-13
14
214 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
  • 8
  • 5
14 Comments
 
LVL 4

Expert Comment

by:grogman
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:rfitzh
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now