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

comparing query results

1. What is the best way to compare efficiency (performance) of two different
queries that return the same result in general?
2. What if one contains user defined function one query and another without
user defined function? The reason I am asking this is that the optimizer's
cost model do not reflect the work done in UDF in the query execution plan.
ThanksJustin
Compare both exection plans first. In my experience ( sure it depends) that
queries which don't no UDF/s perform better
"Justin" <nospam@.nospam.com> wrote in message
news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl...
> 1. What is the best way to compare efficiency (performance) of two
> different queries that return the same result in general?
> 2. What if one contains user defined function one query and another
> without user defined function? The reason I am asking this is that the
> optimizer's cost model do not reflect the work done in UDF in the query
> execution plan.
> Thanks
>|||I'd like to use Profiler to see reads etc. Profiler *does* include the work
done inside an UDF
(something that execution plan or STATISTICS IO doesn't do).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Justin" <nospam@.nospam.com> wrote in message news:O%23$Nz46jGHA.4828@.TK2MSFTNGP04.phx.gbl.
.
> 1. What is the best way to compare efficiency (performance) of two differe
nt queries that return
> the same result in general?
> 2. What if one contains user defined function one query and another withou
t user defined function?
> The reason I am asking this is that the optimizer's cost model do not refl
ect the work done in UDF
> in the query execution plan.
> Thanks
>|||Justin a crit :
> 1. What is the best way to compare efficiency (performance) of two differe
nt
> queries that return the same result in general?
> 2. What if one contains user defined function one query and another withou
t
> user defined function? The reason I am asking this is that the optimizer'
s
> cost model do not reflect the work done in UDF in the query execution plan
.
> Thanks
>
The most effective parameters in order are :
1) IO, so execute your queries with this flag set :
SET STATISTICS IO ON /OFF
2) the time taken by CPU and other subsystems, so
SET STATISTICS TIME ON
The exection plan is not a good indicator because it is not quiclky
readable and the number of basic steps is not correlated with time or IO
consummation.
A +
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************

Comparing Date to SQL Server Date

Hey everyone...I need to create a job which occurs once monthly that is based on a stored procedure that contains as a rough example:

CREATE PROCEDURE CompareDate
@.CurrentDate datetime

AS

SELECT * FROM Contracts WHERE EndDate LIKE (CURRENT SERVER DATE)
GO

How do I declare a parameter to automatically get the current server date in the stored procedure? Also, once it is declared how can I add Months to it? For example this job is executed monthly and is supposed to select contracts that expire 2 months from the current month. So once I get the current server date, how do I add 2 months to the date in the stored procedure? Thanks very much in advance!!!Use getdate() function to get the current server date.

Comparing 2 tables

Here is my problem:
I have two tables, one table contians used IP addresses and one table contains all of the IP addresses my department has been assigned. What I would like to be able to do is to compare the two tables and return only the IP Addresses that have not been used yet. I have tried such things as the DISTINCT property, but have had no luck. All I get is duplicate data all throughout my query. Any SQL pro's out there ever attempted such a crazy thing? If so, what did you do to solve it? Any advice would be GREATLY appreciated!First of all, force all of your IP addresses into the same format. I'd recommend minimal form (not permitting leading zeros within an octet, a dot between each octet, no leading or trailing blanks). So the "self" address would be formatted as "127.0.0.1" (obviously without the quotation marks) in minimal form. It doesn't really matter which form you use, as long as all of them use the same form.

Then build a table with all of the possible IP addresses within the range that you've been allocated. The easiest way to handle this is usually a nested loop solution to insert the rows. Remember to remove any rows that have been preallocated by network hardware (routers, etc) needed to support the rest of your IP range.

Finally, build a SELECT statement using the list of available IP addresses, and a NOT EXISTS against the IP addresses that you know have been used.

-PatP|||That worked like a charm! Thank you!

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

compare two text files

Dear all,

What software can compare two text files? They are contains about 100k data generated from Visual Basic 6.0 program and MS SQL 2000. I have many files to compare daily. Please give me some suggestions. Thanks.

Alex

I think VSS (source safe) is suitable for that|||

thanks Eisa. it is helpful.

Could you suggest some freewares?

|||Hi Alex,
I don't know if it is allowed to share free software here or not, howere you can serach google for compare text files free|||Thank your for your help|||Windows has a command line utility (fc) that compares two files. Type "fc /?" at a command prompt, and it will list the options/switches. It's a good solution if it has the features you need, because it's built into Windows, and you won't have to bother installing it everywhere you need it. If you want to use it in a job, you can invoke it with an operating system type step, or use xp_cmdshell if you need to call it after dynamically building the command at run-time. In a job, you would have to pipe the results to another text file, for review.|||

CompareIt! - cool

http://www.grigsoft.com/

|||thanks a lot

compare two tables

Hi all,
We have one master table with all records of the second table but the second
table doesn't contains all the records of the master table.
Like:
Master Table
ID Value
1 A
1 B
1 C
1 D
2 A
2 B
Second Table
ID Value
1 A
1 D
2 A
How can i compare so I can have this result for 2 million records?
1 B
1 C
2 B
Tks in advance
JFBSELECT MasterTable.Id, MasterTable.Value, SecondTable.Id
FROM MasterTable
LEFT OUTER JOIN SecondTable
ON MasterTable.Id = SecondTable.Id
AND MasterTable.Value = SecondTable.Value
WHERE SecondTable.ID IS NULL
Should display all the records from the Master Table that do not have a
match in the SecondTable.
"JFB" <help@.jfb.com> wrote in message
news:eQhPgH56FHA.3388@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> We have one master table with all records of the second table but the
> second table doesn't contains all the records of the master table.
> Like:
> Master Table
> ID Value
> 1 A
> 1 B
> 1 C
> 1 D
> 2 A
> 2 B
> Second Table
> ID Value
> 1 A
> 1 D
> 2 A
> How can i compare so I can have this result for 2 million records?
> 1 B
> 1 C
> 2 B
> Tks in advance
> JFB
>|||Great Bill... tks for you help :)
Rgds
JFB
"Bill Edwards" <billedwards@.msn.com> wrote in message
news:u8sxPT56FHA.3648@.tk2msftngp13.phx.gbl...
> SELECT MasterTable.Id, MasterTable.Value, SecondTable.Id
> FROM MasterTable
> LEFT OUTER JOIN SecondTable
> ON MasterTable.Id = SecondTable.Id
> AND MasterTable.Value = SecondTable.Value
> WHERE SecondTable.ID IS NULL
> Should display all the records from the Master Table that do not have a
> match in the SecondTable.
> "JFB" <help@.jfb.com> wrote in message
> news:eQhPgH56FHA.3388@.TK2MSFTNGP11.phx.gbl...
>sqlsql

compare two different date format column?

Hi guys,

I want to compare two date fields, which are located on different tables. One field contains a date value in this format: ###/###-#### and the other field on the other table contains (###-###-#### and some ##########)

So I want to compare this two different format. May be changing both the formats to a common format and make the comparison, something like that.

Any idea is appreciate.

Hi Amde

Just for clarity, would you be able to provide real examples (from your tables) of a date in each format?

Thanks
Chris

|||

oh yeah,

Table 1.

Phone no.

847/678-2828

Table 2.

Phone no.

333-777-5555

252 321 3333

2223334444

(123)456-789

...

|||

For phone numbers, I would consider creating a Function that strips out all but the numbers -and then saving and comparing only numbers. (Leave the formating for the client application.)

Comparing DATE fields will be somewhat different -however formating is not an issue if the datatype is (small)datetime. If Date data is stored as (var)char -that is a whole different can of worms.

|||

Well that changes things slightly - you originally said that these were date fields...

Another couple of questions - how many rows of data are you looking to compare? Are there any other formats that we should know about?

Thanks
Chris

|||

I've just put together the function below that will strip any non-numerical characters from an input string.

Don't expect fantastic performance from the function when comparing thousands of rows. It would be far better, as Arnie highlighted, to store an unformatted string and leave the formatting to the client.

Chris

CREATE FUNCTION dbo.CleanString(@.InputString VARCHAR(8000))
RETURNS VARCHAR(8000)
WITH RETURNS NULL ON NULL INPUT
AS
BEGIN
DECLARE @.NewString VARCHAR(8000)
SET @.NewString = ''
DECLARE @.ValidChars CHAR(10)
SET @.ValidChars = '0123456789'
DECLARE @.Loop INT
SET @.Loop = 1
WHILE @.Loop <= LEN(@.InputString)
BEGIN
IF CHARINDEX(SUBSTRING(@.InputString, @.Loop, 1), @.ValidChars) > 0
SET @.NewString = @.NewString + SUBSTRING(@.InputString, @.Loop, 1)

SET @.Loop = @.Loop + 1
END

RETURN @.NewString
END
GO

SELECT dbo.CleanString('1329842DCXLXMC2QPWXMQWX09SWDM09324')
--Returns: 132984220909324

SELECT CASE WHEN dbo.CleanString('1M&&2-FL3FCM4+=5M#6khn7wmx8KE9AAS0')
= dbo.CleanString('cxmn1dUI2QWWJHB3NCU4PWCK5IVN67DBW8XW9K 0')
THEN 'These strings are the same when cleansed.'
END,
dbo.CleanString('1M&&2-FL3FCM4+=5M#6khn7wmx8KE9AAS0'),
dbo.CleanString('cxmn1dUI2QWWJHB3NCU4PWCK5IVN67DBW8XW9K 0')
--Returns: 'These strings are the same when cleansed.', 1234567890, 1234567890

|||

Oh!my bad, I am really sorry.

- I want to say a phone number (which is varchar data type)

- The first table contain about 40,000 records and the second table contains about 60, 000 records. So I want to get the difference.

- Those are the common formats. I hope if I find a solution for one of the format, I can apply similar logic.

Sorry for the confusion.

|||

For a large amount of data, avoiding the loop may increase performance.

By using a 'Numbers' table, this method avoids looping.

-- Prepare Numbers Table

SET NOCOUNT ON
CREATE TABLE Numbers ( n int )
GO

DECLARE @.n int
SET @.n = 1
WHILE @.n < 100
BEGIN
INSERT INTO Numbers VALUES ( @.n )
SET @.n = ( @.n + 1 )
END
-- End Numbers Table

CREATE FUNCTION dbo.NumbersOnly
( @.Text varchar(100) )
RETURNS varchar(100)
AS
BEGIN
DECLARE @.Output varchar(100)
SELECT @.Output = ''
SELECT @.Output = @.Output + CASE
WHEN RealNum LIKE '[0-9]'
THEN RealNum
ELSE ''
END
FROM (SELECT substring( @.Text, n, 1 ) RealNum
FROM Numbers
) d
RETURN @.Output
END
GO

SELECT dbo.NumbersOnly( '(555) 987-4321' )

--
5559874321

|||

You should clean the data and store it in a normalized manner. This is the best way to handle. Solutions using UDFs are fine but they will be slow and you will have to keep changing them if you find newer formats or rules. For example, how do you handle phone numbers like 1-800-96MSFAX? What about phone numbers from other countries etc? There are so many cases where hard-coded solutions break and you will end up with more problems than necessary. I have built data warehouses in the past (in my previous company for data mining/analytics purpose) and we used public geography data, address book scrubbing software etc. So it is better to invest in a solution or package that is specialized for the type of data you are dealing with and focus on your business problem. Once you normalize the phone numbers then it is very easy to compare them - you can easily restrict phone numbers based on country code for example or compare different formats easily.

|||

I agree with you. Having a normalized database is the best thing. However, I want to do this task only for the time being. Here is the thing, I received an excel sheet which contains thousands of records. On the other hand we have a table in our database. So my goal is to get the difference and insert the new data to my table. So what I did is I created an SSIS package to migrate the excel sheet data into SQL Server table, so that it will be easy to write a query to get the differential data. Inserting the data is not an issue. All I need is to compare these two tables and get the different. The only unique field I have to do the comparison is the PHONE column. And the PHONE column has a different format, as I tried to explain before.

Thx.

|||

Thanks guys, I really appreciate your help. I'm good now.

Compare Time values of a datetime field

I have a table called 'JobSchedules' it contains datetime values for when to
start a job and when to end a job. The date part of the equation is
irrelevant as the schedule is meant to start and stop jobs daily.
How can I do a query to return jobs with a starttime = 9:30 a.m. (for
example) or where the time portion of getdate() is between the StartTime and
EndTime (time portions).
There has to be relatively simple method to do this right?You should make sure the DATE portion is always the same value.
'20050101' for example is Jan 01 of 2005. Then the only relevant part will
be the time and you can use = or Between and compare directly.
Andrew J. Kelly SQL MVP
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:OdXxi9zRFHA.3336@.TK2MSFTNGP10.phx.gbl...
>I have a table called 'JobSchedules' it contains datetime values for when
>to start a job and when to end a job. The date part of the equation is
>irrelevant as the schedule is meant to start and stop jobs daily.
> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime
> and EndTime (time portions).
> There has to be relatively simple method to do this right?
>|||>I have a table called 'JobSchedules' it contains datetime values for when
>to start a job and when to end a job. The date part of the equation is
>irrelevant as the schedule is meant to start and stop jobs daily.
Then why not always use the same date (e.g. 1900-01-01)?

> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime
> and EndTime (time portions).
> There has to be relatively simple method to do this right?
If the dates are all different, then with your existing data you will have
to do messy converts etc. One way would be:
SELECT columns FROM table WHERE CONVERT(CHAR(8), column, 108) = '09:30:00'
Not very efficient. I would recommend doing this:
UPDATE table SET column = CONVERT(CHAR(8), column, 108)
And updating the application(s) and/or stored procedure(s) to insert time
only. Then the dates will all be 1900-01-01. Now you can use an index, and
say:
DECLARE @.startTime SMALLDATETIME
SET @.startTime = '9:30'
SELECT columns FROM table WHERE starttime = @.startTime
(If you need date only for some other reason, e.g. indicating when the
recurring schedule started, then use a separate column.)|||Try,
...
where
getdate() between cast(convert(varchar(11), getdate(), 126) +
right(convert(varchar(50), StartTime, 126), 12) as datetime)
and cast(convert(varchar(11), getdate(), 126) + right(convert(varchar(50),
EndTime, 126), 12) as datetime);
No search-arguments in the expression, so do not expect and index s.
AMB
"Roger Twomey" wrote:

> I have a table called 'JobSchedules' it contains datetime values for when
to
> start a job and when to end a job. The date part of the equation is
> irrelevant as the schedule is meant to start and stop jobs daily.
> How can I do a query to return jobs with a starttime = 9:30 a.m. (for
> example) or where the time portion of getdate() is between the StartTime a
nd
> EndTime (time portions).
> There has to be relatively simple method to do this right?
>
>

Compare Dates

Hi,
I have a datetime SQL column wich contains a timestamp from when the row was
added using the GetDate() function. I would like to retrive rows based on a
comparsion of the date part of this value (discard the hours,minutes etc).
The datetime variable I am passing in to my stored procedure is a DateTime
object from VB .Net and only contains Year,Month,Day. How do I produce a
select statement that ignores the time part of the sql datetime value ?
i.e.
SELECT * From MyColumn WHERE DateAdded=@.InDate
Niclas"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:Oi2u5M%23PGHA.3432@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a datetime SQL column wich contains a timestamp from when the row
> was added using the GetDate() function. I would like to retrive rows based
> on a comparsion of the date part of this value (discard the hours,minutes
> etc).
> The datetime variable I am passing in to my stored procedure is a DateTime
> object from VB .Net and only contains Year,Month,Day. How do I produce a
> select statement that ignores the time part of the sql datetime value ?
> i.e.
> SELECT * From MyColumn WHERE DateAdded=@.InDate
> Niclas
>
SELECT *
FROM your_table
WHERE dateadded >= @.indate
AND dateadded < DATEADD(DAY,1,@.indate);
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi there,
I always use the ISO representation of the date:
CONVERT(VARCHAR(10),GETDATE(),112)
HTH, Jens Suessmeyer.|||To remove the time part of the date:
select convert(datetime,convert(char(8),getdate
(),112))
2006-03-04 00:00:00.000
Roy Harvey
Beacon Falls, CT
On Sat, 4 Mar 2006 23:21:14 -0000, "Niclas"
<lindblom_niclas@.hotmail.com> wrote:

>Hi,
>I have a datetime SQL column wich contains a timestamp from when the row wa
s
>added using the GetDate() function. I would like to retrive rows based on a
>comparsion of the date part of this value (discard the hours,minutes etc).
>The datetime variable I am passing in to my stored procedure is a DateTime
>object from VB .Net and only contains Year,Month,Day. How do I produce a
>select statement that ignores the time part of the sql datetime value ?
>i.e.
>SELECT * From MyColumn WHERE DateAdded=@.InDate
>Niclas
>|||Niclas
SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT))AS DATETIME)
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:Oi2u5M%23PGHA.3432@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a datetime SQL column wich contains a timestamp from when the row
> was added using the GetDate() function. I would like to retrive rows based
> on a comparsion of the date part of this value (discard the hours,minutes
> etc).
> The datetime variable I am passing in to my stored procedure is a DateTime
> object from VB .Net and only contains Year,Month,Day. How do I produce a
> select statement that ignores the time part of the sql datetime value ?
> i.e.
> SELECT * From MyColumn WHERE DateAdded=@.InDate
> Niclas
>

Commas in the result set

I am using SQL 2005 and have a query that returns seven columns of data. In one the columns, the results contains commas. For example, one of the results is 'brooke, nick, dustin, and me.jpg'. The results are saved into a .csv file so it can be imported into Excel. The user named the file with the commas and I need to preserve the original file name.

My question:

Is there some way to format the data inside of SQL so that when it gets saved as a .csv file, the extra commas don't throw off the column layout?

I don't want to have to manually clean up possibly several hundred rows of data everytime I run the query.

Thanks in advance.

As I recall, you need to wrap the string in double quotes, and then double up any quotes that appear inside the string. Something like this:

SELECT '"' + REPLACE(col, '"', '""') + '"' AS col FROM ...

Do all of your varchar columns like that and you should be in the clear as far as Excel is concerned.
|||

I am not sure how you are importing the data into CSV. The CSV columns can have Commas, yes you can survie with comma, enclose the values with Double Quotes.

sample,

"1", "brooke, nick, dustin and me.jpg", "col3 value", "col4 value"

"2", "dustin and me.jpg", "col3 value", "col4 value"

here the sample C# code,

String.Format("\"{0}\", \"{1}\", \"{2}\"\n", col1.Replace("\"", "\"\""), col2.Replace("\"", "\"\""), col3.Replace("\"", "\"\""));

|||

I did use the Replace function to replace the commas with an extra space in the result set instead of trying to preserve the comma. I tried several different ways to keep the comma but none of them worked. The data is manually imported into Excel. The solution will work for now.

Thanks so much for your help!

commas

how do i insert a string that contains a comma?Quotes around the comma.

","

Combining XQuery and data paging in SQL Server 2005

Folks,
I have a table in SQL Server 2005 that contains a row of type "XML" (for now
untyped - no schema behind it).
I can easily query that table and retrieve values from the XML - way !
I can also easily query that table and use the data paging mechanism based
on the ROW_NUMBER() function - way , too!
But I can't seem to combine the two...
Imagine I have books stored in my XML column:
<books>
<book id='123'>
<author>Smith</author>
<yearpublished>1999</yearpublished>
</book>
<book id='177'>
<author>Black</author>
<yearpublished>2002</yearpublished>
</book>
... (lots more books) .....
</books>
To query the table, I use something like:
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished'
FROM BooksTable
Works fine. Now what I'd like to do is add a ROW_NUMBER() function over the
e.g. "YearPublished" attribute (stored in the XML) to get my books by year
published, in batches of 10 or whatever:
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as 'YearPublished',
ROW_NUMBER() OVER(ORDER BY YearPublished) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Trouble is - this won't work, since MgmtStudio complains it doesn't know
about the "YearPublished" column.....
What am I missing? Can I still achieve this goal somehow?
Any hints are most welcome !!
Marc
(mscheuner -* AT *- gmail.com)Untested but try this
WITH BooksList as
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') as 'Author',
BooksXML.value('(/book/yearpublished)[1]', 'int') as
'YearPublished',
ROW_NUMBER() OVER(ORDER BY
BooksXML.value('(/book/yearpublished)[1]', 'int')) AS 'rownum'
FROM BooksTable
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20|||... or :-)
WITH x AS
(
SELECT
-- <some fields from the base table>
BooksXML.value('(/book/author)[1]', 'varchar(50)') AS Author,
BooksXML.value('(/book/yearpublished)[1]', 'int') AS YearPublished,
FROM BooksTable
), BooksList AS (
SELECT
*
,ROW_NUMBER() OVER (ORDER BY YearPublished) AS RowNum
FROM x
)
SELECT * FROM BooksList
WHERE rownum BETWEEN 11 AND 20
Also, please consider not using single quotes (') for naming aliases, you
should use double quotes (") as that is the ANSI standard and using single
quotes will be debrecated.
HTH
/ Tobias