|
Expert Coming wrote: I know I can read the data, add the values, and then update, but that doesn't seem right.
It is.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
UPDATE Table1 SET X = (X + 1.3)
|
|
|
|
|
I have a query which I am running accross several DB's to get information on several of our store's oulet. The query Below provided the info I need but I am not sure if it is the best way to do it and how it will be affected when we have millions of record in our DB. Or can I make it into a single query so when executed in C# I do do get two table in my dataset. Any comment or suggestion is appreciated.
Select 'My Business' AS BusName, sum(PassVer.PassedVerifier) AS 'Passed Verifiers', Sum(PassVer.CashFunded)AS 'Cash Funded', SUM(PassVer.OtherFunding) AS 'Other Funding', Sum(PassVer.CompletedApplications) AS 'Completed Applications', SUM(PassVer.CompleteFundedCash) AS 'Complete - Funded Cash', SUM(Passver.CompleteNotFundedCash) AS 'Complete-Not Cash', SUM(PassVer.Total) AS 'Total Applications',
cast((Sum(PassVer.PassedVerifier) * 100.00) / sum(PassVer.CompletedApplications) AS Float) AS '% Completed and verified',
cast((SUM(PassVer.CompletedApplications)* 100.00) / Sum(PassVer.Total) AS Float) AS '% of Completed from Total'
FROM (
--Selects Applications which Passed Verifier
Select 1 AS 'PassedVerifier', 0 As 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Pass Validation'
UNION ALL
--Selects Applications which passed the verifier and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 1 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId = 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL
--Selects Applications which passed the verifier and are funded by methods other than Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 1 As 'OtherFunding', 0 AS 'CompletedApplications',0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where A.fundingMethodId != 'Cash' and Q.QueueId= 'Pass Validation'
UNION ALL
--Selects Applications which are completed.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 1 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed'
UNION ALL
--Selects Applications which are completed and are funded by Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 1 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed' and A.fundingMethodId = 'Cash'
UNION ALL
--Selects Applications which are completed and are not funded by Cash.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 1 AS 'CompleteNotFundedCash', 0 AS 'Total'
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
where Q.QueueId= 'Application Completed' and A.fundingMethodId != 'Cash'
UNION ALL
--Total amount of applications.
SELECT 0 AS 'PassedVerifier', 0 AS 'CashFunded', 0 As 'OtherFunding', 0 AS 'CompletedApplications', 0 AS 'CompleteFundedCash', 0 AS 'CompleteNotFundedCash', 1 AS 'Total'
FROM dbo.BK_Account) PassVer
SELECT 'My Business' AS BusName,
CASE WHEN GROUPING (CAST(MONTH(A.CreationDate) AS VARCHAR))= 1 THEN 'Grand Total'
else (CAST(MONTH(A.CreationDate) AS VARCHAR)) end as MonthCreated,
Count(*) AS TotalCompletedApplications,
SUM(A.FundingAmount) AS SumTotalCompletedApplications
FROM dbo.BK_Account A LEFT JOIN dbo.BK_AccountQueue Q ON
A.Guid=Q.AccountGuid
WHERE (A.Visible = 1) AND (Q.QueueId= 'Pass Validation' )
GROUP BY (CAST(MONTH(A.CreationDate) AS VARCHAR))
With rollup
Skan
If you knew it would not compile why didn't you tell me?!?!?!
|
|
|
|
|
Try using case statements instead?
...
CASE WHEN Q.QueueId= 'Pass Validation' THEN 1 ELSE 0 END AS 'PassedVerifier',
CASE WHEN A.fundingMethodId = 'Cash' and Q.QueueId= 'Pass Validation' THEN 1 ELSE 0 END AS 'CashFunded',
...
|
|
|
|
|
I'm trying to allow control over events/records displayed on a calendar using checkboxes.
Here is what I currently have:
http://propeller.head.home.comcast.net/~propeller.head/page1.html
It's just your run-of-the-mill asp calendar with some added AJAX so that when you mouse-over the date's, it displays the list of events for that day.
What I am attempting to add is the ability to control the SELECT (or display) of events by way of some checkboxes. Something that looks like this:
http://propeller.head.home.comcast.net/~propeller.head/page2.html
But im confused about how to update/change SELECT statements on the fly - or if it's possible... Any hints, pointers, samples?
Here is the calendar script:
http://propeller.head.home.comcast.net/~propeller.head/new/calendar.txt
Here's my AJAX event:
http://propeller.head.home.comcast.net/~propeller.head/new/ajaxevent.txt
Thanks!
-swo
|
|
|
|
|
I am creating a user defined type for time only. I have a book called pro sql server 2005 database design and optimization that has the code to create an assembly to create a user defined type for date only, but I need to create one for time only.
Does anyone know what the difference is between creating a UDT for date vs. time?
Has anyone here ever created one before that works?
Do you have the code?
I read somewhere that sql server 2008 has a time only type included in it. Does anyone know if there is a way to get the code that microsoft used to create the assembly in 2008 for the time only data type?
|
|
|
|
|
Hi all,
I am executing the following SP. But when i execute it, I get the error
Server: Msg 16929, Level 16, State 1, Procedure BicRank, Line 34.
The cursor is readonly. The Statement has been terminated.
Please help me to come out from this problem. Its very urgent for me.
This is the SP.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.BicRank /
CREATE PROCEDURE BicRank
AS
-- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS')
-- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007
--rankings are completed per dmoq, service, bicuniverse combination
--100% is considered best
Declare BicRank_DESC Cursor
for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel
where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS')
order by compare, Mo3_percent desc
declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60)
declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER
OPEN BicRank_DESC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_DESC into @compare, @3mo
while (@@fetch_status <> -1)
begin
IF (@heldcompare <> @compare)
begin
set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare
end
ELSE IF (@3mo <> @bic)
begin
set @rank = @rank + 1 set @bic = @3mo
end
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC
FETCH NEXT FROM bicrank_DESC into @compare, @3mo
END
commit transaction
CLOSE BicRank_DESC
DEALLOCATE BicRank_DESC
--determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3')
--rankings are completed per dmoq, service, bicuniverse combination
--0% is considered best
Declare BicRank_ASC Cursor
for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel
where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F')
order by compare, Mo3_percent asc
OPEN BicRank_ASC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_ASC into @compare, @3mo
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare
end
ELSE IF (@3mo <> @bic)
begin
set @rank = @rank + 1 set @bic = @3mo
end
Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_ASC
FETCH NEXT FROM bicrank_ASC into @compare, @3mo
END
CLOSE BicRank_ASC
DEALLOCATE BicRank_ASC
--determineYTD bic andYTD ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3')
--rankings are completed per dmoq, service, bicuniverse combination
--0% is considered best
Declare BicRank_YTD_DESC Cursor
for Select dmoq + service + bicuniverse as compare, YTD_percent from mtce_toexcel
where ( YTD_Percent < 101 AND YTD_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F')
order by compare, YTD_percent asc
declare @YTD float
OPEN BicRank_YTD_DESC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_YTD_DESC into @compare, @YTD
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @YTD set @firstbic = @YTD set @heldcompare = @compare
end
ELSE IF (@YTD <> @bic)
begin
set @rank = @rank + 1 set @bic = @YTD
end
Update MTce_toExcel set YTD_bic = @firstbic,YTD_rank = @rank where current of BicRank_YTD_DESC
FETCH NEXT FROM bicrank_YTD_DESC into @compare, @YTD
END
CLOSE BicRank_YTD_DESC
DEALLOCATE BicRank_YTD_DESC
--determineYTD bic andYTD ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS')
--rankings are completed per dmoq, service, bicuniverse combination
--100% is considered best
-- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007
Declare BicRank_YTD_ASC Cursor
for Select dmoq + service + bicuniverse as compare, YTD_percent from mtce_toexcel
where (YTD_Percent < 101 ) and dmoq in ('TTR_1', 'TTR_2', 'TTR_3','TTR_6','TTR_12','TTR_24','PROGRS')
order by compare, YTD_percent desc
OPEN BicRank_YTD_ASC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_YTD_ASC into @compare, @YTD
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @YTD set @firstbic = @YTD set @heldcompare = @compare
end
ELSE IF (@YTD <> @bic)
begin
set @rank = @rank + 1 set @bic = @YTD
end
Update MTce_toExcel set YTD_bic = @firstbic,YTD_rank = @rank where current of BicRank_YTD_ASC
FETCH NEXT FROM bicrank_YTD_ASC into @compare, @YTD
END
CLOSE BicRank_YTD_ASC
DEALLOCATE BicRank_YTD_ASC
--
--Populate MO3_BIC on records where MO3_BIC is null
--This is due to the MO3_PERCENT being 101, 102 or NULL
--
Declare BicRank_null Cursor
for Select distinct dmoq, service, bicuniverse from mtce_toexcel where mo3_bic is null
OPEN BicRank_null
FETCH NEXT FROM bicrank_null into @dmoq, @service, @bicuniverse
while (@@fetch_status <> -1)
begin
Declare
mo3_bic Cursor For
Select DISTINCT MO3_BIC from mtce_toexcel where mo3_bic is NOT null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
SET @RCD_CNT = @@ROWCOUNT
-- get the mo3_bic
Open mo3_bic
Fetch Next From mo3_bic Into @FIRSTBIC
if @RCD_CNT > 0
BEGIN
UPdate MTce_toExcel set Mo3_bic = @firstbic where mo3_bic is null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
END
Close mo3_bic
Deallocate mo3_bic
FETCH NEXT FROM bicrank_null into @dmoq, @service, @bicuniverse
END
CLOSE BicRank_null
DEALLOCATE BicRank_null
--
--Populate YTD_BIC on records where YTD_BIC is null
--This is due to the YTD_PERCENT being 101, 102 or NULL
--
Declare BicRank_null_ytd Cursor
for Select distinct dmoq, service, bicuniverse from mtce_toexcel where YTD_bic is null
OPEN BicRank_null_ytd
FETCH NEXT FROM bicrank_null_ytd into @dmoq, @service, @bicuniverse
while (@@fetch_status <> -1)
begin
Declare
YTD_bic Cursor For
Select distinct ytd_bic from mtce_toexcel where YTD_bic is not null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
SET @RCD_CNT = @@ROWCOUNT
-- get theYTD_bic
Open YTD_bic
Fetch Next From YTD_bic Into @firstbic
if @RCD_CNT > 0
BEGIN
Update MTce_toExcel set YTD_bic = @firstbic where YTD_bic is null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
END
Close YTD_bic
Deallocate YTD_bic
FETCH NEXT FROM bicrank_null_ytd into @dmoq, @service, @bicuniverse
END
CLOSE BicRank_null_ytd
DEALLOCATE BicRank_null_ytd
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Thanks and Regards,
|
|
|
|
|
This is probably a permissions issue. Check that you have the appropriate permissions to execute the updates. BTW, you could probably rewrite your code without using cursors.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
would u please give me database only with fields too , so that i could run and help u by running this procedure. actually it is really hard to understand as like. u can use my mail id. 
manoj Kumar Jha
"Learn to smile at every situation. See it as an opportunity to prove your strength and ability."
|
|
|
|
|
Your update statement is using "where current of BicRank_DESC". This is only allowed if the cursor was declared with the "for update" clause.
Regards
Andy
|
|
|
|
|
Hi
Thanks lot.
Can you help me out how to apply that for update in my procedure.
Since the same procedure executed in morning but when i tried later it gives an error. But didnt change anything in the procedure.
Regards,
|
|
|
|
|
Hi,
I tried to apply for update like below in SP.
Error:
Server: Msg 16957, Level 16, State 4, Procedure BicRank, Line 12
FOR UPDATE cannot be specified on a READ ONLY cursor.
SP:
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/****** Object: Stored Procedure dbo.BicRank Script Date: 08/01/2008 12:44:52 ******/
ALTER PROCEDURE BicRank
AS
-- determine mo3 bic and m03 ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS')
-- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007
--rankings are completed per dmoq, service, bicuniverse combination
--100% is considered best
Declare BicRank_DESC Cursor
for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel
where (Mo3_Percent < 101) and dmoq in ('TTR_1','TTR_2','TTR_3','TTR_6','TTR_12','TTR_24','PROGRS')
order by compare, Mo3_percent desc
for update of mo3_bic,mo3_rank
declare @3mo as float, @rank as int, @heldcompare as varchar(60), @bic as float, @firstbic as float, @compare as varchar(60)
declare @dmoq as varchar(20), @service as varchar(20), @bicuniverse as varchar(6), @RCD_CNT INTEGER
OPEN BicRank_DESC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_DESC into @compare, @3mo
while (@@fetch_status <> -1)
begin
IF (@heldcompare <> @compare)
begin
set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare
end
ELSE IF (@3mo <> @bic)
begin
set @rank = @rank + 1 set @bic = @3mo
end
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_DESC
FETCH NEXT FROM bicrank_DESC into @compare, @3mo
END
commit transaction
CLOSE BicRank_DESC
DEALLOCATE BicRank_DESC
--determine mo3 bic and m03 ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3')
--rankings are completed per dmoq, service, bicuniverse combination
--0% is considered best
Declare BicRank_ASC Cursor
for Select dmoq + service + bicuniverse as compare, mo3_percent from mtce_toexcel
where ( Mo3_Percent < 101 AND MO3_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F')
order by compare, Mo3_percent asc
for update of mo3_bic,mo3_rank
OPEN BicRank_ASC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_ASC into @compare, @3mo
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @3mo set @firstbic = @3mo set @heldcompare = @compare
end
ELSE IF (@3mo <> @bic)
begin
set @rank = @rank + 1 set @bic = @3mo
end
Update MTce_toExcel set Mo3_bic = @firstbic, Mo3_rank = @rank where current of BicRank_ASC
FETCH NEXT FROM bicrank_ASC into @compare, @3mo
END
CLOSE BicRank_ASC
DEALLOCATE BicRank_ASC
--determineYTD bic andYTD ranking for mtc dmoq's ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3')
--rankings are completed per dmoq, service, bicuniverse combination
--0% is considered best
Declare BicRank_YTD_DESC Cursor
for Select dmoq + service + bicuniverse as compare, YTD_percent from mtce_toexcel
where ( YTD_Percent < 101 AND YTD_PERCENT IS NOT NULL) and dmoq in ('FAIL_FREQ', 'NRF', 'MTTR','MTTR_3','MTTR_F')
order by compare, YTD_percent asc
for update of ytd_bic,ytd_rank
declare @YTD float
OPEN BicRank_YTD_DESC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_YTD_DESC into @compare, @YTD
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @YTD set @firstbic = @YTD set @heldcompare = @compare
end
ELSE IF (@YTD <> @bic)
begin
set @rank = @rank + 1 set @bic = @YTD
end
Update MTce_toExcel set YTD_bic = @firstbic,YTD_rank = @rank where current of BicRank_YTD_DESC
FETCH NEXT FROM bicrank_YTD_DESC into @compare, @YTD
END
CLOSE BicRank_YTD_DESC
DEALLOCATE BicRank_YTD_DESC
--determineYTD bic andYTD ranking for mtc dmoq's ('TTR_1', 'TTR_2', 'TTR_3', 'PROGRS')
--rankings are completed per dmoq, service, bicuniverse combination
--100% is considered best
-- ADDED ('TTR_6', 'TTR_12', 'TTR_24') By Nirmala 02/09/2007
Declare BicRank_YTD_ASC Cursor
for Select dmoq + service + bicuniverse as compare, YTD_percent from mtce_toexcel
where (YTD_Percent < 101 ) and dmoq in ('TTR_1', 'TTR_2', 'TTR_3','TTR_6','TTR_12','TTR_24','PROGRS')
order by compare, YTD_percent desc
for update of ytd_bic,ytd_rank
OPEN BicRank_YTD_ASC
set @heldcompare = 'xxxxxxxxxxxxxxxxxxxxxxx'
FETCH NEXT FROM bicrank_YTD_ASC into @compare, @YTD
while (@@fetch_status <> -1)
begin
IF @heldcompare <> @compare
begin
set @rank = 1 set @bic = @YTD set @firstbic = @YTD set @heldcompare = @compare
end
ELSE IF (@YTD <> @bic)
begin
set @rank = @rank + 1 set @bic = @YTD
end
Update MTce_toExcel set YTD_bic = @firstbic,YTD_rank = @rank where current of BicRank_YTD_ASC
FETCH NEXT FROM bicrank_YTD_ASC into @compare, @YTD
END
CLOSE BicRank_YTD_ASC
DEALLOCATE BicRank_YTD_ASC
--
--Populate MO3_BIC on records where MO3_BIC is null
--This is due to the MO3_PERCENT being 101, 102 or NULL
--
Declare BicRank_null Cursor
for Select distinct dmoq, service, bicuniverse from mtce_toexcel where mo3_bic is null
OPEN BicRank_null
FETCH NEXT FROM bicrank_null into @dmoq, @service, @bicuniverse
while (@@fetch_status <> -1)
begin
Declare
mo3_bic Cursor For
Select DISTINCT MO3_BIC from mtce_toexcel where mo3_bic is NOT null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
SET @RCD_CNT = @@ROWCOUNT
-- get the mo3_bic
Open mo3_bic
Fetch Next From mo3_bic Into @FIRSTBIC
if @RCD_CNT > 0
BEGIN
UPdate MTce_toExcel set Mo3_bic = @firstbic where mo3_bic is null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
END
Close mo3_bic
Deallocate mo3_bic
FETCH NEXT FROM bicrank_null into @dmoq, @service, @bicuniverse
END
CLOSE BicRank_null
DEALLOCATE BicRank_null
--
--Populate YTD_BIC on records where YTD_BIC is null
--This is due to the YTD_PERCENT being 101, 102 or NULL
--
Declare BicRank_null_ytd Cursor
for Select distinct dmoq, service, bicuniverse from mtce_toexcel where YTD_bic is null
OPEN BicRank_null_ytd
FETCH NEXT FROM bicrank_null_ytd into @dmoq, @service, @bicuniverse
while (@@fetch_status <> -1)
begin
Declare
YTD_bic Cursor For
Select distinct ytd_bic from mtce_toexcel where YTD_bic is not null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
SET @RCD_CNT = @@ROWCOUNT
-- get theYTD_bic
Open YTD_bic
Fetch Next From YTD_bic Into @firstbic
if @RCD_CNT > 0
BEGIN
Update MTce_toExcel set YTD_bic = @firstbic where YTD_bic is null and
dmoq = @dmoq And service = @service and bicuniverse = @bicuniverse
END
Close YTD_bic
Deallocate YTD_bic
FETCH NEXT FROM bicrank_null_ytd into @dmoq, @service, @bicuniverse
END
CLOSE BicRank_null_ytd
DEALLOCATE BicRank_null_ytd
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
|
|
|
|
Hello All,
Our server poopped the bed and we got it back to crawling slowly. We had some small but slightly important databases on the server which we are trying to reattach. We keep receiving an error indicating the log file is corrupted in some manner, therefore we are trying to reattach the mdf file without the ldf. Again we are running into issues.
Has anyone ever successfully reattached a database without the LDF file?
Any responses will be greatly appreciated.
|
|
|
|
|
The way SQL Server works is that when you make a change to the database, the operation is immediately logged to the log file. It then makes the changes to the in-memory copy of the data, but does not immediately flush those changes back to the data file. It only does so when it needs to flush data out to reuse the buffer space, on idle, and when a checkpoint operation occurs. The checkpoint operation basically synchronizes the data and log, and is done frequently enough that the configured recovery interval can be achieved, that is, the time to recover from a system failure assuming that the data and log files are intact. The default recover interval is one minute, that is, a recovery should take around one minute to complete.
(In practice the log is also buffered to allow multiple log records to be written out in one operation. However, when you commit a transaction, the log records relating to that transaction have to be completely written to disk before SQL Server will proceed with the following statement or return to the client.)
The upshot of this lazy writing is that the data file may not be transactionally consistent. In fact, it probably isn't. Your best bet is to restore from a known-good backup - you will of course lose the operations that took place after the backup was made.
I'm not sure if you can reattach this database. What you probably should have done is run DBCC CHECKDB with one of the REPAIR options. You may be able to attach using CREATE DATABASE ... FOR ATTACH_REBUILD_LOG on SQL Server 2005; for SQL Server 2000 try sp_attach_db.
For the future, if you're not already doing so: it's considered best practice to keep your transaction logs on a separate disk (array) from the data, so that a failed data volume doesn't cause you to lose your transaction logs as well - remember that if you have logs but no data file you can back up the logs, restore a full database backup then restore the log backup to recover to the point of failure. In addition you can get some performance benefit because the disk head is typically over the place that the next block of log records need to be written - disk latency is governed by head seek time. If you plan to use RAID to protect your logs, go with RAID 1, mirroring - there is no benefit from striping (the writes are sequential and would simply target each disk in turn) and RAID 5 hurts write performance.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
I know MS-SQL can connect to other databases to communicate to them with Linked Servers. Does anyone know if Sybase can do this? If so, how is this accomplished? Thank you in advance.
modified on Tuesday, January 22, 2008 9:49:42 AM
|
|
|
|
|
Hi,
I have an sp name Movie_SP which returns all the movies by taking MovieName
Now in my New sp(Search_Movies_SP) I want to use the out put of the first SP(Movie_SP)
and with the result of Movie_SP I have to manipulate
Actually I need to set priorities as my first SP Movie_SP is returning all the movies with say BLACK in an Improper way
I want to make it to a order where Black comes First,
then The Movies which starts with Black second,
and the third is The Movies which contains Black
Plz help me
thanks
Pashi
|
|
|
|
|
Could've been better if you have mentioned which database you are working with.Assuming that you are working with Sql Server...
pashitech wrote: use the out put of the first SP
Are you looking out for something like an Output Parameter[^]?
If no (which I assume so), I guess you'll have to populate the data onto a temporary table for manipulations.
pashitech wrote: make it to a order
You may try using the LIKE[^]operator.
Select * from tblMovies where MovieName = 'Black'
The above query fetches you details where the movie name is black (an exact match)
Select * from tblMovies where MovieName Like 'Black%'
The above query gives you details where the movie name begins with black (including black)
Select * from tblMovies where MovieName like '%Black%'
This one fetches movie names that contain the word black.
-- For better usage with the like operator, refer to the URL linked.
|
|
|
|
|
How about:
create table #temp1 (
--list of columns returned by Movie_SP goes here
)
insert into #temp1
exec Movie_SP @Param1, Param2, ...
select * from #temp1
order by case
when Title = 'Black' then 'A'
when Title LIKE 'Black %' then 'B'
else 'C'
end,
Title
You can use "insert into" to return the resultset of a stored procedure into a temporary table. The following select statement uses a case statement to prioritorise the ordering. You might need to tweak this a bit because I didn't quite understand your requirements.
Regards
Andy
|
|
|
|
|
I need to create a User Defined Type for time. I tried rewriting my database tables as shown below and I get the error that is shown below.
Do I need to do something else to create them? Do I need to use hh:mm for the formatting of the datapart I need to use?
All I need in ts_from and ts_to is the hour and the minutes.
I am using SQL Server Express 2005.
create table tbl_teach_sched (<br />
ts_id int NOT NULL IDENTITY(1000,1),<br />
teach_id int,<br />
ts_day text,<br />
ts_datetime datetime,<br />
ts_from as (datepart(hh:mm, ts_datetime)) persisted,<br />
ts_to as (datepart(hh:mm, ts_datetime)) persisted,<br />
primary key(ts_id),<br />
foreign key(teach_id) references tbl_teachers(teach_id));
This is the error.
Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'hh:'.
Msg 132, Level 15, State 1, Line 8
The label 'hh' has already been declared. Label names must be unique within a query batch or stored procedure.
|
|
|
|
|
Yeah, you can't do that.
Either just use the DateTime, or use a smallint.
The smallint could be the number of minutes or seconds from midnight, or it could be hours*100+minutes.
But I spend so much time converting between time-storage schemes that I prefer DateTime; the savings in maintenance makes up for the waste of space.
|
|
|
|
|
I have a book taht suggests creating a user defined data type for time only? How is it that I can't create a user defined type for time alone?
|
|
|
|
|
I have Googled without success (I keep getting blog posts that deride Access/Jet SQL as 'sucking'* with a comment feature at the bottom of the page), but does anybody know how to code-comment Access/Jet SQL?
I'm attempting to show our resident VB "whiz" how he's left himself open to SQL Injection attacks - when his application targets SQL Server I can successfully hack his code in the manner described by various posts/articles by injecting my own SQL and by commenting out his, but in Access/Jet SQL it doesn't appear to work.
*Yes, I'm inclined to agree with the majority of these posts.
|
|
|
|
|
Hi,
I am new in the field of databases.
I am trying to create a strong typed dataset (using vs 2005 add data set wizard).
The table in my data base include foreign keys (relations).
When the wizard finishes the dataset is created with all the necessary tables and fields, EXCEPT for data relations.
When I am performing the same thing with access data base the relations are loaded well.
Does anyone have an idea of what it could be.
Sincerely yours
Y.R.
|
|
|
|
|
how would I select and count the number of values that match: j0107_a131 and show the following two values
j0107_a131
j0107_a131_01
the roomcode is in a string that is why i specified below 'like roomcode'
i.e. select count (room_code) from rooms where room_code like room_code
hope that makes sense cheers
|
|
|
|
|
Well, you could always do this:
select @room_code = @room_code + '%'
select count(room_code) from rooms where room_code like @room_code
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
