change a named instance to default instance

| From: "Brian" <deeshubby76@.nospam.nospam>
| Subject: change a named instance to default instance
| Date: Wed, 22 Dec 2004 17:10:30 -0500
| Lines: 16
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1437
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441
| Message-ID: <uoZWQMH6EHA.260@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.connect
| NNTP-Posting-Host: ip68-107-195-73.cl.ri.cox.net 68.107.195.73
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED02.phx.gbl!TK2MSFT NGP08.phx.gbl!TK2MSFTNGP10
.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.sqlserver.connect:44043
| X-Tomcat-NG: microsoft.public.sqlserver.connect
|
| Somehow, on my XP box, when I installed SQL Server 2000, I have only a
named
| instance running (and no default instance).
|
| So, I have these ASP.NET applications that have "web.config" files that
are
| trying to connect to "localhost".
| My apps don't work until I change the connection string to
| "computername\instancename".. even though there is only one SQL Server
| running on my local machine.
|
| Is there anyway I can make my named instance behave like the default?
|
| Thanks,
|
| BW
|
|
|
Hello Brian,
From your post, my understanding on this issue is: You'd like to know how
to change a named instance to the default instance. If I'm off base, please
feel free to let me know.
I truly understand this inconvenience this has brought you. However, there
is no documented, supported way to change a named instance of SQL Server
2000 to the default instance. Indeed, a re-installation is the usual
approach.
You could however, if it is feasible, just go to the client computer, and
use the Client Network Configuration program on that machine to create the
alias name and map that to your named instance of SQL Server 2000.
The following article is for your reference:
INF: How to Connect to an SQL Server 2000 Named Instance with the Previous
Version's Client Tools (265808)
http://support.microsoft.com/default...B;EN-US;265808
The following steps are for your reference:
Configure an Alias by Using TCP/IP Sockets
NOTE: To configure an alias using TCP/IP, you must provide the server name
and TCP/IP port number.
1.
Determine the SQL Server instance port number.
To find the SQL Server instance port number:
a.
On the Microsoft SQL Server 2000 server, start the SQL Server Network
Utility.
b.
Click the General tab, and then select the instance you want from the
Instances drop-down menu.
c.
Highlight TCP/IP, and then click Properties. The port number for this
instance is shown. Write down this port number for use later.
2.
Configure the server alias on the client computer.
a.
Start the Client Network Utility.
b.
In the General tab of the Server Alias Configuration dialog box, click
Add, and then click TCP/IP.
c.
Enter an alias name in the Server Alias text box. The alias can contain
any name.
d.
Enter the SQL Server 2000 computer name or IP address in the Computer Name
text box. Do not add an instance name.
e.
In the Port Number text box, enter the port number you recorded in step 1.
Click OK twice.
Configure an Alias Using Named Pipes
NOTE: In order to configure an alias by using Named Pipes, you must provide
a Server name and a pipe name.
1.
Determine the server instance pipe.
a.
On the Microsoft SQL Server 2000 server, start the Server Network Utility.
b.
In the General tab, select the instance you want from the Instances
drop-down menu.
c.
Highlight Named Pipes, and then click Properties. Write down the pipe name
for use later.
2.
Configure the server alias on the client computer.
a.
Start the Client Network Utility.
b.
In the General tab of the Server Alias Configuration dialog box, click
Add, and then click Named Pipes.
c.
Enter an alias name in the Server Alias text box. The alias can contain
any name.
d.
Enter the pipe name you recorded from step 1.
e.
Make sure that you change the "." (period) to the actual SQL Server Name.
For example:
\\ComputerA\pipe\MSSQL$s2000\sql\query
Please do not add the instance name. Click OK twice.
I hope this help.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
Sophie,
Thanks for the tips, since I'm developing locally, with a local SQL Server,
I was able to use the steps below to get things working perfectly.
There was one step that was not mentioned in what you shared:
After I configured the Alias and port number for TCP/IP in the Server
Network Utility, it did not initially work until I had disabled the PIPES
protocol and restarted my SQL service. (MSSQL$instancename)
Thanks for you help!
Brian
"Sophie Guo" wrote:

> Hello Brian,
> From your post, my understanding on this issue is: You'd like to know how
> to change a named instance to the default instance. If I'm off base, please
> feel free to let me know.
> I truly understand this inconvenience this has brought you. However, there
> is no documented, supported way to change a named instance of SQL Server
> 2000 to the default instance. Indeed, a re-installation is the usual
> approach.
> You could however, if it is feasible, just go to the client computer, and
> use the Client Network Configuration program on that machine to create the
> alias name and map that to your named instance of SQL Server 2000.
> The following article is for your reference:
> INF: How to Connect to an SQL Server 2000 Named Instance with the Previous
> Version's Client Tools (265808)
> http://support.microsoft.com/default...B;EN-US;265808
> The following steps are for your reference:
> Configure an Alias by Using TCP/IP Sockets
> NOTE: To configure an alias using TCP/IP, you must provide the server name
> and TCP/IP port number.
> 1. Determine the SQL Server instance port number.
> To find the SQL Server instance port number:
> a. On the Microsoft SQL Server 2000 server, start the SQL Server Network
> Utility.
> b. Click the General tab, and then select the instance you want from the
> Instances drop-down menu.
> c. Highlight TCP/IP, and then click Properties. The port number for this
> instance is shown. Write down this port number for use later.
> 2. Configure the server alias on the client computer.
> a. Start the Client Network Utility.
> b. In the General tab of the Server Alias Configuration dialog box, click
> Add, and then click TCP/IP.
> c. Enter an alias name in the Server Alias text box. The alias can contain
> any name.
> d. Enter the SQL Server 2000 computer name or IP address in the Computer Name text box. Do not add an instance name.
> e. In the Port Number text box, enter the port number you recorded in step 1.
> Click OK twice.
>
>
> Configure an Alias Using Named Pipes
> NOTE: In order to configure an alias by using Named Pipes, you must provide
> a Server name and a pipe name.
> 1. Determine the server instance pipe.
> a. On the Microsoft SQL Server 2000 server, start the Server Network Utility.
> b. In the General tab, select the instance you want from the Instances
> drop-down menu.
> c. Highlight Named Pipes, and then click Properties. Write down the pipe name
> for use later.
> 2. Configure the server alias on the client computer.
> a. Start the Client Network Utility.
> b. In the General tab of the Server Alias Configuration dialog box, click
> Add, and then click Named Pipes.
> c. Enter an alias name in the Server Alias text box. The alias can contain
> any name.
> d. Enter the pipe name you recorded from step 1.
> e. Make sure that you change the "." (period) to the actual SQL Server Name.
> For example:
> \\ComputerA\pipe\MSSQL$s2000\sql\query
> Please do not add the instance name. Click OK twice.
> I hope this help.
> Sophie Guo
> Microsoft Online Partner Support

CDONS.mail not working

I have a procedure that sends mail using CDONTS.mail
CREATE PROCEDURE SendMail(
@.From varchar(255),
@.To varchar(255),
@.Message varchar(8000),
@.Subject varchar(255))
AS
DECLARE @.CDO int, @.OLEResult int, @.Out int
--Create CDONTS.NewMail object
EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
--Call Send method of the object
execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
@.Subject, @.Message, 1 --0 is low 1 is normal
IF @.OLEResult <> 0 PRINT 'Send'
--Destroy CDO
EXECUTE @.OLEResult = sp_OADestroy @.CDO
return @.OLEResult
It runs well in a server but fails in other server
When I executed it in QA, it displays "The command(s) completed
successfully." without sending the mail
Is that server missing any settings?
MadhivananDon't use CDONTS.NewMail, it's been deprecated and no longer ships with
Windows.
My suggestion is to set up an SMTP server and use xp_smtp_sendmail. Barring
that, use CDO.Message. See http://www.aspfaq.com/2403 for more details on
both methods (note that in the working sample, each property is set
individually, whereas you attempt to pass a bunch of properties into a
single call).
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:1136812148.799884.141170@.g49g2000cwa.googlegroups.com...
> I have a procedure that sends mail using CDONTS.mail
>
> CREATE PROCEDURE SendMail(
> @.From varchar(255),
> @.To varchar(255),
> @.Message varchar(8000),
> @.Subject varchar(255))
> AS
> DECLARE @.CDO int, @.OLEResult int, @.Out int
> --Create CDONTS.NewMail object
> EXECUTE @.OLEResult = sp_OACreate 'CDONTS.NewMail', @.CDO OUT
> IF @.OLEResult <> 0 PRINT 'CDONTS.NewMail'
>
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'BodyFormat', 0
> EXECUTE @.OLEResult = sp_OASetProperty @.CDO, 'MailFormat', 0
> --Call Send method of the object
> execute @.OLEResult = sp_OAMethod @.CDO, 'Send', Null, @.From, @.To,
> @.Subject, @.Message, 1 --0 is low 1 is normal
> IF @.OLEResult <> 0 PRINT 'Send'
> --Destroy CDO
> EXECUTE @.OLEResult = sp_OADestroy @.CDO
> return @.OLEResult
> It runs well in a server but fails in other server
> When I executed it in QA, it displays "The command(s) completed
> successfully." without sending the mail
> Is that server missing any settings?
> Madhivanan
>

Cast @[System::StartTime] Problem

This is a Subject Expression I put in my Mail component in my SSIS 2005 package but it's not liking the StartTime concatenation. I tried other ways but can't get it to allow this:

"Process Started by : " + @.[System::UserName] + (DT_DATE) @.[System::StartTime]

I get the error:

TITLE: Expression Builder

Expression cannot be evaluated.

ADDITIONAL INFORMATION:

The data types "DT_WSTR" and "DT_DATE" are incompatible for binary operator "+". The operand types could not be implicitly cast into compatible types for the operation. To perform this operation, one or both operands need to be explicitly cast with a cast operator.

Attempt to set the result type of binary operation ""Process Started by : " + @.[System::UserName] + (DT_DATE)@.[System::StartTime]" failed with error code 0xC0047080.

I tried to use CAST() but not sure if that's allowed or the syntax for it specifically in SSIS 2005

As the error message says, you cannot concatenate DT_WSTR & DT_DATE. Try:

"Process Started by : " + @.[System::UserName] + (DT_WSTR) @.[System::StartTime]

-Jamie