SQL Server 2014 - Instalando e configurando o driver ACE OLEDB12 para ler XLS via Linked Server

Instalando e Configurando o driver ACE OLEDB12 PARA ler arquivo XLS no SQL SERVER 2014

Caso esteja ocorrendo o erro abaixo ao tentar criar um linked server para leitura de arquivo do Excel (.xls), execute as etapas de Instalação e Configuração.

Msg 7403, Level 16, State 1, Line 1 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" has not been registered.

Caso esteja ocorrendo os erros abaixo, execute somente a etapa de Configuração(uma vez que você já tem o ACE OLEDB12 instalado)

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)" reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 2
Cannot obtain the required interface ("IID_IDBCreateCommand") from OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)".

Msg 7302, Level 16, State 1, Line 2
Cannot create an instance of OLE DB provider "MSDASC" for linked server "(null)".

OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)" returned message "Unspecified error".

Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0″ for linked server "(null)".

INSTALANDO O ACCESS DATABASE ENGINE X64

Fonte: http://blogs.msdn.com/b/farukcelik/archive/2010/06/04/accessing-excel-files-on-a-x64-machine.aspx

Siga os passos abaixo :

1)    Baixar a versão 64 bits do Acess Database Engine (AccessDatabaseEngine_X64.exe) do "Microsoft Access Database Engine 2010 Redistributable" no link abaixo :

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en&WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]

2) Instalar o AccessDatabaseEngine_X64.exe no seu Servidor SQL

3)    Abra uma Nova Janela (new query window) no  SQL Server Management Studio (SSMS) e execute os comandos abaixo : 

USE [master]

GO 

sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OverRide

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE WITH OverRide

GO

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

GO

*VERIFIQUE SE PARA O SEU CASO A OPÇÃO "ALLOW IN PROCESS" DEVE ESTAR ATIVADA OU INATIVADA. SE DER ERRO NA ETAPA 4, EXECUTE O COMANDO ACIMA COM ALLOWINPROCESS COM 0 NO LUGAR DE 1. IRÁ FUNCIONAR.

4)    Agora tente executar a query abaixo, que simula o linked server:

 select *

FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\planilha.xls',

               [plan1$])

Caso ocorra erro, execute a configuração abaixo.

 
CONFIGURANDO A CONTA DO SQL SERVER NO MSDAINITIALIZE PARA PERMITIR CONEXÃO LOCAL
 
Fontes:

http://www.codykonior.com/2014/05/02/msdainitialize-minimum-required-permissions/

http://blogs.msdn.com/b/dataaccesstechnologies/archive/2011/09/28/troubleshooting-cannot-create-an-instance-of-ole-db-provider.aspx

• Acessar o menu Iniciar\Executar e digitar: DCOMCNFG   e pressionar ENTER 
• Clicar em Computers\My Computer\DCOM Config
• Clicar na opção MSDAINITIALIZE com botão direito e clicar em Properties;
• Clicar na aba Security;
• No quadro Access Permissions, marcar Customize e clicar em Edit;
• Clicar em Add e adicionar o usuário da Instância do SQL Server (Ex. SQLServerUser.Prod)
• Marcar a opção Local Access (Allow);
• Clicar em OK, OK e APPLY.

Pronto. Agora irá funcionar. Resolvido.

 

Como fazer logoff / logout de uma sessão / compartilhamento de rede

Quando você acessa um compartilhamento remoto (\\server\arquivos) sua senha é salva.

Para remover esse login, utilize os procedimentos abaixo.

No Windows XP

- Acesse o menu Iniciar\Executar e digite:
control userpasswords2

- Clique na aba Avançado.

- Clique em Gerenciar Senhas.

- Remova a conexão que deseja fazer logout.

No Windows 7, Vista

Acesse Iniciar> Executar digite cmd  para abrir o ms-dos.
No Ms-Dos, digite: rundll32.exe keymgr.dll, KRShowKeyMgr
(tudo de uma vez inclusive a vírgula)

Será aberta essa janela, onde você pode remover senhas salvas.

Nota: Senhas salvas de sites não são removidas com esse procedimento, apenas senhas de rede e Windows Messenger.

Fonte: http://hrapytor.webnode.com.br/news/removendo-senhas-salvas-/

Dokken - Almost Over (letra)

Letra da música Almost Over, da banda Dokken.

--------------------------

We've lost our mind
We're out of time
Now it's the end of everything.

I've come to know
It's just a show
I've been a thousand times
To see it once again.

The clowns are laughing in our face,
They've won the war.

It's almost over
It's just a matter of time
It's almost over
Until the reaper rings the bell
And sends it straight to hell

I've read the news
It's over now
I don't believe a thing you say
A bastard's lie
You never cry
As you sacrifice the innocent

Liar!
Look within your soul
Standing on the mountains made from graves

It's almost over
It's just a matter of time
It's almost over
Until the reaper rings the bell
And sends us all to hell

You talked a game
They taught you oh so well
Your father must be proud
How many lives must there be lost before we bring you down
And cast away your bones

It's almost over
It's just a matter of time
It's almost over
Until the reaper rings the bell
And sends it straight to hell

Dokken - Magic Man (letra)

Letra da música Magic Man, da banda Dokken.

-------------------------------------

Magic man what's in the bag?
What will a dime get me now
It's been so long I've missed you
So send me on a ride

Give me my lady fair
My angel of love
Crystal clear in the mirror
When I shot for the moon
You met me there
And stayed with me till the morning

Magic man takes me along for a ride
We'll be upside down but it feels right to me

Over the hills so far away
Dreamin of life as it was
86 was a very good year
When I woke up and you were gone

Magic man takes me along for a ride
We'll be upside down but it feels right to me

Magic man you took me along for a ride
We were upside down but it felt right to me

Job concluído com sucesso mas mostrando In Progress no status

Eu tinha um job no SQL Server que executava um arquivo .BAT era executado / concluído com sucesso. Porém, na tabela sysjobhistory o status do step que fazia o FTP via um arquivo .BAT, ficava com status IN PROGRESS.

Não encontrei nada na internet, apenas este caso igual ao meu, mas nenhuma solução real.

http://itknowledgeexchange.techtarget.com/itanswers/the-ssis-2008-r2-job-does-not-complete-execution/

O step do meu job era do tipo Operating System (cmdExec).

E o comando era "C:\Interface\FTP_Download.bat"

Solução

Para resolver, alterei o tipo do step para o tipo Transac-SQL script (script SQL puro), e fiz o código executar o arquivo .BAT via xp_cmdshell, controlando o erro através de uma variável inteira.

DECLARE @SQL                    varchar(500)
DECLARE @Retorno                int
SET @SQL = 'C:\Interface\FTP_Download.bat'

EXEC @Retorno = MASTER..xp_cmdshelL @SQL

--Se executou com sucesso
IF (@Retorno = 0)
    PRINT 'Download via FTP concluído com sucesso'
ELSE
BEGIN
    --Se execução NÃO foi concluída com sucesso, força o erro para que o job seja concluído com erro.
    RAISERROR('Erro ao fazer download via FTP. Verifique o log do FTP',16,1)
END


Resolvido! O job não ficou mais no status IN PROGRESS.

____________________________________________

Completed job showing In Progress Status

____________________________________________

SQL Server - Erro ao fazer INSERT OPENROWSET: "Cannot update. Database or object is read-only"

O erro "OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot update.  Database or object is read-only." " ocorre ao tentar inserir registros de uma tabela em um arquivo do Excel.

Solução: retire o parâmetro IMEX=1 ou deixe-o IMEX=0.

Desta forma irá funcionar:

--------------------------

    INSERT INTO OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\teste.xls;HDR=YES;IMEX=0',

'Select Campo1, Campo2 From [Plan1$]')      

    Select Campo1

          ,Campo2

         from Tabela

------------------------

Obs: IMEX=1 funciona apenas para SELECT.

SQL Server - entendendo o master.dbo.xp_dirtree para listar conteúdo de um diretório

No SQL Server, para listar os arquivos e pastas de um diretório, utilizamos o comando master.dbo.xp_dirtree

Esta procedure possui 3 parâmetros:

1. Directory - este parâmetro indica o diretório cujo conteúdo você deseja listar
2. Depth - este parâmetro indica quantos níveis de subpasta você deseja listar. 0 irá listar todas as subpastas.
• 0 - lista todas as subpastas
• 1 - lista somente a pasta indicada no parâmetro directory
• 2- lista a pasta indicada no parâmetro directory + uma subpasta
• ... e assim por diante
3. File - indica o que você deseja listas no seu diretório:
• 0 - somente pastas
• 1 - pastas e arquivos

Esta procedure retorna 3 colunas

• Name - nome do arquivo ou pasta
• Depth - indica o nível em que o arquivo/pasta está (1 - está na pasta, 2 - está na subpasta, etc)
• IsFile - 0 indica que é uma pasta e 1 indica que é um arquivo.

==================================================

Exemplo:

===================================================

Tenho a seguinte estrutura:

• C:\Pasta1
• Arquivo1.txt
• Arquivo11.txt
• Pasta2
• Arquivo2.txt
• Arquivo22.txt
• Pasta3
• Arquivo3.txt
• Arquivo33.txt

O comando abaio irá listar todos os arquivos e pastas, mas somente no primeiro nível, ou seja, somente o conteúdo do diretório C:\Pasta. Não irá listar o conteúdo das sub-pastas.

--------------------------

CREATE TABLE #FileList(Name varchar(200), depth numeric, IsFile numeric) 

INSERT INTO #FileList exec master.dbo.xp_dirtree 'C:\Pasta1', 1, 1 

select * from #filelist

drop table #filelist

---------------------------

O comando abaixo irá listar o conteúdo da Pasta1 e o conteúdo da pasta2 (segundo nível)

exec master.dbo.xp_dirtree 'C:\Pasta1', 2, 1 

O comando abaixo irá listar o conteúdo da Pasta1 , pasta2 (segundo nível) e pasta 3 (terceiro nível)

exec master.dbo.xp_dirtree 'C:\Pasta1', 3, 1 

Caso você execute desta forma, a procedure irá retornar apenas as coluna Name e Depth, pois, uma vez que você não está listando arquivos, você não precisa da coluna IsFile.

exe master.dbo.xp_dirtree 'C:\Pasta1', 1, 0   

ou

exe master.dbo.xp_dirtree 'C:\Pasta1', 1

Oracle - alterando o tempo de expiração da senha via SQLPLUS

Caso precise alterar o tempo de expiração da senha de algum usuário e a máquina não tiver o PL/SQL instalado, você pode fazer isso via SQLPLUS.

Pelo prompt de comando (MS-DOS), digite SQLPLUS <enter>

E entre com o usuário e senha que possua permissão para este tipo de operação.

Para alterar o tempo de expiração, precisamos primeiro identificar o PROFILE que o usuário faz parte:

SELECT PROFILE FROM dba_users WHERE username = 'SEU USUARIO'


Em seguida, executamos a alteração:

ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME unlimited;


O parâmetro acima, seta o PROFILE para nunca expirar a senha. Caso queira apenas aumentar o limite, basta digitar a quantidade de dias para expiração no lugar do "unlimited".

Agora, verificamos se o parâmetro foi alterado:

SELECT LIMIT FROM DBA_PROFILES WHERE PROFILE='DEFAULT' AND RESOURCE_NAME='PASSWORD_LIFE_TIME'

SQL Server - como dar grant em todas as Procedures e Tabelas e SQL Profiler

Para gerar o script de grant de todas as tabelas e todas as procedures para um determinado usuário, utilize o exemplo abaixo, que irá retornar o script pronto para ser executado.

Mais abaixo, o script para dar grant create para o usuário, e grant para rodar o SQL Profiler.


--creating the script to grant in all tables
use [databasename]
go

SELECT 'GRANT SELECT, INSERT, UPDATE, DELETE ON ' + NAME + ' TO ' + '[Domain\Username]'
  FROM SYSOBJECTS
WHERE TYPE = 'U'
  AND CATEGORY <> 2  -ignore system tables
ORDER BY NAME

Copie as linhas retornadas no select e execute

 

-- creating script to grant in all procedures
use [databasename]
go

declare @sql nvarchar(4000)
declare @db  sysname ; set @db = DB_NAME()
declare @u   sysname ; set @u = QUOTENAME('Domain\Username')
-- or -- declare @u   sysname ; set @u = QUOTENAME('username')select 'grant exec on ' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ' TO ' + @u 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'

Copie as linhas retornadas no select e execute


--grant create procedure
grant create procedure to [Domain\Username]


--grant to run SQL Profiler
grant alter trace to [Domain\User]

 

Rave Report - Como incluir totalizador de valores (Delphi 7)

VOU ADICIONAR AS IMAGENS POSTERIORMENTE.

Existem duas formas de incluir um totalizador de valores:

·         uma, a mais simples, é utilizando o CalcTextComponent

·         a outra, mais complexa, é utilizando o CalcComponent + Parâmetro + DataText .

O CalcTextComponent é mais simples de usar. Eu o descobri quando eu tentei adicionar um novo CalcComponent  no meu relatório e não funcionou. Na verdade, ele totalizava, mas não zerava o contador a cada novo agrupamento.

Utilizando o CalcTextComponent

Primeiro, devemos inserir uma Band totalizar os valores de um DataBand.

·         Acesse a aba Report

·         Clique no ícone do Band Component

·         Clique na Region do seu relatório para adicioná-lo.

·         Ele ficará posicionado embaixo das outras Bands. Se quiser mudar sua posição, clique nele com o botão direito e clique em  Order\Move Behind;

·         Clique na Band e defina um nome para ela, clicando na Propriedade Name no lado esquerdo da tela. Ex. Footer

Agora, vamos inserir o componente que irá calcular o total:

·         Acesse a aba Report;

·         Clique no ícone do cinza CalcTextComponent;

·         Clique na Band onde quer  ele seja mostrado no seu relatório (a Band inserida acima)

·         Clique no item adicionado e altere as seguintes propriedades:

o   CalcType: ctSum (para somar)

o   Controler: qual é o DataBand que exibe os valores que você deseja totalizar.

o   DataView: qual é o dataset que possui o campo que você deseja totalizar

o   DataField: qual campo você deseja totalizar

o   DisplayFormat: defina o formato em que o valor deve ser exibido

o   Fonte:  formatação para exibição

o   Name: defina um nome para o componente, para facilitar uma futura manutenção

o   Running Total:

§  FALSE – indica que ele ira zerar o totalizador após sair da Band atual.

§  TRUE – indica que ele irá acumular o valor

Como estamos criando um sub-total, vamos selecionar a opção FALSE.

Se quiser  criar um Total Geral, selecione TRUE.

Pronto. Agora é só salvar e executar o relatório.

Utilizando o CalcTotalComponent (opção mais complexa)

Primeiro, vamos inserir uma Parameter, que nada mais é que uma variável que irá acumular o total).

·         Clique no seu relatório (report componente)

·         Localize a propriedade Parameter do lado esquerdo da tela;

·         Clique no botão com três pontos [...]

·         Na tela Strings Editor, defina um nome para o seu parâmetro e clique em OK.

Em seguida, devemos inserir uma Band totalizar os valores de um DataBand.

·         Acesse a aba Report

·         Clique no ícone do Band Component

·         Clique na Region do seu relatório para adicioná-lo.

·         Ele ficará posicionado embaixo das outras Bands. Se quiser mudar sua posição, clique nele com o botão direito e clique em  Order\Move Behind;

·         Clique na Band e defina um nome para ela, clicando na Propriedade Name no lado esquerdo da tela. Ex. Footer

Agora, vamos inserir o componente que irá calcular o total:

·         Acesse a aba Report;

·         Clique no ícone verde CalcTotalComponent;

·         Clique na Band onde quer ele ele seja mostrado no seu relatório (a Band inserida acima);

·         Clique no item adicionado e altere as seguintes propriedades no lado esquerdo da tela:

o   CalcType: ctSum (para somar)

o   Controler: qual é o DataBand que exibe os valores que você deseja totalizar.

o   DataView: qual é o dataset que possui o campo que você deseja totalizar

o   DataField: qual campo você deseja totalizar

o   Destparam: é o parâmetro que receberá o valor totalizado (selecione o parâmetro cadastrado na etapa acima);

o   DisplayFormat: defina o formato em que o valor deve ser exibido

o   Fonte:  formatação para exibição

o   Name: defina um nome para o componente, para facilitar uma futura manutenção

o   Running Total:

§  FALSE – indica que ele ira zerar o totalizador após sair da Band atual.

§  TRUE – indica que ele irá acumular o valor

Como estamos criando um sub-total, vamos selecionar a opção FALSE.

Se quiser criar  um Total Geral, selecione TRUE.

 

Por último, vamos inserir o componente que vai exibir o valor calculado pelo CalcTotalComponent e armazenado no parâmetro (variável) do relatório.

·         Acesse a aba Report;

·         Clique no ícone cinza DataText;

·          Clique na Band onde quer ele ele seja mostrado no seu relatório (a Band na primeira etapa);

·         Clique no item adicionado  e altere as seguintes propriedades no lado esquerdo da tela:

o   DataField: selecione o parâmetro que possui o total acumulado.

§  Clique no botão com três pontos [...]

§  No painel Project Parameters, localize o parâmetro que foi cadastrado e clique no botão Insert Parameter

§  Clique em OK;

Pronto. Agora é só salvar e executar o relatório.

Caso tenha problemas na totalização com o CalcTotalComponent , utilize o CalcTextComponent.