Trying to access data in SQL 2008 for water quality state reporting

defcon.klaxon

Lifetime Supporting Member
Join Date
Feb 2015
Location
Far NorCal
Posts
616
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!
 
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.
 
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).
 
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?
 
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.
 
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.

SQL1.png
 
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.
 
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.
 
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.
 
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.
 
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.
 
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
 

Similar Topics

Not sure what the issue is, there is no security enable on the project and I can download and go online normally. If I hit run to try and trace...
Replies
1
Views
1,790
Out of 21 (near identical) Modules in this system, I have only one available to test on. On start-up I check to see which Modules are not...
Replies
5
Views
4,571
I can't seem to get the Panel View Plus 7 standard edition to downgrade to V_11, when I check the AB downloads and compatibility websites for this...
Replies
1
Views
95
Hi I used to be able to launch PLCsim without any problem. Now it tells me " STEP 7 Professional Licence is required to simulate this PLC"...
Replies
15
Views
453
Back
Top Bottom