Showing posts with label SQL Errors. Show all posts
Showing posts with label SQL Errors. Show all posts

Sunday, August 28, 2022

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

 Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

This message occurs when you are doing a SELECT DISTINCT combined with the ORDER BY clause and one of the columns in the ORDER BY is not specified as one of the columns in the SELECT DISTINCT.

SELECT DISTINCT orderid
FROM [dbo].[Orders]
ORDER BY [OrderDate] DESC
Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.  

SELECT orderid
FROM [dbo].[Orders]
GROUP BY orderid
ORDER BY MAX([OrderDate]) DESC


Cannot assign a default value to a local variable

Msg 139, Level 15, State 1, Line 1
Cannot assign a default value to a local variable. 

Prior to SQL Server 2008, assigning a default value (or initial value) to a local variable is not allowed; otherwise this error message will be encountered.

This error message can easily be generated using the following DECLARE statement entered in either SQL Server 2000 or SQL Server 2005.
DECLARE @CurrentDate DATETIME = GETDATE()
Msg 139, Level 15, State 1, Line 0
Cannot assign a default value to a local variable.

SQL Server now allows the assigning of a value to a variable in the DECLARE statement.

Cannot use a BREAK statement outside the scope of a WHILE statement

 Msg 135, Level 16, State 1, Line 1
Cannot use a BREAK statement outside the scope of a WHILE statement.

The BREAK statement exits the innermost loop in a WHILE or IF… ELSE statement. Any statements appearing after the END keyword, marking the end of the loop, are executed. BREAK is frequently, but not always, started by an IF test condition.

The error message occurs when using the BREAK statement outside a WHILE statement. Here are a few examples of how this error is encountered:
-- BREAK used inside an IF condition but outside a WHILE statement
IF NOT EXISTS (SELECT custid FROM [dbo].[Customers]
               WHERE custname = 'Cust_1')
    BREAK
-- Misplaced BREAK Condition
DECLARE @Counter  INT
DECLARE @UserID   INT

SET @Counter = 0
SET @UserID = 1
WHILE EXISTS (SELECT * FROM [dbo].[UserTransaction]
              WHERE [UserID] = @UserID) 
    DELETE TOP (10) FROM [dbo].[UserTransaction]
    WHERE [UserID] = @UserID

    SET @Counter = Counter + 1

    IF @Counter > 10
        BREAK
Msg 135, Level 15, State 1, Line 38
Cannot use a BREAK statement outside the scope of a WHILE statement.

  • The BREAK statement can only be used inside the scope of a WHILE statement. In cases when a set of Transact-SQL statements need to be skipped if a particular condition is not met, instead of using a BREAK statement, the GOTO statement can be used.
  • In the case of exiting a stored procedure if a particular condition is met, instead of using the BREAK statement, the RETURN statement should be used. The RETURN statement exits unconditionally from a query or procedure. RETURN is immediate and complete and can be used at any point to exit from a procedure, batch or statement block.
  • When executing a statement block or statement group within a WHILE loop, the groups of statements need to be enclosed within a BEGIN END blocks. Otherwise only the first Transact-SQL statement within that statement block will be part of the WHILE loop. The other Transact-SQL statement will be executed after the condition in the WHILE loop is not met anymore or a BREAK statement has been encountered within the WHILE loop.

DECLARE @Counter  INT
DECLARE @UserID   INT

SET @Counter = 0
SET @UserID = 1
WHILE EXISTS (SELECT * FROM [dbo].[UserTransaction]
              WHERE [UserID] = @UserID)
BEGIN
    DELETE TOP (10) FROM [dbo].[UserTransaction]
    WHERE [UserID] = @UserID

    SET @Counter = Counter + 1

    IF @Counter > 10
        BREAK
END


Cannot update a timestamp column

 Msg 272, Level 16, State 1, Line 1
Cannot update a timestamp column.

The timestamp data type is an 8-byte data type that exposes automatically generated, unique binary numbers within a database. It is generally used as a mechanism for version-stamping table rows.

Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a timestamp column within the database. This counter, the database timestamp, tracks a relative time within a database and not an actual time that can be associated with a clock.
Since the timestamp column is automatically generated by the database and is automatically incremented by the database during an update on the table, this error will be encountered if the timestamp column is manually updated through an UPDATE command.

A table can only have one timestamp column. Every time that a row with a timestamp column is modified or inserted, the incremented database timestamp value is inserted in the timestamp column.

CREATE TABLE [dbo].[Team] (
    [TeamID]     INT NOT NULL PRIMARY KEY IDENTITY(1, 1),
    [TeamName]   VARCHAR(50),
    TIMESTAMP )
Insert one record into the table.
INSERT INTO [dbo].[Team] ( [TeamName], [Timestamp] )
VALUES ( 'Miami Marlins', DEFAULT )

select * from [dbo].[Team]
Output:

TeamID

TeamName

TIMESTAMP

1

Miami Marlins

0x00000000000007D1


Let's try to update which will generate the error msg.
UPDATE [dbo].[Team]
SET [Timestamp] = @@DBTS
WHERE [TeamName] = 'Miami Marlins'
Msg 272, Level 16, State 1, Line 11
Cannot update a timestamp column.

The timestamp column is usually used to determine whether any value in the row has changed since the last time it was read. If any change is made to the row, the timestamp value is updated. If no change is made to the row, the timestamp value is the same as when it was previously read.

If there’s really a need to update the timestamp column of a table just to mark that particular record as having been updated, here’s one work around that can be done without really affecting the row but having the timestamp column updated:
UPDATE [dbo].[Team]
SET [TeamName] = [TeamName]
WHERE [TeamName] = 'Miami Marlins'

select * from [dbo].[Team]
Output:

TeamID

TeamName

TIMESTAMP

1

Miami Marlins

0x00000000000007D2


Select statements included within a function cannot return data to a client

Msg 444, Level 16, State 2, Line 1
Select statements included within a function cannot return data to a client.

When you are trying to issue a SELECT statement inside a function that will return the result to the caller, which is not allowed inside a function.

Let's see the below example to understand more.

CREATE FUNCTION [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
RETURNS VARCHAR(100)
AS 
BEGIN
    DECLARE @ErrorMessage VARCHAR(100)

    SET @ErrorMessage = ''
    IF EXISTS (SELECT 'X' FROM [dbo].[Users] WHERE [UserName] = @UserName)
        SELECT * FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    ELSE
        SET @ErrorMessage = 'Invalid User Name'

    RETURN @ErrorMessage
END
GO
Msg 444, Level 16, State 2, Procedure GetUserInformation, Line 9 [Batch Start Line 0]
Select statements included within a function cannot return data to a client.

If you need to return result sets to the client, you have to use a stored procedure and not a function for this purpose.

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 

SELECT * FROM [dbo].[Users]
WHERE [UserName] = @UserName
GO

If you want to return an error message if the user is not found, one way to do this is to return the error message as a separate result set:

CREATE PROCEDURE [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS 
    IF EXISTS (SELECT 'X' FROM [dbo].[Users]
               WHERE [UserName] = @UserName)
    BEGIN
        SELECT '' AS [ErrorMessage]
        SELECT * FROM [dbo].[Users]
        WHERE [UserName] = @UserName
    END
    ELSE
        SELECT 'Invalid User Name' AS [ErrorMessage]
GO
If the user name is found in the Users table, 2 result sets are returned.  The first result set contains the error message, which is an empty string, and the second result set is the user information.  If the user name is not found in the Users table, only 1 result set is returned which contains the error message.

Saturday, August 27, 2022

The TABLESAMPLE clause can only be used with local tables

Msg 494, Level 16, State 1, Line 1
The TABLESAMPLE clause can only be used with local tables.

The TABLESAMPLE clause cannot be applied to derived tables, tables from linked servers, and tables derived from table-valued functions, rowset functions, or OPENXML. TABLESAMPLE cannot also be specified in the definition of a view or an inline table-valued function.

If the TABLESAMPLE clause is applied to a table-valued function, such as the following SELECT statement, then this error message will be generated:
CREATE FUNCTION [dbo].[ufn_GetCustomersByZIPCode] ( @ZIPCode VARCHAR(5))
RETURNS TABLE
AS
RETURN (SELECT * FROM [dbo].[Customers]
        WHERE [ZIPCode] = @ZIPCode)
GO


SELECT * FROM [dbo].[ufn_GetCustomersByZIPCode] A TABLESAMPLE (100 ROWS)
Msg 494, Level 16, State 1, Line 8
The TABLESAMPLE clause can only be used with local tables.

If the purpose of using the TABLESAMPLE clause is to generate a random set of rows from a table, table-valued function or view, then instead of using the TABLESAMPLE clause, the TOP clause together with the ORDER BY NEWID() clause can be used in its place.

In the case of the inline table-valued function shown earlier, the following SELECT statement can be used in its place which will generate 100 random rows from the rows returned by the inline table-valued function:
SELECT TOP 100 *
FROM [dbo].[ufn_GetCustomersByZIPCode] ( '12345' ) A
ORDER BY NEWID()


Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses

Msg 497, Level 15, State 1, Line 1
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

The TABLESAMPLE clause, limits the number of rows returned from a table in the FROM clause to a sample number or PERCENT of rows. The syntax for the TABLESAMPLE clause is as follows:

One limitation of the TABLESAMPLE clause is that both the sample_number and repeat_seed parameters cannot be local variables; otherwise this error message is generated.
To generate the error let's see the below example.

Example:
CREATE TABLE [dbo].[CallDetailRecord] ( 
    [CallDetailRecordID]        INT NOT NULL IDENTITY(1, 1),
    [SourceNumber]              VARCHAR(30),
    [DestinationNumber]         VARCHAR(30),
    [CallTime]                  DATETIME,
    [CallDuration]              INT
)


-- Using a local variable for the sample number of rows
DECLARE @RowCount INT
SET @RowCount = 100
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( @RowCount ROWS)
Msg 497, Level 15, State 1, Line 12
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.
-- Using a local variable for sample percentage
DECLARE @PercentSample	DECIMAL(5, 2)
SET @PercentSample = 75.0
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( @PercentSample PERCENT)
Msg 497, Level 15, State 1, Line 17
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.
-- Using a local variable for the repeat seed
DECLARE @RepeatSeed INT
SET @RepeatSeed = 124
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) REPEATABLE ( @RepeatSeed )
Msg 497, Level 15, State 1, Line 22
Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.

Solution :

One way to avoid this error is not to make use of local variables when specifying the sample number of rows, the sample percentage or the repeat seed. Using the examples above, the following SELECT statement will generate the desired output:
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 100 ROWS )
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 75.0 PERCENT )
SELECT * FROM [dbo].[CallDetailRecord] TABLESAMPLE ( 50 PERCENT ) REPEATABLE ( 124 )

To make it dynamic it can be done in Dynamic SQL statements.

-- Using a local variable for the sample number of rows
DECLARE @RowCount INT
DECLARE @SQLStmt VARCHAR(100)
SET @RowCount = 100
SET @SQLStmt = 'SELECT * FROM [dbo].[CallDetailRecord] 
                TABLESAMPLE ( ' + CAST(@RowCount AS VARCHAR(10)) + ' ROWS )'
EXECUTE ( @SQLStmt )

Similarly other example can be done like above.

Saturday, July 9, 2022

The scale must be less than or equal to the precision

Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.

To avoid the error below points can be followed.
  • The scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point
  • In the case of the incorrect scale in the definition of a DECIMAL or NUMERIC data type in column of a table, simply increase the size of the precision to include the digits both before and after the decimal point.
Try to find out the error:
DECIMAL and NUMERIC are numeric data types that have fixed precision and scale. When maximum precision is used, which is 38, valid values are from -10^38 through 10^38 – 1. NUMERIC data type is functionally equivalent to DECIMAL data type. The syntax for declaring a local variable or a column as DECIMAL or NUMERIC data type is as follows:
DECIMAL ( p [, s] )
NUMERIC ( p [, s] )

Precision (p) is the maximum number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision of 38. The default precision is 18.

The optional scale (s) is the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through the value of the precision (p). Scale can only be specified if precision is specified. The default scale is 0.

Given the definition of the precision and scale of a DECIMAL or NUMERIC data type, this error message will be encountered if the specified scale is greater than the precision when defining a local variable.
DECLARE @Pi    DECIMAL(1, 6) -- 3.141592
Msg 192, Level 15, State 1, Line 1
The scale must be less than or equal to the precision.
DECLARE @Latitude    DECIMAL(2, 6)
DECLARE @Longitue    DECIMAL(3, 6)
Msg 192, Level 15, State 1, Line 2
The scale must be less than or equal to the precision.
Msg 192, Level 15, State 1, Line 3
The scale must be less than or equal to the precision.

A different error message will be encountered when the scale is greater than the precision when defining a DECIMAL or NUMERIC column in a table:
CREATE TABLE [dbo].[Product] ( 
    [ProductID]      INT,
    [ProductName]    VARCHAR(100),
    [Width]          DECIMAL(4, 6),
    [Length]         DECIMAL(4, 6),
    [Height]         DECIMAL(4, 6)
)
Msg 183, Level 15, State 1, Line 8
The scale (6) for column 'Width' must be within the range 0 to 4.

To avoid this error, as the error message suggests, the scale must be within the range of 0 and the value of the precision. This can easily be done by increasing the value of the precision to include the digits both before and after the decimal point.
Here’s an updated version of the scripts earlier that fixes the issue:
DECLARE @Pi    DECIMAL(7, 6) -- 3.141592
DECLARE @Latitude    DECIMAL(8, 6) –- 2 Digits to the left and 6 digits to the right.
DECLARE @Longitue    DECIMAL(9, 6) –- 3 Digits to the left and 6 digits to the right.

In the case of the incorrect scale in the definition of a DECIMAL or NUMERIC data type in column of a table, simply increase the size of the precision to include the digits both before and after the decimal point.
CREATE TABLE [dbo].[Product] ( 
    [ProductID]      INT,
    [ProductName]    VARCHAR(100),
    [Width]          DECIMAL(10, 6),
    [Length]         DECIMAL(10, 6),
    [Height]         DECIMAL(10, 6)
)

Sunday, July 3, 2022

The last statement included within a function must be a return statement

 Msg 455, Level 16, State 2, Line 1
The last statement included within a function must be a return statement.

As the error message suggests, the last statement in a function must be a RETURN statement.  Even if the execution path of the statements in a function will execute a RETURN statement, the error will still be encountered.

To understand better, here’s a user-defined function that returns the smaller number between two integer parameters:

CREATE FUNCTION [dbo].[ufn_Least] ( @pInt1 INT, @pInt2 INT )
RETURNS INT
AS
BEGIN
    IF @pInt1 > @pInt2
        RETURN @pInt2
    ELSE
        RETURN @pInt1
END
GO
Output:
Msg 455, Level 16, State 2, Procedure ufn_Least, Line 8 [Batch Start Line 0]
The last statement included within a function must be a return statement.

To avoid this error, make sure that the last statement in your user-defined function is the RETURN statement.  In the case of the user-defined function shown above, here’s an updated version of the function that gets rid of the error:

CREATE FUNCTION [dbo].[ufn_Least] ( @pInt1 INT, @pInt2 INT )
RETURNS INT
AS
BEGIN
IF @pInt1 > @pInt2
    RETURN @pInt2

RETURN @pInt1
END
GO

Instead of putting the last RETURN statement inside the ELSE statement, it is executed by itself and the function still produces the same result.

Sunday, June 19, 2022

Case expressions may only be nested to level %d

Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level %d.

I never experienced this in SQL Server 2017 onwards, but this was being experienced in previous version on Linked Server only. So below example is the demonstration of that assuming linked server.

Declare @id as int
set @id=13
SELECT 
    (CASE @Id
        WHEN 1 THEN 1
        WHEN 2 THEN 2
        WHEN 3 THEN 3
        WHEN 4 THEN 4
        WHEN 5 THEN 5
        WHEN 6 THEN 6
        WHEN 7 THEN 7
        WHEN 8 THEN 8
        WHEN 9 THEN 9
        WHEN 10 THEN 10
        WHEN 11 THEN 11
	WHEN 12 THEN 12
        WHEN 13 THEN 13
        WHEN 14 THEN 14
    END) AS Test
	from [SANTANA\MSSQLSERVER17].Rohit.dbo.Emp

You can resolve this by using below technique.

Using multiple coalesce cases.
Using Open query.

Points to remember :
  • The above query will work absolutely fine if you run from the local instances even more than 10 conditions.this error message only happens when we are applying a case from data that comes from a Linked Server.
  • If the query does not use Linked Server, you do not need to use this technique. You can use as many conditions as you want in the same case.
  • If your query uses Linked Server that points to its own instance, you do not need to use this technique either. It works like a normal query without linked server.
  • The COALESCE function accepts multiple conditions, so it is not limited to just 2 cases, they can be multiple (although the complexity of the code is increasing.)
  • You can only use up to 9 conditions in each CASE in scenarios where the query is done on remote data.
  • Using ELSE NULL at the end of each case is optional.
  • Subquery and CTE do not resolve this issue.
  • OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either.


Saturday, June 18, 2022

The select list for the INSERT statement contains more items than the insert list

 Msg 121, Level 15, State 1, Line 1

The select list for the INSERT statement contains more items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

SQL Server error 121 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
It happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Here is the example of the code that cause the error.

INSERT INTO OrdersLatest (
    OrderId,OrderDate
    )
SELECT 
    OrderId,OrderDate,OrderDesc
	FROM OrdersMarch;

Output:

 Msg 121, Level 15, State 1, Line 1
The select list for the INSERT statement contains more items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

You can fix this by correcting it.

INSERT INTO OrdersLatest (
    OrderId,OrderDate
    )
SELECT 
    OrderId,OrderDate
	FROM OrdersMarch;

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.


The select list for the INSERT statement contains fewer items than the insert list

 Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

SQL Server error 120 occurs when you don’t specify enough columns in your INSERT list when using a SELECT list for the values to insert.
It happens when you use a SELECT list in your INSERT statement, but the SELECT list doesn’t return as many columns as you’re specifying with the INSERT.

This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.

Here is the example of the code that cause the error.

INSERT INTO OrdersLatest (
    OrderId,OrderDate,OrderDesc
    )
SELECT 
    OrderId,OrderDate
	FROM OrdersMarch;

Output:

 Msg 120, Level 15, State 1, Line 1
The select list for the INSERT statement contains fewer items than the insert list. 
The number of SELECT values must match the number of INSERT columns.

You can fix this by correcting it.

INSERT INTO OrdersLatest (
    OrderId,OrderDate,OrderDesc
    )
SELECT 
    OrderId,OrderDate,OrderDesc
	FROM OrdersMarch;

You can use less columns this would depend on whether or not we have any NOT NULL constraints on the destination table.


Thursday, June 16, 2022

SQL Error 119 - Must pass parameter number 2 and subsequent parameters

 Msg 119, Level 15, State 1, Line 1

Must pass parameter number 2 and subsequent parameters as '@name = value'.  After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Reason : If we want to use variable names while passing parameters to stored procedure then we must have to use variable in all the parameters.

Suppose we have created a simple stored procedure uspDisplayData in SQL Server

CREATE PROCEDURE uspDisplayData(
     @Param1 AS VARCHAR(100),
     @Param2 AS INT
)
AS
SELECT @Param1, @Param2

Now if we will execute this stored procedure:

EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',2

We will get error message like:

Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

Solution:

Correct ways to pass the parameters are:
EXECUTE dbo.uspDisplayData 'Sql Server',2
OR
EXECUTE dbo.uspDisplayData @Param1 = 'Sql Server',@Param2 = 2

This will result correct result and execute without error. 

Sunday, June 5, 2022

The object name 'Object Name' contains more than the maximum number of prefixes. The maximum is 2

Msg 117, Level 15, State 2, Line 4
The object name 'Object Name' contains more than the maximum number of prefixes.  The maximum is 2.

Lets see the below example. Trying Select the records.

select * from [SANTANA\MSSQLSERVER17].Rohit.dbo.Employee.Employeeid
where EmployeeID=101

Output:

Msg 117, Level 15, State 1, Line 2
The object name 'SANTANA\MSSQLSERVER17.Rohit.dbo.Employee.Employeeid' contains more than the maximum number of prefixes. The maximum is 3.

In the above example Employeeid is an invalid selection results in the error.

This error usually comes up when you are trying to perform a SELECT..INTO across a linked server. The problem is that a 4 part name cannot be used as the “INTO” parameter. This will fail.
The trick is to create the table on the remote server, then perform an INSERT INTO.



 

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

This error is usually encountered when using a sub-query together with the IN or NOT IN logical operator. The IN logical operator determines whether a specified value matches any value in a subquery or a list.

Select EmployeeID,FirstName,LastName 
from Employee
where EmployeeID IN (Select EmployeeID,FirstName from Employee)

Output:

Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

You can't return two (or multiple) columns in your subquery to do the comparison in the EmployeeID IN (subquery) clause - which column is it supposed to compare EmployeeID to? The subquery must only return the one column needed for the comparison to the column on the other side of the IN. So the query needs to be of the form:

Select EmployeeID,FirstName,LastName 
from Employee
where EmployeeID IN (Select EmployeeID from Employee)

You also want to add sorting so you can select just from the top rows, but you don't need to return the COUNT as a column in order to do your sort; sorting in the ORDER clause is independent of the columns returned by the query.


Saturday, June 4, 2022

Browse mode is invalid for a statement that assigns values to a variable

 SQL Error 114 : Browse mode is invalid for a statement that assigns values to a variable.

NetBackup VMware backups using SQL Server application protection fails showing unimplemented error code 114(114)

Grant sysadmin permissions to the System account (NT AUTHORITY\SYSTEM) on all SQL Server instances that are installed and online on the server where you are trying to perform the VSS backup or use another  local/domain account/group that has sysadmin permissions on the SQL Server online instances for the "Log on as" account that starts the SQL VSS Writer service.


I will share more info if I have...

SQL Error 113 - Missing end comment mark

 Msg 113, Level 15, State 1, Line 1
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 are 2 ways of specifying comments in a Transact-SQL script, namely with the use of two hyphens (--) for single-line comments, and with the use of /* and */ for multi-line comments. This error message occurs when using the /* and */ for multi-line comments and the closing */ is missing.

The error can be generated below 2 ways.

/*
Select * from Employee
Go

/*
--Select * from Employee /*
Go
*/

Output:

 Msg 113, Level 15, State 1, Line 1
Missing end comment mark '*/'.

To avoid this error make sure there are same number of opening and closing tags

/*
Select * from Employee
Go */

/*
--Select * from Employee /*
Go
*/*/

'CREATE FUNCTION' must be the first statement in a query batch

 Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.

When creating the User Defined function, if the CREATE FUNCTION is not the first statement in the query, you will receive this error
There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch

Below is the SQL Script that will lead to the error.

Select * from Employee

Create FUNCTION [dbo].[Calculate_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END
Output:
Msg 111, Level 15, State 1, Line 3
'CREATE FUNCTION' must be the first statement in a query batch.

To avoid this error, CREATE FUNCTION statement must always be the first statement in the Query batch. To fix this, the GO command needs to be added to seperate the SELECT Command from the CREATE FUNCTION. One of the advantages of this is that the GO command lets the SQL Server know that its the end of the batch of T-SQL statements and anything after the GO is a new batch of query.

Select * from Employee
Go

Create FUNCTION [dbo].[Calculate_Age]
(
@DOB datetime , @calcDate datetime
)
RETURNS int
AS
BEGIN
declare @age int
IF (@calcDate < @DOB )
RETURN -1
-- If a DOB is supplied after the comparison date, then return -1
SELECT @age = YEAR(@calcDate) - YEAR(@DOB) +
CASE WHEN DATEADD(year,YEAR(@calcDate) - YEAR(@DOB)
,@DOB) > @calcDate THEN -1 ELSE 0 END
RETURN @age
END


Thursday, June 2, 2022

SQL Error 110 - There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

 Msg 110, Level 15, State 1, Line 7
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

The error occurs while trying to do insert operation using INSERT..INTO.There are fewer columns in the INSERT statement than values specified in the VALUES clause.

Example:

Create Table Students 
(
	Id int,
	FirstName Varchar(50),
	LastName Varchar(50)
)
INSERT INTO Students (Id,FirstName)
VALUES (1,'Rohit','Kumar')
Output: 

 Msg 110, Level 15, State 1, Line 7
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

This can be resolved by the number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

SQL Error 109 - There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement

 Msg 109, Level 15, State 1, Line 7
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

You will see this error message when you are doing an INSERT operation to a table using INSERT TO.. 
There are more columns in the INSERT statement than values specified in the VALUES clause

Example:

Create Table Students 
(
	Id int,
	FirstName Varchar(50),
	LastName Varchar(50)
)
INSERT INTO Students (Id,FirstName,LastName)
VALUES (1,'Rohit')
Output:

Msg 109, Level 15, State 1, Line 7
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

The above error msg is also self descrptive.