tag:blogger.com,1999:blog-72913457358489201292024-03-06T02:19:38.733+05:30ROHIT techVlogSoftware guide, IT Technical Knowledge sharing and Technology Discussion Forum.Unknownnoreply@blogger.comBlogger156125tag:blogger.com,1999:blog-7291345735848920129.post-78323173591170060432022-08-28T22:50:00.001+05:302022-08-28T22:50:47.503+05:30ORDER BY items must appear in the select list if SELECT DISTINCT is specified<div style="text-align: left;"><span style="font-family: Cambria;"> Msg 145, Level 15, State 1, Line 1<br />ORDER BY items must appear in the select list if SELECT DISTINCT is specified.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /><span style="background-color: white;">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.</span></span></div><p><span style="background-color: white; font-family: Cambria;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Cambria;"><span style="color: #2b00fe;">SELECT DISTINCT</span> orderid
<span style="color: #2b00fe;">FROM </span>[dbo].[Orders]
<span style="color: #2b00fe;">ORDER BY</span> [OrderDate] <span style="color: #2b00fe;">DESC</span></span></pre><div style="text-align: left;"><span style="color: red; font-family: Cambria;">Msg 145, Level 15, State 1, Line 1<br />ORDER BY items must appear in the select list if SELECT DISTINCT is specified.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /><span style="background-color: white;">Instead of using the DISTINCT clause, you can use the GROUP BY clause to get the unique records from a table.</span><span style="background-color: white;"> </span> </span></div><p><span style="background-color: white; font-family: Cambria;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Cambria;"><span style="color: #2b00fe;">SELECT </span>orderid
<span style="color: #2b00fe;">FROM </span>[dbo].[Orders]
<span style="color: #2b00fe;">GROUP BY</span> orderid
<span style="color: #2b00fe;">ORDER BY</span> <span style="color: #ff00fe;">MAX</span>([OrderDate]) <span style="color: #2b00fe;">DESC</span></span></pre><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-149886649954167122022-08-28T22:27:00.002+05:302022-08-28T22:27:35.822+05:30Cannot assign a default value to a local variable<div style="text-align: left;"><span style="font-family: Cambria;">Msg 139, Level 15, State 1, Line 1<br />Cannot assign a default value to a local variable. </span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br />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.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br />This error message can easily be generated using the following DECLARE statement entered in either SQL Server 2000 or SQL Server 2005.</span></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; padding: 10px;"><span style="white-space: normal;"><span style="font-family: Cambria;"><span style="color: #2b00fe;">DECLARE </span>@CurrentDate <span style="color: #2b00fe;">DATETIME </span>= <span style="color: #ff00fe;">GETDATE</span>()</span></span></pre></div><div style="text-align: left;"><span style="color: red; font-family: Cambria;">Msg 139, Level 15, State 1, Line 0<br />Cannot assign a default value to a local variable.</span></div><p><span style="background-color: white;"><span style="font-family: Cambria;">SQL Server now allows the assigning of a value to a variable in the DECLARE statement.</span></span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-41287853857865695272022-08-28T21:58:00.000+05:302022-08-28T21:58:39.090+05:30Cannot use a BREAK statement outside the scope of a WHILE statement<div style="text-align: left;"><span style="font-family: Cambria;"> Msg 135, Level 16, State 1, Line 1<br /></span><div><span style="font-family: Cambria;">Cannot use a BREAK statement outside the scope of a WHILE statement.</span></div><div><span style="font-family: Cambria;"><br /></span></div><div><span style="font-family: Cambria;">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.</span></div><div><span style="font-family: Cambria;"><br />The error message occurs when using the BREAK statement outside a WHILE statement. Here are a few examples of how this error is encountered:</span></div></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; text-align: left;"><span style="font-family: Consolas;"><span style="color: #6aa84f;">-- BREAK used inside an IF condition but outside a WHILE statement</span>
<span style="color: #2b00fe;">IF NOT</span> EXISTS (<span style="color: #2b00fe;">SELECT </span>custid <span style="color: #2b00fe;">FROM </span>[dbo].[Customers]
<span style="color: #2b00fe;">WHERE </span>custname = 'Cust_1')
<span style="color: #2b00fe;">BREAK</span></span></pre><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #6aa84f;">-- Misplaced BREAK Condition</span>
<span style="color: #2b00fe;">DECLARE </span>@Counter <span style="color: #2b00fe;">INT</span>
<span style="color: #2b00fe;">DECLARE </span>@UserID <span style="color: #2b00fe;">INT</span>
<span style="color: #2b00fe;">SET </span>@Counter = 0
<span style="color: #2b00fe;">SET </span>@UserID = 1
<span style="color: #2b00fe;">WHILE </span>EXISTS (<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[UserTransaction]
<span style="color: #2b00fe;">WHERE </span>[UserID] = @UserID)
<span style="color: #2b00fe;">DELETE TOP</span> (10) <span style="color: #2b00fe;">FROM </span>[dbo].[UserTransaction]
<span style="color: #2b00fe;">WHERE </span>[UserID] = @UserID
SET @Counter = Counter + 1
IF @Counter > 10
<span style="color: #2b00fe;">BREAK</span></span></pre><div style="text-align: left;"><span style="color: red; font-family: Cambria;"><span>Msg 135, Level 15, State 1, Line 38<br /></span><span>Cannot use a BREAK statement outside the scope of a WHILE statement.</span></span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /><ul style="text-align: left;"><li><span style="background-color: white;">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.</span></li><li><span style="background-color: white;">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.</span></li><li><span style="background-color: white;">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.</span></li></ul></span></div><p><span style="font-size: 13.3333px;"><span style="background-color: white; font-family: segoe ui, tahoma;"></span></span></p><p style="font-family: "segoe ui", tahoma; font-size: 13.3333px;"><span style="background-color: white; font-size: 13.3333px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">DECLARE </span>@Counter INT
<span style="color: #2b00fe;">DECLARE </span>@UserID INT
<span style="color: #2b00fe;">SET </span>@Counter = 0
<span style="color: #2b00fe;">SET </span>@UserID = 1
WHILE EXISTS (<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[UserTransaction]
WHERE [UserID] = @UserID)
<span style="color: #2b00fe;">BEGIN</span>
<span style="color: #2b00fe;">DELETE TOP</span> (10) <span style="color: #2b00fe;">FROM </span>[dbo].[UserTransaction]
<span style="color: #2b00fe;">WHERE </span>[UserID] = @UserID
<span style="color: #2b00fe;">SET </span>@Counter = Counter + 1
<span style="color: #2b00fe;">IF </span>@Counter > 10
<span style="color: #2b00fe;">BREAK</span>
<span style="color: #2b00fe;">END</span></span></pre><p style="font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-23436259443383689892022-08-28T18:09:00.002+05:302022-08-28T18:10:38.161+05:30Cannot update a timestamp column<div style="text-align: left;"><span style="color: red; font-family: Cambria;"> Msg 272, Level 16, State 1, Line 1<br /></span><div style="text-align: left;"><span style="color: red; font-family: Cambria;">Cannot update a timestamp column.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /></span></div><div style="text-align: left;"><span style="font-family: Cambria;">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.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /></span><div><table border="0" cellpadding="0" cellspacing="0" style="color: black; width: 100%px;"><tbody></tbody></table><div><span style="font-family: Cambria;">Each database has a counter that is incremented for each insert or update operation that is performed on a table that contains a <b>timestamp</b> 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.<br /></span><span style="font-family: Cambria;">Since the <b>timestamp</b> 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.</span></div></div></div></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /></span></div><div style="text-align: left;"><span style="font-family: Cambria;"><b>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.</b></span></div><div style="text-align: left;"><span style="font-family: Cambria;"><b><br /></b></span></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE TABLE</span> [dbo].[Team] (
[TeamID] <span style="color: #2b00fe;">INT </span>NOT NULL <span style="color: #2b00fe;">PRIMARY KEY IDENTITY</span>(1, 1),
[TeamName] <span style="color: #2b00fe;">VARCHAR</span>(50),
TIMESTAMP )</span></pre></div><div style="font-family: "segoe ui", tahoma; font-size: 13.3333px; text-align: left;">Insert one record into the table.</div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">INSERT INTO</span> [dbo].[Team] ( [TeamName], [Timestamp] )
<span style="color: #2b00fe;">VALUES </span>( '<span style="color: red;">Miami Marlins</span>', <span style="color: #2b00fe;">DEFAULT </span>)</span></pre></div><div style="font-family: "segoe ui", tahoma; font-size: 13.3333px; text-align: left;"><br /></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">select * from</span> [dbo].[Team]</span></pre></div><div style="font-family: "segoe ui", tahoma; font-size: 13.3333px; text-align: left;">Output:</div><div style="text-align: left;"><br /><table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; margin-left: 4.75pt; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1184; width: 318px;">
<tbody><tr style="height: 15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td nowrap="" style="border: 1pt solid windowtext; height: 15pt; mso-border-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 46.05pt;" valign="bottom" width="61">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><b><span style="font-family: Cambria;">TeamID<o:p></o:p></span></b></p>
</td>
<td nowrap="" style="border-left: none; border: 1pt solid windowtext; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 80.95pt;" valign="bottom" width="108">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><b><span style="font-family: Cambria;">TeamName<o:p></o:p></span></b></p>
</td>
<td nowrap="" style="border-left: none; border: 1pt solid windowtext; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 111.55pt;" valign="bottom" width="149">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><b><span style="font-family: Cambria;">TIMESTAMP<o:p></o:p></span></b></p>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;">
<td nowrap="" style="border-top: none; border: 1pt solid windowtext; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 46.05pt;" valign="bottom" width="61">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><span style="font-family: Cambria;">1<o:p></o:p></span></p>
</td>
<td nowrap="" style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 80.95pt;" valign="bottom" width="108">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><span style="font-family: Cambria;">Miami
Marlins<o:p></o:p></span></p>
</td>
<td nowrap="" style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 111.55pt;" valign="bottom" width="149">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><span style="font-family: Cambria;">0x00000000000007D1<o:p></o:p></span></p>
</td>
</tr>
</tbody></table><br /></div><div style="text-align: left;"><span style="font-family: Cambria;">Let's try to update which will generate the error msg.</span></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #ff00fe;">UPDATE </span>[dbo].[Team]
<span style="color: #2b00fe;">SET </span>[Timestamp] = <span style="color: #ff00fe;">@@DBTS</span>
<span style="color: #2b00fe;">WHERE </span>[TeamName] = '<span style="color: red;">Miami Marlins</span>'</span></pre></div><div style="text-align: left;"><div><span style="color: red; font-family: Cambria;">Msg 272, Level 16, State 1, Line 11</span></div><div><span style="color: red; font-family: Cambria;">Cannot update a timestamp column.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /></span></div><div style="text-align: left;"><div><span style="font-family: Cambria;">The <b>timestamp</b> 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.</span></div><div><span style="font-family: Cambria;"><br />If there’s really a need to update the <b>timestamp</b> 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 <b>timestamp</b> column updated:</span></div></div></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #ff00fe;">UPDATE </span>[dbo].[Team]
<span style="color: #2b00fe;">SET </span>[TeamName] = [TeamName]
<span style="color: #2b00fe;">WHERE </span>[TeamName] = '<span style="color: red;">Miami Marlins</span>'</span></pre></div><div style="font-family: "segoe ui", tahoma; font-size: 13.3333px; text-align: left;"><br /></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Cambria;"><span style="color: #2b00fe;">select * from</span> [dbo].[Team]</span></pre></div><div style="font-family: "segoe ui", tahoma; font-size: 13.3333px; text-align: left;">Output:</div><div style="font-family: "segoe ui", tahoma; font-size: 13.3333px; text-align: left;"><br /></div><div style="text-align: left;"><table border="0" cellpadding="0" cellspacing="0" class="MsoNormalTable" style="border-collapse: collapse; font-family: "segoe ui", tahoma; font-size: 13.3333px; margin-left: 4.75pt; width: 318px;">
<tbody><tr style="height: 15pt; mso-yfti-firstrow: yes; mso-yfti-irow: 0;">
<td nowrap="" style="border: 1pt solid windowtext; height: 15pt; mso-border-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 46.05pt;" valign="bottom" width="61">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN; mso-hansi-font-family: Calibri;"><span style="font-family: Cambria; font-size: small;">TeamID<o:p></o:p></span></span></b></p>
</td>
<td nowrap="" style="border-left: none; border: 1pt solid windowtext; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 80.95pt;" valign="bottom" width="108">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN; mso-hansi-font-family: Calibri;"><span style="font-family: Cambria; font-size: small;">TeamName<o:p></o:p></span></span></b></p>
</td>
<td nowrap="" style="border-left: none; border: 1pt solid windowtext; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; mso-border-top-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 111.55pt;" valign="bottom" width="149">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><b><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN; mso-hansi-font-family: Calibri;"><span style="font-family: Cambria; font-size: small;">TIMESTAMP<o:p></o:p></span></span></b></p>
</td>
</tr>
<tr style="height: 15pt; mso-yfti-irow: 1; mso-yfti-lastrow: yes;">
<td nowrap="" style="border-top: none; border: 1pt solid windowtext; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-left-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 46.05pt;" valign="bottom" width="61">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN; mso-hansi-font-family: Calibri;"><span style="font-family: Cambria; font-size: small;">1<o:p></o:p></span></span></p>
</td>
<td nowrap="" style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 80.95pt;" valign="bottom" width="108">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN; mso-hansi-font-family: Calibri;"><span style="font-family: Cambria; font-size: small;">Miami
Marlins<o:p></o:p></span></span></p>
</td>
<td nowrap="" style="border-bottom: 1pt solid windowtext; border-left: none; border-right: 1pt solid windowtext; border-top: none; height: 15pt; mso-border-bottom-alt: solid windowtext .5pt; mso-border-right-alt: solid windowtext .5pt; padding: 0cm 5.4pt; width: 111.55pt;" valign="bottom" width="149">
<p align="center" class="MsoNormal" style="line-height: normal; margin-bottom: 0cm; text-align: center;"><span style="color: black; mso-ascii-font-family: Calibri; mso-bidi-font-family: "Times New Roman"; mso-fareast-font-family: "Times New Roman"; mso-fareast-language: EN-IN; mso-hansi-font-family: Calibri;"><span style="font-family: Cambria; font-size: small;">0x00000000000007D2<o:p></o:p></span></span></p>
</td>
</tr>
</tbody></table><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-23121792081796648782022-08-28T15:13:00.000+05:302022-08-28T15:13:29.322+05:30Select statements included within a function cannot return data to a client<div style="text-align: left;"><span style="font-family: Cambria;">Msg 444, Level 16, State 2, Line 1<br />Select statements included within a function cannot return data to a client.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /><span style="background-color: white;">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.</span></span></div><div style="text-align: left;"><span style="font-family: Cambria;"><span style="background-color: white;"><br /></span><span style="background-color: white;">Let's see the below example to understand more.</span></span></div><p><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE FUNCTION</span> [dbo].[GetUserInformation] ( @UserName <span style="color: #2b00fe;">VARCHAR</span>(10) )
<span style="color: #2b00fe;">RETURNS VARCHAR</span>(100)
<span style="color: #2b00fe;">AS
BEGIN</span>
<span style="color: #2b00fe;">DECLARE </span>@ErrorMessage <span style="color: #2b00fe;">VARCHAR</span>(100)
<span style="color: #2b00fe;">SET </span>@ErrorMessage = ''
<span style="color: #2b00fe;">IF EXISTS</span> (SELECT '<span style="color: red;">X</span>' <span style="color: #2b00fe;">FROM </span>[dbo].[Users] <span style="color: #2b00fe;">WHERE </span>[UserName] = @UserName)
<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[Users]
WHERE [UserName] = @UserName
<span style="color: #2b00fe;">ELSE</span>
<span style="color: #2b00fe;">SET </span>@ErrorMessage = '<span style="color: red;">Invalid User Name</span>'
<span style="color: #2b00fe;">RETURN </span>@ErrorMessage
<span style="color: #2b00fe;">END
GO</span></span></pre><div style="text-align: left;"><span style="color: red; font-family: Cambria;">Msg 444, Level 16, State 2, Procedure GetUserInformation, Line 9 [Batch Start Line 0]<br />Select statements included within a function cannot return data to a client.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br /><span style="background-color: white;">If you need to return result sets to the client, you have to use a stored procedure and not a function for this purpose.</span></span></div><p><span style="font-family: segoe ui, tahoma;"><span style="background-color: white; font-size: 13.3333px;"></span></span></p><p><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE PROCEDURE</span> [dbo].[GetUserInformation] ( @UserName <span style="color: #2b00fe;">VARCHAR</span>(10) )
AS
<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[Users]
<span style="color: #2b00fe;">WHERE </span>[UserName] = @UserName
<span style="color: #2b00fe;">GO</span></span></pre><p><span style="background-color: white;"><span style="font-family: Cambria;">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:</span></span></p><p><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE PROCEDURE</span> [dbo].[GetUserInformation] ( @UserName VARCHAR(10) )
AS
<span style="color: #2b00fe;">IF EXISTS</span> (<span style="color: #2b00fe;">SELECT </span>'<span style="color: red;">X</span>' FROM [dbo].[Users]
<span style="color: #2b00fe;">WHERE </span>[UserName] = @UserName)
<span style="color: #2b00fe;">BEGIN</span>
<span style="color: #2b00fe;">SELECT </span>'' AS [ErrorMessage]
<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[Users]
<span style="color: #2b00fe;">WHERE </span>[UserName] = @UserName
<span style="color: #2b00fe;">END
ELSE
SELECT</span> '<span style="color: red;">Invalid User Name</span>' <span style="color: #2b00fe;">AS </span>[ErrorMessage]
GO</span></pre><div style="text-align: left;"><span style="background-color: white;"><span style="font-family: Cambria;">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.</span></span></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-23051946301396824662022-08-27T21:19:00.003+05:302022-08-27T21:22:19.039+05:30The TABLESAMPLE clause can only be used with local tables<div style="text-align: left;"><span style="color: red; font-family: Cambria;">Msg 494, Level 16, State 1, Line 1<br />The TABLESAMPLE clause can only be used with local tables.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br />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.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br />If the TABLESAMPLE clause is applied to a table-valued function, such as the following SELECT statement, then this error message will be generated:</span></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE FUNCTION</span> [dbo].[ufn_GetCustomersByZIPCode] ( @ZIPCode VARCHAR(5))
<span style="color: #2b00fe;">RETURNS TABLE
AS
RETURN (SELECT * FROM</span> [dbo].[Customers]
<span style="color: #2b00fe;">WHERE </span>[ZIPCode] = @ZIPCode)
GO</span></pre><p style="font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[ufn_GetCustomersByZIPCode] A <span style="color: #2b00fe;">TABLESAMPLE </span>(100 <span style="color: #2b00fe;">ROWS</span>)</span></pre><div style="text-align: left;"><span style="font-family: Cambria;"><span>Msg 494, Level 16, State 1, Line 8<br /></span><span>The TABLESAMPLE clause can only be used with local tables.</span></span></div><div style="text-align: left;"><span style="font-family: Cambria;"><span><br /></span>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.</span></div><div style="text-align: left;"><span style="font-family: Cambria;"><br />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:</span></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">SELECT TOP</span> 100 *
<span style="color: #2b00fe;">FROM </span>[dbo].[ufn_GetCustomersByZIPCode] ( '<span style="color: red;">12345</span>' ) A
ORDER BY <span style="color: #ff00fe;">NEWID</span>()</span></pre><p style="font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-50100197494509369412022-08-27T18:42:00.001+05:302022-08-27T18:42:49.377+05:30Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses<div style="text-align: left;"><span style="color: red; font-family: Source Serif Pro;">Msg 497, Level 15, State 1, Line 1<br />Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.</span></div><div style="text-align: left;"><span style="font-family: Source Serif Pro;"><br /></span></div><div style="text-align: left;"><span><span style="background-color: white; font-family: georgia;">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:</span></span></div><div style="text-align: left;"><span style="font-family: georgia;"><span style="background-color: white;"><br /></span>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.</span></div><div style="text-align: left;"><span style="font-family: georgia;">To generate the error let's see the below example.</span></div><div style="text-align: left;"><span style="font-family: Source Serif Pro;"><br /></span><span style="font-family: segoe ui, tahoma; font-size: 13.3333px;"><b>Example</b>:</span></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE TABLE</span> [dbo].[CallDetailRecord] (
[CallDetailRecordID] INT NOT NULL <span style="color: #2b00fe;">IDENTITY</span>(1, 1),
[SourceNumber] <span style="color: #2b00fe;">VARCHAR</span>(30),
[DestinationNumber] <span style="color: #2b00fe;">VARCHAR</span>(30),
[CallTime] <span style="color: #2b00fe;">DATETIME</span>,
[CallDuration] <span style="color: #2b00fe;">INT</span>
)<br /></span></pre><p><br /></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #38761d;">-- Using a local variable for the sample number of rows</span>
<span style="color: #2b00fe;">DECLARE </span>@RowCount <span style="color: #2b00fe;">INT</span>
<span style="color: #2b00fe;">SET </span>@RowCount = 100
<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[CallDetailRecord] <span style="color: #2b00fe;">TABLESAMPLE </span>( @RowCount <span style="color: #2b00fe;">ROWS</span>)</span></pre><div style="text-align: left;"><span style="font-family: georgia;">Msg 497, Level 15, State 1, Line 12<br />Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.</span></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #6aa84f;">-- Using a local variable for sample percentage</span>
<span style="color: #2b00fe;">DECLARE </span>@PercentSample <span style="color: #2b00fe;">DECIMAL</span>(5, 2)
<span style="color: #2b00fe;">SET </span>@PercentSample = 75.0
<span style="color: #2b00fe;">SELECT * FROM</span> [dbo].[CallDetailRecord] <span style="color: #2b00fe;">TABLESAMPLE</span> ( @PercentSample <span style="color: #2b00fe;">PERCENT</span>)</span></pre><div style="text-align: left;"><span style="font-family: georgia;">Msg 497, Level 15, State 1, Line 17<br />Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.</span></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #6aa84f;">-- Using a local variable for the repeat seed</span>
<span style="color: #2b00fe;">DECLARE </span>@RepeatSeed <span style="color: #2b00fe;">INT</span>
<span style="color: #2b00fe;">SET </span>@RepeatSeed = 124
<span style="color: #2b00fe;">SELECT </span>* <span style="color: #2b00fe;">FROM </span>[dbo].[CallDetailRecord] <span style="color: #2b00fe;">TABLESAMPLE </span>( 50 PERCENT ) <span style="color: #2b00fe;">REPEATABLE </span>( @RepeatSeed )</span></pre><div style="text-align: left;"><span style="font-family: georgia;">Msg 497, Level 15, State 1, Line 22<br />Variables are not allowed in the TABLESAMPLE or REPEATABLE clauses.</span></div><div style="text-align: left;"><span style="font-family: georgia;"><br /></span></div><div style="text-align: left;"><span style="background-color: white;"><span style="font-family: georgia;"><b>Solution :</b></span></span></div><div style="text-align: left;"><span style="background-color: white;"><span style="font-family: georgia;"><b><br /></b></span></span></div><div style="text-align: left;"><span style="background-color: white;"><span style="font-family: georgia;">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:</span></span></div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">SELECT </span>* <span style="color: #2b00fe;">FROM </span>[dbo].[CallDetailRecord] <span style="color: #2b00fe;">TABLESAMPLE </span>( 100 <span style="color: #2b00fe;">ROWS </span>)
<span style="color: #2b00fe;">SELECT </span>* <span style="color: #2b00fe;">FROM </span>[dbo].[CallDetailRecord] <span style="color: #2b00fe;">TABLESAMPLE </span>( 75.0 <span style="color: #2b00fe;">PERCENT </span>)
<span style="color: #2b00fe;">SELECT </span>* <span style="color: #2b00fe;">FROM </span>[dbo].[CallDetailRecord] <span style="color: #2b00fe;">TABLESAMPLE </span>( 50 <span style="color: #2b00fe;">PERCENT </span>) <span style="color: #2b00fe;">REPEATABLE </span>( 124 )</span></pre><p><span style="font-family: georgia;">To make it dynamic it can be done in Dynamic SQL statements.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #6aa84f;">-- Using a local variable for the sample number of rows</span>
DECLARE @RowCount <span style="color: #2b00fe;">INT</span>
DECLARE @SQLStmt <span style="color: #2b00fe;">VARCHAR</span>(100)
SET @RowCount = 100
SET @SQLStmt = '<span style="color: red;">SELECT * FROM [dbo].[CallDetailRecord]
TABLESAMPLE ( </span>' + <span style="color: #ff00fe;">CAST</span>(@RowCount <span style="color: #2b00fe;">AS VARCHAR</span>(10)) + <span style="color: red;">' ROWS )'</span>
<span style="color: #2b00fe;">EXECUTE </span>( @SQLStmt )</span></pre><p><span style="font-family: georgia;">Similarly other example can be done like above.</span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-16284592644251594572022-07-09T16:02:00.001+05:302022-07-09T16:06:24.826+05:30The scale must be less than or equal to the precision<div style="text-align: left;"><span style="font-family: Cambria;"><span style="color: red;">Msg 192, Level 15, State 1, Line 1<br />The scale must be less than or equal to the precision.</span></span></div><div style="text-align: left;"><span><span style="font-family: Cambria;"><br /></span><span style="font-family: Cambria;"><span>To avoid the error below points can be followed.</span><br /></span><ul style="text-align: left;"><li><span style="font-family: Cambria;">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</span></li><li><span><span style="background-color: white; font-family: Cambria;">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.</span></span></li></ul><span style="background-color: white;"><div style="text-align: left;"><span><span style="background-color: white; font-family: Cambria;"><b>Try to find out the error:</b></span></span></div><span style="font-family: Cambria;">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:</span><br /></span><span style="background-color: white; font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; white-space: pre;"><span style="color: #2b00fe;">DECIMAL </span>( p [, s] )
<span style="color: #2b00fe;">NUMERIC </span>( p [, s] )</pre></span></span></div><div style="text-align: left;"><span><span style="background-color: white; font-family: Cambria;"><br /></span><span style="font-family: Cambria;">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.</span></span></div><div style="text-align: left;"><span><span style="font-family: Cambria;"><br /></span><span style="font-family: Cambria;">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.</span></span></div><div style="text-align: left;"><span><span style="font-family: Cambria;"><br /></span><span style="font-family: Cambria;">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.</span><br /><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">DECLARE </span>@Pi <span style="color: #2b00fe;">DECIMAL</span>(1, 6) -- 3.141592</span></pre></span></div><div style="text-align: left;"><div><span style="color: red; font-family: Cambria;">Msg 192, Level 15, State 1, Line 1</span></div><div><span style="color: red; font-family: Cambria;">The scale must be less than or equal to the precision.</span></div></div><div style="text-align: left;"><span style="font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; white-space: pre;"><span style="color: #2b00fe;">DECLARE </span>@Latitude <span style="color: #2b00fe;">DECIMAL</span>(2, 6)
<span style="color: #2b00fe;">DECLARE </span>@Longitue <span style="color: #2b00fe;">DECIMAL</span>(3, 6)</pre></span></div><div style="text-align: left;"><span><div><span style="color: red; font-family: Cambria;">Msg 192, Level 15, State 1, Line 2</span></div><div><span style="color: red; font-family: Cambria;">The scale must be less than or equal to the precision.</span></div><div><span style="color: red; font-family: Cambria;">Msg 192, Level 15, State 1, Line 3</span></div><div><span style="color: red; font-family: Cambria;">The scale must be less than or equal to the precision.</span></div><div><br /></div><span style="background-color: white; font-family: Cambria;">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:<br /></span><span style="background-color: white; font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; white-space: pre;"><span style="color: #2b00fe;">CREATE TABLE</span> [dbo].[Product] (
[ProductID] <span style="color: #2b00fe;">INT</span>,
[ProductName] <span style="color: #2b00fe;">VARCHAR</span>(100),
[Width] <span style="color: #2b00fe;">DECIMAL</span>(4, 6),
[Length] <span style="color: #2b00fe;">DECIMAL</span>(4, 6),
[Height] <span style="color: #2b00fe;">DECIMAL</span>(4, 6)
)</pre></span></span></div><div style="text-align: left;"><span><span style="background-color: white; font-family: Cambria;"><div><span style="color: red;">Msg 183, Level 15, State 1, Line 8</span></div><div><span style="color: red;">The scale (6) for column 'Width' must be within the range 0 to 4.</span></div><div><br /></div></span><span style="font-family: Cambria;"><b>To avoid this error</b>, 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.</span><br /><span style="font-family: Cambria;">Here’s an updated version of the scripts earlier that fixes the issue:</span><br /><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">DECLARE </span>@Pi <span style="color: #2b00fe;">DECIMAL</span>(7, 6) <span style="color: #38761d;">-- 3.141592</span>
<span style="color: #2b00fe;">DECLARE </span>@Latitude <span style="color: #2b00fe;">DECIMAL</span>(8, 6) <span style="color: #38761d;">–- 2 Digits to the left and 6 digits to the right.</span>
<span style="color: #2b00fe;">DECLARE </span>@Longitue <span style="color: #2b00fe;">DECIMAL</span>(9, 6) <span style="color: #38761d;">–- 3 Digits to the left and 6 digits to the right.</span></span></pre></span></div><div style="text-align: left;"><span><br /><span style="background-color: white; font-family: Cambria;">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.<br /></span><span style="background-color: white; font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; white-space: pre;"><span style="color: #2b00fe;">CREATE TABLE</span> [dbo].[Product] (
[ProductID] <span style="color: #2b00fe;">INT</span>,
[ProductName] <span style="color: #2b00fe;">VARCHAR</span>(100),
[Width] <span style="color: #2b00fe;">DECIMAL</span>(10, 6),
[Length] <span style="color: #2b00fe;">DECIMAL</span>(10, 6),
[Height] <span style="color: #2b00fe;">DECIMAL</span>(10, 6)
)</pre></span></span></div><div style="text-align: left;"><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-84070969353503810992022-07-03T16:04:00.001+05:302022-07-03T16:04:18.732+05:30The last statement included within a function must be a return statement<div style="text-align: left;"><span style="color: red; font-family: georgia;"> Msg 455, Level 16, State 2, Line 1<br />The last statement included within a function must be a return statement.</span></div><div style="text-align: left;"><span style="font-family: georgia;"><br /></span></div><table border="0" cellpadding="0" cellspacing="0" style="color: black; width: 100%px;"><tbody><tr valign="top"><td style="padding-left: 5px;"><p><span style="font-family: georgia;">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.</span></p></td></tr></tbody></table><p><span style="font-family: georgia;">To understand better, here’s a user-defined function that returns the smaller number between two integer parameters:</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">CREATE FUNCTION </span><span style="font-family: Consolas;">[dbo].[ufn_Least]</span><span style="color: #2b00fe; font-family: Consolas;"> ( @</span><span style="font-family: Consolas;">pInt1 </span><span style="color: #2b00fe; font-family: Consolas;">INT, @</span><span style="font-family: Consolas;">pInt2 </span><span style="color: #2b00fe; font-family: Consolas;">INT )
RETURNS INT
AS
BEGIN
IF @</span><span style="font-family: Consolas;">pInt1 </span><span style="color: #2b00fe; font-family: Consolas;">> @</span><span style="font-family: Consolas;">pInt2</span><span style="color: #2b00fe; font-family: Consolas;">
RETURN @</span><span style="font-family: Consolas;">pInt2</span><span style="color: #2b00fe; font-family: Consolas;">
ELSE
RETURN @</span><span style="font-family: Consolas;">pInt1</span><span style="color: #2b00fe; font-family: Consolas;">
END
GO</span></pre><div style="text-align: left;"><span style="font-family: georgia;">Output:<br /><span style="color: red;">Msg 455, Level 16, State 2, Procedure ufn_Least, Line 8 [Batch Start Line 0]<br />The last statement included within a function must be a return statement.</span></span></div><p><span style="background-color: white; font-family: georgia;">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:</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">CREATE FUNCTION </span><span style="font-family: Consolas;">[dbo].[ufn_Least]</span><span style="color: #2b00fe; font-family: Consolas;"> ( @</span><span style="font-family: Consolas;">pInt1 </span><span style="color: #2b00fe; font-family: Consolas;">INT, @</span><span style="font-family: Consolas;">pInt2 </span><span style="color: #2b00fe; font-family: Consolas;">INT )
RETURNS INT
AS
BEGIN
IF </span><span style="font-family: Consolas;">@pInt1 > @pInt2</span><span style="color: #2b00fe; font-family: Consolas;">
RETURN @</span><span style="font-family: Consolas;">pInt2</span><span style="color: #2b00fe; font-family: Consolas;">
RETURN @</span><span style="font-family: Consolas;">pInt1</span><span style="color: #2b00fe; font-family: Consolas;">
END
GO</span></pre><p><span style="background-color: white; font-family: georgia;">Instead of putting the last RETURN statement inside the ELSE statement, it is executed by itself and the function still produces the same result.</span></p>Unknownnoreply@blogger.com1tag:blogger.com,1999:blog-7291345735848920129.post-40933201234719496842022-06-19T18:46:00.004+05:302022-06-19T18:46:54.125+05:30Case expressions may only be nested to level %d<div style="text-align: left;"><span style="font-family: georgia;"><span style="color: red; font-size: small;">Msg 125, Level 15, State 1, Line 1<br /></span><span style="background-color: white; color: red;">Case expressions may only be nested to level %d.</span></span></div><div style="text-align: left;"><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;">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.</span></div><div style="text-align: left;"><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></span></div><div style="text-align: left;"><span style="background-color: white; font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; white-space: pre;"><span style="color: #2b00fe;">Declare @</span>id <span style="color: #2b00fe;">as int
set </span>@id=13<span style="color: #2b00fe;">
SELECT
(CASE @</span>Id<span style="color: #2b00fe;">
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 </span>Test<span style="color: #2b00fe;">
from </span>[SANTANA\MSSQLSERVER17].Rohit.dbo.Emp</pre></span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;"><br /></span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;">You can resolve this by using below technique.</span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;"><br /></span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;">Using multiple coalesce cases.</span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;">Using Open query.</span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;"><br /></span></div><div style="text-align: left;"><span style="background-color: white; font-family: georgia;"><b>Points to remember :</b></span></div><div style="text-align: left;"><ul style="text-align: left;"><li><span style="font-family: georgia;"><span style="background-color: white;">The above query will work absolutely fine if you run from the local instances even more than 10 conditions.</span><span style="background-color: white;">this error message only happens when we are applying a case from data that comes from a Linked Server.</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">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.</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">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.</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">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.)</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">You can only use up to 9 conditions in each CASE in scenarios where the query is done on remote data.</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">Using ELSE NULL at the end of each case is optional.</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">Subquery and CTE do not resolve this issue.</span></span></li><li><span style="background-color: white; font-style: inherit; font-variant-caps: inherit; font-variant-ligatures: inherit; font-weight: inherit;"><span style="font-family: georgia;">OPENQUERY and OPENROWSET do not have this limitation of 10 CASE conditions either.</span></span></li></ul></div><div style="text-align: left;"><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></span></div><div style="text-align: left;"><span style="background-color: white; font-family: "segoe ui", tahoma; font-size: 13.3333px;"><br /></span></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-35120650757710483752022-06-18T18:10:00.005+05:302022-06-18T18:10:47.747+05:30The select list for the INSERT statement contains more items than the insert list<p> <span style="color: red; font-family: verdana; font-size: small;">Msg 121, Level 15, State 1, Line 1</span></p><div><span style="font-family: verdana;"><span style="color: red; font-size: x-small;">The select list for the INSERT statement contains more items than the insert list. <br />The number of SELECT values must match the number of INSERT columns.</span></span></div><div><span><span style="color: red; font-family: verdana; font-size: x-small;"><br /></span><span style="font-family: georgia;">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.<br />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.</span></span></div><div><span style="font-family: georgia;"><br />This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.</span></div><p><span style="font-family: georgia;">Here is the example of the code that cause the error.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">INSERT INTO </span><span style="font-family: Consolas;">OrdersLatest (
OrderId,OrderDate
)</span><span style="color: #2b00fe; font-family: Consolas;">
SELECT
</span><span style="font-family: Consolas;"> OrderId,OrderDate,OrderDesc</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">OrdersMarch</span><span style="color: #2b00fe; font-family: Consolas;">;</span></pre><p>Output:</p><div><span style="color: red; font-family: verdana; font-size: x-small;"> Msg 121, Level 15, State 1, Line 1<br />The select list for the INSERT statement contains more items than the insert list. <br />The number of SELECT values must match the number of INSERT columns.</span></div><p><span style="font-family: verdana;">You can fix this by correcting it.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">INSERT INTO </span><span style="font-family: Consolas;">OrdersLatest (
OrderId,OrderDate
)</span><span style="color: #2b00fe; font-family: Consolas;">
SELECT
</span><span style="font-family: Consolas;"> OrderId,OrderDate</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">OrdersMarch</span><span style="color: #2b00fe; font-family: Consolas;">;</span></pre><p><span style="font-family: georgia;">You can use less columns <span style="background-color: white;">this would depend on whether or not we have any NOT NULL </span><span style="background-color: white;">constraints on the destination table.</span></span></p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-90896775883409679002022-06-18T17:56:00.002+05:302022-06-18T17:59:44.995+05:30The select list for the INSERT statement contains fewer items than the insert list<div style="text-align: left;"><span style="font-family: verdana;"><span style="color: red; font-size: x-small;"> Msg 120, Level 15, State 1, Line 1<br />The select list for the INSERT statement contains fewer items than the insert list. <br />The number of SELECT values must match the number of INSERT columns.</span></span></div><div style="text-align: left;"><span><span style="color: red; font-family: verdana; font-size: x-small;"><br /></span><span style="font-family: georgia;">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.<br />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.</span></span></div><div style="text-align: left;"><span style="font-family: georgia;"><br />This is easy to fix. Simply make sure the number of columns match between your INSERT and SELECT list.</span></div><p><span style="font-family: georgia;">Here is the example of the code that cause the error.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">INSERT INTO </span><span style="font-family: Consolas;">OrdersLatest (
OrderId,OrderDate,OrderDesc
)</span><span style="color: #2b00fe; font-family: Consolas;">
SELECT
</span><span style="font-family: Consolas;"> OrderId,OrderDate</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">OrdersMarch</span><span style="color: #2b00fe; font-family: Consolas;">;</span></pre><p>Output:</p><div style="text-align: left;"><span style="color: red; font-family: verdana; font-size: x-small;"> Msg 120, Level 15, State 1, Line 1<br />The select list for the INSERT statement contains fewer items than the insert list. <br />The number of SELECT values must match the number of INSERT columns.</span></div><p><span style="font-family: verdana;">You can fix this by correcting it.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">INSERT INTO </span><span style="font-family: Consolas;">OrdersLatest (
OrderId,OrderDate,OrderDesc
)</span><span style="color: #2b00fe; font-family: Consolas;">
SELECT
</span><span style="font-family: Consolas;"> OrderId,OrderDate,OrderDesc</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">OrdersMarch</span><span style="color: #2b00fe; font-family: Consolas;">;</span></pre><p><span style="font-family: verdana;">You can use less columns <span style="background-color: white;">this would depend on whether or not we have any NOT NULL </span><span style="background-color: white;">constraints on the destination table.</span></span></p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-41132220281043717752022-06-16T23:12:00.001+05:302022-06-16T23:12:29.276+05:30SQL Error 119 - Must pass parameter number 2 and subsequent parameters<p><span style="color: red; font-family: arial;"> Msg 119, Level 15, State 1, Line 1</span></p><p><span style="color: red; font-family: arial;">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'.</span></p><p><span style="color: red; font-family: arial;"><span style="background-color: white; color: black; text-align: justify;">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.</span></span></p><div><span style="font-family: arial;">Suppose we have created a simple stored procedure uspDisplayData in SQL Server</span></div><div class="MsoNormal" style="background-color: white; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><br /></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; text-align: left;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">CREATE PROCEDURE </span>uspDisplayData<span style="color: #2b00fe;">(
@</span>Param1 <span style="color: #2b00fe;">AS VARCHAR(100),
@</span>Param2 <span style="color: #2b00fe;">AS INT
)
AS
SELECT @</span>Param1<span style="color: #2b00fe;">, @</span>Param2</span></pre></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt;"><br /></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span><span style="font-family: arial;">Now if we will execute this stored procedure:</span><span face="Verdana, Geneva, sans-serif"><o:p style="font-size: 14pt;"></o:p></span></span></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-family: "Courier New"; font-size: 14pt;"><br /></span></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; text-align: left; white-space: pre;"><span style="color: #2b00fe;">EXECUTE </span>dbo.uspDisplayData @Param1<span style="color: #2b00fe;"> = '</span><span style="color: red;">Sql Server</span><span style="color: #2b00fe;">',2</span></pre></span></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><br /></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-family: "Courier New"; font-size: 14pt;">We will get error message like:</span></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><br /></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span><span style="color: red; font-family: arial;">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'.</span><span face="Verdana, Geneva, sans-serif" style="font-size: 14pt; font-weight: bold;"><o:p></o:p></span></span></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><br /></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-family: arial;">Solution:</span></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-family: arial;"><br /></span></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span><span style="font-family: arial;">Correct ways to pass the parameters are:</span><span face="Verdana, Geneva, sans-serif"><o:p style="font-size: 14pt;"></o:p></span></span></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-family: Consolas;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; text-align: left; white-space: pre;"><span style="color: #2b00fe;">EXECUTE </span>dbo.uspDisplayData<span style="color: #2b00fe;"> '</span><span style="color: red;">Sql Server</span><span style="color: #2b00fe;">',2</span></pre></span></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt; text-align: justify;"><span style="font-size: 14.85px;">OR</span></div><div class="MsoNormal" style="background-color: white; margin-bottom: 0.0001pt; text-align: justify;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; text-align: left;"><span style="color: #2b00fe; font-family: Consolas;">EXECUTE </span><span style="font-family: Consolas;">dbo.uspDisplayData @Param1</span><span style="color: #2b00fe; font-family: Consolas;"> = '</span><span style="font-family: Consolas;">Sql Server</span><span style="color: #2b00fe; font-family: Consolas;">',</span><span style="font-family: Consolas;">@Param</span><span style="color: #2b00fe; font-family: Consolas;">2 = </span><span style="font-family: Consolas;">2</span></pre></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt;"><br /></div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt;">This will result correct result and execute without error. </div><div class="MsoNormal" style="background-color: white; font-family: Verdana, Geneva, sans-serif; font-size: 14.85px; margin-bottom: 0.0001pt;"><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-37661171334118953872022-06-05T18:02:00.003+05:302022-06-05T18:02:55.460+05:30The object name 'Object Name' contains more than the maximum number of prefixes. The maximum is 2<div style="text-align: left;"><span style="color: red; font-family: arial;">Msg 117, Level 15, State 2, Line 4<br />The object name 'Object Name' contains more than the maximum number of prefixes. The maximum is 2.</span></div><p><span style="font-family: arial;">Lets see the below example. Trying Select the records.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">select * from </span><span style="font-family: Consolas;">[SANTANA\MSSQLSERVER17].Rohit.dbo.Employee.<b>Employeeid</b><span style="color: #2b00fe;">
where </span>EmployeeID=101</span></pre><p><b>Output</b>:</p><div style="text-align: left;"><span style="color: red; font-family: arial;">Msg 117, Level 15, State 1, Line 2<br />The object name 'SANTANA\MSSQLSERVER17.Rohit.dbo.Employee.Employeeid' contains more than the maximum number of prefixes. The maximum is 3.</span></div><div style="text-align: left;"><span style="color: red;"><br /></span></div><div style="text-align: left;"><span style="font-family: arial;">In the above example Employeeid is an invalid selection results in the error.</span></div><div style="text-align: left;"><span style="font-family: arial;"><br /><span style="background-color: white; font-size: 14.5116px;">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.<br /></span><span style="background-color: white; font-size: 14.5116px;">The trick is to create the table on the remote server, then perform an INSERT INTO.</span></span></div><p><br /></p><p><br /></p><p> </p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-50896322649676800452022-06-05T17:23:00.001+05:302022-06-05T17:23:33.107+05:30Only one expression can be specified in the select list when the subquery is not introduced with EXISTS<div style="text-align: left;"><span style="color: red;">Msg 116, Level 16, State 1, Line 1<br />Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.</span></div><p><span face="Helvetica, sans-serif" style="background-color: white; font-family: arial;">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.</span></p><p><span face="Helvetica, sans-serif" style="background-color: white;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">Select </span>EmployeeID,FirstName,LastName
<span style="color: #2b00fe;">from </span>Employee
<span style="color: #2b00fe;">where </span>EmployeeID IN (<span style="color: #2b00fe;">Select </span>EmployeeID,FirstName <span style="color: #2b00fe;">from </span>Employee)</span></pre><p><span face="Helvetica, sans-serif" style="background-color: white;">Output:</span></p><div style="text-align: left;"><span style="color: red;">Msg 116, Level 16, State 1, Line 1<br />Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.</span></div><p><span face="Helvetica, sans-serif" style="background-color: white;"></span></p><p><span style="font-family: arial;">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:</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">Select </span><span style="font-family: Consolas;">EmployeeID,FirstName,LastName</span><span style="color: #6aa84f; font-family: Consolas;">
</span><span style="color: #2b00fe; font-family: Consolas;">from </span><span style="font-family: Consolas;">Employee</span><span style="color: #6aa84f; font-family: Consolas;">
</span><span style="color: #2b00fe; font-family: Consolas;">where </span><span style="font-family: Consolas;">EmployeeID IN (<span style="color: #2b00fe;">Select </span>EmployeeID <span style="color: #2b00fe;">from </span>Employee)</span></pre><p><span style="font-family: arial;">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.</span></p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-79267935288068897912022-06-04T22:05:00.000+05:302022-06-04T22:05:03.647+05:30Browse mode is invalid for a statement that assigns values to a variable<p><span style="font-family: arial;"> SQL Error 114 : <span style="background-color: white;">Browse mode is invalid for a statement that assigns values to a variable.</span></span></p><p><span style="color: #333333; font-family: arial;">NetBackup VMware backups using SQL Server application protection fails showing unimplemented error code 114(114)</span></p><p><span style="color: #333333; font-family: arial;">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.</span></p><p><span style="color: #333333; font-family: arial;"><br /></span></p><p><span style="color: #333333; font-family: arial;">I will share more info if I have...</span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-72951553152907155022022-06-04T21:18:00.004+05:302022-06-04T21:41:31.623+05:30SQL Error 113 - Missing end comment mark<div style="text-align: left;"><span style="color: red;"> Msg 113, Level 15, State 1, Line 1<br />Missing end comment mark '*/'.</span></div><div style="text-align: left;"><br /></div><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;">SQL Server error message 113 occurs when you omit a closing comment mark.</span></p><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;">This can occur when you open a comment but forget to close it. It can also occur when you accidentally type an opening comment.</span></p><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;">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.</span></p><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;">The error can be generated below 2 ways.</span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #6aa84f; font-family: Consolas;">/*
Select * from Employee
Go
/*
--Select * from Employee /*
Go
*/</span></pre><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;">Output:</span></p><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="color: red; font-family: arial;"><span style="font-family: "Times New Roman";"> Msg 113, Level 15, State 1, Line 1</span><br style="font-family: "Times New Roman";" /><span style="font-family: "Times New Roman";">Missing end comment mark '*/'.</span></span></p><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;">To avoid this error make sure there are same number of opening and closing tags</span></p><p style="background-color: white; box-sizing: border-box; margin-bottom: 1rem; margin-top: 0px;"><span style="font-family: arial;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #38761d; font-family: Consolas;">/*
Select * from Employee
Go */
/*
--Select * from Employee /*
Go
*/*/</span></pre>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-37400531679941538072022-06-04T15:57:00.005+05:302022-06-04T20:52:52.827+05:30'CREATE FUNCTION' must be the first statement in a query batch<div style="text-align: left;"><span style="color: red;"> Msg 111, Level 15, State 1, Line 3<br />'CREATE FUNCTION' must be the first statement in a query batch.</span></div><div style="text-align: left;"><br /><span style="font-family: arial;"><span face="poppins, sans-serif" style="background-color: white;">When creating the User Defined function, if the CREATE FUNCTION is not the </span>first statement <span face="poppins, sans-serif" style="background-color: white;">in the query, you will receive this error<br /></span><span face="Helvetica, sans-serif" style="background-color: white;">There should be no other statements before the CREATION FUNCTION statement that would make it not the first statement in a query batch</span></span></div><div style="text-align: left;"><span style="font-family: arial;"><span face="Helvetica, sans-serif" style="background-color: white;"><br /></span><span face="Helvetica, sans-serif" style="background-color: white;">Below is the SQL Script that will lead to the error.</span></span></div><p><span face="Helvetica, sans-serif" style="background-color: white;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">Select * from </span><span style="font-family: Consolas;">Employee</span><span style="color: #2b00fe; font-family: Consolas;">
Create FUNCTION </span><span style="font-family: Consolas;">[dbo].[Calculate_Age]</span><span style="color: #2b00fe; font-family: Consolas;">
(
@</span><span style="font-family: Consolas;">DOB </span><span style="color: #2b00fe; font-family: Consolas;">datetime , @</span><span style="font-family: Consolas;">calcDate </span><span style="color: #2b00fe; font-family: Consolas;">datetime
)
RETURNS int
AS
BEGIN
declare @</span><span style="font-family: Consolas;">age </span><span style="color: #2b00fe; font-family: Consolas;">int
IF (@</span><span style="font-family: Consolas;">calcDate </span><span style="color: #2b00fe; font-family: Consolas;">< @</span><span style="font-family: Consolas;">DOB </span><span style="color: #2b00fe; font-family: Consolas;">)
RETURN -1
-- </span><span style="color: #38761d; font-family: Consolas;">If a DOB is supplied after the comparison date, then return -1</span><span style="color: #2b00fe; font-family: Consolas;">
SELECT @</span><span style="font-family: Consolas;">age </span><span style="color: #2b00fe; font-family: Consolas;">= YEAR(@</span><span style="font-family: Consolas;">calcDate</span><span style="color: #2b00fe; font-family: Consolas;">) - YEAR(@</span><span style="font-family: Consolas;">DOB</span><span style="color: #2b00fe; font-family: Consolas;">) +
CASE WHEN </span><span style="color: #ff00fe; font-family: Consolas;">DATEADD</span><span style="color: #2b00fe; font-family: Consolas;">(</span><span style="color: #ff00fe; font-family: Consolas;">year</span><span style="color: #2b00fe; font-family: Consolas;">,</span><span style="color: #ff00fe; font-family: Consolas;">YEAR</span><span style="color: #2b00fe; font-family: Consolas;">(@</span><span style="font-family: Consolas;">calcDate</span><span style="color: #2b00fe; font-family: Consolas;">) - </span><span style="color: #ff00fe; font-family: Consolas;">YEAR</span><span style="color: #2b00fe; font-family: Consolas;">(@</span><span style="font-family: Consolas;">DOB</span><span style="color: #2b00fe; font-family: Consolas;">)
,@</span><span style="font-family: Consolas;">DOB</span><span style="color: #2b00fe; font-family: Consolas;">) > @</span><span style="font-family: Consolas;">calcDate </span><span style="color: #2b00fe; font-family: Consolas;">THEN -1 ELSE 0 END
RETURN @</span><span style="font-family: Consolas;">age</span><span style="color: #2b00fe; font-family: Consolas;">
END</span></pre><div style="text-align: left;"><span face="Helvetica, sans-serif" style="background-color: white;">Output:<br /></span><span style="color: red;">Msg 111, Level 15, State 1, Line 3<br />'CREATE FUNCTION' must be the first statement in a query batch.</span></div><p><span face="Helvetica, sans-serif" style="background-color: white;"></span></p><p><span style="background-color: white; font-family: arial;"><b>To avoid this error</b>, 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.</span></p><p><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">Select * from </span><span style="font-family: Consolas;">Employee</span><span style="color: #2b00fe; font-family: Consolas;">
Go
Create FUNCTION </span><span style="font-family: Consolas;">[dbo].[Calculate_Age]</span><span style="color: #2b00fe; font-family: Consolas;">
(
@</span><span style="font-family: Consolas;">DOB </span><span style="color: #2b00fe; font-family: Consolas;">datetime , @</span><span style="font-family: Consolas;">calcDate </span><span style="color: #2b00fe; font-family: Consolas;">datetime
)
RETURNS int
AS
BEGIN
declare @</span><span style="font-family: Consolas;">age </span><span style="color: #2b00fe; font-family: Consolas;">int
IF (@</span><span style="font-family: Consolas;">calcDate </span><span style="color: #2b00fe; font-family: Consolas;">< @</span><span style="font-family: Consolas;">DOB </span><span style="color: #2b00fe; font-family: Consolas;">)
RETURN -1
-- </span><span style="color: #6aa84f; font-family: Consolas;">If a DOB is supplied after the comparison date, then return -1</span><span style="color: #2b00fe; font-family: Consolas;">
SELECT @age = </span><span style="color: #ff00fe; font-family: Consolas;">YEAR</span><span style="color: #2b00fe; font-family: Consolas;">(@</span><span style="font-family: Consolas;">calcDate</span><span style="color: #2b00fe; font-family: Consolas;">) - YEAR(@</span><span style="font-family: Consolas;">DOB</span><span style="color: #2b00fe; font-family: Consolas;">) +
CASE WHEN </span><span style="color: #ff00fe; font-family: Consolas;">DATEADD</span><span style="color: #2b00fe; font-family: Consolas;">(</span><span style="color: #ff00fe; font-family: Consolas;">year</span><span style="color: #2b00fe; font-family: Consolas;">,</span><span style="color: #ff00fe; font-family: Consolas;">YEAR</span><span style="color: #2b00fe; font-family: Consolas;">(@</span><span style="font-family: Consolas;">calcDate</span><span style="color: #2b00fe; font-family: Consolas;">) - </span><span style="color: #ff00fe; font-family: Consolas;">YEAR</span><span style="color: #2b00fe; font-family: Consolas;">(@</span><span style="font-family: Consolas;">DOB</span><span style="color: #2b00fe; font-family: Consolas;">)
,@</span><span style="font-family: Consolas;">DOB</span><span style="color: #2b00fe; font-family: Consolas;">) > @</span><span style="font-family: Consolas;">calcDate </span><span style="color: #2b00fe; font-family: Consolas;">THEN -1 ELSE 0 END
RETURN @</span><span style="font-family: Consolas;">age</span><span style="color: #2b00fe; font-family: Consolas;">
END</span>
</pre><div><br /></div><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-54780267363753189742022-06-02T18:35:00.000+05:302022-06-02T18:35:03.935+05:30SQL 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.<div style="text-align: left;"><span style="color: red; font-family: arial;"> Msg 110, Level 15, State 1, Line 7<br />There are <b>fewer </b>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.</span></div><div style="text-align: left;"><span style="font-family: arial;"><br />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.</span></div><p><b>Example:</b></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">Create Table </span><span style="font-family: Consolas;">Students </span><span style="color: #2b00fe; font-family: Consolas;">
(
</span><span style="font-family: Consolas;">Id </span><span style="color: #2b00fe; font-family: Consolas;">int,
</span><span style="font-family: Consolas;">FirstName </span><span style="color: #2b00fe; font-family: Consolas;">Varchar(50),
</span><span style="font-family: Consolas;">LastName </span><span style="color: #2b00fe; font-family: Consolas;">Varchar(50)
)
INSERT INTO </span><span style="font-family: Consolas;">Students (Id,FirstName)</span><span style="color: #2b00fe; font-family: Consolas;">
VALUES (1,'</span><span style="color: red; font-family: Consolas;">Rohit</span><span style="color: #2b00fe; font-family: Consolas;">','</span><span style="color: red; font-family: Consolas;">Kumar</span><span style="color: #2b00fe; font-family: Consolas;">')</span></pre><div style="text-align: left;"><b>Output: </b></div><div style="text-align: left;"><br /><span style="color: red; font-family: arial;"> Msg 110, Level 15, State 1, Line 7<br />There are <b>fewer </b>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.</span></div><div style="text-align: left;"><span style="font-family: arial;"><br />This can be resolved by the number of values in the VALUES clause must match the number of columns specified in the INSERT statement.</span></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-79501635366266672462022-06-02T18:24:00.000+05:302022-06-02T18:24:04.631+05:30SQL 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<div style="text-align: left;"><span style="color: red; font-family: arial;"> Msg 109, Level 15, State 1, Line 7<br />There are <b>more </b>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.</span></div><div style="text-align: left;"><span style="font-family: arial;"><br /><span face="poppins, sans-serif" style="background-color: white; font-size: 16px;">You will see this error message when you are doing an INSERT operation to a table using INSERT TO.. </span></span><span style="font-family: arial;">There are </span><b style="font-family: arial;">more </b><span style="font-family: arial;">columns in the INSERT statement than values specified in the VALUES clause</span></div><div style="text-align: left;"><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><br /></span><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><b>Example</b>:</span></div><p><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="color: #2b00fe; font-family: Consolas;">Create Table </span><span style="font-family: Consolas;">Students </span><span style="color: #2b00fe; font-family: Consolas;">
(
</span><span style="font-family: Consolas;">Id </span><span style="color: #2b00fe; font-family: Consolas;">int,
</span><span style="font-family: Consolas;">FirstName </span><span style="color: #2b00fe; font-family: Consolas;">Varchar(50),
</span><span style="font-family: Consolas;">LastName </span><span style="color: #2b00fe; font-family: Consolas;">Varchar(50)
)
INSERT INTO </span><span style="font-family: Consolas;">Students (Id,FirstName,LastName)</span><span style="color: #2b00fe; font-family: Consolas;">
VALUES (1,'</span><span style="color: red; font-family: Consolas;">Rohit</span><span style="color: #2b00fe; font-family: Consolas;">')</span></pre><div style="text-align: left;"><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;">Output:</span></div><div style="text-align: left;"><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><br /></span><span style="color: red; font-family: arial;">Msg 109, Level 15, State 1, Line 7<br />There are <b>more </b>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.</span></div><p><span face="poppins, sans-serif" style="background-color: white; color: #333333;"></span></p><p><span face="poppins, sans-serif" style="background-color: white; font-family: arial; font-size: 16px;">The above error msg is also self descrptive.</span></p><p><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><br /></span></p><p><br /></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-69668211728220735592022-06-02T16:02:00.001+05:302022-06-02T16:02:45.578+05:30The ORDER BY position number is out of range of the number of items in the select list<div style="text-align: left;"><span style="color: red; font-family: arial;">Msg 108, Level 15, State 1, Line 1<br />The ORDER BY position number is out of range of the number of items in the select list. </span></div><div style="text-align: left;"><span style="font-family: arial;"><br /><span face="Helvetica, sans-serif" style="background-color: white;">This error occurs when specifying the column index or position number in an ORDER BY clause in your SELECT statement and the column index or position number is either 0 or higher than the number of columns specified in the SELECT clause.</span></span></div><div style="text-align: left;"><span style="font-family: arial;"><span face="Helvetica, sans-serif" style="background-color: white;"><br /></span><span face="poppins, sans-serif" style="background-color: white;">This error usually happens when you are using the index in the ORDER BY clause instead of the column name and you have either used 0 or the position that is higher than the number of columns specified in the SELECT clause.</span></span></div><div style="text-align: left;"><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><br /></span></div><p style="text-align: left;"><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px; text-align: left;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">Use </span>AdventureWorks2017
<span style="color: #2b00fe;">Go</span>
<span style="color: #2b00fe;">Select </span>FirstName,MiddleName,LastName
<span style="color: #2b00fe;">from </span>Person.Person
<span style="color: #2b00fe;">Order By</span> 4</span></pre><p><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><b>OutPut</b>:</span></p><div style="text-align: left;"><span style="color: red;">Msg 108, Level 16, State 1, Line 3<br />The ORDER BY position number 4 is out of range of the number of items in the select list.</span></div><p><span face="poppins, sans-serif" style="background-color: white; color: #333333;"></span></p><div style="-webkit-font-smoothing: antialiased; background-color: white; border: 0px; box-sizing: border-box; font-size: 16px; line-height: inherit; margin: 0px 0px 2rem; max-width: 100%; outline: 0px; overflow-wrap: break-word; padding: 0px; text-align: left; text-rendering: optimizelegibility; vertical-align: baseline;"><span style="font-family: arial;">To avoid this, error make sure that the position number or the column index specified in the ORDER by clause is with-in the range of items in the SELECT clause.</span></div><div style="-webkit-font-smoothing: antialiased; background-color: white; border: 0px; box-sizing: border-box; font-size: 16px; line-height: inherit; margin: 0px 0px 2rem; max-width: 100%; outline: 0px; overflow-wrap: break-word; padding: 0px; text-align: left; text-rendering: optimizelegibility; vertical-align: baseline;"><span style="font-family: arial;">The best practice is to use the column name instead of the index as shown below.</span></div><p><span face="poppins, sans-serif" style="background-color: white; font-size: 16px;"></span></p><p><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"></span></p><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;"><span style="color: #2b00fe;">Use </span>AdventureWorks2017
<span style="color: #2b00fe;">Go</span>
<span style="color: #2b00fe;">Select </span>FirstName,MiddleName,LastName
<span style="color: #2b00fe;">from </span>Person.Person
<span style="color: #2b00fe;">Order By</span> FirstName</span></pre><p><span face="poppins, sans-serif" style="background-color: white; font-size: 16px;"><span face="poppins, sans-serif" style="font-family: arial;">One of the advantages of specifying the column name instead of the index is that even you add new columns in to the SELECT clause, you will have the desired result with-out any errors. Using index for sorting will result in undesired results when you add new columns in to your SELECT clause.</span></span></p><p><span face="poppins, sans-serif" style="background-color: white; color: #333333; font-size: 16px;"><br /></span></p><p><span face="Helvetica, sans-serif" style="background-color: white;"><br /></span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-31078828474543037182022-05-29T19:52:00.000+05:302022-05-29T19:52:05.351+05:30The column prefix does not match with a table name or alias name used in the query<div style="text-align: left;"><span style="color: red; font-family: arial;">Msg 107, Level 16, State 3, Line 1</span></div><div style="text-align: left;"><span style="color: red; font-family: arial;">The column prefix does not match with a table name or alias name used in the query.</span></div><div style="text-align: left;"><span style="font-family: arial;"><br /></span></div><div style="text-align: left;"><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="font-family: arial;"><span style="background: white; line-height: 115%;">The actual Error message
varies with the Alias being used. The </span><span style="background: white; border: 1pt none windowtext; line-height: 115%; padding: 0cm;">usual message</span><span style="background: white; line-height: 115%;"> is
as shown below.</span></span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="background: white; color: #333333; line-height: 115%;"><span style="font-family: arial;"><br /></span></span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="background: white; line-height: 115%;"><span style="color: red; font-family: arial;">The column prefix '%.*ls' does not match with a table name or alias name used in the query.</span></span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="font-family: arial;"><span style="background: white; color: #333333; line-height: 115%;"><br /></span><span style="background: white; line-height: 115%;">The % in the above can be
replaced with the alias that was used and caused the issue.</span></span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="font-family: arial;"><span style="background: white; line-height: 115%;"><br /></span><span>For example, below
are 2 scenarios that will lead to the <b>SQL </b></span><b><span>Error msg 107</span></b><span>.</span></span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="font-family: arial;"><br /><ul style="text-align: left;"><li><span>When the Column Prefix doesnot correspond to
the table or alias name that is used in the SQL query. The below query
leads to the error.</span><span> </span></li></ul></span><div><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;">Use AdventureWorks2017
Go
<span style="color: #2b00fe;">SELECT </span>P.*
<span style="color: #2b00fe;">FROM </span>Person.Person
<span style="color: #2b00fe;">where </span>FirstName='<span style="color: red;">Adam</span>'</span></pre></div><span style="background: white; line-height: 115%;"><div style="background: white; color: #333333; font-family: Arial, "sans-serif"; font-size: 10pt; line-height: normal; text-align: left; vertical-align: baseline;"><span face=""Arial","sans-serif"" style="background: white; color: #333333; font-size: 10pt; line-height: 115%;"><br /></span></div><div style="background: white; color: #333333; line-height: normal; text-align: left; vertical-align: baseline;"><span style="background: white; color: #333333; line-height: 115%;"><span style="font-family: arial;"><b>Output</b>:</span></span></div><div style="background: white; color: #333333; line-height: normal; text-align: left; vertical-align: baseline;"><span style="background: white; color: #333333; line-height: 115%;"><span style="font-family: arial;"><br /></span></span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><span style="background: white; line-height: 115%;"><span style="font-family: arial;"><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; line-height: normal; vertical-align: baseline;"><span style="color: red;">Msg 107, Level 15, State 1, Line 7</span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; line-height: normal; vertical-align: baseline;"><span style="color: red;">The column prefix 'P' does not match with a table name or alias name used in the query.</span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; line-height: normal; vertical-align: baseline;"><br /></div></span></span></div><span style="font-family: arial;"><span style="color: #333333;">In this query, the alias
</span><b style="color: #333333;">P</b><span style="color: #333333;"> cannot be identified and will result</span></span></span><span style="font-family: arial;"> above error</span></div><div style="background: white; line-height: normal; text-align: left; vertical-align: baseline;"><ul style="text-align: left;"><li><span style="color: #333333;"><span style="font-family: arial;">The table name is specified as a column prefix where as the alias name is supplied in the FROM clause. </span></span></li></ul></div><p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p></o:p></p>
<p class="MsoNormal"><o:p></o:p></p></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;">Use AdventureWorks2017
Go
<span style="color: #2b00fe;">SELECT </span>Person.*
<span style="color: #2b00fe;">FROM </span>Person.Person <span style="color: #2b00fe;">As </span>PR
<span style="color: #2b00fe;">where </span>FirstName='<span style="color: red;">Adam</span>'</span></pre></div><div style="text-align: left;"><div style="background: white; color: #333333; font-family: Arial, "sans-serif"; font-size: 13.3333px; line-height: normal; vertical-align: baseline;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; font-size: 10pt; line-height: 15.3333px;"><b>Output</b>:</span></div><div style="background: white; color: #333333; font-family: Arial, "sans-serif"; font-size: 13.3333px; line-height: normal; vertical-align: baseline;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; font-size: 10pt; line-height: 15.3333px;"><br /></span></div><div style="background: white; line-height: normal; vertical-align: baseline;"><span style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; line-height: 15.3333px;"><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; line-height: normal; vertical-align: baseline;"><span style="color: red; font-family: arial;">Msg 107, Level 15, State 1, Line 7</span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; line-height: normal; vertical-align: baseline;"><span style="color: red; font-family: arial;">The column prefix 'Person' does not match with a table name or alias name used in the query.</span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; font-family: Arial, "sans-serif"; font-size: 10pt; line-height: normal; vertical-align: baseline;"><br /></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; font-family: Arial, "sans-serif"; line-height: normal; vertical-align: baseline;"><span face="Helvetica, sans-serif" style="color: black;"><b>To avoid this error</b>, always make sure that the table name used as a prefix of a column in a query exists in the FROM clause.</span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; font-family: Arial, "sans-serif"; font-size: 10pt; line-height: normal; vertical-align: baseline;"><span face="Helvetica, sans-serif" style="color: black; font-size: medium;"><br /></span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; line-height: normal; vertical-align: baseline;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;">Use AdventureWorks2017
Go
<span style="color: #2b00fe;">SELECT </span>Person.*
<span style="color: #2b00fe;">FROM </span>Person.Person
<span style="color: #2b00fe;">where </span>FirstName='<span style="color: red;">Adam</span>'</span></pre></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; font-family: Arial, "sans-serif"; font-size: 10pt; line-height: normal; vertical-align: baseline;"><span face="Helvetica, sans-serif" style="color: black; font-size: medium;"><br /></span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; line-height: normal; vertical-align: baseline;"><span face="Helvetica, sans-serif" style="color: black; font-family: arial;">Also, to avoid the second cause of this error, once you assign an alias to a table in the FROM clause, make sure to use that alias in a column prefix and not the original table name:</span></div><div style="background-attachment: initial; background-clip: initial; background-image: initial; background-origin: initial; background-position: initial; background-repeat: initial; background-size: initial; color: #333333; font-family: Arial, "sans-serif"; font-size: 10pt; line-height: normal; vertical-align: baseline;"><br /></div></span></div></div><div style="text-align: left;"><pre class="sqlscript" style="background-color: #f7f7e6; border-style: dashed; border-width: 1px; box-shadow: rgba(0, 0, 0, 0.2) 0px 8px 16px 0px; overflow-x: auto; padding: 10px;"><span style="font-family: Consolas;">Use AdventureWorks2017
Go
<span style="color: #2b00fe;">SELECT </span>PR.*
<span style="color: #2b00fe;">FROM </span>Person.Person <span style="color: #2b00fe;">As </span>PR
<span style="color: #2b00fe;">where </span>FirstName='<span style="color: red;">Adam</span>'</span></pre></div><div style="text-align: left;"><br /></div>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-26949711038302732962022-05-29T16:00:00.003+05:302022-05-29T16:00:59.927+05:30Too many table names in the query. The maximum allowable is %d<p><span style="font-family: arial;"> While using SQL Server 2005, You might have exceeded maximum allowed tables.</span></p><p><span style="font-family: arial;"><span style="background-color: white; color: #333333;">This time the error message was </span><span style="background-color: white; margin: 0px; padding: 0px;"><span style="color: red;">Too many table names in the query. The maximum allowable is 256</span></span><span style="background-color: white; color: #333333; margin: 0px; padding: 0px;">.</span></span></p><p><span style="background-color: white; color: #333333; margin: 0px; padding: 0px;"><span style="font-family: arial;">Unfortunately, it’s not very feasible to reduce the number of tables being used as that number is dependent on the query.</span></span></p><p><span style="color: #333333; font-family: arial;"><span style="background-color: white;">Higher version of sql server is not having such problem.</span></span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-8292519171764982272022-05-29T14:57:00.001+05:302022-05-29T19:08:00.433+05:30SQL Error 105 : Unclosed quotation mark before the character string '%.*ls'<p class="MsoNoSpacing"><span style="background: white; font-family: arial;"><span style="color: red;">Msg 105, Level 15, State 1, Line n<br style="box-sizing: inherit;" />
Unclosed quotation mark before the character string '%.*ls'</span><span style="color: #333333;"><o:p></o:p></span></span></p><p class="MsoNoSpacing"><span style="background: white; font-family: arial;">The error msg indicate not correctly formatted
string. In SQL Server it must be enclosed with single quotation mark ('
'). <o:p></o:p></span></p><p>
</p><p class="MsoNoSpacing"><span style="background: white; font-family: arial;">SQL Server uses the single quote to delimit
strings. If the <strong style="outline: 0px;"><span style="border: 1pt none windowtext; padding: 0cm;">data</span></strong> itself contains a single quote you must
pass in two single quotes in order for sql server to process the string
correctly.</span></p><p class="MsoNoSpacing"><span face=""Segoe UI", "sans-serif"" style="background: white; color: #333333;"></span></p><pre class="prettyprint notranslate prettyprinted" style="background: rgb(238, 238, 238); border: 1px solid rgb(136, 136, 136); margin-top: -5px; overflow-x: auto; padding: 2px; width: 655.844px;"><span style="color: #2b00fe; font-family: Consolas;">SELECT '</span><span style="color: red; font-family: Consolas;">Hello</span></pre><p><span style="background-color: white; color: #333333;"><b>Output</b>:</span></p><div style="text-align: left;"><span style="color: red;">Msg 105, Level 15, State 1, Line 1<br /></span><span style="color: red;">Unclosed quotation mark after the character string 'Hello<br /></span><span style="color: red;">'.<br /></span><span style="color: red;">Msg 102, Level 15, State 1, Line 1<br /></span><span style="color: red;">Incorrect syntax near 'Hello<br /></span><span style="color: red;">'.</span></div><p class="MsoNoSpacing"><span style="color: red;"><span style="background-color: white;"></span></span></p><p><span face="Rubik, sans-serif" style="background-color: white; color: #444444; font-size: 16px;"></span></p><pre class="prettyprint notranslate prettyprinted" style="background: rgb(238, 238, 238); border: 1px solid rgb(136, 136, 136); margin-top: -5px; overflow-x: auto; padding: 2px; width: 655.844px;"><span style="color: #2b00fe; font-family: Consolas;">SELECT </span><span style="color: red; font-family: Consolas;">'Hello'' World'''</span></pre><p><span face="Rubik, sans-serif" style="background-color: white; color: #444444; font-size: 16px;"><b>Output</b>:</span></p><p><span face="Rubik, sans-serif" style="background-color: white; color: #444444;">Hello' World'</span></p>Unknownnoreply@blogger.com0tag:blogger.com,1999:blog-7291345735848920129.post-89474448941411761692022-05-28T21:25:00.000+05:302022-05-28T21:25:13.831+05:30SQL Error 104 : ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator<p><span style="color: red; font-family: arial;"> Msg 104, Level 16, State 1, Line 11</span></p><p><span style="color: red; font-family: arial;">ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.</span></p><p style="background-color: white;"><span style="color: #444444;"><span style="font-family: arial;">This error message appears when you try to sort a resultset by an element that is not contained in the SELECT list and the statement contains a UNION-, INTERSECT-, or EXCEPT operator.</span></span></p><p style="background-color: white;"><span style="font-family: arial;">For Example, suppose you are fetching FirstName and LastName columns and you want to generate a list of these names where the first name and last name are concatenated together to form the full name and sort the output by the LastName column:</span></p><pre class="prettyprint notranslate prettyprinted" style="background: rgb(238, 238, 238); border: 1px solid rgb(136, 136, 136); margin-top: -5px; overflow-x: auto; padding: 2px; width: 655.844px;"><span style="color: #2b00fe; font-family: Consolas;">Use AdventureWorks2017
Go
SELECT </span><span style="font-family: Consolas;">[FirstName] +' '+ [LastName] AS [FullName]</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">Person.Person</span><span style="color: #2b00fe; font-family: Consolas;">
where </span><span style="font-family: Consolas;">FirstName=</span><span style="color: #2b00fe; font-family: Consolas;">'</span><span style="color: red; font-family: Consolas;">Adam</span><span style="color: #2b00fe; font-family: Consolas;">'
UNION
SELECT </span><span style="font-family: Consolas;">[FirstName] +' '+ [LastName] AS [FullName]</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">Person.Person</span><span style="color: #2b00fe; font-family: Consolas;">
where </span><span style="font-family: Consolas;">FirstName</span><span style="color: #2b00fe; font-family: Consolas;">='</span><span style="color: red; font-family: Consolas;">Ben</span><span style="color: #2b00fe; font-family: Consolas;">'
ORDER BY </span><span style="font-family: Consolas;">[LastName]</span></pre><p style="background-color: white;"><span style="font-family: arial;">Output:</span></p><p style="background-color: white;"><span style="color: red; font-family: arial;">Msg 104, Level 16, State 1, Line 11</span></p><p style="background-color: white;"><span style="color: red; font-family: arial;">ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.</span></p><p style="background-color: white;"><span style="font-family: arial;">Since the LastName column is not part of the output, although it’s part of one of the columns in the SELECT list, the above error will be encountered</span></p><p style="background-color: white;"><span style="font-family: arial;">To avoid this error, simply remove the ORDER BY clause from your SELECT statement. If you really need to sort it by a particular column, just include that column in your SELECT statement.</span></p><pre class="prettyprint notranslate prettyprinted" style="background: rgb(238, 238, 238); border: 1px solid rgb(136, 136, 136); margin-top: -5px; overflow-x: auto; padding: 2px; width: 655.844px;"><span style="color: #2b00fe; font-family: Consolas;">Use </span><span style="font-family: Consolas;">AdventureWorks2017</span><span style="color: #2b00fe; font-family: Consolas;">
Go
SELECT </span><span style="font-family: Consolas;">[FirstName] + [LastName] AS [FullName],LastName</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">Person.Person</span><span style="color: #2b00fe; font-family: Consolas;">
where </span><span style="font-family: Consolas;">FirstName</span><span style="color: #2b00fe; font-family: Consolas;">='</span><span style="color: red; font-family: Consolas;">Adam</span><span style="color: #2b00fe; font-family: Consolas;">'
UNION
SELECT </span><span style="font-family: Consolas;">[FirstName] + [LastName] AS [FullName],LastName<span style="color: #2b00fe;">
FROM </span>Person.Person<span style="color: #2b00fe;">
where </span></span><span style="font-family: Consolas;">FirstName</span><span style="color: #2b00fe; font-family: Consolas;">='</span><span style="color: red; font-family: Consolas;">Ben</span><span style="color: #2b00fe; font-family: Consolas;">'
ORDER BY </span><span style="font-family: Consolas;">[LastName]</span></pre><p style="background-color: white; font-family: Helvetica, sans-serif;">If you don’t want the extra column in your output, you can put the SELECT statement with the UNION in a sub-query, as follows:</p><pre class="prettyprint notranslate prettyprinted" style="background: rgb(238, 238, 238); border: 1px solid rgb(136, 136, 136); margin-top: -5px; overflow-x: auto; padding: 2px; width: 655.844px;"><span style="color: #2b00fe; font-family: Consolas;">Use </span><span style="font-family: Consolas;">AdventureWorks2017</span><span style="color: #2b00fe; font-family: Consolas;">
Go
SELECT </span><span style="font-family: Consolas;">[FullName]</span><span style="color: #2b00fe; font-family: Consolas;">
FROM (
SELECT </span><span style="font-family: Consolas;">[FirstName] +' '+ [LastName] AS [FullName],LastName</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">Person.Person</span><span style="color: #2b00fe; font-family: Consolas;">
where </span><span style="font-family: Consolas;">FirstName</span><span style="color: #2b00fe; font-family: Consolas;">='</span><span style="color: red; font-family: Consolas;">Adam</span><span style="color: #2b00fe; font-family: Consolas;">'
UNION
SELECT </span><span style="font-family: Consolas;">[FirstName] +' '+ [LastName] AS [FullName],LastName</span><span style="color: #2b00fe; font-family: Consolas;">
FROM </span><span style="font-family: Consolas;">Person.Person</span><span style="color: #2b00fe; font-family: Consolas;">
where </span><span style="font-family: Consolas;">FirstName</span><span style="color: #2b00fe; font-family: Consolas;">='</span><span style="color: red; font-family: Consolas;">Ben</span><span style="color: #2b00fe; font-family: Consolas;">'
) A
ORDER BY </span><span style="font-family: Consolas;">[LastName]</span></pre><p><br /></p>Unknownnoreply@blogger.com0