You are not registered yet. Please click here to register!


 
 
plc storereviewsdownloads
This board is for PLC Related Q&A ONLY. Please DON'T use it for advertising, etc.
 
Try our online PLC Simulator- FREE.  Click here now to try it.

---------->>>>>Get FREE PLC Programming Tips

New Here? Please read this important info!!!


Go Back   PLCS.net - Interactive Q & A > PLCS.net - Interactive Q & A > LIVE PLC Questions And Answers

PLC training tools sale

Reply
Thread Tools Display Modes
Unread June 17th, 2019, 05:38 PM   #1
defcon.klaxon
Lifetime Supporting Member
United States

defcon.klaxon is offline
 
Join Date: Feb 2015
Location: Far NorCal
Posts: 449
Trying to access data in SQL 2008 for water quality state reporting

Hi guys,

A new client has an existing system that works just fine (surface water plant) but they had a falling out with the previous integrator and they're a bit lost, trying to get everything sorted. My specific task is to help them figure out how to access the data needed for the reporting they send into the water quality agency. Apparently the previous integrator would remote in, grab the raw data, and provide it to the operators. Maybe so he could bill monthly maintenance, I don't know...I know very little about SQL but I believe you can create scripts that would do this for you; point is, I'm trying to figure out how to access the data in SQL as easily as possible so that the operators don't need me to do it for them.

I've taken a look at the system, lo and behold in SQL there is a table called "State Reporting" and it has several tags like flow rates, CL2 residuals, turbidities, and the like. So that is easily found, but when I try to extract data through the wizard I find that there is no way for me to specify date range for the data I'm trying to grab. It simply creates a spreadsheet with several months of data from the year 2014. So I don't know if the SQL server is currently running and saving data or if there's an error somewhere, and if it is currently running, how to access the data for just the month I'm interested in.

I've been searching around and have some some info that is a bit helpful but the date range still has me scratching my head. Could anyone provide some info that could help me, a total SQL noob, to understand how to interact with the database? Until now all my work with SQL has had some software intermediary (like Dream Report) so I haven't had to deal with the database directly. Thanks for any help!
  Reply With Quote
Unread June 17th, 2019, 06:25 PM   #2
Archie
Member
United States

Archie is offline
 
Join Date: May 2002
Location: Orangeburg, SC
Posts: 1,912
The tool you want to look for is SQL Server Management Studio. You will also need a user and password to log into SQL Server or it may let you through the Windows user that is logged in depending on how SQL was setup. If SQL Server Management Studio is not already on the PC with SQL Server, you can download it for free from Microsoft.
__________________
Expectations lead to disappointment. Appreciation leads to satisfaction.

AdvancedHMI - HMI Software without the license key hassles
  Reply With Quote
Unread June 17th, 2019, 06:35 PM   #3
defcon.klaxon
Lifetime Supporting Member
United States

defcon.klaxon is offline
 
Join Date: Feb 2015
Location: Far NorCal
Posts: 449
Quote:
Originally Posted by Archie View Post
The tool you want to look for is SQL Server Management Studio. You will also need a user and password to log into SQL Server or it may let you through the Windows user that is logged in depending on how SQL was setup. If SQL Server Management Studio is not already on the PC with SQL Server, you can download it for free from Microsoft.
Hi, yeah I am able to access the SQL database through Server Management Studio, and it uses windows credentials. What I don't know about is how to extract the data from the database in a usable format (rows with timestamp, columns with different data tags).
  Reply With Quote
Unread June 17th, 2019, 07:05 PM   #4
Maxkling
Member
United States

Maxkling is offline
 
Join Date: Mar 2011
Location: Atlanta
Posts: 263
Can you post the query you are using?

A simple “select * from yourtablename” will return all data from the table. What columns are in the table you wish to query?
  Reply With Quote
Unread June 17th, 2019, 07:05 PM   #5
Archie
Member
United States

Archie is offline
 
Join Date: May 2002
Location: Orangeburg, SC
Posts: 1,912
It really depends on how the database schema is setup. Check under the Views to see if anything has been created that ties together any parent child relationships. Also check for Stored Procedures.

Once you get a view of the data, you can export to something like a CSV file.

You may also want to see if the PC has Reporting Services on it. Maybe the previous integrator used created some reports stored in the Report Server.
__________________
Expectations lead to disappointment. Appreciation leads to satisfaction.

AdvancedHMI - HMI Software without the license key hassles
  Reply With Quote
Unread June 18th, 2019, 04:23 AM   #6
parky
Member
United Kingdom

parky is offline
 
Join Date: Oct 2004
Location: Midlands
Posts: 1,250
In Excel, go to Data Tab at top then from other sources select from other sources Then enter the Server name, Once connected select the table then import the table to Excel. If you record a macro before you start to import and then save it you can then run the macro again and again. Depends on what version of Excel you have and if VBA is enabled you should be able to edit the macro to add date ranges in the SQL script. Also if a later version of Excel then you can limit the date range to filter out the unwanted data.
Attached Images
File Type: png SQL1.png (75.1 KB, 112 views)
  Reply With Quote
Unread June 18th, 2019, 02:26 PM   #7
defcon.klaxon
Lifetime Supporting Member
United States

defcon.klaxon is offline
 
Join Date: Feb 2015
Location: Far NorCal
Posts: 449
Thanks for all the help guys, I'll do a little research based on what you've suggested and reply. I'll get some screenshots to help with figuring out how things are set up.
  Reply With Quote
Unread June 19th, 2019, 03:01 AM   #8
parky
Member
United Kingdom

parky is offline
 
Join Date: Oct 2004
Location: Midlands
Posts: 1,250
Try this if you have Excel

Go to Control panel and select ODBC Data sources, Add a new DSN (unless you already have one). Select SQL Server Native client, Give it a name, Select the server you wish to connect to (assume your pc has access to it). Press next, select type of login (if you can log in via SQLExpress without password then it should be windows auth.). Press next, select the database (default is master) you wish to connect to, press next, then finish, test the connection to see if it works. If successful then you have created a DSN.
Open up Excel as a new sheet, select Data Tab, then from other sources, select from Microsoft query, this opens up a box that will have a list of DSN’s, select the DSN you created, Select the table you want from the list (note: there will be lots of tables), select the fields you want (populates right hand column) or select all, Press next, This allows you to filter the data if required (for example if the table contains many thousands of entries you can filter by date/time to reduce the amount of data returned. You can save this query and even edit it to change the dates etc.
  Reply With Quote
Unread June 19th, 2019, 03:32 PM   #9
defcon.klaxon
Lifetime Supporting Member
United States

defcon.klaxon is offline
 
Join Date: Feb 2015
Location: Far NorCal
Posts: 449
OK guys, quick update. The PC where the SQL database is running is Windows XP, and there is no installation of Excel at all. Seems like the previous guy was running some sort of script to grab the data, and would process it on another computer.

A major goal of this project is to get a system in place for the operators to pull the data without my involvement, so it sounds like getting Excel on that machine is going to be the next step moving forward.

Man, I don't even know how I'm going to do that though, I doubt that the newest version of Office would even run on XP, and is there a way to buy a version that is new enough so that grabbing data from SQL is easy, yet is old enough to run on XP. I guess I could buy a license off of eBay, worst case.
  Reply With Quote
Unread June 19th, 2019, 03:55 PM   #10
Dravik
Member
United States

Dravik is online now
 
Join Date: Jun 2008
Location: New York
Posts: 1,436
Is this machine standalone or networked?

You said XP, So I am guessing it is standalone.

But if its networked, You could access the SQL server remotely using some more modern software.

If standalone, Powershell could be used to script pulling data and shoving it into a .csv file.
  Reply With Quote
Unread June 19th, 2019, 04:04 PM   #11
Bob O
Member
United States

Bob O is offline
 
Join Date: May 2003
Location:
Posts: 1,713
SWAG...I hope this won't be a distraction. I don't know enough to know if this will even work for you.



https://www.sytech.com/product-xlreporter-overview.asp
__________________
There Is A Frost Advisory...GBR!
  Reply With Quote
Unread June 19th, 2019, 05:39 PM   #12
defcon.klaxon
Lifetime Supporting Member
United States

defcon.klaxon is offline
 
Join Date: Feb 2015
Location: Far NorCal
Posts: 449
Quote:
Originally Posted by Dravik View Post
Is this machine standalone or networked?

You said XP, So I am guessing it is standalone.
Yeah it's standalone, it's a panel mount PC in the filter building. It does have access to the internet for TeamViewer.
  Reply With Quote
Unread June 20th, 2019, 04:20 AM   #13
parky
Member
United Kingdom

parky is offline
 
Join Date: Oct 2004
Location: Midlands
Posts: 1,250
It sounds to me if they used team viewer to access the PC then there may be some software to grab the data although this may be on their side, is this on your site network or is it connected via a modem directly to a telephone line.
Maybe if you have an IT department they may know if the PC is on the network but isolated from normal site network. I believe Office 2010 will work on XP. with service pack 3, but not later versions. You could use SQLEXPRESS by creating a SQL Script and limit the date or may be a batch code like this
SELECT [BatchStarted]
,[BatchID]
,[Cooker]
,[Recipe]
,[No_Phases]
,[BatchEnd]
,[Phase1Time]
,[Phase2Time]
,[Phase3Time]
,[Phase4Time]
,[Phase5Time]
,[Phase6Time]
,[Phase7time]
,[Phase8Time]
FROM [DerbySC].[dbo].[CookerBatch]
WHERE [BatchStarted] > '2019-04-16 16:55:09.000' AND [BatchStarted] < '2019-04-17 16:57:09.000'


You will have to modify the script each time by entering the date range or batch code then execute the script and then save the result as a CSV in SQL Management studio. If you have problems post the structure (a screen shot of the database fields and I can show you the script required.
  Reply With Quote
Unread June 20th, 2019, 11:20 AM   #14
defcon.klaxon
Lifetime Supporting Member
United States

defcon.klaxon is offline
 
Join Date: Feb 2015
Location: Far NorCal
Posts: 449
Quote:
Originally Posted by Bob O View Post
SWAG...I hope this won't be a distraction. I don't know enough to know if this will even work for you.



https://www.sytech.com/product-xlreporter-overview.asp
Sorry, but I don't know what SWAG means, can you explain? Thanks for the link, I'll check it out!
  Reply With Quote
Unread June 20th, 2019, 12:11 PM   #15
James Mcquade
Member
United States

James Mcquade is offline
 
Join Date: Oct 2007
Location: Tennessee
Posts: 2,576
Wag - wild a** gues
Swag - Super wild a** guess


if your customer had a falling out with the integrator and they had access to the pc, I hate to say it, but they may have gotten into the pc and erased the critical programs and files needed to obtain the information you need. I would look at the deleted filed. Warning, be careful of what you find. they may have changed the files before deleting them, if that is what they did.
I have known of this happening, not saying they did.

james
  Reply With Quote
Reply
Jump to Live PLC Question and Answer Forum

Bookmarks


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Topics
Thread Thread Starter Forum Replies Last Post
Loading .Pre in an array of timers? dginbuffalo LIVE PLC Questions And Answers 24 March 11th, 2016 05:44 PM
Wonderware / SQL Reporting Server I I L LIVE PLC Questions And Answers 3 May 20th, 2009 06:43 PM
Get proccess data from SQL , using WinCC V6.0 ! nguyentrungkiet LIVE PLC Questions And Answers 1 August 29th, 2007 06:07 AM
MPI comunication Manuel Raposo LIVE PLC Questions And Answers 22 July 16th, 2007 06:24 AM
Data logging & reporting ckchew666 LIVE PLC Questions And Answers 18 November 11th, 2005 11:08 PM


All times are GMT -5. The time now is 07:12 AM.


.