anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:
> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>sql

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could d
o
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
--
Paul G
Software engineer.More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
--
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>

anyone know the best way to do this

Hi I am currently performing the following tasks manually and was wondering
if anyone might suggest if a script,dts or possibly .net application could do
this.
1. get file name from user
2. copy file from one server to another (path does not change so could be
hardcoded)
3. insert the name of the file in a database table (SQL2000).
thanks.
Paul G
Software engineer.
More info needed here - what interface does the user have to the
application - where are they giving the file name to you? In a SQL
context or Application context?
If assumptions are kept simple.....
1. depends on the application, but shouldn't be too hard
2. USE Master
GO
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
3. USE Userdb
GO
INSERT table
select 'filename'
Maybe write a SP with a filename parameter and execute #2 & #3 with the
same SP.
|||Hi thanks for the information. Sounds like you can perform a copy with the
command you provided.Think I will just use a .net application that calls a
stored procedure passing the name of the file as an input to the procedure.
will use this command as you provided.
EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
"\\server2\filepath\filename.xyz"'
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> More info needed here - what interface does the user have to the
> application - where are they giving the file name to you? In a SQL
> context or Application context?
> If assumptions are kept simple.....
> 1. depends on the application, but shouldn't be too hard
> 2. USE Master
> GO
> EXEC xp_cmdshell 'copy "\\server1\filepath\filename.xyz"
> "\\server2\filepath\filename.xyz"'
> 3. USE Userdb
> GO
> INSERT table
> select 'filename'
> Maybe write a SP with a filename parameter and execute #2 & #3 with the
> same SP.
>
|||Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
(or application) will have to have the correct OS permissions on the
aforementioned directories (I believe)....Can anyone else confirm?
Also - I wouldn't give your users a chance to input a command...just
give them the ability to enter a filepath and you hardcode the
command/sp in your application. Otherwise, they could do nasty things
with xp_cmdshell, as it's an open window into the OS.
|||ok thanks was thinking of hardcoding the path and then building the rest of
the string with the file name that is passed into the stored procedure. Was
also thinking of somehow testing the filename input (probably in the .net
app) and not call the stored procedure unless a valid filename is supplied.
Will most likely only be 1 user other than myself but also thinking they
would have to have permissions to the directories, source and destination.
Paul G
Software engineer.
"unc27932@.yahoo.com" wrote:

> Yes - check out BOL on xp_cmdshell. Beware though - the logged in user
> (or application) will have to have the correct OS permissions on the
> aforementioned directories (I believe)....Can anyone else confirm?
> Also - I wouldn't give your users a chance to input a command...just
> give them the ability to enter a filepath and you hardcode the
> command/sp in your application. Otherwise, they could do nasty things
> with xp_cmdshell, as it's an open window into the OS.
>

Any way to check if a log file (.txt) already exists before BCPing a file?

I want to have one log file for each task ran within a job. There are several tasks for each job that may be handled by different people so I would like to have a running log file on the network that can be checked by supervisors. I know how to creat the log file:

master..xp_cmdshell bcp "SELECT * FROM ##logFile" queryout "c:\log.txt"

Problem is next time a task is run, the original text file will get written over by the new one. I came up with a solution for that by using bcp to export a temporary log file, then append that file to my existing log file, then kill the temporary file and that works fine:

master..xp_cmdshell copy log_temp.txt + log.txt

The problem is the second command obviously fails if the main log file does not exist.

Ideally I would like to check to see if the log file exists, if not create it, if it does exist, append to it.

Having a master log table stored in SQL has been suggested and shot down. They want one text file for job ran.

Any help would be greatly appreciated.

Thanks.

Tim

Write VBScript code (for example) and run this in a SQLAgent job. Don't use xp_cmdshell for these sort of things. It is less flexible and a security vulnerability. It takes few lines of code in VBScript to check for file. Or you can write a CMD script and run it in a SQLAgent job.

Any free sql backup compression utility ?

Hi,
I perform backup by maintenance plan tasks and think about add additional
task to compress backups and delete original backups.
Do you know some script base resolution (winzip, winrar, 7zip...)?
Regards,
anxcomp
From what I understand there is a command line version of winzip. But that
does not give you the same thing as using one of the tools from Red-gate or
Quest. They compress the backup as it is being read and when written it is
already compress. You would have to do it in two steps and it would require
much more CPU & Disk I/O than with Red-Gate. Not to mention you now have to
have extra disk space to store the initial backup before you compress it.
The same is true for restores. It makes the process much longer and more
difficult than it needs to be.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:F6756C86-51F1-4344-85ED-A20ADA7E32F8@.microsoft.com...
> Hi,
> I perform backup by maintenance plan tasks and think about add additional
> task to compress backups and delete original backups.
> Do you know some script base resolution (winzip, winrar, 7zip...)?
> --
> Regards,
> anxcomp
|||Hello,
This is good for me, I don't want live compression and decompression. My
databases are on simple mode I perform only full nightly backup.
Do you use some scripts (vbs, sql with xp_cmdshell or something...) to
perform this task?
Thank you inadvice
Regards,
anxcomp
|||What does the fact this is in simple mode have to do with this? In any case
have a look here:
http://www.winzip.com/prodpagecl.htm
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"anxcomp" <anxcomp@.discussions.microsoft.com> wrote in message
news:6195075F-3BB9-4D62-8088-A107309DC75E@.microsoft.com...
> Hello,
> This is good for me, I don't want live compression and decompression. My
> databases are on simple mode I perform only full nightly backup.
> Do you use some scripts (vbs, sql with xp_cmdshell or something...) to
> perform this task?
> Thank you inadvice
> --
> Regards,
> anxcomp