Insert - Exec Select statements

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Insert - Exec Select statements

Author

Topic

venkat09
Starting Member

17 Posts

Posted - 2010-03-30 : 07:17:05

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.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 07:23:44

Why are you making a base table? Wouldn't a temp table have been as good. (better even)

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

venkat09
Starting Member

17 Posts

Posted - 2010-03-30 : 07:51:55

Hi Charlie,

Sorry, there were some mistakes when I pasted the code earlier. The name of the table was IndexDocsTemp and not IndexBrkDocsTemp.
Thanks for the reply. I am not aware of something called temp table.
But what seems to be the problem with the code? Why does it not return a recordset?

Thanks in advance,
Venkat R Prasad.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 08:19:49

You are creating a base table with the stored proc. You could make a temp table using a # before the table name


CREATE TABLE #IndexDocsTemp(PhyPath nvarchar(512))

This has the advantage that you'd be able to run the procedure concurrently without locking. as each instance of the stored proc would have their own table to work with.

Is this the whole SQL text of your stored proc? If so what is the GO command doing in there?

If it is and you get a result running it directly then you must be doing something interesting in your application.

Can you post the whole sql text of the stored proc rather than the snippet you think is the problem?

Also -- when you run the sql manually who are logged in as? If the user your application is using to call the proc is different then maybe it can't get a directory listing?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

venkat09
Starting Member

17 Posts

Posted - 2010-03-30 : 09:46:15

Hi Charlie,

The code itself is the entire storedprocedure, except that I get the Path based on some arguments (@ProductId, etc).

So, you can safely assume that the code is

Create Prdcedure spIndexedDocs

as

.... What ever I pasted above.

Also, any user who runs this stored procedure using COM from a webpage will run it with windows authentication and as an administrator. So, there is no question of access denial to the SQL server. (This is an internal application.)

I wrote the code, creating a temp table, but it made no difference. I don't think 'locking' is the issue here, as there's only one user who uses it and only in a single connection.

As per the "GO" command, it always shows up at the end of the code, in the enterprise manager when I open a stored procedure.

The actual problem is that I get results when I execute it, but I can't return a record set to the COM function that uses this query. There's absolutely nothing wrong with the COM function, and I am totally sure of it.

How does the stored procedure in its runtime figure out that it's running a db transaction or querying the database? (I use SQL server 2000 with SP2). i.e, how does the procedure know that it has to return a result set or the result of a transaction?

I read that this is a bug in SQL server 2000 and it had been fixed in SP4, but there should be something I could to make it work in SP2, right?

Thanks in advance,
Venkat Renuka Prasad.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 10:05:46

At the end you are doing


select * from IndexBrkDocsTemp

in the stored proc.

That returns a result set. Whichever application layer you are calling the sp with should be able to take that result set.

I didn't think that changing the table to a temp table would solve your problem -- was just recommending that you change it because it eliminates a lot of other potential problems.

Can you confirm that if you EXEC the stored proc directly from a query analyser window that you get results? (I'm not sure if you are extracting the SQL text and executing it in a different window).

Have you sniffed the EXEC string for the stored proc in your application? Are you sure that a proper parameter is being passed?

If you've called the stored proc outside of the application and got a result set and you have ruled out permissions (db user permissions) then the problem is probably in the application layer.

If you've changed the logic to use a temp table (#) then you can take out all the checking for one and streamline the code a little. Also, why do you need the transaction? If you are just dumping to a temp table then you don't really care about atomicity here. Take out all the unnecessary cruft and then we will know that it's not causing the issue.

I believe that the code can be cut down to :


SET NOCOUNT ON
select @DirectoryCommand = 'dir "D:\Indexed\" /B/S/AA'	

CREATE TABLE #IndexDocsTemp(PhyPath nvarchar(512))
CREATE INDEX IX_PhyPathTemp on #IndexDocsTemp(PhyPath)	

insert into #IndexDocsTemp(PhyPath) 
EXEC master..xp_cmdshell @DirectoryCommand

select PhyPath from #IndexDocsTemp
where
	IsNull(RTrim(LTrim(PhyPath)), '') NOT IN (
		''
		, 'File Not Found'
		, 'The system cannot find the file specified.'
		)

Which has less to go wrong.

-- I've run this on my server and it works OK (returns a result) Obviously you would wrap that into a proc.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

venkat09
Starting Member

17 Posts

Posted - 2010-03-31 : 07:14:36

Hi Charlie,

The procedure works fine, just after I commented out the line where I created the index on the temp table. I could see the result-set in the front end.

I didn't notice that I was getting a warning, which says, "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".

I don'd understand: the index is created on a single column whose size is not greater that 512 bytes. Why do I still get the warning when I execute the stored procedure?

Thanks in advance,
Renuka Prasad.

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-31 : 07:34:51

PhyPath is an NVARCHAR(512), which means it uses 2 bytes per character, so 1024 bytes. This is a unicode type. Unless you need unicode (which you don't because dir will return ASCII AFAIK), then you should change it to VARCHAR(512), which will only use 1 byte per character.

You should also DROP the temp table when you're finished with it.

There are 10 types of people in the world, those that understand binary, and those that don't.

venkat09
Starting Member

17 Posts

Posted - 2010-03-31 : 07:46:58

Hi DBA in the making,

Thanks a lot for the enligntenment :P
I think I fall into the type which understands binary.
It works now, without issuing any warning!!!

Venkat R. Prasad

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 07:50:46

Looks to me a permission problem. When you run proc, are you using sql authentication or windows authentication? What authentication COM is using when calling this proc?

Harsh Athalye
http://www.letsgeek.net/

venkat09
Starting Member

17 Posts

Posted - 2010-03-31 : 09:37:38

DBA in the making,

One should not drop the temp table as the temp table is created and managed inside the thread of the procedure in runtime.
It does not exist in the database at any given time.

Venkat R. Prasad

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-03-31 : 14:08:38

quote:
Originally posted by venkat09

DBA in the making,

One should not drop the temp table as the temp table is created and managed inside the thread of the procedure in runtime.
It does not exist in the database at any given time.

I've always lived by the motto that if you create it, you should drop it when you're done with it. When you create a table using "CREATE TABLE #tablename...", then you should always execute a "DROP TABLE #tablename" when you're done. Perhaps you're talking about table variables, eg, "DECLARE @tablename TABLE...". These don't need to be dropped.

There are 10 types of people in the world, those that understand binary, and those that don't.

venkat09
Starting Member

17 Posts

Posted - 2010-04-01 : 02:54:26

Hey DBA in the making,

I understood the idea of you, following a coding-practice.
But what would go wrong if I don't delete the temporary table? After all, the temporary table is made to be temporary, existing only in the runtime with-in the thread of the procedure being executed.

Of course I can execute the drop table #tblname after I am done with it.

Venkat R. Prasad

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-04-05 : 04:35:46

the temp table will automatically be dropped when the scope of the stored proc no longer exists (i.e it's finished). I don't think there is any overhead for dropping the temp table as long as it's after the result set is returned.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources