Not allowed to use xp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Not allowed to use xp_cmdshell

Not allowed to use xp_cmdshell

Rate Topic

Display Mode

Topic Options

Author

Message

terry hayduck

Posted Tuesday, October 23, 2012 6:04 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 2, Visits: 129

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.

Post #1375991

Abu Dina

Posted Tuesday, October 23, 2012 6:20 AM

SSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 9:30 AM
Points: 484, Visits: 2,122

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

-----------------------------------
http://www.SQL4n00bs.com

Post #1375997

terry hayduck

Posted Thursday, October 25, 2012 5:48 AM

Forum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 10:03 AM
Points: 2, Visits: 129

Thanks.

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

Post #1376937

Jeff Moden

Posted Thursday, October 25, 2012 10:05 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:30 PM
Points: 32,893, Visits: 26,770

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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1377382

« Prev Topic | Next Topic »

Permissions