Nick,
Picky is good. It was my understanding that this was an ODBC restriction.
I've also seen the same error in the test bed in applications written in C
and Java and that was the explanation we had come to accept.
I'm not arguing with you, but if the restriction is with SQL Server, then my
understanding of something else needs to change. I might have a nebulous
grasp on how this can be, but I would like to hear from someone else to
(match|firm) up what I'm thinking.
The following ISQL script does the same sort of thing (it's bad practice to
grant permissions to individual users, but it works as an example), but will
not return an error, even though the grant is being executed while the usr
cursor is still active in the established connection. Looking at the current
activity while it takes place, it looks as if it handles it as a whole and
not as individual statements (That's my current theory on how it's possible
in an ISQL script), but profiler still shows a statement being prepared,
executed, and individual fetches being done, and the grant statement being
executed while usr is still active, and I don't see any additional
connections being made - only the existing connection is referred to. Do you
have a moment to take on explaining this?
<ISQL Script>
DECLARE usr CURSOR FOR SELECT name FROM sysusers WHERE issqluser = 1
DECLARE @usrname varchar(50), @exec varchar(50)
OPEN usr
FETCH NEXT FROM usr INTO @usrname
WHILE @@fetch_status != -1
BEGIN
IF @@fetch_status != -2
BEGIN
SELECT @exec = 'GRANT SELECT ON Customers TO ' +
@usrname
exec(@exec)
END
FETCH NEXT FROM usr INTO @usrname
END
CLOSE usr
DEALLOCATE usr
</ISQL Script>
Thanks,
Steve H.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of
Nick Gorham
Sent: Saturday, September 29, 2001 5:12 AM
To: Steve Howard
Cc: Tim Harsch; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: selector screws do in ODBC 0.28
Steve Howard wrote:
> If I'm following through this correctly, this is not a bug. ODBC only
allows
> one active statement per connection. You are using the $hDB database
handle
> for this statement:
Well to be picky, its not ODBC but SQL Server that has that restriction, you
"can" get around it by requesting a non forward only cursor on the
statement,
(ask for dynamic, and it will fall back to something lesser). However,
beware,
I can show a a 10 ODBC call program that can put SQL Server into a spin wait
doing that :-(
--
Nick Gorham
Emacs would be a great operating system if it just had a decent text
editor...
