Replacing data in a string

Hi

I have a table with unique data in but it has been inputted, below is an example of the data

6331245073
638 535 0797
7023593578
ID Number 650 379 4428
OCA8 9F 32
ocb92w153
vh235704

I need to strip out the all letters and spaces so it will look like this:

6331245073
6385350797
7023593578
6503794428

ect....

Thanks

Rich

Hi Rich,

You need to create a scalar function to do this. Here is an example:
CREATE FUNCTION FormatString(@.input VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@.character CHAR(1),
@.newstring VARCHAR(100),
@.counter INT

SET @.counter = 0
SET @.newstring = ''

WHILE @.counter < LEN(@.input)
BEGIN
SET @.counter = @.counter + 1
SET @.character = SUBSTRING(@.input, @.counter, 1)

IF @.character >= '0' AND @.character <= '9'
SET @.newstring = @.newstring + @.character
END

RETURN @.newstring
END

You can call the function as follow (note the dbo. prefix):

SELECT dbo.FormatString('ID Number 650 379 4428' )
SELECT dbo.FormatString(fieldname) FROM tablename

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thanks Geert did the job

Rich

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 syntax error

Hello

In my database table I have replaced <h3> Some Text </h3> with <h2> Some Text </h2> in the Description column as below:

UPDATE CAT_Products

SET Description = replace (Description, '</h3>', '</h2>')
WHERE Description <> '</h2>'

UPDATE CAT_Products
SET Description = replace (Description, '<h3>', '<h2>')
WHERE Description <> '<h2>'

However, when I try the same replace syntax code with the DescriptionHTML column in the same table, it does not work, e.g.

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

What do I need to adjust?

Thanks

can you explain what you mean by not working? also pls provide some sample data you have that you are expecting the REPLACE to happen on.

|||

Hello ndinaker

When I put in this code:

UPDATE CAT_Products
SET DescriptionHTML = replace (DescriptionHTML, ' <h3> ', '<h2>')
WHERE Description <> '<h2>'

I dose not execute and I get this error message instead:

[Error] Script lines: 1-5 --------
Argument data type ntext is invalid for argument 1 of replace function.

[Executed: 12/04/07 19:17:21 BST ] [Execution: 0/ms]

As in the text Description column, I am trying to update the <h3> and </h3> to <h2> and </h2> for example: <h3> Some Text </h3> to be replaced by <h2> Some Text </h2>

However, as it is written in html format in the DescriptionHTML database column it looks like:

<h3> Some Text </h 3> to be replaced by <h 2> Some Text </h2>

Thanks

|||

I think the pattern you are matching is only for <h3> and not </h3>. So you probably need to a double replace.

declare

@.str varchar(100)

set

@.str='<h3> Some Text </h3>'

--final value = <h 2> Some Text </h2>

select

@.str,replace(@.str,'<h3>','<h2>'),replace(replace(@.str,'<h3>','<h2>'),'</h3>','</h2>')

|||

Hi ndinakar

Thanks for the code, but I needed to change:

@.str varchar(100)

To

@.str ntext

H

owever this change cause the code to fail:

UPDATE CAT_Products

@.str ntext
set

@.str = '<h3> * </h3>'
--final value = <h 2> * </h2>

select

@.str, replace(@.str,'<h3>', '<h2>'), replace( replace(@.str,'<h3>', '<h2>'), '</h3>', '</h2>')

I got this error message:

[Error] Script lines: 1-13 --------
Line 4: Incorrect syntax near'@.str'.

More exceptions ... Must declare the variable'@.str'.

[Executed: 12/04/07 21:49:22 BST ] [Execution: 0/ms]

|||

Try if this works:

UPDATE

CAT_Products

set

DescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>')

WHERE

Description<>'<h2>'

|||

Before you try the update try a SELECT first to see what records are getting affected and how.

SELECT

NewDescriptionHTML=replace(replace(DescriptionHTML,'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

As suggested, I tried this first:

SELECT

NewDescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>'),
DescriptionHTML

FROM

CAT_Products
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:40:42 BST ] [Execution: 0/ms]

Then I tried this:

UPDATE

CAT_Products
set

DescriptionHTML = replace( replace(DescriptionHTML,'<h3>', '<h2>'), '</h3>', '</h2>')
WHERE

DescriptionHTML <> '<h2>'

But I got this error message:

[Error] Script lines: 1-11 --------
Argument data type ntext is invalid for argument 1 of replace function.

More exceptions ... The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 12/04/07 23:43:54 BST ] [Execution: 0/ms]

It looks like I will have to update this column manually, I had best roll up my sleeves.

Thanks

|||Ahhhh...its the NTEXT. Its going to be a pain to retrieve those values/update them through the query analyzer. Perhaps you can write a little tool..a VB tool with a form...that retrieves the values into a textbox and a button to update it..that will be easier..|||

Try this:

SELECT

NewDescriptionHTML

=replace(replace(convert(varchar(4000),DescriptionHTML),'<h3>','<h2>'),'</h3>','</h2>'),

DescriptionHTML

FROM

CAT_Products

WHERE

Description<>'<h2>'

|||

Hi ndinakar

Thanks again for your help and support

Yes you are right the Ntext is a pain, cos when I tried your latest code suggestion I got this error:

[Error] Script lines: 1-15 --------
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

[Executed: 13/04/07 09:17:43 BST ] [Execution: 0/ms]

It certainly dose not like the NTEXT.

A cup of coffee and rolling up of the sleeves coming up.

Thanks

sql

Replace SA account name

I want to change the sa account and rights associated with it, while putting
in a new user that hides below the radar.
Anyone know the PHB real name?
This was a request out of a brain storming session or was a lunchtime BS
session? Either way I'm looking for a comic name. Second on my list is
Bill Watterson. So any other ideas?
TIAYou cannot modify the rights of the sa account. However, in SQL Server
2005, you can rename it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
"_Stephen" <srussell@.electracash.com> wrote in message
news:Oev4Ox1rGHA.3748@.TK2MSFTNGP04.phx.gbl...
I want to change the sa account and rights associated with it, while putting
in a new user that hides below the radar.
Anyone know the PHB real name?
This was a request out of a brain storming session or was a lunchtime BS
session? Either way I'm looking for a comic name. Second on my list is
Bill Watterson. So any other ideas?
TIAsql

Replace Multiple LIKEs

I have a query below that performs horribly:

@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)

SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)

The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.

What can I do to get this to perform reasonably? I cannot use full-text
search.
Any help is appreciated.
lqlaurenquantrell wrote:

Quote:

Originally Posted by

I have a query below that performs horribly:
>
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
>
>
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
>
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
>
What can I do to get this to perform reasonably? I cannot use full-text
search.

Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of

create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)

insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')

do this:

create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)

create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)

insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')

insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')|||Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq

Ed Murphy wrote:

Quote:

Originally Posted by

laurenquantrell wrote:
>

Quote:

Originally Posted by

I have a query below that performs horribly:

@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)

SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)

The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.

What can I do to get this to perform reasonably? I cannot use full-text
search.

>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')

|||I need a way to improve performace of the query with the existing table

Quote:

Originally Posted by

structure.

There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.

Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq
>
>
Ed Murphy wrote:

Quote:

Originally Posted by

>laurenquantrell wrote:
>>

Quote:

Originally Posted by

I have a query below that performs horribly:
>
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
>
>
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
>
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
>
What can I do to get this to perform reasonably? I cannot use full-text
search.

>>
>Normalize tblStuff by removing column Keyword and replacing it with a
>second table tblStuffKeyword. For instance, instead of
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50),
> Keyword text
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (1, 'A', 'B', '123456,321654,987987,345987')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (2, 'C', 'D', '')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (3, 'E', 'F', '123456,987987')
>>
>do this:
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50)
>)
>>
>create table tblStuffKeyword (
> StuffKey int,
> Keyword varchar(50)
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (1, 'A', 'B')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (2, 'C', 'D')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (3, 'E', 'F')
>>
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '321654')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '987987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '345987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '987987')

>

|||Dan,
Thanks.

Dan Guzman wrote:

Quote:

Originally Posted by

Quote:

Originally Posted by

I need a way to improve performace of the query with the existing table
structure.

>
There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.
>
Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...

Quote:

Originally Posted by

Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq

Ed Murphy wrote:

Quote:

Originally Posted by

laurenquantrell wrote:
>
I have a query below that performs horribly:

@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)

SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)

The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.

What can I do to get this to perform reasonably? I cannot use full-text
search.
>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')

Re-phrased w more details (SQL is giving different row counts)

Hi,

...giving a very 'summarized' scenario of the problem I have trying to
solve all day (make it 2 days now).

Below are the relevant DDLs... I am not listing the DDLs of my other tables:

CREATE TABLE [SalesFACT] (
[varchar] (10),
[TransDate] [varchar] (10),
[SaleAmt] [float],
[CustCode] [varchar] (10)
. . .
)

I populate the above table via a DTS and have checked and have verified that correct data is coming in... I also have a product master table; for business reasons we can have the same product created with different ProductCodes though the rest of the Product details are EXACTLY the same. We have covered this using a field named 'UniqueProdCode'.

CREATE TABLE ProdMaster(
[ProdCode] [varchar] (10),
[ProdName] [varchar] (35),

[UniqueProdCode] [varchar] (10),

... many other product fields e.g. unit price, category etc...
...
)

First a small Request:
Please note that I have NOT defined links between my tables (in the diagram editor) nor have I defined Primary keys (or any constraint) for any of the tables. When you kindly reply, please suggest I should define primary keys for the tables and also link them in the diagram editor.

[u]THE PROBLEM:
When I do a count(*) query on the table 'SalesFACT', I get the correct number of records.

If I create a view, add table 'SalesFACT' and table ProdMaster, link the
UniqueProdCode field of table 'SalesFACT' with the UniqueProdCode field of ProdMaster (so that I can also get the name, category, etc. for the products in the SalesFACT), and run a count(*) query I get a much higher and incorrect number of rows. The SQL for the view is:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.UniqueProdCode

Kindly note that I have checked and the contents of the table SalesFACT' UniqueProdCode field DOES contain the correct data i.e. it contains the UniqueProdCode and NOT the ProdCode.

But if i link the "wrong fields", I get the correct count count :confused: i.e. I create a very similar view (as mentioned above) but instead link the UniqueProdCode of table SalesFACT with the ProdCode field (not the UniqueProdCode field) of ProdMaster
table I get the correct count. This is really driving me nuts and I just can't understand what's going on. For your convenience here is the SQL for the 2nd view:

SELECT dbo.SalesFACT.TransDate, dbo.SalesFACT.UniqueProdCode,
dbo.SalesFACT.SaleAmt
FROM dbo.SalesFACT INNER JOIN dbo.ProdMaster ON dbo.SalesFACT.UniqueProdCode = dbo.ProdMaster.ProdCode

Please guide... I have run out of all the things that I could check and thus this SOS and F1

Billions of thansk in advance.in prodMaster you heve not unique UniqueProdCode

try this

select UniqueProdCode from prodMaster
group by UniqueProdCode
having count(*)>1

Repetitive SQL Tasks

Thanks in advance.

I am new to sql script area.

Below is my ugly program.

Help, please)

There should be a better code to handle these repetitive tasks.

/* DECLARE Part Omitted */

SET @.Job_Code_A = '(Job_Type = ''A'')';

SET @.Job_Code_B = '(Job_Type = ''B'')';

.... And so on

SET @.SQL1 = 'Select Count(*) FROM tblWorkorders WHERE' + @.Job_Code_A;

SET @.SQL2 = 'Select Count(*) FROM tblWorkorders WHERE' + @.Job_Code_B;

..... And so on

EXEC (@.SQL1);

EXEC (@.SQL2);

..... And so on

Why do you need dynamic SQL? You can do:

select o.Job_Type, count(*)

from tblWorkOrders as o

where o.Job_Type in ('A', 'B')

group by o.Job_Type;

|||

Thank you for reply.

I need to use three-level depth loops from outer conditions.

repeating Group Values

Hi,
I am having a problem with grouping in reporting services. I have data like
below
Col1 Col2 Col3
A B 1
A B 3
K L 1
K L 1
I am creating group on Col1 and Col2.
I nreport I am select 3 columns
Col1 Col2 Sum(Col3)
My results look like below
Col1 Col2 Col3
A B 3
A B 3
K L 2
K L 2
I am not sure why I am getting duplicate data. I want to sum col3 by col1 an
col2. I only want to get one result row per group like in SQL group by result
set.
Please help.
Thanks,
--
SRaniPWhich band are the duplicate rows being displayed in? You will most likely
want to move the display of tCol1, Col2 and SUM(COl3) the 2nd group's footer
band.
"SRaniP" <SRaniP@.discussions.microsoft.com> wrote in message
news:3551D311-578B-4336-B272-347F85FF2100@.microsoft.com...
> Hi,
> I am having a problem with grouping in reporting services. I have data
> like
> below
> Col1 Col2 Col3
> A B 1
> A B 3
> K L 1
> K L 1
> I am creating group on Col1 and Col2.
> I nreport I am select 3 columns
> Col1 Col2 Sum(Col3)
> My results look like below
> Col1 Col2 Col3
> A B 3
> A B 3
> K L 2
> K L 2
> I am not sure why I am getting duplicate data. I want to sum col3 by col1
> an
> col2. I only want to get one result row per group like in SQL group by
> result
> set.
> Please help.
> Thanks,
> --
> SRaniP

repeating free form list item fields for each group

Hi, I have a nested report where i have some dept name and address fields placed in the free form list, below the address fields i have the related employee info in a table. i have grouped on the dept id for both the list and table. for a particular dept , when the related employee table information goes on to the next page i want to repeat the dept name and address fields on each page for the group.i cannot place the fields in the group header of the table, since there are a lot of fields at different positions .so i have kept in free form in the list region.It's basically the group header information, i wanted repeated on each page, but i have placed the fields above the table, in free form.Please help. Really apreciate it . Thanks.

You can nest rectangles and text boxes in group and table header cells. This means it is possible to create a free form layout in the table or group header.

For example, you can add a rectangle to the group header cell in your table and then add multiple text boxes or images to that rectangle at any position you desire.

|||Thanks a lot.

Repeated Rows in Matrix

Hi,

I am facing a problem with Matrix control. My requirement is similar to the below problem.

The report should display Manager, Leaders under him, Members under leaders. The data is the working hours of members grouped by Month.

Manager Leader Member Jan Feb Mar Apr .....

M1 L1 E1 170 160 150 180

E2 159 161 130 185

E3 150 180 159 161

L2 E5 159 161 170 160

E6 159 161 130 185

E1 160 150 150 180

M2 L3 E5 150 180 159 161

L1 E1 160 150 150 180

I took Manager, Leader and Member Name columns as Row groups and Month of Working day as Column group. Working hours sum in Data Section. Every thing is working fine but some rows for same Manager are dividing into to parts and displaying as entries for seperate managers as below.

Manager Leader Member Jan Feb Mar Apr .....

M1 L1 E1 140 150 100 120

E2 119 149 50 120

E3 150 180 159 161

L2 E5 159 161 170 160

E6 159 161 130 185

E1 160 150 150 190

M1 L1 E1 30 10 50 60

E2 40 11 80 65

Is this any bug of Matrix control or is there any thing wrong in my design ?( I just dragged respective columns to row, column and data groups, nothing much). Are there any things i need to ensure ?

This could be caused by Manager fields not being exactly the same. There could be extra white space or a different case used for a character or two.

Try using the following as the grouping expression.

"=Fields!FieldName.Value.Trim().ToLower()"

Ian|||It is working. Thank you very much.

Repeat charts in every page

hi guys,

In reporting services i have a table and below which i have a chart. The table has lot of values so the report spans multiple pages. I have set page breaks such that the table displays only 10 records. After all the values have been displayed in the last page the chart is displayed. I want the chart to display in each and every page of the report. It is the same chart that i want to display. How do i do this?

plz can anyone give me ideas.

Thanks,

Sai Abhiram Bandhakavi

Include a new upper group in the table object and check the property for "Repeat on every page". Place the chart object in the new area. Done.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am not sure what you mean by upper group and placing the chart in that area. Can you be more clear.

Thanks,

Sai Abhiram Bandhakavi

|||

You should add one new row in table and set the property repeatonnewpage as true, And place the chart in this row.

then same chart will repeat on each page.

To do this follow the follwing steps:

1. Right click the first row of the table and insert a row above the selected row,

2. Press shift key and select all cells of new added row, by right click select merge cells.

3. place the chart in this row (by drag or by cut / copy).

4. increase the height of this row. Preview the report.

Repeat charts in every page

hi guys,

In reporting services i have a table and below which i have a chart. The table has lot of values so the report spans multiple pages. I have set page breaks such that the table displays only 10 records. After all the values have been displayed in the last page the chart is displayed. I want the chart to display in each and every page of the report. It is the same chart that i want to display. How do i do this?

plz can anyone give me ideas.

Thanks,

Sai Abhiram Bandhakavi

Include a new upper group in the table object and check the property for "Repeat on every page". Place the chart object in the new area. Done.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am not sure what you mean by upper group and placing the chart in that area. Can you be more clear.

Thanks,

Sai Abhiram Bandhakavi

|||

You should add one new row in table and set the property repeatonnewpage as true, And place the chart in this row.

then same chart will repeat on each page.

To do this follow the follwing steps:

1. Right click the first row of the table and insert a row above the selected row,

2. Press shift key and select all cells of new added row, by right click select merge cells.

3. place the chart in this row (by drag or by cut / copy).

4. increase the height of this row. Preview the report.

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
--
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.
Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>
|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>
|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

reorg database files

I want to reorganize the data files for optimum
performance. I have described below the existing
and the intended scenario that I wish to attain.
** existing database scenario **
database files
mydb.mdf (50gb) - primary data file
mydblog.ldf (1gb) - log file
** intended database scenario **
database files:
mydb1.mdf (10gb) - primary data file
mydb2.ndf (10gb) - secondary data file
mydb3.ndf (10gb) - secondary data file
mydb4.ndf (10gb) - secondary data file
mydb5.ndf (10gb) - secondary data file
mydblog.ldf (1gb) - log file
Are there tools that can me help do this?
Thank you in advance.Using T-SQL tools, you can do this:
Use ALTER DATABASE to create new files and place them in filegroups.
Now, use sp_spaceused to determine which tables and/or indexes you want to
move to the new filegroups.
You can use ALTER TABLE with ON FileGroupName to move data around. Notice
that the filegroup is actually home to an index, but a clustered index is
the table, of course. From the BOL:
ON {filegroup | DEFAULT}
Specifies the storage location of the index created for the constraint. If
filegroup is specified, the index is created in the named filegroup. If
DEFAULT is specified, the index is created in the default filegroup. If ON
is not specified, the index is created in the filegroup that contains the
table. If ON is specified when adding a clustered index for a PRIMARY KEY or
UNIQUE constraint, the entire table is moved to the specified filegroup when
the clustered index is created.
Once you have successfully moved tables by recreating the clustered indexes,
you can use DBCC SHRINKFILE to shrink the original large file down to the
appropriate size.
Russell Fields
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||None that I know of that would do much in that situation. If you want to
spread your data evenly across a filegroup with multiple files from one that
has a single file you pretty much have to export all the data. Then
truncate all the tables and reimport it back again. It's not that difficult
of a task but obviously you will need to take your users off line for some
period of time. In the past when I have done this I basically scripted out
the database and all the objects in such a way that I could recreate the
database schema with the new files and all the tables , sp's UDF's ect but
leaving off the triggers, RI and Indexes. Then after you BCP out all the
data you can drop the DB and recreate it without those to make it easier and
faster to load. Then Bulk Insert the data and add back the RI, Triggers
etc. Just make sure you have good and tested backups first.
Andrew J. Kelly SQL MVP
"fragb" <anonymous@.discussions.microsoft.com> wrote in message
news:0ca401c47b0c$fb6e7280$a401280a@.phx.gbl...
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>|||Hi,
The CREATE TABLE & ALTER TABLE statements only allow you to specify the
FILEGROUP on which you wish to create the table. Not the actual data
file within the Filegroup.
In your intended scenario, you do not draw a distinction between Data
Files, and FileGroups. A possible alternative is :
FileGroupPRIMARY mydb1.mdf (10gb)
FileGroup02 mydb2.ndf (10gb)
FileGroup03 mydb3.ndf (10gb)
FileGroup04 mydb4.ndf (10gb)
FileGroup05 mydb5.ndf (10gb)
mydblog.ldf (1gb)
I use Power Designer - Data Architect to model my databases. After I
make changes to the model (ie, change the FileGroup for a table), Data
Architect compares my Model against the database on the server, and
generates a "Modify" script, which I then run against the database.
It generates the typical script to move to a different Filegroup :
alter table dbo.tbl_Customer
drop constraint PK_Customer
go
alter table dbo.tbl_Customer
add constraint PK_Customer primary key clustered (CustomerId)
on "NEW_FILEGROUP"
go
thanks
Ian
fragb wrote:
> I want to reorganize the data files for optimum
> performance. I have described below the existing
> and the intended scenario that I wish to attain.
> ** existing database scenario **
> database files
> mydb.mdf (50gb) - primary data file
> mydblog.ldf (1gb) - log file
> ** intended database scenario **
> database files:
> mydb1.mdf (10gb) - primary data file
> mydb2.ndf (10gb) - secondary data file
> mydb3.ndf (10gb) - secondary data file
> mydb4.ndf (10gb) - secondary data file
> mydb5.ndf (10gb) - secondary data file
> mydblog.ldf (1gb) - log file
> Are there tools that can me help do this?
> Thank you in advance.
>

rendering to browser instead to file

Did anyone have rendered a report in excel format directly to browser
instead to a file? I tried this code below wich works fine for pdf
rendering:
result = rs.Render("/MyReports/SVREL00104", "Excel", Nothing, _
Nothing, params, Nothing, _
Nothing, encoding, mime, history, warnings, streamsId)
Page.Response.ClearContent()
Page.Response.ClearHeaders()
Page.Response.ContentType = mime
Page.Response.BinaryWrite(result)
Page.Response.Flush()
Page.Response.Close()
This code should load the excel into the web browser window, but I'm getting
this errors:
1. A message box saying: The Microsoft Excel can not access the file:
'http://localhost/MyReports/test.aspx'.
2. An input box asking for an alternate name for 'Titulos_de_Impressao'
(Print Title) because this is already an internal name.
Any ideas?
[]s
Renato
--
----
Renato Aloi
Analista Programador
J&W Informática Ltda.
+55 11 30406675If you can tolerate a dialog box asking if the user wants to open or save,
this will work:
Response.ClearContent();
Response.ClearHeaders();
Response.AppendHeader("Content-Disposition",
"attachment;filename=\"MyReport.xls\"");
Response.BinaryWrite(result);
Response.End();
It works for PDF as well. There may be other ways, but this worked for us
so we stuck with it.
"Renato Aloi" wrote:
> Did anyone have rendered a report in excel format directly to browser
> instead to a file? I tried this code below wich works fine for pdf
> rendering:
> result = rs.Render("/MyReports/SVREL00104", "Excel", Nothing, _
> Nothing, params, Nothing, _
> Nothing, encoding, mime, history, warnings, streamsId)
> Page.Response.ClearContent()
> Page.Response.ClearHeaders()
> Page.Response.ContentType = mime
> Page.Response.BinaryWrite(result)
> Page.Response.Flush()
> Page.Response.Close()
> This code should load the excel into the web browser window, but I'm getting
> this errors:
> 1. A message box saying: The Microsoft Excel can not access the file:
> 'http://localhost/MyReports/test.aspx'.
> 2. An input box asking for an alternate name for 'Titulos_de_Impressao'
> (Print Title) because this is already an internal name.
> Any ideas?
> []s
> Renato
> --
> ----
> Renato Aloi
> Analista Programador
> J&W Informática Ltda.
> +55 11 30406675
>
>|||Thanks for your idea, Debra. Though I was really trying to avoid these
dialog boxes. In fact I am 'translating' all my systems' reports from
Crystal Reports to Reporting Services. The problem is my clients are already
using my systems with this export option to excel, without needing to save
any file. If I barelly whisper that they will need to take more clicks to do
something that is already working well, I will be exterminated.
However, I think the approach i will adopt is save a file to disk then use
this code:
Page.Response.ClearContent()
Page.Response.ClearHeaders()
Page.Response.ContentType = mime
Page.Response.WriteFile(savedFile)
Page.Response.Flush()
Page.Response.Close()
This approach works fine without any dialog boxes. The problem is at my
clients using web farms with load balance. I am afraid of exceptions, like
file not found.
Any other ideas, let me know, please.
[]s
Renato
"debra doty" <debradoty@.discussions.microsoft.com> escreveu na mensagem
news:22D7671D-13F2-413F-84B8-022E222F3A4C@.microsoft.com...
> If you can tolerate a dialog box asking if the user wants to open or save,
> this will work:
> Response.ClearContent();
> Response.ClearHeaders();
> Response.AppendHeader("Content-Disposition",
> "attachment;filename=\"MyReport.xls\"");
> Response.BinaryWrite(result);
> Response.End();
> It works for PDF as well. There may be other ways, but this worked for us
> so we stuck with it.
>
> "Renato Aloi" wrote:
> > Did anyone have rendered a report in excel format directly to browser
> > instead to a file? I tried this code below wich works fine for pdf
> > rendering:
> >
> > result = rs.Render("/MyReports/SVREL00104", "Excel", Nothing, _
> > Nothing, params, Nothing, _
> > Nothing, encoding, mime, history, warnings, streamsId)
> >
> > Page.Response.ClearContent()
> > Page.Response.ClearHeaders()
> > Page.Response.ContentType = mime
> > Page.Response.BinaryWrite(result)
> > Page.Response.Flush()
> > Page.Response.Close()
> >
> > This code should load the excel into the web browser window, but I'm
getting
> > this errors:
> >
> > 1. A message box saying: The Microsoft Excel can not access the file:
> > 'http://localhost/MyReports/test.aspx'.
> >
> > 2. An input box asking for an alternate name for 'Titulos_de_Impressao'
> > (Print Title) because this is already an internal name.
> >
> > Any ideas?
> >
> > []s
> > Renato
> >
> > --
> > ----
> > Renato Aloi
> > Analista Programador
> > J&W Informática Ltda.
> > +55 11 30406675
> >
> >
> >