SQL Server Magazine

I need help here...
xp_cmdshell is disabled and I need to run this type of code within a SQL Server Agent Job...

Can anyone help me on how to use SQLCMD or similar code within a SQL Server Agent job without having to use xp_cmdshell?

Thanks

DECLARE
@Date char(8),
@SQLCmd nvarchar(360)
@ServerName varchar(100),
@DatabaseName varchar(100),
@QueryStmt varchar(1000),
@TargetFile varchar(256)


SELECT
@ServerName = 'DEVSERVER',
@DatabaseName = 'DEVDB',
@QueryStmt = 'EXEC uspMyProc ''2005'', ''2008-04-01 00:00:00.000'',''2009-03-31 00:00:00.000''',
@TargetFile = '\\FILESERVER\FILESAHRE\Budget_',
@Date = convert(varchar(8), getdate(), 112),

SELECT @TargetFile = @TargetFile + SUBSTRING(@Date,1,4) + '_' + SUBSTRING(@Date,5,2) + '_' + SUBSTRING(@Date,7,2)+ '.txt'

SELECT @SQLCmd = 'exec master.dbo.xp_cmdshell ''sqlcmd -S ' + @ServerName + ' -E -d ' + @DatabaseName + ' -Q "' + @QueryStmt + '" -w 300 -o "' + @TargetFile +'"'''

EXEC sp_executesql @SQLCmd

Nice...
Excuse my stupidity but I need a kick start.
Can you give me a sample code based on what I have provided?
This will avoid my several hours of searching.
Much appreciated.

To answer your first question, we're using SQL 2005 SP2
As for your linked server option, I wish it were that simple.
We need to output to file through a SQL Agent job.
The only way we've been able to easily do this was through a SQLCMD (ISQL), passing parameters by building the statement executed by xp_cmdshell.

Using SQLCMD from SSMS query window is an option, however this option is only good when we are logged on and running SSMS. This needs to be done through a scheduled job.....

So... apart from hunting in Books On Line for a viable proposal, how can I build an ActiveX script that would replicate what I need to do as indicated in my opening remarks?