Insert - Exec Select statements

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » Insert - Exec Select statements

Insert - Exec Select statements

Rate Topic

Display Mode

Topic Options

Author

Message

renu.iitkgp

Posted Tuesday, March 30, 2010 5:21 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 5:56 AM
Points: 12, Visits: 31

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)), '') = '' or IsNull(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.

Post #892613

Garadin

Posted Tuesday, March 30, 2010 8:18 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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

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

Post #892819

renu.iitkgp

Posted Wednesday, March 31, 2010 5:24 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 5:56 AM
Points: 12, Visits: 31

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.

Post #893648

renu.iitkgp

Posted Wednesday, March 31, 2010 5:50 AM

Grasshopper

Group: General Forum Members
Last Login: Wednesday, September 05, 2012 5:56 AM
Points: 12, Visits: 31

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.

Post #893663

Garadin

Posted Wednesday, March 31, 2010 8:13 AM

SSCommitted

Group: General Forum Members
Last Login: Wednesday, November 07, 2012 4:08 PM
Points: 1,525, Visits: 4,047

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

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

Post #893798

« Prev Topic | Next Topic »

Permissions