[sqlite] System.Data.SQLite: The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column
Windows 7 64bit SQLite version 3.17.0 sqlite-netFx46-static-binary-x64-2015-1.0.104.0 Note: The code is working correctly with Devart ODBC driver for SQLite 2.1.4 The problem is with System.Data.SQLite How to reproduce: ON SQLite Create database N:\SqLite\outStream.db CREATE TABLE FromNum (id int NOT NULL, Num numeric(5, 5)); INSERT INTO FromNum (id, Num) VALUES (1, .0); On SQL Server CREATE TABLE [dbo].[ToNum]( [id] [int] NOT NULL, [Num] [numeric](5, 5) NULL, PRIMARY KEY CLUSTERED ([id] ASC) ); PowerShell script: [string]ToTableName = "SERVER.Test.dbo.ToNum" [string]FromFileName = 'N:\SqLite\outStream.db' [string[]]ttname = ToTableName.Split(".") [string]ServerName = ttname[0] [string]dbName = ttname[1] [string]schema = ttname[2] [string]DestinationTable = ttname[3] sqlString = "SELECT * FROM FromNum" con = New-Object -TypeName System.Data.SQLite.SQLiteConnection -ArgumentList "Data Source=$FromFileName" #$con = New-Object -TypeName System.Data.Odbc.OdbcConnection -ArgumentList "DSN=SqLite;Database=N:\SqLite\outStream.db;Direct=True" this is Devart ODBC con.Open() SourceSQLCommand = con.CreateCommand() SourceSQLCommand.CommandText = sqlString DestinationConnectionString = "Data Source=$ServerName;Initial Catalog=$dbName;integrated security=false" try { reader = SourceSQLCommand.ExecuteReader() bulkCopy = New-Object ("System.Data.SqlClient.SqlBulkCopy") DestinationConnectionString, ([System.Data.SqlClient.SqlBulkCopyOptions]::TableLock -bor [System.Data.SqlClient.SqlBulkCopyOptions]::UseInternalTransaction) bulkCopy.DestinationTableName = DestinationTable bulkcopy.EnableStreaming = true; bulkCopy.BatchSize = 5 #The number of rows in each batch sent to the server bulkCopy.BulkCopyTimeout = 0 #the number of seconds before a time-out bulkCopy.WriteToServer($reader) #copy all rows to the server } catch { ex = _.Exception Write-Error ex.Message } finally { reader.Close() con.Close() } Error Message: Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." Dmitriy Burtsev This message, and any of its attachments, is for the intended recipient(s) only, and it may contain information that is privileged, confidential, and/or proprietary and subject to important terms and conditions available at http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the intended recipient, please delete this message and immediately notify the sender. No confidentiality, privilege, or property rights are waived or lost by any errors in transmission. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with System.Data.SQLite
I try both "SELECT 1,0;" and "SELECT 1, cast(0 as real)"? The same error: Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Hick Gunter Sent: Wednesday, January 11, 2017 11:29 AM To: 'SQLite mailing list' Subject: Re: [sqlite] Need help with System.Data.SQLite What is the result if you exchange your currently executed statement to "SELECT 1,0;" ? Or to "SELECT 1, cast(0 as real)"? The effect of declaring a NUMERIC(5,5) field in SQLite is that it's affinity becomes NUMERIC. The value .0 thus becomes the integer 0. The other legal values (.1 to .9) are all converted to real (double) floating point numbers, each to the closest possible approximation (or, if the approximation is not "close enough", as text !!!). -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Burtsev, Dmitriy Gesendet: Mittwoch, 11. Jänner 2017 15:48 An: 'SQLite mailing list' Betreff: Re: [sqlite] Need help with System.Data.SQLite I didn't assign any value directly. This is what I did: 1. On SQLite create source table. CREATE TABLE FromNum ( id INT, Num NUMERIC(5,5) NULL); INSERT INTO FromNum (id, Num) VALUES (1, .0); 2. On MS SQL Server 2014 (SP2) create empty destination table. CREATE TABLE dbo.ToNum ( id INT NOT NULL , Num NUMERIC(5,5) NULL); I can't copy my PowerShell script in this e-mail. Got error "The message's content type was not explicitly allowed". I will try pseudocode. Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll' Create System.Data.SQLite.SQLiteConnection Connection CreateCommand() CommandText = "SELECT * FROM FromNum" System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader() Create System.Data.SqlClient.SqlConnection Create System.Data.SqlClient.SqlBulkCopy SqlConnection, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock SqlbulkCopy.DestinationTableName = "ToNum" Sqlbulkcopy.EnableStreaming = true SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not working with NUMERIC(5,5). -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB Sent: Wednesday, January 11, 2017 2:25 AM To: SQLite mailing list Subject: Re: [sqlite] Need help with System.Data.SQLite SQLite does not really care about precision and scale. See https://sqlite.org/datatype3.html for more information. The culprit lies in the use of SqlDecimal which cannot be used by System.Data.SQLite directly and has no implicit conversion to something understandable by System.Data.SQLite (for more information about SqlDecimal see https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx). However, an explicit conversion exists, so an explicit cast to System.Decimal would work. Maybe you could try this at the Point where you assign the Value (more information about the explicit operator can be found here: https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx). Also have a close look at what is being fed into the other numeric columns. I doubt these are SqlDecimals too. Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33: > Thank you for response. > > It looks like we are on different pages here. Let me start from the beginning. > > We are moving data between SQLite database and Microsoft SQL Server. At this > time we are using Excel files but we run into some Excel limitation. > I am trying to change Excel files to SQLite database files. > > The test table has several NUMERIC type columns. We have NUMERIC(11,0), > NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) > column. > It looks like the problem is not with NUMERIC type in general, but only when > precision is equal to scale. > > Exception calling "WriteToServer" with "1" argument(s): "The given value of > type SqlDecimal from the data source cannot be converted to type decimal of > the specified target column." > > > -Original Message- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB > Sent: Tuesday, January 10, 2017 2:08 AM > To: SQLite mailing list > Subject: Re: [sqlite] Need help with System.Data.SQLite > > System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and > thus the SQLite provider doesn't know how to handle it. Try using > System.Decimal as a more generic approach.
Re: [sqlite] Need help with System.Data.SQLite
I didn't assign any value directly. This is what I did: 1. On SQLite create source table. CREATE TABLE FromNum ( id INT, Num NUMERIC(5,5) NULL); INSERT INTO FromNum (id, Num) VALUES (1, .0); 2. On MS SQL Server 2014 (SP2) create empty destination table. CREATE TABLE dbo.ToNum ( id INT NOT NULL , Num NUMERIC(5,5) NULL); I can't copy my PowerShell script in this e-mail. Got error "The message's content type was not explicitly allowed". I will try pseudocode. Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll' Create System.Data.SQLite.SQLiteConnection Connection CreateCommand() CommandText = "SELECT * FROM FromNum" System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader() Create System.Data.SqlClient.SqlConnection Create System.Data.SqlClient.SqlBulkCopy SqlConnection, [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock SqlbulkCopy.DestinationTableName = "ToNum" Sqlbulkcopy.EnableStreaming = true SqlbulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here This code is working with NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3) but not working with NUMERIC(5,5). -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB Sent: Wednesday, January 11, 2017 2:25 AM To: SQLite mailing list Subject: Re: [sqlite] Need help with System.Data.SQLite SQLite does not really care about precision and scale. See https://sqlite.org/datatype3.html for more information. The culprit lies in the use of SqlDecimal which cannot be used by System.Data.SQLite directly and has no implicit conversion to something understandable by System.Data.SQLite (for more information about SqlDecimal see https://msdn.microsoft.com/en-us/library/xaktx377(v=vs.110).aspx). However, an explicit conversion exists, so an explicit cast to System.Decimal would work. Maybe you could try this at the Point where you assign the Value (more information about the explicit operator can be found here: https://msdn.microsoft.com/en-us/library/xhbhezf4.aspx). Also have a close look at what is being fed into the other numeric columns. I doubt these are SqlDecimals too. Burtsev, Dmitriy schrieb am 10.01.2017 um 15:33: > Thank you for response. > > It looks like we are on different pages here. Let me start from the beginning. > > We are moving data between SQLite database and Microsoft SQL Server. At this > time we are using Excel files but we run into some Excel limitation. > I am trying to change Excel files to SQLite database files. > > The test table has several NUMERIC type columns. We have NUMERIC(11,0), > NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) > column. > It looks like the problem is not with NUMERIC type in general, but only when > precision is equal to scale. > > Exception calling "WriteToServer" with "1" argument(s): "The given value of > type SqlDecimal from the data source cannot be converted to type decimal of > the specified target column." > > > -Original Message- > From: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB > Sent: Tuesday, January 10, 2017 2:08 AM > To: SQLite mailing list > Subject: Re: [sqlite] Need help with System.Data.SQLite > > System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and > thus the SQLite provider doesn't know how to handle it. Try using > System.Decimal as a more generic approach. If you need to be portable across > providers, you will be better off using classes from System.Data.Common > anyway. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > This message, and any of its attachments, is for the intended recipient(s) > only, and it may contain information that is privileged, confidential, and/or > proprietary and subject to important terms and conditions available at > http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the > intended recipient, please delete this message and immediately notify the > sender. No confidentiality, privilege, or property rights are waived or lost > by any errors in transmission. > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Need help with System.Data.SQLite
Thank you for response. It looks like we are on different pages here. Let me start from the beginning. We are moving data between SQLite database and Microsoft SQL Server. At this time we are using Excel files but we run into some Excel limitation. I am trying to change Excel files to SQLite database files. The test table has several NUMERIC type columns. We have NUMERIC(11,0), NUMERIC(3,2) , NUMERIC(5,3). Our code works fine until we add NUMERIC(5,5) column. It looks like the problem is not with NUMERIC type in general, but only when precision is equal to scale. Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of GB Sent: Tuesday, January 10, 2017 2:08 AM To: SQLite mailing list Subject: Re: [sqlite] Need help with System.Data.SQLite System.Data.SqlTypes.SqlDecimal is specific to the SQL Server provider and thus the SQLite provider doesn't know how to handle it. Try using System.Decimal as a more generic approach. If you need to be portable across providers, you will be better off using classes from System.Data.Common anyway. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This message, and any of its attachments, is for the intended recipient(s) only, and it may contain information that is privileged, confidential, and/or proprietary and subject to important terms and conditions available at http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the intended recipient, please delete this message and immediately notify the sender. No confidentiality, privilege, or property rights are waived or lost by any errors in transmission. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need help with System.Data.SQLite
Is this a right mail group for System.Data.SQLite ? -Original Message- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Burtsev, Dmitriy Sent: Thursday, January 05, 2017 9:08 AM To: 'sqlite-users@mailinglists.sqlite.org' Subject: [sqlite] Need help with System.Data.SQLite.SQLiteDataReader and NUMERIC(5, 5) column Hello I got this error: Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." How to reproduce. 1. On SQLite execute this statements. CREATE TABLE FromNum ( id INT, Num NUMERIC(5,5) NULL); INSERT INTO FromNum (id, Num) VALUES (1, .0); 2. On MS SQL Server 2014 (SP2) execute this statement CREATE TABLE dbo.ToNum ( id INT NOT NULL , Num NUMERIC(5,5) NULL); I can't copy my PowerShell script in this e-mail. Got error "The message's content type was not explicitly allowed". I will try pseudocode. Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll' Create System.Data.SQLite.SQLiteConnection Connection CreateCommand() CommandText = "SELECT * FROM FromNum" System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader() Create System.Data.SqlClient.SqlConnection Create System.Data.SqlClient.SqlBulkCopy with [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock bulkCopy.BatchSize = 5000 bulkCopy.DestinationTableName = "ToNum" bulkcopy.EnableStreaming = true bulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here Note: I did run it with System.Data.SqlClient.SqlDataReader as a source. It works with NUMERIC(5,5) column. I did run it with System.Data.SQLite.SQLiteDataReader but without NUMERIC(5,5) column. It works. The problem appears only when I run SQLiteDataReader as a sourse and table have NUMERIC(5,5) column. Powershell version 5, 64 bit System.Data.SQLite.dll version 1.0.103.0 64 bit .NET Framework 4.6.1 Thank you Dmitriy Burtsev This message, and any of its attachments, is for the intended recipient(s) only, and it may contain information that is privileged, confidential, and/or proprietary and subject to important terms and conditions available at http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the intended recipient, please delete this message and immediately notify the sender. No confidentiality, privilege, or property rights are waived or lost by any errors in transmission. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Need help with System.Data.SQLite.SQLiteDataReader and NUMERIC(5, 5) column
Hello I got this error: Exception calling "WriteToServer" with "1" argument(s): "The given value of type SqlDecimal from the data source cannot be converted to type decimal of the specified target column." How to reproduce. 1. On SQLite execute this statements. CREATE TABLE FromNum ( id INT, Num NUMERIC(5,5) NULL); INSERT INTO FromNum (id, Num) VALUES (1, .0); 2. On MS SQL Server 2014 (SP2) execute this statement CREATE TABLE dbo.ToNum ( id INT NOT NULL , Num NUMERIC(5,5) NULL); I can't copy my PowerShell script in this e-mail. Got error "The message's content type was not explicitly allowed". I will try pseudocode. Add-Type -Path '\\SERVER01\SQL\SQLite\netFx46\System.Data.SQLite.dll' Create System.Data.SQLite.SQLiteConnection Connection CreateCommand() CommandText = "SELECT * FROM FromNum" System.Data.SQLite.SQLiteDataReader = Command.ExecuteReader() Create System.Data.SqlClient.SqlConnection Create System.Data.SqlClient.SqlBulkCopy with [System.Data.SqlClient.SqlBulkCopyOptions]::TableLock bulkCopy.BatchSize = 5000 bulkCopy.DestinationTableName = "ToNum" bulkcopy.EnableStreaming = true bulkCopy.WriteToServer(SQLiteDataReader) <-- Got error here Note: I did run it with System.Data.SqlClient.SqlDataReader as a source. It works with NUMERIC(5,5) column. I did run it with System.Data.SQLite.SQLiteDataReader but without NUMERIC(5,5) column. It works. The problem appears only when I run SQLiteDataReader as a sourse and table have NUMERIC(5,5) column. Powershell version 5, 64 bit System.Data.SQLite.dll version 1.0.103.0 64 bit .NET Framework 4.6.1 Thank you Dmitriy Burtsev This message, and any of its attachments, is for the intended recipient(s) only, and it may contain information that is privileged, confidential, and/or proprietary and subject to important terms and conditions available at http://www.stifel.com/disclosures/emaildisclaimers/. If you are not the intended recipient, please delete this message and immediately notify the sender. No confidentiality, privilege, or property rights are waived or lost by any errors in transmission. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users