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?