Not allowed to use xp_cmdshell

Question

Post reply

Not allowed to use xp_cmdshell

terry hayduck

Grasshopper

Points: 16
More actions
October 23, 2012 at 6:04 am

#263611

We are not allowed to use xp_cmdshell for security reasons.
I currently extract data (real-time) from Oracle tables in my sql stored procedures using linked servers.
I was told that I might be able to improve performance by using ssis packages to to do this.
Below is a example on how I do this :
USE [RMReports]
GO
/****** Object: StoredProcedure [NonFreight].[procRptLstTeam] Script Date: 10/23/2012 06:48:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [NonFreight].[procRptLstTeam]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql VARCHAR(MAX)
--Build #Team Sql table
CREATE TABLE [NonFreight].[#Team]
(
[TeamCode] VARCHAR(10),
[TeamName] VARCHAR(30)
)
ON [PRIMARY]
SET @Sql = 'SELECT DISTINCT C.TEAM as TeamCode, C.TEAM as TeamName
FROM GPCOMP1.GPCOLL C'
SET @Sql = N'INSERT INTO [NonFreight].[#Team]
SELECT * from openquery
([GPNFE], ''' + Replace(@Sql, '''', '''''') + ''')'
EXEC (@Sql)
-- Dropdown query
SELECT TeamCode,
TeamName,
Count(*) OVER(PARTITION BY NULL) AS TeamCount
FROM NonFreight.#Team
WHERE TeamName != ''
ORDER BY TeamName
END
Is there another way I could execute a ssis package with parms via my sql stored procedure in place of xp_cmdshell ?
My stored procedures can be called by many users at the same time through out the day.
Also, I am not sure if you can call a ssis package via a sql stored procedure and create temp tables that can be then used in the calling sql stored procedure.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Abu Dina SSChampion Points: 14155 More actions · Answer 1

See this: http://social.msdn.microsoft.com/Forums/en/transactsql/thread/cf3a9b8c-90f2-410c-978b-c1d995f7e0f9

---------------------------------------------------------

It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]

Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn

terry hayduck Grasshopper Points: 16 More actions · Answer 2

Thanks.

For right now, I will continue with the way I am doing it now.

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

terry hayduck (10/25/2012)
Thanks.
For right now, I will continue with the way I am doing it now.

Not that it matters but I agree. I'm not so sure that an SSIS package would be any faster than the OPENQUERY method you used. You also don't have to mess around with an SSIS package this way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)