Category Archives: Desenvolvimento

Erro FileStream .FilePath()

Resolvi escrever este forum rapido ao pegar um problema no forum de um outro usuario, ao pesquisar na internet não encontrei muitas referencias portanto deixo aqui a solução.
No SQL Server, na maioria dos casos os comandos não são CASE_SENSITIVE, ou seja, um “CREATE TABLE” funciona normalmente assim como um “create table” porem para toda regra há sua exceção e o PathName do FileStream é uma delas:

O erro gerado foi o seguinte:
Msg 4121, Level 16, State 1, Line 1
Cannot find either column “FS_ARQUIVO” or the user-defined function or aggregate “FS_ARQUIVO.pathname”, or the name is ambiguous.

O Script utilizado foi:

CREATE DATABASE FileStreamDB ON PRIMARY  (

 NAME = FileStreamDB_data,

    FILENAME = 'D:\Filestream\FileStreamDB_data.mdf'),

FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM

  ( NAME = FileStreamDB_FILE,

    FILENAME = 'D:\Filestream\arquivos')

LOG ON

  ( NAME = 'FileStreamDB_log',

    FILENAME = 'D:\Filestream\FileStreamDB_log.ldf');
GO
    
USE FileStreamDB;
GO
CREATE TABLE FSDESC (
IDFSDESC INT IDENTITY NOT NULL,
DESCRICAO VARCHAR(50) NOT NULL
)
GO
ALTER TABLE FSDESC ADD CONSTRAINT PK_FSDESC PRIMARY KEY (IDFSDESC)
GO

USE FileStreamDB;
GO
CREATE TABLE FSFILE (
ID_FSARQUIVO INT IDENTITY NOT NULL,
ID_DESC INT NOT NULL,
ID_GUID UNIQUEIDENTIFIER ROWGUIDCOL UNIQUE NOT NULL,
FS_ARQUIVO VARBINARY(MAX) FILESTREAM NULL
)
FILESTREAM_ON FileStreamGroup1;

ALTER TABLE FSFILE ADD CONSTRAINT PK_IDFSARQUIVO PRIMARY KEY (ID_FSARQUIVO)
GO
ALTER TABLE FSFILE ADD CONSTRAINT FK_DESC_ARQUIVO FOREIGN KEY (ID_DESC) REFERENCES FSDESC(IDFSDESC);
GO

--insere descrição
INSERT INTO FSDESC (DESCRICAO)
VALUES('IMAGEM .JPG'),('E-BOOK .PDF'),('OFFICE .DOC')
GO

--insere documento jpg, pdf, doc etc...
INSERT INTO FSFILE (ID_DESC,ID_GUID,FS_ARQUIVO)
SELECT 1,NEWID(),*
FROM OPENROWSET (BULK 'D:\FileStream\Elo_Perdido.jpg',SINGLE_BLOB) AS Document
GO
INSERT INTO FSFILE (ID_DESC,ID_GUID,FS_ARQUIVO)
SELECT 3,NEWID(),*
FROM OPENROWSET (BULK 'D:\FileStream\ArquivoWord.docx',SINGLE_BLOB) AS Document
GO

SELECT [FS_ARQUIVO].PathName()  FROM dbo.FSFILE --> Executado com sucesso
SELECT [FS_ARQUIVO].pathname()  FROM dbo.FSFILE --> Erro

Resumo:
Sem erro:

SELECT [FS_ARQUIVO].PathName()  FROM dbo.FSFILE

Com erro:

SELECT [FS_ARQUIVO].pathname()  FROM dbo.FSFILE
Anúncios

SQL Server 2012 – Função PARSE

Atualmente, trabalho em uma empresa aonda diariamente lidamos com valores monetarios no formato brasileiro, ou seja, “00,00”, porem, para trabalharmos no SQL Server com Float desta maneira, ele daria erro, a não pelo pelo nivel de linguagem de por default ja é padrão em ingles, então todas as vezes, trabalhamos com o mesmo sendo texto, e fazemos uma troca de “,” para “.”, podendo tratar com o Flat nativamente do SQL Server.

A dica teste post, é bem rapida, porem ao meu ver, extremamente util!…Por que não podemos, em tempo de execução, definir uma linguagem para o valor de entrada e obtermos no formato que quisermos, tipo um decimal….Bem, no SQL Server 2012 isso é possivel sim, graças a uma nova função chamada PARSE, sua execução é bem simples, porem seu ganho, enorme!

Veja exemplos:
Tente executar a seguinte consulta:

SELECT CAST(‘10,50’ AS decimal(9,2)) As Resultado

Voce tera um erro de conversão, pois no lugar da “,” deveria ser um ponto para termos os efetivos “dez e conquenta”, agora, veja como resolver este problema com o uso da função PARSE.

SELECT PARSE(‘10,50’ AS decimal(9,2) USING ‘pt-PT’) As Resultado

Voce podera notar que o retorno gerado é “10.50”, como seria se fizessemos a conversão manual, porem agora, pode trabalhar de forma muito mais simples, e mantendo o codigo muito mais limpo, sem as famosas “Gambiarras”.

Espero que tenham gostado, e assim que possivel, continuarei falando das novas funções do SQL Server 2012!!!!

Heap X Clustered, na pratica….

Galera, hoje, irei falar sobre um assunto muito legal!, algo que gosto muito de verdade, que é a ordenação fisica das pagina de uma tabela, bem, todos sabemos que o SQL Server trabalha com uma estrutura de paginas, contendo cada uma 8KB.

Bem, este tema abre varias raizes para varias explicações, neste, quero falar especificamente, ou melhor, mostrar, as diferenças entre uma tabela HEAP e uma tabela Clusterizada.

Do ponto de vista teorico temos a seguinte explicação:

Uma tabela HEAP é aquela que não contem uma indice cluster, portanto, suas paginas são ordenadas e localizadas a partir de uma tabela especial chamada IAM (Index Allocation Map, ou, como ultimamente tem sido chamada, Allocation Unit [AU]). Ja uma tabela Clusterizada possui um indice Cluster e toda sua estrutura sera feita a partir desta caracterisca, ou seja, sua paginas são ligadas diretamente uma na outra, mantendo uma ordem, segundo os dados especificados no indice Cluster.

Vamos ver na pratica como que funciona?…

Primeiro uma criação de tabela simples, algumas inserções de dados e um select apenas para confirmação de inserção:

--Criação de tabela
CREATE TABLE Teste
(
	Id UNIQUEIDENTIFIER
)

--Inserção de dados
INSERT INTO Teste VALUES (NEWID())
GO 3000

--Select de teste de inserção de dados
SELECT *
FROM Teste

Depois, o comando para verificarmos as paginas desta tabela:

DBCC IND('Master', Teste, -1)

O que é legal de observar:
Temos, pela PageType = 10, nosso IAM (ou AU), e PageType = 1, paginas de dados, alem de podermos ver que os campos NextPagePID e PrevPagePID estão todos zerados, ou seja, não ha uma ordernação das paginas.

Criação de um indice não clusterizado

CREATE INDEX IX_01 ON Teste(Id)

Nova verificação das paginas:

DBCC IND('Master', Teste, -1)

Podemos agora observar que as paginas de dados, ou seja, com PageType = 1 continuam ser ordenação entre si, porem, um novo PageType foi introduzido, o PageType 2, que quer dizer que estamos falando de uma pagina de indice, se observar os campos NextPagePID e PrevPagePID, podemos observar que os mesmo estão preenchidos, mantendo uma ordenação conforme se espera de um indice, ou seja, ainda estamos falando de uma tabela HEAP.

Agora, vamos dropar este indice não clusterizado, e criar um indice cluster nesta tabela.

DROP INDEX IX_01 ON Teste

CREATE CLUSTERED INDEX IX_01 ON Teste(Id)

Reexecução do comando de verificação das paginas:

DBCC IND('Master', Teste, -1)

Agora, podemos observar que voltamos a não ter o PageType = 2, porem, agora nossas paginas de dados estão ordenadas pelo campo Id.

Aonde isso pode me ajudar:
Deixo aqui como “lição arrumada” de um projeto de software que pegamos la na empresa, no caso, temos uma tabela de discagem de utiliza um campo NEWID, ou seja, UNIQUEIDENTIFIER como Primary Key (Assim como o campo ID no titulo), ou seja, todas as vezes de alguma inserção era feita na tabela, um novo UNIQUEIDENTIFIER era criado, como o mesmo não segue uma ordem obrigatoria de crescente ou decrescente, toda nova inserção fazia com que o SQL Server se arrumasse inteiro, realocando todas as paginas de sua tabela, com a simples remoção do indice Primary Key deste campo, fizemos qualquer operação reduzir de 45 segundos em media, para no maximo 3 segundos.

SQL Server 2012 – FileTable

Continuando os posts sobre SQL Server 2012, neste, irei comentar sobre um recurso chamado FileTable, que utiliza o recurso FileStream, ja existente em versões anteriores do SQL Server (Como o 2008….).

Bom, o que é este novo recurso? O mesmo foi introduzido com a função de faciliar o acesso a arquivos atraz de aplicações, de forma que seja automatico o mapeamento do arquivo pelo SQL Server, com apenas a simples ação de se colocar o arquivo em uma pasta.

Vale a pena comentar que aqui estou abstraindo o fato de habilitar o FileStream e sua configuração, aonde uma pasta de rede sera criada, muito provavelmente com o nome da propria instancia, no meu caso \\FABRIZZIO-PC\FABRIZZIO12ENT.

Todo o processo consiste em pequenos e simples passos, que são exemplificados a seguir:

– Criação de uma base de dados com um FileGroup com FileStream habilitado

--CRIAR BASE COM FILEGROUP DE FILESTREAM
CREATE DATABASE BlogFileTable
ON
PRIMARY ( NAME = Arch1,
    FILENAME = 'd:\SQL_Server\2012\Data\BlogFileTable.mdf'),
FILEGROUP FileStreamGroup1 CONTAINS FILESTREAM( NAME = Arch3,
    FILENAME = 'd:\SQL_Server\2012\Data\filestream')
LOG ON  ( NAME = Archlog1,
    FILENAME = 'd:\SQL_Server\2012\Log\BlogFileTable.ldf')
GO

– Alteração da base, ou seja, criação da pasta, dentro do FileStream para a utilização da FileTable

--ALTERA PARA ACESSO NÃO TRANSACIONAL
ALTER DATABASE BlogFileTable SET FILESTREAM( NON_TRANSACTED_ACCESS = FULL, 
DIRECTORY_NAME = 'BlogFileTable') WITH NO_WAIT
GO

Aqui,voce ja deve poder visualisar se o acesso FileStream esta habilitado em sua base de dados, vide select:

--SELECT DE ACESSO NÃO TRANSACIONAL
SELECT DB_NAME(database_id), non_transacted_access, non_transacted_access_desc
FROM sys.database_filestream_options;

Todos os arquivos que iremos visualizar estara contido em uma tabela, obviamente, portanto, devemos criar esta tabela, que possui uma sintaxe curiosa, e um pouco diferente de um CREATE TABLE regular…

CREATE TABLE Documentos AS FileTable
    WITH ( 
          FileTable_Directory = 'BlogFileTable',
          FileTable_Collate_Filename = database_default
         );
GO

Agora, basta voce copiar e colar os arquivos no caminho especificado, que no meu caso, para o exemplo acima é: \\FABRIZZIO-PC\fabrizzio12ent\BlogFileTable\BlogFileTable

Caso voce não esteja conseguindo abrir a pasta, execute:

--VERIFICAÇÃO DA PASTA
SELECT FileTableRootPath ('Documentos', 2)

Ou seja, trocando “Documentos” pelo nome da sua tabela FileTable

O select nesta tabela pode e deve ser feito normalmente:

SELECT *
FROM Documentos

Aonde sera possivel encontrar N informações sobre o arquivo.

Outra curiosidade é a possibilidade de se criar diretorios, ou seja, alem de automatico, sua arquivos tambem pode ficar organizados!

SQL Server 2012 – LEAD e LAG

Primeiramente queria pedir desculpas pelo tempo em que não consegui postar nada, existem varias coisas acontecendo, o que impede minha total dedicação ao Blog, todo caso, não posso deixar de postar sobre algumas coisas bem legais!!!
Essa semana começei a estudar para as provas do SQL Server 2012, portanto, muito provavelmente meus proximos posts serão todos relacionados a tal.

Para este primeiro, gostaria de comentar sobre 2 funções bem simples, porem extremamente uteis chamadas LEAD e LAG.

Muito provavelmente voces ja passaram por situações aonde certa linha deve receber o valor de uma linha de baixo ou de cima, um exemplo classico é por exemplo:

Cidade KmInicial KmFinak
Cidade a 1 50
Cidade b 51 100
Cidade c 101 105

Em situações normais até o SQL Server 2008, voce seria obrigado, para resolver este problema, a:
1. Ou armazenar os valores iniciais e finais de cada cidade, o que é bem ruim, uma vez que estou seguindo a regra de que a

proxima cidade inicia em 1Km após a anterior, eu conseguiria tal valor.
2. Utilizando Self Joins, o que pode causar a consulta um pouco complexa, principalmente para iniciantes….

Porem, no SQL Server 2012, temos as funções LEAD e LAG (Como descrito acima….), porem, o que elas fazem?

LAG: Pega qualquer valor, de uma linha acima da atual, seus valores de entrada são obrigatoriamente o campo da tabela que voce quer, o numero de linhas acima que voce quer (Que em 99% da vezes sera 1, ou seja, a linha acima) e um valor default caso o mesmo não existe (Para quando ser a primeira linha por exemplo…)

LEAD: Possui os mesmos valores de entrada, porem, ao invez de pegarmos linha acima, podemos pegar linha abaixo.
Segue exemplo de TSQL

CREATE TABLE Analises
(
	Cidade VARCHAR(100),
	Total INT
)

INSERT INTO Analises VALUES
('São Paulo', 10),
('São Paulo', 7),
('São Paulo', 6),
('São Paulo', 3),
('Campinas', 15),
('Campinas', 10)

--LEAD
SELECT Cidade, Total, LEAD(Total, 1, 0) OVER(ORDER BY Total) As Proximo_Total
FROM Analises

--LAG
SELECT Cidade, Total, LAG(Total, 1, 0) OVER(ORDER BY Total) As Ultimo_Total
FROM Analises

Exemplo pratico de utilização do comando MERGE

Um amigo meu veio me perguntar sobre algumas duvidas em relação ao comando MERGE do SQL Server, principalmente em relação a performance e sua utilização.

Abaixo segue o script que o enviei, assim como o ajudou, espero que possa ajudar outras pessoas tambem.

CREATE TABLE Funcionarios
(
	nm_Funcionario VARCHAR(100),
	pct_Comissao INT
)

CREATE TABLE Funcionarios2
(
	nm_Funcionario VARCHAR(100),
	pct_Comissao INT
)

CREATE TABLE LogTeste
(
	nm_Funcionario VARCHAR(100),
	Texto VARCHAR(100)
)

INSERT INTO Funcionarios VALUES
('Fabrizzio', 5),
('Caputo', 10),
('Antoniaci', 15)

INSERT INTO Funcionarios2 VALUES
('Giovanni', 5),
('Caputo', 20),
('Antoniaci', 25)



--Antes
SELECT * FROM Funcionarios
/*
Fabrizzio	5
Caputo		10
Antoniaci	15
*/

SELECT * FROM Funcionarios2
/*
Giovanni	5
Caputo		20
Antoniaci	25
*/

MERGE Funcionarios AS Target
USING (SELECT nm_Funcionario, pct_Comissao FROM Funcionarios2) As Source(Nome, Comissao)
ON (Target.nm_Funcionario = Source.Nome)
WHEN MATCHED THEN
	UPDATE SET Target.pct_Comissao = Source.Comissao
WHEN NOT MATCHED THEN
	INSERT (nm_Funcionario, pct_Comissao)
	VALUES (Source.Nome, Source.Comissao);
	
--DEPOIS
--Antes
SELECT * FROM Funcionarios
/*
Fabrizzio	5
Caputo		20
Antoniaci	25
Giovanni	5
*/

SELECT * FROM Funcionarios2
/*
Giovanni	5
Caputo	20
Antoniaci	25
*/

/*EM RELAÇÃO AO QUE FAZ

O merge é comando muito bom, disponivel (Se não me engano) desde o SQL Server 2005, o proposito dele é enxugar codificação
aonde com apenas um comando, voce consegue fazer N ações, como no exemplo acima que eu fiz:
	- Caso o funcionario não exista na tabela "Funcionario", o mesmo sera inserido.
	- Caso o mesmo ja exista, o pct_Comissao sera atualizado
Alem disso, é bem flexivel, a ponto de eu conseguir especificar algumas regras como:
	- Só atualize se o pct_Comissao for mais baixo
	- Atualize o Target ou o Source
	- Faça Match pelo Target ou Source ao invez de um match generico
O "problema" é que ele realmente não é dos comandos mais simples, alem de ter muitas opções, o que acaba confundindo muitas 

pessoas,
alem de algumas restrições que o fazem ser menos utilizado.

 EM RELAÇÃO A PERFORMANCE

O que meu codigo fez:
	- Inseriu novos e atualizou os ja existentes

No merge: 0,028126 de custo 
Sem merge: 0.0468959 de custo (Soma dos 2 abaixo)
Ou seja, houve sim uma queda de performance e o Merge se mostrou melhor.
Mas lembrando que performance é algo complicado e depende de indices, que por sua vez depende do percentual de atendimento 

condicional
em relação ao todo e algumas outras coisas.....
(Lembrando que estamos falando de uma tabela com 3 registros)
*/
--INSERIR NOVOS
INSERT INTO Funcionarios
SELECT b.nm_Funcionario, b.pct_Comissao
FROM Funcionarios a
RIGHT JOIN Funcionarios2 b
	ON (a.nm_Funcionario = b.nm_Funcionario)
WHERE a.nm_Funcionario IS NULL
--0,0300194
--ATUALIZAR ANTIGOS
UPDATE a
SET a.pct_Comissao = b.pct_Comissao
FROM Funcionarios a
INNER JOIN Funcionarios2 b
	ON (a.nm_Funcionario = b.nm_Funcionario)
--0,0168765

SELECT 0.0300194 + 0.0168765

Como pesquisar _ em um campo textual sem o mesmo ser um caracter coringa

Hoje, devido a falta de tempo, um post bem rapido para ajudar as pessoas com uma duvida bem simples.
Todos sabemos que quando estamos fazendo alguma comparação com VARCHAR/NVARCHAR ou qualquer outro DataType de texto podemos utilizar alguns caracteres coringas, quando não sabemos com 100% de exatidão o que queremos pesquisar.

Pois bem, então como explicação rapida temos o % para quantos e quais forem os caracteres, e temos o _ para apenas um, porem qualquer caracter.

Algumas pessoas possuem a duvida: E, como pesquisar um _ em um campo?
A resposta segue abaixo.

DECLARE @t1 TABLE (Id VARCHAR(100))

INSERT INTO @t1 VALUES
('100100'),('100_100')

SELECT *
FROM @t1
WHERE Id LIKE '%_%'

SELECT *
FROM @t1
WHERE Id LIKE '%[_]%'

SQL Server Basico 7 – Gerando um XML

Algumas pessoas pessoas possuem muitas duvidas quanto ao tipo de arquivo XML, a questão é que um arquivo XML pode parecer muito complexo quando olhado pela primeira vez, porem, com o passar do tempo se tornara bem intuitivo, simples e facil de ser utilizado.

O que é um arquivo XML?
Um arquivo XML é um arquivo estruturado de texto, ou seja, aonde o texto no arquivo segue um padrão, assim como em programação HTML.

O que tem um arquivo XML?
Basicamente um arquivo XML possui tags(Que são denominadas nós) e as informações em si.

Um exemplo…..

<Empresa>
	<Funcionarios>
		<Nome>Fabrizzio</Nome>
		<Nome> Giovanni</Nome>
	</Functionarios>
</Empresa>

Neste artigo (O primeiro de uma serie sobre XML), colocarei o basico sobre como criar uma estrutura XML a partir de uma tabela.

Abaixo explicações e codificações.


CREATE TABLE Funcionarios
(
	Id_Departamento INT,
	nm_Funcionario VARCHAR(100),
	sn_Funcionario VARCHAR(100)
)

INSERT INTO Funcionarios VALUES
(1, 'Fabrizzio','Caputo'),
(2, 'Camila', 'Lira'),
(1, 'Marco', 'Caputo')

SELECT *
FROM Funcionarios

--Criando uma estrutura XML basica de forma automatica
SELECT *
FROM Funcionarios
FOR XML AUTO
/*
<Funcionarios Id_Departamento="1" nm_Funcionario="Fabrizzio" sn_Funcionario="Caputo" />
<Funcionarios Id_Departamento="2" nm_Funcionario="Camila" sn_Funcionario="Lira" />
<Funcionarios Id_Departamento="1" nm_Funcionario="Marco" sn_Funcionario="Caputo" />
*/

--CRIANDO DE FORMA AUTOMATICA ESPECIFICANDO UM ROOT
SELECT *
FROM Funcionarios
FOR XML AUTO, ROOT('Empresa')
/*
<Empresa>
  <Funcionarios Id_Departamento="1" nm_Funcionario="Fabrizzio" sn_Funcionario="Caputo" />
  <Funcionarios Id_Departamento="2" nm_Funcionario="Camila" sn_Funcionario="Lira" />
  <Funcionarios Id_Departamento="1" nm_Funcionario="Marco" sn_Funcionario="Caputo" />
</Empresa>
*/
/*
Por que é importante colocar um root?
	Tendo em vista e necessidade de se manter sempre as boas praticas em programação, seja para voce ou para as demais
	pessoas que irão utilizar e mexer no seu sistema, colocar o root faz com que nunca exista informações jogadas em um 

arquivo XML
*/

--GERANDO INFORMAÇÕES ENTRE TAGS
SELECT Id_Departamento, nm_Funcionario, sn_Funcionario
FROM Funcionarios
FOR XML PATH('Empresa'), ROOT('Geral')
/*
<Geral>
  <Empresa>
    <Id_Departamento>1</Id_Departamento>
    <nm_Funcionario>Fabrizzio</nm_Funcionario>
    <sn_Funcionario>Caputo</sn_Funcionario>
  </Empresa>
  <Empresa>
    <Id_Departamento>2</Id_Departamento>
    <nm_Funcionario>Camila</nm_Funcionario>
    <sn_Funcionario>Lira</sn_Funcionario>
  </Empresa>
  <Empresa>
    <Id_Departamento>1</Id_Departamento>
    <nm_Funcionario>Marco</nm_Funcionario>
    <sn_Funcionario>Caputo</sn_Funcionario>
  </Empresa>
</Geral>
*/

--MESCLANDO INFORMAÇÕES NAS TAGS E ENTRE TAGS
SELECT Id_Departamento As '@Dpto', nm_Funcionario, sn_Funcionario
FROM Funcionarios
FOR XML PATH('Empresa'), ROOT('Geral')
/*
<Geral>
  <Empresa Dpto="1">
    <nm_Funcionario>Fabrizzio</nm_Funcionario>
    <sn_Funcionario>Caputo</sn_Funcionario>
  </Empresa>
  <Empresa Dpto="2">
    <nm_Funcionario>Camila</nm_Funcionario>
    <sn_Funcionario>Lira</sn_Funcionario>
  </Empresa>
  <Empresa Dpto="1">
    <nm_Funcionario>Marco</nm_Funcionario>
    <sn_Funcionario>Caputo</sn_Funcionario>
  </Empresa>
</Geral>
*/

Criptografia de dados – Alexandre Ricardo (Sherman) Di Primio


Em um periodo complicado de escrever novos posts para ajudar as pessoas, um grande amigo, Alexandre Ricardo Di Primio (Twitter: @ardp) me ajudou com um post dele, que com muito prazer, irei postar aqui para que possa ser utilizado por quem precise.

O assunto deste, é criptografia de dados em si, e não sobre os arquivos de dados, como expliquei em outros posts sobre TDE.

Creio que, assim como eu recebi o documento, ja esta bem explicado e seguindo um passo-a-passo.

1. Executar o seguinte script para criação de chave de criptografia comum as duas bases:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Senha Master do SQL utilizada para criptografar os cerificados armazenados no servidor';

CREATE CERTIFICATE myCERTIFICATE WITH SUBJECT = 'Nome do Certificado para identificação caso esse venha a ser exportado', EXPIRY_DATE = '20990621'

CREATE SYMMETRIC KEY mySymKey WITH
    KEY_SOURCE = 'Senha Primária da chave',
    ALGORITHM = TRIPLE_DES, 
    IDENTITY_VALUE = 'Senha de identificação da chave'
    ENCRYPTION BY CERTIFICATE myCERTIFICATE;

2. A chave para criptografar os dados deve ser criada apenas uma vez, e com ela criada rodar a seguinte sequencia de comandos para criptografar os dados e extraí-los no formato texto em hexadecimal:

declare 
      @Nome       char(50)
,     @NomeCrypt  varbinary(500)
,     @NomeHex    varchar(500)
select @Nome = 'Nome ou texto a ser criptografado'
OPEN SYMMETRIC KEY mySymKey DECRYPTION BY CERTIFICATE myCERTIFICATE
select @NomeCrypt =  ENCRYPTBYkey(KEY_GUID('mySymKey'), @Nome)
      -- A linha abaixo converte um varbinary em uma string com valores expressados em hexadecimal
      -- isso é necessário pois não é possível gravar um varbinary diretamente num arquivo texto comum
      -- seria possível gravar em um arquivo padrão UNICODE porém isso gera uma série de inconvenientes.
      -- Uma observação imporante é que a função sys.fn_varbintohexsubstring não é documentada
      --então deve-se ter cuidado ao usá-la pois essa pode ser descontinuada a qqr momento
select @NomeHex  = sys.fn_varbintohexsubstring(0, @NomeCrypt,1,0)
close symmetric key mySymKey
select @NomeHex, @Nome

3. Para descriptografar a informação no outro servidor rodar a seguinte sequencia da comandos:

declare 
      @Nome       char(50)
,     @NomeHex    varchar(500)
,     @NomeCrypt  varbinary(500)
,     @sql        nvarchar(max)

OPEN SYMMETRIC KEY mySymKey
DECRYPTION BY CERTIFICATE myCERTIFICATE
select @NomeHex = 'Valor criptografado em hexadecimal exatamente como saiu no select da variavel @NomeHex'

      -- Quando lemos o valor criptografado do arquivo precisamos converte-lo de hexadecimal para varbinary
      -- uma forma fácil de fazer isso é usando o sp_executesql dessa forma.
select @sql = 'select @NomeCrypt = 0x' + @NomeHex
exec sp_executesql @sql, N'@NomeCrypt varbinary(500) output', @NomeCrypt output
select @Nome =  DeCRYPTBYkey(@NomeCrypt)
close symmetric key mySymKey
select @NomeCrypt, @Nome

Erro com table function em subquery

Hoje caiu em minhas mãos uma tarefa que aparentemente era simples, mas que acabou dando um pouco mais de trabalho do que o imaginado.
A questão era a seguinte: Criar uma query, aonde uma subquery no select, era o retorno de uma subquery, que utilizava uma table function.
Algo parecido com isso:

SELECT
(SELECT [Status] FROM dbo.TableFunction(a.Id))
FROM Tabela1 a

Exemplicando melhor:

CREATE TABLE Teste
(
	ID INT IDENTITY(1,1)
)


INSERT INTO Teste DEFAULT VALUES
GO 50

CREATE FUNCTION dbo.ReturnId(@Id INT)
RETURNS TABLE
AS
RETURN
(
SELECT * FROM Teste WHERE id = @Id
)


SELECT a.Id,
(SELECT Id FROM dbo.ReturnId(a.Id))
FROM Teste a


Coisa que até o momento, era bem tranquilo, problema era que, o erro:


Msg 102, Level 15, State 1, Line 2
Sintaxe incorreta próxima a ‘.’.

Ou

Msg 102, Level 15, State 1, Line 2
Incorrect sintax near ‘.’.

Me aparecia todas as vezes.

De primeira imaginei que seria algum problema com algum SET, ou seja, alguma configuração de sessão….

Estava errado, descobri, junto com um amigo depois, que o problema estava no nivel de compatibilidade da base, como haviamos migrado recentemente para o SQL Server 2008, a base estava com compatibilidade 80, portanto a unica solução encontrada foi alterar o nivel da base para 100, como ja haviamos verificado se a aplicação funcionaria em SQL Server 2008, não houve problema, mas tomem cuidado ao realizar essa operação!