Can't start a cloned connection while in manual transaction mode.

I got this error recently using SQL Server, and I found a microsoft site
which gives a solution by setting the SelectMethod property to Cursor but I
don't understand why. We've run this app before with no problems, yet now all
of a sudden we need to customize the URL string of the driver connection.
Can anyone explain what this means exactly and why this might have become a
problem now but was not before?
Most appreciated,
ol,
add "SelectMethod=cursor" your URL connection
ex:
Connection conn =
DriverManager.getConnection("jdbc:microsoft:sqlser ver://localhost:1433;SelectMethod=cursor");
newton
Posted via http://www.webservertalk.com
View this thread: http://www.webservertalk.com/message1022266.html
|||| Thread-Topic: Can't start a cloned connection while in manual transaction
mode.
| thread-index: AcVBMGMZaFbT7Q3cQUeyebz5WmA8JQ==
| X-WBNR-Posting-Host: 66.114.64.114
| From: "=?Utf-8?B?TXJOb2JvZHk=?=" <MrNobody@.discussions.microsoft.com>
| Subject: Can't start a cloned connection while in manual transaction mode.
| Date: Thu, 14 Apr 2005 13:27:32 -0700
| Lines: 11
| Message-ID: <120F0C3C-9AAD-4B6A-B7F4-3F966E95E4DD@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA02.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSF TNGXA03.phx.gbl
| Xref: TK2MSFTNGXA02.phx.gbl microsoft.public.sqlserver.jdbcdriver:6826
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| I got this error recently using SQL Server, and I found a microsoft site
| which gives a solution by setting the SelectMethod property to Cursor but
I
| don't understand why. We've run this app before with no problems, yet now
all
| of a sudden we need to customize the URL string of the driver connection.
|
| Can anyone explain what this means exactly and why this might have become
a
| problem now but was not before?
|
| Most appreciated,
|
|
|
Hello,
You will definitely need to use cursor mode to allow cloned connections to
be spawned on your behalf in a transaction. The behavior of SelectMethod
has been the same for the last several years.
If your code always used SelectMethod=direct, then the only thing I can
think of is that your app never entered the codepath that required a new
connection to be spawned.
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.

Cant seem to make this query work

Hi,

I have a query thatI need to make into one query for the sake of an application reading only one cursor.

Here's the syntax:

select (select distinct(x.amount) from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,
a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled

from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer

The error message i'm recieving is the following:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any ideas? any suggestions would be great.

Thanks

This error came from the only subquery in your query (which produces the New column). You're trying to return a result set 'distinct(x.amount)' and then use it as an expression, which is not permitted. Because SQL can not predict how many rows will be returned by the subquery, and there is no relationship between the column New by the subquery and other columns in the whole query, thus SQL can't join theNew column to the whole result set as other columns. In this case, you should either make the subquery return a single value:

select (select max(distinct(x.amount)) from escrow k inner join e120 x on k.escrow = x.escrow
inner join a10 g on x.escrow = g.escrow where k.ftype = 'S' group by x.amount, g.officer) As New,
a.officer as Officer, count(distinct(j.fstatus))as Escrow_Type, count(distinct(j.amount))as Amount, count(distinct(d.open_date))as [Open], count(distinct(d.close_date)) as Closed, count(distinct(can_date))as Cancelled

from a10 a
inner join escrow d on a.escrow = d.escrow
inner join e120 j on j.escrow = d.escrow where j.id_scr = 'e21' and j.fstatus = 'PAID' group by a.officer

or move the tables referenced by the subquery and find join conditions with other tables, then returned a column data which satisfies the join conditions, rather than using subquery.

|||

Hi when I run the query I get the following error:

Server: Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Warning: Null value is eliminated by an aggregate or other SET operation.

Is there a substitute for the = operator I can use to get this to parse correctly?

Thanks,

|||

Strange What's the query you're using? Are you sure you add a MAX to make the subquery return single value?

selectmax(distinct(x.amount))

Cant reference logical table within cursor

Does anyone know if MS SQL can't process a "select * from deleted" from within a cursor that's embedded in a trigger? I'm getting an error when I run this...

DECLARE check_contact_fields CURSOR
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
set @.SQL = 'select ' + @.column_name + ' into ##DeletedData from deleted'
exec sp_executesql @.SQL
set @.SQL = 'select ' + @.column_name + ' into ##InsertedData from inserted'
exec sp_executesql @.SQL
if (select * from ##DeletedData) <> (select * from ##InsertedData)
select * from ##InsertedData
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

drop table ##DeletedData
drop table ##InsertedData

Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'deleted'.

TIAThe error is returned correctly, because logical table deleted is not known within the execution context of dynamic SQL.|||That's what I thought. So, how can I accomplish what I'm trying to do? I can't think of another way to do it without dynamic SQL.

Thanks again|||Maybe you can tell us what you are trying to do...I can't figure it out...

AND a cursor in a trigger is never a good idea....|||Brett -

I need to record the old and new data for a set of contact related fields when a contact record is updated. The set of fields that need to be monitored are stored in the contacts_fields table (because the contact fields are dynamic). So, my thinking was that I could create an update trigger to check those columns in the contacts_fields table that are in the deleted row against the inserted row. If any of the column_name values changed, I would then only record that *specific* column that was modified.

Thanks|||Why bother...just update the whole row...what difference does it make...

What's the DDL of the table look like?

What do you mean by dynamic?|||DDL ("master" audit table):
/****** Object: Table [dbo].[contacts_history] Script Date: 8/4/2004 1:41:36 PM ******/
CREATE TABLE [contacts_history] (
[contact_history_id] [int] IDENTITY (1, 1) NOT NULL ,
[contact_id] [int] NULL ,
[who_created] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[who_modified] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[fields_lastUpdated] [nvarchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history] PRIMARY KEY CLUSTERED
(
[contact_history_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

DDL (data capture table):
/****** Object: Table [dbo].[contacts_history_values] Script Date: 8/4/2004 1:41:10 PM ******/
CREATE TABLE [contacts_history_values] (
[contacts_history_value_ID] [int] IDENTITY (1, 1) NOT NULL ,
[contact_history_id] [int] NULL ,
[field_id] [int] NULL ,
[old_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[current_data] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_contacts_history_values] PRIMARY KEY CLUSTERED
(
[contacts_history_value_ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Dynamic in the sense that the fields in the "contacts" table can change on the fly (there's a field management area).

Thanks|||holy crap...

You basically have "vertical" rows....

Is this a third party product, did you build this or did you inherit this

What does the concat table DDL look like?

And what do you mean the "fields" (you mean columns right) can change on the fly.

Do you mean the data or the table structure?

What does "if any of the column_name values have changed" mean?

I'm sorry...I just can't see it yet...|||Yes, the columns on the contacts table can change on the fly (but its PK, contact_id, would always exist).

I mean the table structure.

What does "if any of the column_name values have changed" mean?
--> I'm not sure if you saw what the check_contact_fields cursor looks like, but what I mean by this is that since I store all the columns that need to be audited in the contacts_fields table I could "loop" over these columns (hence the cursor) and check each column_name value (these are really just the column names) against the deleted and inserted tables. If the data for any of these columns changed I would then write the changes (along with the old data) to the contacts_history_values table.

Hope this makes sense.|||ok...I understand...not that I want to.....

Is the id 1 per "table"

Are you making up and configuring "tables" for end users through a web application?

I'd be very interested in the reason that your doing this...

As for your solution...I gotta ponder this one.....

Also because there is no ordinal position in your table, and the order of rows of data in a database is meangless...how do you know what column name has changed?

It doesn't work that way.

You column name is essentially a key in this process...actually a composite key along with contactId

So actually trying to udate the key...which to me is a bad thing...

You should go for a logical update...which mean you do no updates but rather a DELETE first, then an INSERT...

That should be rather straight forward in a trigger...|||Sorry, but I don't follow you.

Have you looked at the DDL's I posted? There's a column called "field_id" (in the contacts_history_values table) that's a FK for the contacts_fields table.
This allows the app to keep track of the column that was modified.

I think I'm more confused now... =\|||Ok good...

I think I got confused...

You want to audit the change correct?

Just do a join between inserted and deleted on contactId and fieldid and insert that results to your audit tables from inside a trigger

What happens for a new "field" or a field that has been "deleted"

You still haven't told me who built this...|||I think I found the right design but I'm having issues executing it.

DECLARE check_contact_fields CURSOR LOCAL
FOR SELECT field_id, column_name FROM contacts_fields
OPEN check_contact_fields
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
if (select '+@.column_name+' from inserted) <> (select '+@.column_name+' from deleted)
print @.column_name + ' boo'
else if (select '+@.column_name+' from inserted) = (select '+@.column_name+' from deleted)
print 'identical values'
FETCH NEXT FROM check_contact_fields INTO @.field_id, @.column_name
END
CLOSE check_contact_fields
DEALLOCATE check_contact_fields

This always returns "identical values" even though some of the data has changed.

Any ideas?|||What table is the Trigger on?

I don't think you want or need the cursor|||I Think you want this...can't test without the other tables DDL

CREATE TRIGGER Contact_Fields_TR1 ON Contact_Fields
AS
BEGIN
INSERT INTO contacts_history_values(contact_history_id, field_id, old_data, current_data)
SELECT i.ContactId, i.field_Id, o.column_name, i.column_name
FROM inserted i INNER JOIN deleted o
WHERE i.Contact_Id = o.Contact_Id AND i.Field_Id = o.Field_ID
END