One ms sql server 2000 instance blocks the other (thru a dts-job)

Hello,

we use two instances of the ms sql server 2000 (Version: 8.00.760) on a
4 processor windows 2000 (sp 4) machine.
A dts-packet on one instance completly blocks the work on the other
instance. Both instances have 2 dedicated processors. The two
processors of the blocked instance are in idle states.
How is this possible? Has someone had the same behavior of the sql
server and found a solution for this problem?

Thanks in advance for answers

Jrgen Simonsen(juergen.simonsen@.web.de) writes:
> we use two instances of the ms sql server 2000 (Version: 8.00.760) on a
> 4 processor windows 2000 (sp 4) machine.
> A dts-packet on one instance completly blocks the work on the other
> instance. Both instances have 2 dedicated processors. The two
> processors of the blocked instance are in idle states.
> How is this possible? Has someone had the same behavior of the sql
> server and found a solution for this problem?

Have you tried to access that blocked instance, to see if it is possible
to run queries on it? Have you checked the SQL Server error log for the
blocked instance?

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

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

One instance or two instances

Hi,

If I have two applications which have several databases for each application, do you create one instance for two applications or you create one instance for each application? Which way is better on pricing, performance, maintanence etc?

Thanks for your input

It does not matter as far as pricing goes. You can install multiple instances on the same server once you have a single license.

If you have multiple instances on the same server, you need to think about how much memory you allocate to each instance (by setting the max memory option). In most cases you would be better off (especially from a performance and maint perspective) to only have one instance on the machine, rather than one instance per application.

Multiple instances can be more secure (from each other), and they can be at different SP levels.

|||

thanks for your help.

Where I can change the parameter for the instance on memory limitation etc?

Thanks

|||

In SQL Server 2005, you can use SQL Server Management Studio (SSMS). In Object Explorer, select the instance, right-click and select Properties. Then select Memory, and set the value for the Max server memory. You want to leave some memory for the OS.

Please mark this question as answered if this was helpful. Thanks!

One dataflow for sevral Oracle Instances ?

Hi,

I designed a dataflow which extracts, transforms and load (the main goals of an ETL ....) data from an Oracle db to an Sql Server 2005 db. The issue is that I would like to run this dataflow on several Oracle instances in a row. This intances are not on the same Oracle Servers. Is there a way to set only one data source for all these intances and to use a loop to execute the dataflow for each?.

Regards

Ayzan

You could place the Data Flow inside a For Each Loop. Select the enumerator best suited to the list of servers you have, and obviously pass this out through a variable(s) so that you can use the specifics in an expression on the Oracle connection.|||

Ok, that's what I tried to do. But how do you pass variables to the Data Source. by editing the XML source ? Do you have any example ?

Regards

Ayzan

|||

You need to pass the current loop value through to the connection, which is used inside the loop. Store it in a variable, then use an expression on the connection string property of the connection, used by the source component in the data flow. This article demonstrates some of the concepts:

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)

|||Right click on your Connection Manager and then Properties. Under the Expressions property click on the Ellipses [...].

In the Property Expressions Editor choose ConnectionString for the

property and then click on the Ellipses [...] next to the expression.

In the expression builder your expression will be similiar to the follwing

"Data Source=" + @.[User::InstanceName] + ";User

ID=MyUser;Provider=OraOLEDB.Oracle.1;Persist Security

Info=True;Password=MyPassword;"

You just need to replace the parts of the connection string that are

applicable to your environment (Instance Variable Name, UserID,

Provider, Password).

This assumes you are using the tnsnames.ora to declare the instances.

Larry Pope|||

Thank you all, that works really fine.

Regards

Ayzan

One dataflow for sevral Oracle Instances ?

Hi,

I designed a dataflow which extracts, transforms and load (the main goals of an ETL ....) data from an Oracle db to an Sql Server 2005 db. The issue is that I would like to run this dataflow on several Oracle instances in a row. This intances are not on the same Oracle Servers. Is there a way to set only one data source for all these intances and to use a loop to execute the dataflow for each?.

Regards

Ayzan

You could place the Data Flow inside a For Each Loop. Select the enumerator best suited to the list of servers you have, and obviously pass this out through a variable(s) so that you can use the specifics in an expression on the Oracle connection.|||

Ok, that's what I tried to do. But how do you pass variables to the Data Source. by editing the XML source ? Do you have any example ?

Regards

Ayzan

|||

You need to pass the current loop value through to the connection, which is used inside the loop. Store it in a variable, then use an expression on the connection string property of the connection, used by the source component in the data flow. This article demonstrates some of the concepts:

Looping over files with the Foreach Loop
(http://www.sqlis.com/default.aspx?55)

|||Right click on your Connection Manager and then Properties. Under the Expressions property click on the Ellipses [...].
In the Property Expressions Editor choose ConnectionString for the property and then click on the Ellipses [...] next to the expression.
In the expression builder your expression will be similiar to the follwing
"Data Source=" + @.[User::InstanceName] + ";User ID=MyUser;Provider=OraOLEDB.Oracle.1;Persist Security Info=True;Password=MyPassword;"
You just need to replace the parts of the connection string that are applicable to your environment (Instance Variable Name, UserID, Provider, Password).
This assumes you are using the tnsnames.ora to declare the instances.
Larry Pope
|||

Thank you all, that works really fine.

Regards

Ayzan