error parsing file right after CREATE statement

merkmerk Posts: 17
I'm trying to use sql source control. This is a new install on a new machine. I've linked my dev database to the source control (SVN). I'm on the 'get latest' tab and that is where i get the error. In this case the full error is:

Errors occurred whilst parsing file C:\Users\Ian\AppData\Local\Red Gate\SQL Source Control 3\Transients\05j54fiv.xrc\Stored Procedures\dbo.spViewProductStatisticByUserID.sql
'' at line 7, column 7

So far the error message is always the same, the only difference is the particular file it's complaining about and the line and column. It seems to be complaining about the same 3 or 4 files, although it seems to change randomly which of the 4 it's going to complain about.

The one commonality between them is that it always seems to indicate the problem is right after the CREATE statement.

As an example, here is line 7 column 7 from from the above file:
CREATE PROCEDURE [dbo].[spViewProductStatisticByUserID]

Anyone have any ideas whats going on here? I already tried deleting all the files in that folder, had no effect.
Tagged:

Comments

  • James BJames B Posts: 1,124 Silver 4
    Hi,

    The line number in the error often doesn't seem to translate back to an actual line number when viewing the script (this happens to me with SSMS too) so we'd probably need to see the full script to understand what's happening. It's possible you're using some syntax our parser doesn't understand, especially if using some of the new SQL 2012 functions. The actual "create" line you posted looks normal enough.
    Systems Software Engineer

    Redgate Software

  • merkmerk Posts: 17
    edited August 1, 2017 9:07AM
    here's the full sql
    USE [MachineSales]
    GO
    /****** Object:  StoredProcedure [dbo].[spViewProductStatisticByUserID]    Script Date: 8/3/2012 1:48:00 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    
    ALTER PROCEDURE [dbo].[spViewProductStatisticByUserID]
    	@ID			INT,
    	@StartRow	INT,
    	@PageSize	INT
    AS
    BEGIN
    	SET NOCOUNT ON
    	
    	SELECT p.[ID], p.[Title], p.[ShortDescription], p.[Price], img.[ImageThumbID] AS [ImageID], ps.[SearchResults], ps.[PageViews], ps.[PhoneClicks], ps.[EmailClicks], ps.[WebsiteClicks],
    			p.[Modified], p.[ModifiedBy], p.[Created], p.[CreatedBy]
    	FROM	[Product] AS p
    	INNER JOIN [ProductStatistic] AS ps ON ps.[ProductID] = p.[ID] 
    	LEFT JOIN [ProductImage] AS img ON img.[ProductID] = p.[ID] AND img.[SortOrder] = (SELECT MIN([SortOrder]) FROM [ProductImage] WHERE [ProductID] = p.[ID])
    	WHERE	p.[Deleted] = 0 
    	AND		p.[UserID] = @ID
    	ORDER BY p.[Created],p.[ID] DESC
    	OFFSET @StartRow ROWS
    	FETCH NEXT @PageSize ROWS ONLY
    END
    
  • Thanks - looks like the fetch and offset is likely to be the cause (new in 2012).
    I think that SQL Compare has recently been updated with support for this, so hopefully it'll make its way into the next build of SQL Source Control in a week or two.
    Systems Software Engineer

    Redgate Software

  • bkillenbkillen Posts: 4 New member
    edited August 1, 2017 9:07AM
    I'm having very similar issues. When I link to a folder with the same name as the database, I do not get any errors. For example, if the database is named NLS_Prod and I link to a GIT folder named NLS_Prod. I get 0 errors. When I try to link the database NLS_Prod to a GIT folder NLS. I get an error trying to parse a table definition. Below is the file in question. I noticed that on line 281, the "C" in the create index is being left off and another ")" is added on line 284.
    /****** Object:  Table [dbo].[loanacct]    Script Date: 6/23/2017 7:46:29 AM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[loanacct]') AND type in (N'U'))
    BEGIN
    CREATE TABLE [dbo].[loanacct](
    	[acctrefno] [numeric](10, 0) IDENTITY(1,1) NOT NULL,
    	[loan_type] [numeric](1, 0) NOT NULL,
    	[portfolio_code_id] [numeric](9, 0) NOT NULL,
    	[loan_group_no] [numeric](9, 0) NOT NULL,
    	[master_acctrefno] [numeric](10, 0) NOT NULL,
    	[pool_acctrefno] [numeric](10, 0) NOT NULL,
    	[restructured_acctrefno] [numeric](10, 0) NOT NULL,
    	[shadow_loan_exists] [numeric](1, 0) NOT NULL,
    	[cifno] [numeric](10, 0) NOT NULL,
    	[dealer_cifno] [numeric](10, 0) NULL,
    	[name] [varchar](200) NULL,
    	[loan_class1_no] [numeric](9, 0) NOT NULL,
    	[loan_class2_no] [numeric](9, 0) NOT NULL,
    	[shortname] [varchar](20) NULL,
    	[risk_rating_no] [numeric](9, 0) NOT NULL,
    	[loan_number] [varchar](25) NOT NULL,
    	[tin] [varchar](100) NULL,
    	[entity] [varchar](25) NOT NULL,
    	[input_date] [datetime] NOT NULL,
    	[input_gl_date] [datetime] NOT NULL,
    	[status_code_no] [numeric](9, 0) NOT NULL,
    	[loan_officer_no] [numeric](9, 0) NULL,
    	[open_date] [datetime] NOT NULL,
    	[collection_officer_no] [numeric](9, 0) NULL,
    	[curr_date] [datetime] NOT NULL,
    	[open_maturity_date] [datetime] NULL,
    	[curr_maturity_date] [datetime] NULL,
    	[payoff_date] [datetime] NULL,
    	[closed_date] [datetime] NULL,
    	[last_activity_date] [datetime] NULL,
    	[processing_start_date] [datetime] NOT NULL,
    	[interest_paid_thru_date] [datetime] NOT NULL,
    	[principal_paid_thru_date] [datetime] NOT NULL,
    	[interest_accrued_thru_date] [datetime] NOT NULL,
    	[original_note_amount] [numeric](16, 2) NOT NULL,
    	[current_note_amount] [numeric](16, 2) NOT NULL,
    	[current_principal_balance] [numeric](16, 2) NOT NULL,
    	[current_interest_balance] [numeric](24, 10) NOT NULL,
    	[current_fees_balance] [numeric](19, 5) NOT NULL,
    	[current_def_interest_balance] [numeric](24, 10) NOT NULL,
    	[current_late_charge_balance] [numeric](16, 2) NOT NULL,
    	[current_payoff_balance] [numeric](24, 10) NOT NULL,
    	[current_udf1_balance] [numeric](19, 5) NOT NULL,
    	[current_perdiem] [numeric](21, 10) NOT NULL,
    	[current_suspense] [numeric](16, 2) NOT NULL,
    	[current_udf2_balance] [numeric](19, 5) NOT NULL,
    	[current_interest_rate] [numeric](12, 8) NOT NULL,
    	[current_udf3_balance] [numeric](19, 5) NOT NULL,
    	[total_past_due_balance] [numeric](16, 2) NOT NULL,
    	[current_udf4_balance] [numeric](19, 5) NOT NULL,
    	[total_current_due_balance] [numeric](16, 2) NOT NULL,
    	[current_udf5_balance] [numeric](19, 5) NOT NULL,
    	[current_udf6_balance] [numeric](19, 5) NOT NULL,
    	[starting_interest_rate] [numeric](12, 8) NOT NULL,
    	[times_renewed] [numeric](5, 0) NOT NULL,
    	[current_def_perdiem] [numeric](21, 10) NOT NULL,
    	[current_impound_balance] [numeric](16, 2) NOT NULL,
    	[current_pending] [numeric](16, 2) NOT NULL,
    	[current_ol1_balance] [numeric](19, 5) NOT NULL,
    	[current_udf7_balance] [numeric](19, 5) NOT NULL,
    	[current_ol2_balance] [numeric](19, 5) NOT NULL,
    	[starting_year_interest_rate] [numeric](12, 8) NOT NULL,
    	[current_ol3_balance] [numeric](19, 5) NOT NULL,
    	[current_udf8_balance] [numeric](19, 5) NOT NULL,
    	[last_rate_change_date] [datetime] NULL,
    	[current_udf9_balance] [numeric](19, 5) NOT NULL,
    	[times_extended] [numeric](5, 0) NOT NULL,
    	[current_udf10_balance] [numeric](19, 5) NOT NULL,
    	[next_rate_change_date] [datetime] NULL,
    	[next_interest_step_date] [datetime] NULL,
    	[default_interest_indicator] [numeric](1, 0) NOT NULL,
    	[addonint_total] [numeric](21, 10) NOT NULL,
    	[addonint_remaining] [numeric](21, 10) NOT NULL,
    	[starting_year_def_int_rate] [numeric](12, 8) NOT NULL,
    	[starting_def_interest_rate] [numeric](12, 8) NOT NULL,
    	[next_rate_change_notice_date] [datetime] NULL,
    	[compound_interest_balance] [numeric](21, 10) NOT NULL,
    	[current_def_interest_rate] [numeric](12, 8) NOT NULL,
    	[next_accrual_cutoff] [datetime] NULL,
    	[last_def_rate_change_date] [datetime] NULL,
    	[next_def_rate_change_date] [datetime] NULL,
    	[next_billing_date] [datetime] NULL,
    	[next_recurring_trans_date] [datetime] NULL,
    	[next_def_interest_step_date] [datetime] NULL,
    	[next_statement1_date] [datetime] NULL,
    	[next_def_rate_change_notice] [datetime] NULL,
    	[days_in_current_period] [numeric](5, 0) NULL,
    	[default_def_interest_indicator] [numeric](1, 0) NOT NULL,
    	[next_statement2_date] [datetime] NULL,
    	[next_amort_fees_date] [datetime] NULL,
    	[amortized_fees_eim_flag] [numeric](1, 0) NOT NULL,
    	[days_past_due] [numeric](5, 0) NOT NULL,
    	[next_pending_date] [datetime] NULL,
    	[compound_def_interest_balance] [numeric](21, 10) NOT NULL,
    	[compound_interest_indicator] [numeric](1, 0) NOT NULL,
    	[compound_def_int_indicator] [numeric](1, 0) NOT NULL,
    	[current_servicing_balance] [numeric](16, 2) NOT NULL,
    	[current_reserve_balance] [numeric](16, 2) NOT NULL,
    	[SAC_expiration_date] [datetime] NULL,
    	[SAC_ineligible_date] [datetime] NULL,
    	[CurrencyID] [numeric](9, 0) NOT NULL,
    	[intro_interest_expiration] [datetime] NULL,
    	[intro_def_interest_expiration] [datetime] NULL,
    	[tin_hash] [varchar](50) NULL,
    	[current_adb_interest_balance] [numeric](24, 10) NOT NULL,
    	[Branch_cifno] [numeric](10, 0) NULL,
    	[statement1_billing_end_date] [datetime] NULL,
    	[statement2_billing_end_date] [datetime] NULL,
     CONSTRAINT [PK__loanacct__1FCDBCEB] PRIMARY KEY CLUSTERED 
    (
    	[acctrefno] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    END
    GO
    IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[loanacct]') AND name = N'in_Loanacct_loan_Class1_no')
    REATE NONCLUSTERED INDEX [in_Loanacct_loan_Class1_no] ON [dbo].[loanacct]
    (
    	[loan_class1_no] ASC
    )
    )
    INCLUDE ([acctrefno],[portfolio_code_id],[loan_group_no],[cifno],[dealer_cifno],[loan_class2_no],[loan_number],[input_date],[open_date],[curr_maturity_date],[payoff_date],[closed_date],[original_note_amount],
    [current_note_amount],[current_principal_balance],[current_interest_balance],[current_fees_balance],[current_late_charge_balance],[current_payoff_balance],[current_udf1_balance],[current_udf2_balance],[current_interest_rate],
    [current_udf3_balance],[total_past_due_balance],[current_udf4_balance],[current_udf5_balance],[current_udf6_balance],[current_udf7_balance],[starting_year_interest_rate],[current_udf8_balance],[current_udf9_balance],
    [current_udf10_balance],[addonint_total],[addonint_remaining],[compound_interest_balance],[days_past_due])
    
    	 WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    GO
    
  • I found the error, it was the initial check-in that had errors in the file.
  • Glad you were able to figure it out bkilen, thanks for letting us know.
Sign In or Register to comment.