having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them async,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.
On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...
|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...
|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
>
|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx
|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>
|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:
> You can use RAISERROR...WITH NOWAIT to send informational progress messages.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
>
Showing posts with label method. Show all posts
Showing posts with label method. Show all posts
Friday, March 30, 2012
query progress indicator
having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them async,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>> You can use RAISERROR...WITH NOWAIT to send informational progress
>> messages. RAISERROR...WITH NOWAIT will flush the output buffer
>> immediately, where SELECT or PRINT will wailt until the out buffer is
>> full.
>> RAISERROR('Start message', 0, 1) WITH NOWAIT
>> WAITFOR DELAY '00:00:02'
>> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
>> WAITFOR DELAY '00:00:02'
>> RAISERROR('End message', 0, 1) WITH NOWAIT
>> --
>> Hope this helps.
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:
> > also, when running sp, supposing sp is composed of complex [many]
> > subqueries, is there some clever method sp can indicate to calling process
> > which part of code is currently being executed [some kind of semaphores
> > between subqueries f.e.]
> You can use RAISERROR...WITH NOWAIT to send informational progress messages.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> > having sql2000 on win2000
> > when running long lasting queries [30 minutes f.e.], and running them
> > async, is there some method to periodicaly check the percentage of
> > completness?
> >
> > i know there is a status indicating when the query is done, but is it
> > possible to know intermediate status?
> > since execution plan has a good knowledge about what have to be done, i
> > think it is not technicaly impossible mission to have some estimation of
> > remaining time to run
> >
> > also, when running sp, supposing sp is composed of complex [many]
> > subqueries, is there some clever method sp can indicate to calling process
> > which part of code is currently being executed [some kind of semaphores
> > between subqueries f.e.]
> >
> > any comments?
> >
> > thnx.
> >
>
when running long lasting queries [30 minutes f.e.], and running them async,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphores
> between subqueries f.e.]
> any comments?
> thnx.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>> You can use RAISERROR...WITH NOWAIT to send informational progress
>> messages. RAISERROR...WITH NOWAIT will flush the output buffer
>> immediately, where SELECT or PRINT will wailt until the out buffer is
>> full.
>> RAISERROR('Start message', 0, 1) WITH NOWAIT
>> WAITFOR DELAY '00:00:02'
>> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
>> WAITFOR DELAY '00:00:02'
>> RAISERROR('End message', 0, 1) WITH NOWAIT
>> --
>> Hope this helps.
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:
> > also, when running sp, supposing sp is composed of complex [many]
> > subqueries, is there some clever method sp can indicate to calling process
> > which part of code is currently being executed [some kind of semaphores
> > between subqueries f.e.]
> You can use RAISERROR...WITH NOWAIT to send informational progress messages.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> > having sql2000 on win2000
> > when running long lasting queries [30 minutes f.e.], and running them
> > async, is there some method to periodicaly check the percentage of
> > completness?
> >
> > i know there is a status indicating when the query is done, but is it
> > possible to know intermediate status?
> > since execution plan has a good knowledge about what have to be done, i
> > think it is not technicaly impossible mission to have some estimation of
> > remaining time to run
> >
> > also, when running sp, supposing sp is composed of complex [many]
> > subqueries, is there some clever method sp can indicate to calling process
> > which part of code is currently being executed [some kind of semaphores
> > between subqueries f.e.]
> >
> > any comments?
> >
> > thnx.
> >
>
query progress indicator
having sql2000 on win2000
when running long lasting queries [30 minutes f.e.], and running them as
ync,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:
> You can use RAISERROR...WITH NOWAIT to send informational progress message
s.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
>
when running long lasting queries [30 minutes f.e.], and running them as
ync,
is there some method to periodicaly check the percentage of completness?
i know there is a status indicating when the query is done, but is it
possible to know intermediate status?
since execution plan has a good knowledge about what have to be done, i
think it is not technicaly impossible mission to have some estimation of
remaining time to run
also, when running sp, supposing sp is composed of complex [many]
subqueries, is there some clever method sp can indicate to calling process
which part of code is currently being executed [some kind of semaphores
between subqueries f.e.]
any comments?
thnx.On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + getdate()
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
getdate()
Exec my_proc2 etc...|||On Jul 31, 12:32 pm, "sali" <s...@.euroherc.hr> wrote:
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
async,
> is there some method to periodicaly check the percentage of completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
Sure.
Select 'starting complex sub query 1 ' + convert(varchar(20),getdate(),
109)
Exec my_myproc
Select 'finished complex sub query 1 and starting query 2 ' +
convert(varchar(20),getdate(),109)
Exec my_proc2
etc...|||> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
You can use RAISERROR...WITH NOWAIT to send informational progress messages.
RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
SELECT or PRINT will wailt until the out buffer is full.
RAISERROR('Start message', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
WAITFOR DELAY '00:00:02'
RAISERROR('End message', 0, 1) WITH NOWAIT
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
> having sql2000 on win2000
> when running long lasting queries [30 minutes f.e.], and running them
> async, is there some method to periodicaly check the percentage of
> completness?
> i know there is a status indicating when the query is done, but is it
> possible to know intermediate status?
> since execution plan has a good knowledge about what have to be done, i
> think it is not technicaly impossible mission to have some estimation of
> remaining time to run
> also, when running sp, supposing sp is composed of complex [many]
> subqueries, is there some clever method sp can indicate to calling process
> which part of code is currently being executed [some kind of semaphore
s
> between subqueries f.e.]
> any comments?
> thnx.
>|||"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
> You can use RAISERROR...WITH NOWAIT to send informational progress
> messages. RAISERROR...WITH NOWAIT will flush the output buffer
> immediately, where SELECT or PRINT will wailt until the out buffer is
> full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
thnx, looks good.
so, inside sp, on convenient points, to place raiseerror construct, and
later, catch error event inside client app, and filter out custom errors.
nice!
but, what with first part of problem: how to monitor query execution
progress on long lasting monolith queries?
is there some events sql may fire inside query, to tell me "now, i am on 40%
on table scan"
thnx|||> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
Sorry, but there is no query progress event mechanism built into SQL Server.
Execution plans can involve many operators that make it problematic to
predict overall progress. I've heard that some have played around with
query governor cost limit to predict elapsed time but without much success.
Hope this helps.
Dan Guzman
SQL Server MVP
"sali" <sali@.euroherc.hr> wrote in message
news:u8Ohis20HHA.5408@.TK2MSFTNGP02.phx.gbl...
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> je napisao u poruci
> interesnoj grupi:90246E14-10C1-4FAC-B7D3-ED5816594470@.microsoft.com...
>
> thnx, looks good.
> so, inside sp, on convenient points, to place raiseerror construct, and
> later, catch error event inside client app, and filter out custom errors.
> nice!
> but, what with first part of problem: how to monitor query execution
> progress on long lasting monolith queries?
> is there some events sql may fire inside query, to tell me "now, i am on
> 40% on table scan"
> thnx
>|||Another method to feedback on the progress of a multi-SQL-statement
long-running script/stored procedure is to insert current date time values
into a table, and monitor that table for progress.
Linchi
"Dan Guzman" wrote:
> You can use RAISERROR...WITH NOWAIT to send informational progress message
s.
> RAISERROR...WITH NOWAIT will flush the output buffer immediately, where
> SELECT or PRINT will wailt until the out buffer is full.
> RAISERROR('Start message', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('Progress mesage', 0, 1) WITH NOWAIT
> WAITFOR DELAY '00:00:02'
> RAISERROR('End message', 0, 1) WITH NOWAIT
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "sali" <sali@.euroherc.hr> wrote in message
> news:egrF5wz0HHA.5408@.TK2MSFTNGP02.phx.gbl...
>
Wednesday, March 7, 2012
query on data and log file
If I have to query the two filenames of a database for both data and log file, how can I do
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log file, how can I do
>> it from master database. One method I know is to use the database and call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> > If I have to query the two filenames of a database for both data and log file, how can I
> > do
> > it from master database. One method I know is to use the database and call sp_helpfile.
> > But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log
>> file, how can I do
>> it from master database. One method I know is to use the database and
>> call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file
>> of a database.
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log file, how can I do
>> it from master database. One method I know is to use the database and call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> > If I have to query the two filenames of a database for both data and log file, how can I
> > do
> > it from master database. One method I know is to use the database and call sp_helpfile.
> > But I want the query to run from the masterdb about the data and log file of a database.
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
>> If I have to query the two filenames of a database for both data and log
>> file, how can I do
>> it from master database. One method I know is to use the database and
>> call sp_helpfile.
>> But I want the query to run from the masterdb about the data and log file
>> of a database.
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
query on data and log file
If I have to query the two filenames of a database for both data and log fil
e, how can I do
it from master database. One method I know is to use the database and call s
p_helpfile.
But I want the query to run from the masterdb about the data and log file of
a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log f
ile, how can I
> do
> it from master database. One method I know is to use the database and call
sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a dat
abase.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
e, how can I do
it from master database. One method I know is to use the database and call s
p_helpfile.
But I want the query to run from the masterdb about the data and log file of
a database.
TIA"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log f
ile, how can I
> do
> it from master database. One method I know is to use the database and call
sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a dat
abase.
The database has only 1 data file and 1 log file.|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1
@.individual.net...
> The database has only 1 data file and 1 log file.
>|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
query on data and log file
If I have to query the two filenames of a database for both data and log file, how can I do
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.
|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
it from master database. One method I know is to use the database and call sp_helpfile.
But I want the query to run from the masterdb about the data and log file of a database.
TIA
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> If I have to query the two filenames of a database for both data and log file, how can I
> do
> it from master database. One method I know is to use the database and call sp_helpfile.
> But I want the query to run from the masterdb about the data and log file of a database.
The database has only 1 data file and 1 log file.
|||dbnane.dbo.sysfiles
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||Use the name of the db to call the sp.
Example:
use master
go
exec pubs..sp_helpfile
exec northwind..sp_helpfile
go
AMB
"SQL Server DBA" wrote:
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||You can query directly from master.dbo.sysaltfiles table, or run a cursor
through the sysfiles table in each database.
"SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
news:3agpefF68pc4bU1@.individual.net...
> "SQL Server DBA" <sqlsdba@.gmail.com> wrote in message
> news:3agp50F6cit2sU1@.individual.net...
> The database has only 1 data file and 1 log file.
>
|||thanks all.
Actually after posting I figured it out myself by reading the source code of
sp_helpfile :-). Geez, I should have done that before posting here.
Query on Custom source Component
Hi
in the acquireconnection method Using the below statment I can get a connection Object
oledbConnection = cmado.AcquireConnection(transaction) as OleDbConnection;
from the connection object I can get the connectionstring from the object by calling
oledbConnection.connectionstring() property which will have all the details like DataBase, UserName & other Inofrmation but there is no password Info.
How to get the password Information, I need that information since I will use that info to make OCI calls to fetch the data from the Oracle database in m,y custome source component.
any help is much appriciated
thanks in advance.
Just a guess, but look up OleDbConnection in the framework documentation, and see this-
The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.
Saying that, the password property may just be write-only, they often are for security, but I cannot find documentation for the extended properties right now.
|||so does that mean that its not possible to extract password from connection Object
|||Hi,
we are in process of developing custom source component. At design time we are using OLEDB connection to get the metadata.
we want to use the connection information given at design time by user to initialize OracleConnection of System.Data.OracleClient.
Can anybody guide us how to acheive this?
Thanks,
Anil
|||To be clear you must be using an ADO.Net OLE-DB connection, not the native OLE-DB connection otherwise you won't be able to make head nor tail of the acquired connection. With that in mind, why not just use an ADO.NET OracleClient directly and stop messing about with "converting" it?
|||Hi Darren,
Thanks for your reply. but we cant go ahead by specifying the connection type as ADO.Net: OracleClient as we are using the UI for some other component as well.
Is there any other way to do the same?
Thanks,
Anil.
|||Why do you want OleDb connections one minute and Oracle the next. They are very similar, but yet still different so masking one with the other does not make sense. Having multiple connections is not a problem. What UI? If it is your own Ui creating the OleDb connection, then why can it not create Oracle as well?|||I created a single connection, ConnectionManagerType - ADO.NET:System.Data.OleDb.OleDbConnection..., which used the "Microsoft OLE DB Provider for Oracle" underneath.
I then used the following Script Task code to display the connection string and it shows the password just fine.
Public Sub Main()
Dim conn As ConnectionManager = Dts.Connections(0)
Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)
System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)
Dts.TaskResult = Dts.Results.Success
End Sub
I cannot see the problem, password is there, is this not what you want?
|||Hi Darren
Dim oledb As OleDb.OleDbConnection = CType(conn.AcquireConnection(Nothing), OleDb.OleDbConnection)
System.Windows.Forms.MessageBox.Show(oledb.ConnectionString)
If the User Selects the Check Box "Save Password" then the password is there in the connection string Otherwise no.
Is there any way that we can get the password if the user has not select the option to save the password.
|||No.
The .NET Framework Data Provider for OLE DB does not persist or return the password in a connection string unless you set the Persist Security Info keyword to true (not recommended). To maintain a high level of security, it is strongly recommended that you use the Integrated Security keyword with Persist Security Info set to false.
Subscribe to:
Posts (Atom)