SQL Express: is there a commandline way to set the default..

Is using the method described, e.g.

USE master
GO
CREATE ENDPOINT [CustomConnection]
STATE = STARTED
AS TCP
(LISTENER_PORT = 1500, LISTENER_IP =ALL)
FOR TSQL() ;
GOequivalent to what happens when the default port is changed using the
Configuration Manager?Does this turn off dynamic ports as well? I suppose
this means that there is no wayto set this up using the installer and
cmd-line flags?k"Rick Byham, (MSFT)"
wrote in message
> Check out the Books Online topic How to: Configure the Database Engine to
> Listen on Multiple TCP Ports.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> "Keith" wrote in message
>
>> Hello,
>>
>> I'd like to set the default static port of a named SQL Express instance
>> either at install or immediately afterward from the command line. I know
>> how to do it using the UI tools, but I want to do this invisibly from my
>> installer.
>>
>> I've experienced other apps that do it but can't find any docs or
>> examples. I half-expected to find a setup switch to go along with
>> DISABLENETWORKPROTOCOLS, like STATICPORT or something but no.
>>
>> Is there a way to do it from sqlcmd or something similar? I don't want
>> to have to install additional tools just to do this either.
>>
>> Thanks!
>>
>> Keith
>>
>
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

OK,

This morning I thought "Hey, I bet I can change the instance's registry
values for the tcp ports!" and bypass all this other stuff. I changed them
using regedit and everything seems to work fine, so I wrote some C# code to
do to test it out and

"UnauthorizedAccessException: Cannot write to Registry Key."
StackTrace = " at
System.ThrowHelper.ThrowUnauthorizedAccessException(ExceptionResource
resource)\r\n at Microsoft.Win32.RegistryKey.SetValue(String name, Object
value, RegistryValueKind valueKind)\r\n at
Microsoft.Win32.RegistryKey.SetValue(String name, Object va...

I'm not sure what to do about this since the exception contains no helpful
information.

Suggestions?

k
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

"Keith" wrote in message

> Is using the method described, e.g.
>
> USE master
> GO
> CREATE ENDPOINT [CustomConnection]
> STATE = STARTED
> AS TCP
> (LISTENER_PORT = 1500, LISTENER_IP =ALL)
> FOR TSQL() ;
> GOequivalent to what happens when the default port is changed using the
> Configuration Manager?Does this turn off dynamic ports as well? I suppose
> this means that there is no wayto set this up using the installer and
> cmd-line flags?k

OK. Tried this, and SQL Server said:

This "CREATE ENDPOINT" statement is not supported on this edition of SQL
Server."

This is against SQL Server 2005 Express, version 9.0.30.42.

Suggestions?
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

Anyone have some thoughts on this?

"Keith" wrote in message

> OK,
>
> This morning I thought "Hey, I bet I can change the instance's registry
> values for the tcp ports!" and bypass all this other stuff. I changed
> them using regedit and everything seems to work fine, so I wrote some C#
> code to do to test it out and
>
> "UnauthorizedAccessException: Cannot write to Registry Key."
> StackTrace = " at
> System.ThrowHelper.ThrowUnauthorizedAccessException(ExceptionResource
> resource)\r\n at Microsoft.Win32.RegistryKey.SetValue(String name,
> Object value, RegistryValueKind valueKind)\r\n at
> Microsoft.Win32.RegistryKey.SetValue(String name, Object va...
>
> I'm not sure what to do about this since the exception contains no helpful
> information.
>
> Suggestions?
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

MS folks, do you have any suggestions or comments on the faliure I listed?
The BOL does not say that CREATE ENDPOINT is for SQL Server 2005 only (vs.
Express) and actually lists specific limitations for Express when using some
options, implying that there should be no other issues.

k

"Keith" wrote in message

>
> "Keith" wrote in message
>
>> Is using the method described, e.g.
>>
>> USE master
>> GO
>> CREATE ENDPOINT [CustomConnection]
>> STATE = STARTED
>> AS TCP
>> (LISTENER_PORT = 1500, LISTENER_IP =ALL)
>> FOR TSQL() ;
>> GOequivalent to what happens when the default port is changed using the
>> Configuration Manager?Does this turn off dynamic ports as well? I
>> suppose this means that there is no wayto set this up using the installer
>> and cmd-line flags?k
>
> OK. Tried this, and SQL Server said:
>
> This "CREATE ENDPOINT" statement is not supported on this edition of SQL
> Server."
>
> This is against SQL Server 2005 Express, version 9.0.30.42.
>
> Suggestions?
>
>
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

I wasn't aware that there was a restriction on CREATE ENDPOINT for SQL
Server 2005 Express. I'll work on confirming that and adding the restriction
to the documentation. That won't help you much though.
I'm surprised that the registry hack didn't work. We don't support people
making direct changes to the registry and I haven't tried it, so I can't
offer advice on that.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.

"Keith" wrote in message

> MS folks, do you have any suggestions or comments on the faliure I listed?
> The BOL does not say that CREATE ENDPOINT is for SQL Server 2005 only (vs.
> Express) and actually lists specific limitations for Express when using
> some options, implying that there should be no other issues.
>
> k
>
> "Keith" wrote in message
>
>>
>> "Keith" wrote in message
>>
>>> Is using the method described, e.g.
>>>
>>> USE master
>>> GO
>>> CREATE ENDPOINT [CustomConnection]
>>> STATE = STARTED
>>> AS TCP
>>> (LISTENER_PORT = 1500, LISTENER_IP =ALL)
>>> FOR TSQL() ;
>>> GOequivalent to what happens when the default port is changed using the
>>> Configuration Manager?Does this turn off dynamic ports as well? I
>>> suppose this means that there is no wayto set this up using the
>>> installer and cmd-line flags?k
>>
>> OK. Tried this, and SQL Server said:
>>
>> This "CREATE ENDPOINT" statement is not supported on this edition of SQL
>> Server."
>>
>> This is against SQL Server 2005 Express, version 9.0.30.42.
>>
>> Suggestions?
>>
>>
>
>
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

Rick,

I appreciate your looking into this. I've continued to monkey around with
the registry approach but haven't had any luck yet.

I've also discovered issues with the following combination: SQL Server,
dynamic ports, firewall exception for sqlservr.exe, and a Virtual PC VM.

Because I haven't been able to programmatically set a static port for use
with the XP firewall, I've tried to use dynamic ports and added an app
exception as recommended by MSDN. the SQL Browser is also running and I
have a port exception (1434 TCP) for it as well. This setup works just fine
on a "real" machine, but on an XP SP2 virtual machine it doesn't. I can
locate the SQL instance using apps that use the SQL browser (like Management
studio) but I can never conect to it, even using other MS tools. I tried
changing the networking style of the VM (from bridged to NAT) and things
just got worse; SQL Browser-aware apps couldn't find the instance at all.

If I manually change to static ports (for both SQL and the firewall)
everything works as expected.

Rick Byham, (MSFT)" wrote in message

>I wasn't aware that there was a restriction on CREATE ENDPOINT for SQL
>Server 2005 Express. I'll work on confirming that and adding the
>restriction to the documentation. That won't help you much though.
> I'm surprised that the registry hack didn't work. We don't support people
> making direct changes to the registry and I haven't tried it, so I can't
> offer advice on that.
> --
> Rick Byham (MSFT)
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
 >> Stay informed about: SQL Express: is there a commandline way to set the default.. 

Msg 7878, Level 16, State 1, Line 1
This "CREATE ENDPOINT" statement is not supported on this edition of SQL Server.


From http://www.google.co.in/search?hl=en&client=firefox-a&channel=s&rls=or...ozilla:"CREATE+ENDPOINT"+statement+is+not+supported+on+this+edition+of+SQL+Server.&start=10&sa=N

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/
 >> Stay informed about: SQL Express: is there a commandline way to set the default..