[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 436
  • Last Modified:

Use a stored procedure to call xp_cmd and run a dtsx package

I created a stored procedure with the command below. I want this stored procedure to execute a dtsx package using xp_cmdshell. The procedure was successfully created but when i try to run it the query just runs, never finishes. The dtsx package works, i've used it many times using xp_cmdshell, so i figured it would work through a SP so that i can call it from an application. Here is how i'm calling it

use Forensicon_Max105
execute uspWorkTick

I have never used SPs before so please be clear when offering suggestions or questions.

Thank you.
-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Yaniv Schiff
-- Create date: 3/16/2011
-- Description:	Import SP WorkTick Data
-- =============================================

alter PROCEDURE uspWorkTick 
	-- Add the parameters for the stored procedure here
	@Path nvarchar(50) = 'c:\ImportWorkTickDB.dtsx'
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	print @Path

	exec Forensicon_Max105..xp_cmdshell @Path
END
GO

Open in new window

0
Yaniv Schiff
Asked:
Yaniv Schiff
1 Solution
 
Daniel_PLDB Expert/ArchitectCommented:
Try changing line 35 :) :
 
declare @sql nvarchar(1000)
set @sql='exec xp_cmdshell ''dtexec /f'+' "'+@Path+'"'''
select @sql
exec sp_executesql @sql

Open in new window

0
 
Yaniv SchiffDirector of Digital Forensics Author Commented:
That worked perfectly!

Thanks
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now