How to Script Muplitple SPROCS/VIEWS at one time

In Enterprise Manager one can select several SPROCS/VIEWS using the
CONTROL key and then Right-Click to script out those objects.
Alternativly, pressing CONTROL-C copies, to the clipboard, the T-SQL to
create the selected objects.
SQL Management Studio seems to only allow you to script one object at a
time.
Is there a way in SQL Management Studio to select multiple objects and
generate create or modify scripts?Hi, naviagte to the database node and script the objects using the
wizard, select only the procedures you want to script.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--

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 SQL 2005 solution?

Hi,
I have a solution within SQL 2005, which contains scripts of all
stored procs and views so that they are in source control. My
question is, how can I save this so that every time I open up SQL 2005
management strudio, it appears in the right hand pane, instead of
having to open it up every time.
Thanks for any assistance.How about having a shortcut for the sln file on your desktop?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172157510.539730.34010@.k78g2000cwa.googlegroups.com...
> Hi,
> I have a solution within SQL 2005, which contains scripts of all
> stored procs and views so that they are in source control. My
> question is, how can I save this so that every time I open up SQL 2005
> management strudio, it appears in the right hand pane, instead of
> having to open it up every time.
> Thanks for any assistance.
>|||On Feb 22, 3:29 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> How about having a shortcut for the sln file on your desktop?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://www.solidqualitylearning.com/
> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> news:1172157510.539730.34010@.k78g2000cwa.googlegroups.com...
> > Hi,
> > I have a solution within SQL 2005, which contains scripts of all
> > stored procs and views so that they are in source control. My
> > question is, how can I save this so that every time I open up SQL 2005
> > management strudio, it appears in the right hand pane, instead of
> > having to open it up every time.
> > Thanks for any assistance.
Is that the only way? Also, it is very slow loading them all. Think
I might just stick to using souce safe without the solution.

How to save SQL 2005 solution?

Hi,
I have a solution within SQL 2005, which contains scripts of all
stored procs and views so that they are in source control. My
question is, how can I save this so that every time I open up SQL 2005
management strudio, it appears in the right hand pane, instead of
having to open it up every time.
Thanks for any assistance.How about having a shortcut for the sln file on your desktop?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"nomad" <d.bedgood@.ntlworld.com> wrote in message
news:1172157510.539730.34010@.k78g2000cwa.googlegroups.com...
> Hi,
> I have a solution within SQL 2005, which contains scripts of all
> stored procs and views so that they are in source control. My
> question is, how can I save this so that every time I open up SQL 2005
> management strudio, it appears in the right hand pane, instead of
> having to open it up every time.
> Thanks for any assistance.
>|||On Feb 22, 3:29 pm, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:[vbcol=seagreen]
> How about having a shortcut for the sln file on your desktop?
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://www.solidqualitylearning.com/
> "nomad" <d.bedg...@.ntlworld.com> wrote in message
> news:1172157510.539730.34010@.k78g2000cwa.googlegroups.com...
>
>
>
Is that the only way? Also, it is very slow loading them all. Think
I might just stick to using souce safe without the solution.

How to run *.sql ddl automatically

I have tables, views, functions, procedures created in a database with test
data
I would like to be able to automatically kick off the Create sql files via a
script/bat file and bypass having to open all the *sql files in Query
Analyzer.
Can anyone point me to knowledge based articles or How-Tos on this topic?
thx
use copy command to append all files into one, and run osql with -i to input
the file, lastly, schedule osql in a scheduled batch process, or SQL job.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test
>data
> I would like to be able to automatically kick off the Create sql files via
> a script/bat file and bypass having to open all the *sql files in Query
> Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>
|||For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several files, use FOR in your
batch file:
for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TroyS" <troy.stauber@.ilg.com> wrote in message news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test data
> I would like to be able to automatically kick off the Create sql files via a script/bat file and
> bypass having to open all the *sql files in Query Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>
|||thx. i will try the FOR as i have multiple files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237qXfcoAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several
> files, use FOR in your batch file:
> for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>
|||thx. i'm having a blond moment and had in my mind multiple files to deal
with rather than just paste everything into 1 file.
"Richard Ding" <richard.ding@.monster.com> wrote in message
news:u8RjaYoAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> use copy command to append all files into one, and run osql with -i to
> input the file, lastly, schedule osql in a scheduled batch process, or SQL
> job.
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>

How to run *.sql ddl automatically

I have tables, views, functions, procedures created in a database with test
data
I would like to be able to automatically kick off the Create sql files via a
script/bat file and bypass having to open all the *sql files in Query
Analyzer.
Can anyone point me to knowledge based articles or How-Tos on this topic?
thxuse copy command to append all files into one, and run osql with -i to input
the file, lastly, schedule osql in a scheduled batch process, or SQL job.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test
>data
> I would like to be able to automatically kick off the Create sql files via
> a script/bat file and bypass having to open all the *sql files in Query
> Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several files, use FOR in your
batch file:
for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TroyS" <troy.stauber@.ilg.com> wrote in message news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test data
> I would like to be able to automatically kick off the Create sql files via a script/bat file and
> bypass having to open all the *sql files in Query Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||thx. i will try the FOR as i have multiple files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237qXfcoAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several
> files, use FOR in your batch file:
> for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>>I have tables, views, functions, procedures created in a database with
>>test data
>> I would like to be able to automatically kick off the Create sql files
>> via a script/bat file and bypass having to open all the *sql files in
>> Query Analyzer.
>> Can anyone point me to knowledge based articles or How-Tos on this topic?
>> thx
>|||thx. i'm having a blond moment and had in my mind multiple files to deal
with rather than just paste everything into 1 file.
"Richard Ding" <richard.ding@.monster.com> wrote in message
news:u8RjaYoAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> use copy command to append all files into one, and run osql with -i to
> input the file, lastly, schedule osql in a scheduled batch process, or SQL
> job.
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>>I have tables, views, functions, procedures created in a database with
>>test data
>> I would like to be able to automatically kick off the Create sql files
>> via a script/bat file and bypass having to open all the *sql files in
>> Query Analyzer.
>> Can anyone point me to knowledge based articles or How-Tos on this topic?
>> thx
>

How to run *.sql ddl automatically

I have tables, views, functions, procedures created in a database with test
data
I would like to be able to automatically kick off the Create sql files via a
script/bat file and bypass having to open all the *sql files in Query
Analyzer.
Can anyone point me to knowledge based articles or How-Tos on this topic?
thxuse copy command to append all files into one, and run osql with -i to input
the file, lastly, schedule osql in a scheduled batch process, or SQL job.
"TroyS" <troy.stauber@.ilg.com> wrote in message
news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>I have tables, views, functions, procedures created in a database with test
>data
> I would like to be able to automatically kick off the Create sql files via
> a script/bat file and bypass having to open all the *sql files in Query
> Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several fi
les, use FOR in your
batch file:
for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"TroyS" <troy.stauber@.ilg.com> wrote in message news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl..
.
>I have tables, views, functions, procedures created in a database with test
data
> I would like to be able to automatically kick off the Create sql files via
a script/bat file and
> bypass having to open all the *sql files in Query Analyzer.
> Can anyone point me to knowledge based articles or How-Tos on this topic?
> thx
>|||thx. i will try the FOR as i have multiple files
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237qXfcoAGHA.1460@.TK2MSFTNGP14.phx.gbl...
> For 2000, use OSQL.EXE. For 2005, use SQLCMD.EXE. To iterate over several
> files, use FOR in your batch file:
> for %%i in (*.sql) do osql -E -d pubs -STIBWORK\RTM -i %%i
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>|||thx. i'm having a blond moment and had in my mind multiple files to deal
with rather than just paste everything into 1 file.
"Richard Ding" <richard.ding@.monster.com> wrote in message
news:u8RjaYoAGHA.2036@.TK2MSFTNGP14.phx.gbl...
> use copy command to append all files into one, and run osql with -i to
> input the file, lastly, schedule osql in a scheduled batch process, or SQL
> job.
>
> "TroyS" <troy.stauber@.ilg.com> wrote in message
> news:uld8QToAGHA.2908@.TK2MSFTNGP10.phx.gbl...
>

How to retrieve Views and Stored Procedure statement text?

Hi,
Is there any way that I can find where the SQL server stored our Create
View or Create Procedure statement (the text), I try to find on all
system tables but can not find it.
Thanks,
RicardCREATE statements for textual objects are stored in syscomments.
Hope this helps.
Dan Guzman
SQL Server MVP
"ricard" <ricard_notrealmail@.example.com> wrote in message
news:uodgYY%23OFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all system
> tables but can not find it.
> Thanks,
> Ricard|||Hi Ricard
The stored procedure to see the definition of stored procedures and views is
sp_helptext.
If you want to see how sp_helptext gets the definition, you can look at IT'S
definition. :-)
USE master
GO
EXEC sp_helptext sp_helptext
GO
You will see that after some error checking, sp_helptext basically just does
a SELECT from the syscomments table.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"ricard" <ricard_notrealmail@.example.com> wrote in message
news:uodgYY%23OFHA.3156@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all system
> tables but can not find it.
> Thanks,
> Ricard|||ricard wrote:
> Hi,
> Is there any way that I can find where the SQL server stored our Create
> View or Create Procedure statement (the text), I try to find on all
> system tables but can not find it.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Views:
SELECT TABLE_NAME, VIEW_DEFINITION
FROM INFORMATION_SCHEMA.VIEWS
WHERE TABLE_NAME = 'view name'
Procedures and Functions:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'procedure or function name'
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQlYtxYechKqOuFEgEQId7QCcDENj6rwofI0D
eqt8hsZclqc0+McAn2Sr
g+1dfblirMiFk+jMbbf8TWZZ
=HvW0
--END PGP SIGNATURE--

How to retrieve tables' name?

Hi,
I want to retrive all tables and views name from a database.
How can I do this?
GoodMorningSky wrote:
> Hi,
> I want to retrive all tables and views name from a database.
> How can I do this?
Select * from INFORMATION_SCHEMA.TABLES
Select * from INFORMATION_SCHEMA.VIEWS
David G.
|||INFORMATION_SCHEMA.TABLES returns both tables and views so only this view
needs to be accessed to return a list of both.
--tables and views
SELECT * FROM INFORMATION_SCHEMA.TABLES
--tables only
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--views only
SELECT * FROM INFORMATION_SCHEMA.VIEWS
or
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
Hope this helps.
Dan Guzman
SQL Server MVP
"David G." <david_nospam@.nospam.com> wrote in message
news:OEJXuJ5mEHA.596@.TK2MSFTNGP11.phx.gbl...
> GoodMorningSky wrote:
> Select * from INFORMATION_SCHEMA.TABLES
> Select * from INFORMATION_SCHEMA.VIEWS
> --
> David G.
|||Select * from INFORMATION_SCHEMA.TABLES
Select * from INFORMATION_SCHEMA.VIEWS
"GoodMorningSky" wrote:

> Hi,
> I want to retrive all tables and views name from a database.
> How can I do this?
>
>
|||Dan Guzman wrote:[vbcol=seagreen]
> INFORMATION_SCHEMA.TABLES returns both tables and views so only this
> view needs to be accessed to return a list of both.
> --tables and views
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> --tables only
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> --views only
> SELECT * FROM INFORMATION_SCHEMA.VIEWS
> or
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OEJXuJ5mEHA.596@.TK2MSFTNGP11.phx.gbl...
Never even looked at the output... silly me. I'm so used to hitting the
system tables directly... Thanks for the clarification.
David Gugick
Imceda Software
www.imceda.com

How to retrieve tables' name?

Hi,
I want to retrive all tables and views name from a database.
How can I do this?GoodMorningSky wrote:
> Hi,
> I want to retrive all tables and views name from a database.
> How can I do this?
Select * from INFORMATION_SCHEMA.TABLES
Select * from INFORMATION_SCHEMA.VIEWS
--
David G.|||INFORMATION_SCHEMA.TABLES returns both tables and views so only this view
needs to be accessed to return a list of both.
--tables and views
SELECT * FROM INFORMATION_SCHEMA.TABLES
--tables only
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
--views only
SELECT * FROM INFORMATION_SCHEMA.VIEWS
or
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David G." <david_nospam@.nospam.com> wrote in message
news:OEJXuJ5mEHA.596@.TK2MSFTNGP11.phx.gbl...
> GoodMorningSky wrote:
>> Hi,
>> I want to retrive all tables and views name from a database.
>> How can I do this?
> Select * from INFORMATION_SCHEMA.TABLES
> Select * from INFORMATION_SCHEMA.VIEWS
> --
> David G.|||Dan Guzman wrote:
> INFORMATION_SCHEMA.TABLES returns both tables and views so only this
> view needs to be accessed to return a list of both.
> --tables and views
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> --tables only
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> --views only
> SELECT * FROM INFORMATION_SCHEMA.VIEWS
> or
> SELECT * FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'VIEW'
>
> "David G." <david_nospam@.nospam.com> wrote in message
> news:OEJXuJ5mEHA.596@.TK2MSFTNGP11.phx.gbl...
>> GoodMorningSky wrote:
>> Hi,
>> I want to retrive all tables and views name from a database.
>> How can I do this?
>> Select * from INFORMATION_SCHEMA.TABLES
>> Select * from INFORMATION_SCHEMA.VIEWS
>> --
>> David G.
Never even looked at the output... silly me. I'm so used to hitting the
system tables directly... Thanks for the clarification.
--
David Gugick
Imceda Software
www.imceda.com|||Select * from INFORMATION_SCHEMA.TABLES
Select * from INFORMATION_SCHEMA.VIEWS
"GoodMorningSky" wrote:
> Hi,
> I want to retrive all tables and views name from a database.
> How can I do this?
>
>