March 2008 | SansSQL

Thursday, March 20, 2008

Retrieve Processes Using Specified Database

If you want to know how many processes are there in any particular database, it can be retrieved querying sysprocesses in master database.
1st Method
USE master
GO
DECLARE @dbid INT
SELECT @dbid = dbid
FROM sysdatabases
WHERE name = ‘DBName’
IF EXISTS (SELECT spid
FROM sysprocesses
WHERE dbid = @dbid)
BEGIN
SELECT ‘These processes are using current database’ AS Note,
spid, last_batch,
status, hostname, loginame
FROM sysprocesses
WHERE dbid = @dbid
END
GO

2nd Method
SELECT 'These processes are using database ' AS Note,
[Database] =DB_NAME (dbid), spid, last_batch,
status, hostname, loginame
FROM sysprocesses
WHERE dbid = DB_ID (‘DBName')

Tuesday, March 18, 2008

Script to Script Out all Publication Stored Procs

You may come across a situation where in your replication is failing with the error “could not find the stored procedure ‘sp_MSupd_TableName’ ”
or
With the error “could not find the stored procedure ‘sp_MSins_TableName’ ”

To fix this replication errors run the following command in the publication database

sp_scriptpublicationcustomprocs ‘Publication Name’

After running the above stored proc, obtain the results from the result set and run them in the subscription database. Now restart the agent and this should fix the error.