February 2, 2011 at 1:21 pm
Need a little help here. We're a manufacturing place and in December i upgraded our SQL server from SQL 2000 to SQL 2008. The day after the upgrade it was just noticed that some of the data from our manufacturing equipment is not being written to the sql server. Since i was just told of this i started doing some logging with sql server profiler to see if i can spot the problem. So far no luck.
Example:
Serial number 1103300015 is in db and i see the code the machine used to put the data in the db.
Serial number 1103300014 & 110330013 are not in the db and i do not see any code that show's it trying to put it in.
The missing data happens at different times of the day so i can't trace it to a machine/operator problem. We use a program called labview to put the data into sql and they do not have any advisories that state our version of labview does not work with sql2008. Since the upgrade the server has been pretty sluggish when i'm working on it. It has 2GB in it now and i have 8GB that I’ll be installing this weekend. Could that be my poblem? Is there a better logging tool i can use on the SQL server? What about on the computer that's entering the data, is there something i could install (hopefully not a sniffer)on it to see if it’s even sending the commands to the sql server?
Thank you for any suggestions.
February 2, 2011 at 1:41 pm
is there a trigger on the table that Labview is inserting into? if the trigger is raising an error, that would explain the missing data...if there is a trigger, maybe the trigger expects a table, or tries to update to a database or table that no longer exists due to the upgrade, and the error rolls back the insert;
the sluggishness could be due to statistics; I've seen a lot of issues where statistics must be rebuilt in databases that were upgraded from 2000 to higher versions; i'd consider doing that or an index rebuild right away.
Lowell
February 2, 2011 at 2:03 pm
I don't think labview is waiting on any triggers. We don't delete the table and recreate with each entry. In this example everything is writing to a database called production and this machine writes to a table called thickness.
February 2, 2011 at 2:12 pm
no, i mean if you look at the definition of the table in SQL Server Manangement Studio, is there a trigger on it at all? that is independent of Labview, i just mean the specific table that is "missing" the data. triggers can cause missing data.
I used to work in a shop where we used labview to pull instrument data as well...i still remember writing stuff in QBasic for RS-232 and IEEE...way back when....
Lowell
February 8, 2011 at 9:03 am
Found the problem, but i now have another question. It appears SQL 2008 interrupts the data type differently then SQL 2000. In SQL 2000 it would accept this value 1.#QNaN as a nvarchar (50). In SQL 2008 it will not even thou the data type is the same. Has anbody else seen this problem before?
February 8, 2011 at 9:14 am
What type of clients and what type of driver are they using? If they are not .net you should upgrade to SQL Native client 10 (SQLNCLI10). The clients should be receiving errors if inserts are failing... have you looked in the event logs at the client(s)?
The probability of survival is inversely proportional to the angle of arrival.
February 8, 2011 at 9:38 am
This is just a standard windows 2000 box running labview 7.1. We did not see any errors on the local machine until we setup labview to record all SQL write failures. Shown below is the labview error message. When i looked in the local backup text file i see 1.#QNaN in one of the fields and this is the data that's being rejected by SQL. This is why i'm curious if SQL 2008 interrupts datatypes differnt then SQL 2008.
Cmd Execute.vi->DB Tools Insert Data.vi->Autopole1 Insert Data.vi->Autopole1 Write Data.vi->Autopole1 Row Test Only.vi->Autopole1 Pole & Test Sub.vi->Autopole1 Main Same Time.vi<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [Microsoft][ODBC SQL Server Driver][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 12 (""): The supplied value is not a valid instance of data type real. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.. in Rec Create - Command.vi->Cmd Execute.vi->DB Tools Insert Data.vi->Autopole1 Insert Data.vi->Autopole1 Write Data.vi->Autopole1 Row Test Only.vi->Autopole1 Pole & Test Sub.vi->Autopole1 Main Same Time.vi
February 8, 2011 at 9:48 am
see if "labview" allows you to use a different driver (specifiy a connection string, or in a DSN). install and use SQLNCLI10. Also, id you can configure the connection string (or DSN) try using: DataTypeCompatibility=80; in the connections string.
I know nothing about Labview so I cannot provide any more advice than this, but I'm sure it can be resolved if you can use the SQL Native Client Driver, and especially if you have control over the DSN or Connection String the App uses.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply