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')
Thursday, March 20, 2008
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.
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.
Labels:
SQL Queries
Subscribe to:
Posts (Atom)