SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Insert - Exec Select statements


Insert - Exec Select statements

Author
Message
renu.iitkgp
renu.iitkgp
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
Garadin
Garadin
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2699 Visits: 4107
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
renu.iitkgp
renu.iitkgp
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
renu.iitkgp
renu.iitkgp
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 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.
Garadin
Garadin
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2699 Visits: 4107
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search