ConnectionTimeout

I'm creating connection object to SQL Server db this way
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
Vlad
objConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...

> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?
|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...

> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
"PROVIDER=MSDASQL;dsn=Coordinator;uid=vladc;pwd=de veloper4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?

ConnectionTimeout

I'm creating connection object to SQL Server db this way
Set objConn = New ADODB.Connection
objConn.CursorLocation = adUseClient
objConn.ConnectionString =
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
objConn.ConnectionTimeout = 1
objConn.Open
It doesn't matter what value I'm setting to the ConnectionTimeout property.
If server is unavailable then it produces an error only after approximately
30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds. If
I comment assignment line in my code, it's still about 30 seconds.
So, for me ConnectionTimeout property doesn't work.
What am I doing wrong?
Thank you
VladobjConn.ConnectionTimeout = 30
Vctor Koch From Argentina.
"Vlad" <vovan.c@.verizon.net> escribi en el mensaje
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...
> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
> It doesn't matter what value I'm setting to the ConnectionTimeout
property.
> If server is unavailable then it produces an error only after
approximately
> 30 seconds. I read in MSDN than default ConnectionTimeout is 15 seconds.
If
> I comment assignment line in my code, it's still about 30 seconds.
> So, for me ConnectionTimeout property doesn't work.
> What am I doing wrong?
> Thank you
> Vlad
>|||"Vlad" <vovan.c@.verizon.net> wrote in message
news:O$JZcAuWEHA.3664@.TK2MSFTNGP12.phx.gbl...

> I'm creating connection object to SQL Server db this way
> Set objConn = New ADODB.Connection
> objConn.CursorLocation = adUseClient
> objConn.ConnectionString =
>
" PROVIDER=MSDASQL;dsn=Coordinator;uid=vla
dc;pwd=developer4444;database=SRS;"
> objConn.ConnectionTimeout = 1
> objConn.Open
Why use ODBC to connect to SQL Server? Why not use the native SQL OLE DB
provider?

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

Connection Problems

Each time a user tries to connect to an application, they
keep getting the following error:
Unable to create ADODB Connection - [DBNETLIB]
[ConnectionOpen (connectionOpen()).]SQL Server does not
exist or access denied.
This has been happening for the last three days and each
time this happens the server has to be rebooted before
users can connect to the database. The application uses
windows authentication. The database is SQL Server 2000
SP3a.Can you use the same accounts to connect to sql server ? Anything you see
in the Application and system event logs? Try connecting locally (on the
same box) and checking.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||Thanks Vikram,
I have solved the problem.
>--Original Message--
>Can you use the same accounts to connect to sql server ?
Anything you see
>in the Application and system event logs? Try connecting
locally (on the
>same box) and checking.
>Thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>

Connection Problems

Each time a user tries to connect to an application, they
keep getting the following error:
Unable to create ADODB Connection - [DBNETLIB]
[ConnectionOpen (connectionOpen()).]SQL Server does not
exist or access denied.
This has been happening for the last three days and each
time this happens the server has to be rebooted before
users can connect to the database. The application uses
windows authentication. The database is SQL Server 2000
SP3a.
Can you use the same accounts to connect to sql server ? Anything you see
in the Application and system event logs? Try connecting locally (on the
same box) and checking.
Thanks,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
|||Thanks Vikram,
I have solved the problem.
>--Original Message--
>Can you use the same accounts to connect to sql server ?
Anything you see
>in the Application and system event logs? Try connecting
locally (on the
>same box) and checking.
>Thanks,
>Vikram Jayaram
>Microsoft, SQL Server
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Subscribe to MSDN & use
http://msdn.microsoft.com/newsgroups.
>
>.
>