Karuta’s ASP & M$ SQLserver

Dicas, códigos e outras coisinhas de meus estudos para não esquecer…

Archive for outubro \30\UTC 2015

JSON for SQL Server 2016

Posted by karuta em outubro 30, 2015

Problem

In previous tips, Introducing JSON for SQL Server 2016 and JSON Support in SQL Server 2016, you learned about the functionality available in CTP2: translating relational data into JSON string output, using FOR JSON AUTO and FOR JSON PATH. However, much more functionality is coming.

Solution

In CTP3, more elaborate functionality will be available, including the ability to translate JSON text into relational data, extract scalar values from JSON strings, and validate the format of JSON input. In this tip, I’ll demonstrate these three features briefly.

OPENJSON()

This rowset function will return relationally structured data from JSON input; the opposite of FOR JSON AUTO/PATH. A simple example:

DECLARE @json NVARCHAR(4000) = N'{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 }';

 SELECT * FROM OPENJSON(@json);

The output in this case is a rather generic list of key-value pairs, rather similar to the input:

key          value           type
----------   -------------   ----
UserID       1               2
UserName     AaronBertrand   1
Active       true            3
SignupDate   2015-10-01      1

However, you can also define more specific output structure using the WITH clause, for example (and adding a second row):

DECLARE @json NVARCHAR(4000) = N'[{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 },
 {
 "UserID" : 2,
 "UserName": "BobO''Neil",
 "Active": false,
 "SignupDate": "2014-12-13"
 }]';

 SELECT * FROM OPENJSON(@json)
 WITH 
 (
   UserID INT, 
   UserName NVARCHAR(64),
   Active BIT,
   [Started] DATETIME '$.SignupDate' -- remap column name
 );

The results are now a bit more relational:

UserID  UserName       Active  Started
------  -------------  ------  -----------------------
1       AaronBertrand  1       2015-10-01 00:00:00.000
2       BobO'Neil      0       2014-12-13 00:00:00.000

Another interesting use case for OPENSJSON() is to facilitate string splitting. In current versions of SQL Server, people typically resort to user-defined functions, CLR, or table-valued parameters . Watch how easy this work becomes with OPENJSON():

CREATE PROCEDURE dbo.ShowObjects
  @list NVARCHAR(4000)
AS
BEGIN
  SET NOCOUNT ON;

  SELECT name 
    FROM sys.all_objects
    WHERE [object_id] IN 
    (
      SELECT CONVERT(INT, value)
        FROM OPENJSON(N'[' + @list + N']')
    );
END
GO

EXEC dbo.ShowObjects @list = N'-101,-102,-103';

Results:

all_columns
all_views
all_objects

You can see more elaborate examples of using OPENJSON() in these blog posts from Microsoft’s Jovan Popovic:OPENJSON – The easiest way to import JSON text into table and JSON Support in SQL Server 2016.

JSON_VALUE()

This function will extract specific scalar values from within the JSON text. As you can imagine, JSON strings can get pretty complex, so there is a set of syntax for determining exactly where in the hierarchy to extract from. A few simple examples:

DECLARE @json NVARCHAR(4000) = N'{
 "UserID" : 1,
 "Cars": [ 
   { "Year":2014, "Make":"Jeep",   "Model":"Grand Cherokee" },
   { "Year":2010, "Make":"Nissan", "Model":"Murano", "Options":
     [{ "AC":true,"Panoramic Roof":true }]
  ]
 }';

SELECT 
  UserID = JSON_VALUE(@json, '$.UserID'),
  Model1 = JSON_VALUE(@json, '$.Cars[0].Model'),
  Model2 = JSON_VALUE(@json, '$.Cars[1].Model'),
  Has_AC = JSON_VALUE(@json, '$.Cars[1].Options[0].AC');

Results:

UserID    Model1            Model2    Has_AC
------    --------------    ------    ------
1         Grand Cherokee    Murano    true

ISJSON()

This function returns a bit value: 1 if the input is a valid JSON document; 0 if not. On its own, the usage is quite simple:

DECLARE @json NVARCHAR(4000) = N'[{
 "UserID" : 1,
 "UserName": "AaronBertrand",
 "Active": true,
 "SignupDate": "2015-10-01"
 }]';

 SELECT ISJSON(@json),  -- returns 1
        ISJSON(N'foo'); -- returns 0

The value here is not so much to interrogate individual values, but rather to define things like validation logic in triggers and check constraints. For example, if I have a table where I am storing JSON text, I may want a constraint to prevent any garbage from getting in there (keep in mind that there is no JSON type; the existing NVARCHAR type is used).

CREATE TABLE dbo.JSONExample
(
  UserID INT PRIMARY KEY,
  Attributes NVARCHAR(4000),
  CONSTRAINT [No Garbage] CHECK (ISJSON(Attributes)) = 1
);

Now, if I try to insert garbage into this column:

INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'foo';

I get the following error message:

Msg 547, Level 16, State 0
The INSERT statement conflicted with the CHECK constraint "No Garbage".
The conflict occurred in database "tempdb", table "dbo.JSONExample", 
column 'Attributes'.
The statement has been terminated.

However this will work fine:

INSERT dbo.JSONExample(UserID, Attributes) SELECT 1, N'{"garbage": false}';

Summary

The introduction of JSON functionality to SQL Server will allow for greater compatibility with existing applications and fewer barriers and cumbersome workarounds to sharing data between systems. In forthcoming related tips, I will talk in more detail about practical use cases for some of these features, and demonstrate the conversion of an EAV structure to one that uses JSON for storage instead.

 

By:
Anúncios

Posted in sql server | Leave a Comment »

Buscar ‘termos’ dentro de Store Procedures

Posted by karuta em outubro 25, 2015

select object_name(id) from syscomments where text like ‘% termos %’

Posted in sql server, store procedure | Leave a Comment »

Reading Excel Files in Classic ASP

Posted by karuta em outubro 25, 2015

Recently I had the need to source data from Excel files in the old Classic ASP platform. There are some good resources online which can help you with this, but I thought I’d log my little experience here which may hopefully expedite the process for someone else someday 🙂

In my experience most people use VB script in their ASP environment,. the past few years I have grown to prefer using JScript. I’ll provide my testing in both.

A Little Environment Preface

In my examples I will have a file called unlocodes.xlsx placed in the directory c:\temp\
The content of the Excel file looks like this:

ASP (VB)

Here is a barebones ASP sample connecting to the Excel file.

<%
ExcelFile = c:\temp\unlocodes.xlsx
SQL = SELECT [ISO 3166-1], [Country Name] FROM [Sheet1$]
Set ExcelConnection = Server.createobject(ADODB.Connection)
ExcelConnection.Open Provider=Microsoft.ACE.OLEDB.12.0;Data Source= & ExcelFile & ;Extended Properties=“”Excel 12.0 Xml;HDR=YES;IMEX=1“”;
SET RS = Server.CreateObject(ADODB.Recordset)
RS.Open SQL, ExcelConnection
Response.Write <table border=“”1“”><thead><tr>
FOR EACH Column IN RS.Fields
Response.Write <th> & Column.Name & </th>
NEXT
Response.Write </tr></thead><tbody>
IF NOT RS.EOF THEN
WHILE NOT RS.eof
Response.Write <tr>
FOR EACH Field IN RS.Fields
Response.Write <td> & Field.value & </td>
NEXT
Response.Write </tr>
RS.movenext
WEND
END IF
Response.Write </tbody></table>
RS.close
ExcelConnection.Close
%>
view rawexcel-vb.asp hosted with ❤ by GitHub

This resulted in:

ASP (JScript)

Here is a JScript sample connecting to the Excel file. In my current environment I never use serverside JScript to render HTML, rather only to serve in a JSON based API format. I trimmed down the scaffolding into the bare necessities: an Excel interface and a JSON polyfill that works in ASP JScript.

<%@ Language=JScript %>
<!–#include file=”excel.asp” –><%/* https://gist.githubusercontent.com/patcullen/096a79ca8000b367537e/raw/7a806439bdf388335f2403ba89d8f51f039fb6a8/excel.asp */%>
<!–#include file=”json.asp” –><%/* https://raw.githubusercontent.com/douglascrockford/JSONjs/master/json2.js */%>
<%
excelFile = c:/temp/unlocodes.xlsx
sql = SELECT [ISO 3166-1], [Country Name] FROM [Sheet1$]
excel.open(excelFile).query(sql, {}, function(codes) {
Response.write(
JSON.stringify(codes.asArray())
);
excel.close();
});
%>
view rawexcel-js.asp hosted with ❤ by GitHub

This resulted in:

Some Extra Notes

I crossed paths with two errors, both of which were resolved by simply choosing the correct connection string.
This error:

ADODB.Connection error '800a0e7a'

Provider cannot be found. It may not be properly installed.

and this error:

Microsoft JET Database Engine error '80004005'

External table is not in the expected format.

The ConnectionStrings.com website is a great resource for finding a connection string compatible with your installed version of Excel. I found that on my machine with Excel 2010 this connection string worked:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

Whereas on our production server we have 2013 installed:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\somefile.xlsx;Extended Properties="Excel 8.0;HDR=YES;IMEX=1";

If you continue to have problems finding the correct driver, or it complaining it’s not installed, then be sure to download and install the Microsoft Access Database Engine 2010 Redistributable. This includes the latest ACE drivers which come in 32 and 64 bit flavors. For posterity you may want to try install the 64bit version in command line using the follwoing syntax:

AccessDatabaseEngine_X64.exe /passive

Posted in asp | Leave a Comment »

DATABASE MIRRORING

Posted by karuta em outubro 22, 2015

Surgido no SQL Server 2005 esta tecnologia consegue realizar a “cópia” da database do servidor primário e a disponibiliza para o servidor Mirror. Esta operação é realizada através da transferência dos registros do transaction log (Primário para o Mirror), aplicando assim todas as transações ocorridas. Vale ressaltar que o Database Mirroring é compatível com qualquer tipo de hardware suportado pelo SQL Server.

Servidor Primário: Servidor que será a referência para a aplicação armazenar seus dados.

Servidor Mirror: Este será o servidor espelho do servidor principal. O servidor Mirror irá permanecer no estado de restore e este não pode ser acessado diretamente.

Servidor Witness: Este servidor é responsável por monitorar a disponibilidade do servidor principal e em caso de falha o mesmo notifica as aplicações através de um parâmetro na string de conexão e assim direciona para o servidor secundário. Para maiores informações referente ao funcionamento e criação da string de conexão, disponibilizo para vocês uma publicação da Microsoft abordando este tema:

https://msdn.microsoft.com/subscriptions/index/ms175484

Observação: A solução Database Mirroring não permite o espelhamento das databases de sistema (Master, Msdb, Tempdb e Model.)

MODOS DE OPERAÇÃO

Possuímos três modos operacionais para o Database Mirroring, são eles:


TABELA 1: FUNCIONALIDADES.

Apresentaremos primeiro de forma resumida os modos operacionais e funcionalidades que o Database Mirroring nos possibilita, logo a diante iremos detalharemos os componentes envolvidos em cada modo (TRANSACTION SAFETY, WITNESS, FAILOVER).

Alta Disponibilidade

O modo de alta disponibilidade ou High Availability é o único que trabalha com o failover automático e com o servidor Witness. Para o seu funcionamento o mesmo necessitará operar de forma ¹síncrona, que nos garantirá que não haverá perda de dados.

Caso o servidor principal esteja indisponível, ocorrerá o processo conhecido como “role switching”, na qual significará que o servidor mirror assumirá o papel do servidor principal e quando servidor principal antigo retornar sua disponibilidade, este assumirá o papel de servidor Mirror.

Alta Proteção

O modo de alta proteção ou High Protection requer que o transaction safety seja definido como FULL, que representará que as transações deverão trabalhar de forma síncrona, garantindo assim que não haverá perda de dados, mas este também não nos garantirá a disponibilidade da aplicação, devido a não possuir um servidor Witness, tornando assim um failover manual.

Alta Performance

O modo de alta performance ou High Performance, é o modo mais performático (rápido) comparado com outros modos operacionais, isto se dá devido a possuir o modo de transação do tipo ²assíncrona, ou seja, a transação não necessita de ser efetivada em ambos os parceiros para confirmar a transação, entretanto este método não previne na perda de dados, caso ocorra a troca de papéis dos servidores primário e Mirror.

  TABELA 2: MODOS DE OPERAÇÃO

TRANSACTION SAFETY

Este irá definir o nível de segurança que a transação terá no Database Mirror, podendo ser operada de duas formas:

FULL: Virá configurada como default, este representará o nível máximo de     segurança, onde as transações serão operadas de forma ¹ Síncrona.

OFF: Este representará o modo de alto desempenho, onde as transações serão operadas de forma ² Assíncrona.

¹ Síncrona: Essa opção exige que ambos os servidores (Principal e Mirror) confirmem a transação antes que a mesma seja efetivada. Lembramos que este tipo de sincronização eleva o nível de latência das transações.

² Assíncrona: Ao contrário da síncrona, a assíncrona confirma a transação antes que a mesma seja efetivada em ambos os nós, tornando-a mais performática, entretanto esta não previne a perda de dados.

WITNESS

Este campo informará se o modo operacional utilizará o Witness.

FAILOVER

Failover Manual: Este failover requer uma ação manual do DBA para realizar a troca do servidor principal para o Mirror, não acarretando na perda de dados, devido ao tipo de failover operar de forma síncrona.

Failover Forçado: Este também realizará a transferência de forma manual do servidor primário para o servidor Mirror, entretanto este modo do failover possibilitará a perda de dados, isto se dá por possuir o tipo de transação assíncrona.

Failover Automático: Suportado apenas pelo modo operacional de Alta Disponibilidade, este necessita do Witness para realizar o failover automático em caso de falha do servidor principal.

 

Redigida por

Posted in sql server | Leave a Comment »

SQL SERVER – Database Size Limitation in SQL Express

Posted by karuta em outubro 21, 2015

SQL 2000 Desktop Engine (MSDE) has limit of 2 GB data file(s) size.

SQL 2005 Express Edition has limit of 4 GB data file(s) size.

SQL 2008, SQL 2008 R2, SQL 2012 and SQL 2014 Express Edition has limit of 10 GB data file(s) size.

Posted in sql server | Leave a Comment »

CROSS APPLY

Posted by karuta em outubro 16, 2015

Hoje eu vou falar sobre o APPLY. Esse cara serve para fazer uma espécie de JOIN só que sem a clausula ON onde o INNER esta para o CROSS assim como o LEFT ou o RIGHT para o OUTER. Isso resulta em coisas do tipo:

SELECT      T.Nome, CA.Nome,  CA.Gols
FROM        time       T
CROSS APPLY (SELECT * FROM jogador J WHERE J.idTime = T.idTime) CA

Que tem exatamente o mesmo plano de execução e resultado disso aqui:

SELECT      T.Nome,           J.Nome,           J.Gols
FROM        time        T
INNER JOIN  Jogador     J      ON          J.idTime    = T.idTime

Então o que torna esse cara especial? Em minha opinião e experiência uma das coisas mais interessantes e com um grande potencial são as pesquisas com o TOP(n) onde o n tem uma variação de acordo com a regra de negócio em questão e, ainda mais importante, o fato de você conseguir fazer consultas com funções que retornam tabelas!

CREATE FUNCTION Exemplo(@id AS int, @n AS INT)

RETURNS TABLE

AS

RETURN

SELECT      TOP(@n)     *

FROM        jogador     j

WHERE       j.idTime    = @id

ORDER BY    j.Gols      DESC

GO

SELECT            *

FROM        Time  t

CROSS APPLY dbo.Exemplo(t.idTime, CASE WHEN(t.idTime=1) THEN 1 ELSE 2 END)

ORDER BY    t.IdTime    ASC

Vejam que as possibilidades com esse operador são grandes porem no dia a dia da “firma” são raros os casos onde ele pode/precisa ser utilizado.

 

CRÉDITOS E AGRADECIMENTOS: 

http://stackoverflow.com/questions/1139160/when-should-i-use-cross-apply-over-inner-join

Posted in sql server | Leave a Comment »