Tuesday, February 01, 2011
Thursday, January 27, 2011
Find Clustered Indexes that are uniqueidentifier field
SELECT
OBJECT_NAME(i.ID) as tablename
,ISNULL(SYSCOLUMNS.NAME,'') as columnname
,i.name as indexname
, i.indid as isclustered
,systypes.name as columndatatype
--,*
FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
inner join systypes on systypes.xtype = SYSCOLUMNS.xtype
inner join sysobjects on sysobjects.id = i.id
WHERE I.INDID =1
AND I.INDID < 255 AND (I.STATUS & 64)=0 and SYSCOLUMNS.xtype = 36 and i.id > 255
and sysobjects.xtype = 'U'
order by
OBJECT_NAME(i.ID)
OBJECT_NAME(i.ID) as tablename
,ISNULL(SYSCOLUMNS.NAME,'') as columnname
,i.name as indexname
, i.indid as isclustered
,systypes.name as columndatatype
--,*
FROM SYSINDEXES I
INNER JOIN SYSINDEXKEYS ON I.ID=SYSINDEXKEYS.ID AND I.INDID=SYSINDEXKEYS.INDID
INNER JOIN SYSCOLUMNS ON SYSINDEXKEYS.ID=SYSCOLUMNS.ID AND SYSINDEXKEYS.COLID=SYSCOLUMNS.COLID
inner join systypes on systypes.xtype = SYSCOLUMNS.xtype
inner join sysobjects on sysobjects.id = i.id
WHERE I.INDID =1
AND I.INDID < 255 AND (I.STATUS & 64)=0 and SYSCOLUMNS.xtype = 36 and i.id > 255
and sysobjects.xtype = 'U'
order by
OBJECT_NAME(i.ID)
Tuesday, January 25, 2011
drop indexes for all tables in DB
sp_msforeachtable
'
declare @name varchar(50) ,@errorsave int, @tab_name varchar(100) = ''?''
if exists (select name from sysindexes
where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0) begin declare ind_cursor cursor for select name from sysindexes where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0
open ind_cursor
fetch next from ind_cursor into @name
while (@@fetch_status = 0)
begin
print ''drop index '' + @tab_name + ''.'' + @name
print ''go''
fetch next from ind_cursor into @name
end
close ind_cursor
deallocate ind_cursor
end
'
'
declare @name varchar(50) ,@errorsave int, @tab_name varchar(100) = ''?''
if exists (select name from sysindexes
where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0) begin declare ind_cursor cursor for select name from sysindexes where id = object_id(@tab_name) and indid > 0 and indid < 255 and (status & 64)=0
open ind_cursor
fetch next from ind_cursor into @name
while (@@fetch_status = 0)
begin
print ''drop index '' + @tab_name + ''.'' + @name
print ''go''
fetch next from ind_cursor into @name
end
close ind_cursor
deallocate ind_cursor
end
'
Monday, January 24, 2011
sp_alert_jobs
USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[sp_alert_jobs] Script Date: 01/24/2011 07:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_alert_jobs]
(
@maxrundurationhour int = 6
)
as
begin
--CAN NOT EXECUTE THIS PROC BY QUERY , ONLY RUNS FROM JOB
if(object_id('msdb..#tmp')>1)
drop table #tmp
if(object_id('msdb..__tmp_final')>1)
drop table __tmp_final
declare @maxrunduration int = 0
--set @maxrunduration = 60 * 60 * 6 -- 6 hours
set @maxrunduration = 60 * 60 * @maxrundurationhour
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLD4SW2';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * From #tmp
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration
into __tmp_final
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where --j.name like 'csod%'
h.run_status != 1
or h.run_duration > @maxrunduration
--select * from __tmp_final
--select 'Job = '+jobname+' : Status = '+lastrunoutcome+ ' : Run Duration = '+ convert(nvarchar(10),lastrunduration)+' min' from __tmp_final
if(select COUNT(*) from __tmp_final) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'SQL JOB ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'ayegudkin@csod.com;palexander@csod.com;Defcon4@csod.com'
exec msdb.dbo.sp_send_dbmail
@body_format = 'HTML',
@profile_name = 'default',
@Subject = @subject,
--@body = 'test',
@query = 'set nocount on; select ''Job = ''+jobname+'' : Status = ''+lastrunoutcome+ '' : Run Duration = ''+ convert(nvarchar(10),lastrunduration)+'' min
'' from __tmp_final;set nocount off; ',
--@attach_query_result_as_file = 0,
@query_result_header = 0,
--@query_result_width = 1000,
--@exclude_query_output = 1,
--@query_result_separator = ';',
@execute_query_database = 'msdb',
@recipients = @recipientslist
end
--EXEC sys.xp_logininfo @acctname = 'CSODMGMT\palexander', @option ='members'
--EXEC sys.xp_logininfo @acctname = 'CSODProd\SQLClusterLA4SW4'
end
GO
/****** Object: StoredProcedure [dbo].[sp_alert_jobs] Script Date: 01/24/2011 07:50:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[sp_alert_jobs]
(
@maxrundurationhour int = 6
)
as
begin
--CAN NOT EXECUTE THIS PROC BY QUERY , ONLY RUNS FROM JOB
if(object_id('msdb..#tmp')>1)
drop table #tmp
if(object_id('msdb..__tmp_final')>1)
drop table __tmp_final
declare @maxrunduration int = 0
--set @maxrunduration = 60 * 60 * 6 -- 6 hours
set @maxrunduration = 60 * 60 * @maxrundurationhour
create table #tmp (job_id uniqueidentifier NOT NULL,last_run_date nvarchar (20) NOT NULL,last_run_time nvarchar (20) NOT NULL,next_run_date nvarchar (20) NOT NULL,next_run_time nvarchar (20) NOT NULL, next_run_schedule_id INT NOT NULL,requested_to_run INT NOT NULL,request_source INT NOT NULL,request_source_id sysname COLLATE database_default NULL, running INT NOT NULL,current_step INT NOT NULL,current_retry_attempt INT NOT NULL,job_state INT NOT NULL);
insert into #tmp EXECUTE master.dbo.xp_sqlagent_enum_jobs 1,'CSODProd\SQLClusterLD4SW2';
UPDATE #tmp SET last_run_time = right ('000000' + last_run_time, 6), next_run_time = right ('000000' + next_run_time, 6);
--select * From #tmp
SELECT @@servername as ServerName , j.name AS JobName, j.enabled AS Enabled,
Case x.running WHEN 1 THEN 'Running' Else Case h.run_status WHEN 2 THEN 'Inactive' WHEN 4 THEN 'Inactive' Else 'Completed' End End AS CurrentStatus,
coalesce (x.current_step, 0) AS CurrentStepNbr,
CASE WHEN x.last_run_date > 0 THEN convert (datetime, substring (x.last_run_date, 1, 4)+ '-' + substring (x.last_run_date, 5, 2)+ '-'+ substring (x.last_run_date, 7, 2)+ ' '+ substring (x.last_run_time, 1, 2) + ':' + substring (x.last_run_time, 3, 2)+ ':' + substring (x.last_run_time, 5, 2)+ '.000',121) Else NULL End AS LastRunTime,
Case h.run_status WHEN 0 THEN 'Fail'WHEN 1 THEN 'Success' WHEN 2 THEN 'Retry' WHEN 3 THEN 'Cancel' WHEN 4 THEN 'In progress' End AS LastRunOutcome,
CASE WHEN h.run_duration > 0 THEN (h.run_duration / 1000000) * (3600 * 24) + (h.run_duration / 10000 % 100) * 3600 + (h.run_duration / 100 % 100) * 60 + (h.run_duration % 100) Else NULL End AS LastRunDuration
into __tmp_final
FROM #tmp x
Left Join msdb.dbo.sysjobs j ON x.job_id = j.job_id
LEFT OUTER JOIN msdb.dbo.syscategories c ON j.category_id = c.category_id
LEFT OUTER JOIN msdb.dbo.sysjobhistory h ON x.job_id = h.job_id AND x.last_run_date = h.run_date AND x.last_run_time = h.run_time AND h.step_id = 0
where --j.name like 'csod%'
h.run_status != 1
or h.run_duration > @maxrunduration
--select * from __tmp_final
--select 'Job = '+jobname+' : Status = '+lastrunoutcome+ ' : Run Duration = '+ convert(nvarchar(10),lastrunduration)+' min' from __tmp_final
if(select COUNT(*) from __tmp_final) >0
begin
declare @subject nvarchar(1000), @recipientslist nvarchar(1000)
set @subject = 'SQL JOB ALERT for '+@@SERVERNAME+' : '+Convert(nvarchar(100),getdate(),101)
set @recipientslist = 'ayegudkin@csod.com;palexander@csod.com;Defcon4@csod.com'
exec msdb.dbo.sp_send_dbmail
@body_format = 'HTML',
@profile_name = 'default',
@Subject = @subject,
--@body = 'test',
@query = 'set nocount on; select ''Job = ''+jobname+'' : Status = ''+lastrunoutcome+ '' : Run Duration = ''+ convert(nvarchar(10),lastrunduration)+'' min
'' from __tmp_final;set nocount off; ',
--@attach_query_result_as_file = 0,
@query_result_header = 0,
--@query_result_width = 1000,
--@exclude_query_output = 1,
--@query_result_separator = ';',
@execute_query_database = 'msdb',
@recipients = @recipientslist
end
--EXEC sys.xp_logininfo @acctname = 'CSODMGMT\palexander', @option ='members'
--EXEC sys.xp_logininfo @acctname = 'CSODProd\SQLClusterLA4SW4'
end
Tuesday, January 11, 2011
Monday, January 10, 2011
Run an SSIS Package Under a Different Account - SQLServerCentral
Run an SSIS Package Under a Different Account - SQLServerCentral
Run an SSIS Package Under a Different Account
By Polar Bear, 2010/03/25
Total article views: 6535 | Views in the last 30 days: 88
Rate this | Join the discussion | Briefcase | Print
Recently, in a SSIS package, I needed to get some data from a SQL Server database which is used by a third party application. This application uses named user license. The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance. If we set ETLadmin to an application admin, this will waste an application admin license. And usually ETLadmin account should only have read permission to grab data. Application admin permission will be too much for this account. It will be nice if we can use an existing application admin user account to run the package. This can be done by using SQL Server Agent proxies.
Here are the steps to setup a proxy by using an existing application user account:
Create a credential using the account that having right access to the application database
Open SSMS, connect to the SQL Server instance that the SSIS will be scheduled to run
Go to Security - > Credentials, and click on 'New Credential...' to create a new credential
Zoom in | Open in new window
Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.
Click 'ok' and the new credential should be listed.
Zoom in | Open in new window
Create a proxy using the credential created in the previous step
Right click SQL Server Agent -> Proxies, and select 'New proxy...'
Zoom in | Open in new window
Enter the new proxy name, and choose the credential that created from the previous step from the dropdown list. And check 'SQL Server Integration Services Package' under 'Active to the following subsystems'. The SQL Server Agent proxy can be activated for many sub systems (as listed in the screen shot). In this case we are only enabling it for SSIS packages.
Zoom in | Open in new window
Click 'OK' and the new proxy should be listed.
Setup a job to run the SSIS package using the proxy
Open the job step properties for the step that run the SSIS package
Select the proxy that created in the previous step from the 'Run as' dropdown list
Zoom in | Open in new window
Click 'OK' to save the change
Now you can run the package using the application account.
By using SQL Server Agent proxy, we can run jobs on different databases, different servers using existing accounts, and avoid giving excessive permission to ETL users or developers.
Run an SSIS Package Under a Different Account
By Polar Bear, 2010/03/25
Total article views: 6535 | Views in the last 30 days: 88
Rate this | Join the discussion | Briefcase | Print
Recently, in a SSIS package, I needed to get some data from a SQL Server database which is used by a third party application. This application uses named user license. The ETLadmin account, which is a domain account that we use it to run SSIS packages, does not have the right permission to access views in that database even if it has sysadmin permission on that SQL Server instance. If we set ETLadmin to an application admin, this will waste an application admin license. And usually ETLadmin account should only have read permission to grab data. Application admin permission will be too much for this account. It will be nice if we can use an existing application admin user account to run the package. This can be done by using SQL Server Agent proxies.
Here are the steps to setup a proxy by using an existing application user account:
Create a credential using the account that having right access to the application database
Open SSMS, connect to the SQL Server instance that the SSIS will be scheduled to run
Go to Security - > Credentials, and click on 'New Credential...' to create a new credential
Zoom in | Open in new window
Enter the credential name - Enter the domain account, and password. Repeat the password in the 'Confirm password'. This account should have the right access to the application database.
Click 'ok' and the new credential should be listed.
Zoom in | Open in new window
Create a proxy using the credential created in the previous step
Right click SQL Server Agent -> Proxies, and select 'New proxy...'
Zoom in | Open in new window
Enter the new proxy name, and choose the credential that created from the previous step from the dropdown list. And check 'SQL Server Integration Services Package' under 'Active to the following subsystems'. The SQL Server Agent proxy can be activated for many sub systems (as listed in the screen shot). In this case we are only enabling it for SSIS packages.
Zoom in | Open in new window
Click 'OK' and the new proxy should be listed.
Setup a job to run the SSIS package using the proxy
Open the job step properties for the step that run the SSIS package
Select the proxy that created in the previous step from the 'Run as' dropdown list
Zoom in | Open in new window
Click 'OK' to save the change
Now you can run the package using the application account.
By using SQL Server Agent proxy, we can run jobs on different databases, different servers using existing accounts, and avoid giving excessive permission to ETL users or developers.
Wednesday, January 05, 2011
Tuesday, January 04, 2011
Subscribe to:
Posts (Atom)