January 9, 2008 at 1:02 pm
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?
January 9, 2008 at 1:32 pm
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
January 9, 2008 at 1:36 pm
Gail,
It sure does help - I'll get busy and try to digest it all.
Thanks a million!
Gary
January 9, 2008 at 2:05 pm
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.
January 9, 2008 at 11:22 pm
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
January 10, 2008 at 3:37 am
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
January 10, 2008 at 7:02 am
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
January 10, 2008 at 7:09 am
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
January 10, 2008 at 7:24 am
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
January 10, 2008 at 11:52 am
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
January 10, 2008 at 1:11 pm
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
January 10, 2008 at 1:22 pm
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
January 10, 2008 at 1:25 pm
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?
January 10, 2008 at 1:36 pm
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
January 10, 2008 at 1:37 pm
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
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply