You’ll get error 7710 if you try to split a partition in SQL Server, but you haven’t specified a “next used” filegroup.
Continue readingTag: errors
How to Fix “ALTER TABLE SWITCH statement failed” Msg 4982 (SQL Server)
If you get error message 4982 in SQL Server, it’s because your source table doesn’t have a constraint that restricts data to only the range supported by the partition that you’re trying to switch to.
Continue readingSQL Server Error 113: Missing end comment mark ‘*/’
SQL Server error message 113 occurs when you omit a closing comment mark.
This can occur when you open a comment but forget to close it. It can also occur when you accidentally type an opening comment.
There may also be odd occasions where you get this error due to other factors, such as the way your SQL utility handles certain keywords etc.
Continue readingHow to Fix “A correlation name must be specified for the bulk rowset in the from clause.” in SQL Server
If you encounter error Msg 491, Level 16 “A correlation name must be specified for the bulk rowset in the from clause” in SQL Server, it’s probably because you’re trying to read a file without using a correlation name.
Continue readingOPENJSON “Incorrect syntax near the keyword ‘with’.” in SQL Server (SOLVED)
If you’re trying to run some OPENJSON() code in SQL Server, but you’re getting error Msg 319, Level 15 “Incorrect syntax near the keyword ‘with'”, one possibility is that you really do have a syntax error.
But if you’ve check and double checked, and you’re convinced there’s no syntax error, it could actually be that the error is a side effect of having the wrong database compatibility level.
Normally you’d get error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.” when using a database compatibility level of lower than 130, but in some cases, SQL Server finds a problem with the WITH clause first.
How to Fix “Invalid object name ‘OPENJSON’.” in SQL Server
If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.
OPENJSON() is only available under compatibility level 130 or higher.
To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.
Continue readingSQL Server Error 7222: “Only a SQL Server provider is allowed on this instance”
I was trying to set up a up a linked server from SQL Server to PostgreSQL when I got error Msg 7222, Level 16 “Only a SQL Server provider is allowed on this instance”.
The message is reasonably self explanatory, but it still didn’t tell me what it was about my instance that prevented it from being allowed.
It didn’t take long to find out.
Continue readingHow to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s)…” in SQL Server
If you encounter error Msg 11535, Level 16 while trying to execute a stored procedure, it’s because you didn’t define enough result sets in the WITH RESULT SETS clause.
Some stored procedures return multiple result sets. When using the WITH RESULT SETS clause, you need to define each expected result set. You need to do this even if you only want to change the definition of one or some of the result sets.
To fix this error, simply add the additional result sets to the WITH RESULT SETS clause, each separated by a comma.
You could also fix it by removing the WITH RESULT SETS clause, but I’ll assume you’re using it for a reason (i.e. you need to redefine the result set returned by the procedure).
How to Fix “EXECUTE statement failed because its WITH RESULT SETS clause specified 2 column(s) for result set…” Msg 11537 in SQL Server
If you encounter error Msg 11537, Level 16 in SQL Server, chances are that you’re trying to execute a stored procedure by using the WITH RESULT SETS clause, but you haven’t included all the columns in your definition.
When you use the WITH RESULT SETS clause in the EXECUTE/EXEC statement, you must provide a definition for all columns returned by the stored procedure. If you don’t, you’ll get this error.
How to Fix “The associated partition function generates more partitions than there are file groups mentioned in the scheme” Msg 7707 in SQL Server
If you get error message 7707 in SQL Server, it’s because you’re trying to create a partition scheme that doesn’t specify enough filegroups to match the partition function.
Fortunately, this is easy to fix.
Continue reading