HELP! Website is slammed!

I don't even know if this is on topic or not...I post regularly here and it
does involve SQL2000 so here goes.
I have a view(listed below) I'm selecting from it involves about 10 tables
and I'm filtering down to around 60 rows in the result set like this:
select * from dbo.vwb_webeventlist where blnwebexpired=0 and
blnwebavailable=1 and lngeventtypefk=63
This has been working fine in place for months. Nothing that we can
determine has changed. The query above runs from query analyzer in less
than a second from my workstation with the same connection settings as we
are running from our app. The app is executing the same query as follows:
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.cn;
cmd.CommandText = "select * from vwb_webeventlist where blnwebexpired=0
and blnwebavailable=1 and lngeventtypefk=63";
cmd.CommandType = CommandType.Text;
this.cn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
This afternoon this was taking more than 15 seconds to run so our website
started timing out on everyone. After rebooting the SQL Server box the time
dropped to around 5 seconds which is still obviously unacceptable. I'm at a
loss as to what could cause this. The query still executes quickly from
query analyzer. Our website is basically unusable at the moment and it is
getting much hotter in here....
CREATE VIEW [dbo].[vwb_WebEventList]
AS
SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
evttyp.strType AS strEventType, evttyp.strTicketMID,
evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
strState,
evt.dtmStartDate, evt.dtmEndDate, evtweb.dtmWebStart,
evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
convfee.curPrice AS curConvenienceFee,
evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
evt.lngHostMemFK, evt.strEventName,
evtweb.dtmBS, evt.strMID AS strFRMID,
faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate AS
cursingrateR,
evt.curDoubRate AS curdoubrateR, latefee.curPrice AS
curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
curSingRate,
doubfee.curPrice AS curDoubRate, canxfee.curPrice AS
curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
evt.strJobCode
FROM dbo.tblBEvents AS evt INNER JOIN
dbo.tblBEventMeetings AS evtm ON
evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
dbo.tblBEventTypes AS evttyp ON evttyp.lngEventTypePK
= evt.lngEventTypeFK LEFT OUTER JOIN
dbo.tblBMeetingRooms AS evtmr ON
evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK =
evtmr.lngFacilityFK LEFT OUTER JOIN
dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK =
fac.lngFacilityMemFK INNER JOIN
dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK =
evt.lngEventPK INNER JOIN
dbo.tblBWebEventMeetings AS evtwebm ON
evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS price ON
price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK =
1 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS singfee ON
singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS doubfee ON
doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS convfee ON
convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS canxfee ON
canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS latefee ON
latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
latefee.intPriceTypeFK = 7
ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
evt.dtmStartDate"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23Pvfj27cGHA.2188@.TK2MSFTNGP04.phx.gbl...
> I don't even know if this is on topic or not...I post regularly here and
it
> does involve SQL2000 so here goes.
>
> I have a view(listed below) I'm selecting from it involves about 10 tables
> and I'm filtering down to around 60 rows in the result set like this:
> select * from dbo.vwb_webeventlist where blnwebexpired=0 and
> blnwebavailable=1 and lngeventtypefk=63
Well, first I'd get rid of the select *. Return only the rows that you
need.
But that obviously doesn't explain the sudden difference in performance
you're seeing.
I'd also make sure that you run update statistics on the tables involved.
And just to be extra careful, make sure your switch or something didn't
autonegotiate to something weird like 10Mb/sec half-duplex.

> This has been working fine in place for months. Nothing that we can
> determine has changed. The query above runs from query analyzer in less
> than a second from my workstation with the same connection settings as we
> are running from our app. The app is executing the same query as follows:
> SqlCommand cmd = new SqlCommand();
> cmd.Connection = this.cn;
> cmd.CommandText = "select * from vwb_webeventlist where blnwebexpired=0
> and blnwebavailable=1 and lngeventtypefk=63";
> cmd.CommandType = CommandType.Text;
> this.cn.Open();
> SqlDataReader rdr = cmd.ExecuteReader();
> This afternoon this was taking more than 15 seconds to run so our website
> started timing out on everyone. After rebooting the SQL Server box the
time
> dropped to around 5 seconds which is still obviously unacceptable. I'm at
a
> loss as to what could cause this. The query still executes quickly from
> query analyzer. Our website is basically unusable at the moment and it is
> getting much hotter in here....
>
>
>
>
> CREATE VIEW [dbo].[vwb_WebEventList]
> AS
> SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
> dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
> evttyp.strType AS strEventType, evttyp.strTicketMID,
> evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
> strState,
> evt.dtmStartDate, evt.dtmEndDate,
evtweb.dtmWebStart,
> evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
> convfee.curPrice AS curConvenienceFee,
> evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
> evt.lngHostMemFK, evt.strEventName,
> evtweb.dtmBS, evt.strMID AS strFRMID,
> faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate
AS
> cursingrateR,
> evt.curDoubRate AS curdoubrateR, latefee.curPrice AS
> curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
> curSingRate,
> doubfee.curPrice AS curDoubRate, canxfee.curPrice AS
> curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
> evt.strJobCode
> FROM dbo.tblBEvents AS evt INNER JOIN
> dbo.tblBEventMeetings AS evtm ON
> evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
> dbo.tblBEventTypes AS evttyp ON
evttyp.lngEventTypePK
> = evt.lngEventTypeFK LEFT OUTER JOIN
> dbo.tblBMeetingRooms AS evtmr ON
> evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
> dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK =
> evtmr.lngFacilityFK LEFT OUTER JOIN
> dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK =
> fac.lngFacilityMemFK INNER JOIN
> dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK =
> evt.lngEventPK INNER JOIN
> dbo.tblBWebEventMeetings AS evtwebm ON
> evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS price ON
> price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK
=
> 1 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS singfee ON
> singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS doubfee ON
> doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS convfee ON
> convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS canxfee ON
> canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS latefee ON
> latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> latefee.intPriceTypeFK = 7
> ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
> evt.dtmStartDate
>
>|||Thanks for responding....
I understand the "select *" issue. I would NEVER code that unless I was
investigating in query analyzer or something. I did not write the code in
question, but I know the particular view was created specifically for this
purpose ( a great waste ) and this purpose only (so why not just code the
query)...
This was the strangest thing. Changing the server in my connection string
to another server and the .NET app ran as it should. Point it back at
production and wham...
This morning I backed up and restored the production database and oddly
enough, now it seems to be fine. Go figure.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eBaoD38cGHA.3348@.TK2MSFTNGP03.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23Pvfj27cGHA.2188@.TK2MSFTNGP04.phx.gbl...
> it
> Well, first I'd get rid of the select *. Return only the rows that you
> need.
> But that obviously doesn't explain the sudden difference in performance
> you're seeing.
> I'd also make sure that you run update statistics on the tables involved.
> And just to be extra careful, make sure your switch or something didn't
> autonegotiate to something weird like 10Mb/sec half-duplex.
>
> time
> a
> evtweb.dtmWebStart,
> AS
> evttyp.lngEventTypePK
> =
>

HELP! Website is slammed!

I don't even know if this is on topic or not...I post regularly here and it
does involve SQL2000 so here goes.
I have a view(listed below) I'm selecting from it involves about 10 tables
and I'm filtering down to around 60 rows in the result set like this:
select * from dbo.vwb_webeventlist where blnwebexpired=0 and
blnwebavailable=1 and lngeventtypefk=63
This has been working fine in place for months. Nothing that we can
determine has changed. The query above runs from query analyzer in less
than a second from my workstation with the same connection settings as we
are running from our app. The app is executing the same query as follows:
SqlCommand cmd = new SqlCommand();
cmd.Connection = this.cn;
cmd.CommandText = "select * from vwb_webeventlist where blnwebexpired=0
and blnwebavailable=1 and lngeventtypefk=63";
cmd.CommandType = CommandType.Text;
this.cn.Open();
SqlDataReader rdr = cmd.ExecuteReader();
This afternoon this was taking more than 15 seconds to run so our website
started timing out on everyone. After rebooting the SQL Server box the time
dropped to around 5 seconds which is still obviously unacceptable. I'm at a
loss as to what could cause this. The query still executes quickly from
query analyzer. Our website is basically unusable at the moment and it is
getting much hotter in here....
CREATE VIEW [dbo].[vwb_WebEventList]
AS
SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
evttyp.strType AS strEventType, evttyp.strTicketMID,
evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
strState,
evt.dtmStartDate, evt.dtmEndDate, evtweb.dtmWebStart,
evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
convfee.curPrice AS curConvenienceFee,
evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
evt.lngHostMemFK, evt.strEventName,
evtweb.dtmBS, evt.strMID AS strFRMID,
faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate AS
cursingrateR,
evt.curDoubRate AS curdoubrateR, latefee.curPrice AS
curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
curSingRate,
doubfee.curPrice AS curDoubRate, canxfee.curPrice AS
curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
evt.strJobCode
FROM dbo.tblBEvents AS evt INNER JOIN
dbo.tblBEventMeetings AS evtm ON
evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
dbo.tblBEventTypes AS evttyp ON evttyp.lngEventTypePK
= evt.lngEventTypeFK LEFT OUTER JOIN
dbo.tblBMeetingRooms AS evtmr ON
evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK = evtmr.lngFacilityFK LEFT OUTER JOIN
dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK = fac.lngFacilityMemFK INNER JOIN
dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK = evt.lngEventPK INNER JOIN
dbo.tblBWebEventMeetings AS evtwebm ON
evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS price ON
price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK = 1 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS singfee ON
singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS doubfee ON
doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS convfee ON
convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS canxfee ON
canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
dbo.tblBEventMeetingPrices AS latefee ON
latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
latefee.intPriceTypeFK = 7
ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
evt.dtmStartDate"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23Pvfj27cGHA.2188@.TK2MSFTNGP04.phx.gbl...
> I don't even know if this is on topic or not...I post regularly here and
it
> does involve SQL2000 so here goes.
>
> I have a view(listed below) I'm selecting from it involves about 10 tables
> and I'm filtering down to around 60 rows in the result set like this:
> select * from dbo.vwb_webeventlist where blnwebexpired=0 and
> blnwebavailable=1 and lngeventtypefk=63
Well, first I'd get rid of the select *. Return only the rows that you
need.
But that obviously doesn't explain the sudden difference in performance
you're seeing.
I'd also make sure that you run update statistics on the tables involved.
And just to be extra careful, make sure your switch or something didn't
autonegotiate to something weird like 10Mb/sec half-duplex.
> This has been working fine in place for months. Nothing that we can
> determine has changed. The query above runs from query analyzer in less
> than a second from my workstation with the same connection settings as we
> are running from our app. The app is executing the same query as follows:
> SqlCommand cmd = new SqlCommand();
> cmd.Connection = this.cn;
> cmd.CommandText = "select * from vwb_webeventlist where blnwebexpired=0
> and blnwebavailable=1 and lngeventtypefk=63";
> cmd.CommandType = CommandType.Text;
> this.cn.Open();
> SqlDataReader rdr = cmd.ExecuteReader();
> This afternoon this was taking more than 15 seconds to run so our website
> started timing out on everyone. After rebooting the SQL Server box the
time
> dropped to around 5 seconds which is still obviously unacceptable. I'm at
a
> loss as to what could cause this. The query still executes quickly from
> query analyzer. Our website is basically unusable at the moment and it is
> getting much hotter in here....
>
>
>
>
> CREATE VIEW [dbo].[vwb_WebEventList]
> AS
> SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
> dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
> evttyp.strType AS strEventType, evttyp.strTicketMID,
> evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
> strState,
> evt.dtmStartDate, evt.dtmEndDate,
evtweb.dtmWebStart,
> evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
> convfee.curPrice AS curConvenienceFee,
> evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
> evt.lngHostMemFK, evt.strEventName,
> evtweb.dtmBS, evt.strMID AS strFRMID,
> faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate
AS
> cursingrateR,
> evt.curDoubRate AS curdoubrateR, latefee.curPrice AS
> curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
> curSingRate,
> doubfee.curPrice AS curDoubRate, canxfee.curPrice AS
> curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
> evt.strJobCode
> FROM dbo.tblBEvents AS evt INNER JOIN
> dbo.tblBEventMeetings AS evtm ON
> evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
> dbo.tblBEventTypes AS evttyp ON
evttyp.lngEventTypePK
> = evt.lngEventTypeFK LEFT OUTER JOIN
> dbo.tblBMeetingRooms AS evtmr ON
> evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
> dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK => evtmr.lngFacilityFK LEFT OUTER JOIN
> dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK => fac.lngFacilityMemFK INNER JOIN
> dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK => evt.lngEventPK INNER JOIN
> dbo.tblBWebEventMeetings AS evtwebm ON
> evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS price ON
> price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK
=> 1 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS singfee ON
> singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS doubfee ON
> doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS convfee ON
> convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS canxfee ON
> canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
> dbo.tblBEventMeetingPrices AS latefee ON
> latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
> latefee.intPriceTypeFK = 7
> ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
> evt.dtmStartDate
>
>|||Thanks for responding....
I understand the "select *" issue. I would NEVER code that unless I was
investigating in query analyzer or something. I did not write the code in
question, but I know the particular view was created specifically for this
purpose ( a great waste ) and this purpose only (so why not just code the
query)...
This was the strangest thing. Changing the server in my connection string
to another server and the .NET app ran as it should. Point it back at
production and wham...
This morning I backed up and restored the production database and oddly
enough, now it seems to be fine. Go figure.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:eBaoD38cGHA.3348@.TK2MSFTNGP03.phx.gbl...
> "Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
> news:%23Pvfj27cGHA.2188@.TK2MSFTNGP04.phx.gbl...
>> I don't even know if this is on topic or not...I post regularly here and
> it
>> does involve SQL2000 so here goes.
>>
>> I have a view(listed below) I'm selecting from it involves about 10
>> tables
>> and I'm filtering down to around 60 rows in the result set like this:
>> select * from dbo.vwb_webeventlist where blnwebexpired=0 and
>> blnwebavailable=1 and lngeventtypefk=63
> Well, first I'd get rid of the select *. Return only the rows that you
> need.
> But that obviously doesn't explain the sudden difference in performance
> you're seeing.
> I'd also make sure that you run update statistics on the tables involved.
> And just to be extra careful, make sure your switch or something didn't
> autonegotiate to something weird like 10Mb/sec half-duplex.
>
>> This has been working fine in place for months. Nothing that we can
>> determine has changed. The query above runs from query analyzer in less
>> than a second from my workstation with the same connection settings as we
>> are running from our app. The app is executing the same query as
>> follows:
>> SqlCommand cmd = new SqlCommand();
>> cmd.Connection = this.cn;
>> cmd.CommandText = "select * from vwb_webeventlist where
>> blnwebexpired=0
>> and blnwebavailable=1 and lngeventtypefk=63";
>> cmd.CommandType = CommandType.Text;
>> this.cn.Open();
>> SqlDataReader rdr = cmd.ExecuteReader();
>> This afternoon this was taking more than 15 seconds to run so our website
>> started timing out on everyone. After rebooting the SQL Server box the
> time
>> dropped to around 5 seconds which is still obviously unacceptable. I'm
>> at
> a
>> loss as to what could cause this. The query still executes quickly from
>> query analyzer. Our website is basically unusable at the moment and it
>> is
>> getting much hotter in here....
>>
>>
>>
>>
>> CREATE VIEW [dbo].[vwb_WebEventList]
>> AS
>> SELECT TOP 100 PERCENT evt.lngEventPK, evtm.lngEventMeetingPK,
>> dbo.fnbTicketsLeft(evtm.lngEventMeetingPK) AS intTicketsLeft,
>> evttyp.strType AS strEventType,
>> evttyp.strTicketMID,
>> evttyp.strHotelMID, evt.strEventName AS strLocation, evt.strEventState AS
>> strState,
>> evt.dtmStartDate, evt.dtmEndDate,
> evtweb.dtmWebStart,
>> evtweb.dtmWebEnd, evt.lngEventTypeFK, price.curPrice AS curMeetingPrice,
>> convfee.curPrice AS curConvenienceFee,
>> evttyp.blnWebExpired, evt.blnWebAvailable, evt.blnUnderConstruction,
>> evt.lngHostMemFK, evt.strEventName,
>> evtweb.dtmBS, evt.strMID AS strFRMID,
>> faccmp.strCompanyName AS strFacility, evtwebm.strSpeaker, evt.curSingRate
> AS
>> cursingrateR,
>> evt.curDoubRate AS curdoubrateR, latefee.curPrice
>> AS
>> curLateFee, evt.curCancelFee AS curCancelFeer, singfee.curPrice AS
>> curSingRate,
>> doubfee.curPrice AS curDoubRate, canxfee.curPrice
>> AS
>> curCancelFee, evt.strRegWhere, evt.dtmRegBeginTime, evt.dtmRegEndTime,
>> evt.strJobCode
>> FROM dbo.tblBEvents AS evt INNER JOIN
>> dbo.tblBEventMeetings AS evtm ON
>> evtm.lngEventMeetingPK = evt.lngEventMeetingFK INNER JOIN
>> dbo.tblBEventTypes AS evttyp ON
> evttyp.lngEventTypePK
>> = evt.lngEventTypeFK LEFT OUTER JOIN
>> dbo.tblBMeetingRooms AS evtmr ON
>> evtmr.lngEventMeetingFK = evt.lngEventMeetingFK LEFT OUTER JOIN
>> dbo.tblBFacilities AS fac ON fac.lngFacilitiesPK =>> evtmr.lngFacilityFK LEFT OUTER JOIN
>> dbo.tblMCompany AS faccmp ON faccmp.lngMemNumberPK
>> =>> fac.lngFacilityMemFK INNER JOIN
>> dbo.tblBWebEvents AS evtweb ON evtweb.lngEventFK =>> evt.lngEventPK INNER JOIN
>> dbo.tblBWebEventMeetings AS evtwebm ON
>> evtwebm.lngEventMeetingFK = evtm.lngEventMeetingPK LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS price ON
>> price.lngEventMeetingFK = evtm.lngEventMeetingPK AND price.intPriceTypeFK
> =>> 1 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS singfee ON
>> singfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> singfee.intPriceTypeFK = 2 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS doubfee ON
>> doubfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> doubfee.intPriceTypeFK = 3 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS convfee ON
>> convfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> convfee.intPriceTypeFK = 6 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS canxfee ON
>> canxfee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> canxfee.intPriceTypeFK = 8 LEFT OUTER JOIN
>> dbo.tblBEventMeetingPrices AS latefee ON
>> latefee.lngEventMeetingFK = evtm.lngEventMeetingPK AND
>> latefee.intPriceTypeFK = 7
>> ORDER BY price.curPrice DESC, fac.strState, fac.strLocation,
>> evt.dtmStartDate
>>
>

Help! Report Model Project doesn't like my primary key

I'm creating a report model in VS2005 I've created my data source fine and I have selected all the tables I want in the report model data view.

The problem is that for one of the tables it is refusing to acknowledge the promary key. If I try to create the report model it compains that the table doesn't have a primary key.

So I went into SQL Management Studio and checked the table, Lo and behold the primary key is there!!! I tried droping the primary key and recreating it but it still says there is no primary ley on the table.

Any ideas?!?Just did some fiddling and managed to find the problem.

There is a Unique Clustered Index on the table which the problem comes from, with the index it doesn't see the primary key, without it the key suddenly appears

HELP! Maintenance plan failure on ARITHABORT and QUOTED_IDENTIFIER

This has been going on for some time now. The only significant db change we
can track it to time-wise is the creation of a schema bound view for a
temporary project. This has since been deleted but the error continues so
we don't believe there to be any relation. I include the result of our
maintenance plan log here. I do not understand how it got so far into the
index rebuilds if the error is truly correct as these are database options
not table options.
Does anyone have any idea why these errors would be generated' I'm quite
concerned. I manually rebuilt the indexes from the last table listed
through all other tables in the database and received no errors.
Greatly appreciate any ideas you may have...
[1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'atempdnrefunds'
Rebuilding indexes for table 'Audit1'
Rebuilding indexes for table 'BellevueDN2004Refunds'
Rebuilding indexes for table 'helpsql'
Rebuilding indexes for table 'IBONumbersUsed'
Rebuilding indexes for table 'IBOPasswords'
Rebuilding indexes for table 'IBOTree'
Rebuilding indexes for table 'IBOTreeWithNames'
Rebuilding indexes for table 'kateupdate'
Rebuilding indexes for table 'New Table'
Rebuilding indexes for table 'Resultss'
Rebuilding indexes for table 'tblAddressQueries'
Rebuilding indexes for table 'tblAppControl'
Rebuilding indexes for table 'tblAppModules'
Rebuilding indexes for table 'tblAttendeeLegCounter'
Rebuilding indexes for table 'tblBAccomodations'
Rebuilding indexes for table 'tblBAppHotel'
Rebuilding indexes for table 'tblBApplication'
Rebuilding indexes for table 'tblBAppLocations'
Rebuilding indexes for table 'tblBAttendeeMeetings'
Rebuilding indexes for table 'tblBAttendees'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft]&#
91;ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options
have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.You have a table with a calculated column or possible an indexed view.
These need certain SET conditions to be set when you access them in order
for them to work properly. The maintenance wizard can not deal correctly
with those. The solution is to create your own Reindex job that has the
settings properly set.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
> This has been going on for some time now. The only significant db change
we
> can track it to time-wise is the creation of a schema bound view for a
> temporary project. This has since been deleted but the error continues so
> we don't believe there to be any relation. I include the result of our
> maintenance plan log here. I do not understand how it got so far into the
> index rebuilds if the error is truly correct as these are database options
> not table options.
> Does anyone have any idea why these errors would be generated' I'm quite
> concerned. I manually rebuilt the indexes from the last table listed
> through all other tables in the database and received no errors.
> Greatly appreciate any ideas you may have...
> [1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'atempdnrefunds'
> Rebuilding indexes for table 'Audit1'
> Rebuilding indexes for table 'BellevueDN2004Refunds'
> Rebuilding indexes for table 'helpsql'
> Rebuilding indexes for table 'IBONumbersUsed'
> Rebuilding indexes for table 'IBOPasswords'
> Rebuilding indexes for table 'IBOTree'
> Rebuilding indexes for table 'IBOTreeWithNames'
> Rebuilding indexes for table 'kateupdate'
> Rebuilding indexes for table 'New Table'
> Rebuilding indexes for table 'Resultss'
> Rebuilding indexes for table 'tblAddressQueries'
> Rebuilding indexes for table 'tblAppControl'
> Rebuilding indexes for table 'tblAppModules'
> Rebuilding indexes for table 'tblAttendeeLegCounter'
> Rebuilding indexes for table 'tblBAccomodations'
> Rebuilding indexes for table 'tblBAppHotel'
> Rebuilding indexes for table 'tblBApplication'
> Rebuilding indexes for table 'tblBAppLocations'
> Rebuilding indexes for table 'tblBAttendeeMeetings'
> Rebuilding indexes for table 'tblBAttendees'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODB
C
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options[/vbcol
]
have[vbcol=seagreen]
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
>
>|||Thanks for the quick response. Indeed the table it fails at has several
calculated columns. I guess what confuses me is I would have assumed the
maintenance wizard was using the same dbcc dbreindex functionality that I
use from my own jobs which work fine.
Thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...
> You have a table with a calculated column or possible an indexed view.
> These need certain SET conditions to be set when you access them in order
> for them to work properly. The maintenance wizard can not deal correctly
> with those. The solution is to create your own Reindex job that has the
> settings properly set.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
> news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
> we
so[vbcol=seagreen]
the[vbcol=seagreen]
options[vbcol=seagreen]
quite[vbcol=seagreen]
> SQL
> have
>|||It does use the same DBCC but it sets it's environment incorrectly and it's
hard coded.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:u82%23pAVXEHA.4000@.TK2MSFTNGP09.phx.gbl...
> Thanks for the quick response. Indeed the table it fails at has several
> calculated columns. I guess what confuses me is I would have assumed the
> maintenance wizard was using the same dbcc dbreindex functionality that I
> use from my own jobs which work fine.
> Thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...
order[vbcol=seagreen]
correctly[vbcol=seagreen]
> change
continues[vbcol=seagreen]
> so
our[vbcol=seagreen]
> the
> options
> quite
[Microsoft][ODBC[vbcol=seagreen]
options[vbcol=seagreen]
>sql

HELP! Maintenance plan failure on ARITHABORT and QUOTED_IDENTIFIER

This has been going on for some time now. The only significant db change we
can track it to time-wise is the creation of a schema bound view for a
temporary project. This has since been deleted but the error continues so
we don't believe there to be any relation. I include the result of our
maintenance plan log here. I do not understand how it got so far into the
index rebuilds if the error is truly correct as these are database options
not table options.
Does anyone have any idea why these errors would be generated' I'm quite
concerned. I manually rebuilt the indexes from the last table listed
through all other tables in the database and received no errors.
Greatly appreciate any ideas you may have...
[1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'atempdnrefunds'
Rebuilding indexes for table 'Audit1'
Rebuilding indexes for table 'BellevueDN2004Refunds'
Rebuilding indexes for table 'helpsql'
Rebuilding indexes for table 'IBONumbersUsed'
Rebuilding indexes for table 'IBOPasswords'
Rebuilding indexes for table 'IBOTree'
Rebuilding indexes for table 'IBOTreeWithNames'
Rebuilding indexes for table 'kateupdate'
Rebuilding indexes for table 'New Table'
Rebuilding indexes for table 'Resultss'
Rebuilding indexes for table 'tblAddressQueries'
Rebuilding indexes for table 'tblAppControl'
Rebuilding indexes for table 'tblAppModules'
Rebuilding indexes for table 'tblAttendeeLegCounter'
Rebuilding indexes for table 'tblBAccomodations'
Rebuilding indexes for table 'tblBAppHotel'
Rebuilding indexes for table 'tblBApplication'
Rebuilding indexes for table 'tblBAppLocations'
Rebuilding indexes for table 'tblBAttendeeMeetings'
Rebuilding indexes for table 'tblBAttendees'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.You have a table with a calculated column or possible an indexed view.
These need certain SET conditions to be set when you access them in order
for them to work properly. The maintenance wizard can not deal correctly
with those. The solution is to create your own Reindex job that has the
settings properly set.
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
> This has been going on for some time now. The only significant db change
we
> can track it to time-wise is the creation of a schema bound view for a
> temporary project. This has since been deleted but the error continues so
> we don't believe there to be any relation. I include the result of our
> maintenance plan log here. I do not understand how it got so far into the
> index rebuilds if the error is truly correct as these are database options
> not table options.
> Does anyone have any idea why these errors would be generated' I'm quite
> concerned. I manually rebuilt the indexes from the last table listed
> through all other tables in the database and received no errors.
> Greatly appreciate any ideas you may have...
> [1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'atempdnrefunds'
> Rebuilding indexes for table 'Audit1'
> Rebuilding indexes for table 'BellevueDN2004Refunds'
> Rebuilding indexes for table 'helpsql'
> Rebuilding indexes for table 'IBONumbersUsed'
> Rebuilding indexes for table 'IBOPasswords'
> Rebuilding indexes for table 'IBOTree'
> Rebuilding indexes for table 'IBOTreeWithNames'
> Rebuilding indexes for table 'kateupdate'
> Rebuilding indexes for table 'New Table'
> Rebuilding indexes for table 'Resultss'
> Rebuilding indexes for table 'tblAddressQueries'
> Rebuilding indexes for table 'tblAppControl'
> Rebuilding indexes for table 'tblAppModules'
> Rebuilding indexes for table 'tblAttendeeLegCounter'
> Rebuilding indexes for table 'tblBAccomodations'
> Rebuilding indexes for table 'tblBAppHotel'
> Rebuilding indexes for table 'tblBApplication'
> Rebuilding indexes for table 'tblBAppLocations'
> Rebuilding indexes for table 'tblBAttendeeMeetings'
> Rebuilding indexes for table 'tblBAttendees'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
>
>|||Thanks for the quick response. Indeed the table it fails at has several
calculated columns. I guess what confuses me is I would have assumed the
maintenance wizard was using the same dbcc dbreindex functionality that I
use from my own jobs which work fine.
Thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...
> You have a table with a calculated column or possible an indexed view.
> These need certain SET conditions to be set when you access them in order
> for them to work properly. The maintenance wizard can not deal correctly
> with those. The solution is to create your own Reindex job that has the
> settings properly set.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
> news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
> > This has been going on for some time now. The only significant db
change
> we
> > can track it to time-wise is the creation of a schema bound view for a
> > temporary project. This has since been deleted but the error continues
so
> > we don't believe there to be any relation. I include the result of our
> > maintenance plan log here. I do not understand how it got so far into
the
> > index rebuilds if the error is truly correct as these are database
options
> > not table options.
> >
> > Does anyone have any idea why these errors would be generated' I'm
quite
> > concerned. I manually rebuilt the indexes from the last table listed
> > through all other tables in the database and received no errors.
> >
> > Greatly appreciate any ideas you may have...
> >
> > [1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
> >
> > Rebuilding indexes for table 'atempdnrefunds'
> > Rebuilding indexes for table 'Audit1'
> > Rebuilding indexes for table 'BellevueDN2004Refunds'
> > Rebuilding indexes for table 'helpsql'
> > Rebuilding indexes for table 'IBONumbersUsed'
> > Rebuilding indexes for table 'IBOPasswords'
> > Rebuilding indexes for table 'IBOTree'
> > Rebuilding indexes for table 'IBOTreeWithNames'
> > Rebuilding indexes for table 'kateupdate'
> > Rebuilding indexes for table 'New Table'
> > Rebuilding indexes for table 'Resultss'
> > Rebuilding indexes for table 'tblAddressQueries'
> > Rebuilding indexes for table 'tblAppControl'
> > Rebuilding indexes for table 'tblAppModules'
> > Rebuilding indexes for table 'tblAttendeeLegCounter'
> > Rebuilding indexes for table 'tblBAccomodations'
> > Rebuilding indexes for table 'tblBAppHotel'
> > Rebuilding indexes for table 'tblBApplication'
> > Rebuilding indexes for table 'tblBAppLocations'
> > Rebuilding indexes for table 'tblBAttendeeMeetings'
> > Rebuilding indexes for table 'tblBAttendees'
> > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
> SQL
> > Server Driver][SQL Server]DBCC failed because the following SET options
> have
> > incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> >
> >
> >
>|||It does use the same DBCC but it sets it's environment incorrectly and it's
hard coded.
--
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:u82%23pAVXEHA.4000@.TK2MSFTNGP09.phx.gbl...
> Thanks for the quick response. Indeed the table it fails at has several
> calculated columns. I guess what confuses me is I would have assumed the
> maintenance wizard was using the same dbcc dbreindex functionality that I
> use from my own jobs which work fine.
> Thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...
> > You have a table with a calculated column or possible an indexed view.
> > These need certain SET conditions to be set when you access them in
order
> > for them to work properly. The maintenance wizard can not deal
correctly
> > with those. The solution is to create your own Reindex job that has the
> > settings properly set.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
> > news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
> > > This has been going on for some time now. The only significant db
> change
> > we
> > > can track it to time-wise is the creation of a schema bound view for a
> > > temporary project. This has since been deleted but the error
continues
> so
> > > we don't believe there to be any relation. I include the result of
our
> > > maintenance plan log here. I do not understand how it got so far into
> the
> > > index rebuilds if the error is truly correct as these are database
> options
> > > not table options.
> > >
> > > Does anyone have any idea why these errors would be generated' I'm
> quite
> > > concerned. I manually rebuilt the indexes from the last table listed
> > > through all other tables in the database and received no errors.
> > >
> > > Greatly appreciate any ideas you may have...
> > >
> > > [1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
> > >
> > > Rebuilding indexes for table 'atempdnrefunds'
> > > Rebuilding indexes for table 'Audit1'
> > > Rebuilding indexes for table 'BellevueDN2004Refunds'
> > > Rebuilding indexes for table 'helpsql'
> > > Rebuilding indexes for table 'IBONumbersUsed'
> > > Rebuilding indexes for table 'IBOPasswords'
> > > Rebuilding indexes for table 'IBOTree'
> > > Rebuilding indexes for table 'IBOTreeWithNames'
> > > Rebuilding indexes for table 'kateupdate'
> > > Rebuilding indexes for table 'New Table'
> > > Rebuilding indexes for table 'Resultss'
> > > Rebuilding indexes for table 'tblAddressQueries'
> > > Rebuilding indexes for table 'tblAppControl'
> > > Rebuilding indexes for table 'tblAppModules'
> > > Rebuilding indexes for table 'tblAttendeeLegCounter'
> > > Rebuilding indexes for table 'tblBAccomodations'
> > > Rebuilding indexes for table 'tblBAppHotel'
> > > Rebuilding indexes for table 'tblBApplication'
> > > Rebuilding indexes for table 'tblBAppLocations'
> > > Rebuilding indexes for table 'tblBAttendeeMeetings'
> > > Rebuilding indexes for table 'tblBAttendees'
> > > [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934:
[Microsoft][ODBC
> > SQL
> > > Server Driver][SQL Server]DBCC failed because the following SET
options
> > have
> > > incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
> > >
> > >
> > >
> >
> >
>

HELP! Maintenance plan failure on ARITHABORT and QUOTED_IDENTIFIER

This has been going on for some time now. The only significant db change we
can track it to time-wise is the creation of a schema bound view for a
temporary project. This has since been deleted but the error continues so
we don't believe there to be any relation. I include the result of our
maintenance plan log here. I do not understand how it got so far into the
index rebuilds if the error is truly correct as these are database options
not table options.
Does anyone have any idea why these errors would be generated? I'm quite
concerned. I manually rebuilt the indexes from the last table listed
through all other tables in the database and received no errors.
Greatly appreciate any ideas you may have...
[1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
Rebuilding indexes for table 'atempdnrefunds'
Rebuilding indexes for table 'Audit1'
Rebuilding indexes for table 'BellevueDN2004Refunds'
Rebuilding indexes for table 'helpsql'
Rebuilding indexes for table 'IBONumbersUsed'
Rebuilding indexes for table 'IBOPasswords'
Rebuilding indexes for table 'IBOTree'
Rebuilding indexes for table 'IBOTreeWithNames'
Rebuilding indexes for table 'kateupdate'
Rebuilding indexes for table 'New Table'
Rebuilding indexes for table 'Resultss'
Rebuilding indexes for table 'tblAddressQueries'
Rebuilding indexes for table 'tblAppControl'
Rebuilding indexes for table 'tblAppModules'
Rebuilding indexes for table 'tblAttendeeLegCounter'
Rebuilding indexes for table 'tblBAccomodations'
Rebuilding indexes for table 'tblBAppHotel'
Rebuilding indexes for table 'tblBApplication'
Rebuilding indexes for table 'tblBAppLocations'
Rebuilding indexes for table 'tblBAttendeeMeetings'
Rebuilding indexes for table 'tblBAttendees'
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL
Server Driver][SQL Server]DBCC failed because the following SET options have
incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
You have a table with a calculated column or possible an indexed view.
These need certain SET conditions to be set when you access them in order
for them to work properly. The maintenance wizard can not deal correctly
with those. The solution is to create your own Reindex job that has the
settings properly set.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
> This has been going on for some time now. The only significant db change
we
> can track it to time-wise is the creation of a schema bound view for a
> temporary project. This has since been deleted but the error continues so
> we don't believe there to be any relation. I include the result of our
> maintenance plan log here. I do not understand how it got so far into the
> index rebuilds if the error is truly correct as these are database options
> not table options.
> Does anyone have any idea why these errors would be generated? I'm quite
> concerned. I manually rebuilt the indexes from the last table listed
> through all other tables in the database and received no errors.
> Greatly appreciate any ideas you may have...
> [1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'atempdnrefunds'
> Rebuilding indexes for table 'Audit1'
> Rebuilding indexes for table 'BellevueDN2004Refunds'
> Rebuilding indexes for table 'helpsql'
> Rebuilding indexes for table 'IBONumbersUsed'
> Rebuilding indexes for table 'IBOPasswords'
> Rebuilding indexes for table 'IBOTree'
> Rebuilding indexes for table 'IBOTreeWithNames'
> Rebuilding indexes for table 'kateupdate'
> Rebuilding indexes for table 'New Table'
> Rebuilding indexes for table 'Resultss'
> Rebuilding indexes for table 'tblAddressQueries'
> Rebuilding indexes for table 'tblAppControl'
> Rebuilding indexes for table 'tblAppModules'
> Rebuilding indexes for table 'tblAttendeeLegCounter'
> Rebuilding indexes for table 'tblBAccomodations'
> Rebuilding indexes for table 'tblBAppHotel'
> Rebuilding indexes for table 'tblBApplication'
> Rebuilding indexes for table 'tblBAppLocations'
> Rebuilding indexes for table 'tblBAttendeeMeetings'
> Rebuilding indexes for table 'tblBAttendees'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
>
>
|||You have a table with a calculated column or possible an indexed view.
These need certain SET conditions to be set when you access them in order
for them to work properly. The maintenance wizard can not deal correctly
with those. The solution is to create your own Reindex job that has the
settings properly set.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
> This has been going on for some time now. The only significant db change
we
> can track it to time-wise is the creation of a schema bound view for a
> temporary project. This has since been deleted but the error continues so
> we don't believe there to be any relation. I include the result of our
> maintenance plan log here. I do not understand how it got so far into the
> index rebuilds if the error is truly correct as these are database options
> not table options.
> Does anyone have any idea why these errors would be generated? I'm quite
> concerned. I manually rebuilt the indexes from the last table listed
> through all other tables in the database and received no errors.
> Greatly appreciate any ideas you may have...
> [1] Database IBOTree: Index Rebuild (leaving 10%% free space)...
> Rebuilding indexes for table 'atempdnrefunds'
> Rebuilding indexes for table 'Audit1'
> Rebuilding indexes for table 'BellevueDN2004Refunds'
> Rebuilding indexes for table 'helpsql'
> Rebuilding indexes for table 'IBONumbersUsed'
> Rebuilding indexes for table 'IBOPasswords'
> Rebuilding indexes for table 'IBOTree'
> Rebuilding indexes for table 'IBOTreeWithNames'
> Rebuilding indexes for table 'kateupdate'
> Rebuilding indexes for table 'New Table'
> Rebuilding indexes for table 'Resultss'
> Rebuilding indexes for table 'tblAddressQueries'
> Rebuilding indexes for table 'tblAppControl'
> Rebuilding indexes for table 'tblAppModules'
> Rebuilding indexes for table 'tblAttendeeLegCounter'
> Rebuilding indexes for table 'tblBAccomodations'
> Rebuilding indexes for table 'tblBAppHotel'
> Rebuilding indexes for table 'tblBApplication'
> Rebuilding indexes for table 'tblBAppLocations'
> Rebuilding indexes for table 'tblBAttendeeMeetings'
> Rebuilding indexes for table 'tblBAttendees'
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC
SQL
> Server Driver][SQL Server]DBCC failed because the following SET options
have
> incorrect settings: 'QUOTED_IDENTIFIER, ARITHABORT'.
>
>
|||Thanks for the quick response. Indeed the table it fails at has several
calculated columns. I guess what confuses me is I would have assumed the
maintenance wizard was using the same dbcc dbreindex functionality that I
use from my own jobs which work fine.
Thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> You have a table with a calculated column or possible an indexed view.
> These need certain SET conditions to be set when you access them in order
> for them to work properly. The maintenance wizard can not deal correctly
> with those. The solution is to create your own Reindex job that has the
> settings properly set.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
> news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
> we
so[vbcol=seagreen]
the[vbcol=seagreen]
options[vbcol=seagreen]
quite
> SQL
> have
>
|||Thanks for the quick response. Indeed the table it fails at has several
calculated columns. I guess what confuses me is I would have assumed the
maintenance wizard was using the same dbcc dbreindex functionality that I
use from my own jobs which work fine.
Thanks again
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> You have a table with a calculated column or possible an indexed view.
> These need certain SET conditions to be set when you access them in order
> for them to work properly. The maintenance wizard can not deal correctly
> with those. The solution is to create your own Reindex job that has the
> settings properly set.
> --
> Andrew J. Kelly SQL MVP
>
> "Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
> news:OzPUQRSXEHA.3988@.tk2msftngp13.phx.gbl...
change[vbcol=seagreen]
> we
so[vbcol=seagreen]
the[vbcol=seagreen]
options[vbcol=seagreen]
quite
> SQL
> have
>
|||It does use the same DBCC but it sets it's environment incorrectly and it's
hard coded.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:u82%23pAVXEHA.4000@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for the quick response. Indeed the table it fails at has several
> calculated columns. I guess what confuses me is I would have assumed the
> maintenance wizard was using the same dbcc dbreindex functionality that I
> use from my own jobs which work fine.
> Thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...
order[vbcol=seagreen]
correctly[vbcol=seagreen]
> change
continues[vbcol=seagreen]
> so
our[vbcol=seagreen]
> the
> options
> quite
[Microsoft][ODBC[vbcol=seagreen]
options
>
|||It does use the same DBCC but it sets it's environment incorrectly and it's
hard coded.
Andrew J. Kelly SQL MVP
"Tim Greenwood" <tim_greenwood@.yahoo.com> wrote in message
news:u82%23pAVXEHA.4000@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for the quick response. Indeed the table it fails at has several
> calculated columns. I guess what confuses me is I would have assumed the
> maintenance wizard was using the same dbcc dbreindex functionality that I
> use from my own jobs which work fine.
> Thanks again
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eE9kesSXEHA.2636@.TK2MSFTNGP10.phx.gbl...
order[vbcol=seagreen]
correctly[vbcol=seagreen]
> change
continues[vbcol=seagreen]
> so
our[vbcol=seagreen]
> the
> options
> quite
[Microsoft][ODBC[vbcol=seagreen]
options
>

HELP! - Problem with hints...

Hello everyone,
I'm having this problem, I'm trying to run a query that consists of six
tables and one view (all joined together)
When I run it without anything on the where (or just one criteria in the
where clause) works just fine, but if I run it with two criteria in the
where clause I get the following message:
<<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
larger than the page size into a hash table. Resubmit the query with the
ROBUST PLAN hint. >>
And when I do so -adding the ROBUST PLAN option-, I get the following
message:
<<< 'Query hints' cannot be used in this query type. >>
Any idea what is going on?!? please let me know!!!
TIA,
sb-rEveryone,
I forgot to put this...
I'm using SQL Server 2000 on Win2003 Server, SP 1
Thanks again...
"segis bata" <segisbata@.hotmail.com> wrote in message
news:exo1vo0qGHA.4192@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>|||segis bata (segisbata@.hotmail.com) writes:
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
><<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
><<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
Sounds like you are in dire straits. But at least you could post the
query you are having problem with.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||What happen if you try the query without the view in it? with and without the
robust plan in the query.
"segis bata" wrote:
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not insert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>
>

HELP! - Problem with hints...

Hello everyone,
I'm having this problem, I'm trying to run a query that consists of six
tables and one view (all joined together)
When I run it without anything on the where (or just one criteria in the
where clause) works just fine, but if I run it with two criteria in the
where clause I get the following message:
<<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not ins
ert a row
larger than the page size into a hash table. Resubmit the query with the
ROBUST PLAN hint. >>>
And when I do so -adding the ROBUST PLAN option-, I get the following
message:
<<< 'Query hints' cannot be used in this query type. >>>
Any idea what is going on?!? please let me know!!!
TIA,
sb-rEveryone,
I forgot to put this...
I'm using SQL Server 2000 on Win2003 Server, SP 1
Thanks again...
"segis bata" <segisbata@.hotmail.com> wrote in message
news:exo1vo0qGHA.4192@.TK2MSFTNGP04.phx.gbl...
> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not i
nsert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>|||segis bata (segisbata@.hotmail.com) writes:
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
><<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not in
sert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
><<< 'Query hints' cannot be used in this query type. >>>
> Any idea what is going on?!? please let me know!!!
Sounds like you are in dire straits. But at least you could post the
query you are having problem with.
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|||What happen if you try the query without the view in it? with and without th
e
robust plan in the query.
"segis bata" wrote:

> Hello everyone,
> I'm having this problem, I'm trying to run a query that consists of six
> tables and one view (all joined together)
> When I run it without anything on the where (or just one criteria in the
> where clause) works just fine, but if I run it with two criteria in the
> where clause I get the following message:
> <<< [Microsoft][ODBC SQL Server Driver][SQL Server]Could not i
nsert a row
> larger than the page size into a hash table. Resubmit the query with the
> ROBUST PLAN hint. >>>
> And when I do so -adding the ROBUST PLAN option-, I get the following
> message:
> <<< 'Query hints' cannot be used in this query type. >>>
> Any idea what is going on?!? please let me know!!!
> TIA,
> sb-r
>
>

Help writing a querry

I need some help with the following
I have a view as follow
date ValueIndex Value
20060131 2001 0.0455
20060130 2001 0.0455
20060129 2001 0.0454
20060128 2001 0.0453
20060127 2001 0.0453
20060126 2001 0.0452
20060125 2001 0.0452
20060124 2001 0.0451
20060123 2001 0.0451
20060122 2001 0.045
I would like to import a file with the date and the ValueIndex once I import
that file I would like to do a look up and get the value.
Is this possiable
Thanks
Chris>> I would like to import a file with the date and the ValueIndex once I
Yes, it is possible. What exactly do you find it difficult in doing it?
Importing the file to a table? Or writing the query to get the value?
Anith|||Anith,
Both -- I have no idea where to start
Please HELP!!!!!
"Anith Sen" wrote:

> Yes, it is possible. What exactly do you find it difficult in doing it?
> Importing the file to a table? Or writing the query to get the value?
> --
> Anith
>
>|||First of all, to get the data from a file to an SQL table, you have several
options.
Perhaps the easiest one is using DTS. Check out the topic Data
Transformation Services in the SQL Server Books Online. A simple interface
in SQL Enterprise Manager can get the job done.
Alternatively, you can use command line utilities like BCP or use BULK
INSERT to get the data from a file to the table. Again, you have all the
information needed in SQL Server Books Online.
To extract the desired data from an SQL table, you issue a query, generally
in the form of a SELECT statement. Given that your table has the columns
date_column, value_index & value with date_column as the primary key, you
could retrieve the value by using a SELECT statement like:
SELECT value FROM tbl WHERE date_column = @.param ;
-- where @.param is the parameter passed to the query to look up the desired
value.
All details needed for writing SQL can be found in SQL Server Books Online.
Anith|||Anith,
Thank you for your helpfull information.
After looking at the db I do not need to import the table it is already in
the db. I need to add a column to the table/view and populate that column
with the value base on the date and valueindex combination
Thank You
"Anith Sen" wrote:

> First of all, to get the data from a file to an SQL table, you have severa
l
> options.
> Perhaps the easiest one is using DTS. Check out the topic Data
> Transformation Services in the SQL Server Books Online. A simple interface
> in SQL Enterprise Manager can get the job done.
> Alternatively, you can use command line utilities like BCP or use BULK
> INSERT to get the data from a file to the table. Again, you have all the
> information needed in SQL Server Books Online.
> To extract the desired data from an SQL table, you issue a query, generall
y
> in the form of a SELECT statement. Given that your table has the columns
> date_column, value_index & value with date_column as the primary key, you
> could retrieve the value by using a SELECT statement like:
> SELECT value FROM tbl WHERE date_column = @.param ;
> -- where @.param is the parameter passed to the query to look up the desire
d
> value.
> All details needed for writing SQL can be found in SQL Server Books Online
.
> --
> Anith
>
>|||>> I need to add a column to the table/view
Look at ALTER TABLE .. ADD .. statement in SQL Server Books Online.
You can use an UPDATE statement, something along the lines of:
UPDATE tbl
SET value = ( SELECT t1.value FROM source_tbl t1
WHERE t1.date_col = tbl.date_col
AND t1.value_idx = tbl.value_idx )
WHERE EXISTS ( SELECT * FROM source_tbl t1
WHERE t1.date_col = tbl.date_col
AND t1.value_idx = tbl.value_idx );
For more details, lookup the topic UPDATE in SQL Server Books Online.
Anith|||A couple of links that may also help you understand the SQL...
http://www.w3schools.com/sql/sql_intro.asp
http://sqlzoo.net/
There are many others, but these both provide general walkthroughs of the
basics.
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:esd6BOLPGHA.1216@.TK2MSFTNGP14.phx.gbl...
> Look at ALTER TABLE .. ADD .. statement in SQL Server Books Online.
>
> You can use an UPDATE statement, something along the lines of:
> UPDATE tbl
> SET value = ( SELECT t1.value FROM source_tbl t1
> WHERE t1.date_col = tbl.date_col
> AND t1.value_idx = tbl.value_idx )
> WHERE EXISTS ( SELECT * FROM source_tbl t1
> WHERE t1.date_col = tbl.date_col
> AND t1.value_idx = tbl.value_idx );
> For more details, lookup the topic UPDATE in SQL Server Books Online.
> --
> Anith
>

Help with view

I have a table with the following layout:
Docnumber, SlspersonA, AcctStatus, ThirdPartyName, SlspersonB, SlsManager,
SlsperACommissionRate, SlsperBCommissionRate, SlsManagerRate.
I need to create a view (or report) somehow that allows me to return
multiple rows where the Salesperson = X. The problem is that the SlspersonI
D
can exist in SlspersonA on one transaction and be SlspersonB on another. A
sample result set is below:
INV1000, GOEBEL, New, Marriott, HENDERSON,NULL,5.0,5.0,0
INV2000,HENDERSON, New, Hyatt, GOEBEL,NULL,5.0,5.0,0
How can I structure the select statement using variables to pull in just the
following:
Docnumber, Slsperson, AcctStatus, ThirdPartyName, CommissionRate.
Any help would be greatly appreciatedlooks fairly straight forward unless I am completely mistaken
declare @.salesperson varchar(10)
Set @.salesperson = 'GOEBEL'
Select Docnumber, @.salesperson, AcctStatus, ThirdPartyName, CommissionRate
from tbl
where @.salesperson in (SlspersonA,SlspersonB)Let me know if this is what u
wanted.|||create view SomeView as
select Docnumber
, SlspersonA as Slsperson
, AcctStatus
, ThirdPartyName
, CommissionRate
from SomeTable
union
select Docnumber
, SlspersonB as Slsperson
, AcctStatus
, ThirdPartyName
, CommissionRate
from SomeTable;
go
Select from SomeView where Slsperson = 'HENDERSON';
"Dan Shepherd" <DanShepherd@.discussions.microsoft.com> wrote in message
news:DF69F7BD-88FC-4B52-B613-7FEEA696F9C3@.microsoft.com...
> I have a table with the following layout:
> Docnumber, SlspersonA, AcctStatus, ThirdPartyName, SlspersonB, SlsManager,
> SlsperACommissionRate, SlsperBCommissionRate, SlsManagerRate.
> I need to create a view (or report) somehow that allows me to return
> multiple rows where the Salesperson = X. The problem is that the
SlspersonID
> can exist in SlspersonA on one transaction and be SlspersonB on another.
A
> sample result set is below:
> INV1000, GOEBEL, New, Marriott, HENDERSON,NULL,5.0,5.0,0
> INV2000,HENDERSON, New, Hyatt, GOEBEL,NULL,5.0,5.0,0
> How can I structure the select statement using variables to pull in just
the
> following:
> Docnumber, Slsperson, AcctStatus, ThirdPartyName, CommissionRate.
> Any help would be greatly appreciated