Adhoc vs PROC

Hi Guru,

When I ran my adhoc script below it generated only 45000 reads or 4 seconds but when I wrapped it into procedure it took about two minutes or millions of reads. The parameters calling both adhoc and proc are indeed the same. I'm pretty 99.9% sure that the proc does not recompile because I don't mix up between DDL and DML, no temp tables or any thing to cause proc to recompile. The big difference is adhoc used index scan for 45% but proc used bookmark lookup for 75%. Why it's so difference since they both returned the same results?

Please help...

Below is my code,

DECLARE @.Mode varchar(10),
@.UserID varchar(36),
@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.Inst tinyint,
@.LocationID smallint,
@.BunitID tinyint,
@.TeamID int

SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805

--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END

IF @.Mode = 'RM'
BEGIN
IF @.BUnitId > 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, '' As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1 ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND (DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId = 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID = dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID) AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
END

When wrapped in SP have you recompiled for the first time to execute the same, check the estimated execution plan from QA in this case that explains why there is delay.

Adhoc vs PROC

Hi Guru,

When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?

Please help...

Silaphet,

Below is my code,

DECLARE @.Modevarchar(10),
@.UserIDvarchar(36),
@.FromDatesmalldatetime,
@.ToDatesmalldatetime,
@.Insttinyint,
@.LocationIDsmallint,
@.BunitIDtinyint,
@.TeamIDint

SET @.Mode='TEAM'
SET @.UserID=''
SET @.FromDate='Dec 1 2006 12:00AM'
SET @.ToDate='Dec 31 2006 12:00AM'
SET @.Inst=28
SET @.LocationID=0
SET @.BunitID=2
SET @.TeamID=805

--IF @.Mode = 'TEAM'
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active,
TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped RIGHT OUTER JOIN
dbo.MyTeamsRpt ON
vw_Referrals_Grouped.OfficerID = dbo.MyTeamsRpt.OfficerId
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (MyTeamID = @.TeamID) AND ((StartDate
BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate BETWEEN @.FromDate
AND @.ToDate))
GROUP BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,
Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY TeamName, OffAffiliateDesc, OffLocationDesc, OfficerName,
Active
END

IF @.Mode = 'RM'
BEGIN
IF @.BUnitId 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''
As TeamName, OffBUnitDesc As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID)
AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate
BETWEEN @.FromDate AND @.ToDate)) AND OffBUnitID = @.BUnitID
GROUP BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,
OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY OffBUnitDesc, OffAffiliateDesc, OffLocationDesc,
OfficerName, Active
END
--ELSE
IF @.BUnitId = 0
BEGIN
SELECT OffAffiliateDesc, OffLocationDesc, OfficerName, Active, ''
As TeamName, '' As BUnit,
Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1 ELSE 0
END) As CurrYr,
Sum(CASE WHEN StartDate BETWEEN @.FromDate-365 AND @.ToDate-365 THEN 1
ELSE 0 END) As PrevYr,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) AND ((OutcomeId IS NULL) OR (OutcomeID =0)) AND
(DATEDIFF(dd,StartDate,@.ToDate) * -1 <-30) THEN 1 ELSE 0 END) As
PastDue,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 1037) THEN 1 ELSE 0 END) As Ref,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 63) THEN 1 ELSE 0 END) As CallSched,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 64) THEN 1 ELSE 0 END) As PropPres,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 65) THEN 1 ELSE 0 END) As PropAcc,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 66) THEN 1 ELSE 0 END) As BremApp,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND (StageId
= 67) THEN 1 ELSE 0 END) As BusBook,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 106) THEN 1 ELSE 0 END) As NonQual,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeId = 992) THEN 0 ELSE 0 END) As Duplicate,
Sum(CASE WHEN (StartDate BETWEEN @.FromDate AND @.ToDate) AND
(OutcomeID = 107) THEN 1 ELSE 0 END) As Outdated
FROM vw_Referrals_Grouped
LEFT OUTER JOIN dbo.vw_Officers ON vw_Referrals_Grouped.OfficerID =
dbo.vw_Officers.OfficerID
WHERE (ReferralID>0) AND (vw_Referrals_Grouped.OfficerID = @.UserID)
AND ((StartDate BETWEEN @.FromDate-365 AND @.ToDate-365) OR (StartDate
BETWEEN @.FromDate AND @.ToDate))
GROUP BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
HAVING Sum(CASE WHEN StartDate BETWEEN @.FromDate AND @.ToDate THEN 1
ELSE 0 END)>0 Or Active = 1
ORDER BY OffAffiliateDesc, OffLocationDesc, OfficerName, Active
END
END
ENDOn 4 Jan 2007 07:12:58 -0800, kmounkhaty@.yahoo.com wrote:

Quote:

Originally Posted by

>Hi Guru,
>
>When I ran my adhoc script below it generated only 45000 reads or 4
>seconds but when I wrapped it into procedure it took about two minutes
>or millions of reads. The parameters calling both adhoc and proc are
>indeed the same. I'm pretty 99.9% sure that the proc does not recompile
>because I don't mix up between DDL and DML, no temp tables or any thing
>to cause proc to recompile. The big difference is adhoc used index scan
>for 45% but proc used bookmark lookup for 75%. Why it's so difference
>since they both returned the same results?
>
>Please help...

Hi Silaphet,

You might suffer from parameter sniffing. Google for this term to find
out what it is and how you can try to deal with it.

You should also consider creating three procedures for the three
versions of the SELECT statement, and change your current stored proc
into a simple IF ELSE tree to call either one of them. This way, each
stored proc can get an execution plan that is otimized for its
parameters.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:

Quote:

Originally Posted by

When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?

Run this:

select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')

If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.

It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||First off, I think I see a bug:

"(StartDate BETWEEN @.FromDate-365 AND @.ToDate-365)"

What happens on leap years?

It's impossible to tell what the performance of this query is going to
be like without knowing how all of the tables and views are structured.
I'd imagine that your biggest issue is going to be with the range of
dates that you're dealing with, but there's no way to know for sure
without knowing the table structure, view definitions, and statistical
distribution of data in the tables.

My advice: If it's not a proc that's being used a lot during the day,
try defining it with the RECOMPILE option (see docs) so that the
procedure gets re-optimized each time you run it.

-Dave

Erland Sommarskog wrote:

Quote:

Originally Posted by

kmounkhaty@.yahoo.com (smounkhaty@.bremer.com) writes:

Quote:

Originally Posted by

When I ran my adhoc script below it generated only 45000 reads or 4
seconds but when I wrapped it into procedure it took about two minutes
or millions of reads. The parameters calling both adhoc and proc are
indeed the same. I'm pretty 99.9% sure that the proc does not recompile
because I don't mix up between DDL and DML, no temp tables or any thing
to cause proc to recompile. The big difference is adhoc used index scan
for 45% but proc used bookmark lookup for 75%. Why it's so difference
since they both returned the same results?

>
Run this:
>
select objectproperty(object_id('yoursp'), 'ExecIsAnsiNullsOn'),
objectproperty(object_id('yoursp'), 'ExecIsQuotedIdentOn')
>
If any of these return 0, recreate the procedure and make sure that
the settings ANSI_NULLS and QUOTED_IDENTIFIER are ON. This matters if
there is an indexed view or an index on a computed column. They can
only be used if these two settings are active, and these two are saved
with the stored procedure.
>
It could also depend how you pass the parameters, as Hugo discussed,
but we don't that. If the script is your actual ad-hoc script, it
may be that it works better if you copy the parameters to local variables.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

AdHoc Query faster than Stored Proc?

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?the stored proc was past its "best before" date

like, if you look in your fridge and the milk says april 2003, you have to throw it out and buy some more

;)|||Well, that was what i exactly did. Now i can drink milk safely, but i'd like to prevent this situation again if it's possibile.

It's always frustrating when things happen and I cannot understand why :(|||It's always frustrating when things happen and I cannot understand why :(boy, i know that feeling

switch from milk to bourbon -- keeps forever :)

trouble is, i always finish it, and have to go out for more...|||See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html|||Thanks for the link. Though very interesting i was already aware of that problem, and in fact i tried to run the body of the stored procedure using parameters to closely simulate the behaviour of the stored procedure.

Since the sp was executed with WITH RECOMPILE i expected the same execution plan of the ad-hoc query (that is what normally happens), but this time SQL Server produced two different execution plans.

UPDATE:
mmm...i've read more deeply the info at the link i've told me and seems that the problem was really parameter sniffing. a BIG thanks! :)

addlogin into linked server

Hi,
I need to develop a stored procedure to add or drop SQL server logins into
SQL servers based on some logics users give to me. My stored procedure is on
one server. I need to add logins to other servers through linked server.
Does anybody know how to run sp_addlogin to add SQL logins into linked serve
r?
Thanks a lot.BF (BF@.discussions.microsoft.com) writes:
> I need to develop a stored procedure to add or drop SQL server logins
> into SQL servers based on some logics users give to me. My stored
> procedure is on one server. I need to add logins to other servers
> through linked server.
> Does anybody know how to run sp_addlogin to add SQL logins into linked
> server?
Did you try
EXEC SOMESERVER.master.dbo.sp_addlogin=
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I just tried
exec wsdev.master.dbo.sp_addlogin 'test', 'test'
After this, I checked and saw the new login has been created. But I got the
following error message:
New login created.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should
be discarded.
I cannot use this in production system with error message like this. Any
further suggestions?
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> Did you try
> EXEC SOMESERVER.master.dbo.sp_addlogin=
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I just tried
> exec wsdev.master.dbo.sp_addlogin 'test', 'test'
> After this, I checked and saw the new login has been created. But I got
> the following error message:
> New login created.
> Msg 0, Level 11, State 0, Line 0
> A severe error occurred on the current command. The results, if any,
> should be discarded.
> I cannot use this in production system with error message like this. Any
> further suggestions?
I was not able to repeat this. Exactly which versions of SQL Server do
you have on the two servers? Use serverproperty('ProductVersion') to
determine this.
Anyway, the error message looks like you are running SQL 2005. In such
case you can use EXEC() AT:
EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
If the target server is also running SQL 2005, you should not use
sp_addlogin at all, but rather CREATE LOGIN:
EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
Note however, that this command will fail, because SQL 2005 validates
the password according to Windows policy. This mainly happens on SQL 2003,
but SQL 2005 always frowns at username = password.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I am using SQL Server 2000. The exact version is 8.00.760.
I cannot use EXEC AT on my server. But it's good to know this new feature on
SQL 2005.
Now I am thinking open 2 database connections from my .NET application to
access 2 different servers.
Thanks.
"Erland Sommarskog" wrote:

> BF (BF@.discussions.microsoft.com) writes:
> I was not able to repeat this. Exactly which versions of SQL Server do
> you have on the two servers? Use serverproperty('ProductVersion') to
> determine this.
> Anyway, the error message looks like you are running SQL 2005. In such
> case you can use EXEC() AT:
> EXEC ('EXEC master.dbo.sp_addlogin ''test'', ''test''') AT wsdev
> If the target server is also running SQL 2005, you should not use
> sp_addlogin at all, but rather CREATE LOGIN:
> EXEC ('CREATE LOGIN test WITH PASSWORD = ''test''') AT wsdev
> Note however, that this command will fail, because SQL 2005 validates
> the password according to Windows policy. This mainly happens on SQL 2003,
> but SQL 2005 always frowns at username = password.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||BF (BF@.discussions.microsoft.com) writes:
> I am using SQL Server 2000. The exact version is 8.00.760.
> I cannot use EXEC AT on my server. But it's good to know this new
> feature on SQL 2005.
> Now I am thinking open 2 database connections from my .NET application to
> access 2 different servers.
I assumed that you were on SQL 2005, because I took for granted that
you were running this from a query tool, which in that case would
be Management Studio which uses SqlClient.
Moral: please always be specific with the environment you are using.
As for the solution, this what I have recommended in the first place,
if I had known that you were doing this from an application. Relying
on linked servers is fragile, since the definition of a linked server
could change or disappear completely.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

adding VbLf

When using the Xp_sendmail stored procedure from MS SQL 2000, I cannot use LF or CR of both otr VBnewLine in my messages.

For example

<code>
Trim(lblEmployee.Text) & " has requested a holiday. From " & CalendarPopupStartDate.SelectedDate & " till " & CalendarPopupStartDate.SelectedDate _
& "." & CARRIAGE RETURN WANTED &lblRequestedDays.Text & txtComments.Text
<code
where you find the CARRIAGE RETURN WANTED, I want to begin a new line. My email is not formated asked :(

any help?

Greetz,
Geoffyour looking for a CRLF

chr(13) & chr(10)|||yes I know. That is not the problem. The problem is that the xp_sendmail accepts a parameter @.message. this is build up from severall labels on my page. But I do not succeed in entering en CR or LF|||if your creating the message from a web page then use environment.newline to insert the CRLF|||SQL will parse it if you use CHAR(ascii)|||Yes I knwo that... I'll try to explain

When I build up a query I do this like this

Dim blabla as String = "Exec xp_sendmail @.message, @.and some others"
dim comBlaBla as sqlcommand(blabla , sqlconnection1)

----
Now when I fill up the parameter @. message and I use a char(10) or char(13) this is just printed as written in the string. I tried to use other characters that escaped these like \ and so one. Can someone give me an example of how I can add Lf or CR to a string that is passed as a parameter? Hopefully now you guys understand what I'm talking about.

Greets,
Geoff

Adding to outputs together to retrieve the top(10) - Is it possible?

Hi There,

I have been struggling day and night with the creation of a store procedure due to not being able to retieve the rows I need for SUM and AVG functions.

I have two tables ('actions' & 'incident_types') which both have a score value for each record. In my database, I have reports that contain both action codes and incident_type codes based on a personnel_code.

In simple terms I'm trying to do the following:-

For all reports, find each personnel member and thier attached incident_types and action codes and then, for each score from the actions and incident_types tables, create the SUM of the 'combined' scores.

I have successfully retrieved the output for the scores individually but I need the TOP(10) of the combined output.....

I'm currently using to seperate queries as follows :--

--This gives me the incident type output...... Creating the 'Volume' column which is the total Score for incidetn_types

SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(incident_types.type_score) as 'Volume', avg(incident_types.type_score) as 'Average' INTO 'Incident_Scores' from Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname

-- I then use the following to get the total of all action scores, again in the 'Volume' column

SELECT Top (@.Number) Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname, sum(actions.action_score) as 'Volume', AVG(actions.action_score) as 'Average' from profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE (report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

group by Personnel_details.personnel_code, Personnel_details.personnel_forename, Personnel_details.personnel_Surname

So my question is - How can I get the sum(incident_types.type_score) + sum(actions.action_score) and then get the TOP(10) rows?

Is it possible to output these 2 result to a new table and then join the new tables?

Thanks for any assistance, this is really draining me at the moment..... :-/

here it is,

Select TOP (@.Number)

incident_Data.personnel_code

, incident_Data.personnel_forename

, incident_Data.personnel_Surname

, incident_Data.Volume

, incident_Data.Average

, actions_data.Volume

, actions_data.Average

, incident_Data.Volume + actions_data.Volume as Total_Volume

, incident_Data.Average + actions_data.Average as Total_Average

from

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(incident_types.type_score) as 'Volume'

, avg(incident_types.type_score) as 'Average'

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as incident_Data

Inner Join

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(actions.action_score) as 'Volume'

, AVG(actions.action_score) as 'Average'

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as actions_data

On actions_data.personnel_code = incident_Data.personnel_code

|||

Thank you so much for your reply this has really made my day!

I forgot to mention that the action record is not always present but the incident record is. Therefore I made the actions_data join = 'Left Outer Join' (see code below)... This gives me a problem in that when the action_data record contains 'NULL' the Volume is also 'NULL' - is it possible to make NULL = 0 [zero] when there is no action record?

Thanks again (see code and output).....

[code I've removed some of the selection criteria from previous post for testing purposes]

Select TOP (1000)

incident_Data.personnel_code

, incident_Data.personnel_forename

, incident_Data.personnel_Surname

, incident_Data.Volume

, incident_Data.Average

, actions_data.Volume

, actions_data.Average

, incident_Data.Volume + actions_data.Volume as Total_Volume

, incident_Data.Average + actions_data.Average as Total_Average

from

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(incident_types.type_score) as 'Volume'

, avg(incident_types.type_score) as 'Average'

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

report_basedon.personnel_code <> 0

AND report_header.record_status='1'

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as incident_Data

Left Outer Join

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, sum(actions.action_score) as 'Volume'

, AVG(actions.action_score) as 'Average'

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

personnel_details.personnel_code <> 0

AND report_header.record_status='1'

AND profile.record_status='1'

group by

Personnel_details.personnel_code,

Personnel_details.personnel_forename,

Personnel_details.personnel_Surname

) as actions_data

On actions_data.personnel_code = incident_Data.personnel_code

Order by incident_data.personnel_code

[example of results]

15 Oscar Freeman 0 0 NULL NULL NULL NULL
16 Sofia Daniels 0 0 NULL NULL NULL NULL
19 Megan Hardy 25 12 120 40 145 52
20 Finley Randall -25 -25 NULL NULL NULL NULL|||

observations

1. since actions_data subquery may be missing [i.e. OUTER JOIN] you should use this line instead

, incident_Data.Volume + ISNULL(actions_data.Volume,0) as Total_Volume

, incident_Data.Average + ISNULL(actions_data.Average,0) as Total_Average

2. the Total_Average calculation is flawed, e.g.
4 rows of incident_Data with average of 50 and 1 row of actions_data with average of 20 would yield a Total_Average of 70

when what you probably wanted was 44 (or 35 for simplicity if you don't care about weighting)

Dick

|||

Thank you Dick, for both points, your absolutly correct. I was so concerned with not being ale to extract the data that I hadn't thought this though. You've propably saved me a lot more pain!

Just for completeness, I had to go on and resolve a divide by zero error when calculating the final averages etc etc and I found a very useful link http://www.sql-server-helper.com/error-messages/msg-8134.aspx which shows 3 ways of dealing with this error.

Thanks again for everyones help - I can now get on with developing the program!!!!

|||

Yes. ISNULL will fix the issue... If the average is your issue the following query might be a best one..

Not sure why we need a TOP here.. It wont give any performance gain.

Select TOP (@.Number)

personnel_code

, personnel_forename

, personnel_Surname

, sum(case when type='incident' then Score end) as incident_volume

, avg(case when type='incident' then Score end) as incident_average

, sum(case when type='actions' then Score end) as actions_volume

, avg(case when type='actions' then Score end) as actions_average

, sum(Score) as Total_Volume

, avg(Score) as Total_Average

From

(

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, (incident_types.type_score) as 'Score'

, 'incident' Type

from

Report_header

JOIN incident_types on incident_types.type_code = report_header.report_incident_Code

JOIN report_basedon on report_basedon.report_code = report_header.report_code

JOIN personnel_details on personnel_details.personnel_code = report_basedon.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND report_basedon.personnel_code <> 0

AND report_header.record_status=@.recordStatus

UNION ALL

SELECT

Personnel_details.personnel_code

, Personnel_details.personnel_forename

, Personnel_details.personnel_Surname

, (actions.action_score) as 'Score'

, 'actions' Type

from

profile

JOIN actions on actions.action_code = profile.action_code

JOIN report_header on report_header.report_code = profile.incident_ID

JOIN personnel_details on personnel_details.personnel_code = profile.personnel_code

WHERE

(report_header.report_date >= @.fromDate and report_header.report_date <= @.toDate)

AND (report_header.report_time >= @.fromTime and report_header.report_time <= @.toTime)

AND personnel_details.personnel_code <> 0

AND report_header.record_status=@.recordStatus

AND profile.record_status=@.recordStatus

) as data

Group By

personnel_code

, personnel_forename

, personnel_Surname

Adding the parameters dynamicaly to SP

In our website we have created the reportengine which passes the parameters dynamicaly means whatever parameters are provided in Stored procedure is automaticaly binded to grid from where values are passed to sp as follows
.Actualy this code is written by some other developer
If Not ParameterTable Is Nothing Then
RecordCount = ParameterTable.Rows.Count
If RecordCount >= 1 Then
For Index = 0 to (RecordCount - 1)
ParameterRow = ParameterTable.Rows(Index)
Select Case ParameterRow(1)
Case "231"
RecordType = "SQlDbType.NVarChar"
Case "60"
RecordType = "SQlDbType.Money"
Case "61"
RecordType = "SQlDbType.DateTime"
Case "62"
RecordType = "SQlDbType.Int"
Case "59"
RecordType = "SQlDbType.Float"
Case "104"
RecordType = "SQlDbType.Bit"
Case Else
RecordType = "SQlDbType.Int"

End Select
myCommand.Parameters.Add(ParameterRow(0), RecordType).Value = (ParameterRow(2))

[red]When Sp is executed with link[/red]<A href="http://links.10026.com/?link=Main.aspx?ReportID=1075443551&ReportName=RptAPandARComparative&ReportDescription=RptAPandARComparative"
target="main">

I get the error that nvarchar can not be converted int

Why is is So?
Is anybody having same type of experiance
In above link I got reportid as queries
select id from sysobjects where name='RptAPandARComparative'

Swati Jain:

I get the error that nvarchar can not be converted int

Is your stored procedure parameter declared as an int or varchar?

|||

I tried all datatypes still I got the same error with sp as well as code

|||

Swati Jain:

I tried all datatypes still I got the same error with sp as well as code

Can you show us your input parameters for the stored procedures?

You would also have to specify the parameter value size as well to make sure there are no data type mismatch errors.

|||

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

AS

No Code is written for specifying the size of the datatype

|||

Swati Jain:

alterPROCEDURE [enterprise].[RptAPandARComparative]

@.Year1Int,--nvarchar(8), I tried various datatypes

@.Year2Int--nvarchar(8)

your stored procedures are declared with INTEGER input paramters, so you have to set the type for your parameters toSqlDbType.Int for both input parameters.

You should also set the size as well, I believe int datatypes in SQL Server is 8 bytes.

Adding procedure to SQL Server Express

I am adding (trying to add) a procedure (written by a friend of mine and someone who is experienced). I am adding it via the SQL Server Mgt. Studio Express. When I run it, it says that the command completed successfully, but it doesn't appear in the Procedure folder.

When I run my ASP 2.0 form that calls it, it says that it can't find the procedure.

Can anyone help me determine what to look for or what I might be doing wrong?

Thank you.

How exactly are you adding the object to the database inside the Management tools. Has you friend supplied a SQL script file and you are running it inside a new query window. If so you will need to make sure that the query you are running is inside the correct database. Generally you should have a use statement at the top of the database

Query for example

use northwind
go

and then have the Stored procedure script following it.

|||

He forgot the USE statement. Previously it was adding into the system area. My knowlede of procedures is limited, but I should have thought of that myself.

Thank you.

Adding number of decimal places during table design

Hello,

Nice easy one (hopefully) from a newbie on SQL 2000.

I have a table HolidayTakenBooked which is populated from a stored procedure via the following statement;

TRUNCATE TABLE HolidayTakenBooked
INSERT INTO HolidayTakenBooked
SELECT * FROM #TMP_HolidayTakenBooked ORDER BY ABR_Clock_No

I am finding that for certain values in the HolidayTakenBooked table decimals are not being transferred correctly. ie. 0.5 in the TMP table appears as 1 in the HolidayTakenBooked table.

I'm pretty sure that this is down to the data definition of the table see sample field below;
[HOL_DaysTaken1] [decimal](18, 0) NULL ,

So the simple question here is how do I define decimal places when I define a new table. When designing a new table in Enterprise Manager I select decimal and the server does not allow me to change the value of 9 it defaults to.

What simple thing I am not doing ?

Cheers
NealPut values in Scale properties,you will find it just below precision if you are creating tables in EM design Table option...|||Many thanks.
Neal

Adding node back after hardware failure.

After you have to take a node out of a SQL cluster, what is
the procedure to get it back in the cluster?
The second machine is back up and working on the cluster
with everything except the SQL resources, which makes sense
because you have to install SQL on the cluster after it is
set up.
But how should we go about getting this machine back in the
SQL cluster? Should we just reinstall SQL on the cluster,
or should we uninstall first? Is uninstalling simple on a
cluster?
Is there anything obvious that I'm leaving out that will
cause us headaches?
Thanks in advance,
Shawn
Look up 'Maintaining a Failover Cluster' in BOL. It has step-by-step
instructions on how to replace a failed node in a SQL cluster.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Shawn" <anonymous@.discussions.microsoft.com> wrote in message
news:18cfe01c44bdc$c8664390$a501280a@.phx.gbl...
> After you have to take a node out of a SQL cluster, what is
> the procedure to get it back in the cluster?
> The second machine is back up and working on the cluster
> with everything except the SQL resources, which makes sense
> because you have to install SQL on the cluster after it is
> set up.
> But how should we go about getting this machine back in the
> SQL cluster? Should we just reinstall SQL on the cluster,
> or should we uninstall first? Is uninstalling simple on a
> cluster?
> Is there anything obvious that I'm leaving out that will
> cause us headaches?
> Thanks in advance,
> Shawn
|||Awesome. Thanks.
>--Original Message--
>Look up 'Maintaining a Failover Cluster' in BOL. It has
step-by-step
>instructions on how to replace a failed node in a SQL cluster.
>
>--
>Geoff N. Hiten
>Microsoft SQL Server MVP
>Senior Database Administrator
>Careerbuilder.com
>I support the Professional Association for SQL Server
>www.sqlpass.org
>"Shawn" <anonymous@.discussions.microsoft.com> wrote in message
>news:18cfe01c44bdc$c8664390$a501280a@.phx.gbl...
>
>.
>

Adding new columns with check constraints using same batch

I'm using a stored procedure to add fields to an existing table.

These fields must have check constraints and I need to use one T-SQL batch.

In Sql2000 Ok. In Sql2005, if table exists, I get error "invalid column xxxxx" in Add Constraint statement before new column creation.

the code is

Declare @.Setup bit

Set @.Setup = 1

if @.Setup = 1 Begin

--Alter Table MyTable Add MyField Numeric(1, 0) Not Null Default 3

Exec mySp_Add_Column 'MyTable', 'MyField', 'Numeric (1, 0) Not Null', '3'

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0

Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)

End Else Begin

-- drop column

End

GO

If MyTable does not exist and, naturally, I add it before of check constraints (using another Sp which add tables) ok.

If I add FK to new fields, ok.

Now I have to split batch in two parts as workaround...

Can anyone tell me if this is a bug or a "fix" for previous versions?

Many thanks,

Giulio

Interesting, it probably is an undocumented "fix" that exists pretty much because it is illogical to add a check constraint when something doesn't exist, so when they parse it (before they execute it) it fails.

I would personally just add the check constraint creation to the ADD column proc, if I were you. It would just require a parameter, and it would be easy enough. The parameter I would add would be @.checkCriteria. Then pass in 'MyField >= 1 and MyField <= 3)'

Then you can formulate the name (or at worst add another parameter as override) and build the statement in the proc. You can add the constraint when you are adding the column too:

create table test
(
testId int primary key
)
go
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

|||Another workaround might be this:

If IsNull(ObjectProperty(Object_Id('xCK_MyTable_MyField'), 'IsConstraint'), 0) = 0
exec('
Alter Table MyTable Add Constraint xCK_MyTable_MyField Check (MyField >= 1 And MyField <= 3)')

Steve Kass
Drew University
www.stevekass.com

|||

Thanks Louis and Steve.

I've just resolved problem. Since in Sql 2000 this problem did not exist (using same T-Sql code) I'd like to know if it's a bug...

Really, if I use Alter Table, instead of mySp_Add_Column to add new columns, I get error too... So if I can understand parser can't know what SP wants to do, why doesn't parser understand I'll add new column before of check constraints? Why can I add FKs on new fields while I can't add CKs?

It seems a parser bug...

Bye,

Giulio

|||

Really? When I execute:

create table test
(
testId int primary key
)
alter table test
add newColumn varchar(2000) not null
constraint ckTestNewColumn check (newColumn = 1)

in a single batch, it works just fine. What error are you getting?

|||

ok, in this way it works...

Only when I add a column to an existing table and, after, I add a check constraint, all in one batch, I get error...

And only after I installed SqlSrv 2k5... on SqlSrv 2k it works fine...

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULL
The only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
.... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:

> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>
|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure wh
it the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I wil
l need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach t
o
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:

> My question is...
> Is there any way to add a new column dinamically inside a store procedure
whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I w
ill need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
--
http://www.aspfaq.com/
(Reverse address to reply.)

Adding Leading Zeros to a char value - TSQL Question

Hi all:

I have save stored procedure which does an insert and update. Before the insert or update statements, I have the following SQL to adding leading zeroes to in my input params:

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + '123',6))

SELECT @.SchoolCode_Modified AS Col1

The problem is that this works on by itself on SSMS, but when incorporated in the SPROC, it doesnt add the leading zeros. The datatype for both of the colum is char(6) as well. If I run the above query in SQL Server Management Studio, it displays Col1 with 000123 in it, which is what I want, from the sproc, but that doesnt happen. Why? Can someone please help me? The following is how it is implemented in the SPROC:

CREATE PROCEDURE [LAF].[uspSaveLoanApplication]

...more params here

@.SchoolCode char(6),

@.BranchCode char(2),

@.PK int OUTPUT,

@.PreviousLoanApplicationStatus char(3) OUTPUT

AS

SET NOCOUNT OFF

SET @.PreviousLoanApplicationStatus = 0

IF EXISTS(SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

SET @.PreviousLoanApplicationStatus = (SELECT [LoanApplicationStatusCode] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

SELECT @.PreviousLoanApplicationStatus PreviousLoanApplicationStatus

END

-- Add Leading Zeroes to School Code and Branch Code to conform with CLIPS Formatting

DECLARE @.SchoolCode_Modified char(6)

DECLARE @.BranchCode_Modified char(2)

SET @.SchoolCode_Modified = (SELECT Right(Replicate('0',6) + @.SchoolCode,6))

SET @.BranchCode_Modified = (SELECT Right(Replicate('0',2) + @.BranchCode,2))

IF EXISTS(SELECT [LoanApplicationID] FROM [LAF].[LoanApplication] WHERE [LoanApplicationID] = @.LoanApplicationID)

BEGIN

UPDATE [LAF].[LoanApplication] SET

....more here

[SchoolCode] = @.SchoolCode_Modified,

[BranchCode] = @.BranchCode_Modified,

...more here

WHERE

[LoanApplicationID] = @.LoanApplicationID

AND [UpdatedOn] = @.LastUpdated

SELECT @.LoanApplicationID PK

SET @.PK = @.LoanApplicationID

END

ELSE

BEGIN

INSERT INTO [LAF].[LoanApplication] (

.....more here

[SchoolCode],

[BranchCode],

.....more here

) VALUES (

....more here

@.SchoolCode_Modified,

@.BranchCode_Modified,

....more here

)

SET @.PK = SCOPE_IDENTITY()

SELECT @.PK PK

END

With a quick scan, your code appears correct.

How is it that you are finding that the values of @.SchoolCode_Modified and @.BranchCode_Modified do NOT have the leading zeros?

|||

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

|||

DanR1 wrote:

It appears that your problem is that

@.SchoolCode is declared as char(6).

So it is NOT '123', but ' 123', i.e., 3 spaces followed by '123'.

It appears you must declare @.SchoolCode as varchar(6) if you want to get those leading zeroes in there.

Dan

Good catch...

Actually, I don't think that it would be ' 123', but instead 'should' be '123 '. In either case though, adding preceding characters and then taking the rightmost 6 characters would not effect any change in the value.

|||

Arnie,

Yes, you are right about the trailing blanks, instead of leading blanks as I suggested. (I checked on it after I made my post, and figured it wasn't worth the trouble to make the edit.)

So it seems like "ltrim(rtrim(@.SchoolCode))" is what should be preceded by "000000" before taking the 6 rightmost characters.

Dan

|||

DECLARE @.SchoolCode_Modified char(6)

SET @.SchoolCode_Modified = '123'

SET @.SchoolCode_Modified = REPLICATE('0',6 -LEN(@.SchoolCode_Modified))+ @.SchoolCode_Modified

SELECT @.SchoolCode_Modified AS Col1

adding fields in page footer

Hi Every one
I have problem in using page footer field from the storeprocedure. I have a procedure and defined two fields one is page header and page footer. When I drag and drop them repective area it displaying error message you can't use fields for header and footer than how can I use them please help me. I am new to this reporting services.Please, I got this issue too.
Does anyone get good solution?
thanks.|||I don't know if I understand you correctly but....if you are using visual studio.net
you can only use globals! and textboxes with text in them.

I hope this is what you are looking for. I am pretty new at this also.

Hope you find an answer to your problem,
Kerrie|||Can you put them in the table header & table footer?|||

Hi,
For some reason VS.net is very limited on what you can do in the headers and footers. No fields or tools can go in them. VS.net will only accept globals and textboxes with text.

I have not tried any constants-i dont know about that one.

I hoped that helped.

thanx and have a great day...

Kerrie

|||You can use fields in the page header and footer by using a hidden parameter. You select the fields in your stored procedure or SQL just like normal fields and then set up a hidden parameter, referencing the field. In the SSRS 2000 version, leave the Prompt value blank – this makes it hidden. In SSRS 2005, set the Hidden check box on the Report Parameters dialog box.

Next, add a new parameter to your report and set the “default value” by selecting the field from your dataset.
Then, create a text box whose value will be the new parameter - i.e. “=Parameters!Footer.Value”

Adding Field to Stored Procedure for Crystal Reports

Hello:

I have a stored procedure for generating our invoices in Crystal
Reports. I have added a new field to the SP, but when I try to add the
field to my Crystal Report invoice, the field isn't available in the
list. However, if I create a new, blank report using the same stored
procedure as the datasource, the field is available. I've seemingly
tried every iteration of "Verify Database" to no avail.

The obvious answer would be to simply drop the sp from my existing
report and then re-add it. However, if you do this, all your fields on
the report are dropped.

Any ideas?

Thanks,
ScottScott (scott@.oppligerbanking.com) writes:
> I have a stored procedure for generating our invoices in Crystal
> Reports. I have added a new field to the SP, but when I try to add the
> field to my Crystal Report invoice, the field isn't available in the
> list. However, if I create a new, blank report using the same stored
> procedure as the datasource, the field is available. I've seemingly
> tried every iteration of "Verify Database" to no avail.
> The obvious answer would be to simply drop the sp from my existing
> report and then re-add it. However, if you do this, all your fields on
> the report are dropped.

You probably get a better answer in a forum devoted to Crystal Reports.

In my shop we use ttx-files as the "database" for our report, so the
report has no connection to the SQL Server database. If one adds a column
to the result set, one has to add it to the TTX file too.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Adding Field or Stored Procedure to MSDE

Is there a way to add a field or a stored procedure to a server running MSDE? Like a script on the command line or?? how can this be done.

Thank you,Sure can. Check out the osql command-line utility, which lets you run individual T-SQL statements or script files. Or, there are several tools you can use to manage MSDE:

ASP.NET Enterprise Manager, an open source SQL Server and MSDE management tool.

ASP.NET WebMatrix (which includes a database management tool) from this web site (click on the Web Matrix tab at the top of this page).

Microsoft's Web Data Administrator is a free web-based MSDE management program written using C# and ASP.NET, and includes source code.

You can also access MSDE using Access.

Don|||If I write a stored procedure and then script it, how can I use osql to run the script and add the sp to a database on the server? Can you show me an example? Do I create a cmd file that contains the script?

Thank you,|||Here's how you run a script file:

osql -E -i "myScripts.sql"

And to run a line of T-SQL directly:

osql -E -Q "DROP DATABASE pubs"

There are a gaggle of options for osql, so you'll need to look at the docs to use the ones appropriate for your setup.

Don|||Can you show me a sample of a stored procedure to add a field to a table that is already in a database?

Thanks again for your help,|||

CREATE PROCEDURE MyProc
AS
 ALTER TABLE myTable ADD newCol VARCHAR(20) NULL
GO

Adding Delete to Stored Procedures

I have a stored procedure that Inserts data from one table to the next, I need to add a delete statement to it. I jusually just use the delete option in Access 2003 but I have decided it would be easier to just delete from original table through the sp, only problem is I dont rememeber how to incorporate it into my SP, although at one time I did have it in there then I took it out

REATE PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
FROM EmployeeGamingLicense AS a
WHERE a.STATUS = 'TERMINATED'
IF @.@.Error <> '0'
RETURN

GOI dont believe that a simple answer like
DELETE FROM EmployeeGamingLicense WHERE STATUS = 'TERMINATED'
is what you are looking at. Can u please give us some more details on what is expected?|||From the way your question is phrased, it sounds like you want to insert from tableA to tableB then if that was successful delete from tableA. If so:


declare @.err int
BEGIN TRAN
INSERT INTO TableB (col1, col2, col3, etc)
SELECT col1, col2, col3, etc)
FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

DELETE FROM TableA
WHERE condition

SELECT @.err = @.@.error
IF @.err <> 0
BEGIN
ROLLBACK
RETURN @.err
END

COMMIT|||I am trying to delete the data from the EmployeeGamingLicense table after its been inserted into the Termination table

ALTER PROCEDURE InsertTerms
AS
INSERT INTO [GamingCommissiondb].[dbo].[TERMINATION] ( [TM #],
[FirstName],
[LastName],
[SocialSecurityNumber],
[DateHired],
[Status],
[Title],
[DepartmentName],
[Pictures])

SELECT a.TM#, a.FirstName, a.LASTNAME, a.SSN#, a.HIREDATE, a.STATUS, a.JOBTITLE, a.DEPT#, a.PICS
DELETE FROM EmployeeGamingLicense o
WHERE EXISTS (SELECT * FROM inserted i WHERE STATUS = 'TERMINATED' AND o.[SSN#] = i.[SSN#])
IF @.@.Error <> '0'

RETURN

GO|||inserted is a virtual table only accessable via a trigger, which does not fit with your ALTER proc.

Why do a write just to have to do a select with the "If Exists". Don't make it more complicated than necessary.

This is one of the prime examples of a transaction ... a unit of work that must succeed or fail as a unit. If the insert works and then the server crashes, you have a duplicate entry in two tables. The BEGIN TRAN ... COMMIT block ensures consistency between the two tables ... it either exists in one or the other, thanks to the COMMIT or ROLLBACK.

adding date timestamp to xp_sendmail procedure

I am trying to figure out how to add a time datestamp to my xp_sendmail procedure:

use master;
go

CREATE PROC pr_sendmail
AS

DECLARE @.DT DATETIME
SET @.DT=GETDATE()

BEGIN

EXEC xp_sendmail @.recipients = 'me@.work.com',
@.message = 'send email from SQL Server Stored Procedure.',
@.copy_recipients = 'me@.work.com',
@.subject = 'Job Started at ', @.DT

END

How do I get this to work? Thanks!@.subject = 'Job Started at ' + cast(getdate() as varchar)

you can also use CONVERT instead of CAST to format the date in various different formats.