I'm curious if anybody has some information on migrating a database from SQL Anywhere 12 to SQL Anywhere 17 when it comes to the new roles feature that was implemented in SQL Anywhere 16.

enter code here

First off, I unloaded the full database and schema out of our version 12 database (12.0.1.3942)

Opened SQL Anywhere 17.0.4.2053

No active connections in SQL Central.

  1. Select to Create a new database
  2. Select to Create a new database on this computer
  3. Create name for my new database in SA17 (dbname.db)
  4. Select to Maintain the following transaction log file (dbname.log)
  5. Select that no transaction log mirror is needed
  6. For the sake of the migration, I set the minimum password length to 1
  7. Set the dba user and use the old default password
  8. No jConnect metadata needed (using ODBC)
  9. Leave encryption disabled
  10. Select the 4k page size. No space preallocation.
  11. Select to use SQL Anywhere defaults
  12. Select the default collation: 1252LATIN1
  13. Select the default collation sequence (Ignore accents)
  14. Leave the collation tailoring options set to default

Now comes the tricky part. Choose the Security Model for the system procedures

Based on my guess, because my database is coming from SQL Anywhere 12, I need to select the second option, to execute the system procedures as the definer.

Finally I give it the server name and the database name, and tell it to open the database after created.

Once in the new database in 17, I want to select the reload script that was created when I unloaded my SQL Anywhere 12 database.

READ "C:\Users\Jeff Gibson\Documents\Extraction\reload.sql"

Immediately at line 52, I hit the following error...

Could not execute statement.
Invalid user ID or role name 'dbo' specified
SQLCODE=-1536, ODBC 3 State="HY000"
C:\Users\Jeff Gibson\Documents\Extraction\reload.sql:52

You can continue executing or stop.

GRANT CONNECT,DBA,GROUP,RESOURCE TO "dbo" AT 3

From there I just hit stop. Because I'm not sure if there is something critical that needs to be handled with what was the old dbo system user.

I'm not even sure I have my brain totally wrapped around roles yet. But from what I can tell, it looks like the dbo system user was moved into roles.

Is this an error/warning that I can skip? Since dbo gets created under roles? Or do I need to modify something so the user gets handled correctly on the creation of the SQL Anywhere 17 database.

Any information on this would be greatly appreciated! Thanks in advance for your help!

Jeff Gibson
Intercept Solutions
Nashville, TN

asked 05 Apr, 18:30

Jeff%20Gibson's gravatar image

Jeff Gibson
1.4k314759
accept rate: 21%


This issue is caused by unloading using v12 software. That creates a reload script that is valid for v12 but may not be for other versions.You should use the v17 unload tool to create the unload script. Or as Reimer has suggested directly unloading into a new database.

In v16 and later, the statement:

GRANT <...> TO "<user>"

is only valid for an USER and "dbo" is a System Role not an user.

permanent link

answered 06 Apr, 09:52

Chris%20Keating's gravatar image

Chris Keating
3.8k2165
accept rate: 30%

edited 09 Apr, 19:13

You mean "is only valid for an user", right?

(06 Apr, 14:51) Volker Barth

Correct. The statement is valid only for an user.

(09 Apr, 19:13) Chris Keating

To be honest, I didn't look that much into new features, when we started converting client databases from SA12 to SA17. So we trusted in the frequently cited Watcom rule and just did an unload into a new database.

dbunload -v -c "<connection string for old db>" -an "<path for new db" -ap 4096 -ea None -ii

The defaults worked perfectly, so we continued using this approach. The only downside: I don't have any advice for your specific question

permanent link

answered 06 Apr, 03:50

Reimer%20Pods's gravatar image

Reimer Pods
4.4k374789
accept rate: 12%

edited 06 Apr, 03:52

We have also migrated several databases from 12.0.1 to v16 and v17 without issues, including replicated databases, generally via dbunload. So we still use the old privilege system (with a few adaptions). We also still use dbo as schema for most user tables, and that was no problem during the migration.

In your case, do you also connect as dbo (i.e. with its own credentials) or do you only use that to (say, via SETUSER) to create unqualified database objects?

permanent link

answered 06 Apr, 09:48

Volker%20Barth's gravatar image

Volker Barth
32.1k327470688
accept rate: 32%

1

In short: Using DBUNLOAD with one of -an or -ar should prevent you from answering all those questions asked by SQL Central...

(06 Apr, 09:53) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×422
×98
×48
×2

question asked: 05 Apr, 18:30

question was seen: 2,278 times

last updated: 09 Apr, 19:13