Hey Jeanmarie,
I have always used OpenQuery() for my linked server connections.
SELECT *
FROM
openquery([connectionname], '[query]')
The cool thing about this is that you can bring everything from the access
dB to the SQL server for joining.
Example might be...
SELECT *
FROM
openquery([connectionname], '[query]') a,
openquery([connectionname], '[query]') b,
WHERE
a.[colName] = b.[ colName]
Hope this helps,
William
------------------
William E. Seiter
Need to have your mortgage modified?
I charge no fees until I am successful,
then I charge almost half the rate you
would find elsewhere.
Professional. Dedicated. Effective.
The Easy 24/7 way to get started: http://www.goldengrove.net/
or you can call: (626) 593 - 5501
-----Original Message-----
From: Jeanmarie Richardson [mailto:[email protected]]
Sent: Thursday, March 12, 2009 1:48 PM
To: cf-talk
Subject: SQL Queries to Linked Server SQL 2005 / Access 2007
Hi --
I am quering against a linked server (access2007) via SQL 2005. When I
first started working on this I wrote all of my CF queries against a local
copy of the Access2007 database. Each query works fine. Now that I am
migrating my code to query against this linked server, queries that contain
joins are not working properly. I have tested these with CF debugging and
then directly through SQL server management studio. What am i doing wrong
here?
Here is a query that works directly against the accessdb via CF8:
SELECT distinct tblcompanies.[company name] AS company,
tblcompanies.[company id] as companyid
FROM LINKDB...tblPeopleAddresses,
LINKDB...tblPeople INNER JOIN LINKDB...tblCompanies ON
tblPeople.[Company ID] = tblCompanies.[Company ID]
WHERE tblPeople.[Person ID]= tblPeopleAddresses.[Person id]
AND tblPeopleAddresses.state='#state#'
AND tblPeopleAddresses."primary" = 1
AND tblPeople.showonweb = 1
ORDER by tblcompanies.[company name]
Note: If I remove the last WHERE paramter, I get data, but not the data
that I want
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;207172674;29440083;f
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:320482
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
