471,573 Members | 1,744 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,573 software developers and data experts.

Connect to SP in remote SQL server

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.

Nov 17 '05 #1
5 4225
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."

Nov 17 '05 #2
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.Get PooledConnection(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]" wrote:
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."

Nov 17 '05 #3
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."

Nov 17 '05 #4
It worked!! Thank you.

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

Thanks again.

"Kevin Yu [MSFT]" wrote:
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."

Nov 17 '05 #5
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."

Nov 17 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Steven | last post: by
reply views Thread by XIAOLAOHU | last post: by
reply views Thread by leo001 | last post: by
reply views Thread by lumer26 | last post: by
reply views Thread by Vinnie | last post: by
reply views Thread by lumer26 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.