script to kill long running ad hoc queries

The following script is meant to identify long running queries and then kill
them after sending a warning. The main problem I have with this is how to
uniquely identify a process so that I don't terminate the wrong one as I
would in the following scenario:
1. Long running ad hoc query with SPID 50 is identified and warning messages
is sent
2. User terminates query
3. Another user connects and is assigned SPID 50. The process goes on to
kill this SPID.
Here is the script. I was planning to run the proc as a job. The time
intervals are for testing only and will probably be increased:
USE AdminDB
GO
--Create the logging table
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
type = 'U') DROP TABLE AdHocQueryLog
GO
CREATE TABLE AdHocQueryLog
(
AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
totalruntime smallint NOT NULL,
spid smallint NOT NULL,
cpu int NOT NULL,
last_batch datetime NOT NULL,
physical_io int NOT NULL,
[memusage] int NOT NULL,
open_tran smallint NOT NULL,
loginame nvarchar(128) NOT NULL,
hostname nvarchar(128) NOT NULL,
login_time datetime NOT NULL,
dbid smallint NOT NULL,
program_name nvarchar(128) NOT NULL,
cmd nvarchar(16) NOT NULL,
warned bit NOT NULL DEFAULT 0,
killed bit NOT NULL DEFAULT 0,
stopped bit NOT NULL DEFAULT 0,
insertdate datetime NOT NULL DEFAULT getdate()
)
--Add Indexes later
--Create stored proc
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'uspKillAdHocQuery'
AND type = 'P')
DROP PROCEDURE uspKillAdHocQuery
GO
CREATE PROCEDURE uspKillAdHocQuery
AS
SET NOCOUNT ON
DECLARE @.iReturnCode int,
@.iNextRowId int,
@.iCurrentRowId int,
@.iLoopControl int,
@.spid smallint,
@.hostname nvarchar(128),
@.warned bit,
@.killed bit,
@.warning nvarchar(256),
@.cmd nvarchar(512)
INSERT AdHocQueryLog
(
totalruntime,
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
)
SELECT datediff(ss,last_batch,getdate()),
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE status='runnable'
AND program_name not like 'SQL Agent%'
AND program_name not in ('%DiagnosticMan','SQL Profiler')
AND datediff(ss,last_batch,getdate())>60
--What if is already it was already there
--Loop through active processes and send warning / kill
--Initialize variables
SELECT @.iLoopControl = 1
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE killed=0
--Retrieve the first row
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
--Main processing loop
WHILE @.iLoopControl = 1
BEGIN
IF @.warned=1
BEGIN
PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate the cmd variable
--!!!What about if it is still rolling back
--!!!check whether record exists
SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
--Kill the process if it is still active
EXEC sp_executesql @.stmt=@.cmd
--update the log - process has been killed
UPDATE AdminDB.dbo.AdHocQueryLog
SET killed=1
WHERE AdHocQueryID = @.iNextRowId
END
ELSE
BEGIN--send a warning and update log
PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate variable for warning message and cmd
SELECT @.warning='Warning: An Ad Hoc query with SPID
'+convert(varchar(3),@.spid)+' has been running at this workstation for over
5
minutes and will be terminated in 5 minutes'
SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
'+@.warning+''''
--send warning
EXEC sp_executesql @.stmt=@.cmd
--update the log - warning has been sent
UPDATE AdminDB.dbo.AdHocQueryLog
SET warned=1
WHERE AdHocQueryID = @.iNextRowId
END
-- Reset looping variables
SELECT @.iNextRowId = NULL
-- get the next iRowId
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID < @.iCurrentRowId
AND killed=0
-- check if valid next row id?
IF ISNULL(@.iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
END
GOYou might want to take a look at the sysprocesses table and specifically the
sql_handle column. But I would concentrate on fixing why the long running
queries occur as a real cure<g>.
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> The following script is meant to identify long running queries and then
kill
> them after sending a warning. The main problem I have with this is how to
> uniquely identify a process so that I don't terminate the wrong one as I
> would in the following scenario:
> 1. Long running ad hoc query with SPID 50 is identified and warning
messages
> is sent
> 2. User terminates query
> 3. Another user connects and is assigned SPID 50. The process goes on to
> kill this SPID.
> Here is the script. I was planning to run the proc as a job. The time
> intervals are for testing only and will probably be increased:
> USE AdminDB
> GO
> --Create the logging table
> IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
> type = 'U') DROP TABLE AdHocQueryLog
> GO
> CREATE TABLE AdHocQueryLog
> (
> AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> totalruntime smallint NOT NULL,
> spid smallint NOT NULL,
> cpu int NOT NULL,
> last_batch datetime NOT NULL,
> physical_io int NOT NULL,
> [memusage] int NOT NULL,
> open_tran smallint NOT NULL,
> loginame nvarchar(128) NOT NULL,
> hostname nvarchar(128) NOT NULL,
> login_time datetime NOT NULL,
> dbid smallint NOT NULL,
> program_name nvarchar(128) NOT NULL,
> cmd nvarchar(16) NOT NULL,
> warned bit NOT NULL DEFAULT 0,
> killed bit NOT NULL DEFAULT 0,
> stopped bit NOT NULL DEFAULT 0,
> insertdate datetime NOT NULL DEFAULT getdate()
> )
> --Add Indexes later
>
> --Create stored proc
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'uspKillAdHocQuery'
> AND type = 'P')
> DROP PROCEDURE uspKillAdHocQuery
> GO
> CREATE PROCEDURE uspKillAdHocQuery
> AS
> SET NOCOUNT ON
> DECLARE @.iReturnCode int,
> @.iNextRowId int,
> @.iCurrentRowId int,
> @.iLoopControl int,
> @.spid smallint,
> @.hostname nvarchar(128),
> @.warned bit,
> @.killed bit,
> @.warning nvarchar(256),
> @.cmd nvarchar(512)
> INSERT AdHocQueryLog
> (
> totalruntime,
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> )
> SELECT datediff(ss,last_batch,getdate()),
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> FROM master.dbo.sysprocesses WITH (NOLOCK)
> WHERE status='runnable'
> AND program_name not like 'SQL Agent%'
> AND program_name not in ('%DiagnosticMan','SQL Profiler')
> AND datediff(ss,last_batch,getdate())>60
> --What if is already it was already there
>
> --Loop through active processes and send warning / kill
> --Initialize variables
> SELECT @.iLoopControl = 1
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE killed=0
> --Retrieve the first row
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> --Main processing loop
> WHILE @.iLoopControl = 1
> BEGIN
> IF @.warned=1
> BEGIN
> PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate the cmd variable
> --!!!What about if it is still rolling back
> --!!!check whether record exists
> SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
> --Kill the process if it is still active
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - process has been killed
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET killed=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> ELSE
> BEGIN--send a warning and update log
> PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate variable for warning message and cmd
> SELECT @.warning='Warning: An Ad Hoc query with SPID
> '+convert(varchar(3),@.spid)+' has been running at this workstation for
over 5
> minutes and will be terminated in 5 minutes'
> SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
> '+@.warning+''''
> --send warning
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - warning has been sent
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET warned=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> -- Reset looping variables
> SELECT @.iNextRowId = NULL
> -- get the next iRowId
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID < @.iCurrentRowId
> AND killed=0
> -- check if valid next row id?
> IF ISNULL(@.iNextRowId,0) = 0
> BEGIN
> BREAK
> END
> -- get the next row.
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> END
> GO
>|||Andrew,
I have looked at sql_handles (which is not documented in books online) and
it seems that this doesn't contain unique values either. Can you point me to
a website where I can find out more about this column?
As far as fixing the root problem, I completely agree with you but we have
been required to give some users access to run select statements through ODB
C
connections (used by Access) so I am trying to keep them from shooting
themselves in the foot.
"Andrew J. Kelly" wrote:

> You might want to take a look at the sysprocesses table and specifically t
he
> sql_handle column. But I would concentrate on fixing why the long running
> queries occur as a real cure<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> kill
> messages
> over 5
>
>|||While it has been a while since I messed with that column I was pretty sure
it changed with each batch run by the associated spid. I would do a Google
search and see what turns up. Otherwise, sorry I don't know of any docs for
this.In either case you are probably better off using the "query governor
cost limit option" in SQL Server vs trying to write your own.
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:4A8DCA0E-10E7-497E-8FA3-DCFD6B6ABFBE@.microsoft.com...
> Andrew,
> I have looked at sql_handles (which is not documented in books online) and
> it seems that this doesn't contain unique values either. Can you point me
to
> a website where I can find out more about this column?
> As far as fixing the root problem, I completely agree with you but we have
> been required to give some users access to run select statements through
ODBC[vbcol=seagreen]
> connections (used by Access) so I am trying to keep them from shooting
> themselves in the foot.
> "Andrew J. Kelly" wrote:
>
the[vbcol=seagreen]
running[vbcol=seagreen]
then[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
AND[vbcol=seagreen]

script to kill long running ad hoc queries

The following script is meant to identify long running queries and then kill
them after sending a warning. The main problem I have with this is how to
uniquely identify a process so that I don't terminate the wrong one as I
would in the following scenario:
1. Long running ad hoc query with SPID 50 is identified and warning messages
is sent
2. User terminates query
3. Another user connects and is assigned SPID 50. The process goes on to
kill this SPID.
Here is the script. I was planning to run the proc as a job. The time
intervals are for testing only and will probably be increased:
USE AdminDB
GO
--Create the logging table
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
type = 'U') DROP TABLE AdHocQueryLog
GO
CREATE TABLE AdHocQueryLog
(
AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
totalruntime smallint NOT NULL,
spid smallint NOT NULL,
cpu int NOT NULL,
last_batch datetime NOT NULL,
physical_io int NOT NULL,
[memusage] int NOT NULL,
open_tran smallint NOT NULL,
loginame nvarchar(128) NOT NULL,
hostname nvarchar(128) NOT NULL,
login_time datetime NOT NULL,
dbid smallint NOT NULL,
program_name nvarchar(128) NOT NULL,
cmd nvarchar(16) NOT NULL,
warned bit NOT NULL DEFAULT 0,
killed bit NOT NULL DEFAULT 0,
stopped bit NOT NULL DEFAULT 0,
insertdate datetime NOT NULL DEFAULT getdate()
)
--Add Indexes later
--Create stored proc
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'uspKillAdHocQuery'
AND type = 'P')
DROP PROCEDURE uspKillAdHocQuery
GO
CREATE PROCEDURE uspKillAdHocQuery
AS
SET NOCOUNT ON
DECLARE @.iReturnCode int,
@.iNextRowId int,
@.iCurrentRowId int,
@.iLoopControl int,
@.spid smallint,
@.hostname nvarchar(128),
@.warned bit,
@.killedbit,
@.warningnvarchar(256),
@.cmdnvarchar(512)
INSERT AdHocQueryLog
(
totalruntime,
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
)
SELECT datediff(ss,last_batch,getdate()),
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE status='runnable'
AND program_name not like 'SQL Agent%'
ANDprogram_name not in ('%DiagnosticMan','SQL Profiler')
AND datediff(ss,last_batch,getdate())>60
--What if is already it was already there
--Loop through active processes and send warning / kill
--Initialize variables
SELECT @.iLoopControl = 1
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE killed=0
--Retrieve the first row
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
--Main processing loop
WHILE @.iLoopControl = 1
BEGIN
IF @.warned=1
BEGIN
PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate the cmd variable
--!!!What about if it is still rolling back
--!!!check whether record exists
SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
--Kill the process if it is still active
EXEC sp_executesql @.stmt=@.cmd
--update the log - process has been killed
UPDATE AdminDB.dbo.AdHocQueryLog
SET killed=1
WHERE AdHocQueryID = @.iNextRowId
END
ELSE
BEGIN--send a warning and update log
PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate variable for warning message and cmd
SELECT @.warning='Warning: An Ad Hoc query with SPID
'+convert(varchar(3),@.spid)+' has been running at this workstation for over 5
minutes and will be terminated in 5 minutes'
SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
'+@.warning+''''
--send warning
EXEC sp_executesql @.stmt=@.cmd
--update the log - warning has been sent
UPDATE AdminDB.dbo.AdHocQueryLog
SET warned=1
WHERE AdHocQueryID = @.iNextRowId
END
-- Reset looping variables
SELECT @.iNextRowId = NULL
-- get the next iRowId
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID < @.iCurrentRowId
AND killed=0
-- check if valid next row id?
IF ISNULL(@.iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
END
GO
You might want to take a look at the sysprocesses table and specifically the
sql_handle column. But I would concentrate on fixing why the long running
queries occur as a real cure<g>.
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> The following script is meant to identify long running queries and then
kill
> them after sending a warning. The main problem I have with this is how to
> uniquely identify a process so that I don't terminate the wrong one as I
> would in the following scenario:
> 1. Long running ad hoc query with SPID 50 is identified and warning
messages
> is sent
> 2. User terminates query
> 3. Another user connects and is assigned SPID 50. The process goes on to
> kill this SPID.
> Here is the script. I was planning to run the proc as a job. The time
> intervals are for testing only and will probably be increased:
> USE AdminDB
> GO
> --Create the logging table
> IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
> type = 'U') DROP TABLE AdHocQueryLog
> GO
> CREATE TABLE AdHocQueryLog
> (
> AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> totalruntime smallint NOT NULL,
> spid smallint NOT NULL,
> cpu int NOT NULL,
> last_batch datetime NOT NULL,
> physical_io int NOT NULL,
> [memusage] int NOT NULL,
> open_tran smallint NOT NULL,
> loginame nvarchar(128) NOT NULL,
> hostname nvarchar(128) NOT NULL,
> login_time datetime NOT NULL,
> dbid smallint NOT NULL,
> program_name nvarchar(128) NOT NULL,
> cmd nvarchar(16) NOT NULL,
> warned bit NOT NULL DEFAULT 0,
> killed bit NOT NULL DEFAULT 0,
> stopped bit NOT NULL DEFAULT 0,
> insertdate datetime NOT NULL DEFAULT getdate()
> )
> --Add Indexes later
>
> --Create stored proc
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'uspKillAdHocQuery'
> AND type = 'P')
> DROP PROCEDURE uspKillAdHocQuery
> GO
> CREATE PROCEDURE uspKillAdHocQuery
> AS
> SET NOCOUNT ON
> DECLARE @.iReturnCode int,
> @.iNextRowId int,
> @.iCurrentRowId int,
> @.iLoopControl int,
> @.spid smallint,
> @.hostname nvarchar(128),
> @.warned bit,
> @.killed bit,
> @.warning nvarchar(256),
> @.cmd nvarchar(512)
> INSERT AdHocQueryLog
> (
> totalruntime,
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> )
> SELECT datediff(ss,last_batch,getdate()),
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> FROM master.dbo.sysprocesses WITH (NOLOCK)
> WHERE status='runnable'
> AND program_name not like 'SQL Agent%'
> AND program_name not in ('%DiagnosticMan','SQL Profiler')
> AND datediff(ss,last_batch,getdate())>60
> --What if is already it was already there
>
> --Loop through active processes and send warning / kill
> --Initialize variables
> SELECT @.iLoopControl = 1
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE killed=0
> --Retrieve the first row
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> --Main processing loop
> WHILE @.iLoopControl = 1
> BEGIN
> IF @.warned=1
> BEGIN
> PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate the cmd variable
> --!!!What about if it is still rolling back
> --!!!check whether record exists
> SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
> --Kill the process if it is still active
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - process has been killed
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET killed=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> ELSE
> BEGIN--send a warning and update log
> PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate variable for warning message and cmd
> SELECT @.warning='Warning: An Ad Hoc query with SPID
> '+convert(varchar(3),@.spid)+' has been running at this workstation for
over 5
> minutes and will be terminated in 5 minutes'
> SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
> '+@.warning+''''
> --send warning
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - warning has been sent
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET warned=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> -- Reset looping variables
> SELECT @.iNextRowId = NULL
> -- get the next iRowId
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID < @.iCurrentRowId
> AND killed=0
> -- check if valid next row id?
> IF ISNULL(@.iNextRowId,0) = 0
> BEGIN
> BREAK
> END
> -- get the next row.
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> END
> GO
>
|||Andrew,
I have looked at sql_handles (which is not documented in books online) and
it seems that this doesn't contain unique values either. Can you point me to
a website where I can find out more about this column?
As far as fixing the root problem, I completely agree with you but we have
been required to give some users access to run select statements through ODBC
connections (used by Access) so I am trying to keep them from shooting
themselves in the foot.
"Andrew J. Kelly" wrote:

> You might want to take a look at the sysprocesses table and specifically the
> sql_handle column. But I would concentrate on fixing why the long running
> queries occur as a real cure<g>.
> --
> Andrew J. Kelly SQL MVP
>
> "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> kill
> messages
> over 5
>
>
|||While it has been a while since I messed with that column I was pretty sure
it changed with each batch run by the associated spid. I would do a Google
search and see what turns up. Otherwise, sorry I don't know of any docs for
this.In either case you are probably better off using the "query governor
cost limit option" in SQL Server vs trying to write your own.
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:4A8DCA0E-10E7-497E-8FA3-DCFD6B6ABFBE@.microsoft.com...
> Andrew,
> I have looked at sql_handles (which is not documented in books online) and
> it seems that this doesn't contain unique values either. Can you point me
to
> a website where I can find out more about this column?
> As far as fixing the root problem, I completely agree with you but we have
> been required to give some users access to run select statements through
ODBC[vbcol=seagreen]
> connections (used by Access) so I am trying to keep them from shooting
> themselves in the foot.
> "Andrew J. Kelly" wrote:
the[vbcol=seagreen]
running[vbcol=seagreen]
then[vbcol=seagreen]
to[vbcol=seagreen]
I[vbcol=seagreen]
to[vbcol=seagreen]
AND[vbcol=seagreen]

script to kill long running ad hoc queries

The following script is meant to identify long running queries and then kill
them after sending a warning. The main problem I have with this is how to
uniquely identify a process so that I don't terminate the wrong one as I
would in the following scenario:
1. Long running ad hoc query with SPID 50 is identified and warning messages
is sent
2. User terminates query
3. Another user connects and is assigned SPID 50. The process goes on to
kill this SPID.
Here is the script. I was planning to run the proc as a job. The time
intervals are for testing only and will probably be increased:
USE AdminDB
GO
--Create the logging table
IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
type = 'U') DROP TABLE AdHocQueryLog
GO
CREATE TABLE AdHocQueryLog
(
AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
totalruntime smallint NOT NULL,
spid smallint NOT NULL,
cpu int NOT NULL,
last_batch datetime NOT NULL,
physical_io int NOT NULL,
[memusage] int NOT NULL,
open_tran smallint NOT NULL,
loginame nvarchar(128) NOT NULL,
hostname nvarchar(128) NOT NULL,
login_time datetime NOT NULL,
dbid smallint NOT NULL,
program_name nvarchar(128) NOT NULL,
cmd nvarchar(16) NOT NULL,
warned bit NOT NULL DEFAULT 0,
killed bit NOT NULL DEFAULT 0,
stopped bit NOT NULL DEFAULT 0,
insertdate datetime NOT NULL DEFAULT getdate()
)
--Add Indexes later
--Create stored proc
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'uspKillAdHocQuery'
AND type = 'P')
DROP PROCEDURE uspKillAdHocQuery
GO
CREATE PROCEDURE uspKillAdHocQuery
AS
SET NOCOUNT ON
DECLARE @.iReturnCode int,
@.iNextRowId int,
@.iCurrentRowId int,
@.iLoopControl int,
@.spid smallint,
@.hostname nvarchar(128),
@.warned bit,
@.killed bit,
@.warning nvarchar(256),
@.cmd nvarchar(512)
INSERT AdHocQueryLog
(
totalruntime,
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
)
SELECT datediff(ss,last_batch,getdate()),
spid,
cpu,
last_batch,
physical_io,
[memusage],
open_tran,
loginame,
hostname,
login_time,
dbid,
program_name,
cmd
FROM master.dbo.sysprocesses WITH (NOLOCK)
WHERE status='runnable'
AND program_name not like 'SQL Agent%'
AND program_name not in ('%DiagnosticMan','SQL Profiler')
AND datediff(ss,last_batch,getdate())>60
--What if is already it was already there
--Loop through active processes and send warning / kill
--Initialize variables
SELECT @.iLoopControl = 1
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE killed=0
--Retrieve the first row
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
--Main processing loop
WHILE @.iLoopControl = 1
BEGIN
IF @.warned=1
BEGIN
PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate the cmd variable
--!!!What about if it is still rolling back
--!!!check whether record exists
SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
--Kill the process if it is still active
EXEC sp_executesql @.stmt=@.cmd
--update the log - process has been killed
UPDATE AdminDB.dbo.AdHocQueryLog
SET killed=1
WHERE AdHocQueryID = @.iNextRowId
END
ELSE
BEGIN--send a warning and update log
PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
convert(char(3),@.warned) +','+ convert(char(1),@.killed)
--populate variable for warning message and cmd
SELECT @.warning='Warning: An Ad Hoc query with SPID
'+convert(varchar(3),@.spid)+' has been running at this workstation for over 5
minutes and will be terminated in 5 minutes'
SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
'+@.warning+''''
--send warning
EXEC sp_executesql @.stmt=@.cmd
--update the log - warning has been sent
UPDATE AdminDB.dbo.AdHocQueryLog
SET warned=1
WHERE AdHocQueryID = @.iNextRowId
END
-- Reset looping variables
SELECT @.iNextRowId = NULL
-- get the next iRowId
SELECT @.iNextRowId = MIN(AdHocQueryID)
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID < @.iCurrentRowId
AND killed=0
-- check if valid next row id?
IF ISNULL(@.iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @.hostname=hostname,
@.spid=spid,
@.warned=warned,
@.killed=killed
FROM AdminDB.dbo.AdHocQueryLog
WHERE AdHocQueryID = @.iNextRowId
AND killed=0
END
GOYou might want to take a look at the sysprocesses table and specifically the
sql_handle column. But I would concentrate on fixing why the long running
queries occur as a real cure<g>.
--
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> The following script is meant to identify long running queries and then
kill
> them after sending a warning. The main problem I have with this is how to
> uniquely identify a process so that I don't terminate the wrong one as I
> would in the following scenario:
> 1. Long running ad hoc query with SPID 50 is identified and warning
messages
> is sent
> 2. User terminates query
> 3. Another user connects and is assigned SPID 50. The process goes on to
> kill this SPID.
> Here is the script. I was planning to run the proc as a job. The time
> intervals are for testing only and will probably be increased:
> USE AdminDB
> GO
> --Create the logging table
> IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog' AND
> type = 'U') DROP TABLE AdHocQueryLog
> GO
> CREATE TABLE AdHocQueryLog
> (
> AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> totalruntime smallint NOT NULL,
> spid smallint NOT NULL,
> cpu int NOT NULL,
> last_batch datetime NOT NULL,
> physical_io int NOT NULL,
> [memusage] int NOT NULL,
> open_tran smallint NOT NULL,
> loginame nvarchar(128) NOT NULL,
> hostname nvarchar(128) NOT NULL,
> login_time datetime NOT NULL,
> dbid smallint NOT NULL,
> program_name nvarchar(128) NOT NULL,
> cmd nvarchar(16) NOT NULL,
> warned bit NOT NULL DEFAULT 0,
> killed bit NOT NULL DEFAULT 0,
> stopped bit NOT NULL DEFAULT 0,
> insertdate datetime NOT NULL DEFAULT getdate()
> )
> --Add Indexes later
>
> --Create stored proc
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'uspKillAdHocQuery'
> AND type = 'P')
> DROP PROCEDURE uspKillAdHocQuery
> GO
> CREATE PROCEDURE uspKillAdHocQuery
> AS
> SET NOCOUNT ON
> DECLARE @.iReturnCode int,
> @.iNextRowId int,
> @.iCurrentRowId int,
> @.iLoopControl int,
> @.spid smallint,
> @.hostname nvarchar(128),
> @.warned bit,
> @.killed bit,
> @.warning nvarchar(256),
> @.cmd nvarchar(512)
> INSERT AdHocQueryLog
> (
> totalruntime,
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> )
> SELECT datediff(ss,last_batch,getdate()),
> spid,
> cpu,
> last_batch,
> physical_io,
> [memusage],
> open_tran,
> loginame,
> hostname,
> login_time,
> dbid,
> program_name,
> cmd
> FROM master.dbo.sysprocesses WITH (NOLOCK)
> WHERE status='runnable'
> AND program_name not like 'SQL Agent%'
> AND program_name not in ('%DiagnosticMan','SQL Profiler')
> AND datediff(ss,last_batch,getdate())>60
> --What if is already it was already there
>
> --Loop through active processes and send warning / kill
> --Initialize variables
> SELECT @.iLoopControl = 1
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE killed=0
> --Retrieve the first row
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> --Main processing loop
> WHILE @.iLoopControl = 1
> BEGIN
> IF @.warned=1
> BEGIN
> PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate the cmd variable
> --!!!What about if it is still rolling back
> --!!!check whether record exists
> SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
> --Kill the process if it is still active
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - process has been killed
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET killed=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> ELSE
> BEGIN--send a warning and update log
> PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> --populate variable for warning message and cmd
> SELECT @.warning='Warning: An Ad Hoc query with SPID
> '+convert(varchar(3),@.spid)+' has been running at this workstation for
over 5
> minutes and will be terminated in 5 minutes'
> SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
> '+@.warning+''''
> --send warning
> EXEC sp_executesql @.stmt=@.cmd
> --update the log - warning has been sent
> UPDATE AdminDB.dbo.AdHocQueryLog
> SET warned=1
> WHERE AdHocQueryID = @.iNextRowId
> END
> -- Reset looping variables
> SELECT @.iNextRowId = NULL
> -- get the next iRowId
> SELECT @.iNextRowId = MIN(AdHocQueryID)
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID < @.iCurrentRowId
> AND killed=0
> -- check if valid next row id?
> IF ISNULL(@.iNextRowId,0) = 0
> BEGIN
> BREAK
> END
> -- get the next row.
> SELECT @.hostname=hostname,
> @.spid=spid,
> @.warned=warned,
> @.killed=killed
> FROM AdminDB.dbo.AdHocQueryLog
> WHERE AdHocQueryID = @.iNextRowId
> AND killed=0
> END
> GO
>|||While it has been a while since I messed with that column I was pretty sure
it changed with each batch run by the associated spid. I would do a Google
search and see what turns up. Otherwise, sorry I don't know of any docs for
this.In either case you are probably better off using the "query governor
cost limit option" in SQL Server vs trying to write your own.
--
Andrew J. Kelly SQL MVP
"DBA72" <DBA72@.discussions.microsoft.com> wrote in message
news:4A8DCA0E-10E7-497E-8FA3-DCFD6B6ABFBE@.microsoft.com...
> Andrew,
> I have looked at sql_handles (which is not documented in books online) and
> it seems that this doesn't contain unique values either. Can you point me
to
> a website where I can find out more about this column?
> As far as fixing the root problem, I completely agree with you but we have
> been required to give some users access to run select statements through
ODBC
> connections (used by Access) so I am trying to keep them from shooting
> themselves in the foot.
> "Andrew J. Kelly" wrote:
> > You might want to take a look at the sysprocesses table and specifically
the
> > sql_handle column. But I would concentrate on fixing why the long
running
> > queries occur as a real cure<g>.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "DBA72" <DBA72@.discussions.microsoft.com> wrote in message
> > news:C4620B3D-6EDC-440E-902D-6A5C923F0666@.microsoft.com...
> > > The following script is meant to identify long running queries and
then
> > kill
> > > them after sending a warning. The main problem I have with this is how
to
> > > uniquely identify a process so that I don't terminate the wrong one as
I
> > > would in the following scenario:
> > >
> > > 1. Long running ad hoc query with SPID 50 is identified and warning
> > messages
> > > is sent
> > > 2. User terminates query
> > > 3. Another user connects and is assigned SPID 50. The process goes on
to
> > > kill this SPID.
> > >
> > > Here is the script. I was planning to run the proc as a job. The time
> > > intervals are for testing only and will probably be increased:
> > >
> > > USE AdminDB
> > > GO
> > >
> > > --Create the logging table
> > > IF EXISTS(SELECT name FROM sysobjects WHERE name = N'AdHocQueryLog'
AND
> > > type = 'U') DROP TABLE AdHocQueryLog
> > > GO
> > > CREATE TABLE AdHocQueryLog
> > > (
> > > AdHocQueryID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
> > > totalruntime smallint NOT NULL,
> > > spid smallint NOT NULL,
> > > cpu int NOT NULL,
> > > last_batch datetime NOT NULL,
> > > physical_io int NOT NULL,
> > > [memusage] int NOT NULL,
> > > open_tran smallint NOT NULL,
> > > loginame nvarchar(128) NOT NULL,
> > > hostname nvarchar(128) NOT NULL,
> > > login_time datetime NOT NULL,
> > > dbid smallint NOT NULL,
> > > program_name nvarchar(128) NOT NULL,
> > > cmd nvarchar(16) NOT NULL,
> > > warned bit NOT NULL DEFAULT 0,
> > > killed bit NOT NULL DEFAULT 0,
> > > stopped bit NOT NULL DEFAULT 0,
> > > insertdate datetime NOT NULL DEFAULT getdate()
> > > )
> > >
> > > --Add Indexes later
> > >
> > >
> > > --Create stored proc
> > > IF EXISTS (SELECT name
> > > FROM sysobjects
> > > WHERE name = N'uspKillAdHocQuery'
> > > AND type = 'P')
> > > DROP PROCEDURE uspKillAdHocQuery
> > > GO
> > >
> > > CREATE PROCEDURE uspKillAdHocQuery
> > > AS
> > > SET NOCOUNT ON
> > > DECLARE @.iReturnCode int,
> > > @.iNextRowId int,
> > > @.iCurrentRowId int,
> > > @.iLoopControl int,
> > > @.spid smallint,
> > > @.hostname nvarchar(128),
> > > @.warned bit,
> > > @.killed bit,
> > > @.warning nvarchar(256),
> > > @.cmd nvarchar(512)
> > >
> > > INSERT AdHocQueryLog
> > > (
> > > totalruntime,
> > > spid,
> > > cpu,
> > > last_batch,
> > > physical_io,
> > > [memusage],
> > > open_tran,
> > > loginame,
> > > hostname,
> > > login_time,
> > > dbid,
> > > program_name,
> > > cmd
> > > )
> > > SELECT datediff(ss,last_batch,getdate()),
> > > spid,
> > > cpu,
> > > last_batch,
> > > physical_io,
> > > [memusage],
> > > open_tran,
> > > loginame,
> > > hostname,
> > > login_time,
> > > dbid,
> > > program_name,
> > > cmd
> > > FROM master.dbo.sysprocesses WITH (NOLOCK)
> > > WHERE status='runnable'
> > > AND program_name not like 'SQL Agent%'
> > > AND program_name not in ('%DiagnosticMan','SQL Profiler')
> > > AND datediff(ss,last_batch,getdate())>60
> > > --What if is already it was already there
> > >
> > >
> > > --Loop through active processes and send warning / kill
> > > --Initialize variables
> > > SELECT @.iLoopControl = 1
> > > SELECT @.iNextRowId = MIN(AdHocQueryID)
> > > FROM AdminDB.dbo.AdHocQueryLog
> > > WHERE killed=0
> > >
> > > --Retrieve the first row
> > > SELECT @.hostname=hostname,
> > > @.spid=spid,
> > > @.warned=warned,
> > > @.killed=killed
> > > FROM AdminDB.dbo.AdHocQueryLog
> > > WHERE AdHocQueryID = @.iNextRowId
> > > AND killed=0
> > >
> > > --Main processing loop
> > > WHILE @.iLoopControl = 1
> > > BEGIN
> > > IF @.warned=1
> > > BEGIN
> > > PRINT 'KILL: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> > > convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> > > --populate the cmd variable
> > > --!!!What about if it is still rolling back
> > > --!!!check whether record exists
> > > SELECT @.cmd='KILL '+convert(varchar(3),@.spid)
> > > --Kill the process if it is still active
> > > EXEC sp_executesql @.stmt=@.cmd
> > > --update the log - process has been killed
> > > UPDATE AdminDB.dbo.AdHocQueryLog
> > > SET killed=1
> > > WHERE AdHocQueryID = @.iNextRowId
> > > END
> > > ELSE
> > > BEGIN--send a warning and update log
> > > PRINT 'WARN: '+ @.hostname +','+ convert(varchar(3),@.spid) +','+
> > > convert(char(3),@.warned) +','+ convert(char(1),@.killed)
> > > --populate variable for warning message and cmd
> > > SELECT @.warning='Warning: An Ad Hoc query with SPID
> > > '+convert(varchar(3),@.spid)+' has been running at this workstation for
> > over 5
> > > minutes and will be terminated in 5 minutes'
> > > SELECT @.cmd='master.dbo.xp_cmdshell ''NET SEND '+@.hostname+'
> > > '+@.warning+''''
> > > --send warning
> > > EXEC sp_executesql @.stmt=@.cmd
> > > --update the log - warning has been sent
> > > UPDATE AdminDB.dbo.AdHocQueryLog
> > > SET warned=1
> > > WHERE AdHocQueryID = @.iNextRowId
> > > END
> > > -- Reset looping variables
> > > SELECT @.iNextRowId = NULL
> > > -- get the next iRowId
> > > SELECT @.iNextRowId = MIN(AdHocQueryID)
> > > FROM AdminDB.dbo.AdHocQueryLog
> > > WHERE AdHocQueryID < @.iCurrentRowId
> > > AND killed=0
> > > -- check if valid next row id?
> > > IF ISNULL(@.iNextRowId,0) = 0
> > > BEGIN
> > > BREAK
> > > END
> > > -- get the next row.
> > > SELECT @.hostname=hostname,
> > > @.spid=spid,
> > > @.warned=warned,
> > > @.killed=killed
> > > FROM AdminDB.dbo.AdHocQueryLog
> > > WHERE AdHocQueryID = @.iNextRowId
> > > AND killed=0
> > > END
> > > GO
> > >
> >
> >
> >sql

Script to kill all user connections to a db except three

Hi guru's
SQL 2000 Database needs all user connections terminated to it except
three. These need to be kept running for a job ran later on.
Is anyone aware of a script that will recursively go through all the
connections to a specific database and kill all processes except the
ones we want to remain intact ?
Please let me know if you need any more information
Thanks in advance
PaulYou can use the one at http://www.dbmaint.com/util_proc.asp as a starting po
int...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>|||ALTER DATABASE .... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>

Script to kill all user connections to a db except three

Hi guru's
SQL 2000 Database needs all user connections terminated to it except
three. These need to be kept running for a job ran later on.
Is anyone aware of a script that will recursively go through all the
connections to a specific database and kill all processes except the
ones we want to remain intact ?
Please let me know if you need any more information
Thanks in advance
Paul
ALTER DATABASE .... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegro ups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>

Script to kill all user connections to a db except three

Hi guru's
SQL 2000 Database needs all user connections terminated to it except
three. These need to be kept running for a job ran later on.
Is anyone aware of a script that will recursively go through all the
connections to a specific database and kill all processes except the
ones we want to remain intact ?
Please let me know if you need any more information
Thanks in advance
PaulYou can use the one at http://www.dbmaint.com/util_proc.asp as a starting point...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>|||ALTER DATABASE .... SET SINGLE_USER WITH ROLLBACK IMMEDIATE
<paullie69@.hotmail.com> wrote in message
news:1173778649.068899.204390@.64g2000cwx.googlegroups.com...
> Hi guru's
> SQL 2000 Database needs all user connections terminated to it except
> three. These need to be kept running for a job ran later on.
> Is anyone aware of a script that will recursively go through all the
> connections to a specific database and kill all processes except the
> ones we want to remain intact ?
> Please let me know if you need any more information
> Thanks in advance
> Paul
>

Script to detect long running jobs

Is there a function/proc to help me detect jobs that are running long, say twice the normal run time?
I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?

Script to detect long running jobs

Is there a function/proc to help me detect jobs that are running long, say twice the normal run time?I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?

Script to detect long running jobs

Is there a function/proc to help me detect jobs that are running long, say t
wice the normal run time?I don't think so, but you could build a process that reviews the
sysjobhistory table and determine those jobs runs that contain durations
longer then the average. Review the format of the sysjobhistory table in
BOL.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Ken Dutton" <fj60landcruiser@.yahoo.com> wrote in message
news:1FCC1245-DF44-4CA6-8856-4AEEC52206C8@.microsoft.com...
> Is there a function/proc to help me detect jobs that are running long, say
twice the normal run time?

Script tasks not pre-compiling all of a sudden

I've been building and running script tasks for years without issue. Then all of sudden last week Visual Studio starts showing the "Task is configured to pre-compile the script, but binary code is not found" error anytime I open a script -- even I make no changes. If I copy the dtsx package to another machine, the script compiles fine and I can see the binary data in the raw .dtx file -- so I know the code is correct.

I can also reproduce the error simply by adding a new script task, going into "Design Script", make no changes to the default code (which is basically one line: Dts.TaskResult = Dts.Results.Success) and simply press "OK" on the Script Task Editor.

I've been trying to find some VS setting somewhere that might stop the script IDE from producing the binary code, but I can't. It doesn't seem to be project setting, because all my SSIS projects are now suffering this problem on my main dev box.

Anyone experience this?

Did you just recently apply a bunch of Microsoft patches?

Did you see this thread? http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1855810&SiteID=1

Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed becau
se
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support

Script runs fine in QA, not as a job

I'm running the following script, which works fin in Query Analyzer.
When I'm running it in a job, the job fails.
When I'm running as a stored proc, through a job that calls the SP, I get a
failure.
When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
the following SET options have incorrect settings: 'ANSI_NULLS.') on one
table.
I added these 2 lines in the Stored Proc without any success.
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
How can I have this script run as a job?
Running SQL2000, sp3a on Windows 2000 Server.
My reindex script
DECLARE @.TableName VARCHAR(255)
DECLARE @.exec_string VARCHAR(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
exec(@.exec_string)
--DBCC DBREINDEX([@.TableName])
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
--
La Senza SupportThe mostly cause of the problem is security context. When you run the
code interactively in Query Analyzer you are running under your
security context. When you run it as a job you are using SQL Agent's
security context. Check what user is used to start the SQL Agent and
make sure it has the proper permissions to execute the job.
-Uncle Pete
La Senza Support wrote:
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support|||Have you thought to create a stored procedure which holds all that code?
You could call the stored procedure from within the job.
Also, keep in mind that the user that owns the job must have the appropriate
permissions to execute the stored procedure (and its underlying statements).
Keith Kratochvil
"La Senza Support" <LaSenzaSupport@.discussions.microsoft.com> wrote in
message news:BAE98C02-7721-499E-A79F-3B3AB0A36C99@.microsoft.com...
> I'm running the following script, which works fin in Query Analyzer.
> When I'm running it in a job, the job fails.
> When I'm running as a stored proc, through a job that calls the SP, I get
> a
> failure.
> When I lauch the stored proc from Query Analyzer, I get (DBCC failed
> because
> the following SET options have incorrect settings: 'ANSI_NULLS.') on one
> table.
> I added these 2 lines in the Stored Proc without any success.
> SET QUOTED_IDENTIFIER ON
> SET ARITHABORT ON
> How can I have this script run as a job?
> Running SQL2000, sp3a on Windows 2000 Server.
>
> My reindex script
> DECLARE @.TableName VARCHAR(255)
> DECLARE @.exec_string VARCHAR(255)
> DECLARE TableCursor CURSOR FOR
> SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> WHERE UPPER(TABLE_TYPE) = 'BASE TABLE'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.exec_string = 'dbcc dbreindex ([' + @.TableName + '])'
> exec(@.exec_string)
> --DBCC DBREINDEX([@.TableName])
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> GO
> --
> La Senza Support

script in MSDE

Hi,
i have msde on my system and i running my queries though osql utility
can any one tell me how can i run a script file in osql i.e in my C:
drive
what cmd i have to type
Thanks,
Use the /i switch for OSQL.EXE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1181204598.353419.105710@.k79g2000hse.googlegr oups.com...
> Hi,
> i have msde on my system and i running my queries though osql utility
> can any one tell me how can i run a script file in osql i.e in my C:
> drive
> what cmd i have to type
> Thanks,
>
|||On Jun 7, 2:59 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> Use the /i switch for OSQL.EXE.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Abhi" <bawej...@.gmail.com> wrote in message
> news:1181204598.353419.105710@.k79g2000hse.googlegr oups.com...
>
right now I am in osql utility but still i am not able to run the
script i.e on c:\folder
Please advice...
|||So you want to call a script while *inside* OSQL? If so, check out the :r option.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1181212829.895541.324540@.n15g2000prd.googlegr oups.com...
> On Jun 7, 2:59 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> right now I am in osql utility but still i am not able to run the
> script i.e on c:\folder
> Please advice...
>
|||On Jun 7, 3:53 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> So you want to call a script while *inside* OSQL? If so, check out the :r option.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Abhi" <bawej...@.gmail.com> wrote in message
> news:1181212829.895541.324540@.n15g2000prd.googlegr oups.com...
>
>
>
>
Many Thanks, Sir it's working in osql

script in MSDE

Hi,
i have msde on my system and i running my queries though osql utility
can any one tell me how can i run a script file in osql i.e in my C:
drive
what cmd i have to type
Thanks,Use the /i switch for OSQL.EXE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1181204598.353419.105710@.k79g2000hse.googlegroups.com...
> Hi,
> i have msde on my system and i running my queries though osql utility
> can any one tell me how can i run a script file in osql i.e in my C:
> drive
> what cmd i have to type
> Thanks,
>|||On Jun 7, 2:59 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> Use the /i switch for OSQL.EXE.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Abhi" <bawej...@.gmail.com> wrote in message
> news:1181204598.353419.105710@.k79g2000hse.googlegroups.com...
> > Hi,
> > i have msde on my system and i running my queries though osql utility
> > can any one tell me how can i run a script file in osql i.e in my C:
> > drive
> > what cmd i have to type
> > Thanks,
right now I am in osql utility but still i am not able to run the
script i.e on c:\folder
Please advice...|||So you want to call a script while *inside* OSQL? If so, check out the :r option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1181212829.895541.324540@.n15g2000prd.googlegroups.com...
> On Jun 7, 2:59 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> Use the /i switch for OSQL.EXE.
>> --
>> Tibor Karaszi, SQL Server
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "Abhi" <bawej...@.gmail.com> wrote in message
>> news:1181204598.353419.105710@.k79g2000hse.googlegroups.com...
>> > Hi,
>> > i have msde on my system and i running my queries though osql utility
>> > can any one tell me how can i run a script file in osql i.e in my C:
>> > drive
>> > what cmd i have to type
>> > Thanks,
> right now I am in osql utility but still i am not able to run the
> script i.e on c:\folder
> Please advice...
>|||On Jun 7, 3:53 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> So you want to call a script while *inside* OSQL? If so, check out the :r option.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Abhi" <bawej...@.gmail.com> wrote in message
> news:1181212829.895541.324540@.n15g2000prd.googlegroups.com...
> > On Jun 7, 2:59 pm, "Tibor Karaszi"
> > <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> >> Use the /i switch for OSQL.EXE.
> >> --
> >> Tibor Karaszi, SQL Server
> >> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/...
> >> "Abhi" <bawej...@.gmail.com> wrote in message
> >>news:1181204598.353419.105710@.k79g2000hse.googlegroups.com...
> >> > Hi,
> >> > i have msde on my system and i running my queries though osql utility
> >> > can any one tell me how can i run a script file in osql i.e in my C:
> >> > drive
> >> > what cmd i have to type
> >> > Thanks,
> > right now I am in osql utility but still i am not able to run the
> > script i.e on c:\folder
> > Please advice...
Many Thanks, Sir it's working in osql

script in MSDE

Hi,
i have msde on my system and i running my queries though osql utility
can any one tell me how can i run a script file in osql i.e in my C:
drive
what cmd i have to type
Thanks,Use the /i switch for OSQL.EXE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1181204598.353419.105710@.k79g2000hse.googlegroups.com...
> Hi,
> i have msde on my system and i running my queries though osql utility
> can any one tell me how can i run a script file in osql i.e in my C:
> drive
> what cmd i have to type
> Thanks,
>|||On Jun 7, 2:59 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> Use the /i switch for OSQL.EXE.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Abhi" <bawej...@.gmail.com> wrote in message
> news:1181204598.353419.105710@.k79g2000hse.googlegroups.com...
>
>
>
right now I am in osql utility but still i am not able to run the
script i.e on c:\folder
Please advice...|||So you want to call a script while *inside* OSQL? If so, check out the :r op
tion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Abhi" <bawejaji@.gmail.com> wrote in message
news:1181212829.895541.324540@.n15g2000prd.googlegroups.com...
> On Jun 7, 2:59 pm, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> right now I am in osql utility but still i am not able to run the
> script i.e on c:\folder
> Please advice...
>|||On Jun 7, 3:53 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> So you want to call a script while *inside* OSQL? If so, check out the :r
option.
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Abhi" <bawej...@.gmail.com> wrote in message
> news:1181212829.895541.324540@.n15g2000prd.googlegroups.com...
>
>
>
>
>
>
>
>
Many Thanks, Sir it's working in osql

Script errors in SSIS

I have been getting a recurring error while running the folowing script from an SSIS package. I have bolded the parts that I think may be of use. I didn't know if this would be a T-SQL or SSIS question, but thanks in advance for help.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
" failed with the following error: "Line 15: Incorrect syntax near '2003'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task 1

Could you have a database named '2003', maybe it's being converted to an integer and then when building the sql string dynamically it is failing because it needs to be of a character data type.

Do you have the 'resultset' property of the execute sql task set to 'none'?

Does that code work when executed from within management studio?

Why don't you try putting your code into a stored procedure and call the stored proc from the execute sql task

|||-Its very possible that a database could be named a number, how can I remedy this situation?
-The resultset is properly set to none
-The code works from within the management studio
-Can I just create an sp that the SSIS package calls from the repository server? I don't want to have to create the sp on each server.

Thanks for your help
-Kyle
|||

Try substituting this portion of the script.

Code Snippet

from (
select b.name as USERName, c.name as RoleName
from [' + @.dbName+'].dbo.sysmembers a '+char(13)+
' join ['+ @.dbName+'].dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join ['+@.dbName +'].dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

This will take care of any database names that throw errors.|||SP_HELPLOGINS is the answer.
-Kyle

Script errors in SSIS

I have been getting a recurring error while running the folowing script from an SSIS package. I have bolded the parts that I think may be of use. I didn't know if this would be a T-SQL or SSIS question, but thanks in advance for help.

Error: 0xC002F210 at Execute SQL Task 1, Execute SQL Task: Executing the query "declare @.dbname varchar(200)
declare @.mSql1 varchar(8000)

DECLARE DBName_Cursor CURSOR FOR
select name
from master.dbo.sysdatabases
where name not in ('mssecurity','tempdb')
Order by name

OPEN DBName_Cursor

FETCH NEXT FROM DBName_Cursor INTO @.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN
Set @.mSQL1 = ' Insert into [tempdb].[dbo].[DBROLES] ( DBName, UserName, db_owner, db_accessadmin,
db_securityadmin, db_ddladmin, db_datareader, db_datawriter,
db_denydatareader, db_denydatawriter )
SELECT '+''''+@.dbName +''''+ ' as DBName ,UserName, '+char(13)+ '
Max(CASE RoleName WHEN ''db_owner'' THEN ''Yes'' ELSE ''No'' END) AS db_owner,
Max(CASE RoleName WHEN ''db_accessadmin '' THEN ''Yes'' ELSE ''No'' END) AS db_accessadmin ,
Max(CASE RoleName WHEN ''db_securityadmin'' THEN ''Yes'' ELSE ''No'' END) AS db_securityadmin,
Max(CASE RoleName WHEN ''db_ddladmin'' THEN ''Yes'' ELSE ''No'' END) AS db_ddladmin,
Max(CASE RoleName WHEN ''db_datareader'' THEN ''Yes'' ELSE ''No'' END) AS db_datareader,
Max(CASE RoleName WHEN ''db_datawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_datawriter,
Max(CASE RoleName WHEN ''db_denydatareader'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatareader,
Max(CASE RoleName WHEN ''db_denydatawriter'' THEN ''Yes'' ELSE ''No'' END) AS db_denydatawriter
from (
select b.name as USERName, c.name as RoleName
from ' + @.dbName+'.dbo.sysmembers a '+char(13)+
' join '+ @.dbName+'.dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join '+@.dbName +'.dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

--Print @.mSql1
Execute (@.mSql1)

FETCH NEXT FROM DBName_Cursor INTO @.dbname
END

CLOSE DBName_Cursor
DEALLOCATE DBName_Cursor
" failed with the following error: "Line 15: Incorrect syntax near '2003'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Task failed: Execute SQL Task 1

Could you have a database named '2003', maybe it's being converted to an integer and then when building the sql string dynamically it is failing because it needs to be of a character data type.

Do you have the 'resultset' property of the execute sql task set to 'none'?

Does that code work when executed from within management studio?

Why don't you try putting your code into a stored procedure and call the stored proc from the execute sql task

|||-Its very possible that a database could be named a number, how can I remedy this situation?
-The resultset is properly set to none
-The code works from within the management studio
-Can I just create an sp that the SSIS package calls from the repository server? I don't want to have to create the sp on each server.

Thanks for your help
-Kyle
|||

Try substituting this portion of the script.

Code Snippet

from (
select b.name as USERName, c.name as RoleName
from [' + @.dbName+'].dbo.sysmembers a '+char(13)+
' join ['+ @.dbName+'].dbo.sysusers b '+char(13)+
' on a.memberuid = b.uid join ['+@.dbName +'].dbo.sysusers c
on a.groupuid = c.uid )s
Group by USERName
order by UserName'

This will take care of any database names that throw errors.|||SP_HELPLOGINS is the answer.
-Kyle

Script Component failed validation and returned validation status "VS_ISBROKEN". Error

I have been running this package before (with no errors) but now I get this error message. Is it my enviroment that is broken? All my Script Compnents end up with this error code.

Error: 0xC0047062 at Data Flow Task, Script Component [53]: System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed with HRESULT: 0x80040154(Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))).

at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName, String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error: 0xC0047062 at Data Flow Task, Script Component [53]: System.Runtime.InteropServices.COMException (0x80040154): Retrieving the COM class factory for component with CLSID {A138CF39-2CAE-42C2-ADB3-022658D79F2F} failed with HRESULT: 0x80040154(Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))).

at Microsoft.VisualBasic.Vsa.VsaEngine.CreateEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.CheckEngine()

at Microsoft.VisualBasic.Vsa.VsaEngine.set_RootMoniker(String value)

at Microsoft.SqlServer.VSAHosting.Runtime.CreateVsaEngine()

at Microsoft.SqlServer.Dts.Pipeline.ScriptRuntime..ctor(String projectName, String moniker, String language, Boolean showErrorUI)

at Microsoft.SqlServer.Dts.Pipeline.ScriptComponentHost.CreateUserComponent()

Error: 0xC004706B at Data Flow Task, DTS.Pipeline: "component "Script Component" (53)" failed validation and returned validation status "VS_ISBROKEN".

Error: 0xC004700C at Data Flow Task, DTS.Pipeline: One or more component failed validation.

Error: 0xC0024107 at Data Flow Task: There were errors during task validation.

SSIS package "src_abs_budg_sales_wc.dtsx" finished: Failure.

The CLSID quoted in the error message is the Visual Studio for Applications runtime, progid "VsaVbRT.8.0," described as "Microsoft Visual Basic Scripting Engine." It looks like the corresponding COM DLL is VsaVb7rt.dll. So it looks like your VSA installation is corrupted or incomplete. Can you try re-registering that DLL using regsvr32.exe?|||

It was my installation that was corrupted, thanks

Script code for "Reinitialize All Subscripers" function SQL 2005

Hello All
We are running Merge replication on SQL 2005 with Pocket PC Clients.
My question is, can i script (T-SQL) the same function as if i rightclick on
the Replication Publication, and select "Reinitialize All Subscripers" and
then click on "Create New SnapShot" + "Upload Changes"?
Which system stored procedures should i use? I need to do the same just with
T-SQL.
Thanks
use sp_reinitsubscription
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...
> Hello All
> We are running Merge replication on SQL 2005 with Pocket PC Clients.
> My question is, can i script (T-SQL) the same function as if i rightclick
> on
> the Replication Publication, and select "Reinitialize All Subscripers" and
> then click on "Create New SnapShot" + "Upload Changes"?
> Which system stored procedures should i use? I need to do the same just
> with
> T-SQL.
> Thanks
|||or sp_reinitpullsubscription if you have pull subscriptions
Hilary Cotter wrote:[vbcol=seagreen]
> use sp_reinitsubscription
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "RU Developer" <RUDeveloper@.discussions.microsoft.com> wrote in message
> news:30B41C88-47D1-4C19-B580-757A3F40FE77@.microsoft.com...

Script

Hi all,
I need to put together a script that would let me know when the space is running low on each drive. Do you have a sample code? Thanks.Use xp_fixeddrives. The rest should be easy enough.|||I vote for WMI. Plenty of samples for you to choose from at this site. (http://www.microsoft.com/technet/scriptcenter/default.mspx).

xp_fixxeddrives may be easier, but you should also be monitoring your backup resources and other locations as well. Anyway, it's easy to learn and a usefull secondary skill to have.

Regards,

hmscott