[cfaussie] Re: Querying across multiple databases
You could do cross database queries even if they were on different servers. In the case of MSSQL, you just have to register linked servers through sp_addlinkedserver, and then use myLinkedServer.Mydatabase.MyDBuser.Mytable .TofOn 9/13/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:Are the databases on the same sql server? If so you can simply include the database name as part of the query.ie dbname.dbuser.dbtable Hi all I think I remember a post on this before but I can't find it anywhere. the app I'm extending uses 2 databases (2 diff DSN's). I now need to pull in data from both into the same recordset. eg: SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID , INST.AbsoluteSerialNum FROM tbl_Quiz_Schedule AS SCHED INNER JOIN evaluations.dbo.tbl_InstrumentReleased AS INST ON INST.> I could:- use Query'o'query to combine 2 seperate queries into one (too much irrelivant data)- put the SQL onto one of the DB's as a SProc (back-up plan)- work out how to join the two databases within the same CFQUERY (preferred) any suggestions? thanx barry.b --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: Querying across multiple databases
Nothing wrong with cross-database joins - heaps quicker than a QofQ. On 13/09/06, Barry Beattie [EMAIL PROTECTED] wrote: Thanx Rod well, bugger me, it worked! cfquery name=qry datasource=FOUREX SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID, INST.AbsoluteSerialNum FROM evaluationsQuizDev.dbo.tbl_Quiz_Schedule AS SCHED INNER JOIN tbl_InstrumentReleased AS INST ON INST.OnlineQuizID = SCHED.Quiz_ID /cfquery cfdump var=#qry# expand=false I suppose it'd crash'n'burn if the username/password was changed per database, not just logon to the db server...yes? thanx again b On 9/13/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Are the databases on the same sql server? If so you can simply include the database name as part of the query. ie dbname.dbuser.dbtable Hi all I think I remember a post on this before but I can't find it anywhere. the app I'm extending uses 2 databases (2 diff DSN's). I now need to pull in data from both into the same recordset. eg: SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID, INST.AbsoluteSerialNum FROM tbl_Quiz_Schedule AS SCHED INNER JOIN evaluations.dbo.tbl_InstrumentReleased AS INST ON INST.OnlineQuizID = SCHED.Quiz_ID I could: - use Query'o'query to combine 2 seperate queries into one (too much irrelivant data) - put the SQL onto one of the DB's as a SProc (back-up plan) - work out how to join the two databases within the same CFQUERY (preferred) any suggestions? thanx barry.b -- Darryl http://www.acheron.org/darryl/ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: Querying across multiple databases
Whats the database type your using? and are botht he databases on the same server? -Original Message- From: cfaussie@googlegroups.com [mailto:[EMAIL PROTECTED] Behalf Of Barry Beattie Sent: Wednesday, September 13, 2006 10:52 AM To: cfaussie@googlegroups.com Subject: [cfaussie] Querying across multiple databases Hi all I think I remember a post on this before but I can't find it anywhere. the app I'm extending uses 2 databases (2 diff DSN's). I now need to pull in data from both into the same recordset. eg: SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID, INST.AbsoluteSerialNum FROM tbl_Quiz_Schedule AS SCHED INNER JOIN evaluations.dbo.tbl_InstrumentReleased AS INST ON INST.OnlineQuizID = SCHED.Quiz_ID I could: - use Query'o'query to combine 2 seperate queries into one (too much irrelivant data) - put the SQL onto one of the DB's as a SProc (back-up plan) - work out how to join the two databases within the same CFQUERY (preferred) any suggestions? thanx barry.b --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: Querying across multiple databases
Hi, The query you have posted shows the correct syntax for using a table from a different database. (i think I would add the server name too). you could always create a view with the table inside of it. [EMAIL PROTECTED] 13/09/2006 10:52 am Hi all I think I remember a post on this before but I can't find it anywhere. the app I'm extending uses 2 databases (2 diff DSN's). I now need to pull in data from both into the same recordset. eg: SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID, INST.AbsoluteSerialNum FROM tbl_Quiz_Schedule AS SCHED INNER JOIN evaluations.dbo.tbl_InstrumentReleased AS INST ON INST.OnlineQuizID = SCHED.Quiz_ID I could: - use Query'o'query to combine 2 seperate queries into one (too much irrelivant data) - put the SQL onto one of the DB's as a SProc (back-up plan) - work out how to join the two databases within the same CFQUERY (preferred) any suggestions? thanx barry.b --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
[cfaussie] Re: Querying across multiple databases
Thanx Rod well, bugger me, it worked! cfquery name=qry datasource=FOUREX SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID, INST.AbsoluteSerialNum FROM evaluationsQuizDev.dbo.tbl_Quiz_Schedule AS SCHED INNER JOIN tbl_InstrumentReleased AS INST ON INST.OnlineQuizID = SCHED.Quiz_ID /cfquery cfdump var=#qry# expand=false I suppose it'd crash'n'burn if the username/password was changed per database, not just logon to the db server...yes? thanx again b On 9/13/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Are the databases on the same sql server? If so you can simply include the database name as part of the query. ie dbname.dbuser.dbtable Hi all I think I remember a post on this before but I can't find it anywhere. the app I'm extending uses 2 databases (2 diff DSN's). I now need to pull in data from both into the same recordset. eg: SELECT SCHED.StartDateTime, SCHED.EndDateTime, SCHED.Quiz_ID, INST.AbsoluteSerialNum FROM tbl_Quiz_Schedule AS SCHED INNER JOIN evaluations.dbo.tbl_InstrumentReleased AS INST ON INST.OnlineQuizID = SCHED.Quiz_ID I could: - use Query'o'query to combine 2 seperate queries into one (too much irrelivant data) - put the SQL onto one of the DB's as a SProc (back-up plan) - work out how to join the two databases within the same CFQUERY (preferred) any suggestions? thanx barry.b --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups cfaussie group. To post to this group, send email to cfaussie@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cfaussie -~--~~~~--~~--~--~---
