Hi everyone,
In database 'BASE1' I have this stored proc called 'SrediCjenik':
-------------------------------------------------------------------------
USE BASE1
GO
/****** Object:  StoredProcedure [dbo].[SrediCjenik]    Script Date: 
09/17/2013 22:19:16 ******/

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[SrediCjenik]

AS


BEGIN


delete from dbo.robecjenici

END
-------------------------------------------------------------------------

but it doesn't work, it doesn't delete anything.

But when I wrote only this :

-------------------------------------------------------------------------
delete from [BASE1].dbo.robecjenici
-------------------------------------------------------------------------

It delete  robecjenici table.

Same proc work's on other database (BASE2).


any help????


Tomislav

Tomislav (andolandol_MAKNI_@gmail.com) writes:
> ALTER PROCEDURE [dbo].[SrediCjenik] AS
> BEGIN
> delete from dbo.robecjenici
> 
> END
> -------------------------------------------------------------------------
> 
> but it doesn't work, it doesn't delete anything.
> 
> But when I wrote only this :
> 
> -------------------------------------------------------------------------
> delete from [BASE1].dbo.robecjenici
> -------------------------------------------------------------------------
> 
> It delete  robecjenici table.
> 
> Same proc work's on other database (BASE2).
> 
> 
> any help????
 
Any error message? And you did actually run the procedure, did you?
A trigger on the table?

Unfortunately, with the amount of information, wild guesses is all I
can offer. My experience is that in weird situations like this one,
there is often something the poster did not tell me, which was essential
for the answer.

-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

On 18-Sep-2013, Tomislav  wrote:

> In database 'BASE1' I have this stored proc called 'SrediCjenik':
> -------------------------------------------------------------------------
> USE BASE1
> GO
> /****** Object:  StoredProcedure [dbo].[SrediCjenik]    Script Date:
> 09/17/2013 22:19:16 ******/
>
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
>
> ALTER PROCEDURE [dbo].[SrediCjenik]
>
> AS
>
>
> BEGIN
>
>
> delete from dbo.robecjenici
>
> END
> -------------------------------------------------------------------------
>
> but it doesn't work, it doesn't delete anything.
>
> But when I wrote only this :
>
> -------------------------------------------------------------------------
> delete from [BASE1].dbo.robecjenici
> -------------------------------------------------------------------------
>
> It delete  robecjenici table.
>
> Same proc work's on other database (BASE2).
>
>
> any help????

The USE BASE1 that you have before the CREATE PROCEDURE statement really
just says that you want to create the procedure in the BASE1 database.
However, if you want the stored procedure to operate on a table in that
database then you need to include the USE BASE1 inside the stored procedure.
Otherwise, when you run the stored procedure it will try to run against
whatever is the default database for the login you are using and unless you
have specifically set the database for that login to be BASE1, it is
probably trying to run against the MASTER database.

Beyond that, within your stored procedure you need to put a TRY...CATCH
block around critical portions of your code and if an error is detected
display the error and then re-throw the exception so that the stored
procedure exits. Granted, you only have one statement in this stored
procedure, but it is still a good practice to always include TRY...CATCH
logic.

I would note one other thing as well. If you are trying to empty a table,
DELETE is not the best way to accomplish that because it forces the
transaction log to record the delete of every single row so that if you ever
need to restore your database it will repeat those actions. What you
probably shout be doing is a TRUNCATE since that is a DDL action that is not
recoverable - but you probably don't need recovery capabilities in this
case. The only thing you have to ensure is that either the login that you
use has DDL permissions or that DDL permissions are granted to the stored
procedure itself.

Ken (ktsahl@yoohoo.com) writes:
> The USE BASE1 that you have before the CREATE PROCEDURE statement really 
> just says that you want to create the procedure in the BASE1 database. 
> However, if you want the stored procedure to operate on a table in that 
> database then you need to include the USE BASE1 inside the stored
> procedure. Otherwise, when you run the stored procedure it will try to
> run against whatever is the default database for the login you are using
> and unless you have specifically set the database for that login to be
> BASE1, it is probably trying to run against the MASTER database. 


No, this is completely wrong. The default database for a stored procedure is 
always the database it was created in. A USE statement is something you 
rarely, if ever, should include in a stored procedure. Because one day you 
may want to restore the databasees under different names as a test 
environment, and in that case hard-coded database names will bite you badly.


-- 
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

On 19.9.2013. 9:06, Erland Sommarskog wrote:
> Ken (ktsahl@yoohoo.com) writes:
>> The USE BASE1 that you have before the CREATE PROCEDURE statement really
>> just says that you want to create the procedure in the BASE1 database.
>> However, if you want the stored procedure to operate on a table in that
>> database then you need to include the USE BASE1 inside the stored
>> procedure. Otherwise, when you run the stored procedure it will try to
>> run against whatever is the default database for the login you are using
>> and unless you have specifically set the database for that login to be
>> BASE1, it is probably trying to run against the MASTER database.
>
>
> No, this is completely wrong. The default database for a stored procedure is
> always the database it was created in. A USE statement is something you
> rarely, if ever, should include in a stored procedure. Because one day you
> may want to restore the databasees under different names as a test
> environment, and in that case hard-coded database names will bite you badly.
>
>
Every beginning is hard.
if You won't to execute stored procedure  You must execute proc with 
'EXECUTE [dbo].[SrediCjenik]' :)

TNX

On 19-Sep-2013, Erland Sommarskog  wrote:

> Ken (ktsahl@yoohoo.com) writes:
> > The USE BASE1 that you have before the CREATE PROCEDURE statement really
> >
> > just says that you want to create the procedure in the BASE1 database.
> > However, if you want the stored procedure to operate on a table in that
> > database then you need to include the USE BASE1 inside the stored
> > procedure. Otherwise, when you run the stored procedure it will try to
> > run against whatever is the default database for the login you are using
> > and unless you have specifically set the database for that login to be
> > BASE1, it is probably trying to run against the MASTER database.
>
>
> No, this is completely wrong. The default database for a stored procedure
> is
> always the database it was created in. A USE statement is something you
> rarely, if ever, should include in a stored procedure. Because one day you
>
> may want to restore the databasees under different names as a test
> environment, and in that case hard-coded database names will bite you
> badly.
>
>

That's only true if you write sloppy generalized code and don't worry about
security implications. In actual production systems it is very common to
require precise user management and ensure that the proper schema is used in
the proper database and that only authorized users are allowed to execute
stored procedures - especially when the same application is being run in
multiple databases in the same instance by different users. Welcome to the
world of corporate security.