given value of type String from the data source cannot be converted to type nvar

Question

Post reply

given value of type String from the data source cannot be converted to type nvar

bvi1998

SSC Enthusiast

Points: 115
More actions
April 9, 2024 at 5:29 pm

#4409332

Hi,
I have created a variable in Powershell to export to a SQL table.
The variable looks like this $DateTime = $((Get-Date).ToString('yyyy-MM-dd-hh_mm_ss'))
When I try to insert this into the table I get the following message:
The given value of type String from the data source cannot be converted to type
nvarchar of the specified target column."
I have tried nvarchar(50) and varchar(50) and get the same message.
What can I look for?
I am using a function called write-table which uses SQLBulkCopy ...
$conn.Open()
$bulkCopy = new-object ("Data.SqlClient.SqlBulkCopy") $connectionString
$bulkCopy.DestinationTableName = $tableName
$bulkCopy.BatchSize = $BatchSize
$bulkCopy.BulkCopyTimeout = $QueryTimeOut
$bulkCopy.WriteToServer($Data)
$conn.Close()
Would someone please give me some suggestions?

Thanks!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18769 More actions · Answer 1

Ken McKelvey

SSCoach

Points: 18769

April 9, 2024 at 6:36 pm

#4409359

Try a space, not a -, between dd and hh.

Mr. Brian Gale SSC-Insane Points: 24699 More actions · Answer 2

I don't think it is due to the -, but I'd double check the length of the column and the string. If you are certain that the length is 50 characters and your string is length is less than, then I'm stumped. Since it is a string to nvarchar or varchar, a string of 50 characters or less should go in no problem.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 3

Also try removing the last character from the string.

$dateTimeString = (Get-Date).ToString('yyyy-MM-dd HH:mm:ss')
$dateTimeString = $dateTimeString.Substring(0, $dateTimeString.Length - 1)

bvi1998 SSC Enthusiast Points: 115 More actions · Answer 4

Hi All, thanks for your help.

I think the issue might be that the columns in my input file don't end up matching the order of my table columns. Is it true that bulkcopy does not necessarily put the data into the tables in the right columns? Is there a way for me to debug which column is failing? I have been focused on my date column errors but now I am not sure that is the problem.

Thanks !

Jonathan AC Roberts SSCoach Points: 18263 More actions · Answer 5

You could import them all as nvarchar then try to work out where the error is.

You need to have the columns consistent throughout the entire file.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 6

Overall, what are you actually trying to do? Bulk insert from files to the database or???

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bvi1998 SSC Enthusiast Points: 115 More actions · Answer 7

Thanks, I had tried that before and it had not worked. I rebuilt the table and they are all being inserted now.

bvi1998 SSC Enthusiast Points: 115 More actions · Answer 8

Yes, I am performing a bulk copy from PowerShell, from a csv file. I think the nvarchar will be ok for the user. I had changed all of the columns one at a time to test, but it never worked correctly. Now with all nvarchar it's fine. Thanks

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 9

bvi1998 wrote:

Yes, I am performing a bulk copy from PowerShell, from a csv file.

As a bit of a sidebar, it's always fascinating to me to watch how people do things like this. Since they're controlling the process outside of SQL Server but want to keep logs about it all inside SQL, it always seems to get more complicated than it needs to, IMHO.

Glad you got it sorted.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

frederico_fonseca SSCoach Points: 16023 More actions · Answer 10

you have 2 issues to address.

1 - if your destination datatype is a date then your variable assignment needs to cast it as a datetime, not as a string - although depending on the format the bulkcopy may convert it correctly - a possible string is on format "yyyy-MM-dd HH:mm:ss" (case is important)

2 - if the column order on your source is not the same as the destination table then you MUST use another constructor to map the source columns to the destination columns

on your script you would therefore need $bulkcopy.ColumnMappings added to it with all your mappings. see https://www.powershellgallery.com/packages/DbData/1.0.4.2/Content/New-DbBulkCopy.ps1 for one example containing such a mapping.