Insert - Exec Select statements

Question

Post reply

Insert - Exec Select statements

renu.iitkgp

Mr or Mrs. 500

Points: 573
More actions
March 30, 2010 at 5:21 am

#386606

Hi all,
I have a difficulty in this piece of code inside a stored procedure.
....
....
SET NOCOUNT ON
BEGIN TRANSACTION
select @DirectoryCommand = 'dir "D:\Indexed\" /B/S/AA'
if exists (select * from SYSOBJECTS where id = OBJECT_ID(N'IndexDocsTemp')
and OBJECTPROPERTY(id, N'IsUserTable')=1)
begin
drop table IndexDocsTemp
end
CREATE TABLE IndexDocsTemp(PhyPath nvarchar(512))
CREATE INDEX IX_PhyPathTemp on IndexBrkDocsTemp(PhyPath) on [primary]
insert into IndexDocsTemp(PhyPath)
EXEC master..xp_cmdshell @DirectoryCommand
delete from IndexDocsTemp where IsNull(RTrim(LTrim(PhyPath)), '') = '' orIsNull(RTrim(LTrim(PhyPath)), '') = 'File Not Found' or
IsNull(RTrim(LTrim(PhyPath)), '') = 'The system cannot find the file specified.'
COMMIT TRANSACTION
select * from IndexBrkDocsTemp
drop table IndexBrkDocsTemp
return
GO
What I do is I create a temporary table, populate it with files at a physical location, return the rows of the table as a result set.
I when I execute the stored procedure, I could see the results, but a COM function which uses the stored procedure to get the result set is not able to get any result set.
Can some one tell me what could be the issue.
Thanks in advance,
Renuka Prasad.

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

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Are you aware you're selecting out of a different table than you're dropping/creating/populating?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

renu.iitkgp Mr or Mrs. 500 Points: 573 More actions · Answer 2

Dude, that was a mistake in changing the name of the table, before copying the piece of the code and removing company specific info and other parts, not concerned with the problem.

I found out the issue any way. Whenever I executed the stored procedure, I didn't notice that I got a warning which said, "Warning! The maximum key length is 900 bytes. The index 'IX_PhyPathTemp' has maximum length of 1024 bytes. For some combination of large values, the insert/update operation will fail.".

When I removed the line where I created index on the table, I didnot face the problem and I could get a resultset in the COM function.

But Seth, how come I got the warning, as the index is created on a single column with a length of 512 bytes?

Thanks in advance,

Renuka Prasad.

renu.iitkgp Mr or Mrs. 500 Points: 573 More actions · Answer 3

Hey Seth,

Got it! nvarchar(512) takes 1024 bytes because it's unicode. Since my windows paths are always ASCII, I could have declared the column in the table as varchar(512) which takes 512 bytes.

It works, thanks to some "DBA in the making" on another forum.

Venkat Renuka Prasad Sahukara.

Garadin One Orange Chip Points: 29613 More actions · Answer 4

renu.iitkgp (3/31/2010)
Dude, that was a mistake in changing the name of the table, before copying the piece of the code and removing company specific info and other parts, not concerned with the problem.

Sometimes the simplest solution is the correct one... sometimes it's not. I wasn't trying to give you a hard time, just pointing out the first thing I saw.

Glad you're all set now.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]