Solved

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

Posted on 2011-03-16
2
431 Views
Last Modified: 2012-05-11
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
Comment
Question by:Forensicon
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 14

Accepted Solution

by:
Daniel_PL earned 500 total points
ID: 35150238
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
 

Author Closing Comment

by:Forensicon
ID: 35151242
That worked perfectly!

Thanks
0

Featured Post

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Loops and updating in SQL Query 9 66
Job - date manual 1 43
GeoClustering  and AOG 25 43
How to add a suffix to a value in a column based on the value in another column 4 32
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question