Char and Varchar: creates

Showing posts with label creates. Show all posts

Thursday, March 22, 2012

check if database is in use via a script

Hi I have a large script that creates a database and imports data from
another database. If first drops the destination database if it exists. I
was wondering if there is a way to put conditional code in a script to check
if the database is in use and if so skip the script and display a warning
message? Since the script takes a few minutes to run this could save some
time! Thanks.
--
Paul G
Software engineer.Do a select from master..sysprocesses where dbid = <dbid>
If at least 1 record is returned then the db id in use.
--
MG
"Paul" wrote:
> Hi I have a large script that creates a database and imports data from
> another database. If first drops the destination database if it exists. I
> was wondering if there is a way to put conditional code in a script to check
> if the database is in use and if so skip the script and display a warning
> message? Since the script takes a few minutes to run this could save some
> time! Thanks.
> --
> Paul G
> Software engineer.|||You can use things like sysprocesses (etc, depending on your version) to check whether there are
connection in the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> Hi I have a large script that creates a database and imports data from
> another database. If first drops the destination database if it exists. I
> was wondering if there is a way to put conditional code in a script to check
> if the database is in use and if so skip the script and display a warning
> message? Since the script takes a few minutes to run this could save some
> time! Thanks.
> --
> Paul G
> Software engineer.|||HI thanks for the response. Is the dbid the database process id or just the
database name?
--
Paul G
Software engineer.
"Hurme" wrote:
> Do a select from master..sysprocesses where dbid = <dbid>
> If at least 1 record is returned then the db id in use.
> --
> MG
>
> "Paul" wrote:
> > Hi I have a large script that creates a database and imports data from
> > another database. If first drops the destination database if it exists. I
> > was wondering if there is a way to put conditional code in a script to check
> > if the database is in use and if so skip the script and display a warning
> > message? Since the script takes a few minutes to run this could save some
> > time! Thanks.
> > --
> > Paul G
> > Software engineer.|||Thanks for the information. I tried
select * from sysprocesses --
and it returned the name of the database along with the DB_ID, database ID,
although it did not return any information reguarding if in use. Using
MSSQL 2000 enterprise.
Paul G
Software engineer.
"Tibor Karaszi" wrote:
> You can use things like sysprocesses (etc, depending on your version) to check whether there are
> connection in the database.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> > Hi I have a large script that creates a database and imports data from
> > another database. If first drops the destination database if it exists. I
> > was wondering if there is a way to put conditional code in a script to check
> > if the database is in use and if so skip the script and display a warning
> > message? Since the script takes a few minutes to run this could save some
> > time! Thanks.
> > --
> > Paul G
> > Software engineer.
>|||On Tue, 10 Jul 2007 23:04:08 +0200, "Tibor Karaszi"
<tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote:
>You can use things like sysprocesses (etc, depending on your version) to check whether there are
>connection in the database.
sysprocesses will certainly tell you if any connection is defaulting
to a particular database, but I don't think it reveals that the
connection made to database Foo is referencing Bar..object. Perhaps
checking locks too would take care of that?
Roy Harvey
Beacon Falls, CT|||The DBID is the database ID of the database. If you execute sp_helpdb you
can find out what it is. If you don't see any records in the sysprocesses
table with that dbid then the db is not in use.
--
MG
"Paul" wrote:
> Thanks for the information. I tried
> select * from sysprocesses --
> and it returned the name of the database along with the DB_ID, database ID,
> although it did not return any information reguarding if in use. Using
> MSSQL 2000 enterprise.
> Paul G
> Software engineer.
>
> "Tibor Karaszi" wrote:
> > You can use things like sysprocesses (etc, depending on your version) to check whether there are
> > connection in the database.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> > > Hi I have a large script that creates a database and imports data from
> > > another database. If first drops the destination database if it exists. I
> > > was wondering if there is a way to put conditional code in a script to check
> > > if the database is in use and if so skip the script and display a warning
> > > message? Since the script takes a few minutes to run this could save some
> > > time! Thanks.
> > > --
> > > Paul G
> > > Software engineer.
> >
> >|||ok thanks for the information, so for each record that is returned that
means that the corresponding database is in use?
--
Paul G
Software engineer.
"Hurme" wrote:
> The DBID is the database ID of the database. If you execute sp_helpdb you
> can find out what it is. If you don't see any records in the sysprocesses
> table with that dbid then the db is not in use.
> --
> MG
>
> "Paul" wrote:
> > Thanks for the information. I tried
> > select * from sysprocesses --
> > and it returned the name of the database along with the DB_ID, database ID,
> > although it did not return any information reguarding if in use. Using
> > MSSQL 2000 enterprise.
> >
> > Paul G
> > Software engineer.
> >
> >
> > "Tibor Karaszi" wrote:
> >
> > > You can use things like sysprocesses (etc, depending on your version) to check whether there are
> > > connection in the database.
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > http://www.karaszi.com/sqlserver/default.asp
> > > http://sqlblog.com/blogs/tibor_karaszi
> > >
> > >
> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > > news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
> > > > Hi I have a large script that creates a database and imports data from
> > > > another database. If first drops the destination database if it exists. I
> > > > was wondering if there is a way to put conditional code in a script to check
> > > > if the database is in use and if so skip the script and display a warning
> > > > message? Since the script takes a few minutes to run this could save some
> > > > time! Thanks.
> > > > --
> > > > Paul G
> > > > Software engineer.
> > >
> > >|||YEs, each row represent a connection to the database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:35FC149B-8748-46A4-9E06-59A621E59857@.microsoft.com...
> ok thanks for the information, so for each record that is returned that
> means that the corresponding database is in use?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>> The DBID is the database ID of the database. If you execute sp_helpdb you
>> can find out what it is. If you don't see any records in the sysprocesses
>> table with that dbid then the db is not in use.
>> --
>> MG
>>
>> "Paul" wrote:
>> > Thanks for the information. I tried
>> > select * from sysprocesses --
>> > and it returned the name of the database along with the DB_ID, database ID,
>> > although it did not return any information reguarding if in use. Using
>> > MSSQL 2000 enterprise.
>> >
>> > Paul G
>> > Software engineer.
>> >
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> > > You can use things like sysprocesses (etc, depending on your version) to check whether there
>> > > are
>> > > connection in the database.
>> > >
>> > > --
>> > > Tibor Karaszi, SQL Server MVP
>> > > http://www.karaszi.com/sqlserver/default.asp
>> > > http://sqlblog.com/blogs/tibor_karaszi
>> > >
>> > >
>> > > "Paul" <Paul@.discussions.microsoft.com> wrote in message
>> > > news:9552307B-1C24-4948-947E-B0CF052C78FA@.microsoft.com...
>> > > > Hi I have a large script that creates a database and imports data from
>> > > > another database. If first drops the destination database if it exists. I
>> > > > was wondering if there is a way to put conditional code in a script to check
>> > > > if the database is in use and if so skip the script and display a warning
>> > > > message? Since the script takes a few minutes to run this could save some
>> > > > time! Thanks.
>> > > > --
>> > > > Paul G
>> > > > Software engineer.
>> > >
>> > >|||dbid is the id for the database. Use DB_NAME() to translate to database name.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:85F2D15F-6203-4C43-91E1-635D6BD448D5@.microsoft.com...
> HI thanks for the response. Is the dbid the database process id or just the
> database name?
> --
> Paul G
> Software engineer.
>
> "Hurme" wrote:
>> Do a select from master..sysprocesses where dbid = <dbid>
>> If at least 1 record is returned then the db id in use.
>> --
>> MG
>>
>> "Paul" wrote:
>> > Hi I have a large script that creates a database and imports data from
>> > another database. If first drops the destination database if it exists. I
>> > was wondering if there is a way to put conditional code in a script to check
>> > if the database is in use and if so skip the script and display a warning
>> > message? Since the script takes a few minutes to run this could save some
>> > time! Thanks.
>> > --
>> > Paul G
>> > Software engineer.

Tuesday, March 20, 2012

Check if a windows/nt user account exists

I have some script which creates/grant priveleges to windows/nt users to DB but i frequently get the message below:

Windows NT user or group '<user or group>' not found. Check the name again.

I understand that this is because the said user/group is indeed not present in the environment I'm running the script (ie. testing and production environment).

But is it possible to have some sort of checking whether the user/group exists in the environment so that I could determine whether or not to call some lines of code?

Not sure if this is possible so i had to ask. c",)

SELECT *

FROM sysusers

will show the users from the current database. There are columns like isntname and isntgroup that will tell you if it is an NT user name or group. There are more than just the normal users, so take a look at what is in that table.

|||

If i'm not mistaken that will only show users already registered for the current database.

I needed to check if a NT user name/group is valid/exists in the environment rather than if it is registered for the current database.

Appreciate your help but i think i need something else or could you expound how would that apply for my objective?

Thanks in advance

|||

What version of SQL Server are you using? You could use SUSER_SID('<your_windows_login_name>' and check for non-null value. But this method depends on your configuration - the SQL Server service account, member server or standalone server etc. So there is no easy way to do this from the database engine.

You could use the ADSI OLEDB provider and perform distributed queries but this depends on your network configuration & whether LDAP is allowed and so on. In SQL Server 2005, you can write SQLCLR function to perform the check but this requires enabling CLR on the server, creating assembly with possibly UNSAFE or minimum of EXTERNAL_ACCESS permissions.

Best is to perform the check outside of the database, filter the list of users and create the logins. Or you can simply perform the error check in TSQL. This is also easier with TRY..CATCH in SQL Server 2005.

|||Thanks, will definitely try to look into these.|||I think i'll just go for the try-catch. Thanks|||Try catch should be fine, but be careful that you don't need speed. Performance will be a bigger concern just catching the error instead of checking for it first.

Thursday, March 22, 2012

check if database is in use via a script

check if database is in use via a script

check if database is in use via a script

Tuesday, March 20, 2012

Check if a windows/nt user account exists

Char and Varchar

Blog Archive

About Me