July 9, 2008 at 2:30 pm
Hello,
I'm trying to develop a stored procedure that searches for a file using xp_cmdshell. Instead of returning the desired full filepath of a found file, it returns 0. I've tried using a temp table and table variable to store the results of xp_cmdshell, with the same results. I've also used an explicit OUTPUT parameter... no joy. I'm confused as to what I've missed.
SQL 2K5 SP2
When running the raw code or stored procedure in SSMS, I get the filepath in the Result tab and 0 in the Messages tab.
Code follows:
CREATE PROCEDURE[dbo].[getPartnerListFileName] (
@PartnerID int
)
AS
BEGIN
DECLARE@PartnerName varchar(100),
@CmdString varchar(1000)
DECLARE@Output table (
output nvarchar(255)
)
SELECT@PartnerName = PartnerName
FROMPartner
WHEREPartnerID = @PartnerID
SELECT@CmdString = 'dir "\\servername\directory\*' + @PartnerName + '*" /s/b/o-d'
INSERT INTO@Output (
output
)
EXECxp_cmdshell @CmdString
SELECT TOP 1output
FROM@Output
WHEREoutput IS NOT NULL
END
Executing the following gives the results described above:
DECLARE@PartnerID int,
@FullFileName varchar(255)
SET @PartnerID = 2
EXEC @FullFileName = getPartnerListFileName @PartnerID
PRINT @FullFileName
As always, any input appreciated.
--SJTerrill--
July 9, 2008 at 2:41 pm
INSERT INTO ... EXEC does *not* works with table variables, try with TEMPORARY Tables (#) instead.
* Noel
July 9, 2008 at 2:44 pm
Also read the entry in books online about returning things...
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/afe3d86d-c9ab-44e4-b74d-4e3dbd9cc58c.htm
The Redneck DBA
July 9, 2008 at 2:46 pm
noeld (7/9/2008)
INSERT INTO ... EXEC does *not* works with table variables, try with TEMPORARY Tables (#) instead.
No longer true in SQL 2005.
declare @Cmd varchar(100)
declare @T table (
Val varchar(1000))
select @cmd = 'dir c:\ /b'
insert into @T (val)
exec master.dbo.xp_cmdshell @cmd
select *
from @t
Assuming xp_cmdshell has been activated (it's disabled by default) and the user running it has the necessary rights, this will work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 2:46 pm
Noeld,
I have tried the temporary table, but I tried it again anyway. Same results.
July 9, 2008 at 2:57 pm
There are two problems with the original code. First:
EXEC @FullFileName = getPartnerListFileName @PartnerID
Doesn't work that way. @FullFileName will end up being 0, because what a proc returns is the error code, not the output. If you want the proc to return a value in that kind of way, what you need is an output parameter.
CREATE PROCEDURE [dbo].[getPartnerListFileName] (
@PartnerID int,
@FileName nvarchar(255) output)
AS
BEGIN
DECLARE @PartnerName varchar(100),
@CmdString varchar(1000)
DECLARE @Output table (
output nvarchar(255))
SELECT @PartnerName = PartnerName
FROM Partner
WHERE PartnerID = @PartnerID
SELECT @CmdString = 'dir "\\servername\directory\*' + @PartnerName + '*" /s/b/o-d'
INSERT INTO @Output (output)
EXEC xp_cmdshell @CmdString
SELECT @FileName =
(SELECT TOP 1 output
FROM @Output
WHERE output IS NOT NULL)
END
Then call it like this:
DECLARE @PartnerID int, @FullFileName varchar(255)
SET @PartnerID = 2
EXEC getPartnerListFileName @PartnerID, @FullFileName output
PRINT @FullFileName
Now, for the second problem, I don't believe xp_CmdShell will access network shares/directories. I've tried it, and maybe I did something wrong, but I haven't been able to get it to work, regardless of what permissions were set, etc. I think this may be a security feature. If you can get it to work, that would be great, and please let me know how you did it. But I don't expect it to work.
What you're probably better off doing, is creating a CLR proc that uses C# or VB.NET to get the data you're looking for. I know the VB FileSystemObject can do this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 2:58 pm
Jason,
Thank you for your reply. I have read the BOL entry for CREATE PROCEDURE, and have tried versions of this sproc both with and without OUTPUT parameters. I didn't really see anything else that explained what I'm missing. Hence my post.
GSquared,
xp_cmdshell is enabled. I did check this, but I think I can be confident it's on given that I _do_ get a result representing an actual file I wanted (In the Results tab), but not from the PRINT, which is where I get 0 or (In the Messages tab).
Is the root of the problem in my EXEC?
EXEC @FullFileName = getPartnerListFileName @PartnerID --Without OUTPUT parameter
--EXEC getPartnerListFileName @PartnerID,@FullFileName --With OUTPUT parameter
PRINT @FullFileName
I'm wondering if I'd be better off using a function...
--SJT--
July 9, 2008 at 3:04 pm
Did you change the proc the way I suggested, and change the exec command the way I suggested?
What you included in your last post doesn't have the "output" keyword after the second parameter variable, so it won't get the data.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 3:11 pm
GSquared,
Yup. I took the code you provided (thank you!) and created a new sproc using your methods. I was hoping the nested SELECT at the end for the OUTPUT parameter would do the trick. Unfortunately, this code results in the same thing I've been running into... well, okay, the 0 is gone... but the output from PRINT is blank/null.
If you like, I'll repost my code as it exists now.
--SJT--
July 9, 2008 at 3:17 pm
As for CLR vs. xp_cmdshell:
I actually had this working in a manual sort of way in SSMS using xp_cmdshell. My current 'solution' is using a domain administrator equivalent to run the SQL Server Service. It works well for development purposes, in that I can find the files and do what I was trying to do in the first place (BULK INSERT). For production, we'll be paring down the file access to just the required directories on the other server.
A CLR Assembly would need to be set as unsafe if it went after anything in the file system, right? For my purposes, I'm not sure if that's better.
--SJT--
July 9, 2008 at 3:24 pm
GSquared,
Apologies. I just noticed the OUTPUT in the actual call you posted.
SUCCESS!
Thank you all for your time and wattage.
--Sauron J. Terrill--
July 9, 2008 at 3:28 pm
You're welcome.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 9, 2008 at 4:22 pm
Heh... There is a saying ... you can learn something new every day. I wasn't aware that "insert exec restriction" was removed in 2005.
* Noel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply