Kristin Collins wrote: > I need to write an sql statement within a cfquery tag that pulls from two > seperate datasources. > > But it's coming back with "Invalid object name " > > Below is the what I have written: > > <cfquery datasource="#request.dsn#" name="towns"> > SELECT townid as tid, town as cityname from towns > where siteID = 1 > union > select distinct city as cityname > from compdb.dbo.commlease > order by cityname > </cfquery> > > Can anyone tell me what I'm doing wrong? >
Does the user that you are connecting to the request.dsn with have access to the compdb database? Are the two databases on the same server or are the servers linked? You said "datasources" in your message, and you can't query across CF datasources (except by combining them with QoQ), but you can do something like what you are trying to in your query if the databases are on the same SQL Server or set up as a linked server, and the permissions are correct. I say "something like" what you are trying to do, as the UNION also has a problem...you need to have the same number of columns with compatible datatypes in each query to do a UNION. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| ColdFusion MX7 and Flex 2 Build sales & marketing dashboard RIAâs for your business. Upgrade now http://www.adobe.com/products/coldfusion/flex2?sdid=RVJT Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:272934 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
