SQL difference between SQL Management studio and VS sqlproj

Question

Post reply

SQL difference between SQL Management studio and VS sqlproj

garylogin

SSC Enthusiast

Points: 113
More actions
January 3, 2020 at 12:17 pm

#3712082

I have a project "fred.sqlproj"
it includes this file, atable.sql which contains the following:
CREATE PARTITION FUNCTION [Ehouse_TP_Function](smallint) AS RANGE RIGHT FOR VALUES (N'30', N'100', N'140')
GO
CREATE PARTITION SCHEME [Ehouse_TP_Scheme] AS PARTITION [Ehouse_TP_Function] TO ([FG1], [FG2], [FG3], [FG4])
GO
CREATE TABLE [dbo].[Ehouse] (
[Id] BIGINT IDENTITY (1, 1) NOT NULL,
[AnimalTypeId] SMALLINT NOT NULL,
[AnimalId] INT NOT NULL,
) ON [Ehouse_TP_Scheme]([AnimalTypeId]);
GO
CREATE CLUSTERED INDEX [IX_Ehouse_ClusteredIndex] ON [dbo].[Ehouse]
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO
Building through VS 2017\VS2019 (SQL Server Data Tools 15.1.61903.01040) i get this error:
Severity Code Description Project File Line Suppression State
Error SQL71567: Partition Scheme: [Ehouse_TP_Scheme] cannot be specified on Table: [dbo].[Ehouse] when there is the clustered Index: [dbo].[Ehouse].[IX_Ehouse_ClusteredIndex] on the table. Product C:\Projects\R8.7-DevPd\WebApp\Src\Database\Product\dbo\Tables\ATable.sql 9
Running in SQL Management studio 17.9.1 (Microsoft SQL Server Management Studio 14.0.17289.0) i get no errors?
Any ideas would be greatly appreciated 🙂

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

frederico_fonseca SSCoach Points: 16014 More actions · Answer 2

create the clustered index on the same partition schema - other indexes do not need to be on a partition but the clustered one does

garylogin SSC Enthusiast Points: 113 More actions · Answer 3

thanks for the response...we tired that didn't work 🙁

dmbaker SSCertifiable Points: 5086 More actions · Answer 4

Is your project in Visual Studio targeting the expected version of SQL Server? Right-click the project, choose "Properties" and check the "Target Platform". If it's not right then change it then try it again. That's probably not the problem but it's worth a check if you've not already checked that. You might also check the Database Settings (also under Properties [Project Settings]), maybe there's some setting that's not the same (e.g. compatibility level or something).

Could also be a bug of course, or something else somewhere else, but I don't know what/where.