Technical and Installation Discussions

[ACT-TMN]

Im trying to create a view in SQL Express 2005 which resides on the same server as MAS using ODBC but I am having a tough time figuring out the syntax I should use to connect and query the data.

My goal is to create a view that extracts data from two very large tables in MAS, and updates my two tables in SQL every night so I can build reports from SQL instead of MAS. Im hoping to get some faster results.

Any Ideas or recommendations?

[bpoole · Accepted solution]

I would recommend creating a new DSN conection on the SQL server to be used just for this purpose.  You can then hard code a company, username and password in the DSN.  Once that tests correctly, add a linked server into SQL referencing only the DSN in the linked server setup. I will be glad to send you screenshots if you would like.  If you will need to extract data from multiple companies, you would need to have multiple DSN's.I have a client with 60+ companies that does an extraction and for simplicity we built a stored procedure that create the linked server on the fly during the extraction, but that adds a level of complexity that you may not need.

Keep in mind two things.  First you will need to write the extract in a stored procedure b/c views in SQL will not update tables.  Second, SQL Express does not come with a scheduler, so you will need to use a additional method for scheduling the job to run.  

I have used http://www.valesoftware.com/products-express-agent.php  for clients where we have done the exact same extraction technique.  It is not freeware, but well worth the $79.

[ACT-TMN]

Thats what I thought, but whats wierd, is when I create the linked server and then run a select statement referencing the linked server DNS, it sits there forever.. I have to actually end task on SQL Server as it sits there for hours with no response. Maybe I was using incorrect syntax. Do you happen to have a sample syntax statement I could use. The screens would be greatly appreciated. My email is sbrickey@advancedclutch.com. Thanks so much for your help!

[bpoole]

I sent some screenshots of my set up.  you may want to also look at permission issues between the SQL server and the MAS server (or user running the SQL service).

[ACT-TMN]

I think our SPAM filter blocked your email. Would you mind re-sending to scooterb84@gmail.com. Thanks again for your help.

[bpoole]

I sent it to your gmail account as well.

[ACT-TMN]

That definitly helps. Let me run this and I will let you know the results. Thanks again for your help!

[ACT-TMN]

THANKS! Your reccomendation worked and I can now query the tables. You were a huge help! Thanks a lot!

[Jackie]

I am trying to set up a link to MAS 90 4.3 in SQL Server 2005.  I have previously had a setup to an earlier version of MAS 90 which used the Prodidex 32 driver.  Since I am creating a new DSN connection I need to make a new linked server in SQL.  That is where my problem lies.  I am not real familiar with SQL.  I have created the linked server but when I execute my stored procedure, it just runs...  I do not get an error but it never completes. 

Do you have an example of the linked server setups?  The following is the script I ran to create my link:

EXEC sp_addlinkedserver @server='MAS904x',

@provider='MSDASQL',

@srvproduct = 'MAS904x',

@datasrc='MAS904x'

My System DSN name is MAS904x with the MAS 90 4.0 ODBC Driver.

Thanks for any help you can give.

Jackie

[BigLouie]

Jackie, Welcome to the Sage Community. While I may not have an answer for you I would suggest that you start a new thread. As this one is marked as "Solved" not many people will come here to read new post such as yours.