SQLIte System.Data.Odbc.OdbcDataReader ussue with NUMBER(5.5) column

SQLIte System.Data.Odbc.OdbcDataReader ussue with NUMBER(5.5) column

Postby dburtsev » Wed 18 Jan 2017 20:11

Note: this is problem only with table with NUMERIC(5, 5) column.
It works with Numeric (5,3) and NUMERIC(11,0)

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, .00000);

2. On MS SQL Server 2014 execute this statement
CREATE TABLE dbo.ToNum (
id INT NOT NULL ,
Num NUMERIC(5,5) NULL);

3. Use System.Data.SqlClient.SqlBulkCopy to copy data from SQLite to MS SQL Server
Code: Select all
[string]$ToTableName = "SERVER.Test.dbo.ToNum"
[string]$FromFileName = 'N:\SqLite\outStream.db'

# get sql info
[string[]]$ttname = $ToTableName.Split(".")
[string]$ServerName = $ttname[0]
[string]$dbName = $ttname[1]
[string]$schema = $ttname[2]
[string]$TableName = $ttname[3]

$sqlString = "SELECT * FROM FromNum"

$DeleteContentsOfTableBeforeCopy = $true; #if table exists, do we clear out existing data?
$DestinationTable = $TableName #the name of the table to put it in
$Destinationinstance = $ServerName #the name of the server or instance
$Destinationdatabase = $dbName #the name of the database where you want to put the data
$DestinationWindowsSecurity = $true #or $False if you aren't using Windows security
$DestinationUserID = '' #the name of the SQL Server user if not integrated security
$DeleteContentsOfTableBeforeCopy = $false

#I just create a connection to my existing database
$con = New-Object -TypeName System.Data.Odbc.OdbcConnection

# I then give it a simple connection string
$con.ConnectionString = "DSN=SqLite;Database=N:\SqLite\outStream.db;Direct=True"
#and open the connection
$con.Open()
#We'll just start by creating a SQL statement in a command
$SourceSQLCommand = $con.CreateCommand()
$SourceSQLCommand.CommandText = $sqlString

$DestinationConnectionString = "Data Source=$Destinationinstance;Initial Catalog=$Destinationdatabase;$(
    if ($DestinationWindowsSecurity) { 'integrated security=true' }
    else
    {
        "User id=$DestinationUserID; Password=$(((Get-Credential $DestinationUserID).GetNetworkCredential()).Password); integrated security=false"
    })"

        try
        {
            #now squirt the data in using the bulk copy library.
            $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 = 50000 #The number of rows in each batch sent to the server

            $bulkCopy.BulkCopyTimeout = 0 #the number of seconds before a time-out

         $bulkCopy.WriteToServer($SourceSQLCommand.ExecuteReader()) #copy all rows to the server
        }
        catch
        {
            $ex = $_.Exception
            Write-Error "Error: $($ex.Message)"
        }
      finally
      {
      $con.Close()
      }
dburtsev
 
Posts: 1
Joined: Wed 18 Jan 2017 19:57

Re: SQLIte System.Data.Odbc.OdbcDataReader ussue with NUMBER(5.5) column

Postby AlexP » Thu 19 Jan 2017 12:15

We have reproduced the described situation and will investigate the reasons for such behavior.
AlexP
Devart Team
 
Posts: 5528
Joined: Tue 10 Aug 2010 11:35


Return to ODBC Drivers