images not showing up on server

I am serving up reports using the web service using the render method.
The reports have images on them. The following scenarios work
.Net application and Reporting Services are all on the same
workstation. Images show up and everything works well.
.Net application on same workstation and I point to Reporting Services
which is on Test environment server A. Images show up and everthing
works well.
This is not working
.Net application is on server B and Reporting Services is on the same
server A. The report shows up but all images show up with the red x.
What can be different between the code on my local workstation and the
code on the server? I have copied all the neccessary files out there.
Is there something on my workstation which has Visual Studio installed
that is not on the server B which only has IIS installed? Or is there
something else I could be missing in the migration to the server
environment?We have found that the report comes up just fine in the server
environment but the image is using different credentials than the user
that is logged in. If we run impersonate as user ABC, all reports come
up fine under ABC, but the image prompts a login box. The image wants
to load under the users that logged in and not the user we use to
impersonate. Any thoughts on how to get around this?

images in HTML reports

How to render image in HTML reports?
I have a webapp which invokes Reporting Services web service to generate
reports in HTML format. The reports are for internet users - the image is not
accessible to these internet users because they cannot access the report
server directly which is in the intranet. Is there a solution to this problem
at all?
Thanks
SanjayYes, although it is not simple. You need to use the RenderStream method to
get the image and put it in a place where it is available to the Internet
users. You need to pass this path into the initial Render() call using the
<StreamRoot> deviceInfo.
For what it is worth, this will be much easier in SQL 2005 RS.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:1A5971D1-5DB8-4B28-8AB1-38DFC1E1C25D@.microsoft.com...
> How to render image in HTML reports?
> I have a webapp which invokes Reporting Services web service to generate
> reports in HTML format. The reports are for internet users - the image is
> not
> accessible to these internet users because they cannot access the report
> server directly which is in the intranet. Is there a solution to this
> problem
> at all?
> Thanks
> Sanjay|||That works for me for the intranet users but not for the internet users
because the location where I can generate the image with RenderStream is not
available on internet.
Including an External Web image could work for me because I'm including the
same banner image in my reports, but when I tried to include an External Web
Image like
http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif
in my report in report designer, I got message 'Invalid Image'. Is that
expected or is it because of some restrictions in my environment?
Thanks
Sanjay
"Brian Welcker [MS]" wrote:
> Yes, although it is not simple. You need to use the RenderStream method to
> get the image and put it in a place where it is available to the Internet
> users. You need to pass this path into the initial Render() call using the
> <StreamRoot> deviceInfo.
> For what it is worth, this will be much easier in SQL 2005 RS.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:1A5971D1-5DB8-4B28-8AB1-38DFC1E1C25D@.microsoft.com...
> > How to render image in HTML reports?
> > I have a webapp which invokes Reporting Services web service to generate
> > reports in HTML format. The reports are for internet users - the image is
> > not
> > accessible to these internet users because they cannot access the report
> > server directly which is in the intranet. Is there a solution to this
> > problem
> > at all?
> >
> > Thanks
> > Sanjay
>
>|||All images in the reports are effectively "internal". URL only means that
the Report Server retrieves the image from a URL. We need to actually store
the image for consistency (caching, snapshots, etc.). We don't have a 'late
bound' image.
I don't understand why you can't copy the image to a directory on the
external web site.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:FEC10F8E-F9C2-4938-9F16-3E3565EA723C@.microsoft.com...
> That works for me for the intranet users but not for the internet users
> because the location where I can generate the image with RenderStream is
> not
> available on internet.
> Including an External Web image could work for me because I'm including
> the
> same banner image in my reports, but when I tried to include an External
> Web
> Image like
> http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif
> in my report in report designer, I got message 'Invalid Image'. Is that
> expected or is it because of some restrictions in my environment?
> Thanks
> Sanjay
> "Brian Welcker [MS]" wrote:
>> Yes, although it is not simple. You need to use the RenderStream method
>> to
>> get the image and put it in a place where it is available to the Internet
>> users. You need to pass this path into the initial Render() call using
>> the
>> <StreamRoot> deviceInfo.
>> For what it is worth, this will be much easier in SQL 2005 RS.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
>> news:1A5971D1-5DB8-4B28-8AB1-38DFC1E1C25D@.microsoft.com...
>> > How to render image in HTML reports?
>> > I have a webapp which invokes Reporting Services web service to
>> > generate
>> > reports in HTML format. The reports are for internet users - the image
>> > is
>> > not
>> > accessible to these internet users because they cannot access the
>> > report
>> > server directly which is in the intranet. Is there a solution to this
>> > problem
>> > at all?
>> >
>> > Thanks
>> > Sanjay
>>|||I can't copy the image to a directory on the external web site because I
don't have access to the report server from the external web site machine as
a result of which I have an intermediate ReportsGeneratorService sitting in
the intranet which generates the reports. In addition I don't have access to
create image files on the external web site machine from the intranet-sitting
ReportsGeneratorService. For Security Reasons. Any suggestions?
Can I manipulate the HTML on the external web-site to pick up the images
from a location in the external web-site?
"Brian Welcker [MS]" wrote:
> All images in the reports are effectively "internal". URL only means that
> the Report Server retrieves the image from a URL. We need to actually store
> the image for consistency (caching, snapshots, etc.). We don't have a 'late
> bound' image.
> I don't understand why you can't copy the image to a directory on the
> external web site.
> --
> Brian Welcker
> Group Program Manager
> Microsoft SQL Server
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:FEC10F8E-F9C2-4938-9F16-3E3565EA723C@.microsoft.com...
> > That works for me for the intranet users but not for the internet users
> > because the location where I can generate the image with RenderStream is
> > not
> > available on internet.
> > Including an External Web image could work for me because I'm including
> > the
> > same banner image in my reports, but when I tried to include an External
> > Web
> > Image like
> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif
> > in my report in report designer, I got message 'Invalid Image'. Is that
> > expected or is it because of some restrictions in my environment?
> >
> > Thanks
> > Sanjay
> > "Brian Welcker [MS]" wrote:
> >
> >> Yes, although it is not simple. You need to use the RenderStream method
> >> to
> >> get the image and put it in a place where it is available to the Internet
> >> users. You need to pass this path into the initial Render() call using
> >> the
> >> <StreamRoot> deviceInfo.
> >>
> >> For what it is worth, this will be much easier in SQL 2005 RS.
> >>
> >> --
> >> Brian Welcker
> >> Group Program Manager
> >> Microsoft SQL Server
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> >> news:1A5971D1-5DB8-4B28-8AB1-38DFC1E1C25D@.microsoft.com...
> >> > How to render image in HTML reports?
> >> > I have a webapp which invokes Reporting Services web service to
> >> > generate
> >> > reports in HTML format. The reports are for internet users - the image
> >> > is
> >> > not
> >> > accessible to these internet users because they cannot access the
> >> > report
> >> > server directly which is in the intranet. Is there a solution to this
> >> > problem
> >> > at all?
> >> >
> >> > Thanks
> >> > Sanjay
> >>
> >>
> >>
>
>|||Yes, this is what StreamRoot is for. You can put whatever location you want
for picking up the images in the report.
--
Brian Welcker
Group Program Manager
Microsoft SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:23B5C014-89AB-41AA-8A33-C9CA8381D0AB@.microsoft.com...
>I can't copy the image to a directory on the external web site because I
> don't have access to the report server from the external web site machine
> as
> a result of which I have an intermediate ReportsGeneratorService sitting
> in
> the intranet which generates the reports. In addition I don't have access
> to
> create image files on the external web site machine from the
> intranet-sitting
> ReportsGeneratorService. For Security Reasons. Any suggestions?
> Can I manipulate the HTML on the external web-site to pick up the images
> from a location in the external web-site?
> "Brian Welcker [MS]" wrote:
>> All images in the reports are effectively "internal". URL only means that
>> the Report Server retrieves the image from a URL. We need to actually
>> store
>> the image for consistency (caching, snapshots, etc.). We don't have a
>> 'late
>> bound' image.
>> I don't understand why you can't copy the image to a directory on the
>> external web site.
>> --
>> Brian Welcker
>> Group Program Manager
>> Microsoft SQL Server
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
>> news:FEC10F8E-F9C2-4938-9F16-3E3565EA723C@.microsoft.com...
>> > That works for me for the intranet users but not for the internet users
>> > because the location where I can generate the image with RenderStream
>> > is
>> > not
>> > available on internet.
>> > Including an External Web image could work for me because I'm including
>> > the
>> > same banner image in my reports, but when I tried to include an
>> > External
>> > Web
>> > Image like
>> > http://msdn.microsoft.com/library/toolbar/3.0/images/banners/msdn_masthead_ltr.gif
>> > in my report in report designer, I got message 'Invalid Image'. Is that
>> > expected or is it because of some restrictions in my environment?
>> >
>> > Thanks
>> > Sanjay
>> > "Brian Welcker [MS]" wrote:
>> >
>> >> Yes, although it is not simple. You need to use the RenderStream
>> >> method
>> >> to
>> >> get the image and put it in a place where it is available to the
>> >> Internet
>> >> users. You need to pass this path into the initial Render() call using
>> >> the
>> >> <StreamRoot> deviceInfo.
>> >>
>> >> For what it is worth, this will be much easier in SQL 2005 RS.
>> >>
>> >> --
>> >> Brian Welcker
>> >> Group Program Manager
>> >> Microsoft SQL Server
>> >>
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
>> >> news:1A5971D1-5DB8-4B28-8AB1-38DFC1E1C25D@.microsoft.com...
>> >> > How to render image in HTML reports?
>> >> > I have a webapp which invokes Reporting Services web service to
>> >> > generate
>> >> > reports in HTML format. The reports are for internet users - the
>> >> > image
>> >> > is
>> >> > not
>> >> > accessible to these internet users because they cannot access the
>> >> > report
>> >> > server directly which is in the intranet. Is there a solution to
>> >> > this
>> >> > problem
>> >> > at all?
>> >> >
>> >> > Thanks
>> >> > Sanjay
>> >>
>> >>
>> >>
>>

Image Property from Business object in Reporting Service 2005

Hi

I have a question from Reporting service 2005
I use Business Objects in My Report
and one of My object's property's type is "Image" or "BitMap"

when i Drop that property in my report and set value in "Fields!MyPicture.Value" and Run that then i see "#Error"

when i drop an "Image" from Toolbar and set that in "Database" and set value in "Fields!MyPicture.Value" and Run that then i see a red "X"

how can i show my image property from business object in my Report (.rdlc)

Thanks alot

Please read my answer in your other thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=467270&SiteID=1

-- Robert

Image Property from Business object in Reporting Service 2005

Hi

I have a question from Reporting service 2005
I use Business Objects in My Report
and one of My object's property's type is "Image" or "BitMap"

when i Drop that property in my report and set value in "Fields!MyPicture.Value" and Run that then i see "#Error"

when i drop an "Image" from Toolbar and set that in "Database" and set value in "Fields!MyPicture.Value" and Run that then i see a red "X"

how can i show my image property from business object in my Report (.rdlc)

thanks alotI have not used RS2005, but in Rs2000 that you need drop and drag a image component to your report and set database filed to this image, and then drag the image to you grid or matrix.
hope this can help you

Image problem

Hello,

I'm using SQL 2005 & reporting service 2005

I have a picture control and the image come from a web. (http://myserver/logo.jpg)

The report contain parameters.

While user generate the reporting the image display without a problem.

But if user change his parameters selected (without close the reports) than click on generate report the image doesn't dispaly and red X show.

If user click on the refresh icon in the report the image load again.

Any sugestion how to solve this problem ?

Thanks

I had a similar problem before and had to use an embedded image instead to get rid of the problem.|||This issue was addressed in SP1. After applying SP1, are you still seeing the issue?|||I still didn;t applay SP1 because the "Select All" in ,ulti selection list removed in SP1|||If you don't want to apply SP1, then the only workaround is to use an embedded image instead.|||Thanks

Image Problem

I have a web application which invokes MS reporting service, gets the result
of a render call and writes the result out to the Response object. Everything
is fine except when the rendered format is html and the report contains an
â'Externalâ' image (image in Report Project) - here the browser asks for the
report server login credentials before serving the image. If the login is not
correct, the image is not served. Is there any way the image can be made
available to the browser in this case?Take a look at the SP1 ReadMe item
http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> I have a web application which invokes MS reporting service, gets the
result
> of a render call and writes the result out to the Response object.
Everything
> is fine except when the rendered format is html and the report contains an
> â'Externalâ' image (image in Report Project) - here the browser asks for
the
> report server login credentials before serving the image. If the login is
not
> correct, the image is not served. Is there any way the image can be made
> available to the browser in this case?
>
>|||"Ravi Mumulla (Microsoft)" wrote:
> Take a look at the SP1 ReadMe item
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > I have a web application which invokes MS reporting service, gets the
> result
> > of a render call and writes the result out to the Response object.
> Everything
> > is fine except when the rendered format is html and the report contains an
> > ââ?¬Ë?Externalââ?¬â?¢ image (image in Report Project) - here the browser asks for
> the
> > report server login credentials before serving the image. If the login is
> not
> > correct, the image is not served. Is there any way the image can be made
> > available to the browser in this case?
> >
> >
> >
> >
>
>|||i have installed SP1 on the SERVER and still get this error.
does sp1 need to be installed on the Client'
i have not seen this work ever!!!
"Ravi Mumulla (Microsoft)" wrote:
> Take a look at the SP1 ReadMe item
> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > I have a web application which invokes MS reporting service, gets the
> result
> > of a render call and writes the result out to the Response object.
> Everything
> > is fine except when the rendered format is html and the report contains an
> > ââ?¬Ë?Externalââ?¬â?¢ image (image in Report Project) - here the browser asks for
> the
> > report server login credentials before serving the image. If the login is
> not
> > correct, the image is not served. Is there any way the image can be made
> > available to the browser in this case?
> >
> >
> >
> >
>
>|||SP1 should be installed on the Server and on all Development Workstations
where you develop reports. There is nothing to install on the clients.
Now your problems are directly the result of you not setting the Unattended
Execution Account up properly. When you have set that up you'll find that it
works.
From the link that Ravi sent you:
When the report is run on the report server, the report server uses the
unattended execution account to retrieve the image. If the unattended
execution account is not specified, the image is retrieved using no
credentials (anonymous user account). If either of these accounts have
insufficient rights to access the image, the image will not be displayed in
the report. For more information about setting the unattended execution
account on the report server, see "Configuring an Account for Unattended
Report Processing" in Reporting Services Books Online.
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"luzer" <luzer@.discussions.microsoft.com> wrote in message
news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
>i have installed SP1 on the SERVER and still get this error.
> does sp1 need to be installed on the Client'
> i have not seen this work ever!!!
> "Ravi Mumulla (Microsoft)" wrote:
>> Take a look at the SP1 ReadMe item
>> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
>> --
>> Ravi Mumulla (Microsoft)
>> SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
>> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
>> > I have a web application which invokes MS reporting service, gets the
>> result
>> > of a render call and writes the result out to the Response object.
>> Everything
>> > is fine except when the rendered format is html and the report contains
>> > an
>> > â?~Externalâ?T image (image in Report Project) - here the browser asks
>> > for
>> the
>> > report server login credentials before serving the image. If the login
>> > is
>> not
>> > correct, the image is not served. Is there any way the image can be
>> > made
>> > available to the browser in this case?
>> >
>> >
>> >
>> >
>>|||the problem was related to Anonymous access on IIS6 messed up.
thanks
"Peter Blackburn (www.sqlreportingservice" wrote:
> SP1 should be installed on the Server and on all Development Workstations
> where you develop reports. There is nothing to install on the clients.
> Now your problems are directly the result of you not setting the Unattended
> Execution Account up properly. When you have set that up you'll find that it
> works.
> From the link that Ravi sent you:
> When the report is run on the report server, the report server uses the
> unattended execution account to retrieve the image. If the unattended
> execution account is not specified, the image is retrieved using no
> credentials (anonymous user account). If either of these accounts have
> insufficient rights to access the image, the image will not be displayed in
> the report. For more information about setting the unattended execution
> account on the report server, see "Configuring an Account for Unattended
> Report Processing" in Reporting Services Books Online.
>
> Peter Blackburn
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
>
>
> "luzer" <luzer@.discussions.microsoft.com> wrote in message
> news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
> >i have installed SP1 on the SERVER and still get this error.
> > does sp1 need to be installed on the Client'
> >
> > i have not seen this work ever!!!
> >
> > "Ravi Mumulla (Microsoft)" wrote:
> >
> >> Take a look at the SP1 ReadMe item
> >> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> >>
> >> --
> >> Ravi Mumulla (Microsoft)
> >> SQL Server Reporting Services
> >>
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> >> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> >> > I have a web application which invokes MS reporting service, gets the
> >> result
> >> > of a render call and writes the result out to the Response object.
> >> Everything
> >> > is fine except when the rendered format is html and the report contains
> >> > an
> >> > â?~Externalâ?T image (image in Report Project) - here the browser asks
> >> > for
> >> the
> >> > report server login credentials before serving the image. If the login
> >> > is
> >> not
> >> > correct, the image is not served. Is there any way the image can be
> >> > made
> >> > available to the browser in this case?
> >> >
> >> >
> >> >
> >> >
> >>
> >>
> >>
>
>|||Hi,
What are you doing about the anonymous access on IIS6?
maybe it will resolved my problem...
thanks
"luzer" wrote:
> the problem was related to Anonymous access on IIS6 messed up.
> thanks
>
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > SP1 should be installed on the Server and on all Development Workstations
> > where you develop reports. There is nothing to install on the clients.
> >
> > Now your problems are directly the result of you not setting the Unattended
> > Execution Account up properly. When you have set that up you'll find that it
> > works.
> >
> > From the link that Ravi sent you:
> >
> > When the report is run on the report server, the report server uses the
> > unattended execution account to retrieve the image. If the unattended
> > execution account is not specified, the image is retrieved using no
> > credentials (anonymous user account). If either of these accounts have
> > insufficient rights to access the image, the image will not be displayed in
> > the report. For more information about setting the unattended execution
> > account on the report server, see "Configuring an Account for Unattended
> > Report Processing" in Reporting Services Books Online.
> >
> >
> > Peter Blackburn
> > Hitchhiker's Guide to SQL Server 2000 Reporting Services
> > http://www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> >
> > "luzer" <luzer@.discussions.microsoft.com> wrote in message
> > news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
> > >i have installed SP1 on the SERVER and still get this error.
> > > does sp1 need to be installed on the Client'
> > >
> > > i have not seen this work ever!!!
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > >> Take a look at the SP1 ReadMe item
> > >> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> > >>
> > >> --
> > >> Ravi Mumulla (Microsoft)
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > >> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > >> > I have a web application which invokes MS reporting service, gets the
> > >> result
> > >> > of a render call and writes the result out to the Response object.
> > >> Everything
> > >> > is fine except when the rendered format is html and the report contains
> > >> > an
> > >> > â?~Externalâ?T image (image in Report Project) - here the browser asks
> > >> > for
> > >> the
> > >> > report server login credentials before serving the image. If the login
> > >> > is
> > >> not
> > >> > correct, the image is not served. Is there any way the image can be
> > >> > made
> > >> > available to the browser in this case?
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >|||Other strange thing,
If the image is located in the same server that IIS, the image is right
displayed.
someone can tell me what is happen?
"luzer" wrote:
> the problem was related to Anonymous access on IIS6 messed up.
> thanks
>
> "Peter Blackburn (www.sqlreportingservice" wrote:
> > SP1 should be installed on the Server and on all Development Workstations
> > where you develop reports. There is nothing to install on the clients.
> >
> > Now your problems are directly the result of you not setting the Unattended
> > Execution Account up properly. When you have set that up you'll find that it
> > works.
> >
> > From the link that Ravi sent you:
> >
> > When the report is run on the report server, the report server uses the
> > unattended execution account to retrieve the image. If the unattended
> > execution account is not specified, the image is retrieved using no
> > credentials (anonymous user account). If either of these accounts have
> > insufficient rights to access the image, the image will not be displayed in
> > the report. For more information about setting the unattended execution
> > account on the report server, see "Configuring an Account for Unattended
> > Report Processing" in Reporting Services Books Online.
> >
> >
> > Peter Blackburn
> > Hitchhiker's Guide to SQL Server 2000 Reporting Services
> > http://www.sqlreportingservices.net
> >
> >
> >
> >
> >
> >
> >
> > "luzer" <luzer@.discussions.microsoft.com> wrote in message
> > news:A65244C5-368B-4A00-B8F6-051806250686@.microsoft.com...
> > >i have installed SP1 on the SERVER and still get this error.
> > > does sp1 need to be installed on the Client'
> > >
> > > i have not seen this work ever!!!
> > >
> > > "Ravi Mumulla (Microsoft)" wrote:
> > >
> > >> Take a look at the SP1 ReadMe item
> > >> http://download.microsoft.com/download/7/f/b/7fb1a251-13ad-404c-a034-10d79ddaa510/SP1Readme_EN.htm#_external_images.
> > >>
> > >> --
> > >> Ravi Mumulla (Microsoft)
> > >> SQL Server Reporting Services
> > >>
> > >> This posting is provided "AS IS" with no warranties, and confers no
> > >> rights.
> > >> "Sanjay" <Sanjay@.discussions.microsoft.com> wrote in message
> > >> news:358C3D3A-707B-43A7-AC1F-CB0162976516@.microsoft.com...
> > >> > I have a web application which invokes MS reporting service, gets the
> > >> result
> > >> > of a render call and writes the result out to the Response object.
> > >> Everything
> > >> > is fine except when the rendered format is html and the report contains
> > >> > an
> > >> > â?~Externalâ?T image (image in Report Project) - here the browser asks
> > >> > for
> > >> the
> > >> > report server login credentials before serving the image. If the login
> > >> > is
> > >> not
> > >> > correct, the image is not served. Is there any way the image can be
> > >> > made
> > >> > available to the browser in this case?
> > >> >
> > >> >
> > >> >
> > >> >
> > >>
> > >>
> > >>
> >
> >
> >sql

I'm not sure what to do...Help

Hi everyone

I have a secure web site with SSRS 2005 installed. The service points to a backend server on our network where the databases reside and we have a firewall.

Previously we have configured our reports and data extentions to use Windows Integrated Security. Now that I have a remote install I need to switch to either forms authentication or Kerberos authentication.

Which one is the easiest to use? My guess is that doing a little coding for forms authentication is the way to go.

With forms authentication I need to create a security extention. What will that do to my custom data extention?

When using Kerberos or Forms authentication can a client still use Report Builder?

I would appreciate any help.

Thanks

-JW

I can't speak to forms authentication, but Kerberos can work.

I say 'can' because if the certificates aren't 100% perfect (URL name matches cert, complete cert chain is trusted, etc), then it will fail, and Kerberos failures are painfully difficult to troubleshoot as normally the only helpful message you receive is 'access denied', with little clue why.

If the server is re-using the client's credentials to access the backend data, then you are already using Kerberos, because that's the only way it's possible to do that, using delegation. An easy way to test if Kerberos will work without having to access outside a firewall is to disable NTLM for that directory in IIS using the adsutil.vbs utility (google can assist with a sample, I don't have the syntax handy), so only the Kerberos half of Windows Authentication is enabled.

And yes, I was able to get Report Builder to work as well. Not sure what effect it might have on the custom data extension without some more details of what it does.

So, my suggestion would be to give Kerberos a try, because if it works, it'll work fairly quickly with no code required. If it gets muddy, then maybe give Forms a try. But as I said, I've never used Forms so can't help you on how.

Geof

IIS Service is getting down very frequently

Hi All,

We are having one web service it is running in iis 5.0.now a days it is getting down very frequently.can any one help me on this what could be the problem.

where i can see the log details when the service get down.

Hi,

Check the event logs and see if you find anything there. Paste the IIS/Web related events here.

IIS security problem

Hi,
I have IIS5.0 running on w2k (SP3) for SQL reporting service(SP1). IIS has all the patches that I can find. While IIS running, this computer has been hijacked or turn to proxy server to deliver spam mails. How can I provent it (no smpt service running.)I am not sure I understand the issue you're facing, can you explain some
more?
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"csoon" <csoon@.discussions.microsoft.com> wrote in message
news:C14F67AF-AA2D-4529-BF58-E170171B0410@.microsoft.com...
> Hi,
> I have IIS5.0 running on w2k (SP3) for SQL reporting service(SP1). IIS
has all the patches that I can find. While IIS running, this computer has
been hijacked or turn to proxy server to deliver spam mails. How can I
provent it (no smpt service running.)|||Note that you do not need to have either an email client nor an email server
running. There would be no way for your server to be hijacked if you do not
have these installed. Yes, you can use either but you do not need to. I am
configured where RS sends the email to an SMTP server which resides
elsewhere.
Bruce L-C
"csoon" <csoon@.discussions.microsoft.com> wrote in message
news:CFC072A9-A3E9-46A2-BDAF-ECD7BE320BB9@.microsoft.com...
> I keep getting email indicate that my computer was hijack and used as
proxy server to deliver hugh emails while IIS5.0 running. It is very
difficult to get budget to try sql reporting service specially it is not
secure. First I did implement sending email in sql reporting service by
changing in configuration file (drop off email files in a folder to smtp to
pick them up). Once I had this problem then I turned off smtp but I still
got emails indicated that my computer was hijacked. I installed lock down
IIS and all the patch for IIS5.0. I would like to try RS but if I still
cause problem to others, I might not be able to test or implement this
software. If you need more info. please let me know.
> "Tudor Trufinescu (MSFT)" wrote:
> > I am not sure I understand the issue you're facing, can you explain some
> > more?
> >
> > --
> > Tudor Trufinescu
> > Dev Lead
> > Sql Server Reporting Services
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> >
> > "csoon" <csoon@.discussions.microsoft.com> wrote in message
> > news:C14F67AF-AA2D-4529-BF58-E170171B0410@.microsoft.com...
> > > Hi,
> > > I have IIS5.0 running on w2k (SP3) for SQL reporting service(SP1).
IIS
> > has all the patches that I can find. While IIS running, this computer
has
> > been hijacked or turn to proxy server to deliver spam mails. How can I
> > provent it (no smpt service running.)
> >
> >
> >|||Thanks. Unfortunately, this is a test on my computer, I have to have mail client on this computer.
"Bruce Loehle-Conger" wrote:
> Note that you do not need to have either an email client nor an email server
> running. There would be no way for your server to be hijacked if you do not
> have these installed. Yes, you can use either but you do not need to. I am
> configured where RS sends the email to an SMTP server which resides
> elsewhere.
> Bruce L-C
> "csoon" <csoon@.discussions.microsoft.com> wrote in message
> news:CFC072A9-A3E9-46A2-BDAF-ECD7BE320BB9@.microsoft.com...
> > I keep getting email indicate that my computer was hijack and used as
> proxy server to deliver hugh emails while IIS5.0 running. It is very
> difficult to get budget to try sql reporting service specially it is not
> secure. First I did implement sending email in sql reporting service by
> changing in configuration file (drop off email files in a folder to smtp to
> pick them up). Once I had this problem then I turned off smtp but I still
> got emails indicated that my computer was hijacked. I installed lock down
> IIS and all the patch for IIS5.0. I would like to try RS but if I still
> cause problem to others, I might not be able to test or implement this
> software. If you need more info. please let me know.
> >
> > "Tudor Trufinescu (MSFT)" wrote:
> >
> > > I am not sure I understand the issue you're facing, can you explain some
> > > more?
> > >
> > > --
> > > Tudor Trufinescu
> > > Dev Lead
> > > Sql Server Reporting Services
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > >
> > > "csoon" <csoon@.discussions.microsoft.com> wrote in message
> > > news:C14F67AF-AA2D-4529-BF58-E170171B0410@.microsoft.com...
> > > > Hi,
> > > > I have IIS5.0 running on w2k (SP3) for SQL reporting service(SP1).
> IIS
> > > has all the patches that I can find. While IIS running, this computer
> has
> > > been hijacked or turn to proxy server to deliver spam mails. How can I
> > > provent it (no smpt service running.)
> > >
> > >
> > >
>
>

IIS crash and format

Hi

due to big problems (basically since a couple of month iis service crash
several times a day) i need to format a web server (win2k, sp4, iis5.0)
where all our sites are hosted .
To avoid downtime i would like to transfer them all on the new server.
Do i have to recreate every single web (with a lot of virtual folders ) or
there is an automatic (semiautomatic) way to migrate them all?
Many thanks in advance
Marco"Marco M. Danni" <mmdanni@.netbrokers.it> wrote in message
news:WAgfb.6952$e6.211030@.twister2.libero.it...
> Hi
> due to big problems (basically since a couple of month iis service crash
> several times a day) i need to format a web server (win2k, sp4, iis5.0)
> where all our sites are hosted .
> To avoid downtime i would like to transfer them all on the new server.
> Do i have to recreate every single web (with a lot of virtual folders ) or
> there is an automatic (semiautomatic) way to migrate them all?
> Many thanks in advance

This has nothing to do that I can see with SQL Server, but in theory if you
back them up in the ISS Enterprise Manager and find the file (I can't recall
exactly where it is, but it's like %systemroot%\system32\inetsrv\backup),
copy that to the new machine and load it, you'll get your metabase intact.
DLLs will not necessarily be loaded though (say Cold Fusion, etc.)

> Marco|||sorry...my fault

"Greg D. Moore (Strider)" <mooregr@.greenms.com> ha scritto nel messaggio
news:Othfb.10709$LI.8014@.twister.nyroc.rr.com...
> "Marco M. Danni" <mmdanni@.netbrokers.it> wrote in message
> news:WAgfb.6952$e6.211030@.twister2.libero.it...
> > Hi
> > due to big problems (basically since a couple of month iis service crash
> > several times a day) i need to format a web server (win2k, sp4, iis5.0)
> > where all our sites are hosted .
> > To avoid downtime i would like to transfer them all on the new server.
> > Do i have to recreate every single web (with a lot of virtual folders )
or
> > there is an automatic (semiautomatic) way to migrate them all?
> > Many thanks in advance
> This has nothing to do that I can see with SQL Server, but in theory if
you
> back them up in the ISS Enterprise Manager and find the file (I can't
recall
> exactly where it is, but it's like %systemroot%\system32\inetsrv\backup),
> copy that to the new machine and load it, you'll get your metabase intact.
> DLLs will not necessarily be loaded though (say Cold Fusion, etc.)
>
> > Marco

Ignoring spaces!

Hi All,
Wondering if i can tap into your knowlege...
I have 2 lists of ID Codes (users and potential users of a service)
which i need to match together
1 list is of existing users, 1 list of potential users.
I want to find, from the list of potential users, ID codes which are
not in the list of users.
This is simple enough and i'm using a lef join to establish matching
ID codes in the 2 lists, those not matched have not used the service.
My problem is, that ID codes from both lists sometimes have a single
space at random points within the code and these are not constent
between the 2 lists.
What i ideally would like is a piece of code which says to match list
1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
then ignore the ' ' [space].
Any advice?
PS i know i could use the replace ' ', with '' code in both lists to
uniform them, but i don't really want to have to go down that line
everytime i want to do the match.
Thanks!
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
Then fix the problem instead of searching for some magical better
alternative to using replace.
By "fix the problem" I mean:
(a) correct the existing data that shouldn't have spaces; and, more
importantly,
(b) correct the code/app(s) that is putting the spaces into the data in the
first place.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006
|||On Fri, 15 Jun 2007 06:00:13 -0700, chriselias271@.gmail.com wrote:

>What i ideally would like is a piece of code which says to match list
>1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
>then ignore the ' ' [space].
ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
However, performance will be poor as that can not use indexes. If the
tables are not too large and the match is not run too often
performance might be acceptable, or not.

>PS i know i could use the replace ' ', with '' code in both lists to
>uniform them, but i don't really want to have to go down that line
>everytime i want to do the match.
If you don't fix the data - which would seem to be the ideal solution
- then whatver "piece of code" you use will have to be used every time
the match is performed. I don't know what sort of code could be
simpler than using REPLACE as in the example above.
If it is not practical to remove the spaces, and the match must be run
regularly, then I would consider adding another column to each table
to hold the column without the blank, or adding such a column to a
pair of views on the two tables and indexing them to create indexed
views and match on the views.
Roy Harvey
Beacon Falls, CT
|||I agree with Aaron on it's better to fix the data source, but assuming
you cannot...
A user-defined scalar-value function can do the string cleaning...if
you are on 2005 and can use CLR, just a simple wrapper of
Regex.Replace will do the trick in one line...if you are 2000 or no
CLR, then you would have to do t-sql string manipulation to clean it
up...did this last week for an ETL project...not a very good idea
performance wise, as it will scan all your base tables if you are
using it in the join:
-- Returns only the digits contained in @.input
CREATE FUNCTION dbo.VarcharDigits
(
@.input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.i int
DECLARE @.cur char
DECLARE @.output varchar(255)
SET @.output = ''
SET @.i = 1
WHILE (@.i <= LEN(@.input))
BEGIN
SET @.cur = SUBSTRING(@.input,@.i,1)
IF (ASCII(@.cur) BETWEEN 48 AND 57) -- Digits only
SET @.output = @.output + @.cur
SET @.i = @.i + 1
END
RETURN @.output
END
On Jun 15, 9:00 am, chriselias...@.gmail.com wrote:
> Hi All,
> Wondering if i can tap into your knowlege...
> I have 2 lists of ID Codes (users and potential users of a service)
> which i need to match together
> 1 list is of existing users, 1 list of potential users.
> I want to find, from the list of potential users, ID codes which are
> not in the list of users.
> This is simple enough and i'm using a lef join to establish matching
> ID codes in the 2 lists, those not matched have not used the service.
> My problem is, that ID codes from both lists sometimes have a single
> space at random points within the code and these are not constent
> between the 2 lists.
> What i ideally would like is a piece of code which says to match list
> 1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
> then ignore the ' ' [space].
> Any advice?
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
> Thanks!
|||On 15 Jun, 15:18, Roy Harvey <roy_har...@.snet.net> wrote:
> On Fri, 15 Jun 2007 06:00:13 -0700, chriselias...@.gmail.com wrote:
> ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
> However, performance will be poor as that can not use indexes. If the
> tables are not too large and the match is not run too often
> performance might be acceptable, or not.
>
> If you don't fix the data - which would seem to be the ideal solution
> - then whatver "piece of code" you use will have to be used every time
> the match is performed. I don't know what sort of code could be
> simpler than using REPLACE as in the example above.
> If it is not practical to remove thespaces, and the match must be run
> regularly, then I would consider adding another column to each table
> to hold the column without the blank, or adding such a column to a
> pair of views on the two tables and indexing them to create indexed
> views and match on the views.
> Roy Harvey
> Beacon Falls, CT
Absolutely spot on exactly what i wanted.
Thanks for understanding the problem so well!!

Ignoring spaces!

Hi All,
Wondering if i can tap into your knowlege...
I have 2 lists of ID Codes (users and potential users of a service)
which i need to match together
1 list is of existing users, 1 list of potential users.
I want to find, from the list of potential users, ID codes which are
not in the list of users.
This is simple enough and i'm using a lef join to establish matching
ID codes in the 2 lists, those not matched have not used the service.
My problem is, that ID codes from both lists sometimes have a single
space at random points within the code and these are not constent
between the 2 lists.
What i ideally would like is a piece of code which says to match list
1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
then ignore the ' ' [space].
Any advice'
PS i know i could use the replace ' ', with '' code in both lists to
uniform them, but i don't really want to have to go down that line
everytime i want to do the match.
Thanks!> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
Then fix the problem instead of searching for some magical better
alternative to using replace.
By "fix the problem" I mean:
(a) correct the existing data that shouldn't have spaces; and, more
importantly,
(b) correct the code/app(s) that is putting the spaces into the data in the
first place.
--
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On Fri, 15 Jun 2007 06:00:13 -0700, chriselias271@.gmail.com wrote:
>What i ideally would like is a piece of code which says to match list
>1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
>then ignore the ' ' [space].
ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
However, performance will be poor as that can not use indexes. If the
tables are not too large and the match is not run too often
performance might be acceptable, or not.
>PS i know i could use the replace ' ', with '' code in both lists to
>uniform them, but i don't really want to have to go down that line
>everytime i want to do the match.
If you don't fix the data - which would seem to be the ideal solution
- then whatver "piece of code" you use will have to be used every time
the match is performed. I don't know what sort of code could be
simpler than using REPLACE as in the example above.
If it is not practical to remove the spaces, and the match must be run
regularly, then I would consider adding another column to each table
to hold the column without the blank, or adding such a column to a
pair of views on the two tables and indexing them to create indexed
views and match on the views.
Roy Harvey
Beacon Falls, CT|||I agree with Aaron on it's better to fix the data source, but assuming
you cannot...
A user-defined scalar-value function can do the string cleaning...if
you are on 2005 and can use CLR, just a simple wrapper of
Regex.Replace will do the trick in one line...if you are 2000 or no
CLR, then you would have to do t-sql string manipulation to clean it
up...did this last week for an ETL project...not a very good idea
performance wise, as it will scan all your base tables if you are
using it in the join:
-- Returns only the digits contained in @.input
CREATE FUNCTION dbo.VarcharDigits
(
@.input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.i int
DECLARE @.cur char
DECLARE @.output varchar(255)
SET @.output = ''
SET @.i = 1
WHILE (@.i <= LEN(@.input))
BEGIN
SET @.cur = SUBSTRING(@.input,@.i,1)
IF (ASCII(@.cur) BETWEEN 48 AND 57) -- Digits only
SET @.output = @.output + @.cur
SET @.i = @.i + 1
END
RETURN @.output
END
On Jun 15, 9:00 am, chriselias...@.gmail.com wrote:
> Hi All,
> Wondering if i can tap into your knowlege...
> I have 2 lists of ID Codes (users and potential users of a service)
> which i need to match together
> 1 list is of existing users, 1 list of potential users.
> I want to find, from the list of potential users, ID codes which are
> not in the list of users.
> This is simple enough and i'm using a lef join to establish matching
> ID codes in the 2 lists, those not matched have not used the service.
> My problem is, that ID codes from both lists sometimes have a single
> space at random points within the code and these are not constent
> between the 2 lists.
> What i ideally would like is a piece of code which says to match list
> 1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
> then ignore the ' ' [space].
> Any advice'
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
> Thanks!|||On 15 Jun, 15:18, Roy Harvey <roy_har...@.snet.net> wrote:
> On Fri, 15 Jun 2007 06:00:13 -0700, chriselias...@.gmail.com wrote:
> >What i ideally would like is a piece of code which says to match list
> >1 with list 2 butignoreanything which is not a-zA-Z0-9, which would
> >thenignorethe ' ' [space].
> ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
> However, performance will be poor as that can not use indexes. If the
> tables are not too large and the match is not run too often
> performance might be acceptable, or not.
> >PS i know i could use the replace ' ', with '' code in both lists to
> >uniform them, but i don't really want to have to go down that line
> >everytime i want to do the match.
> If you don't fix the data - which would seem to be the ideal solution
> - then whatver "piece of code" you use will have to be used every time
> the match is performed. I don't know what sort of code could be
> simpler than using REPLACE as in the example above.
> If it is not practical to remove thespaces, and the match must be run
> regularly, then I would consider adding another column to each table
> to hold the column without the blank, or adding such a column to a
> pair of views on the two tables and indexing them to create indexed
> views and match on the views.
> Roy Harvey
> Beacon Falls, CT
Absolutely spot on exactly what i wanted.
Thanks for understanding the problem so well!!

Ignoring spaces!

Hi All,
Wondering if i can tap into your knowlege...
I have 2 lists of ID Codes (users and potential users of a service)
which i need to match together
1 list is of existing users, 1 list of potential users.
I want to find, from the list of potential users, ID codes which are
not in the list of users.
This is simple enough and i'm using a lef join to establish matching
ID codes in the 2 lists, those not matched have not used the service.
My problem is, that ID codes from both lists sometimes have a single
space at random points within the code and these are not constent
between the 2 lists.
What i ideally would like is a piece of code which says to match list
1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
then ignore the ' ' [space].
Any advice'
PS i know i could use the replace ' ', with '' code in both lists to
uniform them, but i don't really want to have to go down that line
everytime i want to do the match.
Thanks!> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
Then fix the problem instead of searching for some magical better
alternative to using replace.
By "fix the problem" I mean:
(a) correct the existing data that shouldn't have spaces; and, more
importantly,
(b) correct the code/app(s) that is putting the spaces into the data in the
first place.
Aaron Bertrand
SQL Server MVP
http://www.sqlblog.com/
http://www.aspfaq.com/5006|||On Fri, 15 Jun 2007 06:00:13 -0700, chriselias271@.gmail.com wrote:

>What i ideally would like is a piece of code which says to match list
>1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
>then ignore the ' ' [space].
ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
However, performance will be poor as that can not use indexes. If the
tables are not too large and the match is not run too often
performance might be acceptable, or not.

>PS i know i could use the replace ' ', with '' code in both lists to
>uniform them, but i don't really want to have to go down that line
>everytime i want to do the match.
If you don't fix the data - which would seem to be the ideal solution
- then whatver "piece of code" you use will have to be used every time
the match is performed. I don't know what sort of code could be
simpler than using REPLACE as in the example above.
If it is not practical to remove the spaces, and the match must be run
regularly, then I would consider adding another column to each table
to hold the column without the blank, or adding such a column to a
pair of views on the two tables and indexing them to create indexed
views and match on the views.
Roy Harvey
Beacon Falls, CT|||I agree with Aaron on it's better to fix the data source, but assuming
you cannot...
A user-defined scalar-value function can do the string cleaning...if
you are on 2005 and can use CLR, just a simple wrapper of
Regex.Replace will do the trick in one line...if you are 2000 or no
CLR, then you would have to do t-sql string manipulation to clean it
up...did this last week for an ETL project...not a very good idea
performance wise, as it will scan all your base tables if you are
using it in the join:
-- Returns only the digits contained in @.input
CREATE FUNCTION dbo.VarcharDigits
(
@.input varchar(255)
)
RETURNS varchar(255)
AS
BEGIN
DECLARE @.i int
DECLARE @.cur char
DECLARE @.output varchar(255)
SET @.output = ''
SET @.i = 1
WHILE (@.i <= LEN(@.input))
BEGIN
SET @.cur = SUBSTRING(@.input,@.i,1)
IF (ASCII(@.cur) BETWEEN 48 AND 57) -- Digits only
SET @.output = @.output + @.cur
SET @.i = @.i + 1
END
RETURN @.output
END
On Jun 15, 9:00 am, chriselias...@.gmail.com wrote:
> Hi All,
> Wondering if i can tap into your knowlege...
> I have 2 lists of ID Codes (users and potential users of a service)
> which i need to match together
> 1 list is of existing users, 1 list of potential users.
> I want to find, from the list of potential users, ID codes which are
> not in the list of users.
> This is simple enough and i'm using a lef join to establish matching
> ID codes in the 2 lists, those not matched have not used the service.
> My problem is, that ID codes from both lists sometimes have a single
> space at random points within the code and these are not constent
> between the 2 lists.
> What i ideally would like is a piece of code which says to match list
> 1 with list 2 but ignore anything which is not a-zA-Z0-9, which would
> then ignore the ' ' [space].
> Any advice'
> PS i know i could use the replace ' ', with '' code in both lists to
> uniform them, but i don't really want to have to go down that line
> everytime i want to do the match.
> Thanks!|||On 15 Jun, 15:18, Roy Harvey <roy_har...@.snet.net> wrote:
> On Fri, 15 Jun 2007 06:00:13 -0700, chriselias...@.gmail.com wrote:
> ON REPLACE(A.ID, ' ', '') = REPLACE(B.ID, ' ', '')
> However, performance will be poor as that can not use indexes. If the
> tables are not too large and the match is not run too often
> performance might be acceptable, or not.
>
> If you don't fix the data - which would seem to be the ideal solution
> - then whatver "piece of code" you use will have to be used every time
> the match is performed. I don't know what sort of code could be
> simpler than using REPLACE as in the example above.
> If it is not practical to remove thespaces, and the match must be run
> regularly, then I would consider adding another column to each table
> to hold the column without the blank, or adding such a column to a
> pair of views on the two tables and indexing them to create indexed
> views and match on the views.
> Roy Harvey
> Beacon Falls, CT
Absolutely spot on exactly what i wanted.
Thanks for understanding the problem so well!!