Jump to Report & Multi Value vs. Single Value

I have a parameterized main report that contains a link to a sub report with parameters. Then from the sub report I have a link that takes you back to main report. If I use multi value parameters or single value parameters the links going back and forth between the two reports works just dandy, when I run it on my PC. However when I deploy the reports to the Web Server, the link going from the sub report back to the main report only works if the parameters are single select. Going from the Main to the Sub it doesn’t matter.

This is the error I get using multi select parameters going from the sub report back to the main.

The path of the item "(null)" is not valid. The path must be less than 260 characters long and must start with slash. Other restrictions apply. (rsInvalidItemPath)

Any suggestions?

By any chance, are the drillthrough links located in the page header or page footer?

-- Robert

Jump to a linked report

I have a problem using the jump-to feature in a linked report.
We are deploying all reports to a single folder 'Deployment' (not visible to
the browser role) and then creating links to these reports in folders that
are visible to the browser role. So from a maintenance point of view, there
is only ever one version of the report to update.
So we have report A that jumps-to report B and both of these are deployed to
the â'Deploymentâ' folder which is only visible to developers. We then create
links, AL and BL, to both A and B, in another folder called â'Runtimeâ' folder
which is visible to end users. End users are able to execute report AL fine
but when they try to jump-to BL, report manager looks for BL in the
â'Deploymentâ' folder instead of the â'Runtimeâ' folder, therefore, errors are
thrown because users donâ't have access to the â'Deployment Folderâ'.
How do we force the jump to action on linked reports to remain in the
current folder?
Obviously this seems to work fine if we are not using linked reports.
Hope this makes sense and any advice would be appreciated.
TIAWhat you want to be doing is doing a jump to the linked report, not the base
report. Instead of the name of the report (which assumes it is in the
current folder, i.e. Deployment folder in your case, do this instead:
/Runtime/TheReportYouWant
This will allow you to jump to the link report as you want. Note that it
means you have to deploy to test the link, you won't be able to test from
within Report Designer.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>I have a problem using the jump-to feature in a linked report.
> We are deploying all reports to a single folder 'Deployment' (not visible
> to
> the browser role) and then creating links to these reports in folders that
> are visible to the browser role. So from a maintenance point of view,
> there
> is only ever one version of the report to update.
> So we have report A that jumps-to report B and both of these are deployed
> to
> the 'Deployment' folder which is only visible to developers. We then
> create
> links, AL and BL, to both A and B, in another folder called 'Runtime'
> folder
> which is visible to end users. End users are able to execute report AL
> fine
> but when they try to jump-to BL, report manager looks for BL in the
> 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> thrown because users don't have access to the 'Deployment Folder'.
> How do we force the jump to action on linked reports to remain in the
> current folder?
> Obviously this seems to work fine if we are not using linked reports.
> Hope this makes sense and any advice would be appreciated.
> TIA
>|||Thanks Bruce.
But to further complicate matters...
If we only want to deploy one version of the main report, how would this
work if we wanted to have multiple links to the reports, i.e. one in
'Runtime' folder and one in 'Runtime2' folder, with different access
permissions on each folder?
Cheers
"Bruce L-C [MVP]" wrote:
> What you want to be doing is doing a jump to the linked report, not the base
> report. Instead of the name of the report (which assumes it is in the
> current folder, i.e. Deployment folder in your case, do this instead:
> /Runtime/TheReportYouWant
> This will allow you to jump to the link report as you want. Note that it
> means you have to deploy to test the link, you won't be able to test from
> within Report Designer.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> >I have a problem using the jump-to feature in a linked report.
> > We are deploying all reports to a single folder 'Deployment' (not visible
> > to
> > the browser role) and then creating links to these reports in folders that
> > are visible to the browser role. So from a maintenance point of view,
> > there
> > is only ever one version of the report to update.
> > So we have report A that jumps-to report B and both of these are deployed
> > to
> > the 'Deployment' folder which is only visible to developers. We then
> > create
> > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > folder
> > which is visible to end users. End users are able to execute report AL
> > fine
> > but when they try to jump-to BL, report manager looks for BL in the
> > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > thrown because users don't have access to the 'Deployment Folder'.
> > How do we force the jump to action on linked reports to remain in the
> > current folder?
> > Obviously this seems to work fine if we are not using linked reports.
> > Hope this makes sense and any advice would be appreciated.
> > TIA
> >
>
>|||Do you realize that instead of all these links that you can set the report
permissions so that based on the role they only see the reports they have
rights to view?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
> Thanks Bruce.
> But to further complicate matters...
> If we only want to deploy one version of the main report, how would this
> work if we wanted to have multiple links to the reports, i.e. one in
> 'Runtime' folder and one in 'Runtime2' folder, with different access
> permissions on each folder?
> Cheers
> "Bruce L-C [MVP]" wrote:
>> What you want to be doing is doing a jump to the linked report, not the
>> base
>> report. Instead of the name of the report (which assumes it is in the
>> current folder, i.e. Deployment folder in your case, do this instead:
>> /Runtime/TheReportYouWant
>> This will allow you to jump to the link report as you want. Note that it
>> means you have to deploy to test the link, you won't be able to test from
>> within Report Designer.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>> >I have a problem using the jump-to feature in a linked report.
>> > We are deploying all reports to a single folder 'Deployment' (not
>> > visible
>> > to
>> > the browser role) and then creating links to these reports in folders
>> > that
>> > are visible to the browser role. So from a maintenance point of view,
>> > there
>> > is only ever one version of the report to update.
>> > So we have report A that jumps-to report B and both of these are
>> > deployed
>> > to
>> > the 'Deployment' folder which is only visible to developers. We then
>> > create
>> > links, AL and BL, to both A and B, in another folder called 'Runtime'
>> > folder
>> > which is visible to end users. End users are able to execute report AL
>> > fine
>> > but when they try to jump-to BL, report manager looks for BL in the
>> > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors
>> > are
>> > thrown because users don't have access to the 'Deployment Folder'.
>> > How do we force the jump to action on linked reports to remain in the
>> > current folder?
>> > Obviously this seems to work fine if we are not using linked reports.
>> > Hope this makes sense and any advice would be appreciated.
>> > TIA
>> >
>>|||I guess we can but we need to be able to specify different parameters for
each report depending on who is viewing it.
Does that make sense or do you think we are going about things in the wrong
way?
"Bruce L-C [MVP]" wrote:
> Do you realize that instead of all these links that you can set the report
> permissions so that based on the role they only see the reports they have
> rights to view?
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
> > Thanks Bruce.
> >
> > But to further complicate matters...
> >
> > If we only want to deploy one version of the main report, how would this
> > work if we wanted to have multiple links to the reports, i.e. one in
> > 'Runtime' folder and one in 'Runtime2' folder, with different access
> > permissions on each folder?
> >
> > Cheers
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> What you want to be doing is doing a jump to the linked report, not the
> >> base
> >> report. Instead of the name of the report (which assumes it is in the
> >> current folder, i.e. Deployment folder in your case, do this instead:
> >>
> >> /Runtime/TheReportYouWant
> >>
> >> This will allow you to jump to the link report as you want. Note that it
> >> means you have to deploy to test the link, you won't be able to test from
> >> within Report Designer.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> >> >I have a problem using the jump-to feature in a linked report.
> >> > We are deploying all reports to a single folder 'Deployment' (not
> >> > visible
> >> > to
> >> > the browser role) and then creating links to these reports in folders
> >> > that
> >> > are visible to the browser role. So from a maintenance point of view,
> >> > there
> >> > is only ever one version of the report to update.
> >> > So we have report A that jumps-to report B and both of these are
> >> > deployed
> >> > to
> >> > the 'Deployment' folder which is only visible to developers. We then
> >> > create
> >> > links, AL and BL, to both A and B, in another folder called 'Runtime'
> >> > folder
> >> > which is visible to end users. End users are able to execute report AL
> >> > fine
> >> > but when they try to jump-to BL, report manager looks for BL in the
> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors
> >> > are
> >> > thrown because users don't have access to the 'Deployment Folder'.
> >> > How do we force the jump to action on linked reports to remain in the
> >> > current folder?
> >> > Obviously this seems to work fine if we are not using linked reports.
> >> > Hope this makes sense and any advice would be appreciated.
> >> > TIA
> >> >
> >>
> >>
> >>
>
>|||Is the user going to this or are you integrating into your own app. It
sounds like here you are integrating it into your app.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:8B07B2C4-34CA-461B-929F-6DBEB4B44AAC@.microsoft.com...
>I guess we can but we need to be able to specify different parameters for
> each report depending on who is viewing it.
> Does that make sense or do you think we are going about things in the
> wrong
> way?
> "Bruce L-C [MVP]" wrote:
>> Do you realize that instead of all these links that you can set the
>> report
>> permissions so that based on the role they only see the reports they have
>> rights to view?
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
>> > Thanks Bruce.
>> >
>> > But to further complicate matters...
>> >
>> > If we only want to deploy one version of the main report, how would
>> > this
>> > work if we wanted to have multiple links to the reports, i.e. one in
>> > 'Runtime' folder and one in 'Runtime2' folder, with different access
>> > permissions on each folder?
>> >
>> > Cheers
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> What you want to be doing is doing a jump to the linked report, not
>> >> the
>> >> base
>> >> report. Instead of the name of the report (which assumes it is in the
>> >> current folder, i.e. Deployment folder in your case, do this instead:
>> >>
>> >> /Runtime/TheReportYouWant
>> >>
>> >> This will allow you to jump to the link report as you want. Note that
>> >> it
>> >> means you have to deploy to test the link, you won't be able to test
>> >> from
>> >> within Report Designer.
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>> >> >I have a problem using the jump-to feature in a linked report.
>> >> > We are deploying all reports to a single folder 'Deployment' (not
>> >> > visible
>> >> > to
>> >> > the browser role) and then creating links to these reports in
>> >> > folders
>> >> > that
>> >> > are visible to the browser role. So from a maintenance point of
>> >> > view,
>> >> > there
>> >> > is only ever one version of the report to update.
>> >> > So we have report A that jumps-to report B and both of these are
>> >> > deployed
>> >> > to
>> >> > the 'Deployment' folder which is only visible to developers. We
>> >> > then
>> >> > create
>> >> > links, AL and BL, to both A and B, in another folder called
>> >> > 'Runtime'
>> >> > folder
>> >> > which is visible to end users. End users are able to execute report
>> >> > AL
>> >> > fine
>> >> > but when they try to jump-to BL, report manager looks for BL in the
>> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore,
>> >> > errors
>> >> > are
>> >> > thrown because users don't have access to the 'Deployment Folder'.
>> >> > How do we force the jump to action on linked reports to remain in
>> >> > the
>> >> > current folder?
>> >> > Obviously this seems to work fine if we are not using linked
>> >> > reports.
>> >> > Hope this makes sense and any advice would be appreciated.
>> >> > TIA
>> >> >
>> >>
>> >>
>> >>
>>|||Both really, but primarily users will access the reports directly through
report manager.
"Bruce L-C [MVP]" wrote:
> Is the user going to this or are you integrating into your own app. It
> sounds like here you are integrating it into your app.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> news:8B07B2C4-34CA-461B-929F-6DBEB4B44AAC@.microsoft.com...
> >I guess we can but we need to be able to specify different parameters for
> > each report depending on who is viewing it.
> >
> > Does that make sense or do you think we are going about things in the
> > wrong
> > way?
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Do you realize that instead of all these links that you can set the
> >> report
> >> permissions so that based on the role they only see the reports they have
> >> rights to view?
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> >> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
> >> > Thanks Bruce.
> >> >
> >> > But to further complicate matters...
> >> >
> >> > If we only want to deploy one version of the main report, how would
> >> > this
> >> > work if we wanted to have multiple links to the reports, i.e. one in
> >> > 'Runtime' folder and one in 'Runtime2' folder, with different access
> >> > permissions on each folder?
> >> >
> >> > Cheers
> >> >
> >> > "Bruce L-C [MVP]" wrote:
> >> >
> >> >> What you want to be doing is doing a jump to the linked report, not
> >> >> the
> >> >> base
> >> >> report. Instead of the name of the report (which assumes it is in the
> >> >> current folder, i.e. Deployment folder in your case, do this instead:
> >> >>
> >> >> /Runtime/TheReportYouWant
> >> >>
> >> >> This will allow you to jump to the link report as you want. Note that
> >> >> it
> >> >> means you have to deploy to test the link, you won't be able to test
> >> >> from
> >> >> within Report Designer.
> >> >>
> >> >>
> >> >> --
> >> >> Bruce Loehle-Conger
> >> >> MVP SQL Server Reporting Services
> >> >>
> >> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> >> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> >> >> >I have a problem using the jump-to feature in a linked report.
> >> >> > We are deploying all reports to a single folder 'Deployment' (not
> >> >> > visible
> >> >> > to
> >> >> > the browser role) and then creating links to these reports in
> >> >> > folders
> >> >> > that
> >> >> > are visible to the browser role. So from a maintenance point of
> >> >> > view,
> >> >> > there
> >> >> > is only ever one version of the report to update.
> >> >> > So we have report A that jumps-to report B and both of these are
> >> >> > deployed
> >> >> > to
> >> >> > the 'Deployment' folder which is only visible to developers. We
> >> >> > then
> >> >> > create
> >> >> > links, AL and BL, to both A and B, in another folder called
> >> >> > 'Runtime'
> >> >> > folder
> >> >> > which is visible to end users. End users are able to execute report
> >> >> > AL
> >> >> > fine
> >> >> > but when they try to jump-to BL, report manager looks for BL in the
> >> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore,
> >> >> > errors
> >> >> > are
> >> >> > thrown because users don't have access to the 'Deployment Folder'.
> >> >> > How do we force the jump to action on linked reports to remain in
> >> >> > the
> >> >> > current folder?
> >> >> > Obviously this seems to work fine if we are not using linked
> >> >> > reports.
> >> >> > Hope this makes sense and any advice would be appreciated.
> >> >> > TIA
> >> >> >
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>|||One thing you can do is use the global variable User!UserID. Then query a
table that holds the report configuration options in a table. Query
parameters do not have to map to report parameters.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
news:620FB6AB-309D-4B45-85F7-296867EBD074@.microsoft.com...
> Both really, but primarily users will access the reports directly through
> report manager.
> "Bruce L-C [MVP]" wrote:
>> Is the user going to this or are you integrating into your own app. It
>> sounds like here you are integrating it into your app.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> news:8B07B2C4-34CA-461B-929F-6DBEB4B44AAC@.microsoft.com...
>> >I guess we can but we need to be able to specify different parameters
>> >for
>> > each report depending on who is viewing it.
>> >
>> > Does that make sense or do you think we are going about things in the
>> > wrong
>> > way?
>> >
>> > "Bruce L-C [MVP]" wrote:
>> >
>> >> Do you realize that instead of all these links that you can set the
>> >> report
>> >> permissions so that based on the role they only see the reports they
>> >> have
>> >> rights to view?
>> >>
>> >>
>> >> --
>> >> Bruce Loehle-Conger
>> >> MVP SQL Server Reporting Services
>> >>
>> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> >> news:B108D0AF-8A60-4001-BA8D-99ACDC4A945E@.microsoft.com...
>> >> > Thanks Bruce.
>> >> >
>> >> > But to further complicate matters...
>> >> >
>> >> > If we only want to deploy one version of the main report, how would
>> >> > this
>> >> > work if we wanted to have multiple links to the reports, i.e. one in
>> >> > 'Runtime' folder and one in 'Runtime2' folder, with different access
>> >> > permissions on each folder?
>> >> >
>> >> > Cheers
>> >> >
>> >> > "Bruce L-C [MVP]" wrote:
>> >> >
>> >> >> What you want to be doing is doing a jump to the linked report, not
>> >> >> the
>> >> >> base
>> >> >> report. Instead of the name of the report (which assumes it is in
>> >> >> the
>> >> >> current folder, i.e. Deployment folder in your case, do this
>> >> >> instead:
>> >> >>
>> >> >> /Runtime/TheReportYouWant
>> >> >>
>> >> >> This will allow you to jump to the link report as you want. Note
>> >> >> that
>> >> >> it
>> >> >> means you have to deploy to test the link, you won't be able to
>> >> >> test
>> >> >> from
>> >> >> within Report Designer.
>> >> >>
>> >> >>
>> >> >> --
>> >> >> Bruce Loehle-Conger
>> >> >> MVP SQL Server Reporting Services
>> >> >>
>> >> >> "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
>> >> >> news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
>> >> >> >I have a problem using the jump-to feature in a linked report.
>> >> >> > We are deploying all reports to a single folder 'Deployment' (not
>> >> >> > visible
>> >> >> > to
>> >> >> > the browser role) and then creating links to these reports in
>> >> >> > folders
>> >> >> > that
>> >> >> > are visible to the browser role. So from a maintenance point of
>> >> >> > view,
>> >> >> > there
>> >> >> > is only ever one version of the report to update.
>> >> >> > So we have report A that jumps-to report B and both of these are
>> >> >> > deployed
>> >> >> > to
>> >> >> > the 'Deployment' folder which is only visible to developers. We
>> >> >> > then
>> >> >> > create
>> >> >> > links, AL and BL, to both A and B, in another folder called
>> >> >> > 'Runtime'
>> >> >> > folder
>> >> >> > which is visible to end users. End users are able to execute
>> >> >> > report
>> >> >> > AL
>> >> >> > fine
>> >> >> > but when they try to jump-to BL, report manager looks for BL in
>> >> >> > the
>> >> >> > 'Deployment' folder instead of the 'Runtime' folder, therefore,
>> >> >> > errors
>> >> >> > are
>> >> >> > thrown because users don't have access to the 'Deployment
>> >> >> > Folder'.
>> >> >> > How do we force the jump to action on linked reports to remain in
>> >> >> > the
>> >> >> > current folder?
>> >> >> > Obviously this seems to work fine if we are not using linked
>> >> >> > reports.
>> >> >> > Hope this makes sense and any advice would be appreciated.
>> >> >> > TIA
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>|||I have an identical Management requirement (meaning the higher levels of
management have decided to do this, and told me to figure out how).
Like trmpalmer, I published all me report to a "Production Reports" folder,
hidden from browsers. Like trmpalmer, I have been instructed to create
various folders based on management dictated groups, which will also be
duplicated in ADS to give specific groups access to specific reports
designated for that group/department.
Also like trmpalmer, some of my reports use Jump-To to reach other reports.
I tried relative pathing, i.e. " ./Report_2_Name " but that didn't help.
Linked Reports is ideal for me becasue of the administration issue of
maintaining multiple copies of the same report, versus maintaining a single
report and linking to that master copy. I am really hopng to find a solution
that will allow me to use Linked Reports and Jump-To-Report at the same time.
"trmpalmer" wrote:
> Thanks Bruce.
> But to further complicate matters...
> If we only want to deploy one version of the main report, how would this
> work if we wanted to have multiple links to the reports, i.e. one in
> 'Runtime' folder and one in 'Runtime2' folder, with different access
> permissions on each folder?
> Cheers
> "Bruce L-C [MVP]" wrote:
> > What you want to be doing is doing a jump to the linked report, not the base
> > report. Instead of the name of the report (which assumes it is in the
> > current folder, i.e. Deployment folder in your case, do this instead:
> >
> > /Runtime/TheReportYouWant
> >
> > This will allow you to jump to the link report as you want. Note that it
> > means you have to deploy to test the link, you won't be able to test from
> > within Report Designer.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> > news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> > >I have a problem using the jump-to feature in a linked report.
> > > We are deploying all reports to a single folder 'Deployment' (not visible
> > > to
> > > the browser role) and then creating links to these reports in folders that
> > > are visible to the browser role. So from a maintenance point of view,
> > > there
> > > is only ever one version of the report to update.
> > > So we have report A that jumps-to report B and both of these are deployed
> > > to
> > > the 'Deployment' folder which is only visible to developers. We then
> > > create
> > > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > > folder
> > > which is visible to end users. End users are able to execute report AL
> > > fine
> > > but when they try to jump-to BL, report manager looks for BL in the
> > > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > > thrown because users don't have access to the 'Deployment Folder'.
> > > How do we force the jump to action on linked reports to remain in the
> > > current folder?
> > > Obviously this seems to work fine if we are not using linked reports.
> > > Hope this makes sense and any advice would be appreciated.
> > > TIA
> > >
> >
> >
> >|||I just found a solution, and I wanted to share it here, and I will make a
seperate post.
To accomplish the goal of having a Linked Report perform a Jump-To with a
target report within the same directory as the initial linked report (the
target can also be a linked report), you need some means of "relative
pathing". This can be achieved with the following placed in the Jump to
Report field (click on the fx button or type directly):
=Globals!ReportFolder + "/" + "ReportName"
This will grab the folder the report being run is in. So if you have a
report in runtime2, it would show "/runtime2/ReportName", but would show
"/runtime/ReportName" when run from the runtime folder.
"trmpalmer" wrote:
> Thanks Bruce.
> But to further complicate matters...
> If we only want to deploy one version of the main report, how would this
> work if we wanted to have multiple links to the reports, i.e. one in
> 'Runtime' folder and one in 'Runtime2' folder, with different access
> permissions on each folder?
> Cheers
> "Bruce L-C [MVP]" wrote:
> > What you want to be doing is doing a jump to the linked report, not the base
> > report. Instead of the name of the report (which assumes it is in the
> > current folder, i.e. Deployment folder in your case, do this instead:
> >
> > /Runtime/TheReportYouWant
> >
> > This will allow you to jump to the link report as you want. Note that it
> > means you have to deploy to test the link, you won't be able to test from
> > within Report Designer.
> >
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> > news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> > >I have a problem using the jump-to feature in a linked report.
> > > We are deploying all reports to a single folder 'Deployment' (not visible
> > > to
> > > the browser role) and then creating links to these reports in folders that
> > > are visible to the browser role. So from a maintenance point of view,
> > > there
> > > is only ever one version of the report to update.
> > > So we have report A that jumps-to report B and both of these are deployed
> > > to
> > > the 'Deployment' folder which is only visible to developers. We then
> > > create
> > > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > > folder
> > > which is visible to end users. End users are able to execute report AL
> > > fine
> > > but when they try to jump-to BL, report manager looks for BL in the
> > > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > > thrown because users don't have access to the 'Deployment Folder'.
> > > How do we force the jump to action on linked reports to remain in the
> > > current folder?
> > > Obviously this seems to work fine if we are not using linked reports.
> > > Hope this makes sense and any advice would be appreciated.
> > > TIA
> > >
> >
> >
> >|||Thanks a million!!
Just what I was looking for :-)
"Rob 'Spike' Stevens" wrote:
> I just found a solution, and I wanted to share it here, and I will make a
> seperate post.
> To accomplish the goal of having a Linked Report perform a Jump-To with a
> target report within the same directory as the initial linked report (the
> target can also be a linked report), you need some means of "relative
> pathing". This can be achieved with the following placed in the Jump to
> Report field (click on the fx button or type directly):
> =Globals!ReportFolder + "/" + "ReportName"
> This will grab the folder the report being run is in. So if you have a
> report in runtime2, it would show "/runtime2/ReportName", but would show
> "/runtime/ReportName" when run from the runtime folder.
> "trmpalmer" wrote:
> > Thanks Bruce.
> >
> > But to further complicate matters...
> >
> > If we only want to deploy one version of the main report, how would this
> > work if we wanted to have multiple links to the reports, i.e. one in
> > 'Runtime' folder and one in 'Runtime2' folder, with different access
> > permissions on each folder?
> >
> > Cheers
> >
> > "Bruce L-C [MVP]" wrote:
> >
> > > What you want to be doing is doing a jump to the linked report, not the base
> > > report. Instead of the name of the report (which assumes it is in the
> > > current folder, i.e. Deployment folder in your case, do this instead:
> > >
> > > /Runtime/TheReportYouWant
> > >
> > > This will allow you to jump to the link report as you want. Note that it
> > > means you have to deploy to test the link, you won't be able to test from
> > > within Report Designer.
> > >
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "trmpalmer" <trmpalmer@.discussions.microsoft.com> wrote in message
> > > news:041781FC-7486-4AC6-A689-EF00533DED12@.microsoft.com...
> > > >I have a problem using the jump-to feature in a linked report.
> > > > We are deploying all reports to a single folder 'Deployment' (not visible
> > > > to
> > > > the browser role) and then creating links to these reports in folders that
> > > > are visible to the browser role. So from a maintenance point of view,
> > > > there
> > > > is only ever one version of the report to update.
> > > > So we have report A that jumps-to report B and both of these are deployed
> > > > to
> > > > the 'Deployment' folder which is only visible to developers. We then
> > > > create
> > > > links, AL and BL, to both A and B, in another folder called 'Runtime'
> > > > folder
> > > > which is visible to end users. End users are able to execute report AL
> > > > fine
> > > > but when they try to jump-to BL, report manager looks for BL in the
> > > > 'Deployment' folder instead of the 'Runtime' folder, therefore, errors are
> > > > thrown because users don't have access to the 'Deployment Folder'.
> > > > How do we force the jump to action on linked reports to remain in the
> > > > current folder?
> > > > Obviously this seems to work fine if we are not using linked reports.
> > > > Hope this makes sense and any advice would be appreciated.
> > > > TIA
> > > >
> > >
> > >
> > >

Joins Across Servers/Databases

I am trying to determine how best to create a query that will allow me to perform 'joins across servers'.

I want to create a single join that will join my production database (Oracle) with my Land Database (MSSQL) on a common key.

From what I have gathered on other sites, it appears that SQLServer can perform this function, but I am not that technical and I am not a SQL programmer (eventhough I have access to one), and I am hoping there are some point and click apps that will let me perform this function.

Any thoughts ?Hello,

the best way to join two databases for example from Oracle to MSSQL is to use ADBC. ADBC is a software package that expands the Oracle database. Once installed, you can select the MSSQL tables that should be visible in Oracle.

If you like, we can assist you installing ADBC and developing the necessary functions for MSSQL to Oracle access.

Please send me a mail to m.peter@.alligatorsql.com if you need further help in this way.

Hope that helps ?

Manfred Peter
(Alligator Company Software GmbH)
http://www.alligatorsql.com

Joining two fields to single field

If I have a database table with the following columns:
ID
Other_ID
Description

And I want to join the two ID fields to one field in another table that contains the following fields:
ID
Name

How would i do that?

Here is some sample data and what I would like returned

TABLE1

ID Other_ID Description
row 1 1 2 Number1
row 2 3 1 Number2

TABLE2

ID Name
row 1 1 John
row 2 2 Bob
row 3 3 Bill

I want to query TABLE1, row 1 so that I pull back the Names for the values stored in the ID and Other_ID fields so that my results are like:
John Bob Number1

The only way around it now is that I store Other_Name in Table1.

Thanks.

try this

select (select name from Table2 t2 where t1.id=t2.id) ,
(select name from Table2 t2 where t1.Other_id=t2.id) ,Description
from Table1 t1|||

I'll take a stab at it:

SELECT T2.Name as Name1, T3.Name as Name2, Description FROM TABLE1 INNER JOIN TABLE2 AS T2 ON TABLE1.ID=T2.ID INNER JOIN TABLE2 AS T3 ON TABLE1.Other_ID=T3.ID

|||Thanks. This worked great.sql

Joining two datasets in a report

Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful
responses.
Is it possible to join two datasets into a single report based on a specific
column in each dataset? That is, if I have dataset 'dspatient' and dataset
'dsvisits', I'd like to join the two separate datasets on patient_id which
is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are
created by two different datasources.
Any help would be greatly appreciated.
Thank you,
JayThis is a multi-part message in MIME format.
--=_NextPart_000_003E_01C53E7B.9989C690
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: 7bit
Jay,
Do both of the datasets live on the same server but just different
databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message--
From: Jason [mailto:jason@.grossmans.net]
Posted At: Friday, April 08, 2005 6:15 PM
Posted To: microsoft.public.sqlserver.reportingsvcs
Conversation: Joining two datasets in a report
Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful
responses.
Is it possible to join two datasets into a single report based on a
specific column in each dataset? That is, if I have dataset 'dspatient'
and dataset 'dsvisits', I'd like to join the two separate datasets on
patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are
created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay
--=_NextPart_000_003E_01C53E7B.9989C690
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
&

---

Dataset clarification
Jay,
Do both of the datasets live on the same server but =just different databases? I'm assuming that since you are using =SQL Server 2000?
Mark
--Original Message--
From: Jason [mailto:jason@.grossmans.net] =Posted At: Friday, April 08, 2005 6:15 PM
Posted To: =microsoft.public.sqlserver.reportingsvcs
Conversation: Joining two datasets in a report
Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I =have dataset 'dspatient' and dataset 'dsvisits', I'd like to join the =two separate datasets on patient_id which is present in each dataset and =display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay

---

--=_NextPart_000_003E_01C53E7B.9989C690--|||This is a multi-part message in MIME format.
--=_NextPart_000_002E_01C53E77.5A06E9C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationOne database is on sqlserver 2000 and the other is =on a vms/vax box, but both datasets are currently in one report (I'm not =sure how I'd store a dataset in a database directly). So, basically I =can get two separate datasets into my report, but cannot make a join on =these datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful =responses.
Is it possible to join two datasets into a single report based on a =specific column in each dataset? That is, if I have dataset 'dspatient' =and dataset 'dsvisits', I'd like to join the two separate datasets on =patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets are =created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_002E_01C53E77.5A06E9C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Dataset clarification
One database is on sqlserver 2000 and =the other is on a vms/vax box, but both datasets are currently in one report (I'm not =sure how I'd store a dataset in a database directly). So, basically I =can get two separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but =just different databases? I'm assuming that since you are using SQL =Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: =microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a =report Subject: Joining two datasets in a report = Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I have dataset = 'dspatient' and dataset 'dsvisits', I'd like to join the two separate =datasets on patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay

---

--=_NextPart_000_002E_01C53E77.5A06E9C0--|||This is a multi-part message in MIME format.
--=_NextPart_000_0071_01C53E82.4266EF80
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationYou cannot joing two datasets together. You have =two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use =openquery and not 4 part naming if using linked databases). Or you can =use subreports.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services
"Jason" <jason@.grossmans.net> wrote in message =news:eOJtlmqPFHA.2132@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and the other is on a vms/vax box, =but both datasets are currently in one report (I'm not sure how I'd =store a dataset in a database directly). So, basically I can get two =separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any helpful =responses.
Is it possible to join two datasets into a single report based on a =specific column in each dataset? That is, if I have dataset 'dspatient' =and dataset 'dsvisits', I'd like to join the two separate datasets on =patient_id which is present in each dataset and display the results.
How might I accomplish this? I should also mention these datasets =are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_0071_01C53E82.4266EF80
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Dataset clarification
You cannot joing two datasets together. =You have two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use openquery and =not 4 part naming if using linked databases). Or you can use =subreports.
-- Bruce Loehle-Conger MVP =SQL Server Reporting Services
"Jason" wrote =in message news:eOJtlmqPFHA.2132=@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and =the other is on a vms/vax box, but both datasets are currently in one report =(I'm not sure how I'd store a dataset in a database directly). So, =basically I can get two separate datasets into my report, but cannot make a join =on these datasets for the report.

Thanks!


"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server =but just different databases? I'm assuming that since you are using SQL =Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: =microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a =report Subject: Joining two datasets in a report = Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a single =report based on a specific column in each dataset? That is, if I have =dataset 'dspatient' and dataset 'dsvisits', I'd like to join the two =separate datasets on patient_id which is present in each dataset and display =the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay =

---

--=_NextPart_000_0071_01C53E82.4266EF80--|||This is a multi-part message in MIME format.
--=_NextPart_000_0067_01C53E7C.A4688FF0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Dataset clarificationAlright, thanks. I'll work on the subreport option =hopefully.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message =news:eDZiswqPFHA.3144@.tk2msftngp13.phx.gbl...
You cannot joing two datasets together. You have two choices, do this =in a stored procdure using linked databases (be careful or you can =really have performance problems. You should use openquery and not 4 =part naming if using linked databases). Or you can use subreports.
-- Bruce Loehle-Conger MVP SQL Server Reporting Services
"Jason" <jason@.grossmans.net> wrote in message =news:eOJtlmqPFHA.2132@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 and the other is on a vms/vax box, =but both datasets are currently in one report (I'm not sure how I'd =store a dataset in a database directly). So, basically I can get two =separate datasets into my report, but cannot make a join on these =datasets for the report.
Thanks!
"Mark Monica" <mmonica@.stf.com> wrote in message =news:%231Wvh0pPFHA.2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server but just different =databases? I'm assuming that since you are using SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really appreciate any =helpful responses.
Is it possible to join two datasets into a single report based on =a specific column in each dataset? That is, if I have dataset ='dspatient' and dataset 'dsvisits', I'd like to join the two separate =datasets on patient_id which is present in each dataset and display the =results.
How might I accomplish this? I should also mention these datasets =are created by two different datasources.
Any help would be greatly appreciated.
Thank you,
Jay --=_NextPart_000_0067_01C53E7C.A4688FF0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Dataset clarification
Alright, thanks. I'll work on the =subreport option hopefully.
"Bruce L-C [MVP]" wrote in message news:eDZiswqPFHA.3144=@.tk2msftngp13.phx.gbl...
You cannot joing two datasets =together. You have two choices, do this in a stored procdure using linked databases (be =careful or you can really have performance problems. You should use openquery =and not 4 part naming if using linked databases). Or you can use subreports.

-- Bruce Loehle-Conger =MVP SQL Server Reporting Services

"Jason" =wrote in message news:eOJtlmqPFHA.2132=@.TK2MSFTNGP14.phx.gbl...
One database is on sqlserver 2000 =and the other is on a vms/vax box, but both datasets are currently in one report =(I'm not sure how I'd store a dataset in a database directly). So, =basically I can get two separate datasets into my report, but cannot make a join =on these datasets for the report.

Thanks!


"Mark Monica" wrote in =message news:%231Wvh0pPFHA.=2932@.TK2MSFTNGP09.phx.gbl...
Jay,
Do both of the datasets live on the same server =but just different databases? I'm assuming that since you are using =SQL Server 2000?
Mark
--Original Message-- From: Jason [mailto:jason@.grossmans.net] Posted At: Friday, April 08, 2005 6:15 =PM Posted To: microsoft.public.sqlserver.reportingsvcs Conversation: Joining two datasets in a report =Subject: Joining two datasets in a report
Good afternoon,
This is actually quite urgent and I'd really =appreciate any helpful responses.
Is it possible to join two datasets into a =single report based on a specific column in each dataset? That is, if I =have dataset 'dspatient' and dataset 'dsvisits', I'd like to join the =two separate datasets on patient_id which is present in each dataset =and display the results.
How might I accomplish this? I should also =mention these datasets are created by two different datasources. = Any help would be greatly appreciated. = Thank you,
Jay

---

--=_NextPart_000_0067_01C53E7C.A4688FF0--sql

Join to a single date rate table?

Given an exchange rate table like:
Create Table XRates
(
EffectiveDate smalldatetime,
FromCury char(3),
ToCury char(3),
Rate float
)
and Transaction table like
Create Table Trans
(
TranDate smalldatetime,
TranCury char(3),
TranAmt float
)
What would be the best SQL query to join from the Transaction table to the
correct exchange rate based on the transaction date? Any sugestions?
Thanks in advanceHere is one way (SQL Server 2005):
WITH Transactions
AS
(SELECT T.TranDate, T.TranCury, T.TranAmt,
R.ToCury, R.Rate,
ROW_NUMBER() OVER(
PARTITION BY R.FromCury
ORDER BY R.EffectiveDate DESC) AS seq
FROM Trans AS T
JOIN XRates AS R
ON T.TranCury = R.FromCury
AND T.TranDate >= R.EffectiveDate)
SELECT TranDate, TranCury, TranAmt, ToCury, Rate
FROM Transactions
WHERE seq = 1;
HTH,
Plamen Ratchev
http://www.SQLStudio.com

join on column with different data types

Hi.
I have a query where a single column is used as join key.
In TableA the column is nvarchar and in TableB it is varchar.
TableB has a clustered index on that column but the join will do a full
table scan
on TableB.
When I convert the coresponding column in TableA to varchar the query use
the index.
The join query will not do an implicit convert between varchar and
nvarchar.
Perhaps as designed.
Where can I find documentation on this issue.
I have been searching books online and Googles but haven't found it.
Sqlserver 2000 or 7.0
--
/dg
----
Dan van Ginhoven
SchlumbergerSema AB
S-412 97 GÖTEBORG Sweden
Phone +46 317 51 44 13
Mob/Cell +46 708 51 44 13convert(varchar(10, column_tableA) = column_tableB
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||There is a chart which shows which data types are implicitly or explicitly
convertable in books on line search for convert... The chart indicates that
SQL can implicitly convert between nchar/nvarchar and char/varchar
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uwiH8IuPDHA.2480@.tk2msftngp13.phx.gbl...
> Hi.
> I have a query where a single column is used as join key.
> In TableA the column is nvarchar and in TableB it is varchar.
> TableB has a clustered index on that column but the join will do a full
> table scan
> on TableB.
> When I convert the coresponding column in TableA to varchar the query use
> the index.
> The join query will not do an implicit convert between varchar and
> nvarchar.
> Perhaps as designed.
> Where can I find documentation on this issue.
> I have been searching books online and Googles but haven't found it.
> Sqlserver 2000 or 7.0
> --
> /dg
> ----
> Dan van Ginhoven
> SchlumbergerSema AB
> S-412 97 GÖTEBORG Sweden
> Phone +46 317 51 44 13
> Mob/Cell +46 708 51 44 13
>|||Hi Wayne!
Yes I have seen the chart. It surprises me a bit that the query didn't use
the index.
It may be bug.
I´m looking for a text that describes in what situations the Query Planner
will not use an
index, but will do a full table scan. I think I have seen it once.
One example is when a query contains <column> like '%value%'
it won´t use an index on that column to solve the query.
/dg|||> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
Nope, it is how SQL Server works. Although you don't have to write any code
for an implicit conversion SQL Server still converts one datatype to another
when it creates the execution plan. If you run the following code in Query
Analyzer and look at the execution plan you will see that #B.B is converted
before the two tables are joined:
CREATE TABLE #A (A nvarchar(20))
GO
CREATE TABLE #B (B varchar(20))
GO
SELECT * FROM #A
INNER JOIN #B
ON #A.A = #B.B
GO
DROP TABLE #A, #B
GO
Why is #B.B converted and not #A.A? That is determined by the Data Type
Precedence. varchar has a lower Data Type Precedence than nvarchar, so
varchar gets converted. (You can find the complete list of the data type
precedence in Books Online under Data Type Precedence).
Because #B.B is used in a function (when it is converted), the Query
Optimizer can't use any indexes on the column and has to use a table scan.
As you saw when the other (nvarchar) column in the join is explicitly
converted, the varchar column won't be implicitly converted and the index on
the varchar column can be used.
About a text: Kalen Delaney has written a series of articles for SQL Server
Magazine (www.sqlmag.com) about which search conditions can make use of
indexes and which don't, and there is also a bit about it in her book Inside
SQL Server 2000.
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Dan van Ginhoven" <nospam@.got.sema.se> wrote in message
news:uPHxWevPDHA.1720@.TK2MSFTNGP11.phx.gbl...
> Hi Wayne!
> Yes I have seen the chart. It surprises me a bit that the query didn't use
> the index.
> It may be bug.
> I´m looking for a text that describes in what situations the Query
Planner
> will not use an
> index, but will do a full table scan. I think I have seen it once.
> One example is when a query contains <column> like '%value%'
> it won´t use an index on that column to solve the query.
> /dg
>