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 .00000 thus becomes the integer 0. The other legal values (.00001 to .99999) 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' <sqlite-users@mailinglists.sqlite.org> 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, .00000); 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 ___________________________________________ Gunter Hick Software Engineer Scientific Games International GmbH FN 157284 a, HG Wien Klitschgasse 2-4, A-1130 Vienna, Austria Tel: +43 1 80100 0 E-Mail: h...@scigames.at This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation. _______________________________________________ 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
