link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default...b;en-us;280106
http://support.microsoft.com/default...b;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'
sql

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
--
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error message
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/default.aspx?scid=kb;en-us;280106
http://support.microsoft.com/default.aspx?scid=kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:
> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any errors.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error message
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

link to oracle error

Hi
I am connecting from SQL server 2000 to oracle 9i.
I used following command and entry in tnsnames.ora file.
I am unable to run 2 select queries parallely from SQL server to oracle.
It throws me this error message.
I am amble to insert, delete. select from one connection without any errors.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' reported an error.
[OLE/DB provider returned message: Oracle error occurred, but error mess
age
could not be retrieved from Oracle.]
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005: ].
--
Links created by
sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'Presumptively, your Oracle server is e2.
1- We/I will need to see the tnsnames.ora entry- it is possible the
parameter should be 'e2.world' or similar and not just 'e2'
2- Is your %Oracle_Home% environmental variable set
3- is the Oracle Listener looking for the 'e2' as the service name on it's
server?
--
Joseph R.P. Maloney, CSP,CCP,CDP
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'|||Hi,
Have you had a look in the Knowledge base?
http://support.microsoft.com/defaul...kb;en-us;280106
http://support.microsoft.com/defaul...kb;en-us;814398
Regards
"Mangesh Deshpande" wrote:

> Hi
> I am connecting from SQL server 2000 to oracle 9i.
> I used following command and entry in tnsnames.ora file.
> I am unable to run 2 select queries parallely from SQL server to oracle.
> It throws me this error message.
> I am amble to insert, delete. select from one connection without any error
s.
> --
> Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'MSDAORA' reported an error.
> [OLE/DB provider returned message: Oracle error occurred, but error me
ssage
> could not be retrieved from Oracle.]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initializ
e
> returned 0x80004005: ].
> --
> Links created by
> sp_addlinkedserver 'e2', 'Oracle', 'MSDAORA', 'production'
> sp_addlinkedsrvlogin 'e2', false, 'amnon', 'offshore','offtest34'

Line Break in T-SQL

Hello,

In a Stored Proc, I am building a string variable. I am getting outputs
from 4 different queries and would like the string to have line breaks
to display each entry in a different line in a text area. How can I do
this?

i.e
result = result1 + result2 + result3 + result4.
What characters can I enter so that the output is displayed in the
textarea as
result1
result2
result3
result4

Thanks,SELECT 'asdfasd'+char(13)+'ASDF ASF ASDF ASD'

maybe it's CHR not CHAR|||The line terminator on a Windows platform is carriage return/line feed
(ASCII 10 and 13). You can concatenate CHAR(13) + CHAR(10) where you want
line breaks.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<nashak@.hotmail.comwrote in message
news:1158285904.128023.10670@.e3g2000cwe.googlegrou ps.com...

Quote:

Originally Posted by

Hello,
>
In a Stored Proc, I am building a string variable. I am getting outputs
from 4 different queries and would like the string to have line breaks
to display each entry in a different line in a text area. How can I do
this?
>
i.e
result = result1 + result2 + result3 + result4.
What characters can I enter so that the output is displayed in the
textarea as
result1
result2
result3
result4
>
Thanks,
>

|||On 14 Sep 2006 19:05:04 -0700, nashak@.hotmail.com wrote:

Quote:

Originally Posted by

>Hello,
>
>In a Stored Proc, I am building a string variable. I am getting outputs
>from 4 different queries and would like the string to have line breaks
>to display each entry in a different line in a text area. How can I do
>this?

Hi Nashak,

In addition to the answers already given by Alexander and Dan, here's
another option - just use a newline character inside a string constant.

For example:

SELECT 'First line
Second line.'

This will show the output on two lines (make sure to select output to
text, not output to grid - the grid doesn't handle newlines too well).

If the data comes from columns, try

SELECT Column1 + '
' + Column2
FROM YourTable
WHERE ...

--
Hugo Kornelis, SQL Server MVP

Limiting large queries in AS2005?

Does anyone know of a way to stop large queries from running on an AS2005 instance, similar to the SQL2005 'query governor cost limit' configuration option? I want to stop users running large queries from apps like Excel, and bringing the server to a grinding halt.

Thanks

The only thing that I am aware of that comes close to this is the query timeout parameter see: http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!932.entry unfortunately it is retrospective in that it will not evaluate the cost of the query before it runs, rather if the query has been running for too long it will kill it.

limiting heavy queries

Hi
I who'd like to know if there is a best accepted practice
that can guide me to write a regulation for limiting
Users and developers running Heavy queries on the server
during working hours."Oren" <orenb70@.bezeqint.net> wrote in message
news:07d901c4b6e4$18097c10$a401280a@.phx.gbl...
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
Look up Query governer.
>|||There is a way to do this using the Query Governor configuration parameter.
You can specify a 'cost' limit for queries in where they won't run if the the
cost (based on execution plan) will exceed this value. You can't limit this
by user, but you can limit by connection using SET statement. All of this is
in Books online.
Colleen
"Oren" wrote:
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
>|||The Query Governor option seems more like a stop-gap measure and won't
really solve the problem. If your users are running queries during business
hours to get information they need to do their jobs, limiting their ability
to run those queries might hurt more than it helps... If you have queries
that are taking a long time or using a lot of server resources, there are
several things you can do to improve performance - add resources to the
server, tune your indexes, optimize queries, optimize server
configuration... Also, can't tell from your statement, but if developers
are using your production servers for development and testing, you might
look at giving them their own SQL server...
Thanks,
Michael C., MCDBA
"Oren" <orenb70@.bezeqint.net> wrote in message
news:07d901c4b6e4$18097c10$a401280a@.phx.gbl...
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
>

limiting heavy queries

Hi
I who'd like to know if there is a best accepted practice
that can guide me to write a regulation for limiting
Users and developers running Heavy queries on the server
during working hours.
"Oren" <orenb70@.bezeqint.net> wrote in message
news:07d901c4b6e4$18097c10$a401280a@.phx.gbl...
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
Look up Query governer.

>
|||There is a way to do this using the Query Governor configuration parameter.
You can specify a 'cost' limit for queries in where they won't run if the the
cost (based on execution plan) will exceed this value. You can't limit this
by user, but you can limit by connection using SET statement. All of this is
in Books online.
Colleen
"Oren" wrote:

> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
>
|||The Query Governor option seems more like a stop-gap measure and won't
really solve the problem. If your users are running queries during business
hours to get information they need to do their jobs, limiting their ability
to run those queries might hurt more than it helps... If you have queries
that are taking a long time or using a lot of server resources, there are
several things you can do to improve performance - add resources to the
server, tune your indexes, optimize queries, optimize server
configuration... Also, can't tell from your statement, but if developers
are using your production servers for development and testing, you might
look at giving them their own SQL server...
Thanks,
Michael C., MCDBA
"Oren" <orenb70@.bezeqint.net> wrote in message
news:07d901c4b6e4$18097c10$a401280a@.phx.gbl...
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
>

limiting heavy queries

Hi
I who'd like to know if there is a best accepted practice
that can guide me to write a regulation for limiting
Users and developers running Heavy queries on the server
during working hours."Oren" <orenb70@.bezeqint.net> wrote in message
news:07d901c4b6e4$18097c10$a401280a@.phx.gbl...
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
Look up Query governer.

>|||There is a way to do this using the Query Governor configuration parameter.
You can specify a 'cost' limit for queries in where they won't run if the th
e
cost (based on execution plan) will exceed this value. You can't limit this
by user, but you can limit by connection using SET statement. All of this i
s
in Books online.
Colleen
"Oren" wrote:

> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
>|||The Query Governor option seems more like a stop-gap measure and won't
really solve the problem. If your users are running queries during business
hours to get information they need to do their jobs, limiting their ability
to run those queries might hurt more than it helps... If you have queries
that are taking a long time or using a lot of server resources, there are
several things you can do to improve performance - add resources to the
server, tune your indexes, optimize queries, optimize server
configuration... Also, can't tell from your statement, but if developers
are using your production servers for development and testing, you might
look at giving them their own SQL server...
Thanks,
Michael C., MCDBA
"Oren" <orenb70@.bezeqint.net> wrote in message
news:07d901c4b6e4$18097c10$a401280a@.phx.gbl...
> Hi
> I who'd like to know if there is a best accepted practice
> that can guide me to write a regulation for limiting
> Users and developers running Heavy queries on the server
> during working hours.
>

Limiting data in Indexed Views by date

Hi everyone,
We have a fact table containing 28,000,000 rows and are experiencing
scalability issues when performing queries. It turns out that indexed
views are an excellent way to pre-calculate aggregates and improve
scalability.
The problem is that the table is part of an OLTP solution, and is
updated constantly throughout the day. However, our indexed view only
needs to include data up to yesterday (which never changes once it's
written). However, when we create an indexed view, it includes today's
data, and the overhead required to maintain the view is causing our
data insert routines to slow down by 90%.
What are our options to limit the data in the indexed view to
yesterday? Ideally, we'd like the index to automatically update at
midnight each night, without the need for replication or batch jobs.
Here are the options we know of:
1. Create indexed view without regard for the date. As stated above,
this slows down insert speeds on the table and is not feasible.
2. Create a copy of the table which contains data only up to yesterday,
and run the indexed query on this table. Run a batch process at 12:01am
every morning to copy the new rows to the new table. The table is very
large, so this will necessitate some downtime however.
Is there another simpler way to do this?On 1 Dec 2005 09:25:46 -0800, marketing@.adwarereport.com wrote:
(snip)
>What are our options to limit the data in the indexed view to
>yesterday?
(snip)
>Is there another simpler way to do this?
Hi marketing,
Have you considered creating a second database for reporting. Each
night, after the backup of the OLTP database has finished, load the
reporting database with that backup.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Limiting data in Indexed Views by date

Hi everyone,
We have a fact table containing 28,000,000 rows and are experiencing
scalability issues when performing queries. It turns out that indexed
views are an excellent way to pre-calculate aggregates and improve
scalability.
The problem is that the table is part of an OLTP solution, and is
updated constantly throughout the day. However, our indexed view only
needs to include data up to yesterday (which never changes once it's
written). However, when we create an indexed view, it includes today's
data, and the overhead required to maintain the view is causing our
data insert routines to slow down by 90%.
What are our options to limit the data in the indexed view to
yesterday? Ideally, we'd like the index to automatically update at
midnight each night, without the need for replication or batch jobs.
Here are the options we know of:
1. Create indexed view without regard for the date. As stated above,
this slows down insert speeds on the table and is not feasible.
2. Create a copy of the table which contains data only up to yesterday,
and run the indexed query on this table. Run a batch process at 12:01am
every morning to copy the new rows to the new table. The table is very
large, so this will necessitate some downtime however.
Is there another simpler way to do this?
On 1 Dec 2005 09:25:46 -0800, marketing@.adwarereport.com wrote:
(snip)
>What are our options to limit the data in the indexed view to
>yesterday?
(snip)
>Is there another simpler way to do this?
Hi marketing,
Have you considered creating a second database for reporting. Each
night, after the backup of the OLTP database has finished, load the
reporting database with that backup.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Limiting data in Indexed Views by date

Hi everyone,
We have a fact table containing 28,000,000 rows and are experiencing
scalability issues when performing queries. It turns out that indexed
views are an excellent way to pre-calculate aggregates and improve
scalability.
The problem is that the table is part of an OLTP solution, and is
updated constantly throughout the day. However, our indexed view only
needs to include data up to yesterday (which never changes once it's
written). However, when we create an indexed view, it includes today's
data, and the overhead required to maintain the view is causing our
data insert routines to slow down by 90%.
What are our options to limit the data in the indexed view to
yesterday? Ideally, we'd like the index to automatically update at
midnight each night, without the need for replication or batch jobs.
Here are the options we know of:
1. Create indexed view without regard for the date. As stated above,
this slows down insert speeds on the table and is not feasible.
2. Create a copy of the table which contains data only up to yesterday,
and run the indexed query on this table. Run a batch process at 12:01am
every morning to copy the new rows to the new table. The table is very
large, so this will necessitate some downtime however.
Is there another simpler way to do this?On 1 Dec 2005 09:25:46 -0800, marketing@.adwarereport.com wrote:
(snip)
>What are our options to limit the data in the indexed view to
>yesterday?
(snip)
>Is there another simpler way to do this?
Hi marketing,
Have you considered creating a second database for reporting. Each
night, after the backup of the OLTP database has finished, load the
reporting database with that backup.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

limit rows in join

Hi all -
I have been trying to figure out if this can be done in one query, or if I
need to break it out into multiple queries. The rows are for a report - so
subreports may be the direction I need to go.
I have an sql statement with 5 joins in it - three of the joins are no
problem - I need the fourth join (row) to get to the fifth join (table). Th
e
problem is that the fourth table will have multiple rows that will match, bu
t
I only need the latest row based on a date field in the row. Here's the par
t
that really is biting me - I need to have, in the select list for display,
two fields from the row the has the latest date, I need yet a different fiel
d
(from the same row) to join to the last table - so in essence, I need (3 plu
s
fields) the whole row which is making it difficult to use a subquery. - I
have been expirimenting with putting select statements in the select list,
select statements in the from clause, select statements in the Having clause
(of a version where I am using a group by) -- query and details below -
Select d.DA_Case_Id,
d.DA_Case_Nbr,
IsNull(S.Initials, 'not assigned') As 'Assigned DA/ADA',
IsNull(bso.name, 'unknown') As 'Last Modified By',
ds.Display_Name As 'Defendant',
bse.string_parm,
bse.raised_on
From DACase d
left join Subject S on d.Assigned_To_Id = S.Subject_Id
left join bs_relationships bs On d.da_case_id = bs.c_globject_id
left join Subject ds on bs.a_globject_id = ds.Subject_Id
join bs_events bse on d.da_case_id = bse.number_parm
join bs_objects bso on bse.subject_globjectid = bso.globject_id
Where Gateway_opt_out_flag <> 'F'
And bse.eventclass_id = 412
And bse.string_parm Like 'Enabled%'
And bs.relationship_type_id = 10
Group By DA_Case_Id, DA_Case_Nbr, S.Initials, ds.Display_Name, bso.name,
bse.string_parm, bse.raised_on
Having bse.raised_on = (Select Max(bse.raised_on))
Order by d.da_case_nbr asc, ds.display_name asc, bse.raised_on desc
The select in the having doesn't seem to do a thing - I am still getting
multiple rows from the bs_events table (the fourth join) - I need to limit
this join to only return one row - but again, I need to display two values
from this row, use another value from the row to join to the fifth table, an
d
the value that is used to get from the third table to the bs_events table is
yet another value (from the same one row) - is there a way this can be
accomplished?
cheers ~a good night sleep, fresh pair of eyes and your comments - thank you! I hav
e
it solved!
Myles
"ML" wrote:

> You know how sometimes you see an impenetrable wall in front of you, and w
hen
> you take two steps back you realize it's just a giant popsicle? :)
> For the fourth join use a derived table, based on a subquery with
> aggregation (MAX) and appropriate grouping. If you post DDL, we can help y
ou
> better.
>
> ML|||I bet the sleep did it. :)
ML|||Hello, Myles
You probably want something like this:
Select d.DA_Case_Id,
d.DA_Case_Nbr,
IsNull(S.Initials, 'not assigned') As 'Assigned DA/ADA',
IsNull(bso.name, 'unknown') As 'Last Modified By',
ds.Display_Name As 'Defendant',
y.string_parm,
y.raised_on
>From DACase d
left join Subject S on d.Assigned_To_Id = S.Subject_Id
left join bs_relationships bs On d.da_case_id = bs.c_globject_id
left join Subject ds on bs.a_globject_id = ds.Subject_Id
join (
select bse1.*
from bs_events bse1
inner join (
select bse2.number_parm,
max(bse2.raised_on) as last_raised_on
from bs_events bse2
group by bse2.number_parm
) x on bse1.number_parm=x.number_parm
and bse1.raised_on=x.last_raised_on
) y on d.da_case_id = y.number_parm
join bs_objects bso on y.subject_globjectid = bso.globject_id
Where Gateway_opt_out_flag <> 'F'
And y.eventclass_id = 412
And y.string_parm Like 'Enabled%'
And bs.relationship_type_id = 10
Order by d.da_case_nbr asc, ds.display_name asc, bse.raised_on desc
The query is untested, because you didn't provide DDL, sample data and
expected results; see: http://www.aspfaq.com/etiquette.asp?id=5006
Razvan

Limit query response time to 5 mins

Right now we have queries running from 20 ms to 200 secs or even more.. We
know that from our application perspective, we do not expect our customers
to be around if the response time is greater than say 5 mins. Is there a way
that we can control this at the db level or server level from the SQL side
vs the application layer ?
Also right now, if I have to profile and say for example run a query that
runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
batch completed event for 2 mins. Is there any way to find out that that
query was a cancelled query and did not run to completion ?
Using SQL 2KHassan
Yes, SQL Server Profiler is your friend here, but why would want to cancel
the query? Do you really want the users cancel queries?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Right now we have queries running from 20 ms to 200 secs or even more.. We
> know that from our application perspective, we do not expect our customers
> to be around if the response time is greater than say 5 mins. Is there a
> way that we can control this at the db level or server level from the SQL
> side vs the application layer ?
> Also right now, if I have to profile and say for example run a query that
> runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
> batch completed event for 2 mins. Is there any way to find out that that
> query was a cancelled query and did not run to completion ?
> Using SQL 2K
>
>|||I am just looking for a way to do so.. Not really narrowed it down to how we
would use it eventually
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23wjjDoifGHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Yes, SQL Server Profiler is your friend here, but why would want to cancel
> the query? Do you really want the users cancel queries?
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
>

Limit query response time to 5 mins

Right now we have queries running from 20 ms to 200 secs or even more.. We
know that from our application perspective, we do not expect our customers
to be around if the response time is greater than say 5 mins. Is there a way
that we can control this at the db level or server level from the SQL side
vs the application layer ?
Also right now, if I have to profile and say for example run a query that
runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
batch completed event for 2 mins. Is there any way to find out that that
query was a cancelled query and did not run to completion ?
Using SQL 2KHassan
Yes, SQL Server Profiler is your friend here, but why would want to cancel
the query? Do you really want the users cancel queries?
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
> Right now we have queries running from 20 ms to 200 secs or even more.. We
> know that from our application perspective, we do not expect our customers
> to be around if the response time is greater than say 5 mins. Is there a
> way that we can control this at the db level or server level from the SQL
> side vs the application layer ?
> Also right now, if I have to profile and say for example run a query that
> runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
> batch completed event for 2 mins. Is there any way to find out that that
> query was a cancelled query and did not run to completion ?
> Using SQL 2K
>
>|||I am just looking for a way to do so.. Not really narrowed it down to how we
would use it eventually
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23wjjDoifGHA.4568@.TK2MSFTNGP03.phx.gbl...
> Hassan
> Yes, SQL Server Profiler is your friend here, but why would want to cancel
> the query? Do you really want the users cancel queries?
>
> "Hassan" <Hassan@.hotmail.com> wrote in message
> news:%23VoNa4hfGHA.5104@.TK2MSFTNGP04.phx.gbl...
>> Right now we have queries running from 20 ms to 200 secs or even more..
>> We know that from our application perspective, we do not expect our
>> customers to be around if the response time is greater than say 5 mins.
>> Is there a way that we can control this at the db level or server level
>> from the SQL side vs the application layer ?
>> Also right now, if I have to profile and say for example run a query that
>> runs for 5 mins.. If i cancel the query in 2 mins, the profiler reports a
>> batch completed event for 2 mins. Is there any way to find out that that
>> query was a cancelled query and did not run to completion ?
>> Using SQL 2K
>>
>

limit on sp's through an odbc connection

To all the gurus,
Is there a limit on the number of queries that can be run through an
odbc connection to another SQL server?
The primary server is running abinitio to query a sql server.
When we run the set of sps that is needed the server essetially grind to
a halt...
thanx
Bill
*** Sent via Developersdex http://www.codecomments.com ***ok how about this one,
is there a limit on the number of threads that can be used on one odbc
source?
Bill
*** Sent via Developersdex http://www.codecomments.com ***|||Does this article from MSoft help you at all...
http://msdn2.microsoft.com/en-us/library/ms187024.aspx
Should help you get the Maximum out of worker threads.
thanks,
/*
Warren Brunk - MCITP - SQL 2005, MCDBA
www.techintsolutions.com
*/
"Bill" <nospam@.devdex.com> wrote in message
news:ustGql01GHA.4924@.TK2MSFTNGP05.phx.gbl...
> ok how about this one,
> is there a limit on the number of threads that can be used on one odbc
> source?
>
> Bill
>
> *** Sent via Developersdex http://www.codecomments.com ***|||Bill,
Regarding this:
>When we run the set of sps that is needed the server essetially grind to
>a halt...
My question is what diagnostics, monitoring were done to
determine that this was related to some type of limits on
connections? That would really be the starting point -
monitoring where the bottlenecks are, monitoring for
locking/blocking issues, what's going on in SQL Server when
these are run, etc. Were those done?
-Sue
On Wed, 13 Sep 2006 07:29:56 -0700, Bill <nospam@.devdex.com>
wrote:

>To all the gurus,
>Is there a limit on the number of queries that can be run through an
>odbc connection to another SQL server?
>The primary server is running abinitio to query a sql server.
>When we run the set of sps that is needed the server essetially grind to
>a halt...
>
>thanx
>Bill
>
>*** Sent via Developersdex http://www.codecomments.com ***|||Mr. Brunk,
thank you very much
this answered my question totally
BillO
*** Sent via Developersdex http://www.codecomments.com ***