"New transaction is not allowed because there are other threads running in the session." -

That is a SqlException I got at a...

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()

Anyone an idea what THAT means? How do I cause it? How can I work around it?

I think you probably have an open connection somewhere. See if you can start the transaction after you close the connection.|||

I have about 25 open connections. Because that is - quess what - an ASP.NET application.

Once connection per page.

And every page has - one thread. No multi threading at all.

Plus it is legal for a thread to have multiple open connections.

|||

Have you enabled Multiple Active Resultsets (MARS)?

For connections with MARS enabled, manual transactions started via TSQL BEGIN TRAN must end (COMMIT or ROLLBACK) within the same scope that started the transaction

If your issue meets the the aboving conditions, you can try this work aroud:

To begin tansactions that are not required to be scoped under
MARS-enabled connections, use the client driver API constructs to begin and end transactions. If existing code relies on BEGIN TRAN operations without
corresponding COMMIT/ROLLBACK, MARS can be turned off until the application can be modified to use the client driver transaction APIs

|||Nope, no MARS. Actually that was (not is) old SQL Server 2000 code from .NET 1.1 that was traansported over. Worked flawlessly under .NET 1.1, and looks flawless to me still.

"must declare variable" when variable has been declared!

Any idea why the following message is returned?

Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@.table'.

The script is created to run through each column in the database and check it against cross referenced data in other table. It had to be built because of the database we own has not a single referntial constraint in it (long story but it's from the the age old "our code works better than a well designed database" school of thought).

excuse the poorly laid out SQL - it's hard to copy and paste into this thing...

Thanks
Yal

------------------------

SET NOCOUNT ON

DECLARE @.table VARCHAR(32)
,@.column VARCHAR(32)
,@.x_table VARCHAR(32)
,@.x_column VARCHAR(32)
,@.x_type VARCHAR(32)
,@.problem VARCHAR(32)
,@.count VARCHAR(32)

DECLARE xref_check CURSOR FOR
SELECT table_name, column_name, xref_table, xref_column, xref_type
FROM xref_check

OPEN xref_check
FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type

WHILE @.@.FETCH_STATUS = 0
BEGIN

IF @.x_table = 'PS_XREF'
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT code FROM PS_XREF WHERE type = '''+ @.x_type +''')
END'
)

END

ELSE
BEGIN
-- SELECT @.table, @.column, @.x_table, @.x_column, @.x_type
EXEC ('IF (SELECT COUNT('+ @.column +')
FROM '+ @.table +'
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'+ @.column +'
FROM '+ @.table +' x
WHERE '+ @.column + ' NOT IN (SELECT '+ @.x_column +' FROM '+ @.x_table +')
END')
END

FETCH NEXT FROM xref_check INTO @.table, @.column, @.x_table, @.x_column, @.x_type

END

CLOSE xref_check
DEALLOCATE xref_checkMake sure your references to these variables exist outside the string. For example:

@.x_type +''')) > 0
BEGIN
SELECT @.table, @.column, @.x_table, @.x_column, @.x_type, x.'

should have @.table, @.column ... outside the string like you did before this statement.