Changing computer name

Can anyone advise how to change the computer name of a PC
which is running sql client with msde.
I find if I change the computer name sql server will not
start.
Any advise would be appreciated. Thanks
The following works for the default instance:
DECLARE @.old_server_name SYSNAME
DECLARE @.new_server_name SYSNAME
SET @.old_server_name = @.@.SERVERNAME
SET @.new_server_name = <your new computer name>
EXEC sp_dropserver @.server = @.old_server_name
EXEC sp_addserver @.server = @.new_server_name , @.local = 'LOCAL'
UPDATE msdb..sysjobs
SET originating_server = @.new_server_name
WHERE originating_server = @.old_server_name
You have to restart SQL Server (MSDE), and then change the computername, for
it to take effect though.
Jacco Schalkwijk
SQL Server MVP
"Elayne" <pstjw@.hotmail.com> wrote in message
news:c37501c47a33$c0d79090$a401280a@.phx.gbl...
> Can anyone advise how to change the computer name of a PC
> which is running sql client with msde.
> I find if I change the computer name sql server will not
> start.
> Any advise would be appreciated. Thanks
|||I've never experienced the SQL Server service failing to start after the
computer name was changed. What is the error you get when starting the
service from the command line (net start mssqlserver)? Are you able to
start SQL Server as console app by running "sqlservr.exe -c" from the
command line?
Regards,
Farooq Mahmud [MS SQL Support]
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both servers
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.
See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.
|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.
|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both server
s
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

Changing Computer Name

Here's my situation. We have a production SQL server 2000 with about 18
different databases running on it. The lease is up on the server and now i
must roll everything over to the new server. What i need to do is join the
domain with the new server using a different ip address and name than the
current SQL server, get everything installed, test it, then take both servers
down, rename the new server and change it's ip address to what the old one
was, and put it into production. Does anyone know how painful this will be
and what obstacles i need to look out for? Also, if there's a step by step
guide somewhere that'd be pretty great too. The old server has windows 2000
on it, the new server will have windows 2003. Thanks.See answer to "Moving SQL Server"
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||Double Post. Answered in "Moving SQL Server".
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.|||"get everything installed"
Install SQL Server, service packs, restore the databases...
"test it"
How are you going to test it? Can you just leave the new server name in
place and change the connection string to connect to the new computer? I
suppose not, or you would not have the question. Anyway, this is the
easiest option.
"rename the new server"
When the box is restarted SQL Server will display an error message on start.
You will have to pop in the SQL Server CD and choose install. This step
will not actually install SQL Server...it will just fix up some registry
entries.
If you have jobs defined on your server you might have to update the value
as stored within the originating_server column.
SELECT originating_server FROM msdb..sysjobs
If @.@.servername is incorrect you will have to drop and add server using the
stored procedures listed below:
sp_dropserver 'OldServerName'
go
sp_addserver 'NewServerName', 'local'
--
Keith
"dimsdale_007" <dimsdale007@.discussions.microsoft.com> wrote in message
news:F61F754D-1967-4A67-BACE-136759C158D6@.microsoft.com...
> Here's my situation. We have a production SQL server 2000 with about 18
> different databases running on it. The lease is up on the server and now
i
> must roll everything over to the new server. What i need to do is join
the
> domain with the new server using a different ip address and name than the
> current SQL server, get everything installed, test it, then take both
servers
> down, rename the new server and change it's ip address to what the old one
> was, and put it into production. Does anyone know how painful this will
be
> and what obstacles i need to look out for? Also, if there's a step by
step
> guide somewhere that'd be pretty great too. The old server has windows
2000
> on it, the new server will have windows 2003. Thanks.

changing collation_name of SqlServer Database

Hi

I want to change collation_name of my SqlServer Database by running a script. But I don't know how.
Please help me.

ThanksYou should be able to do this:

ALTER DATABASE myDatabase COLLATE SQL_Latin1_General_CP1_CI_AS

This will change the default collation for any new objects added to the database. To change the collation of existing objects you will need to run an ALTER TABLE command against each table.

Terrisql

Changing backup model before running reindex

Our txn log is ballooning after we re-index. I've see a recommendataion to
change to Bulk-logged before re-indexing and I'm wandering, will this break
my backup chain? Currently we are running full backups every Sun morning
and re-indexing Mon and Thurs.
Thanks.Are you doing log backups? If not, go to simple recovery model.
If you are doing log backup, then changing to bulk-logged will produce less
records in the ldf file,
but the following log backup can potentially be larger than if in full recov
ery. It will not
adversely affect your backup strategy, but two things to remember are:
If you have performed a bulk-logged operation, then you can't restore a log
backup to point-in time.
If db is in bulk-logged mode, you won't be able to backup log if a data file
crashes.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kevin" <none@.none.com> wrote in message news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft
.com...
> Our txn log is ballooning after we re-index. I've see a recommendataion t
o change to Bulk-logged
> before re-indexing and I'm wandering, will this break my backup chain? Cu
rrently we are running
> full backups every Sun morning and re-indexing Mon and Thurs.
> Thanks.|||Addition to Tibor's comment: Take backup before and after changing your
Recovery Model to SIMPLE\BULK LOGGED for indexing operations.
Ekrem nsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:8BF808EA-D953-4467-83BD-3CDC59691BC7@.microsoft.com...
> Are you doing log backups? If not, go to simple recovery model.
> If you are doing log backup, then changing to bulk-logged will produce
> less records in the ldf file, but the following log backup can potentially
> be larger than if in full recovery. It will not adversely affect your
> backup strategy, but two things to remember are:
> If you have performed a bulk-logged operation, then you can't restore a
> log backup to point-in time.
> If db is in bulk-logged mode, you won't be able to backup log if a data
> file crashes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kevin" <none@.none.com> wrote in message
> news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
>

Changing backup model before running reindex

Our txn log is ballooning after we re-index. I've see a recommendataion to
change to Bulk-logged before re-indexing and I'm wandering, will this break
my backup chain? Currently we are running full backups every Sun morning
and re-indexing Mon and Thurs.
Thanks.Are you doing log backups? If not, go to simple recovery model.
If you are doing log backup, then changing to bulk-logged will produce less records in the ldf file,
but the following log backup can potentially be larger than if in full recovery. It will not
adversely affect your backup strategy, but two things to remember are:
If you have performed a bulk-logged operation, then you can't restore a log backup to point-in time.
If db is in bulk-logged mode, you won't be able to backup log if a data file crashes.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kevin" <none@.none.com> wrote in message news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
> Our txn log is ballooning after we re-index. I've see a recommendataion to change to Bulk-logged
> before re-indexing and I'm wandering, will this break my backup chain? Currently we are running
> full backups every Sun morning and re-indexing Mon and Thurs.
> Thanks.|||Addition to Tibor's comment: Take backup before and after changing your
Recovery Model to SIMPLE\BULK LOGGED for indexing operations.
--
Ekrem Önsoy
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:8BF808EA-D953-4467-83BD-3CDC59691BC7@.microsoft.com...
> Are you doing log backups? If not, go to simple recovery model.
> If you are doing log backup, then changing to bulk-logged will produce
> less records in the ldf file, but the following log backup can potentially
> be larger than if in full recovery. It will not adversely affect your
> backup strategy, but two things to remember are:
> If you have performed a bulk-logged operation, then you can't restore a
> log backup to point-in time.
> If db is in bulk-logged mode, you won't be able to backup log if a data
> file crashes.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kevin" <none@.none.com> wrote in message
> news:6D4D3721-2B84-4AC7-B19C-7307EC729604@.microsoft.com...
>> Our txn log is ballooning after we re-index. I've see a recommendataion
>> to change to Bulk-logged before re-indexing and I'm wandering, will this
>> break my backup chain? Currently we are running full backups every Sun
>> morning and re-indexing Mon and Thurs.
>> Thanks.
>

changing authentication error, Please HELP!

I am running SQL Server 2005 Express. I am trying to change the server
property to the mixed mode authentication (SQL Server and Windows
authentication mode) in SQL Server Management Studio. I loged in to the
server which is local in Managament Studio using windows authentication. When
I right click on the connection I get the following error:
-----
TITLE: Microsoft SQL Server Management Studio Express
--
Cannot show requested dialog.
--
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
--
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SqlManagerUI)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
--
Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
(Microsoft.SqlServer.Express.ConnectionInfo)
--
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft
SQL Server, Error: 233)
-----
All the protocols for SQLEXPRESS are enabled. I don't know what's going on.
I also checked the Surface Area Configuration and everything seems to be
turned on there. The log file contains the following.
-----
2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
2006-03-30 16:38:11.98 Logon Could not connect because the maximum
number of '1' user connections has already been reached. The system
administrator can use sp_configure to increase the maximum value. The
connection has been closed. [CLIENT: <local machine>]
-----
But I can't increase the maximum value of connections because when I right
click on connection I get the same error mentioned above. What is going on
here? Do I need to reinstall SQL Express? I can't get rid of this stupid
error. Thank you.
Ilyause command line utility to connect to express and change the setting.
e.g.
sqlcmd -S".\Express" -Q"exec sp_configure 'user connections','32767';
reconfigure with override"
--
-oj
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:D58D1B38-F886-4285-90FB-F1CADFCE9B4A@.microsoft.com...
>I am running SQL Server 2005 Express. I am trying to change the server
> property to the mixed mode authentication (SQL Server and Windows
> authentication mode) in SQL Server Management Studio. I loged in to the
> server which is local in Managament Studio using windows authentication.
> When
> I right click on the connection I get the following error:
> -----
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> --
> Failed to retrieve data for this request.
> (Microsoft.SqlServer.Express.SqlManagerUI)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> --
> Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> (Microsoft
> SQL Server, Error: 233)
> -----
> All the protocols for SQLEXPRESS are enabled. I don't know what's going
> on.
> I also checked the Surface Area Configuration and everything seems to be
> turned on there. The log file contains the following.
> -----
> 2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
> 2006-03-30 16:38:11.98 Logon Could not connect because the maximum
> number of '1' user connections has already been reached. The system
> administrator can use sp_configure to increase the maximum value. The
> connection has been closed. [CLIENT: <local machine>]
> -----
> But I can't increase the maximum value of connections because when I right
> click on connection I get the same error mentioned above. What is going on
> here? Do I need to reinstall SQL Express? I can't get rid of this stupid
> error. Thank you.
> Ilya
>

changing authentication error, Please HELP!

I am running SQL Server 2005 Express. I am trying to change the server
property to the mixed mode authentication (SQL Server and Windows
authentication mode) in SQL Server Management Studio. I loged in to the
server which is local in Managament Studio using windows authentication. When
I right click on the connection I get the following error:
-----
TITLE: Microsoft SQL Server Management Studio Express
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SqlManagerUI)
For help, click:
http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
(Microsoft.SqlServer.Express.ConnectionInfo)
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft
SQL Server, Error: 233)
-----
All the protocols for SQLEXPRESS are enabled. I don't know what's going on.
I also checked the Surface Area Configuration and everything seems to be
turned on there. The log file contains the following.
-----
2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
2006-03-30 16:38:11.98 Logon Could not connect because the maximum
number of '1' user connections has already been reached. The system
administrator can use sp_configure to increase the maximum value. The
connection has been closed. [CLIENT: <local machine>]
-----
But I can't increase the maximum value of connections because when I right
click on connection I get the same error mentioned above. What is going on
here? Do I need to reinstall SQL Express? I can't get rid of this stupid
error. Thank you.
Ilya
use command line utility to connect to express and change the setting.
e.g.
sqlcmd -S".\Express" -Q"exec sp_configure 'user connections','32767';
reconfigure with override"
-oj
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:D58D1B38-F886-4285-90FB-F1CADFCE9B4A@.microsoft.com...
>I am running SQL Server 2005 Express. I am trying to change the server
> property to the mixed mode authentication (SQL Server and Windows
> authentication mode) in SQL Server Management Studio. I loged in to the
> server which is local in Managament Studio using windows authentication.
> When
> I right click on the connection I get the following error:
> -----
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> --
> Failed to retrieve data for this request.
> (Microsoft.SqlServer.Express.SqlManagerUI)
> For help, click:
> http://go.microsoft.com/fwlink?ProdN...r&LinkId=20476
> --
> Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> (Microsoft
> SQL Server, Error: 233)
> -----
> All the protocols for SQLEXPRESS are enabled. I don't know what's going
> on.
> I also checked the Surface Area Configuration and everything seems to be
> turned on there. The log file contains the following.
> -----
> 2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
> 2006-03-30 16:38:11.98 Logon Could not connect because the maximum
> number of '1' user connections has already been reached. The system
> administrator can use sp_configure to increase the maximum value. The
> connection has been closed. [CLIENT: <local machine>]
> -----
> But I can't increase the maximum value of connections because when I right
> click on connection I get the same error mentioned above. What is going on
> here? Do I need to reinstall SQL Express? I can't get rid of this stupid
> error. Thank you.
> Ilya
>

changing authentication error, Please HELP!

I am running SQL Server 2005 Express. I am trying to change the server
property to the mixed mode authentication (SQL Server and Windows
authentication mode) in SQL Server Management Studio. I loged in to the
server which is local in Managament Studio using windows authentication. Whe
n
I right click on the connection I get the following error:
----
--
TITLE: Microsoft SQL Server Management Studio Express
--
Cannot show requested dialog.
ADDITIONAL INFORMATION:
Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
Failed to retrieve data for this request.
(Microsoft.SqlServer.Express.SqlManagerUI)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
(Microsoft.SqlServer.Express.ConnectionInfo)
A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft
SQL Server, Error: 233)
----
--
All the protocols for SQLEXPRESS are enabled. I don't know what's going on.
I also checked the Surface Area Configuration and everything seems to be
turned on there. The log file contains the following.
----
--
2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
2006-03-30 16:38:11.98 Logon Could not connect because the maximum
number of '1' user connections has already been reached. The system
administrator can use sp_configure to increase the maximum value. The
connection has been closed. [CLIENT: <local machine>]
----
--
But I can't increase the maximum value of connections because when I right
click on connection I get the same error mentioned above. What is going on
here? Do I need to reinstall SQL Express? I can't get rid of this stupid
error. Thank you.
Ilyause command line utility to connect to express and change the setting.
e.g.
sqlcmd -S".\Express" -Q"exec sp_configure 'user connections','32767';
reconfigure with override"
-oj
"Ilya" <Ilya@.discussions.microsoft.com> wrote in message
news:D58D1B38-F886-4285-90FB-F1CADFCE9B4A@.microsoft.com...
>I am running SQL Server 2005 Express. I am trying to change the server
> property to the mixed mode authentication (SQL Server and Windows
> authentication mode) in SQL Server Management Studio. I loged in to the
> server which is local in Managament Studio using windows authentication.
> When
> I right click on the connection I get the following error:
> ----
--
> TITLE: Microsoft SQL Server Management Studio Express
> --
> Cannot show requested dialog.
> --
> ADDITIONAL INFORMATION:
> Cannot show requested dialog. (Microsoft.SqlServer.Express.SqlMgmt)
> --
> Failed to retrieve data for this request.
> (Microsoft.SqlServer.Express.SqlManagerUI)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
> --
> Failed to connect to server ILYA-TEST-PC\SQLEXPRESS.
> (Microsoft.SqlServer.Express.ConnectionInfo)
> --
> A connection was successfully established with the server, but then an
> error occurred during the login process. (provider: Shared Memory
> Provider, error: 0 - No process is on the other end of the pipe.)
> (Microsoft
> SQL Server, Error: 233)
> ----
--
> All the protocols for SQLEXPRESS are enabled. I don't know what's going
> on.
> I also checked the Surface Area Configuration and everything seems to be
> turned on there. The log file contains the following.
> ----
--
> 2006-03-30 16:38:11.98 Logon Error: 17809, Severity: 20, State: 1.
> 2006-03-30 16:38:11.98 Logon Could not connect because the maximum
> number of '1' user connections has already been reached. The system
> administrator can use sp_configure to increase the maximum value. The
> connection has been closed. [CLIENT: <local machine>]
> ----
--
> But I can't increase the maximum value of connections because when I right
> click on connection I get the same error mentioned above. What is going on
> here? Do I need to reinstall SQL Express? I can't get rid of this stupid
> error. Thank you.
> Ilya
>

Changing ASP Application to use SQL Native Client

I am attempting to change an ASP application we've got to use SQL Native Client instead of MDAC and running into a problem.

If I use ODBC driver our application runs without problem, but if I try to use the OLEDB provider certain pages are throwing the following error: Item cannot be found in the collection corresponding to the requested name or ordinal.

The pages that are not working are using ADODB.Command object to execute a Stored Procedure and populate ADODB.Recordset object. The error is thrown when I try to access a field (by name or index) in the recordset. I am using recordsetname(0) or recordsetname("fieldname") to access the data in the recordset.

I have other pages that are working that use this same technique, so I must be missing something else that is different about the two ASP's.

Has anyone else run into this problem switching to the OLEDB provider?

Thanks,
Kelsey

Are you accessing columns in any of the new SQL Server 2005 data types? With ADO you must use DataTypeCompatibility=80 in your connection string. For example

Dim con As New ADODB.Connection

con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(local);" _
& "Database=AdventureWorks;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "MARS Connection=True;"
con.Open

See http://msdn2.microsoft.com/en-us/library/ms130978.aspx for more details about using SQL Native Client from ADO applications

If this doesn't help please send more details (preferably source code)

|||

Hi Chris -

We are not accessing any of the new SQL Server 2005 data types yet. We do have DataTypeCompatibility=80 in the connection string. Here is an example of the code that produces the error. "oursp" is a stored procedure that has "url" as a field in the resultset. When I run this page I get the "item can not be found in collection" error. If I change this code to use "Driver={SQL Native Client}" instead of the "Provider=SQLNCLI;" it works without any problem.

Any ideas?

Thanks,
Kelsey

--

<%
'Dim some local variables.
Dim lid, llist, rsGen, Cn, p, cmd

'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")

'Set local variables for Stored Procedure Call
lid = 3
llist = "36,37,38"

'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")

'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250

'Open connection with SQL Native Client Provider
Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=ourserver;"_
&"Database=ourdb;"_
&"UID=ouruser;"_
&"PWD=ourpass;"

'Create Command Object
set cmd = server.CreateObject("ADODB.Command")

'Set active connection
cmd.activeconnection = Cn

'Set command text to Stored Procedure name
'and command type to stored procedure
cmd.Commandtext = "oursp"
cmd.Commandtype = 4

'Create Stored Procedure parameters
set p = cmd.Parameters
p.Append cmd.CreateParameter("@.id", 3, 1)
p.Append cmd.CreateParameter("@.list", 200, 1, 255)

cmd("@.id") = lid
cmd("@.list") = llist

'Create the recordset with results of Stored Procedure call
set rsGen = cmd.Execute

'Trap and display any errors thrown when
'trying to access field in recordset
ON ERROR RESUME NEXT

response.write rsGen("url")

If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If

cn.Close
Set cn = Nothing
%>

|||

So this works when you use SQL Native Client ODBC via Kagera (aka MSDASQL the OLE DB to OBC bride which is now deprecated) - which isn't a supported scenario (see http://msdn2.microsoft.com/en-us/library/ms131035.aspx) but not when you use SQL Native Client OLE DB directly (which is supported).

The next step for me is to know if MDAC OLE DB worked as you expected or if this has only ever worked via Kagera.

One possibility is that your stored procedure is generating multiple results and this is handled differently in the two cases. If this is so, then executing

set rsGen = rsGen.NextRecordset

until you settle on the right result set would solve the problem

It might also be that for some reason no rows are being returned, so a check for BOF to detect this would help.

If you call the stored proc from SQL Management Studio you should be able to see if multiple results are being returned.

I don't think I can get much further without knowing if SQLOLEDB works OK, knowing that a row is definitely being returned and knowing more about what the stored procedure does.

|||

You are correct... there was multiple resultsets coming back from the Stored Procedure call. Doing the rsGen.NextRecordset made the error go away. When I execute the Stored Procedure in SMS it looks like a count is being returned first, then the actual result set. If I set NOCOUNT on in SMS it goes away. Is there a way I can set NOCOUNT on in my connection string, or is this a database setting I need to change?

Thank you very much for the help.

Kelsey

|||

Hi Kelsey,

When SQL Server executes a stored procedure it will send a row count to the client for each non-query statement executed. Setting NOCOUNT ON stop this.

Connection string keywords for SQL NAtive Client are here http://msdn2.microsoft.com/en-us/library/ms130822.aspx

You can't set NOCOUNT ON via the connection string, but you can execute the statement 'SET NOCOUNT ON' on the connection before calling the stored procedure. If you execute the SET statement in the stored procedure itself, its effect will be limited to execution of the stored procedure. See http://msdn2.microsoft.com/en-us/library/ms189837.aspx for reference

|||

Hi Chris,

Thanks for the info. The only thing that is hanging me up now is the fact that we have 3000 stored procedures and many more ASP's calling them. The connection string is in an include, but I don't want to call "SET NOCOUNT ON" for each connection open (page load). I could probably justify spending the time required to go through and add it to each of our Stored Procedures if there was going to be performance gain and/or would enable us to use the latest technology available.

Do you know why SQL Native Client OLE DB treats the count as a result set when MDAC and even SQL Native Client ODBC do not? Is it possible that SQL Native Client OLE DB will be updated to function the same way in the future preventing me from needing to go through our Stored Procedures and adding "SET NOCOUNT ON"?

The main reason we are trying to upgrade is that we've noticed through Profiler Traces that using SQL Native Client OLE DB (or ODBC for that matter) eliminates a large number of sp_cursor type calls that are occuring when our application is using MDAC. With SQL Native Client we've noticed that these calls are handled with a single sp_executesql instead of the many sp_cursor calls.

Thanks again for your time and help.

Kelsey

|||

Hi Kelsey,

I'd like to investigate this further, could you send me sample code that is representative of one of your stored procs that is giving problems and how it is called. If you can do this in VB6 that would be even better without being in an ASP page. If not just code fragments will be enough to get me started.

If you're not seeing sp_cursor calls where you were before, then this indicates that a default result set is being returned where previously a server cursor was used. This would be consistent with the stored proc not being eligable for use with a server cursor (to be used with a server cursor a proc has to be a single select statement or a call to another proc that is a single select statement). If a proc can't be used with a server cursor, then it may return one or more counts/default result sets.

I'd expect OLE DB in MDAC and SQL Native Client to behave consistently, and ODBC in MDAC and SQL Native Client to behave consistently with regard to handling of results. ODBC and OLE DB may not necessarily behave the same though. To get a complete picture I'll need to compare each API running the same proc on both SQL Server 2000 and SQL Server 2005. Are you comparing MDAC to SQL Native Client with the same version of SQL Server?

|||

Chris,

It appears like the sp_cursor calls are not happening in places where we are executing Stored Procedures, but rather only in places where we are opening multiple AD-HOC Queries on the ASP. Here is a sample I have created that uses the PUBS DB. If run this page using old MDAC way the second AD-HOC query on the page is opened using a cursor. If I run this page using SQL Native Client (ODBC or OLEDB) it uses sp_executesql for the second AD-HOC instead of sp_cursoropen.

I have also noticed that if I change the first AD-HOC query to open the recordset using "rsGen.Open CMD.Execute" instead of "Set rsGen = cmd.Execute" the second AD-HOC will no longer be opened using sp_cursoropen.

I'm not sure if this behavior is directly related to the problem I am having with some Stored Procedures returning the count as a seperate resultset and others not, or if it is completely seperate.

I've got a large number of ASP's that have multiple AD-HOCs on them so I really would like to switch to SQL Native Client to prevent these cursor calls from happening.

Here is the sample code for recreating the multi AD-HOC problem. I am going to see if I can work up a seperate sample that shows the Stored Procedures that are returning count as a resultset and those that are not. I will post later on today.

Thanks for all the help.

Kelsey

--

<%
'Dim some local variables.
Dim rsGen, Cn, p, cmd

Const adCmdText = &H0001
Const adOpenForwardOnly = 0
Const adUseServer = 2

'Create Recordset Object
Set rsGen = Server.CreateObject("ADODB.Recordset")

'Create Connection Object
Set Cn = Server.CreateObject ("ADODB.Connection")

'Set Timeouts
Cn.ConnectionTimeout = 250
Cn.CommandTimeout = 250

'--
'New SQL Native Client way of connecting
'--
'Open connection with SQL Native Client Provider
'Driver={SQL Native Client}

Cn.Open "Provider=SQLNCLI;"_
&"DataTypeCompatibility=80;"_
&"MultipleActiveResultSets=false;"_
&"Connection Reset=false;"_
&"Server=SPILLWAY\DEV;"_
&"Database=pubs;"_
&"UID=test_user;"_
&"MARS Connection=False;"_
&"PWD=test;"
'--

'--
'Old MDAC Way of connecting
'using System DSN
'--
'Cn.Open "pubs", "test_user", "test"
'--

DIM lzip, lfname
lzip = 84152
lfname = "Gary"

'Create Command Object
set cmd = server.CreateObject("ADODB.Command")

'Set active connection
cmd.activeconnection = Cn

'
'Test AD HOC Query CAll #1
'
qryGen = "SELECT lname, fname FROM employee WHERE fname = ?"

cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lfname", 202, 1, 20)
cmd("lfname") = lfname
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
Set rsGen = cmd.Execute
'rsGen.Open cmd.Execute
response.write rsGen("lname")

rsGen.Close

'
'Test AD HOC Query Call #2
'
qryGen = "SELECT au_lname, au_fname FROM authors WHERE zip = ?"

set cmd = server.CreateObject("ADODB.Command")

cmd.CommandText = qryGen
cmd.ActiveConnection = CN
cmd.CommandType = adcmdtext
cmd.Parameters.Append cmd.CreateParameter("lzip", 129, 1, 5)
cmd("lzip") = lzip
rsGen.CursorType = adOpenForwardOnly
rsGen.CursorLocation = adUseServer
set rsGen = cmd.Execute
'

ON ERROR RESUME NEXT

if not rsGen.EOF then
response.write rsGen("au_lname")
end if

If Err.number <> 0 then
response.write Err.Number&"-"&Err.description
Cn.Close
Set Cn = Nothing
response.end
End If

cn.Close
Set cn = Nothing
%>

|||Thanks for this Kelsey. Sample code for a stored procedure would really help to understand what's happening. I don't know of a reason why SQL Server 2005 and SQL Server 2000 should behave differently in the way you are seeing, nor why ODBC and OLE DB should behave differently, so i'd really like to reproduce your issue.|||

Hi Kelsey,

In your include, where you configure the conection, you can add this:

oConn.Execute "Set NOCOUNT ON"

You don't need change all stored. In my case it works!

Regards

Jaime

PD: This post help me to be in the right way, thanks Chris and Kelsey

|||

Hi Jamie,

I had tried this as well.

What I didn't like about this solution is that "SET NOCOUNT ON" is good for the entire DB Session, so it seemed like this would create a situation where it was being called more times then nessecary.

I am not sure exactly how the scope of a DB Session is determined, so it is possible that it doesn't last beyond the life of single page load anyway, but I wasn't sure.

Perhaps Chris could shed some more light on this.

I'm still trying to fight my way back to sample stored procedures... just haven't had the time yet.

Thanks,
Kelsey

|||

SET NOCOUNT ON will last until the connection is closed, or you execute a SET NOCOUNT OFF

I tried your earlier sample code with MDAC and SQL Native Client against SQL Server 2000 and SQL Server 2005 using OLE DB and ODBC. MDAC + ODBC uses a server cursor for the second query, in all other cases you get a default result set. A default result set will generally give the best performance, so this is what you want in most cases. I don't see much point in exploring MDAC+ODBC further since OLE DB is the preferred choice for ADO (and ODBC+ADO isn't a supported scenario for SQL Native Client, since MSDASQL is now deprecated).

Changing Activation Key

I need to change the Activation Key for a copy of SQL Server 2000 that I already have installed and up and running. I have the new key but can't figure out how to change from the old, no longer valid, key.

How can I change keys w/o dumping the DB, uninstalling, and reinstalling all of my instances?

Hi,

why do you want to change it? Normally it should be just fine if you have the license at hand if someone asks for it.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Original key was from a Microsoft Partner Program which is no longer active. MS said we need to stop using that activation key. We have a new one.

|||Hi,

http://groups.google.de/group/microsoft.public.sqlserver.server/browse_frm/thread/d9f82a4dd49eb581

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Changeing Remote ports

I have two instances of SQl 2005 running on a server. One I'm going to allow outside remote access to. But I don't want to do it on the default port. though I have it allowing remote and that seems to be working. I can seem to find where to change the default listening port.

and I scimmed the help and topics I saw. But didn't see one relevant to this question.

Open the Server Configuration Manager > Protocols for [InstanceName] > TCP/IP Properties > TCP Port > Restart the service

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
thanks - dave"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA|||Administrative Tools, Services, there you select the SQL Server service and
change the password for
the login. this applies for all services in windows, not only SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update t
he
> pw for my username for when it starts?
> --
> thanks - dave|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
thanks - dave
"David Thielen" wrote:

> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update t
he
> pw for my username for when it starts?
> --
> thanks - dave

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
--
thanks - dave"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA|||Administrative Tools, Services, there you select the SQL Server service and change the password for
the login. this applies for all services in windows, not only SQL Server.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
--
thanks - dave
"David Thielen" wrote:
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave

Changed user p/w, can't start Sql Server

Hi;
I am running Sql Server dev edition on my home system - not on a domain.
When I installed I selecgted mixed security so can have user/pw ad well as
SSPI. I told it to run the server using my login.
I changed the password for my Windows login and Sql Server will now not
start. If I change back to the old pw it will. How do I get it to update the
pw for my username for when it starts?
thanks - dave
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
the
> pw for my username for when it starts?
> --
> thanks - dave
Open up AdminTools -> Services
Find the MSSQLServer and SQLServerAgent service and update the passwords
there. Restart the services.
Rick Sawtell
MCT, MCSD, MCDBA
|||Administrative Tools, Services, there you select the SQL Server service and change the password for
the login. this applies for all services in windows, not only SQL Server.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave
|||Change the password for the MSSQLSERVER service and the SQLSERVERAGENT
service using the control panel service control applet. Next time, use
Enterprise Mangler to change the password for the service before you change
it at the Windows level.
Geoff N. Hiten
Microsoft SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave
|||Hi,
After changing the Service startup account password, you need to change that
in Control panel -- admin tools -- services.
How to do:-
1. Control Panel - Admin tools -- Services
2. Double click above the MSSQL Server service
3. In the Log on tab, change the password, Click ok and restart the
service.
Note:
Do the same for SQL Agent service.
Thanks
Hari
SQL Server MVP
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:30A6CEF8-15F4-4C42-9796-F769CCFADEF8@.microsoft.com...
> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update
> the
> pw for my username for when it starts?
> --
> thanks - dave
|||Thank you everyone. As soon as I saw that answer it was "of course - stupid
me."
thanks - dave
"David Thielen" wrote:

> Hi;
> I am running Sql Server dev edition on my home system - not on a domain.
> When I installed I selecgted mixed security so can have user/pw ad well as
> SSPI. I told it to run the server using my login.
> I changed the password for my Windows login and Sql Server will now not
> start. If I change back to the old pw it will. How do I get it to update the
> pw for my username for when it starts?
> --
> thanks - dave

Changed Snapshot Agent Schedule, but Agent still running on old on

I recently changed my snapshot agent schedule to run 1x each night. In the
Job History (SQL Server Agent), it shows to run as scheduled. However, when
I look at the Agent History, it is recording session activity every hour.
What is going on here?
Thanks,
Buddy
Hi,
Try to stop and start SQL Agent and if doesn't work, try to create a new
profile in the Snapshot agent properties/agent profiles and click to use this
new profile avoiding the default.
Regards
"Budman" wrote:

> I recently changed my snapshot agent schedule to run 1x each night. In the
> Job History (SQL Server Agent), it shows to run as scheduled. However, when
> I look at the Agent History, it is recording session activity every hour.
> What is going on here?
> Thanks,
> Buddy

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening por
t
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out wi
th
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

changed listening port - cant connect Mangement Studio ?

Hi,
I have "SQL 2005 express edition" running on 2003 standard (R2).
I used the "SQL Server Configuration Manager" to changed the listening port
from 1433 to 1722.
I do this by following these instructions:
http://msdn2.microsoft.com/en-us/library/ms177440.aspx
Under "Protocols for MSSQLSERVER"
TCP/IP
IP1 = 1722
IP2 = 1722
IPALL = 1722
(dynamic ports are blank)
Under "SQL Native Client Configuration"
TCP/IP = 1722
Internally from an other machine i can run the following from a cmd prompt
"telnet 192.168.2.6 1722" and i get a connection.
When running "SQL Server Management Studio Express" however i now cannot
connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
the following:
"Cannot connect ... an error has occurred ... maybe caused by the fact
that under default settings SQL Server does not allow remote connection".
This is a incorrect as it works on 1433 fine. (also tried connection
Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
but no banana.
Thank for any help
Scottscott wrote:
> Hi,
> I have "SQL 2005 express edition" running on 2003 standard (R2).
> I used the "SQL Server Configuration Manager" to changed the listening port
> from 1433 to 1722.
> I do this by following these instructions:
> http://msdn2.microsoft.com/en-us/library/ms177440.aspx
> Under "Protocols for MSSQLSERVER"
> TCP/IP
> IP1 = 1722
> IP2 = 1722
> IPALL = 1722
> (dynamic ports are blank)
> Under "SQL Native Client Configuration"
> TCP/IP = 1722
> Internally from an other machine i can run the following from a cmd prompt
> "telnet 192.168.2.6 1722" and i get a connection.
> When running "SQL Server Management Studio Express" however i now cannot
> connect. I enter 192.168.2.6:1722 as the connection IP and it times out with
> the following:
> "Cannot connect ... an error has occurred ... maybe caused by the fact
> that under default settings SQL Server does not allow remote connection".
> This is a incorrect as it works on 1433 fine. (also tried connection
> Management Studio as 192.168.2.6 1722 (i.e a space between ip and port)
> but no banana.
> Thank for any help
> Scott
>
Use a comma in Management Studio, like this: 192.168.2.6,1722
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||perfect, thanks
Scott

Changed a parameter to multi-value and now it doesn't work

I have a report with multiple parameters that the user can specifiy before
running the report. One of them is a field that the user can type in a value
for. It's defined as a string and allows a blank value in the event the user
does not want to enter anything. It works fine when not multi-value. But
when I change it to multi-value, the report brings back nothing when the user
does not enter any values.
The related dataset code for this field (tracking_number) is
...
WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
...
Any help would be appreciated.
StephanieRemove the multi-value property. that is not really meant for a text field,
only for instances where one might choose from multiple values of a drop down
list.
"Stephanie" wrote:
> I have a report with multiple parameters that the user can specifiy before
> running the report. One of them is a field that the user can type in a value
> for. It's defined as a string and allows a blank value in the event the user
> does not want to enter anything. It works fine when not multi-value. But
> when I change it to multi-value, the report brings back nothing when the user
> does not enter any values.
> The related dataset code for this field (tracking_number) is
> ...
> WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> ...
> Any help would be appreciated.
> Stephanie|||When I do that, I cannot add multple values for the field.
I've tried:
x,y
x y
'x','y'
Suggestions?
"Carl Henthorn" wrote:
> Remove the multi-value property. that is not really meant for a text field,
> only for instances where one might choose from multiple values of a drop down
> list.
> "Stephanie" wrote:
> > I have a report with multiple parameters that the user can specifiy before
> > running the report. One of them is a field that the user can type in a value
> > for. It's defined as a string and allows a blank value in the event the user
> > does not want to enter anything. It works fine when not multi-value. But
> > when I change it to multi-value, the report brings back nothing when the user
> > does not enter any values.
> >
> > The related dataset code for this field (tracking_number) is
> > ...
> > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > ...
> >
> > Any help would be appreciated.
> >
> > Stephanie|||Everything that a person enters into that field will be considered one
string. you have to parse out the string in the sproc to break it up into its
component pieces.
Without seeing what you are doing, I dont understand why you "cant add
multiple values for the field." tha field just wraps a single quote around
whatever is there and passes it in as the parameter.
in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
not happening for you, you may want to make things easier on you and just
have a multi-valued dropdown list. At least that way you are not hostage to
your users spelling ability.
"Stephanie" wrote:
> When I do that, I cannot add multple values for the field.
> I've tried:
> x,y
> x y
> 'x','y'
> Suggestions?
> "Carl Henthorn" wrote:
> > Remove the multi-value property. that is not really meant for a text field,
> > only for instances where one might choose from multiple values of a drop down
> > list.
> >
> > "Stephanie" wrote:
> >
> > > I have a report with multiple parameters that the user can specifiy before
> > > running the report. One of them is a field that the user can type in a value
> > > for. It's defined as a string and allows a blank value in the event the user
> > > does not want to enter anything. It works fine when not multi-value. But
> > > when I change it to multi-value, the report brings back nothing when the user
> > > does not enter any values.
> > >
> > > The related dataset code for this field (tracking_number) is
> > > ...
> > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > ...
> > >
> > > Any help would be appreciated.
> > >
> > > Stephanie|||The problem is that this is a string, not an integer. So a single quote at
the beginning and the end is not helpful. The user does not what a drop-down
list because the number of values that would be there would be very large.
They want to type in something like: CIM070524001,CIM070522002. They want to
be able to type in one or multiple values.
Can you test with a string and let me know how that goes? I just can't get
it to work.
"Carl Henthorn" wrote:
> Everything that a person enters into that field will be considered one
> string. you have to parse out the string in the sproc to break it up into its
> component pieces.
> Without seeing what you are doing, I dont understand why you "cant add
> multiple values for the field." tha field just wraps a single quote around
> whatever is there and passes it in as the parameter.
> in my test, I put in 1,2,3,4,5 into my text box. what was passed in to my
> sproc was '1,2,3,4,5' <note the addition of the single quotes>. If this is
> not happening for you, you may want to make things easier on you and just
> have a multi-valued dropdown list. At least that way you are not hostage to
> your users spelling ability.
> "Stephanie" wrote:
> > When I do that, I cannot add multple values for the field.
> >
> > I've tried:
> >
> > x,y
> > x y
> > 'x','y'
> >
> > Suggestions?
> >
> > "Carl Henthorn" wrote:
> >
> > > Remove the multi-value property. that is not really meant for a text field,
> > > only for instances where one might choose from multiple values of a drop down
> > > list.
> > >
> > > "Stephanie" wrote:
> > >
> > > > I have a report with multiple parameters that the user can specifiy before
> > > > running the report. One of them is a field that the user can type in a value
> > > > for. It's defined as a string and allows a blank value in the event the user
> > > > does not want to enter anything. It works fine when not multi-value. But
> > > > when I change it to multi-value, the report brings back nothing when the user
> > > > does not enter any values.
> > > >
> > > > The related dataset code for this field (tracking_number) is
> > > > ...
> > > > WHERE (D.name IN (@.name) OR ('-1' IN (@.name)))
> > > > AND (D.tracking_number IN (@.tracking_number) OR '' IN (@.tracking_number))
> > > > AND (D.promo_code IN (@.promo_code) or '-1' IN (@.promo_code))
> > > > ...
> > > >
> > > > Any help would be appreciated.
> > > >
> > > > Stephanie