use two databases in one query?

asp.net , db Thailand
  • 12 years ago

    Hi again, another question from me, [wehehe] Stick out tongue

    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,

     

  • 12 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>

  • 12 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?

  • 12 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.

  • 12 years ago

    Cool someone to the rescue :-)

    Thanks I did not know that.

  • 12 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..

     Yes

  • 12 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...

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“In order to understand recursion, one must first understand recursion.”