Please help, MAS -> SQL integration (linked server etc)

[CoderMan1]

I have been trying to get SQL server to link to MAS for the last week. Meaning I am trying to create a linked server to MAS or create an extended stored procedure to read MAS data and return it to SQL server.

So far I have had success reading MAS data from a regular application, but when I attempt to use an extended stored procedure I get all kinds of varying results. Nothing works consistently and its becoming very frustating. I dont know if its the way that SQL server manages connections internally or what but the same exact code in my VB.NET Windows Application works fine, but when its executed by SQL Server it connects sometimes (but never returns data correctly) and sometimes I get an "Invalid handle" error like this:

System.Data.Odbc.OdbcException
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcConnectionString constr, OdbcEnvironmentHandle environmentHandle)
at System.Data.Odbc.OdbcConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.Odbc.OdbcConnection.Open()
at ImportMASCompany.ImportCompany(String CompanyCode)

and here is my VB code that SQL calls through an extended stored procedure:

Public Shared Function ImportAllCompanies() As Integer

Dim conn As SqlConnection

Try

conn = New SqlConnection("Context Connection = True")
conn.Open()

Dim sqlReader As SqlDataReader
Dim cmd As SqlCommand
cmd = conn.CreateCommand
cmd.CommandText = "SELECT * FROM sysobjects"
sqlReader = cmd.ExecuteReader()
SqlContext.Pipe.Send(sqlReader)

Catch ex As Exception

SqlContext.Pipe.Send(ex.ToString())

Finally

SqlContext.Pipe.Send("Successful")

End Try


End Function




Has anyone ever tried this or gotten a MAS linked server setup right in SQL Server? If so, please help. Im about ready to quit!

 

[CoderMan1]

PS Im using SQL 2005 or 2000 (neither work), MAS90 ODBC Driver 3.21 and VB.NET 2005 Express

 

[BigLouie]

Many people have it working. You need to go to the Sage Talk Forums that you can reach through the Sage Support web page if you are up to date on your maintenance. Post this question in the MAS90/200 section and those who have done this should be able to detail how to do it. Never done it myself but I know a lot have and have helped others. Not suppose to be that hard.

 

[BigLouie]

Forgot to mention that you need to use a silent DSN not the normal SOTAMAS90

 

[CoderMan1]

What do you mean a silet DSN?

 

[BigLouie]

If you have a version of MAS prior to 4.05 you should have a users manual CD. In the section on Crystal there is detail on how to setup a silent DSN. Basically it is a copy of the normal DSN but with the company code, user ID and password entered in. With the normal DSN these values are not present.

 

[CoderMan1]

Another thing I tried was creating a dynamic DSN in VB.NET and connecting to that, this had all the login information in it. While creating the DSN worked, connecting to it had the same effect as any other code. This also doesnt explain why the working code I pasted works in a windows app and not from an extended stored proc. I just had a feeling it had something to with the way SQL server manages connections

 

[CoderMan1]

BigLouie, I noticed you posted on those forums quite a bit. After reading for a while I think I figured out that I need to update my ODBC driver. Currently 3.21.

Im on the ProvideX drivers page and Im wondering which one I need to install to help me. The Local version, Client Server, Client, etc...and I also installed version 4.0 and its asking me to put in an activation code and serial# or something. Where would I get those?

 

[BigLouie]

The ODBC driver is updated when you run work station setup. With your CDs you should have a letter with your unlocking code. Based on your question I would suggest having your reseller assist with the install.

 

[CoderMan1]

Well Im an outside contractor working with a client. I am just trying to get things done as fast as possible without having to wait on them to get me CD's or serial numbers. But I guess I must in this case.

In any case, which driver should I be downloading? Ive already installed the Local Driver, which seems to work but is running in demo mode. This is the error I get (which is different than the normal "catostrophic failure" error I get) is:

OLE DB provider "SQLNCLI" for linked server "MAS92" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "MAS92" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "MAS92".


Im assuming that this is because I did not install the license. Hopefully once I get the license information I can get around this problem.

 

[BigLouie]

My suggestion is to work with their reseller. Installation is not hard but you do have to know what you are doing.