problems displaying images

Hi
I have a query that returns a companyname eg. "ACD". This info is then saved in a parameter.
On the logo (which depends on company), I use this parameter value to the pick up the right picture like this:

=Parameter!.company.Value &"_logo.jpg"

The image is there on the server an the path is correct, but the image is not displayed!
What is wrong?

Hi,

Under what account does your reporting services run? Have you checked that the account has the correct permissions to browse to the location. You can test it by setting a static image on your report and set its url to the location you want.

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Yes, if i for example sets the path ACD_logo.jpg it works.
Dynamically it doesn't work. Neither in client or on the web.

|||I've had similar problems. Have you tried hosting the images on a website and using a URL to access the images instead of a local windows path?|||

No, i have not tried that.
Maybe I should.

Is there any way to get the base URL to the report?

Ex rs.base.url ?

|||

I think you're misunderstanding. Use a URL to access the LOGOs (not the report). In other words, host your images on a website and access them from there instead of from windows.

If you don't have a website, set up an account at http://photobucket.com/ and host your images there. It's quick and simple.

|||

Hi

It worked to put the images on a webserver.

I still though wonder why it doesn′t work when the image is an local external link e.g "ACD_logo.jpg" instead of ′the webserversolution eg. http://localhost:4000/reportimgs/ACD_logo.jpg

Thanks for the help!

/A

Problem: Using Custom Code to provide default values for parameter

Hi, I have reports with parameters that use custom code, and when you hide
the parameters in the report manager the reports no longer work.
I thought it was a parameter dependancy problem, but have done the following
test to prove its not:
I have just created a report, with one parameter called prmUserID that gets
its value from my dll. The default non-queried value for this parameter is
set as follows:
=libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True , False,
False )
The report displays a table, and the table references a dataset that uses
the parameter value. This is the string for the dataset:
="Select * from mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
The report works fine when I deploy it to the report
manager. The parameter shows the value returned from my
dll correctly.
However, I changed the parameter properties of my report in report manager
and took the tick out of the prompt user checkbox. The report no longer
works - I'm pretty sure that my prmUserID is just parsing an empty string now
to the dataset. Maybe once you've hidden a parameter the code from my dll is
no longer called?
I also know its not a problem with my dll as I did the same test above but
used custom code directly in the report (ie added to the code tab of the
report properties dialog). This produced the same result - deployed it,
worked fine, hide the parameter and the code no longer seems to run.
I would be grateful if someone at MS could try and replicate this and let me
know if I'm doing something wrong or if its a bug.
My Firm is VERY keen to move forward with Reporting Services, the printing
issue (which I know is being addressed in SP2) and this parameter problem is
the only thing holding us back at the moment.
Thanks
JeanineHave you installed SP1?... I think that hidden parameters are NOT settable
unless you have SP1 installed...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for you reply, yes I do have SP1 installed.
"Wayne Snyder" wrote:
> Have you installed SP1?... I think that hidden parameters are NOT settable
> unless you have SP1 installed...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||You need to change the report parameter property. Set the PROMPT string to
"" and the prompt value to TRUE.
It will not display the prompt but at least it will be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> Hi, I have reports with parameters that use custom code, and when you hide
> the parameters in the report manager the reports no longer work.
> I thought it was a parameter dependancy problem, but have done the
> following
> test to prove its not:
> I have just created a report, with one parameter called prmUserID that
> gets
> its value from my dll. The default non-queried value for this parameter
> is
> set as follows:
> =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> False,
> False )
> The report displays a table, and the table references a dataset that uses
> the parameter value. This is the string for the dataset:
> ="Select * from mhgroup.docusers where userid='" &
> Parameters!prmUserID.Value & "'"
> The report works fine when I deploy it to the report
> manager. The parameter shows the value returned from my
> dll correctly.
> However, I changed the parameter properties of my report in report manager
> and took the tick out of the prompt user checkbox. The report no longer
> works - I'm pretty sure that my prmUserID is just parsing an empty string
> now
> to the dataset. Maybe once you've hidden a parameter the code from my dll
> is
> no longer called?
> I also know its not a problem with my dll as I did the same test above but
> used custom code directly in the report (ie added to the code tab of the
> report properties dialog). This produced the same result - deployed it,
> worked fine, hide the parameter and the code no longer seems to run.
> I would be grateful if someone at MS could try and replicate this and let
> me
> know if I'm doing something wrong or if its a bug.
> My Firm is VERY keen to move forward with Reporting Services, the printing
> issue (which I know is being addressed in SP2) and this parameter problem
> is
> the only thing holding us back at the moment.
> Thanks
> Jeanine|||Thanks for that. I tried that and it worked - as long as you don't then go
and edit the report parameters in report manager.
What I would really like to do is have linked reports, one with a boolean
parameter set to true and one with a boolean parameter set to false.
So as soon as I use the report manager to alter the report parameter
properties (even though I've done you've said below), the reports don't work
properly again.
I still think this is a bug of some kind, and am hoping MS come up with a
fix for this.
Jeanine
"Christopher Conner" wrote:
> You need to change the report parameter property. Set the PROMPT string to
> "" and the prompt value to TRUE.
> It will not display the prompt but at least it will be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> > Hi, I have reports with parameters that use custom code, and when you hide
> > the parameters in the report manager the reports no longer work.
> >
> > I thought it was a parameter dependancy problem, but have done the
> > following
> > test to prove its not:
> >
> > I have just created a report, with one parameter called prmUserID that
> > gets
> > its value from my dll. The default non-queried value for this parameter
> > is
> > set as follows:
> >
> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> > False,
> > False )
> >
> > The report displays a table, and the table references a dataset that uses
> > the parameter value. This is the string for the dataset:
> >
> > ="Select * from mhgroup.docusers where userid='" &
> > Parameters!prmUserID.Value & "'"
> >
> > The report works fine when I deploy it to the report
> > manager. The parameter shows the value returned from my
> > dll correctly.
> >
> > However, I changed the parameter properties of my report in report manager
> > and took the tick out of the prompt user checkbox. The report no longer
> > works - I'm pretty sure that my prmUserID is just parsing an empty string
> > now
> > to the dataset. Maybe once you've hidden a parameter the code from my dll
> > is
> > no longer called?
> >
> > I also know its not a problem with my dll as I did the same test above but
> > used custom code directly in the report (ie added to the code tab of the
> > report properties dialog). This produced the same result - deployed it,
> > worked fine, hide the parameter and the code no longer seems to run.
> >
> > I would be grateful if someone at MS could try and replicate this and let
> > me
> > know if I'm doing something wrong or if its a bug.
> >
> > My Firm is VERY keen to move forward with Reporting Services, the printing
> > issue (which I know is being addressed in SP2) and this parameter problem
> > is
> > the only thing holding us back at the moment.
> >
> > Thanks
> > Jeanine
>
>|||Well first off - users shouldn't have rights to modify the report
parameters.
Second, I set those properties in the report designer - not off the report
manager. It would be a pain to update those reports everytime I did a
deployment.
What happens when you do this inside your report designer with your linked
report?
I'll see if I can replicate your issue here, but otherwise I haven't run
into any problems - albeit just a few quirks like the solution I just gave
you to not show parameters but yet allow them to be updatable.
=-Chris
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> Thanks for that. I tried that and it worked - as long as you don't then
> go
> and edit the report parameters in report manager.
> What I would really like to do is have linked reports, one with a boolean
> parameter set to true and one with a boolean parameter set to false.
> So as soon as I use the report manager to alter the report parameter
> properties (even though I've done you've said below), the reports don't
> work
> properly again.
> I still think this is a bug of some kind, and am hoping MS come up with a
> fix for this.
> Jeanine
>
> "Christopher Conner" wrote:
>> You need to change the report parameter property. Set the PROMPT string
>> to
>> "" and the prompt value to TRUE.
>> It will not display the prompt but at least it will be updatable.
>> =-Chris
>>
>> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
>> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
>> > Hi, I have reports with parameters that use custom code, and when you
>> > hide
>> > the parameters in the report manager the reports no longer work.
>> >
>> > I thought it was a parameter dependancy problem, but have done the
>> > following
>> > test to prove its not:
>> >
>> > I have just created a report, with one parameter called prmUserID that
>> > gets
>> > its value from my dll. The default non-queried value for this
>> > parameter
>> > is
>> > set as follows:
>> >
>> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
>> > False,
>> > False )
>> >
>> > The report displays a table, and the table references a dataset that
>> > uses
>> > the parameter value. This is the string for the dataset:
>> >
>> > ="Select * from mhgroup.docusers where userid='" &
>> > Parameters!prmUserID.Value & "'"
>> >
>> > The report works fine when I deploy it to the report
>> > manager. The parameter shows the value returned from my
>> > dll correctly.
>> >
>> > However, I changed the parameter properties of my report in report
>> > manager
>> > and took the tick out of the prompt user checkbox. The report no
>> > longer
>> > works - I'm pretty sure that my prmUserID is just parsing an empty
>> > string
>> > now
>> > to the dataset. Maybe once you've hidden a parameter the code from my
>> > dll
>> > is
>> > no longer called?
>> >
>> > I also know its not a problem with my dll as I did the same test above
>> > but
>> > used custom code directly in the report (ie added to the code tab of
>> > the
>> > report properties dialog). This produced the same result - deployed
>> > it,
>> > worked fine, hide the parameter and the code no longer seems to run.
>> >
>> > I would be grateful if someone at MS could try and replicate this and
>> > let
>> > me
>> > know if I'm doing something wrong or if its a bug.
>> >
>> > My Firm is VERY keen to move forward with Reporting Services, the
>> > printing
>> > issue (which I know is being addressed in SP2) and this parameter
>> > problem
>> > is
>> > the only thing holding us back at the moment.
>> >
>> > Thanks
>> > Jeanine
>>|||Hi,
1) Users will not be modifying report parameters settings. They will
however be able to use the prms that I set as being visible.
2) What I'm trying to achieve is to have 1 main report and create from this,
several linked reports. By linked reports, I'm referring to the ability to
create a linked report from another report in Report manager. These linked
reports would have different parameter settings. This way I can use the
parameter information to tailor 1 main report, dependant upon the parameter
values. This saves me from having to create multiple reports in the report
designer, that contain basically the same information.
I tried your suggestion, with one of my production reports that has quite a
few parameters, and it worked fine. However to achieve 2) I will need to
create from this a linked report, change the parameter settings - which will
involve things such as changing a boolean prm from true to false etc.
If it is indeed a problem it is very easy to replicate, just create a report
with a parameter and set the default value of the parameter to a string
returned from a function in the custom code of the report. Use this
parameter in the datasource of the report, eg, ="Select * from
mhgroup.docusers where userid='" &
Parameters!prmUserID.Value & "'"
Deploy the report, use the report manager to hide the parameter, and the
report should then no longer display the correct information (because the
default value of the parameter was not populated with the custom code value).
Thanks very much.
"Christopher Conner" wrote:
> Well first off - users shouldn't have rights to modify the report
> parameters.
> Second, I set those properties in the report designer - not off the report
> manager. It would be a pain to update those reports everytime I did a
> deployment.
> What happens when you do this inside your report designer with your linked
> report?
> I'll see if I can replicate your issue here, but otherwise I haven't run
> into any problems - albeit just a few quirks like the solution I just gave
> you to not show parameters but yet allow them to be updatable.
> =-Chris
>
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:038B93E0-E09F-487E-877F-FC1F2DCDB0E9@.microsoft.com...
> > Thanks for that. I tried that and it worked - as long as you don't then
> > go
> > and edit the report parameters in report manager.
> >
> > What I would really like to do is have linked reports, one with a boolean
> > parameter set to true and one with a boolean parameter set to false.
> >
> > So as soon as I use the report manager to alter the report parameter
> > properties (even though I've done you've said below), the reports don't
> > work
> > properly again.
> >
> > I still think this is a bug of some kind, and am hoping MS come up with a
> > fix for this.
> >
> > Jeanine
> >
> >
> > "Christopher Conner" wrote:
> >
> >> You need to change the report parameter property. Set the PROMPT string
> >> to
> >> "" and the prompt value to TRUE.
> >>
> >> It will not display the prompt but at least it will be updatable.
> >>
> >> =-Chris
> >>
> >>
> >>
> >> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> >> news:E953EF89-AD1F-408D-9729-AC99C109D784@.microsoft.com...
> >> > Hi, I have reports with parameters that use custom code, and when you
> >> > hide
> >> > the parameters in the report manager the reports no longer work.
> >> >
> >> > I thought it was a parameter dependancy problem, but have done the
> >> > following
> >> > test to prove its not:
> >> >
> >> > I have just created a report, with one parameter called prmUserID that
> >> > gets
> >> > its value from my dll. The default non-queried value for this
> >> > parameter
> >> > is
> >> > set as follows:
> >> >
> >> > =libIntranetReporting.clsGeneral.GetCurrentUser( User!UserID, True ,
> >> > False,
> >> > False )
> >> >
> >> > The report displays a table, and the table references a dataset that
> >> > uses
> >> > the parameter value. This is the string for the dataset:
> >> >
> >> > ="Select * from mhgroup.docusers where userid='" &
> >> > Parameters!prmUserID.Value & "'"
> >> >
> >> > The report works fine when I deploy it to the report
> >> > manager. The parameter shows the value returned from my
> >> > dll correctly.
> >> >
> >> > However, I changed the parameter properties of my report in report
> >> > manager
> >> > and took the tick out of the prompt user checkbox. The report no
> >> > longer
> >> > works - I'm pretty sure that my prmUserID is just parsing an empty
> >> > string
> >> > now
> >> > to the dataset. Maybe once you've hidden a parameter the code from my
> >> > dll
> >> > is
> >> > no longer called?
> >> >
> >> > I also know its not a problem with my dll as I did the same test above
> >> > but
> >> > used custom code directly in the report (ie added to the code tab of
> >> > the
> >> > report properties dialog). This produced the same result - deployed
> >> > it,
> >> > worked fine, hide the parameter and the code no longer seems to run.
> >> >
> >> > I would be grateful if someone at MS could try and replicate this and
> >> > let
> >> > me
> >> > know if I'm doing something wrong or if its a bug.
> >> >
> >> > My Firm is VERY keen to move forward with Reporting Services, the
> >> > printing
> >> > issue (which I know is being addressed in SP2) and this parameter
> >> > problem
> >> > is
> >> > the only thing holding us back at the moment.
> >> >
> >> > Thanks
> >> > Jeanine
> >>
> >>
> >>
>
>|||i myself are having a similar problem
i believe its a BUG in report manager,
that changing any of the options for parameters in report manager
(which is what is needed to be done to make a writable hidden
parameter) causes default calculations (even query based) to not work
for parameters that are not pull down boxes.|||Thanks for that. Its nice to know I'm not the only one experiencing this
problem. I'm hoping somone from MS may be able to shed some light on this as
well.
"klumsy@.xtra.co.nz" wrote:
> i myself are having a similar problem
> i believe its a BUG in report manager,
> that changing any of the options for parameters in report manager
> (which is what is needed to be done to make a writable hidden
> parameter) causes default calculations (even query based) to not work
> for parameters that are not pull down boxes.
>|||Please check this related thread:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
Also check my recent posting in that same thread for a possible workaround
on RS 2000 (specifying hidden parameters through report designer).
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> Thanks for that. Its nice to know I'm not the only one experiencing this
> problem. I'm hoping somone from MS may be able to shed some light on this
> as
> well.
>
> "klumsy@.xtra.co.nz" wrote:
>> i myself are having a similar problem
>> i believe its a BUG in report manager,
>> that changing any of the options for parameters in report manager
>> (which is what is needed to be done to make a writable hidden
>> parameter) causes default calculations (even query based) to not work
>> for parameters that are not pull down boxes.
>>|||Thanks Robert. I'll look forward to the release of Yukon.
"Robert Bruckner [MSFT]" wrote:
> Please check this related thread:
> http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=5d83b939-71ab-47fa-9f85-9d15d90dacb9
> Also check my recent posting in that same thread for a possible workaround
> on RS 2000 (specifying hidden parameters through report designer).
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Jeanine" <Jeanine@.discussions.microsoft.com> wrote in message
> news:F1B09944-8648-49FD-95AE-66004ECD212D@.microsoft.com...
> > Thanks for that. Its nice to know I'm not the only one experiencing this
> > problem. I'm hoping somone from MS may be able to shed some light on this
> > as
> > well.
> >
> >
> > "klumsy@.xtra.co.nz" wrote:
> >
> >> i myself are having a similar problem
> >> i believe its a BUG in report manager,
> >> that changing any of the options for parameters in report manager
> >> (which is what is needed to be done to make a writable hidden
> >> parameter) causes default calculations (even query based) to not work
> >> for parameters that are not pull down boxes.
> >>
> >>
>
>

Problem: Parameter with available values from query

=========================================

dataSource: dsABC (ODBC)

dataSet: dsetABC

=========================================

I have a parameterized report and one of parameter with available values from

query.

The query for the parameter is from another dataset (dsCompany) in order to allow

users choosing the company values. In the Designer platform, it normally provide a

drop-down list including expected company values. But when i upload to reporting

server (http://xxxx/reports) and the following error messages will appear

Reporting processing error (rsProcessingAborted)

...............Cannot connect to DataSource dsABC. (rsErrorOpeningConnection)

...........................................................................

Besides, i can run the report normally if i remove the task of "available values from

query".

How can i solve this problem? Thank you for any opinion.

Is dsCompany using a different datasource? If so, make sure that is deployed to your Report Server also.|||

guyinkalamazoo3 wrote:

Is dsCompany using a different datasource? If so, make sure that is deployed to your Report Server

also.

In fact, the dataset of dsetABC & dsCompany are also used a dataSource "dsABC",

now just the "available values" task make error

in the report. Why? (the report run normally if the "available values" task is removed in

Report Server)

|||

Anyone can give me some suggestions. thank you!

Problem: Non Cascading Params causing report reload on each select

When running a report I am noticing the following behaviour:
As each parameter is selected, the report is doing some kind of postback or
refresh despite the fact that none of the parameters are linked
(cascading/dependent parameters).
Why is this occuring?
It is very annoying to the user as a report with 8 params is reloading 8
times even before they run the report.
Virtually all the params are query based with a default of query based.
config
RS2005 sp1I think this is by design, no way to change, unless write custom code which
is time consuming... may be MS will improve in the furture versions. :-)
Amarnath, MCTS
"adolf garlic" wrote:
> When running a report I am noticing the following behaviour:
> As each parameter is selected, the report is doing some kind of postback or
> refresh despite the fact that none of the parameters are linked
> (cascading/dependent parameters).
> Why is this occuring?
> It is very annoying to the user as a report with 8 params is reloading 8
> times even before they run the report.
> Virtually all the params are query based with a default of query based.
>
> config
> RS2005 sp1

Problem with xp_sendmail and @query parameter

I am having a bit of trouble with a new SQLServer 2000 installation. I am
in the process of swapping out a server (sql sp3) and replacing it with a
new server (sql sp3a). Everything went smoothly until I hit a brick wall
with xp_sendmail.
The problem is when xp_sendmail tries to attach a query (with the @.query
parameter), and the SQL for the query runs on a linked server. Under the
old server, this was never a problem. But now I receive the following error
message:
ODBC error 7410 (42000) Remote access not allowed for Windows NT user
activated by SETUSER.
Also, here is the actual sql stmt which is running:
exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
test',@.subject='Test 1', @.attach_results=true,@.query='select * from
server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
The links to the other server are identical on both the old and new
machines. Any assistance would be greatly appreciated.
Thanks,
BobMost probably the job is owned by someone who isn't sysadmin, and some stuff
aren't allowed to do when the jobs owner isn't sysadmin (stuff at the server
level, as Agent uses SETUSER to emulate the jobowner's user context *in that
particular database*).
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message
news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> I am having a bit of trouble with a new SQLServer 2000 installation. I am
> in the process of swapping out a server (sql sp3) and replacing it with a
> new server (sql sp3a). Everything went smoothly until I hit a brick wall
> with xp_sendmail.
> The problem is when xp_sendmail tries to attach a query (with the @.query
> parameter), and the SQL for the query runs on a linked server. Under the
> old server, this was never a problem. But now I receive the following
error
> message:
> ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> activated by SETUSER.
> Also, here is the actual sql stmt which is running:
> exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> The links to the other server are identical on both the old and new
> machines. Any assistance would be greatly appreciated.
> Thanks,
> Bob
>|||Thanks, but this is not a job. The problem happens in Query Analyzer. If I
run the same SQL Stmt against the old server in Query Analyzer, it works
fine.
-Bob
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> Most probably the job is owned by someone who isn't sysadmin, and some
stuff
> aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
server
> level, as Agent uses SETUSER to emulate the jobowner's user context *in
that
> particular database*).
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
> news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > I am having a bit of trouble with a new SQLServer 2000 installation. I
am
> > in the process of swapping out a server (sql sp3) and replacing it with
a
> > new server (sql sp3a). Everything went smoothly until I hit a brick
wall
> > with xp_sendmail.
> >
> > The problem is when xp_sendmail tries to attach a query (with the @.query
> > parameter), and the SQL for the query runs on a linked server. Under
the
> > old server, this was never a problem. But now I receive the following
> error
> > message:
> >
> > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > activated by SETUSER.
> >
> > Also, here is the actual sql stmt which is running:
> >
> > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> >
> > The links to the other server are identical on both the old and new
> > machines. Any assistance would be greatly appreciated.
> >
> > Thanks,
> >
> > Bob
> >
> >
>|||Perhaps one is executed to text and the other to grid. Grid is faster, and possibly also the text
handling can have been improved in some sp of QA (not sure about this, though). Also, does SET
NOCOUNT ON help.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Bob R." <bobr2@.earthlink.net> wrote in message news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> Thanks, but this is not a job. The problem happens in Query Analyzer. If I
> run the same SQL Stmt against the old server in Query Analyzer, it works
> fine.
> -Bob
>
> "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > Most probably the job is owned by someone who isn't sysadmin, and some
> stuff
> > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> server
> > level, as Agent uses SETUSER to emulate the jobowner's user context *in
> that
> > particular database*).
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > Archive at:
> >
> http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> >
> >
> > "Bob R." <bobr2@.earthlink.net> wrote in message
> > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > I am having a bit of trouble with a new SQLServer 2000 installation. I
> am
> > > in the process of swapping out a server (sql sp3) and replacing it with
> a
> > > new server (sql sp3a). Everything went smoothly until I hit a brick
> wall
> > > with xp_sendmail.
> > >
> > > The problem is when xp_sendmail tries to attach a query (with the @.query
> > > parameter), and the SQL for the query runs on a linked server. Under
> the
> > > old server, this was never a problem. But now I receive the following
> > error
> > > message:
> > >
> > > ODBC error 7410 (42000) Remote access not allowed for Windows NT user
> > > activated by SETUSER.
> > >
> > > Also, here is the actual sql stmt which is running:
> > >
> > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > >
> > > The links to the other server are identical on both the old and new
> > > machines. Any assistance would be greatly appreciated.
> > >
> > > Thanks,
> > >
> > > Bob
> > >
> > >
> >
> >
>|||I ran a trace on both versions, and I can see that the new version (sp3a) is
running "sp_setuserbylogid" before executing the query. The older machine
is on sp3, and does not do this. I looked at the dll for xp_sendmail
between both machines, and it looks like sp3a updated sqlmap70.dll. (This
is not mentioned anywhere in the release notes) I swapped out the dll for
the one from sp3, and everything works fine. However, since I could find no
documentation concerning changes to xp_sendmail in sp3a, I am not sure if
this is a good idea.
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:%236bGUserDHA.372@.TK2MSFTNGP11.phx.gbl...
> Perhaps one is executed to text and the other to grid. Grid is faster, and
possibly also the text
> handling can have been improved in some sp of QA (not sure about this,
though). Also, does SET
> NOCOUNT ON help.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Bob R." <bobr2@.earthlink.net> wrote in message
news:uL3%23d3drDHA.1948@.TK2MSFTNGP12.phx.gbl...
> > Thanks, but this is not a job. The problem happens in Query Analyzer.
If I
> > run the same SQL Stmt against the old server in Query Analyzer, it works
> > fine.
> >
> > -Bob
> >
> >
> > "Tibor Karaszi"
<tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
> > wrote in message news:eg$aOOVrDHA.2772@.TK2MSFTNGP10.phx.gbl...
> > > Most probably the job is owned by someone who isn't sysadmin, and some
> > stuff
> > > aren't allowed to do when the jobs owner isn't sysadmin (stuff at the
> > server
> > > level, as Agent uses SETUSER to emulate the jobowner's user context
*in
> > that
> > > particular database*).
> > >
> > > --
> > > Tibor Karaszi, SQL Server MVP
> > > Archive at:
> > >
> >
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
> > >
> > >
> > > "Bob R." <bobr2@.earthlink.net> wrote in message
> > > news:uoc7zPUrDHA.1488@.TK2MSFTNGP12.phx.gbl...
> > > > I am having a bit of trouble with a new SQLServer 2000 installation.
I
> > am
> > > > in the process of swapping out a server (sql sp3) and replacing it
with
> > a
> > > > new server (sql sp3a). Everything went smoothly until I hit a brick
> > wall
> > > > with xp_sendmail.
> > > >
> > > > The problem is when xp_sendmail tries to attach a query (with the
@.query
> > > > parameter), and the SQL for the query runs on a linked server.
Under
> > the
> > > > old server, this was never a problem. But now I receive the
following
> > > error
> > > > message:
> > > >
> > > > ODBC error 7410 (42000) Remote access not allowed for Windows NT
user
> > > > activated by SETUSER.
> > > >
> > > > Also, here is the actual sql stmt which is running:
> > > >
> > > > exec xp_sendmail @.recipients='bob@.msn.com', @.message='This is a
> > > > test',@.subject='Test 1', @.attach_results=true,@.query='select * from
> > > > server1.sales.dbo.branches', @.no_header=true, @.ansi_attachment=TRUE
> > > >
> > > > The links to the other server are identical on both the old and new
> > > > machines. Any assistance would be greatly appreciated.
> > > >
> > > > Thanks,
> > > >
> > > > Bob
> > > >
> > > >
> > >
> > >
> >
> >
>

Problem with using report parameters in SQL query function, SSRS 2

Using Visual Studio to build reports for Reporting Services 2000: is it
allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
select function(@.variable) from table where ..
I get "syntax error or access violation" when trying.
Regards, JouniI think that question would be more appropriate in the sqlserver.programming
group. With that said...
Have you tried running that query in Query Analyzer?
yeah...I don't think that Sql statement will ever run.
from the Books Online for CREATE FUNCTION (transact SQL):
"@.parameter_name
Is a parameter in the user-defined function. One or more parameters can be
declared.
[...]
Specify a parameter name by using an at sign (@.) as the first character. The
parameter name must comply with the rules for identifiers. Parameters are
local to the function; the same parameter names can be used in other
functions. Parameters can take the place only of constants; they cannot be
used instead of table names, column names, or the names of other database
objects. "
Soooo...assuming your user-defined function is a scalar-value function that
returns field names and is owned by "dbo" schema, try using dynamic sql,
something like this:
DECLARE @.sql varchar(8000)
SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
SET @.sql= @.sql + ' FROM MyTable '
SET @.sql= @.sql + ' WHERE ... '
EXEC @.sql
Give it a shot, and if I am way off...reply and let me know. Hope this help
you.
--
Regards,
Thiago Silva
"Jouni" wrote:
> Using Visual Studio to build reports for Reporting Services 2000: is it
> allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> select function(@.variable) from table where ..
> I get "syntax error or access violation" when trying.
> Regards, Jouni|||Hi Thiago,
haven't had time to test this. Actually I already switched to another
approach: defining the whole query as a db procedure and just calling that:
exec jounis_procedure @.report_parameter
I noticed that Microsoft people have chosen this approach themselves: in
"Report Pack for SPS" all the reports use procedure calls.
Regards,
Jouni
"HC" wrote:
> I think that question would be more appropriate in the sqlserver.programming
> group. With that said...
> Have you tried running that query in Query Analyzer?
> yeah...I don't think that Sql statement will ever run.
> from the Books Online for CREATE FUNCTION (transact SQL):
> "@.parameter_name
> Is a parameter in the user-defined function. One or more parameters can be
> declared.
> [...]
> Specify a parameter name by using an at sign (@.) as the first character. The
> parameter name must comply with the rules for identifiers. Parameters are
> local to the function; the same parameter names can be used in other
> functions. Parameters can take the place only of constants; they cannot be
> used instead of table names, column names, or the names of other database
> objects. "
> Soooo...assuming your user-defined function is a scalar-value function that
> returns field names and is owned by "dbo" schema, try using dynamic sql,
> something like this:
> DECLARE @.sql varchar(8000)
> SET @.sql= 'SELECT ' + dbo.myFn(@.myParam) + ' AS MyDynamicColumn '
> SET @.sql= @.sql + ' FROM MyTable '
> SET @.sql= @.sql + ' WHERE ... '
> EXEC @.sql
> Give it a shot, and if I am way off...reply and let me know. Hope this help
> you.
> --
> Regards,
> Thiago Silva
> "Jouni" wrote:
> > Using Visual Studio to build reports for Reporting Services 2000: is it
> > allowed to pass a report parameter to a FUNCTION in dataset defintion. Like:
> >
> > select function(@.variable) from table where ..
> >
> > I get "syntax error or access violation" when trying.
> >
> > Regards, Jouni

Problem with uniqueidentifeir and union

Hi,
I have a report and a list of products in a parameter field. I also need a
"All" option there, but have problems doing it...
the sql query is following
Select name, productid from filteredproduct where new_team_lty = @.team and
new_alkupaivamaara_lty between convert(smalldatetime, '1/1/' + @.year) and
convert(smalldatetime, '12/31/' + @.year)
union all
Select 'All', 'All'
This would work fine if there was only a name selected. But the productid is
uniqueidentifier type and I get a error message:
"An Error occurred while reading data from the query result set. Syntax
error converting from a character string to uniqueidentifier"
I understand what that means, but the problem is how can I avoid it? I can't
drop the productid out of the query and also need the 'All' option as well...
And I'm out of ideas how to have them both...
Thanks in advance!
RegardsWell...
The solution to this one was pretty simple after all...
CONVERT(varchar(255),productid) was the solution :)|||Ok,
problems again. That convert helped to make that one query to work, but in
the next query where I need that productid, it wont work anymore...