Joining Tables in SQL Server Express

  • I’m trying to build an alarm logging system using SQL Server Express. I will have several tables each monitoring one alarm type, i.e., TABLE 1 EMPTY, TABLE 1 FULL, TABLE 1 OVERILL, TABLE 2 EMPTY, TABLE 2 FULL, TABLE 2 OVERFILL. The TABLE number will range from 1 to 1000.

    Each table will have columns:

    IDNAMEVALUEDATE/TIME

    The data is being automatically fed into the tables via an OPC log system.

    I need a report the customer can call up that will join all the data from each TABLE 1 table.

    I need a report the customer can call up that will join all the data from each OVERFILL table.

    I need a report the customer can call up that will join all the data from all the tables.

    The customer needs to call up these tables (read only) from any computer on the plant network.

    So far, I am able to ad hoc query each table and show the data properly.

    I don’t know how to join the data.

    I don’t know how to have a button on each PC that can call up these various joined tables.

    I'm a little concerned that when these tables get large, when the customer call up a joined table, he might have quite a delay, wating for the data. Maybe I could trigger the joining every, say 60 seconds, to keep the data fresh. But then I don't know what happens when the customer calls it up, another wait?

  • You could install management studio express on each of the machines. If you have a machine with IIs installed, you could use reporting services. That way the clients access the reports from their web browser.

    You could also go all out and write a front end app in C#/Visual Basic, but that is a fair bit of work.

    For joining the tables together, look up UNION/ UNION ALL in Books Online (The SQL documentation)

    Brief example:

    SELECT col1, col2, col3, ... -- list all the columns you need

    FROM Table_1_Empty

    UNION ALL -- all rows from both, don't eliminate duplicates

    SELECT col1, col2, col3, ...

    FROM Table_1_Full

    UNION ALL

    SELECT col1, col2, col3, ...

    FROM Table_1_OverFull

    There's no scheduling of joining or anything like that. The query's just reading and manipulating the tables, not storing or modifying any data anywhere.

    Does that help at all?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Gail,

    It sure does help - I'll get busy and try to digest it all.

    Thanks a million!

    Gary

  • This code works just fine if I don't use the substring - when I use the substring I get the error below. What am I doing wrong?

    Thanks,

    Gary

    -- SELECT DATA

    sp_dbcmptlevel @new_cmptlevel = 90

    SELECT * FROM dbo.UNIT_1_EMPTY_TABLE

    UNION ALL

    SELECT * FROM dbo.UNIT_1_EMPTY_TABLE

    substring (TAG , 10,30) as 'ALARM NAME', VALUE as '1=ON',

    CONVERT(CHAR(27),(TIME),109) as 'DATE & TIME'

    "TAG" is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.

  • It think you're wanting something like this?

    SELECT substring (TAG , 10,30) as 'ALARM NAME', VALUE as '1=ON',

    CONVERT(CHAR(27),(TIME),109) as 'DATE & TIME'

    FROM dbo.UNIT_1_EMPTY_TABLE

    UNION ALL

    SELECT substring (TAG , 10,30) as 'ALARM NAME', VALUE as '1=ON',

    CONVERT(CHAR(27),(TIME),109) as 'DATE & TIME'

    FROM dbo.UNIT_1_EMPTY_TABLE

    Column specifications go in the select clause *(replacing the *). Putting them where you did meant they were part of the from clause and were treated like a table identifier.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • gboehm (1/9/2008)


    I’m trying to build an alarm logging system using SQL Server Express. I will have several tables each monitoring one alarm type, i.e., TABLE 1 EMPTY, TABLE 1 FULL, TABLE 1 OVERILL, TABLE 2 EMPTY, TABLE 2 FULL, TABLE 2 OVERFILL. The TABLE number will range from 1 to 1000.

    Sorry for asking: are you going to have up to 1000 tables of each kind?

    ...and your only reply is slàinte mhath

  • I get an error with this code and I know the object name (UNITA_1_EMPTY_TABLE) is correct:

    -- SELECT DATA

    sp_dbcmptlevel @new_cmptlevel = 90

    SELECT substring (TAG , 10,30) as 'ALARM NAME', VALUE as '1=ON',

    CONVERT(CHAR(27),(TIME),109) as 'DATE & TIME'

    FROM dbo.UNITA_1_EMPTY_TABLE

    UNION ALL

    SELECT substring (TAG , 10,30) as 'ALARM NAME', VALUE as '1=ON',

    CONVERT(CHAR(27),(TIME),109) as 'DATE & TIME'

    FROM dbo.UNITB_1_EMPTY_TABLE

    Valid values of the database compatibility level are 60, 65, 70, 80, or 90.

    Msg 208, Level 16, State 1, Line 4

    Invalid object name 'dbo.UNITA_1_EMPTY_TABLE'.

    The 1=ON heading is for a bit column where 1=ON and 0=OFF.

    Thanks,

    Gary

  • Can't see anything obviously wrong. Do you get the same if you run the two queries seperatly?

    In your convert, replace the round bracket around Time with []. Might be related, not sure.

    You don't need to run the compat mode set every time you do a query. You set the compat mode to a value, it stays on that value until set to something else.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Oops, no, make that OOPS!

    I forgot to reference the correct database when I fired up SQL Server this am.

    Everything is working properly.

    Sorry!

    Now I'm lookng into a customer interface. It looks like my best avenue might be VB code, since I should give the customer the ability to select dates. They might want to limit the report to a selected date range. I don't know if the SQL reporting services has that capability.

    By the way, the total number of tables will be around 1000, not 1000 of each kind.

    Thanks again,

    Gary

  • I am trying to connect to my SQL database in VB, but I get this error message:

    Unable to open the physical file “filename.mdf”. Operating sysem error 32: “32(The process cannot access the file because it is being used by another process.)”.

    An attempt to attach an auto-named database for file C:\filename.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

    VB allows me to find the filename.mdf file, but then it can't connect.

    What am I doing wrong?

    Thanks,

    Gary

  • Could you post the VB code where you try to connect please?

    Re Reporting services. You can create reports with parameters. Users can then enter values they like. Is very much like Crystal Reports, if you've used that.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Actually I wasn't using any code.

    I clicked on Add new data source,

    then clicked on DataBase

    then clicked on New Connection

    then clicked Browse under DataBase File Name

    (Data Source was already selected as Microsoft SQL Server Database file(SQLClient))

    then navigated to my Database.mdf file.

    Then I clicked Test Connection and then I get the error code.

    I'm using Micrposoft Visual Basic 2005 Express Edition.

    Thanks,

    Gary

  • Also -

    depending on how you set the DB up (e.g. if you had Visual Studio create the database for you), you might have ended up with the DB being set up as a "User Instance", meaning - it gets attached and detached dynamically as things are requested of it. Sounds nice in theory, except that this introduces a substantial delay (anywhere from 5-45 secs on medium sized databases, could go higher) each time you need to reattach the DB (which if your DB doesn't get a lot of action would be pretty much every time something access the DB).

    If your VB.NET connection string mentions a specific .MDF file to attach instead of a server instance/DB name, you might care to turn that thing into a "permanently attached" DB, meaning - attach the database using SSMS, and turn off the "auto-close" option in the database properties. This will cut down a LOT on any perceived delays on queries, etc....

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • Report delays will not please our customer. Remember all we are trying to report are alarms on individual machines in a manufacturing plant. Basically, any PC in the plant can call up the alarms for viewing only, with the ability to limit the machine viewed (any or all) and the time and date of the alarms report. When the user makes a selection, the report should appear pretty much instantaneously. Maybe I should delve deeper into the SQL reporting services, as long as I can have the reporting on any PC, it should do what I need - right?

    We would really like to call up a report any time there is a new alarm - but so far, I haven't seen any way to automatically cause a report to be generated.

    Thanks,

    Gary

  • Since you're using SQL Express, and the db files are already in use by the sql service, you don't want to do the file-based connection.

    You want to connect to a server, specify the server name then select the database name. The server name will be whatever you connect to from management studio.

    sorry I can't be much clearer. I'm not a C#/VB developer.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 15 posts - 1 through 15 (of 23 total)

You must be logged in to reply to this topic. Login to reply