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 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,
-Isaacsql

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

Can't add stored procedure to SQL Express

I select New Stored Procedure from the context menu of the stored procedure
tab in my database, and it procedes to makes a file called SQLQuery1.sql and
saves it as a file, but doesn't add it to the list of my stored procedures...
How do I add it? What am I doing wrong? I see no method of importing the sql
file into my database...
Any help appreciated
Stored procedures are created by executing the statement(s) in the
SQLQuery1.sql file. You don't mention what interface you're using (e.g.
Visual Studio, SQL Server Management Studio, etc.), but you should have an
EXECUTE button somewhere on the toolbar. With the file open, click EXECUTE.
Assuming no errors were encountered the stored procedure is created in the
database. You can keep the file that contains the CREATE PROC statement or
not depending on your needs.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"BLiTZWiNG" <BLiTZWiNG@.discussions.microsoft.com> wrote in message
news:273B5E46-FCBE-4D90-A5C6-7A37ADDC1CE9@.microsoft.com...
>I select New Stored Procedure from the context menu of the stored procedure
> tab in my database, and it procedes to makes a file called SQLQuery1.sql
> and
> saves it as a file, but doesn't add it to the list of my stored
> procedures...
> How do I add it? What am I doing wrong? I see no method of importing the
> sql
> file into my database...
> Any help appreciated
|||Thanks for that. I'm using the Managment Studio CTP (not sure if there is a
release version of it). Anyway, Execute worked for me. I never thought to
Execute it because the SP relies on parameters to execute, so without some
test data I just figured Execute would fail and do nothing else. It did not
seem obvious to me though that the Execute command would add the SP to the
database.
"Gail Erickson [MS]" wrote:

> Stored procedures are created by executing the statement(s) in the
> SQLQuery1.sql file. You don't mention what interface you're using (e.g.
> Visual Studio, SQL Server Management Studio, etc.), but you should have an
> EXECUTE button somewhere on the toolbar. With the file open, click EXECUTE.
> Assuming no errors were encountered the stored procedure is created in the
> database. You can keep the file that contains the CREATE PROC statement or
> not depending on your needs.
> --
> Gail Erickson [MS]
> SQL Server Documentation Team
> This posting is provided "AS IS" with no warranties, and confers no rights
> "BLiTZWiNG" <BLiTZWiNG@.discussions.microsoft.com> wrote in message
> news:273B5E46-FCBE-4D90-A5C6-7A37ADDC1CE9@.microsoft.com...
>
>
|||> I never thought to
> Execute it because the SP relies on parameters to execute, so without some
> test data I just figured Execute would fail and do nothing else. It did
> not
> seem obvious to me though that the Execute command would add the SP to the
> database.
Okay, I can understand how you would think that. It's important to
understand that when you use the Execute button, only the statement or
statements in the query window are executed. In your case, the T-SQL
statement CREATE PROCEDURE is executed, but the statements could also have
been INSERT, DELETE, ALTER PROCEDURE, or any other T-SQL statement.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"BLiTZWiNG" <BLiTZWiNG@.discussions.microsoft.com> wrote in message
news:AFF3E778-449C-4D24-9DD5-2D7E3B70D5B9@.microsoft.com...[vbcol=seagreen]
> Thanks for that. I'm using the Managment Studio CTP (not sure if there is
> a
> release version of it). Anyway, Execute worked for me. I never thought to
> Execute it because the SP relies on parameters to execute, so without some
> test data I just figured Execute would fail and do nothing else. It did
> not
> seem obvious to me though that the Execute command would add the SP to the
> database.
> "Gail Erickson [MS]" wrote:

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 any SP to master DB

Hello
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
Martin
On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)
|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database
|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application
|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin

Can't add any SP to master DB

Hello
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448
/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
MartinOn Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb
/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There
is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]The
re is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hi
de quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin

Can't add any SP to master DB

Hello
I have a problem with one of our DB servers, MS SQL 2000 SP4.
We use SP for weekly maintenance, this SP is stored in the master DB.
Therefore I had to add a new SP into all DB servers but when I run the
script on this server I get the error:
Server: Msg 2714, Level 16, State 5, Procedure
sp_u_IndexDefragAndRebuild, Line 172
There is already an object named 'sp_u_IndexDefragAndRebuild' in the
database.
The script does have an exist and drop procedure command at the
beginning. I also tried several naming for the SP but I get always the
same error.
While I was googling for a solution a found http://support.microsoft.com/kb/827448/en-us
a workaround. But when I run this script I get the same error:
Server: Msg 2714, Level 16, State 6, Line 1
There is already an object named 'test_table0' in the database.
and if I try to drop the table I get:
Server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'test_table0', because it does not exist in the
system catalog.
Is there a solution without restoring the whole master DB? And if I
have to restore the master DB what do I need to look for?
I appreciate any help
MartinOn Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> Hello
> I have a problem with one of our DB servers, MS SQL 2000 SP4.
> We use SP for weekly maintenance, this SP is stored in the master DB.
> Therefore I had to add a new SP into all DB servers but when I run the
> script on this server I get the error:
> Server: Msg 2714, Level 16, State 5, Procedure
> sp_u_IndexDefragAndRebuild, Line 172
> There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> database.
> The script does have an exist and drop procedure command at the
> beginning. I also tried several naming for the SP but I get always the
> same error.
> While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> a workaround. But when I run this script I get the same error:
> Server: Msg 2714, Level 16, State 6, Line 1
> There is already an object named 'test_table0' in the database.
> and if I try to drop the table I get:
> Server: Msg 3701, Level 11, State 5, Line 1
> Cannot drop the table 'test_table0', because it does not exist in the
> system catalog.
> Is there a solution without restoring the whole master DB? And if I
> have to restore the master DB what do I need to look for?
> I appreciate any help
> Martin
Check whether you have create 'test_table0' in the SP more than
once.
Also make sure that table is in master and check the owner of the
table ( may not be dbo)|||On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
> > Hello
> > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > We use SP for weekly maintenance, this SP is stored in the master DB.
> > Therefore I had to add a new SP into all DB servers but when I run the
> > script on this server I get the error:
> > Server: Msg 2714, Level 16, State 5, Procedure
> > sp_u_IndexDefragAndRebuild, Line 172
> > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > database.
> > The script does have an exist and drop procedure command at the
> > beginning. I also tried several naming for the SP but I get always the
> > same error.
> > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > a workaround. But when I run this script I get the same error:
> > Server: Msg 2714, Level 16, State 6, Line 1
> > There is already an object named 'test_table0' in the database.
> > and if I try to drop the table I get:
> > Server: Msg 3701, Level 11, State 5, Line 1
> > Cannot drop the table 'test_table0', because it does not exist in the
> > system catalog.
> > Is there a solution without restoring the whole master DB? And if I
> > have to restore the master DB what do I need to look for?
> > I appreciate any help
> > Martin
> Check whether you have create 'test_table0' in the SP more than
> once.
> Also make sure that table is in master and check the owner of the
> table ( may not be dbo)- Hide quoted text -
> - Show quoted text -
no, the 'test_table0' was not created, not even once but the error
says that there is already a table. If I go throught sysobject I cant
find the SP and if I try to add a table manually by right mousbutton
and new table... I get the same error:
'test_tab' table
- Unable to create table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object named 'test_tab' in the database.
same happens if I try to add a new SP manually:
Error 2714: There is already an object named 'test_sp' in the database|||On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
>
>
> > On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> > > Hello
> > > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > > We use SP for weekly maintenance, this SP is stored in the master DB.
> > > Therefore I had to add a new SP into all DB servers but when I run the
> > > script on this server I get the error:
> > > Server: Msg 2714, Level 16, State 5, Procedure
> > > sp_u_IndexDefragAndRebuild, Line 172
> > > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > > database.
> > > The script does have an exist and drop procedure command at the
> > > beginning. I also tried several naming for the SP but I get always the
> > > same error.
> > > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > > a workaround. But when I run this script I get the same error:
> > > Server: Msg 2714, Level 16, State 6, Line 1
> > > There is already an object named 'test_table0' in the database.
> > > and if I try to drop the table I get:
> > > Server: Msg 3701, Level 11, State 5, Line 1
> > > Cannot drop the table 'test_table0', because it does not exist in the
> > > system catalog.
> > > Is there a solution without restoring the whole master DB? And if I
> > > have to restore the master DB what do I need to look for?
> > > I appreciate any help
> > > Martin
> > Check whether you have create 'test_table0' in the SP more than
> > once.
> > Also make sure that table is in master and check the owner of the
> > table ( may not be dbo)- Hide quoted text -
> > - Show quoted text -
> no, the 'test_table0' was not created, not even once but the error
> says that there is already a table. If I go throught sysobject I cant
> find the SP and if I try to add a table manually by right mousbutton
> and new table... I get the same error:
> 'test_tab' table
> - Unable to create table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> already an object named 'test_tab' in the database.
> same happens if I try to add a new SP manually:
> Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> - Show quoted text -
Following may point to some of the issues
1. Check database connection in ODBC pointing to intended server/
instance and database
2. ODBC user may be connected with a sql server user and there may be
table name with owner being the connected user . If sql user connected
through ODBC is abcuser check any table with abcuser is present in the
database
3. No spelling mistakes in object names (table, SP etc)
4. Multiple users are connecting to your database and trying to run
same code (create table, create procedure etc) . I suggest keep
creating tables , SPs as a separate task . Do not mingle this with
your application|||On Aug 3, 10:49 am, M A Srinivas <masri...@.gmail.com> wrote:
> On Aug 3, 12:54 pm, DBA <martin-za.fr...@.ubs.com> wrote:
>
>
> > On Aug 3, 9:20 am, M A Srinivas <masri...@.gmail.com> wrote:
> > > On Aug 3, 12:03 pm, DBA <martin-za.fr...@.ubs.com> wrote:
> > > > Hello
> > > > I have a problem with one of our DB servers, MS SQL 2000 SP4.
> > > > We use SP for weekly maintenance, this SP is stored in the master DB.
> > > > Therefore I had to add a new SP into all DB servers but when I run the
> > > > script on this server I get the error:
> > > > Server: Msg 2714, Level 16, State 5, Procedure
> > > > sp_u_IndexDefragAndRebuild, Line 172
> > > > There is already an object named 'sp_u_IndexDefragAndRebuild' in the
> > > > database.
> > > > The script does have an exist and drop procedure command at the
> > > > beginning. I also tried several naming for the SP but I get always the
> > > > same error.
> > > > While I was googling for a solution a foundhttp://support.microsoft.com/kb/827448/en-us
> > > > a workaround. But when I run this script I get the same error:
> > > > Server: Msg 2714, Level 16, State 6, Line 1
> > > > There is already an object named 'test_table0' in the database.
> > > > and if I try to drop the table I get:
> > > > Server: Msg 3701, Level 11, State 5, Line 1
> > > > Cannot drop the table 'test_table0', because it does not exist in the
> > > > system catalog.
> > > > Is there a solution without restoring the whole master DB? And if I
> > > > have to restore the master DB what do I need to look for?
> > > > I appreciate any help
> > > > Martin
> > > Check whether you have create 'test_table0' in the SP more than
> > > once.
> > > Also make sure that table is in master and check the owner of the
> > > table ( may not be dbo)- Hide quoted text -
> > > - Show quoted text -
> > no, the 'test_table0' was not created, not even once but the error
> > says that there is already a table. If I go throught sysobject I cant
> > find the SP and if I try to add a table manually by right mousbutton
> > and new table... I get the same error:
> > 'test_tab' table
> > - Unable to create table.
> > ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]There is
> > already an object named 'test_tab' in the database.
> > same happens if I try to add a new SP manually:
> > Error 2714: There is already an object named 'test_sp' in the database- Hide quoted text -
> > - Show quoted text -
> Following may point to some of the issues
> 1. Check database connection in ODBC pointing to intended server/
> instance and database
> 2. ODBC user may be connected with a sql server user and there may be
> table name with owner being the connected user . If sql user connected
> through ODBC is abcuser check any table with abcuser is present in the
> database
> 3. No spelling mistakes in object names (table, SP etc)
> 4. Multiple users are connecting to your database and trying to run
> same code (create table, create procedure etc) . I suggest keep
> creating tables , SPs as a separate task . Do not mingle this with
> your application- Hide quoted text -
> - Show quoted text -
Thanks for your input but all of them look good.
I am the DBA and nobody else is allowed to use master DB or add any
tables/SP to the master DB. There are some users connected to other
DBs but without permission to add objects to any DB.
I don't think this is an user issue, I rather believe there is
something wrong with my master DB.
http://support.microsoft.com/kb/827448/en-us
The object ID is already in use and the existing object has rows in
the syscolumns system table. When you try to create a stored
procedure, a failure may occur with error number 134. This problem
occurs because the syscolumns table already contains a row with the
same values for the objid, the colid, and the paramid columns.
How can I find out what object ID does that SP uses if I can't find it
in sysobject of the master DB?
thanks
Martin

Cant access sql express stored procedure..help!

Hi everyone, im having alot of trouble trying to execute a stored proc from sql express. heres my code

DbProviderFactory db =DbProviderFactories.GetFactory("System.Data.SqlClient");

using(DbConnection conn = db.CreateConnection()){

ConnectionStringSettings s =ConfigurationManager.ConnectionStrings["constrolservicetest"];

conn.ConnectionString = s.ConnectionString;

conn.Open();

DbCommand cmd = conn.CreateCommand();

cmd.CommandText ="StoredProcedure1";

cmd.CommandType =CommandType.StoredProcedure;

DbParameter param = db.CreateParameter();

param.ParameterName ="@.test";

param.Value = 2;

cmd.Parameters.Add(param);

cmd.ExecuteNonQuery();

}

****************************procedure code***************************

Create PROCEDUREdbo.StoredProcedure1

(

@.testint

)

AS

Update temp

Settest = @.test

******************************************************************

The code just doesn't update. I know my connection string is correct because i got the datareader to work, but i just cant get the stored proc to call. Any help will be greatly appreciated.

Thanks,

-D

That code looks reasonable to me. Are you sure it's being called?

Can't access properties on Stored Procedures

Hi. I have set up an instance of SQL 2005 on my local computer. I use MS SQL Server Management Studio to login into my 2005 instance via Windows login. When I expand "Stored Procedures" under "Programmability" of the Database I want and go to a stored procedure and right click, I don't see the "properities" selection.

I know it's there because I can get to it on other instances but I can't get to it on my local instance. I added my windows user to my Logins and then to my database users but it didn't help.

Can anyone please tell me what I need to do so I can access the properties of the stored procedures?

Thanks

Your procedure was created with encryption option. Because this you can′t see the contents.

|||Its either an encryption problem (like already mentioned by the other poster) or you don′t have the appropiate permissions viewing the properties. Which access level does the account you are using have ? Is there a pas lock on the stored procedure symbol ?

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Can't access linked server, except from

Hi, (excuse my poor english).
I want to access data stored on a SQL2000 server using
"SELECT * FROM openquery(SQL2000srv, ..." from a
connection on my SQL2005 server.
I checked "Allow remote connections" on both servers, and
configured Nammed pipes as prefered protocol.
Security is Windows authentification only for both servers.
If I test my query on Management studio on my 2005 server,
everything is OK. But when I test from any other machine,
I have this error : (sorry, I translated it from french :
OLE DB 'SQLNCLI' from linked server returns 'Connection timeout'.
Msg 5, level 16, state 1, Line 0
Nammed pipes : can't open connection to SQL Server [5]. ".).
I can connect to both servers using any connection, so it's not
a user security problem, I think.
Do you have any clues ?
Thank you,
Jid.Hi Jide,
you have to ensure that Named Pipes is turned on as a Client Protocol.
You do this by opening up SQL Server Configuration Manager from the Sql
Server program group.
Underneath the SQL Native Client Configuration you find the Client Protocols
- here the Named Pipes protocol have to be enabled...
That may be the reason - at least its disabled at default.
Regards,
Mogens Nielsen
WM-data Danmark
"jidehem" wrote:

> Hi, (excuse my poor english).
> I want to access data stored on a SQL2000 server using
> "SELECT * FROM openquery(SQL2000srv, ..." from a
> connection on my SQL2005 server.
> I checked "Allow remote connections" on both servers, and
> configured Nammed pipes as prefered protocol.
> Security is Windows authentification only for both servers.
> If I test my query on Management studio on my 2005 server,
> everything is OK. But when I test from any other machine,
> I have this error : (sorry, I translated it from french :
> OLE DB 'SQLNCLI' from linked server returns 'Connection timeout'.
> Msg 5, level 16, state 1, Line 0
> Nammed pipes : can't open connection to SQL Server [5]. ".).
> I can connect to both servers using any connection, so it's not
> a user security problem, I think.
> Do you have any clues ?
> Thank you,
> Jidé.
>
>|||Mogens Nielsen - WM-Data A/S wrote:
> Hi Jide,
> you have to ensure that Named Pipes is turned on as a Client Protocol.
> You do this by opening up SQL Server Configuration Manager from the
> Sql Server program group.
> Underneath the SQL Native Client Configuration you find the Client
> Protocols - here the Named Pipes protocol have to be enabled...
> That may be the reason - at least its disabled at default.
Nammed pipes was already my prefered protocol for both servers
and in Client configuration utility. But thank you for your answer.
Another idea ?
Jid.|||Thought it was the simple problem like not turning named pipes on. I don't
think I've got any other good ideas...
Well, you could try connect by using an IP-adress instead of using the
server name, but then again - you shouldn't be able to connect through the
enterprise manager.
I think this is not of much help...
Maybe someone else has a clue !?
"jidehem" wrote:

> Mogens Nielsen - WM-Data A/S wrote:
> Nammed pipes was already my prefered protocol for both servers
> and in Client configuration utility. But thank you for your answer.
> Another idea ?
> Jidé.
>
>|||Mogens Nielsen - WM-Data A/S wrote:
> I think this is not of much help...
> Maybe someone else has a clue !?
Thank you again for your answers. I found the problem :
it's was "double hop" authentification problem.
In active directory, you need to activate delegation for
the user of the account of the SQL Server service, and for
the computer.
I have manually registred with setspn my linked server:
setspn -A MSSQLSvc/server.mydomaine.local:1433 account
(and not :
setspn -A MSSQLSvc/server:1433 account
nor
setspn -A MSSQLSvc/server:1433 mydomaine\account).
Now, all is OK.
Jid.

Cannot View Master.sysdatabase Names in Webpage

I have a webpage that should display the database names from the master.sysdatabases in a dropdownlist.

The code for the stored procedure works fine in Query Analyzer and returns the list of all database names under that instance of SQL-Server 2000.

The code:

select name from master.dbo.sysdatabases
order by 1

doesn't return the sysdatabase names to the ASP.NET webpage when executing the stored procedure.

It returns:

System.Data.DataRowView

Why does it do this and how can I fix it?

Tx

I don't know what you are trying to do but the Master database and it's content are Microsoft Property, I don't think you can use them in your web application. If you look inside the Master or look at the System tables Poster you know using those tables in your application is not something you do. Hope this helps.|||Probably a permissions issue.|||

Thanks.

The lookup is for our administrative/development purposes.
Since we have multiple databases to search through, the app shows the databases, you select a database, there's a lookup and display of tables, views and sprocs, and the contents are displayed in a large textbox for cutting and pasting purposes.

So the sysdatabases lookup is needed to select though the production databases.

The sproc doing the work has remote privs.
Other than that, how would you assign a priv to access the tables?
I didn't need it in QA.

Your help is appreciated.

|||I don't think it's a security/permissions issue. The built-in stored procedure sp_databases, which does exactly what your sql query does, has its execute permissions default to thepublic role. I think the problem is in your code, so you should post your code snippet here.|||

A quick test is to run this in both the webpage and your QA and see the difference:

SELECT SUSER_NAME(),USER_NAME()

That will tell you who are logged into SQL Server as, and what login are you being mapped to.

Although, rereading your original message, herman is most likely correct. It appears that your .NET code is wrong, considering that system.whatever.dataview is a .NET class, I would say that you've assigned a dataview to a variable using the .ToString method, or have done so implicitly.

Cannot use the OUTPUT option when passing a constant to a stored p

Hi,
I'm trying to export a text column into a text file using the bcp command
but I keep getting the error message in the subject line. So I checked the
database properties and didn't find anything.
My Code:
bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions
WHERE Admit_Date>'2005-06-01' and Admit_Date<'2005-07-01'" out
"I:\sttr\STTRLabsIn.txt" -c -T
go
Does anyone know why I am getting this message?
Much Appreciated,
ChiekoError message? Wish I could see it.
ML|||Since your BCP source is a query rather than a table/view, you need to
specify 'queryout' instead of 'out'.
Hope this helps.
Dan Guzman
SQL Server MVP
"chieko" <chieko@.discussions.microsoft.com> wrote in message
news:A82B768C-9367-447E-81DE-8EBFFAFBD62C@.microsoft.com...
> Hi,
> I'm trying to export a text column into a text file using the bcp command
> but I keep getting the error message in the subject line. So I checked the
> database properties and didn't find anything.
> My Code:
> bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions
> WHERE Admit_Date>'2005-06-01' and Admit_Date<'2005-07-01'" out
> "I:\sttr\STTRLabsIn.txt" -c -T
> go
> Does anyone know why I am getting this message?
> Much Appreciated,
> Chieko
>

Cannot use the OUTPUT option when passing a constant Stored Proced

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
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
>> >