CODEWITHSUNDEEP
c#sqldataadapter
BEK
BEK

Reputation: 1

Having a problem using SQL 4 Part Naming with C# SqlDataAdapter when a proc has parameters, with and without the SQL server being a Linked Server

The below has worked for me without fail for a long time now until recently when I needed to call a proc on a linked server that used 4 part naming (server.database.schema.object).

I've got a simple proc, ProcA that just returns a Select * based on a criteria.

From the config:

<add key="default" value="Data Source=serverXYZ;Initial Catalog=master;Integrated Security=SSPI;MultipleActiveResultSets=true;" />

SqlConnection SQL = new SqlConnection(ConfigurationManager.AppSettings.Get("default"));

Query = server.database.schema.MyProcCall
SqlCommand SQLCmd = new SqlCommand(Query, SQLCon);
SQLCmd.CommandType = CommandType.StoredProcedure;
SQLCmd.CommandTimeout = 180;
SqlParameter NewParam = new SqlParameter(Name, Value);
SQLCmd.Parameters.Add(NewParam)
DataSet SQLDS = new DataSet();
SqlDataAdapter SQLDA = new SqlDataAdapter(SQLCmd);
RecordCount = SQLDA.Fill(SQLDS);

The above runs just fine with a 4 part naming when the proc doesn't have a parameter, but returns this otherwise: "Invalid 3 part name format for TypeName."

I've tried setting my connection directly to the server, and go to a 3 part name (database.schema.object), the code works just fine for any proc, regardless of parameters.

I've confirmed I'm able to run each of these procs in SSMS as myself, so permissions shouldn't be an issue.

I've also tried tried adding the param with the specific DbType per parameter, but I get the same error.

Is there something extra that needs to be done when using a 4 part named call that I'm just missing? That error unfortunately doesn't have a whole lot of hits when I tried to research it.

Upvotes: 0

Views: 73

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

To have explicit control of the call use CommandType.Text and put the parameter in the call. With CommandType.StoredProcedure you're asking the driver to assemble the call for you, and it's not working. So something like:

SQLCmd.CommandText = "exec server.database.schema.MyProcCall @param";

Upvotes: 0

Related Questions