I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.
Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>
|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:[vbcol=seagreen]
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...[vbcol=seagreen]
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
a
>
|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:[vbcol=seagreen]
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
>
|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
Showing posts with label lots. Show all posts
Showing posts with label lots. Show all posts
Sunday, March 11, 2012
Defrag on SAN disk ?
I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds like a
>> good
>> idea.|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
> > Jim,
> >
> > Interesting question. Who is the SAN vendor? SANs store data
> > differently to "normal" file systems. Blocks do not get overwritten
> > (usually), but a new block gets written when data changes. So, I'm
> > not entirely sure what would happen if you ran a disk defrag tool on
> > a SAN volume.
> > I would first of all make sure that you don't have any SQL Server
> > fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> > talking to your infrastructure team about this, failing that speak to
> > the SAN vendor.
> >
> > I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >
> >
> > Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds like
a
> >> good
> >> idea.
>|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
>> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
>> necessary on our CX series.
>>
>> Mark Allison wrote:
>> Jim,
>> Interesting question. Who is the SAN vendor? SANs store data
>> differently to "normal" file systems. Blocks do not get overwritten
>> (usually), but a new block gets written when data changes. So, I'm
>> not entirely sure what would happen if you ran a disk defrag tool on
>> a SAN volume.
>> I would first of all make sure that you don't have any SQL Server
>> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
>> consider talking to your infrastructure team about this, failing
>> that speak to the SAN vendor.
>> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>>
>> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds
>> like a good
>> idea.|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
> > Tell your engineer he is full of sxxx<g>. While a large amount of
> > cache may abstract some aspects of data being read and written to
> > disk there is always the fact fragmentation can lead to pages that
> > are not as full as you would like. If the page is half empty on disk
> > it will be half empty when read into the sql server data cache as
> > well. This means you can only have half the amount of data or
> > indexes in cache at any one time. It also means lots more I/O's
> > (even if they are logical) and that means lots more cpu ect.
> >
> >
> > "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> > news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> >> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
> >> necessary on our CX series.
> >>
> >>
> >> Mark Allison wrote:
> >> Jim,
> >>
> >> Interesting question. Who is the SAN vendor? SANs store data
> >> differently to "normal" file systems. Blocks do not get overwritten
> >> (usually), but a new block gets written when data changes. So, I'm
> >> not entirely sure what would happen if you ran a disk defrag tool on
> >> a SAN volume.
> >> I would first of all make sure that you don't have any SQL Server
> >> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
> >> consider talking to your infrastructure team about this, failing
> >> that speak to the SAN vendor.
> >>
> >> I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >>
> >>
> >> Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds
> >> like a good
> >> idea.
>|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
there lots of fragmentation, BUT, they are all on SAN disk, so my question
is, will there be any value in running the defrag ?
I do plan to run it when SQL is not running, that sounds like a good idea.Jim,
Interesting question. Who is the SAN vendor? SANs store data differently
to "normal" file systems. Blocks do not get overwritten (usually), but a
new block gets written when data changes. So, I'm not entirely sure what
would happen if you ran a disk defrag tool on a SAN volume.
I would first of all make sure that you don't have any SQL Server
fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
talking to your infrastructure team about this, failing that speak to
the SAN vendor.
I don't think I would want to defrag a SAN, but I'm not 100% sure.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Jim Trowbridge wrote:
> I ran the standard Windows Disk Defrag analyzer on my SQL Servers, and wow is
> there lots of fragmentation, BUT, they are all on SAN disk, so my question
> is, will there be any value in running the defrag ?
> I do plan to run it when SQL is not running, that sounds like a good idea.
>|||I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
on our CX series.
Mark Allison wrote:
> Jim,
> Interesting question. Who is the SAN vendor? SANs store data
> differently to "normal" file systems. Blocks do not get overwritten
> (usually), but a new block gets written when data changes. So, I'm
> not entirely sure what would happen if you ran a disk defrag tool on
> a SAN volume.
> I would first of all make sure that you don't have any SQL Server
> fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> talking to your infrastructure team about this, failing that speak to
> the SAN vendor.
> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>
> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds like a
>> good
>> idea.|||Tell your engineer he is full of sxxx<g>. While a large amount of cache may
abstract some aspects of data being read and written to disk there is always
the fact fragmentation can lead to pages that are not as full as you would
like. If the page is half empty on disk it will be half empty when read
into the sql server data cache as well. This means you can only have half
the amount of data or indexes in cache at any one time. It also means lots
more I/O's (even if they are logical) and that means lots more cpu ect.
--
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> I agree. FWIW, our EMC/Dell Engineer told us defragging was not necessary
> on our CX series.
>
> Mark Allison wrote:
> > Jim,
> >
> > Interesting question. Who is the SAN vendor? SANs store data
> > differently to "normal" file systems. Blocks do not get overwritten
> > (usually), but a new block gets written when data changes. So, I'm
> > not entirely sure what would happen if you ran a disk defrag tool on
> > a SAN volume.
> > I would first of all make sure that you don't have any SQL Server
> > fragmentation using DBCC SHOWCONTIG. If this is all OK, then consider
> > talking to your infrastructure team about this, failing that speak to
> > the SAN vendor.
> >
> > I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >
> >
> > Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds like
a
> >> good
> >> idea.
>|||I was answering the file-system fragmentation question, not the data and
index fragmentation question. I did not mean to imply one shouldn't handle
the database fragmentation if the data stores are on a SAN. OK, that being
said, I shot an email to our Engineer and asked again about running a
windows defragmentation on our SAN and he said absolutely keep it defragged
with hard disk defragmentation tool, so regardless of _what_ I was talking
about, I was still wrong. :-O
Thanks Andrew. It's probably beer-thirty for me anyway.
Eric
Andrew J. Kelly wrote:
> Tell your engineer he is full of sxxx<g>. While a large amount of
> cache may abstract some aspects of data being read and written to
> disk there is always the fact fragmentation can lead to pages that
> are not as full as you would like. If the page is half empty on disk
> it will be half empty when read into the sql server data cache as
> well. This means you can only have half the amount of data or
> indexes in cache at any one time. It also means lots more I/O's
> (even if they are logical) and that means lots more cpu ect.
>
> "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
>> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
>> necessary on our CX series.
>>
>> Mark Allison wrote:
>> Jim,
>> Interesting question. Who is the SAN vendor? SANs store data
>> differently to "normal" file systems. Blocks do not get overwritten
>> (usually), but a new block gets written when data changes. So, I'm
>> not entirely sure what would happen if you ran a disk defrag tool on
>> a SAN volume.
>> I would first of all make sure that you don't have any SQL Server
>> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
>> consider talking to your infrastructure team about this, failing
>> that speak to the SAN vendor.
>> I don't think I would want to defrag a SAN, but I'm not 100% sure.
>>
>> Jim Trowbridge wrote:
>> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
>> and wow is there lots of fragmentation, BUT, they are all on SAN
>> disk, so my question is, will there be any value in running the
>> defrag ? I do plan to run it when SQL is not running, that sounds
>> like a good
>> idea.|||Have one for me too<g>.
Andrew J. Kelly SQL MVP
"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
>
> Andrew J. Kelly wrote:
> > Tell your engineer he is full of sxxx<g>. While a large amount of
> > cache may abstract some aspects of data being read and written to
> > disk there is always the fact fragmentation can lead to pages that
> > are not as full as you would like. If the page is half empty on disk
> > it will be half empty when read into the sql server data cache as
> > well. This means you can only have half the amount of data or
> > indexes in cache at any one time. It also means lots more I/O's
> > (even if they are logical) and that means lots more cpu ect.
> >
> >
> > "Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
> > news:ehM3E8ioEHA.2784@.TK2MSFTNGP14.phx.gbl...
> >> I agree. FWIW, our EMC/Dell Engineer told us defragging was not
> >> necessary on our CX series.
> >>
> >>
> >> Mark Allison wrote:
> >> Jim,
> >>
> >> Interesting question. Who is the SAN vendor? SANs store data
> >> differently to "normal" file systems. Blocks do not get overwritten
> >> (usually), but a new block gets written when data changes. So, I'm
> >> not entirely sure what would happen if you ran a disk defrag tool on
> >> a SAN volume.
> >> I would first of all make sure that you don't have any SQL Server
> >> fragmentation using DBCC SHOWCONTIG. If this is all OK, then
> >> consider talking to your infrastructure team about this, failing
> >> that speak to the SAN vendor.
> >>
> >> I don't think I would want to defrag a SAN, but I'm not 100% sure.
> >>
> >>
> >> Jim Trowbridge wrote:
> >> I ran the standard Windows Disk Defrag analyzer on my SQL Servers,
> >> and wow is there lots of fragmentation, BUT, they are all on SAN
> >> disk, so my question is, will there be any value in running the
> >> defrag ? I do plan to run it when SQL is not running, that sounds
> >> like a good
> >> idea.
>|||"Eric Sabine" <mopar41@.mail_after_hot_not_before.com> wrote in message
news:%230lCGploEHA.868@.TK2MSFTNGP10.phx.gbl...
> I was answering the file-system fragmentation question, not the data and
> index fragmentation question. I did not mean to imply one shouldn't
handle
> the database fragmentation if the data stores are on a SAN. OK, that
being
> said, I shot an email to our Engineer and asked again about running a
> windows defragmentation on our SAN and he said absolutely keep it
defragged
> with hard disk defragmentation tool, so regardless of _what_ I was talking
> about, I was still wrong. :-O
>
Not necessarily.
Windows defrag may do nothing on the SAN. Oh, the SAN will report it done,
etc, but it may virtualize away the actions and no real difference will
happen.
Again, it depends a lot on the SAN.
> Thanks Andrew. It's probably beer-thirty for me anyway.
> Eric
Friday, March 9, 2012
Defining dataset query in vb.net
Hi all,
there are lots of objects about defining the report data source ,data set,
query etc.
but is there any examples of defining them and assign to a report?
i feel confuse cos i dun quite know how to use them.(i want to construct the
Query in web form first then pass to the report.)
thanks in advance!hi all,
may be i should post my questions clearly,
i know how to assign a datasource to the report using setreportdatasource(),
but|||BUT
HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
HOW TO ASSIGN A QUERY TO A DATASET?
i dun know if my concept is corroect or not, please let me know if i have
said something wrong
thanks in advance.|||Two points. First, the way you are envisioning it today can not easily be
done. People do this but it is not an out of the box experience. By that I
mean, it is not a built in capability but it is possible to use the
extensions and write your own data extension to do this.
The way it is really designed today is for you to create the reports and the
dataset from within Reporting Services. The name matches the design. It is
designed to be a service that you can invoke. You can use the product three
ways. First, it ships with its own portal. This is the quickest way to use
it and is the best way to at least get your feet wet. Then you can integrate
with your own web app by using either URL integration (your app creates the
URL string passing any parameters). You can also integrate with soap (web
services).
Version 2 (Yukon and Widbey) will come with new controls that should allow
you to do this more the way you are envisioning: create a recordset and
passing it to the control. There will be both a winform and a webform
control.
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> BUT
> HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> HOW TO ASSIGN A QUERY TO A DATASET?
> i dun know if my concept is corroect or not, please let me know if i have
> said something wrong
> thanks in advance.|||thank you so much bruce,
Does it mean that what i can do for the time being is just passing
paramenters to the query built in the rs designer, not passing a query to a
dataset?
"Bruce Loehle-Conger" wrote:
> Two points. First, the way you are envisioning it today can not easily be
> done. People do this but it is not an out of the box experience. By that I
> mean, it is not a built in capability but it is possible to use the
> extensions and write your own data extension to do this.
> The way it is really designed today is for you to create the reports and the
> dataset from within Reporting Services. The name matches the design. It is
> designed to be a service that you can invoke. You can use the product three
> ways. First, it ships with its own portal. This is the quickest way to use
> it and is the best way to at least get your feet wet. Then you can integrate
> with your own web app by using either URL integration (your app creates the
> URL string passing any parameters). You can also integrate with soap (web
> services).
> Version 2 (Yukon and Widbey) will come with new controls that should allow
> you to do this more the way you are envisioning: create a recordset and
> passing it to the control. There will be both a winform and a webform
> control.
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> > BUT
> > HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> > HOW TO ASSIGN A QUERY TO A DATASET?
> >
> > i dun know if my concept is corroect or not, please let me know if i have
> > said something wrong
> >
> > thanks in advance.
>
>|||by the way, could you please explain more about the method you suggest?
as i really have to make a dynamic query string (not just for the data
value, also the whole query)|||RS has a lot of flexibility in how the query is designed. You can have query
parameters (which map to report parameters). You can refer to global
variable (for instance user!userid), you can base the query on an expression
(expressions are very powerful so there is a whole lot you can do once you
do that).
I think before you jump all the way to where you want to end up you should
play around with it a little bit so you understand the capabilities. Create
a few simple reports, have a query parameter, try out a simple query based
on an expression.
After you understand the capabilities you can decide if it meets your needs.
As I said you can integrate with URL if you want to have your own web site
or you can also use a very full featured web services (soap). Then there are
also extentions possible (data extensions, security extensions).
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> by the way, could you please explain more about the method you suggest?
> as i really have to make a dynamic query string (not just for the data
> value, also the whole query)|||Thanks a lot Bruce,
you said by expression, do you mean by SQL command?
my situation is like this , i use the Render() function to generate reports
with the query "SELECT * FROM table WHERE value=@.parameter" in the report
project, i have tried to pass the parameter to the query by inputting it to
the render function.
thats the most i can do, but the parameters are only limited to a value, not
a clause(esp WHERE clause)...what if i wanna get the data with "WHERE value2
= @.para2 AND value3 =@.para3"'
will URL solve my problem? i have found a method , which is about editing
the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
"Bruce Loehle-Conger" wrote:
> RS has a lot of flexibility in how the query is designed. You can have query
> parameters (which map to report parameters). You can refer to global
> variable (for instance user!userid), you can base the query on an expression
> (expressions are very powerful so there is a whole lot you can do once you
> do that).
> I think before you jump all the way to where you want to end up you should
> play around with it a little bit so you understand the capabilities. Create
> a few simple reports, have a query parameter, try out a simple query based
> on an expression.
> After you understand the capabilities you can decide if it meets your needs.
> As I said you can integrate with URL if you want to have your own web site
> or you can also use a very full featured web services (soap). Then there are
> also extentions possible (data extensions, security extensions).
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> > by the way, could you please explain more about the method you suggest?
> >
> > as i really have to make a dynamic query string (not just for the data
> > value, also the whole query)
>
>|||My suggestion is to back up and first learn the capabilities of Reporting
Services before you start doing integration work. Spend a little time
experimenting. Do some sample reports. Although you can edit RDl and deploy
when you deploy it it will be accesible to all users so you would have to
come up with a unique naming scenario. Plus you have the performance hit of
deploying every time the user requests the report.
Read up on dynamic SQL. Read up and play with expressions and make sure you
understand the power of expressions. Be sure you understand the difference
between query parameter and report parameter. Read up and try a creating a
url to pass a simple parameter to a report. Then get into creating a dynamic
query.
And no, an expression is not a SQL command. It will evaluate to an sql
command but it can have iif statements, nested iif statements etc so you can
create a SQL command with a varying amount of clauses in the where command.
So far I have not heard anything you are trying to do that requires editing
the rdl file directly or writing a data extension. Custom assemblies are
also possible but again, in most cases are not necessary. I write a little
code behind but have found that normally an expression solves my needs.
Bruce L-C
MVP Reporting Services
Again, you are jumping to fast to the end result without first learning
Reporting Services.
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> Thanks a lot Bruce,
> you said by expression, do you mean by SQL command?
> my situation is like this , i use the Render() function to generate
> reports
> with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> project, i have tried to pass the parameter to the query by inputting it
> to
> the render function.
> thats the most i can do, but the parameters are only limited to a value,
> not
> a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> value2
> = @.para2 AND value3 =@.para3"'
> will URL solve my problem? i have found a method , which is about editing
> the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
>
> "Bruce Loehle-Conger" wrote:
>> RS has a lot of flexibility in how the query is designed. You can have
>> query
>> parameters (which map to report parameters). You can refer to global
>> variable (for instance user!userid), you can base the query on an
>> expression
>> (expressions are very powerful so there is a whole lot you can do once
>> you
>> do that).
>> I think before you jump all the way to where you want to end up you
>> should
>> play around with it a little bit so you understand the capabilities.
>> Create
>> a few simple reports, have a query parameter, try out a simple query
>> based
>> on an expression.
>> After you understand the capabilities you can decide if it meets your
>> needs.
>> As I said you can integrate with URL if you want to have your own web
>> site
>> or you can also use a very full featured web services (soap). Then there
>> are
>> also extentions possible (data extensions, security extensions).
>> Bruce L-C
>> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
>> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
>> > by the way, could you please explain more about the method you suggest?
>> >
>> > as i really have to make a dynamic query string (not just for the data
>> > value, also the whole query)
>>|||thank you so much bruce,
after a day's study, i start to understand now, and i think nested iif maybe
the direction that i am heading to~~~
thank you sooo much for showing me the way!
"Bruce Loehle-Conger" wrote:
> My suggestion is to back up and first learn the capabilities of Reporting
> Services before you start doing integration work. Spend a little time
> experimenting. Do some sample reports. Although you can edit RDl and deploy
> when you deploy it it will be accesible to all users so you would have to
> come up with a unique naming scenario. Plus you have the performance hit of
> deploying every time the user requests the report.
> Read up on dynamic SQL. Read up and play with expressions and make sure you
> understand the power of expressions. Be sure you understand the difference
> between query parameter and report parameter. Read up and try a creating a
> url to pass a simple parameter to a report. Then get into creating a dynamic
> query.
> And no, an expression is not a SQL command. It will evaluate to an sql
> command but it can have iif statements, nested iif statements etc so you can
> create a SQL command with a varying amount of clauses in the where command.
> So far I have not heard anything you are trying to do that requires editing
> the rdl file directly or writing a data extension. Custom assemblies are
> also possible but again, in most cases are not necessary. I write a little
> code behind but have found that normally an expression solves my needs.
> Bruce L-C
> MVP Reporting Services
> Again, you are jumping to fast to the end result without first learning
> Reporting Services.
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> > Thanks a lot Bruce,
> >
> > you said by expression, do you mean by SQL command?
> >
> > my situation is like this , i use the Render() function to generate
> > reports
> > with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> > project, i have tried to pass the parameter to the query by inputting it
> > to
> > the render function.
> >
> > thats the most i can do, but the parameters are only limited to a value,
> > not
> > a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> > value2
> > = @.para2 AND value3 =@.para3"'
> >
> > will URL solve my problem? i have found a method , which is about editing
> > the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
> >
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> >> RS has a lot of flexibility in how the query is designed. You can have
> >> query
> >> parameters (which map to report parameters). You can refer to global
> >> variable (for instance user!userid), you can base the query on an
> >> expression
> >> (expressions are very powerful so there is a whole lot you can do once
> >> you
> >> do that).
> >>
> >> I think before you jump all the way to where you want to end up you
> >> should
> >> play around with it a little bit so you understand the capabilities.
> >> Create
> >> a few simple reports, have a query parameter, try out a simple query
> >> based
> >> on an expression.
> >>
> >> After you understand the capabilities you can decide if it meets your
> >> needs.
> >> As I said you can integrate with URL if you want to have your own web
> >> site
> >> or you can also use a very full featured web services (soap). Then there
> >> are
> >> also extentions possible (data extensions, security extensions).
> >>
> >> Bruce L-C
> >>
> >> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> >> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> >> > by the way, could you please explain more about the method you suggest?
> >> >
> >> > as i really have to make a dynamic query string (not just for the data
> >> > value, also the whole query)
> >>
> >>
> >>
>
>
there are lots of objects about defining the report data source ,data set,
query etc.
but is there any examples of defining them and assign to a report?
i feel confuse cos i dun quite know how to use them.(i want to construct the
Query in web form first then pass to the report.)
thanks in advance!hi all,
may be i should post my questions clearly,
i know how to assign a datasource to the report using setreportdatasource(),
but|||BUT
HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
HOW TO ASSIGN A QUERY TO A DATASET?
i dun know if my concept is corroect or not, please let me know if i have
said something wrong
thanks in advance.|||Two points. First, the way you are envisioning it today can not easily be
done. People do this but it is not an out of the box experience. By that I
mean, it is not a built in capability but it is possible to use the
extensions and write your own data extension to do this.
The way it is really designed today is for you to create the reports and the
dataset from within Reporting Services. The name matches the design. It is
designed to be a service that you can invoke. You can use the product three
ways. First, it ships with its own portal. This is the quickest way to use
it and is the best way to at least get your feet wet. Then you can integrate
with your own web app by using either URL integration (your app creates the
URL string passing any parameters). You can also integrate with soap (web
services).
Version 2 (Yukon and Widbey) will come with new controls that should allow
you to do this more the way you are envisioning: create a recordset and
passing it to the control. There will be both a winform and a webform
control.
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> BUT
> HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> HOW TO ASSIGN A QUERY TO A DATASET?
> i dun know if my concept is corroect or not, please let me know if i have
> said something wrong
> thanks in advance.|||thank you so much bruce,
Does it mean that what i can do for the time being is just passing
paramenters to the query built in the rs designer, not passing a query to a
dataset?
"Bruce Loehle-Conger" wrote:
> Two points. First, the way you are envisioning it today can not easily be
> done. People do this but it is not an out of the box experience. By that I
> mean, it is not a built in capability but it is possible to use the
> extensions and write your own data extension to do this.
> The way it is really designed today is for you to create the reports and the
> dataset from within Reporting Services. The name matches the design. It is
> designed to be a service that you can invoke. You can use the product three
> ways. First, it ships with its own portal. This is the quickest way to use
> it and is the best way to at least get your feet wet. Then you can integrate
> with your own web app by using either URL integration (your app creates the
> URL string passing any parameters). You can also integrate with soap (web
> services).
> Version 2 (Yukon and Widbey) will come with new controls that should allow
> you to do this more the way you are envisioning: create a recordset and
> passing it to the control. There will be both a winform and a webform
> control.
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:D4798A3A-E8D1-4A8E-9DD2-14E8A9725B6E@.microsoft.com...
> > BUT
> > HOW TO ASSIGN A DATASET TO A DATASOURCE, AND
> > HOW TO ASSIGN A QUERY TO A DATASET?
> >
> > i dun know if my concept is corroect or not, please let me know if i have
> > said something wrong
> >
> > thanks in advance.
>
>|||by the way, could you please explain more about the method you suggest?
as i really have to make a dynamic query string (not just for the data
value, also the whole query)|||RS has a lot of flexibility in how the query is designed. You can have query
parameters (which map to report parameters). You can refer to global
variable (for instance user!userid), you can base the query on an expression
(expressions are very powerful so there is a whole lot you can do once you
do that).
I think before you jump all the way to where you want to end up you should
play around with it a little bit so you understand the capabilities. Create
a few simple reports, have a query parameter, try out a simple query based
on an expression.
After you understand the capabilities you can decide if it meets your needs.
As I said you can integrate with URL if you want to have your own web site
or you can also use a very full featured web services (soap). Then there are
also extentions possible (data extensions, security extensions).
Bruce L-C
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> by the way, could you please explain more about the method you suggest?
> as i really have to make a dynamic query string (not just for the data
> value, also the whole query)|||Thanks a lot Bruce,
you said by expression, do you mean by SQL command?
my situation is like this , i use the Render() function to generate reports
with the query "SELECT * FROM table WHERE value=@.parameter" in the report
project, i have tried to pass the parameter to the query by inputting it to
the render function.
thats the most i can do, but the parameters are only limited to a value, not
a clause(esp WHERE clause)...what if i wanna get the data with "WHERE value2
= @.para2 AND value3 =@.para3"'
will URL solve my problem? i have found a method , which is about editing
the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
"Bruce Loehle-Conger" wrote:
> RS has a lot of flexibility in how the query is designed. You can have query
> parameters (which map to report parameters). You can refer to global
> variable (for instance user!userid), you can base the query on an expression
> (expressions are very powerful so there is a whole lot you can do once you
> do that).
> I think before you jump all the way to where you want to end up you should
> play around with it a little bit so you understand the capabilities. Create
> a few simple reports, have a query parameter, try out a simple query based
> on an expression.
> After you understand the capabilities you can decide if it meets your needs.
> As I said you can integrate with URL if you want to have your own web site
> or you can also use a very full featured web services (soap). Then there are
> also extentions possible (data extensions, security extensions).
> Bruce L-C
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> > by the way, could you please explain more about the method you suggest?
> >
> > as i really have to make a dynamic query string (not just for the data
> > value, also the whole query)
>
>|||My suggestion is to back up and first learn the capabilities of Reporting
Services before you start doing integration work. Spend a little time
experimenting. Do some sample reports. Although you can edit RDl and deploy
when you deploy it it will be accesible to all users so you would have to
come up with a unique naming scenario. Plus you have the performance hit of
deploying every time the user requests the report.
Read up on dynamic SQL. Read up and play with expressions and make sure you
understand the power of expressions. Be sure you understand the difference
between query parameter and report parameter. Read up and try a creating a
url to pass a simple parameter to a report. Then get into creating a dynamic
query.
And no, an expression is not a SQL command. It will evaluate to an sql
command but it can have iif statements, nested iif statements etc so you can
create a SQL command with a varying amount of clauses in the where command.
So far I have not heard anything you are trying to do that requires editing
the rdl file directly or writing a data extension. Custom assemblies are
also possible but again, in most cases are not necessary. I write a little
code behind but have found that normally an expression solves my needs.
Bruce L-C
MVP Reporting Services
Again, you are jumping to fast to the end result without first learning
Reporting Services.
"Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> Thanks a lot Bruce,
> you said by expression, do you mean by SQL command?
> my situation is like this , i use the Render() function to generate
> reports
> with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> project, i have tried to pass the parameter to the query by inputting it
> to
> the render function.
> thats the most i can do, but the parameters are only limited to a value,
> not
> a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> value2
> = @.para2 AND value3 =@.para3"'
> will URL solve my problem? i have found a method , which is about editing
> the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
>
> "Bruce Loehle-Conger" wrote:
>> RS has a lot of flexibility in how the query is designed. You can have
>> query
>> parameters (which map to report parameters). You can refer to global
>> variable (for instance user!userid), you can base the query on an
>> expression
>> (expressions are very powerful so there is a whole lot you can do once
>> you
>> do that).
>> I think before you jump all the way to where you want to end up you
>> should
>> play around with it a little bit so you understand the capabilities.
>> Create
>> a few simple reports, have a query parameter, try out a simple query
>> based
>> on an expression.
>> After you understand the capabilities you can decide if it meets your
>> needs.
>> As I said you can integrate with URL if you want to have your own web
>> site
>> or you can also use a very full featured web services (soap). Then there
>> are
>> also extentions possible (data extensions, security extensions).
>> Bruce L-C
>> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
>> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
>> > by the way, could you please explain more about the method you suggest?
>> >
>> > as i really have to make a dynamic query string (not just for the data
>> > value, also the whole query)
>>|||thank you so much bruce,
after a day's study, i start to understand now, and i think nested iif maybe
the direction that i am heading to~~~
thank you sooo much for showing me the way!
"Bruce Loehle-Conger" wrote:
> My suggestion is to back up and first learn the capabilities of Reporting
> Services before you start doing integration work. Spend a little time
> experimenting. Do some sample reports. Although you can edit RDl and deploy
> when you deploy it it will be accesible to all users so you would have to
> come up with a unique naming scenario. Plus you have the performance hit of
> deploying every time the user requests the report.
> Read up on dynamic SQL. Read up and play with expressions and make sure you
> understand the power of expressions. Be sure you understand the difference
> between query parameter and report parameter. Read up and try a creating a
> url to pass a simple parameter to a report. Then get into creating a dynamic
> query.
> And no, an expression is not a SQL command. It will evaluate to an sql
> command but it can have iif statements, nested iif statements etc so you can
> create a SQL command with a varying amount of clauses in the where command.
> So far I have not heard anything you are trying to do that requires editing
> the rdl file directly or writing a data extension. Custom assemblies are
> also possible but again, in most cases are not necessary. I write a little
> code behind but have found that normally an expression solves my needs.
> Bruce L-C
> MVP Reporting Services
> Again, you are jumping to fast to the end result without first learning
> Reporting Services.
> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> news:ECCC7777-8D8D-42A4-80FB-EB84CA933851@.microsoft.com...
> > Thanks a lot Bruce,
> >
> > you said by expression, do you mean by SQL command?
> >
> > my situation is like this , i use the Render() function to generate
> > reports
> > with the query "SELECT * FROM table WHERE value=@.parameter" in the report
> > project, i have tried to pass the parameter to the query by inputting it
> > to
> > the render function.
> >
> > thats the most i can do, but the parameters are only limited to a value,
> > not
> > a clause(esp WHERE clause)...what if i wanna get the data with "WHERE
> > value2
> > = @.para2 AND value3 =@.para3"'
> >
> > will URL solve my problem? i have found a method , which is about editing
> > the rdl (xml)file in vb...but then i will have to edit the whole rdl file.
> >
> >
> >
> > "Bruce Loehle-Conger" wrote:
> >
> >> RS has a lot of flexibility in how the query is designed. You can have
> >> query
> >> parameters (which map to report parameters). You can refer to global
> >> variable (for instance user!userid), you can base the query on an
> >> expression
> >> (expressions are very powerful so there is a whole lot you can do once
> >> you
> >> do that).
> >>
> >> I think before you jump all the way to where you want to end up you
> >> should
> >> play around with it a little bit so you understand the capabilities.
> >> Create
> >> a few simple reports, have a query parameter, try out a simple query
> >> based
> >> on an expression.
> >>
> >> After you understand the capabilities you can decide if it meets your
> >> needs.
> >> As I said you can integrate with URL if you want to have your own web
> >> site
> >> or you can also use a very full featured web services (soap). Then there
> >> are
> >> also extentions possible (data extensions, security extensions).
> >>
> >> Bruce L-C
> >>
> >> "Jasonymk" <Jasonymk@.discussions.microsoft.com> wrote in message
> >> news:131AC930-6A8F-497B-9E0B-D5CD0B554FF1@.microsoft.com...
> >> > by the way, could you please explain more about the method you suggest?
> >> >
> >> > as i really have to make a dynamic query string (not just for the data
> >> > value, also the whole query)
> >>
> >>
> >>
>
>
Subscribe to:
Posts (Atom)