Char and Varchar: imports

Showing posts with label imports. 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.

Thursday, March 8, 2012

Check condition and wait

Hi,

I have a data-flow-task that imports data to sqlserver.

Now I want to check, if a special column of an imported record is null.

If yes, I have to wait 10 minutes and jump to the data-flow-task again. (Cjeck and wait).

How can I do this with the integration services?

Thanks

Gerd

You can do timer style loops, so this could be extended for your loop on the 10 minute, and maybe use some extra variables to keep track.

For Loop Container Samples
(http://www.sqlis.com/310.aspx)

You could have an Exec SQL Task inside your loop as the first task. Query the column and assign a variable value using the query result to indicate if the column is true. Then have a Data Flow Task linked from the Exec SQL Task and use an Expression on the constraint such that it is satisfied only when the variable in indicates that the Exec SQL Task found a value.

The final thing would be to extend the loop EvalExpression such that if it would not wait/loop if the Exec SQL Task assigned variable indicate data had been found.

The principal seems sound, though you may want to adjust the expressions and variables used to fit exactly with what you want.

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

Thursday, March 8, 2012

Check condition and wait

Char and Varchar

Blog Archive

About Me