Comparing Reporting Programs

I am developing an ASP.Net 2.0 application that will make heavy use of reports. The VisualStudio.Net 2005 Professional edition contains two reporting programs -- Crystal Reports and SQL Server 2005 Reporting Services. Can anyone refer me to a white paper, technical article, book chapter, blog, etc. that compares the strengths and weaknesses of these two programs? Thanks for any suggestions.

Ken McLean

In my opinion SSRS, though far from perfect beats Crystal Reports. I will look up some references for you.

|||

Have a look at:

SQL Server Reporting Services 2005 XML extensions http://www.codeproject.com/KB/reporting-services/SSRS2005XML.aspx|||

Thanks a lot. That's exactly what I was looking for.

Ken McLean

Compare dates

Hi!
I'm just attempting to write a SQL statement that will see if a date matches
.
Using asp.net, I place the date into a field in the database. It's formatted
like this:
08/12/2005
I'd like to create a query that counts how many rows a date (yesterday) is i
n.
This is the query I created:
select count(*) from FSRTurnover where theDate = Convert(Char(12),
DateAdd("d", -1, getdate()), 101)
but it doesn't seem to work. It doesn't return any rows...
If I use a static date, it does work:
select count(*) from FSRTurnover where theDate = '08/18/2005'
Any ideas why this might be?A couple of issues here.
First of all, 8/12/2005 is an ambiguous date. Depending on locale it might
mean August 12 or December 8. To be safe, always use the ISO date format,
YYYYMMDD.
Second, your query is only good for dates that happen to have a timestamp of
exactly midnight. But I'm guessing that's not what you really want... You
probably want ALL times from yesterday?
Try:
select count(*)
from FSRTurnover
where theDate >= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0)
AND theDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
That will give you all dates >= midnight yesterday, and < midnight today.
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Casey" <Casey@.discussions.microsoft.com> wrote in message
news:D31A0999-5F58-4882-A0B5-C910A7F813C0@.microsoft.com...
> Hi!
> I'm just attempting to write a SQL statement that will see if a date
matches.
> Using asp.net, I place the date into a field in the database. It's
formatted
> like this:
> 08/12/2005
> I'd like to create a query that counts how many rows a date (yesterday) is
in.
> This is the query I created:
> select count(*) from FSRTurnover where theDate = Convert(Char(12),
> DateAdd("d", -1, getdate()), 101)
> but it doesn't seem to work. It doesn't return any rows...
> If I use a static date, it does work:
> select count(*) from FSRTurnover where theDate = '08/18/2005'
> Any ideas why this might be?|||> I'm just attempting to write a SQL statement that will see if a date
> matches.
> Using asp.net, I place the date into a field in the database. It's
> formatted
> like this:
> 08/12/2005
No, it's not, if it is a DATETIME or SMALLDATETIME column. That is just how
*your* client tool shows it to you. Behind the scenes, it is actually
stored as two numeric values and does not have any ridiculously ambiguous
and confusing format like mm/dd/yyyy.

> I'd like to create a query that counts how many rows a date (yesterday) is
> in.
> This is the query I created:
> select count(*) from FSRTurnover where theDate = Convert(Char(12),
> DateAdd("d", -1, getdate()), 101)
Why are you converting to a character format? And why on earth would you
use CHAR(12)? You're comparing dates, not strings!
SELECT COUNT(*)
FROM FSRTurnover
WHERE theDate -- awful column name!
>= DATEADD(DAY,-1,DATEDIFF(DAY,0,GETDATE()))
AND theDate
< DATEADD(DAY,0,DATEDIFF(DAY,0,GETDATE()))
or broken down:
DECLARE @.yesterday SMALLDATETIME, @.today SMALLDATETIME
SET @.yesterday = DATEDIFF(DAY, 0, GETDATE())-1
SET @.today = @.yesterday + 1
SELECT COUNT(*)
FROM FSRTurnover
WHERE theDate >= @.yesterday
AND theDate < @.today
A|||Yeah. That seemed to work. I keep the ISO date format thing in mind for the
future.
Thanks.
"Adam Machanic" wrote:

> A couple of issues here.
> First of all, 8/12/2005 is an ambiguous date. Depending on locale it migh
t
> mean August 12 or December 8. To be safe, always use the ISO date format,
> YYYYMMDD.
> Second, your query is only good for dates that happen to have a timestamp
of
> exactly midnight. But I'm guessing that's not what you really want... You
> probably want ALL times from yesterday?
> Try:
> select count(*)
> from FSRTurnover
> where theDate >= DATEADD(dd, DATEDIFF(dd, 1, GETDATE()), 0)
> AND theDate < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)
> That will give you all dates >= midnight yesterday, and < midnight today.
>
> --
> Adam Machanic
> SQL Server MVP
> http://www.datamanipulation.net
> --
>
> "Casey" <Casey@.discussions.microsoft.com> wrote in message
> news:D31A0999-5F58-4882-A0B5-C910A7F813C0@.microsoft.com...
> matches.
> formatted
> in.
>
>

CompactDatabase in ASP with SQL Server Microsoft SQL Server Compact

I've been playing around with Microsoft SQL Server Compact Edition
v3.1 as a posible replacement for the SQL Server CE 2.0 (can't
remember exactly what the called it). Its working pretty well. I
was able to connect to the database using the same recordset object,
ADODB.RecordSet and connection object, ADOCE.Connection.3.1. However
where I've run into trouble is when I try to compact the database.
This code works on 2.0.

srcConn = "Provider=Microsoft." & "SQLServer" & ".OLEDB.CE.2.0;Data
Source=\myDB.sdf"
destConn = "Provider=Microsoft." & "SQLServer" & ".OLEDB.CE.2.0;Data
Source=\myDB.sdf"

Set DBEngine = CreateObject("SSCE.Engine.2.0")

DBEngine.CompactDatabase srcConn, destConn

But now when I try to run it vor v3.1

srcConn = "provider=microsoft.sqlserver.mobile.oledb.3.0;data
source=\myDB.sdf"
destConn = "provider=microsoft.sqlserver.mobile.oledb.3.0;data
source=\mDB1.sdf"

Set DBEngine = CreateObject("SSCE.Engine.3.0")

DBEngine.CompactDatabase srcConn, destConn

I get this error message:

Info: Object required: 'DBEngine'

As I understand it SSCE.Engine.3.0 is still an ActiveX
object. Is it no longer posible to run this directly from
asp?

Using SQL CE under asp.net has been blocked in SQL CE 3.1, see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceengine.aspx. Maybe this is what you are facing with asp as well. Have no idea how to do a

Code Snippet

AppDomain.CurrentDomain.SetData(

under asp classic.

Communicating with a SQL Server installed on a remote machine using a web service

I have SQL Server 2005 express installed on my machine, and I was wondering if there is any way to have an asp.net 2.0 web service hosted by a different company be able to send sql commands to the sql server on my machine.

Yes.

It would require that you make your computer somewhat vulnerable to the internet.

You will have to open a port in the firewall.

You will have to deal with Web Services security issues or all kinds of folks 'could' be sending commands to your SQL Server. And if your security isn't 'irontight', one of those commands could even be "FORMAT C:".

So my point is yes, it can be done, I want you to understand that there are a lot of steps necessary to 'do it right'.

|||Thank you for your help with this. I think for now I will hold off on this until I can find time to research the security issues much more thouroughly.

Comments Table Help

Hi everyone. I've taken a while off of developing site in ASP.net but had a site that I wanted to upgrade a little, but needed a little help.

What I have currently is a website of a person with videos and images of that person. To view the videos, I have a "view_video.aspx?ID=" page that plays the video from YouTube by looking my database for the ID, YouTube URL, Name, and Description. What I want to do is create a "comments" table for visitors to add comments for each video and then display all comments on the page.

So far, my "comments" table looks like this. ID, otherID, name, email, comment, type, date.

The ID is the id of the comment, otherID is the foreign key to the "videos" table, the name is the name of the person leaving the comment, e-mail is for the person, comment is the text, type is the type of video (tutorial, sampler, random video), and date is a timestamp for when users leave the comment.

So hopefully I have a good start. I don't have any code to show right at the moment for I am at work, but if anyone has any ideas or critiques so far, I'd love to hear them. This is an interesting project for my friend and I'd love to implement this sometime.

Thanks,
TetrisSmalls

It sounds great. But some unknown issue is always be detected during the development. So it's too early for us to judge the project or even suggestions on the project.

Command TimeOut

Hi,

i've a ASP application builds SQL for a report component; report execution get an error about command timeout, since i can't set it (i pass only query and connection to this component) is there a default generic option (for the SqlServer instance) that permits to me to increase this value?

Thanks : :Dyou need check your sql script: how long does it execute in query analyzer and how many records will return.
if the time is spending on join or you get a large number of records, you need use stored prcoedure to speed up.

comma delimited list update stored procedure

I have a stored procedure that I want to use to update multiple records. I'm using ASP and the request form collection is returning values in a comma delimited list.
Example:
name1 = value1, value2, value3, etc.
name2 = value1, value2, value3, etc.
name3 = value1, value2, value3, etc.

Here is how I wrote my stored procedure:

CREATE PROCEDURE dbo.Sp_Update_ABR_Record
(
@.abrID int,
@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt
varchar(50),
@.incrdecr varchar(50),
@.review char(10),
@.abrdetlsID varchar(50)
)
AS
UPDATE DIM_ABR_REQ_HDR
SET ABR_review = @.review
WHERE ABR_ID = @.abrID

UPDATE DIM_ABR_REQ_DETLS
SET ABR_DETLS_DDO = @.ddo, ABR_DETLS_AY = @.ay,
ABR_DETLS_STRATEGY = @.strategy, ABR_DETLS_BUDG_ACCT = @.budgacct,
ABR_DETLS_BUDG_OBJ = @.budgobj, ABR_DETLS_FUND_ORIG_AMT = convert(money, @.origamt), ABR_DETLS_FUND_INCR_DECR = convert(money, @.incrdecr)
WHERE
ABR_DETLS_ID = @.abrdetlsID
GO

The second update is where the comma delimited list needs to be handled. The first update is only updating one field once.

Is there a way to write the procedure to handle the comma delimited list? Or, is the way I have the stored procedure okay and I just need to handle the comma delimited list within the ASP code? I'm not sure which way I can accomplish this?

Thanks for any help.
-D-Hi,
I think providing values in XML format rather than comma delimited will deliver more flexibility to retrieve values from that.
Still you can write User Defined Functions to pass the comma delimited string into that and get particular value.
Regards,
Leila|||Which parameter is the list, and how did you intend to use it?

-PatP|||The request form collection will return each of these parameters in a comma delimited list, which the second update in the stored procedure would handle:

@.ddo varchar(50),
@.ay varchar(50),
@.strategy varchar(10),
@.budgacct varchar(10),
@.budgobj varchar(10),
@.origamt varchar(50),
@.incrdecr varchar(50),
@.abrdetlsID varchar(50)

So, should I use the split function and pass the information into the procedure that way? I've used the split funciton for one parameter, but not multiple parameters. So, I wasn't sure how to code for that?

Thank you for your help.
-D-|||If the number of parameters the same across the set then do parse them and execute the procedure once for each combination. Otherwise, you need to review this design and get away from doing thing this way.|||Yes, there are the same number of parameters for each variable in the set. So, if I use the split function:

ddolist = Split(Request.Form("ddo"),", "
strategylist = Split(Request.Form("strategy"),", "
aylist = Split(Request.Form("ay"),", "
budgobjlist = Split(Request.Form("budgobj"),", "
budgacctlist = Split(Request.Form("budgacct"),", "
incrdecrlist = Split(Request.Form("incrdecr"),", "
abrdetlsIDlist = Split(Request.Form("abrdetlsID"),", "

I can use any of the parameters to determine the number of loops by using Ubound?

i.e.:

Loop_Max = UBound(abrdetlsIDlist)
For x = 0 to Loop_Max
Command_Name.Parameters.Item("@.ddo").Value = ddolist(x)
Command_Name.Parameters.Item("@.strategy").Value = strategylist(x)
Command_Name.Parameters.Item("@.ay").Value = aylist(x)
Command_Name.Parameters.Item("@.budgobj").Value = budgobjlist(x)
Command_Name.Parameters.Item("@.budgacct").Value = budgacctlist(x)
Command_Name.Parameters.Item("@.incrdecr").Value = incrdecrlist(x)
Command_Name.Parameters.Item("@.abrdetlsID").Value = abrdetlsIDlist(x)
Command_Name.Execute()
Next

Would that be correct?

Thank you for your help.
Regards,
-D-