William Vaughn
6/6/2008 4:03:00 PM
Sure. In my TSQL classes at MSU, one of the example students wrote was to
recreate ISQL (now SQLCMD). This is a text processor that takes a batch or
script of TSQL statements and executed them in sequence. Each batch in the
script was separated from the other by GO (just as in SQLCMD). In your case,
I would simply write the commands to a file and process them using simple
parsing code. Again, the point is, I would not do two round trips per
operation. If the commands are in a table on the server, I would create
strings on the server and use SQL EXECUTE to execute them... not bring them
to the client to do so.
--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
____________________________________________________________________________________________
"John" <info@nospam.infovis.co.uk> wrote in message
news:uzUu9JzxIHA.4952@TK2MSFTNGP05.phx.gbl...
> Hi Bill
>
> I am using a loop as I am processing a command queue. I fetch a command
> from a table, process it (code not included) and if command executes
> successfully I delete it form the queue. Can't think of a way of doing
> this without a loop.
>
> Thanks
>
> Regards
>
> "William Vaughn [MVP]" <billvaNoSPAM@betav.com> wrote in message
> news:DC7069B8-F5BF-49AA-8023-A806CFF1EBA1@microsoft.com...
>> Unless you use MARS (Multiple Active Resultsets) you cannot reuse a SQL
>> Server connection until the rowset has been fetched. That is, the rows
>> you requested via the SELECT must be fetched in their entirety before the
>> connection can be used for another operation--either that or you have to
>> use the Cancel method on the Command. A typical solution to this problem
>> is to open a second connection to perform the updates.
>>
>> The basic problem with your code is that you are not using SQL Server as
>> it should be used. When you want to delete rows based on IDs fetched from
>> another rowset, you should do so on the server, not via looping through
>> the rowset on the client. For example,
>> DELETE MyTable WHERE ID IN (SELECT ID FROM SomeOtherTable WHERE <some
>> criteria>)
>>
>> I discuss this approach in my book.
>>
>> --
>> __________________________________________________________________________
>> William R. Vaughn
>> President and Founder Beta V Corporation
>> Author, Mentor, Dad, Grandpa
>> Microsoft MVP
>> (425) 556-9205 (Pacific time)
>> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
>> ____________________________________________________________________________________________
>>
>> "John" <info@nospam.infovis.co.uk> wrote in message
>> news:O7N9SJuxIHA.4492@TK2MSFTNGP02.phx.gbl...
>>> Hi
>>>
>>> I have below code;
>>>
>>> Cmd = New SqlCommand("SELECT * FROM MyTable01", DBConnectionRemote())
>>> Reader = Cmd.ExecuteReader()
>>>
>>> While (Reader.Read())
>>> ID = CInt(Reader.GetValue(Reader.GetOrdinal("ID")))
>>>
>>> Dim delstr As String = "DELETE FROM MyTable02 WHERE [MyTable2].ID =
>>> " & ID.ToString
>>> Dim delCmd As New SqlCommand(delstr, DBConnectionRemote())
>>>
>>> delCmd.ExecuteNonQuery()
>>> End While
>>>
>>> I am getting a 'System.InvalidOperationException: There is already an
>>> open DataReader associated with this Command which must be closed first'
>>> error on the delCmd.ExecuteNonQuery() statement. What is the problem and
>>> how can I fix it?
>>>
>>> Thanks
>>>
>>> Regards
>>>
>
>