Comparing 2 files

Dear All,
Periodically, i need to copy a file from the source folder to destination
folder. the source file will be updated from time to time. Before copying
the file to destination folder, i need to compare the modified date of the
file, mean only copy the file to destination folder if the source file is
updated. How can we do this in SQL?
I will use the xp_cmdShell to copy the file. If use the xp_getfiledetails,
how can i get the information return fromt the stored procedure to check the
modified date? Or, is there any better method of doing this?
I am using SQL Server 2000.
Thanks & Best Regards,
EdwardInsert as below..and then you can compare ..
CREATE TABLE #FILE
(
name varchar(30) null,
size int null,
creationdate int null,
creationtime int null,
lastwrittendate int null,
lastwrittentime int null,
lastaccesseddate int null,
lastaccessedtime int null,
attributes int null
)
INSERT #FILE EXEC master..xp_getfiledetails @.FILE_NAME
MJKulangara
http://sqladventures.blogspot.com|||Hi
You could do the file copy and comparison using a DTS package and an ActiveX
script. See
http://www.sqldts.com/default.aspx?292
John
"Edward Low" <wc_low@.hotmail.com> wrote in message
news:eRZ8g9qLGHA.3936@.TK2MSFTNGP12.phx.gbl...
> Dear All,
> Periodically, i need to copy a file from the source folder to destination
> folder. the source file will be updated from time to time. Before copying
> the file to destination folder, i need to compare the modified date of the
> file, mean only copy the file to destination folder if the source file is
> updated. How can we do this in SQL?
> I will use the xp_cmdShell to copy the file. If use the xp_getfiledetails,
> how can i get the information return fromt the stored procedure to check
> the modified date? Or, is there any better method of doing this?
> I am using SQL Server 2000.
> Thanks & Best Regards,
>
> Edward
>

Compare Standard Evalulation to Standard license Version

Hi All,

Good Evening.

I need a comparision between Evaluation copy and Standard license version of SQL Server 2005 /SSIS.

I'm assigned a task to evaluate SSIS and migrate a project to SSIS.

I have downloaded SSIS evaluation version and started working on that.

Now i'm being posed questions for the complete functionality of the tool...

- Whether it has a Bulk load trasformation ?

- Whether the evaluation version contain all the features of a standard license version ?

I need to submit a consolidated report for SSIS in comparision to the current ETL tool features.

Can you please let me is there any considerable features not given with a evaluation copy ?

Thanks in advance,

Suresh N

As I understand it, the only difference between trial versions of SQL Server and the retail versions of the same edition (in this case, Standard Edition) is the 120-day time bomb on the software. All of the features should be identical. WIth that said, I've beeen unable to find supporting documentation on this point, so please do not take my word as gospel here.

Another option is for you (or your employer/client) to get an MSDN subscription. With this you can use the 'real" SQL Server Standard Edition and not worry about any differences with the trial edition.

One last point: SSIS has a Bulk Load task, but for most situations you actually want to use the Data FLow task when you're bulk loading data, as it has phenominal performance and many more capabilities than a simple bulk load.

|||I think that most of the trial versions include the Enterprise Edition feature set. You can see the comparison between editions here: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx|||

Thanks much Matthew.

Regards,

Suresh N

Compare data in Tables

I am trying to determine the changes an application makes to a database.
The plan is to copy the existing schema (active) to a reference schema, run
the application and then diff the table data between the reference and the
a active schema. I have found one software vendor who has a tool to do
this, but it will only do one table at a time (interactively); I have more
then 300 and will run this a few times.

One other way of determining the changes, I guess, would be to log all sql
statements (in order), but I don't know how to do this (either).

Any pointers would be greatly appreciated.

LeoIf its changes you are looking for try running SQL Profiler against it.
Filter for where writes > 0. Another solution would to write a script to
doing the all tables comparison. Something like

Create a table with tablename, checksumbefore, checksumafter, rowsbefore,
rowsafter, numberofrowsdiff
Write a cursor of all user tables
For each table
Get count of rows with select count(*)
calc the CHECKSUM of each row and write to individual temp tables
select count(*) from checksumafter where checksum not in checksumbefore
insert/update the table

By the end of the script you should have indentified which tables change and
by how much.

"Leo" <leolist@.optushome.com.au> wrote in message
news:Xns9639579AFB3Bleolistoptushomecoma@.211.29.13 3.50...
>I am trying to determine the changes an application makes to a database.
> The plan is to copy the existing schema (active) to a reference schema,
> run
> the application and then diff the table data between the reference and the
> a active schema. I have found one software vendor who has a tool to do
> this, but it will only do one table at a time (interactively); I have more
> then 300 and will run this a few times.
> One other way of determining the changes, I guess, would be to log all sql
> statements (in order), but I don't know how to do this (either).
> Any pointers would be greatly appreciated.
> Leo|||There is a software tool that can do this for you called DB Ghost
(www.dbghost.com). Its very fast at comparing data and can be run from
the command line for a fully automated process. A single command will
do any number of tables that you desire.

It's also the cornerstone of a full change management solution for SQL
Server databases i.e. it can build, compare and synchronize the schema
AND data directly from drop/create scripts held in a source control
system.

I highly recommend you check it out.|||"Malcolm" <malcolm.leach@.innovartis.co.uk> wrote in
news:1113551521.457015.133450@.l41g2000cwc.googlegr oups.com:

> DB Ghost

DB Ghost did exactly what I needed.

Thanks for your advice

Leo

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Compare csv file layout to SQL Server table

I am creating a facility whereby you can select a source file (.csv), and a target (SQL table).

Then I call a DTS to copy the csv file to the SQL table.

How can I validate that the two files have the same number of columns ?

Moved to SQL Server Tools forum|||In order to check the number of rows you need to work on DTS with workflow, refer to books online for more information and you can schedule the DTS package a SQLagent job by right-click on selected package.|||I need to validate that the correct file has been selected before scheduling the DTS package. This is why I need to do it in my VB.Net Windows application.|||

You can write a custom Script Task inside the SSIS package that will open the file and check how many columns it has. You can utilize Excel object model for that or just go with regular .NET IO libraries.

Hope that helps.

Maciek Sarnowicz

Command works in DOS but not in xp_cmdshell

Can anyone give clues as to why the same copy command works in
DOS and not in xp_command shell.I am an admin in both servers and
in the database.

The results indicate one file is copied but no file is found
at destination.

Your input is appreciated

Vincento

copy "D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\back1.
txt" "\\corp156\d$\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\"

works just fine but

use
master
exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\back1.
txt" "\\corp156\d$\Program Files\Microsoft SQL
Server\MSSQL\BACKUP\QMISDEV\"'What is the user context that the script is running in? Usually it is
system (I think), which isn't going to have access rights to corp156.

Zach

Vincento Harris wrote:
> Can anyone give clues as to why the same copy command works in
> DOS and not in xp_command shell.I am an admin in both servers and
> in the database.
> The results indicate one file is copied but no file is found
> at destination.
> Your input is appreciated
> Vincento
>
>
> copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"
> works just fine but
> use
> master
> exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"'|||"Vincento Harris" <wumutek@.yahoo.com> wrote in message
news:2fa13ee7.0410130938.3a4db7ec@.posting.google.c om...
> Can anyone give clues as to why the same copy command works in
> DOS and not in xp_command shell.I am an admin in both servers and
> in the database.
> The results indicate one file is copied but no file is found
> at destination.
> Your input is appreciated
> Vincento
>
>
> copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"
> works just fine but
> use
> master
> exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\back1.
> txt" "\\corp156\d$\Program Files\Microsoft SQL
> Server\MSSQL\BACKUP\QMISDEV\"'

I tried this out, and it worked fine for me (I didn't test using a share,
but I did test using folder names with spaces) - was the result you saw "1
file(s) copied"? Or did you run this as a scheduled job perhaps? When a
sysadmin executes xp_cmdshell, it runs as the MSSQL service account, so you
might want to check that account's permissions also.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message news:<416d9609$1_3@.news.bluewin.ch>...
> "Vincento Harris" <wumutek@.yahoo.com> wrote in message
> news:2fa13ee7.0410130938.3a4db7ec@.posting.google.c om...
> > Can anyone give clues as to why the same copy command works in
> > DOS and not in xp_command shell.I am an admin in both servers and
> > in the database.
> > The results indicate one file is copied but no file is found
> > at destination.
> > Your input is appreciated
> > Vincento
> > copy "D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\back1.
> > txt" "\\corp156\d$\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\"
> > works just fine but
> > use
> > master
> > exec xp_cmdshell 'copy "D:\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\back1.
> > txt" "\\corp156\d$\Program Files\Microsoft SQL
> > Server\MSSQL\BACKUP\QMISDEV\"'
> I tried this out, and it worked fine for me (I didn't test using a share,
> but I did test using folder names with spaces) - was the result you saw "1
> file(s) copied"? Or did you run this as a scheduled job perhaps? When a
> sysadmin executes xp_cmdshell, it runs as the MSSQL service account, so you
> might want to check that account's permissions also.
> Simon
I appreciate your answers guys .It does work on the same server for me too.
When the mssqlserver was under local admin account
The result was " Access denied"

When I made this account part of the admin account
The result was "1 file copied" and it actually works

So my problem was not making mssqlservice account an admin
of the //server (//corp156)

Thanks again for the exceptional contributions I always get from
this group

Vincento

comma delimited list

Hello,
I have created a small query that will output a list of email addresses that
are separated by commas. I plan to copy and paste the list into a email
invite field (send to: field for an email app). But what is happening is tha
t
only one address appears in the field when I paste because of the added
spaces between the names, example currently outputs like:
blahblah@.blah.com,
blahblah@.blah.com,
blahblah@.blah.com
What I need is it to output like this:
blahblah@.blah.com,blahblah@.blah.com,blahblah@.blah.com
Here is the small query that I am using to extract the emails.
SELECT RTRIM(LTRIM(EMAIL_ADDRESS)) + ',' AS [Name]
FROM evPRUclinicala
WHERE CREATE_DATE BETWEEN '4/1/06' AND '4/30/06'
AND EMAIL_ADDRESS IS NOT NULL
Any help would be appreciated and thanks in advance.
Message posted via http://www.webservertalk.comIn general, a recommended approach is to extract the resultset outside the
server and massage the data to appropriate display format using some client.
Regarding the alterantives for doing it at the server, you can check out the
following links:
( For SQL 2005 only )
http://groups.google.com/group/micr...br />
9b9b968a
( For SQL 2000 & 2005 )
http://groups.google.com/group/micr...br />
6dd9e73e
Anith|||Anith Sen wrote:
>In general, a recommended approach is to extract the resultset outside the
>server and massage the data to appropriate display format using some client
.
>Regarding the alterantives for doing it at the server, you can check out th
e
>following links:
>( For SQL 2005 only )
>http://groups.google.com/group/micr...r />
a9b9b968a
>( For SQL 2000 & 2005 )
>http://groups.google.com/group/micr...r />
66dd9e73e
>
Thanks for the direction, but I am still have some problems. I elected to us
e
the cursor version of your solution: see below
DECLARE @.temptable TABLE ( list VARCHAR( 8000 ) )
SET NOCOUNT ON
DECLARE @.PRUemail VARCHAR( 1000 ), @.emailNext VARCHAR (100)
DECLARE c CURSOR FOR
SELECT EMAIL_ADDRESS
FROM evPRUclinicala
ORDER BY EMAIL_ADDRESS
OPEN c
FETCH NEXT FROM c INTO @.emailNext
SET @.PRUemail = @.emailNext
WHILE @.@.FETCH_STATUS = 0 BEGIN
IF @.emailNext > @.PRUemail BEGIN
INSERT @.temptable SELECT @.PRUemail
SELECT @.PRUemail = @.emailNext
END ELSE
SET @.PRUemail = COALESCE( @.PRUemail + ',', SPACE( 0 ) ) +
@.emailNext
FETCH NEXT FROM c INTO @.emailNext
END
INSERT @.temptable SELECT @.PRUemail
CLOSE c
DEALLOCATE c
SET NOCOUNT OFF
SELECT * FROM @.temptable
But I am still getting a vertical list with no commas. Can you tell me what
I
am doing wrong? Thanks
Message posted via http://www.webservertalk.com|||>> I elected to use the cursor version of your solution:
Why? It is a bad choice to begin with.
I have no idea how the table evPRUclinicala is structured or what the nature
of the data is. Can you post your table DDLs & a few sample data? For
details, refer to: www.aspfaq.com/5006
Anith|||"Jay via webservertalk.com" <u7124@.uwe> wrote in message
news:601bd1f4c21b5@.uwe...
>.
> Can you tell me what I am doing wrong?
>.
Yeah, your not using Rac :)
www.rac4sql.net/onlinehelp.asp?topic=236|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
What would have been a better choice? If I should use a different method tha
n
I would appreciate your advise here. If not I will get you the DDLs.
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1|||Cursors are generally performance hogs. In most cases, it is wiser to
utilize a set based solution in SQL where a set of rows are manipulated as a
whole rather than doing it one row at a time.
As a long term solution, consider returning the resultset to the client and
manipulate the data using a client application program -- this allows the
stored procedure to be sufficiently generic, eliminates the need to use
server's processing power for string manipulations & loop-based constructs
and leverages the string functionalities of the client language, which in
most cases are more versatile than t-SQL.
If you are convinced to use the server for such stuff, consider the table
valued UDF approach which might be better performing for medium sized
datasets. For SQL 2005, the XML kludge might be faster, though we do not
have much useful benchmarks with this approach.
Alternatively, for large volume transactions of this nature, consider using
3rd party tool's like RAC mentioned in Steve's post.
Anith|||Anith Sen wrote:
>Why? It is a bad choice to begin with.
>
>I have no idea how the table evPRUclinicala is structured or what the natur
e
>of the data is. Can you post your table DDLs & a few sample data? For
>details, refer to: www.aspfaq.com/5006
>
Ok I will look at the other solutions rather than the one chosen. Thanks
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200605/1