DataAdapter.Fill gives error "Could not find server 'System' in sys.servers."

Question

I'm trying to get some values from my SQL Server database, I can connect to it with:

SqlConnection conx = new SqlConnection();
SqlConnection conx4 = new SqlConnection("Data Source=.\\Sistema_Deci;Initial Catalog=sistema_decisiones_financieras;Persist Security Info=True;User id=sa;Password=somepassword;timeout=120;");
conx = conx4;

conx.Open();
conx.Close();

Up to that point it goes perfectly, however when I try to get values from it it goes wrong, code is following:

try
{
    SqlCommand cmd2 = new SqlCommand("SELECT * FROM Ordenes_Servicios", conx);
    SqlDataReader reader1;
    DataTable dtordenes = new DataTable();

    conx.Open();

    SqlDataAdapter sqlDA = new SqlDataAdapter(cmd2.ToString(), conx.ConnectionString);
    sqlDA.Fill(dtordenes);  // <--- at this point the error occurs

    conx.Close();
}
catch(Exception ex)
{}

The exception is:

System.Data.SqlClient.SqlException (0x80131904): Could not find server 'System' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

So far I've tried this on SQL Server to see the name of the servers, obviously there isn't any "System" server in the results, however I don't get why is it trying to connect to 'System' Instead of 'Sistema_Deci'

select name from sys.servers

Oh my Visual Studio is 2017 (Same problem with 2019) and running SQL Server 2019 Express since I couldn't install 2014 which is the one I've been using so far (idk why, but it started throwing errors while installing 2014).

I got one way that worked out, however that's not how I'm trying to program it because it's a lot of extra work to map everything out

SqlCommand cmd2 = new SqlCommand("SELECT * FROM Ordenes_Servicios", conx);

SqlDataReader reader1;
DataTable dtordenes = new DataTable();

dtordenes.Columns.Add("ID");
dtordenes.Columns.Add("Cliente");

conx.Open();

reader1 = cmd2.ExecuteReader();

while (reader1.Read())
{
    string temp = reader1["ID_Reporte"].ToString();
    string temp2 = reader1["Cliente"].ToString();
    dtordenes.Rows.Add(temp, temp2);
}

Change `SqlDataAdapter sqlDA = new SqlDataAdapter(cmd2.ToString(), conx.ConnectionString);` to `SqlDataAdapter sqlDA = new SqlDataAdapter(cmd2);` — Chetan, Aug 12 '21 at 01:22
https://stackoverflow.com/questions/6073382/read-sql-table-into-c-sharp-datatable — Chetan, Aug 12 '21 at 01:24
I'm laughing so hard, I can't believe it but that totally solved the issue, can you please explain to me why was that the issue? — Alanprime, Aug 12 '21 at 01:35
You are doing `new SqlDataAdapter(cmd2.ToString(), conx.ConnectionString);` which will pass `"System.Data.SqlClient.SqlCommand"` and the connectionstring to the constructor. The first string is expected to the actual SQL query not the `"System.Data.SqlClient.SqlCommand"`. The other constructor of SqlDataAdapter accepts only `SqlCommand`. You just needed to use that. https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldataadapter.-ctor?view=dotnet-plat-ext-5.0 — Chetan, Aug 12 '21 at 01:48

score 2 · Accepted Answer · answered Aug 12 '21 at 05:29

This is wrong:

SqlDataAdapter sqlDA = new SqlDataAdapter(cmd2.ToString(), conx.ConnectionString);

cmd2 is a SqlConnection, which doesn't have a ToString of its own, so it uses the default one from object which is simply the type of the object. This means your "SQL" as the data adapter saw it, ends up being "System.Data.SqlClient.SqlConnection" and your sqlserver thinks you're calling a stored procedure on another server called "System" (in a database called Data, in a schema called SqlClient, for a proc called SqlConnection)

Just do this:

var dt = new DataTable();
using var da = new SqlDataAdapter("SELECT * FROM Ordenes_Servicios", conx);
da.Fill(dt);

Throw all that other code away; it's all extraneous. You only need these 3 lines. A dataadapter knows how to open a connection itself

score 0 · Answer 2 · answered Aug 12 '21 at 05:34

You are getting error because of following code:

SqlDataAdapter sqlDA = new SqlDataAdapter(cmd2.ToString(), conx.ConnectionString); //SqlDataAdapter(String, String)

If you are going to use this overload, you may simply use something like this:

SqlDataAdapter sqlDA = new SqlDataAdapter("SELECT * FROM Ordenes_Servicios", "Data Source=.\\Sistema_Deci;Initial Catalog=sistema_decisiones_financieras;Persist Security Info=True;User id=sa;Password=somepassword;timeout=120;"); //SqlDataAdapter(String, String)

OR

SqlDataAdapter sqlDA = new SqlDataAdapter(cmd2); //SqlDataAdapter(SqlCommand)

OR

SqlDataAdapter sqlDA = new SqlDataAdapter("SELECT * FROM Ordenes_Servicios", conx); //SqlDataAdapter(String, SqlConnection)

Also keep only one connection as conx and use it:

SqlConnection conx = new SqlConnection("Data Source=.\\Sistema_Deci;Initial Catalog=sistema_decisiones_financieras;Persist Security Info=True;User id=sa;Password=somepassword;timeout=120;");

Read more info on SqlDataAdapter on Docs.

DataAdapter.Fill gives error "Could not find server 'System' in sys.servers."

2 Answers2