Author Archives: admin

Write a VB Script to test connection to your sql server

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)

objConnection.Open _
“Provider=SQLOLEDB;Data Source=localhost;” & _
“Trusted_Connection=Yes;Initial Catalog=TestDB;” & _
“User ID=testUser;Password=password;”

objRecordSet.Open “SELECT * FROM Customers”, _
objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Wscript.Echo objRecordSet.RecordCount

how to check user privileges on a table in SQL Server

SELECT SUSER_NAME(), USER_NAME();

SELECT
HAS_PERMS_BY_NAME
(
N’dbo.QTest’,
N’OBJECT’,
N’DELETE’
);

EXECUTE AS LOGIN = ‘TestUser’;

–Verify the execution context is now login1.
SELECT SUSER_NAME(), USER_NAME();
SELECT
HAS_PERMS_BY_NAME
(
N’dbo.QTest’,
N’OBJECT’,
N’DELETE’
);
REVERT;
–Display current execution context.
SELECT SUSER_NAME(), USER_NAME();

Find SSRS report execution time

SELECT
ExecutionLog.ReportID
,TBL_Catalog.”Name” Report_Name
,TBL_Catalog.”Path” Report_Path
,COUNT(*) AS RunCount
,COUNT(DISTINCT ExecutionLog.UserName) AS UserCount
,MIN(ExecutionLog.TimeEnd – ExecutionLog.TimeStart ) Min_Runtime
,Max(ExecutionLog.TimeEnd – ExecutionLog.TimeStart ) Max_Runtime
,AVG(ExecutionLog.TimeEnd – ExecutionLog.TimeStart ) avg_Runtime
,MIN(ByteCount) Min_ByteCount
,Max(ByteCount) Max_ByteCount
,AVG(ByteCount) Avg_ByteCount
,MIN(RowCount) Min_RowCount
,Max(RowCount) Max_RowCount
,AVG(RowCount) Avg_RowCount
FROM
dbo.ExecutionLog ExecutionLog
INNER JOIN
/dbo.”Catalog” TBL_Catalog
ON ExecutionLog.ReportID = TBL_Catalog.ItemID

WHERE ExecutionLog.TimeStart > DATEADD(‘dd’,-30, current_date())
GROUP BY
ExecutionLog.ReportID
,TBL_Catalog.”Name”
,TBL_Catalog.”Path”
ORDER BY COUNT(*) DESC

Find the latest update on tables by user

SELECT spid,
[Current LSN],
[Operation],
[Context],
[Transaction ID],
[Description]
INTO #temp
FROM
fn_dblog (NULL, NULL),
(SELECT
[Transaction ID] AS [tid]
FROM
fn_dblog (NULL, NULL)
WHERE
[Transaction Name] LIKE ‘%%’) [fd]

WHERE
[Transaction ID] = [fd].[tid] and [Description] like ‘%update%’;
GO

—————————————————-
create table #tmp

(

spid int, ecid int, status varchar(50),loginname varchar(50),

hostname varchar(50),blk int, dbname varchar(50),

cmd varchar(150), request_id int

)

insert into #tmp

exec sp_who
—————————-
select * From #temp join #tmp on #temp.spid = #tmp.spid

drop table #temp
drop table #tmp

SQL server find all the cdc related databases and tables

DECLARE @sql as varchar(max)

SET @sql = ‘Select @@SERVERNAME DB_SERVER, NULL AS DB_NAME, NULL AS Table_name, NULL AS CDC_table_name, Null As Create_Date’

SELECT @sql=@sql+’ UNION ALL select @@SERVERNAME DB_SERVER, ”’ + name + ”’ AS DB_Name, sys.name COLLATE DATABASE_DEFAULT AS table_name
, cdc.capture_instance + ”_CT” as CDC_table_name
,cdc.Create_Date

FROM ‘ + name + ‘.sys.tables sys(NOLOCK) JOIN ‘ + name + ‘.cdc.change_tables cdc (NOLOCK) on sys.object_id = cdc.source_object_id’ +char(10)
FROM sys.databases
WHERE is_cdc_enabled = 1

EXEC (@sql)