Problem with SqlCommandBuilder.DeriveParameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

H

I have the following code

sqlConnection1.Open()
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("[SOMEUSER].[PAK_TEST.P_OPEN]", sqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd)

Since I don't know the parameternames in advance I need to call DeriveParameters to fill the Parameters collection on the SqlCommand object. Previously this has not been a problem since the OleDbCommand object does not need to know the name of the parameters. I’m not very happy doing the extra roundtrip to the server getting the parameters, and it does not make it better that DeriveParameters bombs out when the stored procedure name contains periods.

As you see in the code I try to derive parameters from [SOMEUSER].[PAK_TEST.P_OPEN]
This is a perfectly valid Procedurename.

When I call DeriveParameters it throws a System.Data.SqlClient.SqlException with the following message: "Could not find stored procedure ''.
When doing a quick watch on the SqlCommand object after the exception I found that CommandText was changed to "[[SomeUser]]..sp_procedure_params_rowset".

The same also happens if I try to set the commandtext of the sqlcommand object in designmode. It also magically changes the commandtext from [SOMEUSER].[PAK_TEST.P_OPEN] to [SOMEUSER].[PAK_TEST.[P_OPEN]], which of course is terrible wrong

My conclusion is that DeriveParameters totally ignores all delimiters and will therefore never work correctly on stored procedurenames containing periods

I guess this is a known problem…
Do you have a workaround or fix? Btw: Changing the procedure names is not an option

Thank
Xandu
 
What if you try to use quoted identifiers instead of square brackets ?

Patrice

--

Xandu said:
Hi

I have the following code:

sqlConnection1.Open();
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("[SOMEUSER].[PAK_TEST.P_OPEN]",
sqlConnection1);
cmd.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);

Since I don't know the parameternames in advance I need to call
Click to expand...
DeriveParameters to fill the Parameters collection on the SqlCommand object.
Previously this has not been a problem since the OleDbCommand object does
not need to know the name of the parameters. I’m not very happy doing the
extra roundtrip to the server getting the parameters, and it does not make
it better that DeriveParameters bombs out when the stored procedure name
contains periods.
As you see in the code I try to derive parameters from [SOMEUSER].[PAK_TEST.P_OPEN]
This is a perfectly valid Procedurename.

When I call DeriveParameters it throws a
Click to expand...
System.Data.SqlClient.SqlException with the following message: "Could not
find stored procedure ''."
When doing a quick watch on the SqlCommand object after the exception I
Click to expand...
found that CommandText was changed to
"[[SomeUser]]..sp_procedure_params_rowset".
The same also happens if I try to set the commandtext of the sqlcommand
Click to expand...
object in designmode. It also magically changes the commandtext from
[SOMEUSER].[PAK_TEST.P_OPEN] to [SOMEUSER].[PAK_TEST.[P_OPEN]], which of
course is terrible wrong.
My conclusion is that DeriveParameters totally ignores all delimiters and
Click to expand...
will therefore never work correctly on stored procedurenames containing
periods.
 
I've tried with
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("\"SomeUSer\".\"PAK_TEST.P_OPEN\"", sqlConnection1)
cmd.CommandType = CommandType.StoredProcedure
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd)

This also fails, but with a different errormessage
Could not locate entry in sysdatabases for database "SomeUser". No entry found with that name. Make sure the name is entered correctly

To me it seems that DeriveParameters splits the sp-name into db.user.sp-name using period (.) and not quoted identifiers or square brackets as it should

Xandu
 
In this case, I don't see how to workaround other than doing your own
derivation (either by calling the same procedure or by using directly
Information_Schema.Parameters). Additionaly you'll be able then to cache
this information to save server roundtrips...

Patrice

--

Xandu said:
I've tried with:
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand("\"SomeUSer\".\"PAK_TEST.P_OPEN\"",
sqlConnection1);
cmd.CommandType = CommandType.StoredProcedure;
System.Data.SqlClient.SqlCommandBuilder.DeriveParameters(cmd);

This also fails, but with a different errormessage:
Could not locate entry in sysdatabases for database "SomeUser". No entry
Click to expand...
found with that name. Make sure the name is entered correctly.
To me it seems that DeriveParameters splits the sp-name into
Click to expand...
db.user.sp-name using period (.) and not quoted identifiers or square
brackets as it should.
 
Back
Top