AdHoc Query faster than Stored Proc?

Yesterday i face a strange SQL Server 2000 behaviour :-(

I had a query that was wrapped inside a stored procedure, as usual.
Suddenly, the stored procedure execution time raised from 9 secs to 80.

So to understand where the problem was i cut and pasted the sp body's into a new query analyzer window an then executed it again. Speed back to 9 secs.
Tried stored procedure again, and speed again set to 80 secs.

Tried to recompile sp. Nothing. Tried to restart SQL Server. Nothing. Tried to DROP & RE-CREATE sp. Done! Speed again at 9 secs.

My collegue asked me "why?", but i had no words. :confused: Do you have any explanation?the stored proc was past its "best before" date

like, if you look in your fridge and the milk says april 2003, you have to throw it out and buy some more

;)|||Well, that was what i exactly did. Now i can drink milk safely, but i'd like to prevent this situation again if it's possibile.

It's always frustrating when things happen and I cannot understand why :(|||It's always frustrating when things happen and I cannot understand why :(boy, i know that feeling

switch from milk to bourbon -- keeps forever :)

trouble is, i always finish it, and have to go out for more...|||See Bart Duncan's notes on parameter sniffing here http://www.examnotes.net/article48335.html|||Thanks for the link. Though very interesting i was already aware of that problem, and in fact i tried to run the body of the stored procedure using parameters to closely simulate the behaviour of the stored procedure.

Since the sp was executed with WITH RECOMPILE i expected the same execution plan of the ad-hoc query (that is what normally happens), but this time SQL Server produced two different execution plans.

UPDATE:
mmm...i've read more deeply the info at the link i've told me and seems that the problem was really parameter sniffing. a BIG thanks! :)

AddParameter to SubReport ? <VbScript>

Hello,

Can i pass parameter to a subreport inside a report with addParameter ?
Like that:

Dim webSource0
Set webSource0 = CreateObject("WebReportSource.WebReportSource")
webSource0.ReportSource = webBroker
webSource0.URL = "http://localhost/product.rpt"
webSource0.PromptOnRefresh = True
webSource0.AddParameter "password0", "cria"
webSource0.AddParameter "user0", "sa"
webSource0.AddParameter "prompt0", "1"
webSource0.AddParameter "subreport.password0", "cria" ?
webSource0.AddParameter "subreport.user0", "sa" ?

when i try to view the report with crystal viewer, it prompts for the
database settings of the subreport !

i'm using cr8.5 with oledb connection and vbscriptsee thread http://www.dev-archive.com/forum/showthread.php?t=315611

I think you have the same problem, caused by not understanding that each subreport's query must establish its own permissions with the server.

Dave

Adding spaces inside text box values

I need to adding spaces inside the expression of a text box (in table header
or in page header).
For example: = "string 1" & " " & "string 2";
this expression returns only one space between string 1 and string 2.
Many thanksHi,
Just use =space(<no of spaces>) e.g space(10)
Amarnath
"Pasquale" wrote:
> I need to adding spaces inside the expression of a text box (in table header
> or in page header).
> For example: = "string 1" & " " & "string 2";
> this expression returns only one space between string 1 and string 2.
> Many thanks|||I have tried this suggest: it functions inside Visual Studio.NET environment
but then
his distribution I have seen only a single space!
How can I solve this issue?
Thanks
"Amarnath" wrote:
> Hi,
> Just use =space(<no of spaces>) e.g space(10)
> Amarnath
> "Pasquale" wrote:
> > I need to adding spaces inside the expression of a text box (in table header
> > or in page header).
> > For example: = "string 1" & " " & "string 2";
> > this expression returns only one space between string 1 and string 2.
> >
> > Many thanks|||Try adding the space inside the string that you need the space like
= "string 1 " & "string 2"; where the space is inserted aftter the 1.
"Pasquale" wrote:
> I need to adding spaces inside the expression of a text box (in table header
> or in page header).
> For example: = "string 1" & " " & "string 2";
> this expression returns only one space between string 1 and string 2.
> Many thanks|||I have used = string1 & " " & string2 and it has worked. But the room
used by the spaces is not the same as you see when you define it. Try to put
much more spaces between the strings and you will see the distance increase.
"Pasquale" wrote:
> I have tried this suggest: it functions inside Visual Studio.NET environment
> but then
> his distribution I have seen only a single space!
> How can I solve this issue?
> Thanks
>
> "Amarnath" wrote:
> > Hi,
> >
> > Just use =space(<no of spaces>) e.g space(10)
> >
> > Amarnath
> >
> > "Pasquale" wrote:
> >
> > > I need to adding spaces inside the expression of a text box (in table header
> > > or in page header).
> > > For example: = "string 1" & " " & "string 2";
> > > this expression returns only one space between string 1 and string 2.
> > >
> > > Many thanks|||I have posted this issue then executing some proofs to put some spaces in a
text box.
I have tried:
- = "string1 " & "string2";
- = "string1" & space(10) & "string2";
- = "string1" & " " & "string2";
- = "string1 " & " " & space(10) & "
string2".
These solutions function inside MS VisualStudio .NET (I see the results by
preview),
BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
browser).
Many thanks
"PSM" wrote:
> I have used = string1 & " " & string2 and it has worked. But the room
> used by the spaces is not the same as you see when you define it. Try to put
> much more spaces between the strings and you will see the distance increase.
> "Pasquale" wrote:
> > I have tried this suggest: it functions inside Visual Studio.NET environment
> > but then
> > his distribution I have seen only a single space!
> >
> > How can I solve this issue?
> >
> > Thanks
> >
> >
> > "Amarnath" wrote:
> >
> > > Hi,
> > >
> > > Just use =space(<no of spaces>) e.g space(10)
> > >
> > > Amarnath
> > >
> > > "Pasquale" wrote:
> > >
> > > > I need to adding spaces inside the expression of a text box (in table header
> > > > or in page header).
> > > > For example: = "string 1" & " " & "string 2";
> > > > this expression returns only one space between string 1 and string 2.
> > > >
> > > > Many thanks|||The only thing it occur to me is to use non-breaking spaces. You can use
ChrW(160) as nonbreaking space and the Internet browser won't change them.
"Pasquale" wrote:
> I have posted this issue then executing some proofs to put some spaces in a
> text box.
> I have tried:
> - = "string1 " & "string2";
> - = "string1" & space(10) & "string2";
> - = "string1" & " " & "string2";
> - = "string1 " & " " & space(10) & "
> string2".
> These solutions function inside MS VisualStudio .NET (I see the results by
> preview),
> BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
> browser).
> Many thanks
>
> "PSM" wrote:
> > I have used = string1 & " " & string2 and it has worked. But the room
> > used by the spaces is not the same as you see when you define it. Try to put
> > much more spaces between the strings and you will see the distance increase.
> >
> > "Pasquale" wrote:
> >
> > > I have tried this suggest: it functions inside Visual Studio.NET environment
> > > but then
> > > his distribution I have seen only a single space!
> > >
> > > How can I solve this issue?
> > >
> > > Thanks
> > >
> > >
> > > "Amarnath" wrote:
> > >
> > > > Hi,
> > > >
> > > > Just use =space(<no of spaces>) e.g space(10)
> > > >
> > > > Amarnath
> > > >
> > > > "Pasquale" wrote:
> > > >
> > > > > I need to adding spaces inside the expression of a text box (in table header
> > > > > or in page header).
> > > > > For example: = "string 1" & " " & "string 2";
> > > > > this expression returns only one space between string 1 and string 2.
> > > > >
> > > > > Many thanks|||Excellent!
This is the solution! Many thanks
"PSM" wrote:
> The only thing it occur to me is to use non-breaking spaces. You can use
> ChrW(160) as nonbreaking space and the Internet browser won't change them.
>
> "Pasquale" wrote:
> > I have posted this issue then executing some proofs to put some spaces in a
> > text box.
> >
> > I have tried:
> > - = "string1 " & "string2";
> > - = "string1" & space(10) & "string2";
> > - = "string1" & " " & "string2";
> > - = "string1 " & " " & space(10) & "
> > string2".
> >
> > These solutions function inside MS VisualStudio .NET (I see the results by
> > preview),
> > BUT NOT FUNCTION AFTER THEIR DISTRIBUTION (I see the results inside Internet
> > browser).
> >
> > Many thanks
> >
> >
> >
> > "PSM" wrote:
> >
> > > I have used = string1 & " " & string2 and it has worked. But the room
> > > used by the spaces is not the same as you see when you define it. Try to put
> > > much more spaces between the strings and you will see the distance increase.
> > >
> > > "Pasquale" wrote:
> > >
> > > > I have tried this suggest: it functions inside Visual Studio.NET environment
> > > > but then
> > > > his distribution I have seen only a single space!
> > > >
> > > > How can I solve this issue?
> > > >
> > > > Thanks
> > > >
> > > >
> > > > "Amarnath" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > Just use =space(<no of spaces>) e.g space(10)
> > > > >
> > > > > Amarnath
> > > > >
> > > > > "Pasquale" wrote:
> > > > >
> > > > > > I need to adding spaces inside the expression of a text box (in table header
> > > > > > or in page header).
> > > > > > For example: = "string 1" & " " & "string 2";
> > > > > > this expression returns only one space between string 1 and string 2.
> > > > > >
> > > > > > Many thanks|||Yeah, I just wasted 2 hours on trying to fix this, excellent :-)
In case you were wondering what is going on I can explain...
I have a SQL statement that returns a string of dates delimited by 8 spaces,
eg.
1-Mar-2006 2-Mar-2006 etc.
When in VS.Net IDE the preview window shows the spaces correctly but when
deployed to a website they are displayed as HTML and hence the multiple
spaces are ignored and displayed as a single space.
Try putting in and Reporting services sees the & and converts it to a html
&, so your source looks like , insert scream here.
CharW(160) gets past this and is rendered into HTML by RS as
Thanks again, I will be able to sleep tonight.
John
"Pasquale" wrote:
> Excellent!
> This is the solution! Many thanks
>
> "PSM" wrote:
> > The only thing it occur to me is to use non-breaking spaces. You can use
> > ChrW(160) as nonbreaking space and the Internet browser won't change them.

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULL
The only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
.... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:

> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>
|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure wh
it the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I wil
l need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach t
o
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:

> My question is...
> Is there any way to add a new column dinamically inside a store procedure
whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I w
ill need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
http://www.aspfaq.com/
(Reverse address to reply.)

Adding new column from inside a store procedure....

My question is...
Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
Using normal code it will be something like this:
ALTER TABLE TableName
ADD ColumnName DataType NULL
But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
ALTER TABLE TableName
ADD 'ColumnName' DataType NULLThe only way you are going to be able to do this is to use execute ( string
). The string can be any sql statement.
So in your stored proc you'd do something like.
declare @.command varchar(8000)
set @.command = 'ALTER TABLE ' + @.table_name + ' ADD ' + @.column_name + ' '
+ @.data_type + ' NULL '
execute ( @.command )
This assumes that @.table_name, @.column_name, @.data_type are coming in
through the stored proc. As you can see this approach allows you to do
customzie the statement to any level you want. I've used the same approach to
write tools to auto generate index and foreignkey creation statements.
Combine this with a loop, and you can scroll through a record set and issues
statements.
One thing to note is that the execute ( ) executes on what seems like a
seperate scope. That is if you delcare a variable in the proc, and then
reference it in the execute statement it will fail. The trick I've found to
get around this is to use temp tables. You can create a really basically
#table to hold your variables, and then grab them as need in the execute
statement.
The 8000 character limit to varchars is also kind of a b. The only way
around this I've found is to concat 8000 varchars together in the execute
statement like
delcare @.cmd1 varchar(8000)
declare @.cmd2 varchar(8000)
... set em up
execute ( @.cmd1 + @.cmd2 )
Using this approach you can issue really big statements. I've used it to
generate triggers that where larger then 8k.
Also execute can issue more then one statement. So you can have something
like
execute ( 'select junk = 1 select date = getdate()'
and you'll get to result sets.
"Andres Romero" wrote:
> My question is...
> Is there any way to add a new column dinamically inside a store procedure whit the column name as string?
> Using normal code it will be something like this:
> ALTER TABLE TableName
> ADD ColumnName DataType NULL
> But, in my case the ColumnName is unknow until the sp is executed, and I will need something like this;
> ALTER TABLE TableName
> ADD 'ColumnName' DataType NULL
>|||> seperate scope. That is if you delcare a variable in the proc, and then
> reference it in the execute statement it will fail. The trick I've found
to
> get around this is to use temp tables.
Or, concatenate? Dynamic SQL doesn't have to know that the value came from
a variable, e.g.
DECLARE @.sql VARCHAR(8000), @.colname VARCHAR(32), @.collength INT
SELECT @.colname = 'newCol1', @.collength = 32
SET @.sql = 'ALTER TABLE table ADD '+@.colname+'
VARCHAR('+RTRIM(@.collength)+')'
PRINT @.sql
--
http://www.aspfaq.com/
(Reverse address to reply.)

Adding member on a Role

Hi all,

I'm wondering if I can add a member on my role inside an SP.

something like this.

-- EXEC sp_addrolemember 'RoleName', 'UserName'

but i can't use the above statemnt inside a stored proc. I'm getting this error msg

Msg 15002, Level 16, State 1, Procedure sp_addrolemember, Line 19

The procedure 'sp_addrolemember' cannot be executed within a transaction.

Is there any other alternative way to add a member to a role inside an SP?

Thank you so much and have a nice day to all

My bad it is not inside a [stored proc] it is inside a [TRIGGER after INSERT]|||

You can commit the transaction inside the trigger before you execute sp_addrolemember, just put a COMMIT statement before the call to sp_addrolemember. This could have undesirable effects if there are other triggers, so be careful and test it thoroughly! You should probably make this trigger the last trigger with sp_settriggerorder, which will at least make sure that it never commits before other triggers fire.

The other possibly better option is to create a stored procedure that performs the insert and then calls sp_addrolemember, and make sure that you always use that sproc to insert into the table. Using sprocs exclusively for all data modifications is a good principle anyway.

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are ignored."

thx,
FrankHi!
I had the same problem, and i found a solution for the embedded data regions in EXCEL! Altough it is a limitation in RS :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp
There is a solution! Have a look at the riport item "List" in design mode! It has almost the same functionality like teh "Table". You have to manually add headers over the "List" and manually place the textboxes hierarchical inside the list if you want to group fields, but it has a big advantage! Inside the "List" every embedded data regions (just like matrix, or subreport) are displayed correctly in EXCEL!
IMPORTANT: if you place embedded data regions in a "List" you have to right click on the "List" and select properties. On the first tab of the property window click "edit details group" and you must specify grouping expressions here. For examle if there are "Parent" and "Child" rows inside a "List", a good grouping expression would be the Parent row ID.
I hope this helps:)
Rolf|||Oh! And to adding matrix headers, for columns, you have to create a rectangle instead of a table. And place textbox-es inside the rectangle. And finally drag and drop the rectangle inside a merged matrix columnheader cell.
Rolf|||

I am a newbie to the RS and need much more information then given here.

Can anybody please show me an example implementation of the same.

Tanveer

|||I have found the same problem with the "List" object in Reporting Services. Renders fine in HTML and PDF but Excel displays "Data Regions within table/matrix cells are ignored". Very frustrating limitation|||

Unfortunately there is no viable workaround for this particular limitation of the Excel renderer. Data regions cannot be nested in table or matrix cells. We are working to add this functinonality in the next version of Reporting Services.

Tanveer, please let us know specifically what questions you have and we'll do our best to answer.

-Chris

|||Do you have a timeline of when the next version of RS is going to be available?|||

This is exactly what brings touble down the road, One recommends RS over CR and then, when I implement a large matrix report which for very valid reasons is embedded within a table, I receive a user response that this stuff just simply doesn't work. I TRULY wish this stuff would just work as it is supposed to. I hate the fact that Microsoft's marketing machine is better and faster than it's technical arm which does absolutely nothing for our credibility!

Why include the option to export to Excel if it does not work?!

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are
ignored."
thx,
FrankI'm assuming you mean the top left cell of the matrix?
Add a rectangle outside the data region, insert a textbox for each
column heading formatting, sizing and positioning them accordingly.
Cut the rectangle and paste it into the matrix cell.
--
Regards
Chris
Frank Mestdagh wrote:
> Hi,
> how can I add headers to my matrixcolumns?
> I created a table inside the merged cell above, but this won't export
> to excel: i get the error "Data Regions within table/matrix cells are
> ignored."
> thx,
> Frank|||Thanks Chris!!! I am going to try that ... it seems hokey that we cant have
the OPTION to iniclude column headings for that area to me! I will try what
you suggested.
"Chris McGuigan" wrote:
> I'm assuming you mean the top left cell of the matrix?
> Add a rectangle outside the data region, insert a textbox for each
> column heading formatting, sizing and positioning them accordingly.
> Cut the rectangle and paste it into the matrix cell.
> --
> Regards
> Chris
>
> Frank Mestdagh wrote:
> > Hi,
> > how can I add headers to my matrixcolumns?
> > I created a table inside the merged cell above, but this won't export
> > to excel: i get the error "Data Regions within table/matrix cells are
> > ignored."
> >
> > thx,
> > Frank
>

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are ignored."

thx,
FrankHi!
I had the same problem, and i found a solution for the embedded data regions in EXCEL! Altough it is a limitation in RS :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp
There is a solution! Have a look at the riport item "List" in design mode! It has almost the same functionality like teh "Table". You have to manually add headers over the "List" and manually place the textboxes hierarchical inside the list if you want to group fields, but it has a big advantage! Inside the "List" every embedded data regions (just like matrix, or subreport) are displayed correctly in EXCEL!
IMPORTANT: if you place embedded data regions in a "List" you have to right click on the "List" and select properties. On the first tab of the property window click "edit details group" and you must specify grouping expressions here. For examle if there are "Parent" and "Child" rows inside a "List", a good grouping expression would be the Parent row ID.
I hope this helps:)
Rolf|||Oh! And to adding matrix headers, for columns, you have to create a rectangle instead of a table. And place textbox-es inside the rectangle. And finally drag and drop the rectangle inside a merged matrix columnheader cell.
Rolf|||

I am a newbie to the RS and need much more information then given here.

Can anybody please show me an example implementation of the same.

Tanveer

|||I have found the same problem with the "List" object in Reporting Services. Renders fine in HTML and PDF but Excel displays "Data Regions within table/matrix cells are ignored". Very frustrating limitation|||

Unfortunately there is no viable workaround for this particular limitation of the Excel renderer. Data regions cannot be nested in table or matrix cells. We are working to add this functinonality in the next version of Reporting Services.

Tanveer, please let us know specifically what questions you have and we'll do our best to answer.

-Chris

|||Do you have a timeline of when the next version of RS is going to be available?|||

This is exactly what brings touble down the road, One recommends RS over CR and then, when I implement a large matrix report which for very valid reasons is embedded within a table, I receive a user response that this stuff just simply doesn't work. I TRULY wish this stuff would just work as it is supposed to. I hate the fact that Microsoft's marketing machine is better and faster than it's technical arm which does absolutely nothing for our credibility!

Why include the option to export to Excel if it does not work?!

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are ignored."

thx,
FrankHi!
I had the same problem, and i found a solution for the embedded data regions in EXCEL! Altough it is a limitation in RS :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp
There is a solution! Have a look at the riport item "List" in design mode! It has almost the same functionality like teh "Table". You have to manually add headers over the "List" and manually place the textboxes hierarchical inside the list if you want to group fields, but it has a big advantage! Inside the "List" every embedded data regions (just like matrix, or subreport) are displayed correctly in EXCEL!
IMPORTANT: if you place embedded data regions in a "List" you have to right click on the "List" and select properties. On the first tab of the property window click "edit details group" and you must specify grouping expressions here. For examle if there are "Parent" and "Child" rows inside a "List", a good grouping expression would be the Parent row ID.
I hope this helps:)
Rolf|||Oh! And to adding matrix headers, for columns, you have to create a rectangle instead of a table. And place textbox-es inside the rectangle. And finally drag and drop the rectangle inside a merged matrix columnheader cell.
Rolf|||

I am a newbie to the RS and need much more information then given here.

Can anybody please show me an example implementation of the same.

Tanveer

|||I have found the same problem with the "List" object in Reporting Services. Renders fine in HTML and PDF but Excel displays "Data Regions within table/matrix cells are ignored". Very frustrating limitation|||

Unfortunately there is no viable workaround for this particular limitation of the Excel renderer. Data regions cannot be nested in table or matrix cells. We are working to add this functinonality in the next version of Reporting Services.

Tanveer, please let us know specifically what questions you have and we'll do our best to answer.

-Chris

|||Do you have a timeline of when the next version of RS is going to be available?|||

This is exactly what brings touble down the road, One recommends RS over CR and then, when I implement a large matrix report which for very valid reasons is embedded within a table, I receive a user response that this stuff just simply doesn't work. I TRULY wish this stuff would just work as it is supposed to. I hate the fact that Microsoft's marketing machine is better and faster than it's technical arm which does absolutely nothing for our credibility!

Why include the option to export to Excel if it does not work?!

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are ignored."

thx,
FrankHi!
I had the same problem, and i found a solution for the embedded data regions in EXCEL! Altough it is a limitation in RS :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp
There is a solution! Have a look at the riport item "List" in design mode! It has almost the same functionality like teh "Table". You have to manually add headers over the "List" and manually place the textboxes hierarchical inside the list if you want to group fields, but it has a big advantage! Inside the "List" every embedded data regions (just like matrix, or subreport) are displayed correctly in EXCEL!
IMPORTANT: if you place embedded data regions in a "List" you have to right click on the "List" and select properties. On the first tab of the property window click "edit details group" and you must specify grouping expressions here. For examle if there are "Parent" and "Child" rows inside a "List", a good grouping expression would be the Parent row ID.
I hope this helps:)
Rolf|||Oh! And to adding matrix headers, for columns, you have to create a rectangle instead of a table. And place textbox-es inside the rectangle. And finally drag and drop the rectangle inside a merged matrix columnheader cell.
Rolf|||

I am a newbie to the RS and need much more information then given here.

Can anybody please show me an example implementation of the same.

Tanveer

|||I have found the same problem with the "List" object in Reporting Services. Renders fine in HTML and PDF but Excel displays "Data Regions within table/matrix cells are ignored". Very frustrating limitation|||

Unfortunately there is no viable workaround for this particular limitation of the Excel renderer. Data regions cannot be nested in table or matrix cells. We are working to add this functinonality in the next version of Reporting Services.

Tanveer, please let us know specifically what questions you have and we'll do our best to answer.

-Chris

|||Do you have a timeline of when the next version of RS is going to be available?|||

This is exactly what brings touble down the road, One recommends RS over CR and then, when I implement a large matrix report which for very valid reasons is embedded within a table, I receive a user response that this stuff just simply doesn't work. I TRULY wish this stuff would just work as it is supposed to. I hate the fact that Microsoft's marketing machine is better and faster than it's technical arm which does absolutely nothing for our credibility!

Why include the option to export to Excel if it does not work?!

Adding headers to matrix

Hi,
how can I add headers to my matrixcolumns?
I created a table inside the merged cell above, but this won't export
to excel: i get the error "Data Regions within table/matrix cells are ignored."

thx,
FrankHi!
I had the same problem, and i found a solution for the embedded data regions in EXCEL! Altough it is a limitation in RS :
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_dc_v1_8yhy.asp
There is a solution! Have a look at the riport item "List" in design mode! It has almost the same functionality like teh "Table". You have to manually add headers over the "List" and manually place the textboxes hierarchical inside the list if you want to group fields, but it has a big advantage! Inside the "List" every embedded data regions (just like matrix, or subreport) are displayed correctly in EXCEL!
IMPORTANT: if you place embedded data regions in a "List" you have to right click on the "List" and select properties. On the first tab of the property window click "edit details group" and you must specify grouping expressions here. For examle if there are "Parent" and "Child" rows inside a "List", a good grouping expression would be the Parent row ID.
I hope this helps:)
Rolf|||Oh! And to adding matrix headers, for columns, you have to create a rectangle instead of a table. And place textbox-es inside the rectangle. And finally drag and drop the rectangle inside a merged matrix columnheader cell.
Rolf|||

I am a newbie to the RS and need much more information then given here.

Can anybody please show me an example implementation of the same.

Tanveer

|||I have found the same problem with the "List" object in Reporting Services. Renders fine in HTML and PDF but Excel displays "Data Regions within table/matrix cells are ignored". Very frustrating limitation|||

Unfortunately there is no viable workaround for this particular limitation of the Excel renderer. Data regions cannot be nested in table or matrix cells. We are working to add this functinonality in the next version of Reporting Services.

Tanveer, please let us know specifically what questions you have and we'll do our best to answer.

-Chris

|||Do you have a timeline of when the next version of RS is going to be available?|||

This is exactly what brings touble down the road, One recommends RS over CR and then, when I implement a large matrix report which for very valid reasons is embedded within a table, I receive a user response that this stuff just simply doesn't work. I TRULY wish this stuff would just work as it is supposed to. I hate the fact that Microsoft's marketing machine is better and faster than it's technical arm which does absolutely nothing for our credibility!

Why include the option to export to Excel if it does not work?!

Adding folders in the solution explorer?

Hi..
Isn′t it possible to create sub folders in the solution explorer?
I have it hard time to create a decent structure inside the SSIS Package folder, since I have like 30 packages in my project. Is it just me being silly or cannot this be done?
Have a nice day
/Erik

nope...

You can add folders to a solution but withing a project, everything is either in packages of misc.

|||ok, thanks for the input though

Adding Column Using TSQL With Sequence Specified

I am looking for ways to add new column to a table with records inside. If I add the column using this statement:

ALTER TABLE Table1 ADD NewColumn1 decimal(18, 2) NULL

The column would appear at the end.

Is there a way to set where the column should be placed? (Excluding dropping all columns and add the columns in sequence again). I know this might not be very important, I am interested in knowing how Enterprise Manager done this, since you can move a column up and down to change their sequence eventhough there're records inside.EM creates a temp table, then reorganizes the columns in the base table, then inserts the records into the newly structured table.