I am updating my VS 2015, ASP.NET MVC project to have a Data Project that holds all my data models/tables, stored procedures, etc.
When I build (or rebuild), I have 1755 errors in this project, all referring to problems I see as "class problems". However, these errors are on my stored procedure files (.sql). How do I make VS realize these are .sql files and should not be treated as classes?
Some of my errors:
- CS1022 C# Type or namespace definition, or end-of-file expected
- CS0246 C# The type or namespace name 'BEGIN' could not be found (are you missing a using directive or an assembly reference?) -as in BEGIN my SQL statement
- CS0103 C# The name '@Name' does not exist in the current context. -as in a parameter name
- CS1003 C# Syntax error, ',' expected -this one is in the middle of a commented out line
I have tried updating the Properties -> Build Action to Compile (as suggested on some sites) but that didn't fix the problem.
Also, the project seems to have issues with me including the schema in the declaration, aka:
CREATE PROCEDURE [SCHEMA].[SPROC NAME] ...
It wants me to only use the stored procedure name. This also applies to my table declaration/create table statements. It errors on the following (where italicized):
CREATE TABLE [SCHEMA].[TABLE NAME]
...
ALTER TABLE [SCHEMA].[TABLE NAME] WITH CHECK
ADD CONSTRAINT [FOREIGN KEY]
FOREIGN KEY ([FIELD NAME]) REFERENCES [SCHEMA].[FOREIGN TABLE NAME]([ID])
UPDATE --- For those interested --- And New Question
To create my stored procedures, in my Solution Explorer, I right clicked on my schema, and hovered over Add and selected "Stored Procedure...", and then copied/pasted my stored procedure in from my working SSMS database. As I'm exploring my new data project, I found the "SQL Server Object Explorer" and found that none of my stored procedures were listed under this Database Project. I went through and re-added all of my stored procedures by navigating to my Database Project -> Programmability -> Stored Procedures, right click -> Add New Stored Procedure. I deleted all my initial stored procedures and this illiminated all of the weird errors.
When creating a stored procedure this way, the file was auto-populated with
CREATE PROCEDURE [SCHEMA].[SPROC NAME]
If I build with the [SCHEMA] included, I get the error
SQL71501: Procedure: [SCHEMA].[SPROC NAME] has an unresolved reference to Schema [SCHEMA]
Same thing happens for my tables (and foreign keys).
If I remove the [SCHEMA] from the beginning of all my [SPROC NAME], [TABLE NAME], etc, the errors go away, however when looking at the SQL Server Object Explorer, all my tables, stored procedures, etc are in the schema dbo.
How do I get the code to understand that all elements are in the specified schema without erroring?
1) For Schema - Do you have the "Create Schema" code as a separate file? If not, add it in and see if that makes the relationship more obvious to VS 2) Typically the Build Action would be set to "Build" 3) I have never seen the build issues you describe... Have you made sure to add in "GO" where needed so that you have valid SQL? May help if you posted the content of one of the files that VS says is causing an error.