Error using export-csv in powershell ans SQL Server - Conversion overflow

Question

Im trying to automate a csv backup from a database i'm not adm. So i couldn't use most of the tools SQL Server dispose. The solution i was able to use is the following powershell code. It worked perfectly except for one specific table with a huge numeric (38 positions) field.

When i try to use the export-csv including the field, it returns the following error:

Exceção ao chamar "Fill" com "1" argumento(s): "Estouros de conversão."

No linha:29 caractere:5

$SqlAdapter.Fill($DataSet)

CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : OverflowException

Im sorry about the error in Portuguese, but my computer is managed by my company, so i can't change the language. Im gonna try to translate it:

Exception calling "Fill" with "1" argument: "Conversion overflow."

Line: 29 character: 5

$SqlAdapter.Fill($DataSet)

CategoryInfo : NotSpecified: (:) [], MethodInvocationException

FullyQualifiedErrorId : OverflowException

I already was able to export it without the big number column or filtering the field like "< 99999999999999999999999999999". The type of the column is NUMERIC(38,0) and here is some examples of values that are causing the errors.

9332003150820027276792001711182003

143295882003819000120030011493142

1411110005678200117658820118210141

1205000040601104009022001104009396

#Variables
$SQLServer = "myserver"
$SQLDBName = "myDB"
$SQLSchema = "Schema"
$delimiter = "¬"
$encoding = "UTF8"
$UseQuotes = "Always"
$tabelas = @("myTable")

#Conexão SQL
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True;"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.Connection = $SqlConnection

foreach ($tab in $tabelas) {
    #Comandos SQL
    $SqlQuery = "SELECT * from [$SQLDBName].[$SQLSchema].[$tab];"
    $SqlCmd.CommandText = $SqlQuery
    $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
    $SqlAdapter.SelectCommand = $SqlCmd
    $SqlAdapter.SelectCommand.CommandTimeout = 2400;

    #Exporta dados
    $hour = Get-Date -Format HH:mm:ss
    "Começou o export da tabela $tab às $hour"
    Get-Date -Format HH:mm:ss
    $DataSet = New-Object System.Data.DataSet
    $SqlAdapter.Fill($DataSet)
    $DataSet.Tables[0] | export-csv -Delimiter $delimiter -Encoding $encoding -Path "C:\exports\$SQLSchema.$tab.csv" -NoTypeInformation
    $hour = Get-Date -Format HH:mm:ss
    "Terminou o export da tabela $tab às $hour"
}

My guess is the process is trying to convert the column to number and the value is bigger than powershell can handle. Could anyone help/save me? =)

This unfortunately appears to be by design. .NET maximum scale is 28 for decimal structure. SQL's maximum is 38. — AdminOfThings
– AdminOfThings, Commented Oct 1, 2020 at 1:56

Kirill Pashkov · Accepted Answer · 2020-09-30 23:26:05Z

1

As a sort of workaround.

You can explicitly specify column names on SQL query and by using convert function change the data type of that numeric(38,0) column you having issues with to varchar so you could then store that number as a value of string datatype.

$SqlQuery = "SELECT col1,col2,convert(nvarchar(38),col3) as col3 from [$SQLDBName].[$SQLSchema].[$tab];"

Doing this way you could also add conditional column set depending on table names in case you have multiplue tables. Like so:

First you want to declare condition mapping via hashtable type of variable with table names with their related column names as well as possible other specific things.

$HashTable = @{
    TableName111 = 'col1';
    TableName222 = 'col1,col2,convert(varchar(38),col3)';
    TableName333 = 'col1,*';
}

Then you add the HashTable values to foreach loop to create its own SQL-query with particular column set for every table or use the default pattern.

foreach ($tab in $tabelas) {
    #Comandos SQL
    $SqlColumns = if($HashTable[$tab]){$HashTable[$tab]}else{'*'}
    $SqlQuery = "SELECT $SqlColumns from [$SQLDBName].[$SQLSchema].[$tab];"
    ...
}

edited Sep 30, 2020 at 23:26

answered Sep 30, 2020 at 22:58

Kirill Pashkov

3,2461 gold badge19 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

João Luis Rodrigues Over a year ago

Thank you for the answer, Kirill. But this is not an option for this case. As the DB evolve, new tables are droped and created every day. So, it would be a huge work mantain a mapping of every columns of every table. I was wondering if there was a way to export everything as char.

Collectives™ on Stack Overflow

Error using export-csv in powershell ans SQL Server - Conversion overflow

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related