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 schedule an SSIS package that invokes a web service

Hey,

I have an SSIS package that invokes a web service and then updates a table. It runs fine as long as I am running it on the local machine. However, as soon as I save this package to the sql server, and try to schedule this as a job, it starts to fail. Now, the web service writes to an xml file and also uses an xsd and and an xsl file. When I save a dts package to the sql server, whats the proper way of referencing these files? I think this probably is what is making the package to fail, ut I am not sure.

Any help is greatly appreciated!!

Thanks!

You should use a configuration (right-click in the package and choose configurations) to set the ConnectionString property of the connection managers for the files. Or you could use expressions to set the connection strings (paths and filenames) based on variables. The variables can be set at runtime using the /SET option of DTEXEC.|||Also make sure you've configured SSIS logging, so you can find out why the package fails now or (once you fix the problem and go to production) if something goes wrong with scheduled package in production.|||That depends on where you want to keep them. I prefer to keep them in files on the disk. If your package is in SQL and you prefer to avoid the disk entirely, you can keep them in the database and just load them into variables via the Execute SQL task. The XML task and the XML Source component support receiving the XSD/XSLT from variables.
|||Thanks a lot for the suggestions. I will try them out and see how it works.|||

Hey,

Sorry for this delayed reply. Since I posted this question a lot of issues cropped up with my SQL server which eventually led to a total reinstallation of all apps on my pc. Anyway, I discovered that the problem I have been having was because of permission issues. I was able to fix that problem and just when I thought that I had everything going, I came across a new problem. After I save the SSIS package in sql server and create a job, the job starts failing. This is the error that I am getting:

-1073548540,0x,An error occurred with the following error message: "Microsoft.SqlServer.Dts.Tasks.WebServiceTask.WebserviceTaskException: The Web Service threw an error during method execution. The error is: Unable to connect to the remote server.

Any suggestions would be very helpful.

Thanks!

|||Could it be a authentication issue? Is there any security on the web service?|||

Guys!!Thanks a lot for all the suggestions. Really appreciate your help. Problem was a combination of many issues. One was related to 32bit/64bit differences, the other was authentication, and finally some syntax problems when invoking the web service. Seems it is working really well now.

Thanks again!

How to run my Curser more faster?

I have a stored procedure written for number of updates, inserts to the
database. This stored procedure takes too long to run. I have created few
indexes around few tables, but the response time is not that defferent.
THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
another stored procedure is called within this Curser. I found that lot of
time been taken to run this Curser. So I want to run/make it faster the
curser. So how should I improve the Curser to run more faster
I would greatly appreciate anyone's idea or suggesions.
thanks
raj
"raj" <raj@.discussions.microsoft.com> wrote in message
news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> I have a stored procedure written for number of updates, inserts to the
> database. This stored procedure takes too long to run. I have created few
> indexes around few tables, but the response time is not that defferent.
> THere is a Curser "CURSOR FAST_FORWARD FOR" in the stored procedure, and
> another stored procedure is called within this Curser. I found that lot of
> time been taken to run this Curser. So I want to run/make it faster the
> curser. So how should I improve the Curser to run more faster
> I would greatly appreciate anyone's idea or suggesions.
Best Advice: Eliminate the Cursor.
But, without a DDL and some sample table folks here won't be able to help
you with that.
So, if you can, post your DDL and some data and see what folks can do.

> thanks
> raj
|||this is the part of the query takes too long to run
BEGIN
DECLARE CustCursor CURSOR FAST_FORWARD FOR
SELECT DISTINCT D.CustID FROM tblInv D INNER JOIN #Invoice I ON D.InvID =
I.InvID
OPEN CustCursor
FETCH NEXT FROM CustCursor INTO @.CustID
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
EXEC sp_UpdateDunning @.CustID
IF @.@.Error <> 0
BEGIN
CLOSE CustCursor
DEALLOCATE CustCursor
ROLLBACK
RAISERROR('Error executing - > sp_UpdateDunning.', 16, 1)
RETURN
END
FETCH NEXT FROM CustCursor INTO @.CustID
END
CLOSE CustCursor
DEALLOCATE CustCursor
END
All the tables involved in this curser, indexes have been created.
raj
"Greg D. Moore (Strider)" wrote:

> "raj" <raj@.discussions.microsoft.com> wrote in message
> news:618E892D-9327-41F8-8160-3E84EE32303A@.microsoft.com...
> Best Advice: Eliminate the Cursor.
> But, without a DDL and some sample table folks here won't be able to help
> you with that.
> So, if you can, post your DDL and some data and see what folks can do.
>
>
>
|||The following approach would sacrify the transactional
opertion on the batch, but is likely the fast.
-- ===============
-- Run the script at QA
-- ===============
declare @.CustID int, @.cmd varchar(255)
delcare tmp_cur cursor for
select distinct D.CustID
from tblInv D inner join #Invoice I
on D.InvID = I.InvID
open tmp_cur
fetch next from tmp_cur into @.CustID
while @.@.fetch_status = 0
begin
select @.cmd = 'exec sp_UpdateDunning ' + convert
(varchar, @.CustID)
print @.cmd
print 'go'
fetch next from tmp_cur into @.CustID
end
close tmp_cur
deallocate tmp_cur
-- ===============
-- Run the result of script at QA
-- ===============
For example:
exec sp_UpdateDunning 1
go
exec sp_UpdateDunning 2
go
...
^^
Regards,
Alfred

>--Original Message--
>this is the part of the query takes too long to run
>----
>BEGIN
>DECLARE CustCursor CURSOR FAST_FORWARD FOR
>SELECT DISTINCT D.CustID FROM tblInv D
INNER JOIN #Invoice I ON D.InvID =
>I.InvID
>OPEN CustCursor
>FETCH NEXT FROM CustCursor INTO @.CustID
>WHILE (@.@.FETCH_STATUS <> -1)
>BEGIN
>EXEC sp_UpdateDunning @.CustID
>IF @.@.Error <> 0
>BEGIN
>CLOSE CustCursor
>DEALLOCATE CustCursor
>ROLLBACK
>RAISERROR('Error executing - >
sp_UpdateDunning.', 16, 1)
>RETURN
>END
>FETCH NEXT FROM CustCursor INTO @.CustID

>END
>CLOSE CustCursor
>DEALLOCATE CustCursor
>END
>----
--
>All the tables involved in this curser, indexes have been
created.[vbcol=seagreen]
>raj
>
>"Greg D. Moore (Strider)" wrote:
3E84EE32303A@.microsoft.com...[vbcol=seagreen]
updates, inserts to the[vbcol=seagreen]
run. I have created few[vbcol=seagreen]
not that defferent.[vbcol=seagreen]
stored procedure, and[vbcol=seagreen]
Curser. I found that lot of[vbcol=seagreen]
run/make it faster the[vbcol=seagreen]
more faster[vbcol=seagreen]
suggesions.[vbcol=seagreen]
won't be able to help[vbcol=seagreen]
what folks can do.
>.
>

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to Rollback Transactions

hi everybody,

i have 4 flat files from a source folder which updates four different tables, this has to be done parallely,on success of this transaction the files have to be moved to another folder.

my problem comes here,now if there is any problem in moving any file to another folder,that particular transaction has to be rolledback without affecting others.i tried setting the transaction property of the control flow,but it rollbacks all the transaction..

please help me on this

You can scope transactions to a container, not just a package. Use Sequence containers to give some separation between the four load processes and their file operations.

You could also use the manual method of managing transactions, (http://blogs.conchango.com/jamiethomson/archive/2005/08/20/SSIS-Nugget_3A00_-RetainSameConnection-property-of-the-OLE-DB-Connection-Manager.aspx), just repeat the pattern four times, once for each load, with four separate connections, one for each load, and the associate SQL tasks used to manage thetransaction.

|||

hi darren,

thanks for your post it was very helpful.

i have used foreach containers for the four load process as i can get multiple files.

now i have one more problem,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

|||

You could make the foreach loop to go through all files and then use expressions in the precedence constraints to decide whether a file needs to be processed or not. You may need aditional variables to get the dates and compare it. A simplier example here:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

How to return a string from a Stored Procedure

Up till now I've used SP's for updates and only ever needed to return error messages.

Now I have an SP that checks and validates something and has to return a string containing the result, (always a string/varchar!)

It works fine in Query Analyzer, I just need a demo of how to incorporate it into a VB app.

Hope that makes sense.

Thanks
Markuse OUTPUT Parameters...check BOL for more info..

hth|||Thanks, I already have that.

My question was how to return it into VB.Net|||if you mean how to catch the returned value from vb.net

...
'output parameter
myParam = mycommand.CreateParameter()
myParam.ParameterName = "@.result"
myParam.Direction = ParameterDirection.Output
myParam.SqlDbType = SqlDbType.varchar
mycommand.Parameters.Add(myParam)
mycommand.ExecuteNonQuery()
return convert.todouble((mycommand.Parameters("@.result").Value))
objcon.close
...

hth|||OK, I got what I wanted.

I hadn't thought of using ExecuteScalar with a SP, just needed to broaden my thinking a bit.

so...abbreviated...here's what I did.

Dim con as New SqlConnection("myConnectionString")
Dim cmd = New SqlCommand("mySPname",con)
cmd.CommandType=CommandType.StoredProcedure
cmd.Parameters.Add("@.ItemID",12345)
" " "
" " "
Try
cmd.Connection = con
cmd.Connection.Open()
x = cmd.ExecuteScalar()
Catch exp As SqlException
x = exp.Message
Finally
cmd.Collnection.Close()
End Try

This way I either get the string value I was looking for or the error message.