xp_cmdshell alternative

Last Post 20 May 2009 05:46 AM by MattiasLind. 3 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
desperado
New Member
New Member

--
09 Dec 2008 06:28 AM
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
desperado
New Member
New Member

--
09 Dec 2008 07:06 AM
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.
desperado
New Member
New Member

--
10 Dec 2008 10:11 AM
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?
MattiasLind
New Member
New Member

--
20 May 2009 05:46 AM
Check out the tutorials on SSIS together with the samples.

Create a SSIS package that extracts the data from the database and creates the file.

I believe the tutorial Creating a Basic package will do this for you.

You will need a variable for the filename and configure the "Flat File Data Source"-Connectionsstring with an expression setting it to the variable.

Check out the tutorial, it'll rock you and you'll see the light!

Then deploy the package to the server of your choice and schedule it with SQL Agent.


Acceptable Use Policy
---