Connection string for Remote Database connectivity(SQL Server2000)

hai,

I am working on ASP.NET 2003 with SQLServer2000. My application requires to be connected to the Database which is there in my Headoffice.

My SQLServerName is "MyDBServer\TestDB"

Database Name is "WebTO", UserID="******" and Password="******"

My Remote Server IP Address is "192.168.1.2" and its Static IP is "58.93.61.235"

I have specified the Connection string for my Remote Server as

"Provider=SQLOLEDB.1;Server=58.93.61.235\TestDB;UID=******;PWD=******;Database=WebTO"

(or)

"Provider=SQLOLEDB.1;Server=58.93.61.235\TestDB,1433;UID=******;PWD=******;Database=WebTO" (1433 is the Port number of the Remote SQLServer)

but it is giving the error " SQL Server does not exist or access denied".

if i execute the same application at my Headoffice (Remote Server) by changing the Connection string as

"Provider=SQLOLEDB.1;Server=192.168.1.2\TestDB;UID=******;PWD=******;Database=WebTO"

then, it is working fine.

Can anyone tell me where i went wrong or what i have to specify in my Connection string so that i can access my Remote Database Server.

Thanks in Advance,

Srinivas.

try

server=192.168.1.2,1433 ................... hope it will work

|||

192.168.1.2,1433 is also not working.

But, i don't know how can we access the Remote Server just by giving the IP without giving it's static IP.

|||

did u try with the static ip

58.93.61.235,1433

|||

My friend it seems that u aren't aware of my previos post where i have clearly mentioned that i have already tested with Static IP, 58.93.61.235,1433 but it is giving the error "SQL Server doesn't exist or access denied".

|||

Hi Srinivas,

have you enabled remote connections on your sql server? one more thing why are you specifying provider=SQLOLEDB?

do take a look at this link >>>>http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_22604523.html, under the threadhttp://forums.asp.net/t/1118398.aspx

migth be helpful to you

thanks,

satish.

|||

Hi Satish,

Thanks for ur reply.

Remote Connections on my SQL Server option is already in Enabled state. ( "Allow other SQL Servers to connect remotely to this SQL Server using RPC" check box is already in Checked state.)

And Regarding Provider=SQLOLEDB, when i am using OLEDB object, i need to provide the Provider information right?

And the link which u had given is asking to get registered in that site which is not a Free site.

Thank you.

Srinivas.

|||

Hi,

The IP (192.168...) works because you are in LAN. Each machine can be connected with each other and the IP of the remote machine is setting to "192.168..." (You can useipconfig -all to check it in command prompt.) You can't use Query Analyzer to connect the romote database by your static IP neither. So, just assign your machine with your static IP and have a try.

Thanks.

Connection problems from VB

When monitoring errors concerning connection and updating data on my MS SQL
Server2000 from a VB application I get these errors.
-2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
(send()).
-2147467259 [Microsoft][ODBC SQL Server Driver]Connection error
-2147467259 [Microsoft][ODBC SQL Server Driver][SQL
Server]SqlDumpExceptionHandler: Process 79 generated fatal exception
c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
-2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
(send()).
The application has been communicating with an MS SQL Server2000 for 5-6
years with no problems of this kind. The problem started when I installed my
application to run against a new MS SQL Server2000 at a new company, and I
am wondering if it could have something to do with how the server is
configured.
I am not sure why the err.description ref. to ODBC SQL Server Driver because
as shown below I use OLEDB connection object.
Any Ideas on how to manage this problem?
Below you can see example of my functions in VB:
Private Sub cmdSelect_Click()
funConnect
set rstMyRecordset = rstExecData(mySelectSQL)
funDisconnect
End Sub
this procedure works just fine
this next code works fine as well, but if I try to run a select as above
after the update if fails with the error in function rstExecData
Private Sub cmdUpdate_Click()
funConnect
rstExecData(myUpdateSQL)
funDisconnect
End Sub
Functions used:
Function funConnect() As Boolean
On Error GoTo errHandler
strCnn =
" driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabase"
Set cnn = New ADODB.Connection
cnn.CursorLocation = adUseClient
cnn.Open strCnn
Exit Function
errHandler:
funConnect = False
End Function
Function funDisconnect() As Boolean
On Error GoTo errHandler
If cnn.State = 1 Then
Set cnn = Nothing
Else
Exit Function
End If
errHandler:
funDisconnect = False
End Function
Public Function rstExecData(strSQL As String) As ADODB.Recordset
Set rstExecData = New ADODB.Recordset
rstExecData.CursorType = adOpenDynamic
rstExecData.LockType = adLockOptimistic
rstExecData.Open Trim(strSQL), cnn
End Function
TIRislaaWhat about following line:
strCnn =
" driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabase"
This is call to OLEDB for ODBC Provider and ODBC Driver.
Why not OLEDB Driver for SQL Server?
strCnn = "Provider=SQLOLEDB.1;Data Source=DC01;Password=mysapassword;User
ID=sa;Initial Catalog=mydatabase;"
Regards
Jacek
"Tor Inge Rislaa" wrote:

> When monitoring errors concerning connection and updating data on my MS SQ
L
> Server2000 from a VB application I get these errors.
>
> -2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
> (send()).
> -2147467259 [Microsoft][ODBC SQL Server Driver]Connection error
> -2147467259 [Microsoft][ODBC SQL Server Driver][SQL
> Server]SqlDumpExceptionHandler: Process 79 generated fatal exception
> c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this proces
s.
>
> -2147467259 [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite
> (send()).
>
> The application has been communicating with an MS SQL Server2000 for 5-6
> years with no problems of this kind. The problem started when I installed
my
> application to run against a new MS SQL Server2000 at a new company, and I
> am wondering if it could have something to do with how the server is
> configured.
>
> I am not sure why the err.description ref. to ODBC SQL Server Driver becau
se
> as shown below I use OLEDB connection object.
>
> Any Ideas on how to manage this problem?
>
>
> Below you can see example of my functions in VB:
>
> Private Sub cmdSelect_Click()
> funConnect
> set rstMyRecordset = rstExecData(mySelectSQL)
> funDisconnect
> End Sub
> this procedure works just fine
> this next code works fine as well, but if I try to run a select as above
> after the update if fails with the error in function rstExecData
> Private Sub cmdUpdate_Click()
> funConnect
> rstExecData(myUpdateSQL)
> funDisconnect
> End Sub
> Functions used:
>
> Function funConnect() As Boolean
> On Error GoTo errHandler
> strCnn =
> " driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabas
e"
> Set cnn = New ADODB.Connection
> cnn.CursorLocation = adUseClient
> cnn.Open strCnn
> Exit Function
> errHandler:
> funConnect = False
> End Function
>
> Function funDisconnect() As Boolean
> On Error GoTo errHandler
> If cnn.State = 1 Then
> Set cnn = Nothing
> Else
> Exit Function
> End If
>
> errHandler:
> funDisconnect = False
> End Function
> Public Function rstExecData(strSQL As String) As ADODB.Recordset
> Set rstExecData = New ADODB.Recordset
> rstExecData.CursorType = adOpenDynamic
> rstExecData.LockType = adLockOptimistic
> rstExecData.Open Trim(strSQL), cnn
> End Function
> TIRislaa
>
>
>|||This connectionstring works OK for select statements, but if I try to update
data, the application hangs totally (don't answer)
Is it possible that there are settings on the SQL server that blocks update
and insert statements?
TIRislaa
"JacekZ" <JacekZ@.discussions.microsoft.com> skrev i melding
news:0B026699-EDBA-4B7E-8B1F-BFCED79AA73E@.microsoft.com...
> What about following line:
> strCnn =
> " driver={SQLServer};server=DC01;uid=sa;pw
d=mysapassword;database=mydatabas
e"
> This is call to OLEDB for ODBC Provider and ODBC Driver.
> Why not OLEDB Driver for SQL Server?
> strCnn = "Provider=SQLOLEDB.1;Data Source=DC01;Password=mysapassword;User
> ID=sa;Initial Catalog=mydatabase;"
>
> Regards
> Jacek
> "Tor Inge Rislaa" wrote:
>