connexion, processes ...

From the system table sysprocesses, how to do in order to extract
the SQL command related to the processes ?
What are system views/tables, scripts used to monitor and obtain more information
about processes, connexions... ?Straight from the Holy Book (SQL books Online)
----------------------
DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM sysprocesses WHERE spid = 52
SELECT * FROM ::fn_get_sql(@.Handle)

----------------------|||Thanks for the reply, but the code is not working in my server (and mya be this is due to SP...). By the way, how to find the last SP applied to sql server ?

DBCC INPUTBUFFER(80)
EventType Parameters EventInfo
--- ---- ---------
RPC Event 0 DVSPAYS_CODELIB_DVSPRINC_S;1

DECLARE @.Handle binary(20)
SELECT @.Handle = sql_handle FROM sysprocesses WHERE spid = 80
SELECT * FROM ::fn_get_sql(@.Handle)
dbid objectid number encrypted text
-- ---- -- --- ----------------------------------------------------------------------------
No row(s)sqlsql

Connection.Command.Text length

Is there a way to set the length of my command text? I have a very large
script I am trying to pass through the adodb.connection object, and it keeps
getting truncated.Hi
How large?
Regards
Mike
"Mike Morella" wrote:

> Is there a way to set the length of my command text? I have a very large
> script I am trying to pass through the adodb.connection object, and it kee
ps
> getting truncated.|||Here is the script. It is 248 lines long.
declare @.cmd nvarchar(1024)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmplg]'))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(10),
LogSize real,
LogSpaceUsed real,
Status int
)
SELECT @.cmd = 'dbcc sqlperf (logspace)'
INSERT INTO #Tmplg EXECUTE (@.cmd)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_stats]'))
drop table #tmp_stats
create table #tmp_stats (
totalextents int,
usedextents int,
dbname varchar(10),
logsize real,
logspaceused real
)
go
use [aw]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'aw'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'aw'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'aw'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [css]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'css'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'css'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'css'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [master]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'master'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'master'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'master'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [model]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'model'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'model'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'model'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [msdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'msdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'msdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'msdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [rms]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'rms'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'rms'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'rms'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [sbb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'sbb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'sbb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'sbb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [tempdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'tempdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'tempdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsiz
e,logspaceused)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'tempdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
SELECT dbname,totalextents*64/1024 as dbSize, usedextents*64/1024 as
dbSpaceUsed ,logsize ,logSpaceUsed from #tmp_stats
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> How large?
> Regards
> Mike
> "Mike Morella" wrote:
>|||Mike Morella (MikeMorella@.discussions.microsoft.com) writes:
> Here is the script. It is 248 lines long.
You pass all that in one call to cmd.Execute? Truncation or not, you
will get hoops of syntax errors, like here:

> create table #tmp_stats (
> totalextents int,
> usedextents int,
> dbname varchar(10),
> logsize real,
> logspaceused real
> )
> go
You can't have an identifier directly after a CREATE TABLE commmand, and
you have plenty of these "go".
Yes, I know what you are thinking, but GO is not an SQL command. As far
as SQL command that "go" is just another identifier. Lots of client tools
uses "go" on a separate line as a batch terminator. So could you, but you
need to break the input in batches on the "go" before you send it to SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Connection.Command.Text length

Is there a way to set the length of my command text? I have a very large
script I am trying to pass through the adodb.connection object, and it keeps
getting truncated.
Hi
How large?
Regards
Mike
"Mike Morella" wrote:

> Is there a way to set the length of my command text? I have a very large
> script I am trying to pass through the adodb.connection object, and it keeps
> getting truncated.
|||Here is the script. It is 248 lines long.
declare @.cmd nvarchar(1024)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmplg]'))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(10),
LogSize real,
LogSpaceUsed real,
Status int
)
SELECT @.cmd = 'dbcc sqlperf (logspace)'
INSERT INTO #Tmplg EXECUTE (@.cmd)
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_stats]'))
drop table #tmp_stats
create table #tmp_stats (
totalextents int,
usedextents int,
dbname varchar(10),
logsize real,
logspaceused real
)
go
use [aw]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'aw'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'aw'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'aw'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [css]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'css'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'css'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'css'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [master]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'master'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'master'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'master'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [model]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'model'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'model'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'model'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [msdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'msdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'msdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'msdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [rms]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'rms'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'rms'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'rms'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [sbb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'sbb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'sbb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'sbb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
use [tempdb]
go
if exists (select * from tempdb..sysobjects where id =
object_id(N'[tempdb]..[#tmp_sfs]'))
drop table #tmp_sfs
create table #tmp_sfs (
fileid int,
filegroup int,
totalextents int,
usedextents int,
name varchar(10),
filename varchar(1024)
)
go
declare @.cmd nvarchar(1024)
set @.cmd='DBCC SHOWFILESTATS'
insert into #tmp_sfs execute(@.cmd)
declare @.logsize real
declare @.logspaceused real
select @.logsize= logsize from #tmplg where dbname = 'tempdb'
select @.logspaceused = (logsize*logspaceused)/100.0
from #tmplg where dbname = 'tempdb'
set @.cmd = 'insert into #tmp_stats' +
'(totalextents,usedextents,dbname,logsize,logspace used)' +
' select sum(totalextents), sum(usedextents),' + char(39) + 'tempdb'+
char(39) + ',' +
cast(@.logsize as varchar) + ',' +
cast(@.logspaceused as varchar) +
' from #tmp_sfs'
exec sp_executesql @.cmd
SELECT dbname,totalextents*64/1024 as dbSize, usedextents*64/1024 as
dbSpaceUsed ,logsize ,logSpaceUsed from #tmp_stats
"Mike Epprecht (SQL MVP)" wrote:
[vbcol=seagreen]
> Hi
> How large?
> Regards
> Mike
> "Mike Morella" wrote:
|||Mike Morella (MikeMorella@.discussions.microsoft.com) writes:
> Here is the script. It is 248 lines long.
You pass all that in one call to cmd.Execute? Truncation or not, you
will get hoops of syntax errors, like here:

> create table #tmp_stats (
> totalextents int,
> usedextents int,
> dbname varchar(10),
> logsize real,
> logspaceused real
> )
> go
You can't have an identifier directly after a CREATE TABLE commmand, and
you have plenty of these "go".
Yes, I know what you are thinking, but GO is not an SQL command. As far
as SQL command that "go" is just another identifier. Lots of client tools
uses "go" on a separate line as a batch terminator. So could you, but you
need to break the input in batches on the "go" before you send it to SQL
Server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp