Erro 4866 com Bulk Insert

Ao me deparar com a situação de realizar uma importação de dados para o SQL Server a partir de arquivos CSV, percebi q seria uma boa oportunidade de usar o BULK INSERT.

Meu CSV é separado por vírgulas e foi escrito em unix. Isso vai nos levar ao velho problema da representação de fim de linha que é diferente entre windows e *unix.

Quando um arquivo plain-text *unix for usado com o código abaixo, o SQL Server vai enviar sempre ao invés de \n, o conjunto \r\n.

BULK INSERT [Data2010-2] 
    FROM 'C:\dataWithoutHeads.csv' 
    WITH 
    ( 
        KEEPNULLS,
        FIELDTERMINATOR = ',', 
        ROWTERMINATOR = '\n' 
    )

Esta inconsistência, vai gerar 3 erros quando for executado:

Msg 4866, Level 16, State 1, Line 1

The bulk load failed. The column is too long in the data file for row 1, column 30. Verify that the field terminator and row terminator are specified correctly.

Msg 7399, Level 16, State 1, Line 1

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Msg 7330, Level 16, State 2, Line 1

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

O grande erro a ser corrigido aqui é o 4866, que representa um erro no BULK INSERT. Os outros são consequência deste erro.

Para resolver isso de uma forma simples, no unix mesmo, antes de executar o bulk insert, use o aplicativo unix2dos. Ele existe em qualquer distribuição linux e irá “converter” as quebras de linha formato unix para o formato Windows (caso você precise fazer o caminho inverso, existe o aplicativo dos2unix) e sua sintaxe é bem simples. Em qualquer linux shell, digite:

$ unix2dos dataWithoutHeads.csv

A depender do tamanho do arquivo demorará bastante. Para se ter uma referência, na minha máquina (quad core 2.33GHz, 2gb de RAM) levou aproximadamente 10 min para converter 261MB de texto.

Agora, a execução do bulk insert será sem erros.

Searching columns by type

Today I went to review the SQL Server 2008 enhancements on XML support. To test the queries on AdventureWorks, the first thing I needed to know was: “Where are the XML data columns?”

I don’t actually have the graphic on the database model to search for and wasn’t on the mood to create the database diagram to search visually for the XML columns.

Then, I’ve got the idea of searching on the catalog for that. It should be simple to do (and it was!) but worth the post, as many people may have the need to use the same thing, not only on AdventureWorks, but on large (I mean many tables) databases:

Here is the script:

SELECT t.name, c.name, ty.name
  FROM sys.tables t INNER JOIN sys.columns c  ON
          (t.object_id = c.object_id)
      INNER JOIN sys.systypes ty ON
          (c.system_type_id = ty.xtype)
WHERE ty.name = ‘xml’

With this script, you can create a procedure that returns the table name and the column name that uses a type that’s passed by a parameter. Or even create a PowerShell function to achieve this. Let’s see the latter:

param([string] $typeName, [string] $server, [string] $database)
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection "server=$server;database=$database;Integrated Security=sspi"
$sqlConnection.Open()
$sqlCommand = $sqlConnection.CreateCommand()
$sqlCommand.CommandText = "SELECT t.name [Table], c.name [Column]
                                                  FROM sys.tables t INNER JOIN sys.columns c
                                                          ON (t.object_id = c.object_id) 
                                                                           INNER JOIN sys.systypes ty 
                                                          ON (c.system_type_id = ty.xtype)
                                               WHERE ty.name = ‘$typename ‘"
$sqlReader = $sqlCommand.ExecuteReader()
$dataTable = New-Object System.Data.DataTable
$dataTable.Load($sqlReader);
$sqlConnection.Close()
Write-Output $dataTable

Interesting, huh?

MCTS: SQL Server 2005

Bem, um pouquinho tarde, mas enfim, passei nesse sábado na prova 70-431: Microsoft SQL Server 2005 – Implementation and Maintenance, inaugurando a minha intenção de me especializar em banco de dados.

A prova é composta de duas partes, uma com 35 questões objetivas, e outra com 12 questões de simulação. A parte objetiva é bem tradicional, e pelo menos as questões que foram selecionadas na minha prova foram bem distribuídas nos assuntos. Houveram questões de XML, de desenvolvimento com CLR, backup/restore, … acho que só não caiu de Service Broker. Na parte prática, tive sorte e das 12 questões, 4 foram sobre backup/restore (para configurar um backup de acordo com algumas características pedidas).

Agora é estudar pra atualizar pra MCTS: SQL Server 2008!

Backup no SQL Server 2008

Este vai ser um post sobre um tema introdutório para administradores de banco de dados. Uma tarefa comum que deve ser realizada de tempos em tempos e pela sua importância, é importante que ocorra com frequência, para que se tenha segurança com relação a algum desastre que possa acontecer… ninguém sabe quando um erro pode ocorrer, ou quando os dados podem ficar corrompidos, por esse ou aquele motivo.

Dentro do SQL Server 2008, os backups podem ser feitos de algumas formas:

• Full
  • Faz o backup de todo o banco de dados. Isso inclui não somente o arquivo de dados mas também o log de transações, e com isso representam todo o banco de dados num determinado momento do tempo.
  • Sintaxe: BACKUP DATABASE <NOME_DO_DATABASE> TO DISK =<CAMINHO_PRO_ARQUIVO_DE_BACKUP>;
  • Ex.: BACKUP DATABASE teste TO DISK = N’C:\Backups\teste.bak’;
• Differential
  • Não confundir com backup incremental. Este backup diz respeito as diferenças ocorridas no banco de dados após o último full backup. Um backup incremental armazenaria as diferenças entre o momento atual e o último backup (que não necessariamente seria full);
  • De acordo com o que foi exposto acima, não é possível então, fazer um backup diferencial sem antes ter feito um backup full;
  • Por manipularem somente as diferenças entre um backup full e o estado atual, tendem a ser sempre backups mais rápidos de serem executados, e menores no espaço em disco ocupado;
  • Sintaxe: BACKUP DATABASE <NOME_DO_DATABASE> TO DISK <CAMINHO_PRO_ARQUIVO_DE_BACKUP> WITH DIFFERENTIAL;
  • Ex.: BACKUP DATABASE teste TO DISK = N’C:\Backups\teste.bak’ WITH DIFFERENTIAL;
• Log Full
  • Realiza o backup total do log de transações;
  • Sintaxe: BACKUP LOG <NOME_DO_DATABASE> TO DISK <CAMINHO_PRO_ARQUIVO_DE_BACKUP>;
  • Ex.: BACKUP LOG teste TO DISK = N’C:\Backups\teste.trn’ WITH DIFFERENTIAL;
• Log Differential
  • Realiza o backup diferencial do log de transações;
  • Sintaxe: BACKUP LOG <NOME_DO_DATABASE> TO DISK <CAMINHO_PRO_ARQUIVO_DE_BACKUP> WITH DIFFERENTIAL;
  • Ex.: BACKUP LOG teste TO DISK = N’C:\Backups\teste.trn’ WITH DIFFERENTIAL;

Apesar de usarmos as extensões bak e trn, não há obrigatoriedade dessas extensões, sendo somente uma boa prática as que foram usadas nos exemplos.

No SQL Server 2008 foi introduzida uma novidade que é a compressão de backup. Por default este é um recurso que está desativado. Para poder usar como um recurso padrão para todos os backups, primeiro é preciso reconfigurar a base de dados, com:

sp_configure ‘backup compression default’, 1;
go

reconfigure;

Com isso, habilitamos no servidor a compressão de backup por default, o que significa que todo banco de dados terá backups comprimidos. Foi então adicionada a query de backup a opção do with COMPRESSION ou NO_COMPRESSION, para que se possa escolher na query se se deseja um backup comprimido ou não.

Além destas formas, também é possível usar a simples estratégia de Attach/Detach que é extremamente conveniente, quando se pode deixar o banco off-line por algum tempo.

[1] White Paper sobre compressão de backup no SQL Server 2008
[2] Backing Up and Restoring How-to Topics
[3] Copy-Only Backups
[4] Full Database Backups
[5] Differential Database Backups

Who’s connected to my SQL Server?

There are two easy ways to do this. The SQL Server, have two procedures, sp_who and sp_who2 that solves this problem. Syntax:

sp_who 'loginname'

The sp_who procedure shows information about the actual users, sessions and process in a instance of SQL Server. Can be executed without parameters, and returns info about all users and sessions and processes.

The sp_who2 procedure show the same information that sp_who shows (and works the same way too), but adds the cpu time and disk io consumption on the result.

Another way is use the query below, you can retrieve the same information (in less detail) from the sysprocesses system view.

SELECT DB_NAME(dbid) as [database]
                        [login_time], 
                        [open_tran], 
                        [nt_username]
                        [hostname], 
                        [program_name] 
  FROM master..sysProcesses

[1] sp_who documentation

[2] Forum Messages about sp_who and sp_who2 (Portuguese)