Dicas sobre o Microsoft SQL Server
1-Terminando uma proc com erro
-- saindo com erro
RAISERROR('Erro na SP10.', 16, 1)
2-alter PROCEDURE LE_ARQUIVO_TEXTO (
@FileName varchar(255),
@Text1 varchar(2000) OUT) AS
DECLARE @FS int
DECLARE @OLEResult int
DECLARE @FileID int
3- criando o file script object no servidor
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 begin
PRINT 'ERRO na criação do Scripting.FileSystemObject'
return
end
4-Abrindo o arquivo
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 1
IF @OLEResult <> 0 begin
PRINT 'Erro no OpenTextFile'
return
end
5-Lendo o texto
execute @OLEResult = sp_OAMethod @FileID, 'ReadLine', @Text1 OUT
IF @OLEResult <> 0 begin
PRINT 'Erro no Readline'
return
end
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
/*
declare @dado_lido varchar(8000)
exec Le_ARQUIVO_TEXTO 'C:\cadcnvcn_001.TXT',@dado_lido out
print 'Dado lido:' + @dado_lido
--pre-requisito : precisa que o oledb esteja habilitado
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ole Automation Procedures',1
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
*/
6-Carregando um arquivo texto para uma tabela
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
alter procedure loaddata as
BEGIN
declare @query varchar(7000)
declare @string varchar(1500)
declare @string1 varchar(500)
drop table x
create table x (name varchar(2000))
set @query ='master.dbo.xp_cmdshell "type C:\SAMPLE_FILE.TXT"'
insert x exec (@query)
declare C1 cursor local fast_forward for select name from x;
open c1
fetch next from c1 into @string
while @@FETCH_STATUS = 0
BEGIN
fetch next from c1 into @string1
if (len(@string1)<>0 or @string1<>null)
Begin
set @string=@string+@string1
END
ELSE if @string<>''
Begin
insert into filedata values(
substring(@string,1,16),
substring(@string,17,35),
substring(@string,52,25),
substring(@string,77,25),
substring(@string,102,25),
substring(@string,127,25),
substring(@string,152,25),
substring(@string,177,20),
substring(@string,197,15),
substring(@string,212,15),
substring(@string,227,50),
substring(@string,277,15),
substring(@string,292,17),
substring(@string,309,17),
substring(@string,326,17),
substring(@string,343,17),
substring(@string,360,17),
substring(@string,377,17),
substring(@string,394,3),
substring(@string,397,6),
substring(@string,403,4),
substring(@string,407,6),
substring(@string,413,40),
substring(@string,453,15),
substring(@string,468,1),
substring(@string,469,30),
substring(@string,499,30),
substring(@string,529,30),
substring(@string,559,30),
substring(@string,588,30),
substring(@string,619,30),
substring(@string,649,30),
substring(@string,679,30),
substring(@string,709,30),
substring(@string,739,30))
set @string=''
End
END
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
7-Gravando dados em um arquivo texto
CREATE PROCEDURE GRAVA_ARQUIVO_TEXTO (
--CREATE PROCEDURE GRAVA_ARQUIVO_TEXTO (
@FileName varchar(255),
@Text1 varchar(2000)) AS
DECLARE @FS int
DECLARE @OLEResult int
DECLARE @FileID int
-- criando o file script object no servidor
EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT
IF @OLEResult <> 0 PRINT 'ERRO na criação do Scripting.FileSystemObject'
--Abrindo o arquivo
execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1
IF @OLEResult <> 0 PRINT 'Erro no OpenTextFile'
--Gravando o texto
execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1
IF @OLEResult <> 0 PRINT 'Erro no WriteLine'
EXECUTE @OLEResult = sp_OADestroy @FileID
EXECUTE @OLEResult = sp_OADestroy @FS
/*
exec GRAVA_ARQUIVO_TEXTO 'C:\BKP_LOCAL_GTD01\teste_gravacao.txt','alo, alo...123...isto e um teste'
--pre-requisito : precisa que o oledb esteja habilitado
exec sp_configure 'show advanced options',1
reconfigure
sp_configure
exec sp_configure 'Ole Automation Procedures',1
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
GRAVA_ARQUIVO_TEXTO 'C:\BKP_LOCAL_GTD01\dbcc.bat','TESTE'
*/
8-Listando todos os triggers da base de dados
--SQL SERVER 2000:
select name from sysobjects where type='TR'
-- SQL SERVER 2005:
select name from sys.triggers where parent_class=1
-- Note: In SQL Server 2005, the triggers can be Assembly trigger (CLR) or
-- a SQL trigger. In addition, we can find whether the trigger is an AFTER
-- trigger or INSTEAD of trigger. Execute the following query:
select name from sys.triggers where type='TA'
select name from sys.triggers where type='TR'
select name from sys.triggers where is_instead_of_trigger=1
9-Listando todos os logins do servidor
--SQL SERVER 2000:
select * from master..syslogins where isntgroup=0 and isntname=0
--SQL SERVER 2005:
select * from sys.sql_logins
10-Listando as dependencias dos objetos na base de dados
--SQL SERVER 2000:
select * from sysdepends
--SQL SERVER 2005:
select * from sys.sql_dependencies
11-Listando todos os tipos de dados do servido SQL
--SQL SERVER 2000:
select * from systypes
-- SQL SERVER 2005:
select * from sys.systypes
12-Listando todas as mensagens de erro do servidor SQL
--SQL SERVER 2000:
select * from master..sysmessages
--SQL SERVER 2005:
select * from sys.messages
13-Arquivos fisicos dos bancos de dados da intância
SELECT name, physical_name
FROM sys.master_files
where database_id = db_id('BKP_TAB_FUNIL')
I)Composição do SQL Server :
1) Serviço MS SQL Server :
- Servidor de banco de dados
2) Serviço MS SQL Agent :
- Gerencia Jobs, Alertas e Operators
3) Serviço Ms Distributed Transaction Cordinator (DTS) :
- Permite que clientes heterogêneos acessem as transações do servidor.
II) Administração :
- SQL Server Enterprise Manager Snap-in MS Management Console (MMC):
- Cliente administrativo do servidor. Permite administrar o servidor.
III) Tools :
- SQL Server Client Configuration :
- Configura os componentes de comunicação entre o cliente e o server
- SQL Server Performance Monitor :
- Permite monitorar em real-time as estatísticas de performance do servidor
- SQL Server Profiler :
- Permite salvar continuamente as atividades do servidor bem como auditoria.
- SQL Server Query Analyser :
- Ferramenta gráfica que permite interagir com a base de dados. Permite
visualizar o plano de execução da query, as informações de estatísticas
e performance do servidor.
- SQL Server Service Management :
- Permite verificar e alterar o estado dos serviços do ms sql ( Iniciar, Parar,
pausar).
- SQL Server Setup :
- Permite instalar ou reconfigurar o servidor sql
- SQL Server Wizards :
- Coleção de ferramentas que guiam os usuários na execução de tarefas complexas.
SQL Agent : Executa as seguintes tarefas administrativas :
- Gerenciamento de alertas - alert management :
- Resultado de processos quando um job é completado ( via event log do nt )
- Notificações - notifications :
- Emissão de Email ligado aos alertas
- Execução de Jobs - job execution :
- Executa os jobs agendados
- Gerenciamento de replicação - Replication Management :
- Sincroniza os dados entre os servidores.
Versão do Framework .NET utilizadas nos servidores MS SQL Server :
Framework é o modelo utilizado na construção da aplicação, alias, framework traduzido para português seria modelo.
Ano Versão Framework
2003 1.0
2005 2.0
2008 3.5
2010 4.0
2012 4.5
2013 4.5.1
xxxx 4.5.2
2015 4.6
2017 4.6.1
xxxx 4.6.2
xxxx 4.7
xxxx 4.7.1
2019 4.7.2
xxxx 4.8
xxxx 4.8.1
Testados OK :
Dispositivos do sistema : exec sp_helpdevice
Índices de uma tabela ou objeto : sp_helpindex sysobjects
Locks ativos : sp_lock
Tipo e versão do servidor ms sql :
select 'servidor=' + @@servername + ', Versao=' + @@version
Fragmentação bd : dbcc showcontig
Opções do Servidor : exec sp_dboption
exec sp_configure
Utilização de memória : dbcc memusage
Quem esta usando o servidor : sp_who
Tabelas : select * from sysobjects where xtype='U'
exec sp_tables
Tabelas de sistemas : select * from sysobjects where xtype='S'
Stored Procedures : select * from sysobjects where xtype='P'
Índices : select * from sysobjects where xtype='X' ???
Chave estrangeira ( FK ): select * from sysobjects where xtype='F'
Chave Primária ( PK ) : select * from sysobjects where xtype='PK'
Chave Unica : select * from sysobjects where xtype='UQ'
Função do sistema : select * from sysobjects where xtype='TF' ???
Função : select * from sysobjects where xtype='IF' ???
View : select * from sysobjects where xtype='V'
Usuários : select * from sysusers
sp_helpuser
Objetos do sistema :
select * from sysobjects where left(name,3)='SYS'
Segmentos : select * from syssegments
Constraints : select * from sysconstraints
select * from sysfilegroups
Bancos de dados : SELECT * FROM master.dbo.sysdatabases
DECLARE @db_id smallint;
set @db_id = DB_ID(N'AssistServicos');
print @db_id
Deram erros :
exec xp_cmdshell "dir"
exec xp_loginconfig
sp_help sysdatabases
exec xp_msver
Não deu pra testar :
dbcc checkdb (nome_banco_dados)
dbcc_chectable(nome_tabela)
Obtenção das colunas com tipo e demais informações :
SELECT sysobjects.name AS table_name, syscolumns.name AS column_name,
systypes.name AS datatype, syscolumns.length AS length
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = 'U')
ORDER BY sysobjects.name, syscolumns.colid
SELECT sysobjects.name AS table_name, syscolumns.name AS column_name,
systypes.name AS datatype, syscolumns.LENGTH AS LENGTH
FROM sysobjects INNER JOIN syscolumns ON sysobjects.id = syscolumns.id
INNER JOIN systypes ON syscolumns.xtype = systypes.xtype
WHERE (sysobjects.xtype = 'U')ORDER BY sysobjects.name,
syscolumns.colid
Agendamento de tarefas nos servidores SQL
20 hs -> Check da integridade dos bancos de dados ( DBCC CHECKDB )
21 hs -> Compactação dos bancos de dados ( DBCC SHRINKDB )
23:30 hs -> Backup Full das bases agendadas
07:40 hs -> Execulção das tarefas diárias