Change Edition of existing isntance?

I have a named instance of SQL Server 2005 Developer Edition. I want to install VS Team System Foundation Server on the same box, but this needs a default instance of Standard or Enterprise edition. I installed the Standard version alongside the existing Developer instance but now I can't start SSIS and without it VSTS Foundation Server won't install.

I have tried to uninstall SSIS and reisntall it from both the Standard Edition and Developer edition media, but it makes no difference, the service still won't start.

Could the SSIS failure be due to having different editions on the same box? If so, is it possible to change the edition of the Developer instance to Standard (or to change them both to Enterprise edition) without having to uninstall and start all over again. The developer instance is used by several team members, so I don't want to have to uninstall it if I can help.

Any ideas anyone?

No. I should not be something like that as different SQL Server 2005 SKUs(Editions) can be installed side by side successfully provided that they have unique instance names. What kind of processor architecture of your machine? If you install 32-bit and 64-bit SQL Server 2005 on an x64 machines side by side, which is also supported, then it is possible that only one version of SSIS works at one time.|||Both isntances are 32-bit. Only differnece is that named instance is Developer Edition and default instance is Standard Edition
|||

Having two different editions (Developer and Standard) on the same box should not cause SSIS to be unable to start. Do the event logs have any messages? When you installed the second instance, did you change any of the logon service accounts?

Thanks,
Sam Lester (MSFT)

|||

Here is a lengthy thread on SSIS not starting. Can you read through it and see if it applies to your situation?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=375216&SiteID=1

Thanks,
Sam

sql

Change Edition of existing isntance?

I have a named instance of SQL Server 2005 Developer Edition. I want to install VS Team System Foundation Server on the same box, but this needs a default instance of Standard or Enterprise edition. I installed the Standard version alongside the existing Developer instance but now I can't start SSIS and without it VSTS Foundation Server won't install.

I have tried to uninstall SSIS and reisntall it from both the Standard Edition and Developer edition media, but it makes no difference, the service still won't start.

Could the SSIS failure be due to having different editions on the same box? If so, is it possible to change the edition of the Developer instance to Standard (or to change them both to Enterprise edition) without having to uninstall and start all over again. The developer instance is used by several team members, so I don't want to have to uninstall it if I can help.

Any ideas anyone?

No. I should not be something like that as different SQL Server 2005 SKUs(Editions) can be installed side by side successfully provided that they have unique instance names. What kind of processor architecture of your machine? If you install 32-bit and 64-bit SQL Server 2005 on an x64 machines side by side, which is also supported, then it is possible that only one version of SSIS works at one time.|||Both isntances are 32-bit. Only differnece is that named instance is Developer Edition and default instance is Standard Edition|||

Having two different editions (Developer and Standard) on the same box should not cause SSIS to be unable to start. Do the event logs have any messages? When you installed the second instance, did you change any of the logon service accounts?

Thanks,
Sam Lester (MSFT)

|||

Here is a lengthy thread on SSIS not starting. Can you read through it and see if it applies to your situation?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=375216&SiteID=1

Thanks,
Sam

Change default to named instance?

I have an app that connects to the default instance of msde. After some
conflicts with other app's msdes, I now want to use a named instance. This
is no problem from within the app, but can I change the default msde
instance to a named instance without losing the any data, and without
reinstalling msde?
Hi,
No you cant do directly.
1. Take a backup of all databases (Including system databases)
2. Stop SQL Server and copy the MDF and LDF to a safe place
3. Un Install the MSDE
4. Install MSDE and same service pack as old with same folder structure
5. Stop sql server
6. COpy the MDF and LDF (taken in step 2) to the same folder as old
7. Restart SQL server service
8. Execute the below comamnds in OSQL to change the server name
sp_dropserver <oldserver>
go
sp_addserver <new named server>,local
Note:
If these steps fail (Step 6 and 7) then use the database backup taken in
step-1 to restore all the databases.
Thanks
Hari
MCDBA
"Robbs" <rms14can@.hotmail.com> wrote in message
news:e1kT4FGOEHA.3312@.tk2msftngp13.phx.gbl...
> I have an app that connects to the default instance of msde. After some
> conflicts with other app's msdes, I now want to use a named instance. This
> is no problem from within the app, but can I change the default msde
> instance to a named instance without losing the any data, and without
> reinstalling msde?
>
|||Hi Robbs,
You can not change a default installation to a named instance.
You will have to reinstall MSDE as a named instance.
You will not loose data. Just make sure that you backup the database.
HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.
|||Thanks everyone!! Thats what I figured I would have to do; just wnated to
make sure there was no easier way.
Thnx Robb
"Ashish Ruparel [MSFT]" <v-ashrup@.online.microsoft.com> wrote in message
news:9k5UMmJOEHA.2692@.cpmsftngxa10.phx.gbl...
> Hi Robbs,
> You can not change a default installation to a named instance.
> You will have to reinstall MSDE as a named instance.
> You will not loose data. Just make sure that you backup the database.
> HTH
> Ashish
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>

change Datasource at runtime

Hi

Is it possible to change the datasource dynamically in reporting services.

For example if I have a dataset named DataSet1 whose datasource1 connects to server1, how can I change the same DataSet1 to use datasource2 to points to a different server. I would like to know if this datasource change is possible out side the dataset (dynamically).
Thanks

Hi pzmrcd
I've had the same problem. I found that it was not possible without re-rendering the SRS code(XML). It is not possible in SRS since you can't use an expression to change the dataset. Also the field names of datasets may differ.

change a named instance to the default instance

We have a SQL 7 that I want to upgrade to SQL 2000 but
the setup wizard does not allow it to do so. The only
option that I'm allowed is to create a named instance.
If I do this and copy all of the databases to the SQL
2000 named instance, can I uninstall SQL 7 and change the
named instance to the default instance?You cannot make a named instance to a default or change name of an instance.
You have do use backup/restore or detach/attach.
Some of the below articles *might* be of help:
Moving SQL Server Databases
http://www.support.microsoft.com/?id=224071
Moving Databases between Servers
http://www.support.microsoft.com/?id=314546
Using WITH MOVE in a Restore to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map users to the correct login
http://www.dbmaint.com/SyncSqlLogins.asp
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message
news:047801c3aa2a$3cd905f0$a101280a@.phx.gbl...
> We have a SQL 7 that I want to upgrade to SQL 2000 but
> the setup wizard does not allow it to do so. The only
> option that I'm allowed is to create a named instance.
> If I do this and copy all of the databases to the SQL
> 2000 named instance, can I uninstall SQL 7 and change the
> named instance to the default instance?|||Hi Jim,
Thank you for using MSDN Newsgroup! It's my pleasure to assist you with
your issue.
First of all, I would like to confirm my understanding of your issue.
From your description, I understand that you'd like to upgrade SQL 7 to SQL
2000. However, I'm unsure of the meaning "The only option that I'm allowed
is to create a named instance." When upgrading, SQL Server 7.0
automatically becomes the default instance of SQL Server 2000.
As we known, you can overwrite the existing installation of SQL Server 7.0
by installing a default instance of SQL Server 2000. You can also keep the
installation of SQL Server 7.0 intact by installing a named instance of SQL
Server 2000. Both operations are performed using the following procedure.
To upgrade SQL Server 7.0 to SQL Server 2000
1. Insert the Microsoft SQL Server 2000 compact disc for the edition to
which you want to upgrade into your CD-ROM drive. If the compact disc does
not autorun, double-click Autorun.exe in the root directory of the compact
disc.
2. Select SQL Server 2000 Components, select Install Database Server, and
then setup prepares the SQL Server Installation Wizard. At the Welcome
screen, click Next.
3. In Computer Name dialog box, Local Computer is the default option and
the local computer name appears in the edit box. Click Next.
4. In the Installation Selection dialog box, click Upgrade, remove, or add
components to an existing instance of SQL Server, and then click Next.
5. In the Instance Name dialog box, Default will be selected. Click Next.
# Note # When upgrading, SQL Server 7.0 automatically becomes the
default instance of SQL Server 2000.
6. In the Existing Installation dialog box, click Upgrade your existing
installation, and then click Next.
7. In the Upgrade dialog box, you are prompted as to whether you want to
proceed with the requested upgrade. Click Yes, upgrade my <text specific to
the upgrade> to start the upgrade process, and then click Next. The upgrade
runs until finished.
8. In the Connect to Server dialog box, select an authentication mode, and
then click Next.
If you are not sure which mode to use, accept the default: The Windows
account information I use to log on to my computer with (Windows).
9. In Start Copying Files dialog box, click Next.
10. In the Setup Complete dialog box, click Yes, I want to restart my
computer now, and then click Finish.
Additionally, instead of a full upgrade from SQL Server 7.0 to SQL Server
2000, which overwrites SQL Server 7.0, you can upgrade SQL Server 7.0
databases and associated meta data. To upgrade a SQL Server 7.0 database to
a SQL Server 2000 database, please use the following procedure:
1. Expand a server group, and then expand a server.
2. Right-click the server, point to All Tasks, and then click Copy Database
Wizard.
3. Complete the steps in the wizard.
After upgrading databases from SQL Server 7.0, run sp_updatestats (update
statistics) against the database on the destination server to ensure
optimal performance of the copied database.
Jim, does this answer your question? Please feel free to let me know if
this help solves your problem. If there is anything more I can still
assist you with, please feel free to post it in the group.
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||You are correct in that I want to upgrade my SQL 7 to SQL
2000. I followed the steps you have below But when I get
to step 5 the wizard comes up with an empty instance text
box and will not allow me to use the word Default. It
says that there is an error in the current SQL 7 install
that the Client tools or the registry may have been
modified and only gives me the option to install a named
instance.
I need the upgrade to be the default instance because
there is a large number of programs that call the
database with the default instance. I am thinking that I
might be able to detach all of the databases then
uninstall SQL 7 and install SQL 2000 and finish up by
reattahing the databases.
I am conserned that I will lose scheduled jobs,
maintenace programs and DTS packages by doing the detach
attach method. Is there a way to ensute that I can bring
these other objects to the new SQ: 2000?
>--Original Message--
>Hi Jim,
>Thank you for using MSDN Newsgroup! It's my pleasure to
assist you with
>your issue.
>First of all, I would like to confirm my understanding
of your issue.
>From your description, I understand that you'd like to
upgrade SQL 7 to SQL
>2000. However, I'm unsure of the meaning "The only
option that I'm allowed
>is to create a named instance." When upgrading, SQL
Server 7.0
>automatically becomes the default instance of SQL Server
2000.
>As we known, you can overwrite the existing installation
of SQL Server 7.0
>by installing a default instance of SQL Server 2000. You
can also keep the
>installation of SQL Server 7.0 intact by installing a
named instance of SQL
>Server 2000. Both operations are performed using the
following procedure.
>To upgrade SQL Server 7.0 to SQL Server 2000
>1. Insert the Microsoft SQL Server 2000 compact disc
for the edition to
>which you want to upgrade into your CD-ROM drive. If the
compact disc does
>not autorun, double-click Autorun.exe in the root
directory of the compact
>disc.
>2. Select SQL Server 2000 Components, select Install
Database Server, and
>then setup prepares the SQL Server Installation Wizard.
At the Welcome
>screen, click Next.
>3. In Computer Name dialog box, Local Computer is
the default option and
>the local computer name appears in the edit box. Click
Next.
>4. In the Installation Selection dialog box, click
Upgrade, remove, or add
>components to an existing instance of SQL Server, and
then click Next.
>5. In the Instance Name dialog box, Default will be
selected. Click Next.
> # Note # When upgrading, SQL Server 7.0
automatically becomes the
>default instance of SQL Server 2000.
>6. In the Existing Installation dialog box, click
Upgrade your existing
>installation, and then click Next.
>7. In the Upgrade dialog box, you are prompted as to
whether you want to
>proceed with the requested upgrade. Click Yes, upgrade
my <text specific to
>the upgrade> to start the upgrade process, and then
click Next. The upgrade
>runs until finished.
>8. In the Connect to Server dialog box, select an
authentication mode, and
>then click Next.
>If you are not sure which mode to use, accept the
default: The Windows
>account information I use to log on to my computer with
(Windows).
>9. In Start Copying Files dialog box, click Next.
>10. In the Setup Complete dialog box, click Yes, I
want to restart my
>computer now, and then click Finish.
>
>Additionally, instead of a full upgrade from SQL Server
7.0 to SQL Server
>2000, which overwrites SQL Server 7.0, you can upgrade
SQL Server 7.0
>databases and associated meta data. To upgrade a SQL
Server 7.0 database to
>a SQL Server 2000 database, please use the following
procedure:
>1. Expand a server group, and then expand a server.
>2. Right-click the server, point to All Tasks, and
then click Copy Database
>Wizard.
>3. Complete the steps in the wizard.
>After upgrading databases from SQL Server 7.0, run
sp_updatestats (update
>statistics) against the database on the destination
server to ensure
>optimal performance of the copied database.
>
>Jim, does this answer your question? Please feel free to
let me know if
>this help solves your problem. If there is anything
more I can still
>assist you with, please feel free to post it in the
group.
>Best regards,
>Billy Yao
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>.
>|||> I am conserned that I will lose scheduled jobs,
> maintenace programs and DTS packages by doing the detach
> attach method. Is there a way to ensute that I can bring
> these other objects to the new SQ: 2000?
You can't restore msdb. But you can script the jobs alerts etc (EM, Management, Agent ... Script).
But I very much doubt it'll work for maint wiz. However, that shouldn't be too much work to redo
through the wizard. Also, don't forget stuff in master.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jim" <jim.abel@.lmco.com> wrote in message news:017801c3af8f$a20b0080$a301280a@.phx.gbl...
> You are correct in that I want to upgrade my SQL 7 to SQL
> 2000. I followed the steps you have below But when I get
> to step 5 the wizard comes up with an empty instance text
> box and will not allow me to use the word Default. It
> says that there is an error in the current SQL 7 install
> that the Client tools or the registry may have been
> modified and only gives me the option to install a named
> instance.
> I need the upgrade to be the default instance because
> there is a large number of programs that call the
> database with the default instance. I am thinking that I
> might be able to detach all of the databases then
> uninstall SQL 7 and install SQL 2000 and finish up by
> reattahing the databases.
> I am conserned that I will lose scheduled jobs,
> maintenace programs and DTS packages by doing the detach
> attach method. Is there a way to ensute that I can bring
> these other objects to the new SQ: 2000?
> >--Original Message--
> >Hi Jim,
> >
> >Thank you for using MSDN Newsgroup! It's my pleasure to
> assist you with
> >your issue.
> >
> >First of all, I would like to confirm my understanding
> of your issue.
> >
> >From your description, I understand that you'd like to
> upgrade SQL 7 to SQL
> >2000. However, I'm unsure of the meaning "The only
> option that I'm allowed
> >is to create a named instance." When upgrading, SQL
> Server 7.0
> >automatically becomes the default instance of SQL Server
> 2000.
> >
> >As we known, you can overwrite the existing installation
> of SQL Server 7.0
> >by installing a default instance of SQL Server 2000. You
> can also keep the
> >installation of SQL Server 7.0 intact by installing a
> named instance of SQL
> >Server 2000. Both operations are performed using the
> following procedure.
> >
> >To upgrade SQL Server 7.0 to SQL Server 2000
> >1. Insert the Microsoft SQL Server 2000 compact disc
> for the edition to
> >which you want to upgrade into your CD-ROM drive. If the
> compact disc does
> >not autorun, double-click Autorun.exe in the root
> directory of the compact
> >disc.
> >2. Select SQL Server 2000 Components, select Install
> Database Server, and
> >then setup prepares the SQL Server Installation Wizard.
> At the Welcome
> >screen, click Next.
> >3. In Computer Name dialog box, Local Computer is
> the default option and
> >the local computer name appears in the edit box. Click
> Next.
> >4. In the Installation Selection dialog box, click
> Upgrade, remove, or add
> >components to an existing instance of SQL Server, and
> then click Next.
> >5. In the Instance Name dialog box, Default will be
> selected. Click Next.
> > # Note # When upgrading, SQL Server 7.0
> automatically becomes the
> >default instance of SQL Server 2000.
> >
> >6. In the Existing Installation dialog box, click
> Upgrade your existing
> >installation, and then click Next.
> >7. In the Upgrade dialog box, you are prompted as to
> whether you want to
> >proceed with the requested upgrade. Click Yes, upgrade
> my <text specific to
> >the upgrade> to start the upgrade process, and then
> click Next. The upgrade
> >runs until finished.
> >8. In the Connect to Server dialog box, select an
> authentication mode, and
> >then click Next.
> >If you are not sure which mode to use, accept the
> default: The Windows
> >account information I use to log on to my computer with
> (Windows).
> >9. In Start Copying Files dialog box, click Next.
> >10. In the Setup Complete dialog box, click Yes, I
> want to restart my
> >computer now, and then click Finish.
> >
> >
> >Additionally, instead of a full upgrade from SQL Server
> 7.0 to SQL Server
> >2000, which overwrites SQL Server 7.0, you can upgrade
> SQL Server 7.0
> >databases and associated meta data. To upgrade a SQL
> Server 7.0 database to
> >a SQL Server 2000 database, please use the following
> procedure:
> >
> >1. Expand a server group, and then expand a server.
> >2. Right-click the server, point to All Tasks, and
> then click Copy Database
> >Wizard.
> >3. Complete the steps in the wizard.
> >After upgrading databases from SQL Server 7.0, run
> sp_updatestats (update
> >statistics) against the database on the destination
> server to ensure
> >optimal performance of the copied database.
> >
> >
> >Jim, does this answer your question? Please feel free to
> let me know if
> >this help solves your problem. If there is anything
> more I can still
> >assist you with, please feel free to post it in the
> group.
> >
> >Best regards,
> >
> >Billy Yao
> >Microsoft Online Support
> >----
> >Get Secure! - www.microsoft.com/security
> >This posting is provided "as is" with no warranties and
> confers no rights.
> >Please reply to newsgroups only. Thanks.
> >
> >
> >.
> >|||Hi Jim,
The following step by step KB guide you move the DTS packages and Scheduled Jobs.
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
Additionally, if you want to move the database diagrams, please following the following article
to separately move them (otherwise they will be lost):
320125 HOW TO: Move a Database Diagram
http://support.microsoft.com/?id=320125
Best regards,
Billy Yao
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.
=====================
The issue is not resolved. I'm trying to decide the next steps to take to get the 7.0 Database
upgraded to 2000.
I'm upgrading a Standard edition to a Standard edition. As stated in the thread I need to find
out
what other objects that I need to migrate, and how, that won't be done when I detach and
reattach
the databases. Mainly what comes to mind is the DTS packages, Scheduled Jobs and not
so
important backups and maintenance plans.
A lost of the items and links of where to find How to's would be great.|||Billy,
Thanks for your help. This did the trick. I now have
all the information that I need to do the upgrade this
Saturday. Once again thank you for digging up all of the
articales. They have given me the necessary information
to complete the task.
Take Care
>--Original Message--
>Hi Jim,
>The following step by step KB guide you move the DTS
packages and Scheduled Jobs.
>314546 HOW TO: Move Databases Between Computers That Are
Running SQL Server
>http://support.microsoft.com/?id=314546
>Additionally, if you want to move the database diagrams,
please following the following article
>to separately move them (otherwise they will be lost):
>320125 HOW TO: Move a Database Diagram
>http://support.microsoft.com/?id=320125
>
>Best regards,
>Billy Yao
>Microsoft Online Support
>----
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and
confers no rights.
>Please reply to newsgroups only. Thanks.
>
>
>=====================>The issue is not resolved. I'm trying to decide the
next steps to take to get the 7.0 Database
>upgraded to 2000.
>I'm upgrading a Standard edition to a Standard edition.
As stated in the thread I need to find
>out
>what other objects that I need to migrate, and how, that
won't be done when I detach and
>reattach
>the databases. Mainly what comes to mind is the DTS
packages, Scheduled Jobs and not
>so
>important backups and maintenance plans.
>A lost of the items and links of where to find How to's
would be great.
>.
>

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

challenge (permission denied) no sense, please help !

I have a database named MCP and a SQL Login MCP_USER (not sysadmin).
MCP_USER has db_datareader, db_datawriter and db_ddladmin permissions on MCP
database
I created a JOB (simple select on the MCP database) = select * from
mcp.tbl_test
the JOB has the MCP_USER as owner
the MCP_USER has SQLAgentOperatiorRole, SQLAgentReaderRole,
SQLAgentUserRole and targetServersRole permissions on the msdb database
if I try running the JOB with MCP_USER (that is what I need) I got: "the
EXECUTION permission was denied on the object sp_start_job, database msdb,
schema dbo"
This looks like a easy issue, but i think I've done all steps needed.
All I need is run a JOB with a non sysdmin user ..
thanks heaps ..
Julio MattosJulion
One question , when you start the job , did you log in as a MCP_USER ?
BOL says
Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs that
they own. Members of SQLAgentOperatorRole can start all local jobs including
those that are owned by other users. Members of sysadmin can start all local
and multiserver jobs.
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:87A1E4F0-2C93-4821-AA51-2C2CE0E11D77@.microsoft.com...
>I have a database named MCP and a SQL Login MCP_USER (not sysadmin).
> MCP_USER has db_datareader, db_datawriter and db_ddladmin permissions on
> MCP
> database
> I created a JOB (simple select on the MCP database) = select * from
> mcp.tbl_test
> the JOB has the MCP_USER as owner
> the MCP_USER has SQLAgentOperatiorRole, SQLAgentReaderRole,
> SQLAgentUserRole and targetServersRole permissions on the msdb database
> if I try running the JOB with MCP_USER (that is what I need) I got: "the
> EXECUTION permission was denied on the object sp_start_job, database msdb,
> schema dbo"
> This looks like a easy issue, but i think I've done all steps needed.
> All I need is run a JOB with a non sysdmin user ..
> thanks heaps ..
> Julio Mattos|||Hi Uri,
yes I am logged in as MCP_USER. The good news I found the issue. I had
assigned MCP_USER to SQLAgentOperatiorRole, SQLAgentReaderRole and
SQLAgentUserRole, but I just need to assign to one role not all the 3.
MCP_USER is SQLAgentOperatiorRole only and its working fine. If you want to
understand better why, have a look to the role descriptions. Basically one
role was overlaping the restrictions to another role.
thanks a lot
Julio
"Uri Dimant" wrote:

> Julion
> One question , when you start the job , did you log in as a MCP_USER ?
> BOL says
> Members of SQLAgentUserRole and SQLAgentReaderRole can only start jobs tha
t
> they own. Members of SQLAgentOperatorRole can start all local jobs includi
ng
> those that are owned by other users. Members of sysadmin can start all loc
al
> and multiserver jobs.
> "Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
> news:87A1E4F0-2C93-4821-AA51-2C2CE0E11D77@.microsoft.com...
>
>|||Thanks for the info Julio
"Julio Mattos" <JulioMattos@.discussions.microsoft.com> wrote in message
news:9D0D446D-2961-44DC-8D61-ED5B6ED12CF1@.microsoft.com...[vbcol=seagreen]
> Hi Uri,
> yes I am logged in as MCP_USER. The good news I found the issue. I had
> assigned MCP_USER to SQLAgentOperatiorRole, SQLAgentReaderRole and
> SQLAgentUserRole, but I just need to assign to one role not all the 3.
> MCP_USER is SQLAgentOperatiorRole only and its working fine. If you want
> to
> understand better why, have a look to the role descriptions. Basically one
> role was overlaping the restrictions to another role.
> thanks a lot
> Julio
> "Uri Dimant" wrote:
>

Cast as Integer problem.

I have DB that contains a field named GarmentSize. The field type is TEXT as it can contain for example

8
10
12
14

or

L
XL
XXL

The problem is when I run a query and order by GarmentSize the results displayed are as follows if it contains numbers

10
12
14
8

Instead of

8
10
12
14

If I use order by CAST(GarmentSize as Integer) this works fine, unless the field contains text then is throws the following error.

Syntax error converting the varchar value 'XL' to a column of data type int

Is there a way of determining if the field contains characters that can be casted before doing the CAST?I'd convert the single digit numbers to a more managable form, something like: ORDER BY CASE WHEN GarmentSize LIKE '[0-9]'
THEN ' ' + GarmentSize ELSE GarmentSize END-PatP|||yeah, but pat, that still doesn't solve the sequencing problem

appending '42' to a space is still gonna come after appending '105' to a space

gosman, i urge you most strenuously, downsize your TEXT field to at least VARCHAR(8000)

in fact, i would take bets that you could probably downsize it to VARCHAR(100) and still accommodate all the GarmentSizes in your database|||Wouldn't a size 42 have to be about five meters tall, and weigh 1000 Kg or more? ...and I thought that Americans were big! I'm not sure I'm prepared to explore the size 105.

To futher address this problem so it can handle more cases, how about:CASE WHEN IsNumeric(GarmentSize)
THEN Cast(Cast(GarmentSize AS MONEY)) AS CHAR(50))
ELSE GarmentSize END-PatP ;)|||varchar?...if we're changing datatypes, why not santize the data and make it int?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 text)
GO

INSERT INTO myTable99(Col1)
SELECT 'XL' UNION ALL
SELECT '1'
GO

SELECT CASE WHEN ISNUMERIC(CONVERT(varchar(8000),Col1)) = 0 THEN 'NO' ELSE 'YES' END AS [A Number?], Col1
FROM myTable99
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)|||Thanks Guys.

Pat the field type is actually VARCHAR 50

I've tried the following

Select GarmentSize from PHOOLRATIOS order by CASE WHEN IsNumeric(GarmentSize)THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))ELSE GarmentSize END

But I get this message. Incorrect syntax near the keyword 'THEN'.

Forgive my ignorance but I'm a complete SQL novice.|||sanitize the data and make it integer? hitting the 'ritas at lunch again, were we?

what do you propose as integer equivalents of XS, S, M, L, XL?

oh, oh, oh, please say "use foreign keys to look up the size name"

:) :) :)

I would say that the data is not normalized, since XL is a representation of a size range, not the actuall size...

And the Foreign key bit for a surrogate

puuuuleeeze

http://weblogs.sqlteam.com/brettk/archive/2004/06/09/1530.aspx

You might want a "size fits from" and a "size fits to" columns

Now aint that nitpicky...|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)|||SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize|||gosman, you lied, your first post distinctly says TEXT :)

try WHEN IsNumeric(GarmentSize) = 1 THEN ...

sorry, brett, garmentsize is normalized

if a range is your criterion of not normalized, then any of your tables that uses a datetime column is not normalized, since we all know that a datetime value is not a single value but rather represents a range

;)

Who's been drinking at lunch?|||Sorry, that was my bad! How about:Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END-PatP|||Hi Pat

Select GarmentSize
from PHOOLRATIOS
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer)) AS CHAR(50))
ELSE GarmentSize
END

Server: Msg 1035, Level 15, State 10, Line 4
Incorrect syntax near 'Cast', expected 'AS'.|||USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

--Pat's off his meds..forgive him

Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
END

SET NOCOUNT OFF
DROP TABLE myTable99
GO|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)|||Well I'll be a Hunkey's Monkle! Brett is correct.

It appears that CAST does a right fill with spaces. My example ought to read:SELECT GarmentSize
FROM PHOOLRATIOS
ORDER BY CASE WHEN 1 = IsNumeric(GarmentSize)
THEN Str(GarmentSize, 50)
ELSE GarmentSize
ENDThe alternative that he posted would also be good, although I'd reverse the order to put the numeric values first, something like:SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN 1
ELSE 2
END, GarmentSize-PatP|||brett obviously is dropping his myTable99 before looking at the results

this --Select GarmentSize
from myTable99
order by CASE WHEN IsNumeric(GarmentSize) = 1
THEN Cast(Cast(GarmentSize AS integer) AS CHAR(50))
ELSE GarmentSize
ENDdoes not sort the "numeric" values into numeric sequence!!!

insert the value '42' and you'll see what i mean

and yes, every datetime value represents a range

and i don't start drinking until after 5:00 p.m.

:) :) :)

Rudy...ya gotta stop smokin crack...it's not good for you...

Yes, I know..that's Pat's query...did you see the first query?

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(GarmentSize char(50))
GO

INSERT INTO myTable99(GarmentSize)
SELECT 'XL' UNION ALL
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT '4' UNION ALL
SELECT '5' UNION ALL
SELECT '6' UNION ALL
SELECT '10' UNION ALL
SELECT '40' UNION ALL
SELECT '11' UNION ALL
SELECT '112'
GO

SELECT GarmentSize
FROM myTable99
ORDER BY CASE WHEN ISNUMERIC(GarmentSize) = 1
THEN CONVERT(int, GarmentSize)
ELSE 0
END
, GarmentSize

SET NOCOUNT OFF
DROP TABLE myTable99
GO

And what's with the very specific point in time being a range?|||sorry, pal, i don't do crack either

keep fishing, you're sure to hit one of my vices eventually

there is no such thing as a point in time

what i was trying to get you to see goes something like this --

if A implies B, and C is A, then C implies B

A = a range
B = not normalized
C = some value

you claimed a range was not normalized

i'm saying that by this very same logic, since a datetime value is a range, therefore any of your tables using a datetime value is not normalized

actually, i have no problem with datetime values, and i know exactly how to handle them

what i was trying to point out is that your claim that a size is actually a range and therefore not nomalized is nonsense

no offence, good buddy, but it was

and if you were only joking, then you simply must learn how to use smileys correctly|||It appears that CAST does a right fill with spaces. My example ought to read...It's not the CAST, it's the datatype that you use within the CAST, - CHAR(50). If you used VARCHAR(50) there won't be any "right fill"-ing ;)|||OK...attempt to be clear.

Our friends Column, GarmentSize, is retaining 2 types of data in the same column.

One is the very specific size of a garmet, let's say a size 10.

Recording the size of the garment as M or medium presents no specific size. M is a range of sizes, like 10-12. Not 1 size.

Apples and Oranges. They're both fruit, but they are essentially different.

And I did say I was being picky, but I'd still say they'd need another table that addresses this and descibes what each (M, L, XL, ect) actually means.

I still don't get what you mean by that a datetime column represents a range.

I never said a "range was not normalized". It's the combination of a specific size and a range of sizes (XL, for example) is not normalized. The size range values in their own column would be fine.

OK, Vices, how about Fishing?|||yes, XL is a range, it is the range between where L leaves off and XXL begins

just like size 8 is a range, between where 7 leaves off and 9 begins!!

nope, i don't fish, nor do i hunt

i do like baiting gullible DBAs, though, and you are providing a world of entertainment for us today!!

have you ever gone into the Senior VP of Marketing's office and told her she cannot use XL as a size, and should use a numeric range instead?

what utter nonsense

"The size range values in their own column would be fine" -- they already are in their own column, it's called GarmentSize, and it has a perfectly valid domain consisting of sizes which happen to be either numbers or letters

but you DBAs don't know what a domain is, so let's leave the modelling nuances alone and go back to talking about other stuff like raid stripes and scuzzies and other important stuff

:)|||Then it's got to be some sort of hallucinogen

Is an apple a range, is an airplane a range, is a shirt a range?

Isn't our freinds problem proof enough that their data is not normalized?|||Is an apple a range, is an airplane a range, is a shirt a range?no, they are not

Isn't our freinds problem proof enough that their data is not normalized?no, the fact that numerics in a VARCHAR column sort as characters is certainly not proof that the data is not normalized

i hesitate to ask, but do you even know what normalized means?

like i suggested in my last post, perhaps (for your sake) we should just drop the subject|||Scrapped! Looked good on paper...|||OK, try again:

ORDER BY right('000000000...50 of these...0000000' + GarmentSize, 50)|||Since the data itself is not going to be sorted very well, dare I suggest a sortorder column? This may curdle Rudy's blood, but hey, why should he have all the fun, eh? ;-)|||fantastic idea!! a sortorder column!!

okay, now, let's see, how should we populate this column

I KNOW!! let's make it numberic, and for "numeric" sizes, we'll use the actual numbers!!

now, what about those pesky alpha sizes, like S, M, XL, and so on...

HEY!! BRILLIANT IDEA!! let's assign S to 7, M to 9, XL to 14, ...

uh oh, wait a sec

that would make a man's XL shirt, which is approximately a chest 42" the same as a woman's XL dress, which is a size 6...

brett? where are you, brett? could you please normalize this sortorder column for us?

pretty please?|||You just aren't seeing it Rudy..

XL is not 1 size.

And I gotta admit, you're a little edgy today...

In pants a men's XL would be 38-40...

woops, gotta run...

Don't forget your midol