Sage 100

Has anyone had any success in running an automated job to dump MAS tables to a SQL 2008 table? I know DSD has SQL mirroring but we are looking for the cost effective route since they only need it for two tables. We used to do it with SQL 2000 and the DTS jobs but they took that option away in SQL 2005/2008. Any suggestions would be GREATLY appreciated. I can't figure it out for the life of me in SQL 2008.

I think @DanBurleson might have something he sells that does this

@BrianBuck I also have a program called Reflection that does this (designed to do a scheduled dump, not the real time interaction DSD does), and works with all versions of MAS (tested back to 3.71, probably works on earlier versions as well).

Thanks @PhilMcIntosh, I would be interested in taking a look at it. Any info you can PM me would be helpful and I can run it by my client. They are currently on 4.4 (soon to upgrade to 4.5) and they are using SQL 2008 Express.

Can't you set something up in windows scheduler to kick off the job?

The first problem is I can't find where to save an import job (or edit it) after I create it in SQL 2008. If I could find it then yes I probably could use Windows Scheduler.

@BrianBuck you can create a linked server in SQL to the Providex Database, including logon credentials like in ODBC. Then create a stored procedure to run a query to copy data from the source in Providex into your SQL table. How your SP is constructed (Select Into, Make Table, ect.) depends on the size of the data file you are pulling from. BizNet uses Linked Servers to pull data from MAS 90 for BizInsights. I works reasonably quickly.

Sorry, once the SP is constructed, you can create a SQL Agent job to run the SP on a schedule. Be sure the SQL Agent Service is running.

Thanks @ShawnSlavin I am going to give that a whirl. Looks like I can create Linked Servers in Express version too. Now to just see if I remember how to write SP's.

I don't think you can use the SQL Agent in Express though.

Arg...you are correct @RobertWood , looks like they will have to spring for SQL Standard.

Always glad to rain on your parade.

Oh, no worries @RobertWood, I am used to it, remember, I am a Sage Reseller. Sage does it to us all the time.

That's not rain. Just sayin'.

There's always a way. Try this: http://www.sqlteam.com/article/scheduling-jobs-in-sql-server-express

...or this: http://www.mssqltips.com/sqlservertip/1083/free-job-scheduling-tool-for-sql-server-express-and-msde/

...and so you know you have choices, try this: http://social.msdn.microsoft.com/Forums/en-US/sqlexpress/thread/343d4708-e29d-4356-b889-4ad231f2340b

...for $79 bucks, http://www.valesoftware.com/products-express-agent.php

Whoa...great info again @ShawnSlavin! Thanks again for this info, it is very helpful.

@RobertWood, you are right...that is more brown in color isn't it?

Yeah. We may need more than an umbrella in Nashville.

I am skipping Summit this year...but you might want to bring more than umbrella...I would bring some deep waders and some shovels for what I am sure Sage will be dropping on you.