0

Disable/Enable all triggers in SQL Server Database

Posted by Chris Straw on May 12, 2012 in SQL Server | ∞

This script will create a script to disable and enable all the trigger within a SQL Server Database.


Select 'DISABLE TRIGGER ' +  sys.triggers.name + ' ' + ' ON ' + ' [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.NAME + ']
'  +
'go'
from  sys.triggers
join sys.objects on sys.objects.object_id = sys.triggers.parent_id
where sys.triggers.is_disabled=0
ORDER BY sys.triggers.NAME

go

Select 'ENABLE TRIGGER ' +  sys.triggers.name + ' ' + ' ON ' + ' [' + SCHEMA_NAME(sys.objects.schema_id) + '].[' + sys.objects.name + ']
'  +
'go'
from  sys.triggers
join sys.objects on sys.objects.object_id = sys.triggers.parent_id
where sys.triggers.is_disabled=1
ORDER BY sys.triggers.NAME

You will want to change your results to TEXT instead of GRID to get the line breaks for the “go”

Share

Tags:

0

Monitor BizTalk Server (BizTalkMgmtDb) SQL Job Fails

Posted by Chris Straw on May 1, 2012 in BizTalk, SQL Server | ∞

A client was experiencing failures with the BizTalk job Monitor BizTalk Server (BizTalkMgmtDb), which calls stored procedure [dbo].[btsmon_Inconsistent]. This proc calls several other stored procedures to cleanup the messagebox and other meta-tables within BizTalk.

These procs generate dynamic SQL scripts and to run against the tables within the BizTalk databases.  Depending on your configuration, you might receive errors when this job runs.  We have several host instances within our configuration, which causes the generated SQL scripts to be quite large.  The generated SQL script was overflowing the nvarchar variable used to hold the generated script.  This caused us to get errors like:

Incorrect syntax near ‘FROM’. [SQLSTATE 42000] (Error 102).  The step failed.  

The primary proc that caused our problem was [dbo].[btsmon_MessagesWithoutReferences] within the BizTalkMsgBoxDb.  The variable used to store the generated script was of type nvarchar(4000).  The generated script  from our configuration caused the script’s length to exceed the 4000 characters allowed.

Here is the original script:


CREATE PROCEDURE [dbo].[btsmon_MessagesWithoutReferences]
@nIssues bigint output
AS
	declare @Host sysname
	declare @tsql nvarchar(4000)
	--Find Message without refcounts
	set @nIssues = 0
	set @tsql = N'Select @MessagesWithoutReferences = count(*) from [dbo].[Spool] (READPAST)
		WHERE [uidMessageID ] != N''50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3'' AND --Control Messages
		[uidMessageID ] != N''61EAA7FC-AC85-42D9-BF3E-1BED258B82BE'' AND
		[uidMessageID ] != N''57E5E753-0207-435D-8BE7-2B9F3C6556F9'' AND
		[uidMessageID ] != N''2BE3D5B8-5685-40F2-BD97-51ADA3D02347'' AND
		[uidMessageID] NOT IN (
			SELECT [uidMessageID] FROM [dbo].[TrackingMessageReferences]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog1]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog2]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLogTotals]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageZeroSum]
	'

	declare Host_Cursor insensitive cursor for
	select nvcApplicationName from dbo.Applications
	open Host_Cursor
	fetch next from Host_Cursor into @Host
	while @@fetch_status = 0
	begin
		set @tsql = @tsql + '
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Scheduled]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Suspended]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].[InstanceStateMessageReferences_'+ @Host +']
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'_MessageRefCountLog]
		'
		fetch next from Host_Cursor into @Host
	end
	close Host_Cursor
	deallocate Host_Cursor
	set @tsql = @tsql + ')'

	exec sp_executesql @tsql, N'@MessagesWithoutReferences bigint output', @MessagesWithoutReferences = @nIssues output

To resolve the issue, I changed the @tsql variable from nvarchar(4000) to nvarchar(max) on line 5.

Here is the adjusted script:


ALTER PROCEDURE [dbo].[btsmon_MessagesWithoutReferences]
@nIssues bigint output
AS
	declare @Host sysname
	declare @tsql nvarchar(max)
	--Find Message without refcounts
	set @nIssues = 0
	set @tsql = N'Select @MessagesWithoutReferences = count(*) from [dbo].[Spool] (READPAST)
		WHERE [uidMessageID ] != N''50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3'' AND
		[uidMessageID ] != N''61EAA7FC-AC85-42D9-BF3E-1BED258B82BE'' AND
		[uidMessageID ] != N''57E5E753-0207-435D-8BE7-2B9F3C6556F9'' AND
		[uidMessageID ] != N''2BE3D5B8-5685-40F2-BD97-51ADA3D02347'' AND
		[uidMessageID] NOT IN (
			SELECT [uidMessageID] FROM [dbo].[TrackingMessageReferences]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog1]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLog2]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageRefCountLogTotals]
				UNION ALL
			SELECT [uidMessageID ] FROM [dbo].[MessageZeroSum]
	'

	declare Host_Cursor insensitive cursor for
	select nvcApplicationName from dbo.Applications
	open Host_Cursor
	fetch next from Host_Cursor into @Host
	while @@fetch_status = 0
	begin
		set @tsql = @tsql + '
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Scheduled]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'Q_Suspended]
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].[InstanceStateMessageReferences_'+ @Host +']
			UNION ALL
		SELECT [uidMessageID] FROM [dbo].['+ @Host +'_MessageRefCountLog]
		'
		fetch next from Host_Cursor into @Host
	end
	close Host_Cursor
	deallocate Host_Cursor
	set @tsql = @tsql + ')'

	exec sp_executesql @tsql, N'@MessagesWithoutReferences bigint output', @MessagesWithoutReferences = @nIssues output

I understand what Microsoft is doing, but I’m not a fan of the implementation, due to the problems when you have more host instances.  I believe a better approach would have been to create stored procs for each host instance that accomplished the same task, generated the SQL scripts to call the stored proc and prevent the overflow from occuring.

Share

Tags: ,

0

SQL Server Trace (aka SQL Profiler) Priveliges without giving sysadmin

Posted by Chris Straw on April 7, 2012 in SQL Server | ∞

I’m a big proponet of using SQL Server Profiler for monitoring DB activity from applications. When a client has a SQL Server DBA on staff, they continuously tell me they can’t grant me Trace privileges without giving me sysadmin privileges. Oh yes they can! Below is the simple script to grant a particular user trace privileges without granting sysadmin (god access).


GRANT ALTER TRACE TO [useridhere];
Share

Tags:

0

How to Clear or Purge the Biztalk Tracking Database

Posted by Chris Straw on April 7, 2012 in BizTalk, SQL Server | ∞

Stop all running Biztalk services, load up SQL Management Studio, on the BiztalkDTADb database run the following script. Note that this script does not archive data.


declare @dtLastBackup datetime
set @dtLastBackup = GetUTCDate()
exec dtasp_PurgeTrackingDatabase 1, 0, 1, @dtLastBackup

go
DBCC SHRINKFILE (N'BizTalkDTADb' , 0)
go
DBCC SHRINKFILE (N'BizTalkDTADb_log' , 0)
go
DBCC SHRINKFILE (N'BizTalkDTADb' , TRUNCATEONLY)
go
DBCC SHRINKFILE (N'BizTalkDTADb_log' , TRUNCATEONLY)
go

The last part shrinks the database and log files.

Share

Tags: ,

0

Script to delete all objects from SQL Server

Posted by Chris Straw on February 2, 2012 in SQL Server | ∞

Today I accidently ran a script on master instead of the DB desired.  So, I created a script that would create the drop statements for those objects.  If some of the object depend on others, you may have to run the statements a few times to get everything to drop.


SELECT 'Drop Procedure [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.create_date >= '2/1/12' AND sobj.type = 'p'

SELECT 'Drop View [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.create_date >= '2/1/12' AND sobj.type = 'V'

select 'Drop Table [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.create_date >= '2/1/12' AND sobj.type = 'U'

select 'Drop Function [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.create_date >= '2/1/12' AND sobj.type = 'FN'

select 'Drop Default [' + schm.name + '].[' + sobj.name + ']'
FROM sys.objects sobj
JOIN sys.schemas schm ON sobj.schema_id = schm.schema_id
WHERE sobj.create_date >= '2/1/12' AND sobj.type = 'D'
Share

Tags:

0

Disable button on click ASP.Net 2.0 or higher (prevent double click)

Posted by Chris Straw on January 20, 2012 in ASP.net, c# | ∞

There is a need, at times, to prevent the user from clicking a button multiple times.  Since ASP.Net already wraps something into the onclick event, you need to change this behavior.  Below is the code to do this:

btnSave.Attributes.Add("onclick",
    "this.value='Processing...';this.disabled=true;" +
    GetPostBackEventReference(btnSave).ToString());

btnSave is the name of the button you want to disable during postback.

Share

Tags: ,

0

Find missing Foreign Keys in SQL Server

Posted by Chris Straw on January 13, 2012 in Database, SQL Server | ∞

I was looking working on a script to find possibly missing foreign keys, when I stumbled upon Michael J Swart’s blog and it gave me exactly what I was looking for.

You might need to tweak this a little to match your naming conventions.

SELECT t.name, c.name
FROM sys.columns c
INNER JOIN sys.tables t
	ON t.object_id = c.object_id
INNER JOIN sys.indexes i
	ON i.object_id = t.object_id
LEFT JOIN sys.foreign_key_columns fkc_Parent
	ON fkc_Parent.parent_column_id = c.column_id
	AND fkc_Parent.parent_object_id = c.object_id
LEFT JOIN sys.foreign_key_columns fkc_Referenced
	ON fkc_Referenced.Referenced_column_id = c.column_id
	AND fkc_Referenced.Referenced_object_id = c.object_id
LEFT JOIN sys.index_columns ic
	ON ic.index_id = i.index_id
	AND ic.object_id = t.object_id
	AND ic.column_id = c.column_id
WHERE fkc_Referenced.constraint_object_id IS NULL
	AND fkc_Parent.constraint_column_id IS NULL
	AND ic.index_column_id IS NULL
	AND c.name LIKE '%id'
	AND i.is_primary_key = 1
ORDER BY t.name, c.name

Here is another flavor I found on this blog

SELECT  C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME
FROM    INFORMATION_SCHEMA.COLUMNS C
        INNER Join INFORMATION_SCHEMA.TABLES T
          ON C.TABLE_NAME = T.TABLE_NAME
          And T.TABLE_TYPE = 'Base Table'
          AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
        LEFT Join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U
          ON C.TABLE_NAME = U.TABLE_NAME
          And C.COLUMN_NAME = U.COLUMN_NAME
          And U.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE   U.COLUMN_NAME IS Null
        And C.COLUMN_NAME Like '%id'
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME

Here is mine with a small tweak.  I added a Join to the INFORMATION_SCHEMA.KEY_COLUMN_USAGE Table to ensure that the column is a Primary Key of some sort.

SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'Base Table' AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON C.TABLE_NAME = U.TABLE_NAME AND C.COLUMN_NAME = U.COLUMN_NAME AND U.TABLE_SCHEMA = C.TABLE_SCHEMA
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON C.COLUMN_NAME = K.COLUMN_NAME --Make sure that the column is a Primary Key
WHERE   U.COLUMN_NAME IS Null
        And C.COLUMN_NAME Like '%id'
GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME

Here is my custom version that works for my naming conventions

DECLARE @TableMissingFK TABLE (
	TABLE_SCHEMA nvarchar(128),
	TABLE_NAME sysname,
	TABLE_NAME_PK_COLUMN NVARCHAR(128),
	FK_TABLE_SCHEMA nvarchar(128),
	FK_TABLE_NAME sysname NULL,
	FK_COLUMN_NAME sysname NULL,
	FK_NAME VARCHAR(30))

INSERT @TableMissingFK (TABLE_SCHEMA, TABLE_NAME, FK_COLUMN_NAME)
	SELECT C.TABLE_SCHEMA,C.TABLE_NAME,C.COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS C
	JOIN INFORMATION_SCHEMA.TABLES T ON C.TABLE_NAME = T.TABLE_NAME AND T.TABLE_TYPE = 'Base Table' AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
	LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE U ON C.TABLE_NAME = U.TABLE_NAME AND C.COLUMN_NAME = U.COLUMN_NAME AND U.TABLE_SCHEMA = C.TABLE_SCHEMA
	JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE K ON C.COLUMN_NAME = K.COLUMN_NAME --Make sure that the column is a Primary Key
	WHERE   U.COLUMN_NAME IS Null
			And C.COLUMN_NAME Like '%id'
	GROUP BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME
	ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.COLUMN_NAME

UPDATE @TableMissingFK SET FK_NAME = SUBSTRING(FK_COLUMN_NAME, 1, CHARINDEX('_', FK_COLUMN_NAME))

UPDATE @TableMissingFK SET
	TABLE_NAME_PK_COLUMN = pkey.COLUMN_NAME,
	FK_NAME = FK_NAME + SUBSTRING(pkey.COLUMN_NAME, 1, CHARINDEX('_', pkey.COLUMN_NAME))
FROM @TableMissingFK ttbl
--JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE keyu ON ttbl.TABLE_SCHEMA = keyu.TABLE_SCHEMA AND ttbl.TABLE_NAME = keyu.TABLE_NAME
JOIN (SELECT tabc.TABLE_SCHEMA, tabc.TABLE_NAME, tabc.CONSTRAINT_NAME, cnst.COLUMN_NAME
	  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tabc
	  JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cnst ON tabc.CONSTRAINT_NAME = cnst.CONSTRAINT_NAME
	  WHERE tabc.CONSTRAINT_TYPE = 'PRIMARY KEY') pkey ON ttbl.TABLE_SCHEMA = pkey.TABLE_SCHEMA AND ttbl.TABLE_NAME = pkey.TABLE_NAME

UPDATE @TableMissingFK SET FK_NAME = FK_NAME + 'FK'

UPDATE @TableMissingFK SET
	FK_TABLE_SCHEMA = tabc.TABLE_SCHEMA,
	FK_TABLE_NAME = tabc.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tabc
JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cnst ON tabc.CONSTRAINT_NAME = cnst.CONSTRAINT_NAME
JOIN @TableMissingFK ttbl ON cnst.COLUMN_NAME = ttbl.FK_COLUMN_NAME
WHERE tabc.CONSTRAINT_TYPE = 'PRIMARY KEY'

SELECT  TABLE_SCHEMA,
        TABLE_NAME,
        TABLE_NAME_PK_COLUMN,
        FK_TABLE_SCHEMA,
        FK_TABLE_NAME,
        FK_COLUMN_NAME,
        FK_NAME,
        'ALTER TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] ADD CONSTRAINT [' + FK_NAME + '] FOREIGN KEY ([' + FK_COLUMN_NAME + ']) REFERENCES [' + FK_TABLE_SCHEMA + '].[' + FK_TABLE_NAME + '] ([' + FK_COLUMN_NAME + '])'
FROM @TableMissingFK
Share

Tags: ,

0

One or more instances of the orchestration still exist – BizTalk 2010

Posted by Chris Straw on November 28, 2011 in BizTalk | ∞

When I tried to deploy a biztalk application to the server, I got an the error “Could not change the bindings for orchestration ‘orchestration_name_here’, Version=x.x.x.x,Culture=neutral, PublicKeyToken=sometoken’ as one or more instances of the orchestration still exist”.

Now what? What does it mean? Quite simply, you have a resumable message sitting in the queue. If this message is important, you need to fix the issue with the message and resume the message or terminate the instance.

Removing instances using the BizTalk Administration Tool
In the BizTalk Administration Group click in the left pane on ‘BizTalk Group [Name of Computer/Name of Management database]’. In the right pane the ‘Group Overview’ appears. Click on the tab page ‘New query’ to create a query that searches for the suspended instances. In the ‘Query Expression’ grid the first Search field is already selected, namely ‘Search For’, also the ‘Equals’ operator is already filled in in the second field. All we have to do in the ‘Value’-field is search for suspended instances. And… lucky us: when we open the dropdown box we see that ‘Suspended Service Instances’ is one of the values we can choose! Next, we run the query by hitting the button with the label ‘Run Query’. The grid ‘Query results’ now contains all suspended instances found. All we need to do is select all suspended instances, right click on the selection and choose ‘Terminate Instances’. The BizTalk Administration Tool asks for confirmation and removes the instances. When you rerun the query, the instances will be removed and the orchestration can be undeployed/redeployed. So far for the polite manner.

Removing instances using a Stored Procedure
The less polite manner to remove suspended instances is by running a Stored Procedure which will clean up the MessageBox. Attention: this is NOT a recommended procedure on production environments!
Open the SQL Server Management Studio and connect to the database server. In the left pane expand the server-node and the Databases-node. Next, click on the MessageBox-database (BizTalkMsgDb) and hit the ‘New Query’-button in the toolbar. In the right pane you can enter SQL commands. Since you selected the MessageBox-database in the left pane, the commands are executed against that database. Now type: ‘exec bts_CleanupMsgbox 0′ (without the quotes) and hit the ‘! Execute’-button in the toolbar. All suspended instances will now be removed. You can now undeploy/redeploy the orchestration.

Share

0

View Message Count by BizTalk Orchistration

Posted by Chris Straw on November 27, 2011 in BizTalk, SQL Server | ∞

This script will give you a count of all messages by orchestration within BizTalk. This includes currently processing messages.

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

SELECT
	o.nvcName AS Orchestration,
	COUNT(*) as Count,
	CASE i.nState
		WHEN 1 THEN 'Ready To Run'
		WHEN 2 THEN 'Active'
		WHEN 4 THEN 'Suspended Resumable'
		WHEN 8 THEN 'Dehydrated'
		WHEN 16 THEN 'Completed With Discarded Messages'
		WHEN 32 THEN 'Suspended Non-Resumable'
	END as State
FROM [BizTalkMsgboxDb]..[Instances] AS i WITH (NOLOCK)
JOIN [BizTalkMgmtDb]..[bts_Orchestration] AS o WITH (NOLOCK) ON i.uidServiceID = o.uidGUID
--WHERE dtCreated > '2004-08-24 00:00:00' AND dtCreated < '2004-08-24 13:30:00'
GROUP BY o.nvcName, i.nState
Share

0

Count of Active or Started Messages in BizTalk.

Posted by Chris Straw on November 27, 2011 in BizTalk, SQL Server | ∞

This script will give you a count of active messages within BizTalk. This includes currently processing messages, as well as suspended resumable messages. Run this against the BizTalkDTADb.

set nocount on
set transaction isolation level read COMMITTED
set deadlock_priority LOW

SELECT COUNT(*)
FROM dbo.dtav_ServiceFacts
WHERE [ServiceInstance/State] = 'Started'
Share

Tags: ,

Copyright © 2011-2012 Chris Straw All rights reserved.
This site is using the Desk Mess Mirrored theme, v2.0.2, from BuyNowShop.com.