exportação/importação SQL Server/Excel

[joaofla123 3]

Já trabalhei com algumas exportações de SQL Server diretamente pra Excel, porém sempre me deparei com muitos erros e incompatibilidades. No momento não estou conseguindo fazer uma exportação e por isso gostaria de tentar sanar algumas dúvidas.

 

1 - Jet.OLEDB.4.0 e ACE.OLEDB.12.0: em que ocasiões usar cada um e quais os requerimentos de sistema? (Por exemplo se influencia 64 ou 32 bits, se é necessário instalar esses linked servers e como fazê-lo)

 

2 - Restrições de acesso: um dos erros que já presenciei é o bloqueio de acesso a um arquivo de determinado computador da rede. Quais são e onde ficam essas configurações?

 

3 - Versões do excel, 8.0 / 12.0 (etc), xls ou xlsx? Como usá-los? Eles estão relacionados com os tipos de linked servers?

 

No final das contas acho que é muita complicação e limitação pra pouca produtividade. Espero que quem soube algo contribua, mesmo que não saiba tudo. Obrigado.

 

 

[advaldomesquita 93]

joaofla123, bem vindo ao clube dos que tiveram problemas com o SSIS.

Bom,

Até o SQL2005, a exportação de dados era realmente bem complicada, pois o VS não tinha tanta integração com os dados e compatibilidade com recursos de rede.

No SQL2008 em diante, temos diversos modos de fazer uma exportação/importação de dados, tanto pelo BCP que requer um usuário com permissão no xp_cmdshell como pelo Visual Studio, onde eu prefiro trabalhar.

Eu passei um tempo para outro user, um esquema de fazer exportação de dados, passando parametro dentro de um pacote DTSx, ou seja, você monta um pacote DTsx único e passa parametro como nome do servidor, caminho do arquivo a ser exportado, por exemplo. Posso te passar também, pois estou finalizando para colocar no meu blog ainda.

Vamos as suas dúvidas:

 

1 - Jet.OLEDB.4.0 e ACE.OLEDB.12.0: em que ocasiões usar cada um e quais os requerimentos de sistema? (Por exemplo se influencia 64 ou 32 bits, se é necessário instalar esses linked servers e como fazê-lo)

 

Eu sempre usei o Jet, pois para Excel versões <= a 2000 é o único compativel. ACE.xxx é para conexões com Acess direto pelo SQL.

Para utilizar ambos, tem que ter instalado o Office na máquima em que está rodando o comando. Então começam as limitações, pois e se não tiver uma licença na máquina não funciona? Realmente não funciona, por isso utilizo o VS, pois o modo que ele trata a conexão é diferente do SQL.

 

2 - Restrições de acesso: um dos erros que já presenciei é o bloqueio de acesso a um arquivo de determinado computador da rede. Quais são e onde ficam essas configurações?

 

Bloqueios deste modo, acontece quando o arquivo tem que pré-existir no diretório, a pasta na rede não tem acesso leitura/escrita para o usuario do SQL, uma vez que as permissões tem que ter origem no SQL.

Muitas exportações são feitas com o usuário SA, pois é um usuário SYSADMIN e com permissão full na rede.

 

3 - Versões do excel, 8.0 / 12.0 (etc), xls ou xlsx? Como usá-los? Eles estão relacionados com os tipos de linked servers?

 

Ai depende (e muito) do seu ambiente.

Eu tenho clientes que usam o SQL2012 Enterprise, mas o Office é o 2003, então no meu caso utilizo do 8.0, exportando para xls, mas há casos e casos.

Realmente vai precisar dos recursos do Office 2007? É necessário xlsx?

Outro cenário:

Cliente quer o arquivo em xlsx pois vai tratar vários dados (tabela dinamica por exemplo) e as funções de um Office 2007 são mais amplas.

Em uma conversa e um briefing, mudei o relatório para ele para Repoting Services, pois eu posso colocar todas as regras de negócios em procedures.

 

No final das contas acho que é muita complicação e limitação pra pouca produtividade. Espero que quem soube algo contribua, mesmo que não saiba tudo. Obrigado.

 

Limitação não creio no caso de exportação, o que existe de limitação, e não me leve a mal por favor!! é conhecimento da ferramenta. Novamente, não me leve a mal, afinal estamos no fórum para nos ajudar.

A ferramenta de Integração de dados, é ampla se tornando complexa, mas posso fazer um passo a passo de exportação de dados em excel, txt pra você, assim podemos discutir melhor, o que acha?

[]´s

[joaofla123 3]

show a explicação, brigadao mesmo. realmente sinto dificuldade ainda pois comecei a mexer c informatica ha 6 meses so, e com sql server bem menos. falei das limitacoes pois os erros que aparecem ainda nao sao claros para q alguem sem mt entendimento como eu consiga resolver sozinho e tb uma simples exportacao acaba se tornando uma dor de cabeca devido a grande qtidade de elementos que precisam estar em sintonia pra coisa funcionar.

 

vou te mostrar o meu erro entao, pois vc entende bem do assunto:

 

 

Estou usando essas querys de teste (iguais, so muda a maquina) para importar do excel, só que apenas a de um servidor não funciona. Não sei se precisa do sql server, se é alguma incompatibilidade entre versao do office e do windows ou algum problema de permissoes de usuario.

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;HDR=YES;Database=\\Iis7-nemesis\llx_gis\Temp\RCC.xls;IMEX=1',

'SELECT NOM_EMPRESA from [Plan1$]')

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

SELECT * FROM OPENROWSET ('Microsoft.ACE.OLEDB.12.0',

'Excel 12.0;HDR=YES;Database=\\MSSQL2008-TYPHO\Banco\Data\RCC.xls;IMEX=1',

'SELECT NOM_EMPRESA from [Plan1$]')

ERRO:

Msg 7399, Level 16, State 1, Line 3

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

Msg 7303, Level 16, State 1, Line 3

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

[advaldomesquita 93]

Sem problemas. Todos tivemos um começo, uns pelo caminho fácil, outros pelas pedras :P

Seu erro é porque não tem o driver ACE.xxx instalado e/ou sua versão do SQL não é 2012.

Passei uma informação incorreta: o driver ACE.xxx é compativel com SQL2012 acima somente.

Porque não faz por linkedserver?

Eu geralmenta faço assim:

 
/***************** SQL 2000 ~ 2008R2  *****************/
EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'C:\DATABASES\teste.xls',
NULL,
'Excel 5.0'
GO
 
/***************** <= SQL 2012 *****************/
EXEC master.dbo.sp_addlinkedserver
@server = N'ExcelSource',
@srvproduct = N'ACE 12.0',
@provider = N'Microsoft.ACE.OLEDB.12.0',
@datasrc = N'C:\DATABASES\_DIVERSAS\teste.xls',
@provstr = N'Excel 12.0' 
GO
*/
 
/***************** SELECT GERAL NO EXCEL  *****************/
/*
SELECT * FROM [ExcelSource]...[Plan1$]
GO
 
SELECT * FROM OPENQUERY(ExcelSource, 'SELECT * FROM [Plan1$]')
GO
 
EXECUTE SP_TABLES_EX 'ExcelSource'
GO
 
*/

Esta tudo comentado acima.

[joaofla123 3]

A maquina na qual eu nao estou conseguindo manipular o Excel é windows server 2008 R2 64 bits, mas ela não tem SQL Server instalado. Seria esse o problema?

 

A minha versao do SQL SERVER na qual criei a query e a 2008 R2.

 

Nessas condicoes q falei posso tentar criar esses linked servers ?

[advaldomesquita 93]

Você pode criar sim, inclusive vai falar que o linked server foi criado com sucesso.

 

Deixa eu tentar simular seu cenário:

Arquivo excel em uma máquina XPTO;

Banco de Dados SQL 2008R2 em outra máquina (ABCX)

 

Você precisa pegar o arquivo da XPTO e colocar na base que esta na ABCX

 

Isso?

 

Tente rodar os comandos que postei.

Ele não altera nada em seu servidor, somente cria um linkedserver e tenta fazer um select direto no excel.