Need to sum columns: databases

Showing posts with label databases. Show all posts

Friday, March 30, 2012

Dear all,
I want to take backup of my Sql server 2000 databases on network drive but I
am unable to find the network drive only local drive are seen in Enterprise
Manager of my Sql server. Actually I have tried both by, maping my network
drive thru Domain/Administrator password as well as domain/users password
also but it will not showing my network drives.I am also changed my
Sqlserver services with this account also.
Kindly help, Thanx in advance.
Lokesh/RavishrikrishnaBOL
Transact-SQL Reference\BACKUP
--
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>|||Hi Lokesh,
DO u have logon services permission on ur account.
U have to do backup by script.
from example backup database path='192.168.1.234\foldername'
dont use the drive name
when u share the folder add the username from which ur sql services are
running.
the mappnetwork drive may or may not work.
HTP
from
Doller|||You will not anything else in EM other than physical disks connected to the
SQL Server.
You must type the UNC name \\servername\folder in the backup directory and
ensure that the SQL Logon account has both priviliges for the network drive
and network access.
--
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>|||Hi,
1.You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename\dbname.bak'
with init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>|||Lokesh Bhatnagar wrote:
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but I
> am unable to find the network drive only local drive are seen in Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>
You'll have to use a UNC path to do it. If you create a backup device
from EM, you'll have to type in the unc path to where you wan't the
backup file. If you do it from QA, you have to use the backup command
with ...TO DISK = '\\YourServerName\BackupFolder\YourBackupFileName.BAK'
If I'm not wrong, you can't create a Maintenance Plan that backs up to a
UNC path.
Regards
Steensql

Network drive backup

Dear all,
I want to take backup of my Sql server 2000 databases on network drive but I
am unable to find the network drive only local drive are seen in Enterprise
Manager of my Sql server. Actually I have tried both by, maping my network
drive thru Domain/Administrator password as well as domain/users password
also but it will not showing my network drives.I am also changed my
Sqlserver services with this account also.
Kindly help, Thanx in advance.
Lokesh/Ravishrikrishna
BOL
Transact-SQL Reference\BACKUP
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>
|||Hi Lokesh,
DO u have logon services permission on ur account.
U have to do backup by script.
from example backup database path='192.168.1.234\foldername'
dont use the drive name
when u share the folder add the username from which ur sql services are
running.
the mappnetwork drive may or may not work.
HTP
from
Doller
|||You will not anything else in EM other than physical disks connected to the
SQL Server.
You must type the UNC name \\servername\folder in the backup directory and
ensure that the SQL Logon account has both priviliges for the network drive
and network access.
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>
|||Hi,
1.You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharenameXX\dbname.bak'
with init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>
|||Lokesh Bhatnagar wrote:
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but I
> am unable to find the network drive only local drive are seen in Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>
You'll have to use a UNC path to do it. If you create a backup device
from EM, you'll have to type in the unc path to where you wan't the
backup file. If you do it from QA, you have to use the backup command
with ...TO DISK = '\\YourServerName\BackupFolder\YourBackupFileName. BAK'
If I'm not wrong, you can't create a Maintenance Plan that backs up to a
UNC path.
Regards
Steen

Network drive backup

Dear all,
I want to take backup of my Sql server 2000 databases on network drive but I
am unable to find the network drive only local drive are seen in Enterprise
Manager of my Sql server. Actually I have tried both by, maping my network
drive thru Domain/Administrator password as well as domain/users password
also but it will not showing my network drives.I am also changed my
Sqlserver services with this account also.
Kindly help, Thanx in advance.
Lokesh/RavishrikrishnaBOL
Transact-SQL Reference\BACKUP
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>|||Hi Lokesh,
DO u have logon services permission on ur account.
U have to do backup by script.
from example backup database path='192.168.1.234\foldername'
dont use the drive name
when u share the folder add the username from which ur sql services are
running.
the mappnetwork drive may or may not work.
HTP
from
Doller|||You will not anything else in EM other than physical disks connected to the
SQL Server.
You must type the UNC name \\servername\folder in the backup directory and
ensure that the SQL Logon account has both priviliges for the network drive
and network access.
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>|||Hi,
1.You Should start SQL server using Domain user who got access to remote
machine Share
2. Should have share in the remote machine
3. If you need to schedule this as a job then SQL Agent should use the same
Domain user in which SQL server was started
4. Restart the services
Now you can execute the Backup script with UNC path
BACKUP Database <dbname> to disk='\\computername\sharename__\dbname.bak'
with init
Note:
Backup to remote machine will not work if you start SQL server using Local
system account
Thanks
Hari
SQL Server MVP
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:errRL%23lwFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
> I
> am unable to find the network drive only local drive are seen in
> Enterprise
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>|||Lokesh Bhatnagar wrote:
> Dear all,
> I want to take backup of my Sql server 2000 databases on network drive but
I
> am unable to find the network drive only local drive are seen in Enterpris
e
> Manager of my Sql server. Actually I have tried both by, maping my network
> drive thru Domain/Administrator password as well as domain/users password
> also but it will not showing my network drives.I am also changed my
> Sqlserver services with this account also.
> Kindly help, Thanx in advance.
> Lokesh/Ravishrikrishna
>
>
You'll have to use a UNC path to do it. If you create a backup device
from EM, you'll have to type in the unc path to where you wan't the
backup file. If you do it from QA, you have to use the backup command
with ...TO DISK = '\\YourServerName\BackupFolder\YourBacku
pFileName.BAK'
If I'm not wrong, you can't create a Maintenance Plan that backs up to a
UNC path.
Regards
Steen

Wednesday, March 28, 2012

Network

I am installing a new server with windows 2000 and SQL 7 i
will be restoring the databases from the old serveer to
the new but need to keep the network name it seems if the
name is renamed it creates problems has anyone carried out
this opreation successfully
If you are using backup software, no problems. If you copying across the
network and re-attaching, then both machines can not be up on the network at
the same time.
Cheers,
Rod
"Steve Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:166b601c447c6$f50d9020$a101280a@.phx.gbl...
> I am installing a new server with windows 2000 and SQL 7 i
> will be restoring the databases from the old serveer to
> the new but need to keep the network name it seems if the
> name is renamed it creates problems has anyone carried out
> this opreation successfully
|||As Rod states you should be fine but there are some links that may be of
interest:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.databasejournal.com/featu...le.php/2228611
Transferring Logins
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
http://www.support.microsoft.com/?id=274463 Copy DB
Wizard issues
Andrew J. Kelly
SQL Server MVP
"Steve Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:166b601c447c6$f50d9020$a101280a@.phx.gbl...
> I am installing a new server with windows 2000 and SQL 7 i
> will be restoring the databases from the old serveer to
> the new but need to keep the network name it seems if the
> name is renamed it creates problems has anyone carried out
> this opreation successfully

Monday, March 12, 2012

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

I am also interested if the distributed transactions work properly in the replicated environment (peer-to-peer). I mean, if I want to update a record on a remote site, but don't want this transaction to be replicated back to me - is that possible?

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

Thanks again,

Alex.

|||It depended from type of your replication. If you use transactional replication, you could try to configure replication filters for ignoring, but it isn't simple task. In common case, for replication distributed and local changes don't have any difference.|||

I suppose I am planning to use peer-to-peer replication - where an object/record can be updated at any site. There I have a situation, where I want to update local record, which will be replicated to all other sites and - in the same transaction - the same record at a specific remote site - and this one I do not want to be replicated.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

Nested distributed transaction

Hello everybody-

I am trying to understand how to make distributed transactions in MS SQL 2005.

For example, I got two databases A and B and a client, connected to one of them - A. From that client I want to initiate transaction to B, using connection to A.

This functionality is available in Oracle using database links. With all new changes, does it exist in MS SQL 2005?

Thanks,

Alex.

For stating distributed transactions you could (for example):

1. Use statement BEGIN DISTRIBUTED TRANSACTION T-SQL statement

2. If you use .NET you could use TransactionScope class

|||

This is good. Then, in the BEGIN DISTRIBUTED TRAN... I need to explicitely say the Remote Server name to identify the target table. Do I register it with DTS or just as on the client alias?

Sorry for that questions - I am the Oracle specialist and trying to apply the same framework...

Thanks,

Alex.

|||

For call OtherServer.OtherDB.OtherSchema.OtherTable you need to add linked server for you SQL Server.

You could to do it at SQL Server Management Studio or by stored procedure sp_addlinkedserver

|||

Okay. But what about this part, this is quite important for my application:

Thanks again,

Alex.

As I said, I can easily do it in Oracle for Multi-Master replication, but don't see it in the MSSQL2005.

Alex.

nested "starting up database" in the logs

Hi,
I noticed from the logs that, my sql server 2000 Standard Ed (runs on a
windows 2k Advanced server) is starting up the databases over and over again
every second. This goes on and on without stopping, like a nested loop. each
time it does it with a different SPID. When I check the SPID, actually it is
doing something else (such as running a query on a database). It is
currently running SP3, and I am going to install SP4. I wonder if this
resolves it. But before applying the SP4, I wonder if has anyone else run
the similar or same problem. If, so is there a solution for that, or would
SP4 takes care of this problem ?
Thanks for any feedback.
Regards.
Murtix.Turn off the autoclose database option for your databases...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Murtix Van Basten" <nospam@.nospam.org> wrote in message news:428e0b2c$1_1@.alt.athenanews.com...
> Hi,
> I noticed from the logs that, my sql server 2000 Standard Ed (runs on a
> windows 2k Advanced server) is starting up the databases over and over again
> every second. This goes on and on without stopping, like a nested loop. each
> time it does it with a different SPID. When I check the SPID, actually it is
> doing something else (such as running a query on a database). It is
> currently running SP3, and I am going to install SP4. I wonder if this
> resolves it. But before applying the SP4, I wonder if has anyone else run
> the similar or same problem. If, so is there a solution for that, or would
> SP4 takes care of this problem ?
> Thanks for any feedback.
> Regards.
> Murtix.
>|||How do I turn off the autoclose for databases ? (I dont remember I turned it
on).
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23IwjkyVXFHA.3320@.TK2MSFTNGP12.phx.gbl...
> Turn off the autoclose database option for your databases...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
> news:428e0b2c$1_1@.alt.athenanews.com...
>> Hi,
>> I noticed from the logs that, my sql server 2000 Standard Ed (runs on a
>> windows 2k Advanced server) is starting up the databases over and over
>> again
>> every second. This goes on and on without stopping, like a nested loop.
>> each
>> time it does it with a different SPID. When I check the SPID, actually it
>> is
>> doing something else (such as running a query on a database). It is
>> currently running SP3, and I am going to install SP4. I wonder if this
>> resolves it. But before applying the SP4, I wonder if has anyone else run
>> the similar or same problem. If, so is there a solution for that, or
>> would
>> SP4 takes care of this problem ?
>> Thanks for any feedback.
>> Regards.
>> Murtix.
>
>|||I found how to turn it off. I will post the result here. It may fix another
issue I am having, too.
"Murtix Van Basten" <nospam@.nospam.org> wrote in message
news:428e3b35$1_1@.alt.athenanews.com...
> How do I turn off the autoclose for databases ? (I dont remember I turned
> it on).
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23IwjkyVXFHA.3320@.TK2MSFTNGP12.phx.gbl...
>> Turn off the autoclose database option for your databases...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Murtix Van Basten" <nospam@.nospam.org> wrote in message
>> news:428e0b2c$1_1@.alt.athenanews.com...
>> Hi,
>> I noticed from the logs that, my sql server 2000 Standard Ed (runs on
>> a
>> windows 2k Advanced server) is starting up the databases over and over
>> again
>> every second. This goes on and on without stopping, like a nested loop.
>> each
>> time it does it with a different SPID. When I check the SPID, actually
>> it is
>> doing something else (such as running a query on a database). It is
>> currently running SP3, and I am going to install SP4. I wonder if this
>> resolves it. But before applying the SP4, I wonder if has anyone else
>> run
>> the similar or same problem. If, so is there a solution for that, or
>> would
>> SP4 takes care of this problem ?
>> Thanks for any feedback.
>> Regards.
>> Murtix.
>>
>>
>

Saturday, February 25, 2012

need tool for managing database object ownership

we have a group of developers which have created and asked us (DBAs) to
create many objects in the databases including tables / stored
procedures / functions / etc.

since our company is growing, however we have an increasing amount of
objects that have either been abandoned or have several versions.

in an effort to clean of the huge amount of clutter and anytime that
something simple like a stored proc needs to change, it is almost
impossible to predict exactly where we will see negative effects of
this change.

i am looking for a system (preferably without developing our own tool)
that would keep track of history of database objects (in terms of who
created it and what purpose it has) as well as link that to all the
developers/users we might need to notify of any changes to that object.
Also, this should be linked to the application which rely on the
object.

bottom line... every object in the database needs to have at least 1
corresponding contact as well as the applications which us it.

with this information, we can much more easily maintain objects in our
DBs.

thxPossibly this can be of help. Seems it isn't released quite yet, though:

http://msdn.microsoft.com/vstudio/t...ro/default.aspx

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

"batman" <uspensky@.gmail.com> wrote in message
news:1151334096.716911.238760@.i40g2000cwc.googlegr oups.com...
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
> with this information, we can much more easily maintain objects in our
> DBs.
> thx|||batman (uspensky@.gmail.com) writes:
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.

In one word: put everything under version control. If does not exist in
the version-control system, it does not exist at all. From tyhe version-
control system your build your baselines, and any changes to objects
requires you check out code from the version control system and and
check it in again, once the change has passed the module test.

The database should be seen as respository for binary objects and
you should be able to wipe it out at any time, to build a new database
from the version-control system. Developers who have neglected to
check out and in, will lose their changes.

A tip is to have several databases, that makes it easier for people
to understand that they need to use the version-control system.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||
Erland Sommarskog <esquel@.sommarskog.se> wrote:

> In one word: put everything under version control.

"put everything under version control" is one word? Seems like 5 to
me!

8-)

Paul...

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

need tool for managing database object ownership

we have a group of developers which have created and asked us (DBAs) to
create many objects in the databases including tables / stored
procedures / functions / etc.
since our company is growing, however we have an increasing amount of
objects that have either been abandoned or have several versions.
in an effort to clean of the huge amount of clutter and anytime that
something simple like a stored proc needs to change, it is almost
impossible to predict exactly where we will see negative effects of
this change.
i am looking for a system (preferably without developing our own tool)
that would keep track of history of database objects (in terms of who
created it and what purpose it has) as well as link that to all the
developers/users we might need to notify of any changes to that object.
Also, this should be linked to the application which rely on the
object.
bottom line... every object in the database needs to have at least 1
corresponding contact as well as the applications which us it.
with this information, we can much more easily maintain objects in our
DBs.
thxPossibly this can be of help. Seems it isn't released quite yet, though:
http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"batman" <uspensky@.gmail.com> wrote in message
news:1151334096.716911.238760@.i40g2000cwc.googlegroups.com...
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
> with this information, we can much more easily maintain objects in our
> DBs.
> thx
>|||batman (uspensky@.gmail.com) writes:
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
In one word: put everything under version control. If does not exist in
the version-control system, it does not exist at all. From tyhe version-
control system your build your baselines, and any changes to objects
requires you check out code from the version control system and and
check it in again, once the change has passed the module test.
The database should be seen as respository for binary objects and
you should be able to wipe it out at any time, to build a new database
from the version-control system. Developers who have neglected to
check out and in, will lose their changes.
A tip is to have several databases, that makes it easier for people
to understand that they need to use the version-control system.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Possibly this can be of help. Seems it isn't released quite yet, though:
http://msdn.microsoft.com/vstudio/t...ro/default.aspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"batman" <uspensky@.gmail.com> wrote in message
news:1151334096.716911.238760@.i40g2000cwc.googlegroups.com...
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
> with this information, we can much more easily maintain objects in our
> DBs.
> thx
>|||batman (uspensky@.gmail.com) writes:
> we have a group of developers which have created and asked us (DBAs) to
> create many objects in the databases including tables / stored
> procedures / functions / etc.
> since our company is growing, however we have an increasing amount of
> objects that have either been abandoned or have several versions.
> in an effort to clean of the huge amount of clutter and anytime that
> something simple like a stored proc needs to change, it is almost
> impossible to predict exactly where we will see negative effects of
> this change.
> i am looking for a system (preferably without developing our own tool)
> that would keep track of history of database objects (in terms of who
> created it and what purpose it has) as well as link that to all the
> developers/users we might need to notify of any changes to that object.
> Also, this should be linked to the application which rely on the
> object.
> bottom line... every object in the database needs to have at least 1
> corresponding contact as well as the applications which us it.
In one word: put everything under version control. If does not exist in
the version-control system, it does not exist at all. From tyhe version-
control system your build your baselines, and any changes to objects
requires you check out code from the version control system and and
check it in again, once the change has passed the module test.
The database should be seen as respository for binary objects and
you should be able to wipe it out at any time, to build a new database
from the version-control system. Developers who have neglected to
check out and in, will lose their changes.
A tip is to have several databases, that makes it easier for people
to understand that they need to use the version-control system.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland Sommarskog <esquel@.sommarskog.se> wrote:

> In one word: put everything under version control.
"put everything under version control" is one word? Seems like 5 to
me!
8-)
Paul...
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work
!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.|||Erland Sommarskog <esquel@.sommarskog.se> wrote:

> In one word: put everything under version control.
"put everything under version control" is one word? Seems like 5 to
me!
8-)
Paul...
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work
!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

Monday, February 20, 2012

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
> >
> >

Friday, March 30, 2012

Network Drives

Network Drives

Network drive backup

Network drive backup

Network drive backup

Wednesday, March 28, 2012

Network

Monday, March 12, 2012

Nested distributed transaction

Nested distributed transaction

nested "starting up database" in the logs

nested "starting up database" in the logs

nested "starting up database" in the logs

Saturday, February 25, 2012

need tool for managing database object ownership

need tool for managing database object ownership

Monday, February 20, 2012

Need to use "USE" in a stored Proc

Need to use "USE" in a stored Proc

Need to use "USE" in a stored Proc

Need to sum columns

Blog Archive

About Me