Mars Connection Problem

I am trying to make a connection to an SQL database held on myserver I am able to connect through a machine data source using access using the credentials as below the attempt fails at con.open with following error:

System.Runtime.InteropServices.COMException was caught
ErrorCode=-2147467259
Message="Named Pipes Provider: Could not open a connection to SQL Server [53]. "
Source="Microsoft SQL Native Client"
StackTrace:
at ADODB.ConnectionClass.Open(String ConnectionString, String UserID, String Password, Int32 Options)
at Client_Access.JobRequest.AddJob_Click(Object sender, EventArgs e) in C:\Documents and Settings\Robert\My Documents\Visual Studio 2005\Projects\ Client Access\ Client Access\JobRequest.vb:line 392

Dim con As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim sPassword As String, sUserID As String
sPassword = "abcde"
sUserID = "cClient"
Try
con.ConnectionString = "Provider=SQLNCLI;" _
& "Server=(myserver);" _
& "Database=transportRecs;" _
& "Integrated Security=SSPI;" _
& "DataTypeCompatibility=80;" _
& "UID=" & sUserID & ";" _
& "PWD=" & sPassword & ";" _
& "MARS Connection=True;"
Dim mySQL As String

mySQL = "SELECT * FROM dbo_jobitem " '& _
'" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset
With rst
.ActiveConnection = con
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.CursorType = ADODB.CursorTypeEnum.adOpenStatic
.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic
.Open(mySQL)
.MoveLast()
.MoveFirst()
.MoveLast()
.MoveFirst()
Debug.Print(.RecordCount)
End With

Catch ex As Exception
MsgBox(ex.ToString)
Finally
If (con.State = ConnectionState.Open) Then con.Close()
End Try

Can anyone help.

Regards,
Joe

The connection string is pooched. Use a UDL to write the connection string for you.

1. Open notepad

2. Save the blank file as test.udl to your desktop

3. Open the UDL and create and test your connection string.

4. Close and open in notepad

5. Copy the generated connection string

Adamus

|||

Hi Adamus Turner

I have follwed the instructions supplied. The connection is good using the connection string below:

con.ConnectionString = "Provider=MSDASQL.1;Password=abcde;Persist Security Info=True;User ID=cClient;Mode=ReadWrite;Extended Properties= DSN=TransportDB_Comp1;Description= ;UID= cClient;PWD=abcde;APP=Microsoft? Windows? Operating System;WSID=MIDLAPTOP2; transportRecs;Network=DBMSSOCN" & _

"MARS Connection=True;"

Dim mySQL As String

mySQL = "SELECT dwjobitemid FROM dbo_jobitem"

'";" '" WHERE [Custid] ='" & strTag & "'"

con.Open()

rst = New ADODB.Recordset

With rst

.ActiveConnection = con

.CursorLocation = ADODB.CursorLocationEnum.adUseClient

.CursorType = ADODB.CursorTypeEnum.adOpenStatic

.LockType = ADODB.LockTypeEnum.adLockBatchOptimistic

.Open(mySQL)

.MoveLast()

.MoveFirst()

.MoveLast()

.MoveFirst()

MsgBox(.RecordCount)

End With

When the connection attemps to open a record set (.open(mySQL) an error is called, Invalid object name? I know the table exists?

Regards

Joe

|||

Is the table name dbo_jobitem or dbo.jobitem?

It's a strange convention to use an underscore.

Adamus

|||

Also, do not post logins and passwords to databases.

Moderator, please remove login and password from connection string.

Adamus

|||

Hi Adamus

I have managed to sort out the problem, the issue was not with the connection string after I had used your advice the connection was good. The database tables when viewed on the sql server all had names starting “dbo_” I had attempted to look at the database schema.tables using “select * from information_schema.tables” out of vbnet but could not work out how you returned recordset!table_name in vb.net> I did managed to achieve this using vba. The table names returned were all shown minus the “dbo_” prefix I altered my sql to match and bingo.

Any user names, passwords etc used in my examples are all fictitious. And have been used for illustration only. Many thanks for your assistance with my postings

Regards

Joe

Manipulate various databases with only one connection

hi guys.

i want to know if is it possible to connect various databases with only one connection?

thx.

Yes if those "various databases" are linked.|||

ok...

but... how do i link them?

give me an example, or post a link, please.

thx.

|||Check out BOL for Linked Servers.|||

tell me, BOL means Books online?

thx.

|||Yes sir.|||

ok...

i am gonna check it out...

thx for the advice.

if i can't resolve my problem with this tip, i'll ask you again.

thx, and keep the good job.

Managing Excel execution through DTS

We have a convoluted DTS package. The package is stored and scheduled to run on database server1. Within the package we:
Make a connection to database server2
Execute a Process Task to execute EXCEL.EXE that is installed on server3
Pass parameters through the Process Task direct EXCEL.EXE to open a .xls file on server4
When the Excel file opens, an auto_exec macro in the Excel file being opened imports a text file local to server4 AND directs Excel to save it with another name on server4.

The questions are:
1. Excel is not installed on server1 so how do we direct Excel to execute on server3 rather than server1 where the DTS package is being executed?

2. And how do we control the security context that executes Excel through this use of automation?

3. Other than potential CPU competition, are there any significant issues with having Excel installed on a dedicated database server?Yikes! That is convoluted. Is there any particular reason you are doing things this way?|||Dude -

Are you into masochism? Enjoy sleepless nights?

Seriously - just pull the excel spreadsheet from the server you are reading from, open it on the machine with the dts package, do the manipulation & then copy the final result to wherever its going....

Dont make it any more complicated than it has to be - K.I.S.S.

Simple = Robust.

Cheers,|||Why do you have 4 servers in this scenario when you just want to modify some data on another server than where the DTS-package is situated?

What kind of data does this file contain, and why does it have to be updated via Excel?