April 6, 2015 at 6:37 pm
I've created this stored procedure to run two DTS packages which pull in data from two Excel files. I'd like to supress the Results tab since the results from the DTS packages are fed to it. I'd like to keep the Messages tab visible. Ideas?
CREATE PROCEDURE [dbo].[ImportHelpDeskTickets]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
DECLARE @ProblemCount int
DECLARE @IncidentCount int
DECLARE @SSISFile AS VARCHAR(2000)
execute PurgeRecords
SET @SSISFile = '""C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "[Full File Path Deleted]""'
EXEC master..xp_cmdshell @SSISFile
SET @SSISFile = '""C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn\DTExec.exe" /FILE "[Full File Path Deleted]""'
EXEC master..xp_cmdshell @SSISFile
execute CleanupProblems
SELECT @ProblemCount = Count(Ticket) FROM Problems
SELECT @IncidentCount = Count(Ticket) FROM ProductionSupportReporting
Print 'Problems :' + CAST(@ProblemCount as varchar(5))
Print 'Incidents: '+ CAST(@IncidentCount as varchar(5))
END
GO
For those of you like me who will spend 8 hours trying to figure out how to get this working... Double up on the quotes as shown above. Also, be certain that NT SERVICE\MSSQLSERVER has been granted appropriate rights to the folders with the the saved packages and the Excel files. You may also need to download the Access Database Engine.
Keywords: run dts package from stored procedure xp_cmdshell
'C:\Program' is not recognized as an internal or external command, operable program or batch file.'
Assorted Links From Today's Exercise.
http://www.microsoft.com/en-us/download/details.aspx?id=13255
https://msdn.microsoft.com/en-us/library/ms175046.aspx
http://www.microsoft.com/en-us/download/details.aspx?id=13255
http://www.sqlservercentral.com/Forums/Topic694445-149-1.aspx
April 6, 2015 at 11:27 pm
Quick suggestion, declare a table variable and do an insert-exec, the output of the exec goes in the variable, then discard it.
😎
April 12, 2015 at 3:16 pm
Done. Fixed. Working. Thanks.
April 13, 2015 at 9:12 am
To suppress the messages thrown by xp_cmdshell extended Stored Procedure use the no_output optional parameter:
exec xp_cmdshell 'DIR *.exe', no_output
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply