November 22, 2011 at 12:13 am
Hi,
I try to change schema name "dbo" as below statement, but its produce error
EXEC sp_changeobjectowner 'hjbhatt.upload_sales', dbo
Msg 15530, Level 16, State 1, Line 1
The object with name "Proc_Unit_Output_08OCT2007" already exists.
Please suggestion me, how to resolve this issues?
thanks
ananda
November 22, 2011 at 12:56 am
If you want to move an object to a different schema use this:
ALTER SCHEMA new_schema TRANSFER old_schema.object_name
[font="Verdana"]Markus Bohse[/font]
November 22, 2011 at 1:30 am
While I agree that we should use the new syntax, I don’t think that the error message was caused by using the procedure. Most chances are that you already have a table with that name at the dbo schema.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
November 22, 2011 at 1:52 am
I have execute this command but same issues..
ALTER SCHEMA dbo TRANSFER hjbhatt.upload_sales
Msg 15530, Level 16, State 1, Line 1
The object with name "upload_sales" already exists.
select * from dbo.upload_sales
Msg 208, Level 16, State 3, Line 1
Invalid object name 'upload_sales'.
select * from hjbhatt.upload_sales - this query working with "hjbhatt" schema. But I could not change to "dbo"
Thanks
ananda
November 22, 2011 at 1:55 am
ananda.murugesan (11/22/2011)
I have execute this command but same issues..ALTER SCHEMA dbo TRANSFER hjbhatt.upload_sales
Msg 15530, Level 16, State 1, Line 1
The object with name "upload_sales" already exists.
select * from dbo.upload_sales
Msg 208, Level 16, State 3, Line 1
Invalid object name 'upload_sales'.
select * from hjbhatt.upload_sales - this query working with "hjbhatt" schema. But I could not change to "dbo"
Thanks
ananda
The query you executed will move the table from dbo to hjbhatt and there you obviously already have a table wqith that name. Try this way and it should work:
ALTER SCHEMA hjbhatt TRANSFER dbo.upload_sales
[font="Verdana"]Markus Bohse[/font]
November 22, 2011 at 2:05 am
use AGRO
go
ALTER SCHEMA hjbhatt TRANSFER dbo.upload_sales
go
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'upload_sales', because it does not exist or you do not have permission.
November 22, 2011 at 2:30 am
due to this error Table & SP does not allowed with same name dbo.upload_sales.
November 22, 2011 at 2:42 am
ananda.murugesan (11/22/2011)
due to this error Table & SP does not allowed with same name dbo.upload_sales.
Does this mean you have a table and a stored procedure with the same name?
Why would you want to do that?
[font="Verdana"]Markus Bohse[/font]
November 22, 2011 at 2:59 am
MarkusB (11/22/2011)
ananda.murugesan (11/22/2011)
due to this error Table & SP does not allowed with same name dbo.upload_sales.Does this mean you have a table and a stored procedure with the same name?
Why would you want to do that?
I have upgrade to SQL 2008 from SQL 2000, privisouly there are some tables are created with schema name(hjbhatt). after upgradation I changed all the table names with default schema "dbo".
In this acitivity most of the table I had changed with "dbo", this only one table(hjbhatt,upload_sales) could not able do it. due to table and SP with same name which was already exist in SQL 2000.
In SQL 2000
Table - hjbhatt.Upload_sales
SP - dbo.upload_sales.
after upgrade in SQL 2008, I tried this command as below
exec changeobjectowner hjbhatt.upload_sales to dbo - it is failed due to already exits with same name in SP.
Can you suggestion me as following steps for resolving this issues.
1. create table with dbo.upload_sales then imports data from hjbhatt.upload_sales.
2. create New SP name with existing SP
3. application side must be change with new table and SP name.
November 22, 2011 at 3:13 am
I have upgrade to SQL 2008 from SQL 2000, privisouly there are some tables are created with schema name(hjbhatt). after upgradation I changed all the table names with default schema "dbo".
In this acitivity most of the table I had changed with "dbo", this only one table(hjbhatt,upload_sales) could not able do it. due to table and SP with same name which was already exist in SQL 2000.
In SQL 2000
Table - hjbhatt.Upload_sales
SP - dbo.upload_sales.
after upgrade in SQL 2008, I tried this command as below
exec changeobjectowner hjbhatt.upload_sales to dbo - it is failed due to already exits with same name in SP.
Can you suggestion me as following steps for resolving this issues.
1. create table with dbo.upload_sales then imports data from hjbhatt.upload_sales.
2. create New SP name with existing SP
3. application side must be change with new table and SP name.
I still don't understand why you changed the schema after upgrading. If you had left everything in the old schema I would expect that it would work just fine.
But since the situation is already messed up, you have to either change the name of the table or the SP and then make the necessary changes on the application side.
As an alternative you could also create synonyms so you don't have to change the application code, but I must admit I have no experience using them.
http://msdn.microsoft.com/en-us/library/ms190626(v=SQL.100).aspx
[font="Verdana"]Markus Bohse[/font]
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply