Arquivo da categoria: SQL Server

SQL SERVER – Ler arquivo excel

Primeiro passo é instalar o AccessDatabaseEngine.exe (download feito no site da MS).

Segundo passo  é reconfigurar o banco.

EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’AllowInProcess’, 1
GO
EXEC sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′, N’DynamicParameters’, 1
GO

sp_configure ‘show advanced options’, 1
GO
RECONFIGURE
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE
GO

 

Sem seguida basta executar a consulta

SELECT * FROM OPENROWSET(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 8.0;DATABASE=C:\caminho\arquivo.XLS’,
‘SELECT * FROM [Plan1$]’)

SQL SERVER – Querys sendo executadas no banco

/********************************************************/
/* Mosta o SQL que cada sessão está executando */
/********************************************************/

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE er.statement_end_offset END –
er.statement_start_offset)/2)
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 — Ignore system spids.
AND session_Id NOT IN (@@SPID) — Ignore this current statement.
ORDER BY 1, 2

SELECT SDER.[statement_start_offset],
SDER.[statement_end_offset],
CASE
WHEN SDER.[statement_start_offset] > 0 THEN
–The start of the active command is not at the beginning of the full command text
CASE SDER.[statement_end_offset]
WHEN -1 THEN
–The end of the full command is also the end of the active statement
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, 2147483647)
ELSE
–The end of the active statement is not at the end of the full command
SUBSTRING(DEST.TEXT, (SDER.[statement_start_offset]/2) + 1, (SDER.[statement_end_offset] – SDER.[statement_start_offset])/2)
END
ELSE
–1st part of full command is running
CASE SDER.[statement_end_offset]
WHEN -1 THEN
–The end of the full command is also the end of the active statement
RTRIM(LTRIM(DEST.[text]))
ELSE
–The end of the active statement is not at the end of the full command
LEFT(DEST.TEXT, (SDER.[statement_end_offset]/2) +1)
END
END AS [executing statement],
DEST.[text] AS [full statement code]
FROM sys.[dm_exec_requests] SDER CROSS APPLY sys.[dm_exec_sql_text](SDER.[sql_handle]) DEST
WHERE SDER.session_id > 50
ORDER BY SDER.[session_id], SDER.[request_id]

 

Copiado de : http://dba-sqlserver.blogspot.com.br/2009/08/o-que-o-seu-sqlserver-esta-executando.html

 

SQL SERVER – Modificar default language

O sql abaixo exibe as linguagens disponíveis no banco de dados.

— Languages in SQL Server 2008 with dateformat

SELECT LanguageID = langid,    name,     alias,    dateformat

FROM     sys.syslanguages

ORDER BY langid

GO

O comando abaixo altera a linguagem

use BANCO_DE_DADOS

EXEC sp_defaultlanguage ‘login_usuario’, ‘name_linguagem’

GO

 

FONTE (copiado de):  http://www.sqlusa.com/bestpractices/setdefaultlanguage/