Scope parameter: following

Showing posts with label following. Show all posts

Monday, March 26, 2012

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 find out the number of row in all the table of a database.

HI,
I am using following script to find out the number of row in all the
table of a database is there any simple way out? if so pls mail me
declare @.TAB VARCHAR (20),
@.qu nvarchar (100)
DECLARE TABALE CURSOR FOR
select name from sysobjects where xtype='u' order by name open tabale
FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
@.TAB = 'SALES1'
--select name from sysobjects where name = @.tab SET @.QU ='SELECT
COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
TABALE INTO @.TAB end close tabale deallocate tabale
Thanks
Sajid ChhapekarYOu could use the undocumented procedure sp_msforeachtable, but keep in
mind that this one is undocumented and might be deprecated in further
versions of SQL Server.
sp_msforeachtable 'SELECT ''?'' as TableName COUNT(*) AS Counted_rows
FROM ?'
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||Hello,
Try the following query to get your row counts without having to use a cursor.
SELECT sysobjects.name, rows FROM Sysindexes
INNER JOIN Sysobjects
on Sysindexes.id = sysobjects.id
AND indid < 2
AND sysobjects.xtype = 'u'
AND sysobjects.name <> 'dtproperties'
You should get the same result as your cursor.
Thanks Kllyj64
"csajid@.gmail.com" wrote:
> HI,
> I am using following script to find out the number of row in all the
> table of a database is there any simple way out? if so pls mail me
>
> declare @.TAB VARCHAR (20),
> @.qu nvarchar (100)
> DECLARE TABALE CURSOR FOR
> select name from sysobjects where xtype='u' order by name open tabale
> FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> @.TAB = 'SALES1'
> --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> TABALE INTO @.TAB end close tabale deallocate tabale
>
> Thanks
> Sajid Chhapekar
>|||Hi,
That's great. Thanks for this.
Thanks and regards,
Sajid.
kllyj64 wrote:
> Hello,
> Try the following query to get your row counts without having to use a cursor.
> SELECT sysobjects.name, rows FROM Sysindexes
> INNER JOIN Sysobjects
> on Sysindexes.id = sysobjects.id
> AND indid < 2
> AND sysobjects.xtype = 'u'
> AND sysobjects.name <> 'dtproperties'
> You should get the same result as your cursor.
>
> --
> Thanks Kllyj64
>
> "csajid@.gmail.com" wrote:
> > HI,
> >
> > I am using following script to find out the number of row in all the
> > table of a database is there any simple way out? if so pls mail me
> >
> >
> > declare @.TAB VARCHAR (20),
> > @.qu nvarchar (100)
> >
> > DECLARE TABALE CURSOR FOR
> > select name from sysobjects where xtype='u' order by name open tabale
> > FETCH NEXT FROM TABALE INTO @.TAB while @.@.fetch_status = 0 begin --SET
> > @.TAB = 'SALES1'
> > --select name from sysobjects where name = @.tab SET @.QU ='SELECT
> > COUNT(*) FROM '+@.TAB print @.tab EXEC sp_executesql @.QU fetch next from
> > TABALE INTO @.TAB end close tabale deallocate tabale
> >
> >
> > Thanks
> > Sajid Chhapekar
> >
> >

Script to find out the number of row in all the table of a database.

Friday, March 23, 2012

Script to convert char to int please help....

I ran the following simple select statement :
select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted = '20041123'
Then I got the following error message after several thousand records were
fetched.
Syntax error converting the varchar value 'ZAER "' to a column of data
type int.
What I like to do is convert that column which is char to int or select
everything with the value 'ZAER' and copy to another table and then convert.
I did the first and updated, copied the data back and ran the select and
still got the same error. Can someone help please? Thank you.
JamesHow do you plan to convert the value 'ZAER' to an INT? And why do you have
non-integer strings mixed in with integer amounts in your 'amount' column?
I would recommend that you clean up your data and re-define the column as
INT to avoid these types of issues in the future.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> I ran the following simple select statement :
> select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => '20041123'
> Then I got the following error message after several thousand records were
> fetched.
> Syntax error converting the varchar value 'ZAER "' to a column of data
> type int.
> What I like to do is convert that column which is char to int or select
> everything with the value 'ZAER' and copy to another table and then
convert.
> I did the first and updated, copied the data back and ran the select and
> still got the same error. Can someone help please? Thank you.
> James|||What I wanted to do was remove all the non-integer strings first with an
update to '0' before converting. Do you know what I can do?
James.
"Adam Machanic" wrote:
> How do you plan to convert the value 'ZAER' to an INT? And why do you have
> non-integer strings mixed in with integer amounts in your 'amount' column?
> I would recommend that you clean up your data and re-define the column as
> INT to avoid these types of issues in the future.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > I ran the following simple select statement :
> >
> > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > '20041123'
> > Then I got the following error message after several thousand records were
> > fetched.
> >
> > Syntax error converting the varchar value 'ZAER "' to a column of data
> > type int.
> >
> > What I like to do is convert that column which is char to int or select
> > everything with the value 'ZAER' and copy to another table and then
> convert.
> > I did the first and updated, copied the data back and ran the select and
> > still got the same error. Can someone help please? Thank you.
> >
> > James
>
>|||Here's what I would do:
UPDATE tcase
SET amount= '0'
WHERE PATINDEX('%[^0-9]%', RTRIM(LTRIM(amount))) > 0
OR amount IS NULL
ALTER TABLE tcase
ALTER COLUMN amount INT NOT NULL
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:506B7C12-D36E-4F82-A2A1-5C545CD643CC@.microsoft.com...
> What I wanted to do was remove all the non-integer strings first with an
> update to '0' before converting. Do you know what I can do?
> James.
>
> "Adam Machanic" wrote:
> > How do you plan to convert the value 'ZAER' to an INT? And why do you
have
> > non-integer strings mixed in with integer amounts in your 'amount'
column?
> > I would recommend that you clean up your data and re-define the column
as
> > INT to avoid these types of issues in the future.
> >
> >
> > --
> > Adam Machanic
> > SQL Server MVP
> > http://www.sqljunkies.com/weblog/amachanic
> > --
> >
> >
> > "James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
> > news:EB3DA6E5-7224-4E94-8FA5-6AB637662A05@.microsoft.com...
> > > I ran the following simple select statement :
> > >
> > > select * from tcase where ltrim(rtrim(amount)) > 66 and dateposted => > > '20041123'
> > > Then I got the following error message after several thousand records
were
> > > fetched.
> > >
> > > Syntax error converting the varchar value 'ZAER "' to a column of
data
> > > type int.
> > >
> > > What I like to do is convert that column which is char to int or
select
> > > everything with the value 'ZAER' and copy to another table and then
> > convert.
> > > I did the first and updated, copied the data back and ran the select
and
> > > still got the same error. Can someone help please? Thank you.
> > >
> > > James
> >
> >
> >

Wednesday, March 21, 2012

Script task: Dts.Logging.Log

I am trying to understand the use-case of Dts.Logging.Log.

Following BOL, I see that I can use Dts.Log to send a custom log message to my log provider. The following example code produces an event called "User:ScriptTaskLogEntry", with the message "My custom message here".

Dim dataBytes(0) As Byte
Dts.Log("My custom message here", 0, dataBytes)

I would like to be able to produce my own custom event (lets call it "MyCustomEvent"), as well as a custom log message. I thought it would be possible via Dts.Logging.Log, which requires that I specify an event name, in addition to a log entry. However, when I place the following code in the script task, I do not get any logging whatsoever.

Dim dataBytes(0) As Byte
Dts.Logging.Log("MyCustomEvent", System.Environment.MachineName, System.Environment.UserName, "Custom Log", "", "", "My custom message here", Now, Now, 0, dataBytes)

Any thoughts?

Thanks!

The script task can't log custom message type. The problem is that custom log message needs to be declared by task in advance so that it appears in the Details tab of the Logging window.

Custom task (reusable separately packaged .NET or C++ code, that appears on toolbox just like any Microsoft task) can do it, but this functionality is not available in Script task.sql

Script Task Error?

I have the following code inside a Script task:

Imports System

Imports System.Data

Imports System.Math

Imports Microsoft.SqlServer.Dts.Runtime

Imports System.Collections

Public Class ScriptMain

Public Sub Main()

Dim nm As String = "ship_metrics_rpt_*.csv"

Dim files As ObjectModel.ReadOnlyCollection(Of String) = My.Computer.FileSystem. _

GetFiles(CStr(Dts.Variables("MainPath").Value))

If files.Count > 0 Then

Dim dic As Generic.SortedDictionary(Of Date, String)

For a As Integer = 1 To files.Count

If files(a) Like nm Then

Dim nfo As System.IO.FileInfo = My.Computer.FileSystem.GetFileInfo(files(a))

dic.Add(nfo.CreationTime, files(a))

End If

If dic.Count > 0 Then

Dim FinalFiles(dic.Count - 1) As String

Dim count As Integer

For Each kvp As Generic.KeyValuePair(Of Date, String) In dic

FinalFiles(count) = kvp.Value

End If

Dts.TaskResult = Dts.Results.Success

End Sub

The following exception gets thrown at "If files.Count > 0 Then"

"Index was out of range. Must be non-negative and less than the size of the collection.
Parameter name: index"

Why?

Thanks for your help

Has MainPath been passed in as a variable? It is scoped properly?|||

Yes it's been passed. You can insert "msgbox files.count" before "if files.count > 0" and get a count just fine. I don't understand it.

|||There are a number of problems here (easily fixed though)

1 . Fence post condition. The ReadOnlyCollection index starts at 0, not 1.
Change

For a As Integer = 1 To files.Count

For a As Integer = 0 To files.Count -1

2. Unitialized Variable:

Change

Dim dic As Generic.SortedDictionary(Of Date, String)

Dim dic As Generic.SortedDictionary(Of Date, String) = New Generic.SortedDictionary(Of Date, String)

|||Ah - I didn't catch the uninitialized part - that should fix it - thanks.

Script Task and Variable Type

Hi,

I am using the following code in Script Task and it is giving me the error as shown below

FileCount is a variable defined as Int16 (with initial value = 0 ) and it is part of Read/Write variables portion of Script

Dim FileCount As Int16

FileCount = CType(Dts.Variables("FileCount").Value, Int16)

Dts.Variables("FileCount").Value = FileCount + 1 //Error on this Line

The type of value being assigned to @.User::FileCount differs from the current variable type. Variables may not change during execution.

Please Guide what is wrong with this.

The result produced from "FileCount + 1" is of type Integer, you are trying to assign it to type Short. Change the line to the following and it should work:

Dts.Variables("FileCount").Value = CShort(FileCount + 1)

Tuesday, March 20, 2012

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 problem with POWER(x,y)

hi,

i can't see why the following field is not working correctly, ie: it's always coming up with 0:

((POWER((1+((((((POWER((1+(dbo_table1.APR/100)),(1/12)))-1))*12))/12)), dbo_table1.FREQUENCY)-1)*(12/dbo_table1.FREQUENCY))*100 AS YIELD

breaking the code down to a smaller formula it does work.

it looks like i can't have more calculations/references inside a POWER(x,y) function.

any comments would be appreciated.

thank you

The problem is integer math. This formula can never return anything other than zero because (1/12) = 0. Guessing at datatypes:

declare @.apr decimal (4,2)
declare @.frequency decimal(3,0)

set @.apr = 6.5
set @.frequency = 24

select ((POWER((1+((((((POWER((1+(@.apr/100)),(1.0/12.0)))-1))*12))/12)), @.frequency)-1)*(12/@.frequency))*100

This now returns:

6.711700

Hope this helps.

|||

thank you very much.

it has solved the issue

regards

Script needed to edit open objects in Sybase

Currently I must manually complete the following commands to set a new value
for the "open objects" and "open indexes". I need a way script this process
in a batch file.

COMMANDS RAN: This is nothing to script, these commands obtain the current
values for open objects and open indexes.

isql -Usa -Pmanager -SCOP1_DS
execute sp_countmetadata "open objects"
execute sp_countmetadata "open indexes"
go

RESULTS: below are the results, there are 5124 user objects and 2122 user
indexes. I need a way to be able to multiple these numbers by a set value
and then use the new value and reset the user objects and user indexes. The
question is how do I isolate the results for each result so I can run the
calculations below. Here is the calculation I need:

current user objects * 1.05 = new value
current user indexes * 1.1 = new value

There are 5124 user objects in all database(s), requiring 6985 Kbytes of
memory.

The 'open objects' configuration parameter is currently set to 500.
(return status = 0)
There are 2122 user indexes in all database(s), requiring 5902 Kbytes of
memory.

The 'open indexes' configuration parameter is currently set to 5000.
(return status = 0)

SETTING THE NEW VALUES: Once I have the new values, I can set the ojects and
indexes as seen below.

1> execute sp_configure "open objects", 5380
2> execute sp_configure "open indexes", 2334
3> goAre you perhaps running Sybase? In that case, you'll probably get a more
help by asking this question in a Sybase forum (e.g. comp.databases.sybase)
rather than one dedicated to Microsoft SQL Server. We Microsoft SQL Server
DBAs are spoiled and don't need to deal with those config options anymore
:-)

--
Hope this helps.

Dan Guzman
SQL Server MVP

"News East" <Methu969@.yahoo.com> wrote in message
news:bY7Lf.171258$WH.51367@.dukeread01...
> Currently I must manually complete the following commands to set a new
> value for the "open objects" and "open indexes". I need a way script this
> process in a batch file.
> COMMANDS RAN: This is nothing to script, these commands obtain the current
> values for open objects and open indexes.
>
> isql -Usa -Pmanager -SCOP1_DS
> execute sp_countmetadata "open objects"
> execute sp_countmetadata "open indexes"
> go
>
>
> RESULTS: below are the results, there are 5124 user objects and 2122 user
> indexes. I need a way to be able to multiple these numbers by a set value
> and then use the new value and reset the user objects and user indexes.
> The question is how do I isolate the results for each result so I can run
> the calculations below. Here is the calculation I need:
> current user objects * 1.05 = new value
> current user indexes * 1.1 = new value
>
> There are 5124 user objects in all database(s), requiring 6985 Kbytes of
> memory.
> The 'open objects' configuration parameter is currently set to 500.
> (return status = 0)
> There are 2122 user indexes in all database(s), requiring 5902 Kbytes of
> memory.
> The 'open indexes' configuration parameter is currently set to 5000.
> (return status = 0)
>
> SETTING THE NEW VALUES: Once I have the new values, I can set the ojects
> and indexes as seen below.
>
> 1> execute sp_configure "open objects", 5380
> 2> execute sp_configure "open indexes", 2334
> 3> go|||News East (Methu969@.yahoo.com) writes:
> Currently I must manually complete the following commands to set a new
> value for the "open objects" and "open indexes". I need a way script
> this process in a batch file.

I guess that you should be able to read these from syscurconfigs or
sysconfigure. Provided that is, that Sybase has not changed the
location for the configuration parameters since last I saw it, and
that was loooong ago.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Friday, March 9, 2012

Script Component Task: function that returns last word in a string

I have a field called CustomerName Varchar 100 and I wish to write a function that can do the following in a script component task

create a function called CleanString (ByVal CustomerName as String) As String

CleanString Returns the last word of a Customer name if the CustomerName field contains more than one word or if the CustomerName field does not contain Corp or Ltd

ie parse 'Mr John Tools' and the function returns 'Tools'

ie parse 'TechnicalBooks' and the function returns 'TechnicalBooks'

ie parse 'Microsoft Corp' return 'Microsoft Corp'

ie parse 'Digidesign Ltd' return 'Digidesign Ltd'

Any idea of a regular expression or existing piece of existing code I can have

thanks in advance

dave

I believe you'd want something along the lines of:

\s*(\w+(\s+(Corp|Ltd))?)$

Public Function CleanString(ByVal CustomerName As String) As String

Dim re As New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")
Dim m As Match
m = re.Match(CustomerName)
' insert sanity to make sure we had a match
' group 0 would be the full string
' group 1 is the one we want
Dim g As Group = m.Groups(1)
Dim cc As CaptureCollection = g.Captures
CleanString = cc(0).Value

End Function

|||

Matt,

Thanks the code almost worked--I had to rewrite it like this. However I need help to tweak the regular expression Regex("\s*(\w+(\s+(Corp|Ltd))?)$") Im getting the following results In/Out

Input: Digidesign Australasia Pty Ltd Output: Pty Ltd --wrong should be Ltd

Input: Ms S Aiten Output: Aiten --correct

Function CleanString(ByVal CustomerName As String) As String

m = re.Match(CustomerName)

' group 0 would be the full string

' group 1 is the one we want

CleanString = m.Groups(1).Value

End Function

'Called by

Public Class ScriptMain

Inherits UserComponent

Dim re As Regex = New Regex("\s*(\w+(\s+(Corp|Ltd))?)$")

Dim m As Match

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

If Len(Row.CUSTOMERNAME) > 0 Then

Output0Buffer.oCUSTOMERNAME = Row.CUSTOMERNAME.ToString

Output0Buffer.oSearchName = CleanString(Row.CUSTOMERNAME.ToString)

End If

|||I don't think you can easily do this with a single regex. You might want to pre-process the string to remove the Pty first, or have a special case that captures the words you want to the left and right of "Pty" whenever the string appears in your customer name.

Saturday, February 25, 2012

SCPTXFR.EXE-- Problem with Drop procedure

hi there
I am using SCPTXFR.EXE to generate script of the database(for backup),using following command.

SCPTXFR.EXE /s abc /d aaa /P abc12345 /f D:\HSE0607\SCHEMA.sql /q/r/T

Now the script is generating fine, but what i find that there is no statment like
"if not exists(...) "
for the Stored Procedure( i had applied the /r parameter), for the Create Tables it is there.

Why there is no statment IF NOT EXISTS(...) for Stored Procedure?
I need this Line, how can i do this?
Regards,
Thanks.
Gurpreet S. Gill

hey nobody help me here, but i find the solution at

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73290

Tuesday, February 21, 2012

SCOPE_IDENTITY()

Hello altogether, my problem ist that I get following error message:

Create Stored Procedure
Unable to chances to the stored procedure.
Error Details:
'Scope_Identity' is not a recognized function name.

This is my Stored Procedure:

CREATE PROCEDURE sp_HyperSoftCustomer
@.Name varchar(25),
@.Adress varchar(250)
as
insert into HyperSoftCustomer(Name, Adress, Date)
values (@.Name, @.Adress, GetDate())
Select SCOPE_IDENTITY()
GO

I am using MSDE - MSSQLServer

I hope there is anybody who can help me?

Thanks, mexx

Hmmmm, that works fine for me. Are you using MSDE 2000?|||

Hi, I am working with

SQL Distributed Management Framework (SQL-DMF)
Microsoft Server Service Manager
Version 7.00.623

My System is Windows Home Edition and I simulate the Internet Information Services IIS (from Windows Professionel Edition) with the Web Matrix Webserver.

Is something missed? Is there a problem of the configuration?

Regards, mexx

|||

mexx:
Hi, I am working with
SQL Distributed Management Framework (SQL-DMF)
Microsoft Server Service Manager
Version 7.00.623

Accoring toHow to identify your SQL Server version, you are running a version of SQL Server 7. MSDE 1.0 runs on the SQL Server 7 engine. SCOPE_IDENTITY was not available in this version; use @.@.IDENTITY instead.

I suggest you install MSDE 2000, which uses the SQL Server 2000 engine. You can get it from here: Installing MSDE for ASP.NET Web Matrix.

scope_identity Vs Parameters

Sorry for double posting but I screwed the last one up. The following code
successfully inserts a record in the Sections table, but the
scope_identity() returns DbNull. If I remove the parameter 'pSectionName'
and replace it with dummy value, it works fine. Alternatively, if I use
@.@.IDENTITY with or withour the parameter, that works fine too.
It seems there is a problem with using scope_identity() when parameters are
involved, but I do need to use parameters. Does anyone know why this would
happen and how to circumvent it ?
Dim sqlConnection As New SqlConnection(getConnectionString())
Dim sqlString As String
Dim result As Integer
Dim pSectionName As New SqlParameter("@.pSectionName",
SqlDbType.NVarChar)
pSectionName.Value = sectionRow.SectionName
sqlString = "INSERT INTO SECTIONS " & _
"VALUES (" & _
" '" & sectionRow.ArticleID.ToString & "'," & _
" @.pSectionName ," & _
" '" & sectionRow.SectionNumber.ToString & "'," & _
" '" & sectionRow.SectionFollowing.ToString & "'," & _
" '" & sectionRow.Attachments.ToString & "'," & _
" '" & sectionRow._Text & "'," & _
" ''," & _
" '" & sectionRow.pictureName & "'," & _
" '" & sectionRow.pictureType & "'," & _
" '" & sectionRow.pictureFilePath & "'," & _
" '" & sectionRow.SectionType & "');"
Dim sqlIDQuery As String
sqlIDQuery = "SELECT scope_identity();"
Dim sqlCommand As New SqlCommand(sqlString)
sqlCommand.Connection = sqlConnection
'Add Parameters
sqlCommand.Parameters.Add(pSectionName)
Dim SectionID As Integer
Try
sqlConnection.Open()
sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
INSERTS RECORD.
sqlCommand.CommandText = sqlIDQuery
SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
FAILS HERE WITH AN EXCEPTION.
Catch ex As Exception
SectionID = 0
Finally
sqlConnection.Close()
End Try
Return SectionID
Best Regards
The Inimitable Mr NewbieHave you considered using a relational design, which will never have
IDENTITY in its tables? These exposed physical locators have nothign to
do with RDBMS or a valid logical model.
Have you considered using a stored procedure inside the database instead
of building a query on the fly at run time? Why do you think that a row
and record are anything alike?
A spec that tells us what you are trying to do, long with some DDL would
be very helpful. Do you program from things this vague and imcomplete?
Us, neither.
--CELKO--
Please post DDL in a human-readable format and not a machine-generated
one. This way people do not have to guess what the keys, constraints,
DRI, datatypes, etc. in your schema are. Sample data is also a good
idea, along with clear specifications.
*** Sent via Developersdex http://www.examnotes.net ***|||If you have nothing useful to say, just save it for someone who might care
that you try and make yourself feel good at the expense of others.
Especially those who are new like me.
Best Regards
The Inimitable Mr Newbie
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||I see you are reusing the same SqlCommand object but I don't see where you
are clearing the parameters collection from the first statement.
Consequently, the command will still contain the unneeded @.pSectionName
parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
sqlCommand.Parameters.Clear()
sqlCommand.CommandText = sqlIDQuery
Also, consider parameterizing the entire INSERT statement or executing a
proc passing parameters. Parameterized values are more secure and avoid the
need to worry about things like embedded quotes in strings and date formats.
Hope this helps.
Dan Guzman
SQL Server MVP
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
>
> Dim sqlConnection As New SqlConnection(getConnectionString())
> Dim sqlString As String
> Dim result As Integer
> Dim pSectionName As New SqlParameter("@.pSectionName",
> SqlDbType.NVarChar)
> pSectionName.Value = sectionRow.SectionName
> sqlString = "INSERT INTO SECTIONS " & _
> "VALUES (" & _
> " '" & sectionRow.ArticleID.ToString & "'," & _
> " @.pSectionName ," & _
> " '" & sectionRow.SectionNumber.ToString & "'," & _
> " '" & sectionRow.SectionFollowing.ToString & "'," & _
> " '" & sectionRow.Attachments.ToString & "'," & _
> " '" & sectionRow._Text & "'," & _
> " ''," & _
> " '" & sectionRow.pictureName & "'," & _
> " '" & sectionRow.pictureType & "'," & _
> " '" & sectionRow.pictureFilePath & "'," & _
> " '" & sectionRow.SectionType & "');"
> Dim sqlIDQuery As String
> sqlIDQuery = "SELECT scope_identity();"
> Dim sqlCommand As New SqlCommand(sqlString)
> sqlCommand.Connection = sqlConnection
> 'Add Parameters
> sqlCommand.Parameters.Add(pSectionName)
> Dim SectionID As Integer
> Try
> sqlConnection.Open()
> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
> INSERTS RECORD.
> sqlCommand.CommandText = sqlIDQuery
> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
> FAILS HERE WITH AN EXCEPTION.
> Catch ex As Exception
> SectionID = 0
> Finally
> sqlConnection.Close()
> End Try
> Return SectionID
>
> --
> Best Regards
> The Inimitable Mr Newbie
>|||Thanks for your reply.
I tried clearing the Params, but that had no effect. so I created an
entirely new sqlCommandID object which used the same connection as the other
one for the scope_identity() but this had no effect.
I know I should parameterise the other parts of the sql query, but they dont
really need it because the contain no user input. Only the section title
carries this on creation.
Any other ideas. This is really bugging me.
Best Regards
The Inimitable Mr Newbie
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OyzuingGGHA.2040@.TK2MSFTNGP14.phx.gbl...
>I see you are reusing the same SqlCommand object but I don't see where you
>are clearing the parameters collection from the first statement.
>Consequently, the command will still contain the unneeded @.pSectionName
>parameter when you execute SELECT SCOPE_IDENTITY(). You might try:
> sqlCommand.Parameters.Clear()
> sqlCommand.CommandText = sqlIDQuery
> Also, consider parameterizing the entire INSERT statement or executing a
> proc passing parameters. Parameterized values are more secure and avoid
> the need to worry about things like embedded quotes in strings and date
> formats.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Mr Newbie" <here@.now.com> wrote in message
> news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
>|||> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
Have you consider going one step further than the 'logical model' and create
an implementation or do you just deal with theory?
The IDENTITY property is a very useful way to create surrogate keys and gain
good performance and is a solution for the really big problem of when a
primary key value changes.

> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
How do you think report builders work? Do you have a stored procedure with
1000 parameters and 1000 if else.
Actually, let me bring you up on this point, you posted a w

or two ago
that you shouldn't use IF ELSE in the database.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <remove.jcelko212@.earthlink.net> wrote in message
news:OaEY7XgGGHA.2000@.TK2MSFTNGP15.phx.gbl...
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
> --CELKO--
> Please post DDL in a human-readable format and not a machine-generated
> one. This way people do not have to guess what the keys, constraints,
> DRI, datatypes, etc. in your schema are. Sample data is also a good
> idea, along with clear specifications.
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You need to run it as a single statement, it would be better if you used a
stored procedure if possible.
Anyway, the problem lies in that SCOPE_IDENTITY() is only for the
connection, your connection will have been reset between calls.
You can concatenate sqlString and sqlIDQuery just put a semi column ; after
the first statement.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Mr Newbie" <here@.now.com> wrote in message
news:uqob9mfGGHA.2012@.TK2MSFTNGP14.phx.gbl...
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
>
> Dim sqlConnection As New SqlConnection(getConnectionString())
> Dim sqlString As String
> Dim result As Integer
> Dim pSectionName As New SqlParameter("@.pSectionName",
> SqlDbType.NVarChar)
> pSectionName.Value = sectionRow.SectionName
> sqlString = "INSERT INTO SECTIONS " & _
> "VALUES (" & _
> " '" & sectionRow.ArticleID.ToString & "'," & _
> " @.pSectionName ," & _
> " '" & sectionRow.SectionNumber.ToString & "'," & _
> " '" & sectionRow.SectionFollowing.ToString & "'," & _
> " '" & sectionRow.Attachments.ToString & "'," & _
> " '" & sectionRow._Text & "'," & _
> " ''," & _
> " '" & sectionRow.pictureName & "'," & _
> " '" & sectionRow.pictureType & "'," & _
> " '" & sectionRow.pictureFilePath & "'," & _
> " '" & sectionRow.SectionType & "');"
> Dim sqlIDQuery As String
> sqlIDQuery = "SELECT scope_identity();"
> Dim sqlCommand As New SqlCommand(sqlString)
> sqlCommand.Connection = sqlConnection
> 'Add Parameters
> sqlCommand.Parameters.Add(pSectionName)
> Dim SectionID As Integer
> Try
> sqlConnection.Open()
> sqlCommand.ExecuteNonQuery() '***** THIS WORKS FINE AND
> INSERTS RECORD.
> sqlCommand.CommandText = sqlIDQuery
> SectionID = CType(sqlCommand.ExecuteScalar, Integer) ' ****
> FAILS HERE WITH AN EXCEPTION.
> Catch ex As Exception
> SectionID = 0
> Finally
> sqlConnection.Close()
> End Try
> Return SectionID
>
> --
> Best Regards
> The Inimitable Mr Newbie
>|||--CELKO-- (remove.jcelko212@.earthlink.net) writes:
> Have you considered using a relational design, which will never have
> IDENTITY in its tables? These exposed physical locators have nothign to
> do with RDBMS or a valid logical model.
> Have you considered using a stored procedure inside the database instead
> of building a query on the fly at run time? Why do you think that a row
> and record are anything alike?
> A spec that tells us what you are trying to do, long with some DDL would
> be very helpful. Do you program from things this vague and imcomplete?
> Us, neither.
>
No, there is no spec needed. Instead of preteding like you are a bad
AI program let loose, learn how ADO .Net works and you will be answer
the question without any further spec.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Mr Newbie (here@.now.com) writes:
> Sorry for double posting but I screwed the last one up. The following
> code successfully inserts a record in the Sections table, but the
> scope_identity() returns DbNull. If I remove the parameter
> 'pSectionName' and replace it with dummy value, it works fine.
> Alternatively, if I use @.@.IDENTITY with or withour the parameter, that
> works fine too.
> It seems there is a problem with using scope_identity() when parameters
> are involved, but I do need to use parameters. Does anyone know why this
> would happen and how to circumvent it ?
scope_idenity() returns the most recently generated identity value
in the current scope. Scope here is a stored procedure, or the top-
level scope. @.@.identity, on the other hand, returns the most recently
generated identity value for the connection, independent on scope.
When you don't use parameters, the INSERT command is submitted as-is,
and thus in the same scope as you later fetch scope_identity().
But when you use parameters, SqlClient submits the command through
sp_executesql. This is because SqlClient does not build the expanded
command string, but instead passes the parameters in an RPC call.
This is usually good for performance. The side effect is that the
INSERT statement no longer is in the top-level scope, and thus you
can get the identity value with scope_identity().
There are two possible ways to do:
o Use @.@.identity. This is fine as long as the table you are inserting
to does not have a trigger which in its turn insert into a table
with an identity column. In this case, @.@.identity will report the
value generated for that table. (It is to avoid this trap, that
scope_identity() was introduced.)
o As Tony suggested, add the SELECT on scope_identity() to the
batch with the INSERT statement.
The latter has the advantage of saving you a network roundtrip, which
is usally good for performance.
In fact, I would like to take you even a step further and do this:
sqlString = "INSERT INTO SECTIONS " & _
"(ArticleID, SectionName, SectionNumber, ... ) " & _
"VALUES (@.ArticleID, @.pSectionName, @.SectionNumber, ...) " & _
"SELECT @.id = scope_identity()"
1) Include the columns you are inserting into. If you leave out the column
list, and the table is later changed, you query will blow up.
2) Pass all values as parameters, don't embed values in the SQL String
(constants are OK). This protects you against SQL injection, and
other problems that could occur if the value includes an '. It also
saves you from hassle when using datetime values.
3) Make the value from scope_identity() an output parameter from
the batch. For this you need to specify the direction as InputOuput.
(SQL Server does not have any output-only parameters.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||>> If you have nothing useful to say, just save it for someone who might car
e that you try and make yourself feel good at the expense of others. Especia
lly those who are new like me. <<
So when I tell you think about stored procedures, it is a bad thing.
But when other people mention stored procedures as an answer, it is
good thing. Interesting.

Monday, March 26, 2012

script to kill long running ad hoc queries

script to kill long running ad hoc queries

script to kill long running ad hoc queries

Script to find out the number of row in all the table of a database.

Script to find out the number of row in all the table of a database.

Friday, March 23, 2012

Script to convert char to int please help....

Script to convert char to int please help....

Wednesday, March 21, 2012

Script task: Dts.Logging.Log

Script Task Error?

Script Task and Variable Type

Tuesday, March 20, 2012

Script runs fine in QA, not as a job

Script runs fine in QA, not as a job

script problem with POWER(x,y)

Script needed to edit open objects in Sybase

Friday, March 9, 2012

Script Component Task: function that returns last word in a string

Saturday, February 25, 2012

SCPTXFR.EXE-- Problem with Drop procedure

Tuesday, February 21, 2012

SCOPE_IDENTITY()

scope_identity Vs Parameters

Scope parameter

Blog Archive

About Me