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! Unable to restore DB from SQL7 to SQL2000

Hi Good Day everybody,
Currently, I'm planning to upgrade SQL7 to SQL2000, the configuration are
below:
Current Server (Server A)
a) Windows NT4 SP6a
b) SQL 7 + SP4
c) Default Collation: SQL_Latin1_General_CP1_CI_AS
d) Default Data Location: E:\MSSQL7
New Server (Server B)
a) Window 2000 Server SP4
b) SQL 2000 + SP3a
c) Default Collation: Latin1_General_CP1_CI_AS (required to set as default)
d) Default Data Location: D:\Program Files\Microsoft SQL Server
Authentication Mode:
a) Mixed Mode
b) SQL and Windows Authentication
* Both server are login with same userid and password.
Problem:
I have create and new user database "PA_CCCTemp" in the SQL2000 server.
Besides, I also backup user database "PA_CCC" from SQL7.
However, during I restore databases that I have backup into the SQL2000
server, I hit an error "Microsoft SQL-DMo [ODBC SQLState:42000] The backup
set holds a backup of a database other than the existing 'PA_CCCTemp"
database. Restore Databse is terminating abnormally.
I do not know what is the problem caused. Either the database is different
name, or the restoration location are different from the original place, or
the collation is different.
I also need help on how I can migrate the database from SQL7 to SQL 2000,
includes user id and logon password.
Regards,
Polar BearHi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.­mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.l­df'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
SQL Server MVP
Thanks
Hari
SQL Server MVP
"Polar Bear" <Polar Bear@.discussions.microsoft.com> wrote in message
news:227A549B-7D5A-48DA-A4F5-1850CE31B84B@.microsoft.com...
> Hi Good Day everybody,
> Currently, I'm planning to upgrade SQL7 to SQL2000, the configuration are
> below:
> Current Server (Server A)
> a) Windows NT4 SP6a
> b) SQL 7 + SP4
> c) Default Collation: SQL_Latin1_General_CP1_CI_AS
> d) Default Data Location: E:\MSSQL7
> New Server (Server B)
> a) Window 2000 Server SP4
> b) SQL 2000 + SP3a
> c) Default Collation: Latin1_General_CP1_CI_AS (required to set as
> default)
> d) Default Data Location: D:\Program Files\Microsoft SQL Server
> Authentication Mode:
> a) Mixed Mode
> b) SQL and Windows Authentication
> * Both server are login with same userid and password.
> Problem:
> I have create and new user database "PA_CCCTemp" in the SQL2000 server.
> Besides, I also backup user database "PA_CCC" from SQL7.
> However, during I restore databases that I have backup into the SQL2000
> server, I hit an error "Microsoft SQL-DMo [ODBC SQLState:42000] The backup
> set holds a backup of a database other than the existing 'PA_CCCTemp"
> database. Restore Databse is terminating abnormally.
> I do not know what is the problem caused. Either the database is different
> name, or the restoration location are different from the original place,
> or
> the collation is different.
> I also need help on how I can migrate the database from SQL7 to SQL 2000,
> includes user id and logon password.
> Regards,
> Polar Bear
>sql

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL que

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! The transaction log is full error in SSIS Execute SQL Task when I execute a DELETE SQL

Dear all:

I had got the below error when I execute a DELETE SQL query in SSIS Execute SQL Task :

Error: 0xC002F210 at DelAFKO, Execute SQL Task: Executing the query "DELETE FROM [CQMS_SAP].[dbo].[AFKO]" failed with the following error: "The transaction log for database 'CQMS_SAP' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

But my disk has large as more than 6 GB space, and I query the log_reuse_wait_desc column in sys.databases which return value as "NOTHING".

So this confused me, any one has any experience on this?

Many thanks,

Tomorrow

Up

Please help me ~~~

|||This issue seems tobe more related to the database engine; you maybe in better luck there...|||

Lets be clear, the transaction log and management is a SQL engine issue, and nothing to do with SSIS. You could run the same SQL in any query submission tool, and would fail in the same manner.

Troubleshooting a Full Transaction Log (Error 9002)
(http://msdn2.microsoft.com/en-us/library/ms175495.aspx)

Just because you have 6GB of free disk space does not mean that disk space is available to the log file. What are the growth options? I strongly believe is very bad practice to rely on auto-growth of data or log files. It can seriously impact performance of a system, so should be actively managed rather than forgotten.

|||

Thanks all.

Tomorrow

Help! Join question

Hi, All
I have two tables as below, TABLE1 and TABLE2.
TABLE 1: Base
ID PName PPrice
--
1 A 30
2 B 20
TABLE 2: History
ID Ldate Amount
--
1 2005/8/7 50
The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign key.
What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
it will return the result as below:
Ldate PName Amount
--
2005/8/7 A 50
2005/8/7 B null
and when I pass the date of 2005/8/8, it will return the result as below:
Ldate PName Amount
--
2005/8/8 A null
2005/8/8 B nullHere you go..
CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
CREATE TABLE #History(id int, Ldate datetime, amount int)
INSERT INTO #Base VALUES(1, 'A',30)
INSERT INTO #Base VALUES(2, 'B',20)
INSERT INTO #History VALUES(1,'20050807',50)
DECLARE @.DateParam datetime
SET @.DateParam = '20050807'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
SET @.DateParam = '20050808'
SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
FROM #Base B
LEFT OUTER JOIN #History H
ON B.Id=H.id AND H.LDate=@.DateParam
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Cool! Thomas. That is what i need.
Many Thanks
"Roji. P. Thomas" wrote:

> Here you go..
>
> CREATE TABLE #Base(id int, PName VARCHAR(10), Price int)
> CREATE TABLE #History(id int, Ldate datetime, amount int)
> INSERT INTO #Base VALUES(1, 'A',30)
> INSERT INTO #Base VALUES(2, 'B',20)
> INSERT INTO #History VALUES(1,'20050807',50)
> DECLARE @.DateParam datetime
> SET @.DateParam = '20050807'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
> SET @.DateParam = '20050808'
> SELECT COALESCE(H.Ldate,@.DateParam) as LDate, B.PName, H.Amount
> FROM #Base B
> LEFT OUTER JOIN #History H
> ON B.Id=H.id AND H.LDate=@.DateParam
>
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "OKLover" <OKLover@.discussions.microsoft.com> wrote in message
> news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
>
>|||Hi
CREATE TABLE #t1
(
rowid int not null primary key,
pname char(1) not null,
pprice decimal(5,2)
)
insert into #t1 values (1,'A',20)
insert into #t1 values (2,'B',30)
CREATE TABLE #t2
(
rowid int ,
ldate datetime not null,
amn decimal(5,2)
)
insert into #t2 values (1,'20050807',20)
select coalesce(Ldate,'20050808'), PName,sum(pprice+amn)
from #t1 left join #t2
on #t2.rowid=#t1.rowid
and #t2.ldate='20050808'
group by Ldate, PName
Note: you will have to change a coded date value to the parameter.
"OKLover" <OKLover@.discussions.microsoft.com> wrote in message
news:C36C3DD6-8A42-427A-9779-4A4776B59C65@.microsoft.com...
> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign
> key.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Hi
Probably you can try this
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
where B.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"OKLover" wrote:

> Hi, All
> I have two tables as below, TABLE1 and TABLE2.
>
> TABLE 1: Base
> ID PName PPrice
> --
> 1 A 30
> 2 B 20
> TABLE 2: History
> ID Ldate Amount
> --
> 1 2005/8/7 50
>
> The ID of TALBE1 is the primary key and the ID of TABLE2 is the foreign ke
y.
> What's the right T-SQL JOIN statement when I pass the date of 2005/8/7,
> it will return the result as below:
>
> Ldate PName Amount
> --
> 2005/8/7 A 50
> 2005/8/7 B null
>
> and when I pass the date of 2005/8/8, it will return the result as below:
> Ldate PName Amount
> --
> 2005/8/8 A null
> 2005/8/8 B null
>
>|||Your solution will not work because you put the joining condition in the
where clause.
--
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
> Hi
> Probably you can try this
> declare
> @.compDate datetime
> set @.compDate = '20050807'
> select ISNULL(Ldate,@.compDate), PName, Amount
> from Base B
> full join History H on H.ID=B.ID
> where B.ldate=@.compDate
>
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "OKLover" wrote:
>|||sorry! thank you for the correction
declare
@.compDate datetime
set @.compDate = '20050807'
select ISNULL(Ldate,@.compDate), PName, Amount
from Base B
full join History H on H.ID=B.ID
and H.ldate=@.compDate
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Roji. P. Thomas" wrote:

> Your solution will not work because you put the joining condition in the
> where clause.
> --
> Roji. P. Thomas
> Net Asset Management
> http://toponewithties.blogspot.com
>
> "Chandra" <chandra@.discussions.microsoft.com> wrote in message
> news:B9A5252D-8F2F-4190-BD02-CC009B1DC36C@.microsoft.com...
>
>

Help! Issues with Export to Excel

Hi,
I need to export my reports to Excel, and I've encountered strange layout problems, as below.
Problem 1: Looks ok in report, looks crazy in Excel
----
I understand that data regions within table and matrices are not supported (see http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp).. so I have a matrix in a rectangle(instead of a table), and this rectangle within a list. Report generates this fine,.. nice and neat.., butonce exported to Excel, the layout is messy and unintelligible. One report column can be represented by 1 and some even 10 cells. Does anyone know what is the cause of this? Perhaps the use of lists?
Problem 2: What's #NAME?
--
I have a column X in report that a calculated value, and formula is
=(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
textbox213 and textbox211 both have values from sums of other field items. So column X has a proper value when generated, but once exported, it says #NAME in the Excel column (error i suppose). When I click on #NAME, it says =(_146/_144) <-- what does this mean?
I would really appreciate anyone's help on this, since i've spend loads of time (too much!) on this.. Seems like what I see in the report is not what I get in Excel! Anyway, thank you in advance.
Best regards,
Julie
--
Message posted via http://www.sqlmonster.comIt's recommended to use tables rather than rectangles and lists when
exporting to Excel. As the link you provided describes, you get
unpredictable results when using anything other than tables or matrixes.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:d285b020b8244ddeb4394efec2c3fb23@.SQLMonster.com...
> Hi,
> I need to export my reports to Excel, and I've encountered strange layout
> problems, as below.
> Problem 1: Looks ok in report, looks crazy in Excel
> ----
> I understand that data regions within table and matrices are not supported
> (see
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp)..
> so I have a matrix in a rectangle(instead of a table), and this rectangle
> within a list. Report generates this fine,.. nice and neat.., butonce
> exported to Excel, the layout is messy and unintelligible. One report
> column can be represented by 1 and some even 10 cells. Does anyone know
> what is the cause of this? Perhaps the use of lists?
> Problem 2: What's #NAME?
> --
> I have a column X in report that a calculated value, and formula is
> =(ReportItems!textbox213.Value / ReportItems!textbox211.Value) ...
> textbox213 and textbox211 both have values from sums of other field items.
> So column X has a proper value when generated, but once exported, it says
> #NAME in the Excel column (error i suppose). When I click on #NAME, it
> says =(_146/_144) <-- what does this mean?
> I would really appreciate anyone's help on this, since i've spend loads of
> time (too much!) on this.. Seems like what I see in the report is not what
> I get in Excel! Anyway, thank you in advance.
> Best regards,
> Julie
> --
> Message posted via http://www.sqlmonster.com|||I can't use tables to encapsulate the matrix. If I do put the matrix within the table, it would say "Data Regions within table/matrix cells are ignored" on Excel when exported. This is a well known issue and is currently not supported, even in SP1.
However, I managed to get it to look slightly better in Excel, but after *much* manipulation on the alignment of the matrices and lists...
--
Message posted via http://www.sqlmonster.com|||Okay, I get it, you're right, there's no easy answer -- just lots of
tweaking layout.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"JTay via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:7e935d41037d4da694ce1277e681894a@.SQLMonster.com...
>I can't use tables to encapsulate the matrix. If I do put the matrix within
>the table, it would say "Data Regions within table/matrix cells are
>ignored" on Excel when exported. This is a well known issue and is
>currently not supported, even in SP1.
> However, I managed to get it to look slightly better in Excel, but after
> *much* manipulation on the alignment of the matrices and lists...
> --
> Message posted via http://www.sqlmonster.com

Help! CUBE? ROLLUP? or COMPUTE BY?

Help! CUBE? ROLLUP? or COMPUTE BY?
Giving a table's content as below...
Date Area Product Amount
----
--
2005/07/23 CA Book 150
2005/07/29 NY Pen 70
2005/08/03 CA Pen 500
2005/08/04 CA Book 200
2005/08/05 NY Book 270
May i ask how to get the result as below?
Area Book Book Pen Pen
----
--
CA SUM(Book_CA_thisMonth) SUM(Book_CA_th
isyear) SUM(Pen_CA_thisMonth) SUM(Pe
n_CA_thisyear)
NY SUM(Book_NY_thisMonth) SUM(Book_NY_th
isyear) SUM(Pen_NY_thisMonth) SUM(Pe
n_NY_thisyear)
Should i use CUBE? ROLLUP? or COMPUTE BY? What's the correct SELECT syntax?Hi
COMPUTE/COMPUTE BY are available for backward compatibility and there should
not be used in new code. The information obtained by using ROLLUP would be:
CREATE TABLE myData([Date] datetime,Area char(2),Product char(4),Amount int)
INSERT INTO myData([Date],Area,Product,Amount)
SELECT '20050723','CA','Book', 150
UNION ALL SELECT '20050729','NY','Pen', 70
UNION ALL SELECT '20050803','CA','Pen', 500
UNION ALL SELECT '20050804','CA','Book', 200
UNION ALL SELECT '20050805','NY','Book', 270
CREATE VIEW MyMonthlyValues AS
SELECT YEAR([Date]) AS Year, MONTH([Date]) AS [Month], Area, Product, Amount
FROM MyData
SELECT CASE WHEN (GROUPING([Year]) = 1) THEN 'All' ELSE CAST([Year] AS
CHAR(5)) END AS [Year],
CASE WHEN (GROUPING([Month]) = 1) THEN 'All' ELSE CAST([Month] AS CHAR(4))
END AS [Month],
CASE WHEN (GROUPING([Area]) = 1) THEN 'All' ELSE [Area] END AS [Area],
CASE WHEN (GROUPING([Product]) = 1) THEN 'All' ELSE [Product] END AS
[Product],
SUM(Amount) AS AmountTotal
FROM MyMonthlyValues
GROUP BY [Year], [Month], Area, Product WITH ROLLUP
To restrict to the current month you could do
SELECT CASE WHEN (GROUPING([Year]) = 1) THEN 'All' ELSE CAST([Year] AS
CHAR(5)) END AS [Year],
CASE WHEN (GROUPING([Month]) = 1) THEN 'All' ELSE CAST([Month] AS CHAR(4))
END AS [Month],
CASE WHEN (GROUPING([Area]) = 1) THEN 'All' ELSE [Area] END AS [Area],
CASE WHEN (GROUPING([Product]) = 1) THEN 'All' ELSE [Product] END AS
[Product],
SUM(Amount) AS AmountTotal
FROM MyMonthlyValues
WHERE [Month] = MONTH(GETDATE()) AND [Year] = YEAR(GetDate())
GROUP BY [Year], [Month], Area, Product WITH ROLLUP
You can process the rows on the client if you don't the format you
specified. Alternatively it is possible to do:
CREATE VIEW MyMonthlyTotals AS
SELECT [Year], [Month], [Area], [Product], SUM(Amount) As MonthTotal
FROM MyMonthlyValues
GROUP BY [Year], [Month], [Area], [Product]
SELECT A.[Year], A.[Month], A.[Area], A.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE A.[Year] = M.[Year] AND
A.[Month] >= M.[Month] AND A.[Area] = M.[Area] AND A.[Product] = M.[Product]
) AS AnnualTotal
FROM MyMonthlyTotals A
WHERE A.[Month] = MONTH(GETDATE())
AND A.[Year] = YEAR(GetDate())
ORDER BY A.[Year], A.[Month], A.[Area], A.[Product]
You may need a Products, Areas and possibly a Calendar Table (see
http://www.aspfaq.com/show.asp?id=2519) to OUTER JOIN to, so that all
products, areas, months are displayed in case data is missing for certain
values.
e.g.
CREATE TABLE Products ( ProductId int NOT NULL IDENTITY(1,1), Product
char(4) )
INSERT INTO Products ( Product )
SELECT 'Book'
UNION ALL SELECT 'Pen'
CREATE TABLE Areas( AreaId int NOT NULL IDENTITY(1,1), Area char(2) )
INSERT INTO Areas ( Area )
SELECT 'CA'
UNION ALL SELECT 'NY'
John
"OKLover" wrote:
> Help! CUBE? ROLLUP? or COMPUTE BY?
>
> Giving a table's content as below...
> Date Area Product Amount
> ----
--
> 2005/07/23 CA Book 150
> 2005/07/29 NY Pen 70
> 2005/08/03 CA Pen 500
> 2005/08/04 CA Book 200
> 2005/08/05 NY Book 270
>
> May i ask how to get the result as below?
> Area Book Book Pen Pen
> ----
--
> CA SUM(Book_CA_thisMonth) SUM(Book_CA_th
isyear) SUM(Pen_CA_thisMonth) SUM(
Pen_CA_thisyear)
> NY SUM(Book_NY_thisMonth) SUM(Book_NY_th
isyear) SUM(Pen_NY_thisMonth) SUM(
Pen_NY_thisyear)
> Should i use CUBE? ROLLUP? or COMPUTE BY? What's the correct SELECT syntax?[/color
]|||What a MVP likes John Bell should to be respected. you do so much. :)
As your suggestion, i got 3 SELECT results as below:
Year Month Area Product AmountTotal
---
2005 7 CA Book 150
2005 7 CA All 150
2005 7 NY Pen 70
2005 7 NY All 70
2005 7 All All 220
2005 8 CA Book 200
2005 8 CA Pen 500
2005 8 CA All 700
2005 8 NY Book 270
2005 8 NY All 270
2005 8 All All 970
2005 All All All 1190
All All All All 1190
Year Month Area Product AmountTotal
---
2005 8 CA Book 200
2005 8 CA Pen 500
2005 8 CA All 700
2005 8 NY Book 270
2005 8 NY All 270
2005 8 All All 970
2005 All All All 970
All All All All 970
Year Month Area Product MonthT AnnualT
----
2005 8 CA Book 200 350
2005 8 CA Pen 500 500
2005 8 NY Book 270 270
Is it possible to get the results like this:
Book Pen
---
CA | 200 350 500 500
NY | 270 270 Null Null
Total with 4 columns and 2 rows excluding the Product and Area label.|||Hi
I would have hoped that you would have taken the suggestions further,
progressing further with the scripts and taken on some of the suggestions,
such as using a product, area and calendar table you can "fill in the gaps"
such as:
CREATE TABLE Calendar ( Month int, Year int, [Date] datetime )
DECLARE @.basedate datetime
SET @.basedate = '20050101'
INSERT Calendar ( Month, Year, [Date] )
SELECT
MONTH(DATEADD(m,i,@.basedate)),Year(DATEA
DD(m,i,@.basedate)),DATEADD(m,i,@.base
date)
FROM ( SELECT 1 AS i
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4
UNION ALL SELECT 5
UNION ALL SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 10
UNION ALL SELECT 11
UNION ALL SELECT 12 ) A
SELECT R.[Area], P.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE C.[Year] = M.[Year] AND
C.[Month] >= M.[Month] AND R.[Area] = M.[Area] AND P.[Product] = M.[Product]
) AS AnnualTotal
FROM Calendar C
CROSS JOIN [Products] P
CROSS JOIN [Areas] R
LEFT JOIN MyMonthlyTotals A ON A.Product = P.Product AND C.[Month] =
A.[Month] AND C.[Year] = A.[Year] AND R.[Area] = A.[Area]
WHERE C.[Month] = 8
AND C.[Year] = 2005
ORDER BY R.[Area], P.[Product]
This will give
Area Product MonthTotal AnnualTotal
-- -- -- --
CA Book 200 350
CA Pen 500 500
NY Book 270 270
NY Pen NULL 70
In the previous post I said to get into your exact format it is best to do
it on the client, but it is possible using SQL, and there are many posts on
how to CROSSTAB or PIVOT your results such as http://tinyurl.com/7hfet where
if you had read the links such as
http://www.windowsitpro.com/SQLServ...5608/15608.html you
should have come up with:
SELECT [Area], SUM(CASE WHEN [Product] = 'Book' THEN MonthTotal ELSE 0 END)
AS [Book Month],
SUM(CASE WHEN [Product] = 'Book' THEN AnnualTotal ELSE 0 END) AS [Book Year],
SUM(CASE WHEN [Product] = 'Pen' THEN MonthTotal ELSE 0 END) AS [Pen Month],
SUM(CASE WHEN [Product] = 'Pen' THEN AnnualTotal ELSE 0 END) AS [Pen Year]
FROM ( SELECT R.[Area], P.[Product], A.[MonthTotal], ( SELECT
SUM(M.[MonthTotal]) FROM MyMonthlyTotals M WHERE C.[Year] = M.[Year] AND
C.[Month] >= M.[Month] AND R.[Area] = M.[Area] AND P.[Product] = M.[Product]
) AS AnnualTotal
FROM Calendar C
CROSS JOIN [Products] P
CROSS JOIN [Areas] R
LEFT JOIN MyMonthlyTotals A ON A.Product = P.Product AND C.[Month] =
A.[Month] AND C.[Year] = A.[Year] AND R.[Area] = A.[Area]
WHERE C.[Month] = 8
AND C.[Year] = 2005 ) D
GROUP BY [Area]
ORDER BY [Area]
John
"OKLover" wrote:

> What a MVP likes John Bell should to be respected. you do so much. :)
> As your suggestion, i got 3 SELECT results as below:
> Year Month Area Product AmountTotal
> ---
> 2005 7 CA Book 150
> 2005 7 CA All 150
> 2005 7 NY Pen 70
> 2005 7 NY All 70
> 2005 7 All All 220
> 2005 8 CA Book 200
> 2005 8 CA Pen 500
> 2005 8 CA All 700
> 2005 8 NY Book 270
> 2005 8 NY All 270
> 2005 8 All All 970
> 2005 All All All 1190
> All All All All 1190
>
>
> Year Month Area Product AmountTotal
> ---
> 2005 8 CA Book 200
> 2005 8 CA Pen 500
> 2005 8 CA All 700
> 2005 8 NY Book 270
> 2005 8 NY All 270
> 2005 8 All All 970
> 2005 All All All 970
> All All All All 970
>
> Year Month Area Product MonthT AnnualT
> ----
--
> 2005 8 CA Book 200 350
> 2005 8 CA Pen 500 500
> 2005 8 NY Book 270 270
>
> Is it possible to get the results like this:
> Book Pen
> ---
> CA | 200 350 500 500
> NY | 270 270 Null Null
>
> Total with 4 columns and 2 rows excluding the Product and Area label.|||THANK YOU VERY MUCH !!!

HELP!

Let's say I have a table similar to the one below
ID Car Colo
1 BMW silve
2 BMW silve
3 BMW red
4 BMW re
5 BMW re
Is it possible to group BMW, and count the colors in separate fields
Car Silver Re
BMW 2"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:9533FD53-A616-438E-BB32-5559CA6B7DDC@.microsoft.com...
> Let's say I have a table similar to the one below:
> ID Car Color
> 1 BMW silver
> 2 BMW silver
> 3 BMW red
> 4 BMW red
> 5 BMW red
> Is it possible to group BMW, and count the colors in separate fields.
> Car Silver Red
> BMW 2 3
If you have a known set of colours, then try
SELECT Car
, (SELECT COUNT(*)
FROM vehicles AS v
WHERE Colour = 'silver' and v.Car = o.Car)
AS Silver
, (SELECT COUNT(*)
FROM vehicles AS v
WHERE Colour = 'red' and v.Car = o.car)
AS Red
FROM vehicles AS o
GROUP BY Car
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.647 / Virus Database: 414 - Release Date: 29/03/2004|||"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:9533FD53-A616-438E-BB32-5559CA6B7DDC@.microsoft.com...
> Let's say I have a table similar to the one below:
> ID Car Color
> 1 BMW silver
> 2 BMW silver
> 3 BMW red
> 4 BMW red
> 5 BMW red
> Is it possible to group BMW, and count the colors in separate fields.
> Car Silver Red
> BMW 2 3
>
>
>|||To create crosstabs both static (known number of pivot columns) and
dynamic (unknown number of pivot columns) without any complicated
sql coding check out the RAC utility for S2k.RAC is somewhat similar
to the Access crosstab but is much more powerful and has many
options.
RAC v2.2 and QALite @.
www.rac4sql.net|||"Chambers" <anonymous@.discussions.microsoft.com> wrote in message
news:3564FA65-B638-415B-9806-362D5A3C8581@.microsoft.com...
> Bob, much thanks to you, the script works perfectly. I did have select
within a select as you did, but you took it a step further using V and O.
What is V and O by the way? Are they virtual fields?
No, they are just aliases for the tables
FROM vehicles AS v
means that you can then refer to the vehicles table as v
Because we are referring to the vehicles table in the outer and inner SELECT
statements, we have to have give them different aliases so SQL knows which
one we are talking about.
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.655 / Virus Database: 420 - Release Date: 08/04/2004

Help woth stored procedure!

Hi,
Below is my stored procedure, for some reason it is not bringing back the
max contract start date although I have sopecified this. Any ideas would be
greatly appreciated. Will also include some data to show what I am trying
to do.
SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
MAX(dbo.tbl_support.s_contract_startdate) AS
Start_date, dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
GROUP BY dbo.tbl_referral_info.ri_closed, dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name, dbo.tbl_support_provider.sp_company,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_add.ra_add1, dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_id
ORDER BY dbo.tbl_referral_name.rn_surname
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth
There are also other records included in the list but I am trying to get the
maximum contract start date for any where there are more than one instance
like the example above.
Appreciate the help
Thanks
DamonWhat does "not bringing back the max contract start date" mean? The
wrong date? No date? An error message? It's hard to help you unless you
post some code that will actually reproduce the problem and explain
what result you expect. See the following article which explains the
best way to post your problem for the group:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi,
Sorry about that. Basically when I include MAX on the contract_startdate
field it is having no effect on my results. What it should be doing is
bringing back the most recent contract_startdates if there is more than once
instance of a person and address but it is bringing back everything. i.e.
Damon Smith, 41 Seven Oaks, Caerau, 0, , Alan Jones, 10/03/2003,
Homeless Team
Damon Smith, 41 Seven Oaks, Caerau, 0, CA, Alan Jones, 06/10/2003, Tai
Troth - I want it to just bring this one back (most recent date) in all
cases where there is more than one instance of a person and address like
above.
Any ideas why this may be?
Damon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1108634350.485310.14970@.l41g2000cwc.googlegroups.com...
> What does "not bringing back the max contract start date" mean? The
> wrong date? No date? An error message? It's hard to help you unless you
> post some code that will actually reproduce the problem and explain
> what result you expect. See the following article which explains the
> best way to post your problem for the group:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||You need something like the following. PLease bear in mind I typed this
and have not tested it. You need to do a subselect in the where clause
of the main query to determine the ID and maximum start date then limit
the records in the main criteria based on the subselect.
You do not need a Group By clause in the main query. I have also
forgotten whether MS SQL Server allows multiple fields for subselects
as I have shown. If it does not then concatenate the two fields into
one. eg dbo.tbl_referral_name.rn_id +
dbo.tbl_support.s_contract_startdate
SELECT dbo.tbl_referral_name.rn_id,
dbo.tbl_referral_name.rn_forename,
dbo.tbl_referral_name.rn_surname,
dbo.tbl_referral_add.ra_add1,
dbo.tbl_referral_add.ra_add2,
dbo.tbl_referral_info.ri_closed,
dbo.tbl_support.s_options,
dbo.tbl_officer.off_full_name,
dbo.tbl_support.s_contract_startdate AS Start_date,
dbo.tbl_support_provider.sp_company
FROM dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id INNER JOIN
dbo.tbl_officer ON dbo.tbl_referral_info.ri_off_id =
dbo.tbl_officer.off_id INNER JOIN
dbo.tbl_support_provider ON dbo.tbl_support.s_sp_id =
dbo.tbl_support_provider.sp_id
WHERE dbo.tbl_referral_name.rn_id, dbo.tbl_support.s_contract_startdate
in ( select dbo.tbl_referral_name.rn_id,
max(dbo.tbl_support.s_contract_startdate)
from dbo.tbl_referral_name INNER JOIN
dbo.tbl_referral_add ON dbo.tbl_referral_name.rn_id =
dbo.tbl_referral_add.ra_rn_id INNER JOIN
dbo.tbl_referral_info ON dbo.tbl_referral_add.ra_id =
dbo.tbl_referral_info.ri_ra_id INNER JOIN
dbo.tbl_support ON dbo.tbl_referral_info.ri_id =
dbo.tbl_support.s_ri_id
group by dbo.tbl_referral_name.rn_id)
ORDER BY dbo.tbl_referral_name.rn_surname
Celtic Kiwi

Help with WHERE Clause

hi guys help please..I have a stored procedure below that basically retrieve data from tables and under my WHERE clause I want to execute conditions depending on the value of "@.FilterBy" variable. If @.FilterBy is equal to "Pending" then execute a conditions under "IF @.FilterBy = 'Pending'" and if it equals to 'Delivered' then execute conditions under IF @.FilterBy = 'Delivered'. But unfortunately I can't figure out how to do that my stored procedure below just wont work becuase it has an error "Incorrect syntax near the keyword 'IF'"...Any help guys on how to solve this problem? Thanks in advance!

USE [CFREEDB]
GO
/****** Object: StoredProcedure [dbo].[usp_DELIVERY_GET] Script Date: 09/01/2007 12:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DELIVERY_GET]
@.FilterBy varchar(20),
@.CustomerID int,
@.FromDate datetime,
@.ToDate datetime
AS
BEGIN

SELECT DISTINCT Delivery.CustomerID, Customer.Customer_LastName, Customer.Customer_MiddleName, Customer.Customer_FirstName,
Customer.Customer_Company, Customer.Customer_Address, Customer.Customer_ContactNo, Customer.Customer_Discount, Customer_Balance

FROM CFREE_Delivery Delivery
INNER JOIN CFREE_Customer Customer
ON Delivery.CustomerID = Customer.CustomerID
WHERE
IF @.FilterBy = 'Pending'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NULL AND
Delivery.IsRemitted IS NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END
IF @.FilterBy = 'Delivered'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NOT NULL AND
Delivery.IsRemitted IS NOT NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END

ORDER BY Customer.Customer_LastName, Customer.Customer_FirstName, Customer.Customer_MiddleName

ENDWHERE Delivery.IsDeleted <> 1
AND Delivery_Date BETWEEN @.FromDate AND @.ToDate
AND (
( @.FilterBy = 'Pending'
AND Delivery.IsDelivered IS NULL
AND Delivery.IsRemitted IS NULL
)
OR ( @.FilterBy = 'Delivered'
AND Delivery.IsDelivered IS NOT NULL
AND Delivery.IsRemitted IS NOT NULL
)
)

Help with Web Sync sql 2005 to sql express

Hello,

OK I finally got the subscriber connected to the IIS server for replication. I am now getting errors when trying to apply the snap shot. Below is the error? Did I setup the publication incorrectly by selecting replication with another sql 2005? Am I supposed to select something different when trying to replicate between slq 2005 and sql express?

Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'activities_2.sch' could not be propagated to the subscriber.
2005-08-24 20:52:35.920 Percent Complete: 0
2005-08-24 20:52:35.920 Category:NULL
Source: Microsoft SQL Native Client
Number: 1703
Message: Online index operations can only be performed in Enterprise edition of SQL Server.

'activities_2.sch' script

drop Table [dbo].[activities]
go
SET ANSI_PADDING ON
go

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[activities](
[activity] [varchar](50) NOT NULL,
[billing] [bit] NOT NULL,
[category] [varchar](50) NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_77F8C0F06FB942A7B7206EF4GD99AD745] DEFAULT (newsequentialid())
)

GO
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
go
SET QUOTED_IDENTIFIER ON
go
ALTER TABLE [dbo].[activities] ADD CONSTRAINT [PK_activities] PRIMARY KEY CLUSTERED
(
[activity] ASC
)WITH (SORT_IN_TEMPDB = OFF, ONLINE = OFF)
GO

I've experienced the same problem but am struggling with the bitwise syntax for disabling the XMLIndex schema option using sp_changemergearticle. Could you provide an example?

Also, as an alternative workaround during development I've been manually commenting out the problem index from the .dri and .sch files in the snapshot:

--WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF)

Obviously it would better to fix the XMLIndex schema option than to have to do this everytime I create a new snapshot!

Thanks, STEVE

|||The current schema option should be something like: 0x04nnnnnn
To remove the XML index, make it 0x00nnnnnn and run snapshot again and sync (reinit).

If it is 0x07nnnnnn, then make it 0x03nnnnnn.
Basically you want to remove the 04 part in it.|||The current schema options on my tables is :

0x000000000C034FD1

I tried changing it to

0x000000000C030FD1

but that didn't work.

Any ideas?

|||The current schema option: 0x000000000C034FD1
New one to try: 0x0000000008034FD1|||This was a known problem with Express subscribers.
Which CTP are you using? This should be fixed in the further CTPs. Either try the next CTP or there is a workaround below:

Meanwhile you can workaround the issue by disabling the XMLIndex schema option (0x04000000) on the table articles.

Use sp_changemergearticle to change the schema_option to remove this and then the snapshot should be applied correctly.

help with understanding transactional replication

Hi everybody,
I was told that for transactional replication (see posts below) I need to
have snapshot scheduled to run for instance each night. But i dont
understand this.
This is my way of thinking how transactional replication should be
initiated:
- logreader, snapshot and distributer are stopped
- logreader is started so that it captures transactions that snapshot might
miss out on
- run snapshot immediately after logreader is started.
- snapshot starts doing its thing (copying the schema and the data in the
tables of the ddatabase). If for example snapshot has already processed
TableA and a change in data is made to TableA, logreader will pick this
change up and record it.
- once snapshot has completed its task, the distributer is started. The
distributer moves the snapshot to its destination and then reindexes the
tables. Finally it processes those transactions captured by the logreader.
Is this right?
I cant understand why the snapshot agent should be scheduled for
transactional replication, if the logreader is processing all future
transactions. My thought was that:
initial_snapshot
+
ongoing_transactions (as processed by the logreader)
=
current state of database
So why is there is a need to schedule snapshot for transactional
replication?
Any help in clearing up any of my misconceptions would be fantastic!
cheers, john
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23$2wX4c7FHA.3648@.tk2msftngp13.phx.gbl...
> The snapshot agent should be scheduled, perhaps each hour, or at a time
> when there are few users on your system. Note that a snapshot will only be
> generated if a subscriber needs one. Otherwise no snapshot will be
> generated. So, the only time you need to start this agent is when a
> subscriber needs one.
> The log reader agent should be running continuously.
> I normally run the distributation agent continuously. It doesn't matter in
> which order you start it.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "john r" <johnr@.trailer.com> wrote in message
> news:ucO85ub7FHA.956@.TK2MSFTNGP10.phx.gbl...
>
John,
the snapshot agent runs for initialization and reinitialization only. If you
have loads of anonymous subscribers where you have no idea when they'll come
online, then perhaps there is a case for frequent snapshots (is this what
was being referred to by whoever it was who told you that the snapshot agent
needs to run every night?), but most likely this isn't the case for you. In
my setup, we have only ever run the snapshot agent once on some
publications. Certainly the snapshot agents are all disabled and only run
manually when necessary.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!