custom procedures in master

Quick question - if I create my own system stored
procedures in the master database, and I have my own
naming convention, presumably they shouldn't be affected
by service packs?
TIA,
Joe
> "Information is the oxygen of the modern age. It seeps
> through the walls topped by barbed wire, it wafts across
> the electrified borders.""
> Ronald Reagan
Ahhhh he was such a philosopher.... ;)

custom procedures in master

Quick question - if I create my own system stored
procedures in the master database, and I have my own
naming convention, presumably they shouldn't be affected
by service packs?
TIA,
JoeNo they won't be unless they are calling another SP or
exteneded procedure in the master database that is already
a Microsoft System DB.
Peter
"Information is the oxygen of the modern age. It seeps
through the walls topped by barbed wire, it wafts across
the electrified borders.""
Ronald Reagan
>--Original Message--
>Quick question - if I create my own system stored
>procedures in the master database, and I have my own
>naming convention, presumably they shouldn't be affected
>by service packs?
>TIA,
>Joe
>.
>|||> "Information is the oxygen of the modern age. It seeps
> through the walls topped by barbed wire, it wafts across
> the electrified borders.""
> Ronald Reagan
Ahhhh he was such a philosopher.... ;)

Cursors within stored procedures

Hi,
I've got a stored procedure that makes use of several cursors to
collate data and then place it into a temp table, which is then
eventually returned to the calling code. The problem is that when I
execute the stored proc, opening the cursors seems to be returning a
recordset, when all I need is the temp table. Code is below, any help
much appreciated!
CREATE PROCEDURE CRM_AccountManagerAnticipatedDeliverable
s2
AS
DECLARE @.Measure varchar(50)
DECLARE @.NumberOfCEFSentToLSCNew int
DECLARE @.NumberOfCEFSentToLSCRepeat int
DECLARE @.NumberOfFASentToLSC int
DECLARE @.NumberOfLAEnteredAccredited int
DECLARE @.NumberOfLAEnteredBespoke int
DECLARE @.AccountManager varchar(50)
DECLARE @.CurrentCount int
/* Create a temporary table */
CREATE TABLE #CalculatedValues
(
Measure varchar(50),
AccountManager varchar(50),
NumberOfCEFSentToLSCNew int,
NumberOfCEFSentToLSCRepeat int,
NumberOfFASentToLSC int,
NumberOfLAEnteredAccredited int,
NumberOfLAEnteredBespoke int
)
/* get measures into a cursor */
DECLARE MeasureCursor CURSOR FOR
SELECT new_name
FROM CRMDEMO_MSCRM.dbo.FilteredNew_Measure
OPEN MeasureCursor
/* start cursor loop */
FETCH NEXT FROM MeasureCursor INTO @.Measure
WHILE @.@.FETCH_STATUS = 0
BEGIN
/* NumberOfCEFSentToLSCNew */
INSERT INTO #CalculatedValues
SELECT @.Measure,
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME),
0, 0, 0, 0
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
AND
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
= 0
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
/* NumberOfCEFSentToLSCRepeat */
DECLARE TempCursor1 CURSOR FOR
SELECT CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as
a1a,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
AND
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
= 1
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor1
FETCH NEXT FROM TempCursor1 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, @.CurrentCount, 0, 0, 0)
END
FETCH NEXT FROM TempCursor1
END
CLOSE TempCursor1
DEALLOCATE TempCursor1
/*NumberOfFASentToLSC*/
DECLARE TempCursor2 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a2a,
COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
FROM
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FASENTTOTHELSC =
0
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor2
FETCH NEXT FROM TempCursor2 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, @.CurrentCount, 0, 0)
END
FETCH NEXT FROM TempCursor2
END
CLOSE TempCursor2
DEALLOCATE TempCursor2
/* NumberOfLAEnteredAccredited */
DECLARE TempCursor3 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a3a,
COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
AS VARCHAR(255)))
FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
'Awaiting Training'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 1
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor3
FETCH NEXT FROM TempCursor3 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, 0, @.CurrentCount, 0)
END
FETCH NEXT FROM TempCursor3
END
CLOSE TempCursor3
DEALLOCATE TempCursor3
/* NumberOfLAEnteredBespoke */
DECLARE TempCursor4 CURSOR FOR
SELECT
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a4a,
COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
as varchar(255)))
FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
WHERE
CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
'Account Manager'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
@.Measure
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
'Awaiting Training'
AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 0
GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
OPEN TempCursor4
FETCH NEXT FROM TempCursor4 INTO @.AccountManager, @.CurrentCount
WHILE @.@.FETCH_STATUS = 0
BEGIN
IF (SELECT COUNT(AccountManager)
FROM #CalculatedValues
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure) > 0
BEGIN
UPDATE #CalculatedValues
SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
WHERE AccountManager = @.AccountManager
AND Measure = @.Measure
END
ELSE
BEGIN
INSERT INTO #CalculatedValues
(Measure,
AccountManager,
NumberOfCEFSentToLSCNew,
NumberOfCEFSentToLSCRepeat,
NumberOfFASentToLSC,
NumberOfLAEnteredAccredited,
NumberOfLAEnteredBespoke)
VALUES(@.Measure, @.AccountManager, 0, 0, 0, 0, @.CurrentCount)
END
FETCH NEXT FROM TempCursor4
END
CLOSE TempCursor4
DEALLOCATE TempCursor4
FETCH NEXT FROM MeasureCursor INTO @.Measure
END
/* end cursor loop */
CLOSE MeasureCursor
DEALLOCATE MeasureCursor
/* return values back */
SELECT * FROM #CalculatedValues
GODo you mean when the recordset are returned to the client. Would SET NOCOUNT
ON solve your problem?
--
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
<peteandrews@.hotmail.com> wrote in message
news:1138029917.821223.30410@.g49g2000cwa.googlegroups.com...
> Hi,
> I've got a stored procedure that makes use of several cursors to
> collate data and then place it into a temp table, which is then
> eventually returned to the calling code. The problem is that when I
> execute the stored proc, opening the cursors seems to be returning a
> recordset, when all I need is the temp table. Code is below, any help
> much appreciated!
> CREATE PROCEDURE CRM_AccountManagerAnticipatedDeliverable
s2
> AS
> DECLARE @.Measure varchar(50)
> DECLARE @.NumberOfCEFSentToLSCNew int
> DECLARE @.NumberOfCEFSentToLSCRepeat int
> DECLARE @.NumberOfFASentToLSC int
> DECLARE @.NumberOfLAEnteredAccredited int
> DECLARE @.NumberOfLAEnteredBespoke int
> DECLARE @.AccountManager varchar(50)
> DECLARE @.CurrentCount int
> /* Create a temporary table */
> CREATE TABLE #CalculatedValues
> (
> Measure varchar(50),
> AccountManager varchar(50),
> NumberOfCEFSentToLSCNew int,
> NumberOfCEFSentToLSCRepeat int,
> NumberOfFASentToLSC int,
> NumberOfLAEnteredAccredited int,
> NumberOfLAEnteredBespoke int
> )
> /* get measures into a cursor */
> DECLARE MeasureCursor CURSOR FOR
> SELECT new_name
> FROM CRMDEMO_MSCRM.dbo.FilteredNew_Measure
> OPEN MeasureCursor
> /* start cursor loop */
> FETCH NEXT FROM MeasureCursor INTO @.Measure
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> /* NumberOfCEFSentToLSCNew */
> INSERT INTO #CalculatedValues
> SELECT @.Measure,
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME),
> 0, 0, 0, 0
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
> AND
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
> = 0
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
>
> /* NumberOfCEFSentToLSCRepeat */
> DECLARE TempCursor1 CURSOR FOR
> SELECT CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as
> a1a,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_CEFSENTTOLSC = 0
> AND
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.NEW_HAVEYOUHADACCESSTOCOF1FUNDINGBEF
> = 1
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor1
> FETCH NEXT FROM TempCursor1 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, @.CurrentCount, 0, 0, 0)
> END
> FETCH NEXT FROM TempCursor1
> END
> CLOSE TempCursor1
> DEALLOCATE TempCursor1
> /*NumberOfFASentToLSC*/
> DECLARE TempCursor2 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a2a,
> COUNT(CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_NAME)
> FROM
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_COMPANYACCOUNTID =
> CRMDEMO_MSCRM.dbo.FILTEREDACCOUNT.ACCOUNTID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FASENTTOTHELSC =
> 0
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor2
> FETCH NEXT FROM TempCursor2 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, @.CurrentCount, 0, 0)
> END
> FETCH NEXT FROM TempCursor2
> END
> CLOSE TempCursor2
> DEALLOCATE TempCursor2
> /* NumberOfLAEnteredAccredited */
> DECLARE TempCursor3 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a3a,
> COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
> AS VARCHAR(255)))
> FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
> 'Awaiting Training'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 1
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
> OPEN TempCursor3
> FETCH NEXT FROM TempCursor3 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, 0, @.CurrentCount, 0)
> END
> FETCH NEXT FROM TempCursor3
> END
> CLOSE TempCursor3
> DEALLOCATE TempCursor3
> /* NumberOfLAEnteredBespoke */
> DECLARE TempCursor4 CURSOR FOR
> SELECT
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY as a4a,
> COUNT(CAST(CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_LEARNINGAIMID
> as varchar(255)))
> FROM CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_FINANCIALANNEXID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.NEW_PARENTFAID
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNINGAIM.new_nationialinsuranceno =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.new_name
> INNER JOIN CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE ON
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_QNUMBERID =
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_COURSEID
> WHERE
> CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERRALTYPENAME =
> 'Account Manager'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_MEASUREIDNAME =
> @.Measure
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_LEARNER.NEW_LEARNERSTATUSNAME =
> 'Awaiting Training'
> AND CRMDEMO_MSCRM.dbo.FILTEREDNEW_COURSE.NEW_ISTHECOURSEACREDITED = 0
> GROUP BY CRMDEMO_MSCRM.dbo.FILTEREDNEW_FINANCIALANNEX.NEW_REFERREDBY
>
> OPEN TempCursor4
> FETCH NEXT FROM TempCursor4 INTO @.AccountManager, @.CurrentCount
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> IF (SELECT COUNT(AccountManager)
> FROM #CalculatedValues
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure) > 0
> BEGIN
> UPDATE #CalculatedValues
> SET NumberOfCEFSentToLSCRepeat = @.CurrentCount
> WHERE AccountManager = @.AccountManager
> AND Measure = @.Measure
> END
> ELSE
> BEGIN
> INSERT INTO #CalculatedValues
> (Measure,
> AccountManager,
> NumberOfCEFSentToLSCNew,
> NumberOfCEFSentToLSCRepeat,
> NumberOfFASentToLSC,
> NumberOfLAEnteredAccredited,
> NumberOfLAEnteredBespoke)
> VALUES(@.Measure, @.AccountManager, 0, 0, 0, 0, @.CurrentCount)
> END
> FETCH NEXT FROM TempCursor4
> END
> CLOSE TempCursor4
> DEALLOCATE TempCursor4
> FETCH NEXT FROM MeasureCursor INTO @.Measure
> END
> /* end cursor loop */
> CLOSE MeasureCursor
> DEALLOCATE MeasureCursor
> /* return values back */
> SELECT * FROM #CalculatedValues
> GO
>|||I found at least on other problem (apart from the possible NOCOUNT problem):
FETCH NEXT FROM TempCursor1
There might be more than one FETCH without specifying INTO @.variable(s). Suc
h will return a
resultset instead of adding the value for each column into the variables. I
didn't want to post this
as I don't have the time to go through the logic and propose a set based sol
ution (which is always
preferred to look at). :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack Vamvas" <DELETE_BEFORE_REPLY_jack@.ciquery.com> wrote in message
news:dr31d7$p6h$1@.nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com...
> Do you mean when the recordset are returned to the client. Would SET NOCOU
NT
> ON solve your problem?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> <peteandrews@.hotmail.com> wrote in message
> news:1138029917.821223.30410@.g49g2000cwa.googlegroups.com...
>|||Thanks for the replies, SET NOCOUNT didn't make any difference but
Tibor's suggestion sorted it - I'd left out the INTO clause from the
FETCH statements, I fixed that and it works great, so thanks very much!
Tibor - out of interest (and a desire to get away from using cursors),
I'd be really grateful if you could point me in the direction of where
I can read up on set based solutions?|||A set based solution is more a way of attacking the problem. You can for ins
tance Google these
newsgroup archives and you should find some posts where a single (or a few)
UPDATE, SELECT, etc have
been suggested instead of a proposed or current cursor solution. The idea is
to manage to do the
whole operation in one UPDATE statement (for example) instead of looping and
looking at each row. An
extremely simplified example would be to increase product price by 10% for e
ach product from country
"Spain":
Procedural thinking:
Create a cursor to loop all rows in product table.
For each row, check if it is from Spain.
If it is, execute an UPDATE statement against that product's PK value.
Set based solution:
UPDATE products SET price = price * 1.1 WHERE country = 'Spain'
I know that above is stupidly simple, but the reasoning goes even if you add
a couple of complexity
levels.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<peteandrews@.hotmail.com> wrote in message
news:1138052525.777723.271910@.g14g2000cwa.googlegroups.com...
> Thanks for the replies, SET NOCOUNT didn't make any difference but
> Tibor's suggestion sorted it - I'd left out the INTO clause from the
> FETCH statements, I fixed that and it works great, so thanks very much!
> Tibor - out of interest (and a desire to get away from using cursors),
> I'd be really grateful if you could point me in the direction of where
> I can read up on set based solutions?
>

cursors CPU usage

h
i started trace and found some procedures running in background
1) sp_cursorope
2) sp_cursorfetch and sp_cursorclose.
wot are these system procedures and it runs after every 2-3 mins and taking too much cpu.
any help would be appreciated
SanjayGenerally, those are SP's used by the MDAC client when a cursor is opened to
retrieve data by an application. Have a look at what application is being
used at the time. Maybe that application needs tuning.
Those 3 are Extended SP's that are shipped with SQL as standard and can not
be changed.
Cheers
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:59A038C1-4F19-4AD0-8FD2-440ECEDF008E@.microsoft.com...
> hi
> i started trace and found some procedures running in background.
> 1) sp_cursoropen
> 2) sp_cursorfetch and sp_cursorclose..
> wot are these system procedures and it runs after every 2-3 mins and
taking too much cpu..
> any help would be appreciated.
> Sanjay|||hi mike,
i checked the application..but this sp's run continously after every 2-3 mins and there are no background processes running on..
do they utilise cpu..as site is working too slow..
can we avoid them'
regards
sanjay|||These stored procedures are being called on the behalf of
the application. For instance, if your application
executes a query "select field1, field2,... from table1",
the ADO drivers are converting this to the three cursor
stored procedure calls. Essentially what is happening is
that the first one, sp_cursoropen executes the query
storing the results in a server side cursor (sort of a
temp table), and the sequence of sp_cursorfetch's return
the rows to the application on the client side.
sp_cursorclose closes the server side cursor.
This is usually the result of an application written to
use the defaults when populating an ADO recordset. It
becomes very inefficient if you have a large number of
rows to return, since the default is to return one row to
the client for each execution of the sp_cursorfetch
stored proc. If the third parameter for sp_cursorfetch
is 1 (one), then you are seeing this default behavior.
To fix this you would have to recode the application to
use non-default ADO property settings for properties like
cachesize or cursorlocation.
I hope that this helps.
Matthew Bando
BandoM@.CSCTGI(RemoveThis).com
>--Original Message--
>hi mike,
> i checked the application..but this sp's run
continously after every 2-3 mins and there are no
background processes running on..
>do they utilise cpu..as site is working too slow..
>can we avoid them'
>regards
>sanjay
>.
>

Cursors and Remote Stored Procedures

given it would appear i can use a cusor as an OUTPUT parameter to a remote
stored procedure, what is the best way to retrieve/return a batch of data
from a stored procedure.On Tue, 8 Nov 2005 07:26:06 -0800, "Martin Samm" <Martin
Samm@.discussions.microsoft.com> wrote:

>given it would appear i can use a cusor as an OUTPUT parameter to a remote
>stored procedure, what is the best way to retrieve/return a batch of data
>from a stored procedure.
Hi Martin,
http://www.sommarskog.se/share_data.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)