Nested Stored Proc

Pleaseet me know the Advantages and Divantages of nested Stored porc in
sql server 2000.Mustafa
You have to understand why you need to use nested stored procedures
In my opinion one of "diventages" is a maintenance, that if one of the
DML is failed to rollback whole batch , so obviously it depends in your
business reqirements
Also read up this article
http://www.sql-server-performance.c..._procedures.asp
"Mustafa" <Mustafa@.discussions.microsoft.com> wrote in message
news:834A3DFF-CBA5-45B9-B798-DE41EC54E871@.microsoft.com...
> Pleaseet me know the Advantages and Divantages of nested Stored porc in
> sql server 2000.|||if by "nesting" you mean calling one SP from another, this is busy subject..
.
See Code Complete, or Code Complete II, by Steve McConnell, for basic ideas.
1. In general, Nesting allows you to better organize, resuse and maintain
the code you write.
2. Portions of code that are used by many SPs within adatabase system can
be written in a separate SP, and maintained in only one spot insteadof havin
g
to be copied and pasted in multiple places.
3. When you write a long SP that executes a different portion of it's code
each time it runs, depending on the values of parameters, the query optimize
r
and compiler will only optimize it for the portion of the code that it ran
the first time it ran - when it was compiled.. Subsequent runs, (unless it i
s
recompiled each time) it may not be running the best possible query plan.
4. There will many more SPs within you system to maintain if you split them
up into Main SPs and Subroutines.
5. Error handling in"Called" Subroutine SPs is a bit trickier. If you're
not familiar, make sure you read up on this topic...
etc.
"Mustafa" wrote:

> Pleaseet me know the Advantages and Divantages of nested Stored porc in
> sql server 2000.

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?
Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
....
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?
|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns>)
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>
|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.
|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems to
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns>)
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:
>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems to
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

need work around for if/then parsing problem

i'm writing a proc where i use an if/then statement to check a condition,
then create a temp table. the problem i'm having is that sql server seems to
think that the proc will attempt to create the same table twice. example:
if A
Select * Into #temp From Foo Where Bar = A
else
Select * Into #temp From Foo Where Bar <> A
How do you work around this?Hi
You need to check whether the table already exists or not
if object_id('tempdb.dbo.#tem') is not null
begin
...
end
"ponhaus" <ponhaus@.discussions.microsoft.com> wrote in message
news:7B4F53C8-09D0-47C7-8F5A-22C7B30C17CD@.microsoft.com...
> i'm writing a proc where i use an if/then statement to check a condition,
> then create a temp table. the problem i'm having is that sql server seems
> to
> think that the proc will attempt to create the same table twice. example:
> if A
> Select * Into #temp From Foo Where Bar = A
> else
> Select * Into #temp From Foo Where Bar <> A
> How do you work around this?|||I rewrote it as you suggest and it still reports error, "There is already an
object named '#Temp' in the database." Look at the example below to see if i
misunderstood what you were suggesting:
Declare @.A int
Set @.A = 0
if @.A = 0
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end
else
begin
if object_id('tempdb.dbo.#Temp') is not null
Drop Table #Temp
Select * Into #Temp From Foo
end|||Two options:
1) Create the temporary table (or table variable) using CREATE TABLE,
then use INSERT INTO in each section,
CREATE TABLE #Temp (<columns> )
If <condition>
INSERT INTO #Temp ...
Else
INSERT INTO #Temp ...
2) Create the temporary table structure first using SELECT * INTO...
then use INSERT INTO
SELECT * INTO #Temp From Foo Where 1 = 0
If <condition>
INSERT INTO ...
Else
INSERT INTO ...
I prefer option 1 because you have control over how the table is created
and what columns are going to be used, and you have the option (if the
table is small enough) of using a table variable instead of a temp table.
Jeff
ponhaus wrote:
I rewrote it as you suggest and it still reports error, "There is
already an
> object named '#Temp' in the database." Look at the example below to see if
i
> misunderstood what you were suggesting:
> Declare @.A int
> Set @.A = 0
> if @.A = 0
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
> else
> begin
> if object_id('tempdb.dbo.#Temp') is not null
> Drop Table #Temp
> Select * Into #Temp From Foo
> end
>|||Thanks,
I ended up using a variation on #2, "Select top 0 From Foo"...but I was
really hoping for a way to sidestep the bug in the interpreter, like turning
off all checking.|||select
*
into #temp
from foo
where (@.flag=1 and bar = A)
or
(@.flag=0 and bar <> A)
lotta code like that lying around.
J.
On Tue, 6 Mar 2007 05:53:00 -0800, ponhaus
<ponhaus@.discussions.microsoft.com> wrote:

>i'm writing a proc where i use an if/then statement to check a condition,
>then create a temp table. the problem i'm having is that sql server seems t
o
>think that the proc will attempt to create the same table twice. example:
>if A
> Select * Into #temp From Foo Where Bar = A
>else
> Select * Into #temp From Foo Where Bar <> A
>How do you work around this?

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
Thanks
You will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
Anith
|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:

> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>
|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
Anith
|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith
|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:

> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
>
|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:[vbcol=seagreen]
> Hi
> I believe Anith is saying that as this is being used in a controlled manner,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then you
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
|||Hi
You could always restore the database with a given name and then change it
after this process using sp_renamedb. You may want to use something like the
following that assumes the database is called TESTUSERS, but you could make
it dynamic:
USE TEMPDB
GO
CREATE TABLE #users ( UserName sysname, UserSID varbinary(85) )
/* You may want to look at TESTUSERS..sysusers, although that would need to
exclude some entries */
INSERT INTO #users ( UserName, UserSID )
EXEC TESTUSERS..sp_change_users_login 'report'
/* Assumes user names and logins are the same */
DECLARE usercursor CURSOR FOR SELECT 'EXEC TESTUSERS..sp_change_users_login
''update_one'', ' + QUOTENAME(UserName) + ', ' + QUOTENAME(UserName) FROM
#users
DECLARE @.updatecmd sysname
OPEN usercursor
FETCH NEXT FROM usercursor INTO @.updatecmd
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.updatecmd
EXEC ( @.updatecmd )
FETCH NEXT FROM usercursor INTO @.updatecmd
END
CLOSE usercursor
DEALLOCATE usercursor
GO
/* Alternatively dropping users */
DECLARE usercursor CURSOR FOR SELECT 'EXEC TESTUSERS..sp_dropuser ' +
QUOTENAME(UserName) FROM #users
DECLARE @.updatecmd sysname
OPEN usercursor
FETCH NEXT FROM usercursor INTO @.updatecmd
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT @.updatecmd
EXEC ( @.updatecmd )
FETCH NEXT FROM usercursor INTO @.updatecmd
END
CLOSE usercursor
DEALLOCATE usercursor
John
"shub" wrote:

> No the databases do not contain the stored proc to drop or create
> users, I complie the stored proc after I restore the database and I
> was wondering if there was some way from my stored proc to restore the
> database if I could do all of that in one step. I think from the input
> I have got, it sounds like I pretty much have to build a large dynamic
> SQL string or do the way I am doing. (Two Step Process)
> I need to drop the databases because every once in a while we will pull
> in databases from different networks so when it is restored on ours
> those users no longer exist so to keep it clean I prefer to delete the
> users.
> John Bell wrote:
>

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
ThanksYou will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
Anith|||On Fri, 15 Dec 2006 09:26:54 -0600, "Anith Sen"
<anith@.bizdatasolutions.com> wrote:

>Consider 3-part naming like database.owner.tablename format rather than USE
>statement.
Since the database name changes, that would require dynamic SQL.
Roy|||On 15 Dec 2006 07:09:00 -0800, "Terry" <tduffy@.calamos.com> wrote:

>You will need to build dynamic sql script, builiding a string with the
>first word 'use dbname', and then execute the string.
It is worth noting that the change that the USE makes does not persist
past the end of the dynamic SQL script. All the processing that has
to occur in the new database would have to be part of the dynamic SQL
string.
Roy|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:

> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
Anith|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:

> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
>|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:[vbcol=seagreen]
> Hi
> I believe Anith is saying that as this is being used in a controlled manne
r,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then y
ou
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
>

Need to use "USE" in a stored Proc

I have a stored Procedure that I always use to restore databases.It
accepts the backup file name,name of the database to be restored as
amongst other parameters. It works pretty good and helps me speed up
the process. However after the database is restored, I need to connect
to that database and compile another stored procedure on that database
and then execute that stored procedure to drop all the users from that
database. I then execute another stored procedure to add the new users
and apply appropriate security.
What I want to do now is to see if there is a way to combine all those
steps all into one stored procedure that will restore the database and
then drop all the users from that database and then add the new users.
When I first attempted it I ran into the issue where I could not use
the "USE " statement in the stored proc, so I ended up writing two
different procs one for dropping users and one for adding new users
that I compile and execute after the database has been restored.
I am sure other people have run into this situation too where within a
stored proc, it is necessary to switch database and perform other tasks
in a different database. I am just looking to get some input to see how
you overcame or handled that issue.
Any help inthis regard will be greatly appreciated.
ThanksYou will need to build dynamic sql script, builiding a string with the
first word 'use dbname', and then execute the string.
Terry
shub wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks|||Consider 3-part naming like database.owner.tablename format rather than USE
statement.
--
Anith|||On Fri, 15 Dec 2006 09:26:54 -0600, "Anith Sen"
<anith@.bizdatasolutions.com> wrote:
>Consider 3-part naming like database.owner.tablename format rather than USE
>statement.
Since the database name changes, that would require dynamic SQL.
Roy|||On 15 Dec 2006 07:09:00 -0800, "Terry" <tduffy@.calamos.com> wrote:
>You will need to build dynamic sql script, builiding a string with the
>first word 'use dbname', and then execute the string.
It is worth noting that the change that the USE makes does not persist
past the end of the dynamic SQL script. All the processing that has
to occur in the new database would have to be part of the dynamic SQL
string.
Roy|||Hi
Why do you need to drop all the users? If you have orphaned users use
sp_change_users_login
John
"shub" wrote:
> I have a stored Procedure that I always use to restore databases.It
> accepts the backup file name,name of the database to be restored as
> amongst other parameters. It works pretty good and helps me speed up
> the process. However after the database is restored, I need to connect
> to that database and compile another stored procedure on that database
> and then execute that stored procedure to drop all the users from that
> database. I then execute another stored procedure to add the new users
> and apply appropriate security.
> What I want to do now is to see if there is a way to combine all those
> steps all into one stored procedure that will restore the database and
> then drop all the users from that database and then add the new users.
> When I first attempted it I ran into the issue where I could not use
> the "USE " statement in the stored proc, so I ended up writing two
> different procs one for dropping users and one for adding new users
> that I compile and execute after the database has been restored.
> I am sure other people have run into this situation too where within a
> stored proc, it is necessary to switch database and perform other tasks
> in a different database. I am just looking to get some input to see how
> you overcame or handled that issue.
> Any help inthis regard will be greatly appreciated.
> Thanks
>|||>> Since the database name changes, that would require dynamic SQL.
That should be fine, if written properly, right? Since it is a
administrative task, it has little to do with injection risks or
recompilation issues.
Otherwise, he can consider a few alternatives:
1. Create individual RESTORE statements within the proc & use IF clause to
control the execution flow
2. Create separate stored procedures in each database & call it from the
main proc with the database as the parameter.
EXEC @.r = @.db.dbo.restore_proc ;
--
Anith|||Anith,
I am not sure I understand what are you suggesting. You are right in
assuming that this is a administrative task and there is no concern or
SQL injection however I do not uderstand the solution.
Currently the Restore Stored proc is complied in the master database
and after the individual database is restored I am then compilibg the
other stored proc to drop all the users.
You may be into something, but I just need to understand to apply it
Thanks
Anith Sen wrote:
> >> Since the database name changes, that would require dynamic SQL.
> That should be fine, if written properly, right? Since it is a
> administrative task, it has little to do with injection risks or
> recompilation issues.
> Otherwise, he can consider a few alternatives:
> 1. Create individual RESTORE statements within the proc & use IF clause to
> control the execution flow
> 2. Create separate stored procedures in each database & call it from the
> main proc with the database as the parameter.
> EXEC @.r = @.db.dbo.restore_proc ;
> --
> Anith|||Hi
I believe Anith is saying that as this is being used in a controlled manner,
there is no need to worry about SQL Injection as there is no input from a
non-trusted user being executed.
If your database contains it own procedure to drop/create the users then you
can just run that, although anyone else with enough rights can run this
procedure as well.
Why do you need to drop/re-create the users?
John
"shub" wrote:
> Anith,
> I am not sure I understand what are you suggesting. You are right in
> assuming that this is a administrative task and there is no concern or
> SQL injection however I do not uderstand the solution.
> Currently the Restore Stored proc is complied in the master database
> and after the individual database is restored I am then compilibg the
> other stored proc to drop all the users.
> You may be into something, but I just need to understand to apply it
> Thanks
> Anith Sen wrote:
> > >> Since the database name changes, that would require dynamic SQL.
> >
> > That should be fine, if written properly, right? Since it is a
> > administrative task, it has little to do with injection risks or
> > recompilation issues.
> >
> > Otherwise, he can consider a few alternatives:
> >
> > 1. Create individual RESTORE statements within the proc & use IF clause to
> > control the execution flow
> > 2. Create separate stored procedures in each database & call it from the
> > main proc with the database as the parameter.
> >
> > EXEC @.r = @.db.dbo.restore_proc ;
> >
> > --
> > Anith
>|||No the databases do not contain the stored proc to drop or create
users, I complie the stored proc after I restore the database and I
was wondering if there was some way from my stored proc to restore the
database if I could do all of that in one step. I think from the input
I have got, it sounds like I pretty much have to build a large dynamic
SQL string or do the way I am doing. (Two Step Process)
I need to drop the databases because every once in a while we will pull
in databases from different networks so when it is restored on ours
those users no longer exist so to keep it clean I prefer to delete the
users.
John Bell wrote:
> Hi
> I believe Anith is saying that as this is being used in a controlled manner,
> there is no need to worry about SQL Injection as there is no input from a
> non-trusted user being executed.
> If your database contains it own procedure to drop/create the users then you
> can just run that, although anyone else with enough rights can run this
> procedure as well.
> Why do you need to drop/re-create the users?
> John
> "shub" wrote:
> > Anith,
> > I am not sure I understand what are you suggesting. You are right in
> > assuming that this is a administrative task and there is no concern or
> > SQL injection however I do not uderstand the solution.
> >
> > Currently the Restore Stored proc is complied in the master database
> > and after the individual database is restored I am then compilibg the
> > other stored proc to drop all the users.
> >
> > You may be into something, but I just need to understand to apply it
> > Thanks
> > Anith Sen wrote:
> > > >> Since the database name changes, that would require dynamic SQL.
> > >
> > > That should be fine, if written properly, right? Since it is a
> > > administrative task, it has little to do with injection risks or
> > > recompilation issues.
> > >
> > > Otherwise, he can consider a few alternatives:
> > >
> > > 1. Create individual RESTORE statements within the proc & use IF clause to
> > > control the execution flow
> > > 2. Create separate stored procedures in each database & call it from the
> > > main proc with the database as the parameter.
> > >
> > > EXEC @.r = @.db.dbo.restore_proc ;
> > >
> > > --
> > > Anith
> >
> >