Executable or Way for User to Launch an SQL Package?

I have a sitation where I need a dts package or similar to run at a user initiated time. I do not want to give the user access to the server. Any ideas on how one goes about something like this?Howdy

If its through a web or VB type app, let the web app execute a stored procedure called by the app.

Cheers,

SG.|||Hey,
from a vb app or vb script you can do this function:

Public Sub ExecuteEDIPackage(FileName As Variant)

Dim sServer As String
Dim sUsername As String
Dim sPassword As String
Dim sPackageName As String
Dim lErr As Long
Dim sSource As String
Dim sDesc As String

Set oPKG = New DTS.Package

' Set Parameter Values
sPackageName = "EDIPackage"

' Load Package
oPKG.LoadFromSQLServer DataSource, UserName, Password, _
DTSSQLStgFlag_Default, , , , sPackageName

' Set Exec on Main Thread
For Each oStep In oPKG.Steps
oStep.ExecuteInMainThread = True
Next

' Execute
oPKG.Execute

' Get Status and Error Message
For Each oStep In oPKG.Steps
If oStep.ExecutionResult = DTSStepExecResult_Failure Then
oStep.GetExecutionErrorInfo lErr, sSource, sDesc
sMessage = sMessage & "Step """ & oStep.Name & _
""" Failed" & vbCrLf & _
vbTab & "Error: " & lErr & vbCrLf & _
vbTab & "Source: " & sSource & vbCrLf & _
vbTab & "Description: " & sDesc & vbCrLf & vbCrLf
Else
sMessage = sMessage & "Step """ & oStep.Name & _
""" Succeeded" & vbCrLf & vbCrLf
End If
Next

oPKG.UnInitialize

Set oStep = Nothing
Set oPKG = Nothing

End Sub

EXEC security question

I am not an expert with SQL security first of all.
I have a sql user account setup and I want to grant
database wide SP EXEC access, is there a way to do this
or do I have to grant each SP EXEC access on that users
account?
On 2005, you can grant execute permissions on schema and also database level. No such option in
2000, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>I am not an expert with SQL security first of all.
> I have a sql user account setup and I want to grant
> database wide SP EXEC access, is there a way to do this
> or do I have to grant each SP EXEC access on that users
> account?
|||Thanks, that makes sense.
On a related note. I have given persmission to the user to EXEC on a SP.
It works, but it keeps dropping the permission back to do not allow. So
he'll run the SP from a VBS file and it works fine, but if we try to run it
again it fails, and when we check the permissions, it is off again, ever
heard of that?
"Tibor Karaszi" wrote:

> On 2005, you can grant execute permissions on schema and also database level. No such option in
> 2000, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>
|||> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
is gone from the fact that the user cannot perform the action anymore? First step is to see what has
happened. Then one might investigate why or who did it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...[vbcol=seagreen]
> Thanks, that makes sense.
> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
> "Tibor Karaszi" wrote:
|||After we received he permission denied error we went into enterprise mngr and
the EXEC privlege was cleared. Where as before it was checked. I don't see
how or why it would clear itself?
"Tibor Karaszi" wrote:

> Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
> is gone from the fact that the user cannot perform the action anymore? First step is to see what has
> happened. Then one might investigate why or who did it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
>
|||Maybe there is a REVOKE or DENY coded into the procedure?
I've seen procedure developers mistakenly put a GRANT or REVOKE
statement at the end of their proc (overlooking the need for a BEGIN
END block or a GO statement).

EXEC security question

I am not an expert with SQL security first of all.
I have a sql user account setup and I want to grant
database wide SP EXEC access, is there a way to do this
or do I have to grant each SP EXEC access on that users
account?On 2005, you can grant execute permissions on schema and also database level. No such option in
2000, though.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>I am not an expert with SQL security first of all.
> I have a sql user account setup and I want to grant
> database wide SP EXEC access, is there a way to do this
> or do I have to grant each SP EXEC access on that users
> account?|||Thanks, that makes sense.
On a related note. I have given persmission to the user to EXEC on a SP.
It works, but it keeps dropping the permission back to do not allow. So
he'll run the SP from a VBS file and it works fine, but if we try to run it
again it fails, and when we check the permissions, it is off again, ever
heard of that?
"Tibor Karaszi" wrote:
> On 2005, you can grant execute permissions on schema and also database level. No such option in
> 2000, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
> >I am not an expert with SQL security first of all.
> >
> > I have a sql user account setup and I want to grant
> > database wide SP EXEC access, is there a way to do this
> > or do I have to grant each SP EXEC access on that users
> > account?
>|||> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
is gone from the fact that the user cannot perform the action anymore? First step is to see what has
happened. Then one might investigate why or who did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
> Thanks, that makes sense.
> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run it
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
> "Tibor Karaszi" wrote:
>> On 2005, you can grant execute permissions on schema and also database level. No such option in
>> 2000, though.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
>> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>> >I am not an expert with SQL security first of all.
>> >
>> > I have a sql user account setup and I want to grant
>> > database wide SP EXEC access, is there a way to do this
>> > or do I have to grant each SP EXEC access on that users
>> > account?
>>|||After we received he permission denied error we went into enterprise mngr and
the EXEC privlege was cleared. Where as before it was checked. I don't see
how or why it would clear itself?
"Tibor Karaszi" wrote:
> > On a related note. I have given persmission to the user to EXEC on a SP.
> > It works, but it keeps dropping the permission back to do not allow. So
> > he'll run the SP from a VBS file and it works fine, but if we try to run it
> > again it fails, and when we check the permissions, it is off again, ever
> > heard of that?
> Did you check that the GRANT you performed really disappears? Or did you draw the conclusion that it
> is gone from the fact that the user cannot perform the action anymore? First step is to see what has
> happened. Then one might investigate why or who did it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
> > Thanks, that makes sense.
> >
> > On a related note. I have given persmission to the user to EXEC on a SP.
> > It works, but it keeps dropping the permission back to do not allow. So
> > he'll run the SP from a VBS file and it works fine, but if we try to run it
> > again it fails, and when we check the permissions, it is off again, ever
> > heard of that?
> >
> > "Tibor Karaszi" wrote:
> >
> >> On 2005, you can grant execute permissions on schema and also database level. No such option in
> >> 2000, though.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> >> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
> >> >I am not an expert with SQL security first of all.
> >> >
> >> > I have a sql user account setup and I want to grant
> >> > database wide SP EXEC access, is there a way to do this
> >> > or do I have to grant each SP EXEC access on that users
> >> > account?
> >>
> >>
>|||Maybe there is a REVOKE or DENY coded into the procedure?
I've seen procedure developers mistakenly put a GRANT or REVOKE
statement at the end of their proc (overlooking the need for a BEGIN
END block or a GO statement).

EXEC security question

I am not an expert with SQL security first of all.
I have a sql user account setup and I want to grant
database wide SP EXEC access, is there a way to do this
or do I have to grant each SP EXEC access on that users
account?On 2005, you can grant execute permissions on schema and also database level
. No such option in
2000, though.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>I am not an expert with SQL security first of all.
> I have a sql user account setup and I want to grant
> database wide SP EXEC access, is there a way to do this
> or do I have to grant each SP EXEC access on that users
> account?|||Thanks, that makes sense.
On a related note. I have given persmission to the user to EXEC on a SP.
It works, but it keeps dropping the permission back to do not allow. So
he'll run the SP from a VBS file and it works fine, but if we try to run it
again it fails, and when we check the permissions, it is off again, ever
heard of that?
"Tibor Karaszi" wrote:

> On 2005, you can grant execute permissions on schema and also database lev
el. No such option in
> 2000, though.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:D8B55EF6-FBAB-4115-B219-0325A1998D6B@.microsoft.com...
>|||> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run i
t
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
Did you check that the GRANT you performed really disappears? Or did you dra
w the conclusion that it
is gone from the fact that the user cannot perform the action anymore? First
step is to see what has
happened. Then one might investigate why or who did it.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...[vbcol=seagreen]
> Thanks, that makes sense.
> On a related note. I have given persmission to the user to EXEC on a SP.
> It works, but it keeps dropping the permission back to do not allow. So
> he'll run the SP from a VBS file and it works fine, but if we try to run i
t
> again it fails, and when we check the permissions, it is off again, ever
> heard of that?
> "Tibor Karaszi" wrote:
>|||After we received he permission denied error we went into enterprise mngr an
d
the EXEC privlege was cleared. Where as before it was checked. I don't see
how or why it would clear itself?
"Tibor Karaszi" wrote:

> Did you check that the GRANT you performed really disappears? Or did you d
raw the conclusion that it
> is gone from the fact that the user cannot perform the action anymore? Fir
st step is to see what has
> happened. Then one might investigate why or who did it.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Chris Patten" <ChrisPatten@.discussions.microsoft.com> wrote in message
> news:655A640F-7AF4-41D0-B720-8876FEA72F94@.microsoft.com...
>|||Maybe there is a REVOKE or DENY coded into the procedure?
I've seen procedure developers mistakenly put a GRANT or REVOKE
statement at the end of their proc (overlooking the need for a BEGIN
END block or a GO statement).

Exclusive DB access

Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequate
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
Roberto
Answered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO and
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Roberto

Exclusive DB access

Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequat
e
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
RobertoAnswered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO an
d
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequ
ate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Robertosql

Exclusive DB access

Hi,
I'm searching a way or a method to gain exclusive access for a short time
period (minutes) to a SQL 2000 database from a VB application using ADO and
SQLDMO, in order to do some data historization and computation, and
preventing that others users may connect to the database during this time.
After searching in the SQLDMO object model, I couldn't find anything adequate
for this purpose. Is the only way to lock/unlock all the tables ?
Has anyone an idea ?
RobertoAnswered in .connectivity
Please do not multi-post.
Regards
Mike
"GunSmoke_62" wrote:
> Hi,
> I'm searching a way or a method to gain exclusive access for a short time
> period (minutes) to a SQL 2000 database from a VB application using ADO and
> SQLDMO, in order to do some data historization and computation, and
> preventing that others users may connect to the database during this time.
> After searching in the SQLDMO object model, I couldn't find anything adequate
> for this purpose. Is the only way to lock/unlock all the tables ?
> Has anyone an idea ?
> Roberto

Exclusive Connection

Is there a way to establish an exclusive SQL 2000 connection, i.e. only
allow 1 user to connect?
In Access I am able using a file DSN with Exclusive=1. Is there a similar
property to use in the connect string or ADO connection object (I do not see
anything glaring there)?
Thanks - Tom> Is there a way to establish an exclusive SQL 2000 connection, i.e. only
> allow 1 user to connect?
> In Access I am able using a file DSN with Exclusive=1. Is there a similar
> property to use in the connect string or ADO connection object (I do not
> see anything glaring there)?
> Thanks - Tom
Use the
ALTER DATABASE db_name SET SINGLE_USER
T-SQL command.
Dejan Sarka

Exclusive accessn cannot be obtained while restoring DB

Hi,
I keep getting this error while trying to restore the
database:
"Exclusive Access could not be obtained because the
database is in use. RESTORE database is terminating
abnormally."
I am sure though that noone has logged onto the system
while I doing the restore but I keep getting this error.
Any clue?
Thanks.use sp_who to see if there is any connection made to the database. Also you
can trying running command through query analyzer.
alter database <db_name> set single_user with rollback immediate.
above command will kill all the session with rolling back on going
transactions for the particular database. then run the restore command with
the same session (since your database is in single user mode).
-Vishal
"John" <kumarj@.logica.com> wrote in message
news:08ed01c33f0c$4c0c7c70$a301280a@.phx.gbl...
> Hi,
> I keep getting this error while trying to restore the
> database:
> "Exclusive Access could not be obtained because the
> database is in use. RESTORE database is terminating
> abnormally."
> I am sure though that noone has logged onto the system
> while I doing the restore but I keep getting this error.
> Any clue?
> Thanks.
>|||John,
Are you trying to restore via enterprise manager?Execute sp_who in Query
analyzer and make sure the concerned databasename is not mentioned in the
output.
If you're running SQL Server 2000, you can issue this command to remove all
connections to the database :
ALTER DATABASE <databasename> SET single_user WITH ROLLBACK IMMEDIATE
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"John" <kumarj@.logica.com> wrote in message
news:08ed01c33f0c$4c0c7c70$a301280a@.phx.gbl...
> Hi,
> I keep getting this error while trying to restore the
> database:
> "Exclusive Access could not be obtained because the
> database is in use. RESTORE database is terminating
> abnormally."
> I am sure though that noone has logged onto the system
> while I doing the restore but I keep getting this error.
> Any clue?
> Thanks.
>

Exclusive access to MS SQL Database

Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
Roberto
Hi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:

> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing that
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto
|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
VB[vbcol=seagreen]
that[vbcol=seagreen]
sql

Exclusive access to MS SQL Database

Hi,
I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
application through ADO for a very short period of time in order to do
special processing (data historization and computation) and preventing that
others users can connect at the same time. Which is the best method ?
Best regards.
RobertoHi
Kill all the other connections where SPID > 50, except for yours. Put the DB
in Single Use mode, do your processing and then remove Single Use Mode.
Regards
Mike
"GunSmoke_62" wrote:

> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||use ALTER DATABASE,
Example:
use master
go
alter database northwind
set SINGLE_USER with ROLLBACK IMMEDIATE
go
--do your stuff
go
alter database northwind
set MULTI_USER with NO_WAIT
go
AMB
"GunSmoke_62" wrote:

> Hi,
> I have to gain exclusive access to a MS SQL Server 2000 Database from a VB
> application through ADO for a very short period of time in order to do
> special processing (data historization and computation) and preventing tha
t
> others users can connect at the same time. Which is the best method ?
> Best regards.
> Roberto|||Your application will need to login with system admin rights in order to do
the following:
Kill all connections automatically (no need to kill each indicidually),
rollback any unresolved transactions, and place the database in single user
mode.
ALTER DATABASE API SET SINGLE_USER WITH ROLLBACK IMMEDIATE
Leave the database in read-only mode. This could help optimize performace of
reporting and data transfer processing.
ALTER DATABASE API SET READ_ONLY
Restrict login to the database to only those users belonging to the database
owner (DBO) role.
ALTER DATABASE API SET RESTRICTED_USER
After doing your thing, the following will restore the database back to
normal.
ALTER DATABASE API SET READ_WRITE
ALTER DATABASE API SET MULTI_USER
Also, for periodic reporting, you may want to restore the latest backup to a
dedicated reporting server or database. That way, the production database
would not need to be made unavailable.
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:5E794404-2B42-4EDB-A8AC-E65B39D234B3@.microsoft.com...
> Hi
> Kill all the other connections where SPID > 50, except for yours. Put the
DB[vbcol=seagreen]
> in Single Use mode, do your processing and then remove Single Use Mode.
> Regards
> Mike
> "GunSmoke_62" wrote:
>
VB[vbcol=seagreen]
that[vbcol=seagreen]

Exclusive Access to DataBase

Hi.
I need to access a database to modify, updates,... massively . It's possible to lock a database and have exclusive access?
(SQLServer 2000)
thanks.
FranciscoALTER DATABASE <dbname> SET SINGLE_USER WITH ROLLBACK IMMEDIATE|||Thanks

Francisco

Exclusive access could not be obtained because the database is in use.

I have a test database that is automatically restored from the live database each day at 2:30AM. I recently started receiving this message:

Exclusive access could not be obtained because the database is in use.

and the restore is not able to occur.

Any ideas on how I can prevent this from happening?

You can't restore a db when it is in use, meaning that there are connections open to the database. Somehow, somewhere, connections are being left open to your database. Before you leave for the day, run the following:

exec sp_who

This will tell you who (username and machine name) has an open connection to the database.

Exclusive access could not be obtained because the database is in use.

How to close the existing connections to a particluar database in sql server. Please note that i donot want to start stop sql server. I just want to close the existing connections so that i can do a restore on that database programatically.

I am using sqldmo for this purpose. Does anyone knows how to do that with sqldmo or is there any other method??

Waiting for your earliest repliessp_who2
followed by
kill <process id>

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

sql

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

Exclusive access could not be obtained because the database is in use

Hi

I am trying to use sql server 2005 management stodio to restore a database. But I got this error message:

Exclusive access could not be obtained because the database is in use

Anyone know how to solve this problem?

Thanks

Li

This error is occurring since some one is trying to access the database while the restore is trying to do a restore. Go to the Management -> activity monitor and see if some one is accessing the DB if so kill the process and then run the restore.

with )s
Santhosh
http://sqlspy.blogspot.com|||

when u restoer the database system should have Exculusive lock. in your case some process is using this database. you should kill those process which are using database. otherwise use this command

use Master

ALTER DATABASE yourdatabasename SET MULTI_USER WITH ROLLBACK IMMEDIATE;

Drop database yourdatabasename

BTW, though Management studio is good and it support all the feature, you must try all these command (Resotre/Backup/Kill /Single_user Mode) etc from the Query Analyser. It gives u a better understanding and it will clear your basics

Madhu

|||I would NOT recommend dropping the database. It is a complete waste of time. If you drop the database, then the first thing that the restore operation has to do is to recreate the database along with reallocating all of the disk space for all of the files. This can make your restore operation MUCH longer than it really needs to be.|||

I'm having the same issue. I do not see activity manager under the management tree. Am I looking in the right place. A screen shot would be helpful if possible.

Thanks

Victor

|||

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

|||

Error 3101
Severity Level 16
Message Text
Exclusive access could not be obtained because the database is in use.

Explanation
This error occurs when you attempt to load a backup while users are accessing the database. This error can occur with RESTORE DATABASE or RESTORE LOG. You cannot use the RESTORE DATABASE statement while the database is in use by any user, including yourself.

Action
Use the ALTER DATABASE SET SINGLE_USER to remove users from the database.

Or, wait until all users have finished using the database, and then use the RESTORE DATABASE statement. Make sure that you are not using the database being loaded when you issue the RESTORE DATABASE statement. Although not required, it is best to run the RESTORE DATABASE statement from the master database.

MAKE USE THAT YOU ARE RUNNING THE QUERY FROM THE MASTER DATABASE!

- If you invoke the query under the database you are trying to restore, you will continue to receive the Error 3101.

|||

hello,

Where is this option Activity Monitor?

Thanks,
Dom

|||

Aaronator wrote:

Use any script view (such as query analyzer) to execute the following script. This will put your db in single user mode and execute the restore:

Code Snippet

Use Master

Alter Database YOURDB
SET SINGLE_USER With ROLLBACK IMMEDIATE

RESTORE DATABASE YOURDB
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\YOURDB.bak'

Aaron Smith

DiaMed - Diabetes Management Software

http://www.chirondata.com

As I am putting my DB in Single User Mode how do I return to the normal mode?

Thanks

|||

Felyjos wrote:

As I am putting my DB in Single User Mode how do I return to the normal mode?

When you restore a database, it will be restored in the mode in which it was when the backup was made, probably MULTI USER. In this case a restore will suffice.

To put a database to MULTI USER yourself:

Code Snippet

USE master;
GO

ALTER DATABASE << db name>>

SET MULTI_USER;
GO

Regards,

Jeroen

|||What does the Rollback Immediate do?

EXCEPTION_ACCESS_VIOLATION

I am using SQL Server 7 and the following query giving
exception access violation error.
Error is : ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 14 generated fatal
exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
is terminating this process.
Could you please suggest what could be wrong with the
Query.
It is working fine if I sub divide the Derived expression
to intermediate temporary tables.
SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
BUDGET_DATE,
(((D1.WB_AVG * ISNULL
(ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
@.MONTH_DAYS AS WB_EXPENSE
INTO #WB_MTD
FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
CHG_OFF_1,
COALESCE(D.REGION,L.REGION) AS REGION,
@.BUDGET_DATE AS BUDGET_DATE,
(((ISNULL(L.PRE_LOANS,0) - ISNULL
(D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
(L.CUR_LOANS,0)))/2) AS WB_AVG
FROM (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
FROM DEPOSITS_CUBE_V
WHERE BUSINESS_DATE = @.BUDGET_DATE AND
CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
FULL OUTER JOIN (SELECT BUSINESS_DATE AS
BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
FROM LOANS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
ON D.BUDGET_DATE = L.BUDGET_DATE AND
D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
(RATE_LAST_USED,0))/100) AS ANN_INTEREST
FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE = @.BUDGET_DATE AND CURR_BAL > 0
GROUP BY REGION) AS R
ON D1.REGION = R.REGION
Thanks in advalceThere types of errors are typically bugs in SQL Server. Assuming you are current on service pack and
have searched KB already, I suggest you open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Girija Ponnaganti" <anonymous@.discussions.microsoft.com> wrote in message
news:006701c3ad6a$a59dd9b0$a401280a@.phx.gbl...
> I am using SQL Server 7 and the following query giving
> exception access violation error.
> Error is : ODBC: Msg 0, Level 19, State 1
> SqlDumpExceptionHandler: Process 14 generated fatal
> exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server
> is terminating this process.
> Could you please suggest what could be wrong with the
> Query.
> It is working fine if I sub divide the Derived expression
> to intermediate temporary tables.
> SELECT D1.CHG_OFF_1,D1.REGION AS REGION,@.BUDGET_DATE AS
> BUDGET_DATE,
> (((D1.WB_AVG * ISNULL
> (ANN_INTEREST,0))/TOTAL_DEPOSITS)/@.YEAR_DAYS) *
> @.MONTH_DAYS AS WB_EXPENSE
> INTO #WB_MTD
> FROM ( SELECT COALESCE(D.CHG_OFF_1,L.CHG_OFF_1) AS
> CHG_OFF_1,
> COALESCE(D.REGION,L.REGION) AS REGION,
> @.BUDGET_DATE AS BUDGET_DATE,
> (((ISNULL(L.PRE_LOANS,0) - ISNULL
> (D.PRE_DEPOSITS,0)) + (ISNULL(D.CUR_DEPOSITS,0) - ISNULL
> (L.CUR_LOANS,0)))/2) AS WB_AVG
> FROM (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_DEPOSITS,SUM(ISNULL(PRE_BAL,0)) AS PRE_DEPOSITS
> FROM DEPOSITS_CUBE_V
> WHERE BUSINESS_DATE = @.BUDGET_DATE AND
> CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS D
> FULL OUTER JOIN (SELECT BUSINESS_DATE AS
> BUDGET_DATE,CHG_OFF_1,REGION,SUM(ISNULL(CURR_BAL,0)) AS
> CUR_LOANS,SUM(ISNULL(PRE_BAL,0)) AS PRE_LOANS
> FROM LOANS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY BUSINESS_DATE,CHG_OFF_1,REGION) AS L
> ON D.BUDGET_DATE = L.BUDGET_DATE AND
> D.CHG_OFF_1 = L.CHG_OFF_1 AND D.REGION = L.REGION ) AS D1
> JOIN (SELECT REGION,SUM(ISNULL(CURR_BAL,0)) AS
> TOTAL_DEPOSITS,SUM((ISNULL(CURR_BAL,0) * ISNULL
> (RATE_LAST_USED,0))/100) AS ANN_INTEREST
> FROM DEPOSITS_CUBE_V WHERE BUSINESS_DATE => @.BUDGET_DATE AND CURR_BAL > 0
> GROUP BY REGION) AS R
> ON D1.REGION = R.REGION
>
> Thanks in advalce