use two databases in one query?

• • Reply

  12 years ago

  by comsc

  comsc

  , Thailand

  Joined 13 years ago

  Hi again, another question from me, [wehehe]

  Is it possible to use two databases in one query? How will I do this in my web.config file? And how will my query [update, insert and delete] statements look like?

  The situation here is, there are 3 systems (2 window-based and 1 web-based) accessing the same database, and when the other systems (window-based) are processing, the web-based app will give a timeout expired error. I don't want to longer the waiting of the user then in the end, it will still give an error. So, my idea here is to separate my tables from theirs.

  Or can I just make that database more accessible that even if the other systems are processing, I can still access it and make transactions through the web-based application?

  Many thanks,

   

  Report abuse
• • Reply

  12 years ago

  by Freon22

  Freon22

  , United States

  Joined 15 years ago

  Are all three application using the same connection string? I am not sure about window applications but I know you can have more then one connection string in your web.config file. So I would try using one connection string for the windows applications and another one for the web application.

  <connectionStrings>

  <add name="windowsConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

  <add name="webConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\myDatabase.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

  Report abuse
• • Reply

  12 years ago

  by comsc

  comsc

  , Thailand

  Joined 13 years ago

  hmm, what I want in my query is something like this:

  select database1.table1.*, database2.table1.* from database1.table1, database2.table1

  is this possible? how will my web.config file looks like?

  Report abuse
• • Reply

  12 years ago

  by quango

  Quango

  , United Kingdom

  Joined 15 years ago

  Yes it is possible if the databases are on the same server: if I have DB1 and DB2, this query is how you do it:

  SELECT TOP 10 myfield1 FROM DB1.dbo.MyTable1
  UNION
  SELECT TOP 10 myfield2 FROM DB2.dbo.MyTable2

  Note you need the .dbo. bit so SQL knows you are accessing Database.Owner.Table

  You can also query across servers in SQL 2xxx by using linked servers - google it if you need more info.

  Report abuse
• • Reply

  12 years ago

  by Freon22

  Freon22

  , United States

  Joined 15 years ago

  Cool someone to the rescue :-)

  Thanks I did not know that.

  Report abuse
• • Reply

  12 years ago

  by comsc

  comsc

  , Thailand

  Joined 13 years ago

  Ok, thanks, I haven't tested it but it's nice to know that it is possible. I'll use this as reference.

  Thanks again..

   

  Report abuse
• • Reply

  12 years ago

  by marvin

  AndyZulu

  Pietermaritzburg, South Africa

  Joined 19 years ago

  My question would be why are the two windows programs taking so much time? Also, it would be the SQL Server that is tied up, so creating another DB and moving your tables wouldn't solve the problem. It would have to run on another server (on a different machine) to solve the problem if the other two programs are tying up the server with, for example, long reporting procedures / periodic processing / etc, which should be optimised and re-scheduled in any case to prevent the server getting so tied up...
  

  Report abuse