473,386 Members | 1,702 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,386 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 4362
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Steven | last post by:
Hi All, I've installed MySQL 4.1.12 on my server, I can connect from the local machine with navicat, but not from a remote machine. I checked with 'nedstat' and there is something listening on...
4
by: banz | last post by:
Hello I have a problem to resolve: I wrote a Perlscript which caches data from a server (local on my machine) I would like to have a other connection to a remote server but I don't know how to...
5
by: mayamorning123 | last post by:
A comparison among six VSS remote tools including SourceOffSite , SourceAnyWhere, VSS Connect, SourceXT, VSS Remoting, VSS.NET To view the full article, please visit...
3
by: bcanavan | last post by:
I'd like to connect to an mdb(v2002+) on the web server which contains readOnly linked tables. My remote users are not permitted to connect directly to the sql server db that the mdb links to...
14
by: DaTurk | last post by:
I am makeing a Multicast server client setup and was wondering what the difference is between Socket.Connect, and Socket.Bind. It may be a stupid question, but I was just curious. Because I...
14
by: Marcus | last post by:
I have a function that simply returns TRUE if it can connect to a particular Sql Server 2005 express, or FALSE if it cannot. I am getting some strange error codes returned when the computer that...
0
by: tamayi | last post by:
I have a problem (like most others posting issues on this forum :) ) I have a remote server running Windows XP SP2, with both SQL Server 2005 Express with Advanced Features and SQL 2000...
8
by: BD | last post by:
I am developing C# win form app to work with remote database on SQL Server 2005. Problem scenario is as follows: 1. a form is open that has downloaded dataset to local cache 2. computer is...
7
by: RN1 | last post by:
Is it possible to connect to my local SQL Server 2005 database from a remote web server? If yes, what ConnectionString do I use? Thanks, Ron
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...

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.