Running agent jobs from command line

Hi,
How can i run a SQL Server Agent Job from the command line on the same box? I am using SQL Server 2000.
ThanksCheck out the SQL Books Online for Command Line execution of SQL Agent

The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS package can be stored in the Microsoft SQL Server msdb database, a COM-structured storage file, or SQL Server Meta Data Services.

Syntax
dtsrun
[/?] |
[
[
/[~]S server_name[\instance_name]
{ {/[~]U user_name [/[~]P password]} | /E }
]
{
{/[~]N package_name }
| {/[~]G package_guid_string}
| {/[~]V package_version_guid_string}
}
[/[~]M package_password]
[/[~]F filename]
[/[~]R repository_database_name]
[/A global_variable_name:typeid=value]
[/L log_file_name]
[/W NT_event_log_completion_status]
[/Z] [/!X] [/!D] [/!Y] [/!C]
]

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password|||So i have to create a DTS package to run my job and then use dtsrun to run that package?|||Sorry I sent you the wrong script. I'll be back with the correct one shortly. Mark|||You could create a stored procedure to run the job. Example:
(You may be able to pass the name through as a parameter ??)

CREATE PROCEDURE sp_RunJob
AS

BEGIN

EXEC msdb..sp_start_job @.job_name = 'The job name'

END

You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax.

Then from the command line, create a .bat file and put the following isql command synax into the .bat file:

isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

This will actually produce some output into the returncode.txt file, which might be useful. I'm not sure whether you'll have to return a value back out of the stored procedure or not. I've not tried. My sample above it a cut down version. But anyhow there should be enough above to give you the idea in terms of syntax etc.

You would then just execute the .bat file on your server/pc etc, perhaps via a scheduler etc...

Anyhow a search on SQL Books Online will give you all the details. Search for isql , isqlw etc..|||Cheers Mark.

Run SQL Script from Web App

Hi,

I have a asp.net 2.0 web app that needs to allow the user to run sql scripts on a database. I provided the user with a large text box which allows them to cut and past their script in. However the script contains complex SQL which cannot be handeled in the normal way of passing a string to a connection object and doing a ExecuteNonQuery.

The scripts contains words such as "GO" which makes teh script fail. Is there a wrapper or method for sending cuch SQL statment. Is there a stored procedures I can call or something that will allow me to excute the TSQL exactly in teh same way Query Analyser does?

Many thanks in advance

PS. yes im aware of the security implications of doing this, I have put the relevant security measures in place

check out the OSQL utility.|||

I am not sure but I think StoredProcedure can not do this because it executes SQL querys. I say I am not sure but if there is some body who is sure 100%, he should let us know:-)

|||

GO is not a valid T-SQL command, so no, you can't send it to the SQL Server and expect it to work. GO is a command to Query Analyzer/osql/sqlcmd/Management Studio, and if you want to emulate it's functionality, your program will have to handle it or call one of the above utilities to do it on your behalf.

Something like (pseudo code here):

dim cmds as string() =string.split({Your query},vbCRLf & "GO" & vbcrlf)

for each cmd in cmds

sqlcmd.commandtext=cmd

sqlcmd.executenonquery

next

|||

Hi

Thanks to everybody for their replies, I have been looking into OSQL Utility approach.

From my basic understanding of OSQL it can only be run from the Command Prompt so their for I would need to create a batch file. Will ASP.NET 2.0 let me call a batch file from within my web application?

Or is there another approach apart from using a batch file?

Many thanks in advanve

RUN SQL 2005 SSIS Package on SQL 2000 BOXES?

I have a package designed and working correctly for months now. My challenge now is to run this package on a box that does not have SQL 2005 SSIS install. It has SQL 2000 installed.

My question is - Can I compile the SSIS into EXE or any other kind of DTS package to run on a none-SQL 2005 box?

Thanks!

--Jon

You don't need ANY version of SQL Server (i.e. the database engine) installed in order to run a SSIS package. You just need SSIS installed.

And no, its not possible to run it without having SSIS installed. That's what you pay the money for.

-Jamie

run both enterprise and msde on same box

Is it possible to run both MSDE and enterprise edition (sql server 2000)
on the same box?
I am working on an application that will include MSDE, and I'd like to
test it on my developing machine that is already running Enterprise. I
could just shut off Enterprise, but the MSDE needs to be a subscriber,
so they both need to be running.
Thanks,
-Casey
hi,
C Williams wrote:
> Is it possible to run both MSDE and enterprise edition (sql server
> 2000) on the same box?
> I am working on an application that will include MSDE, and I'd like to
> test it on my developing machine that is already running Enterprise. I
> could just shut off Enterprise, but the MSDE needs to be a
> subscriber, so they both need to be running.
yes, it can be done..
up to 16 instances can be installed on the very same machine, resources
permitting :D
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

RTF Interpretation

I have text box that is set to RTF Interpretation. The problem arises when the field that this textbox is linked to has no data. Instead of being suppressed like I want it to, it display's an RTF string. Can I suppress this field when that field that is linked to has no data. All my attempts have failed. Any help would be greatly appreiciated.Right click on that text goto format section and in the suppress option, write this formula

if isnull([field])=true then true