Insert query - Error converting data type nvarchar to (null)

Question

I'm trying to insert data from SQL Server to a Linked server but I am encountering the following issue:

Msg 8114, Level 16, State 11, Line 1
Error converting data type nvarchar to (null).

My query is:

INSERT piserver.piarchive..picomp2 
(tag, time, value, annotations)
SELECT cast(c.name as nvarchar) as name, convert(datetime, c.started_at) as started_at, c.value, cast(c.log as nvarchar) as log
FROM [database].[dbo].[table1] c
WHERE c.id = 206

There are no null values in the first 3 columns, but even when I remove the last column from the query, I am still met with a null error.

The datatypes of picomp2 are: tag (WString(4000), not null) time (DateTime, not null) value (Variant, null) annotations (Variant, null)

The datatypes of table1 are all varchar except value which is float.

Answer 1

In SQL Server, you always want to include a length. The default varies by context. It is not clear what the lengths should be, but yo ucan probably just rely on implicit conversion to do the right thing:

INSERT piserver.piarchive..picomp2 (tag, time, value, annotations)
    SELECT c.name, try_convert(datetime, c.started_at) as started_at, c.value, c.log 
    FROM [database].[dbo].[table1] c
    WHERE c.id = 206;

I also recommend using try_convert() for the datetime, unless you want conversion errors to stop the INSERT.