error parsing file right after CREATE statement

merk

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.

James B

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.

merk

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

James B

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.

bkillen

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

bkillen

I found the error, it was the initial check-in that had errors in the file.

[Deleted User]

Glad you were able to figure it out bkilen, thanks for letting us know.