Update Statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Update Statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-14 : 08:41:33

Aryei writes "SQL 7.0 SP3

I have created a stored procedure that will copy one table from the model database new database. I want to be able to call this procedure from within the destination database. I Placed the Stored Procedure in the master database and call it from within the destination database. I receive an invalid object with the name of the destination databes table name. My stored procedure does not specify the detstination database. If I am in that database do I need to speceify the destination database? Does the source database and destination database need to be the same name? below is the SP that I created

CREATE PROCEDURE SP_RESETSECURITY..20 AS
update ..mricldef set properties = model..mricldef107.properties
from model..mricldef20
where ..mricldef.class_id = model..mricldef20.class_id
and ..mricldef.class_id='SYSADM'
GO

The destination database contains the the same table with a different name (as you can see from the syntax)"

Nazim
A custom title

1408 Posts

Posted - 2002-01-14 : 10:09:32

My first suggestion will be change your stored procedure from sp_SP_RESETSECURITY to any another name but not starting from sp_ coz the it is taken as a system stored procedure and will be searched in Master DB first.

coming to your code, looks fyn to me. but, do you have permissions on the table with the user you are logged in??.

quote:

CREATE PROCEDURE SP_RESETSECURITY..20 AS
update ..mricldef set properties = model..mricldef107.properties
from model..mricldef20
where ..mricldef.class_id = model..mricldef20.class_id
and ..mricldef.class_id='SYSADM'
GO

----------------------------------
"True love stories don't have endings."

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-01-14 : 17:57:47

I think your problem could be that you have not specified the owners of the tables explicitly.

eg - owner "dbo" is assumed, unless specified. In this case, use the owner.tablename way of specifying your tables.

eg
UPDATE dbo.mricldef
rather than
UPDATE ..mricldef

Hope that helps...

I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources