Connect to SP in remote SQL server

[Guest]

I'm able to connect to my stored procedure in my local database but not able
to connect to my stored procedure in the remote database.

I use several different UserID and Password that all should work. It also
fails when I try a different SP on the remote server. All local SP work. I
copy local SP to remote SQL but still can not connect to remote SP.

 

[Kevin Yu [MSFT]]

Hi,

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you're trying to connect to a remote
SQL server using VS.NET's Server Explorer. But cannot open and edit the
stored procedure on remote server. If there is any misunderstanding, please
feel free to let me know.

Here, I suggest you try the following:

1. Create a new data connection in Server Explorer using sa account.
2. Connect to the data source and open an SP.

If you cannot open the SP, is there any error messages? Also, can you open
a table?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

I'm not using VS.NET's Server Explorer. Below is a copy of my code, I
replaced my sa password with password for this post (for security purposes).
The code works when I change Server=(db) to Server=(local), db is the remote
server. My local license database is a copy of db license database.


The error occurs at oCn.Open(), the error message is "
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.Data.SqlClient.SqlException: SQL Server does not exist or access
denied.
at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean&
isInTransaction)
at
System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
at System.Data.SqlClient.SqlConnection.Open() ……………………"




string sConnString = "Server=(db);Database=License;Integrated
Security=False;User ID=sa;password=password";
string sProc = "prGet_DealerInfo";


using (SqlConnection oCn = new
SqlConnection(sConnString))
{
using (SqlCommand oCmd = new
SqlCommand(sProc, oCn))
{
oCn.Open();
oCmd.CommandType =
CommandType.StoredProcedure;


oCmd.Parameters.Add("@sDealerNum", SqlDbType.NChar, 6);

oCmd.Parameters["@sDealerNum"].Value = txtDealerNum.Text;


oCmd.Parameters.Add("@sSaleID", SqlDbType.Int, 10);

oCmd.Parameters["@sSaleID"].Direction = ParameterDirection.Output;


oCmd.Parameters.Add("@sSiteName", SqlDbType.NVarChar, 40);

oCmd.Parameters["@sSiteName"].Direction = ParameterDirection.Output;


oCmd.Parameters.Add("@sName", SqlDbType.NVarChar, 40);

oCmd.Parameters["@sName"].Direction = ParameterDirection.Output;


oCmd.Parameters.Add("@sAmount", SqlDbType.Decimal, 10);

oCmd.Parameters["@sAmount"].Direction = ParameterDirection.Output;

oCmd.ExecuteNonQuery();
oCn.Close();

string sSaleID =
oCmd.Parameters["@sSaleID"].Value.ToString();
string sSiteName =
oCmd.Parameters["@sSiteName"].Value.ToString();
string sName =
oCmd.Parameters["@sName"].Value.ToString();
string sAmount =
oCmd.Parameters["@sAmount"].Value.ToString();

}
}



Also, I checked ODBC connections and added db to User DSN, System DSN and
File DSN but still get same error when I run the code.

 

[Kevin Yu [MSFT]]

Hi,

Is db the remote server name? If so, please remove the brackets, and use
the following as connection string.

string sConnString = "Data Source=db;Database=License;Integrated
Security=False;User ID=sa;password=password";

It seems that the server cannot be found or it is a permission issue. If
the above connection string doesn't work, can you connect to the remote
database using Query Analyzer?

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

 

[Guest]

It worked!! Thank you.

To think it was a small change of removing the brackets.

Thanks again.

 

[Kevin Yu [MSFT]]

You're welcome.

Thanks for sharing your experience with all the people here. If you have
any questions, please feel free to post them in the community.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."