How to schedule batch execution

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

How to save PDF files to SQL Server?

Hellow, everyone:

I have PDF files on local hard drive, and want to save them to the table in SQL Server. Can I execute by SQL Server? If yes, how to do that.

Someone said to create a table with IMAGE column, and write PDF files to this table. How about details for this way?

Thanks a lot.

ZYTWhile you can store files inside of SQL Server, I recommend against it. Files are a "complex data type", so you can't sort, index, compare, or do any other practical operations with them. You can however store a UNC name for a file in a database, and there are many parctical things you can do with that.

There are some reasons for considering storing files inside of SQL Server, but they are few and far between and all of the reasons that I know about are kludges to address application design flaws. If you can avoid the headaches associated with storing files inside your database, I'd recommend avoiding it!

-PatP|||Hi, Pat:

Thanks for reply.

I have to save PDF files to SQL Server, and then retreive/dispaly them. Because security issue, the PDF files are not allowed put in web server, so frontend code cannot read them. PDF files are in database server.

Can you offer codes that can write/read PDF to/from SQL Server? Thanks a lot.

ZYT

How to run multiple dependent SQL Jobs with OSQL

I want to execute multiple SQL Server scheduled jobs using OSQL, but I only want to execute a job if the previous job completed. Is this possible? So far I am unable to prevent all jobs from starting before any have completed.

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber1'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber2'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR

OSQL -Sservername -E -b -Q"usp_start_job 'JobNumber3'" -o usp_start_job.out
IF ERRORLEVEL 1 GOTO ERROR
GOTO EXIT

:ERROR
ECHO *** ERROR *** Check Log File

:EXIT
*** JOB COMPLETED ***

DaveCreate a new job that uses separate steps consisting of an sp_start_job to launch one of the other jobs. Each step will execute in turn, launching the appropriate job.

-PatP|||I already tried that with no success. The logic looks as follows:

sp_start_job 'DBA Test' -- Inserts one record into a table
go
sp_start_job 'DBA Test2' -- Waits 20 seconds and inserts a record
go
sp_start_job 'DBA Test3' -- Insert a record
go

All jobs execute immediately.

Dave|||That looks suspiciously like a SQL script instead of a job with a set of job steps to me.

-PatP|||Lightbulb.

We had a DR test this week and apparently I'm still experiencing the affects. Thanks for the help. That should do the trick.

Dave|||I spoke too soon. I added sp_start_job 'job name' to three different job steps and the same problem occurs. All three jobs are executed before any job completes. I believe the reason is due to sp_start_job. SQL Server successfully executes the command, which simply starts the job. It doesn't care whether or not the job it started has completed.

Any suggestions.

Dave

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guys
really understand my problem. if u have any doubt reply me. thanks in advance.
-suresh
No. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas

"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-suresh

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guys
really understand my problem. if u have any doubt reply me. thanks in advance.
-sureshNo. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas
"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-sureshsql

How to run asp page from sql server?

Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u guy
s
really understand my problem. if u have any doubt reply me. thanks in advanc
e.
-sureshNo. There is not. All of the SQL Server executions need to be run
non-interactively. ASP is an interactive system. Why not just code some
ActiveX script or COM object to execute?
You can use the xp_cmdshell or any of the sp_OA stored procedures.
What is that ASP code trying to do?
Sincerely,
Anthony Thomas
"suresh" <suresh@.discussions.microsoft.com> wrote in message
news:8C292B13-4E4A-442E-AFD1-A36E082D112C@.microsoft.com...
Hi ,
I have one asp page that send sms to the users. i just want to send that
sms in a particular time. Since i coudnt execute that asp page in a
particular time, i just want to make use of sql server job Schedule. i used
the sql server job to run that particular asp page using xp_cmdshell stored
procedure. i gave the following pl/sql statement to run that asp page.
exec xp_cmdshell "start test.asp"
but that job exectues sucessfully. but actually that page is not exected. is
there any other way to run a asp web page using sql job option? i hope u
guys
really understand my problem. if u have any doubt reply me. thanks in
advance.
-suresh

How to run a console program in ssis?

I need to execute a console program and capture its output.

What's the best way to do it?

(no xp_cmdshell approach)

Thanks,

The Execute Process task is used for this. Michael Entin just posted about this: http://blogs.msdn.com/michen/archive/2007/08/02/redirecting-output-of-execute-process-task.aspx

How to run "Execute Package" Task Conditionally in SSIS?

I have a boolean variable in my package. How to run "Execute Package" Task Conditionally using that variable?
For example if the variable is True, I want to run the Task else just skip this task and continue to the next one. The Control flow should continue after this task. How do I achieve this?

How to do the same for "Execute SQL" task?Lets assume this simple scenario:

Task 1 > Execute package (task 2) > Task 3

Lets change this for the desired behavior.
After Task1, have one arrow go to Execute package task, as above AND have another arrow go from Task 1 to Task 3 directly.

Now, right-click on BOTH the arrows and specify expression on each of them. One expression will be @.my_variable = true and another will be @.my_variable = false.

Depending upon value of @.my_variable, one of the paths will be taken.

HTH,
Nitesh|||This doe't help I tried it already. It breaks the flow. The control never reaches to Task3. I want to just skip Task2. In Both case I want to run Task3. When the condition is not met for Task2, the control flow breaks there. According to MS, that is by design. I have no idea to about how to achieve this. Any help will be appreciated.|||Ok, Let's try this. Have the control flow as follows:
Task 1 -> Task 2 -> Task 3
And another path directly from Task 1 -> Task 3
For Task 2, set the expression for "Disable" property based on the variable value you want to use to decide whether Task 2 should be executed or not.

If that variable is true, Task 2 will be disabled, if not it will remain enabled. Let me know if that lets you accomplish your scenario.

Thanks
Ranjeeta|||

See this thread:
http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=63760
K

|||

mci wrote:

This doe't help I tried it already. It breaks the flow. The control never reaches to Task3. I want to just skip Task2. In Both case I want to run Task3. When the condition is not met for Task2, the control flow breaks there. According to MS, that is by design. I have no idea to about how to achieve this. Any help will be appreciated.

Place task1 and task2 in a sequence container. Task3 should execute after the sequence container. You can put an expression on the precedence constraint between task1 and task2 to specify whether task2 should execute or not.

More details here: http://blogs.conchango.com/jamiethomson/archive/2005/07/27/1889.aspx. The bit in the "UPDATE" section at the bottom illustrates exactly what I have described here.

-Jamie

How to return the result of an EXEC from a function

Hi,

I am trying to find a way to return the result of an EXEC(*sqlstring*) from a function. I can return the tsql but not the result of an execute.

This is my function:

ALTER FUNCTION [dbo].[ReturnPickItemValue]
(
-- Add the parameters for the function here
@.TypeID int,
@.CaseID int
)
RETURNS varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1
BEGIN
SET @.RTN = 'SELECT PickItem I +
CASE D.IsStartDateEstimated
WHEN 0 THEN CAST(StartDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)
END +
CASE D.IsEndDateEstimated
WHEN 0 THEN CAST(EndDate as varchar)
ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)
END

FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID
LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND P.CaseID = ' + CAST(@.CaseID as varchar)
END
ELSE
BEGIN
SET @.RTN=
'SELECT I.PickItem
FROM TBL_LU_PICK L
INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID
INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID
WHERE L.PickTypeID = ' + CAST(@.TypeID as varchar) + '
AND CaseID = ' + CAST(@.CaseID as varchar)
END

RETURN @.RTN

END

Each time I try " RETURN EXEC(@.RTN) " or something similar I get an error.

I have tried executing the tsql and assigning the result to a varchar and returning that varchar but i get an error.

Anyone with any ideas?

You need to give a look to the CREATE FUNCTION article in books online. There are a number of constraints on the DML that can be used inside of a function. In this case you are being stopped by the constraint that does not allow use of the EXEC ( @.anSQLString ) inside of a function. You are also not allowed to execute stored procedures from inside the body of a function. I would suggest that if you want to run an EXEC ( @.someKindOfString ) that you really ought to use a stored procedure instead of a function. Since the problem is dynamic SQL you might want to first see if you can eliminate the dynamic SQL.

Kent

|||

Following on from Kent, in looking at your query do you actually need to build a dynamic string at all?

Code Snippet

CREATE FUNCTION [dbo].[ReturnPickItemValue]

(

-- Add the parameters for the function here

@.TypeID int,

@.CaseID int

)

RETURNS varchar(max)

AS

BEGIN

-- Declare the return variable here

DECLARE @.RTN varchar(max)

IF(SELECT IncludeDates FROM TBL_LU_PICK WHERE PickTypeID = @.TypeID) = 1

BEGIN

SET @.Rtn = (SELECT I.PickItem +

CASE D.IsStartDateEstimated

WHEN 0 THEN CAST(StartDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsStartDateEstimated, 0) as varchar)

END +

CASE D.IsEndDateEstimated

WHEN 0 THEN CAST(EndDate as varchar)

ELSE CAST(dbo.ReturnEstimatedDate(D.IsEndDateEstimated, 1) as varchar)

END

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_PICK P ON P.PickItemID = I.PickItemID

LEFT JOIN TBL_PickDates D ON D.PickID = P.PickID

WHERE L.PickTypeID = @.TypeID

AND P.CaseID = @.CaseID)

END

ELSE

BEGIN

SET @.Rtn = ( SELECT I.PickItem

FROM TBL_LU_PICK L

INNER JOIN TBL_Pick_Items I ON I.PickTypeID = L.PickTypeID

INNER JOIN TBL_Pick P ON P.PickItemID = I.PickItemID

WHERE L.PickTypeID = @.TypeID

AND CaseID = @.CaseID)

END

RETURN @.Rtn

END

HTH!

|||cheers richbrownesq

That works a treat!!

how to retrive a SP table inside a SP?

Hello, inside of my SP i want to execute another SP, something like:

EXEC

[dbo].[Forum_DeleteBoard] @.BoardID= @.DelBoardID

this function Forum_DeleteBoard returs one row with 3 columns as a table, how do i get the first column of that table into a variable so i can check if it was ok or not
(it returns just one row with 3 columns).

Columns it returns:
QResult , Threads , Answers

SELECT @.isok = QResult FROM EXEC [dbo].[Forum_DeleteBoard] @.BoardID= @.DelBoardID ?

or how do you get it?

Patrick

you can try to do it by creating temp table and next by do insert from your stored procedure into this temp table. Temp table have to have the same layout as returned table. and you can select value you need from this table.

If you can modify your second procedure try to modify it to return output parameters instead of table result set. It will be simple and much faster.

You can find information about both solutions in SQL help, but if you have a problem with it just post again.

Thanks