Solved

Query Existing Data, Query New Data, Append New Data

Posted on 2007-04-02
22
1,654 Views
Last Modified: 2013-12-16
Scenario:  The scenario is our employees can attend some training at the vendors facility.  The training/test results are sent to us in an access mdb file.  We need to update our SQL database with the test results.  Any employee (userID) can take more than one course so there could be one employee with multiple titleID (course title).  There will not be duplicate userID/titleID pair.  testID is simple the date the test was taken and is paired with titleID.

Need:  We receive an access mdb file from a vendor (A) that contains records (userID, titleID, and testDate).  I need help with a query that will query our current database (B) and then query the new data (A) and update the current database (B) with any new values.  

Field Names:  userID, titleID, and testDate

We are using Coldfusion and SQL2000.  We have the .mdb file setup as a datasource so we can directly query the mdb file.  
0
Comment
Question by:dl8453
  • 11
  • 8
  • 3
22 Comments
 
LVL 8

Expert Comment

by:Julianva
ID: 18836289
It will be a lot easier if you created a staging database in SQL and then export the access data in the staging database. then wrote sql queries to update the data with new values.

Will all the columns have new values or just one or some of them ?
0
 
LVL 1

Author Comment

by:dl8453
ID: 18836356
Julianva:

Setting up a 'staging database' is a possibility.  

I'm not sure what you mean by "will all the columns have new values..."  We receive weekly updates.  Some of the data that comes to us may already have been sent to us in a previous data feed so we have to query it and remove the entries that have already been sent to us.  Otherwise, I'd just perform a query/insert and be done with it.  The training vendor doesn't want to make it easy on us.  :)  
0
 
LVL 1

Author Comment

by:dl8453
ID: 18836366
One additional note.  One approach I was looking at was to query the mdb file into a comma deliminated list and then writing an SQL script to take the values NOT in the comma deliminated list.  I know how to do this when I'm returning single values but since I need the userID AND the titleID, I'm not sure how to approach this.
0
 
LVL 8

Expert Comment

by:Julianva
ID: 18836444
That wont be a problem  to find the records that already exist. you can write an if exists query to first check if the records exist.
you mentioned update the current database with new values.
to me that is updating a specific value to a new value for that column that you get in your mdb file.
if column 1 had a value of  2 and the mdb file had a value of 6 then you would do an update.
Maybe i misinterpreted the question.

once you create your staging database - export your data from mdb to the database table then write your if exist query to insert records that dont exist in your live database to another table in your staging database then you can easily insert that new data in the live database.using DTS or just sql insert query.






0
 
LVL 8

Expert Comment

by:Julianva
ID: 18836463
Yes , you can do your matching of new records from access it self. then put those new records in a new table then insert into the sql live database.

0
 
LVL 1

Author Comment

by:dl8453
ID: 18836479
Sorry for the confusion, update is not the correct word, insert would be more appropriate.  I cant use a DTS as the updates will be occurring via buttons on a web page (I've got those worked out using Ajax).  Our server team wont allow us to execute DTS packages from a web server.  :(
0
 
LVL 1

Author Comment

by:dl8453
ID: 18860201
I left out that the datasources for the two data locations are not the same, one datasource source is an access mdb file and the second datasource source is an SQL server.  If I had more points to my name, I would bump my points up.  :)
0
 
LVL 1

Author Comment

by:dl8453
ID: 18860319
I thought I would offer this up in hopes of striking up some ideas...  Here is where I'm starting from... (I hope it pastes ok):

<!--- user_id is numeric but username has letters and numbers (AB1234) --->
<cfquery name="existingData" datasource="#ds_one#" >
      SELECT distinct
            user_id,
            username,
            active
      FROM ytd_user
      ORDER BY
            user_id
</cfquery>
<!---  this is where I'm having trouble --->
<cfset existingData_ids = ''>
<cfoutput query="existingData">
      <cfset existingData_ids = existingData_ids & existingData.user_id>
      <cfif existingData.recordCount is not existingData.currentRow>      
            <cfset existingData_ids = existingData_ids & ','>
      </cfif>
</cfoutput>
<!--- if I can get the above to work, then I think this will fall into place --->
<cfquery name="newData" datasource="#ds_two#" >
      SELECT distinct
            user_id,
            username,
            firstname,
            lastname,
            active
      FROM kpuser
      WHERE
            user_id not in (#existingData_ids#)
</cfquery>

0
 
LVL 52

Accepted Solution

by:
_agx_ earned 250 total points
ID: 18880210
dl8453,

Have you investigated whether you can use OPENROWSET, OPENQUERY or OPENDATASOURCE?  They can be used to query an Access database from within SQL Server.  You could pull the Access data into a #temp table and then do your inserts/updates on the live table.  Its not the best solution for every situation but it might be worth looking into.  

Here are a few examples. Caveat - The examples assume the correct drivers, permissions, etc are in place. Check the Books On Line - Transact-SQL Reference for details, restrictions and security recommendations.

--- access database location: c:\TheAccessDatabase.mdb
--- access database table: TestResults

--- 1) use a linked server
USE master
GO
EXEC sp_addlinkedserver
      @server = 'AccessTestResultsDB',
        @provider = 'Microsoft.Jet.OLEDB.4.0',
         @srvproduct = 'OLE DB Provider for Jet',
        @datasrc = 'c:\TheAccessDatabase.mdb'
GO

EXEC sp_addlinkedsrvlogin 'AccessTestResultsDB', 'false', 'sa', 'Admin', NULL
GO

USE YourSQL200DatabaseName
GO

SELECT       UserID, TitleID, TestDate
FROM       OPENQUERY
      (
      AccessTestResultsDB,
      'SELECT UserID, TitleID, TestDate FROM TestResults'
      )

GO


-- 2) use OPENDATASOURCE
SELECT  UserID, TitleID, TestDate
FROM        OPENDATASOURCE
      (
      'Microsoft.Jet.OLEDB.4.0',
       'Data Source="c:\TheAccessDatabase.mdb";
       User ID=Admin;Password='
       )...TestResults

-- 3) use OPENROWSET without an odbc datasource
SELECT       UserID, TitleID, TestDate
FROM       OPENROWSET
      (
      'Microsoft.Jet.OLEDB.4.0',
         'c:\TheAccessDatabase.mdb';'Admin';'',
      TestResults
      )

-- 4) use OPENROWSET with an odbc datasource
SELECT       *
FROM       OPENROWSET
      (
      'MSDASQL', 'TestResultsDSN';'Admin';'',
      'SELECT UserID, TitleID, TestDate FROM TestResults'
      )
0
 
LVL 1

Author Comment

by:dl8453
ID: 18882116
agx:

I don't know how to begin using this.  Looks interesting though.  Can you walk me through it?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 18883569
Understand they are 4 different examples of how to do the same thing: query an Access database from SQL Server.  If you can read the Access data using your sql server datasource, the rest should be easy.  

Try one of the simpler examples first (either #2 and #3).  Assuming you have the right permissions, they should work from either cfquery or in a Query Analyzer window.  Just change the path to the Access database (c:\YourAccessDatabase.mdb) and the table name (TheAccessTableName). If your Access database is password protected you will also need to change the password and possibly user id.

<cfquery name="TestData" datasource="YourSQLServer2000Datasource" >
   SELECT  UserID, TitleID, TestDate
   FROM    OPENDATASOURCE
          (
          'Microsoft.Jet.OLEDB.4.0',
          'Data Source="c:\YourAccessDatabase.mdb";
          User ID=Admin;Password='
          )...TheAccessTableName
</cfquery>

<cfdump var="#TestData#">
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 1

Author Comment

by:dl8453
ID: 18883740
#2 Gave me this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
and code for #2
<cfquery name="TestData2" datasource="#dbtraining#" >
   SELECT  result_id, user_id, au_sid
   FROM    OPENDATASOURCE
          (
          'Microsoft.Jet.OLEDB.4.0',
          'Data Source="\\servername\folder\Tables.mdb";
          User ID=Admin;Password='
          )...Assess_results
</cfquery>

<cfdump var="#TestData2#">
<hr>
#3 Gave me this error:
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 5: Incorrect syntax near 'Admin'.

here is the code for #3
<cfquery name="TestData3" datasource="#dbtraining#" >
   SELECT  result_id, user_id, au_sid
   FROM    OPENROWSET
          (
          'Microsoft.Jet.OLEDB.4.0',
          'Data Source="\\servername\folder\Tables.mdb",'Admin','',
          )Assess_results
</cfquery>
<cfdump var="#TestData3#">
0
 
LVL 52

Expert Comment

by:_agx_
ID: 18884292
Like I said there are some some restrictions. You can find a much fuller description of the requirements in the sql server Books On Line:
http://www.microsoft.com/downloads/details.aspx?familyid=a6f79cb1-a420-445f-8a4b-bd77a7da194b

I don't know much about your enviornment. Its possible that you may not have the permissions to use these functions. I would suggest doing a few tests, but first:

1. Do you have administrative (sa) access to the sql server database?
2. Do you have access to a tool like Enterprise Manager?
3. Do you have permission to make registry changes on the server hosting the sql server database?
4. Do you have permission to create ODBC datasources on the server hosting the sql server database?
5. Is it possible to copy the Access database to the server hosting the sql server database so you can use a local path like c:\theDatabase.mdb instead of a unc path?
0
 
LVL 1

Author Comment

by:dl8453
ID: 18884648
1. Do you have administrative (sa) access to the sql server database?
Yes.
2. Do you have access to a tool like Enterprise Manager?
Yes
3. Do you have permission to make registry changes on the server hosting the sql server database?
No
4. Do you have permission to create ODBC datasources on the server hosting the sql server database?
I can have an ODBC setup.
5. Is it possible to copy the Access database to the server hosting the sql server database so you can use a local path like c:\theDatabase.mdb instead of a unc path?
The file is already on the sql server.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 18884855
Regarding Answer #3 and #4

"BOL:   OPENDATASOURCE/OPENROWSET  can be used to access remote data from OLE DB data sources only if the DisallowAdhocAccess registry option is explicitly set to 0. When this option is not set, the default behavior does not allow ad hoc access."

You have to change a registry setting in order to use those functions. If the setting can't be changed then I don't think you can use OPENDATASOURCE/OPENROWSET.

Regarding Answer #5
Try creating a linked server.  Since both databases are on the same server, try using a local path instead of unc.

You can create a linked server using TSQL or using Enterprise Manager.  Using TSQL there are two steps 1) create the linked server and 2) set the permissions. You only have to do this once. After you create the linked server, you can call it your queries almost like a regular sql server table.


--- 1) create the linked server
USE master
GO
EXEC sp_addlinkedserver
        @server = 'AccessTestResultsDB',
        @provider = 'Microsoft.Jet.OLEDB.4.0',
         @srvproduct = 'OLE DB Provider for Jet',
        @datasrc = 'c:\TheAccessDatabase.mdb'
GO

-- 2) set the permissions
USE master
GO

EXEC sp_addlinkedsrvlogin 'AccessTestResultsDB', 'false', 'sa', 'Admin', NULL
GO

If it works you should be able to query the linked server like this:
 
SELECT       UserID, TitleID, TestDate
FROM       OPENQUERY
      (
      AccessTestResultsDB,
      'SELECT UserID, TitleID, TestDate FROM TestResults'
      )

GO




0
 
LVL 52

Expert Comment

by:_agx_
ID: 18891227
Using Enterprise Manager:  Security -> Linked Servers -> New Linked Server

[General Tab]
Linked server: (Enter any name like "AccessTestResultsDB")
Other Datasource: "Microsoft Jet 4.0 OLE DB Provider"
Product Name: (Empty)
Data source:   c:\TheAccessDatabase.mdb
Provider String: (Empty)

You may also need to set Security properties. Example

[Security Tab]
Local Login: sa
Impersonate: (Not checked)
Remote user: Admin
Remote Password: (Empty)

Click OK


You might also find this kb article helpful

HOW TO: Use the DisallowAdHocAccess Setting to Control Access to Linked Servers
http://support.microsoft.com/kb/327489
0
 
LVL 1

Author Comment

by:dl8453
ID: 18891452
_agx_

I'm meeting with the Server Team to get the Linked Table setup.  I spoke with them briefly and this will be the easiest way to do this and the easiest in terms of maintenance, too.  Then I'll be able to query both datasources and my problems will be solved.  :)

If anyone else comes across this page, as _agx_ stated, there are numerous solutions but I just accepted the one that I implemented.  My suggestion to you would be evaluate each one and determine which one is best for your application.  My server team is very easy to work with and accomodating.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 18891591
Glad you found a workable solution :) Thanks for adding the note about evaluating the available options. I agree that's very important.

Good Luck!
0
 
LVL 1

Author Comment

by:dl8453
ID: 18906379
_agx_ If you are still watching this, my SQL guys got the access db file setup as a linked server.  I can query the access db file via query analyzer just fine.  I don't know how to write the cfquery statement though.  Do I need to include a datasource now or what?

<cfquery name="openQuery" datasource="#dev#">
select * from OPENQUERY(UserData, 'SELECT id, uname, score FROM tRecords')
</cfquery>

datasource="#dev#"   == datasource name as setup in CF Server (Administrator)
UserData == DSN Name
tRecords == table withing the access.mdb file
0
 
LVL 52

Expert Comment

by:_agx_
ID: 18906701
Just take the sql you ran in the query analyzer and add your sql server datasource name.  

sqlserverdsn == name of datasource you normally use to query your sql server tables
UserData == name of the linked server created for the access database
tRecords == table withing the access.mdb file

<cfquery name="openQuery" datasource="#sqlserverdsn#">
select * from OPENQUERY(UserData, 'SELECT id, uname, score FROM tRecords')
</cfquery>
0
 
LVL 1

Author Comment

by:dl8453
ID: 18906768
Okay, thanks.  My query is right but still getting an erorr.  

[Microsoft][ODBC SQL Server Driver][SQL Server]Access to the remote server is denied because no login-mapping exists.

My server guy knows how to fix this error.  Thanks for the correction to my query.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 18906857
Okay. If you have any other questions, just let me know.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
The purpose of this video is to demonstrate how to prevent comment spam on a WordPress Website. This will be demonstrated using a Windows 8 PC. Plugin Akismet will be used. Go to your WordPress login page. This will look like the following: myw…

708 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

20 Experts available now in Live!

Get 1:1 Help Now