Broken Subscriptions Due to Empty Table

Hi,

I was wondering what the best way was to deal with subscriptions breaking due to an empty table. I have subscriptions that people have scheduled to go out daily, but on certain days the table may be empty, in this case the subscription doesn't read the parameters for the report and then the subscription breaks.

My original solution involved creating a #temp table with the same columns as the original table and inserting one row into it which I'd union with the original table, this row in the temp table had all its values set to 0. The solution worked when I ran it in SQL Server Management Studio but it seems SRS doesn't like the INSERT INTO statement, which is the error I get, but I've read on these forums that it doesn't like #temp tables either. I proceeded to use a stored procedure with all the code in it, but I might have trouble filtering on multi value parameters, because at times these parameter lists get real big, plus I have to do this for multiple reports and don't want to get into creating stored procedures for each report.

Following is what the code I used look something like that executes and does the job in Management Studio but not SRS. I'm mainly just looking for the easiest and cleanest way to do this, since it'll have to be done across multiple reports, so disregard the code if there's an easier way to do it. Thanks in advance.

create table #dummytable

(
name varchar(35),

country varchar(35),

idnumber (int)

)

GO

insert into #dummytable (name),values('0');

select name, country, idnumber

from originaltable

where name in (@.name)

union

select name = 0, country = 0, idnumber = 0

from #dummytable

drop #dummytable

Another solution might be to do a union with a single known row that will always be delivered.

If the delivery query returns 0 rows, what would you want the subscription to do?

Thanks,

-Lukasz

|||

>> SRS doesn't like the INSERT

Instead of creating a temp table and INSERTing, why not just do a SELECT of literals?

Code Snippet

SELECT Name, Country, idnumber FROM

OriginalTable WHERE Name IN (@.Name)

UNION

SELECT SPACE(0),SPACE(0), 0

>L<

|||

Thank you Lisa, that does get the job done, much appreciated.

Broken merge. Need help.

Hi,
Seems I found how to brake merge replication.
Actually I reproduced this situation on my test env. after we found it on
production.
OK we have SQL2K EE SP3a on W2k AE SP4.
Suppose You create a table with few fields of different type
(field1,field2,field3.etc.. rowguid)
Then you publish it for merge replication an push the subscription to
subscriber (with initialization).
Everything works fine.
Then suppose you need to ALTER one of columns (let's say field2) in that
table (char(12 ->char(20))
In merge (or whatever) you have to do sp_repldropcolumn then
sp_repladdcolumn and restore data of that column on publisher.
All changes are done and synchronized with the subscriber. Everything still
works.
Then you create a new merge publication including the same table and push it
to another subscriber (with initialization).
After we found that changes are not correctly transferred between two
subscribers. All data is still correct at the publisher but at the
subscribers some fields are different.
Does anyone know about this? Or any suggestions how to resolve this problem?
Thank You
Darius T.
Hi Darius,
Try looking at the .sch file created by the publication. It is usually
located at: ...\MSSQL\REPLDATA\name_of_publication. I have dealt with cases
that the snapshot file for the creation of the table was not correctly
created. You can manually modify this file to create the correct table with
the correct columns. When the initialization takes place, it will take this
file and apply it tio the subscribers. Hope this helps!
-Jose Molina
"Darius T." <dta@.takas.lt> wrote in message
news:%23s$CB3Z$EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Seems I found how to brake merge replication.
> Actually I reproduced this situation on my test env. after we found it on
> production.
> OK we have SQL2K EE SP3a on W2k AE SP4.
> Suppose You create a table with few fields of different type
> (field1,field2,field3.etc.. rowguid)
> Then you publish it for merge replication an push the subscription to
> subscriber (with initialization).
> Everything works fine.
> Then suppose you need to ALTER one of columns (let's say field2) in that
> table (char(12 ->char(20))
> In merge (or whatever) you have to do sp_repldropcolumn then
> sp_repladdcolumn and restore data of that column on publisher.
> All changes are done and synchronized with the subscriber. Everything
> still works.
> Then you create a new merge publication including the same table and push
> it to another subscriber (with initialization).
> After we found that changes are not correctly transferred between two
> subscribers. All data is still correct at the publisher but at the
> subscribers some fields are different.
> Does anyone know about this? Or any suggestions how to resolve this
> problem?
> Thank You
> Darius T.
>
>
|||> After we found that changes are not correctly transferred between two
> subscribers. All data is still correct at the publisher but at the
> subscribers some fields are different.
Could you be more specific about this situation ?
What do you mean with "some fields are different." ?
Are you making inserts/updates only on publisher ?
Is replication working properly (replicating data to both subscribers) if
you skip step 2 (alter of the column) ?
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Darius T." <dta@.takas.lt> wrote in message
news:%23s$CB3Z$EHA.1452@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Seems I found how to brake merge replication.
> Actually I reproduced this situation on my test env. after we found it on
> production.
> OK we have SQL2K EE SP3a on W2k AE SP4.
> Suppose You create a table with few fields of different type
> (field1,field2,field3.etc.. rowguid)
> Then you publish it for merge replication an push the subscription to
> subscriber (with initialization).
> Everything works fine.
> Then suppose you need to ALTER one of columns (let's say field2) in that
> table (char(12 ->char(20))
> In merge (or whatever) you have to do sp_repldropcolumn then
> sp_repladdcolumn and restore data of that column on publisher.
> All changes are done and synchronized with the subscriber. Everything
still
> works.
> Then you create a new merge publication including the same table and push
it
> to another subscriber (with initialization).
> After we found that changes are not correctly transferred between two
> subscribers. All data is still correct at the publisher but at the
> subscribers some fields are different.
> Does anyone know about this? Or any suggestions how to resolve this
problem?
> Thank You
> Darius T.
>
>
|||"Some field are differnet" means that if you make update on a record at
publisher and run merge agents to both subscribers You find that all data is
correct only at subscriber 2, but at subscriber 1 data in some fiels in not
updated correctly. (INSERT,DELETE statements work properly).
The same things happening when you make an update on subscriber 2. Data is
merged correctly with publisher but not with subscriber 1.
I could post a step by step script here to see how it happens (if you are
interested)
Darius
P.S. gal galime susirayti ir lietuvikai jei k.
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:%232dPB%23s$EHA.2444@.TK2MSFTNGP10.phx.gbl...
> Could you be more specific about this situation ?
> What do you mean with "some fields are different." ?
> Are you making inserts/updates only on publisher ?
> Is replication working properly (replicating data to both subscribers) if
> you skip step 2 (alter of the column) ?
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Darius T." <dta@.takas.lt> wrote in message
> news:%23s$CB3Z$EHA.1452@.TK2MSFTNGP11.phx.gbl...
> still
> it
> problem?
>
|||Thanks, Jose,
I've checked those .sch scripts. Didn't find any problem.
Darius
"Jose Molina" <rcmaniac23@.yahoo.com> wrote in message
news:OCHeF8k$EHA.3840@.tk2msftngp13.phx.gbl...
> Hi Darius,
> Try looking at the .sch file created by the publication. It is usually
> located at: ...\MSSQL\REPLDATA\name_of_publication. I have dealt with
> cases that the snapshot file for the creation of the table was not
> correctly created. You can manually modify this file to create the
> correct table with the correct columns. When the initialization takes
> place, it will take this file and apply it tio the subscribers. Hope this
> helps!
> -Jose Molina
>
> "Darius T." <dta@.takas.lt> wrote in message
> news:%23s$CB3Z$EHA.1452@.TK2MSFTNGP11.phx.gbl...
>

broken links when using https://

Hi,
My reports are hosted on a secure website (SSL). Most of the pages
appear just fine, but the 4 links in the top-right corner of the screen
("Home", "My Subscriptions", "Site Settings", "Help") are broken as
they are referring to pages using http:// when of course they should be
using https://
For example, the HTML for the "My Subscriptions" link looks like this:
<a
href="http://links.10026.com/?link=http://dub-mis-001/Reports/Pages/Subscriptions.aspx">My Subscriptions</a>
But it should look like this:
<a
href="http://links.10026.com/?link=https://dub-mis-001/Reports/Pages/Subscriptions.aspx">My Subscriptions</a>
I'm aware that this porblem has been mentioned a number of times
previously in this group, but nobody seems to have a solution?
Is there way I can "change" the HTML that is generated so that the
links use https:// or relative addressing?
Best Wishes,
DonAlternatively, is there any way to remove these links?
Cheers,
Don

Broken Linked Server

Been running SQL 7.0 sp4 for 1 1/2 years on two NT4.0 sp6a
servers on the same subnet with no problems. ServerA is
linked to ServerB, and ServerB is linked to ServerA.
I haven't loaded any new software. This past Monday, all
the jobs on ServerA that use data from ServerB failed.
All the jobs on ServerB that use data on ServerA failed.
I have not been able to figure out what caused the
problem, nor have I been able to correct the problem. I
deleted the link on each server, and tried recreating the
link but failed with the errorlog showing "Login failed
for user "ServerA(B)\Administrator". Same error on both
machines. Yet, both servers are able to successfully
register with each other through EM. The audit log shows
the message "Login succeeded for user 'ServerA(B)
\Administrator'.connection:Trusted." So, I would assume,
that they can communicate with each other.
I even deleted all my network services/protocols then
reinstalled them with no help.
Any ideas?
Did the passwords change on the Administrator account?
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Nope. Nobody else admins the account. I thought the same thing, and
just in
case recent them. It didn't help. What I don't get is the server,
through EM,
can successfully register both servers (which uses the account I logged
in with),
but can't link the servers (which I map to the very same account).
Kevin McDonnell [MSFT] wrote:

>Did the passwords change on the Administrator account?
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and confers no rights.
>
>
>

Broken link to chart

Hi,

I have made a chart that displays data for my company. However, when I have the report.rdl on the "live" serverand use the "live" data source, I can't get the report manager to display the chart, only the famuos x of a broken image link. If I place the report.rdl on the test server and use the "live" data source the chart is displayed.

What is the difference between having the report on the test server versus the "live" server?

Report manager -> report on test server -> live data source = working

Report manager -> report on "live" server -> live data source = no image

I dont think the problem is sequrity related, but I dont know for sure...

Thanks in advance!

Niklas

I am able to display data in text, but not images, like the chart...

Any thoughts?

|||

I mean, what is the difference between showing data in an image and data in text?

Please, need help

|||

Wow, I found out how to solve this problem and if might say so...Damn I'm good.

Open the folder "...\Reporting Services\ReportServer" and find the file called "RSReportServer" (it is a Web Configuration File).

In this file you need to find the line called: "

<Extension Name="HTML4.0" Type="Microsoft.ReportingServices.Rendering.HtmlRenderer.Html40RenderingExtension,Microsoft.ReportingServices.HtmlRendering" Visible="false"/>

Change Visible to true and the chart image is displayed, or if that doesent work, try changing something else in that file.

It's great to see that the support really works here =)

Thank you Niklas.

Ah, you're welcome!

Broken link in e-mail subscription

I have scheduled the first report at this customers site to send out e-mails
of reports. It is sending out the report just fine and including the PDF
file that I requested however I also checked the Include Link box in the
schedule because of the requirements of the users wanting to hide details in
the e-mailed report and wanting to link to the report and then drill down to
see the details only when necessary.
When the e-mail is sent it doesn't formulate the URL correctly and says the
following:
The report is accessible at the following address:
http://localhost/reports?%2fAccounting%.........
The closest thing I've found online to resolve this was this link:
http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad125641/ReadmeSQL2005.htm
-- See "Incomplete Report Server Configuration on an SSL-Protected Server
Causes Broken Report Links in Report Server E-Mail Notifications" section.
Unfortunately, an examination of the suggestion revealed that we are not
using SSL and when I checked the <UrlRoot> in the RSReportServer.config file
it was set correctly to the server name that I'd want used in replacing
localhost in the URL.
Does anyone know how to fix this?You'll actually need to modify the rswebapplication.config file for that
setting.
The section should look like the following to get your issue resolved:
<ReportServerUrl>http://SERVERNAME/ReportServer</ReportServerUrl>
<ReportServerVirtualDirectory></ReportServerVirtualDirectory>
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Broken link in e-mail subscription
> I have scheduled the first report at this customers site to send out
e-mails
> of reports. It is sending out the report just fine and including the PDF
> file that I requested however I also checked the Include Link box in the
> schedule because of the requirements of the users wanting to hide details
in
> the e-mailed report and wanting to link to the report and then drill down
to
> see the details only when necessary.
> When the e-mail is sent it doesn't formulate the URL correctly and says
the
> following:
> The report is accessible at the following address:
> http://localhost/reports?%2fAccounting%.........
> The closest thing I've found online to resolve this was this link:
>
http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad1
25641/ReadmeSQL2005.htm
> -- See "Incomplete Report Server Configuration on an SSL-Protected
Server
> Causes Broken Report Links in Report Server E-Mail Notifications" section.
> Unfortunately, an examination of the suggestion revealed that we are not
> using SSL and when I checked the <UrlRoot> in the RSReportServer.config
file
> it was set correctly to the server name that I'd want used in replacing
> localhost in the URL.
> Does anyone know how to fix this?
>|||Thanks for the quick reply!
That didn't work; the e-mailed report still says localhost. Does it require
a reboot of the server or the service?
I changed two files named rswebapplication.config located here on the server:
C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportManager
and
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager
Any ideas?
Thanks,
Keith
"Chris Alton [MSFT]" wrote:
> You'll actually need to modify the rswebapplication.config file for that
> setting.
> The section should look like the following to get your issue resolved:
> <ReportServerUrl>http://SERVERNAME/ReportServer</ReportServerUrl>
> <ReportServerVirtualDirectory></ReportServerVirtualDirectory>
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > Thread-Topic: Broken link in e-mail subscription
> >
> > I have scheduled the first report at this customers site to send out
> e-mails
> > of reports. It is sending out the report just fine and including the PDF
> > file that I requested however I also checked the Include Link box in the
> > schedule because of the requirements of the users wanting to hide details
> in
> > the e-mailed report and wanting to link to the report and then drill down
> to
> > see the details only when necessary.
> >
> > When the e-mail is sent it doesn't formulate the URL correctly and says
> the
> > following:
> >
> > The report is accessible at the following address:
> > http://localhost/reports?%2fAccounting%.........
> >
> > The closest thing I've found online to resolve this was this link:
> >
> http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad1
> 25641/ReadmeSQL2005.htm
> > -- See "Incomplete Report Server Configuration on an SSL-Protected
> Server
> > Causes Broken Report Links in Report Server E-Mail Notifications" section.
> >
> > Unfortunately, an examination of the suggestion revealed that we are not
> > using SSL and when I checked the <UrlRoot> in the RSReportServer.config
> file
> > it was set correctly to the server name that I'd want used in replacing
> > localhost in the URL.
> >
> > Does anyone know how to fix this?
> >
> >
>|||If you have two instances of Reporting Services installed then you need to
figure out which instance you are connecting to and modify the correct
configuration files. Also, if you modified the rsreportserver.config file
for the wrong instance then that could have caused it as well.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Broken link in e-mail subscription
> thread-index: AcgKl6UBYx8q8qj4SQiu7zquybnHhQ==> X-WBNR-Posting-Host: 64.4.185.178
> From: =?Utf-8?B?Z3JlZW5tdG5zdW4=?= <greenmtnsun@.discussions.microsoft.com>
> References: <F510629C-E0E1-4162-9845-87980E21B924@.microsoft.com>
<8SY8AQpCIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> Subject: RE: Broken link in e-mail subscription
> Thanks for the quick reply!
> That didn't work; the e-mailed report still says localhost. Does it
require
> a reboot of the server or the service?
> I changed two files named rswebapplication.config located here on the
server:
> C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
Services\ReportManager
> and
> C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportManager
> Any ideas?
> Thanks,
> Keith
>
> "Chris Alton [MSFT]" wrote:
> > You'll actually need to modify the rswebapplication.config file for
that
> > setting.
> >
> > The section should look like the following to get your issue resolved:
> > <ReportServerUrl>http://SERVERNAME/ReportServer</ReportServerUrl>
> > <ReportServerVirtualDirectory></ReportServerVirtualDirectory>
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> > > Thread-Topic: Broken link in e-mail subscription
> > >
> > > I have scheduled the first report at this customers site to send out
> > e-mails
> > > of reports. It is sending out the report just fine and including the
PDF
> > > file that I requested however I also checked the Include Link box in
the
> > > schedule because of the requirements of the users wanting to hide
details
> > in
> > > the e-mailed report and wanting to link to the report and then drill
down
> > to
> > > see the details only when necessary.
> > >
> > > When the e-mail is sent it doesn't formulate the URL correctly and
says
> > the
> > > following:
> > >
> > > The report is accessible at the following address:
> > > http://localhost/reports?%2fAccounting%.........
> > >
> > > The closest thing I've found online to resolve this was this link:
> > >
> >
http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad1
> > 25641/ReadmeSQL2005.htm
> > > -- See "Incomplete Report Server Configuration on an SSL-Protected
> > Server
> > > Causes Broken Report Links in Report Server E-Mail Notifications"
section.
> > >
> > > Unfortunately, an examination of the suggestion revealed that we are
not
> > > using SSL and when I checked the <UrlRoot> in the
RSReportServer.config
> > file
> > > it was set correctly to the server name that I'd want used in
replacing
> > > localhost in the URL.
> > >
> > > Does anyone know how to fix this?
> > >
> > >
> >
> >
>|||Welp, I didn't know I had a second instance, but you were right! I have it
fixed.
One thing though, I couldn't google a fix to this problem. Perhaps there is
something that could be added to BOL? Or did I miss it?
"Chris Alton [MSFT]" wrote:
> If you have two instances of Reporting Services installed then you need to
> figure out which instance you are connecting to and modify the correct
> configuration files. Also, if you modified the rsreportserver.config file
> for the wrong instance then that could have caused it as well.
> --
> Chris Alton, Microsoft Corp.
> SQL Server Developer Support Engineer
> This posting is provided "AS IS" with no warranties, and confers no rights.
> --
> > Thread-Topic: Broken link in e-mail subscription
> > thread-index: AcgKl6UBYx8q8qj4SQiu7zquybnHhQ==> > X-WBNR-Posting-Host: 64.4.185.178
> > From: =?Utf-8?B?Z3JlZW5tdG5zdW4=?= <greenmtnsun@.discussions.microsoft.com>
> > References: <F510629C-E0E1-4162-9845-87980E21B924@.microsoft.com>
> <8SY8AQpCIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> > Subject: RE: Broken link in e-mail subscription
> >
> > Thanks for the quick reply!
> >
> > That didn't work; the e-mailed report still says localhost. Does it
> require
> > a reboot of the server or the service?
> >
> > I changed two files named rswebapplication.config located here on the
> server:
> > C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
> Services\ReportManager
> > and
> > C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> Services\ReportManager
> >
> > Any ideas?
> >
> > Thanks,
> > Keith
> >
> >
> > "Chris Alton [MSFT]" wrote:
> >
> > > You'll actually need to modify the rswebapplication.config file for
> that
> > > setting.
> > >
> > > The section should look like the following to get your issue resolved:
> > > <ReportServerUrl>http://SERVERNAME/ReportServer</ReportServerUrl>
> > > <ReportServerVirtualDirectory></ReportServerVirtualDirectory>
> > > --
> > > Chris Alton, Microsoft Corp.
> > > SQL Server Developer Support Engineer
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > > --
> > > > Thread-Topic: Broken link in e-mail subscription
> > > >
> > > > I have scheduled the first report at this customers site to send out
> > > e-mails
> > > > of reports. It is sending out the report just fine and including the
> PDF
> > > > file that I requested however I also checked the Include Link box in
> the
> > > > schedule because of the requirements of the users wanting to hide
> details
> > > in
> > > > the e-mailed report and wanting to link to the report and then drill
> down
> > > to
> > > > see the details only when necessary.
> > > >
> > > > When the e-mail is sent it doesn't formulate the URL correctly and
> says
> > > the
> > > > following:
> > > >
> > > > The report is accessible at the following address:
> > > > http://localhost/reports?%2fAccounting%.........
> > > >
> > > > The closest thing I've found online to resolve this was this link:
> > > >
> > >
> http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad1
> > > 25641/ReadmeSQL2005.htm
> > > > -- See "Incomplete Report Server Configuration on an SSL-Protected
> > > Server
> > > > Causes Broken Report Links in Report Server E-Mail Notifications"
> section.
> > > >
> > > > Unfortunately, an examination of the suggestion revealed that we are
> not
> > > > using SSL and when I checked the <UrlRoot> in the
> RSReportServer.config
> > > file
> > > > it was set correctly to the server name that I'd want used in
> replacing
> > > > localhost in the URL.
> > > >
> > > > Does anyone know how to fix this?
> > > >
> > > >
> > >
> > >
> >
>|||I'm sure its in Books Online somewhere. It has to get the link for the
report from somewhere and if it is configured as localhost in all the
configuration files then its going to use localhost.
--
Chris Alton, Microsoft Corp.
SQL Server Developer Support Engineer
This posting is provided "AS IS" with no warranties, and confers no rights.
--
> Thread-Topic: Broken link in e-mail subscription
> Welp, I didn't know I had a second instance, but you were right! I have
it
> fixed.
> One thing though, I couldn't google a fix to this problem. Perhaps there
is
> something that could be added to BOL? Or did I miss it?
> "Chris Alton [MSFT]" wrote:
> > If you have two instances of Reporting Services installed then you need
to
> > figure out which instance you are connecting to and modify the correct
> > configuration files. Also, if you modified the rsreportserver.config
file
> > for the wrong instance then that could have caused it as well.
> > --
> > Chris Alton, Microsoft Corp.
> > SQL Server Developer Support Engineer
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > --
> > > Thread-Topic: Broken link in e-mail subscription
> > > thread-index: AcgKl6UBYx8q8qj4SQiu7zquybnHhQ==> > > X-WBNR-Posting-Host: 64.4.185.178
> > > From: =?Utf-8?B?Z3JlZW5tdG5zdW4=?=<greenmtnsun@.discussions.microsoft.com>
> > > References: <F510629C-E0E1-4162-9845-87980E21B924@.microsoft.com>
> > <8SY8AQpCIHA.360@.TK2MSFTNGHUB02.phx.gbl>
> > > Subject: RE: Broken link in e-mail subscription
> > >
> > > Thanks for the quick reply!
> > >
> > > That didn't work; the e-mailed report still says localhost. Does it
> > require
> > > a reboot of the server or the service?
> > >
> > > I changed two files named rswebapplication.config located here on the
> > server:
> > > C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting
> > Services\ReportManager
> > > and
> > > C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
> > Services\ReportManager
> > >
> > > Any ideas?
> > >
> > > Thanks,
> > > Keith
> > >
> > >
> > > "Chris Alton [MSFT]" wrote:
> > >
> > > > You'll actually need to modify the rswebapplication.config file for
> > that
> > > > setting.
> > > >
> > > > The section should look like the following to get your issue
resolved:
> > > > <ReportServerUrl>http://SERVERNAME/ReportServer</ReportServerUrl>
> > > > <ReportServerVirtualDirectory></ReportServerVirtualDirectory>
> > > > --
> > > > Chris Alton, Microsoft Corp.
> > > > SQL Server Developer Support Engineer
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > > --
> > > > > Thread-Topic: Broken link in e-mail subscription
> > > > >
> > > > > I have scheduled the first report at this customers site to send
out
> > > > e-mails
> > > > > of reports. It is sending out the report just fine and including
the
> > PDF
> > > > > file that I requested however I also checked the Include Link box
in
> > the
> > > > > schedule because of the requirements of the users wanting to hide
> > details
> > > > in
> > > > > the e-mailed report and wanting to link to the report and then
drill
> > down
> > > > to
> > > > > see the details only when necessary.
> > > > >
> > > > > When the e-mail is sent it doesn't formulate the URL correctly
and
> > says
> > > > the
> > > > > following:
> > > > >
> > > > > The report is accessible at the following address:
> > > > > http://localhost/reports?%2fAccounting%.........
> > > > >
> > > > > The closest thing I've found online to resolve this was this link:
> > > > >
> > > >
> >
http://download.microsoft.com/download/5/0/e/50ec0a69-d69e-4962-b2c9-80bbad1
> > > > 25641/ReadmeSQL2005.htm
> > > > > -- See "Incomplete Report Server Configuration on an
SSL-Protected
> > > > Server
> > > > > Causes Broken Report Links in Report Server E-Mail Notifications"
> > section.
> > > > >
> > > > > Unfortunately, an examination of the suggestion revealed that we
are
> > not
> > > > > using SSL and when I checked the <UrlRoot> in the
> > RSReportServer.config
> > > > file
> > > > > it was set correctly to the server name that I'd want used in
> > replacing
> > > > > localhost in the URL.
> > > > >
> > > > > Does anyone know how to fix this?
> > > > >
> > > > >
> > > >
> > > >
> > >
> >
> >
>

Broken Images and Charts in Deployed Reports

Hi all,
Does anyone know why on some installations of RS, using the Reports Viewer
(http://localhost/Reports/), all images and charts appear to be broken, but
if exported, work fine. Using ReportServer works, but its not very
user-friendly for our clients.
I only read a couple of posts about this issue, but couldnt find an answer.
Some people said that if you enable Allow All Cookie's in IE, it works... but
why then is this problem only occuring on some installations of RS?
The one major differance I can point out is that it seams that broken images
only occur on Servers (Win2003) but not on development (XP) computers... Is
this as simple as an IIS issue, or security perhaps?
Please help! I'm sure there are many others with the same issue!
Thanks,
AndreAnswer is simple, and stupid...
IE does block the cookies from the RS website for some reason.
There are two fixes I know.
1) Inform anyone using RS to find the security icon at the bottom of IE and
right click it and allow Cookies from that site.
2) Inform anyone using RS to go to IE security and turn of automatic
handling of cookies and allow all cookies... I dont think this is quite hte
best idea though.
Andre
"Arkiliknam" wrote:
> Hi all,
> Does anyone know why on some installations of RS, using the Reports Viewer
> (http://localhost/Reports/), all images and charts appear to be broken, but
> if exported, work fine. Using ReportServer works, but its not very
> user-friendly for our clients.
> I only read a couple of posts about this issue, but couldnt find an answer.
> Some people said that if you enable Allow All Cookie's in IE, it works... but
> why then is this problem only occuring on some installations of RS?
> The one major differance I can point out is that it seams that broken images
> only occur on Servers (Win2003) but not on development (XP) computers... Is
> this as simple as an IIS issue, or security perhaps?
> Please help! I'm sure there are many others with the same issue!
> Thanks,
> Andre

Broken Image when using SQL 2000 Reporting Services

Hi,

I have a report that works fine when i deploy it in the development environment, but when i deploy the same report on the production server pointing to the same data source as the development environment; the images doesn't show up. I do see broken image X on all the images.

I searched the internet but couldn't find a solution to solve this problem. Any help on this will be appreciated.

Thanks,

Chaitanya

How did you express the image source expression? And is it embedded or external?

>L<

|||

The problem has been resolved.

When i am investigating the security setting and other configuration files for the reporting services i realised that i have not applied SP2 for the Reporting Services on the Production Server. SP2 provided a lot of updates which include showing the Print button on the Report Viewer, displaying images etc which are helpful.

By the way the images are web based external images.

Thanks,

Chaitanya

Broken hyperlinks in subscription emails

Users are receiving link-only subscriptions with broken links where the break
always starts with an exclamation mark. It looks like this in email source
view:
<table><tr><td><span>My comment text added to
subscription</span></td></tr></table><table><tr><td><span>The report is
accessible at the following address:</span></td><tr><td><a
href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
Any suggestions how to fix or work around this?I have updated Reporting Services to version 9.00.2040.00 but I am still
getting broken hyperlinks in subscription emails.
"PeterG" wrote:
> Users are receiving link-only subscriptions with broken links where the break
> always starts with an exclamation mark. It looks like this in email source
> view:
> <table><tr><td><span>My comment text added to
> subscription</span></td></tr></table><table><tr><td><span>The report is
> accessible at the following address:</span></td><tr><td><a
> href="http://links.10026.com/?link=http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&r!
> s%3aParameterLanguage=en-US">http://servername/reportportal/viewreport.aspx?%2fEncoded+Report+Name&Par1=Encoded+param+value&&Par2=Encoded+param+value&rs%3aParameterLanguage=en-US</a></td></tr></table>
> Any suggestions how to fix or work around this?
>

Broken BI Studio after Office 2k7 install

After an install of Office 2k7 last night, I'm getting some errors in my event log when i run BI Studio. The errors occur when i click a tab that tries to browse the metadata of the cube. IE: The calculations tab, or KPI's, etc..

These are the 2 errors:

Event Type: Error
Event Source: MSOLAP$LocalCube
Event Category: (289)
Event ID: 1
Date: 1/09/07
Time: 8:54:08 AM
User: N/A
Computer: WS124
Description:
The description for Event ID ( 1 ) in Source ( MSOLAP$LocalCube ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Message-handling subsystem: The message manager for the 4105 locale cannot be found..

Event Type: Error
Event Source: MSOLAP$LocalCube
Event Category: (289)
Event ID: 0
Date: 1/09/07
Time: 8:54:08 AM
User: N/A
Computer: WS124
Description:
The description for Event ID ( 0 ) in Source ( MSOLAP$LocalCube ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Errors in the metadata manager. The configuration property updates were not persisted. Internal error: An unexpected exception occured..

I am going to have BI Studio reinstalled, to see if that fixes the error. I am curious if anyone else has had/heard of this issue?

Thanks,
C

Hello. Here is a blog post that might help.

http://geekswithblogs.net/darrengosbell/archive/2006/11/17/97367.aspx

I am running Office 2007 with SQL Server 2005 SP2 CTP3(december). I never uninstalled the Office 2003 web components when I upgraded to Office 2007. This mix works without any problems.

HTH

Thomas Ivarsson

|||Thanks,
We are getting the hotfix from MS to give it a go. I will certainly post the results.

C|||

Hi

I got the same problem after installing MS Excel 2007 with the option to remove Excel 2003. I reinstalled Excel 2003 again later for testing purposes.

Chris, you never posted the results of the hotfix so I suppose that it worked!

Problem is, which hotfix did you use and where did you get it?

Thomas the link that you've supplied doesn't work ...

Thanks

Kobus

|||

Hi

Sorry Thomas, your link worked the second time around. Our Service Provider is experiencing problems with our connection so now we are on the backup ISDN line, we don't have T1 in South Africa like you guys in the US. The page may have timed out.

Info on the hotfix is here:
http://support.microsoft.com/default.aspx/kb/926421

More info on the workaround here:
http://sqlug.be/forums/339/ShowThread.aspx#339

You have to contact MS for the hotfix. SQL Server 2005 SP2 will solve the problem and since our company has that, that's the way I'll go.

Word Out
Kobus

|||Sorry Kobus,

You're right, i didn't post my results..

After applying the hotfix, i still ended up having to copy some DLL's around, as per your second link.

After i did that, it worked fine. But then i had some other software installed (i can't remember what it is right now) and it reverted the DLL's, so i had to re-copy them back so they were all the same version again.

Regardless, it should be ok if you install the hotfix, and make sure all the DLL's in those locations are the same version.

C

Broken BI Studio after Office 2k7 install

After an install of Office 2k7 last night, I'm getting some errors in my event log when i run BI Studio. The errors occur when i click a tab that tries to browse the metadata of the cube. IE: The calculations tab, or KPI's, etc..

These are the 2 errors:

Event Type: Error
Event Source: MSOLAP$LocalCube
Event Category: (289)
Event ID: 1
Date: 1/09/07
Time: 8:54:08 AM
User: N/A
Computer: WS124
Description:
The description for Event ID ( 1 ) in Source ( MSOLAP$LocalCube ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Message-handling subsystem: The message manager for the 4105 locale cannot be found..

Event Type: Error
Event Source: MSOLAP$LocalCube
Event Category: (289)
Event ID: 0
Date: 1/09/07
Time: 8:54:08 AM
User: N/A
Computer: WS124
Description:
The description for Event ID ( 0 ) in Source ( MSOLAP$LocalCube ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. You may be able to use the /AUXSOURCE= flag to retrieve this description; see Help and Support for details. The following information is part of the event: Message-handling subsystem: The message manager for the default locale cannot be found. The locale will be changed to US English. Errors in the metadata manager. The configuration property updates were not persisted. Internal error: An unexpected exception occured..

I am going to have BI Studio reinstalled, to see if that fixes the error. I am curious if anyone else has had/heard of this issue?

Thanks,
C

Hello. Here is a blog post that might help.

http://geekswithblogs.net/darrengosbell/archive/2006/11/17/97367.aspx

I am running Office 2007 with SQL Server 2005 SP2 CTP3(december). I never uninstalled the Office 2003 web components when I upgraded to Office 2007. This mix works without any problems.

HTH

Thomas Ivarsson

|||Thanks,
We are getting the hotfix from MS to give it a go. I will certainly post the results.

C|||

Hi

I got the same problem after installing MS Excel 2007 with the option to remove Excel 2003. I reinstalled Excel 2003 again later for testing purposes.

Chris, you never posted the results of the hotfix so I suppose that it worked!

Problem is, which hotfix did you use and where did you get it?

Thomas the link that you've supplied doesn't work ...

Thanks

Kobus

|||

Hi

Sorry Thomas, your link worked the second time around. Our Service Provider is experiencing problems with our connection so now we are on the backup ISDN line, we don't have T1 in South Africa like you guys in the US. The page may have timed out.

Info on the hotfix is here:
http://support.microsoft.com/default.aspx/kb/926421

More info on the workaround here:
http://sqlug.be/forums/339/ShowThread.aspx#339

You have to contact MS for the hotfix. SQL Server 2005 SP2 will solve the problem and since our company has that, that's the way I'll go.

Word Out
Kobus

|||Sorry Kobus,

You're right, i didn't post my results..

After applying the hotfix, i still ended up having to copy some DLL's around, as per your second link.

After i did that, it worked fine. But then i had some other software installed (i can't remember what it is right now) and it reverted the DLL's, so i had to re-copy them back so they were all the same version again.

Regardless, it should be ok if you install the hotfix, and make sure all the DLL's in those locations are the same version.

C

Breaking up a Table

I hope I can get this across clearly.

I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7

Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2

If Col5 is twice the width... haha just kidding...

so Col5 and Col6 go into LookupTable3

There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3

My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row

thanks folks

T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B

Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:

Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table

etc...

--
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.

I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.

INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL

INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL

... etc

INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
... etc

--
David Portas
SQL Server MVP
--|||Thanks folks....

Lookup Table was a term used to help with the understanding of the question

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegro ups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --

Breaking up a Table

I hope I can get this across clearly.
I have a table that needs to be broken into 3 tables.
Col1 Col2 Col3 Col4 Col5 Col6 Col7
Col1 and Col2 need to go into LookupTable1
Col3 and Col4 into LookupTable2
If Col5 is twice the width... haha just kidding...
so Col5 and Col6 go into LookupTable3
There is a 4th table which is made up of foreign keys which are the PK of
LookupTable1,2,3
My questions is, how to get the data from the columns of each row and add it
to its respective lookuptable
and sequentially step throw the table to repeat the above step until I've
processed each row
thanks folks
T.BThe Bear wrote:
> I hope I can get this across clearly.
> I have a table that needs to be broken into 3 tables.
> Col1 Col2 Col3 Col4 Col5 Col6 Col7
> Col1 and Col2 need to go into LookupTable1
> Col3 and Col4 into LookupTable2
> If Col5 is twice the width... haha just kidding...
> so Col5 and Col6 go into LookupTable3
> There is a 4th table which is made up of foreign keys which are the
> PK of LookupTable1,2,3
> My questions is, how to get the data from the columns of each row and
> add it to its respective lookuptable
> and sequentially step throw the table to repeat the above step until
> I've processed each row
> thanks folks
> T.B
Since your DDL is a mystery, I'll assume col1 and col2 are an
ID/Description combo:
Insert Into LookupTable1 (
col1,
col2 )
Select DISTINCT Col1, Col2
From Table
etc...
David Gugick
Imceda Software
www.imceda.com|||What's a "lookup table"? No such thing in any relational database.
There is only one kind of table.
I guess the following is what you want. There should be absolutely no
reason to do this sequentially row by row.
INSERT INTO Table1 (col1, col2)
SELECT DISTINCT col1, col2
FROM YourTable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
INSERT INTO Table2 (col3, col4)
SELECT DISTINCT col3, col4
FROM YourTable
WHERE col3 IS NOT NULL
AND col4 IS NOT NULL
.. etc
INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
FROM YourTable AS T0
JOIN Table1 AS T1
ON T0.col1 = T1.col1
AND T0.col2 = T1.col2
JOIN Table2 AS T2
ON T0.col3 = T2.col3
AND T0.col4 = T2.col4
.. etc
David Portas
SQL Server MVP
--|||Thanks folks....
Lookup Table was a term used to help with the understanding of the question
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1114019514.957668.75170@.f14g2000cwb.googlegroups.com...
> What's a "lookup table"? No such thing in any relational database.
> There is only one kind of table.
> I guess the following is what you want. There should be absolutely no
> reason to do this sequentially row by row.
> INSERT INTO Table1 (col1, col2)
> SELECT DISTINCT col1, col2
> FROM YourTable
> WHERE col1 IS NOT NULL
> AND col2 IS NOT NULL
> INSERT INTO Table2 (col3, col4)
> SELECT DISTINCT col3, col4
> FROM YourTable
> WHERE col3 IS NOT NULL
> AND col4 IS NOT NULL
> .. etc
> INSERT INTO NewTable (T1.key_col1, T2.key_col2, T3.key_col3)
> SELECT DISTINCT T1.key_col1, T2.key_col2, T3.key_col3
> FROM YourTable AS T0
> JOIN Table1 AS T1
> ON T0.col1 = T1.col1
> AND T0.col2 = T1.col2
> JOIN Table2 AS T2
> ON T0.col3 = T2.col3
> AND T0.col4 = T2.col4
> ... etc
> --
> David Portas
> SQL Server MVP
> --
>