How to script Role Memebers

Is there any way to script Databse Role(s) and Role Memeber(s) using SQL-SMO?

The workaround i am using here is, set the server connection execution mode to CaptureSQL and and use AddMemeber method while looping through Server.Database.Roles.EnumMemebers. This creates a string collection of sp_addrolemembers. (let me know if someone wants to have a look at this code)

I couldn't even figureout how to do this using SQL Server Management Studion. When i script the entire database using SQL Server Mangement Studio, i dont see sp_addrolemember statements.

Am I missing something?

Try something like this:

Dim scrDBScript As Scripter
Dim objSMOObjects(100) As SqlSmoObject
Dim intObjCount As Integer
intObjCount = 0

Dim srv As Server
Dim srvConn As ServerConnection
srv = New Server("MyServer")
srvConn = srv.ConnectionContext
srvConn.LoginSecure = True

Dim db As Database
Dim objRoles As DatabaseRoleCollection
Dim objRole As DatabaseRole

db = srv.Databases("AdventureWorks")
objRoles = db.Roles
For Each objRole In objRoles
objSMOObjects(intObjCount) = objRole
intObjCount += 1
Next

scrDBScript = New Scripter(srv)
scrDBScript.Options.FileName = "c:\DBScript.sql"
scrDBScript.Options.IncludeHeaders = True
scrDBScript.Options.AppendToFile = True

ReDim Preserve objSMOObjects(intObjCount - 1)
scrDBScript.Script(objSMOObjects)

This code will create a script of each role defined in the AdventureWorks database. Hope that helps.

|||

Allen, Thanks for replying on this! However, this code only genrates sp_addrole statements. What I am looking for is to script rolememebers (sp_addrolememeber).

Any thoughts?

|||

Not sure if this works, but if it doesn't I think you'll get the idea:

db = srv.Databases("AdventureWorks")
objRoles = db.Roles
For Each objRole In objRoles
Dim colMembers As System.Collections.Specialized.StringCollection
Dim strMember As String
colMembers = objRole.EnumMembers
For Each strMember In colMembers
objSMOObjects(intObjCount) = objRole
intObjCount += 1
Next
Next

The EnumMembers method of the DatabaseRole object returns a string collection with the members of the role in the collection.

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 query execution plan?

Hello,
Does anyone know how to save a query plan into word doc?
Thanks,
Lianne> Does anyone know how to save a query plan into word doc?
There are a variety of ways.
You can click that pane of QA, print it...to file and save as text (which
works for the textual query plan, or you could Alt+PrntScrn it like I do,
and then crop off the query plan part of the screen shot, save it as a jpg
and then insert that to your word doc.
I bet other people will suggest other things. I like the JPG -> Word
option, because it lets you keep an original of your Q-Plan.
Unfortunately, my way does not capture the information that QA puts in the
tooltips when you wave your mouse over the steps.
--
Peace & happy computing,
Mike Labosh, MCSD MCT
Owner, vbSensei.Com
"Escriba coda ergo sum." -- vbSensei
"Lianne Kwock" <LianneKwock@.discussions.microsoft.com> wrote in message
news:658E97FA-289D-40A7-B998-7F944CD7D472@.microsoft.com...
> Hello,
>
> Thanks,
> Lianne|||Hi Mike,
Thank very much for your suggestion. I appreicate it.
"Mike Labosh" wrote:

> There are a variety of ways.
> You can click that pane of QA, print it...to file and save as text (which
> works for the textual query plan, or you could Alt+PrntScrn it like I do,
> and then crop off the query plan part of the screen shot, save it as a jpg
> and then insert that to your word doc.
> I bet other people will suggest other things. I like the JPG -> Word
> option, because it lets you keep an original of your Q-Plan.
> Unfortunately, my way does not capture the information that QA puts in the
> tooltips when you wave your mouse over the steps.
> --
>
> Peace & happy computing,
> Mike Labosh, MCSD MCT
> Owner, vbSensei.Com
> "Escriba coda ergo sum." -- vbSensei
>
> "Lianne Kwock" <LianneKwock@.discussions.microsoft.com> wrote in message
> news:658E97FA-289D-40A7-B998-7F944CD7D472@.microsoft.com...
>
>|||Lianne Kwock wrote:
> Hi Mike,
> Thank very much for your suggestion. I appreicate it.
> "Mike Labosh" wrote:
>
If you are using SQL Server SQL Server 2005 you can use
set showplan_xml on
which will show exeution plan in XML
which you can use to force query execution plan using USE PLAN query
hint.
You can also create plan guide for query which will guide query for
execution when no plan in cache.
Look at sp_create_plan_guide in BOL.
Regards
Amish Shah

How to Retrieve only nth row as a result by writing one query.

How to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:

SNo SudentName Marks
001 Ashok kumar 67
002 Anderson 70
003 Alfred 60
004 Ameeruddin 65
005 KalyanKumar. 69

Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.

How to write this query in SQL Server.

Query part 1: Select the top three records and ordered by marks (ASC)

Query part 2: select all records where the marks value is the MAX value of the first query part

Do you need to take into consideration, any tied value (e.g. two at 71) - how does this affect those with a score of 70?

|||

SELECT TOP 1 *
FROM (SELECT TOP 3 * FROM MyTable ORDER BY Marks DESC) t1
ORDER BY Marks ASC

|||A nice answer, if I may say|||It's a start, but he still has to deal with what to do with duplicates as you previously mentioned.