Karuta’s ASP & M$ SQLserver

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

Compare o conteúdo de 2 tabelas

Posted by karuta em fevereiro 11, 2019

select * from TABLE_A
EXCEPT
select * from TABLE_B

  • se não retornar nada é porque as tabelas estão iguais

EXCEPT

Quando se quer que apareçam os registros da tabela A que não são iguais aos da tabela B, temos a opção de ‘subtrair’ os registros utilizando EXCEPT.

Anúncios

Posted in sql server | Leave a Comment »

Index Fragmentation on ALL Indexes in a Database

Posted by karuta em fevereiro 5, 2019

Muitas vezes, quando estou usando um ambiente de desenvolvimento ou de teste e me deparo com uma situação em que uma consulta está se comportando mais devagar que o normal, a primeira coisa que quero excluir é “Tenho índices fragmentados?”. Aqui está uma consulta simples que lista todos os índices em todas as tabelas do seu banco de dados, ordenadas por porcentagem de fragmentação do índice. Essa consulta funcionará no SQL2K5 ou mais recente.
SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc

Posted in sql server | Leave a Comment »

SQL Server “Asynchronous” Stored Procedure (insert only)

Posted by karuta em janeiro 16, 2019

COPY FROM – http://abamacus.blogspot.com/2016/05/so-you-have-sql-server-stored-procedure.html – FOR CASE STUDY  – TKS 

So you have a SQL server stored procedure for logging, and its running a little slower than you like. There is no output, you just need to make sure the log message is put into the system. It would be really nice if there were a way to say “run this, and the client doesn’t need to wait for a response”, but unfortunately that’s not a simple built-in feature. So how do you do it, with a minimum of headache?

Much of the credit for this goes to http://rusanu.com/2009/08/05/asynchronous-procedure-execution/. That post solves a slightly different problem – you want to execute a slow stored procedure that has no inputs, and check back later for the output — without leaving a SQL connection open. But it was the basis of my solution here.

First, we setup a trivial example — the destination table for the log message, and the stored procedure, which is slow but eventually does the insert. We will want to see what happens if the stored procedure fails, so we’ll have a simple check to force an error.

CREATE TABLE [LogDestination]([LogValue] [varchar](max));

GO

CREATE PROCEDURE [usp_SlowProcedure]

@message VARCHAR(MAX) AS

BEGIN

WAITFOR DELAY ‘0:00:00.5’ — wait 1/2 second

IF @message LIKE ‘%ERROR%’

raiserror(@message, 16, 10);

ELSE

INSERT INTO [LogDestination]([LogValue]) VALUES(@message);

END

GO

The solution will involve creating a QUEUE and a SERVICE, so you need to make sure your database has the Service Broker turned on. You can do that with this command:

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER;

It sometimes seems to run forever — it won’t want to finish if there are any connections open on the database. You can force those to be closed with this option:

ALTER DATABASE [MyDatabase] SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;

Now to start creating the new objects. For reasons of circular dependencies, you have to CREATE one of these first, and then ALTER it later. That’s just the breaks. Let’s put down the fundamental parts:

CREATE PROCEDURE [usp_AsyncExecActivated] AS

PRINT ‘placeholder’

GO

CREATE QUEUE [AsyncExecQueue]

WITH ACTIVATION(STATUS = ON

,PROCEDURE_NAME = [usp_AsyncExecActivated]

,MAX_QUEUE_READERS = 1

,EXECUTE AS OWNER)

GO

CREATE SERVICE [AsyncExecService] ON QUEUE [AsyncExecQueue] ([DEFAULT]);

GO

CREATE PROCEDURE [usp_SlowProcedureAsync]

@message VARCHAR(MAX)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @h UNIQUEIDENTIFIER;

BEGIN DIALOG CONVERSATION @h

FROM SERVICE [AsyncExecService]

TO SERVICE ‘AsyncExecService’

WITH ENCRYPTION = OFF;

SEND ON CONVERSATION @h (@message);

END CONVERSATION @h;

END

GO

Now we have:

  • [AsyncExecQueue] – the queue to hold the messages on
  • [AsyncExecService] – because only a service can write to a queue
  • [usp_AsyncExecActivated] – stored procedure that will run in the background whenever the queue is written to
  • [usp_SlowProcedureAsync] – to wrap all this up with, hiding the mess from the users. It has the same signature as [usp_SlowProcedure], but will return immediately.

One more consideration — since this is completely asynchronous, the only errors the client can get are if the database is offline, out of space, etc. But if [usp_SlowProcedure] has an error, we want a table to just shove that input into, without leaving it on the queue. Or I guess you could omit this part, and the code that writes to it.

CREATE TABLE [AsyncErrored]([LogValue] [varbinary](max), [ErrorTime] DATETIME2 DEFAULT SYSDATETIME());

GO

Now we ALTER the background stored procedure, which is still just a placeholder, to actually do the processing.

ALTER PROCEDURE usp_AsyncExecActivated

AS

BEGIN

SET NOCOUNT ON;

DECLARE @h UNIQUEIDENTIFIER = NULL

, @messageTypeName SYSNAME = NULL

, @messageBody VARBINARY(MAX) = NULL;

RECEIVE TOP(1)

@h = [conversation_handle]

, @messageTypeName = [message_type_name]

, @messageBody = [message_body]

FROM [AsyncExecQueue];

WHILE (@h IS NOT NULL)

BEGIN

BEGIN TRY

DECLARE @message VARCHAR(MAX) = CAST(@messageBody AS VARCHAR(MAX));

EXECUTE [usp_SlowProcedure] @MESSAGE

END TRY

BEGIN CATCH

INSERT INTO [AsyncErrored]([LogValue]) VALUES(@messageBody);

END CATCH

END CONVERSATION @h WITH CLEANUP;

SET @h = NULL;

RECEIVE TOP(1)

@h = [conversation_handle]

, @messageTypeName = [message_type_name]

, @messageBody = [message_body]

FROM [AsyncExecQueue];

END — WHILE

END — PROCEDURE usp_AsyncExecActivated

GO

That’s all there is to it. But what good is a bunch of tables without running some tests to see it in action?

DECLARE @countQueue VARCHAR(10), @countDestination VARCHAR(10), @countErrored VARCHAR(10)

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ‘ Test 1: invoke [usp_SlowProcedureAsync] one time with simple input.’

EXECUTE [usp_SlowProcedureAsync] ‘Test input message’;

SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ‘ Queue=’ + @countQueue + ‘ Destination=’ + @countDestination + ‘ Errored=’ + @countErrored

WHILE (@countQueue <> ‘0’)

BEGIN

WAITFOR DELAY ‘0:00:00.2’

SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ‘ Queue=’ + @countQueue + ‘ Destination=’ + @countDestination + ‘ Errored=’ + @countErrored

END

DELETE FROM [LogDestination];

DELETE FROM [AsyncErrored];

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ‘ Test 2: invoke [usp_SlowProcedureAsync] five times, with fourth causing an error in [usp_SlowProcedure].’

EXECUTE [usp_SlowProcedureAsync] ‘Test message 1’;

EXECUTE [usp_SlowProcedureAsync] ‘Test message 2’;

EXECUTE [usp_SlowProcedureAsync] ‘Test message 3’;

EXECUTE [usp_SlowProcedureAsync] ‘Test ERROR 4’;

EXECUTE [usp_SlowProcedureAsync] ‘Test message 5’;

SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ‘ Queue=’ + @countQueue + ‘ Destination=’ + @countDestination + ‘ Errored=’ + @countErrored

WHILE (@countQueue <> ‘0’)

BEGIN

WAITFOR DELAY ‘0:00:00.2’

SET @countQueue = (SELECT COUNT(1) FROM [AsyncExecQueue])

SET @countDestination = (SELECT COUNT(1) FROM [LogDestination])

SET @countErrored = (SELECT COUNT(1) FROM [AsyncErrored])

PRINT CAST(CAST(SYSDATETIME() AS TIME) AS VARCHAR(11)) + ‘ Queue=’ + @countQueue + ‘ Destination=’ + @countDestination + ‘ Errored=’ + @countErrored

END

Here’s some output I got from the above test run. You can see the 1/2 second pauses between each insert being processed.

15:59:16.62 Test 1: invoke [usp_SlowProcedureAsync] one time with simple input.

15:59:16.85 Queue=2 Destination=0 Errored=0

15:59:17.07 Queue=1 Destination=0 Errored=0

15:59:17.28 Queue=1 Destination=0 Errored=0

15:59:17.48 Queue=0 Destination=1 Errored=0

(1 row(s) affected)

(0 row(s) affected)

15:59:17.48 Test 2: invoke [usp_SlowProcedureAsync] five times, with fourth causing an error in [usp_SlowProcedure].

15:59:17.48 Queue=9 Destination=0 Errored=0

15:59:17.70 Queue=9 Destination=0 Errored=0

15:59:17.90 Queue=9 Destination=0 Errored=0

15:59:18.10 Queue=7 Destination=1 Errored=0

15:59:18.30 Queue=7 Destination=1 Errored=0

15:59:18.51 Queue=7 Destination=2 Errored=0

15:59:18.71 Queue=5 Destination=2 Errored=0

15:59:18.91 Queue=5 Destination=2 Errored=0

15:59:19.12 Queue=3 Destination=3 Errored=0

15:59:19.32 Queue=3 Destination=3 Errored=0

15:59:19.52 Queue=3 Destination=3 Errored=0

15:59:19.72 Queue=1 Destination=3 Errored=1

15:59:19.93 Queue=1 Destination=3 Errored=1

15:59:20.13 Queue=0 Destination=4 Errored=1

And if you run all of that SQL, and are done playing with it, here’s the easy clean-up:

DROP TABLE [LogDestination]

DROP PROCEDURE [usp_SlowProcedure]

DROP TABLE [AsyncErrored]

DROP PROCEDURE [usp_AsyncExecActivated]

DROP SERVICE [AsyncExecService]

DROP PROCEDURE [usp_SlowProcedureAsync]

DROP QUEUE [AsyncExecQueue]

I hope someone eventually finds this useful; but if not, I will hopefully remember that I put this out here =-]

Posted in Sem categoria | Leave a Comment »

Saber se o “Service Broker” está ativo nas tabelas

Posted by karuta em janeiro 16, 2019

SELECT Name, is_broker_enabled
FROM sys.databases

Posted in sql server | Leave a Comment »

Tratamento de erros no SQL Server (TRY-CATCH)

Posted by karuta em agosto 24, 2018

— Criando tabela para teste 
CREATE TABLE #teste (id int)

— Adicionando restrição à tabela.
— Somente IDs menores do que 10 serão aceitos 

ALTER TABLE #teste ADD CONSTRAINT chkID CHECK (id < 10)

 

— Executando a tarefa em transação
BEGIN TRANSACTION

BEGIN TRY
        
    INSERT INTO #teste VALUES (5)
    INSERT INTO #teste VALUES (2)
    INSERT INTO #teste VALUES (13)  — Inserção de ID inválido

    COMMIT TRANSACTION — Efetivando alterações na base

END TRY
BEGIN CATCH
    
    PRINT ‘Erro ao executar script’
    ROLLBACK TRANSACTION — Desfazendo as alterações na base
          
END CATCH

SELECT * FROM #teste

DROP TABLE #teste

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

Uso de WITH e MERGE para inserção em lote pegando os ID do campo Identity Increment

Posted by karuta em agosto 24, 2018

agenda 1 – tabela com os dados [id_agenda1, nome, telefone]

agenda 2  – tabela nova que vai receber os dados [id_agenda2, nome, telefone]

agenda 3 – id da agenda1 e id criado na agenda 2 [id_agenda1, id_agenda2]

 


;WITH magica
AS (
SELECT * FROM [agenda1]
)
MERGE INTO [agenda2]
USING magica Y
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (nome, telefone)
OUTPUT INSERTED.id_agenda2, Y.id_agenda1  INTO agenda3;

Posted in sql server, store procedure | 2 Comments »

Função para criar JSON a partir de um XML

Posted by karuta em agosto 16, 2018

— Como usar:
SELECT dbo.FlattenedJSON(
(select top 10 artista_id, empresa_id, data_alteracao, login_alteracao from yb_artistas
FOR XML path, root)
)
— —————————————————————————————————————–
CREATE FUNCTION dbo.FlattenedJSON (@XMLResult XML)
RETURNS NVARCHAR(MAX)
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE  @JSONVersion NVARCHAR(MAX), @Rowcount INT
SELECT @JSONVersion = , @rowcount=COUNT(*) FROM @XMLResult.nodes(‘/root/*’) x(a)
SELECT @JSONVersion=@JSONVersion+
STUFF(
  (SELECT TheLine FROM
    (SELECT ‘,
    {‘+
      STUFF((SELECT ‘,”‘+COALESCE(b.c.value(‘local-name(.)’, ‘NVARCHAR(255)’),)+‘”:”‘+
       REPLACE( –escape tab properly within a value
         REPLACE( –escape return properly
           REPLACE( –linefeed must be escaped
             REPLACE( –backslash too
               REPLACE(COALESCE(b.c.value(‘text()[1]’,‘NVARCHAR(MAX)’),),–forwardslash
               ‘\’, ‘\\’),
              ‘/’, ‘\/’),
          CHAR(10),‘\n’),
         CHAR(13),‘\r’),
       CHAR(09),‘\t’)
     +‘”‘
     FROM x.a.nodes(‘*’) b(c)
     FOR XML PATH(),TYPE).value(‘(./text())[1]’,‘NVARCHAR(MAX)’),1,1,)+‘}’
   FROM @XMLResult.nodes(‘/root/*’) x(a)
   ) JSON(theLine)
  FOR XML PATH(),TYPE).value(‘.’,‘NVARCHAR(MAX)’ )
,1,1,)
IF @Rowcount>1 RETURN ‘[‘+@JSONVersion+
]’
RETURN @JSONVersion
END

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

IIS SERVER 2016 – ERROS AMIGÁVEIS NO ASP – Microsoft-IIS/10.0

Posted by karuta em junho 26, 2018

Clicar em: “Erros Pages” (no IIS)

Botão direito: “Edit Feature Settings”

Marcar: Detailed erros

 

Posted in asp | Leave a Comment »

Excel-to SQL INSERT Commands Text Wizard

Posted by karuta em abril 9, 2018

Great solution in web

http://tools.perceptus.ca/text-wiz.php?ops=7

Posted in Sem categoria | Leave a Comment »

“SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS” in the equal to operation.

Posted by karuta em março 22, 2018

Se acontecer na tabela temporária use o seguinte exemplo:

 

SELECT arquivo, count(arquivo) as total, min(data_inicial) as data_inicial, max(data_final) data_final, sum(valor) as soma_valor,
max(canal_de_vendas) as canal_vendas, max(moeda) as moeda, max(aprovado) as aprovado
FROM yb_royalty
WHERE arquivo IN (select arquivo COLLATE DATABASE_DEFAULT from #lista) AND empresa_id = @empresa_id AND moeda = @moeda
GROUP BY arquivo

 

Posted in Sem categoria | Leave a Comment »