Can't Call Stored Procedure in SqlFunction

There is a SqlFunction in my SQL Server Project:

[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@.ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@.ID"].Value = ID;*/

using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}

When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."

What does the error message mean? How could I correct it? Thanks for answering.

The problem is resolved. Just remove the default "SET NOCOUNT ON;" added by SQL 2005 while creating stored procedure, then the SqlFunction will work.|||

Does anyone know of another way to fix this? We have alot of stored procedures with "SET NOCOUNT ON" and I would really rather not have to make that change unless we have to.

Thanks,
JD

|||Hi JD,

Bleh... I can't see good way. :) You could make a normal connection instead of using the context connection, but you'll have to deploy your assembly as external-access.

Cheers,
-Isaac

Can't beat the dynamic sql

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_OnlyWDays,
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_OnlyWDays,
> 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_OnlyWDays,
> 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.
>

Cant attach database

I've created a database inside my ASP.net project, the database is in the App_Data folder as exepcted.

I can't run the aspnet_regsql until I have attached it using SQL Server Management Studio, but when I try to attach the database it doesn't let me navigate through my folders properly.

The database is in C:\Users\Wardie\Documents\FirstProject\App_Data but SQL SMS only lets me navigate to the folder C:\Users\Wardie and no further for some reason, meaning I can't attach the database and can't use the aspnet_regsql service to create the membership tables.

Does anyone know how to fix this?

I had this problem and discovered that SQL Server Management Studio doesnt navigate long directory paths off windows user directories like "My Documents". I ended up creating all my web projects in a sub directory off C:\ something like C:\My Websites\Web1\App_Data\MyDatabase.MDF will work fine. Sorry, but as far as I'm aware you'll have to move your project directory to a shorter path off the root directory.

|||

I guess i'll just have to do the same then, cheers smc.

A bit of a stupid thing though isn't that really?

|||

I agree. There's probably a logical reason... maybe its to do with virtual paths that are used for user "My Directory" folders not being supported. I.e. the "My Documnets" directory is actually something like "C:\Documents and Settings\UserName\My Documents"

Please mark as answered if I was helpful in someway to you.

cant add sql endpoint to my visual studio project. trying "add as web reference" does

i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.

create endpoint testEndpoint authorization [FINTAN-PC\Fintan]
state=STARTED
as http
(
path ='/sql',
authentication=(INTEGRATED),
ports = (CLEAR),
site='SERVER'
)
For SOAP
(
webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE='adventureworks',
NAMESPACE='http://thenamespace.org'
);
GO

the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?

I'm a little unclear on your mentioning of using Visual Studio to look for this SOAP endpoint. Are you referring to using UDDI?

Regarding "add as web reference" in Visual Studio, the method that has worked best for me is to type in the HTTP URL that points to the WSDL document.

In this scenario it will be: http://SERVER/sql?wsdl

This URL comes from:

site='SERVER'

path='/sql'

So, if the endpoint was configured with:

site='foobar'

path='/myOwnPath/subPath'

then the WSDL document URL will be http://foobar/myOwnPath/subPath?wsdl

One easy way to ensure the user account running Visual Studio has access to retrieve the WSDL document is to use a Web Browser (such as IE) and point to the WSDL document URL.

HTH,

Jimmy|||ok, tiny bit of progress made. when i use the http://sitename/path?wsdl , rather than getting a 404 error, i get a page cannot be displayed. this is in IE7. any ideas?

cant add sql endpoint to my visual studio project. trying "add as web reference" does

i have created a database endpoint that just retuns some data. i am trying to call this endpoint as a web service in visual studio by adding a web reference, but i cannot find my endpoint. it seems to just check iis for endpoints, but how do i make it look for my sql endpoint? here is the code for my endpoint.

create endpoint testEndpoint authorization [FINTAN-PC\Fintan]
state=STARTED
as http
(
path ='/sql',
authentication=(INTEGRATED),
ports = (CLEAR),
site='SERVER'
)
For SOAP
(
webmethod'ProvideSQLData'(name='adventureWorks.dbo.testEndpointProcedure'),
WSDL=DEFAULT,
SCHEMA=STANDARD,
DATABASE='adventureworks',
NAMESPACE='http://thenamespace.org'
);
GO

the account that visual studio is running under has been granted connect permissions on the endpoint. can anyone say where im going wrong here?

I'm a little unclear on your mentioning of using Visual Studio to look for this SOAP endpoint. Are you referring to using UDDI?

Regarding "add as web reference" in Visual Studio, the method that has worked best for me is to type in the HTTP URL that points to the WSDL document.

In this scenario it will be: http://SERVER/sql?wsdl

This URL comes from:

site='SERVER'

path='/sql'

So, if the endpoint was configured with:

site='foobar'

path='/myOwnPath/subPath'

then the WSDL document URL will be http://foobar/myOwnPath/subPath?wsdl

One easy way to ensure the user account running Visual Studio has access to retrieve the WSDL document is to use a Web Browser (such as IE) and point to the WSDL document URL.

HTH,

Jimmy|||ok, tiny bit of progress made. when i use the http://sitename/path?wsdl , rather than getting a 404 error, i get a page cannot be displayed. this is in IE7. any ideas?

Can't add ReportViewer to VS toolbox.

I went through all the steps described in sample project but I don't
see ReportViewer in the Toolbox after linking the .dll reference in
Customize Toolbox. What am I missing?Hi Alex,
You should select "Add Remove Items..." on the context menu when you right
click over the Toolbox screen. And then you can point to reportviewer.dll by
browsing it
"Alex Mirzayanov" wrote:
> I went through all the steps described in sample project but I don't
> see ReportViewer in the Toolbox after linking the .dll reference in
> Customize Toolbox. What am I missing?
>sql

can''t add new Stored Procedure item in Database project

I'm trying to create a CLR stored procedure and went ahead and created a Database Project. But when I click on New Item... it doesn't contain "Stored Procedure" as an item, just script items. How do I add this template? At first I was thinking well maybe it knows my SQL Server doesn't have CLR turned on, so I went ahead and turned that on but still doesn't show up. Any ideas?

Thanks,

Craig

You have probably chosen wrong project type. The project type to create SQLCLR "stuff" is under either C# or the VB node and it is called (IIRC) Database Project. What makes it a bit confuding is that there exists a top level Database Project as well (at the same level as C# and VB). The project type is for T-SQL scripts etc. I assume that is what you have chosen!

Niels

Can't Add New Report

In Visual Studio when I create a new Report Project, once it's created I go
into Solution Explorer, right click on the "Reports" folder and select "Add
New Report", and I get the following error:
Exception from HRESULT: 0x8013141A
I've re-installed Visual Studio, the .NET framework, and Reporting Services,
and after all that it still gives me the error.
Any suggestions?
Michael BodnarSome DLL is failing to load. The only sure way to diagnose the problem is
to attach a debugger and see what exceptions it's throwing.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Bodnar" <blah@.blah.com> wrote in message
news:eX63pJHWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> In Visual Studio when I create a new Report Project, once it's created I
go
> into Solution Explorer, right click on the "Reports" folder and select
"Add
> New Report", and I get the following error:
> Exception from HRESULT: 0x8013141A
> I've re-installed Visual Studio, the .NET framework, and Reporting
Services,
> and after all that it still gives me the error.
> Any suggestions?
> Michael Bodnar
>|||Can you dump out a list of DLLs that are loaded into the VS process when the
error occurs? You can either use a tool like procexp.exe
(http://www.sysinternals.com/ntw2k/freeware/procexp.shtml) or attach another
instance of VS as a debugger. Using VS, display the Modules window, select
all rows, copy, and paste into a text file.
--
Albert Yen
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Michael Bodnar" <blah@.blah.com> wrote in message
news:eX63pJHWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> In Visual Studio when I create a new Report Project, once it's created I
go
> into Solution Explorer, right click on the "Reports" folder and select
"Add
> New Report", and I get the following error:
> Exception from HRESULT: 0x8013141A
> I've re-installed Visual Studio, the .NET framework, and Reporting
Services,
> and after all that it still gives me the error.
> Any suggestions?
> Michael Bodnar
>|||The whole problem was I was trying to install it off a network drive. It
must not have been seeing some files over the network or something, or maybe
some files got corrupt. I installed it with the CD and it works fine now.
"Michael Bodnar" <blah@.blah.com> wrote in message
news:eX63pJHWEHA.4064@.TK2MSFTNGP11.phx.gbl...
> In Visual Studio when I create a new Report Project, once it's created I
go
> into Solution Explorer, right click on the "Reports" folder and select
"Add
> New Report", and I get the following error:
> Exception from HRESULT: 0x8013141A
> I've re-installed Visual Studio, the .NET framework, and Reporting
Services,
> and after all that it still gives me the error.
> Any suggestions?
> Michael Bodnar
>

Can't add a SQL Database (2005 Express Edition) to a C# Project over RDP

Good Afternoon.

I installed C# with SQL Server 2005 Express Edition. Everything works fine on the account I installed it on, on the local machine. However.

1) I can't connect using RDP to that same account and use SQL. It times out.
2) Other users on that computer cannot make user of SQL. Also times out.

I couldn't find documentation on these issues.

Thanks,

Philski

When you first set up the SQL Express edition the system installs with the networking systems turned off. You will need to activate the remote connections on the sql server express system by using the SQL Server Configuration Manager tools. (These should have also been installed when you installed the sql express system)

|||Didn't work.

Not sure why i should need to open TCP or memory pipes anyways, since RDP is just a reflection of the desktop, and local users do not need a network connection.

I get the same error message when I try to add a database to my C# project, "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed"

Thanks,

Philski|||

Phil, here is a thread from the DB Engine forum that seems to address this issue. Were you logged into the box through TS or on the box locally?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=116459&SiteID=1

Thanks,
Sam Lester (MSFT)

sql

can't access report through report manager...

Here is what is happening.
I have a working project PROG created using VS.Net. I create a new
project for reports RPTPROJ under PROJ.
I create several reports RPT1.RDL, RPT2.RDL under the new project
RPTPROJ.
Now I start the REPORT MANAGER and create a new folder (MYREPORTS) and
upload all my reports (RDLs) into this new folder. Now when I click on
any of the reports from here, it doesn't show up. Only shows up an
"Action Canceled" message.
Here is the URL, it generates for one of the reports:
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fRPTPROJ%2fRPT1
But when I goto,
http://localhost/reportserver?%2fPROJ%2fRPTPROJ
I can see all my reports and run them.
Please can someone explain what I should do to run them from the
report manager?
ThanksWhen you publish to the report server in Report Designer, in the project
properties dialog (where you set your URL to the report server), there is a
target folder property. Set that to MYREPORTS. By default it is the
project name, which is why you're getting link s containing /RPTPROJ/RPT1
-Lukasz
This posting is provided "AS IS" with no warranties, and confers no rights.
"Roy" <nroy@.iastate.edu> wrote in message
news:ad20302f.0408091310.6e20c373@.posting.google.com...
> Here is what is happening.
> I have a working project PROG created using VS.Net. I create a new
> project for reports RPTPROJ under PROJ.
> I create several reports RPT1.RDL, RPT2.RDL under the new project
> RPTPROJ.
> Now I start the REPORT MANAGER and create a new folder (MYREPORTS) and
> upload all my reports (RDLs) into this new folder. Now when I click on
> any of the reports from here, it doesn't show up. Only shows up an
> "Action Canceled" message.
> Here is the URL, it generates for one of the reports:
> http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fRPTPROJ%2fRPT1
> But when I goto,
> http://localhost/reportserver?%2fPROJ%2fRPTPROJ
> I can see all my reports and run them.
> Please can someone explain what I should do to run them from the
> report manager?
> Thanks|||Lukasz,
I made the changes suggested by you but still the same problem. The URL it shows is:
http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMYREPORTS%2fRPT1
What am I doing wrong?
nroy@.iastate.edu (Roy) wrote in message news:<ad20302f.0408091310.6e20c373@.posting.google.com>...
> Here is what is happening.
> I have a working project PROG created using VS.Net. I create a new
> project for reports RPTPROJ under PROJ.
> I create several reports RPT1.RDL, RPT2.RDL under the new project
> RPTPROJ.
> Now I start the REPORT MANAGER and create a new folder (MYREPORTS) and
> upload all my reports (RDLs) into this new folder. Now when I click on
> any of the reports from here, it doesn't show up. Only shows up an
> "Action Canceled" message.
> Here is the URL, it generates for one of the reports:
> http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fRPTPROJ%2fRPT1
> But when I goto,
> http://localhost/reportserver?%2fPROJ%2fRPTPROJ
> I can see all my reports and run them.
> Please can someone explain what I should do to run them from the
> report manager?
> Thanks

cannot update table

I have an msde database connected to a microsoft project front end.
I am trying to update a table with values. When I use a stored procedure
everything works fine but when I try and edit the table directly I am warned
that the 'the recordset is not updatable'
Is this 'by design'? or are there permission issues?
I am logged into the database as a user almx who has database creation
rights.
I tried logging into the database as sa but I it makes no difference.
Are there any specific permissions I need to set to allow users to update
the table values directly or is this not possible in a MS Access Project?
June
Access won't allow you to update records unless there's a primary key
or unique index defined on the tables.
--Mary
On Wed, 16 Jun 2004 12:24:34 +0100, "June Macleod"
<junework@.hotmail.com> wrote:

>I have an msde database connected to a microsoft project front end.
>I am trying to update a table with values. When I use a stored procedure
>everything works fine but when I try and edit the table directly I am warned
>that the 'the recordset is not updatable'
>Is this 'by design'? or are there permission issues?
>I am logged into the database as a user almx who has database creation
>rights.
>I tried logging into the database as sa but I it makes no difference.
>Are there any specific permissions I need to set to allow users to update
>the table values directly or is this not possible in a MS Access Project?
>June
>

Cannot select Provider in Data Source Wizard Connection Manager

I think I either have an option set wrong or installed something wrong. When
I try to create a New Data Source in Data Model project, the dropdown box for
Provider is disabled and so SQLclient Data Provider is all I can do. I would
like to add a connection to an ODBC data source, which I should be able to
do. Any suggestions on what I can change to enable me to select the Provider?Semantic modelling in Model Designer supports native SQL provider only.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Shane_SDE" <ShaneSDE@.discussions.microsoft.com> wrote in message
news:054075DE-92DF-4127-9B53-0DB5FEB43670@.microsoft.com...
>I think I either have an option set wrong or installed something wrong.
>When
> I try to create a New Data Source in Data Model project, the dropdown box
> for
> Provider is disabled and so SQLclient Data Provider is all I can do. I
> would
> like to add a connection to an ODBC data source, which I should be able to
> do. Any suggestions on what I can change to enable me to select the
> Provider?

Cannot resolve index for fact table

Hi,

When I am trying to deploy or save the project with cube I am getting this error.

Property accessor 'StorageLocation' on object '339f70f7-5d30-4c38-97c8-30b675640b24' threw the following exception:'Cannot resolve the 'Fact Table 2' index because the setting does not support the '2' nesting level.
Make sure that the product is installed properly.' (Microsoft Visual Studio)

Also I have this error in "StorageLocation" property for partition.

Any ideas?

Thank you for your help.

One idea is double check the value for the StorageLocation property. What value did you have for it.
Make sure it points to the valid folder on server's file system.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

cannot rename package....

I created an earlier package called LoopAndLoadProductionTable.dtsx

but the package crashed....and when i try to open a new project and rename the package to the same previous name, it say "will i want to overwrite it?" i press yes and i got an error "Error HRESULT E_FAIL has been returned from a call to a COM component."

when i tryed to find the previous package from a SSIS Package Store,

i click the package path, then i get "connect to SSIS service on local machine failed: the specified service does not exist as an installed service".....

how do i resolve these 2 errors?

There is a separate forum for DTS issues. Look up the SSIS forums, if there is not one for the older DTS.

cannot rename package

I created an earlier package called LoopAndLoadProductionTable.dtsx

but the package crashed....and when i try to open a new project and rename the package to the same previous name, it say "will i want to overwrite it?" i press yes and i got an error "Error HRESULT E_FAIL has been returned from a call to a COM component."

when i tryed to find the previous package from a SSIS Package Store,

i click the package path, then i get "connect to SSIS service on local machine failed: the specified service does not exist as an installed service".....

how do i resolve these 2 errors?

If you are working in a project, check the project folder, not the SSIS package store for the existing/duplicate package. Still sounds all rather messy, and I don't know why it is erroring all over the show.