Link to Oracel database

Hi all
I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:listener could not resolve SERVICE_NAME given in connect description"
I have read article 280106 (troubleshooting) that didn't help
I appreciate any guidance
RayThe Net Assistant doesn't always generate correct
tnsnames.ora and listener.ora files. You most likely have
something missing in one of these files.
Make sure the Oracle home is specified correctly and that
you have a SID_LIST_LISTENER entry which specifies the sid
that the listener is listening for.
-Sue
On Wed, 9 Jun 2004 12:46:10 -0700, Ray
<anonymous@.discussions.microsoft.com> wrote:
>Hi all,
>I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:listener could not resolve SERVICE_NAME given in connect description".
>I have read article 280106 (troubleshooting) that didn't help.
>I appreciate any guidance.
>Ray

Link to Oracel database

Hi all,
I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:l
istener could not resolve SERVICE_NAME given in connect description".
I have read article 280106 (troubleshooting) that didn't help.
I appreciate any guidance.
Ray
The Net Assistant doesn't always generate correct
tnsnames.ora and listener.ora files. You most likely have
something missing in one of these files.
Make sure the Oracle home is specified correctly and that
you have a SID_LIST_LISTENER entry which specifies the sid
that the listener is listening for.
-Sue
On Wed, 9 Jun 2004 12:46:10 -0700, Ray
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I am running SQL Server 2000 Enterprise Edition and I want to create a link server to an oracle database. I have installed Oracle client on the server, but when try to test the connection using Oracle Net Configuration Assistant, I'm getting error: "TNS:
listener could not resolve SERVICE_NAME given in connect description".
>I have read article 280106 (troubleshooting) that didn't help.
>I appreciate any guidance.
>Ray

Link to Oracel database

Hi all,
I am running SQL Server 2000 Enterprise Edition and I want to create a link
server to an oracle database. I have installed Oracle client on the server,
but when try to test the connection using Oracle Net Configuration Assistant
, I'm getting error: "TNS:l
istener could not resolve SERVICE_NAME given in connect description".
I have read article 280106 (troubleshooting) that didn't help.
I appreciate any guidance.
RayThe Net Assistant doesn't always generate correct
tnsnames.ora and listener.ora files. You most likely have
something missing in one of these files.
Make sure the Oracle home is specified correctly and that
you have a SID_LIST_LISTENER entry which specifies the sid
that the listener is listening for.
-Sue
On Wed, 9 Jun 2004 12:46:10 -0700, Ray
<anonymous@.discussions.microsoft.com> wrote:

>Hi all,
>I am running SQL Server 2000 Enterprise Edition and I want to create a link server
to an oracle database. I have installed Oracle client on the server, but when try to
test the connection using Oracle Net Configuration Assistant, I'm getting error: "T
NS:
listener could not resolve SERVICE_NAME given in connect description".
>I have read article 280106 (troubleshooting) that didn't help.
>I appreciate any guidance.
>Ray

Link through 6.5 server to 2000

Is there a way to setup a link through a 6.5 server to a SQL2k database?

Here is our problem: we have a product running on Sun Solaris that connects to a 6.5 database. We upgraded the database to 2k, nad the product can no longer connect - knowledbase for the product suggests setting up a link on 6.5 server to reference teh 2k box (fooling the product into thinking it is still talking to 6.5). Unfortunately, they give no instruction on how to do this. Any ideas?

Craig DolsWhat's the product?

Been a long time since 6.5, but I didn't think (what, again!) that 6.5 had linked servers...

Or it's just that I never needed/ used them...|||The product is Datastage - I know very little about it, I'm just the SQL geek. From what the sysadm is telling me, the version tehy ahve installed does not support SQL2k - and it will be at least a month to get it upgraded - so I need to find a quick temporary fix.|||Never heard of it...

Check out this...

http://www.tools4datastage.com/|||Thanks for the link - I don't see what i need there, but maybe they can point me towards something.

Danke!|||Originally posted by Brett Kaiser
Never heard of it...

Check out this...

http://www.tools4datastage.com/

Datastage usese ODBC connections to get data from MS SQL server to and from.. ODBC is MS SQL native driver. Set up two odbc connections on the DATASTAGE server. Then in DS you can pump data back and forth applying all the rulles that you need. The ds website posted above is awsome and will get you all the help you need.
Jim

Link Server to MySQL Database

Hi

I ma trying to create a linked server to a MySQL database via ODBC. I am running SQL 2005 on Windows 2003 R2 (64-bit). Can any one point me in the right direction .

Any ideas|||MSDASQL (provider shipped with SS2005 which serves as OLEDB-ODBC bridge) is not supported in 64-bit. Check if there is an 64-bit OLEDB provider for MySQL that you can use directly.|||

Thanks for that i will look into it.

|||

Hi James,

Were you able to do that? I am having the trouble doing the same. If you have done, could you please post the procedure.

Thanks,

Neelesh:)

|||Unfortunatly I have not had much time to look at it. I have not managed to find a free / cheap OLE MYSQL driver. Sorry I was not much help|||Can you guys post your email? I can send you a documentation for creating linked servers with MySQL|||

My email address is jamesholmes@.greggs.co.uk

Thanks

|||

Have you tried email me ?

Thanks

Link Server to MySQL Database

Hi

I ma trying to create a linked server to a MySQL database via ODBC. I am running SQL 2005 on Windows 2003 R2 (64-bit). Can any one point me in the right direction .

Any ideas|||MSDASQL (provider shipped with SS2005 which serves as OLEDB-ODBC bridge) is not supported in 64-bit. Check if there is an 64-bit OLEDB provider for MySQL that you can use directly.|||

Thanks for that i will look into it.

|||

Hi James,

Were you able to do that? I am having the trouble doing the same. If you have done, could you please post the procedure.

Thanks,

Neelesh:)

|||Unfortunatly I have not had much time to look at it. I have not managed to find a free / cheap OLE MYSQL driver. Sorry I was not much help|||Can you guys post your email? I can send you a documentation for creating linked servers with MySQL|||

My email address is jamesholmes@.greggs.co.uk

Thanks

|||

Have you tried email me ?

Thanks

Link server not working thru Windows authentication - "Login failed

I've two SQL server 2005 servers running under the same domain and the
SQL server service is also running under domain adminstrator for both
the servers. The OS on both the servers is Win2K3.
All the developers were working on these servers as 'sa' but recently
we have changed it to run on windows authentications. In doing so I
added all the developer's domain logins to a local group "Power Users"
on both the servers and now every body can log on to these servers
using their domain logins(windows authentication). But the link server
from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
run a distributed querry from QA.
I've also tried adding individual logins on the Security tab on link
server gui and checked impersonate check box also but with no results.
On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
sysadmin as server role.
One strange thing is if I directly logon (using my login) to the serv1
and run a distributed querry using the same link server it works.
Any help will be appriciated
Thanks,
SGsaboo,
I believe that you have a delegation problem here. Is Kerberos running in
your domain and are both servers correctly set up to user Kerboros? Read
this for an explanation:
http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx
When you directly login to serv1 you are now only one hop away from serv2
and delegation is not an issue. But, from a desktop to serv2 is two hops.
RLF
"saboo" <subodh97@.gmail.com> wrote in message
news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
> I've two SQL server 2005 servers running under the same domain and the
> SQL server service is also running under domain adminstrator for both
> the servers. The OS on both the servers is Win2K3.
> All the developers were working on these servers as 'sa' but recently
> we have changed it to run on windows authentications. In doing so I
> added all the developer's domain logins to a local group "Power Users"
> on both the servers and now every body can log on to these servers
> using their domain logins(windows authentication). But the link server
> from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> run a distributed querry from QA.
> I've also tried adding individual logins on the Security tab on link
> server gui and checked impersonate check box also but with no results.
> On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> sysadmin as server role.
> One strange thing is if I directly logon (using my login) to the serv1
> and run a distributed querry using the same link server it works.
> Any help will be appriciated
> Thanks,
> SG|||On Feb 13, 2:40=A0pm, "Russell Fields" <russellfie...@.nomail.com> wrote:
> saboo,
> I believe that you have a delegation problem here. =A0Is Kerberos running =in
> your domain and are both servers correctly set up to user Kerboros? =A0Rea=d
> this for an explanation:http://blogs.msdn.com/sql_protocols/archive/2006/0=
8/10/694657.aspx
> When you directly login to serv1 you are now only one hop away from serv2
> and delegation is not an issue. =A0But, from a desktop to serv2 is two hop=s.
> RLF
> "saboo" <subod...@.gmail.com> wrote in message
> news:c210f136-752a-445a-94cf-b05599286972@.j20g2000hsi.googlegroups.com...
>
> > I've two SQL server 2005 servers running under the same domain and the
> > SQL server service is also running under domain adminstrator for both
> > the servers. The OS on both the servers is Win2K3.
> > All the developers were working on these servers as 'sa' but recently
> > we have changed it to run on windows authentications. In doing so I
> > added all the developer's domain logins to a local group "Power Users"
> > on both the servers and now every body can log on to these servers
> > using their domain logins(windows authentication). But the link server
> > from serv1 to serv2 is giving error "Msg 18456, Level 14, State 1,
> > Line 1" "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'." if I
> > run a distributed querry from QA.
> > I've also tried adding individual logins on the Security tab on link
> > server gui and checked impersonate check box also but with no results.
> > On both the servers "NT AUTHORITY\NETWORK SERVICE" account exists with
> > sysadmin as server role.
> > One strange thing is if I directly logon (using my login) to the serv1
> > and run a distributed querry using the same link server it works.
> > Any help will be appriciated
> > Thanks,
> > SG- Hide quoted text -
> - Show quoted text -
Delegation isn't too bad to deal with. Most likely your SQL servers
are not using a Local Service account to run (good for you, this is
not good security) and the accounts it's using are not domain admins
(again, good job). Here's the quick down-low on what you need to do:
You need domain admin rights to create an SPN, if you don't have them
you need to get your DA to do this for you. Download the Windows
Server tools for your flavor of server(http://go.microsoft.com/fwlink/?
LinkId=3D100114). You want the SetSPN tool. You need to create an SPN
for each of the SQL servers, this can be done from your workstation as
it's set Domain wide, not at the server level.
setspn -a service/name hostname
i.e.
setspn -a MSSQLsvc/my.server.name:1433 mydomain\MySqlServiceAccount
Again, do this for BOTH sql servers...sql

Link

Hello. Have a cross-server query that we use a NOLOCK so
nobody complains about locks on their server. ANyway,
this works ok on SQL 7, but running the exact same code on
SQL 2000 I get the error below as though you can't do a
NOLOCK now when using a Linked Server under SQL 2000'
Any ideas on that or a better way to enure that we do as
few locks as possible on that other server we're pulling
data from? Thanks, Bruce
select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
Server: Msg 7377, Level 16, State 1, Procedure PROC_NAME
Cannot specify an index or locking hint for a remote data
source.HI,
Create stored procedure on Linked server:
CREATE PROCEDURE Test
AS
select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
----
-
Change the select statement to following:
EXECUTE [Linked Server].[Database].[Owner].[Test]
JBandi|||Bruce,
If you create the sp on the foreign system, you would NOT have to use the
4part name in the sp.. Only use the 2-part name in the sp, and use the
4-part name to call it from your primary server.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:102a501c414e2$f34ab360$a001280a@.phx
.gbl...
> Hello. Have a cross-server query that we use a NOLOCK so
> nobody complains about locks on their server. ANyway,
> this works ok on SQL 7, but running the exact same code on
> SQL 2000 I get the error below as though you can't do a
> NOLOCK now when using a Linked Server under SQL 2000'
> Any ideas on that or a better way to enure that we do as
> few locks as possible on that other server we're pulling
> data from? Thanks, Bruce
> select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
> Server: Msg 7377, Level 16, State 1, Procedure PROC_NAME
> Cannot specify an index or locking hint for a remote data
> source.|||Thanks. I was actually looking for a way to NOT put any
SP objects in the database we're accessing (political
thing!)... Also, the code wa write with direct join in
these cross-server links. THe NOLOCK option works in SQL
7, but it looks like with SQL 2000 you cannot say NOLOCK
on a remote linked server query?
Is there any other way to directly access data in the
other database without SP's ON that other database, so you
cause as few locks as possible on that database?
Thanks, Bruce

>--Original Message--
>Bruce,
>If you create the sp on the foreign system, you would NOT
have to use the
>4part name in the sp.. Only use the 2-part name in the
sp, and use the
>4-part name to call it from your primary server.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> news:102a501c414e2$f34ab360$a001280a@.phx
.gbl...
so
on
data
>
>.
>|||Thanks. Yes, we could do that but there are issue with
anyone putting any objects in "their" database (yea, even
though we can ACCESS their database!?!)... Looking for a
NOLOCK type of situation where we can retrieve from their
database... THanks, Bruce

>--Original Message--
>HI,
>Create stored procedure on Linked server:
>CREATE PROCEDURE Test
>AS
>select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
>----
--
>
>Change the select statement to following:
>EXECUTE [Linked Server].[Database].[Owner].[Test]
>JBandi
>.
>|||You can try a pass through query, maybe that would do it..
ie
select * from openquery(linkedservername, select col1, col2 FROM
DB.dbo.TABLE WITH (NOLOCK))
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:14f7401c4158b$d6ebc6b0$a501280a@.phx
.gbl...
> Thanks. I was actually looking for a way to NOT put any
> SP objects in the database we're accessing (political
> thing!)... Also, the code wa write with direct join in
> these cross-server links. THe NOLOCK option works in SQL
> 7, but it looks like with SQL 2000 you cannot say NOLOCK
> on a remote linked server query?
> Is there any other way to directly access data in the
> other database without SP's ON that other database, so you
> cause as few locks as possible on that database?
> Thanks, Bruce
>
> have to use the
> sp, and use the
> Charlotte, NC
> (PASS) and it's
> so
> on
> data

linefeeds in text fields extracted with substring

Im running a query like
select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
MyColumn is th the text type.
The text in MyColumn contains a lot of tabulator characters (TAB),
carrige return (CR) and linefeed (LF) characters.
Sometimes (but not often) when I run the query, even in the Query
Analyser, I get the wrong result. I get too few characters, even though
I know they are in the database. (If I read the field with out the
substring, I can see everything).
The error is consistent - either a record works as it should, or it
fails to.
All answers are very welcome.
Thomas K
select @.@.version
----
----
----
--
-
Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
(1 row(s) affected)I think you did not handle the Tab, CR and Linefeed. The records that
dont have these will work otherwise fail
Madhivanan|||Check your 'maximum characters per column' setting in Query Analyzer under
Tools --> Options --> Results. This may be set too small and truncate the
value.
Hope this helps.
Dan Guzman
SQL Server MVP
"Thomas Holme" <seiofecco@.hotmail.com> wrote in message
news:ONdDTThJFHA.1096@.tk2msftngp13.phx.gbl...
> Im running a query like
> select * from MyTable where SUBSTRING(MyColumn,1,300)='searchtext'
> MyColumn is th the text type.
> The text in MyColumn contains a lot of tabulator characters (TAB), carrige
> return (CR) and linefeed (LF) characters.
> Sometimes (but not often) when I run the query, even in the Query
> Analyser, I get the wrong result. I get too few characters, even though I
> know they are in the database. (If I read the field with out the
> substring, I can see everything).
> The error is consistent - either a record works as it should, or it fails
> to.
>
> All answers are very welcome.
> Thomas K
>
> select @.@.version
> ----
----
----
--
--
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
> Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation
> Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
> (1 row(s) affected)sql

Lineage ID Errors

I am running an SSIS package and I keep getting this error when it gets to a the dataflow task. I have a bit of an idea of what it means, but I can't figure out how to fix it. IF anybody could explain this error and fixes for it that would be of immense help. Thank you.

Error: 0xC004701C at Load Server Security, DTS.Pipeline: input column "Server" (1434) has lineage ID 1421 that was not previously used in the Data Flow task.
Error: 0xC004706B at Load Server Security, DTS.Pipeline: "component "OLE DB Destination 1" (1420)" failed validation and returned validation status "VS_NEEDSNEWMETADATA".
Error: 0xC004700C at Load Server Security, DTS.Pipeline: One or more component failed validation.
Error: 0xC0024107 at Load Server Security: There were errors during task validation.The first error simply says that you have a column in the data flow, Server, that isn't used.

The second error looks to me like there were database changes to that table and now the OLE DB destination needs to be updated. Double click on the OLE DB destination and select the mappings tab. If no changes need to be made, simply click OK.|||

Possible reason could be if your destination table structure or the column data type changed.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=102011&SiteID=1

Thanks

|||You should have a triangle with an exclaimation mark on it. Double click on that component and then mappings, if applicable, to fix it.

limiting result set from MS SQL Server Query

Hello,

I am running SQL Server 2000. I would like to know whether
Microsoft Transact-SQL has a method for limiting the result
set from a query in a way analogous to MySQL's LIMIT keyword,
so that, for instance, if the result set contains 10,000 rows,
then only the first 10 rows from the record set are output.

Thank you,

Best Regards,

Neilnzanella@.cs.mun.ca wrote:
> Hello,
> I am running SQL Server 2000. I would like to know whether
> Microsoft Transact-SQL has a method for limiting the result
> set from a query in a way analogous to MySQL's LIMIT keyword,
> so that, for instance, if the result set contains 10,000 rows,
> then only the first 10 rows from the record set are output.

Yes. You need to use the TOP clause.

In your example:

SELECT TOP 10 col1, col2, col3 FROM TABLE|||(nzanella@.cs.mun.ca) writes:
> I am running SQL Server 2000. I would like to know whether
> Microsoft Transact-SQL has a method for limiting the result
> set from a query in a way analogous to MySQL's LIMIT keyword,
> so that, for instance, if the result set contains 10,000 rows,
> then only the first 10 rows from the record set are output.

As Andrew said you can use TOP. For it to be meaningful, you need in
most cases also use an ORDER BY clause.

If you want to implement paging, there are a couple of options. One is
to use a @.last_key variable, and to:

SELECT TOP 10 ... FROM tbl WHERE keycol = @.last_key ORDER BY keycol

To implement arbitrary jumps, you can do something like:

CREATE TABLE #temp (ident int IDENTITY,
...
)

and then

INSERT #temp (...)
SELECT ...

And to get rows 41 to 60

SELECT ... FROM #temp WHERE ident BETWEEN 41 AND 60

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

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 a users resource ie CPU, disk and/or memory

We are currently using Great Plains. This accounting package runs on SQL Server and we have performance problems running integration manager. The result is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K.
TIA
No, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA

Limiting a users resource ie CPU, disk and/or memory

We are currently using Great Plains. This accounting package runs on SQL Se
rver and we have performance problems running integration manager. The resu
lt is that users can not get work done when integration manager is running.
Is there any way I can limit the user running integration manager to a certa
in amount of CPU time and allocate the rest to the other users?
In Oracle, this is easy - I could just use resource plans, but I don't know
if there is a way to do this with SQL Server 2K.
TIANo, SQL Server does not support the ability to limit the amount of =
resources an application or a connections uses.
--=20
Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =
news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =
SQL Server and we have performance problems running integration manager. =
The result is that users can not get work done when integration manager =
is running.
>=20
> Is there any way I can limit the user running integration manager to a =
certain amount of CPU time and allocate the rest to the other users?
>=20
> In Oracle, this is easy - I could just use resource plans, but I don't =
know if there is a way to do this with SQL Server 2K.
>=20
> TIA

Limiting a users resource ie CPU, disk and/or memory

We are currently using Great Plains. This accounting package runs on SQL Server and we have performance problems running integration manager. The result is that users can not get work done when integration manager is running
Is there any way I can limit the user running integration manager to a certain amount of CPU time and allocate the rest to the other users
In Oracle, this is easy - I could just use resource plans, but I don't know if there is a way to do this with SQL Server 2K
TIANo, SQL Server does not support the ability to limit the amount of =resources an application or a connections uses.
-- Keith
"ramick" <anonymous@.discussions.microsoft.com> wrote in message =news:9327DE01-32DA-47B7-917F-E2513C31CB5C@.microsoft.com...
> We are currently using Great Plains. This accounting package runs on =SQL Server and we have performance problems running integration manager. = The result is that users can not get work done when integration manager =is running.
> > Is there any way I can limit the user running integration manager to a =certain amount of CPU time and allocate the rest to the other users?
> > In Oracle, this is easy - I could just use resource plans, but I don't =know if there is a way to do this with SQL Server 2K.
> > TIA

limited # of db's?

Running SQL 2000 Enterprise Edition. Is there a limit to the number of
databases one installaion can support, other than disk space/hardware
limitations?
32,767 per instance. In BOL under 'maximum capacity specifications' there is
a list of this type of data.
Rgds,
Paul Ibison (SQL Server MVP)

limited # of db's?

Running SQL 2000 Enterprise Edition. Is there a limit to the number of
databases one installaion can support, other than disk space/hardware
limitations?32,767 per instance. In BOL under 'maximum capacity specifications' there is
a list of this type of data.
Rgds,
Paul Ibison (SQL Server MVP)