Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Are possible errors caught within the procedure or your vb code ? How do you know that the procedure is not executed successfully if you get no additional error and why do you think it stops ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

Executing SQL Stored Procedures in VB

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

Quote:

Originally Posted by SQLusername

I am having trouble executing a series of 4 stored procedures from VB. The connection code connects and the first 3 stored procedures run through, although the 4th procedure stops running mid execution. No errors are reported to VB. When I run the series of procedures in the SQL Server Query Analyzer everything completes as it should. Anyone have any suggestions on what could be the problem?

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.|||

Quote:

Originally Posted by ck9663

tons of reasons, check for these possibilities:
1. object locking
2. the fourth stored proc is not returning anything
3. your server is configured to time-out after a certain time.

Can you descibe what object locking is and how to remedy it? Also using SQL Server Enterprise Manager where can I edit time-out settings?

Executing Oracle Stored Procedure with output parameters using ADO.NET connection

I am a bit confused by an issue that I am having with executing an Oracle stored procedure (with an output parameter) using an ADO.NET connection object. I am able to get this working using an OLEDB connection, but I have no idea why the ADO.NET connection doesn't work. (Bug, by design, or my ignorance?) Actually, I can even get this to work if I use the .NET Providers for OLE DB\Microsoft OLE DB Provider for ORACLE if we set the connectionType to ADO.NET. This is the error that I am receiving:

[Execute SQL Task] Error: Executing the query "pkg_utility_read.test_out_var " failed with the following error: "The OracleParameterCollection only accepts non-null OracleParameter type objects, not SqlParameter objects.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

It is also worth mentioning that the ORACLE stored procedure has an out parameter with a NUMBER datatype which I think maps to the ADO.NET Int32 datatype. I guess OLE DB datatypes are more closely mapped to ORACLE datatypes. In OLE DB you can set the parameter to double and the ORACLE stored procedure to NUMBER and it works.

Any help on this would be most appriciated.

hi Jason,

I got the same error. What driver are you using? I use .NET provider Oracle client data provider.

Can you also tell me how did you successfully invoke the stored proc using OLEDB for Oracle?

I have an sp with 1 input parameter of type Number and 14 output parameters some of type Number and some Varchar2.

Can you tell me the sql statement syntax to use and the parameter mapping to use. I know that the parameter name starts with 0,1,2...

Please help

thanks.

sandeep

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.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.

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.

ExecuteReader: Connection property has not been initialized.

I have a web form that is generating an error and I can't seem to figure out why for the life of me. Below is the code:

Private Sub VerifyNoDuplicateEmail()
 Dim conn As SqlConnection
 Dim sql As String
 Dim cmd As SqlCommand
 Dim id As Guid
 sql = "Select UserID from SDCUsers where email='{0}'"
 sql = String.Format(sql, txtEmail.Text)
 cmd = New SqlCommand(sql, conn)
 conn = New SqlConnection(ConfigurationSettings.AppSettings("cnSDCADC.ConnectionString"))
 conn.Open()
 Try
 'The first this we need to do here is query the database and verify
 'that no one has registed with this particular e-mail address
 id = cmd.ExecuteScalar()
 Response.Write(id.ToString & "<BR>")
 Catch
 Response.Write(sql & "<BR>")
 Response.Write("An error has occurred: " & Err.Description)
 Finally
 If Not id.ToString Is Nothing Then
 'The e-mail address is already registered.
 Response.Write("Your e-mail address has already been registered with this site.<BR>")
 conn.Close()
 _NoDuplicates = False
 Else
 'It's safe to add the user to the database
 conn.Close()
 _NoDuplicates = True
 End If
 End Try
 End Sub
Web.Config
<appSettings>
<!-- User application and configured property settings go here.-->
<!-- Example: <add key="settingName" value="settingValue"/> -->
<add key="cnSDCADC.ConnectionString" value="workstation id=STEPHEN;packet size=4096;integrated security=SSPI;data source=SDCADC;persist security info=False;initial catalog=sdc" />
</appSettings>

Can anyone show me the error of my ways?

Thanks,
StephenPlease elaborate on "generating an error". An exact error mesage will probably go a long way in helping you correct your problem.

Also, please use parameters and not string concetenation to build your SQL commands!!

Instead of this:

sql = "Select UserID from SDCUsers where email='{0}'"
sql = String.Format(sql, txtEmail.Text)
cmd = New SqlCommand(sql, conn)

do something like this:

sql = "Select UserID from SDCUsers where email=@.email"
cmd = New SqlCommand(sql, conn)
cmd.Parameters.Add(New SqlParameter("@.email", SqlDbType.VarChar, 99)).Value = txtEmail.Text

Terri|||Terri,

The exact error is the title of the thread "ExecuteReader: Connection property has not been initialized."

Thanks,

Stephen|||Terri,

I tried the changes you suggested and still recieved the same error. After taking a little time off and coming back the to the problem I finally found the error of my ways. I was initializing the CMD object before the CONN object. Thanks for you help.

Stephen|||Oh yes, so you were. :-)

Please use parameters anyway -- that suggestion was something of an aside. And next time you post please include the error message. We could have helped you a lot quicker with that information!

Terri

ExecuteNonQuery: Connection property has not been initialized

I am trying to create a web form that will be used to create new users. The
first step that I am taking is creating a web form that can check the
username against a database to see if it already exists. I would it to do
this on the fly, if possible. When I execute my current code, I get the
following error:

ExecuteNonQuery: Connection property has not been initialized

Below is the code from the page itself:
--
<!-- #INCLUDE FILE="../include/context.inc" -->
<!-- #INCLUDE FILE="../include/db_access.inc" --
<script language="VB" runat="server"
Sub CheckButton_Click(Sender as Object, e as EventArgs)

Dim result As Int32
Dim cmd As OdbcCommand

cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OdbcType.Int ).Direction =
ParameterDirection.ReturnValue

cmd.Parameters.Add( "@.userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")

cmd.ExecuteNonQuery()
result = cmd.Parameters("result").Value

If result <> 1 Then
CheckResults.Text="<font color=""#ff0000"">Username already
exists!</font>"
Else
CheckResults.Text="<font color=""#009900"">Username is
available.</font>"
End If

end Sub

</script
<html><body>
<form runat="server">
<asp:TextBox id=userName runat="server" />
<asp:Button id=CheckButton runat="server" Text="Check Username"
onClick="CheckButton_Click" /
<p>
<asp:Label id=CheckResults runat=server />
</form>
</body></html>
--

Can anyone see why I might get this error? Here are some more details of
the error:

Line 15: cmd.Parameters.Add( "@.userName", OdbcType.VarChar, 100 ).Value =
Request.Form("userName")
Line 16:
*Line 17: cmd.ExecuteNonQuery()
Line 18: result = cmd.Parameters("result").Value

Thank You,
Jason WilliardHi Jason,

To me it seems that you are still using asp type techniques of data access. You will have to initate your dbconnection object before you can actually use any database related functions.

With your code you are missing ofdb_conn variable. Which I am presuming that you have in include file, but this will not work with asp.net.

HTH|||It's looks like your maye be geeting error because of following reason.

1.Your connection string is not correct or not open as we can't see when and where it was intialize/open .
2.check your store procdure and see if you are passing correct parameter,correct typen etc.
3.In your code it looks likes may be you have open your connection in db_access.inc but i don't think it's good approach .you should open the connection in same sub and close as soon as you you finished.Or if you want to write neat and clean code and also don't want to wire same code agaian then create data layer class where you can perform all the database realted operation.

Arvind Malik|||I made some changes to the page so that the db connection is all done from within the Sub. Below are the code changes that I made:

--
Sub CheckButton_Click(Sender as Object, e as EventArgs)

Dim db_conn_str As String
Dim db_conn As OdbcConnection
Dim resultAs Int32
Dim cmdAs OdbcCommand
Dim context_dsn_nameAs String = "adwarefilter"

db_conn_str = "dsn=" & context_dsn_name & ";"
db_conn = New OdbcConnection( db_conn_str )
db_conn.Open()

cmd = new OdbcCommand( "(? = CALL CheckUserExists(?))", db_conn )
cmd.CommandType = CommandType.StoredProcedure
--

Now I am getting a new error message:

--
Exception Details: System.Data.Odbc.OdbcException: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.

Source Error:

Line 28: cmd.Parameters.Add( "@.username", OdbcType.VarChar, 100 ).Value = Request.Form("userName")
Line 29:
Line 30: cmd.ExecuteNonQuery()
--

Any suggestions?|||What is this query: "(? = CALL CheckUserExists(?))", ? Where is the SQL Query?

Brian|||This is a call to a Stored Procedure. The SQL query is within the Stored Proc.|||I am pretty sure that your stored procedure call should look like this:

cmd = new OdbcCommand( "{CALL CheckUserExists(?)}", db_conn )

Note that I made these changes:
-- removed the question mark (?) and the equal sign (=)
-- changed a set of parentheses to a set of curly braces

But, I have a question -- why oh why are you using the System.Data.Odbc class instead of System.Data.SqlData? Are you using SQL Server 7?

Terri|||Actually, I hadn't noticed you are using a ReturnValue, sorry!! The question mark and equal sign can remain. The problem is likely just the use of parentheses instead of curly braces.

cmd = new OdbcCommand( "{? =CALL CheckUserExists(?)}", db_conn )

Terri

ExecuteNonQuery requires an open and available Connection

Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
message when trying to UPDATE a very large table:
"ExecuteNonQuery requires an open and available Connection. The connection's
current state is closed."
The UPDATE command affects all the rows in the column. The confusing part
is - it only occurs on a very large table (over 1M rows). It does not orrur
on smaller tables. So I don't think it actually has anything to do with the
connection.
Any ideas would be greatly appreciated.Just a guess, the connection might have timed out or broken. Try increasing
the timeout
"Neil W." wrote:

> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The connection
's
> current state is closed."
> The UPDATE command affects all the rows in the column. The confusing part
> is - it only occurs on a very large table (over 1M rows). It does not orr
ur
> on smaller tables. So I don't think it actually has anything to do with t
he
> connection.
> Any ideas would be greatly appreciated.
>
>
>|||Neil W. (neilw@.NOSPAMTHANKYOUnetlib.com) writes:
> Using SQL 2000 Developer, Windows XP, and DotNet 2.0 I get this error
> message when trying to UPDATE a very large table:
> "ExecuteNonQuery requires an open and available Connection. The
> connection's current state is closed."
> The UPDATE command affects all the rows in the column. The confusing
> part is - it only occurs on a very large table (over 1M rows). It does
> not orrur on smaller tables. So I don't think it actually has anything
> to do with the connection.
Might be so, but I would put my bets on the ADO .Net code. How does
the relevant bits look like.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Execute SQL Task: UDF not taking parameters

Hi,

I have an Execute SQL Task in my SSIS Package.
Now, this Execute SQL Task has the following query (Connection Type is OLE DB):

Code Snippet

SELECT dbo.udf_CommonDateTime_Get (GetDate(), ?) As User_Datetime

I want 2 things from this Task:
1) It should take the 2nd argument to the UDF from a variable.
2) It should store the value returned by this SELECT statement into another variable.

So, I go ahead and modify the Parameter Mapping for the Task. Here I add the Input variable name, Data type and I give the Parameter Name as 0.

I also modify the Result Set for the Task. Here, I specify the Result Name as User_Datetime and give the appropriate Variable Name.

I am getting an error here and I believe it is due to the input parameter. The UDF is not getting the 2nd argument correctly.

My questions:
1) Has the Execute SQL Task been designed to handle UDFs like this. If not, then where am I going wrong?
2) What is the work-around for this? I need to pass a parameter (variable) to the UDF.

Thanks in advance.

Regards,
B@.ns

The error message is:

Code Snippet

Execute SQL Task: Executing the query "SELECT dbo.udf_Common_DateTime_Get (GetDate(), ?)

As User_Datetime" failed with the following error: "Syntax error, permission violation, or other nonspecific error". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Task failed: Set UserDateTime

As a workaround, you could you create a new variable to store your sql statement. Set the 'EvaluateAsExpression' property of the variable to 'true'. Then set the expression like this...

"SELECT dbo.udf_CommonDateTime_Get (GetDate()," + user::VariableNameHere + ") As User_Datetime"

Then in 'SourceVariable' property of the execute sql statement to 'Variable' and then choose the variable name.

|||

Hi Martin,

Thank you for the reply. At least it gives me some hope

Unfortunately, I am getting this error:

Code Snippet

The expression for variable "varQuery" failed evaluation. There was an error in the expression.

If I remove the user::VariableNameHere part, it works fine...

Any ideas?

Thanks again.

Regards,

B@.ns

|||

You replaced user::VariableNameHere with the actual name of your variable correct?

|||

Martin,

It worked!!

Thank you so much!!

I had to do this:

Code Snippet

"SELECT dbo.udf_CommonDateTime_Get(GETDATE(), " + (DT_WSTR, 1) @.[User::VariableName] + ") As UserDateTime"

The only thing worries me is that @.[User::VariableName] can be NULL.

I will have to handle that.

Thanks again.

Regard,

B@.ns

Execute SQL Task Error

Hi:

I am getting the following error message while trying to run a Execute SQL task with Variables in BIDS. My connection type is ADO.NET . My Variables defined are

Varout and Varin. Both are String type Variables. @.varout has a value set to "Category" and @.varin has a value set to "Test Category". I am using the expression

" Select * into " + @.[User::VarOut] + " FROM " + @.[User::Varin]

The expression eveluates correctly. The error I get when i run the package is:

Package Validation Error. Failed to lock Variable "Select * into TestCategory from category" for read access with error 0XC0010001. The Variable cannot be found. This occurs when an attempt is made to retrieve a variable from the variables collection on a container during the execution of package and the variable is not there. The Variable name may have changed or the Variable is not being created.

Can anyone please tell me what I am doing wrong or where do I need to look at?.

Thank you

AK

Can anyone please please give me a solution. This is really urgent. I found a KB describing a issue related to Script task but not sure if it is applicable to this issue. It involves applying a hot fix. Anyone from MS please comment.|||this link might help: http://msdn2.microsoft.com/en-us/library/ms141003.aspx|||

Thanks Duane. That Article helped. I was selecting the SQLSourcetype as Variable and that was throwing errors. I changed it and my package runs like a charm!!!.

Thanks again.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection type

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Task : Input and Output parameters in tsql stataments with ADO.NET connection ty

Hi Everyone,

I haven't been able to successfully use the ADO.NET connection type to use both input and output parameters in an execute sql task containing just tsql statements (no stored procedure calls). I have successfully used input parameters on their own but when i combine it with output parameters it fails on the simplest of tasks.

I would really find it beneficial if you could use the flexibility of an ADO.NET connection type as the parameter marker and parameter name can be referenced anywhere throughout the sql statement in no particular order. The addition of an output parameter would really make it great!!

Thanks

What is the error you get?

have you search this forum and/or the web

http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=ADO.NET+parameters&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||When you say "output parameters" are you talking about using the result set feature?

Note that the result name should be 0, 1, 2, etc... when mapping to a variable name.|||For more than one result, Make sure that you are using the result set of "Full Result Set" then shred this result set using a for each loop, and assign each to a variable.|||

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

|||

PK2000 wrote:

Hi,

By output what i am referring to is the direction of the paramter. What i am trying to do is pass an an input and an output parameter to an execute sql task with the ado.net connection type and to populate a variable in the package, User::Test2, with the value being returned by the output parameter.

ResultSet : None

SQLSourceType: DirectInput

SQLStatement:

Update dbo.table1

Set value = @.Test1

Set @.Test2 = 'Test 3'

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

The error i am getting is

Error: 0xC002F210 at Execute SQL Task 2, Execute SQL Task: Executing the query "

Update dbo.table_1

Set value = @.Test1

Set @.Test2 = 'Test 3'

" failed with the following error: "The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 4 ("@.Test2"): Data type 0xE7 has an invalid data length or metadata length.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Thanks

Use the resultset feature to get output. I'm trying to figure out why you'd want to assign a literal value to an output parameter in a SQL task. There are easier ways of getting that populated. The way you've got it setup only has inbound parameters.|||

Hi Phil,

I will try the resultset feature.

Why i was assinging a literal value to an output parameter was only to test that it works, that is User::Test2 is assigned the value "Test3".

Thanks

|||

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

|||

PK2000 wrote:

Hi Ryan,

I am not actually returning a resultset back. If possible, what i am trying to do is assign a value to @.Test2 in the sql statement which will map this value back to User::Test2. In the Parameter mapping i have declared the following:

VariableName Direction DataType Parameter

User::Test1 Input String @.Test1

User::Test2 Output String @.Test2

Thanks

You can't. Use the resultset feature to return a value from the SQL to a variable.|||

Thanks Phil and Ryan!

I was able to use a resultset and then shred the resultset using a foreach loop.

Execute SQL Insert Statement from Script Task using package OLEDB Connection

Is there a way to directly do this in one step(Execute SQL Insert Statement from Script Task using package OLEDB Connection)?

Right now I'm using a script task to build a sql insert statement using package variables (to fill values) populated by certain logic in the package.

Then assigning this command string to a package variable.

Then using a sql execute task to execute this variable.

A link to an article or code would be greatly appreciated.

You can use the classes in the System.Data.OleDB namespace and the ConnectionManager.AquireConnection method to do this. It is easiest to do this if you have an ADO.NET OLEDB connection, as that returns a managed object that you can use in your script task.

See jaegd's post in this thread for a sample. It shows use of the SQL Client, and it's a script transform rather than a task, but hopefully it's enough to get you started. If not, post back and we'll help you resolve any difficulties.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1895867&SiteID=1

Execute Package Task says Login failed for 'sa'

Please help me with this error, I am desperate.

SSIS package "Clear.dtsx" starting.
Error: 0xC0202009 at Clear, Connection manager "10.11.60.30.msdb.sa": An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'sa'.".
Error: 0xC00220E4 at Execute Package Task: Error 0xC0202009 while preparing to load the package. An OLE DB error has occurred. Error code: 0x%1!8.8X!.
.
Task failed: Execute Package Task
Warning: 0x80019002 at Clear: The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Clear.dtsx" finished: Failure.
Did you run this package in BIDS or the command line? As we're talking about in your other similar post, SSIS doesn't store passwords when promoting them to the server for execution. Either that, or you have the wrong password indicated for "sa."|||Child package is using different username so error should not be for 'sa'. Parent is using sa in Connection which is used to call this child package.|||

Hi Fahad,

If you are absolutely certain the sa password is supplied correctly, the issue may lie with the Package ProtectionLevel property.

First, I would ensure the sa password is correct. Make sure you can connect to the msdb database on the target server using the sa account credentials and that you can execute some simple query: Select 1 As 'One' or Select GetDate() are my favorites. If this succeeds:

Try setting the ProtectionLevel property on the Control Flow properties to either EncryptAllWithPassword or EncryptSensitiveWithPassword. You will need to supply a PackagePassword for this test as well - it's located just above the ProtecionLevel property.
When you're done, rebuild and re-deploy the package. You will need to supply the password when executing the package. If this succeeds, the issue is with the Package ProtectionLevel.

To address this, I recommend using Windows Authentication to connect to the server.

Hope this helps,
Andy

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can
you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...[vbcol
=seagreen]
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>[/vbcol]|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it
interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I d
on't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand wh
at you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...en">
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
Jack
We need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>
|||Then it is a BizTalk issue. Perhaps BizTalk isn't very intelligent in how it interacts with SQL
Server, or BizTalk isn't used in the most efficient way? I can't tell as I don't know anything about
BizTalk.
I suggest you raise the issue in a BizTalk group, as they will understand what you want to achieve,
and can respond to how you try to achieve that goal. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <Jack@.none.com> wrote in message news:Y3T0f.251$865.187@.read3.inet.fi...
> Well it is this BizTalk Orchestration
> http://msdn.microsoft.com/biztalk/
> BTW, it is PowerToys in their own words ;)
>
> -- Original Message --
> From: "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com>
> Newsgroups: microsoft.public.sqlserver.server
> Sent: Friday, September 30, 2005 5:51 PM
> Subject: Re: execute multiple queries over a single connection
>
>

execute multiple queries over a single connection

Hi!
Is SQL Server 2000, just a toy?
Accordind validation tests it needs 30.000 connections to move
170 rows from Linked Server.
JackWe need more information. How do you "move 170 rows from Linked Server"? Can you show us some code?
How did you determine that 30000 connection were needed?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Jack" <none@.INVALIDmail.com> wrote in message news:AYa%e.137$xG6.129@.read3.inet.fi...
> Hi!
> Is SQL Server 2000, just a toy?
> Accordind validation tests it needs 30.000 connections to move
> 170 rows from Linked Server.
> Jack
>