HELP...Server does not exist or access is denied

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

HELP...Server does not exist or access is denied

Author

Topic

drman
Starting Member

38 Posts

Posted - 2006-05-24 : 11:03:40

I have an application that was written in VB 6. It is using SQL 2K as the database. I am running SQL 2k PE on a laptop where the development takes place and I also have a Windows 2000 server running SQL 2k (called testserver).

The application will create a recordset containing items from one table that are to be inserted into another table. Originally, the application was developed by writing the data in the recordset to a directory on the server as a CSV file. Every 15 minutes, a DTS job kicks off to check to see if there are any “update” files. This has been working fine for a couple of years, but now we have been asked to modify the application so the updates are written directly to the destination table, thus eliminating the 15 minute window for the DTS jobs.

I am looping through the recordset, but instead of calling a function that writes to the text file, I call a procedure that uses a stored procedure to insert the records directly into the table.

When I run the new functionality with about 5500 items in the source table, and insert directly to the SQL 2K PE on my laptop, the app runs fine, but when I run the new functionality to testserver with the same records, the app generates the following error at about 1900-2000 records:

[DBNETLIB][ConnectionOpen (Connect().]SQL Server does not exist or access is denied.

I set a breakpoint in the debugger at 1920 records and then began walking through the steps, one record (insert) at a time. I got all of the way up to 3100 records and did not get the error. I then jumped out of the debugger and within 50 records, it bombed out.

Code to follow.

Any Ideas or suggestions will be greatly appreciated.

Thanks

DRMAN

drman
Starting Member

38 Posts

Posted - 2006-05-24 : 11:06:23

Here is some sample code. I have search the existing topics on this site and found the same error message in various instances, but I still appear to be a Fish out of water on this one...

I create a connection when I open the form using the following code

goConn.CursorLocation = intCursorType
goConn.ConnectionString = "Provider = sqloledb;" & _
“Data Source = testserver;" & _
"Initial Catalog = xxx;" & _
"User ID = xxx;" & _
"Password = xxx; '" & _
"Network Library = dbmssocn"
goConn.Mode = adModeReadWrite
goConn.ConnectionTimeout = 15
goConn.Open

Here is a simplified version of the loop :

Private Sub cmdPost_Click()

Dim oRSPost As New Recordset
Dim oSPCommand As ADODB.Command
Dim oParam As ADODB.Parameter

Set oSPCommand = New ADODB.Command
oSPCommand.ActiveConnection = goConn
oSPCommand.CommandType = adCmdStoredProc
oSPCommand.CommandTimeout = 3000

Set oParam = oSPCommand.CreateParameter
oParam.Type = adInteger
oParam.Direction = adParamInput
oParam.Value = CInt(frmViewMovementListing.lblListID.Caption)

'' Post All (includes extra and missing)
'' -------------------------------------
oSPCommand.CommandText = "usp_FetchMediaList"
oParam.Name = "usp_FetchMediaList"
oSPCommand.Parameters.Append oParam

Set oRSPost = oSPCommand.Execute

Do While Not oRSPost.EOF

If strDTSDir <> "" Then
-- Writes to text file for DTS insert
strDataLine = Trim$(gsAccountID) & "|" & _
Field2Str(oRSPost("MediaID")) & "|" & _
Field2Str(oRSPost("DataSetName")) & "|" & _
Field2Str(oRSPost("OutCode")) & "|" & _
Field2Str(oRSPost("LocationID")) & "|" & _
Field2Str(oRSPost("CreateDate")) & "|" & _
IIf(Field2Str(oRSPost("Scanned")) = "S", Field2Str(oRSPost("ReceiveDate")), "") & "|" & _
Field2Str(oRSPost("ReturnDate")) & "|" & _
Field2Str(oRSPost("RecallDate")) & "|" & _
Field2Str(oRSPost("RecalledBy")) & "|" & _
Field2Str(oRSPost("RecalledTo")) & "|" & _
Field2Str(oRSPost("ImportAction")) & "|" & _
Field2Str(oRSPost("LastUpdated"))
Handle.WriteLine (strDataLine)
Else
-- Inserts directly into SQL
Call InsertNewMediaInfo(Trim$(gintAccountNo), _
Field2Str(oRSPost("MediaID")), _
Field2Str(oRSPost("DataSetName")), _
Field2Str(oRSPost("OutCode")), _
Field2Str(oRSPost("LocationID")), _
Field2Str(oRSPost("CreateDate")), _
IIf(Field2Str(oRSPost("Scanned")) = "S", Field2Str(oRSPost("ReceiveDate")), ""), _
Field2Str(oRSPost("ReturnDate")), _
Field2Str(oRSPost("RecallDate")), _
Field2Str(oRSPost("RecalledBy")), _
Field2Str(oRSPost("RecalledTo")))
End If

oRSPost.MoveNext

Loop

End Sub

Private Sub InsertNewMediaInfo(AccountID As Integer, _
MediaID As String, _
DataSet As String, _
Outcode As String, _
LocationID As String, _
CreateDate As String, _
ReceiveDate As String, _
ReturnDate As String, _
RecallDate As String, _
RecalledBy As String, _
RecalledTo As String)

Dim oSPINMICommand As ADODB.Command
Dim oSPParams(10) As Variant

'Mark Media As Extra (insert)
Set oSPINMICommand = New ADODB.Command
oSPINMICommand.ActiveConnection = goConn
oSPINMICommand.CommandType = adCmdStoredProc
oSPINMICommand.CommandTimeout = 600
oSPINMICommand.CommandText = "usp_InsertMediaInfoData"

oSPParams(0) = AccountID
oSPParams(1) = Trim$(MediaID)
oSPParams(2) = Trim$(DataSet)
oSPParams(3) = Trim$(Outcode)
oSPParams(4) = Trim$(LocationID)
oSPParams(5) = Trim$(CreateDate)
oSPParams(6) = Trim$(ReceiveDate)
oSPParams(7) = Trim$(ReturnDate)
oSPParams(8) = Trim$(RecallDate)
oSPParams(9) = Trim$(RecalledBy)
oSPParams(10) = Trim$(RecalledTo)

oSPINMICommand.Execute , oSPParams

Set oSPINMICommand = Nothing

End Sub

Thanks

DRMAN

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-24 : 21:15:31

the error implies permission problem...

verify if you are able to connect to the live server using the credentials you supplied

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-25 : 04:24:13

See if this helps
http://support.microsoft.com/default.aspx?scid=kb;en-us;328306

Madhivanan

Failing to plan is Planning to fail

mr_mist
Grunnio

1870 Posts

Posted - 2006-05-25 : 04:29:05

Either way, this is an horrificly inefficient method of moving data between two tables. You'd be better of either using getrows() to pull all the info from the source into an array and then writing that back, or avoiding VB entirely and just use it to run a stored proc on SQL Server that does all the database work internally.

-------
Moo. :)

drman
Starting Member

38 Posts

Posted - 2006-05-26 : 12:47:08

Thanks for the responses. MM... I think there are better ways that I can modify the stored procs, In fact I have already done one and am working on others. I did solve the problem. The applicaton works fine if you have never made a SQL connection thru Windows XP ODBC Manager. I don't really understand why...

We took a brand new PC out of the Box and loaded the app and it processed 20k in records... over and over. Then we took one of the Laptops the app would give us the error on and rebuilt it. It worked fine. Once we created an ODBC connection using the ODBC manager, the app never worked again...even if we removed the connection thru the ODBC manager.

I have seen this error message on numerous sites and wonder if this has caused other issues.

Thanks again.

DRMAN

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-26 : 12:56:22

Someone is configuring the ODBC data source incorrect then. They are possibly clicking the Network button and specifying incorrect parameters. The screen that you fill out in the Network button modifies the registry and impacts all connections to SQL Server and not just the one via ODBC.

Open up regedit.exe, navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo.

Look at the right pane. What do you see?

Tara Kizer
aka tduggan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources