September 21, 2010 at 9:31 am
Well Microsoft have done it again, include a very useful tool that lets me get a long way through development before a brain-dead stupid restriction within the application basically makes everything I've done completely useless.
why in £$%" did they not make nesting INSERT-EXEC possible? heaven knows. Too lazy to write the next few lines of code probably.
rant over. sorry.
I would very much appreciate any advice that any of you might have... I need to execute an xp_cmdshell action and look for a specific value in the results. Specifcally I have to call php to run a script against an ancient informix database on a sco unix server and check what status code is returned.
based on what code is output to the console (hence the insert-exec) the stored proc 'selects' any of a number of messages to the output the idea being that sproc that called this one will do the same eventually only returning relevant messages to the user. but nooo, microsoft can't do that.
at the moment the only way around it that I can think of is to create a temporary table at the start of the batch and write to it manually adding a header record like %%dtsxdata and then another like %%statuscode.
this makes what would have been a very nice tidy piece of code, essentially, crap.
can anyone suggest a better way of doing this? before I start down the long and tedious road of piling more and more crap into a temporary table that I then have to make sense of later?
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
September 21, 2010 at 9:50 am
I am not familiar with INSERT-EXEC but thought I would point out that if you are using xp-cmdshell to connect to a non SQL server environment it is not really a microsoft problem. there are also many levels of functionality and supported context on different environments and releases. I would not expect every function to work on an older unix system running sybase or some other environment.
why does it have to be done through xp_cmdshell? have you tried to connect to the database directly using an ODBC or some other form? again depending on the age of the unix DB and the type (sybase, or other) with depend on the level of support and the syntax.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 21, 2010 at 9:58 am
Hi Ben, not sure if this is any good to you, I wrote it in a previous life and can't even test it here:
CREATE PROCEDURE [dbo].[CopyDiskFile]
@FileFrom VARCHAR(200), @FileTo VARCHAR(200)
AS
SET NOCOUNT ON
DECLARE @CMD VARCHAR (1000)
SET @CMD = 'COPY "' + @FileFrom + '" "' + @FileTo + '"'
IF OBJECT_ID('tempdb..#Output') IS NOT NULL
DROP TABLE #Output
CREATE TABLE #Output (CopyResult VARCHAR(500))
INSERT INTO #Output EXEC MASTER..XP_CMDSHELL @CMD
IF (SELECT COUNT(*) FROM #Output WHERE CopyResult = ' 1 file(s) copied.') = 0
BEGIN
RAISERROR ('Template file WAS NOT copied.',0,1) WITH NOWAIT
RETURN
END
RAISERROR ('1 file copied.',0,1) WITH NOWAIT
RETURN
It did work.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 21, 2010 at 9:58 am
Hi Dan,
Thanks for reply, I'm using xp_cmdshell in order to kick off dtsx packs and use php to odbc to informix and perform some complex logic that I really really dont want to have to write in sql server.
the issue i am experiencing is most definitely a limitation of sql server, in that you cannot store the results of an exec in a table if the exec you have called is also trying to do store results in a table.
Nevermind. I'll get back on it tomorrow and use some ugly temp tables :s
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
September 21, 2010 at 10:01 am
Hi Chris,
thats essentially what I'm doing but twice.. im trying to call that sproc from another and copy data into a table from that exec which sql cant do.
Ben
^ Thats me!
----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
September 21, 2010 at 10:06 am
BenWard (9/21/2010)
Hi Chris,thats essentially what I'm doing but twice.. im trying to call that sproc from another and copy data into a table from that exec which sql cant do.
Oh right, gotcha now. You can't nest INSERT...EXEC.
Can't you use a permanent table in one of them?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply