Execute sql script in the job

Hi!
I want to run a sql script in the job using T-SQL against a user
database. I tried to copy and paste the whole script in the T-SQL window
of the job but bc of length of the script (it is too long), it truncated
half of the script. How do I do that? Can we call this script using
xp_cmdshell and execute it? Is that possible? Any ideas?
The other option is to run it in the Query Analyzer which is a manual
effort and I do not want to do it bc this process is going to be ongoing
process and it is better if we make it automated.
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:#6MfyY6XFHA.2688@.TK2MSFTNGP10.phx.gbl...
> Hi!
> I want to run a sql script in the job using T-SQL against a user
> database. I tried to copy and paste the whole script in the T-SQL window
> of the job but bc of length of the script (it is too long), it truncated
> half of the script. How do I do that? Can we call this script using
> xp_cmdshell and execute it? Is that possible? Any ideas?
> The other option is to run it in the Query Analyzer which is a manual
> effort and I do not want to do it bc this process is going to be ongoing
> process and it is better if we make it automated.
> Thanks for your help!
> *** Sent via Developersdex http://www.examnotes.net ***
Why not put the T-SQL code in to a stored procedure and then have the job
call the stored procedure.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks Rick but we do not want to create stored procs:
1) avoid creating objects.
2) these scripts will also apply against other dbs ..so again it would
be a manual effort to create procs first and then run it under a job.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:#M4q9L8XFHA.4000@.TK2MSFTNGP10.phx.gbl...
> Thanks Rick but we do not want to create stored procs:
> 1) avoid creating objects.
> 2) these scripts will also apply against other dbs ..so again it would
> be a manual effort to create procs first and then run it under a job.
> Any other ideas?
> *** Sent via Developersdex http://www.examnotes.net ***
Perhaps a DTS package then. You could call the package from a job.
Rick Sawtell
MCT, MCSD, MCDBA

Execute Process Task Error on copy

Hi,

I am trying to run a very simple copy command from an execute process task:

copy O:\myFolder\myFile*.txt D:\myFolder

I have a working directory set also.

However, there is an error icon on the task, and if I attempt to run it I get the following error:

Error at Execute Process Task [Execute Process Task]: File/Process "" does not exist in directory "copy O:\myFolder\myFile*.txt D:\myFolder".

Error at Execute Process Task: There were errors during task validation.

This command works fine from the command line. What am I doing wrong here?

Thanks

O:\myFolder\myFile*.txt D:\myFolder

The above should go in the arguments parameter box if you're not doing that.|||

It seems that it's looking for an executable, like a .bat file. It doesn't seem to recognize the dos copy command.

for example:

Executable: copy

Arguments: o:\myFolder\myFile.txt D:\myFolder

but it still complains that copy is not an executable.

So, it seems I have to put this in a batch file, which seems dumb, or I need to use the File System Task, but I don't know how to specify which files to copy using this method.

?

|||Instead, try using the full path to xcopy

c:\windows\xcopy.exe|||

xcopy is not installed

where can one find just the "copy" command exe?

|||

sadie519590 wrote:

xcopy is not installed

where can one find just the "copy" command exe?

xcopy is not installed? You're kidding me? It's distributed with Windows.

There is no "copy.exe" as it's an internal command within "command.exe or cmd.exe."|||

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

|||

sadie519590 wrote:

Actually you are correct. I was thinking of robocopy.

At any rate, SSIS is complaining it can't find the xcopy exe. Never mind at this point, I'm not going to use it for now.

But for future use, it seems to me that there should be a way to use the File System task to specify file names with wildcards?

You need to specify the full path to xcopy.exe in the Executable parameter..|||But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.|||

sadie519590 wrote:

But I'm still wondering if I can use wild cards to specify files names in the File System Task when deleting files? Seems like this would be easier approach if possible.

I think the way to do this is to use a foreach loop to scan the source directory for the files you want to copy (you can use a wildcard there) and then have it populate some variables (source filename, etc...). Then, inside the foreach loop, you use a filesystem task to perform the operation for each file found by the foreach loop.

Does that make sense?|||Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?|||

sadie519590 wrote:

Yes, thanks. I'm surprised there's no built-in way to do this. Product request, I guess?

Sure thing! http://connect.microsoft.com/sqlserver/feedback