Archives

Top Ten Sessions for M2M Administrators at the 24 Hours of Pass

If you are a M2M Administrator, you should be studying SQL Server. I encourage anyone who works with M2M to study the SQL programming language, how SQL Server works, advanced reporting techniques, etc. In my opinion, if you don’t have at least a basic understanding of all things SQL, you cannot effectively administer M2M. I like to think that’s why many of you read this blog.

The Professional Association for SQL Server wants to help. On May 19th, they will offer 24 one hour long sessions on various aspects of SQL Server. These are some of the best minds in SQL Server and you can watch them free. Yes free, as in no strings attached, absolutely free. You can register here.

To encourage as many of you to participate as I can, I’ve listed the 10 sessions that I think M2M Admins should attend, and more importantly why.

  • Manage Your DBA Career, Don’t Let It Manage You. This is what I’ve been talking about. Take control of your career. Run twice as fast. Don’t become cheap furniture. Protect yourself in this awful economy.
  • Database Design Fundamentals Every SQL Administrator should have a grasp as to how to create databases, tables, and the rules of normalization. Watch the session and see how many fundamentals that the M2M Database violates.
  • Introduction to PowerPivot PowerPivot is a hot new technology for Excel 2010 and SQL Server 2008 R2. Yes, I know you don’t have those yet, none of us do. However, PowerPivot is incredibly slick and powerful and umm… it makes Interactive Dashboards.
  • Solving Common Business Problems With Microsoft PowerPivot. Wouldn’t it be nice if your users could create their own reports directly in Excel?
  • PowerPivot for Financial Decision Makers Want your boss to do back flips? Show him/her PowerPivot with the slicer options and stand back.
  • Easier-Than-Ever Report Authoring in SSRS 2008 R2 You’re not using SQL Reporting Services yet? Why not? I’m in the process of creating some instructional videos for you to see how to use it with M2M. In the meantime, get your feet wet with this demo.
  • Reporting Services Enhancements in SQL Server 2008 R2 Aren’t you sick of those awful Visual FoxPro reports by now?
  • High-Performance Functions Every admin needs to learn T-SQL. I’ve shown you how to create some simple functions in the past. Creating your own functions is an absolutely necessary job skill.
  • Top 10 Mistakes on SQL Server Anyone who has ever used SQL Server has committed some of these. Watch this session and learn how to avoid them.
  • What’s Really Happening On Your Server? 15 Powerful SQL Server Dymanic Management Objects Ever get the complaint, “M2M is so slow today?” Well, learn how to figure out why.

As an aside, you should all be members of PASS. Take advantage of these opportunities to learn. Join your local user groups and attend a SQL Saturday. Those are typically free as well. Volunteer for your local group, like I do. We’re having a SQL Saturday in Dallas later this month.

Network yourself, grow your skills, meet other geeks, recession proof your career, and have fun.

6 comments to Top Ten Sessions for M2M Administrators at the 24 Hours of Pass

  • Judy

    Thanks for the tips! Always appreciate your insight!

  • Dana

    David,

    I’ve been reading your blog since I took over IT consulting for a small company using M2M. I’ve been scouring the web looking for any performance tips but they are pretty scarce. M2M 3.6, VFP database, 2003 SBS raid1 volume (may have something to do w/ it), 4-6 users typically accessing M2M simultaneously and performance is degraded as more users open up M2M. I’ve compressed and re-indexed the tables… no improvement. Do you have any suggestions of where to even begin to troubleshoot? Is this simply a limitation of the file-based foxpro database in a multi-user environment? Any advice you or other readers could provide would be much appreciated. Thanks.

  • Dana, I’ll reply to you via e-mail.

  • This would have been really good to know had I read it when you wrote it. From the link it looks like they’ll be available for download in 2 more weeks though.

    I hadn’t heard anything about PowerPivot until a couple of days ago when my boss brought it up in passing. The little information I’ve found so far seems rather intriguing. I’m definitely interesting in finding out more about that.

  • John Sitka

    Hi, nice site. Always wanted to go to PASS.

    In thanks for all the good reading, Here is a framework for building M2M report programs when you want to put the entire data environment in a stored procedure.

    This model allows a user versed in SQL to skip the foxpro
    layer for the most part but still integrate with the M2M reporting structure.
    The method has been to create a separate company customization database
    right next to the live company. SQL authentication on this database allows read
    only to the m2mdataxx ones, this is a good spot to develop and test your
    stored procedures or query stings against m2mdataxx. You still need to
    hook everything up to m2m as per the manuals plus a few little hidden tricks
    but it is a tough thing to write about.
    This part will get you the custom cursor and .prg file.

    Let’s go VVV

    **V These Parameters seem to be needed at the top of every .prg
    Parameters lcFilter, lcDateRange, lcSortRange, lcAdvFilter, lcOtherVariable, lcSortOrder

    **V unquote for debug session
    **V Run VFP then choose Program>DO. And browse to …/M2MWin/m2m.exe
    **V this allows the use of VFP debugger
    *DEBUG
    *SUSPEND

    **V These are declaritive statements for foxpro I believe
    **V similar to DIM or var, the docs would probably describe their usage
    **V exactly, seems they behave as Basic type = variant
    Local;
    lcSQL

    **V you can drop (uncommented) these little run to msgbox (flags) wherever you need them. Not as good as debugger but
    **V it can get tiresome opening up debug all the time if you can’t or do not know how to exit gracefully
    **V I’ve always found m2m does not behave consistently within a foxpro session compared to a regular run,
    **V YMMV. In this case we are looking at what foxpro has done to our sortrange
    *msgbox(lcSortRange)

    **V we then conditionally manipulate the passed in variable(s) to strip away the preformatting that is generated
    **V from the Report Parameter Interface (RPI)
    **V here we are chosing between differnt divisional Sales Orders with 4 or 5 characters
    IF lcSortRange “.T.”
    ** note the function, more on that later
    lcBothSO= STRIPM2MFORSO(lcSortRange)

    IF LEN(lcBothSO) = 4
    lcLeftSO=lcBothSO
    lcRightSO=lcBothSO
    ENDIF

    IF LEN(lcBothSO) = 8
    lcLeftSO=SUBSTR(lcBothSO,1,4)
    lcRightSO=SUBSTR(lcBothSO,5,8)
    ENDIF

    IF LEN(lcBothSO) = 5
    lcLeftSO=lcBothSO
    lcRightSO=lcBothSO
    ENDIF

    IF LEN(lcBothSO) = 10
    lcLeftSO=SUBSTR(lcBothSO,1,5)
    lcRightSO=SUBSTR(lcBothSO,6,10)
    ENDIF

    **V This was a best guess to trap an error. The RPI sorts out alot of the possible ones but you never know.
    **V This handling is easily expanded depanding on your needs.
    **V An incremental way of doing things. Ask your users to report stange behaviour otherwise you will be
    **V testing combinations of picks and entries until the cows come home.
    IF LEN(lcBothSO) = 9
    msgbox(“can’t range accross companies”,”ERROR”,0)
    RETURN
    ENDIF

    ELSE

    lcLeftSO=”A0001″
    lcRightSO=”X9999″

    ENDIF

    **V checking to see if we are on the right track
    * msgbox(lcLeftSO)
    * msgbox(lcRightSO)

    **V Look up in the foxpro docs ‘CREATE table’ you are going to need one to utilize the data environment for layout
    **V editing. Here is the runtime partner in crime, foxpro calls them cursors. The .dbf file you will bring to your report
    **V is an exact mirror of this cursor below. keep your field name lengths 9 or less

    CREATE Cursor just_a_tb (;
    afield C(8),;
    bfield C(8),;
    cfield C(12),;
    dfield N(16,5),;
    efield C(8),;
    ffield C(10),;
    gfield C(12),;
    hfield N(16,5),;
    ifield N(16,5))

    **V Simple ODBC string note the connection is to the utility database, if you think about the future it will sure be nice
    **V to have all your procs in a separate place come upgrade time.
    **V SQLSTRINGCONNECT returns an interger and that interger represents a connection
    **V It is needed as a Parameter for SQLEXEC()
    **V STORE is some kind of foxpro assignment idiom
    STORE SQLSTRINGCONNECT(“Driver={SQL Server};Server=;Database=;Uid=xxxx;Pwd=xxxx;”) TO lnhandle

    **V here is the calling string whatever you can script in the query analyser is now at your beck and call!!
    lcSQL = “EXEC get_stuff_for_just_a_tb @leftso = ‘” + ;
    lcLeftSO + ;
    “‘, ” + ;
    "@rightso = ‘” + ;
    lcRightSO + ;
    “‘”

    **V neat way to check the Call only you will see it and you can leave it in your live code
    **V Someone says “that report won’t work” while on the phone you ask them what they typed in and see the result right away
    *******************************************************

    IF ALLTRIM(osession.cusername) = “my_login”
    msgbox(lcsql)
    ENDIF

    ******************************************************

    **V here is what we have been working towards look it up in VFP docs
    =SQLEXEC(lnHandle,lcSQL,’just_a_tb’)
    **^ If you look at this run of code without the comments
    **^ you will see the triple structure string=somethingsomething=SQLEXEC()
    **^ ….exactly the program flow style in place here???, my advice
    **^ don’t let it throw you when trying to read foxpro.
    **>(I think the ‘=’ is meaning less)

    **V little message box on right hand side of screen..you won’t need it because we are now set based code rather that
    **V iteration to get the goods, your reports will fly but layout processing is still dependent on the client
    WAIT WIND ‘TRANSACT-SQL via SQLEXEC()’ NOWAIT

    **V checked this out in MSSQL profiler. Seems to work just right but then there is a second connection and disconnect
    **V for no apparent reason. (scary but minor compared to M2M on the whole)
    SQLDISCONNECT(lnhandle)

    Return

    **V These are some very basic examples of what all those M2Mxxx() functions do but
    **V without the docs and team environment of M2M at your displosal you are on your own
    **V keep it simple and mimic the structure of the Foxpro code in the base install prg’s
    **V we haven’t used the date one in our example but you can see the pattern,
    **V tweak and play as needed
    FUNC STRIPM2MFORDATE(lcReturn)
    lcReturn = STRTRAN(lcReturn,’ ‘,”)
    lcReturn = STRTRAN(lcReturn,'(‘,”)
    lcReturn = STRTRAN(lcReturn,’)’,”)
    lcReturn = STRTRAN(lcReturn,’LADETAIL.FDATE’,”)
    lcReturn = STRTRAN(lcReturn,’>’,”)
    lcReturn = STRTRAN(lcReturn,’=’,”)
    lcReturn = STRTRAN(lcReturn,”,”)
    lcReturn = STRTRAN(lcReturn,’=’,”)
    lcReturn = STRTRAN(lcReturn,'<','')
    lcReturn = STRTRAN(lcReturn,'UPPER','')
    lcReturn = STRTRAN(lcReturn,'.AND.','')
    lcReturn = STRTRAN(lcReturn,'"','')
    lcReturn = ALLTRIM(lcReturn)
    Return lcReturn
    ENDFUNC

    This technique handles most reporting needs but the power is in the Transact-SQL scripting, for more info try http://www.sqlteam.com.

  • […] freely download them. All you have to do is sign up for PASS, which is free. Go back and read the Top Ten Sessions for M2M Administrators article and download the […]

Leave a Reply to Get Your Learnin On | Made2Mentor

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>