I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.
Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues
Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.
Please help me.
Thanks!In Books OnLine, look up the keywords OUTPUT variable and RETURN.|||Hmmm...not sure if the OUTPUT parameters alone will do the job, as dynamic SQL operates within its own scope. Try it and see, but you can also use temp tables as a hack method to pass values across scopes.|||I have a strange problem, I want to execute different stored procedures based on certain criteria defined in the database. I am able to execute the sp using the sp_executesql system stored procedure.
Exec sp_executesql Nexec procedurename {parameterlist}, N{parameter declaration}, Parametervalues
Now I want to read a particular value that is being return be the procedure.
NOTE: procedure is returning a resultset.
Please help me.
Thanks!
Try This
DECLARE @.sql nvarchar(2048)
SET @.sql = ' SET @.count = ( SELECT COUNT(*) FROM table1 )'
DECLARE @.temp int
EXEC sp_executesql @.sql, N'@.count int OUTPUT', @.temp OUTPUT
Jamessql
Showing posts with label strange. Show all posts
Showing posts with label strange. Show all posts
Thursday, March 29, 2012
Wednesday, March 21, 2012
ExecuteScalar closed my connection?
Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.sql
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.sql
ExecuteScalar closed my connection?
Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.
Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.
Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
ExecuteScalar closed my connection?
Dear All,
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
I have this strange problem when connected to MSDE 2005 (using SQL
Native Client).
There is only 1 thread running and only 1 client in my test
environment (no multiple concurrent access). But from time to time
(say once a day) I will get an error message says:
"System.InvalidOperationException: This SqlTransaction has
completed; it is no longer usable."
The transaction had already completed and committed to the Database
(without my knowledge). The exception is thrown when I try to call
Commit() in my code.
I can't reproduce this problem, and it happened randomly at random
time / location.
Tracing through my logs, the only commonality between these exceptions
are;
1. Open Connection
2. Begin a transaction (1)
3. Inserted something into the Db
4. Commit the transaciton
5. Begin another Transaction
6. Inserted something into the Db (2)
7. Retrieve the @.@.IDENTITY
8. Commit the transaction <-- Exception thrown here!
9. Close the connection
Note 1: Although in the above sequence, I shown two transactions
within 1 connection. But there are cases where only 1 transaction were
used and it is still throwing an Exception.
Note 2: Although exception was thrown in Step 8, whatever that I have
inserted in step 6 had already committed into the DB.
And NO, I didn't set any behaviour to close the connection
automatically.
Thank In Advance.Hi
I see you are on SQL Server 2005 Express Edition , right?
Can you insert TRY BEGIN CATCH error handle block when you perform DML?
Also , tell the client to check @.@.trancount
IF @.@.trancount > 0 COMMIT TRANSACTION (or ROLLBACK)
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.|||How do you know that the connection was closed? Do you have any COMMIT or
ROLLBACKs in the SQL code?
--
Hope this helps.
Dan Guzman
SQL Server MVP
<ckkwan@.my-deja.com> wrote in message
news:82fab8ed-3b14-4376-86b6-a87f895df339@.s8g2000prg.googlegroups.com...
> Dear All,
> I have this strange problem when connected to MSDE 2005 (using SQL
> Native Client).
> There is only 1 thread running and only 1 client in my test
> environment (no multiple concurrent access). But from time to time
> (say once a day) I will get an error message says:
> "System.InvalidOperationException: This SqlTransaction has
> completed; it is no longer usable."
> The transaction had already completed and committed to the Database
> (without my knowledge). The exception is thrown when I try to call
> Commit() in my code.
> I can't reproduce this problem, and it happened randomly at random
> time / location.
> Tracing through my logs, the only commonality between these exceptions
> are;
> 1. Open Connection
> 2. Begin a transaction (1)
> 3. Inserted something into the Db
> 4. Commit the transaciton
> 5. Begin another Transaction
> 6. Inserted something into the Db (2)
> 7. Retrieve the @.@.IDENTITY
> 8. Commit the transaction <-- Exception thrown here!
> 9. Close the connection
> Note 1: Although in the above sequence, I shown two transactions
> within 1 connection. But there are cases where only 1 transaction were
> used and it is still throwing an Exception.
> Note 2: Although exception was thrown in Step 8, whatever that I have
> inserted in step 6 had already committed into the DB.
> And NO, I didn't set any behaviour to close the connection
> automatically.
> Thank In Advance.
Monday, March 12, 2012
Execute SSIS Package Job fails
Hey Folks,
I've got here a strange Problem. If I try to execute the SQL Server Agent Job, that executes my SSIS Package, it fails. The job succeedes when I run the Job as the Proxy, that maps on the User, that has deployed the Package, or when I run the Job under an System Administrator Proxy. Now my Question - how must I set up an Service-Account, which is no Admin and not has deployed the Package?
I already know, that the User has to be in the sysadmin role, and in all msdb SQLAgent*
Thanks and greez
Karsten
I assume the service account is suitable for running SQL Agent. To be sure you may want to review this rather long BOL topic "Setting Up Windows Service Accounts" (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/instsql9/html/309b9dac-0b3a-4617-85ef-c4519ce9d014.htm), but plenty of good info buried in there.
So if the job fails when using the base service account rather than a higher privileged proxy it says to me that the service account does not have correct permissions. If it errors, then you need to find out why. Here are some tips on doing just that, in particular look at using a CmdExec step, and the Package Store File Permissions section.
Scheduled Packages
(http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html)
Friday, March 9, 2012
Execute SQL Task not running?
Hi All,
I have a strange problem that I can't find any information about and was hoping someone could help me out.
Some background:
* I am calling an SSIS package from ASP.Net using the in-process, .net methods.
* I am importing a flat file into a staging SQL Server table using the OLE DB Destination.
* After I import the data, I call some Execute SQL Tasks that does some cleaning of the data.
* I then move the data from the staging table to a final table.
Here's the problem:
When the SQL Task like this:
WHILE @.@.rowcount > 0
UPDATE Staging_Table
SET [Test1] = REPLACE([Test1], SUBSTRING([Test1], PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]), 1), ' ')
WHERE PATINDEX('%[^a-zA-Z0-9 ]%', [Test1]) <> 0
is supposed to be run, sometimes it will, sometimes it won't. The character replacement always works when I test the package through BIDS but when I call the package from code, sometimes the task doesn't seem to get called. Or, at least, it isn't updating the table correctly. The staging table is poplated and copied to the final table but the character replacement doesn't occur in-between.
Does anyone have any advice on the possible causes or where to investigate further?
Thanks,
s.
Turn on logging so you can find out what's going on.
-Jamie
|||Hi Jamie,
The log doesn't show anything different between a correct and incorrect run. I think I may have figured out an answer though. I finally figured out that the problem only occurs when I run multiple packages on multiple threads at the same time. Although none of the packages update the same table, perhaps it is something with the @.rowcount that is causing trouble? I was able to fix the problem (as far as I have seen so far) by changing the package executions to use dtexec.
s.
Execute SQL Task issue
I'm having a very strange issue in SSIS packages. The Execute SQL Task is no longer showing up in the Toolbox where it would normally be under Control Flow Items. After a quick check against another laptop that seems to be the only item missing. I noticed this after uninstalling the Katmai CTP and reinstalling 2005.
Has anyone heard of such an issue?
I'm running SQL 2005 Developer Edition on a 32 bit version of Vista Business.
/resetuserdata Switch fixed the problem. I wouldn't normally go this path but I had already blown away everything SQL anyway...|||I'm experiencing the same problem. The Execute SQL task is missing from the Control Flow items of the Toolbox. That is the only one missing, all the rest are there.
I've tried to use the "Choose toolbox items..." menu option to add the task back to the toolbox but Execute SQL task is not listed among the available options. This is even though the file Microsoft.SqlServer.SQLTask.dll appears in the directory C:\Program Files\Microsoft SQL Server\90\DTS\Tasks along with all of the other available tasks in the toolbox.
I've also tried the Tools | Import Export Settings... | Reset all Settings menu option in BIDS and Visual Studio with no change.
I'm new to BIDS and Visual Studio so I'm not sure how to use the /resetuserdata switch as you've mentioned. But I'm assuming that it is similar to the reset all settings menu option.
I tried to register the dll Microsoft.SqlServer.SQLTask.dll with regsvr32.exe and even a complete reinstall of BIDS. Neither worked.
|||
You can use the resetuserdata switch by running:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe /resetuserdata
That has always cleared up these issues when I have encountered them.
Execute SQL Task issue
I'm having a very strange issue in SSIS packages. The Execute SQL Task is no longer showing up in the Toolbox where it would normally be under Control Flow Items. After a quick check against another laptop that seems to be the only item missing. I noticed this after uninstalling the Katmai CTP and reinstalling 2005.
Has anyone heard of such an issue?
I'm running SQL 2005 Developer Edition on a 32 bit version of Vista Business.
/resetuserdata Switch fixed the problem. I wouldn't normally go this path but I had already blown away everything SQL anyway...|||I'm experiencing the same problem. The Execute SQL task is missing from the Control Flow items of the Toolbox. That is the only one missing, all the rest are there.
I've tried to use the "Choose toolbox items..." menu option to add the task back to the toolbox but Execute SQL task is not listed among the available options. This is even though the file Microsoft.SqlServer.SQLTask.dll appears in the directory C:\Program Files\Microsoft SQL Server\90\DTS\Tasks along with all of the other available tasks in the toolbox.
I've also tried the Tools | Import Export Settings... | Reset all Settings menu option in BIDS and Visual Studio with no change.
I'm new to BIDS and Visual Studio so I'm not sure how to use the /resetuserdata switch as you've mentioned. But I'm assuming that it is similar to the reset all settings menu option.
I tried to register the dll Microsoft.SqlServer.SQLTask.dll with regsvr32.exe and even a complete reinstall of BIDS. Neither worked.
|||
You can use the resetuserdata switch by running:
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\devenv.exe /resetuserdata
That has always cleared up these issues when I have encountered them.
Sunday, February 19, 2012
Execute Package Task behavior
I’m using a For Loop container to with an Execute Package Task inside, looping until a folder is empty.I’ve noticed some strange behaviors:
1. The child package keeps creating new connections.I start with 3 connections to the DB and when the For Loop container is done I’ve got 364 connections.
2. The Execute Package Task is pulling the wrong version of the package I’ve specified.I’m using a package saved to the File System and there’s only one copy on the drive.I’ve verified the path is going to the correct location.
Does anyone have a work-around for the ‘connection generation’ issue?
TIA
Eric
In your first question, do you load child packages from SQL Server? If yes - make sure you have connection pooling OFF.
Subscribe to:
Posts (Atom)