truncates table via stored procedure help

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

General SQL Server Forums

New to SQL Server Programming

truncates table via stored procedure help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-04 : 06:27:34

Hey guys

I have never created a stored procedure before so Not even sure what i am trying to do is even viable
Aim – Create a stored procedure which truncates a table at a certain time of the day
Table name Dan.Stg_Jitter_Opp want to truncate table at 11am

looking for to any help available

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 07:45:35

You need to just create a procedure like


CREATE PROC TrunCateTableStg_Jitter_Opp 
AS
TRUNCATE TABLE Dan.Stg_Jitter_Opp 
GO

Then add a sql server agent job with single step as


EXEC TrunCateTableStg_Jitter_Opp

and schedule it to execute at 11 am daily

if you want you can even dispense with procedure and call TRUNCATE statement directly from the job.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-04 : 10:19:25

HI Visakh16
FDMS database, and then expand Programmability.
Right-click Stored Procedures, and then click New Stored Procedure.

I applied your logic above into the following

-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Daniel Mason>
-- Create date: <04/10/2013>
-- Description: <Truncates Dan.Stg_Jitter_Opp Table>
-- =============================================
CREATE PROC TrunCateTableStg_Jitter_Opp
AS
TRUNCATE TABLE [FDMS].[Dan].[Stg_Jitter_Opp]
GO

But i recieve the following error message

Msg 2760, Level 16, State 1, Procedure TrunCateTableStg_Jitter_Opp, Line 8
The specified schema name "dbo" either does not exist or you do not have permission to use it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-04 : 10:33:42

where are you specifying dbo? i cant see it in posted code above.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-10-04 : 10:42:52

Server : w4pvd025
Database : FDMS
Tables : [Dan].[Stg_Jitter_Opp]

Does that help ?

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-04 : 11:39:47

Which database are you trying to create the stored procedures in? And in which schema? You can give explicit directions to SQL Server by specifying those. That hopefully will help you narrow down the problem. For example - see in red:


USE [FDMS]
GO	
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC dbo.TrunCateTableStg_Jitter_Opp 
AS
TRUNCATE TABLE [FDMS].[Dan].[Stg_Jitter_Opp]
GO

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-07 : 05:28:24

quote:
Originally posted by masond

Server : w4pvd025
Database : FDMS
Tables : [Dan].[Stg_Jitter_Opp]

Does that help ?

Nope..here schema says Dan and not dbo.
the error message suggested

The specified schema name "dbo" either does not exist or you do not have permission to use it.

which means you were specifying dbo somewhere

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2013-10-21 : 12:05:09

I think that what James K is saying is that the procedure, not the table, is the problem. Since there is no explicit schema on the procedure, it is defaulting to "dbo". Can we verify if "dbo" exists?[CODE]select *
from sys.schemas[/CODE]

=================================================
No, no, you're not thinking, you're just being logical. -Niels Bohr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources