Renaming a SQL2005 - Unable to connect to RS from Management Studi

We have several 2005 boxes that have been renamed. RS works fine from a
browser and I can deplopy reports to it and see it with the config manager.
What I can't do is reference it from Management Studio. On the one box that
I restarted after sp_drop and sp_add, a message box pops up referring to the
old name. on the other box, niether the old name no the new name are found.
Where does the Studio get these names?For anyone who cares, I had to chnge the rsreportserver.config file. It was
referencing the old server names in the ulrroot section. This also impacted
the SAC tool's ability to manage the rs instance.
"Jeff Ericson" wrote:
> We have several 2005 boxes that have been renamed. RS works fine from a
> browser and I can deplopy reports to it and see it with the config manager.
> What I can't do is reference it from Management Studio. On the one box that
> I restarted after sp_drop and sp_add, a message box pops up referring to the
> old name. on the other box, niether the old name no the new name are found.
> Where does the Studio get these names?

Renaming a diagram in SQL Server Management Studio

Hello, I have MS SQL 2005 installed on my server. When I tried to rename a
diagram, I received an error. Is it possible to rename a diagram, or I need
to create a new one and copy the contents? Does this problem exist only in my
installation, or does anyone else have the same problem? Thank you for
response. Here is the error message (renaming Diagram1 to Diagram2):
TITLE: Microsoft SQL Server Management Studio
Unable to rename Diagram2. (ObjectExplorer)
ADDITIONAL INFORMATION:
Could not find stored procedure 'dbo.sp_renamediagram'. (Microsoft SQL
Server, Error: 2812)
For help, click:
[url]http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00 .1399&EvtSrc=MSSQLServer&EvtID=2812&LinkId=20476[/url]
BUTTONS:
OK
On Mar 4, 4:31 pm, Paul J <P...@.discussions.microsoft.com> wrote:
> Hello, I have MS SQL 2005 installed on my server. When I tried to rename a
> diagram, I received an error. Is it possible to rename a diagram, or I need
> to create a new one and copy the contents? Does this problem exist only in my
> installation, or does anyone else have the same problem? Thank you for
> response. Here is the error message (renaming Diagram1 to Diagram2):
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename Diagram2. (ObjectExplorer)
> --
> ADDITIONAL INFORMATION:
> Could not find stored procedure 'dbo.sp_renamediagram'. (Microsoft SQL
> Server, Error: 2812)
> For help, click:http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=...
> --
> BUTTONS:
> OK
> --
You should be able to rename a diagram.
When you created the first diagram in the database you should have
seen a message that database objects needed for diagrams would be
created and the following ojects should have been created:
sp_helpdiagramdefinition
sp_creatediagram
sp_renamediagram
sp_alterdiagram
sp_dropdiagram
fn_diagramobjects
sp_upgraddiagrams
sysdiagrams
PK__sysdiagrams__ID
UK_principal_name
sp_helpdiagrams
|||Paul J (PaulJ@.discussions.microsoft.com) writes:
> Hello, I have MS SQL 2005 installed on my server. When I tried to rename
> a diagram, I received an error. Is it possible to rename a diagram, or I
> need to create a new one and copy the contents? Does this problem exist
> only in my installation, or does anyone else have the same problem?
> Thank you for response. Here is the error message (renaming Diagram1 to
> Diagram2):
> TITLE: Microsoft SQL Server Management Studio
> --
> Unable to rename Diagram2. (ObjectExplorer)
>
To add to Steve's post, create some other database and add diagrams there.
Then do this from a query window:
sp_helptext sp_renamediagram
Copy and paste, and the run the script in the database you are having
problem with. This should work. (But I have not actually tested.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||"Erland Sommarskog" wrote:
> To add to Steve's post, create some other database and add diagrams there.
> Then do this from a query window:
> sp_helptext sp_renamediagram
> Copy and paste, and the run the script in the database you are having
> problem with. This should work. (But I have not actually tested.)
>
>
I already had the procedure there. But it doesn't work even after launching
the script... It works in new DB, so I will switch my objects data to the new
one. I still don't know what's wrong, but I believe it won't happen again :-)
If you still think you can help me with this issue, feel free to ask me for
all information you need. Resolving of this problem would save me much time.
Thank you
|||Paul J (PaulJ@.discussions.microsoft.com) writes:
> I already had the procedure there. But it doesn't work even after
> launching the script... It works in new DB, so I will switch my objects
> data to the new one. I still don't know what's wrong, but I believe it
> won't happen again :-) If you still think you can help me with this
> issue, feel free to ask me for all information you need. Resolving of
> this problem would save me much time.
What does
SELECT schema_id, schema_name(schema_id)
FROM sys.objects WHERE name = 'sp_renamediagram'
return?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
|||1 dbo
"Erland Sommarskog" wrote:

> Paul J (PaulJ@.discussions.microsoft.com) writes:
> What does
> SELECT schema_id, schema_name(schema_id)
> FROM sys.objects WHERE name = 'sp_renamediagram'
> return?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
>

Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to renam
e
a stored procedure the name changes in the GUI, but the actually name used t
o
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actuall
y
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed
it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored pr
ocedure".
My guess is that you have procs with same name but different owners. Check s
ysobjects.
Also, be aware that sp_rename does not change the source code for the proced
ure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to ren
ame
> a stored procedure the name changes in the GUI, but the actually name used
to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editin
g
> you can see that in the code the stored procedure name has not been change
d.
> Whereas if you rename a view of function the name within the code is actua
lly
> changed.
> Can anyone explain this behavior?sql

Rename of stored proc in EM/Manage Studio does not work as expecte

I normally rename stored procedures through script, so I never noticed this
before, but if you use EM (2000) or Server Management Studio (2005) to rename
a stored procedure the name changes in the GUI, but the actually name used to
execute it does not. So, if I start with a stored procedure named 'a' it
executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
still works and EXEC b fails. If you open the stored procedure for editing
you can see that in the code the stored procedure name has not been changed.
Whereas if you rename a view of function the name within the code is actually
changed.
Can anyone explain this behavior?> So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails.
This is not what I am seeing. I created a proc named aa in tempdb. I renamed it to a using EM. EM
submitted the following SQL:
EXEC sp_rename N'[dbo].[aa]', N'a', N'object'
I can now execute a. If I try to execute aa, I get "could not find stored procedure".
My guess is that you have procs with same name but different owners. Check sysobjects.
Also, be aware that sp_rename does not change the source code for the procedure (see syscomments),
the original name will be in the source code.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Byron" <Byron@.discussions.microsoft.com> wrote in message
news:3102CE8C-BA55-49C2-AB12-65BF91CDF87E@.microsoft.com...
>I normally rename stored procedures through script, so I never noticed this
> before, but if you use EM (2000) or Server Management Studio (2005) to rename
> a stored procedure the name changes in the GUI, but the actually name used to
> execute it does not. So, if I start with a stored procedure named 'a' it
> executes using EXEC a. Now, I rename it through the GUI to b and EXEC a
> still works and EXEC b fails. If you open the stored procedure for editing
> you can see that in the code the stored procedure name has not been changed.
> Whereas if you rename a view of function the name within the code is actually
> changed.
> Can anyone explain this behavior?

rename a table

Is it possible to rename a table with T-SQL?

I can do it by rightclicking in the SQL management studio and selecting the rename menu option...

so what is the corresponding T-sql command?

thanks in advance, have a nice day

Carl

use sp_rename

Syntex,

sp_rename 'oldname' , 'newname'

|||

Thanks ManiD this is THE answer to my question... and clicking the modify button on this system stored procedure has learned me a lot new T-SQL procedures and way of working.

Thanks again and have a nice day

Carl

sql

Removing SQL Express instances

Using SQL Express, UNDER COMPUTER management, SQL Server Configuraqtion Manager, SQL Server 2005 Network configuration Ihave the following listed instances

CSOLUTION - not listed under services

MSSQLSERVER - Disabled

SQLEXPRESS - Diaabled

SQLTEST - Disabled

SQLEXPRESSDATA - used by VB05 program

Questions

a) how do I delete all the instancces I do not use. In MSDE, each instance could be removed via the add/remove programs, but I don't see how to remove it under express

b) since SQLEXPRESS is the default for an SQL Express installation, I would like for the VBB05 program to use that instance; how do I do that?

Any help would be appreciated.

Bob

a) When you go to Add/Remove Programs, select the SQL 2005 entry and click on Remove. This will pop up a box showing the instances you have on your machine. You can select one from here and continue with the uninstall to remove it.

b) When you create your DB connection in your VB app, you'll need to supply a DB name to connect to. Here, use <MACHINENAME>/SQLEXPRESS and it will connect to that instance. You'd do the same thing to connect to any other named instance.

Thanks,
Sam Lester (MSFT)

Removing SQL Express instances

Using SQL Express, UNDER COMPUTER management, SQL Server Configuraqtion Manager, SQL Server 2005 Network configuration Ihave the following listed instances

CSOLUTION - not listed under services

MSSQLSERVER - Disabled

SQLEXPRESS - Diaabled

SQLTEST - Disabled

SQLEXPRESSDATA - used by VB05 program

Questions

a) how do I delete all the instancces I do not use. In MSDE, each instance could be removed via the add/remove programs, but I don't see how to remove it under express

b) since SQLEXPRESS is the default for an SQL Express installation, I would like for the VBB05 program to use that instance; how do I do that?

Any help would be appreciated.

Bob

a) When you go to Add/Remove Programs, select the SQL 2005 entry and click on Remove. This will pop up a box showing the instances you have on your machine. You can select one from here and continue with the uninstall to remove it.

b) When you create your DB connection in your VB app, you'll need to supply a DB name to connect to. Here, use <MACHINENAME>/SQLEXPRESS and it will connect to that instance. You'd do the same thing to connect to any other named instance.

Thanks,
Sam Lester (MSFT)

Removing Databases

Using SQL Server Management Studio, I see 4 entries, master, model, msdb and temdb, under Databases -> System Databases. Which of these can I safely remove, if any?

Thank you

Hi there,

I don't think you should remove any of them....Each of the system databases is there for a purpose and removing one or more will adversely affect your database server (possibly to the point of inoperability).

Below is a link to adescription of the purpose of each system database:
System Databases

Hope that helps a bit or sheds some light on the matter, but sorry if it doesn't
|||Why should one delete the system databases ?

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||

Nate,

Thank you for the reponse, it helped quite a bit. I am new to SQL Express and I wasn't sure what those Db's were for, so the link is very helpful.