Run Package error
tomchu
Posts: 7
I am try to run the SQLPackage.exe to make a new database on our sql server (local) it give us an error say ----
<SQLError Package="SQLPackage"><Error>There is already an object named 'AdRight1' in the database.</Error><Assembly>SQLPackageStore1</Assembly><Package>Package1.resx</Package><Batch>Batch981</Batch><SQL>CREATE PROC AdRight1 AS
SELECT *
FROM X_ad
</SQL></SQLError>
How come it said there is an object named and I just try to make a new database!? Then I try to run the query (CREATE PROC AdRight1 AS SELECT * FROM X_ad)in Query Analyzer without any error.
PLease help
Tom
<SQLError Package="SQLPackage"><Error>There is already an object named 'AdRight1' in the database.</Error><Assembly>SQLPackageStore1</Assembly><Package>Package1.resx</Package><Batch>Batch981</Batch><SQL>CREATE PROC AdRight1 AS
SELECT *
FROM X_ad
</SQL></SQLError>
How come it said there is an object named and I just try to make a new database!? Then I try to run the query (CREATE PROC AdRight1 AS SELECT * FROM X_ad)in Query Analyzer without any error.
PLease help
Tom
Comments
I think what's happening here is a limitation in SQL Packager.
My guess is that you have 2 objects called 'AdRight1' in your database, however they were created by 2 different users. Hence, they could be created without explicitely stating the user/schema they belong to; SQL Server worked that out from who was executing the SQL.
The problem now is, that SQL Packager will use the exact statements that were entered when the objects were created. However, it is of course running as the same user, throughout, which results in SQL Packager trying to create 2 objects with the same name for the same user/schema.
If all that sounds confusing, the important point to note is that you can probably fix your problem by explicitely assigning your stored procedure to a user/schema when you create/alter it.
E.g. in your case you could say
ALTER PROC [<user/schema>].[AdRight1]
AS
SELECT * FROM X_ad
Let me know if that helps,
Tilman
PS: This limitation only applies to stored procedures, functions, etc. In most cases SQL Packager works out the user/schema of an object correctly.