DSN or DSNless connection?

I'm starting to move my VB6 application from Access to SQL Server. In the
beginning I'm going to use Access database with tables linked to SQL Server
tables. Then form by form are going to be moved to direct SQL Server
connection. When all of them are moved I delete Access. I have very weak
experience with SQL Server, so I even do not know advantages and
disadvantages of DSN and DSN less connections. I know that I have to learn
everything and I'm going to do that. But for the beginning could somebody
give me some suggestions regarding connection. What is better ? DSN or DSN
less?
Thank you
Vladhttp://groups.google.com/groups?q=dsn+vs+dsn-less
-oj
http://www.rac4sql.net
"Vlad" <vovan.c@.verizon.net> wrote in message
news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
> I'm starting to move my VB6 application from Access to SQL Server. In the
> beginning I'm going to use Access database with tables linked to SQL
Server
> tables. Then form by form are going to be moved to direct SQL Server
> connection. When all of them are moved I delete Access. I have very weak
> experience with SQL Server, so I even do not know advantages and
> disadvantages of DSN and DSN less connections. I know that I have to learn
> everything and I'm going to do that. But for the beginning could somebody
> give me some suggestions regarding connection. What is better ? DSN or DSN
> less?
> Thank you
> Vlad
>|||Thank you very much
Vlad
"oj" <nospam_ojngo@.home.com> wrote in message
news:OYlwjdlAEHA.2448@.TK2MSFTNGP12.phx.gbl...
> http://groups.google.com/groups?q=dsn+vs+dsn-less
> --
> -oj
> http://www.rac4sql.net
>
> "Vlad" <vovan.c@.verizon.net> wrote in message
> news:O5fGXDjAEHA.2180@.TK2MSFTNGP09.phx.gbl...
the
> Server
learn
somebody
DSN
>

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a database. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly appreciated.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LDF'"
Thanks,
Ram
Ram
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/************************************************** *************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
************************************************** **************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>
|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
>)
>AS
>BEGIN
>/************************************************** *******
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
>************************************************* *********
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>
|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
>news:15e7101c416a4$9ad58a70$a501280a@.phx.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drop all the connections to the DataBase

Hi ,
I am using the following code in a batch file to take the back up of a datab
ase. but some times it fails with the message
"Cannot access the database becuase it is being used by another process." Is
there any way i can force all the connections
to the database to be dropped using code. Any help will be greatly apprecia
ted.
isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname = 'JMS
', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 = 'C:\JJMSdb\JMS_Log.LD
F'"
Thanks,
RamRam
This procedure written by Narayana Vyas Kondreddi
CREATE PROC sp_dboption2
(
@.dbname sysname = NULL, --Database name
@.optname varchar(35) = NULL, --Option name
@.optvalue varchar(5) = NULL, --Option value, either 'true' or 'false'
@.wait int = NULL --Seconds to wait, before killing the existing
connections
)
AS
BEGIN
/ ****************************************
***********************************
********************************
Copyright 2001 Narayana Vyas Kondreddi. All rights reserved.
Purpose: The system stored procedure sp_dboption fails to set databases in
'read only'/'single user'/'offline'
modes if the database is in use. This procedure works as a wrapper around
sp_dboption and overcomes that
limitation by killing all the active connections. You can configure it to
kill the connections immediately,
or after waiting for a specified interval. This procedure simulates the
new ALTER TABLE syntax of SQL Server
2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options along with
OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
SINGLE_USER, RESTRICTED_USER, MULTI_USER).
Written by: Narayana Vyas Kondreddi
http://vyaskn.tripod.com
Tested on: SQL Server 7.0, Service Pack 3
Date created: October-29-2001 1:30 AM Indian Standard Time
Date modified: October-29-2001 1:30 AM Indian Standard Time
Email: vyaskn@.hotmail.com
Usage: Just run this complete script in the master database to create this
stored procedure. As far as syntax is
concerned, this procedure works very similar to the system stored
procedure sp_dboption. It has an additional
parameter @.wait, which can be used, to wait for a specified number of
seconds, before killing the connections.
The settable database option names need to be specified in full. For
example, the option name 'single' is
considered invalid and 'single user' is considered valid.
To bring pubs database into single user mode:
EXEC sp_dboption2 'pubs', 'single user', 'true'
To bring pubs database into single user mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'single user', 'true', 30
To bring pubs database into read/write mode:
EXEC sp_dboption2 'pubs', 'read only', 'false'
To bring pubs database into read/write mode. Wait for 30 seconds, for
current connections to leave and
start killing the connections after 30 seconds:
EXEC sp_dboption2 'pubs', 'read only', 'false', 30
****************************************
************************************
*******************************/
DECLARE @.dbid int, @.spid int, @.execstr varchar(15), @.waittime varchar(15),
@.final_chk int
--Only the following options require that, no other connections should
access the database
IF (LOWER(@.optname) IN ('offline', 'read only', 'single user')) AND
(LOWER(@.optvalue) IN('true', 'false'))
BEGIN
--Determining whether to wait, before killing the existing connections
IF @.wait > 0
BEGIN
SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s, @.wait, GETDATE()),
14))
WAITFOR TIME @.waittime --Wait the specified number of seconds
END
SET @.dbid = DB_ID(@.dbname) --Getting the database_id for the specified
database
--Get the lowest spid
TryAgain:
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
WHILE @.spid IS NOT NULL
BEGIN
IF @.spid <> @.@.SPID --To avoid the KILL attempt on own connection
BEGIN
SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
EXEC(@.execstr) --Killing the connection
END
--Get the spid higher than the last spid
SET @.spid = (SELECT MIN(spid) FROM master..sysprocesses WHERE dbid =
@.dbid AND spid > @.spid)
END
END
SET @.final_chk = (SELECT COUNT(spid) FROM master..sysprocesses WHERE dbid =
@.dbid)
IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() = @.dbname)
BEGIN
EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling sp_dboption to
complete the job
END
ELSE
BEGIN
GOTO TryAgain --New connections popped up, or killed connections aren't
cleaned up yet, so try killing them again
END
END
"Ram" <anonymous@.discussions.microsoft.com> wrote in message
news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
> Hi ,
> I am using the following code in a batch file to take the back up of a
database. but some times it fails with the message
> "Cannot access the database becuase it is being used by another process."
Is there any way i can force all the connections
> to the database to be dropped using code. Any help will be greatly
appreciated.
> isql -b -S localhost -E -U xyzuser -P xyz -Q "sp_detach_db 'JMS', 'true'"
> copy C:\JJMSdb\JMS_Data.MDF C:\JJMSdb\JMS_Data_2004-03-29_14-54-5933.MDF
> copy C:\JJMSdb\JMS_Log.LDF C:\JJMSdb\JMS_Log_2004-03-29_14-54-5933.LDF
> isql -b -S localhost -E -U xyzuser -P xyzpwd -Q "sp_attach_db @.dbname =
'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
'C:\JJMSdb\JMS_Log.LDF'"
> Thanks,
> Ram
>
>|||Why would you KILL connections instead of using the "ALTER=20
DATABASE dbname set SINGLE_USER with rollback immediate"=20
syntax that you mention? It is much more clean than=20
issuing a bunch of KILL commands. What if you KILLed a=20
process that was going to take a long time to die or just=20
hung? You could potentially corrupt your database using=20
KILL commands. KILL should be used very carefully. =20
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname =3D NULL, --Database name
> @.optname varchar(35) =3D NULL, --Option name
> @.optvalue varchar(5) =3D NULL, --Option value,=20
either 'true' or 'false'
> @.wait int =3D NULL --Seconds to wait, before killing=20
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright =A9 2001 Narayana Vyas Kondreddi. All rights=20
reserved.
>Purpose: The system stored procedure sp_dboption fails to=20
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works=20
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You=20
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This=20
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options=20
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard=20
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master=20
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the=20
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a=20
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified=20
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered=20
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for=20
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),=20
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other=20
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single=20
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the=20
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime =3D (SELECT CONVERT(varchar, DATEADD(s,=20
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of=20
seconds
> END
> SET @.dbid =3D DB_ID(@.dbname) --Getting the database_id=20
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own=20
connection
> BEGIN
> SET @.execstr =3D 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid =3D (SELECT MIN(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk =3D (SELECT COUNT(spid) FROM=20
master..sysprocesses WHERE dbid =3D
>@.dbid)
> IF (@.final_chk =3D 0) OR (@.final_chk =3D 1 AND DB_NAME() =3D=20
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling=20
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed=20
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in=20
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =3D
>'JMS', @.filename1 =3D 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =3D
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||Van
SQL Server 7.0
If you have an active users in your database you will not be able use 'SET
SINGLE USER' mode
Server: Msg 15089, Level 11, State 1, Procedure sp_dboption, Line 400
Cannot change the 'single user' option of a database while another user is
in the database.
"Van Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
Why would you KILL connections instead of using the "ALTER
DATABASE dbname set SINGLE_USER with rollback immediate"
syntax that you mention? It is much more clean than
issuing a bunch of KILL commands. What if you KILLed a
process that was going to take a long time to die or just
hung? You could potentially corrupt your database using
KILL commands. KILL should be used very carefully.
Van

>--Original Message--
>Ram
>This procedure written by Narayana Vyas Kondreddi
>CREATE PROC sp_dboption2
>(
> @.dbname sysname = NULL, --Database name
> @.optname varchar(35) = NULL, --Option name
> @.optvalue varchar(5) = NULL, --Option value,
either 'true' or 'false'
> @.wait int = NULL --Seconds to wait, before killing
the existing
>connections
> )
>AS
>BEGIN
>/ ****************************************
*****************
******************
>********************************
> Copyright 2001 Narayana Vyas Kondreddi. All rights
reserved.
>Purpose: The system stored procedure sp_dboption fails to
set databases in
>'read only'/'single user'/'offline'
> modes if the database is in use. This procedure works
as a wrapper around
>sp_dboption and overcomes that
> limitation by killing all the active connections. You
can configure it to
>kill the connections immediately,
> or after waiting for a specified interval. This
procedure simulates the
>new ALTER TABLE syntax of SQL Server
> 2000 (the ROLLBACK IMMEDIATE and ROLLBACK AFTER options
along with
>OFFLINE, ONLINE, READ_ONLY, READ_WRITE,
> SINGLE_USER, RESTRICTED_USER, MULTI_USER).
>Written by: Narayana Vyas Kondreddi
> http://vyaskn.tripod.com
>Tested on: SQL Server 7.0, Service Pack 3
>Date created: October-29-2001 1:30 AM Indian Standard Time
>Date modified: October-29-2001 1:30 AM Indian Standard
Time
>Email: vyaskn@.hotmail.com
>Usage: Just run this complete script in the master
database to create this
>stored procedure. As far as syntax is
> concerned, this procedure works very similar to the
system stored
>procedure sp_dboption. It has an additional
> parameter @.wait, which can be used, to wait for a
specified number of
>seconds, before killing the connections.
> The settable database option names need to be specified
in full. For
>example, the option name 'single' is
> considered invalid and 'single user' is considered
valid.
> To bring pubs database into single user mode:
> EXEC sp_dboption2 'pubs', 'single user', 'true'
> To bring pubs database into single user mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'single user', 'true', 30
> To bring pubs database into read/write mode:
> EXEC sp_dboption2 'pubs', 'read only', 'false'
> To bring pubs database into read/write mode. Wait for
30 seconds, for
>current connections to leave and
> start killing the connections after 30 seconds:
> EXEC sp_dboption2 'pubs', 'read only', 'false', 30
>
> ****************************************
******************
******************
>*******************************/
> DECLARE @.dbid int, @.spid int, @.execstr varchar(15),
@.waittime varchar(15),
>@.final_chk int
> --Only the following options require that, no other
connections should
>access the database
> IF (LOWER(@.optname) IN ('offline', 'read only', 'single
user')) AND
>(LOWER(@.optvalue) IN('true', 'false'))
> BEGIN
> --Determining whether to wait, before killing the
existing connections
> IF @.wait > 0
> BEGIN
> SET @.waittime = (SELECT CONVERT(varchar, DATEADD(s,
@.wait, GETDATE()),
>14))
> WAITFOR TIME @.waittime --Wait the specified number of
seconds
> END
> SET @.dbid = DB_ID(@.dbname) --Getting the database_id
for the specified
>database
> --Get the lowest spid
> TryAgain:
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> WHILE @.spid IS NOT NULL
> BEGIN
> IF @.spid <> @.@.SPID --To avoid the KILL attempt on own
connection
> BEGIN
> SET @.execstr = 'KILL ' + LTRIM(STR(@.spid))
> EXEC(@.execstr) --Killing the connection
> END
> --Get the spid higher than the last spid
> SET @.spid = (SELECT MIN(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid AND spid > @.spid)
> END
> END
> SET @.final_chk = (SELECT COUNT(spid) FROM
master..sysprocesses WHERE dbid =
>@.dbid)
> IF (@.final_chk = 0) OR (@.final_chk = 1 AND DB_NAME() =
@.dbname)
> BEGIN
> EXEC sp_dboption @.dbname, @.optname, @.optvalue --Calling
sp_dboption to
>complete the job
> END
> ELSE
> BEGIN
> GOTO TryAgain --New connections popped up, or killed
connections aren't
>cleaned up yet, so try killing them again
> END
>END
>
>
>"Ram" <anonymous@.discussions.microsoft.com> wrote in
message
>news:F0E88B1A-A105-4E24-9BEE-CEB5FC97F532@.microsoft.com...
the back up of a
>database. but some times it fails with the message
another process."
>Is there any way i can force all the connections
will be greatly
>appreciated.
Q "sp_detach_db 'JMS', 'true'"
29_14-54-5933.MDF
29_14-54-5933.LDF
Q "sp_attach_db @.dbname =
>'JMS', @.filename1 = 'C:\JJMSdb\JMS_Data.MDF', @.filename2 =
>'C:\JJMSdb\JMS_Log.LDF'"
>
>.
>|||True, SQL 7.0 doesn't support the "with rollback=20
immediate" syntax (or at least I don't think it does). =20
The person who posted this question didn't state what=20
version of SQL they are using. But even if it is version=20
7.0 and my suggestion that uses the "with rollback=20
immediate" doesn't work, it's still not a good idea to=20
have an SP with KILL commands that runs as a scheduled=20
process. KILL commands should be monitored and used=20
carefully to ensure data and database corruption do not=20
occur. It would be better to stop SQL server, start it=20
back up and then put it into single user mode for the=20
detach. This could be done with NET STOP and NET START=20
from the batch file.
A better solution to the whole thing may be to just use=20
the BACKUP DATABASE syntax and not worry about putting it=20
in single user mode or detaching.

>--Original Message--
>Van
>SQL Server 7.0
>If you have an active users in your database you will not=20
be able use 'SET
>SINGLE USER' mode
>Server: Msg 15089, Level 11, State 1, Procedure=20
sp_dboption, Line 400
>Cannot change the 'single user' option of a database=20
while another user is
>in the database.
>
>"Van Jones" <anonymous@.discussions.microsoft.com> wrote=20
in message
> news:15e7101c416a4$9ad58a70$a501280a@.phx
.gbl...
>Why would you KILL connections instead of using the "ALTER
>DATABASE dbname set SINGLE_USER with rollback immediate"
>syntax that you mention? It is much more clean than
>issuing a bunch of KILL commands. What if you KILLed a
>process that was going to take a long time to die or just
>hung? You could potentially corrupt your database using
>KILL commands. KILL should be used very carefully.
>Van
>
>either 'true' or 'false'
>the existing
*
>******************
>reserved.
>set databases in
>as a wrapper around
>can configure it to
>procedure simulates the
>along with
Time
>Time
>database to create this
>system stored
>specified number of
>in full. For
>valid.
>30 seconds, for
>30 seconds, for
*
>******************
>@.waittime varchar(15),
>connections should
>user')) AND
>existing connections
>@.wait, GETDATE()),
>seconds
>for the specified
>master..sysprocesses WHERE dbid =3D
>connection
>master..sysprocesses WHERE dbid =3D
>master..sysprocesses WHERE dbid =3D
>@.dbname)
>sp_dboption to
>connections aren't
>message
CEB5FC97F532@.microsoft.com...
>the back up of a
>another process."
>will be greatly
>Q "sp_detach_db 'JMS', 'true'"
>29_14-54-5933.MDF
>29_14-54-5933.LDF
>Q "sp_attach_db @.dbname =3D
=3D
>
>.
>

Drive capacity

Hi folks,
I need to find out Drive capacity and free space, I do not have access
to Windows. I just have sa access to the SQL Server. I used
xp_fixeddrives to find out the free space, but how do I know the
capacity of the drive.
Thanks in advance.
--
*** Sent via Developersdex http://www.examnotes.net ***Well, if you are using Windows Server 2003, you can use wmic for this.
SET NOCOUNT ON;
CREATE TABLE #drives
(
drive CHAR(2),
MBFree BIGINT
);
CREATE TABLE #scratch
(
lineitem NVARCHAR(2048)
);
INSERT #drives EXEC master..xp_fixeddrives;
-- might be a good idea to call wmic manually once first
-- to make sure it is installed and loaded
INSERT #scratch EXEC master..xp_cmdshell 'wmic volume list'
SELECT d.drive, s.Capacity, d.MBFree,
[%free] = CONVERT(DECIMAL(5,2), d.MBFree / s.Capacity * 100)
FROM #drives d INNER JOIN (
SELECT capacity = CONVERT(DECIMAL(10,2), CONVERT(BIGINT,
SUBSTRING(lineitem, 23, 13)) / 1024 / 1024.0),
drive = REPLACE(SUBSTRING(lineitem, 112, 2), ':', '')
FROM #scratch
WHERE SUBSTRING(lineitem, 125, 1) = '3'
) s ON d.drive = s.drive
ORDER BY 1;
DROP TABLE #drives, #scratch;
Else, I would use something outside of SQL Server. E.g. you can get this
information from scripting.filesystemobject in a VBS script, called through
wscript and scheduled through windows task scheduler, and stuff its output
into the database, instead of expecting the database to do it internally...
"Venkat" <nospam_venkat_asp@.yahoo.co.uk> wrote in message
news:OxTikU2FGHA.2912@.tk2msftngp13.phx.gbl...
> Hi folks,
> I need to find out Drive capacity and free space, I do not have access
> to Windows. I just have sa access to the SQL Server. I used
> xp_fixeddrives to find out the free space, but how do I know the
> capacity of the drive.
> Thanks in advance.
> --
> *** Sent via Developersdex http://www.examnotes.net ***|||Wow!!! This is what I was looking for. Thanks a lot Aaron. You are
great.
*** Sent via Developersdex http://www.examnotes.net ***

Drilldowns with SOAP rendering or SSRS 2005?

We are running SSRS with SQL Server 2000 and are working on developing an
ASP.NET application that would use SOAP web access to render the reports.
However, I was wondering how this works with linked or drill down reports,
which we are thinking will be an important part of our design.
Has anyone done this successfully? How does it work? Normally, the
reportingservice.render method is called, but I read about an issue that
occurs when a drill down report is encountered. Apparently the user is
redirected to the Report Manager, as there is no way to tell the Report
Server that the drill down destination should be mapped to the calling
application. I also read that there may be something better for this in SQL
Server 2005, but I wonder when we should expect that?
Can anyone shed some light on this issue?
Thanks,
nickpupwell i ve been using SSRS 200 with SQL Server 2000 n ASP.Net for my
reports n i had the same problem of handling linked n toggle reports
as far as linked reports r concerned those were settled by changing the
path of the linked report to my application's webpage that shows the
specified linked report with parameters in URL through JUMP TO URL
PROPERTY however i couldnt resolve the toggled report problem i tried
but couldnt succeed if u get the idea plz let me know as well|||I will - thanks.
"** Spirits **" wrote:
> well i ve been using SSRS 200 with SQL Server 2000 n ASP.Net for my
> reports n i had the same problem of handling linked n toggle reports
> as far as linked reports r concerned those were settled by changing the
> path of the linked report to my application's webpage that shows the
> specified linked report with parameters in URL through JUMP TO URL
> PROPERTY however i couldnt resolve the toggled report problem i tried
> but couldnt succeed if u get the idea plz let me know as well
>

Drill down using url access in ASP.net not working

I've been through a good bit of the message boards trying to find an
answer to this problem.
I have an application that uses an iframe to embed a MSRS report. I'm
using URL access to call the report.
The problem is that any drill downs in the report DO NOT actually
expand anything in the report. I've looked at the rc:ReplacementRoot
in some threads but I don't see any differences with that extra
parameter.
Has anyone gotten a drilldown to work on an embedded report?
FYI, I've tryied using the sample ReportViewer sample application as
well and I have the same problem:drill downs don't work. (Makes sense
since the ReportViewer sample just creates an iframe.)
Tested with both MSRS with and without service pack 1.
Thanks,
Eugene KolovyanskyPlease take a look at Report Manager, which is part of the package. Report
Manager is a separate ASP.NET application, just like yours. It uses SOAP
calls to the server for management but it uses URL access and IFrame to
render reports. See if drill down works in ReportManager. If it does, you
should be able to do it the same way. If not, we can investigate this
problem.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Euge" <yevger@.yahoo.com> wrote in message
news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> I've been through a good bit of the message boards trying to find an
> answer to this problem.
> I have an application that uses an iframe to embed a MSRS report. I'm
> using URL access to call the report.
> The problem is that any drill downs in the report DO NOT actually
> expand anything in the report. I've looked at the rc:ReplacementRoot
> in some threads but I don't see any differences with that extra
> parameter.
> Has anyone gotten a drilldown to work on an embedded report?
> FYI, I've tryied using the sample ReportViewer sample application as
> well and I have the same problem:drill downs don't work. (Makes sense
> since the ReportViewer sample just creates an iframe.)
> Tested with both MSRS with and without service pack 1.
> Thanks,
> Eugene Kolovyansky|||Thanks for the quick response.
I've looked a little at how the Report Manager handles reports. Drill
downs work just fine there. I've noticed a strange issue with drill
downs and images while pursuing this.
Let's say I have a page with two frames.Menu frame points all links to
the main frame. If I have a single link that opens the report into the
main frame , the drill downs do not work and all images are broken.
If I then change the links to open to a new window (calling the URL
access directly), the reports render fine with drilldowns and images.
If I then point the links back to the frames, the reports render just
fine (drill down and all).
Here's the kicker..the drilldowns are expanded to the same positions I
used when I opened it to a new window.
That makes me think that there may be some session action setup by the
reportserver going on that I'm missing. When I close the browser, I
have to start that process over again.
Any thoughts?
Eugene Kolovyansky
"Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> Please take a look at Report Manager, which is part of the package. Report
> Manager is a separate ASP.NET application, just like yours. It uses SOAP
> calls to the server for management but it uses URL access and IFrame to
> render reports. See if drill down works in ReportManager. If it does, you
> should be able to do it the same way. If not, we can investigate this
> problem.
> --
> Dmitry Vasilevsky, SQL Server Reporting Services Developer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> ---
> "Euge" <yevger@.yahoo.com> wrote in message
> news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > I've been through a good bit of the message boards trying to find an
> > answer to this problem.
> > I have an application that uses an iframe to embed a MSRS report. I'm
> > using URL access to call the report.
> > The problem is that any drill downs in the report DO NOT actually
> > expand anything in the report. I've looked at the rc:ReplacementRoot
> > in some threads but I don't see any differences with that extra
> > parameter.
> > Has anyone gotten a drilldown to work on an embedded report?
> >
> > FYI, I've tryied using the sample ReportViewer sample application as
> > well and I have the same problem:drill downs don't work. (Makes sense
> > since the ReportViewer sample just creates an iframe.)
> > Tested with both MSRS with and without service pack 1.
> >
> > Thanks,
> > Eugene Kolovyansky|||I found a way to get the reports to work with drilldowns and all. I
provide a sessionid to the report via rs:SessionId=<anything> with the
URL access url for the report. (as long as <anything> is
alpha-numeric)
I'm looking into sessions in reporting services to see what, if
anything, I would break by doing this.
Eugene Kolovyansky
yevger@.yahoo.com (Euge) wrote in message news:<3f8e8a3d.0407291200.2e9939ab@.posting.google.com>...
> Thanks for the quick response.
> I've looked a little at how the Report Manager handles reports. Drill
> downs work just fine there. I've noticed a strange issue with drill
> downs and images while pursuing this.
> Let's say I have a page with two frames.Menu frame points all links to
> the main frame. If I have a single link that opens the report into the
> main frame , the drill downs do not work and all images are broken.
> If I then change the links to open to a new window (calling the URL
> access directly), the reports render fine with drilldowns and images.
> If I then point the links back to the frames, the reports render just
> fine (drill down and all).
> Here's the kicker..the drilldowns are expanded to the same positions I
> used when I opened it to a new window.
> That makes me think that there may be some session action setup by the
> reportserver going on that I'm missing. When I close the browser, I
> have to start that process over again.
> Any thoughts?
> Eugene Kolovyansky
> "Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> > Please take a look at Report Manager, which is part of the package. Report
> > Manager is a separate ASP.NET application, just like yours. It uses SOAP
> > calls to the server for management but it uses URL access and IFrame to
> > render reports. See if drill down works in ReportManager. If it does, you
> > should be able to do it the same way. If not, we can investigate this
> > problem.
> >
> > --
> > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> > --
> >
> > ---
> > "Euge" <yevger@.yahoo.com> wrote in message
> > news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > > I've been through a good bit of the message boards trying to find an
> > > answer to this problem.
> > > I have an application that uses an iframe to embed a MSRS report. I'm
> > > using URL access to call the report.
> > > The problem is that any drill downs in the report DO NOT actually
> > > expand anything in the report. I've looked at the rc:ReplacementRoot
> > > in some threads but I don't see any differences with that extra
> > > parameter.
> > > Has anyone gotten a drilldown to work on an embedded report?
> > >
> > > FYI, I've tryied using the sample ReportViewer sample application as
> > > well and I have the same problem:drill downs don't work. (Makes sense
> > > since the ReportViewer sample just creates an iframe.)
> > > Tested with both MSRS with and without service pack 1.
> > >
> > > Thanks,
> > > Eugene Kolovyansky|||Drill downs as well as images depend on sessions to work properly. Session
ID is stored in a cookie and set when you first run the report (or press
Ctrl+F5). If your cookies are not shared across frames you get different
sessions.
Providing session ID on the URL forces server to use cookieless sessions.
Session ID that you provide on URL is not present on the server, so server
starts a new session. Since it is a cookieless session, server redirects you
to the new URL with correct session ID (instead of just setting cookie).
Hope this explains the behavior you are seeing.
--
Dmitry Vasilevsky, SQL Server Reporting Services Developer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
---
"Euge" <yevger@.yahoo.com> wrote in message
news:3f8e8a3d.0408020626.3b640531@.posting.google.com...
> I found a way to get the reports to work with drilldowns and all. I
> provide a sessionid to the report via rs:SessionId=<anything> with the
> URL access url for the report. (as long as <anything> is
> alpha-numeric)
> I'm looking into sessions in reporting services to see what, if
> anything, I would break by doing this.
> Eugene Kolovyansky
> yevger@.yahoo.com (Euge) wrote in message
news:<3f8e8a3d.0407291200.2e9939ab@.posting.google.com>...
> > Thanks for the quick response.
> > I've looked a little at how the Report Manager handles reports. Drill
> > downs work just fine there. I've noticed a strange issue with drill
> > downs and images while pursuing this.
> >
> > Let's say I have a page with two frames.Menu frame points all links to
> > the main frame. If I have a single link that opens the report into the
> > main frame , the drill downs do not work and all images are broken.
> >
> > If I then change the links to open to a new window (calling the URL
> > access directly), the reports render fine with drilldowns and images.
> > If I then point the links back to the frames, the reports render just
> > fine (drill down and all).
> > Here's the kicker..the drilldowns are expanded to the same positions I
> > used when I opened it to a new window.
> >
> > That makes me think that there may be some session action setup by the
> > reportserver going on that I'm missing. When I close the browser, I
> > have to start that process over again.
> >
> > Any thoughts?
> > Eugene Kolovyansky
> >
> > "Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message
news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> > > Please take a look at Report Manager, which is part of the package.
Report
> > > Manager is a separate ASP.NET application, just like yours. It uses
SOAP
> > > calls to the server for management but it uses URL access and IFrame
to
> > > render reports. See if drill down works in ReportManager. If it does,
you
> > > should be able to do it the same way. If not, we can investigate this
> > > problem.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > > --
> > >
> > > ---
> > > "Euge" <yevger@.yahoo.com> wrote in message
> > > news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > > > I've been through a good bit of the message boards trying to find an
> > > > answer to this problem.
> > > > I have an application that uses an iframe to embed a MSRS report.
I'm
> > > > using URL access to call the report.
> > > > The problem is that any drill downs in the report DO NOT actually
> > > > expand anything in the report. I've looked at the rc:ReplacementRoot
> > > > in some threads but I don't see any differences with that extra
> > > > parameter.
> > > > Has anyone gotten a drilldown to work on an embedded report?
> > > >
> > > > FYI, I've tryied using the sample ReportViewer sample application as
> > > > well and I have the same problem:drill downs don't work. (Makes
sense
> > > > since the ReportViewer sample just creates an iframe.)
> > > > Tested with both MSRS with and without service pack 1.
> > > >
> > > > Thanks,
> > > > Eugene Kolovyansky|||Hi Eugene,
Did u happen to find find any problems with stating the sessionid=<anything>
Thanks
"Euge" wrote:
> I found a way to get the reports to work with drilldowns and all. I
> provide a sessionid to the report via rs:SessionId=<anything> with the
> URL access url for the report. (as long as <anything> is
> alpha-numeric)
> I'm looking into sessions in reporting services to see what, if
> anything, I would break by doing this.
> Eugene Kolovyansky
> yevger@.yahoo.com (Euge) wrote in message news:<3f8e8a3d.0407291200.2e9939ab@.posting.google.com>...
> > Thanks for the quick response.
> > I've looked a little at how the Report Manager handles reports. Drill
> > downs work just fine there. I've noticed a strange issue with drill
> > downs and images while pursuing this.
> >
> > Let's say I have a page with two frames.Menu frame points all links to
> > the main frame. If I have a single link that opens the report into the
> > main frame , the drill downs do not work and all images are broken.
> >
> > If I then change the links to open to a new window (calling the URL
> > access directly), the reports render fine with drilldowns and images.
> > If I then point the links back to the frames, the reports render just
> > fine (drill down and all).
> > Here's the kicker..the drilldowns are expanded to the same positions I
> > used when I opened it to a new window.
> >
> > That makes me think that there may be some session action setup by the
> > reportserver going on that I'm missing. When I close the browser, I
> > have to start that process over again.
> >
> > Any thoughts?
> > Eugene Kolovyansky
> >
> > "Dmitry Vasilevsky [MSFT]" <dmvasi@.microsoft.com> wrote in message news:<#88SaVRdEHA.1048@.tk2msftngp13.phx.gbl>...
> > > Please take a look at Report Manager, which is part of the package. Report
> > > Manager is a separate ASP.NET application, just like yours. It uses SOAP
> > > calls to the server for management but it uses URL access and IFrame to
> > > render reports. See if drill down works in ReportManager. If it does, you
> > > should be able to do it the same way. If not, we can investigate this
> > > problem.
> > >
> > > --
> > > Dmitry Vasilevsky, SQL Server Reporting Services Developer
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > > --
> > >
> > > ---
> > > "Euge" <yevger@.yahoo.com> wrote in message
> > > news:3f8e8a3d.0407281450.34ad533c@.posting.google.com...
> > > > I've been through a good bit of the message boards trying to find an
> > > > answer to this problem.
> > > > I have an application that uses an iframe to embed a MSRS report. I'm
> > > > using URL access to call the report.
> > > > The problem is that any drill downs in the report DO NOT actually
> > > > expand anything in the report. I've looked at the rc:ReplacementRoot
> > > > in some threads but I don't see any differences with that extra
> > > > parameter.
> > > > Has anyone gotten a drilldown to work on an embedded report?
> > > >
> > > > FYI, I've tryied using the sample ReportViewer sample application as
> > > > well and I have the same problem:drill downs don't work. (Makes sense
> > > > since the ReportViewer sample just creates an iframe.)
> > > > Tested with both MSRS with and without service pack 1.
> > > >
> > > > Thanks,
> > > > Eugene Kolovyansky
>