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