question about IIS Virtual Directory Management for MS SQL server 2000

Hi,

I installed the MS SQL Server 2000 trial version in my computer. The Operating System is WinXP. It seems everything is OK, the MSSQLserver is running after I restart the computer.

Then I tried to create a few virtual directories following these steps:
Choose start => programs => Microsoft SQL server => Configure SQL XML Support in IIS

What I can see is the icon " IIS virtual Directory Management for SQL Server" in the left pane, and nothing in the right pane. Is this normal?

I right clicked the icon, selected the "connect", typed in the computer name as it required. But it showed that an error occurred connecting to the computer. The reason is "this server does not exist or is unavailable". It is quite strange that actually the SQL server is running at this time.

Is there anybody who can tell me what 's wrong with it? Do I have to do some special configuration?

Thanks a lot

Johnwhen u see this icon: IIS virtual Directory Management for SQL Server

click it, at least you should see the computer where the sql server is intalled in right pane. then u can drill down to see the default web site.

if nothing ... try to connect to the computer ur got sql server installed. make sure u have installed IIS.

if still cant, dont know whether it has anything to do with the trial version. i am using personal edition, and everything is fine.|||Thanks.

I made a stupid mistake. I didn't install IIS. :-(

Question about Database Collations

I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS.

Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"

I am assuming that the temp database has one collation value and my databases has another.

Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?

Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?

I have never run across this before but this is the first time I shipped the database offshore.

Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.

Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.

First I can find no provision in the SQL Install to change the Default collation value.

My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS

The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.

I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again
|||

I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.

I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.

I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.

|||

did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?

thanks for your help

|||

Andres,

Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"

That should do it!

Question about Database Collations

I sent a couple of detached databases to a customer. When he installed his SQL Server he got database collation value of Latin1_General_CI_AS however, when he attached my databases they have a collation value of SQL_Latin1_General_CPI_CI_AS.

Now my stored procedures that use temp files are failing and a message is coming out SQL Server "Cannot resolve collation conflict for equal to operation"

I am assuming that the temp database has one collation value and my databases has another.

Q. Can anyone tell me the difference between the two collations? He is in Australia, I am in Canada - would he get a different default than I do?

Q. Can I safely alter the collation sequence of either the databases I sent or the temp database so that they match?

I have never run across this before but this is the first time I shipped the database offshore.

Thanks for any help you can give me. I am going to have to send my database to the Caribbean pretty soon and I need to know if this is going to happen there as well.

Well now that I've solved the problem I wonder what is the reasoning behind the defaults chosen by install.

First I can find no provision in the SQL Install to change the Default collation value.

My customer in Australia installed SQL Server on a new installation of Windows Server 2003 and got Latin1_General_CI_AS.
I built a brand new instance of Windows Server 2003 and when I installed SQL I got SQL_Latin1_General_CP1_CI_AS

The problem with my databases was in the stored procedures that used temporary tables - the collation values were different in the two databases and any comparisons on character fields failed.

I can work around the problem by altering all the stored procedures to include the collation clause to override the collation of tempdb. Is this the best way? I'm not sure. Is it a good practice to always include the collation clause when defining a table? I've always looked at it as kind of an annoyance -- never again
|||

I am replying to this fairly old post because others may be wondering why they get the SQL_Latin1_General_CP1_CI_AS collation.

I believe that this is the 'old fashioned' collation used by versions of SQL Server prior to 2000. It seems to be offered on upgrade, whereas Latin1_General_CI_AS is used in from scratch installations. Certainly the latter is preferred.

I'm disappointed that SQL2005 doesn't have a change collation wizard and you need to create a DMO script in order to change the collation of existing databases.

|||

did you know which were the installation parameters you choose to get SQL_Latin1_General_CP1_CI_AS?

thanks for your help

|||

Andres,

Upon installation, you need to select "Collation designator and sort order:"
Set it to "Latin1_General"
then, check the box for "Accent - sensitive"

That should do it!

Question about data access to SQL Compact DB / Server

I am used to work with relational SQL Server databases in other editions and am fairly new to SQL compact. I have installed the compact edition and successfully opened a connection and worked with DB objects in the Compact edition, using Server Management Studio.

I have 2-3 questions:
- Are there any means / drivers / data access installations that would allow me to connect to a SQL Compact database using the data access means that I am used to in other editions of SQL Server? In other words, can I install a data-access "converter" that would know how to talk to the SQL Compact databases over the usual TCP/1434 protocol (or any other port)? I am looking for this feature in order to connect to the SQL Compact the same way I connect to any other relational database. Right now, I am using Toad for SQL Server and it would be nice if I could connect to a SQL Compact.

- With other (non-Compact) editions, I can create an alias to a SQL Server and then specify the means to connect to the server. Can I create an alias to a SQL Compact database/server? I tried but unsuccessfully. This relates to my previous question- If I could create an alias, then I could (I imagine) connect to the Compact databases/server the same way I connect to any relational database using the alias.

- Are there any tutorials for people who are used to working with other (non-Compact) editions that are starting to work with Compact? Any BOL sections that explain the differences between other editions and Compact, from a management-tools/connectivity standpoint? I found some literature; wondering if there's something more detailed out there.

Thank you in advance.
Please be aware that SQL Server CE is a completely different beast from SQL Server. It is a in-process, embedded database, for use by a single user/application locally, and has options for synchronising with the full SQL Server product. Therefore, you caanot connect to SQL SErver CE over the network, but must load the engine, and open the SDF files of SQL CE directly from within your tool/app. A good starting point for SQL CE is in my opinion the book by Bill Vaughn, which you can get from here: http://www.hitchhikerguides.net/EBooks/default.aspx|||Erik,

Thank you for your reply.
Theoretically speaking, I imagine that I could load the engine over 1434 TCP. Do you know whether there are any such providers that would translate between the loading of the engine to the standard communication protocol?

I will look at the link you provided. Thank you again.
|||Revising my original question a little- is there something equivalent to the SMO that allows one to interface with SQL Compact?
|||No, you must use SQL Server Management Studio Express SP2 (free) or Visual Studio 2005 to manage SQL CE files.

Question about connection pooling

If I have two ASP.NET applications installed on the same machine, and both are using the same connection string to access SQL Server 2005. Are they share the same pool or use separated pools?

I need to be sure that how many connections would be connected to my database server. For example, the max. connection pool size is 100, if both Web app share the same pool, then there're may be maximum 100 connections to database. It they use separated pools, then connections may grow to 200.

Hi,

per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)

It means your application would have separate pools, since they are different applications and therefore have their own app domain.

Another thing is that maximum connection pool size is configurable (within connectionstring), 100 is the default maximum limit.

|||

joteke:

per documentation: "Connections are pooled per process, per application domain, per connection string and when using integrated security, per Windows identity."
Source:http://msdn2.microsoft.com/en-us/library/8xx3tyca.aspx (using Connection Pooling)

It means your application would have separate pools, since they are different applications and therefore have their own app domain.

What if my ASP.NET applications all use the same application pool? In this way, are they still have separate pools?

Thank you!

|||They don't because they have different appdomains. Every ASP.NEt application has its own AppDomain. Only connections in same application, using exactly same connection string, would use the same connection pool.|||

Ok! Now I got it.

Thank your for your help :)

Question about "Connect to the sql server 2000 installed on windows xp sp2"

Hi

I have another question now.

As the topic said, my sql server 2000 installed on windows xp sp2, the computer's name is "ADMIN" and the server 2000's name is "ADMIN".

When i startup the sql server 2000, it just listening on shared memory and name pipes, not including the "TCP/IP" . So i can not connect to the sql server using the port 1433.

Some people said that the reason was the system's firewall. I also found some supports on the websites 'www.support.microsoft.com'. It said that you can set the port 1433 or the sqlservr.exe to the execption. I also do that, but still not works.

Is there any things i forget and how can i solve the problems! Thanks for advances.

Try disabling your firewall first then connect to SQL Server. I am using SQL Server 2000 and SQL Server 2005 (instance) on my Windows XP Pro SP2 and everything works fine. Also, check you Server Network Utility and Client Network Utility. Since your client is also your server (machine), the enabled protocols in your Server Network Utility should be the same as your Client Network Utility|||

Like bass_player stated, you should check the server's network settings and verify that TCP/IP has been enabled. Otherwise, please provide the error text that you're receiving and the connection string that you used.

Il-Sung.

|||

Thanks.

I am sorry. I will describe my sql server 2000's environment now.

OS: windows xp sp2, Firewall had been shutdown

Server Network Utility : just enable the TCP/IP and use the default port 1433

Client Network Utility : also just enable the TCP/IP default port 1433

I found in the sql server's log that it just listening on shared memory.

|||

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

|||

joeydj wrote:

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

As joeydj said, i had been try like that. But it still not works. Now the problem is not on the client side, but the server side. The Sql Server 2000 do not listen on the port 1433 or others is the major problem.

Anyway, Thanks!

|||

Hi

I find another error message now.

It is "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"

Why?

|||

hi,

what version of Sql server are you using

for best performance. I recommend sql server enterprise on

windows 2000 server or windows 2003 server.

I think you computer is currently in trouble

you may have to reformat it

regards,

joey

|||

Hi, joey.

The version of my sql server 2000 is just evolution edition. Is there any problem? Can you tell me the reason?

And should i reinstall my os now?

|||Recently, i enable the multiprotocol on both the client and server side(in fact the client and server is on the same computer), i can connect to the sql server 2000 local , but i can not connect to it from another computer, because the tcp/ip doesn't listen on the port 1433 all the same.|||

I get the answer now.

First, i should update the sql server 2000 to sp4.

Second, the firewall should set an exception to the port (default is 1433).

Third, we should guarantee the sequence of the protocol on the client side. That means if your sql server just listoning on shared memory or name pipe or both of them, then you should not use TCP/IP first, otherwise you may not connect to the server.

Question about "Connect to the sql server 2000 installed on windows xp sp2"

Hi

I have another question now.

As the topic said, my sql server 2000 installed on windows xp sp2, the computer's name is "ADMIN" and the server 2000's name is "ADMIN".

When i startup the sql server 2000, it just listening on shared memory and name pipes, not including the "TCP/IP" . So i can not connect to the sql server using the port 1433.

Some people said that the reason was the system's firewall. I also found some supports on the websites 'www.support.microsoft.com'. It said that you can set the port 1433 or the sqlservr.exe to the execption. I also do that, but still not works.

Is there any things i forget and how can i solve the problems! Thanks for advances.

Try disabling your firewall first then connect to SQL Server. I am using SQL Server 2000 and SQL Server 2005 (instance) on my Windows XP Pro SP2 and everything works fine. Also, check you Server Network Utility and Client Network Utility. Since your client is also your server (machine), the enabled protocols in your Server Network Utility should be the same as your Client Network Utility|||

Like bass_player stated, you should check the server's network settings and verify that TCP/IP has been enabled. Otherwise, please provide the error text that you're receiving and the connection string that you used.

Il-Sung.

|||

Thanks.

I am sorry. I will describe my sql server 2000's environment now.

OS: windows xp sp2, Firewall had been shutdown

Server Network Utility : just enable the TCP/IP and use the default port 1433

Client Network Utility : also just enable the TCP/IP default port 1433

I found in the sql server's log that it just listening on shared memory.

|||

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

|||

joeydj wrote:

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

As joeydj said, i had been try like that. But it still not works. Now the problem is not on the client side, but the server side. The Sql Server 2000 do not listen on the port 1433 or others is the major problem.

Anyway, Thanks!

|||

Hi

I find another error message now.

It is "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"

Why?

|||

hi,

what version of Sql server are you using

for best performance. I recommend sql server enterprise on

windows 2000 server or windows 2003 server.

I think you computer is currently in trouble

you may have to reformat it

regards,

joey

|||

Hi, joey.

The version of my sql server 2000 is just evolution edition. Is there any problem? Can you tell me the reason?

And should i reinstall my os now?

|||Recently, i enable the multiprotocol on both the client and server side(in fact the client and server is on the same computer), i can connect to the sql server 2000 local , but i can not connect to it from another computer, because the tcp/ip doesn't listen on the port 1433 all the same.|||

I get the answer now.

First, i should update the sql server 2000 to sp4.

Second, the firewall should set an exception to the port (default is 1433).

Third, we should guarantee the sequence of the protocol on the client side. That means if your sql server just listoning on shared memory or name pipe or both of them, then you should not use TCP/IP first, otherwise you may not connect to the server.

Question about "Connect to the sql server 2000 installed on windows xp sp2"

Hi

I have another question now.

As the topic said, my sql server 2000 installed on windows xp sp2, the computer's name is "ADMIN" and the server 2000's name is "ADMIN".

When i startup the sql server 2000, it just listening on shared memory and name pipes, not including the "TCP/IP" . So i can not connect to the sql server using the port 1433.

Some people said that the reason was the system's firewall. I also found some supports on the websites 'www.support.microsoft.com'. It said that you can set the port 1433 or the sqlservr.exe to the execption. I also do that, but still not works.

Is there any things i forget and how can i solve the problems! Thanks for advances.

Try disabling your firewall first then connect to SQL Server. I am using SQL Server 2000 and SQL Server 2005 (instance) on my Windows XP Pro SP2 and everything works fine. Also, check you Server Network Utility and Client Network Utility. Since your client is also your server (machine), the enabled protocols in your Server Network Utility should be the same as your Client Network Utility|||

Like bass_player stated, you should check the server's network settings and verify that TCP/IP has been enabled. Otherwise, please provide the error text that you're receiving and the connection string that you used.

Il-Sung.

|||

Thanks.

I am sorry. I will describe my sql server 2000's environment now.

OS: windows xp sp2, Firewall had been shutdown

Server Network Utility : just enable the TCP/IP and use the default port 1433

Client Network Utility : also just enable the TCP/IP default port 1433

I found in the sql server's log that it just listening on shared memory.

|||

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

|||

joeydj wrote:

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

As joeydj said, i had been try like that. But it still not works. Now the problem is not on the client side, but the server side. The Sql Server 2000 do not listen on the port 1433 or others is the major problem.

Anyway, Thanks!

|||

Hi

I find another error message now.

It is "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"

Why?

|||

hi,

what version of Sql server are you using

for best performance. I recommend sql server enterprise on

windows 2000 server or windows 2003 server.

I think you computer is currently in trouble

you may have to reformat it

regards,

joey

|||

Hi, joey.

The version of my sql server 2000 is just evolution edition. Is there any problem? Can you tell me the reason?

And should i reinstall my os now?

|||Recently, i enable the multiprotocol on both the client and server side(in fact the client and server is on the same computer), i can connect to the sql server 2000 local , but i can not connect to it from another computer, because the tcp/ip doesn't listen on the port 1433 all the same.|||

I get the answer now.

First, i should update the sql server 2000 to sp4.

Second, the firewall should set an exception to the port (default is 1433).

Third, we should guarantee the sequence of the protocol on the client side. That means if your sql server just listoning on shared memory or name pipe or both of them, then you should not use TCP/IP first, otherwise you may not connect to the server.

Question about "Connect to the sql server 2000 installed on windows xp sp2"

Hi

I have another question now.

As the topic said, my sql server 2000 installed on windows xp sp2, the computer's name is "ADMIN" and the server 2000's name is "ADMIN".

When i startup the sql server 2000, it just listening on shared memory and name pipes, not including the "TCP/IP" . So i can not connect to the sql server using the port 1433.

Some people said that the reason was the system's firewall. I also found some supports on the websites 'www.support.microsoft.com'. It said that you can set the port 1433 or the sqlservr.exe to the execption. I also do that, but still not works.

Is there any things i forget and how can i solve the problems! Thanks for advances.

Try disabling your firewall first then connect to SQL Server. I am using SQL Server 2000 and SQL Server 2005 (instance) on my Windows XP Pro SP2 and everything works fine. Also, check you Server Network Utility and Client Network Utility. Since your client is also your server (machine), the enabled protocols in your Server Network Utility should be the same as your Client Network Utility|||

Like bass_player stated, you should check the server's network settings and verify that TCP/IP has been enabled. Otherwise, please provide the error text that you're receiving and the connection string that you used.

Il-Sung.

|||

Thanks.

I am sorry. I will describe my sql server 2000's environment now.

OS: windows xp sp2, Firewall had been shutdown

Server Network Utility : just enable the TCP/IP and use the default port 1433

Client Network Utility : also just enable the TCP/IP default port 1433

I found in the sql server's log that it just listening on shared memory.

|||

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

|||

joeydj wrote:

hi

try forcing a connection from the client using tcp/ip.

from the client open QA or EM

connect using (tcp\ip)\instance_name

such as (192.168.0.1)\mysqlinstance.

dont forget to chcek everything from your client network utility

and server netwrok utility which are found in

start>programs>microsoft sql server 2000> client network utility or server netwrok utility

regards,

joey

As joeydj said, i had been try like that. But it still not works. Now the problem is not on the client side, but the server side. The Sql Server 2000 do not listen on the port 1433 or others is the major problem.

Anyway, Thanks!

|||

Hi

I find another error message now.

It is "Failed to obtain TransactionDispenserInterface: Result Code = 0x8004d01b"

Why?

|||

hi,

what version of Sql server are you using

for best performance. I recommend sql server enterprise on

windows 2000 server or windows 2003 server.

I think you computer is currently in trouble

you may have to reformat it

regards,

joey

|||

Hi, joey.

The version of my sql server 2000 is just evolution edition. Is there any problem? Can you tell me the reason?

And should i reinstall my os now?

|||Recently, i enable the multiprotocol on both the client and server side(in fact the client and server is on the same computer), i can connect to the sql server 2000 local , but i can not connect to it from another computer, because the tcp/ip doesn't listen on the port 1433 all the same.|||

I get the answer now.

First, i should update the sql server 2000 to sp4.

Second, the firewall should set an exception to the port (default is 1433).

Third, we should guarantee the sequence of the protocol on the client side. That means if your sql server just listoning on shared memory or name pipe or both of them, then you should not use TCP/IP first, otherwise you may not connect to the server.