Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here
DynDeveloper.com

DynDeveloper.com

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
finlyte 8/6/2019 3:38:32 PM

Need some SQL help with a CURSOR

Hello,

I'm working on a data migration from GP to NetSuite.  I have 16 companies to migrate data from and I created a table in each company db with an account mapping master table called "dbo.GL00105ToNetSuite".  I'm trying to build a cursor to backup these tables into a separate database called Finlyte, because I have to restore over the original databases to pull a second set of data for migration.  So here is what I have:

DECLARE @db VARCHAR(50) --- Database Interid
DECLARE @S_TABLE VARCHAR(50) --- Source table
DECLARE @D_TABLE VARCHAR (50) ---- Backup table

DECLARE db_curser CURSOR FOR 
SELECT INTERID FROM DYNAMICS.dbo.SY01500 
WHERE DYNAMICS.dbo.SY01500.CMPANYID BETWEEN 1 AND 17


OPEN db_curser
FETCH NEXT FROM db_curser into @db

WHILE @@FETCH_STATUS = 0

BEGIN
SET @S_TABLE = @db + '.dbo.GL00105ToNetSuite' -- appends the source company database onto the table name
SET @D_TABLE = 'FINLYTE.dbo.' + @db + '_GL00105TONetSuite' -- adds the source company database to the table name in the Finlyte database.
EXEC('SELECT * INTO ' + @D_TABLE + ' FROM ' + @S_TABLE)  --executes the Select * into backup_db_table from source_db_table
FETCH NEXT FROM db_cursor INTO @db



END

CLOSE db_cursor
DEALLOCATE db_cursor

When I run it, it backs up the first database table correctly and then it crashes.  Below are the error messages that I get.  Help would be greatly appreciated.

Msg 16915, Level 16, State 1, Line 12
A cursor with the name 'db_curser' already exists.
Msg 16905, Level 16, State 1, Line 16
The cursor is already open.

(15127 rows affected)
Msg 16916, Level 16, State 1, Line 24
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 31
A cursor with the name 'db_cursor' does not exist.
Msg 16916, Level 16, State 1, Line 32
A cursor with the name 'db_cursor' does not exist.

 

 

 

Version: GP 2015, GP 2013, GP 2010, GP 10, GP 9, GP 8
Section: SQL Scripts








Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables