Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    25

    Unanswered: Mystery Syntax Error

    Hi, Using SQL Server 2005. The below code is generated in an SSIS package and is throwing the syntax error:

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

    Hopefully you can re create it easily enough as the syntax error is the issue. Does anyone know what could be throwing this as i can't find the offending bit of code anywhere. Is it a known bug?

    As a bit of background the code is supposed to be generating two script files that contain all the create statements for tables and indexes in a DB.

    Thanks guys

    Chris



    Code:
    SET NOCOUNT ON;
    
    
    
        
    
    --Creates Scripts To @T(I)ScriptLocation
    
    DECLARE @oServer			AS INT
    
    DECLARE @method			AS VARCHAR(300)
    
    DECLARE @TSQL				AS VARCHAR(4000)
    
    DECLARE @ScriptType			AS INT
    
    DECLARE @TName			AS VARCHAR (100)
    
    DECLARE @IName				AS VARCHAR (100)
    
    DECLARE @ITName			AS VARCHAR (100)
    
    DECLARE @ITID				AS INT
    
    
    
    
    
    --Initialisation
    
    SET @TSQL =''
    SET @TName =''
    SET @IName =''
    SET @ITID = 0
    
    SET @ScriptType = 1|4096|131072|4|1073741824|512|256
    
    /*
    
    1		iSQLDMOScript_Drops
    
    4096		iSQLDMOScript_IncludeIfNotExists
    
    131072		iSQLDMOScript_IncludeHeaders
    
    4		iSQLDMOScript_Default
    
    1073741824	iSQLDMOScript_NoIdentity
    
    512		iSQLDMOScript_NoDRI
    
    256		SQLDMOScript_AppendToFile
    
    */
    
    
    
    
    
    --Preparation
    
    EXEC sp_OACreate 'SQLDMO.SQLServer', @oServer OUT
    
    EXEC sp_OASetProperty @oServer, 'loginsecure', 'true'
    
    EXEC sp_OAMethod @oServer, 'Connect', NULL, CHRIS-H-XP
    
    
    
    
    
    --Create Tables Script
    
    DECLARE TablesToCreate CURSOR
    
    	FOR SELECT [name] FROM LIVE_CONTROL..sysobjects WHERE xtype = 'U'
    
    
    
    OPEN TablesToCreate
    
    	FETCH NEXT FROM TablesToCreate INTO @TName
    
    	WHILE @@FETCH_STATUS = 0
    
    	BEGIN
    
    	SET @method = 'Databases("LIVE_CONTROL").Tables("'+@TName+'").Script (' + CAST (@ScriptType AS CHAR) +',"C:\Documents and Settings\chris_h\Desktop\DNT_Tables.sql")'
    
    	EXEC sp_OAMethod @oServer, @method ,                           
    
    							@TSQL OUTPUT
    
    	FETCH NEXT FROM TablesToCreate INTO @TName
    
    	END
    
    CLOSE TablesToCreate
    
    DEALLOCATE TablesToCreate 
    
    
    
    
    
    
    
    --Create Indexes Script
    
    DECLARE IndexesToCreate CURSOR
    
    	FOR SELECT [name] FROM LIVE_CONTROL..sysobjects WHERE xtype IN ('UQ','PK')
    
    
    
    OPEN IndexesToCreate
    
    	FETCH NEXT FROM IndexesToCreate INTO @IName
    
    	WHILE @@FETCH_STATUS = 0
    
    	BEGIN
    
    
    
    	SET @ITID = (SELECT parent_obj FROM LIVE_CONTROL..sysobjects WHERE [name] = @IName)
    
    	SET @ITName = (SELECT [name] FROM LIVE_CONTROL..sysobjects WHERE id = @ITID)		
    
    
    
    	SET @method = 'Databases("LIVE_CONTROL").Tables("'+@ITName+'").Indexes("'+@IName+'").Script' +              
    
    				'(' + CAST (@ScriptType AS CHAR) + 	',"C:\Documents and Settings\chris_h\Desktop\DNT_Indexes.sql")'
    
    	EXEC sp_OAMethod @oServer, @method ,                           
    
    							@TSQL OUTPUT
    
    
    
    	
    
    	FETCH NEXT FROM IndexesToCreate INTO @IName
    
    	END
    
    CLOSE IndexesToCreate
    
    DEALLOCATE IndexesToCreate
    
    
    
    
    
    EXEC sp_OADestroy @oServer

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this looks wrong --

    SET @ScriptType = 1|4096|131072|4|1073741824|512|256

    and so does this --

    EXEC sp_OAMethod @oServer, 'Connect', NULL, CHRIS-H-XP
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Looks like you need quotes around the name 'CHRIS-H-XP':
    Code:
    EXEC sp_OAMethod @oServer, 'Connect', NULL, CHRIS-H-XP

  4. #4
    Join Date
    Apr 2009
    Posts
    25
    Buddabing. Cheers guys double quotes around the server name "CHRIS-H-XP" sorted it out.

    Thanks again

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •