Wednesday, March 28, 2012
HELP! Website is slammed!
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!
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
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
Hi,
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.Xmdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.lXdf'
(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
>
Help! Unable to restore DB from SQL7 to SQL2000
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.ldf'
(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! Unable to restore DB from SQL7 to SQL2000
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 back
up
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 ba
ckup
> 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
>
HELP! Transaction Log is huge
I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
Transaction log backup right after is 126MB. I tried shrinking and
everything else I could think of. I even deleted the DB and restored it from
a full DB backup. The ldf still comes up with 11GB. I have another
installation of the same database on another server where the ldf shows a
normal size (40MB).
Can anybody tell me what is going on? During the restore I moved the data
files to another disk that has lots of space but there is something wrong
here and I would like to get to the bottom of it.
Thanks for your help,
ClausHi, what is your recovery set too. If it is set to "Full" you need to do a
translog backup, and truncate after.
If you set it to simple, it will checkpoint and keep the log resonable.
Try backup log (database) with truncate_only
Then shrink the log file.
"cjobes" wrote:
> Hi all,
> I have an SQL2000 running on a W2K server. The mdf file is about 140MB but
> the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and the
> Transaction log backup right after is 126MB. I tried shrinking and
> everything else I could think of. I even deleted the DB and restored it from
> a full DB backup. The ldf still comes up with 11GB. I have another
> installation of the same database on another server where the ldf shows a
> normal size (40MB).
> Can anybody tell me what is going on? During the restore I moved the data
> files to another disk that has lots of space but there is something wrong
> here and I would like to get to the bottom of it.
> Thanks for your help,
> Claus
>
>|||I have it set to full because we do a daily backup and then hourly
transaction log backups. The one instance works as expected but the other
one has this 11GB ldf and I don't seem to be able to get it down.
Now after reading your advice I switched the db to simple, run a backup and
then shrank the log file. Now it's down to 0.5MB. My question is, shouldn't
it do that automatically when the full db backup runs with the option to
remove inactive entries from the transaction log?
Thanks for your help,
Claus
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||Hi,
As soon as you run the trasnaction log backup the inactive entries of
transaction log will be cleared. But the physcal file will not get shrink
automatically until or unless you enable to database option "AUTO_SHRINK".
Turning on this option in a production server is not recommended.
Because auto shrinking will reduce the performance.
To avoid this after the transaction log backup you could do a DBCC
SHRINKFILE (refer books online) on the LDF file frequently
(Daily or weekly once.. based on the LDF growth) to reduce the LDF file
size.
DBCC SQLPERF(LOGSPACE)
will give you the exact file size and usage
Thanks
Hari
MCDBA
"cjobes" <cjobes@.nova-tech.org> wrote in message
news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> I have it set to full because we do a daily backup and then hourly
> transaction log backups. The one instance works as expected but the other
> one has this 11GB ldf and I don't seem to be able to get it down.
> Now after reading your advice I switched the db to simple, run a backup
and
> then shrank the log file. Now it's down to 0.5MB. My question is,
shouldn't
> it do that automatically when the full db backup runs with the option to
> remove inactive entries from the transaction log?
> Thanks for your help,
> Claus
>
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do
a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored
it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf
shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>|||Thanks for the helpful advice. I will keep an eye on this.
Claus
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eWRTv$ZhEHA.2624@.TK2MSFTNGP12.phx.gbl...
> Hi,
> As soon as you run the trasnaction log backup the inactive entries of
> transaction log will be cleared. But the physcal file will not get shrink
> automatically until or unless you enable to database option "AUTO_SHRINK".
> Turning on this option in a production server is not recommended.
> Because auto shrinking will reduce the performance.
> To avoid this after the transaction log backup you could do a DBCC
> SHRINKFILE (refer books online) on the LDF file frequently
> (Daily or weekly once.. based on the LDF growth) to reduce the LDF file
> size.
> DBCC SQLPERF(LOGSPACE)
> will give you the exact file size and usage
> Thanks
> Hari
> MCDBA
>
> "cjobes" <cjobes@.nova-tech.org> wrote in message
> news:uReKjIYhEHA.3992@.TK2MSFTNGP11.phx.gbl...
> > I have it set to full because we do a daily backup and then hourly
> > transaction log backups. The one instance works as expected but the
other
> > one has this 11GB ldf and I don't seem to be able to get it down.
> >
> > Now after reading your advice I switched the db to simple, run a backup
> and
> > then shrank the log file. Now it's down to 0.5MB. My question is,
> shouldn't
> > it do that automatically when the full db backup runs with the option to
> > remove inactive entries from the transaction log?
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do
> a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
> and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
> it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
> shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
>|||I have the same problem with a client as you do. I did the backup and
shrink but the log file is still at 23gigs!
Where do I find the option to 'truncate' in the SQL Server Enterprise
Manager?
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> Hi, what is your recovery set too. If it is set to "Full" you need to do a
> translog backup, and truncate after.
> If you set it to simple, it will checkpoint and keep the log resonable.
> Try backup log (database) with truncate_only
> Then shrink the log file.
>
> "cjobes" wrote:
> > Hi all,
> >
> > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
but
> > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
the
> > Transaction log backup right after is 126MB. I tried shrinking and
> > everything else I could think of. I even deleted the DB and restored it
from
> > a full DB backup. The ldf still comes up with 11GB. I have another
> > installation of the same database on another server where the ldf shows
a
> > normal size (40MB).
> >
> > Can anybody tell me what is going on? During the restore I moved the
data
> > files to another disk that has lots of space but there is something
wrong
> > here and I would like to get to the bottom of it.
> >
> > Thanks for your help,
> >
> > Claus
> >
> >
> >|||You have to do it in the analyzer
backup log "database" with truncate_only
You can also add it as a task in a job.
"Dean Penderghast" wrote:
> I have the same problem with a client as you do. I did the backup and
> shrink but the log file is still at 23gigs!
> Where do I find the option to 'truncate' in the SQL Server Enterprise
> Manager?
> "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > Hi, what is your recovery set too. If it is set to "Full" you need to do a
> > translog backup, and truncate after.
> >
> > If you set it to simple, it will checkpoint and keep the log resonable.
> >
> > Try backup log (database) with truncate_only
> >
> > Then shrink the log file.
> >
> >
> > "cjobes" wrote:
> >
> > > Hi all,
> > >
> > > I have an SQL2000 running on a W2K server. The mdf file is about 140MB
> but
> > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB and
> the
> > > Transaction log backup right after is 126MB. I tried shrinking and
> > > everything else I could think of. I even deleted the DB and restored it
> from
> > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > installation of the same database on another server where the ldf shows
> a
> > > normal size (40MB).
> > >
> > > Can anybody tell me what is going on? During the restore I moved the
> data
> > > files to another disk that has lots of space but there is something
> wrong
> > > here and I would like to get to the bottom of it.
> > >
> > > Thanks for your help,
> > >
> > > Claus
> > >
> > >
> > >
>
>|||I did that and the log file is still 23 gigs in size. I've done a full
transaction backup and tried to resize the file and still can't get it to
shrink. Am I missing something simple here?
Dean Penderghast
"robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
news:DF200740-7079-472C-8D0B-563ECC3FBBFD@.microsoft.com...
> You have to do it in the analyzer
> backup log "database" with truncate_only
> You can also add it as a task in a job.
> "Dean Penderghast" wrote:
> > I have the same problem with a client as you do. I did the backup and
> > shrink but the log file is still at 23gigs!
> >
> > Where do I find the option to 'truncate' in the SQL Server Enterprise
> > Manager?
> >
> > "robert_at_cbb" <robertatcbb@.discussions.microsoft.com> wrote in message
> > news:CE6A255E-1443-41C5-BFEA-D366A848E3F9@.microsoft.com...
> > > Hi, what is your recovery set too. If it is set to "Full" you need to
do a
> > > translog backup, and truncate after.
> > >
> > > If you set it to simple, it will checkpoint and keep the log
resonable.
> > >
> > > Try backup log (database) with truncate_only
> > >
> > > Then shrink the log file.
> > >
> > >
> > > "cjobes" wrote:
> > >
> > > > Hi all,
> > > >
> > > > I have an SQL2000 running on a W2K server. The mdf file is about
140MB
> > but
> > > > the ldf shows over 11GB. I ran backups. The full DB backup is 138MB
and
> > the
> > > > Transaction log backup right after is 126MB. I tried shrinking and
> > > > everything else I could think of. I even deleted the DB and restored
it
> > from
> > > > a full DB backup. The ldf still comes up with 11GB. I have another
> > > > installation of the same database on another server where the ldf
shows
> > a
> > > > normal size (40MB).
> > > >
> > > > Can anybody tell me what is going on? During the restore I moved the
> > data
> > > > files to another disk that has lots of space but there is something
> > wrong
> > > > here and I would like to get to the bottom of it.
> > > >
> > > > Thanks for your help,
> > > >
> > > > Claus
> > > >
> > > >
> > > >
> >
> >
> >sql
Monday, March 26, 2012
HELP! Restore SQL7 database to SQL2000 SP3a problem
SP3a is installed.
Is there a procedure?
Yes.
First obtain three chickens and a mule...
Seriously, a SQL 7.0 database can be restored (or attached after detach) to
a SQL 2000 instance using the normal restore database commands or Enterprise
Manager. There is no difference in restoring a 7.0 backup to a SQL 2000
server than in restoring a SQL 2000 backup to a SQL 2000 server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegr oups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>
|||You can use a RESTORE DATABASE command. For example:
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH REPLACE
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegr oups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>
|||Thanks Geoff,
The error I am getting is:
SQL-DMO (ODBC SQLstate: 42000)
The media set for database (name) has 2 family members but only 1 are
provided. All members must be provided. Restore database is
terminating... "
S.
|||Evidently your backup spanned multiple backup sets (files or tapes). You
need to specify all backup sets in a single restore command:
RESTORE DATABASE MySQL7Database
FROM C:\Backups\MySQL7Backup1.bak,C:\Backups\MySQL7Back up2.bak
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132773276.165857.173410@.z14g2000cwz.googlegr oups.com...
> Thanks Geoff,
> The error I am getting is:
> SQL-DMO (ODBC SQLstate: 42000)
> The media set for database (name) has 2 family members but only 1 are
> provided. All members must be provided. Restore database is
> terminating... "
> S.
>
|||Geoff, thanks
No, this was not the case. The database backup file was created in SQL
Server 7 (SP4) and zips to 2MB after backup.
On selection of the database file it's backup set properties can be
seen. I selected to restore All, and there is no existing database over
which it will overwrite. So it is hard to see why it should be asking
for another file.
Are there any other settings that can be changed? I am having a look...
S.
|||Well guys, thanks very much anyway. I have tried using Dan Guzman's
suggestion of running a command script, but still no good - same error
type. I suppose that somehow there must be a corruption, as whenever I
have previously backed up the system it has been plain sailing to get
it back in again. But that was in SQL 7.
I shall get another backup (500 Km away!), unless you can advise more.
|||It might be that the actual backup spans multiple files, even though you
have only one of the members. You can see if this is your situation by
examining FamilyCount of the RESTORE LABELONLY results:
RESTORE LABELONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegro ups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>
|||The backup is striped. I never seen a case where SQL Server said it is, but it wasn't. Use RESTORE
LABELONLY as suggested by Dan, and you will see the number of members. I.e., you need to get the
other backup files, or a new non-striped backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegro ups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>
HELP! Restore SQL7 database to SQL2000 SP3a problem
SP3a is installed.
Is there a procedure?Yes.
First obtain three chickens and a mule...
Seriously, a SQL 7.0 database can be restored (or attached after detach) to
a SQL 2000 instance using the normal restore database commands or Enterprise
Manager. There is no difference in restoring a 7.0 backup to a SQL 2000
server than in restoring a SQL 2000 backup to a SQL 2000 server.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegroups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>|||You can use a RESTORE DATABASE command. For example:
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH REPLACE
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegroups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>|||Thanks Geoff,
The error I am getting is:
SQL-DMO (ODBC SQLstate: 42000)
The media set for database (name) has 2 family members but only 1 are
provided. All members must be provided. Restore database is
terminating... "
S.|||Evidently your backup spanned multiple backup sets (files or tapes). You
need to specify all backup sets in a single restore command:
RESTORE DATABASE MySQL7Database
FROM C:\Backups\MySQL7Backup1.bak,C:\Backups\MySQL7Backup2.bak
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132773276.165857.173410@.z14g2000cwz.googlegroups.com...
> Thanks Geoff,
> The error I am getting is:
> SQL-DMO (ODBC SQLstate: 42000)
> The media set for database (name) has 2 family members but only 1 are
> provided. All members must be provided. Restore database is
> terminating... "
> S.
>|||Geoff, thanks
No, this was not the case. The database backup file was created in SQL
Server 7 (SP4) and zips to 2MB after backup.
On selection of the database file it's backup set properties can be
seen. I selected to restore All, and there is no existing database over
which it will overwrite. So it is hard to see why it should be asking
for another file.
Are there any other settings that can be changed? I am having a look...
S.|||Well guys, thanks very much anyway. I have tried using Dan Guzman's
suggestion of running a command script, but still no good - same error
type. I suppose that somehow there must be a corruption, as whenever I
have previously backed up the system it has been plain sailing to get
it back in again. But that was in SQL 7.
I shall get another backup (500 Km away!), unless you can advise more.|||It might be that the actual backup spans multiple files, even though you
have only one of the members. You can see if this is your situation by
examining FamilyCount of the RESTORE LABELONLY results:
RESTORE LABELONLY
FROM DISK='C:\Backups\MyDatabase.bak'
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegroups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>|||The backup is striped. I never seen a case where SQL Server said it is, but
it wasn't. Use RESTORE
LABELONLY as suggested by Dan, and you will see the number of members. I.e.,
you need to get the
other backup files, or a new non-striped backup.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegroups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>
HELP! Restore SQL7 database to SQL2000 SP3a problem
SP3a is installed.
Is there a procedure?Yes.
First obtain three chickens and a mule...
Seriously, a SQL 7.0 database can be restored (or attached after detach) to
a SQL 2000 instance using the normal restore database commands or Enterprise
Manager. There is no difference in restoring a 7.0 backup to a SQL 2000
server than in restoring a SQL 2000 backup to a SQL 2000 server.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegroups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>|||You can use a RESTORE DATABASE command. For example:
RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyDatabase.bak'
WITH REPLACE
See the Books Online for details.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132770162.841008.102880@.o13g2000cwo.googlegroups.com...
>I have a small database that urgently needs restoring under SQL 2000.
> SP3a is installed.
> Is there a procedure?
>|||Thanks Geoff,
The error I am getting is:
SQL-DMO (ODBC SQLstate: 42000)
The media set for database (name) has 2 family members but only 1 are
provided. All members must be provided. Restore database is
terminating... "
S.|||Evidently your backup spanned multiple backup sets (files or tapes). You
need to specify all backup sets in a single restore command:
RESTORE DATABASE MySQL7Database
FROM C:\Backups\MySQL7Backup1.bak,C:\Backups\MySQL7Backup2.bak
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132773276.165857.173410@.z14g2000cwz.googlegroups.com...
> Thanks Geoff,
> The error I am getting is:
> SQL-DMO (ODBC SQLstate: 42000)
> The media set for database (name) has 2 family members but only 1 are
> provided. All members must be provided. Restore database is
> terminating... "
> S.
>|||Geoff, thanks
No, this was not the case. The database backup file was created in SQL
Server 7 (SP4) and zips to 2MB after backup.
On selection of the database file it's backup set properties can be
seen. I selected to restore All, and there is no existing database over
which it will overwrite. So it is hard to see why it should be asking
for another file.
Are there any other settings that can be changed? I am having a look...
S.|||Well guys, thanks very much anyway. I have tried using Dan Guzman's
suggestion of running a command script, but still no good - same error
type. I suppose that somehow there must be a corruption, as whenever I
have previously backed up the system it has been plain sailing to get
it back in again. But that was in SQL 7.
I shall get another backup (500 Km away!), unless you can advise more.|||It might be that the actual backup spans multiple files, even though you
have only one of the members. You can see if this is your situation by
examining FamilyCount of the RESTORE LABELONLY results:
RESTORE LABELONLY
FROM DISK='C:\Backups\MyDatabase.bak'
--
Hope this helps.
Dan Guzman
SQL Server MVP
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegroups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>|||The backup is striped. I never seen a case where SQL Server said it is, but it wasn't. Use RESTORE
LABELONLY as suggested by Dan, and you will see the number of members. I.e., you need to get the
other backup files, or a new non-striped backup.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"proverbs3" <seumas.maclaren@.atkinsglobal.com> wrote in message
news:1132774484.545986.59230@.o13g2000cwo.googlegroups.com...
> Geoff, thanks
> No, this was not the case. The database backup file was created in SQL
> Server 7 (SP4) and zips to 2MB after backup.
> On selection of the database file it's backup set properties can be
> seen. I selected to restore All, and there is no existing database over
> which it will overwrite. So it is hard to see why it should be asking
> for another file.
> Are there any other settings that can be changed? I am having a look...
> S.
>sql
Friday, March 23, 2012
Help! Oracle linked server not working
old config:
windows2000
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
new config:
windows2003
sql2000 sp4
mdac 2.8
oracle client 9.2 with latest patches
the oracle db is 9.2.
i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
returned 0x80004005:]
i've scoured the internet and found numerous solutions to this, none of
which work.
i found an article in oracle technet that said to redo all permissions
on the oraclehome
directory. that didn't work.
i made sure the registry entries pointed to the correct oracle dll's.
no help.
i tried the 10g client with latest patch. no help.
i deleted and readded the linked server. no help.
i can use sqlplus or netmanager and connect to the oracle db just fine
from the sql server.
i don't know what else to try.
i'm wondering if it's windows2003 that's screwing things up.
has anybody been able to link to an oracle server from a win2003 server
running sql server sp4?Some additional questions:
What platform is the Oracle database?
Is connection pooling being used?
What language is doing the call or are you doing linked servers (four level
table qualifier)?
That particular error has a lot of possibles, as you found. Don't want to
re-hash what you have already ruled out.
--
Joseph R.P. Maloney, CSP,CCP,CDP
"ch" wrote:
> we upgraded servers and now i can't link to an oracle server
> old config:
> windows2000
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> new config:
> windows2003
> sql2000 sp4
> mdac 2.8
> oracle client 9.2 with latest patches
>
> the oracle db is 9.2.
> i get the error 7399: OLE DB provider 'MSDAORA' reported an error.
> OLE DB error trace [OLE/DB Provider 'MSDAORA' IDBInitialize::Initialize
> returned 0x80004005:]
> i've scoured the internet and found numerous solutions to this, none of
> which work.
> i found an article in oracle technet that said to redo all permissions
> on the oraclehome
> directory. that didn't work.
> i made sure the registry entries pointed to the correct oracle dll's.
> no help.
> i tried the 10g client with latest patch. no help.
> i deleted and readded the linked server. no help.
> i can use sqlplus or netmanager and connect to the oracle db just fine
> from the sql server.
> i don't know what else to try.
> i'm wondering if it's windows2003 that's screwing things up.
> has anybody been able to link to an oracle server from a win2003 server
> running sql server sp4?
>
Monday, March 19, 2012
HELP! How to find the owner of an object?
because the login ID owns objects in one or more databases.
How can I find out what objects does the login ID own?
Thank youThis might help:
select * from information_schema.tables where table_schema <> 'dbo'
or take a look at sysobjects and sysusers.|||use northwind go
SELECT
so.name AS 'ObjectName'
,su.name AS 'OwnerName'
FROM sysobjects so
JOIN
sysusers su
ON so.uid = su.uid
WHERE xtype <> 's'
ORDER BY 2
by the way. SQL Formatting brought to you by SQLInForm.com|||select user_name(objectproperty(id, 'OwnerID')) + '.' + name
from sysobjects
order by objectproperty(id, 'OwnerID'), name|||you just couldnt leave well enough alone, huh?
and after i quote you all the time.
Scott Davis
"If you are not part of the solution, you are probably 1/5th of a suspension" RDjabarov
Wednesday, March 7, 2012
HELP! - Cast from DBNull when there is Data
Hi,
I have built a few pages and a stored procedure and a class on the back of a SQL2000 dbase. and I get the following error:
Cast from type 'DBNull' to type 'String' is not valid.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.InvalidCastException: Cast from type 'DBNull' to type 'String' is not valid.
Source Error:
Line 111: Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATALine 112:Line 113: myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)Line 114: myWorkJobs.WorkID = CStr(parameterWorkID.Value)Line 115: myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()
Source File:C:\Inetpub\wwwroot\Commerce\Components\WorkJobs.vb Line:113
My Database has 1 line of data (for testing) and all fields are populated. I am Querying a column called IndividualID which has a value of 3425243 at the moment. This is hardcoded in the aspx.vb at the moment.
ASPX VB:
Public Class WorkRequest
Inherits System.Web.UI.Page
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub btnEnter_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEnter.Click
'for now, send this value (stored in dbase under individualID) to the querystring
Dim IndividualID As String = "3425243"
Response.Redirect("WorkRequestMain.aspx?IndividualID=" & IndividualID)
End Sub
End Class
COMPONENTS\WorkJobs.vb (This is the class file)
Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Namespace ASPNET.StarterKit.Commerce
Public Class WorkJobsDATA
Public CustomerID As String
Public WorkID As String
Public DateOfQuote As String
Public QuoteAmount As String
Public Title As Decimal
Public FirstName As String
Public Surname As String
Public FirstLine As String
Public District As String
Public Town As String
Public Postcode As String
Public Telephone As String
Public Requirements As String
Public WorkRequired As String
Public EmailAddress As String
End Class
Public Class WorkJobs
Public Function GetWorkDetails(ByVal IndividualID As String) As WorkJobsDATA
Dim myConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim myCommand As SqlCommand = New SqlCommand("SP_PendingQuotes", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim parameterIndividualID As SqlParameter = New SqlParameter("@.IndividualID", SqlDbType.NVarChar, 50)
parameterIndividualID.Value = IndividualID
myCommand.Parameters.Add(parameterIndividualID)
Dim parameterCustomerID As SqlParameter = New SqlParameter("@.CustomerID", SqlDbType.BigInt, 8)
parameterCustomerID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterCustomerID)
Dim parameterWorkID As SqlParameter = New SqlParameter("@.WorkID", SqlDbType.NVarChar, 50)
parameterWorkID.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkID)
Dim parameterDateOfQuote As SqlParameter = New SqlParameter("@.DateOfQuote", SqlDbType.DateTime, 8)
parameterDateOfQuote.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDateOfQuote)
Dim parameterQuoteAmount As SqlParameter = New SqlParameter("@.QuoteAmount", SqlDbType.Money, 8)
parameterQuoteAmount.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterQuoteAmount)
Dim parameterTitle As SqlParameter = New SqlParameter("@.Title", SqlDbType.NVarChar, 50)
parameterTitle.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTitle)
Dim parameterFirstName As SqlParameter = New SqlParameter("@.FirstName", SqlDbType.NVarChar, 50)
parameterFirstName.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstName)
Dim parameterSurname As SqlParameter = New SqlParameter("@.Surname", SqlDbType.NVarChar, 50)
parameterSurname.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterSurname)
Dim parameterFirstLine As SqlParameter = New SqlParameter("@.FirstLine ", SqlDbType.NVarChar, 50)
parameterFirstLine.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterFirstLine)
Dim parameterDistrict As SqlParameter = New SqlParameter("@.District", SqlDbType.NVarChar, 50)
parameterDistrict.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterDistrict)
Dim parameterTown As SqlParameter = New SqlParameter("@.Town", SqlDbType.NVarChar, 50)
parameterTown.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTown)
Dim parameterPostcode As SqlParameter = New SqlParameter("@.Postcode", SqlDbType.NVarChar, 50)
parameterPostcode.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterPostcode)
Dim parameterTelephone As SqlParameter = New SqlParameter("@.Telephone", SqlDbType.NVarChar, 50)
parameterTelephone.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterTelephone)
Dim parameterRequirements As SqlParameter = New SqlParameter("@.Requirements", SqlDbType.NVarChar, 3500)
parameterRequirements.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterRequirements)
Dim parameterWorkRequired As SqlParameter = New SqlParameter("@.WorkRequired", SqlDbType.NVarChar, 3500)
parameterWorkRequired.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterWorkRequired)
Dim parameterEmailAddress As SqlParameter = New SqlParameter("@.EmailAddress", SqlDbType.NVarChar, 100)
parameterEmailAddress.Direction = ParameterDirection.Output
myCommand.Parameters.Add(parameterEmailAddress)
myConnection.Open()
myCommand.ExecuteNonQuery()
myConnection.Close()
parameterEmailAddress.Value.GetType()
Dim myWorkJobs As WorkJobsDATA = New WorkJobsDATA
myWorkJobs.CustomerID = CStr(parameterCustomerID.Value)
myWorkJobs.WorkID = CStr(parameterWorkID.Value)
myWorkJobs.DateOfQuote = CStr(parameterDateOfQuote.Value).Trim()
myWorkJobs.Title = CStr(parameterTitle.Value).Trim()
myWorkJobs.FirstName = CStr(parameterFirstName.Value).Trim()
myWorkJobs.Surname = CStr(parameterSurname.Value).Trim()
myWorkJobs.FirstLine = CStr(parameterFirstLine.Value).Trim()
myWorkJobs.District = CStr(parameterDistrict.Value).Trim()
myWorkJobs.Town = CStr(parameterTown.Value).Trim()
myWorkJobs.Postcode = CStr(parameterPostcode.Value).Trim()
myWorkJobs.Telephone = CStr(parameterTelephone.Value).Trim()
myWorkJobs.Requirements = CStr(parameterRequirements.Value).Trim()
myWorkJobs.WorkRequired = CStr(parameterWorkRequired.Value).Trim()
myWorkJobs.EmailAddress = CStr(parameterEmailAddress.Value).Trim()
Return myWorkJobs
End Function
End Class
End Namespace
And finally my stored procedure:
CREATE Procedure SP_PendingQuotes
(
@.IndividualID nvarchar,
@.CustomerID bigint OUTPUT,
@.WorkID nvarchar(50) OUTPUT,
@.DateOfQuote datetime OUTPUT,
@.QuoteAmount money OUTPUT,
@.Title nvarchar(50) OUTPUT,
@.FirstName nvarchar(50) OUTPUT,
@.Surname nvarchar(50) OUTPUT,
@.FirstLine nvarchar(50) OUTPUT,
@.District nvarchar(50) OUTPUT,
@.Town nvarchar(50) OUTPUT,
@.Postcode nvarchar(50) OUTPUT,
@.Telephone nvarchar(50) OUTPUT,
@.Requirements nvarchar(3500) OUTPUT,
@.WorkRequired nvarchar(3500) OUTPUT,
@.EmailAddress nvarchar(100) OUTPUT
)
AS
SELECT
@.IndividualID = IndividualID,
@.CustomerID = CustomerID,
@.WorkID = WorkID,
@.DateOfQuote = DateOfQuote,
@.QuoteAmount = QuoteAmount,
@.Title = Title,
@.FirstName = FirstName,
@.Surname = Surname,
@.FirstLine = FirstLine,
@.District = District,
@.Town = Town,
@.Postcode = Postcode,
@.Telephone = Telephone,
@.Requirements = Requirements,
@.WorkRequired = WorkRequired,
@.EmailAddress = EmailAddress
FROM
PendingQuotes
WHERE
IndividualID = @.IndividualID
GO
Any ideas anyone?
I appreciate this is a big amount of data, but if anyone wants to chat to me i'm available on MSN Messenger underwolvokid@.msn.com
The problem is in the stored procedure, you've defined @.IndividualID as nvarchar, specify the length, as it is defaulting to 1. Your query then returns no values because there is no record where IndividualID='3'|||
Call me muppett! It all becomes a blur after you go over and over everything a million times trying to find out whats wrong.
Thanks very much. It works now!
Monday, February 27, 2012
HELP!
running SQL 7 to connect to the database. It was slow, so
I upgraded my computer to SQL2000 to test. Now I can't
get connected to the database AND my clients can't connect
either.
Hi,
Can you please give some more information.
I upgraded my computer to SQL2000 to test ??
Did you upgraded your client sql7 software to SQl 2000 in your PC. What is
that test?
Thanks
Hari
MCDBA
"AGFSG" <anonymous@.discussions.microsoft.com> wrote in message
news:2301201c45dfc$78c0cea0$a401280a@.phx.gbl...
> I upgraded my SQL Server to 2000. My clients were all
> running SQL 7 to connect to the database. It was slow, so
> I upgraded my computer to SQL2000 to test. Now I can't
> get connected to the database AND my clients can't connect
> either.
|||What error messages do u get? Was the upgrade successful? You might want to
start by checking the SQL Error logs in the program Files folder, sQL
Server >> LOG folder and look for any error messages that might indicate
basic server problems. And then we'll do other troubleshooting after we
mjake sure the sql server is fine.
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.