Replacing NULL value in multiple columns in a table

Hi,

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

Thanks in Advance
BobBF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.
>
Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL
>
But because there are lots of columns this is pretty much work, also
there are multiple tables.
>
Is there an easy way to replace all NULL values in all columns in a
table?

First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.

No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:

SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Great,

Thanks a lot, with the query I can create the new script much and much
easier.

I replaced the o.name with the tables which I new they had the problem
and now I have have all columns to replace the values.

Thanks a lot.

Regards, Bob

Erland Sommarskog schreef:

Quote:

Originally Posted by

BF (bob@.faessen.net) writes:

Quote:

Originally Posted by

I have some tables where I import data in, lots of field have gotten a
NULL value which the application can not handle.

Now can I replace each NULL value with '' in a columns with:
update <tableset [<column>] = '' where [<column>] IS NULL

But because there are lots of columns this is pretty much work, also
there are multiple tables.

Is there an easy way to replace all NULL values in all columns in a
table?

>
First of all, that operation would only be possible with columns
that hold character data. For numeric and datetime columns there
is rarely any good replacement for NULL values. So, unless, your
database only has nullable character columns, you need to fix the
application to handle NULL values anyway.
>
No, there is no direct function for setting many columns to NULL. You
need to have an UPDATE statement for each table, and one that lists
all columns that should be set to NULL. The good news is that you
can generate the statements:
>
SELECT 'UPDATE ' + o.name + ' SET ' + c.name + ' = '''' WHERE ' +
c.name + ' IS NULL'
FROM sysobjects o
JOIN syscolumns c ON o.id = c.id
JOIN systypes t ON c.xtype = t.xtype
WHERE o.xtype = 'U'
AND (t.name like '%char' or t.name like '%text')
>
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Replacing MSDE by SQL Server 2005

Hello ,
I have an application that uses MSDE internally. Now I want to replace
that MSDE database by full SQL Server 2005. I have SQL server 2005
installed.
Is it possible to replace the MSDE instance that the application is
using, by SQL server instance without changing the application's
configurations?
Thanks,
Anirudh Bakore.
Hi,
You could.. All you have to do is:-
1. back up the databases in MSDE
2. Restore the database in SQL 2005
3. Create the logins
4. Change the application connection string to point to SQL server 2005
Thanks
Hari
"anibakore" <ani.bakore@.gmail.com> wrote in message
news:1162202124.621048.175580@.e3g2000cwe.googlegro ups.com...
> Hello ,
> I have an application that uses MSDE internally. Now I want to replace
> that MSDE database by full SQL Server 2005. I have SQL server 2005
> installed.
> Is it possible to replace the MSDE instance that the application is
> using, by SQL server instance without changing the application's
> configurations?
>
> Thanks,
> Anirudh Bakore.
>
|||That's the main problem.. I dont have an access to the
connectionstring. It is embedded in the application.
Hari Prasad wrote:
[vbcol=seagreen]
> Hi,
> You could.. All you have to do is:-
> 1. back up the databases in MSDE
> 2. Restore the database in SQL 2005
> 3. Create the logins
> 4. Change the application connection string to point to SQL server 2005
> Thanks
> Hari
>
> "anibakore" <ani.bakore@.gmail.com> wrote in message
> news:1162202124.621048.175580@.e3g2000cwe.googlegro ups.com...
|||That may present a bit more of a challenge.
First, it is important to determine if the MSDE is the 'default' instance of
SQL Server, or if it is a 'named instance'.
Go to [Administrative Tools], and select the [Services] applet.
How many services that start with MSSQL do you find?
If more than MSSQLSERVER, what are the names?
Gather and report that information and someone here can help you further.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"anibakore" <ani.bakore@.gmail.com> wrote in message
news:1162270303.823821.177750@.m73g2000cwd.googlegr oups.com...
> That's the main problem.. I dont have an access to the
> connectionstring. It is embedded in the application.
> Hari Prasad wrote:
>
|||Hi,
The instance is a named instance.There is a separate service called
"MSSQL$Appname".
Where Appname is the name of the application I am using.
Thanks,
Anirudh
Arnie Rowland wrote:
[vbcol=seagreen]
> That may present a bit more of a challenge.
> First, it is important to determine if the MSDE is the 'default' instance of
> SQL Server, or if it is a 'named instance'.
> Go to [Administrative Tools], and select the [Services] applet.
> How many services that start with MSSQL do you find?
> If more than MSSQLSERVER, what are the names?
> Gather and report that information and someone here can help you further.
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "anibakore" <ani.bakore@.gmail.com> wrote in message
> news:1162270303.823821.177750@.m73g2000cwd.googlegr oups.com...
|||I would recommend that you first install SQL 2005 on a test computer and
test the application against the test server.
This article seems to be complete about the 'in-place' upgrade process.
http://www.microsoft.com/technet/prodtechnol/sql/2005/msde2sqlexpress.mspx
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"anibakore" <ani.bakore@.gmail.com> wrote in message
news:1162272568.028079.227760@.k70g2000cwa.googlegr oups.com...
> Hi,
> The instance is a named instance.There is a separate service called
> "MSSQL$Appname".
> Where Appname is the name of the application I am using.
> Thanks,
> Anirudh
> Arnie Rowland wrote:
>

Replacing a cursor

I am currently evaluating the performance of an application which relies heavily on cursors. Below is an code example. I am looking for some good performance gains, is this the best method of updating rows of data?

DECLARE @.PAlloc_TimeID as int
DECLARE @.PAlloc_UniqueID as int
DECLARE PAlloc CURSOR FOR
SELECT TimeID,
UniqueID
FROM #PMatch
GROUP BY TimeID, UniqueID
HAVING SUM(Active) > 1

OPEN PAlloc
FETCH NEXT FROM PAlloc INTO @.PAlloc_TimeID , @.PAlloc_UniqueID
WHILE (@.@.FETCH_STATUS = 0)
BEGIN

UPDATE P
SET Active = 0
FROM #PMatch P
WHERE P.TimeID = @.PAlloc_TimeID
AND P.UniqueID = @.PAlloc_UniqueID
AND P.ElementID > (
SELECT MIN(ElementID)
FROM #PMatch
WHERE TimeID = @.PAlloc_TimeID
AND UniqueID = @.PAlloc_UniqueID)

FETCH NEXT FROM PAlloc INTO @.PAlloc_TimeID , @.PAlloc_UniqueID
END
CLOSE PAlloc
DEALLOCATE PAlloc

Normally, using a CURSOR is NOT the best way to update data. (However, in a few situations, it may be necessary.)

A set based operation (UPDATE) is usually the 'best' way to update. Consider if your process above could be re-written as a single UPDATE statement, perhaps using one or more sub-queries. (It might be easier than you think.)

|||

Yes... Arnie is right, It can be replace with single Update

Statement. But if the Database is gigantic then Updating in a single statement

is not preferred.

|||

u'll be able to write this query in one (or more) update statement(s)...or use a case in update statement.... most of the times u wont need a sursor at all.... and i guess even for a large database in this is a better solution..., as cursor on a large table will block resources for long...

sql

Replace View with Join or SubQuery

My application uses a View stored in a database. I have queries that join
this view with other tables. I'd like to eliminate the view. For example, if
the view was defined by:
CREATE VIEW dbo.AcctBalance
AS
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
My VB code (using ADO) creates this T-SQL query:
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
All my attempts to replace the View have failed so far. Can someone provide
guidance?
Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
RichardRichard
Why do you want to eliminate the VIEW? Any reasons?
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN
(
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
) AS AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
> My application uses a View stored in a database. I have queries that join
> this view with other tables. I'd like to eliminate the view. For example,
if
> the view was defined by:
> CREATE VIEW dbo.AcctBalance
> AS
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> My VB code (using ADO) creates this T-SQL query:
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
> All my attempts to replace the View have failed so far. Can someone
provide
> guidance?
> Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
> --
> Richard
>|||The database does not belong to me, but to the customer. I'm trying to get
my code out of the customer's database. Also, if I need to revise the View,
I must code a utility to modify the View in the customer's database. Any
other change can be implemented by building a new dll. I understand that
it's partly a philosophical thing.
Your post indicates that I can join a table that is created in the
parenthesis. I like that idea and will try it. Thanks a lot.
Richard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Richard
> Why do you want to eliminate the VIEW? Any reasons?
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN
> (
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> ) AS AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
>
> "Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
> message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
join
example,
> if
> provide
dbo.Sales.
>|||Hi,
Just to confirm, your code works perfectly for me. Thanks again.
Richard
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:O8NbIERFFHA.1392@.tk2msftngp13.phx.gbl...
> The database does not belong to me, but to the customer. I'm trying to get
> my code out of the customer's database. Also, if I need to revise the
View,
> I must code a utility to modify the View in the customer's database. Any
> other change can be implemented by building a new dll. I understand that
> it's partly a philosophical thing.
> Your post indicates that I can join a table that is created in the
> parenthesis. I like that idea and will try it. Thanks a lot.
> Richard
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> join
> example,
> dbo.Sales.
>

Replace LinkedServer with Service Broker

Hi,

We currently have an application that primarily sits on SQL server and needs to access some legacy data in a DB2 database. The solution we are using is to use a linked server between the two servers. The problem with this is that complex queries occasionally cause the driver to fetch a complete table and filter that table locally. I wondered whether service broker (which I have never used) could allow me to access the DB2 database using MQSeries and thus speed up the application as our ODBC calls have low priority on our mainframe.

Thanks and sorry if this sounds a stupid use of service broker.

Service Broker supports only its own protocol so it cannot communicate with DB2 or MQSeries. You must have Sql on both ends to use Service Broker.

Rick Negrin

Program Manager

Sql Server Service Brokersql

REPLACE function doesnt work with null-bytes

Dear Community,

We have a problem with null-Bytes in varchar-Columns, which are not
handled correctly in our application. Therefor we try to filter them
out using the Transact-SQL REPLACE function.

The Problem was, that the REPLACE-function didn't behave the way we
expected.

Following Example demonstrates the behavior:

declare @.txt varchar(512)
declare @.i int
set @.txt = 'hello ' + char(0) + 'world'
print @.txt
set @.i = 1

while @.i <= len(@.txt)
begin
print str(@.i) + substring(@.txt, @.i, 1)
set @.i = @.i + 1
end

print 'Length: ' + str(len(@.txt))
print 'trying to replace null-byte:'
print replace(@.txt, char(0), '*')
print 'replace Letter h'
print replace(@.txt, 'h', char(39))

-- end example

Output:

hello
1h
2e
3l
4l
5o
6
7
8w
9o
10r
11l
12d
Length: 12
trying to replace null-byte:
*
replace Letter h
'ello

The Null-Byte replace destroys the whole string. This behavior occurs
only on some of our
databases. The others work correctly.

Is it possible that it depends on some server setting?

Thanks
Enno(enno@.berlin.cortex-software.de) writes:
> We have a problem with null-Bytes in varchar-Columns, which are not
> handled correctly in our application. Therefor we try to filter them
> out using the Transact-SQL REPLACE function.
> The Problem was, that the REPLACE-function didn't behave the way we
> expected.
>...
> The Null-Byte replace destroys the whole string. This behavior occurs
> only on some of our
> databases. The others work correctly.
> Is it possible that it depends on some server setting?

I've seen this before, but I was a little puzzled when you said that
it worked on some databases.

Playing around, I was lucky to find that it works if you have an SQL
collation. So in your script, change the crucial line to:

print replace(@.txt COLLATE SQL_Latin1_General_CP1_CI_AS, char(0), '*')

In SQL 2005, the string does not get destroyed, but neither does the
NULL get replaced. Since this is inconsistent, I filed bug for this on
http://lab.msdn.microsoft.com/Produ...ackId=FDBK45444
You can vote on it, if you like.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Would the database setting of ANSI Nulls have an effect on this
behavior? In 2000? In 2005?

-- Mark D Powell --|||Mark D Powell (Mark.Powell@.eds.com) writes:
> Would the database setting of ANSI Nulls have an effect on this
> behavior? In 2000? In 2005?

I haven't tested, but I find that very unlikely.

The effect of the database setting ANSI_NULLS when it's ON is that it forces
the SET option ANSI_NULLS to be in the ON position, even though the process
has it OFF. If the database setting is OFF, it has no effect, but the
setting of the process determines.

And to make it even messier, when you run a stored procedure, what counts is
the setting when the procedure was saved.

As far as I know, ANSI_NULLS has no effect on the interpretation of NUL
characters. ANSI_NULLS determines how things like "IF @.x = NULL" works.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks for the answer.
We have cleaned our data using the sql-Collation.
I think it's not wise to have Data with NUL-bytes in a
varchar-Column, because other Transact-SQL-functions have also
problems.

For example the LIKE operator is also inconsistent.

SELECT 1
WHERE CHAR(0) LIKE '_'

Yields:
<nothing
SELECT 1
WHERE CHAR(65) LIKE '_'

Yields:
1

By using the collation you suggested, the LIKE Operator worked as
expected.

There must be some enviromental COLLATION setting, which is
database-specific, but i didn't find it in exported-scripts from the
enterprise manager.|||(enno@.berlin.cortex-software.de) writes:
> There must be some enviromental COLLATION setting, which is
> database-specific, but i didn't find it in exported-scripts from the
> enterprise manager.

In SQL Server there is first a *server collation* which defines the
collation for the system databases, and the default for new databases.

There is also a database collation, which defines the default collations
for new charcater columns in the database. If memory serves the database
collation also sets the collation for variables. However, temp-table
columns get their default from tempdb. If I remember correctly, table
variables gets their default from tempdb in SQL2000, but the database in SQL
2005.

When you create a table column, you can always explicitly set the collation.

And finally, when you have a character expression, you can always cast
to another collation.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Reordering SQL Tables

Hi,

I have inherited an inhouse employee self service application used to display various employee data.

I have one table that needs to have the data permanently reordered, as the front end web page reads the data from the table from the top to the bottom, but somehow this data has got mixed up and is not in the sequential order that it should be in.

I am new to SQL and haven't a clue how to change the web code, so was hoping I could run a script on that table to reorder the data in an ascending manner on one of the columns.

Is this possible? How can I do this?

Thanks

Chris

Hi Chris,

When you say "not in the sequential order" do you mean there is a column that has incrementing numeric values such as 1,2,3...100000 or that you expect results to be ordered by something like customer name etc?

You can only "order" a table via a clustered index, but this has no impact upon client query result formats (in fact it did pre 2000 if no order by was specified, but that's another story). You will need to specify an ORDER BY in the client, or in a view (via the TOP clause) or stored procedure. You cannot rely upon sql server to return ordered results unless you have explicity stated what and how you wish the ordering to ocurr.

Cheers,

Rob

Rendering to Excel - Crash - Server Application Unavailable - Out of RAM

I have a very simple report which generates a large amount or rows -
63,000. When I run the report all goes to plan and the data it
brought back and I can page through its 1336 returned pages. But I
need to be able to export all of this data ro excel, but when I try it
works away for a bit before eventually "crashing" and returning -
"
Server Application Unavailable
The web application you are attempting to access on this web server is
currently unavailable. Please hit the "Refresh" button in your web
browser to retry your request.
Administrator Note: An error message detailing the cause of this
specific request failure can be found in the application event log of
the web server. Please review this log entry to discover what caused
this error to occur.
"
In the loogs it says -
aspnet_wp.exe (PID: 2352) was recycled because memory consumption
exceeded the 306 MB (60 percent of available RAM).
yet I can go to Excel and go Data - Import External Data - New
Database Query and run the sql from there and export it to excel and
it works fine, and pretty quickly.
Is there anyway around this' I need to find someway to be able to
render my report from my reporting application in Excel for clients...
ThanksUnfortunately Reporting Services was designed to handle very big reports
since it renders 'in memory'. As you've seen from your error the server is
running out of memory. You could try to redesign the report into smaller
reports or try adding more memory on the server.
--
Adrian M.
MCP
"Gearoid" <gearoid_healy@.yahoo.com> wrote in message
news:3d6ebe80.0503300120.1b92ac6f@.posting.google.com...
>I have a very simple report which generates a large amount or rows -
> 63,000. When I run the report all goes to plan and the data it
> brought back and I can page through its 1336 returned pages. But I
> need to be able to export all of this data ro excel, but when I try it
> works away for a bit before eventually "crashing" and returning -
> "
> Server Application Unavailable
> The web application you are attempting to access on this web server is
> currently unavailable. Please hit the "Refresh" button in your web
> browser to retry your request.
> Administrator Note: An error message detailing the cause of this
> specific request failure can be found in the application event log of
> the web server. Please review this log entry to discover what caused
> this error to occur.
> "
> In the loogs it says -
> aspnet_wp.exe (PID: 2352) was recycled because memory consumption
> exceeded the 306 MB (60 percent of available RAM).
> yet I can go to Excel and go Data - Import External Data - New
> Database Query and run the sql from there and export it to excel and
> it works fine, and pretty quickly.
> Is there anyway around this' I need to find someway to be able to
> render my report from my reporting application in Excel for clients...
> Thanks|||> Unfortunately Reporting Services wasn't designed to handle very big
> reports since it renders 'in memory'.
--
Adrian M.
MCP
"Adrian M." <absolutelynospam@.nodomain_.com> wrote in message
news:uClX4mSNFHA.2468@.tk2msftngp13.phx.gbl...
> Unfortunately Reporting Services was designed to handle very big reports
> since it renders 'in memory'. As you've seen from your error the server
> is running out of memory. You could try to redesign the report into
> smaller reports or try adding more memory on the server.
> --
> Adrian M.
> MCP
> "Gearoid" <gearoid_healy@.yahoo.com> wrote in message
> news:3d6ebe80.0503300120.1b92ac6f@.posting.google.com...
>>I have a very simple report which generates a large amount or rows -
>> 63,000. When I run the report all goes to plan and the data it
>> brought back and I can page through its 1336 returned pages. But I
>> need to be able to export all of this data ro excel, but when I try it
>> works away for a bit before eventually "crashing" and returning -
>> "
>> Server Application Unavailable
>> The web application you are attempting to access on this web server is
>> currently unavailable. Please hit the "Refresh" button in your web
>> browser to retry your request.
>> Administrator Note: An error message detailing the cause of this
>> specific request failure can be found in the application event log of
>> the web server. Please review this log entry to discover what caused
>> this error to occur.
>> "
>> In the loogs it says -
>> aspnet_wp.exe (PID: 2352) was recycled because memory consumption
>> exceeded the 306 MB (60 percent of available RAM).
>> yet I can go to Excel and go Data - Import External Data - New
>> Database Query and run the sql from there and export it to excel and
>> it works fine, and pretty quickly.
>> Is there anyway around this' I need to find someway to be able to
>> render my report from my reporting application in Excel for clients...
>> Thanks
>|||Adrian M. wrote:
> Unfortunately Reporting Services was designed to handle very big reports
> since it renders 'in memory'. As you've seen from your error the server is
> running out of memory. You could try to redesign the report into smaller
> reports or try adding more memory on the server.
>
"try adding more memory on the server" - COOL! Worst recomendation ever
seen! How about to redesign RS? Why I don't have such problems in "old
fashion" developing (pre-dotnet)?
For example, I have some data. If I'll export this data as CSV then it
tools 10 MB. But then I'm truing to export same data as Excel - server
dies and tooks 400MB of RAM. My personal "reporter" tooks much less
resources! I this normal? Is this problem with my server?
I think - not. This is problem of R$ and M$.
How I can use RS for ENTERPRISE reporting if it cannot work with large
amounts of data? Only with tiny data (1000 records or less).
Also RS has huge memory management issues. How about to give away unused
memory back to system? Where is magical GC?|||I actually have some suggestions for you to try, but after your rant I have
decided not to share them with you.
--
Adrian M.
MCP
"Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> Adrian M. wrote:
>> Unfortunately Reporting Services was designed to handle very big reports
>> since it renders 'in memory'. As you've seen from your error the server
>> is running out of memory. You could try to redesign the report into
>> smaller reports or try adding more memory on the server.
> "try adding more memory on the server" - COOL! Worst recomendation ever
> seen! How about to redesign RS? Why I don't have such problems in "old
> fashion" developing (pre-dotnet)?
> For example, I have some data. If I'll export this data as CSV then it
> tools 10 MB. But then I'm truing to export same data as Excel - server
> dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> resources! I this normal? Is this problem with my server?
> I think - not. This is problem of R$ and M$.
> How I can use RS for ENTERPRISE reporting if it cannot work with large
> amounts of data? Only with tiny data (1000 records or less).
> Also RS has huge memory management issues. How about to give away unused
> memory back to system? Where is magical GC?|||Adrian,
I have encountered the same issues with Reporting Services when over 90,000
records are being returned to a report. I am using a dedicated server with
2gb of RAM. We are currently getting an extra 2gb to assist in the load, but
I was wondering what your other options were other than reducing the criteria
of the report.
Any help you may be able to provide would be greatly appreciated.
"Adrian M." wrote:
> I actually have some suggestions for you to try, but after your rant I have
> decided not to share them with you.
> --
> Adrian M.
> MCP
>
> "Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
> news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> > Adrian M. wrote:
> >> Unfortunately Reporting Services was designed to handle very big reports
> >> since it renders 'in memory'. As you've seen from your error the server
> >> is running out of memory. You could try to redesign the report into
> >> smaller reports or try adding more memory on the server.
> >>
> >
> > "try adding more memory on the server" - COOL! Worst recomendation ever
> > seen! How about to redesign RS? Why I don't have such problems in "old
> > fashion" developing (pre-dotnet)?
> >
> > For example, I have some data. If I'll export this data as CSV then it
> > tools 10 MB. But then I'm truing to export same data as Excel - server
> > dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> > resources! I this normal? Is this problem with my server?
> > I think - not. This is problem of R$ and M$.
> >
> > How I can use RS for ENTERPRISE reporting if it cannot work with large
> > amounts of data? Only with tiny data (1000 records or less).
> >
> > Also RS has huge memory management issues. How about to give away unused
> > memory back to system? Where is magical GC?
>
>|||See my response to your other post.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"William Foster" <WilliamFoster@.discussions.microsoft.com> wrote in message
news:63F7E982-CCE1-43E7-A077-DB8B4240D35C@.microsoft.com...
> Adrian,
> I have encountered the same issues with Reporting Services when over
90,000
> records are being returned to a report. I am using a dedicated server
with
> 2gb of RAM. We are currently getting an extra 2gb to assist in the load,
but
> I was wondering what your other options were other than reducing the
criteria
> of the report.
> Any help you may be able to provide would be greatly appreciated.
> "Adrian M." wrote:
> > I actually have some suggestions for you to try, but after your rant I
have
> > decided not to share them with you.
> >
> > --
> > Adrian M.
> > MCP
> >
> >
> > "Alexey Pavlov" <alexey_pavlov@.navigator.lv> wrote in message
> > news:umsEHynNFHA.3728@.TK2MSFTNGP10.phx.gbl...
> > > Adrian M. wrote:
> > >> Unfortunately Reporting Services was designed to handle very big
reports
> > >> since it renders 'in memory'. As you've seen from your error the
server
> > >> is running out of memory. You could try to redesign the report into
> > >> smaller reports or try adding more memory on the server.
> > >>
> > >
> > > "try adding more memory on the server" - COOL! Worst recomendation
ever
> > > seen! How about to redesign RS? Why I don't have such problems in "old
> > > fashion" developing (pre-dotnet)?
> > >
> > > For example, I have some data. If I'll export this data as CSV then it
> > > tools 10 MB. But then I'm truing to export same data as Excel - server
> > > dies and tooks 400MB of RAM. My personal "reporter" tooks much less
> > > resources! I this normal? Is this problem with my server?
> > > I think - not. This is problem of R$ and M$.
> > >
> > > How I can use RS for ENTERPRISE reporting if it cannot work with large
> > > amounts of data? Only with tiny data (1000 records or less).
> > >
> > > Also RS has huge memory management issues. How about to give away
unused
> > > memory back to system? Where is magical GC?
> >
> >
> >

Rendering reports using SOAP API vsURL Addressing

Hello,
I have been developing a reporting services application and have had to rely
on rendering using the web service API.
Before I go any further let me explain why we are going this way instead
of using URL addressing.
The client has a couple of reports where the selection criteria can get quite
long and in the past has exceeded the number of characters allowed in the
querystring. This will obvious cause the eport gen to fail.
Ok now that the background is over, here are some things I hope to find out.
First, is there anyway to use the URL addressing via POSTing the variables
to the report server or do all params have to be in the querystring?
Next, since I do not think the POSTing will work, is there any way with SP2
or a hot fix that there is a way to get the total number of pages in a report
that is rendered to HTML? I have found two hacks out there so far. First
the suggestion of rendering the entire report via the API then counting the
number of <HR> tags to ge tthe total number of pages. The second method,
found here: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_7vqa.asp,
suggests to render the first page of the report as an image then get the
length + 1 of the streamId's parameter.
The first method, may work but I cant imagine it would be good on reports
where there is 50 or 60 pages. The second method does not seem to work for
us. When we fire the code against a report that has two pages it gives us
back a streamId.Length of 5.
Finally, I can not imagine that I am the only one that has had the issue
of not being able to call URL address to get my report due to param length.
Has anyone else encountered this and if so how did they get around it.
Thanks for any guidance,
RichPOST should work.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Rich" <rich@.online.nospam> wrote in message
news:923159632507411681250000@.msnews.microsoft.com...
> Hello,
> I have been developing a reporting services application and have had to
> rely on rendering using the web service API.
> Before I go any further let me explain why we are going this way instead
> of using URL addressing.
> The client has a couple of reports where the selection criteria can get
> quite long and in the past has exceeded the number of characters allowed
> in the querystring. This will obvious cause the eport gen to fail.
> Ok now that the background is over, here are some things I hope to find
> out.
> First, is there anyway to use the URL addressing via POSTing the variables
> to the report server or do all params have to be in the querystring?
> Next, since I do not think the POSTing will work, is there any way with
> SP2 or a hot fix that there is a way to get the total number of pages in a
> report that is rendered to HTML? I have found two hacks out there so far.
> First the suggestion of rendering the entire report via the API then
> counting the number of <HR> tags to ge tthe total number of pages. The
> second method, found here:
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_prog_intro_7vqa.asp,
> suggests to render the first page of the report as an image then get the
> length + 1 of the streamId's parameter.
> The first method, may work but I cant imagine it would be good on reports
> where there is 50 or 60 pages. The second method does not seem to work
> for us. When we fire the code against a report that has two pages it
> gives us back a streamId.Length of 5.
> Finally, I can not imagine that I am the only one that has had the issue
> of not being able to call URL address to get my report due to param
> length. Has anyone else encountered this and if so how did they get around
> it.
> Thanks for any guidance,
> Rich
>
>|||I wrote a dumb little function to get the page count... not the most
efficient, but does the job: if you find out a better way of doing it
(supported fully by rs), let me know?
private int FindNumOfPages(string input)
{
string blah = input;
int counter = 1;
int end = blah.Length;
int start = 0;
int at = 0;
int count = 0;
while (start <= end && at > -1)
{
count = end - start;
at = blah.IndexOf("<hr/>", start, count);
if (at == -1)
break;
else
{
counter++;
start = at+1;
}
}
return counter;
}
--
Foober
"Rich" wrote:
> Thanks. So what about my other questions such as getting page count from
> RS API.
> Also if posting works, will the toolbar render correctly? In other words
> will the toolbar just post ot the next page or wil it try to put all the
> params on the querystring.
> Thanks for any additional information.
> Rich
> Hello Lev Semenets [MSFT],
> > POST should work.
> >
> > "Rich" <rich@.online.nospam> wrote in message
> > news:923159632507411681250000@.msnews.microsoft.com...
> >
> >> Hello,
> >>
> >> I have been developing a reporting services application and have had
> >> to
> >> rely on rendering using the web service API.
> >> Before I go any further let me explain why we are going this way
> >> instead
> >> of using URL addressing.
> >> The client has a couple of reports where the selection criteria can
> >> get
> >> quite long and in the past has exceeded the number of characters
> >> allowed
> >> in the querystring. This will obvious cause the eport gen to fail.
> >> Ok now that the background is over, here are some things I hope to
> >> find
> >> out.
> >> First, is there anyway to use the URL addressing via POSTing the
> >> variables to the report server or do all params have to be in the
> >> querystring?
> >>
> >> Next, since I do not think the POSTing will work, is there any way
> >> with
> >>
> >> SP2 or a hot fix that there is a way to get the total number of pages
> >> in a
> >>
> >> report that is rendered to HTML? I have found two hacks out there so
> >> far.
> >>
> >> First the suggestion of rendering the entire report via the API then
> >>
> >> counting the number of <HR> tags to ge tthe total number of pages.
> >> The
> >>
> >> second method, found here:
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rspr
> >> og/htm/rsp_prog_intro_7vqa.asp,
> >>
> >> suggests to render the first page of the report as an image then get
> >> the
> >>
> >> length + 1 of the streamId's parameter.
> >>
> >> The first method, may work but I cant imagine it would be good on
> >> reports
> >>
> >> where there is 50 or 60 pages. The second method does not seem to
> >> work
> >>
> >> for us. When we fire the code against a report that has two pages it
> >>
> >> gives us back a streamId.Length of 5.
> >>
> >> Finally, I can not imagine that I am the only one that has had the
> >> issue
> >>
> >> of not being able to call URL address to get my report due to param
> >>
> >> length. Has anyone else encountered this and if so how did they get
> >> around
> >>
> >> it.
> >>
> >> Thanks for any guidance,
> >>
> >> Rich
> >>
>
>|||That method sounds a lot better than my method :) Better than rendering the
ENTIRE report first, counting horizontal rules, and then rerendering page 1.
I'll implement that instead.
I saw a post *somewhere* that microsoft was aware of the issue and were
looking into a fix in a "future release". Maybe in SQL2005?
--
Foober
"Rich" wrote:
> Hello Foober,
> Thanks for posting this solution. Here is what I ended up coming up with
> regarding a solution. I added a parameter on the report called "ShowpageCount
> set it to a boolean type with a default of false. I then created a textbox
> whose value was set to "~*" & Globals!TotalPages & "*~". Then I set the
> property of the Hidden to "Not Parameters!ShowPages". So now when I want
> the pages on the report, I can call into this report via the api and set
> the showPages param. I then take the rendered stream and regex it for the
> ~*<number>*~ token somewhere (I dont care where) in the stream and pull that
> number. I then turn around and call the report again without setting that
> param. A hack...yes. Best thing I could thinki of so I did not have to
> render what could be a 60 page report.
> Let me know what you thik of this idea. I just wonder....did they at least
> fix this for us in the new version?
>
> > I wrote a dumb little function to get the page count... not the most
> > efficient, but does the job: if you find out a better way of doing it
> > (supported fully by rs), let me know?
> >
> > private int FindNumOfPages(string input)
> > {
> > string blah = input;
> > int counter = 1;
> > int end = blah.Length;
> > int start = 0;
> > int at = 0;
> > int count = 0;
> > while (start <= end && at > -1)
> > {
> > count = end - start;
> > at = blah.IndexOf("<hr/>", start, count);
> > if (at == -1)
> > break;
> > else
> > {
> > counter++;
> > start = at+1;
> > }
> > }
> > return counter;
> > }
> > "Rich" wrote:
> >
> >> Thanks. So what about my other questions such as getting page count
> >> from RS API.
> >>
> >> Also if posting works, will the toolbar render correctly? In other
> >> words will the toolbar just post ot the next page or wil it try to
> >> put all the params on the querystring.
> >>
> >> Thanks for any additional information.
> >>
> >> Rich
> >>
> >> Hello Lev Semenets [MSFT],
> >>
> >> POST should work.
> >>
> >> "Rich" <rich@.online.nospam> wrote in message
> >> news:923159632507411681250000@.msnews.microsoft.com...
> >> Hello,
> >>
> >> I have been developing a reporting services application and have
> >> had
> >> to
> >> rely on rendering using the web service API.
> >> Before I go any further let me explain why we are going this way
> >> instead
> >> of using URL addressing.
> >> The client has a couple of reports where the selection criteria can
> >> get
> >> quite long and in the past has exceeded the number of characters
> >> allowed
> >> in the querystring. This will obvious cause the eport gen to fail.
> >> Ok now that the background is over, here are some things I hope to
> >> find
> >> out.
> >> First, is there anyway to use the URL addressing via POSTing the
> >> variables to the report server or do all params have to be in the
> >> querystring?
> >> Next, since I do not think the POSTing will work, is there any way
> >> with
> >>
> >> SP2 or a hot fix that there is a way to get the total number of
> >> pages in a
> >>
> >> report that is rendered to HTML? I have found two hacks out there
> >> so far.
> >>
> >> First the suggestion of rendering the entire report via the API
> >> then
> >>
> >> counting the number of <HR> tags to ge tthe total number of pages.
> >> The
> >>
> >> second method, found here:
> >>
> >> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rs
> >> pr og/htm/rsp_prog_intro_7vqa.asp,
> >>
> >> suggests to render the first page of the report as an image then
> >> get the
> >>
> >> length + 1 of the streamId's parameter.
> >>
> >> The first method, may work but I cant imagine it would be good on
> >> reports
> >>
> >> where there is 50 or 60 pages. The second method does not seem to
> >> work
> >>
> >> for us. When we fire the code against a report that has two pages
> >> it
> >>
> >> gives us back a streamId.Length of 5.
> >>
> >> Finally, I can not imagine that I am the only one that has had the
> >> issue
> >>
> >> of not being able to call URL address to get my report due to param
> >>
> >> length. Has anyone else encountered this and if so how did they get
> >> around
> >>
> >> it.
> >>
> >> Thanks for any guidance,
> >>
> >> Rich
> >>
>
>

Rendering Reports to Web Application Interactive Features

I installed RS on a Windows 2003 Server: [Unicube] (inside our network, not
available to internet users).
(Windows Authentication now but can be change to Form authentication)
To let outside users to view the report through internet, I create a asp.net
page to render the report: [Product Line Sales]
You can see here:
http://207.14.208.161/UnicareReport/frmImage.aspx
There is a linked report [Employee Sales Summary]: when click the [Employee
Name], ie [Jang, Stephen] in the above report, it should go to the linked
report: [Employee Sales Summary].
The problem is:
(1) How to also render the linked report in asp.net pages to allow internet
users click the [Employee Name] in the above report to go to the linked
report?
(2) The real problem is: How to pass the parameter value in the report
[Product Line Sales] (ie, enp id=20, etc) in the asp.net page to go to the
linked report [Employee Sales Summary].
(it's easy to do in the report designer in VS 2003: jump to URL or Report:
but the linked report in the report server [Unicube] is not availablre to
internet users).I found the solution:
http://207.14.208.161/UnicareReport/frmImage.aspx
You can click the Employee Name such as [Caro, Fernando],[Ito, Shu] to view
the linked report.
My solution is:
(1) First render the first report [Product Line Sales] on the web use render
method:
(You can see the code on RS BOOK ONLINE: ReportingService.Render Method
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp )
For my example, the report rendered on The ASP.Net page frmImage.aspx
(2) Change the [jumpe to report] to [Jump to URL] in report [Product Line
Sales]
then deploy the report on Report Server:
For my example, the [jump to URL] Code is:
="http://207.14.208.161/UnicareReport/ESS.aspx?EmpID="&Fields!EmployeeID.Value
&""
(You need to change it to your web site domain/directory setting)
(3)Render the linked report [Product Line Sales] on the web use render
method
You have to render the 2 linked report [Employee Sales Summary] on webpage:
My ASP.net page is ESS.aspx:
Note:
To pass the parameter you can use request.querystring method, for example,
' Prepare report parameter.
Dim parameters(2) As ParameterValue
Dim EMPIDV As String
EMPIDV = Request.QueryString("EmpID")
parameters(0) = New ParameterValue
parameters(0).Name = "EmpID"
parameters(0).Value = EMPIDV
parameters(1) = New ParameterValue
parameters(1).Name = "ReportMonth"
parameters(1).Value = "6" ' June
parameters(2) = New ParameterValue
parameters(2).Name = "ReportYear"
parameters(2).Value = "2004"
"John Chen" <jchen@.uneriercarecorp.com> wrote in message
news:ewjry75tEHA.2072@.tk2msftngp13.phx.gbl...
>I installed RS on a Windows 2003 Server: [Unicube] (inside our network, not
>available to internet users).
> (Windows Authentication now but can be change to Form authentication)
> To let outside users to view the report through internet, I create a
> asp.net page to render the report: [Product Line Sales]
> You can see here:
> http://207.14.208.161/UnicareReport/frmImage.aspx
> There is a linked report [Employee Sales Summary]: when click the
> [Employee Name], ie [Jang, Stephen] in the above report, it should go to
> the linked report: [Employee Sales Summary].
> The problem is:
> (1) How to also render the linked report in asp.net pages to allow
> internet users click the [Employee Name] in the above report to go to the
> linked report?
> (2) The real problem is: How to pass the parameter value in the report
> [Product Line Sales] (ie, enp id=20, etc) in the asp.net page to go to the
> linked report [Employee Sales Summary].
> (it's easy to do in the report designer in VS 2003: jump to URL or Report:
> but the linked report in the report server [Unicube] is not availablre to
> internet users).
>
>|||Correct:
(3)Render the linked report [Employee Sales Summary] on another webpage:
My ASP.net page is ESS.aspx:
Note:
To pass the parameter you can use request.querystring method, for example,
' Prepare report parameter.
Dim parameters(2) As ParameterValue
Dim EMPIDV As String
EMPIDV = Request.QueryString("EmpID")
parameters(0) = New ParameterValue
parameters(0).Name = "EmpID"
parameters(0).Value = EMPIDV
parameters(1) = New ParameterValue
parameters(1).Name = "ReportMonth"
parameters(1).Value = "6" ' June
parameters(2) = New ParameterValue
parameters(2).Name = "ReportYear"
parameters(2).Value = "2004"
"John Chen" <jchen@.uneriercarecorp.com> wrote in message
news:eJWU8JEuEHA.348@.tk2msftngp13.phx.gbl...
>I found the solution:
> http://207.14.208.161/UnicareReport/frmImage.aspx
> You can click the Employee Name such as [Caro, Fernando],[Ito, Shu] to
> view the linked report.
> My solution is:
> (1) First render the first report [Product Line Sales] on the web use
> render method:
> (You can see the code on RS BOOK ONLINE: ReportingService.Render Method
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSPROG/htm/rsp_ref_soapapi_service_lz_6x0z.asp )
> For my example, the report rendered on The ASP.Net page frmImage.aspx
> (2) Change the [jumpe to report] to [Jump to URL] in report [Product Line
> Sales]
> then deploy the report on Report Server:
> For my example, the [jump to URL] Code is:
> ="http://207.14.208.161/UnicareReport/ESS.aspx?EmpID="&Fields!EmployeeID.Value
> &""
> (You need to change it to your web site domain/directory setting)
> (3)Render the linked report [Product Line Sales] on the web use render
> method
> You have to render the 2 linked report [Employee Sales Summary] on
> webpage:
> My ASP.net page is ESS.aspx:
> Note:
> To pass the parameter you can use request.querystring method, for example,
> ' Prepare report parameter.
> Dim parameters(2) As ParameterValue
> Dim EMPIDV As String
> EMPIDV = Request.QueryString("EmpID")
> parameters(0) = New ParameterValue
> parameters(0).Name = "EmpID"
> parameters(0).Value = EMPIDV
> parameters(1) = New ParameterValue
> parameters(1).Name = "ReportMonth"
> parameters(1).Value = "6" ' June
> parameters(2) = New ParameterValue
> parameters(2).Name = "ReportYear"
> parameters(2).Value = "2004"
>
> "John Chen" <jchen@.uneriercarecorp.com> wrote in message
> news:ewjry75tEHA.2072@.tk2msftngp13.phx.gbl...
>>I installed RS on a Windows 2003 Server: [Unicube] (inside our network,
>>not available to internet users).
>> (Windows Authentication now but can be change to Form authentication)
>> To let outside users to view the report through internet, I create a
>> asp.net page to render the report: [Product Line Sales]
>> You can see here:
>> http://207.14.208.161/UnicareReport/frmImage.aspx
>> There is a linked report [Employee Sales Summary]: when click the
>> [Employee Name], ie [Jang, Stephen] in the above report, it should go to
>> the linked report: [Employee Sales Summary].
>> The problem is:
>> (1) How to also render the linked report in asp.net pages to allow
>> internet users click the [Employee Name] in the above report to go to the
>> linked report?
>> (2) The real problem is: How to pass the parameter value in the report
>> [Product Line Sales] (ie, enp id=20, etc) in the asp.net page to go to
>> the linked report [Employee Sales Summary].
>> (it's easy to do in the report designer in VS 2003: jump to URL or
>> Report: but the linked report in the report server [Unicube] is not
>> availablre to internet users).
>>
>>
>

Rendering Reports to Web Application

I have Web application and I am trying to integrate Reports from SQL
Reporting Service in it.
I can render a report to a page from my application. The problem is the if a
report has links to other reports then those links send you strait to Report
Service site and not back to my application.
Is there a way out of this?
Thanks,
ShimonShimon,
I assume that your application:
1. Renders reports by SOAP on the server side of the application.
2. By report links to the Report Service site you mean reports are requested
by URL but you want them to redirect to your application.
If the above is correct, can you replace those links with your page URL?
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Shimon Sim" <estshim@.att.net> wrote in message
news:uuxWcSssEHA.904@.TK2MSFTNGP11.phx.gbl...
> I have Web application and I am trying to integrate Reports from SQL
> Reporting Service in it.
> I can render a report to a page from my application. The problem is the if
a
> report has links to other reports then those links send you strait to
Report
> Service site and not back to my application.
> Is there a way out of this?
> Thanks,
> Shimon
>|||I am using ASP.NET and I was trying to figure out if I can use RS for our
project. I have form based security access for my ASP.NET application.
Besides that we have role based application check that is custom
implemented. Window based security of RS is a big issue and I am trying to
get around it.
I saw the article that you sent me to about ASPNET account. Some how it just
doesn't work on my machine.
If pass account of local administrator with name and password it does work
on my machine.
The issue with link is following
I report doesn't have drill down, drill through or charts - anything that
requires links for working works fine. But if I have chart - picture is
missing, if I have drill done + then I am sent to regular site
MYSERVER/Reports... and of cause if user doesn't have proper windows account
he is denied. The same thing with drill through - links to other reports-
they send me to wrong page.
I don't know how to replace link.
This is the code that I am using:
private void Page_Load(object sender, System.EventArgs e)
{
string path=Request.Params["Path"];
string format=Request.Params["Format"];
// Create service
ReportingService rs=new WebReports.ReportingService.ReportingService();
System.Net.NetworkCredential nwc=new
System.Net.NetworkCredential("MyName","pass","server");
rs.Credentials=nwc;
ParameterValue[] parameters=new ParameterValue[0];
string encoding;
string mimeType;
ParameterValue[] parametersUsed;
Warning[] warnings;
string[] streamIds;
//render the report
byte[] data;
data=rs.Render(path,format, null,null,parameters,null,null,
out encoding, out mimeType, out parametersUsed, out warnings, out
streamIds);
string extension=this.GetExtension(mimeType); //this is just a private
function to get
//an extension for the mimeType
string reportName=path.Substring(path.LastIndexOf("/")+1);
string fileName=reportName+"."+extension;
//write report back to response object
Response.Clear();
Response.ContentType=mimeType;
//add the file name to the response if it is not a web browser format
if(mimeType!="text/html")
Response.AddHeader("Content-Disposition","attachment;filename=" + fileName);
Response.BinaryWrite(data);
}
(I got it from a book)
I don't see any way to control links.
Can you help?
Shimon.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:O3mwG1xsEHA.3972@.TK2MSFTNGP15.phx.gbl...
> Shimon,
> I assume that your application:
> 1. Renders reports by SOAP on the server side of the application.
> 2. By report links to the Report Service site you mean reports are
> requested
> by URL but you want them to redirect to your application.
> If the above is correct, can you replace those links with your page URL?
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:uuxWcSssEHA.904@.TK2MSFTNGP11.phx.gbl...
>> I have Web application and I am trying to integrate Reports from SQL
>> Reporting Service in it.
>> I can render a report to a page from my application. The problem is the
>> if
> a
>> report has links to other reports then those links send you strait to
> Report
>> Service site and not back to my application.
>> Is there a way out of this?
>> Thanks,
>> Shimon
>>
>|||This happens because the report interactive features require direct access
to the Report Server by URL and there is no way around it. Basically, you
have two approaches:
1. Render the reports on the server side of the application by calling the
Render SOAP API.
- Pros: better security since the report URL cannot be intercepted by
the end user.
- Cons: the reports CANNOT have interactive features since they rely on
URL addressability, you have to take extra steps to handle report images and
the presenting the report to the end user. If you take the SOAP approach,
you may find the enchanced version of the ReportViewer control ( can be
downloaded from the publisher website) useful.
2. Replace the Windows-based security with custom security.
- Pros: you can authenticate and authorize the users any way you want
including integrating RS with your web application.
- Cons: Development effort required.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Shimon Sim" <estshim@.att.net> wrote in message
news:eARiBe%23sEHA.3324@.TK2MSFTNGP15.phx.gbl...
> I am using ASP.NET and I was trying to figure out if I can use RS for our
> project. I have form based security access for my ASP.NET application.
> Besides that we have role based application check that is custom
> implemented. Window based security of RS is a big issue and I am trying to
> get around it.
> I saw the article that you sent me to about ASPNET account. Some how it
just
> doesn't work on my machine.
> If pass account of local administrator with name and password it does
work
> on my machine.
> The issue with link is following
> I report doesn't have drill down, drill through or charts - anything that
> requires links for working works fine. But if I have chart - picture is
> missing, if I have drill done + then I am sent to regular site
> MYSERVER/Reports... and of cause if user doesn't have proper windows
account
> he is denied. The same thing with drill through - links to other reports-
> they send me to wrong page.
> I don't know how to replace link.
> This is the code that I am using:
> private void Page_Load(object sender, System.EventArgs e)
> {
> string path=Request.Params["Path"];
> string format=Request.Params["Format"];
> // Create service
> ReportingService rs=new WebReports.ReportingService.ReportingService();
> System.Net.NetworkCredential nwc=new
> System.Net.NetworkCredential("MyName","pass","server");
> rs.Credentials=nwc;
> ParameterValue[] parameters=new ParameterValue[0];
> string encoding;
> string mimeType;
> ParameterValue[] parametersUsed;
> Warning[] warnings;
> string[] streamIds;
> //render the report
> byte[] data;
> data=rs.Render(path,format, null,null,parameters,null,null,
> out encoding, out mimeType, out parametersUsed, out warnings, out
> streamIds);
> string extension=this.GetExtension(mimeType); //this is just a private
> function to get
> //an extension for the mimeType
> string reportName=path.Substring(path.LastIndexOf("/")+1);
> string fileName=reportName+"."+extension;
> //write report back to response object
> Response.Clear();
> Response.ContentType=mimeType;
> //add the file name to the response if it is not a web browser format
> if(mimeType!="text/html")
> Response.AddHeader("Content-Disposition","attachment;filename=" +
fileName);
> Response.BinaryWrite(data);
> }
> (I got it from a book)
> I don't see any way to control links.
> Can you help?
> Shimon.
>
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:O3mwG1xsEHA.3972@.TK2MSFTNGP15.phx.gbl...
> > Shimon,
> >
> > I assume that your application:
> > 1. Renders reports by SOAP on the server side of the application.
> > 2. By report links to the Report Service site you mean reports are
> > requested
> > by URL but you want them to redirect to your application.
> >
> > If the above is correct, can you replace those links with your page URL?
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Shimon Sim" <estshim@.att.net> wrote in message
> > news:uuxWcSssEHA.904@.TK2MSFTNGP11.phx.gbl...
> >> I have Web application and I am trying to integrate Reports from SQL
> >> Reporting Service in it.
> >> I can render a report to a page from my application. The problem is the
> >> if
> > a
> >> report has links to other reports then those links send you strait to
> > Report
> >> Service site and not back to my application.
> >>
> >> Is there a way out of this?
> >>
> >> Thanks,
> >> Shimon
> >>
> >>
> >
> >
>|||Thank you for the answer. I will go through all the approaches before making
final decision.
Just how do I replace Windows security.Can I work with it as any ASP.NET
application - changing config file and publishing my pages? Can I use code
behind files?
Thanks,
Shimon.
"Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
news:uCm08WJtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> This happens because the report interactive features require direct access
> to the Report Server by URL and there is no way around it. Basically, you
> have two approaches:
> 1. Render the reports on the server side of the application by calling the
> Render SOAP API.
> - Pros: better security since the report URL cannot be intercepted by
> the end user.
> - Cons: the reports CANNOT have interactive features since they rely on
> URL addressability, you have to take extra steps to handle report images
> and
> the presenting the report to the end user. If you take the SOAP approach,
> you may find the enchanced version of the ReportViewer control ( can be
> downloaded from the publisher website) useful.
> 2. Replace the Windows-based security with custom security.
> - Pros: you can authenticate and authorize the users any way you want
> including integrating RS with your web application.
> - Cons: Development effort required.
> --
> Hope this helps.
> ---
> Teo Lachev, MVP [SQL Server], MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ---
> "Shimon Sim" <estshim@.att.net> wrote in message
> news:eARiBe%23sEHA.3324@.TK2MSFTNGP15.phx.gbl...
>> I am using ASP.NET and I was trying to figure out if I can use RS for our
>> project. I have form based security access for my ASP.NET application.
>> Besides that we have role based application check that is custom
>> implemented. Window based security of RS is a big issue and I am trying
>> to
>> get around it.
>> I saw the article that you sent me to about ASPNET account. Some how it
> just
>> doesn't work on my machine.
>> If pass account of local administrator with name and password it does
> work
>> on my machine.
>> The issue with link is following
>> I report doesn't have drill down, drill through or charts - anything that
>> requires links for working works fine. But if I have chart - picture is
>> missing, if I have drill done + then I am sent to regular site
>> MYSERVER/Reports... and of cause if user doesn't have proper windows
> account
>> he is denied. The same thing with drill through - links to other reports-
>> they send me to wrong page.
>> I don't know how to replace link.
>> This is the code that I am using:
>> private void Page_Load(object sender, System.EventArgs e)
>> {
>> string path=Request.Params["Path"];
>> string format=Request.Params["Format"];
>> // Create service
>> ReportingService rs=new WebReports.ReportingService.ReportingService();
>> System.Net.NetworkCredential nwc=new
>> System.Net.NetworkCredential("MyName","pass","server");
>> rs.Credentials=nwc;
>> ParameterValue[] parameters=new ParameterValue[0];
>> string encoding;
>> string mimeType;
>> ParameterValue[] parametersUsed;
>> Warning[] warnings;
>> string[] streamIds;
>> //render the report
>> byte[] data;
>> data=rs.Render(path,format, null,null,parameters,null,null,
>> out encoding, out mimeType, out parametersUsed, out warnings, out
>> streamIds);
>> string extension=this.GetExtension(mimeType); //this is just a private
>> function to get
>> //an extension for the mimeType
>> string reportName=path.Substring(path.LastIndexOf("/")+1);
>> string fileName=reportName+"."+extension;
>> //write report back to response object
>> Response.Clear();
>> Response.ContentType=mimeType;
>> //add the file name to the response if it is not a web browser format
>> if(mimeType!="text/html")
>> Response.AddHeader("Content-Disposition","attachment;filename=" +
> fileName);
>> Response.BinaryWrite(data);
>> }
>> (I got it from a book)
>> I don't see any way to control links.
>> Can you help?
>> Shimon.
>>
>> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
>> news:O3mwG1xsEHA.3972@.TK2MSFTNGP15.phx.gbl...
>> > Shimon,
>> >
>> > I assume that your application:
>> > 1. Renders reports by SOAP on the server side of the application.
>> > 2. By report links to the Report Service site you mean reports are
>> > requested
>> > by URL but you want them to redirect to your application.
>> >
>> > If the above is correct, can you replace those links with your page
>> > URL?
>> >
>> > --
>> > Hope this helps.
>> >
>> > ---
>> > Teo Lachev, MVP [SQL Server], MCSD, MCT
>> > Author: "Microsoft Reporting Services in Action"
>> > Publisher website: http://www.manning.com/lachev
>> > Buy it from Amazon.com: http://shrinkster.com/eq
>> > Home page and blog: http://www.prologika.com/
>> > ---
>> >
>> > "Shimon Sim" <estshim@.att.net> wrote in message
>> > news:uuxWcSssEHA.904@.TK2MSFTNGP11.phx.gbl...
>> >> I have Web application and I am trying to integrate Reports from SQL
>> >> Reporting Service in it.
>> >> I can render a report to a page from my application. The problem is
>> >> the
>> >> if
>> > a
>> >> report has links to other reports then those links send you strait to
>> > Report
>> >> Service site and not back to my application.
>> >>
>> >> Is there a way out of this?
>> >>
>> >> Thanks,
>> >> Shimon
>> >>
>> >>
>> >
>> >
>>
>|||Shimon,
A good place to start with RS custom security is the Forms Authentication
white paper by Microsoft
(http://msdn.microsoft.com/library/?url=/library/en-us/dnsql2k/html/ufairs.a
sp?frame=true#ufairs_topic3).
Custom security (a.k.a Forms Authentication) has been discussed in this
forum on many occasions. So, please be sure to check the forum to understand
the pros and cons before you decide whether you want to go for it.
--
Hope this helps.
---
Teo Lachev, MVP [SQL Server], MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
---
"Shimon Sim" <estshim@.att.net> wrote in message
news:uPURWOKtEHA.2560@.tk2msftngp13.phx.gbl...
> Thank you for the answer. I will go through all the approaches before
making
> final decision.
> Just how do I replace Windows security.Can I work with it as any ASP.NET
> application - changing config file and publishing my pages? Can I use code
> behind files?
> Thanks,
> Shimon.
> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> news:uCm08WJtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> > This happens because the report interactive features require direct
access
> > to the Report Server by URL and there is no way around it. Basically,
you
> > have two approaches:
> >
> > 1. Render the reports on the server side of the application by calling
the
> > Render SOAP API.
> > - Pros: better security since the report URL cannot be intercepted by
> > the end user.
> > - Cons: the reports CANNOT have interactive features since they rely
on
> > URL addressability, you have to take extra steps to handle report images
> > and
> > the presenting the report to the end user. If you take the SOAP
approach,
> > you may find the enchanced version of the ReportViewer control ( can be
> > downloaded from the publisher website) useful.
> >
> > 2. Replace the Windows-based security with custom security.
> > - Pros: you can authenticate and authorize the users any way you want
> > including integrating RS with your web application.
> > - Cons: Development effort required.
> >
> > --
> > Hope this helps.
> >
> > ---
> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ---
> >
> > "Shimon Sim" <estshim@.att.net> wrote in message
> > news:eARiBe%23sEHA.3324@.TK2MSFTNGP15.phx.gbl...
> >> I am using ASP.NET and I was trying to figure out if I can use RS for
our
> >> project. I have form based security access for my ASP.NET application.
> >> Besides that we have role based application check that is custom
> >> implemented. Window based security of RS is a big issue and I am trying
> >> to
> >> get around it.
> >>
> >> I saw the article that you sent me to about ASPNET account. Some how it
> > just
> >> doesn't work on my machine.
> >> If pass account of local administrator with name and password it does
> > work
> >> on my machine.
> >> The issue with link is following
> >> I report doesn't have drill down, drill through or charts - anything
that
> >> requires links for working works fine. But if I have chart - picture is
> >> missing, if I have drill done + then I am sent to regular site
> >> MYSERVER/Reports... and of cause if user doesn't have proper windows
> > account
> >> he is denied. The same thing with drill through - links to other
reports-
> >> they send me to wrong page.
> >>
> >> I don't know how to replace link.
> >>
> >> This is the code that I am using:
> >>
> >> private void Page_Load(object sender, System.EventArgs e)
> >>
> >> {
> >>
> >> string path=Request.Params["Path"];
> >>
> >> string format=Request.Params["Format"];
> >>
> >> // Create service
> >>
> >> ReportingService rs=new WebReports.ReportingService.ReportingService();
> >>
> >> System.Net.NetworkCredential nwc=new
> >> System.Net.NetworkCredential("MyName","pass","server");
> >>
> >> rs.Credentials=nwc;
> >>
> >> ParameterValue[] parameters=new ParameterValue[0];
> >>
> >> string encoding;
> >>
> >> string mimeType;
> >>
> >> ParameterValue[] parametersUsed;
> >>
> >> Warning[] warnings;
> >>
> >> string[] streamIds;
> >>
> >> //render the report
> >>
> >> byte[] data;
> >>
> >> data=rs.Render(path,format, null,null,parameters,null,null,
> >>
> >> out encoding, out mimeType, out parametersUsed, out warnings, out
> >> streamIds);
> >>
> >> string extension=this.GetExtension(mimeType); //this is just a private
> >> function to get
> >>
> >> //an extension for the mimeType
> >>
> >> string reportName=path.Substring(path.LastIndexOf("/")+1);
> >>
> >> string fileName=reportName+"."+extension;
> >>
> >> //write report back to response object
> >>
> >> Response.Clear();
> >>
> >> Response.ContentType=mimeType;
> >>
> >> //add the file name to the response if it is not a web browser format
> >>
> >> if(mimeType!="text/html")
> >>
> >> Response.AddHeader("Content-Disposition","attachment;filename=" +
> > fileName);
> >>
> >> Response.BinaryWrite(data);
> >>
> >> }
> >>
> >> (I got it from a book)
> >> I don't see any way to control links.
> >>
> >> Can you help?
> >> Shimon.
> >>
> >>
> >>
> >> "Teo Lachev [MVP]" <teo.lachev@.nospam.prologika.com> wrote in message
> >> news:O3mwG1xsEHA.3972@.TK2MSFTNGP15.phx.gbl...
> >> > Shimon,
> >> >
> >> > I assume that your application:
> >> > 1. Renders reports by SOAP on the server side of the application.
> >> > 2. By report links to the Report Service site you mean reports are
> >> > requested
> >> > by URL but you want them to redirect to your application.
> >> >
> >> > If the above is correct, can you replace those links with your page
> >> > URL?
> >> >
> >> > --
> >> > Hope this helps.
> >> >
> >> > ---
> >> > Teo Lachev, MVP [SQL Server], MCSD, MCT
> >> > Author: "Microsoft Reporting Services in Action"
> >> > Publisher website: http://www.manning.com/lachev
> >> > Buy it from Amazon.com: http://shrinkster.com/eq
> >> > Home page and blog: http://www.prologika.com/
> >> > ---
> >> >
> >> > "Shimon Sim" <estshim@.att.net> wrote in message
> >> > news:uuxWcSssEHA.904@.TK2MSFTNGP11.phx.gbl...
> >> >> I have Web application and I am trying to integrate Reports from SQL
> >> >> Reporting Service in it.
> >> >> I can render a report to a page from my application. The problem is
> >> >> the
> >> >> if
> >> > a
> >> >> report has links to other reports then those links send you strait
to
> >> > Report
> >> >> Service site and not back to my application.
> >> >>
> >> >> Is there a way out of this?
> >> >>
> >> >> Thanks,
> >> >> Shimon
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>