Tuesday, March 27, 2012
Can't CHECKDB model
When i run DBCC CHECKDB on model, DBBC never end. I try to
backup Model database and the following error appear.
"BAckup, CHECKALLOC, bulk copy, select into, and file
manipulation(such as create file) operations on a database
must be serialized. Reissue the statement after the
current backup, CHECKALLOC or file manipulation is
completed. Backup or Restore operation terminating
abnormally"
I dont know what am i supose to do :\
Best regardsIs the model db backing up right now? Have you backed it up recently?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ebab01c43cf6$af264a00$a301280a@.phx.gbl...
> Hello,
> When i run DBCC CHECKDB on model, DBBC never end. I try to
> backup Model database and the following error appear.
> "BAckup, CHECKALLOC, bulk copy, select into, and file
> manipulation(such as create file) operations on a database
> must be serialized. Reissue the statement after the
> current backup, CHECKALLOC or file manipulation is
> completed. Backup or Restore operation terminating
> abnormally"
> I dont know what am i supose to do :\
> Best regards|||The error message says it all. There are some operations that cannot be perf
ormed simultaneously for a
database. Make sure you schedule your jobs properly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:ebab01c43cf6$af264a00$a3
01280a@.phx.gbl...
> Hello,
> When i run DBCC CHECKDB on model, DBBC never end. I try to
> backup Model database and the following error appear.
> "BAckup, CHECKALLOC, bulk copy, select into, and file
> manipulation(such as create file) operations on a database
> must be serialized. Reissue the statement after the
> current backup, CHECKALLOC or file manipulation is
> completed. Backup or Restore operation terminating
> abnormally"
> I dont know what am i supose to do :\
> Best regards
Can't CHECKDB model
When i run DBCC CHECKDB on model, DBBC never end. I try to
backup Model database and the following error appear.
"BAckup, CHECKALLOC, bulk copy, select into, and file
manipulation(such as create file) operations on a database
must be serialized. Reissue the statement after the
current backup, CHECKALLOC or file manipulation is
completed. Backup or Restore operation terminating
abnormally"
I dont know what am i supose to do :\
Best regards
Is the model db backing up right now? Have you backed it up recently?
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ebab01c43cf6$af264a00$a301280a@.phx.gbl...
> Hello,
> When i run DBCC CHECKDB on model, DBBC never end. I try to
> backup Model database and the following error appear.
> "BAckup, CHECKALLOC, bulk copy, select into, and file
> manipulation(such as create file) operations on a database
> must be serialized. Reissue the statement after the
> current backup, CHECKALLOC or file manipulation is
> completed. Backup or Restore operation terminating
> abnormally"
> I dont know what am i supose to do :\
> Best regards
|||The error message says it all. There are some operations that cannot be performed simultaneously for a
database. Make sure you schedule your jobs properly.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:ebab01c43cf6$af264a00$a301280a@.phx.gbl...
> Hello,
> When i run DBCC CHECKDB on model, DBBC never end. I try to
> backup Model database and the following error appear.
> "BAckup, CHECKALLOC, bulk copy, select into, and file
> manipulation(such as create file) operations on a database
> must be serialized. Reissue the statement after the
> current backup, CHECKALLOC or file manipulation is
> completed. Backup or Restore operation terminating
> abnormally"
> I dont know what am i supose to do :\
> Best regards
sql
Can't CHECKDB model
When i run DBCC CHECKDB on model, DBBC never end. I try to
backup Model database and the following error appear.
"BAckup, CHECKALLOC, bulk copy, select into, and file
manipulation(such as create file) operations on a database
must be serialized. Reissue the statement after the
current backup, CHECKALLOC or file manipulation is
completed. Backup or Restore operation terminating
abnormally"
I dont know what am i supose to do :\
Best regardsIs the model db backing up right now? Have you backed it up recently?
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message
news:ebab01c43cf6$af264a00$a301280a@.phx.gbl...
> Hello,
> When i run DBCC CHECKDB on model, DBBC never end. I try to
> backup Model database and the following error appear.
> "BAckup, CHECKALLOC, bulk copy, select into, and file
> manipulation(such as create file) operations on a database
> must be serialized. Reissue the statement after the
> current backup, CHECKALLOC or file manipulation is
> completed. Backup or Restore operation terminating
> abnormally"
> I dont know what am i supose to do :\
> Best regards|||The error message says it all. There are some operations that cannot be performed simultaneously for a
database. Make sure you schedule your jobs properly.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"CC&JM" <anonymous@.discussions.microsoft.com> wrote in message news:ebab01c43cf6$af264a00$a301280a@.phx.gbl...
> Hello,
> When i run DBCC CHECKDB on model, DBBC never end. I try to
> backup Model database and the following error appear.
> "BAckup, CHECKALLOC, bulk copy, select into, and file
> manipulation(such as create file) operations on a database
> must be serialized. Reissue the statement after the
> current backup, CHECKALLOC or file manipulation is
> completed. Backup or Restore operation terminating
> abnormally"
> I dont know what am i supose to do :\
> Best regards
Can't beat the dynamic sql
I've put in a lot of work in the following stored proc only to find
that it's slower then the old project's dynamic built query(been
running since 1994, I think). Can anyone see any obvious flaw, I know
it's big, but downsizing it might take away some part that contains
some error that someone might find:) What it retrieves is apartments
based on the user's preferences. Anyways here it is, thanx for any
help:
SELECT
Member.l_MemberID as mem_MemberID,
Member.l_PinCode as mem_PinCode,
Member.s_FirstName as mem_FirstName,
Member.s_LastName as mem_LastName,
Member.s_PersNr as mem_SocSec,
Member.s_Co_Nr as mem_CareOf,
Member.s_Address as mem_Address,
Member.l_ZipCode as mem_ZipCode,
Member.s_City as mem_City,
Member.s_WorkPhone as mem_WorkPhone,
Member.s_Phone as mem_Phone,
Member.s_Cell as mem_Cell,
Member.s_Email as mem_Email,
Member.s_Notes as mem_Notes,
Member.b_Rent_Acknowledge as mem_RentAcknowledge,
estates.L_ESTATEID as est_EstateID,
estates.s_HolderName as est_HolderName,
estates.s_HolderNr as est_HolderNr,
estates.s_HolderPhone as est_HolderPhone,
isnull(estates.n_type,0) as est_Type,
estates.B_ONLYCOMPANIES as est_OnlyCompanies,
Convert(nvarchar, estates.d_rooms) as est_Rooms,
estates.n_BedRooms as est_Bedrooms,
RTRIM(estates.S_KITCHEN) as est_Kitchen,
Estates.D_Area as est_Size,
Convert(nvarchar, estates.d_area) as est_Size,
estates.D_BiArea as est_BiArea,
estates.D_LotSize as est_LotSize,
Estates.N_YearBuilt as est_YearBuilt,
Estates.N_YearRestored as est_YearRestored,
Estates.S_Area as est_Area,
Estates.S_Address as est_Address,
RTRIM(Estates.S_AddressNr) as est_AddressNr,
Estates.L_ZipCode as est_ZipCode,
Estates.S_City as est_City,
Estates.N_Floor as est_Floor,
Estates.N_TotFloors as est_TotalFloors,
isnull(Estates.B_Elevator,0) as est_Elevator,
RTRIM(Estates.S_Furnitures) as est_Furnished,
RTRIM(Estates.S_Terrace) as est_Terrace,
isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
isnull(estates.B_SHOWER,0) as est_Shower,
isnull(estates.B_BATHTUB,0) as est_BathTub,
isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
isnull(estates.B_PARABOL,0) as est_Parabol,
isnull(estates.B_TV,0) as est_TV,
isnull(estates.B_TILESTOVE,0) as est_TileStove,
isnull(estates.B_VIDEO,0) as est_Video,
isnull(estates.B_COMPUTER,0) as est_Computer,
isnull(estates.B_MICRO,0) as est_Micro,
isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
isnull(estates.B_BATHROOM,0) as est_Bathroom,
isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
isnull(estates.B_INCHEAT,0) as est_IncHeat,
isnull(estates.B_CABLETV,0) as est_CableTV,
isnull(estates.B_INCWATER,0) as est_IncWater,
isnull(estates.B_BROADBAND,0) as est_Broadband,
isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
isnull(estates.B_PHONE,0) as est_Phone,
isnull(estates.B_INCELEC,0) as est_IncElec,
isnull(estates.B_GAS,0) as est_Gas,
isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
isnull(estates.B_QUIETAREA,0) as est_QuietArea,
isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
isnull(estates.B_SEAVIEW,0) as est_SeaView,
isnull(estates.B_NOPETS,0) as est_NoPets,
isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
isnull(estates.B_NOKIDS,0) as est_NoKids,
isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyW
isnull(estates.B_MAN,0) as est_Man,
isnull(estates.B_WOMAN,0) as est_Woman,
Estates.S_RentAdvance as est_RentAdvance,
Estates.L_Rent as est_Rent,
isnull(Estates.B_Permit,0) as est_Permit,
Estates.S_Landlord as est_Landlord,
Estates.S_Landl_Phone as est_LandlordPhone,
Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
Estates.S_Description as est_Description,
isnull(estates.N_STATE,0) as est_State,
isnull(Estates.B_Printed,0) as est_Printed,
isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
Estates.L_RegisteredID as est_RegisteredID,
Estates.Dat_Registered as est_DatRegistered,
Estates.L_UpdatedID as est_UpdatedID,
Estates.Dat_Updated as est_DatUpdated,
Estates.Dat_Commit_Printed as est_DatCommitPrinted,
Estates.S_TypeBekr as est_TypeCommit,
Estates.S_Comments as est_Comments,
Estates.L_AuthorizedID as est_AuthorizedID,
Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
estates.L_AgentID as est_AgentID
FROM
estates
left join multimedia on multimedia.l_estateid=estates.l_estateid and
isnull(N_INDEX,1)=1
left join member with (nolock) on estates.l_memberid=member.l_memberid
WHERE
isnull(estates.B_FIRSTHAND,0) = 0 AND
isnull(dat_show,getdate()) <= getdate() AND
(ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
10000 else datediff(dd, isnull(case when dat_from<getdate() then
getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
end)>= @.PeriodMin)) AND
((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
10000 else datediff(dd, isnull(case when dat_from<getdate() then
getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
end)<= @.PeriodMax)) AND
((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
(((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
estates.l_zipcode BETWEEN 76100 AND 76295))OR
((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
estates.l_zipcode BETWEEN 66010 AND 66899))OR
((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
estates.l_zipcode BETWEEN 55000 AND 66090 OR
estates.l_zipcode BETWEEN 66900 AND 76099 OR
estates.l_zipcode BETWEEN 76296 AND 99999))OR
((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
estates.l_zipcode BETWEEN 11744 AND 11759 OR
estates.l_zipcode BETWEEN 11600 AND 11899))OR
((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
estates.l_zipcode BETWEEN 11743 AND 11743 OR
estates.l_zipcode BETWEEN 11760 AND 11799))OR
((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
estates.l_zipcode BETWEEN 13500 AND 13699 OR
estates.l_zipcode BETWEEN 13000 AND 13099))OR
((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
estates.l_zipcode BETWEEN 11744 AND 11759 OR
estates.l_zipcode BETWEEN 11600 AND 11899))OR
((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
((@.State = -1)OR (estates.N_STATE = @.State)) AND
((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
getdate())<=@.PublDaysMax)) AND
((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
@.PrivateCompany)) AND
((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
(UPPER(@.Street)+'%')))
ORDER BY
CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
DESC,
CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL ENDtonicvodka wrote:
> Hi all!
> I've put in a lot of work in the following stored proc only to find
> that it's slower then the old project's dynamic built query(been
> running since 1994, I think). Can anyone see any obvious flaw, I know
> it's big, but downsizing it might take away some part that contains
> some error that someone might find:) What it retrieves is apartments
> based on the user's preferences. Anyways here it is, thanx for any
> help:
>
> SELECT
> Member.l_MemberID as mem_MemberID,
> Member.l_PinCode as mem_PinCode,
> Member.s_FirstName as mem_FirstName,
> Member.s_LastName as mem_LastName,
> Member.s_PersNr as mem_SocSec,
> Member.s_Co_Nr as mem_CareOf,
> Member.s_Address as mem_Address,
> Member.l_ZipCode as mem_ZipCode,
> Member.s_City as mem_City,
> Member.s_WorkPhone as mem_WorkPhone,
> Member.s_Phone as mem_Phone,
> Member.s_Cell as mem_Cell,
> Member.s_Email as mem_Email,
> Member.s_Notes as mem_Notes,
> Member.b_Rent_Acknowledge as mem_RentAcknowledge,
> estates.L_ESTATEID as est_EstateID,
> estates.s_HolderName as est_HolderName,
> estates.s_HolderNr as est_HolderNr,
> estates.s_HolderPhone as est_HolderPhone,
> isnull(estates.n_type,0) as est_Type,
> estates.B_ONLYCOMPANIES as est_OnlyCompanies,
> Convert(nvarchar, estates.d_rooms) as est_Rooms,
> estates.n_BedRooms as est_Bedrooms,
> RTRIM(estates.S_KITCHEN) as est_Kitchen,
> Estates.D_Area as est_Size,
> Convert(nvarchar, estates.d_area) as est_Size,
> estates.D_BiArea as est_BiArea,
> estates.D_LotSize as est_LotSize,
> Estates.N_YearBuilt as est_YearBuilt,
> Estates.N_YearRestored as est_YearRestored,
> Estates.S_Area as est_Area,
> Estates.S_Address as est_Address,
> RTRIM(Estates.S_AddressNr) as est_AddressNr,
> Estates.L_ZipCode as est_ZipCode,
> Estates.S_City as est_City,
> Estates.N_Floor as est_Floor,
> Estates.N_TotFloors as est_TotalFloors,
> isnull(Estates.B_Elevator,0) as est_Elevator,
> RTRIM(Estates.S_Furnitures) as est_Furnished,
> RTRIM(Estates.S_Terrace) as est_Terrace,
> isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
> isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
> isnull(estates.B_SHOWER,0) as est_Shower,
> isnull(estates.B_BATHTUB,0) as est_BathTub,
> isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
> isnull(estates.B_PARABOL,0) as est_Parabol,
> isnull(estates.B_TV,0) as est_TV,
> isnull(estates.B_TILESTOVE,0) as est_TileStove,
> isnull(estates.B_VIDEO,0) as est_Video,
> isnull(estates.B_COMPUTER,0) as est_Computer,
> isnull(estates.B_MICRO,0) as est_Micro,
> isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
> isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
> isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
> isnull(estates.B_BATHROOM,0) as est_Bathroom,
> isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
> isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
> isnull(estates.B_INCHEAT,0) as est_IncHeat,
> isnull(estates.B_CABLETV,0) as est_CableTV,
> isnull(estates.B_INCWATER,0) as est_IncWater,
> isnull(estates.B_BROADBAND,0) as est_Broadband,
> isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
> isnull(estates.B_PHONE,0) as est_Phone,
> isnull(estates.B_INCELEC,0) as est_IncElec,
> isnull(estates.B_GAS,0) as est_Gas,
> isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
> isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
> isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
> isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
> isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
> isnull(estates.B_QUIETAREA,0) as est_QuietArea,
> isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
> isnull(estates.B_SEAVIEW,0) as est_SeaView,
> isnull(estates.B_NOPETS,0) as est_NoPets,
> isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
> isnull(estates.B_NOKIDS,0) as est_NoKids,
> isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyW
> isnull(estates.B_MAN,0) as est_Man,
> isnull(estates.B_WOMAN,0) as est_Woman,
> Estates.S_RentAdvance as est_RentAdvance,
> Estates.L_Rent as est_Rent,
> isnull(Estates.B_Permit,0) as est_Permit,
> Estates.S_Landlord as est_Landlord,
> Estates.S_Landl_Phone as est_LandlordPhone,
> Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
> Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
> isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
> Estates.S_Description as est_Description,
> isnull(estates.N_STATE,0) as est_State,
> isnull(Estates.B_Printed,0) as est_Printed,
> isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
> Estates.L_RegisteredID as est_RegisteredID,
> Estates.Dat_Registered as est_DatRegistered,
> Estates.L_UpdatedID as est_UpdatedID,
> Estates.Dat_Updated as est_DatUpdated,
> Estates.Dat_Commit_Printed as est_DatCommitPrinted,
> Estates.S_TypeBekr as est_TypeCommit,
> Estates.S_Comments as est_Comments,
> Estates.L_AuthorizedID as est_AuthorizedID,
> Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
> estates.L_AgentID as est_AgentID
> FROM
> estates
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
> isnull(N_INDEX,1)=1
> left join member with (nolock) on estates.l_memberid=member.l_memberid
> WHERE
> isnull(estates.B_FIRSTHAND,0) = 0 AND
> isnull(dat_show,getdate()) <= getdate() AND
> (ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
> ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
> ((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
> ((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
> ((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
> ((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
> ((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
> ((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
> ((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
> 10000 else datediff(dd, isnull(case when dat_from<getdate() then
> getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
> end)>= @.PeriodMin)) AND
> ((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
> 10000 else datediff(dd, isnull(case when dat_from<getdate() then
> getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
> end)<= @.PeriodMax)) AND
> ((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
> (((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
> estates.l_zipcode BETWEEN 76100 AND 76295))OR
> ((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
> estates.l_zipcode BETWEEN 66010 AND 66899))OR
> ((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
> ((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
> estates.l_zipcode BETWEEN 55000 AND 66090 OR
> estates.l_zipcode BETWEEN 66900 AND 76099 OR
> estates.l_zipcode BETWEEN 76296 AND 99999))OR
> ((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
> estates.l_zipcode BETWEEN 11743 AND 11743 OR
> estates.l_zipcode BETWEEN 11760 AND 11799))OR
> ((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
> estates.l_zipcode BETWEEN 13500 AND 13699 OR
> estates.l_zipcode BETWEEN 13000 AND 13099))OR
> ((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
> ((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
> ((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
> ((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
> ((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
> ((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
> ((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
> ((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
> ((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
> ((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
> ((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
> ((@.State = -1)OR (estates.N_STATE = @.State)) AND
> ((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
> ((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
> getdate())<=@.PublDaysMax)) AND
> ((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
> @.PrivateCompany)) AND
> ((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
> ((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
> (UPPER(@.Street)+'%')))
> ORDER BY
> CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
> DESC,
> CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
> CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
> CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL END
Here are some suggestions.
1. Get rid of all the ORs. For example if you set a default value for
@.roomsmax of 10000 instead of -1 you should be able to just D_ROOMS
BETWEEN @.roomsmin AND @.roomsmax. Same for the other variables.
2. Create a table of zipcodes to join with instead of using CASE
expressions.
3. Pass in date From and To ranges instead of doing the date arithmetic
in the query. You are presently forcing those DATEDIFFs to be performed
for every row.
4. Make some columns non-nullable to eliminate the ISNULLs.
The basic idea is to get expressions in your WHERE clause that are
sargable - that means columns being compared to constants or to other
columns without complex expressions on the columns themselves. Once
you've achieved that you can take best advantage of any indexes you
create.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx|||Dynamic SQL may be faster for something like this, despite
all efforts, but it's worth doing a bit more to see. You need
to balance the security and other risks against the performance.
If you want to improve the performance of the non-dynamic
query, below is another suggestion beyond what David offered.
(You should first look at the query plans and profile
both compilation and execution time to see where the
problems are, then focus on the bottlenecks.)
Precalculate what you can when the data is entered in the first
place. The big one I see is zip code area. Every property
is in some zip code area, so why not store that value in a table?
It would be much better to use estates.l_ziparea = @.ZipArea
than the big mess you have, even if you have to maintain a ziparea
column in a separate table and join to it.
Steve Kass
Drew University
tonicvodka wrote:
>Hi all!
>I've put in a lot of work in the following stored proc only to find
>that it's slower then the old project's dynamic built query(been
>running since 1994, I think). Can anyone see any obvious flaw, I know
>it's big, but downsizing it might take away some part that contains
>some error that someone might find:) What it retrieves is apartments
>based on the user's preferences. Anyways here it is, thanx for any
>help:
>
>SELECT
> Member.l_MemberID as mem_MemberID,
> Member.l_PinCode as mem_PinCode,
> Member.s_FirstName as mem_FirstName,
> Member.s_LastName as mem_LastName,
> Member.s_PersNr as mem_SocSec,
> Member.s_Co_Nr as mem_CareOf,
> Member.s_Address as mem_Address,
> Member.l_ZipCode as mem_ZipCode,
> Member.s_City as mem_City,
> Member.s_WorkPhone as mem_WorkPhone,
> Member.s_Phone as mem_Phone,
> Member.s_Cell as mem_Cell,
> Member.s_Email as mem_Email,
> Member.s_Notes as mem_Notes,
> Member.b_Rent_Acknowledge as mem_RentAcknowledge,
> estates.L_ESTATEID as est_EstateID,
> estates.s_HolderName as est_HolderName,
> estates.s_HolderNr as est_HolderNr,
> estates.s_HolderPhone as est_HolderPhone,
> isnull(estates.n_type,0) as est_Type,
> estates.B_ONLYCOMPANIES as est_OnlyCompanies,
> Convert(nvarchar, estates.d_rooms) as est_Rooms,
> estates.n_BedRooms as est_Bedrooms,
> RTRIM(estates.S_KITCHEN) as est_Kitchen,
> Estates.D_Area as est_Size,
> Convert(nvarchar, estates.d_area) as est_Size,
> estates.D_BiArea as est_BiArea,
> estates.D_LotSize as est_LotSize,
> Estates.N_YearBuilt as est_YearBuilt,
> Estates.N_YearRestored as est_YearRestored,
> Estates.S_Area as est_Area,
> Estates.S_Address as est_Address,
> RTRIM(Estates.S_AddressNr) as est_AddressNr,
> Estates.L_ZipCode as est_ZipCode,
> Estates.S_City as est_City,
> Estates.N_Floor as est_Floor,
> Estates.N_TotFloors as est_TotalFloors,
> isnull(Estates.B_Elevator,0) as est_Elevator,
> RTRIM(Estates.S_Furnitures) as est_Furnished,
> RTRIM(Estates.S_Terrace) as est_Terrace,
> isnull(estates.B_LAUNDRYROOM,0) as est_LaundryRoom,
> isnull(estates.B_WASHINGMACHINE,0) as est_WashingMachine,
> isnull(estates.B_SHOWER,0) as est_Shower,
> isnull(estates.B_BATHTUB,0) as est_BathTub,
> isnull(estates.B_DISHWASHER,0) as est_Dishwasher,
> isnull(estates.B_PARABOL,0) as est_Parabol,
> isnull(estates.B_TV,0) as est_TV,
> isnull(estates.B_TILESTOVE,0) as est_TileStove,
> isnull(estates.B_VIDEO,0) as est_Video,
> isnull(estates.B_COMPUTER,0) as est_Computer,
> isnull(estates.B_MICRO,0) as est_Micro,
> isnull(estates.B_HOUSEROOM,0) as est_HouseRoom,
> isnull(estates.B_OWNENTRANCE,0) as est_OwnEntrance,
> isnull(estates.B_OWNTOILET,0) as est_OwnToilet,
> isnull(estates.B_BATHROOM,0) as est_Bathroom,
> isnull(estates.B_BATHROOMPART,0) as est_BathroomPart,
> isnull(estates.B_LIVINGROOM,0) as est_LivingRoom,
> isnull(estates.B_INCHEAT,0) as est_IncHeat,
> isnull(estates.B_CABLETV,0) as est_CableTV,
> isnull(estates.B_INCWATER,0) as est_IncWater,
> isnull(estates.B_BROADBAND,0) as est_Broadband,
> isnull(estates.B_GARBAGEREM,0) as est_GarbageRem,
> isnull(estates.B_PHONE,0) as est_Phone,
> isnull(estates.B_INCELEC,0) as est_IncElec,
> isnull(estates.B_GAS,0) as est_Gas,
> isnull(estates.B_CLOSETRAIN,0) as est_CloseTrain,
> isnull(estates.B_CLOSETUBE,0) as est_CloseTube,
> isnull(estates.B_CLOSEBUS,0) as est_CloseBus,
> isnull(estates.B_CLOSETVAR,0) as est_CloseTvar,
> isnull(estates.B_CLOSEDOWNTOWN,0) as est_CloseDowntown,
> isnull(estates.B_QUIETAREA,0) as est_QuietArea,
> isnull(estates.B_CLOSENATURE,0) as est_CloseNature,
> isnull(estates.B_SEAVIEW,0) as est_SeaView,
> isnull(estates.B_NOPETS,0) as est_NoPets,
> isnull(estates.B_NOSMOKERS,0) as est_NoSmokers,
> isnull(estates.B_NOKIDS,0) as est_NoKids,
> isnull(estates.B_ONLYWEEKDAYS,0) as est_OnlyW
> isnull(estates.B_MAN,0) as est_Man,
> isnull(estates.B_WOMAN,0) as est_Woman,
> Estates.S_RentAdvance as est_RentAdvance,
> Estates.L_Rent as est_Rent,
> isnull(Estates.B_Permit,0) as est_Permit,
> Estates.S_Landlord as est_Landlord,
> Estates.S_Landl_Phone as est_LandlordPhone,
> Convert(nvarchar(6), Estates.Dat_From, 12) as est_DatFrom,
> Convert(nvarchar(6), Estates.Dat_Until, 12) as est_DatTo,
> isnull(Estates.B_MaybeLonger,0) as est_MaybeLonger,
> Estates.S_Description as est_Description,
> isnull(estates.N_STATE,0) as est_State,
> isnull(Estates.B_Printed,0) as est_Printed,
> isnull(estates.B_EXCLUSIVE,0) as est_Exclusive,
> Estates.L_RegisteredID as est_RegisteredID,
> Estates.Dat_Registered as est_DatRegistered,
> Estates.L_UpdatedID as est_UpdatedID,
> Estates.Dat_Updated as est_DatUpdated,
> Estates.Dat_Commit_Printed as est_DatCommitPrinted,
> Estates.S_TypeBekr as est_TypeCommit,
> Estates.S_Comments as est_Comments,
> Estates.L_AuthorizedID as est_AuthorizedID,
> Convert(nvarchar(10), Estates.Dat_Show, 120) as est_DatShow,
> estates.L_AgentID as est_AgentID
>FROM
> estates
> left join multimedia on multimedia.l_estateid=estates.l_estateid and
>isnull(N_INDEX,1)=1
> left join member with (nolock) on estates.l_memberid=member.l_memberid
>WHERE
> isnull(estates.B_FIRSTHAND,0) = 0 AND
> isnull(dat_show,getdate()) <= getdate() AND
> (ISNULL(MULTIMEDIA.N_INDEX,0) = 0 AND
> ISNULL(MULTIMEDIA.N_TYPE,0) = 0) AND
> ((@.RoomsMin = -1) OR (estates.D_ROOMS >= @.RoomsMin)) AND
> ((@.RoomsMax = -1) OR (estates.D_ROOMS <= @.RoomsMax)) AND
> ((@.RentMin = -1) OR (estates.L_RENT >= @.RentMin)) AND
> ((@.RentMax = -1) OR (estates.L_RENT <= @.RentMax)) AND
> ((@.SizeMin = -1) OR (estates.D_AREA >= @.SizeMin)) AND
> ((@.SizeMax = -1) OR (estates.D_AREA <= @.SizeMax)) AND
> ((@.PeriodMin = -1) OR ((case when estates.dat_until is null then
>10000 else datediff(dd, isnull(case when dat_from<getdate() then
>getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
>end)>= @.PeriodMin)) AND
> ((@.PeriodMax = -1) OR ((case when estates.dat_until is null then
>10000 else datediff(dd, isnull(case when dat_from<getdate() then
>getdate() else dat_from end,getdate()),isnull(dat_until,'2999010
1'))
>end)<= @.PeriodMax)) AND
> ((@.EstateType = -1) OR (estates.N_TYPE = @.EstateType)) AND
> (((@.ZipArea=1) AND( estates.l_zipcode BETWEEN 10000 AND 19999 OR
> estates.l_zipcode BETWEEN 76100 AND 76295))OR
> ((@.ZipArea=2) AND( estates.l_zipcode BETWEEN 40001 AND 54999 OR
> estates.l_zipcode BETWEEN 66010 AND 66899))OR
> ((@.ZipArea=3) AND( estates.l_zipcode BETWEEN 20001 AND 29799))OR
> ((@.ZipArea=4) AND( estates.l_zipcode BETWEEN 30000 AND 39999 OR
> estates.l_zipcode BETWEEN 55000 AND 66090 OR
> estates.l_zipcode BETWEEN 66900 AND 76099 OR
> estates.l_zipcode BETWEEN 76296 AND 99999))OR
> ((@.ZipArea=5) AND( estates.l_zipcode BETWEEN 11100 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=6) AND( estates.l_zipcode BETWEEN 12000 AND 12999 OR
> estates.l_zipcode BETWEEN 11743 AND 11743 OR
> estates.l_zipcode BETWEEN 11760 AND 11799))OR
> ((@.ZipArea=7) AND( estates.l_zipcode BETWEEN 14100 AND 14799 OR
> estates.l_zipcode BETWEEN 13500 AND 13699 OR
> estates.l_zipcode BETWEEN 13000 AND 13099))OR
> ((@.ZipArea=8) AND( estates.l_zipcode BETWEEN 13100 AND 13499))OR
> ((@.ZipArea=9) AND( estates.l_zipcode BETWEEN 16200 AND 16899))OR
> ((@.ZipArea=10)AND( estates.l_zipcode BETWEEN 16900 AND 17499))OR
> ((@.ZipArea=11)AND( estates.l_zipcode BETWEEN 18200 AND 19799))OR
> ((@.ZipArea=12)AND( estates.l_zipcode BETWEEN 11700 AND 11742 OR
> estates.l_zipcode BETWEEN 11744 AND 11759 OR
> estates.l_zipcode BETWEEN 11600 AND 11899))OR
> ((@.ZipArea=13)AND( estates.l_zipcode BETWEEN 15100 AND 15299))OR
> ((@.ZipArea=14)AND( estates.l_zipcode BETWEEN 16700 AND 16899))OR
> ((@.ZipArea=15)AND( estates.l_zipcode BETWEEN 16900 AND 17199))OR
> ((@.ZipArea=16)AND( estates.l_zipcode BETWEEN 17200 AND 17499))OR
> ((@.ZipArea=17)AND( estates.l_zipcode BETWEEN 17500 AND 17799))OR
> ((@.ZipArea=18)AND( estates.l_zipcode BETWEEN 19100 AND 19299))OR
> ((@.ZipArea=19)AND( estates.l_zipcode BETWEEN 76100 AND 76299))) AND
> ((@.State = -1)OR (estates.N_STATE = @.State)) AND
> ((@.Furnished = '-1')OR (estates.S_FURNITURES != @.Furnished)) AND
> ((@.PublDaysMax = -1)OR (DATEDIFF(dd, estates.DAT_SHOW,
>getdate())<=@.PublDaysMax)) AND
> ((@.PrivateCompany = -1)OR (estates.B_ONLYCOMPANIES =
>@.PrivateCompany)) AND
> ((@.Exclusive = -1)OR (estates.B_EXCLUSIVE = @.Exclusive)) AND
> ((@.Street = '')OR (UPPER(estates.S_ADDRESS) LIKE
>(UPPER(@.Street)+'%')))
>ORDER BY
> CASE WHEN @.Sort = 'est_Area' THEN estates.S_AREA ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatShow' THEN estates.dat_Show ELSE NULL END
>DESC,
> CASE WHEN @.Sort = 'est_State' THEN estates.n_State ELSE NULL END,
> CASE WHEN @.Sort = 'est_Type' THEN estates.n_Type ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rooms' THEN estates.d_Rooms ELSE NULL END,
> CASE WHEN @.Sort = 'est_Size' THEN estates.d_Area ELSE NULL END,
> CASE WHEN @.Sort = 'est_Rent' THEN estates.l_Rent ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatFrom' THEN estates.dat_From ELSE NULL END,
> CASE WHEN @.Sort = 'est_DatTo' THEN estates.dat_until ELSE NULL END
>
>|||As an example, all the zip code logic could be replaced with something like:
inner join ZipCodeAreas on ESTATES.L_ZIPCODE = ZipCodeAreas.zipcode
and ZipCodeAreas.ZipArea = @.ZipArea
If you use default parameters of -1 for min and 10000 (or 999999999) for
max
AND ((@.RoomsMin = -1)
OR (ESTATES.D_ROOMS >= @.RoomsMin))
AND ((@.RoomsMax = -1)
OR (ESTATES.D_ROOMS <= @.RoomsMax))
AND ((@.RentMin = -1)
OR (ESTATES.L_RENT >= @.RentMin))
AND ((@.RentMax = -1)
OR (ESTATES.L_RENT <= @.RentMax))
AND ((@.SizeMin = -1)
OR (ESTATES.D_AREA >= @.SizeMin))
AND ((@.SizeMax = -1)
OR (ESTATES.D_AREA <= @.SizeMax))
AND ((@.EstateType = -1)
OR (ESTATES.N_TYPE = @.EstateType))
can be rewritten to:
AND ESTATES.D_ROOMS >= @.RoomsMin
AND ESTATES.D_ROOMS <= @.RoomsMax
AND ESTATES.L_RENT >= @.RentMin
AND ESTATES.L_RENT <= @.RentMax
AND ESTATES.D_AREA >= @.SizeMin
AND ESTATES.D_AREA <= @.SizeMax
and if you you use date ranges
AND ((@.PeriodMin = -1)
OR ((CASE
WHEN ESTATES.DAT_UNTIL IS NULL THEN 10000
ELSE DATEDIFF(DD,ISNULL(CASE
WHEN DAT_FROM < GETDATE()
THEN GETDATE()
ELSE DAT_FROM
END,GETDATE()),ISNULL(DAT_UNTIL,'2999010
1'))
END) >= @.PeriodMin))
AND ((@.PeriodMax = -1)
OR ((CASE
WHEN ESTATES.DAT_UNTIL IS NULL THEN 10000
ELSE DATEDIFF(DD,ISNULL(CASE
WHEN DAT_FROM < GETDATE()
THEN GETDATE()
ELSE DAT_FROM
END,GETDATE()),ISNULL(DAT_UNTIL,'2999010
1'))
END) <= @.PeriodMax))
might be rewritten to:
DAT_FROM >= @.DateMin
DAT_UNTIL <= @.DateMax
Honestly, I'm not sure about the last one as I don't know exactly what date
logic you need regarding nulls and dates <= today or >= today, but you
should be able to simplify it. IF you dont allow nulls in these fields and
you use '29990101' for dat_until instead of a null it may make your life
easier.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1144164113.587997.300040@.z34g2000cwc.googlegroups.com...
> tonicvodka wrote:
> Here are some suggestions.
> 1. Get rid of all the ORs. For example if you set a default value for
> @.roomsmax of 10000 instead of -1 you should be able to just D_ROOMS
> BETWEEN @.roomsmin AND @.roomsmax. Same for the other variables.
> 2. Create a table of zipcodes to join with instead of using CASE
> expressions.
> 3. Pass in date From and To ranges instead of doing the date arithmetic
> in the query. You are presently forcing those DATEDIFFs to be performed
> for every row.
> 4. Make some columns non-nullable to eliminate the ISNULLs.
> The basic idea is to get expressions in your WHERE clause that are
> sargable - that means columns being compared to constants or to other
> columns without complex expressions on the columns themselves. Once
> you've achieved that you can take best advantage of any indexes you
> create.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Thanks a million for all the suggestions!!!
I'm a bit unsure regarding the table ZipArea; would the best design
just be making it two columns? One for ZipArea and one for ZipCode? It
would get extremly tall then, seeing ZipArea 1's span is ZipCodes 10000
to19999 and 76100 to 76295.|||Thanks a million for all the suggestions!!!
They'll definitely come to use, I won't be able to set default
values to the null-columns right now though since it'll screw up the
running pages, but that's high priority.
I'm a bit unsure regarding the table ZipArea; would the best design
just be making it two columns? One for ZipArea and one for ZipCode? It
would get extremly tall then, being ZipArea 1's span is ZipCodes 10000
to 19999 and 76100 to 76295.
Thanks again for the help!|||tonicvodka (tonicvodka@.hotmail.com) writes:
> They'll definitely come to use, I won't be able to set default
> values to the null-columns right now though since it'll screw up the
> running pages, but that's high priority.
> I'm a bit unsure regarding the table ZipArea; would the best design
> just be making it two columns? One for ZipArea and one for ZipCode? It
> would get extremly tall then, being ZipArea 1's span is ZipCodes 10000
> to 19999 and 76100 to 76295.
Yes, I would make it a two-column table, so with ZipCode as key and
ZipArea as output. Of course, that beast would be a nightmare to maintain,
so I might consider another table for input that has ranges, and which
feeds the first table through the trigger. But I would try to avoid
that range table in the query, as it's probably not good for performance.
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|||Then 2-column it is, thanks!|||Apart of removing the allow-null-columns I've implemented all of the
above suggestions except the date range and the difference is big.
Earlier response times on about 1.2 sec now lie on 0.3 sec when same
data is posted, excellent.
The date range I think is difficult to do anything about, since it's a
range the customer is requesting, e.g. I'm looking to rent an apartment
for the duration of at least 3 monthes and the most 6 monthes, while
the table holds data from and to which date the apartment is available.
Any sudden brainwave is welcome! Thanks all.|||If a customer is looking for a duration from 3 to 6 months, what is the
start of that duration?
Can you associate the duration with a specific date relative to today?
i.e. Customer one is looking for an apartment for 3 to 6 months, starting
today. If today happens to be 2006-01-01 then the customer wants the
apartment no later than today, at least until 2006-03-31 and no time after
2006-05-31. Now, your business rules will be different, but the same
general approach should work, no?
"tonicvodka" <tonicvodka@.hotmail.com> wrote in message
news:1144325221.351430.293750@.g10g2000cwb.googlegroups.com...
> Apart of removing the allow-null-columns I've implemented all of the
> above suggestions except the date range and the difference is big.
> Earlier response times on about 1.2 sec now lie on 0.3 sec when same
> data is posted, excellent.
> The date range I think is difficult to do anything about, since it's a
> range the customer is requesting, e.g. I'm looking to rent an apartment
> for the duration of at least 3 monthes and the most 6 monthes, while
> the table holds data from and to which date the apartment is available.
> Any sudden brainwave is welcome! Thanks all.
>
Sunday, March 25, 2012
Can't attach database
HI all, I'm trying to move a mdf from a dev machine onto a production server and when I try to re-attach I keep getting the following error " ...the mdf file is not a primary database file" ...and it won't attach.
It's SS2005, does anyone know why this error occurs.
Thanks
How did you detach the original database?|||Thanks for the prompt Euan, I just found out the database wasn't detached properly, they just burned it onto a CD directly!..hopefully the correctly detached one will be OK.
Can't attach database
HI all, I'm trying to move a mdf from a dev machine onto a production server and when I try to re-attach I keep getting the following error " ...the mdf file is not a primary database file" ...and it won't attach.
It's SS2005, does anyone know why this error occurs.
Thanks
How did you detach the original database?|||Thanks for the prompt Euan, I just found out the database wasn't detached properly, they just burned it onto a CD directly!..hopefully the correctly detached one will be OK.
can't alter column to NOT NULL
create table foo (x nvarchar(128))
create unique index ix_foo on foo (x)
alter table foo alter column x nvarchar(128) not null
I get this error:
Server: Msg 5074, Level 16, State 8, Line 1
The index 'ix_foo' is dependent on column 'x'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN x failed because one or more objects access this
column.
Looking in the documentation it says "The altered column cannot be...Used in
an index, unless the column is a varchar, nvarchar, or varbinary data type,
the data type is not changed, and the new size is equal to or larger than
the old size."
Since the column is nvarchar, the data type is not changed, and the new size
is equal to the old size, this should be allowed.
Is the documentation just wrong or have I misunderstood something?
AndyAndy Fish wrote:
> Since the column is nvarchar, the data type is not changed, and the
> new size is equal to the old size, this should be allowed.
> Is the documentation just wrong or have I misunderstood something?
You need to drop the index, alter the column and then recreate the
index, that's the only way.
HTH,
Stijn Verrept.|||Disable the foreign key constraints / drop them. Alter the table (make
sure there are no NULL values in there). Activate recreate the foreign
key relationship.
HTH, Jens Suessmeyer.sql
Thursday, March 22, 2012
Can't add a sibling or child to a member in a dimension
Following the Foodmart tutorial, I get to a point where I create a parent-child dimension and I browse data. Then I have to add a sibling or a child by right-clicking a member and selecting "New member" (all in the Dimension Editor). However, nothing happens on my right-clicking, no context menu at all. Could it be a versioning issue? I'm using SQL Server 2000 Standard edition. I already found out I can't use the writeback feature in this version...
Thanks and happy holidays!
Ale
Hi Ale,
I think that you need Enterprise Edition, since the right-click menu is only available after the dimension's Write-enabled property is set to True:
http://msdn2.microsoft.com/en-us/library/aa198057(SQL.80).aspx
>>
Analysis Services
Write-Enabled Dimensions
...
Note This feature is available only if you install Analysis Services for Microsoft? SQL Server? 2000 Enterprise Edition.
...
To write-enable a dimension, set its Write-enabled property to True in the properties pane of Dimension Editor (if the dimension is shared) or Cube Editor (if the dimension is private).
>>
|||That makes a lot of sense, thanks Deepak!Can't achieve backups with SQL Server 7.00.1063 on Win2k
On a brand new powerful server, I've had this intermittent problem with
the following message for over a week, when I tried to make a manual or
scheduled backup. I get this message instantly when I start the backup as a
pop-up in Enterprise Manager and in the SQL Server logs.
BackupMedium::ReportIoError: write failure on backup device
'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
1453(Insufficient quota to complete the requested service.).
The backup to be made is on a small 1.5 gb database. On the array, there's
about 189 Gb of free space on the hard drive. It behaves like a hardware
problem
Hi
Check that disk quotas are not enabled for the destination drive at OS level.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pascal" wrote:
> Good morning,
> On a brand new powerful server, I've had this intermittent problem with
> the following message for over a week, when I tried to make a manual or
> scheduled backup. I get this message instantly when I start the backup as a
> pop-up in Enterprise Manager and in the SQL Server logs.
> BackupMedium::ReportIoError: write failure on backup device
> 'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
> 1453(Insufficient quota to complete the requested service.).
> The backup to be made is on a small 1.5 gb database. On the array, there's
> about 189 Gb of free space on the hard drive. It behaves like a hardware
> problem
Can't achieve backups with SQL Server 7.00.1063 on Win2k
On a brand new powerful server, I've had this intermittent problem with
the following message for over a week, when I tried to make a manual or
scheduled backup. I get this message instantly when I start the backup as a
pop-up in Enterprise Manager and in the SQL Server logs.
BackupMedium::ReportIoError: write failure on backup device
'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
1453(Insufficient quota to complete the requested service.).
The backup to be made is on a small 1.5 gb database. On the array, there's
about 189 Gb of free space on the hard drive. It behaves like a hardware
problemHi
Check that disk quotas are not enabled for the destination drive at OS level.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pascal" wrote:
> Good morning,
> On a brand new powerful server, I've had this intermittent problem with
> the following message for over a week, when I tried to make a manual or
> scheduled backup. I get this message instantly when I start the backup as a
> pop-up in Enterprise Manager and in the SQL Server logs.
> BackupMedium::ReportIoError: write failure on backup device
> 'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
> 1453(Insufficient quota to complete the requested service.).
> The backup to be made is on a small 1.5 gb database. On the array, there's
> about 189 Gb of free space on the hard drive. It behaves like a hardware
> problem
Can't achieve backups with SQL Server 7.00.1063 on Win2k
On a brand new powerful server, I've had this intermittent problem with
the following message for over a week, when I tried to make a manual or
scheduled backup. I get this message instantly when I start the backup as a
pop-up in Enterprise Manager and in the SQL Server logs.
BackupMedium::ReportIoError: write failure on backup device
'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
1453(Insufficient quota to complete the requested service.).
The backup to be made is on a small 1.5 gb database. On the array, there's
about 189 Gb of free space on the hard drive. It behaves like a hardware
problemHi
Check that disk quotas are not enabled for the destination drive at OS level
.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Pascal" wrote:
> Good morning,
> On a brand new powerful server, I've had this intermittent problem with
> the following message for over a week, when I tried to make a manual or
> scheduled backup. I get this message instantly when I start the backup as
a
> pop-up in Enterprise Manager and in the SQL Server logs.
> BackupMedium::ReportIoError: write failure on backup device
> 'd:\MSSQL7\BACKUP\FmdtestPascal.BAK'. Operating system error
> 1453(Insufficient quota to complete the requested service.).
> The backup to be made is on a small 1.5 gb database. On the array, there'
s
> about 189 Gb of free space on the hard drive. It behaves like a hardware
> problemsql
Tuesday, March 20, 2012
Can't access OLEDB datasource
table either via an oledb connection or via a linked
server
I can enter the query in the rs/text box and run it
sucessfully. If I either goto edit the query via the
query tool or select the data option. Visual Studio
crashes.
This scenario reproduces the error
select * from link...table, however
this scenario works
select * from openquery(link,'select * from table')
I have downloaded sp1 for reporting services
thanks
DanAre you using the generic query designer, or are you using the graphical
query desinger?
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:b92401c479a8$2a760220$a601280a@.phx.gbl...
> The following problem is occuring when I try to access a
> table either via an oledb connection or via a linked
> server
> I can enter the query in the rs/text box and run it
> sucessfully. If I either goto edit the query via the
> query tool or select the data option. Visual Studio
> crashes.
> This scenario reproduces the error
> select * from link...table, however
> this scenario works
> select * from openquery(link,'select * from table')
> I have downloaded sp1 for reporting services
> thanks
> Dan|||Thank you for the response, I can type in a query in the
generic designed, however if I select edit which is
supposed to launch the graphic tool, it crashes. This is
the same behavior whether I use the oledb interface or
try to access the same table via a linked-server
Regards
>--Original Message--
>Are you using the generic query designer, or are you
using the graphical
>query desinger?
>-Lukasz
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"Dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to access
a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>
>.
>|||Unfortunately, the graphical query editor is a component of Visual Studio
which we reuse. If you send me your query, I can file a bug on their side.
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"dan" <anonymous@.discussions.microsoft.com> wrote in message
news:befe01c479d1$f409b860$a501280a@.phx.gbl...
> Thank you for the response, I can type in a query in the
> generic designed, however if I select edit which is
> supposed to launch the graphic tool, it crashes. This is
> the same behavior whether I use the oledb interface or
> try to access the same table via a linked-server
> Regards
>>--Original Message--
>>Are you using the generic query designer, or are you
> using the graphical
>>query desinger?
>>-Lukasz
>>
>>--
>>This posting is provided "AS IS" with no warranties, and
> confers no rights.
>>
>>"Dan" <anonymous@.discussions.microsoft.com> wrote in
> message
>>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to access
> a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>>
>>.|||Thanks
Here is the example: SELECT * FROM NSXLINK...KNA1
NSXLINK is an OLEDB compliant linked server kna1 is the
table. If you select edit you get the error.
If however you submit the following query
SELECT * FROM OPENQUERY(NSXLINK,'SELECT * FROM NSXLINK')
it works. The same problem will appear if you use the
OLEDB provider directly, without the linked server
If you want I can get the error from the program that is
generated
thanks again
dan
>--Original Message--
>Unfortunately, the graphical query editor is a component
of Visual Studio
>which we reuse. If you send me your query, I can file
a bug on their side.
>-Lukasz
>
>--
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>
>"dan" <anonymous@.discussions.microsoft.com> wrote in
message
>news:befe01c479d1$f409b860$a501280a@.phx.gbl...
>> Thank you for the response, I can type in a query in
the
>> generic designed, however if I select edit which is
>> supposed to launch the graphic tool, it crashes. This
is
>> the same behavior whether I use the oledb interface or
>> try to access the same table via a linked-server
>> Regards
>>--Original Message--
>>Are you using the generic query designer, or are you
>> using the graphical
>>query desinger?
>>-Lukasz
>>
>>--
>>This posting is provided "AS IS" with no warranties,
and
>> confers no rights.
>>
>>"Dan" <anonymous@.discussions.microsoft.com> wrote in
>> message
>>news:b92401c479a8$2a760220$a601280a@.phx.gbl...
>> The following problem is occuring when I try to
access
>> a
>> table either via an oledb connection or via a linked
>> server
>> I can enter the query in the rs/text box and run it
>> sucessfully. If I either goto edit the query via the
>> query tool or select the data option. Visual Studio
>> crashes.
>> This scenario reproduces the error
>> select * from link...table, however
>> this scenario works
>> select * from openquery(link,'select * from table')
>> I have downloaded sp1 for reporting services
>> thanks
>> Dan
>>
>>.
>
>.
>
Monday, March 19, 2012
Cannot view SQL Server logs
the SQL Server logs from my client machine, but receive the following:
TITLE: Microsoft SQL Server Management Studio
--
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or
batch. (Microsoft.SqlServer.ConnectionInfo)
--
A severe error occurred on the current command. The results, if any,
should be discarded. (Microsoft SQL Server, Error: 0)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=0&LinkId=20476
--
BUTTONS:
OK
--
Everything else in the Management Studio works fine. Can anyone
interpret this?I can't interpret or solve, but I can confirm that it also happens on our
clusters.
> Everything else in the Management Studio works fine. Can anyone
> interpret this?
>|||Interesting...I found out that when I was trying to view the logs, for
some reason it was trying to direct me to the virtual servername used
by the Data Group of the cluster. I don't know why it would do that.
I created an alias for the data group name, and pointed it to the ip
address of the sql server instance, and it connects properly now.
Not sure if I explained this right, I'm kinda new to clusters, but
hopefully this will help you.
Aaron Bertrand [SQL Server MVP] wrote:
> I can't interpret or solve, but I can confirm that it also happens on our
> clusters.
>
>
> > Everything else in the Management Studio works fine. Can anyone
> > interpret this?
> >
Sunday, March 11, 2012
Cannot view actual report
Hi,
I am using the reportviewer control the following way
<%@. Page Language="C#" AutoEventWireup="true" CodeFile="RenderReport.aspx.cs" Inherits="pages_RenderReport" Title="View Report"%>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<html runat="server">
<head runat=server></head>
<body>
<form runat=server>
<table width="100%" cellpadding=0 cellspacing=0 border=0>
<tr>
<td valign="top" id="report" width="100%" style="height: 104%">
<rsweb:ReportViewer ID="reportViewer" runat="server" Width=100% SizeToReportContent=false BorderWidth="1pt" ExportContentDisposition="AlwaysInline" AsyncRendering="False" Height="600px" Font-Size=Small ShowParameterPrompts=true ShowPageNavigationControls=true ProcessingMode="Remote">
</rsweb:ReportViewer>
</td>
</tr>
</table>
</form>
</body>
</html>
I cannot see the report at all?..All i see is the parameters area.
thanks.
It doesn't appear that you specified a report to render. The Report Viewer control won't render anything unless it's given a report.
-Albert
|||Actually i had set the report in the code. I should have included that code in this forum. I however figured out the problem.
Thanks.
Cannot view actual report
Hi,
I am using the reportviewer control the following way
<%@. Page Language="C#" AutoEventWireup="true" CodeFile="RenderReport.aspx.cs" Inherits="pages_RenderReport" Title="View Report"%>
<%@. Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<html runat="server">
<head runat=server></head>
<body>
<form runat=server>
<table width="100%" cellpadding=0 cellspacing=0 border=0>
<tr>
<td valign="top" id="report" width="100%" style="height: 104%">
<rsweb:ReportViewer ID="reportViewer" runat="server" Width=100% SizeToReportContent=false BorderWidth="1pt" ExportContentDisposition="AlwaysInline" AsyncRendering="False" Height="600px" Font-Size=Small ShowParameterPrompts=true ShowPageNavigationControls=true ProcessingMode="Remote">
</rsweb:ReportViewer>
</td>
</tr>
</table>
</form>
</body>
</html>
I cannot see the report at all?..All i see is the parameters area.
thanks.
It doesn't appear that you specified a report to render. The Report Viewer control won't render anything unless it's given a report.
-Albert
|||Actually i had set the report in the code. I should have included that code in this forum. I however figured out the problem.
Thanks.
Cannot use the reserved user or role name db_datareader.
SELECT permission denied on object 'Users', database 'Users', owner 'dbo'.
SoI tried to grand this select command in MS Web Data Administration, butit doesnt work. When I try to grand db_datareader role to dbo, I getthe following error
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the reserved user or role name 'db_datareader'.
Does someone have an idea where could be a problem?
You don't need to be granting db_datareader to dbo, you need to beputting the user that you're accessing the database with in that role,or at least giving it SELECT permissions on that table, or better yet,giving it EXECUTE permissions on a stored proc that selects from thattable.
Cannot use the OUTPUT option when passing a constant Stored Proced
I get the following error:
Microsoft SQL Native Client
Cannot use the OUTPUT option when passing a constant to a Stored Procedure
In SQL 2005 SP2, I check Profiler, here are my findings:
UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user exec
this sp as "dbo"
UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
exec this sp as "DOMAIN\UserName"
If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
that user exec this sp as "dbo".
What is going on? This started after updating SQL 2005 to SP2.
Thanks
StephenCan you tell us more about how exactly you are calling this sp? Can you
show the actual code?
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>I am having a very difficult problem.
> I get the following error:
> Microsoft SQL Native Client
> Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> In SQL 2005 SP2, I check Profiler, here are my findings:
> UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec
> this sp as "dbo"
> UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec this sp as "DOMAIN\UserName"
> If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> that user exec this sp as "dbo".
> What is going on? This started after updating SQL 2005 to SP2.
> Thanks
> Stephen
>|||> Cannot use the OUTPUT option when passing a constant to a Stored Procedure
I can reproduce this error message with the following test script:
EXEC dbo.test 1 OUT
So it would seem that your application is doing something similar; a
constant cannot be passed as an output parameter.
> In SQL 2005 SP2, I check Profiler, here are my findings:
> UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec
> this sp as "dbo"
> UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec this sp as "DOMAIN\UserName"
> If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> that user exec this sp as "dbo".
> What is going on? This started after updating SQL 2005 to SP2.
I don't see how this information is related to the subject of this post but
this is expected behavior. Administrators are SQL Server sysadmin role
members by default and are therefore the "dbo" user in all databases.
Non-sysadmin role members access databases under their normal database user
security context.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>I am having a very difficult problem.
> I get the following error:
> Microsoft SQL Native Client
> Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> In SQL 2005 SP2, I check Profiler, here are my findings:
> UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec
> this sp as "dbo"
> UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> exec this sp as "DOMAIN\UserName"
> If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> that user exec this sp as "dbo".
> What is going on? This started after updating SQL 2005 to SP2.
> Thanks
> Stephen
>|||Yes, I will post it in about 2 hours.
Thanks
Stephen
"Andrew J. Kelly" wrote:
> Can you tell us more about how exactly you are calling this sp? Can you
> show the actual code?
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
> >I am having a very difficult problem.
> > I get the following error:
> > Microsoft SQL Native Client
> > Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> >
> > In SQL 2005 SP2, I check Profiler, here are my findings:
> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec
> > this sp as "dbo"
> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec this sp as "DOMAIN\UserName"
> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> > that user exec this sp as "dbo".
> > What is going on? This started after updating SQL 2005 to SP2.
> > Thanks
> > Stephen
> >
>|||Dan, I am sorry but I don't understand your reply.
I listed the information because that is all I know about how I can get this
sp to work... by giving my ADUsers "Domain Admin" rights.
So is it an sp issue or a permission problem? Sorry for not understanding
your reply.
Here is the sp:
USE [Database]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[cf_data_info]
@.table_name varchar(128),
@.column_name varchar(128),
@.data_type varchar(128) OUTPUT,
@.colprecision integer OUTPUT,
@.colscale integer OUTPUT
AS
SELECT @.data_type = systypes.name,
@.colprecision = syscolumns.prec,
@.colscale = syscolumns.scale
FROM sysobjects
LEFT OUTER JOIN syscolumns
ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN systypes
ON systypes.xusertype = syscolumns.xusertype
INNER JOIN sysusers
ON sysusers.uid = sysobjects.uid
AND Upper(sysusers.name) = 'DBO'
WHERE sysobjects.name = @.table_name
AND syscolumns.name = @.column_name
Everything worked fine before upgrading to SQL 2005 SP2.
Thanks
Stephen
"Dan Guzman" wrote:
> > Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> I can reproduce this error message with the following test script:
> EXEC dbo.test 1 OUT
> So it would seem that your application is doing something similar; a
> constant cannot be passed as an output parameter.
> > In SQL 2005 SP2, I check Profiler, here are my findings:
> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec
> > this sp as "dbo"
> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec this sp as "DOMAIN\UserName"
> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> > that user exec this sp as "dbo".
> > What is going on? This started after updating SQL 2005 to SP2.
> I don't see how this information is related to the subject of this post but
> this is expected behavior. Administrators are SQL Server sysadmin role
> members by default and are therefore the "dbo" user in all databases.
> Non-sysadmin role members access databases under their normal database user
> security context.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
> >I am having a very difficult problem.
> > I get the following error:
> > Microsoft SQL Native Client
> > Cannot use the OUTPUT option when passing a constant to a Stored Procedure
> >
> > In SQL 2005 SP2, I check Profiler, here are my findings:
> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec
> > this sp as "dbo"
> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that user
> > exec this sp as "DOMAIN\UserName"
> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler reports
> > that user exec this sp as "dbo".
> > What is going on? This started after updating SQL 2005 to SP2.
> > Thanks
> > Stephen
> >
>|||> So is it an sp issue or a permission problem? Sorry for not understanding
> your reply.
I think you are getting this error because the actual call to this procedure
is not formed correctly. For example, the code below attempts to pass
literals as OUTPUT parameters:
DECLARE
@.data_type sysname,
@.colprecision int,
@.colscale int
EXEC [dbo].[cf_data_info]
@.table_name = 'MyTable',
@.column_name = 'MyColumn',
@.data_type = 'test' OUTPUT,
@.colprecision = 4 OUTPUT,
@.colscale = 2 OUTPUT
I can't explain how permissions might come into play here. Does the
profiler trace show the execute statement?
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:706C9BEF-9A37-4EBA-9FE2-7B58B5B781DA@.microsoft.com...
> Dan, I am sorry but I don't understand your reply.
> I listed the information because that is all I know about how I can get
> this
> sp to work... by giving my ADUsers "Domain Admin" rights.
> So is it an sp issue or a permission problem? Sorry for not understanding
> your reply.
> Here is the sp:
> USE [Database]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[cf_data_info]
> @.table_name varchar(128),
> @.column_name varchar(128),
> @.data_type varchar(128) OUTPUT,
> @.colprecision integer OUTPUT,
> @.colscale integer OUTPUT
> AS
> SELECT @.data_type = systypes.name,
> @.colprecision = syscolumns.prec,
> @.colscale = syscolumns.scale
> FROM sysobjects
> LEFT OUTER JOIN syscolumns
> ON syscolumns.id = sysobjects.id
> LEFT OUTER JOIN systypes
> ON systypes.xusertype = syscolumns.xusertype
> INNER JOIN sysusers
> ON sysusers.uid = sysobjects.uid
> AND Upper(sysusers.name) = 'DBO'
> WHERE sysobjects.name = @.table_name
> AND syscolumns.name = @.column_name
> Everything worked fine before upgrading to SQL 2005 SP2.
> Thanks
> Stephen
> "Dan Guzman" wrote:
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> I can reproduce this error message with the following test script:
>> EXEC dbo.test 1 OUT
>> So it would seem that your application is doing something similar; a
>> constant cannot be passed as an output parameter.
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> I don't see how this information is related to the subject of this post
>> but
>> this is expected behavior. Administrators are SQL Server sysadmin role
>> members by default and are therefore the "dbo" user in all databases.
>> Non-sysadmin role members access databases under their normal database
>> user
>> security context.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
>> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>> >I am having a very difficult problem.
>> > I get the following error:
>> > Microsoft SQL Native Client
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> >
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> > Thanks
>> > Stephen
>> >|||The issue is that the reference to the output needs to be declared in the
execution.
The error is in your execute call "EXEC dbo.test 1 OUT"
Declare @.InAndOutParammeterName
Select @.InAndOutParammeterName=1
EXEC dbo.test @.InAndOutParammeterName OUT
@.InAndOutParammeterName now holds the reference in memory for the returned
value to reside.
"Stephen" <Stephen@.discussions.microsoft.com> wrote in message
news:706C9BEF-9A37-4EBA-9FE2-7B58B5B781DA@.microsoft.com...
> Dan, I am sorry but I don't understand your reply.
> I listed the information because that is all I know about how I can get
> this
> sp to work... by giving my ADUsers "Domain Admin" rights.
> So is it an sp issue or a permission problem? Sorry for not understanding
> your reply.
> Here is the sp:
> USE [Database]
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE [dbo].[cf_data_info]
> @.table_name varchar(128),
> @.column_name varchar(128),
> @.data_type varchar(128) OUTPUT,
> @.colprecision integer OUTPUT,
> @.colscale integer OUTPUT
> AS
> SELECT @.data_type = systypes.name,
> @.colprecision = syscolumns.prec,
> @.colscale = syscolumns.scale
> FROM sysobjects
> LEFT OUTER JOIN syscolumns
> ON syscolumns.id = sysobjects.id
> LEFT OUTER JOIN systypes
> ON systypes.xusertype = syscolumns.xusertype
> INNER JOIN sysusers
> ON sysusers.uid = sysobjects.uid
> AND Upper(sysusers.name) = 'DBO'
> WHERE sysobjects.name = @.table_name
> AND syscolumns.name = @.column_name
> Everything worked fine before upgrading to SQL 2005 SP2.
> Thanks
> Stephen
> "Dan Guzman" wrote:
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> I can reproduce this error message with the following test script:
>> EXEC dbo.test 1 OUT
>> So it would seem that your application is doing something similar; a
>> constant cannot be passed as an output parameter.
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> I don't see how this information is related to the subject of this post
>> but
>> this is expected behavior. Administrators are SQL Server sysadmin role
>> members by default and are therefore the "dbo" user in all databases.
>> Non-sysadmin role members access databases under their normal database
>> user
>> security context.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> http://weblogs.sqlteam.com/dang/
>> "Stephen" <Stephen@.discussions.microsoft.com> wrote in message
>> news:C234429C-44A8-4400-BF3A-CF792F7EBF2A@.microsoft.com...
>> >I am having a very difficult problem.
>> > I get the following error:
>> > Microsoft SQL Native Client
>> > Cannot use the OUTPUT option when passing a constant to a Stored
>> > Procedure
>> >
>> > In SQL 2005 SP2, I check Profiler, here are my findings:
>> > UserA (w/ Domain Admin rights in ADUC) - SQL Profiler reports that user
>> > exec
>> > this sp as "dbo"
>> > UserB (w/out Domain Admin rights in ADUC) - SQL Profiler reports that
>> > user
>> > exec this sp as "DOMAIN\UserName"
>> > If I change UserB to w/ Domain Admin rights in ADUC - SQL Profiler
>> > reports
>> > that user exec this sp as "dbo".
>> > What is going on? This started after updating SQL 2005 to SP2.
>> > Thanks
>> > Stephen
>> >
Cannot use subquery in UPDATE statement?
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> >, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:
>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
>>, >= or when the subquery is used as an expression."
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> > Hi there,
> >
> > I try to run the following update statement on MS SQL Server 2000 SP4
> >
> > UPDATE Site
> >
> > SET Active = 0
> >
> > WHERE
> > RID IN (SELECT site.RID AS site_rid
> > FROM Site INNER JOIN
> > ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> > Manager m ON ms.ManagerID = m.RID
> > WHERE (am.UserName = 'iwk1004') AND
> > (ms.isPrimaryUser = 1))
> >
> > Running this query I get the message "Subquery returned more than 1
> > value. This is not permitted when the subquery follows =, !=, <, <= ,
> >>, >= or when the subquery is used as an expression."
> >
> > Which is weird, because according to the documentation and examples I
> > inferred could use UPDATE with the use of IN in the condition and the
> > subquery returining multiple rows. (sub-query works fine BTW)
> >
> > Any idea what is going on?
> >
> > Regards,
> >
> > Iwan
> >
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ
Cannot use subquery in UPDATE statement?
I try to run the following update statement on MS SQL Server 2000 SP4
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Running this query I get the message "Subquery returned more than 1
value. This is not permitted when the subquery follows =, !=, <, <= ,
>, >= or when the subquery is used as an expression."
Which is weird, because according to the documentation and examples I
inferred could use UPDATE with the use of IN in the condition and the
subquery returining multiple rows. (sub-query works fine BTW)
Any idea what is going on?
Regards,
Iwaniwanvanderkleijn@.gmail.com wrote:
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
Your subquery returns multiple rows so change it to return some
aggregation of site.rid.
like
UPDATE Site
SET Active = 0
WHERE
RID IN (SELECT max(site.RID) AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1))
Regards
Amish Shah
http://shahamishm.tripod.com|||Not tested this at all - just an idea
UPDATE Site
SET Active = 0
FROM
(
SELECT site.RID AS site_rid
FROM Site INNER JOIN
ManagerSite ms ON site.RID = ms.ArtistSiteID INNER
JOIN
Manager m ON ms.ManagerID = m.RID
WHERE (am.UserName = 'iwk1004') AND
(ms.isPrimaryUser = 1
) as tRes
WHERE tRes.site_rid = site.RID
/* You could remove site table from the correlated subquery tRes all
together becuase it actually isn't needed and just join
tREs.ArtistSiteID = site.RID */
Or try your original subquery with EXISTS clause rather than IN|||I agree that the message makes no sense with the code shown, since
multiple values are expected after an IN.
This could be written with an EXISTS test using a correlated subquery,
rather than an IN.
UPDATE Site
SET Active = 0
WHERE EXISTS
(SELECT *
FROM ManagerSite ms
JOIN Manager m
ON ms.ManagerID = m.RID
WHERE site.RID = ms.ArtistSiteID
AND am.UserName = 'iwk1004'
AND ms.isPrimaryUser = 1)
Roy Harvey
Beacon Falls, CT
On 7 Aug 2006 03:07:06 -0700, "iwanvanderkleijn@.gmail.com"
<iwanvanderkleijn@.gmail.com> wrote:
>Hi there,
>I try to run the following update statement on MS SQL Server 2000 SP4
>UPDATE Site
>SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
>(ms.isPrimaryUser = 1))
>Running this query I get the message "Subquery returned more than 1
>value. This is not permitted when the subquery follows =, !=, <, <= ,
>Which is weird, because according to the documentation and examples I
>inferred could use UPDATE with the use of IN in the condition and the
>subquery returining multiple rows. (sub-query works fine BTW)
>Any idea what is going on?
>Regards,
>Iwan|||I'm confused about what the [am.] alias is referring to.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<iwanvanderkleijn@.gmail.com> wrote in message
news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
> Hi there,
> I try to run the following update statement on MS SQL Server 2000 SP4
> UPDATE Site
> SET Active = 0
> WHERE
> RID IN (SELECT site.RID AS site_rid
> FROM Site INNER JOIN
> ManagerSite ms ON site.RID = ms.ArtistSiteID INNER JOIN
> Manager m ON ms.ManagerID = m.RID
> WHERE (am.UserName = 'iwk1004') AND
> (ms.isPrimaryUser = 1))
> Running this query I get the message "Subquery returned more than 1
> value. This is not permitted when the subquery follows =, !=, <, <= ,
> Which is weird, because according to the documentation and examples I
> inferred could use UPDATE with the use of IN in the condition and the
> subquery returining multiple rows. (sub-query works fine BTW)
> Any idea what is going on?
> Regards,
> Iwan
>|||Arnie Rowland wrote:
[vbcol=seagreen]
> I'm confused about what the [am.] alias is referring to.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
> <iwanvanderkleijn@.gmail.com> wrote in message
> news:1154945225.949531.95440@.i42g2000cwa.googlegroups.com...
I misunderstand the prob.
Your query looks ok but am is not referencing any table
Regards
Amish Shah|||I think it is confused over what "Site" referance you are trying to
update because it is name twice. Try the following pattern (tables
from Northwind) where you alias the update table.
update o
set o.quantity = 1
from [Order Details] o
where o.OrderId in
(select [Order Details].OrderID
from [Order Details]
inner join dbo.Orders
on [Order Details].Orderid = Orders.OrderID )
JJ