Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert - Exec Select statements Expand / Collapse
Author
Message
Posted Tuesday, March 30, 2010 5:21 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 2, 2014 9:03 AM
Points: 15, Visits: 57
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
Posted Tuesday, March 30, 2010 8:18 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:20 PM
Points: 1,519, Visits: 4,085
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #892819
Posted Wednesday, March 31, 2010 5:24 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 2, 2014 9:03 AM
Points: 15, Visits: 57
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
Posted Wednesday, March 31, 2010 5:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, June 2, 2014 9:03 AM
Points: 15, Visits: 57
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
Posted Wednesday, March 31, 2010 8:13 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:20 PM
Points: 1,519, Visits: 4,085
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 :: Running Totals :: Tally Table :: Cross Tabs/Pivots :: String Concatenation
Post #893798
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse