web
You’re offline. This is a read only version of the page.
close
Skip to main content
News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Copy Azure SQL Databas...
Microsoft Dynamics AX (Archived)

Copy Azure SQL Database to a SQL Server environment - Error

(0) ShareShare
ReportReport
Posted on by Johnny 6,470

Hello,

I'm trying to Export Azure SQL with the steps provided here.

I have copied the DB and when I execute the Prepare database script I'm getting this error message:

"Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '-'."

What can be a problem? I just copied the given script and changed only the name of my copied DB in "ALTER DATABASE' command.

Before I executed the script I validated that the copying of DB is really over with following scripts:

SELECT * FROM sys.dm_database_copies 
SELECT state_desc, * FROM sys.databases 

*This post is locked for comments

I have the same question (0)
  • Johnny Profile Picture
    Johnny 6,470 on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    To be honest I have no idea what those users are.

    I deleted them manually and run the script after successfully.

    Thanks for your help.

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Hi,

    you should modify the query so that these usernames will be surrounded by square brackets.

    For example:

    select 'DROP USER ' + '[' +  name + ']'

    But do you know what those users are - I don't have them in my Tier 2 sandbox Azure SQL database.

    Please let me know if this helps.

  • Johnny Profile Picture
    Johnny 6,470 on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Yes, I see users with hyphens in security - roles.

    What can I do with those?

    az_5F00_user.png

  • Johnny Profile Picture
    Johnny 6,470 on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    And these are the complete output I'm getting after running the query:

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '-'.

    (1 row affected)

    (1 row affected)

    (1 row affected)

    (1455 rows affected)

    (11 rows affected)

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    For me that works.

    Could you check if you have users which have hyphen in their username (I don't even know if that's possible)?

  • Johnny Profile Picture
    Johnny 6,470 on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Hi,

    yes, there is no line with single hyphen.

    Here is my entire query I just copied with ctrl + a

    --Prepare a database in Azure SQL Database for export to SQL Server.
--Disable change tracking on tables where it is enabled.
declare
@SQL varchar(1000)
set quoted_identifier off
declare changeTrackingCursor CURSOR for
select 'ALTER TABLE ' + t.name + ' DISABLE CHANGE_TRACKING'
from sys.change_tracking_tables c, sys.tables t
where t.object_id = c.object_id
OPEN changeTrackingCursor
FETCH changeTrackingCursor into @SQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@SQL)
FETCH changeTrackingCursor into @SQL
END
CLOSE changeTrackingCursor
DEALLOCATE changeTrackingCursor

--Disable change tracking on the database itself.
ALTER DATABASE
-- SET THE NAME OF YOUR DATABASE BELOW
MyNewCopy
set CHANGE_TRACKING = OFF
--Remove the database level users from the database
--these will be recreated after importing in SQL Server.
declare
@userSQL varchar(1000)
set quoted_identifier off
declare userCursor CURSOR for
select 'DROP USER ' + name
from sys.sysusers
where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo'
OPEN userCursor
FETCH userCursor into @userSQL
WHILE @@Fetch_Status = 0
BEGIN
exec(@userSQL)
FETCH userCursor into @userSQL
END
CLOSE userCursor
DEALLOCATE userCursor
--Delete the SYSSQLRESOURCESTATSVIEW view as it has an Azure-specific definition in it.
--We will run db synch later to recreate the correct view for SQL Server.
if(1=(select 1 from sys.views where name = 'SYSSQLRESOURCESTATSVIEW'))
DROP VIEW SYSSQLRESOURCESTATSVIEW
--Next, set system parameters ready for being a SQL Server Database.
update sysglobalconfiguration
set value = 'SQLSERVER'
where name = 'BACKENDDB'
update sysglobalconfiguration
set value = 0
where name = 'TEMPTABLEINAXDB'
--Clean up the batch server configuration, server sessions, and printers from the previous environment.
TRUNCATE TABLE SYSSERVERCONFIG
TRUNCATE TABLE SYSSERVERSESSIONS
TRUNCATE TABLE SYSCORPNETPRINTERS
--Remove records which could lead to accidentally sending an email externally.
UPDATE SysEmailParameters
SET SMTPRELAYSERVERNAME = ''
GO
UPDATE LogisticsElectronicAddress
SET LOCATOR = ''
WHERE Locator LIKE '%@%'
GO
TRUNCATE TABLE PrintMgmtSettings
TRUNCATE TABLE PrintMgmtDocInstance
--Set any waiting, executing, ready, or canceling batches to withhold.
UPDATE BatchJob
SET STATUS = 0
WHERE STATUS IN (1,2,5,7)
GO


  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Hi,

    you should use sqladmin. It has nothing to do with the incorrect syntax of your SQL statement.

    I tested this statement right now and I don't get any errors. The text that you are showing doesn't contain any hyphens so the error message can't be from it. Could you still double check that you are not executing something else accidentally - such as a comment line without the first hyphen?

    Maybe you could share a screenshot where we can completely see the query being executed, and the output / error message.

  • Johnny Profile Picture
    Johnny 6,470 on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Now I executed the query piece by piece and it fails on below part and gives the error "Incorrect syntax near '-'."

    This part of query removes the database level users from the database.

    Can the problem be that I'm logged-in to SSMS with sqladmin user? I could not log-in with 'Windows authentication'.

    declare

    @userSQL varchar(1000)

    set quoted_identifier off

    declare userCursor CURSOR for

    select 'DROP USER ' + name

    from sys.sysusers

    where issqlrole = 0 and hasdbaccess = 1 and name <> 'dbo'

    OPEN userCursor

    FETCH userCursor into @userSQL

    WHILE @@Fetch_Status = 0

    BEGIN

    exec(@userSQL)

    FETCH userCursor into @userSQL

    END

    CLOSE userCursor

    DEALLOCATE userCursor

  • Suggested answer
    nmaenpaa Profile Picture
    nmaenpaa 101,160 Moderator on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Please check that on the first line of your query you have two hyphens instead of one. Maybe the other one was accidentally not copypasted?

    I mean this line (assuming you copy pasted everything from the docs site):

    --Prepare a database in Azure SQL Database for export to SQL Server.

    If it's still not working, could you please share the entire query that you are trying to run?

  • Johnny Profile Picture
    Johnny 6,470 on at
    RE: Copy Azure SQL Database to a SQL Server environment - Error

    Hi Nikolaos,

    yes, the original db name contains hyphen and I put quotations as you suggested, but I still got the same error.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Dynamics 365 Community Calls Return
Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft
Responsible AI policies for the Community

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Andrés Arias – Community Spotlight

We are honored to recognize Andrés Arias as our Community Spotlight honoree for…

Congratulations to the August Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Syed Haris Shah Profile Picture

Syed Haris Shah 9

#2
Mea_ Profile Picture

Mea_ 4

#3
Rahul Shah Profile Picture

Rahul Shah 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans