Hello,
I have a pretty big batch that I need to execute on schedule basis. I alters
several views, then creates a table and updates this table using these
modified views:
ALTER VIEW vuMonthlySalesOpenOrds
AS
SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
2)),
MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
FROM OPENORDS
WHERE
C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
(SELECT End_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) AND
CLASS='SMI'
GROUP BY CUSNO
GO
/***************************************************************************
**************/
ALTER VIEW vuMTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_MTD_HIST,
CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
FROM SALEHIST
WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
'12/31/2003'
GROUP BY CUST_NBR
GO
/***************************************************************************
**************/
................................................
................................................
INSERT INTO tbl2003December
SELECT vuSalesAnalysisFull.*
FROM vuSalesAnalysisFull
What would be the best way to schedule this batch execution? Can I create a
SP that would include all batch statements?
I tried to use this model that works well for me:
CREATE PROC dbo.uspSaveRandomSeal
@.sTable varchar(25)
AS
DECLARE @.SQLx NVARCHAR(1024)
SET @.SQLx = N'
IF EXISTS (SELECT name FROM sysobjects WHERE name= ''' +
@.sTable + N''' AND type=''U'')
DROP TABLE pafo.' + @.sTable+ N'
SELECT dbo.vuRandomSeal.*
INTO pafo.'+@.sTable+N'
FROM dbo.vuRandomSeal'
EXEC sp_ExecuteSQL @.SQLx
but I'm getting too many errors, and I'm not sure whether I'm doing it
right.
I would appreciate your suggestions.
Thank you,
--
Peter AfoninI'm not sure I 100% understand the question? Yes, SP's are a good way to
manage batches of work. Also consider DTS and SQL Agent.
What are the errors you're getting?
--
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:OKunGiFtDHA.2244@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a pretty big batch that I need to execute on schedule basis. I
alters
> several views, then creates a table and updates this table using these
> modified views:
> ALTER VIEW vuMonthlySalesOpenOrds
> AS
> SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> 2)),
> MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> FROM OPENORDS
> WHERE
> C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) AND
> CLASS='SMI'
> GROUP BY CUSNO
> GO
>
/***************************************************************************
> **************/
> ALTER VIEW vuMTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_MTD_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
> '12/31/2003'
> GROUP BY CUST_NBR
> GO
>
/***************************************************************************
> **************/
> ................................................
> ................................................
> INSERT INTO tbl2003December
> SELECT vuSalesAnalysisFull.*
> FROM vuSalesAnalysisFull
> What would be the best way to schedule this batch execution? Can I create
a
> SP that would include all batch statements?
> I tried to use this model that works well for me:
> CREATE PROC dbo.uspSaveRandomSeal
> @.sTable varchar(25)
> AS
> DECLARE @.SQLx NVARCHAR(1024)
> SET @.SQLx = N'
> IF EXISTS (SELECT name FROM sysobjects WHERE name= ''' +
> @.sTable + N''' AND type=''U'')
> DROP TABLE pafo.' + @.sTable+ N'
> SELECT dbo.vuRandomSeal.*
> INTO pafo.'+@.sTable+N'
> FROM dbo.vuRandomSeal'
> EXEC sp_ExecuteSQL @.SQLx
> but I'm getting too many errors, and I'm not sure whether I'm doing it
> right.
> I would appreciate your suggestions.
> Thank you,
> --
> Peter Afonin
>|||Thanks, Brian.
I'll enclose the whole batch. If I try to create SP like this:
CREATE PROC usp2003December
AS
DECLARE @.SQLx varchar(7999)
SET @.SQLx=N'(
ALTER VIEW vuMonthlySalesOpenOrds
AS
SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
2)),
MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
FROM OPENORDS
WHERE
C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
(SELECT End_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) AND
CLASS='SMI'
GROUP BY CUSNO
GO
ALTER VIEW vuMTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_MTD_HIST,
CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
FROM SALEHIST
WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
'12/31/2003'
GROUP BY CUST_NBR
GO
ALTER VIEW vuPYSSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_PYS_HIST,
CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_PYS_HIST
FROM SALEHIST
WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2002 WHERE
DateAdd(year, -1, CONVERT(VARCHAR, '12/31/2003', 101))+1 Between Start_Date
and End_Date) And
(SELECT End_Date FROM tblFiscalCalendar2002 WHERE
DateAdd(year, -1, CONVERT(VARCHAR, '12/31/2003', 101))+1 Between Start_Date
and End_Date)
GROUP BY CUST_NBR
GO
ALTER VIEW vuYTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_YTD_HIST,
CAST(Sum(INV_AMT) AS float) INV_AMT_YTD_HIST
FROM SALEHIST
WHERE IDT Between '1/1/2003' And '12/31/2003'
GROUP BY CUST_NBR
GO
ALTER VIEW vuPYSYTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_PYS_YTD_HIST,
CAST(Sum(INV_AMT) AS float) INV_AMT_PYS_YTD_HIST
FROM SALEHIST
WHERE IDT Between '1/1/2002' And DateAdd(year, -1, '12/31/2003')
GROUP BY CUST_NBR
GO
ALTER VIEW vuYTDFullSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_YTD_FULL_HIST,
CAST(Sum(INV_AMT) AS numeric(10, 2)) INV_AMT_YTD_FULL_HIST
FROM SALEHIST
WHERE IDT Between '1/1/2002' And '12/31/2002'
GROUP BY CUST_NBR
GO
if exists (select * from dbo.sysobjects where id =object_id(N'[dbo].[tbl2003December]') and OBJECTPROPERTY(id, N'IsUserTable')
= 1)
drop table [dbo].[tbl2003December]
GO
CREATE TABLE [dbo].[tbl2003December] (
[Invoice CMS MTD CS] [float],
[Invoice CMS MTD $$] [float],
[PYS Full Month CS] [float],
[PYS Full Month $$] [float],
[MTD CS %] [float],
[MTD $$ %] [float],
[MTD Open Orders CS] [float],
[MTD Open Orders $$] [float],
[MTD Open + Invoice CS] [float],
[MTD Open + Invoice $$] [float],
[MTD CS % 2] [float],
[MTD $$ % 2] [float],
[YTD Sales CS] [float],
[YTD Sales $$] [float],
[PYS YTD CS] [float],
[PYS YTD $$] [float],
[YTD CS %] [float],
[YTD $$ %] [float],
[Full Year YTD CS] [float],
[Full Year YTD $$] [float],
[Full Year CS %] [float],
[Full Year $$ %] [float],
[CusNo] [decimal](6, 0) NOT NULL ,
[Customer] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[SalesNo] [decimal](3, 0) NULL ,
[SalesName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateTime] [datetime] NOT NULL
) ON [PRIMARY]
GO
INSERT INTO tbl2003December
SELECT vuSalesAnalysisFull.*
FROM vuSalesAnalysisFull
GO
ALTER VIEW vuMonthlySalesOpenOrds
AS
SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
2)),
MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
FROM OPENORDS
WHERE
C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) And
(SELECT End_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) AND
CLASS='SMI'
GROUP BY CUSNO
GO
ALTER VIEW vuMTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_MTD_HIST,
CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
FROM SALEHIST
WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) And
getdate()
GROUP BY CUST_NBR
GO
ALTER VIEW vuPYSSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_PYS_HIST,
CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_PYS_HIST
FROM SALEHIST
WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2002 WHERE
DateAdd(year, -1, CONVERT(VARCHAR, GETDATE(), 101))+1 Between Start_Date
and End_Date) And
(SELECT End_Date FROM tblFiscalCalendar2002 WHERE
DateAdd(year, -1, CONVERT(VARCHAR, GETDATE(), 101))+1 Between Start_Date
and End_Date)
GROUP BY CUST_NBR
GO
ALTER VIEW vuYTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_YTD_HIST,
CAST(Sum(INV_AMT) AS float) INV_AMT_YTD_HIST
FROM SALEHIST
WHERE IDT Between '1/1/2003' And getdate()
GROUP BY CUST_NBR
GO
ALTER VIEW vuPYSYTDSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_PYS_YTD_HIST,
CAST(Sum(INV_AMT) AS float) INV_AMT_PYS_YTD_HIST
FROM SALEHIST
WHERE IDT Between '1/1/2002' And DateAdd(year, -1, getdate())
GROUP BY CUST_NBR
GO
ALTER VIEW vuYTDFullSalesHistory
AS
SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
QTY_YTD_FULL_HIST,
CAST(Sum(INV_AMT) AS numeric(10, 2)) INV_AMT_YTD_FULL_HIST
FROM SALEHIST
WHERE IDT Between '1/1/2002' And '12/31/2002'
GROUP BY CUST_NBR
GO
'
GO
EXEC sp_ExecuteSQL @.SQLx
/***************************************************************************
**************/,
I get these errors like this:
Server: Msg 170, Level 15, State 1, Procedure usp2003December, Line 14
Line 14: Incorrect syntax near '12'.
Server: Msg 105, Level 15, State 1, Procedure usp2003December, Line 16
Unclosed quotation mark before the character string '
GROUP BY CUSNO
'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string '
'.
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@.SQLx'.
I tried to play with quotes, but still was getting errors.
Peter
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:O4GKDAGtDHA.2448@.TK2MSFTNGP09.phx.gbl...
> I'm not sure I 100% understand the question? Yes, SP's are a good way to
> manage batches of work. Also consider DTS and SQL Agent.
> What are the errors you're getting?
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
> news:OKunGiFtDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > Hello,
> >
> > I have a pretty big batch that I need to execute on schedule basis. I
> alters
> > several views, then creates a table and updates this table using these
> > modified views:
> >
> > ALTER VIEW vuMonthlySalesOpenOrds
> > AS
> > SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> > MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> > 2)),
> > MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> > FROM OPENORDS
> > WHERE
> > C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
And
> > (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
AND
> > CLASS='SMI'
> > GROUP BY CUSNO
> > GO
> >
> >
>
/***************************************************************************
> > **************/
> >
> > ALTER VIEW vuMTDSalesHistory
> > AS
> > SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> > QTY_MTD_HIST,
> > CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> > FROM SALEHIST
> > WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
And
> > '12/31/2003'
> > GROUP BY CUST_NBR
> > GO
> >
>
/***************************************************************************
> > **************/
> > ................................................
> > ................................................
> >
> > INSERT INTO tbl2003December
> > SELECT vuSalesAnalysisFull.*
> > FROM vuSalesAnalysisFull
> >
> > What would be the best way to schedule this batch execution? Can I
create
> a
> > SP that would include all batch statements?
> >
> > I tried to use this model that works well for me:
> >
> > CREATE PROC dbo.uspSaveRandomSeal
> > @.sTable varchar(25)
> > AS
> > DECLARE @.SQLx NVARCHAR(1024)
> > SET @.SQLx = N'
> > IF EXISTS (SELECT name FROM sysobjects WHERE name= ''' +
> > @.sTable + N''' AND type=''U'')
> > DROP TABLE pafo.' + @.sTable+ N'
> > SELECT dbo.vuRandomSeal.*
> > INTO pafo.'+@.sTable+N'
> > FROM dbo.vuRandomSeal'
> >
> > EXEC sp_ExecuteSQL @.SQLx
> >
> > but I'm getting too many errors, and I'm not sure whether I'm doing it
> > right.
> >
> > I would appreciate your suggestions.
> >
> > Thank you,
> > --
> > Peter Afonin
> >
> >
>|||Hi,
Dont go for procedure, instead save the contents in to a .SQL file .
Then create a .BAT file with
OSQL -Uuser -Ppassword -Sserver -ic:\act.sql -oc:\act.out
Then schedule the batch using SQL Agent job with type as "Operating system
command".
Thanks
Hari
MCDBA
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:uwia9oGtDHA.4056@.TK2MSFTNGP11.phx.gbl...
> Thanks, Brian.
> I'll enclose the whole batch. If I try to create SP like this:
> CREATE PROC usp2003December
> AS
> DECLARE @.SQLx varchar(7999)
> SET @.SQLx=N'(
> ALTER VIEW vuMonthlySalesOpenOrds
> AS
> SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> 2)),
> MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> FROM OPENORDS
> WHERE
> C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) AND
> CLASS='SMI'
> GROUP BY CUSNO
> GO
> ALTER VIEW vuMTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_MTD_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
> '12/31/2003'
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuPYSSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_PYS_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, '12/31/2003', 101))+1 Between
Start_Date
> and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, '12/31/2003', 101))+1 Between
Start_Date
> and End_Date)
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2003' And '12/31/2003'
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuPYSYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_PYS_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And DateAdd(year, -1, '12/31/2003')
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuYTDFullSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_FULL_HIST,
> CAST(Sum(INV_AMT) AS numeric(10, 2)) INV_AMT_YTD_FULL_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And '12/31/2002'
> GROUP BY CUST_NBR
> GO
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[tbl2003December]') and OBJECTPROPERTY(id,
N'IsUserTable')
> = 1)
> drop table [dbo].[tbl2003December]
> GO
> CREATE TABLE [dbo].[tbl2003December] (
> [Invoice CMS MTD CS] [float],
> [Invoice CMS MTD $$] [float],
> [PYS Full Month CS] [float],
> [PYS Full Month $$] [float],
> [MTD CS %] [float],
> [MTD $$ %] [float],
> [MTD Open Orders CS] [float],
> [MTD Open Orders $$] [float],
> [MTD Open + Invoice CS] [float],
> [MTD Open + Invoice $$] [float],
> [MTD CS % 2] [float],
> [MTD $$ % 2] [float],
> [YTD Sales CS] [float],
> [YTD Sales $$] [float],
> [PYS YTD CS] [float],
> [PYS YTD $$] [float],
> [YTD CS %] [float],
> [YTD $$ %] [float],
> [Full Year YTD CS] [float],
> [Full Year YTD $$] [float],
> [Full Year CS %] [float],
> [Full Year $$ %] [float],
> [CusNo] [decimal](6, 0) NOT NULL ,
> [Customer] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SalesNo] [decimal](3, 0) NULL ,
> [SalesName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateTime] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO tbl2003December
> SELECT vuSalesAnalysisFull.*
> FROM vuSalesAnalysisFull
> GO
> ALTER VIEW vuMonthlySalesOpenOrds
> AS
> SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> 2)),
> MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> FROM OPENORDS
> WHERE
> C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) AND
> CLASS='SMI'
> GROUP BY CUSNO
> GO
> ALTER VIEW vuMTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_MTD_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) And
> getdate()
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuPYSSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_PYS_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, GETDATE(), 101))+1 Between Start_Date
> and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, GETDATE(), 101))+1 Between Start_Date
> and End_Date)
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2003' And getdate()
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuPYSYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_PYS_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And DateAdd(year, -1, getdate())
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuYTDFullSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_FULL_HIST,
> CAST(Sum(INV_AMT) AS numeric(10, 2)) INV_AMT_YTD_FULL_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And '12/31/2002'
> GROUP BY CUST_NBR
> GO
> '
> GO
> EXEC sp_ExecuteSQL @.SQLx
>
/***************************************************************************
> **************/,
> I get these errors like this:
> Server: Msg 170, Level 15, State 1, Procedure usp2003December, Line 14
> Line 14: Incorrect syntax near '12'.
> Server: Msg 105, Level 15, State 1, Procedure usp2003December, Line 16
> Unclosed quotation mark before the character string '
> GROUP BY CUSNO
> '.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
> Server: Msg 137, Level 15, State 2, Line 2
> Must declare the variable '@.SQLx'.
> I tried to play with quotes, but still was getting errors.
> Peter
>
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:O4GKDAGtDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > I'm not sure I 100% understand the question? Yes, SP's are a good way to
> > manage batches of work. Also consider DTS and SQL Agent.
> >
> > What are the errors you're getting?
> >
> > --
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
> > news:OKunGiFtDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > I have a pretty big batch that I need to execute on schedule basis. I
> > alters
> > > several views, then creates a table and updates this table using these
> > > modified views:
> > >
> > > ALTER VIEW vuMonthlySalesOpenOrds
> > > AS
> > > SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> > > MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS
numeric(8,
> > > 2)),
> > > MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> > > FROM OPENORDS
> > > WHERE
> > > C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> > > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
> And
> > > (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> > > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
> AND
> > > CLASS='SMI'
> > > GROUP BY CUSNO
> > > GO
> > >
> > >
> >
>
/***************************************************************************
> > > **************/
> > >
> > > ALTER VIEW vuMTDSalesHistory
> > > AS
> > > SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> > > QTY_MTD_HIST,
> > > CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> > > FROM SALEHIST
> > > WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> > > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
> And
> > > '12/31/2003'
> > > GROUP BY CUST_NBR
> > > GO
> > >
> >
>
/***************************************************************************
> > > **************/
> > > ................................................
> > > ................................................
> > >
> > > INSERT INTO tbl2003December
> > > SELECT vuSalesAnalysisFull.*
> > > FROM vuSalesAnalysisFull
> > >
> > > What would be the best way to schedule this batch execution? Can I
> create
> > a
> > > SP that would include all batch statements?
> > >
> > > I tried to use this model that works well for me:
> > >
> > > CREATE PROC dbo.uspSaveRandomSeal
> > > @.sTable varchar(25)
> > > AS
> > > DECLARE @.SQLx NVARCHAR(1024)
> > > SET @.SQLx = N'
> > > IF EXISTS (SELECT name FROM sysobjects WHERE name= ''' +
> > > @.sTable + N''' AND type=''U'')
> > > DROP TABLE pafo.' + @.sTable+ N'
> > > SELECT dbo.vuRandomSeal.*
> > > INTO pafo.'+@.sTable+N'
> > > FROM dbo.vuRandomSeal'
> > >
> > > EXEC sp_ExecuteSQL @.SQLx
> > >
> > > but I'm getting too many errors, and I'm not sure whether I'm doing it
> > > right.
> > >
> > > I would appreciate your suggestions.
> > >
> > > Thank you,
> > > --
> > > Peter Afonin
> > >
> > >
> >
> >
>|||Methinks you can't do ALTER VIEW in same batch as other commands (i.e., not in stored procedure). I
suggest you create an Agent job with one TSQL jobstep for each ALTER VIEW, quite simply!
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
news:uwia9oGtDHA.4056@.TK2MSFTNGP11.phx.gbl...
> Thanks, Brian.
> I'll enclose the whole batch. If I try to create SP like this:
> CREATE PROC usp2003December
> AS
> DECLARE @.SQLx varchar(7999)
> SET @.SQLx=N'(
> ALTER VIEW vuMonthlySalesOpenOrds
> AS
> SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> 2)),
> MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> FROM OPENORDS
> WHERE
> C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) AND
> CLASS='SMI'
> GROUP BY CUSNO
> GO
> ALTER VIEW vuMTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_MTD_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date) And
> '12/31/2003'
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuPYSSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_PYS_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, '12/31/2003', 101))+1 Between Start_Date
> and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, '12/31/2003', 101))+1 Between Start_Date
> and End_Date)
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2003' And '12/31/2003'
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuPYSYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_PYS_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And DateAdd(year, -1, '12/31/2003')
> GROUP BY CUST_NBR
> GO
>
> ALTER VIEW vuYTDFullSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_FULL_HIST,
> CAST(Sum(INV_AMT) AS numeric(10, 2)) INV_AMT_YTD_FULL_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And '12/31/2002'
> GROUP BY CUST_NBR
> GO
> if exists (select * from dbo.sysobjects where id => object_id(N'[dbo].[tbl2003December]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[tbl2003December]
> GO
> CREATE TABLE [dbo].[tbl2003December] (
> [Invoice CMS MTD CS] [float],
> [Invoice CMS MTD $$] [float],
> [PYS Full Month CS] [float],
> [PYS Full Month $$] [float],
> [MTD CS %] [float],
> [MTD $$ %] [float],
> [MTD Open Orders CS] [float],
> [MTD Open Orders $$] [float],
> [MTD Open + Invoice CS] [float],
> [MTD Open + Invoice $$] [float],
> [MTD CS % 2] [float],
> [MTD $$ % 2] [float],
> [YTD Sales CS] [float],
> [YTD Sales $$] [float],
> [PYS YTD CS] [float],
> [PYS YTD $$] [float],
> [YTD CS %] [float],
> [YTD $$ %] [float],
> [Full Year YTD CS] [float],
> [Full Year YTD $$] [float],
> [Full Year CS %] [float],
> [Full Year $$ %] [float],
> [CusNo] [decimal](6, 0) NOT NULL ,
> [Customer] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [SalesNo] [decimal](3, 0) NULL ,
> [SalesName] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [DateTime] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
> INSERT INTO tbl2003December
> SELECT vuSalesAnalysisFull.*
> FROM vuSalesAnalysisFull
> GO
> ALTER VIEW vuMonthlySalesOpenOrds
> AS
> SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> 2)),
> MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> FROM OPENORDS
> WHERE
> C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) AND
> CLASS='SMI'
> GROUP BY CUSNO
> GO
> ALTER VIEW vuMTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_MTD_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> CONVERT(VARCHAR, GETDATE(), 101) Between Start_Date and End_Date) And
> getdate()
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuPYSSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_HIST,
> CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_PYS_HIST
> FROM SALEHIST
> WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, GETDATE(), 101))+1 Between Start_Date
> and End_Date) And
> (SELECT End_Date FROM tblFiscalCalendar2002 WHERE
> DateAdd(year, -1, CONVERT(VARCHAR, GETDATE(), 101))+1 Between Start_Date
> and End_Date)
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2003' And getdate()
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuPYSYTDSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_PYS_YTD_HIST,
> CAST(Sum(INV_AMT) AS float) INV_AMT_PYS_YTD_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And DateAdd(year, -1, getdate())
> GROUP BY CUST_NBR
> GO
> ALTER VIEW vuYTDFullSalesHistory
> AS
> SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> QTY_YTD_FULL_HIST,
> CAST(Sum(INV_AMT) AS numeric(10, 2)) INV_AMT_YTD_FULL_HIST
> FROM SALEHIST
> WHERE IDT Between '1/1/2002' And '12/31/2002'
> GROUP BY CUST_NBR
> GO
> '
> GO
> EXEC sp_ExecuteSQL @.SQLx
> /***************************************************************************
> **************/,
> I get these errors like this:
> Server: Msg 170, Level 15, State 1, Procedure usp2003December, Line 14
> Line 14: Incorrect syntax near '12'.
> Server: Msg 105, Level 15, State 1, Procedure usp2003December, Line 16
> Unclosed quotation mark before the character string '
> GROUP BY CUSNO
> '.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string '
> '.
> Server: Msg 137, Level 15, State 2, Line 2
> Must declare the variable '@.SQLx'.
> I tried to play with quotes, but still was getting errors.
> Peter
>
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:O4GKDAGtDHA.2448@.TK2MSFTNGP09.phx.gbl...
> > I'm not sure I 100% understand the question? Yes, SP's are a good way to
> > manage batches of work. Also consider DTS and SQL Agent.
> >
> > What are the errors you're getting?
> >
> > --
> >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Peter Afonin" <pafo@.specialtypulltabs.com> wrote in message
> > news:OKunGiFtDHA.2244@.TK2MSFTNGP09.phx.gbl...
> > > Hello,
> > >
> > > I have a pretty big batch that I need to execute on schedule basis. I
> > alters
> > > several views, then creates a table and updates this table using these
> > > modified views:
> > >
> > > ALTER VIEW vuMonthlySalesOpenOrds
> > > AS
> > > SELECT TOP 100 PERCENT CAST(CUSNO AS varchar(6)) CUST_NBR,
> > > MTD_Open_USD=CAST(Sum(([QUANO]-[QUANS]+[QUANN])*[ACTSP]) AS numeric(8,
> > > 2)),
> > > MTD_Open_CS= CAST(Sum([QUANO]-[QUANS]+[QUANN]) AS int)
> > > FROM OPENORDS
> > > WHERE
> > > C2SDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> > > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
> And
> > > (SELECT End_Date FROM tblFiscalCalendar2003 WHERE
> > > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
> AND
> > > CLASS='SMI'
> > > GROUP BY CUSNO
> > > GO
> > >
> > >
> >
> /***************************************************************************
> > > **************/
> > >
> > > ALTER VIEW vuMTDSalesHistory
> > > AS
> > > SELECT TOP 100 PERCENT CAST(CUST_NBR AS varchar(6)) CUST_NBR, Sum(QTY)
> > > QTY_MTD_HIST,
> > > CAST(Sum(INV_AMT) AS float(8, 2)) INV_AMT_MTD_HIST
> > > FROM SALEHIST
> > > WHERE IDT Between (SELECT Start_Date FROM tblFiscalCalendar2003 WHERE
> > > CONVERT(VARCHAR, '12/31/2003', 101) Between Start_Date and End_Date)
> And
> > > '12/31/2003'
> > > GROUP BY CUST_NBR
> > > GO
> > >
> >
> /***************************************************************************
> > > **************/
> > > ................................................
> > > ................................................
> > >
> > > INSERT INTO tbl2003December
> > > SELECT vuSalesAnalysisFull.*
> > > FROM vuSalesAnalysisFull
> > >
> > > What would be the best way to schedule this batch execution? Can I
> create
> > a
> > > SP that would include all batch statements?
> > >
> > > I tried to use this model that works well for me:
> > >
> > > CREATE PROC dbo.uspSaveRandomSeal
> > > @.sTable varchar(25)
> > > AS
> > > DECLARE @.SQLx NVARCHAR(1024)
> > > SET @.SQLx = N'
> > > IF EXISTS (SELECT name FROM sysobjects WHERE name= ''' +
> > > @.sTable + N''' AND type=''U'')
> > > DROP TABLE pafo.' + @.sTable+ N'
> > > SELECT dbo.vuRandomSeal.*
> > > INTO pafo.'+@.sTable+N'
> > > FROM dbo.vuRandomSeal'
> > >
> > > EXEC sp_ExecuteSQL @.SQLx
> > >
> > > but I'm getting too many errors, and I'm not sure whether I'm doing it
> > > right.
> > >
> > > I would appreciate your suggestions.
> > >
> > > Thank you,
> > > --
> > > Peter Afonin
> > >
> > >
> >
> >
>
devi_anitha wrote:
