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! Reset dbo login id password, cannot get company website up

Our company website runs off of Microsoft SQL Server. Someone tried changing the 'administrative' password but the way that they did it locks us out of the database.

Under Enterprise Manager there is a Security group, underneath here is a login. The person reset the password here on the login id that is the DBO for our website's database. Underneath the database in Enterprise Manager the dbo uses this login id. Where else does the password need to get reset in order for that login id to access the database? We cannot set it back to the previous password because it is unknown.

When we go to our website we get the following error:
Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver]Error establishing socket. Connection refused: connect


Please try the following:
Enable Robust Exception Information to provide greater detail about the source of errors. In the Administrator, click Debugging & Logging > Debugging Settings, and select the Robust Exception Information option.
Check the ColdFusion documentation to verify that you are using the correct syntax.
Search the Knowledge Base to find a solution to your problem.

Browser Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322)Also- when trying to login to SQL Query Analyzer using the login id that was changed under security it doesn't work with the new password. This is where I'm assuming the problem is (different password for login id vs. dbo).

SQL Server authentication- login id and password method.|||You have a bigger problem than I'd care to tackle with only support via a web site. You really need professional help on problems like this, it will be far cheaper in the long run than trying to work it out this way once you figure in your own time into the equation.

When you change the password using SQL Enterprise Manager, it will change the password. If you change it there, then can't log in using Query Analyzer, then you almost certainly mis-typed the password in one place or the other.

The error being reported doesn't have anything to do with the password. That error message is telling you that the "client" machine (probably your web server) can't "see" your SQL Server machine. My first guess would be due to a configuration issue with Windows Firewall, but that is only a guess.

You have more than one problem. The problems are complex, and can interfere with one another in "interesting" ways. You really need professional help with this!

-PatP|||We got it figured out. It was on the cold fusion side where the password needed to be changed.

Help! Insert a record into SQL database with ASP.net and C#

I'm trying to make a website that people can upload file to the server, then the webpage will automatically insert the username and the file name into the database. I have thought about this for couple days but still got nothing. can some one help me?

can some one post a very simple aspx file that can insert a record to the sql database with C#? Not the code that generated by ASP.net 2.0!

I want to see how to access the database manually.

Thank you.

A quick example:

protected void Button1_Click(object sender, EventArgs e)
{
string connectionString = @."Data Source=Confute;Initial Catalog=AdventureWorks;Integrated Security=SSPI";

using (SqlConnection connection = new SqlConnection(connectionString))
{

SqlDataSource sds = new SqlDataSource(connectionString, "select * from Orders (where EmployeeID in (@.list))");

SqlCommand cmd = new SqlCommand("insert into t1 (name) select @.name", connection);

//add text of txtBox_Name as input parameter for the insert command
cmd.Parameters.Add("name", txtBox_Name.Text);

connection.Open();
cmd.ExecuteNonQuery();
}

}

|||It is really help! Thank you very much

Help with Website Password Login

Im new to website programming, and im currently making a website where i have to make a user login screen. The usernames and passwords, are stored in a SQL Database (SQL Server 2000)..

How do i search the database for the username the user enters and checks the password to see if its right? And what program do you recommend to set this up, the easiest? - Thnaksusing asp u can execute the sql queries to do the check.|||Assuming you use ASP you could do something like this:

Username = TRIM(Request.Form("Username"))
Password = TRIM(Request.Form("Password"))

IF Username <> "" AND Password <> "" THEN
SQL = "SELECT Password FROM users WHERE Username = '" & Username & "'"
Database = "myconnectionstring"
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open Database

Set RS = MyConn.Execute(SQL)
IF RS.EOF THEN
Response.Redirect("keep_out.asp")
ELSE
dbPassword = TRIM(RS("Password"))
IF Password = dbPassword THEN
Session("mysite_login") = True
Response.Redirect("welcome.asp")
ELSE
Response.Redirect("keep_out.asp")
END IF
END IF
END IF|||Thanks Frettmaestro. Im using an ASP webpage made in Frontpage 2002. Ill try the code, but where do i put it? Do i put it in th cade of the Submit (Login) Button? Thanks again.|||Yes, you probably have a loginpage vith a form. You could put the code from my previous post in the top of that file, and have the form post the data to itself. Then in every page that is supposed to be protected you need to include a file that checks if the login-session has been set or not:

check_user.asp (this is the file you include in all protected files):
<%
IF Session("mysite_login") <> True THEN Response.Redirect("keep_out.asp")
%>

And that's it! You have yourself a "secure" page...|||Im sorry, its not working for me. Do you hava a page you made, that uses that same code? If so, could i have the link, so i can see what your doing that im not? thanks