Cursor in SQL Server

In SQL procedures, a cursor make it possible to define a result set (a set of data rows) and performs complex logic on a row by row basis. By using the same mechanics, an SQL procedure can also define a result set and return it directly to the caller of the SQL procedure or to a client application.

A cursor can be viewed as a pointer to one row in a set of rows. The cursor can only reference one row at a time, but can move to other rows of the result setas needed.

To use cursors in SQL procedures, you need to do the followings:

  • Declare a cursor that defines a result set.
  • Open the cursor to establish the result set.
  • Fetch the data into local variables as needed from the cursor, one row at atime.
  • Close the cursor when done.
Example:

DECLARE @fName varchar(50), @lName varchar(50)
DECLARE cursorName CURSOR -- Declare cursor
LOCAL SCROLL STATIC

FOR

Select firstName, lastName FROM myTable

  OPEN cursorName -- open the cursor

     FETCH NEXT FROM cursorName
     INTO @fName, @lName
     PRINT @fName + ' ' + @lName -- print the name

     WHILE @@FETCH_STATUS = 0

        BEGIN

           FETCH NEXT FROM cursorName
           INTO @fName, @lName
           PRINT @fName + ' ' + @lName -- print the name

        END

  CLOSE cursorName -- close the cursor

DEALLOCATE cursorName -- Deallocate the cursor

Mere Clipper

an article by Roger Donnay, March, 1998

We often do not realize the importance of people and events until many years later when the dust has settled and we can put them into perspective. Three years ago I was asked to write an article for the CDGN Magazine. It was titled "Back to the Future" and it dealt with the the role of Clipper in 1995 and into the future. I recently broke out my old copy of CDGN magazine and reread the article to see if my predictions match the current landscape.

I predicted that I would be writing my final article about Clipper in the year 2035. Many of you assumed that either I was much younger than I really am, that this was a typographical error, or that I had already lost it and was living on a ranch somewhere in California with Ronald Reagan. None of the above are true. I am now fifty-four years old. In the year 2035, I will be 91 years old but I will still be programming because in the year 2007 we will discover a new drug that restores dead brain cells. The year 2035 will mark the year that we finally arise from the ashes of the "great computer meltdown" of 2010. The meltdown will occur because of the Year 2000 debacle which will create a world-wide depression and create a political climate of hatred towards programmers that will build to a frenzy leading to "The Night of Broken Disks". Computer programmers will be fleeing to Russia, Iran and Iraq - the only safe havens in the world that will not be affected by the "Great-Satan Virus" due to their refusal to connect to the Information SuperHighway.
This great oppression of computer programmers will force those who are not killed outright, or sent to "de-programming camps" made their escape to third-world countries to hide for years in attics, befriended by a few, brave souls who know it is not their fault they were born "computer-literate". During this 20-year war on computers, an arsenal of "giant magnets" are created by the new war machine and the industrialized cities of the world are bombed with the largest "de-gaussing" campaign in history. No piece of software or database will be safe from annilation. Finally, the great war machine is defeated because it will fail to realize that it cannot maintain such a war when it destroys the very machines and software that allow it to wage war.
When it is all over, a discovery is made in a little house on the Prinsengracht in Amsterdam. A old notebook computer is found in the attic and sent to a museum. While restoring the computer, a small disk is found, still fused in the disk drive. Because of its condition, it appears that the notebook had been there for many years. Much work goes into the restoration of the disk and its data and finally, it is discovered that the disk contains the diary of a young, computer-literate girl who had lived in hiding for many years. By this time, the only computer-literate people left in the world only speak Russian or Farsi, so it takes much effort to figure out how to decipher the data on the disk. Finally, a compelling and sad story emerges about a young girl who struggles to maintain her innocence, her sanity, her computer-proficiency, and her faith in God while hiding from the great de-gaussing and de-programming regime.

It is a sad story, and it takes time for the world to really understand her story, especially her references to "The Book of Clipper". What was Clipper? Was this what helped her maintain her sanity and her faith in God? What happened to the young girl? Her story spreads around the world, and a never-ending search continues for a copy of "The Book of Clipper". Finally, the search ends in a little Russian town. Not a single person realizes that "The Book of Clipper" is a reference to the manual for the computer language that every single Russian speaks fluently, until someone finds a copy of the manual that Larry Heimendinger had left at the only Russian Clipper conference ever held - in 1992. It is dusty and worn, but it is the only book left that tells the true story of Clipper. Even though every programmer in Russia can speak Clipper, not a single one of them has seen a copy of "The Book of Clipper" because none had ever been sold. The software had been pirated, and then spread from disk to disk throughout the country while the story of Clipper passed from mouth to mouth. The Russians are the only civilized society that still has a programming language, so they offer it as a gift to the world, and the world becomes whole again, and the people rejoyce, and I come out of hiding - to write my final article about Clipper.

It's a Wonderful Life
I like to imagine that I am the angel in the story "It's a Wonderful Life" and that I have been called on by God to rescue a person who feels that his life has been lived in vain. In my rendition of the story, the desperate soul is not George Bailey but instead is Tom Rettig. Tom passed away about a year ago and I find myself haunted by him because I feel that I have never given back to him what he gave to me and the rest of the world.I often think back at what my life was like in the mid 80's. After many successful years as an Electronics Engineer my life was just not working anymore. Thieves broke into my business office and stole my computers and my software (including the backups). I was struggling with a failing computer-accessories manufacturing business that had pushed me deep into debt, and then my wife decided to just leave one day and head for greener pastures. I thought she had been kidnapped because she disappeared without a trace.
Arlo Guthrie once wrote a song about "The Last Man". He said "You think you've got it bad? Look at that guy?." I WAS that guy.

Then, one day, in early 1986 I was struggling with a problem trying to get dBase-III to work properly on my new peer-to-peer network. I recall making a tech support call to the network developer and the person on the phone asked "Are you compiling with Clipper?"

This simple question, in retrospect, was equivalent to someone posting a huge sign "Except a man be born again, he cannot see the kingdom of God". Clipper was my salvation. It allowed me to layeth down in green pastures and it restoreth my soul. So how did it come about that a person could be saved from the depths of depravity by a mere software product? We all have our stories of salvation, and they all take us down different paths but they all lead to the same place. My story in not unlike C.S. Lewis' story in "Mere Christianity", except the players are different. The Book of Clipper is not one story, but hundreds of stories all evolving from the "Platitudes of Vulcan".

Tom Rettig entered the scene around 1986 and offered an add-on product to Clipper titled "Tom Rettig's Library". Tom was a well-liked, generous person who eventually offered his library into the public-domain. Some of us are old enough to remember him as Jeff, the small boy in the original "Lassie" series on television in the 50's. I first met him at a user group in Southern California. After the meeting we went to a bar for a few beers and he sat and talked to us like we had all known him for years. He inspired us to do what he did, because he was just like us. The next day, I thought "If Tom Rettig can make a successful add-on product to Clipper, so can I". I wasn't the only person who had seen the light that night. Tom had broken new ground, had planted the first seeds, and from these seeds, an entire community of user-groups, programmers, applications, add-on products, books, magazines, et al, grew into maturity.

Two Steps Back

Have you ever heard anyone say "He's so far behind he's ahead"? By now, most of you have decided that you must move on to Windows and that there is no place for Clipper in your strategy. Many of you have already done so and are experimenting with products like VO, Delphi, VB, and Power-Builder or have created applications with these development environments. I am not writing this article to suggest that in any way, this was a bad choice. I have spent sufficient time with these products to come to the realization that Windows applications can be developed by travelling many different paths. What I am offering, however, is another perspective; one that frees us to open our minds to look at the future from a different view. Many of us have been so busy and so worried about constantly moving forward that we have forgotten how we got here in the first place, by the use of an enduring and powerful language - Clipper.So you may be thinking "What is he talking about? Clipper is Dead!". In the sense of a product, this may be true, but in the sense of a language, it is far from true. Let's imagine that Chinese is packaged into a product named "Visual-Chinese" and this product includes a set of design-tools for creating quick-Chinese documents that can be easily integrated into our marketing documents. Soon we would find our business opened up to a new market of 1 billion people. The product becomes instantly successful and everyone love its and uses it - until, years later, when we find that our marketing documents are not delivering any sales. Why? Because the language had to be cut and trimmed to fit into the limitations of the software environment. It becomes ambiguous, arrogant and unwanted by the very people who inspired its development, so it dies and Visual-Chinese gets thrown away like every other Visual tool. Does this mean that the Chinese language dies with the product? No. Chinese is a language that will endure. It has lots of users; it is robust, and it is mature.

The key word is "language". Development strategies should be built around the choice of a proper language, not just a product. Clipper is a language that endures. It cannot die. It has widespread use around the world and there are hundreds of thousands of Clipper legacy applications still doing mission- critical work. Unfortunately, because the word "Clipper" is owned by CA, and because CA has essentially abandoned Clipper, it cannot endure under the name Clipper, so it must endure under another name: that name is "Xbase". Software developers try to treat languages like they own them, but they are only temporary custodians. This leads us to a discussion of the current state of the Xbase language. Xbase currently exists in 5 dialects:

  1. dBase - A Windows-based Interpreter.
  2. FoxPro - A Windows-based Interpreter.
  3. CA-Clipper - A DOS-based Xbase compiler.
  4. CA-VO - A 32-bit Windows-based Xbase compiler.
  5. Xbase++ - A 32-bit Multi-Platform Compiler.

Xbase as dBase

dBase was the custodian of the Xbase language from around 1983 until about 1987. Unfortunately, it was an interpretive language so it never gained respectability as a true, robust language, however, it had much to offer the developer in ease-of-use and database design. dBase continues to be supported by Borland, simply because there is still money to be made in upgrades and conferences, but Borland has made it clear that they intend to make dBase programmers learn how to speak Pascal and eventually will phase Xbase out of their products.

Xbase as FoxPro

FoxPro took over as a co-custodian of the Xbase language in about 1987 and emerged around the same time as Clipper. FoxPro defeated dBase nearly overnight simply because it was faster, not because it delivered any new language concepts. FoxPro continues to be supported by Microsoft, simply because there is still money to be made in upgrades and conferences, but Microsoft has made it clear that they intend to make FoxPro programmers learn how to speak Visual Basic and eventually will phase Xbase out of their products.

Xbase as Clipper

Clipper was undoubtedly the best custodian of the Xbase language from 1987 to 1996. Clipper introduced the Xbase compiler, the open-architecture concept of the extend system, code blocks, locals, statics, multi-dimensional arrays, the RDD layer, the preprocessor, and language extensions. Clipper was the first Xbase custodian to give Xbase respectability as a true programming language. Clipper maintained this respectability until around 1996 when CA released CA-Clipper 5.3. CA chose to treat Clipper as a "package" rather than a "language" and alienated nearly the entire Clipper community when they bundled a Windows-IDE and several third-party products into the package. This was when Clipper died.

Xbase as VO

Computer Associates planned for VO to take over as the custodian of the Xbase language by forcing the death of Clipper and dragging CA customers into a new kind of development environment that kind of looks like Clipper, in that it inherited much of the new Clipper extensions. Unfortunately, migration to VO became cumbersome due to too many incompatabilities, poor performance, poor reliability and a third-party community who could not get their products to work with VO. VO promised it would be easy to migrate existing Clipper applications to Windows but could not deliver on the promise. Working in VO is in no way similar to working in Clipper. Many Clipper developers find that using a third-party Windows library (like Five-Win or Clip-4-Win) with Clipper is a much easier migration path than VO.

Xbase as Xbase++

Xbase++ is the newest player in the game of Xbase. Alaska Software has been in the enviable position of being allowed to sit back and watch what others are doing to the Xbase language. They had no stake in it so they were not required to play the game. It kind of reminds me of an old "BC" cartoon in which two ants are sitting on an ant-hill looking down on several pre-historic cavemen as they clubbed each other senseless. One ant says to the other "If we had the bomb we could intervene". Sometimes it takes a bomb to stop the madness.Now Alaska Software has the bomb! - And they are ready to intervene.
In my opinion, Xbase++ will become the Xbase custodian for the next generation of Xbase. Why? Here's a small list:

    1. They chose to support compatability with the most widely accepted version of Clipper, version 5.2e. They listened to the Clipper developers and insured that Xbase++ is 100% compatible with their existing Clipper code and the Clipper preprocessor. 2. They chose to make Xbase++ platform independent, so it can be used with Windows, OS/2, Linux, AS/400, etc. 3. They designed it like Clipper, so Clipper developers can use the same tools they have been using for years, including their favorite editor, their favorite linker, and their favorite third-party libraries. 4. They have embraced the third-party community and have encouraged them to migrate their products to Xbase++. 5. They added an event model and a complete set of easy-to-use GUI classes titled "Xbase-Parts" that makes it easy to design GUI dialogs and also to migrate existing Clipper applications to full-GUI Windows applications. 6. They added a full, state-of-the-art, object-oriented language that adheres to all of the modern OOPS standards. 7. They added a DBE (database engine) layer, based on CORBA technology, that exploits a powerful database architecture. 8. They added stable multi-threading, making it simple to offload time-consuming operations to a host system or separate thread. 9. They added persistence to all data types, including code-blocks and objects. 10. They added memory management systems that have eliminated the problems that have persistently dogged other Xbase dialects.

Why Xbase?

Many of us wonder why Xbase has not been given more respectability as a "mainstream" language. If Xbase is so good, why are Borland and Microsoft phasing it out of their future products? I was watching a television program the other day about an analysis of automobile technology over the years. We often assume that the best technology is what endures over time and that it eventually rises to the top. This may be true in an ideal world, but in a capitalist society, it is usually market dog-fights that determine dominance. In this analysis, it was determined that steam technology could have produced cars just as good as internal combustion technology, but Henry Ford chose the latter.Bill Gates has chosen Basic, not because it is better, but because he owns it. He doesn't own Xbase, and Bill cannot embrace something that he cannot control. Borland chose Pascal. Not because it is better, but becaused they own it.

Over the past 10 years, the success of the Xbase products has been due to the high degree of abstraction of the Xbase language, which makes it vastly simpler to acces and use operating system functions and resources. In addition, Xbase is more than just a specialized programming language, a database navigation language, or a user interface language - instead, it combines all of these roles, harmoniously integrating them with one another.

Xbase offers dynamic data types and is generally described as being highly "tolerant". Taken together, these benefits have persuaded a steadily growing community of users and developers to rely upon it as a choice for implementing mission-critical and commercial PC-desktop applications. In fact, world-wide, more than one-third of all DOS-based commercial applications now in use were written in Xbase, with Clipper accounting for the major share.

Why Xbase++?

Clipper programmers have tried just about everything to migrate their applications to Windows, but the available languages have failed to make this anything other than an expensive and painful process with clumsy results. I have started migrating dCLIP to windows several times and on each occasion have realized I was wasting my time, until now. My experience with Xbase++ is unlike any other product I have tried. Xbase++ gave me a place to start that was comfortable and familiar. It allowed me to compile a small program, link it, and run it just like in Clipper. I was not required to learn an IDE, a Resource Kit, a Windows-API, a new editor, a respository, new functions, new classes, or new commands. What I did need to learn was a new compiler, named XPP.EXE and a new linker, named ALINK.EXE. The compiler is compatible with CLIPPER.EXE so that was really a no-brainer, and ALINK.EXE was basically a piece of cake too, because in Xbase++, there are no complicated linking strategies as required by Clipper. In a few minutes, I had a small Xbase++ program running. In a few hours, I had a large Xbase++ program running, and in a few days, I had both my dCLIP and Time-Cube products compiled and running as 32-bit applications.So is this the Holy Grail? Is this the Magic Wand? In a way it is, and in another way it isn't. Xbase++ doesn't take a DOS text-based application and convert it to a Windows GUI-based application. It does, however, take a DOS text-based application and convert it to a 32-bit text-based application running as a true Windows application. Of course, this isn't the final result that we want for our 32-bit Windows applications, but it is a major step, because until now, nothing could accomplish this feat.

Migrating large Clipper applications requires that we meet certain objectives before continuing to the next level. Many Clipper applications have dependency on some third party library or maybe even a library of your own C routines. Xbase++ allows us to deal with these issues and resolve them before needing to write any GUI migration code at all. I had to deal with this when evaluating a strategy for migrating dCLIP to dCLIP++. The Clipper version of dCLIP has about 20 "C" functions and 10 "ASM" functions that are key to its functionality. The most important of these is the interactive preprocessor that works with the dot-prompt interpreter. Xbase++ has an extend system that is nearly identical to Clipper, so it required making only a few modifications to the parameter passing logic in my original C code and it was ready to compile with any 32-bit C compiler. I was able to test this conversion without writing a single GUI dialogue. I just compiled, linked and ran the new application.

Every other language would require converting all screen output code from text-based to GUI-based before I could run the application. Even a Clipper-compatible languages like VO do not let us start from this vantage point because it it cannot handle the text-based IO requirements of the Clipper application. dCLIP has hundreds of dialog screens, menus and browses that all write text-based screens. In VO, every one of these dialogs, menus, and browses would have to be re-written before I could turn the application over to the users. It could take years to migrate 10 years of Clipper code into a useful program.
Xbase++, on the other hand, allows me to migrate the application in steps:

    1. Recompile the existing application in Xbase++ and run it as a 32-bit Windows application. 2. Give priority to the portions of the application that need to be converted to GUI and start converting the application in little pieces. Dialogues that need to be GUI will look and run just like other 32-bit Windows applications, whereas dialogues that can wait because they may be used less often, will look and feel just like 32-bit text based applications. Both GUI and text-based code can be combined in the same application. 3. Add new features to the application that take advantage of the operating system, like file pick dialogues, the print manager, the TCP/IP Winsock interface, etc.
Many Clipper programmers have already decided to move on to other 32-bit Windows development systems like Delphi and VB. These are great design tools for Windows applications yet they lack the robust architecture and language that was so much appreciated by the Clipper community. If only we could combine these powerful application development environments, Active-X controls and design tools with a powerful language we would have the best of both worlds. I wondered how Alaska thought they could compete with companies as big as Microsoft, Borland and CA. I asked Steffen "Why would anyone abandon their huge investment in Delphi, VB or VO and choose Xbase++?" His answer was perfect. He said that Xbase++ does not replace these products, instead it enhances them by providing a better language to use with their existing design tools and libaries. We no longer live in a world where a developer must decide on a single platform, a single language, or a single software provider. We live in a hybrid world and must learn how to create hybrid applications. Steffen made it clear that Alaska is dedicated to maintaining a level of excellence as a hub that connects to all the lanuages.

Square Pegs and Round Holes

Most Windows programmers will tell you that you cannot take a standard Clipper application with @SAY..GETS, Menu Prompts, etc, and convert it to a Windows GUI program without a major change in the architecture and the functionality of the program. They claim that a text-based, modal design has too little in common with GUI-based, event-driven, non-modal design. They will tell you that it like trying to fit a square peg into a round hole. For years, I believed this because it made sense. I, like everyone else, wrote my Windows applications with a different structure than my Clipper applications. They were built around an event model rather than a procedural model and the code was tightly-bound rather than loosely-bound to the functional model. This always leaves me with an uneasy feeling because it forces me to write applications that are less modular and are platform specific.Microsoft, Borland, and CA each wants us to build applications their way. They want us to learn their programming tools, their methods, their plug-ins, their workshops, and their studios - not their language. Why? Because applications built around their environment will be harder to migrate to competitor's products than applications built around a language.

So they make sure that the language is difficult and inaccessible, and that the application cannot be maintained or migrated to any other platform, other than platforms that they support. Programmers, however, have to survive in the real world and this requires platform flexibility. The reason why so many mission- critical DOS applications are still surviving in the real world is because each development platform supports DOS as a subset, so DOS has been, out of necessity, elevated to the status "platform independent". I can run my Clipper applications under MS-DOS, PC-DOS, DR-DOS, OS/2, Windows 3.1, Windows 95, Citrix- Winframe, MULTI-DOS, Windows NT and Novell-DOS. I can run my Delphi applications only under 32-bit Windows. Is this a step forward?

Xbase++ has taught me that GUI applications can be built with the same functionality and structure as Clipper applications without losing its platform independence. I learned this when I was developing a system to convert my hundreds of @SAY..GET dialogues to GUI dialogues. The event model supported by "Xbase Parts" allowed me to develop a new dialogue system that works with a "Get-List", similar to Clipper, and then pass the GetList array to either the text reader, DC_ReadModal() or the GUI reader, DC_ReadGui(). This abstraction of the functional design of a dialogue from the code implementation of the dialogue allows for architectures in which Clipper @SAY..GETS and menus can be easily migrated to GUI. I am a great believer in the power of "data- driven" systems and have developed several applications that use these concepts heavily. Xbase++ makes it not only possible, but rather easy, to migrate my entire dCLIP library into a new product named dCLIP++ that allows me to simply flip a switch at runtime and the application switches from text-based to GUI-based.

The Torch has been Passed

Alaska Software likes to exploit their slogan "The Next Generation of Xbase" on their marketing documentation, their website, and even the product - but it isn't just a slogan. It truthfully states what the product is and where it fits into the Xbase story. It also defines Xbase++ as not just a 32-bit compiler for Clipper but a product that helps us move beyond Clipper, beyond DOS, and beyond Windows.The only thing that is common between Xbase++ and Clipper is the compatability of the language, otherwise, Xbase++ is an entirely new programming language that is created by an entirely new company who is un-encumbered by an old user-base, by old loyalties, or by an old marketing strategy. Alaska enjoys the freedom to move forward with a vision of "a perfect world" that the other Xbase custodians could not realize. The other Xbase dialects were all acquired by large companies who had already established their language strategy and bought the products not for their technology but for their user-base.

Last week, I had the good fortune to spend time with Steffen Pirsig, the chief architect of Xbase++. I had already spent about 5 months with the EEP releases of Xbase++ and was impressed with the compatibility, the documentation, and the overall product design. I wondered what kind of person was behind this product, and what plans he has for the future. I have met many programmers over the years and find that the really talented ones are also the hardest to get to know. They usually have a body language that discourages communication and an attitude that is condescending. Steffen was different. He had an openness and an engaging manner that left me completely at ease. He had remarkable depth of understanding of his market, his product, the current technology and even the history of Xbase, yet he didn't growl at me - instead he invited me into his world and asked me for my opinions.

Cocoon

In my dream, the "Ghost of Xbase Future" led me through the Land of Clipper and how it might look like up through the year 2035. I couldn't hold back my emotions as I witnessed the data meltdown and the termination of millions of programmers. I asked him "Spirit - is this a vision of how things MUST be or how things COULD be?" He never answered me.I woke up from my dream and ran to the mirror. I was relieved to see that I wasn't 91 years old but was still a young man. I exclaimed "There's still time!" I bolted to the window, looked out, and saw that The Land of Clipper looks different than it did yesterday. The paths are 32-bits wide and they lead everywhere, yet they look familiar and something tells me that there is nothing to fear at the end of these paths. Then I realized that I had not been dreaming and that Clipper had not really died at all but had been in a cocoon, waiting to metomorphose into a butterfly, one with big X's on it's wings. The butterfly is beautiful and it attracts the attention of people like Dirk Lesko (author of Funcky), of Jud Cole (author of Blinker), of Dave Kuechler (author of Comix), and others who once frolicked in the land of Clipper.

Using SanScript for Retrieving System Database Name in Microsoft Dynamics GP

To get the System DB name when integrating with Dynamics, use the following sanscript code:

---------------------------------------------------------------------------------------------
local integer companyid;
local integer dictionary;
local integer file_series;
local integer logical_file;

companyid = DYNAMICS; {constant with value 0 }
dictionary = DYNAMICS;
file_series = TABLESSERIES_SYSTEM; { constant with value 7 }
logical_file = 0;

set sDatabase to SQL_GetDbName(companyid, dictionary, file_series, logical_file, fSuccess).
---------------------------------------------------------------------------------------------

This routine will then return the string name of the System database. This is all done by accessing the SY_Pathnames table and retrieving the column Database Name.

While it probably is much more proper to do this, it probably isn't worth the effort to get the system series DB from code because installation hardcodes the DB to "DYNAMICS" in all current cases. But it is possible in the future this could change so this isn't a bad idea.

VBA Script for connecting to Microsoft Dynamics GP Database

The following VBA script example can be used for the Description_AfterGotFocus event in the Microsoft Dynamics GP Account Maintenance window. This script will connect to the Microsoft Dynamics GP sample TWO database and log on as system administrator with a password. After the connection is made, the script creates a recordset of the data that is stored in the GL00105 account index master table. The script will then return the account index value to the User-Defined1 field in the Account Maintenance window. The script returns this value when you enter a new account or use the Account Lookup button.

To use the example script, follow these steps:
  1. Open the Account Maintenance window in Microsoft Dynamics GP.
  2. On the Tools menu, click Customize, and then click Add Current Window to Visual Basic.
  3. On the Tools menu, click Customize, click Add Fields to Visual Basic, and then click the Account Number field, the Description field, and the User-Defined 1 field.
  4. On the Tools menu, click Customize, and then click Visual Basic Editor.
  5. In Visual Basic Editor, expand Great Plains Objects, and then double-click AccountMaintenance to open an Account Maintenance code window.
  6. Copy the following code, and then paste it into the Account Maintenance code window.
'-------------------------------------------------------------------------------------------------
Private Sub Description_AfterGotFocus()
Dim objRec
Dim objConn
Dim cmdString

Set objRec = CreateObject("ADODB.Recordset")
Set objConn = CreateObject("ADODB.Connection")

objConn.ConnectionString = "Provider=MSDASQL;DSN=GreatPlains;Initial Catalog=TWO;User Id=sa;Password=password"
objConn.Open


cmdString = "Select ACTINDX from GL00105 where (ACTNUMST='" + Account + "')"

Set objRec = objConn.Execute(cmdString)

If objRec.EOF = True Then
AccountMaintenance.UserDefined1 = ""
Else
AccountMaintenance.UserDefined1 = objRec!ACTINDX
End If
objConn.Close
End Sub
'-------------------------------------------------------------------------------------------------

You can also use the RetrieveGlobalsX.dll file as per for Microsoft Dynamics GP versions like 9 for 9.0, 8 for 8.0, to retrieve the same information that this script example retrieves.

Maintenance Plan Wizard in SQL Server Management Studio, SQ: Server 2008

The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, run database integrity checks, or update database statistics at specified intervals.

To create or manage maintenance plans, you must be a member of the sysadmin fixed server role. Object Explorer only displays the Maintenance Plans node for users who are members of the sysadmin fixed server role.

To start the Maintenance Plan Wizard, do the followings:
  • Expand the server.
  • Expand the Management folder.
  • Right-click Maintenance Plans and select Maintenance Plan Wizard. This launches the wizard and you can now step through and create a plan customized to meet your maintenance requirements.

Database Maintenance Plan for Microsoft Dynamics GP on Microsoft SQL Server 2008

For setting up a database maintenance plan on Microsoft SQL Server 2008, for Microsoft Dynamics GP.

You can set up a weekly maintenance plan for databases in Microsoft Dynamics GP.  However, the frequency that you select depends on the environment and on how much activity occurs within the databases. We recommend that you examine the output for warning signs of possible problems after you set up a maintenance plan.

To set up a database maintenance follow these steps:
  1. Click Start, point to All Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio.
  2. In the Connect to Server window, click SQL Server Authentication in the Authentication list, type the sa user name and password, and then click Connect.
  3. In the Object Explorer area, double-click Management, right-click Maintenance Plans, and then click Maintenance Plan Wizard.
  4. On the Maintenance Plan Wizard starting page, click Next.
  5. In the Select Plan Properties window, type a name for the maintenance plan in the Name field, type a descriptive comment in the Description field, and then select Single schedule for the entire plan or No schedule.
  6. In the Job Schedule Properties window, click the Change button for Schedule, set up a schedule type of "Recurring as Enabled" that has the frequency parameters that meet your maintenance needs, and then click OK.
  7. Click Next to move to the Select Maintenance Tasks window.
  8. In the Select Maintenance Tasks window, click to select the check box for each of the following tasks:
    • Check Database Integrity
    • Reorganize Index
    • Update Statistics
    • Back Up Database (Full)
  9. Click Next.
  10. In the Select Maintenance Task Order window, accept the default order, and then click Next.
  11. In the Databases list, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
  12. In the Define Reorganize Index Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
  13. In the Define Update Statistics Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, and then click Next. Use the default setup for the remaining options.
  14. In the Define Back Up Database (Full) Task window, click to select the check box for each Microsoft Dynamics database. To do this, select These databases, click the check box next to each desired database, click OK, review the remaining backup parameters, and then click Next.
  15. In the Select Report Options window, accept the default entries, and then click Next.
  16. In the Complete the Wizard window, click Finish. After the maintenance plan is created, click Close.
  17. If you want to configure the maintenance plan to delete backup files that are older than a specific date, follow these steps:
    • In the Object Explorer area, double-click Management, double-click Maintenance Plans, right-click the maintenance plan that you created, and then click Modify.
    • In the Toolbox area at the bottom of the Object Explorer, drag Maintenance Cleanup Task to the right pane.
    • In the right pane, double-click Maintenance Cleanup Task.
    • In the File Location area, click Delete Specific File.
    • In the File name field, type the path of the location where the backup file is located.
      Note If you have backup files for each database and these files are located in separate folders, you must create a Maintenance Plan Cleanup Task for each database folder.
    • To delete full backup files, type BAK in the File Extension field. To delete Transaction Log backup files, type TRN in the File Extension field. To delete both types of files, you must create a Maintenance Plan Cleanup Task for each type of file.
    • In the File Age area, click the appropriate option to set the schedule according to which the files will be deleted, and then click OK.
    • In the right pane, click Back Up Database (Full). A green arrow now appears at the bottom of the window.
    • Drag the green arrow to Maintenance Cleanup Task. A green arrow will connect Back Up Database (Full) to Maintenance Cleanup Task.
    • Click File, and then click Save Selected Item.

Bulk Copy Process (BCP) to Export Microsoft Dynamics GP Data into New Database

How to use the Bulk Copy Process (BCP) to export data and to import data by using Microsoft Dynamics GP.
  1. Make a backup of your company database.
  2. Copy and paste the following CreateBulkCopyOut.sql script into Microsoft SQL Query Analyzer.
/* Script to create bcp commands to export data for all tables. */
SET QUOTED_IDENTIFIER OFF
select 'bcp "TWO..' + name + '" out ' + name + '.out -e ' + name + '.err -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"' from sysobjects where type = 'U' order by name

In the script, replace the following placeholders with the correct information:
    • Replace TWO with the name of your company database.
    • Replace password with your sa password.
    • Replace SERVERNAME with the name of your instance of Microsoft SQL Server.
Note To open Query Analyzer, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Note If you are using Microsoft SQL Server 2000 Desktop Engine (also known as MSDE 2000), run the statement in Microsoft Support Administrator Console. To open Support Administrator Console, click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console. Support Administrator Console requires a separate installation. You can install the program by using the Great Plains installation CD number 2.
  1. Run the script against the database and then save the results to a batch file. To do this, follow these steps:
    • If you are using Query Analyzer, click in the results pane, and then click Save As on the File menu. Create a folder named BCPData, name this file Copyout.bat, and then click Save.
    • If you are using Support Administrator Console, click File, and then click Export. Create a folder and name it BCPData. Name the file Copyout.bat. Then click Save.
  2. Copy and paste the following CreateBulkCopyIn.sql script into Query Analyzer.
/* Script to create bcp commands to import data for all tables. */
SET QUOTED_IDENTIFIER OFF      
select 'bcp "TWO..' + name + '" in ' + name + '.out -e ' + name + '.err       -c -b 1000 -U sa -P password -t "|" -S SERVERNAME -r "#EOR#\n"' from sysobjects where type = 'U' order by name

In the script, replace the following placeholders with the correct information:
    • Replace TWO with the name of your company database.
    • Replace password with your sa password.
    • Replace SERVERNAME with the name of your instance of SQL Server.

Note To open Query Analyzer, click Start, point to Programs, point to Microsoft SQL Server, and then click Query Analyzer.

Note If you are using SQL Server 2000 Desktop Engine, run the statement in Support Administrator Console. To open Support Administrator Console, click Start, point to Programs, point to Microsoft Support Administrator Console, and then click Support Administrator Console. Support Administrator Console requires a separate installation. You can install the program by using the Great Plains installation CD number 2.
  1. Run the script against the database and then save the results to a batch file.
    • If you are using Query Analyzer, click in the results pane, and then click Save As on the File menu. Create a folder and name it BCPData. Name the file Copyin.bat, and then click Save.
    • If you are using Support Administrator Console, click File and then click Export. Create a folder and name it BCPData. Name the file Copyin.bat. Then click Save.
  2. Perform a bcp command to move the data out of the company database. To do this, use the appropriate method.
      • Open the BCPData folder.
      • Double-click the Copyout.bat file.

        Note The batch file starts the BCP process to copy the data from the database to a text file.
  1. Delete the company from within Great Plains. To do this, log into Great Plains as the sa user. Click Tools, point to Setup, point to System and then click Company. Click the lookup glass to display all the companies listed. Select the company and then click Delete.

    Note For versions of Microsoft Great Plains that are earlier than Microsoft Great Plains 8.0, delete the company. To delete the company, do the following: On the Setup menu, click System, and then click Company to delete the company.
  2. Remove the database.
    • If you are using Microsoft SQL Server, open Enterprise Manager, expand the server name, expand Database, right-click the company database that you deleted in step 7, and then select Delete.
DROP DATABASE TWO
  1. Re-create the company database and procedures. To do this, start Great Plains Utilities, log on as the sa user, and then click Create new company in the Additional Tasks dialog box.

    Note Use the same company name as the one that you deleted in step 7.
  2. After the company is created, the tables must be truncated.
If you are using Microsoft SQL Server, run the following Truncate_Table_Company.sql script in Query Analyzer.

o    /* Script to remove all data from all user tables in the company database */
o    SET QUOTED_IDENTIFIER OFF
o    if exists (select * from sysobjects where name = 'RM_NationalAccounts_MSTR_FKC')
o    ALTER TABLE dbo.RM00105
o    DROP CONSTRAINT RM_NationalAccounts_MSTR_FKC
o    Go
o    declare @tablename char(255)
o    DECLARE t_cursor CURSOR for
o       select "truncate table " + name
o       from sysobjects where type = 'U'
o       set NOCOUNT on
o       open t_cursor
o       FETCH NEXT FROM t_cursor INTO @tablename
o       while (@@fetch_status <> -1)
o       begin
o       if (@@fetch_status <> -2)
o       begin
o       exec (@tablename)
o       end    
o       FETCH NEXT FROM t_cursor into @tablename
o       end
o    DEALLOCATE t_cursor
o    GO
o    ALTER TABLE dbo.RM00105 ADD
o       CONSTRAINT RM_NationalAccounts_MSTR_FKC FOREIGN KEY
o       (
o       CPRCSTNM
o       ) REFERENCES dbo.RM00101 (
o       CUSTNMBR
o       )
o    GO

  1. Move the data back into the company database. To do this, open the BCPData folder, and then double-click the Copyin.bat batch file. Running this batch file starts the process of moving the data back into the company database. When the data is moved back into the database, all indexes are created and verified for each table.

    Note When the process is completed, the BCPData folder will contain .err files. If any one of these .err files is larger than 0 KB, the data was not imported for the company database successfully.

Additional steps

If you are using the BCP process to change your Microsoft SQL Server Sort Order, you must bulk copy data out of the DYNAMICS database and all Company databases.

Note Changing the Microsoft SQL Server Sort Order for the DYNAMICS database and for the Company database is not supported by Microsoft. This is only supported if MBS Professional Services makes the change for you.
To do this, follow steps 1 through 11. In step 10, you must also truncate the DYNAMICS database. To do this, use one of the following methods:
If you are using Microsoft SQL Server, run the following Truncate_Tables_Dynamics.sql script in Query Analyzer.
·         /* ** **
·         Truncate_Tables_Dynamics.sql
·         function: Will remove all data from all user tables in the DYNAMICS database
·          ** */
·          
·         SET QUOTED_IDENTIFIER OFF
·          
·         if exists (select * from sysobjects where name = 'orgEntity_SETP')
·         ALTER TABLE dbo.ORG40100
·         DROP CONSTRAINT orgEntity_SETP
·         GO
·         if exists (select * from sysobjects where name = 'orgRelation_MSTR')
·         ALTER TABLE dbo.ORG00100
·         DROP CONSTRAINT orgRelation_MSTR
·         Go
·         declare @tablename char(255)
·          
·         DECLARE t_cursor CURSOR for
·         select "truncate table " + name
·         from sysobjects where type = 'U'
·          
·         set NOCOUNT on
·         open t_cursor
·         FETCH NEXT FROM t_cursor INTO @tablename
·         while (@@fetch_status <> -1)
·         begin
·         if (@@fetch_status <> -2)
·         begin
·         exec (@tablename)
·         end
·          
·         FETCH NEXT FROM t_cursor into @tablename
·         end
·          
·         DEALLOCATE t_cursor
·         GO
·         ALTER TABLE dbo.ORG40100 ADD
·         CONSTRAINT orgEntity_SETP FOREIGN KEY
·         (
·         ENTYLVL
·         ) REFERENCES dbo.ORG40000 (
·         ENTYLVL
·         )
·         GO
·         ALTER TABLE dbo.ORG00100 ADD
·         CONSTRAINT orgRelation_MSTR FOREIGN KEY
·         ( ENTITYID ) REFERENCES dbo.ORG40100 ( ENTITYID )
·         GO