Can we include a variable field value on the table header.

Can we include a variable field value on the table header.

I am doing a page break on every date change and also set the property of he table header to repeat on each page. By doing this ,I am able to get the table header on each page even on page break. I want the changed date to be displayed on the every page break and in the table header.

Please help.

You should be able to do this without issue. Assuming you have a field which you are pulling from that shows the date, you could do something like this as an expression in the table header.

="Report Header - " & Fields!Date.Value

Hope this helps.

Can we drill down reports in a another different pages with different layouts?

Hi, all experts here,

Thank you for your kind attention.

I am wondering if we can drill down reports in a different page with different layouts and columns? e.g. I have too many columns desired to see in a report, so I want to direct the users to another page with different layouts and columns, so the first view in the report wont display so many columns in a page. Is it possible to make it in SQL Server 2005 Reporting Services? And if it is possible then how? Hope my question is clear for your help.

Thanks a lot in advance and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

Yes, you can use the Action property of your column textbox. When you look at the options, you can either jump to a new report or to a bookmark or to an URL. In your case, you may like to use Jump to Bookmark. Use BookmarkID for the tables or lists which are displayed in subsequent pages and use that ID to jump to, from your first page columns.

Shyam

|||

Hi, Shyam,

Thank you for your kind help and advice.

Yes, in the navigation property (action property), I can set the actions accordingly. But the pages I am going to will still get the same columns as in the first page. What I want is drill down to a page with different columns displayed? Is that possible? Thank you very much. And I am looking forward to hearing from you further.

With best regards,

Yours sincerely,

|||

In that case, you may have to navigate to a subreport which will have different layout. Create a subreport and jump to this subreport providing the necessary parameters.

Shyam

|||

Hi, Shyam,

Thanks a lot for your kind help.

With best regards,

Yours sincerely,

Can we change the page size of crstal report

I need to set the size of the page 8.5*3.5
can i change it.I dont know about the Developer end, but you can change the size of the paper at the CMC end.

In the CMC when you to go the report options and Process tab, there youcan find the page setup. In that option if you check the customize option u can fix the page length and width.

all the best

Can torn page detection fire from existing corruption?

Does torn page detection only show new errors or errors during restore or
can it error due to a torn page that may have been in the database for a
while.
Thanks
Paul
AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
doesn't matter when the page was torn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Does torn page detection only show new errors or errors during restore or can it error due to a
> torn page that may have been in the database for a while.
> Thanks
> Paul
>
|||Thanks Tibor.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>
|||I believe it is only when the page is written assuming you have torn page
detection turned on at the time of the write.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>
|||Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all 1)
are at read time. At least that is how I read
http://www.microsoft.com/technet/pro...lIObasics.mspx (search for "torn")
and BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
option is set, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I believe it is only when the page is written assuming you have torn page detection turned on at
>the time of the write.
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>
|||The check is only performed when the dboption is set, and only for pages
that have been written out since the option was set.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Yes, the bits are flipped at write time (assuming db option is on). But
> detection (all 0 or all 1) are at read time. At least that is how I read
> http://www.microsoft.com/technet/pro...lIObasics.mspx
> (search for "torn") and BOL
> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL% 20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
> I'm not 100% positive whether checking of inconsistent bits are always
> performed or only when db option is set, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>
|||Ahh, thanks. I assume there must be some flag in the page header saying something like "torn pages
flagged/flipped" (i.e. the page was written while detection was on) by which the code can determine
whether to check for tp or not?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> The check is only performed when the dboption is set, and only for pages that have been written
> out since the option was set.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>
|||So torn page detection wil activate only for fresh corruption.
That helps ie our problems are current.
We are in one of those situations where the db is corrupting but our disks
and controllers say all is well. We have turned off caching (batt backed)
etc.
No luck. We have installed sp4 and -T818. No errors from that.
We have run the new sqliostress. No lost writes, stale reads etc but still
the corruptions continue.
System, dell pe 8450 raid 10, had been stable for over 2 years.
We've built another box today and will move tonight.
Wish us luck!
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Ahh, thanks. I assume there must be some flag in the page header saying
> something like "torn pages flagged/flipped" (i.e. the page was written
> while detection was on) by which the code can determine whether to check
> for tp or not?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>

Can torn page detection fire from existing corruption?

Does torn page detection only show new errors or errors during restore or
can it error due to a torn page that may have been in the database for a
while.
Thanks
PaulAFAIK, torn page detection is done every tome a page is accessed from disk.
This mean that it
doesn't matter when the page was torn.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Does torn page detection only show new errors or errors during restore or
can it error due to a
> torn page that may have been in the database for a while.
> Thanks
> Paul
>|||Thanks Tibor.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>|||I believe it is only when the page is written assuming you have torn page
detection turned on at the time of the write.
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>|||Yes, the bits are flipped at write time (assuming db option is on). But dete
ction (all 0 or all 1)
are at read time. At least that is how I read
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[/u
rl] (search for "torn")
and BOL
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\cr
eatedb.chm::/cm_8_des_03_6ohf.htm).
I'm not 100% positive whether checking of inconsistent bits are always perfo
rmed or only when db
option is set, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I believe it is only when the page is written assuming you have torn page d
etection turned on at
>the time of the write.
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>|||The check is only performed when the dboption is set, and only for pages
that have been written out since the option was set.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Yes, the bits are flipped at write time (assuming db option is on). But
> detection (all 0 or all 1) are at read time. At least that is how I read
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx[
/url]
> (search for "torn") and BOL
> (mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\
createdb.chm::/cm_8_des_03_6ohf.htm).
> I'm not 100% positive whether checking of inconsistent bits are always
> performed or only when db option is set, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>|||Ahh, thanks. I assume there must be some flag in the page header saying some
thing like "torn pages
flagged/flipped" (i.e. the page was written while detection was on) by which
the code can determine
whether to check for tp or not?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> The check is only performed when the dboption is set, and only for pages t
hat have been written
> out since the option was set.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights
.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>|||So torn page detection wil activate only for fresh corruption.
That helps ie our problems are current.
We are in one of those situations where the db is corrupting but our disks
and controllers say all is well. We have turned off caching (batt backed)
etc.
No luck. We have installed sp4 and -T818. No errors from that.
We have run the new sqliostress. No lost writes, stale reads etc but still
the corruptions continue.
System, dell pe 8450 raid 10, had been stable for over 2 years.
We've built another box today and will move tonight.
Wish us luck!
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Ahh, thanks. I assume there must be some flag in the page header saying
> something like "torn pages flagged/flipped" (i.e. the page was written
> while detection was on) by which the code can determine whether to check
> for tp or not?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>

Can torn page detection fire from existing corruption?

Does torn page detection only show new errors or errors during restore or
can it error due to a torn page that may have been in the database for a
while.
Thanks
PaulAFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
doesn't matter when the page was torn.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Does torn page detection only show new errors or errors during restore or can it error due to a
> torn page that may have been in the database for a while.
> Thanks
> Paul
>|||Thanks Tibor.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||I believe it is only when the page is written assuming you have torn page
detection turned on at the time of the write.
--
Andrew J. Kelly SQL MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
> AFAIK, torn page detection is done every tome a page is accessed from
> disk. This mean that it doesn't matter when the page was torn.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or
>> can it error due to a torn page that may have been in the database for a
>> while.
>> Thanks
>> Paul
>>
>|||Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all 1)
are at read time. At least that is how I read
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for "torn")
and BOL
(mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
option is set, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>I believe it is only when the page is written assuming you have torn page detection turned on at
>the time of the write.
> --
> Andrew J. Kelly SQL MVP
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to a
>> torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>|||The check is only performed when the dboption is set, and only for pages
that have been written out since the option was set.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
> Yes, the bits are flipped at write time (assuming db option is on). But
> detection (all 0 or all 1) are at read time. At least that is how I read
> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
> (search for "torn") and BOL
> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
> I'm not 100% positive whether checking of inconsistent bits are always
> performed or only when db option is set, though...
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page
>>detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore
>> or can it error due to a torn page that may have been in the database
>> for a while.
>> Thanks
>> Paul
>>
>>
>|||Ahh, thanks. I assume there must be some flag in the page header saying something like "torn pages
flagged/flipped" (i.e. the page was written while detection was on) by which the code can determine
whether to check for tp or not?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
> The check is only performed when the dboption is set, and only for pages that have been written
> out since the option was set.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when db
>> option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on at
>>the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during restore or can it error due to
>> a torn page that may have been in the database for a while.
>> Thanks
>> Paul
>>
>>
>>
>|||So torn page detection wil activate only for fresh corruption.
That helps ie our problems are current.
We are in one of those situations where the db is corrupting but our disks
and controllers say all is well. We have turned off caching (batt backed)
etc.
No luck. We have installed sp4 and -T818. No errors from that.
We have run the new sqliostress. No lost writes, stale reads etc but still
the corruptions continue.
System, dell pe 8450 raid 10, had been stable for over 2 years.
We've built another box today and will move tonight.
Wish us luck!
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> Ahh, thanks. I assume there must be some flag in the page header saying
> something like "torn pages flagged/flipped" (i.e. the page was written
> while detection was on) by which the code can determine whether to check
> for tp or not?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages
>> that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But
>> detection (all 0 or all 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from
>> disk. This mean that it doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Does torn page detection only show new errors or errors during
>> restore or can it error due to a torn page that may have been in the
>> database for a while.
>> Thanks
>> Paul
>>
>>
>>
>>
>|||> So torn page detection wil activate only for fresh corruption.
That is not how I read Paul's statement. Assuming you have had torn page detection on for the
lifetime of the database. Then, as I understand it, a torn page could have happened a year ago.
Assuming you haven't read that page since it happened until now, you wouldn't have discovered it
until now.
Above scenario (again, as I understand how it works) is not very likely, though.
First, torn pages should be detected by DBCC CHECKDB, which I assume you do regularly.
Also, a torn page is most likely to occur if the system is shut down unexpectedly, and during the
following startup and the recovery phase, the pages which are torn would be very likely to be read
as they are likely to be involved in the recovery work.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
> So torn page detection wil activate only for fresh corruption.
> That helps ie our problems are current.
> We are in one of those situations where the db is corrupting but our disks and controllers say all
> is well. We have turned off caching (batt backed) etc.
> No luck. We have installed sp4 and -T818. No errors from that.
> We have run the new sqliostress. No lost writes, stale reads etc but still the corruptions
> continue.
> System, dell pe 8450 raid 10, had been stable for over 2 years.
> We've built another box today and will move tonight.
> Wish us luck!
> Paul
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying something like "torn
>> pages flagged/flipped" (i.e. the page was written while detection was on) by which the code can
>> determine whether to check for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for pages that have been written
>> out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on). But detection (all 0 or all
>> 1) are at read time. At least that is how I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx (search for
>> "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always performed or only when
>> db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn page detection turned on
>>at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
>> news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>> AFAIK, torn page detection is done every tome a page is accessed from disk. This mean that it
>> doesn't matter when the page was torn.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during restore or can it error due
>>> to a torn page that may have been in the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>
>>
>>
>|||I think I may have not been clear. We did not have torn page on but have
been doing a full checkdb/checkalloc using sqlmaint, once a week.
We did not have torn page on as our dell perc3/dc (rebadged megaraid 1600)
have battery backup and we have a hefty ups.
To try to resolve is we had a hardware problem we turned off write bac
cache. No luck we then installed sql sp4 and added -t818. This did not show
any errors.We then enabled torn page. We got a detection after a few hours.
I'm at a loss as to why the dell raid event log shows no errors. I've done a
raid consistency test and it came up clean. chkdsk is OK.
The new sqliostress looks like it an excellent simulation. I ran it with the
extra i/o checking flags. Again clean.
Well we should know by tomorrow night if it was hardware. If it is, then we
may have a very expensive door stop. We'll nuke the box and rebuild it but
I'd feel scared to go back to a box that shows no errors but corrupts data.
I guess we'll run all dells diags for an age, memory etc. This was my dream
machine. I wanted to spec out a great server. 24 small disks, raid 10, 6
channels, 128Mb cache per controller etc.
Perhaps the secret is keep it simple, two big mirrors and just throw a ton
of memory in.
What a fortnight.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23FbYj6JjFHA.1044@.tk2msftngp13.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
> That is not how I read Paul's statement. Assuming you have had torn page
> detection on for the lifetime of the database. Then, as I understand it, a
> torn page could have happened a year ago. Assuming you haven't read that
> page since it happened until now, you wouldn't have discovered it until
> now.
> Above scenario (again, as I understand how it works) is not very likely,
> though.
> First, torn pages should be detected by DBCC CHECKDB, which I assume you
> do regularly.
> Also, a torn page is most likely to occur if the system is shut down
> unexpectedly, and during the following startup and the recovery phase, the
> pages which are torn would be very likely to be read as they are likely to
> be involved in the recovery work.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
> news:%23yLeVqJjFHA.2484@.TK2MSFTNGP15.phx.gbl...
>> So torn page detection wil activate only for fresh corruption.
>> That helps ie our problems are current.
>> We are in one of those situations where the db is corrupting but our
>> disks and controllers say all is well. We have turned off caching (batt
>> backed) etc.
>> No luck. We have installed sp4 and -T818. No errors from that.
>> We have run the new sqliostress. No lost writes, stale reads etc but
>> still the corruptions continue.
>> System, dell pe 8450 raid 10, had been stable for over 2 years.
>> We've built another box today and will move tonight.
>> Wish us luck!
>> Paul
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:uK7U$GJjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> Ahh, thanks. I assume there must be some flag in the page header saying
>> something like "torn pages flagged/flipped" (i.e. the page was written
>> while detection was on) by which the code can determine whether to check
>> for tp or not?
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
>> news:ujX3$2IjFHA.2180@.TK2MSFTNGP15.phx.gbl...
>> The check is only performed when the dboption is set, and only for
>> pages that have been written out since the option was set.
>> --
>> Paul Randal
>> Dev Lead, Microsoft SQL Server Storage Engine
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:%23WoaD2DjFHA.1232@.TK2MSFTNGP15.phx.gbl...
>> Yes, the bits are flipped at write time (assuming db option is on).
>> But detection (all 0 or all 1) are at read time. At least that is how
>> I read
>> http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlIObasics.mspx
>> (search for "torn") and BOL
>> (mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\createdb.chm::/cm_8_des_03_6ohf.htm).
>> I'm not 100% positive whether checking of inconsistent bits are always
>> performed or only when db option is set, though...
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:OTUksY9iFHA.1968@.TK2MSFTNGP14.phx.gbl...
>>I believe it is only when the page is written assuming you have torn
>>page detection turned on at the time of the write.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
>> wrote in message news:OMf$BV8iFHA.2644@.TK2MSFTNGP09.phx.gbl...
>>> AFAIK, torn page detection is done every tome a page is accessed
>>> from disk. This mean that it doesn't matter when the page was torn.
>>>
>>> --
>>> Tibor Karaszi, SQL Server MVP
>>> http://www.karaszi.com/sqlserver/default.asp
>>> http://www.solidqualitylearning.com/
>>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>>
>>>
>>> "Paul Cahill" <xyzpaul.xyzcahill@.dsl.pipex.com> wrote in message
>>> news:O4XO3C8iFHA.3012@.TK2MSFTNGP12.phx.gbl...
>>> Does torn page detection only show new errors or errors during
>>> restore or can it error due to a torn page that may have been in
>>> the database for a while.
>>>
>>> Thanks
>>> Paul
>>>
>>>
>>>
>>
>>
>>
>>
>sql

Can this Stored Proc be more efficient?

Hey all,
Figured I'd get everyone's input on this. The stored proc below works
fine, no errors, however the ASP.NET page which calls it takes forever
to load (it averages 25 seconds per search). Anyone have any insight on
how I can boost the speed? 25-35 seconds is too dang long. For those
who want a full perspective: I have a sortable datagrid with custom
paging. On the site there is a textbox where one can search by
"containernum." As you can see below, the core of the search uses LIKE
'%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
full-text index on the containernum field but perhaps I did it wrong
(it's the first time I've used that feature) because there appears to
be no gain in speed. Help! :-)
BTW, there are about a million records in my test table, the real table
has almost 5 million records, so I can just imagine the slowdown on
that one. :-(
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.SortField nvarchar(50),
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
CREATE TABLE #TempTable
(
ID int IDENTITY PRIMARY KEY,
uid uniqueidentifier NOT NULL,
event nvarchar(6) NOT NULL,
bookingnum nvarchar(50) NOT NULL,
vanowner nvarchar(6) NOT NULL,
containernum nvarchar(8) NULL,
tcn nvarchar(20) NULL,
poe nvarchar(50) NULL,
pod nvarchar(6) NULL,
shipname nvarchar(50) NULL,
vdn nvarchar(8) NULL,
eventlocation nvarchar(50) NOT NULL,
pcfn nvarchar(8) NULL
)
INSERT INTO #TempTable
(
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
dbo.new315_itv
WHERE
containernum LIKE '%'+@.con_num+'%'
ORDER BY
CASE
WHEN @.SortField = 'event' THEN event
WHEN @.SortField = 'bookingnum' THEN bookingnum
WHEN @.SortField = 'containernum' THEN containernum
WHEN @.SortField = 'tcn' THEN tcn
WHEN @.SortField = 'poe' THEN poe
WHEN @.SortField = 'pod' THEN pod
WHEN @.SortField = 'shipname' THEN shipname
WHEN @.SortField = 'vdn' THEN vdn
WHEN @.SortField = 'eventlocation' THEN eventlocation
WHEN @.SortField = 'pcfn' THEN pcfn
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
uid,
event,
bookingnum,
vanowner,
containernum,
tcn,
poe,
pod,
shipname,
vdn,
eventlocation,
pcfn
FROM
#TempTable
WHERE
ID > @.FirstRec AND ID < @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM #TempTable
GOANytime you use Like with a % at the beginning of the value, as in
containernum LIKE '%'+@.con_num+'%'
you automatically induce a full table scan. this is why your query is so
slow.
You need to extract the @.con_num portion of the data into a separate column
and index that... change the query so that it uses = instead of like, or at
least so that there is no % at the beginning...
Also the idea you are using of creating a temp table of all the values, and
then extracting only one pages wrth to return over the wire is a good one,
but you cancarry this a step furthur... Instead of using a temp table, use a
table variable, with an identity Primary Key RowNum, and ONLY put the keys
into this table variable... (You are incurring an enormous amount of
overhead right now stuffing ALL the data into the temp table, not just the
data you will eventually return to client)
Declare @.T Table (RowNum Integer Primary Key Identity Not Null,
PK Integer Not Null)
Insert @.T
Select uid
From ....
Then, at the end just use this table variable t o join back to your main
table, based on which StartRownNum and EndRowwNum defines the page you want
SELECT uid,event,bookingnum,
vanowner,containernum,tcn,
poe,pod,shipname,
vdn,eventlocation,pcfn
FROM new315_itv O Join @.T
On T.RowNum = O.uid
WHERE RowNum Between @.FirstRec AND @.LastRec
"roy.anderson@.gmail.com" wrote:

> Hey all,
> Figured I'd get everyone's input on this. The stored proc below works
> fine, no errors, however the ASP.NET page which calls it takes forever
> to load (it averages 25 seconds per search). Anyone have any insight on
> how I can boost the speed? 25-35 seconds is too dang long. For those
> who want a full perspective: I have a sortable datagrid with custom
> paging. On the site there is a textbox where one can search by
> "containernum." As you can see below, the core of the search uses LIKE
> '%'+@.con_num+'%' which is where the slowdown seems to occur. I have a
> full-text index on the containernum field but perhaps I did it wrong
> (it's the first time I've used that feature) because there appears to
> be no gain in speed. Help! :-)
> BTW, there are about a million records in my test table, the real table
> has almost 5 million records, so I can just imagine the slowdown on
> that one. :-(
>
> CREATE PROCEDURE [Get_Data]
> @.CurrentPage int,
> @.PageSize int,
> @.SortField nvarchar(50),
> @.TotalRecords int output,
> @.con_num nvarchar(8)
> AS
> SET NOCOUNT ON
> CREATE TABLE #TempTable
> (
> ID int IDENTITY PRIMARY KEY,
> uid uniqueidentifier NOT NULL,
> event nvarchar(6) NOT NULL,
> bookingnum nvarchar(50) NOT NULL,
> vanowner nvarchar(6) NOT NULL,
> containernum nvarchar(8) NULL,
> tcn nvarchar(20) NULL,
> poe nvarchar(50) NULL,
> pod nvarchar(6) NULL,
> shipname nvarchar(50) NULL,
> vdn nvarchar(8) NULL,
> eventlocation nvarchar(50) NOT NULL,
> pcfn nvarchar(8) NULL
> )
> INSERT INTO #TempTable
> (
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> )
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> dbo.new315_itv
> WHERE
> containernum LIKE '%'+@.con_num+'%'
> ORDER BY
> CASE
> WHEN @.SortField = 'event' THEN event
> WHEN @.SortField = 'bookingnum' THEN bookingnum
> WHEN @.SortField = 'containernum' THEN containernum
> WHEN @.SortField = 'tcn' THEN tcn
> WHEN @.SortField = 'poe' THEN poe
> WHEN @.SortField = 'pod' THEN pod
> WHEN @.SortField = 'shipname' THEN shipname
> WHEN @.SortField = 'vdn' THEN vdn
> WHEN @.SortField = 'eventlocation' THEN eventlocation
> WHEN @.SortField = 'pcfn' THEN pcfn
> END
> DECLARE @.FirstRec int, @.LastRec int
> SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
> SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
> SELECT
> uid,
> event,
> bookingnum,
> vanowner,
> containernum,
> tcn,
> poe,
> pod,
> shipname,
> vdn,
> eventlocation,
> pcfn
> FROM
> #TempTable
> WHERE
> ID > @.FirstRec AND ID < @.LastRec
> SELECT @.TotalRecords = COUNT(*) FROM #TempTable
> GO
>|||Hey CB,
Thanks for the terrific knowledge. I learned something new today! :-)
Having said that... while using a table variable has increased the
performance, it's only saved me 3 or 4 seconds on average. Here's the
weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
slow down the search, but it doesn't. In fact, the opposite occurs!
When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
seconds.
I'm clueless as to why this is occuring. :-( The only thing I can
come up with is that the "containernum" field is a nvarchar and
includes a mishmash of char's and integers, which may be distorting the
scans somehow.|||This is an indication that your quuery optimizer has decided NOT to use the
index on containernum, and is still doing table scan... (There IS an index o
n
containernum , right ?)
This can happen when there are a large number of records which are a "match"
for the criteria you are passing in... If this query the only query you are
running on this table than you might cnsider mking the index on containernum
the clustered index.. That would improve perfoemce when using ...
containernum Where Like @.con_Num + '%'
What xactly is containernum, and what kind of values are stored in there?
"roy.anderson@.gmail.com" wrote:

> Hey CB,
> Thanks for the terrific knowledge. I learned something new today! :-)
> Having said that... while using a table variable has increased the
> performance, it's only saved me 3 or 4 seconds on average. Here's the
> weird thing, you would imagine that using LIKE '%'+@.con_num+'%' would
> slow down the search, but it doesn't. In fact, the opposite occurs!
> When I use LIKE @.con_num+'%' or LIKE '%'+@.con_num the average time is
> 27 seconds. When I use LIKE '%'+@.con_num+'%' the average time is 24
> seconds.
> I'm clueless as to why this is occuring. :-( The only thing I can
> come up with is that the "containernum" field is a nvarchar and
> includes a mishmash of char's and integers, which may be distorting the
> scans somehow.
>|||"containernum" is an nvarchar(8) field and contains various
alphanumeric characters. The length of entries in that field varies
from 1 to 8. I did have an clustered index on "containernum"
originally, but I was getting slow results and one of my coworkers
suggested enabled a Full-Text Index using "containernum." When I did
that it deleted the original "containernum" clustered index and
replaced it with a "UID" clustered index (UID is the PK for table
new315_itv). I'm currently using the full-text index. Should I delete
it and switch back to using the containernum clustered index?
Am I making sense? :-)|||<roy.anderson@.gmail.com> wrote in message
news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> "containernum" is an nvarchar(8) field and contains various
> alphanumeric characters. The length of entries in that field varies
> from 1 to 8. I did have an clustered index on "containernum"
> originally, but I was getting slow results and one of my coworkers
> suggested enabled a Full-Text Index using "containernum." When I did
> that it deleted the original "containernum" clustered index and
> replaced it with a "UID" clustered index (UID is the PK for table
> new315_itv). I'm currently using the full-text index. Should I delete
> it and switch back to using the containernum clustered index?
> Am I making sense? :-)
>
I think CBretana was suggesting that you reconsider you table design.
Specifically, the containernum column seems to contain composite data; the
container number plus whatever the alphabetic data represents. If these
pieces of data were separated out into discreet columns, the query analyzer
could take advantage of the index on the container_number column. In the
alternative, if you are unable to alter the table model, you might consider
creating an indexed view on the new315_itv table that includes a calculated
expression to extract the actual container number from the containernum
column. Here's a proof of concept on how to extract a number from an
alphanumeric string:
DECLARE @.s NVARCHAR(8)
SET @.s = 'abc123de'
SELECT SUBSTRING(
@.s,
PATINDEX('%[0-9]%',@.s),
CASE PATINDEX('%[0-9]',@.s)
WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
END
)
Also, have you considered using VARCHAR instead of NVARCHAR for your textual
data. NVARCHAR requires twice the storage of VARCHAR an should only be used
if your textual data includes Unicode characters. Here's an article:
http://aspfaq.com/show.asp?id=2354
Finally, here's an article that compares various methods of paging through a
recordset. It includes an example of a stored procedure that makes use of a
temp table as well as other stored procedure examples with better
performance.
http://aspfaq.com/show.asp?id=2120
HTH
-Chris Hohmann|||I agree w/Chris... If you can, Redesign the table so that each discreet data
element is in it's own column... But yes, you should switch back to using
Clustered index on the column that the query predicate (Whats in the Where
Clause) uses... ESPECIALLY If the query extracts a range of values.
And that is what ...
Where X Like @.Value + '%' will be doing, since it translates to Where X >=
@.Value And <= @.Value + 'ZZZZZZZZZZZZZZZZZZZZZZZZ' (actually whatever the
Query Parser determines is the last possible value in sort order that will
satisfy the Like.)
"Chris Hohmann" wrote:

> <roy.anderson@.gmail.com> wrote in message
> news:1112638709.920563.71130@.f14g2000cwb.googlegroups.com...
> I think CBretana was suggesting that you reconsider you table design.
> Specifically, the containernum column seems to contain composite data; the
> container number plus whatever the alphabetic data represents. If these
> pieces of data were separated out into discreet columns, the query analyze
r
> could take advantage of the index on the container_number column. In the
> alternative, if you are unable to alter the table model, you might conside
r
> creating an indexed view on the new315_itv table that includes a calculate
d
> expression to extract the actual container number from the containernum
> column. Here's a proof of concept on how to extract a number from an
> alphanumeric string:
> DECLARE @.s NVARCHAR(8)
> SET @.s = 'abc123de'
> SELECT SUBSTRING(
> @.s,
> PATINDEX('%[0-9]%',@.s),
> CASE PATINDEX('%[0-9]',@.s)
> WHEN 0 THEN PATINDEX('%[0-9][^0-9]%',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> ELSE PATINDEX('%[0-9]',@.s) - PATINDEX('%[0-9]%',@.s) + 1
> END
> )
> Also, have you considered using VARCHAR instead of NVARCHAR for your textu
al
> data. NVARCHAR requires twice the storage of VARCHAR an should only be use
d
> if your textual data includes Unicode characters. Here's an article:
> http://aspfaq.com/show.asp?id=2354
> Finally, here's an article that compares various methods of paging through
a
> recordset. It includes an example of a stored procedure that makes use of
a
> temp table as well as other stored procedure examples with better
> performance.
> http://aspfaq.com/show.asp?id=2120
>
> HTH
> -Chris Hohmann
>
>|||Thanks for the info you two. I'll be sure to check out those articles
Chris.
Actually, believe it or not, I have the Stored Proc at a comfortable
spot now. When a user searches using at least 3 characters, the results
return in under a second, when searches occur with less than 3
characters, the results can take up to 35 seconds to return (but this
warning is now noted on the site). See my new stored proc below. I
utilized CB's table variable idea but maintained the full-text index.
Basically (I believe) if one uses LIKE sqlserver looks for a regular
index, if one uses CONTAINS or FREETEXT sqlserver looks for a full-text
index. However, for whatever reason, I can't use CONTAINS on character
searches that contain less than 3 characters. It doesn't error out, it
just doesn't display anything. Probably an idiosyncrasy of full-text
searches.
CREATE PROCEDURE [Get_Data]
@.CurrentPage int,
@.PageSize int,
@.TotalRecords int output,
@.con_num nvarchar(8)
AS
SET NOCOUNT ON
DECLARE @.T Table
(
RowNum INTEGER PRIMARY KEY Identity NOT NULL,
PK UNIQUEIDENTIFIER NOT NULL
)
IF LEN(@.con_num) >= 3
BEGIN
SET @.con_num = '"'+@.con_num+'*"'
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE CONTAINS (containernum, @.con_num)
END
ELSE
BEGIN
INSERT INTO @.T (PK)
SELECT
uid
FROM
dbo.new315_itv
WHERE containernum LIKE '%'+@.con_num+'%'
END
DECLARE @.FirstRec int, @.LastRec int
SELECT @.FirstRec = (@.CurrentPage - 1) * @.PageSize
SELECT @.LastRec = (@.CurrentPage * @.PageSize + 1)
SELECT
A.uid,
A.event,
A.bookingnum,
A.vanowner,
A.containernum,
A.tcn,
A.poe,
A.pod,
A.shipname,
A.vdn,
A.eventlocation,
A.pcfn
FROM
dbo.new315_itv A INNER JOIN @.T T ON T.PK = A.uid
WHERE
T.RowNum BETWEEN @.FirstRec AND @.LastRec
SELECT @.TotalRecords = COUNT(*) FROM @.T
GO

Can the code.Functions be shared between reports?

hey,
I have created some vb functions in report->Properties->Code page, they are some common converting and formating functions, can these be shared between reports or I have to write it for every reprot? Or I have to write it into a dll and ref that dll in my report?

Thanks.

You should move those functions into a custom assembly and reference that in the reports. Please check this link on MSDN: http://msdn2.microsoft.com/en-us/library/ms345237.aspx

Particularly, scroll to the bottom and check the last two entries in that table about custom assemblies (and calling static functions which is most likely what you are looking for) and custom class instances.

-- Robert

Can SSRS break a "large page" into printable pages?

This is a question of strategy. Can SSRS handle this problem, or should I consider another approach?

My problem: I want to print a family tree chart. Imagine starting with the parents at the top center, then their children spread out in the next row, with their spouses, then the third row has the grandchildren, and so on. No problem, provided that you have the ability to position the entries where you want, and a page that has indefinite width and depth. But of course this has to be printed out on normal pages.

1/ Does SSRS give me the ability to create a very large page, and then automatically break this up for me into standard-sized pages that the real printer can handle. For example, the program might first produce a "virtual A1 page", then physically print this as up to 16 A4 pages (which the user might then glue together). It doesn't matter (from the application's point of view) whether the output is HTML, PDF, or something else, although PDF is probably a preference.

2/ Does SSRS allow me to calculate the position of a textbox at execution time. It is not until I read the data that I can work out the dimensions of the chart.

[Editing: I've found the answer: it's "Yes". But that leads to another question: -]

3/ Can I create new controls dynamically? I won't know how many people there are until I read the data. So whereas normal logic is "Read a record, print a detail line", in this case the logic will be "Read a record, create a texbox and place it {here} on the virtual page"

If yes, then I think that I can figure out the logic. What should I read to learn how to do this?

Thanks, Robert.

hi,

first create one rdlc file with existing ds and certain values (which is in working condition)

then open it as an xml file.

now you can see the xml tags in it and before generating the report generate the rdlc file as per your caluclation (these tags has column width..etc)

generate the rdlc and then, while showing it in report viewer select processing mode as local, then give this rdlc file as report and bind the dataset which you want to show in the report.

First, open a working report's rdlc file content in xml viewer, you can understand what to do, by using System.Xml namespace classes.

I will try to find the code, i have worked on it in the past.

and once you have done this, printer will take care of the rest.

( in rdlc xml tags give length and bredth of report as per your needs after calculating the data and space it occupies)

see the following link, its also similar to your problem

http://forums.asp.net/t/1195123.aspx

all the best

|||

Thanks for this, if you can post the code, that would be great!

Perhaps you can help me with the related problem, http://forums.asp.net/p/1192671/2052349.aspx#2052349. This explains the whole problem, which is that I'm trying to produce a family tree chart. My original idea was that I could produce a huge "virtual page", and then break this up into printable (A4) chunks. However, I can't figure out how program logic such as this at the start of the report: -
For each record
Read record
Create textbox at position ...
Next

Until I can figure this out, I'm completely stuck. In fact, if I could write code like this at the start of each page I wouldn't need to create a large virtual page to be broken up later.

Can SQL break an array into one string? [Stored Procedure]

Hello, I have a question on sql stored procedures.
I have such a procedure, which returnes me rows with ID-s.
Then in my asp.net page I make from that Id-s a string like

SELECT * FROM [eai.Documents] WHERECategoryId=11 ORCategoryId=16 ORCategoryId=18.

My question is: Can I do the same in my stored procedure?
Here is it:

set ANSI_NULLSONset QUOTED_IDENTIFIERONgoALTER PROCEDURE [dbo].[eai.GetSubCategoriesById](@.Idint)ASdeclare @.pathvarchar(100);SELECT @.path=PathFROM [eai.FileCategories]WHERE Id = @.Id;SELECT Id, ParentCategoryId,Name, NumActiveAdsFROM [eai.FileCategories]WHERE PathLIKE @.Path +'%'ORDER BY Path

Thank you
Artashes

There is no Array in Stored procedure, but you do can it by use dynamic sql . pass stored procedure a string as 11,16,18

then

in store procedure do

exec N'select * from yourTable where id in ' + @.idlist

Hope this help

|||

DavidDu thank you for answer. I found another solution.

It is herehttp://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=475225&SiteID=1

Artashes

Can someone show me how.....

I'm beginner in asp.net. Can someone show me how to create coding for this page..

i have to create my own login page and database. I'm using sql sever 2005 for database. can someone show me how to make connection with my login button and my database? please...i'm really need help from you all guys.

Hi

Simple User Account Login demonstration of managing user account information on a server in traditional way using ASP.NET 1.1.

If you prefer asp.net 2.0 refer toHow to implement Two basics uses for the Asp.net Login control 2.0 (login and RememberMe).

Hope this helps.

|||hi yyy8347, i will try it now. thanks yeah!

Can set anonymous to ReportServer

I want to all user can see my reports in the server just like they can see
my web page.
I set the virtual directory ReportServer's access to anonymous.
If there any dangers to do that?It can be depending on how you plan to manage your server and what rights
you set. When anonymous is on all users are seen as the same user. This
means you must give this user rights to everything you want people to
access. This means you can not have certain people access one folder and
other another. If you also want to do management type task as anonymous
then everyone will be able to do it. You could grant the anonymous account
rights to only certain folders and then every time you need to manage the
server, turn anonymous off.
Another option for you might be to use Forms authentication and then give
your users a user name and password. This would allow you more control over
security.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:eVgfnJ9nEHA.2948@.TK2MSFTNGP11.phx.gbl...
> I want to all user can see my reports in the server just like they can see
> my web page.
> I set the virtual directory ReportServer's access to anonymous.
> If there any dangers to do that?
>|||Thank,
Your another option is the answer.
But another question:
I use Forms authentication , how can I pass the user name and password to
rpeorting service from a login form ?
"Daniel Reib [MSFT]" <danreib@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:exGB5t$nEHA.1800@.TK2MSFTNGP15.phx.gbl...
> It can be depending on how you plan to manage your server and what rights
> you set. When anonymous is on all users are seen as the same user. This
> means you must give this user rights to everything you want people to
> access. This means you can not have certain people access one folder and
> other another. If you also want to do management type task as anonymous
> then everyone will be able to do it. You could grant the anonymous
account
> rights to only certain folders and then every time you need to manage the
> server, turn anonymous off.
> Another option for you might be to use Forms authentication and then give
> your users a user name and password. This would allow you more control
over
> security.
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> news:eVgfnJ9nEHA.2948@.TK2MSFTNGP11.phx.gbl...
> > I want to all user can see my reports in the server just like they can
see
> > my web page.
> > I set the virtual directory ReportServer's access to anonymous.
> > If there any dangers to do that?
> >
> >
>|||This is not really my area, but I believe there is a logon/logoff api that
you used. There should be a Forms example that you can look at.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:OmepgkBoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Thank,
> Your another option is the answer.
> But another question:
> I use Forms authentication , how can I pass the user name and password to
> rpeorting service from a login form ?
>
>
> "Daniel Reib [MSFT]" <danreib@.online.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
> :exGB5t$nEHA.1800@.TK2MSFTNGP15.phx.gbl...
> > It can be depending on how you plan to manage your server and what
rights
> > you set. When anonymous is on all users are seen as the same user. This
> > means you must give this user rights to everything you want people to
> > access. This means you can not have certain people access one folder
and
> > other another. If you also want to do management type task as anonymous
> > then everyone will be able to do it. You could grant the anonymous
> account
> > rights to only certain folders and then every time you need to manage
the
> > server, turn anonymous off.
> >
> > Another option for you might be to use Forms authentication and then
give
> > your users a user name and password. This would allow you more control
> over
> > security.
> >
> > --
> > -Daniel
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> >
> > "ad" <ad@.wfes.tcc.edu.tw> wrote in message
> > news:eVgfnJ9nEHA.2948@.TK2MSFTNGP11.phx.gbl...
> > > I want to all user can see my reports in the server just like they can
> see
> > > my web page.
> > > I set the virtual directory ReportServer's access to anonymous.
> > > If there any dangers to do that?
> > >
> > >
> >
> >
>

Can save labels to the DB but not the content of DropDownLists !

hi there, i have a payment page which uses javascript to calculate payments and what remains etc. That all works fine, the problem i have is that when you are finished and you go to the next page it should save all of the records to the order table where the order_id = a querystring. Now i have tried using an insert method and an update method, not really sure whats the difference when putting data into an existing record. What happens though is that i can save the contents of labels etc but it wont save the content of dropdown lists (of which there are 2) i just cant work it out, i have posted my code below

string strOrderID =Convert.ToString(Request.QueryString["OrderID"]);

string strDiscountPercent =Convert.ToString(5656);

double dblDiscountMoney =Convert.ToDouble(txtDiscountMoney.Text);

string strPaymentMethod1 = ddlPaymentMethod1.SelectedValue;

double dblPaymentAmount1 =Convert.ToDouble(txtPaymentAmount1.Text);

string strPaymentRecipt1 = txtPaymentRecipt1.Text;

string strPaymentMethod2 = ddlPaymentMethod2.SelectedValue;

double dblPaymentAmount2 =Convert.ToDouble(txtPaymentAmount2.Text);

string strPaymentRecipt2 = txtPaymentRecipt2.Text;

string sConnectionStringCustInfo ="my connection string";

SqlConnection objConnOID =new SqlConnection(sConnectionStringCustInfo);

//This is the sql statement.

using (objConnOID)

{

objConnOID.Open();

string sqlUpDate ="UPDATE tbl_order SET discount_Percent = " + txtDiscountPercent.Text +

", discount_money = " + dblDiscountMoney +

", payment_method1 = " + strPaymentMethod1 + =========== this is the problem line here!

", payment_amount1 = " + dblPaymentAmount1 +

", payment_ref1 = " + strPaymentRecipt1 +

", payment_amount2 = " + dblPaymentAmount2 +

", payment_ref2 = " + strPaymentRecipt2 +

"WHERE order_ID = " + strOrderID;

SqlCommand objCmd1 =new SqlCommand(sqlUpDate, objConnOID);

try

{

objCmd1.ExecuteNonQuery();

}

and because it throws an error it wont update the database with any other record. does anyone have any ideas, i have spent hours last night trying to figure this one out and im sure its simple! but i need some help!

Cheers

Jez

When concatenating up a sql statement, you would need to wrap your string values with single quotes

sql like this wont work:

column = value

sql like this can work:

column = 'value'

You are however open to injection attacks when you do this and i would recommend you look into using parameterized sql statements instead. When using parameterized statements, you do not need to worry about issues from the single quotes and you are protected from sql injection attacks.

|||

but im not passing a value, its a varaible (string or double) i dont know if that would make a difference?

|||

it does make a difference.

when building up a sql statement, you can directly concatenate in your numeric variables as they do not need to be quoted.

when concatenating in the value of a string variable, you need to include the single quotes around the string value to get well formed sql

string sql = "Select * FROM sometable WHERE somecolumn ='" + yourStringVar + "'";

|||

ok thanks, the only thing i dont get is the statement works with labels and textbox values just not dropdownlists

|||

the other variables you are using seem to be related to numeric data. if the underlying column type (in your database) for those values is a numeric type (integer, long, double etc..) then you dont need the single quotes. the single quotes are needed for string/text datatypes (char, varchar, text etc...).

Can RS export word document?

I saw on the report server web page, the word format is not supported for exporting. Is there any way to export report to a word editable document.

Hello,

There are many posts about this in this forum, but a Word export isn't supplied by Microsoft. You will have to write your own or purchase a 3rd party tool. Take a look here, a couple 3rd party tools are mentioned.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=946719&SiteID=1

Jarret

Can reporting service custom error message or page?

Hi all,
The error message returned by reporting service is not easy to read for
customers, so can reporting service custom error message or page?
ThanksWere you able to solve the problem by redirecting to custom error page, if so
share with me, iam looking solution for the same.
"iwteih" wrote:
> Hi all,
> The error message returned by reporting service is not easy to read for
> customers, so can reporting service custom error message or page?
> Thanks
>

Can report services use Request.QueryString("varName") for record selection?

I know the parameters are available, but let's say if I've got a variable in a request.QueryString() function I want to use from a page which links to the report, is it possible for the record selection to be filtered by the Request.QueryString() variable?You need to pass the page parameters into as report parameter name / value pairs. We do not provide you the raw page querystring in the report (as the report execution isn't even guaranteed to come from a web page).

can only get to Katmai version of page

This page used to take me to a SQL2005 version of the page:

http://msdn2.microsoft.com/en-us/library/ms186627.aspx

Now it redirects me to the Katmai version. How do I get to the SQL2005 version online?

Try this page:

http://msdn2.microsoft.com/es-es/library/ms186627.aspx

(Google? is your friend.)

can not send an email using databasemail from asp.net page in a trigger

Hello,

I want to send an email from a trigger. I have configured all, and if I insert something into the table directly in the db the email is sent.

I give access to the asp.net account to msdb and put it into DatabaseMailUserRole group, but it doesnt work when I launch the trigger.

Any ideas?

What is the error you are getting?

Did you try inserting the row from Query window to check db mail working?

Check mail log for more info...

|||that is what i do not understand. It do not give me any error. If I insert from query window it works, but from asp.net fails. I think it is something related to the asp.net account, but i am using atlas and i can not see the error message. Nothing to see in the log, except a transacction exception, but I do not see that always, sometimes it writes it sometimes not.