0

I'm trying to run an INSERT query but it asks me to convert varchar to null. Here's the code:

INSERT Runtime.dbo.History (DateTime, TagName, vValue)
VALUES ('2015-09-10 09:00:00', 'ErrorComment', 'Error1')

Error message:

Error converting data type nvarchar to (null).

The problem is at the vValue column.

column vValue(nvarchar, null)

How it looks in the database:

enter image description here

The values inside vValue are placed by the program I'm using. I'm just trying to manually insert into the database.

Last post was with the wrong column, I apologize.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Kostas
  • 69
  • 10
  • 1
    'Error1' cannot be converted to a float. A float is essentially a number. – Rodders Oct 15 '15 at 11:34
  • http://stackoverflow.com/questions/1056323/difference-between-numeric-float-and-decimal-in-sql-server Link may help you – Shiju Shaji Oct 15 '15 at 11:35
  • I used wrong collumn but now i get another error – Kostas Oct 15 '15 at 11:48
  • What datatype is `Value`? Is it a computed column? – Mark Sinkinson Oct 15 '15 at 12:44
  • And please tag your question correctly. You are not using both MySQL and SQL Server... – Mark Sinkinson Oct 15 '15 at 12:45
  • Datatype is this vValue(nvarchar, null). What do you mean i'm not using mysql and sql server? – Kostas Oct 15 '15 at 13:13
  • @Kostas he means that Microsoft's SQL (`sql-server` - which you're using) is not the same as MySQL, you've tagged your question ith both. Can I ask why you are interacting with this table? Wonderware isn't set up for you to do inserts here though I'm sure you can work around it I'm not sure if there will be other repercussions. My `Runtime` DB doesn't have a "History" table, what version of WW you running? – Grambot Oct 16 '15 at 13:19

3 Answers3

1

After contacting Wonderware support i found out that INSERT is not supported on the vValue column by design. It’s a string value and updates are supposed to carry out via the StringHistory table.

Kostas
  • 69
  • 10
0

What is the type of the column value in the database ?

If it's float, you should insert a number, not string.

Cast "error1" to FLOAT is non-sense. Float is a number exemple : 1.15, 12.00, 150.15 When you try to CAST "Error1" to float, he tries to transform the text "error1" to number and he can't, it's logic.

You should insert a number in the column.

Temporarylol
  • 1
  • 2
  • It seems i used wrong collumn, but now i get another error – Kostas Oct 15 '15 at 11:50
  • If you try : INSERT Runtime.dbo.History (DateTime, TagName, value, vValue) VALUES ('2015-09-10 09:00:00','ErrorComment', null, 'Error1') ? – Temporarylol Oct 15 '15 at 12:10
  • It doesn't work as i'm trying to insert 4 values in 3 collumns – Kostas Oct 15 '15 at 12:11
  • I will try to add the Value collumn aswell and see so i'll have 4 values for 4 collumns – Kostas Oct 15 '15 at 12:12
  • What software do you use to manage your database ? I know that in PGadmin with postgresql, we can right click on a table, select Scripts and "SCRIPT Insert" that create the structure of request. You have just to add the values. PS You can edit your comment instead of add new comment ^^ – Temporarylol Oct 15 '15 at 12:18
  • I'm using SQL Server Management Studio to connect and execute my Query – Kostas Oct 15 '15 at 13:17
  • Maybe you set your column to be "NOT NULL" ? – Temporarylol Oct 15 '15 at 13:23
  • I don't want to mess up with the Database, as it is setup as it should be. I just don't understand how can the program running can insert data into the collumn and i can't. – Kostas Oct 16 '15 at 07:41
0

I think I can help you with your problem since I've got a decent test environment to experiment with.

Runtime.dbo.History is not a table you can interact directly with, it is a View. In our case here the view is defined as:

select * from [INSQL].[Runtime].dbo.History

...Which I believe implies the History data you are viewing is from the Historian flat file storage itself, a Wonderware Proprietary system. You might see some success if you expand the SQL Server Management Studio's

Server Objects -> Linked Servers -> INSQL

...and play with the data there but I really wouldn't recommend it.

With that said, for what reason do you need to insert tag history? There might be other workarounds for the purpose you need.

Grambot
  • 4,370
  • 5
  • 28
  • 43
  • I'm using version 2014 R2 of Wonderware product. What i wanted to do is insert data into Historian in previous time. I'm inserting data through Intouch and that is the query i want to run. – Kostas Oct 16 '15 at 18:22
  • Historian works great when i change the values inside the program realtime, but if i want to pass some data for previous time i can't – Kostas Oct 16 '15 at 18:23
  • This might honestly be a question to take to WW tech support. It kinda breaks the whole flow of what a historian does. I know they use their "flat file storage" for historian that is not necessarily a RDBMS style storage, you can only access it through SSMS because they provide that Provider hook to InSQL. I'm reading stuff about an InSQL editor that might be available for use – Grambot Oct 17 '15 at 18:10