How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.
> Anyone that can help please?
> Thanks
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi
"Jnadile" wrote:
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John|||"John Bell" wrote:
> Hi
> "Jnadile" wrote:
> > Hi,
> >
> > We have got a huge SQL server 2000 box and we want to setup Multiple
> > tempdbs for a multiprocessor machine, but do not know how.
> >
> > Anyone that can help please?
> >
> > Thanks
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
Johnsql

How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks
"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.

> Anyone that can help please?
> Thanks
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi
"Jnadile" wrote:

> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John
|||"John Bell" wrote:

> Hi
> "Jnadile" wrote:
>
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
John

How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.

> Anyone that can help please?
> Thanks
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||Hi
"Jnadile" wrote:

> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/Fr...65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John|||"John Bell" wrote:
[vbcol=seagreen]
> Hi
> "Jnadile" wrote:
>
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,[/vbcol
]
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
John

How to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

http://msdn2.microsoft.com/en-us/library/ms137789.aspx

|||

Microsoft has a document published on how to install Integration Services on a cluster.

http://msdn2.microsoft.com/en-us/library/ms345193.aspx

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?

How to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

http://msdn2.microsoft.com/en-us/library/ms137789.aspx

|||

Microsoft has a document published on how to install Integration Services on a cluster.

http://msdn2.microsoft.com/en-us/library/ms345193.aspx

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?sql

How to setup integration services on a cluster

Does anyone know how to install SQL 2005 integration services on a cluster machine?

I have an a-a cluster machine. Both nodes are using SQL 2005 naming instance. During the SQL installation, I did include integration services option as one of my setup items. The setup process was running fine but I can’t connect to integration services via MS SQL Server management studio. I see below errors:

“Invalid server name "TESTServer\B123". SSIS service does not support multi-instance, use just server name instead of "server name\instance".

Looks like I must use default instance for 2005 integration services. I re-ran the setup program and added a default instance on the same cluster node. (On that node I have one default instance and one naming instance). Now I can connect to the default SQL Server but when I click on “stored packages”, I don’t see file system and MSDB. It shows nothing at all. Any idea how to make integration services running on a cluster machine?

Thanks

I had the same error when I tried to view an SSIS package on my server with 2 instances but I had no default instance, so my case was a little different than yours. The way I resolved my problem was by hard coding both instances into my XML configuration file. I did not have a cluster in this case but the principle should be the same, you'll just have to play around with it a little bit. You may also have to modify the XML file on both nodes of your cluster.

<ServerName>my-server-01\instance1</ServerName>
<ServerName>my-server-01\instance2</ServerName>

If you have a default instance and a named instance you might try something like this:

<ServerName>my-server-01</ServerName>
<ServerName>my-server-01\instance1</ServerName>

Microsoft SQL Server 2005 Integration Services (SSIS) includes a configuration file for configuring the Integration Services service.

By default, the file is located in the folder, Program Files\Microsoft SQL Server\90\DTS\Binn, and the file name is MsDtsSrvr.ini.xml.

http://msdn2.microsoft.com/en-us/library/ms137789.aspx

|||

Microsoft has a document published on how to install Integration Services on a cluster.

http://msdn2.microsoft.com/en-us/library/ms345193.aspx

|||

I have the same problem in that I have set up SSIS on the cluster using the microsoft instructions. The SSIS service starts and fails over between nodes just fine. The problem is that I cannot see the MSDB or File System under Stored Packages. I've double (tripple) checked the path to the XML in the registry, the registry path in the cluster service properties, and the XML itself. Everything seems in order.

Any ideas?

|||

I'm in exactly the same situation. Did anyone resolve this problem ?

tia

bryn64

|||Have smae problem. Resolution?

How to set up tools so that reports can be designed on client machine

Sorry if this is really simple.

We will be having a SQL 2005 DB set up which we need developers to be able to design & deploy reports on. The developers will only have remote access to this server across a network. What (&how) is the simplest way to set up the relevant tools (and which specific tools are needed) on the developers machines so that they can design, build & deploy reports to the main server ?

Thanks for any help, Will

You need to have Business Developement Studio on the clients installed, thats all.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Cool.

Thanks

How to set the location of a linkedserver?

Hey folks,
I have a WinNT4 machine with the indexing service setup on it. It works.
Also I have a W2k machine running SQL Server 2000. I'd like to add the
indexing service on the WinNT4 machine as a linkedserver to the SQL2000 DB
on the W2k machine.
If the DB as well as indexing service are on the same machine I use this:
sp_addlinkedserver CVDOC, 'Index Server', 'MSIDXS', 'CV'
From reading online I understand that as a 5th parameter you can give the
storedproc a location for the linkedserver however I have not been able to
make this work.
What should that location parameter look like? UNC? Web?
On the NT4 server my documents to index are in C:\somefolder\CV\
The catalog name is CV and points to this location.
Any help would be very much appreciated.
Thanks for reading sofar,
Vincent.
From what I understand the catalogs and services are completely different,
so you can't query a NT 4.0 IS server or its catalog from Win2k.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
<koopman@.furore.com.removethis> wrote in message
news:eLi5QiUzEHA.1264@.TK2MSFTNGP10.phx.gbl...
> Hey folks,
> I have a WinNT4 machine with the indexing service setup on it. It works.
> Also I have a W2k machine running SQL Server 2000. I'd like to add the
> indexing service on the WinNT4 machine as a linkedserver to the SQL2000 DB
> on the W2k machine.
> If the DB as well as indexing service are on the same machine I use this:
> sp_addlinkedserver CVDOC, 'Index Server', 'MSIDXS', 'CV'
> From reading online I understand that as a 5th parameter you can give the
> storedproc a location for the linkedserver however I have not been able to
> make this work.
> What should that location parameter look like? UNC? Web?
> On the NT4 server my documents to index are in C:\somefolder\CV\
> The catalog name is CV and points to this location.
> Any help would be very much appreciated.
> Thanks for reading sofar,
> Vincent.
>
|||And what if I was using another w2k server instead of NT4?
What makes you think the catalogs and services are different? Something I
wrote or is that a fact because I use an NT4 server? I don't get this part
of your message...
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eP6d5aXzEHA.2788@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> From what I understand the catalogs and services are completely different,
> so you can't query a NT 4.0 IS server or its catalog from Win2k.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> Now available for purchase at:
> http://www.nwsu.com/0974973602.html
>
> <koopman@.furore.com.removethis> wrote in message
> news:eLi5QiUzEHA.1264@.TK2MSFTNGP10.phx.gbl...
DB[vbcol=seagreen]
this:[vbcol=seagreen]
the[vbcol=seagreen]
to
>
|||Win2k to Win2k will work fine. Win2k to NT 4 or vice versa won't as the IS
server, the catalogs, and most importantly the providers are all different.
Hilary Cotter
Looking for a SQL Server replication book?
Now available for purchase at:
http://www.nwsu.com/0974973602.html
"news.micosoft.com" <koopman@.furore.com.removethis> wrote in message
news:eMAvcFYzEHA.2316@.TK2MSFTNGP15.phx.gbl...
> And what if I was using another w2k server instead of NT4?
> What makes you think the catalogs and services are different? Something I
> wrote or is that a fact because I use an NT4 server? I don't get this part
> of your message...
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eP6d5aXzEHA.2788@.TK2MSFTNGP15.phx.gbl...
> DB
> this:
> the
> to
>

How to set report server

Hey everyone

I have a requirment here. I create a report that works fine in my local machine. But I want to keep this in a server so that every body can access it. How do I do that any clues please.How to set that what are steps to do that ?Did you install SQL Server Reporting Services? This is a server component of SQL Server that does exactly what you are asking. Are you using SQL Server 2005?|||yes I install reporting services and when I run the report it works fine but I am wondering is there any walkthrough to how to see thw report in web services.|||If you have installed Reporting Services on your server, then go to http://localhost/reports and you can upload the report (.RDL file). From there others can access the report and enter the required parameters to view the report.|||Hi,
I want to upload an rdl file programmatically. Is there anyone who can help me?
Thanks.|||You need to call the SOAP API CreateReport|||thanks but I have another question.
now I need to create subscription for a report. there is a method called createsubscription. but I don't know the values of parameters. in msdn documents, it says

extensionParams[0] = new ParameterValue();
extensionParams[0].Name = "ReplyTo";
extensionParams[0].Value = "reporting@.microsoft.com";

for example.
but how do we know that the name should be "ReplyTo". Are these names discribed somewhere? if so, where? if not, what should we write for each parameter.
I should prepare aspx pages as same as the reporting server web interface. but I do not have enough info.is there a web page that has detailed info other than msdn?
thanks for your help.

How to set Client Machine name when using ADO to connect to SQL Server 2000?

When you connect to SQL Server using SQLConnection, how to set client machine name(or IP address) so that you can monitor the process on Server side using Enterprise Manager?

Whatever IP address you provided in the connection string can be used to monitor along with the user id of sql in case mixed mode authentication is there.

Please post more details..

Satya

|||

In your connection string, you can add a name/value pair like

workstation id=MYCOMPUTERNAME

and now when you runsp_who you will see MYCOMPUTERNAME for the value in thehostname column.

You can also set a value for you application like

Application Name=Your Application

within your connection string.

How to set affinity for SqlExpress ?

I am using "sp_configure" to set the affinity for SqlExpress. I am using 8-cpu machine. When i check configuration "affinity mask" is set 0 (meaning 1st cpu by default) and "affinity I/O mask" is set to 64(7th cpu). If i try to set "affinity mask" to any value other than 64(7th cpu), it thorws error and doesn't allow me to set affinity value.

Here is what i am doing.

"affinity I/O mask" is already set to 64(7th cpu).

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure "affinity mask" , 128 setting affinity 8th cpu.

RECONFIGURE

If i run above statements it gives error like SqlExpress can't run on more than one cpu.

if i set the "affinity mask" to 64, it doesn't give any error but it doesn't work as expected. Am i missing something or doing something wrong ?

SQL Server 2005 Express use only one CPU.

http://blogs.msdn.com/czhower/archive/2006/01/06/510192.aspx

|||

Agreed - SQL Express only uses one CPU. But how does one set up express to always use lets say processor 6 ?

Regards,

Avinash

|||

Are you restarting SQL Server after making the change?

Apparently SQLExpress requires that.

http://msdn2.microsoft.com/en-us/library/ms187104.aspx

|||

Yes - but I guess my troubles are different. Let me try and clarify.

By default the I/O affinity mask is set to 64 and affinity mask to 0.

Lets say I want express to use processor 7 and so I will need to update affinity mask to 64. But then there seems to be a rule that disallows both the i/o affinity and processor affinity to be set to the same processor. So the run time value may never be set to 64 even with the Configure with override option. But you cant also change the affnity on the I/O mask because sql express only supports one processor.

Seems like a catch 22 situation to me. Ofcourse I may be missing something here.

Thanks,

Avinash

|||
Does appear to be a conundrum.

Both affinity I/O mask and affinity mask should be 0 by default.
Not sure why you're getting 64 on the affinity I/O.

Are you getting any messages in the SQL Error Log?|||

Lemme check. Dont remember seeing any though.

Thanks,

Avinash

How to set affinity for SqlExpress ?

I am using "sp_configure" to set the affinity for SqlExpress. I am using 8-cpu machine. When i check configuration "affinity mask" is set 0 (meaning 1st cpu by default) and "affinity I/O mask" is set to 64(7th cpu). If i try to set "affinity mask" to any value other than 64(7th cpu), it thorws error and doesn't allow me to set affinity value.

Here is what i am doing.

"affinity I/O mask" is already set to 64(7th cpu).

sp_configure 'show advanced options', 1;

RECONFIGURE;

sp_configure "affinity mask" , 128 setting affinity 8th cpu.

RECONFIGURE

If i run above statements it gives error like SqlExpress can't run on more than one cpu.

if i set the "affinity mask" to 64, it doesn't give any error but it doesn't work as expected. Am i missing something or doing something wrong ?

SQL Server 2005 Express use only one CPU.

http://blogs.msdn.com/czhower/archive/2006/01/06/510192.aspx

|||

Agreed - SQL Express only uses one CPU. But how does one set up express to always use lets say processor 6 ?

Regards,

Avinash

|||

Are you restarting SQL Server after making the change?

Apparently SQLExpress requires that.

http://msdn2.microsoft.com/en-us/library/ms187104.aspx

|||

Yes - but I guess my troubles are different. Let me try and clarify.

By default the I/O affinity mask is set to 64 and affinity mask to 0.

Lets say I want express to use processor 7 and so I will need to update affinity mask to 64. But then there seems to be a rule that disallows both the i/o affinity and processor affinity to be set to the same processor. So the run time value may never be set to 64 even with the Configure with override option. But you cant also change the affnity on the I/O mask because sql express only supports one processor.

Seems like a catch 22 situation to me. Ofcourse I may be missing something here.

Thanks,

Avinash

|||
Does appear to be a conundrum.

Both affinity I/O mask and affinity mask should be 0 by default.
Not sure why you're getting 64 on the affinity I/O.

Are you getting any messages in the SQL Error Log?|||

Lemme check. Dont remember seeing any though.

Thanks,

Avinash