Does RS use ORDER BY for grouping?

It seems like you don't need to specify SQL's ORDER BY to match Page and Group breaks in RS. I.e., RS does
this handling by itself, with the added work and memory requirements to do it.
My question is whether RS will understand that I do have ORDER BY in the query that matches my Page and Group
breaks, and just read off the input stream without doing the sorting etc by itself. If it doesn't, I wouldn't
want to burden the DBMS with the sorting as it would be redundant...
I tried a report, which was indeed alphabetically grouped by RS without a SQL ORDER BY. Even when adding ORDER
BY NEWID() (which returns rows in a different "random ordering for each execution), the report was still
grouped alphabetically.
(I am not referring to the ordering of the details info, I understand that ORDER BY is needed for that.)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/You don't ever need to use ORDER BY in your query, even to order the
details. RS supports many different types of data sources (and you can
easily add your own by writing a custom data processing extension), not just
SQL, and some these data sources may not have the ability to sort. Also, RS
does not examine your query and cannot detect that you have an ORDER BY.
--
Rajeev Karunakaran [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OPN1N6ifEHA.1644@.tk2msftngp13.phx.gbl...
> It seems like you don't need to specify SQL's ORDER BY to match Page and
> Group breaks in RS. I.e., RS does
> this handling by itself, with the added work and memory requirements to do
> it.
> My question is whether RS will understand that I do have ORDER BY in the
> query that matches my Page and Group
> breaks, and just read off the input stream without doing the sorting etc
> by itself. If it doesn't, I wouldn't
> want to burden the DBMS with the sorting as it would be redundant...
> I tried a report, which was indeed alphabetically grouped by RS without a
> SQL ORDER BY. Even when adding ORDER
> BY NEWID() (which returns rows in a different "random ordering for each
> execution), the report was still
> grouped alphabetically.
> (I am not referring to the ordering of the details info, I understand that
> ORDER BY is needed for that.)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
>

does backup database need to specify file name?

hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder and
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error :
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanks
Yes, you need to specify a file name. It is easy to generate using some TSQL code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder and
> it will automatically generate the image file by using the timestamp as the
> file name. But on SQL server, only specify the backup folder gives me error :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks
|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you
|||I only posted an example. Read in Books Online about the CONVERT function and use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if i
> have multiple copies on the same day, what is the best way to distinct them?
> thank you
|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me error
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you do
normally?
thanks
|||The character ":" is not allowed in a file name. That is the reason for your error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120) + '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me error
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you do
> normally?
> thanks

does backup database need to specify file name?

hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder an
d
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error
:
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL
code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder
and
> it will automatically generate the image file by using the timestamp as th
e
> file name. But on SQL server, only specify the backup folder gives me erro
r :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function an
d use a suitable
formatting code (3:rd parameter).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if
i
> have multiple copies on the same day, what is the best way to distinct the
m?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me erro
r
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you d
o
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your
error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120)
+ '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me er
ror
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you
do
> normally?
> thanks

does backup database need to specify file name?

hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
DB2, when i backup a database, i only need to designate the backup folder and
it will automatically generate the image file by using the timestamp as the
file name. But on SQL server, only specify the backup folder gives me error :
ODBC SQLState: 42000, cannot open backup device (which is the directory
name), device error, can some one help me on this?
thanksYes, you need to specify a file name. It is easy to generate using some TSQL code, though. For
example:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs' + CONVERT(char(8), CURRENT_TIMESTAMP, 112) + '.bak'
BACKUP DATABASE pubs
TO DISK = @.f
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:43F3E954-4DA6-4D3F-8F76-88DF689BBADD@.microsoft.com...
> hi, we are using SQL Server 2000 SP4, i am very new. Since i normally use
> DB2, when i backup a database, i only need to designate the backup folder and
> it will automatically generate the image file by using the timestamp as the
> file name. But on SQL server, only specify the backup folder gives me error :
> ODBC SQLState: 42000, cannot open backup device (which is the directory
> name), device error, can some one help me on this?
> thanks|||but the backup image has not timestamp on it, only year, month, day, so if i
have multiple copies on the same day, what is the best way to distinct them?
thank you|||I only posted an example. Read in Books Online about the CONVERT function and use a suitable
formatting code (3:rd parameter).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:9F07F525-8E2B-4984-BB97-D9810B6F730F@.microsoft.com...
> but the backup image has not timestamp on it, only year, month, day, so if i
> have multiple copies on the same day, what is the best way to distinct them?
> thank you|||yes, i look at the third parameter, i try to use the one with time stamp
(e.g. 120), but they all contain space in the filename , which gives me error
,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
device off-line. See the SQL Server error log for more details. how do you do
normally?
thanks|||The character ":" is not allowed in a file name. That is the reason for your error message. Use
another style, or use REPLACE to change those characters to something else:
DECLARE @.f varchar(8000)
SET @.f = 'C:\temp\pubs ' + REPLACE(CONVERT(char(19), CURRENT_TIMESTAMP, 120) + '.bak', ':', '.')
select @.f
BACKUP DATABASE pubs
TO DISK = @.f
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tulip" <tulip@.discussions.microsoft.com> wrote in message
news:74896E89-E698-446E-9D34-65380179FDB3@.microsoft.com...
> yes, i look at the third parameter, i try to use the one with time stamp
> (e.g. 120), but they all contain space in the filename , which gives me error
> ,Cannot open backup device 'C:\2005-09-06 15:53:53.bak'. Device error or
> device off-line. See the SQL Server error log for more details. how do you do
> normally?
> thanks

Does an embedded XML DataSet have to be hardcoded?

I have an XML DataSource in my report which does not specify a connection
string, because I want to use an embedded XML DataSet. However I would like
the XML DataSet text to be defined using an expression (actually a function
call in my custom assembly). Even though you can define an expression for the
XML DataSet, doing so generates a design time error like this:
"The XmlDP query is invalid. Syntax error at line 1, character 3 of the
ElementPath. (Microsoft.ReportingServices.DataExtensions)"
If I define the DataSet text like this it works fine:
============================================= <Query>
<ElementPath>Root /Parameter {@.ParamName, @.ParamValue} </ElementPath>
<XmlData>
<Root>
<Parameter ParamName="Param1" ParamValue="Value1"></Parameter>
<Parameter ParamName="Param2" ParamValue="Value2"></Parameter>
<Parameter ParamName="Param3" ParamValue="Value3"></Parameter>
<Parameter ParamName="Param4" ParamValue="Value4"></Parameter>
<Parameter ParamName="Param5" ParamValue="Value5"></Parameter>
</Root>
</XmlData>
</Query>
=============================================
If I define the DataSet text like this I get the error message:
============================================= = "<Query>"
+ "<ElementPath>Root /Parameter {@.ParamName, @.ParamValue} </ElementPath>"
+ "<XmlData>"
+ "<Root>"
+ "<Parameter ParamName=""Param1"" ParamValue=""Value1""></Parameter>"
+ "<Parameter ParamName=""Param2"" ParamValue=""Value2""></Parameter>"
+ "<Parameter ParamName=""Param3"" ParamValue=""Value3""></Parameter>"
+ "<Parameter ParamName=""Param4"" ParamValue=""Value4""></Parameter>"
+ "<Parameter ParamName=""Param5"" ParamValue=""Value5""></Parameter>"
+ "</Root>"
+ "</XmlData>"
+ "</Query>"
=============================================
The above is just an expression that shows the error message. Ideally my
DataSet text will look like this:
=Code.RptLib.ReportParametersXML()
It looks like I am up against a limitation in the product. Can you confirm
that this is a limitation in trhe product (embedded XML DataSets MUST be
hardcoded), or is there a workaround or technique that I am missing?
Thanks!
-- Chris
--
Chris, SSSIHello Chirs,
I have reproduced this issue on my side.
I will do some research on this. I appreciate your patience.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
=====================================================
PLEASE NOTE: The partner managed newsgroups are provided to assist with
break/fix
issues and simple how to questions.
We also love to hear your product feedback!
Let us know what you think by posting
- from the web interface: Partner Feedback
- from your newsreader: microsoft.private.directaccess.partnerfeedback.
We look forward to hearing from you!
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================.|||Hi Wei Lu,
I found an interesting thing.
By accident I tried =Code.RptLib.ReportParametersXML instead of
=Code.RptLib.ReportParametersXML() and that worked.
Because my method does not take any parameters, I guess VB.NET does not like
the empty parenthesis and for some reason the Report Designer gives a
misleading error message about this problem.
So I solved my problem and I do not have to hard code the XML. It is coming
from my custom assembly.
However, I have no idea why the expression syntax ="..." does not work. But
since I do not need that approach it is not a problem for me right now.
Still, you might want to pass this "bug" on to the developers of SSRS.
-- Chris
Chris, SSSI
"Wei Lu [MSFT]" wrote:
> Hello Chirs,
> I have reproduced this issue on my side.
> I will do some research on this. I appreciate your patience.
> Sincerely yours,
> Wei Lu
> Microsoft Online Partner Support
> =====================================================> PLEASE NOTE: The partner managed newsgroups are provided to assist with
> break/fix
> issues and simple how to questions.
> We also love to hear your product feedback!
> Let us know what you think by posting
> - from the web interface: Partner Feedback
> - from your newsreader: microsoft.private.directaccess.partnerfeedback.
> We look forward to hearing from you!
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
> ======================================================.
>