Search code examples
sql-servervb.netsqldmo

SQLDMO - [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '*'

I'm currently in charge of maintaining a legacy software. Currently I'm having problem with SQLDMO as following: 

//This function is used to create sql query for drop and create table
Function GenerateScript(ByVal pObjectType As String, ByVal pObjectName As String) As String
    Dim sSQL As String = ""
    If (pObjectType.ToLower() = "user") Then
        If Not IsNothing(db.Users.Item(pObjectName)) Then
            sSQL = db.Users.Item(pObjectName).Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops)
            sSQL = sSQL + db.Users.Item(pObjectName).Script()
        End If
    Else
        If Not IsNothing(db.GetObjectByName(pObjectName)) Then

            sSQL = db.GetObjectByName(pObjectName).Script(SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops)

            sSQL = sSQL + db.GetObjectByName(pObjectName).Script() //Error occur here

        End If
    End If



    Return sSQL
End Function

There are about 20 tables in my database, but only some of them reproduce the exception:

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near '*'.

Please tell me what cause the problem and how to solve it

Update I tried with 2 databases and find out that it run ok in one DB but produce the issue in the other. But those databases have the same table structure, so why do the error occur in one database and not occur in the other?

Solution

  • Solve

    It was the compatible of the database that cause the problem. It run ok with database that compatible with sql server 2005 (90) and produce error with database that compatible with sql server 2008 or above. All I need to do is change to compatible of database to 2005