A View which triggers an Executable? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

A View which triggers an Executable?

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-06-03 : 14:18:50

I must make a view which triggers an executable, retrieves the exe results and displays the results. I really don't care which architecture I use.

I figure it can be View => TVF => EXE, but maybe I'm not thinking outside the box enough.

The EXE can pipe XML to a temp file; the text file can be bulked back into a variable with

--READ THE XML FILE INTO A VARIABLE
SELECT @X = CAST(BULKCOLUMN AS XML) FROM OPENROWSET ( 
   BULK '%temp%\Super_TVF.xml' ,SINGLE_BLOB 
) AS XMLDATA;

I can parse the XML from there into a nice output of my choice.
That all works just fine.

However, the TVF cannot launch the EXE with the following statement because of an error:

INSERT INTO @RetTable EXECUTE @iRet = XP_CMDSHELL @CMD;
Msg 443, Level 16, State 14, Procedure Super_TVF, Line 34
Invalid use of a side-effecting operator 'INSERT EXEC' within a function.

Any creative twisted minds out there

who would care to give me a workaround?

~ Shaun Merrill
Seattle area

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-03 : 14:22:20

You'll need to do this from a stored procedure as it isn't allowed in a view or TVF.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-06-03 : 14:47:08

Well, I still need to base a report off of it, which means I need a view to do the launching.
Do you recommend I try something like this?

create view dbo.Super_View AS
BEGIN
  IF OBJECT_ID('tempdb.dbo.#JobInfo') IS NOT NULL
  DROP TABLE #JobInfo

  SELECT * INTO #JobInfo
  FROM OPENROWSET('SQLNCLI', 'server=(local);trusted_connection=yes', 
  'set fmtonly off exec msdb.dbo.sp_help_job') 
END

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-03 : 14:51:28

You can't do that with a view. A view can only contain a select statement and can't reference OPENROWSET anyway.

Most reporting software allows you to use stored procedures. What reporting software are you using?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-06-03 : 14:55:07

SCCM (formerly SMS)

~ Shaun Merrill
Seattle area

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-03 : 14:57:42

I don't have a good answer for you. What you are trying to achieve can only be done in a stored procedure.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources