Table-valued function 'Nodes' cannot have a column alias.

The following error occurred when we try to read xml data either from a Column or Expression...

1. XML data definition:
The following is the xml data, we just want to read data of Name and Place nodes.

Declare @xml xml
Select @xml = '<SQLBuddy><Name>Pandian S</Name><Place>Chennai</Place></SQLBuddy>'

The actual structure of the xml data is ...





2. Reading node value
Declare @xml xml
Select @xml = '<SQLBuddy><Name>Pandian S</Name><Place>Chennai</Place></SQLBuddy>'

Select Samples.[SQL].value('(Name)[1]', 'Varchar(100)') AS 'Name',
Samples.[SQL].value('(Place)[1]', 'Varchar(100)') AS 'Place'
FROM @xml.Nodes('/SQLBuddy') AS Samples([SQL])
 
It throws an error...
Msg 317, Level 16, State 1, Line 5
Table-valued function 'Nodes' cannot have a column alias.

Reason:
"nodes" is case sensitive. The actual script should be as follows
 
Declare @xml xml
Select @xml = '<SQLBuddy><Name>Pandian S</Name><Place>Chennai</Place></SQLBuddy>'
Select Samples.[SQL].value('(Name)[1]', 'Varchar(100)') AS 'Name',
Samples.[SQL].value('(Place)[1]', 'Varchar(100)') AS 'Place'
From @xml.nodes('/SQLBuddy') AS Samples([SQL])

Expected Result:

How to search a specific STRING in all TABLES in current DATABASE ?

Do you want to search a specific string in current database ? (In all the tables)

/*Creating a new Database*/
If DB_ID('SQLBuddy') Is Null
Create Database SQLBuddy
Go

/*Changing the Database context to "SQLBuddy" Database*/
Use SQLBuddy
Go

/*Creating Sample Tables: "Table1", "Table2", "Table3", "Table4"*/
If OBJECT_ID('Table1') Is Null
Create Table Table1
(
Id Int Identity(1,1),
Column1 Varchar(50),
Column2 NVarchar(50),
Column3 DateTime
)
Go

If OBJECT_ID('Table2') Is Null
Create Table Table2
(
Id Int Identity(1,1),
Column1 Int,
Column2 DateTime,
Column3 Char(50)
)
Go

If OBJECT_ID('Table3') Is Null
Create Table Table3
(
Id Int Identity(1,1),
Column1 Int,
Column2 DateTime
)
Go

If OBJECT_ID('Table4') Is Null
Create Table Table4
(
Id Int Identity(1,1),
Column1 NVarchar(100),
Column2 Varchar(50)
)
Go

/*Inserting Sample Data*/
Insert Table1(Column1, Column2, Column3) Values('MS SQL Server','SQL Administration',GETDATE())
Insert Table1(Column1, Column2, Column3) Values('MS SQL Server','SQL Developement',GETDATE())
Insert Table1(Column1, Column2, Column3) Values('Oracle','Oracle Administration',GETDATE())
Go

Insert Table2(Column1, Column2, Column3) Values(1,getdate(),'MS SQL')
Insert Table2(Column1, Column2, Column3) Values(2,getdate(),'MS Access')
Go

Insert Table3(Column1, Column2) Values(1,getdate())
Insert Table3(Column1, Column2) Values(2,getdate())
Go
 
Insert Table4(Column1, Column2) Values('MS SQL Server','BI')
Insert Table4(Column1, Column2) Values('MS SQL Server','Developement')
Go

/*Creating a stored procedure to findout the Matching Data from tables in current Database*/
If OBJECT_ID('Usp_Isavailable') Is Not null
Drop Proc Usp_Isavailable
Go
Create Proc Usp_Isavailable
(
@Table Varchar(100),
@Column Varchar(100),
@Search Varchar(1000),
@Available Varchar(100) Output
)
As
Begin
Set Nocount On

Declare @Query Varchar(1000)
Declare @Status Table(Available Varchar(100))

Select @Query = 'Select ' + @Column + ' from ' + @Table + ' where ' + @Column + ' like ''%' + @Search + '%'''

Insert @Status
Exec(@Query)

Select @Available = Available from @Status
End
Go
 
/*Creating stored procedure to identify/fetch the matching Columns & Tables in current Database*/
If Object_Id('Usp_FindString') Is Not Null
Drop Proc Usp_FindString
Go
Create Proc Usp_FindString
(
@Database Varchar(100) = NULL,
@TableName Varchar(100) = NULL,
@ColumnName Varchar(100) = NULL,
@SearchString Varchar(100)
)As
Begin
Set Nocount On

Create Table #Table_Column(TableName Varchar(100),ColumnName Varchar(100))
Create Table #Columns(TableSlno Int,ColumnSlno Int, TableName Varchar(100),ColumnName Varchar(100))
Create Table #Status(DatabaseName Varchar(100), TableName Varchar(100), ColumnName Varchar(100), MatchingValue Varchar(4000))

Insert #Table_Column
Select distinct Object_name(c.id) [Table], c.name [Column] from sys.syscolumns c join sys.types t on (c.xtype = t.system_type_id)
where t.collation_name Is Not null
and c.id >100
and Objectproperty(c.id,'IsUserTable') = 1
And Object_name(c.id) = ISNULL(@TableName, Object_name(c.id))
And c.name = ISNULL(@ColumnName, c.name)
Order by 1

Declare @Tables Int, @Seq Int, @Columns Varchar(4000), @Table Varchar(4000), @Script Varchar(MAX),@IsAvailable Varchar(100), @DatabaseName Varchar(100), @ColSeq Int, @ColTotal Int

;With CTEs
As
(
Select distinct o.TableName,o.ColumnName from #Table_Column o
)

Insert #Columns
Select Dense_Rank() Over(order by TableName),Dense_Rank() Over(Partition by TableName Order by ColumnName),TableName,ColumnName from CTEs

Select @Tables = Max(TableSlno) From #Columns
Select @Seq =1, @Columns ='', @Script ='', @DatabaseName = DB_Name(), @ColSeq = 1

While (@Seq <=@Tables)
Begin
Select @ColTotal = Max(ColumnSlno) From #Columns where TableSlno = @Seq
Select @ColSeq = 1

While(@ColSeq <= @ColTotal)
Begin
select @Table = TableName, @Columns = ColumnName from #Columns where TableSlno = @Seq And ColumnSlno = @ColSeq

EXEC Usp_Isavailable @Table,@Columns,@SearchString,@IsAvailable Output

If (@IsAvailable Is Not null AND @IsAvailable <>'')
Insert #Status Values(@DatabaseName,@Table,@Columns,@IsAvailable)

Select @IsAvailable = NULL
Select @ColSeq = @ColSeq + 1
End

Select @Seq = @Seq + 1
End

Select * from #Status

Drop Table #Table_Column
Drop Table #Columns
Drop Table #Status
End
Go
 
/*1. Searching a specific data("SQL") in all the tables & columns in current database*/
Exec Usp_FindString @SearchString = 'SQL'
Go
 
 
 
 
 
 
 
/*2. Searching a specific data("SQL") in all the tables in specific columns("Column1") in current database*/
Exec Usp_FindString @ColumnName ='Column1', @SearchString = 'SQL'
Go
 
 
 
 
 
/*3. Searching a specific data("SQL") in specific table("Table1") in specific columns("Column1") in current database*/
Exec Usp_FindString @TableName='Table1',@SearchString = 'SQL'
Go
 
 
 
 
 
/*4. Searching a specific data("SQL") in specific table("Table1") in specific columns("Column1") in current database*/
Exec Usp_FindString @TableName='Table1', @Columnname ='Column1', @SearchString = 'SQL'
Go
 

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Can we generate sequence number witout Loop ? YES. Using Common Table Expression - Recursion (CTE Recursion)

When we execute the following script, It throws an error..

;
WITH CTEs
AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number + 1 FROM CTEs
)

SELECT * FROM CTEs
GO
 
Error:
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Normally, The default recursion limit in 100. OK

How to genarete upto 1000. Is there any option here ? YES


;
WITH CTEs
AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number + 1 FROM CTEs
)

SELECT * FROM CTEs OPTION(MAXRECURSION 1000)
GO

It generates 1 through 1000 with error when it exceeds the value(1000)

Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 1000 has been exhausted before statement completion.

Then, what would be the limit here ? The maximum limit is 32767 and default is 100

Ok. How to produce the sequence number (1 to 1000) without any ERROR using CTE Recursion

;
WITH CTEs
AS
(
SELECT 1 AS Number
UNION ALL
SELECT Number + 1 FROM CTEs WHERE [Number] <1000
)

SELECT * FROM CTEs OPTION(MAXRECURSION 1000)
GO 

Now, It generates the sequence number 1 through 1000 without any Error

Parameters supplied for object 'Table Name' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

When I execute the following statement, The error occurred...

SELECT * FROM Tb_Table1(NOIOCK)
Go

Error:
Msg 207, Level 16, State 1, Line 1
Invalid column name 'NOIOCK'.
Msg 215, Level 16, State 1, Line 1
Parameters supplied for object 'Tb_Table1' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.

Reason:
The error because of Typo error... "NOIOCK" is not a valid table hint...But, If we try to change the statement with "WITH" keyword, The following error will be the exact one...
 
SELECT * FROM Tb_Table1 With(NOIOCK)
Go

Error:
Msg 321, Level 15, State 1, Line 1
"NOIOCK" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.

So, The source of the error is the Table hint "NOIOCK"

The actual statement/recommended format is...

SELECT Column1, Column2,... FROM Tb_Table1 With(Nolock)
Go

The following are the possible table hints...
 


 

Cannot use the OUTPUT option when passing a constant to a stored procedure.

When we use Output parameter in a Stored procedure, There is a chance to get the following Error... Why?, and How to solve it ?

Msg 179, Level 15, State 1, Line 3
Cannot use the OUTPUT option when passing a constant to a stored procedure.

/*Creating a stored procedure*/
If OBJECT_ID('Usp_Procedure1') Is Not Null
Drop Proc Usp_Procedure1
Go
Create Proc Usp_Procedure1
(
@Param1 Varchar(50) Output
)As
Begin
Set Nocount On
Select @Param1 = 'SQL Server Administration'
End
Go

/*Executing the stored procedure*/
Exec Usp_Procedure1 'SQL Server BI' Output
Go
 
The following error occurred...
Msg 179, Level 15, State 1, Line 2
Cannot use the OUTPUT option when passing a constant to a stored procedure.

Why?
- The reason is, we have one Output parameter in a stored procedure(Usp_Procedure1), Normally, Output parameters can be used in both directions (Input and Output).
- So, when we access the stored procedure, Output keyword is required only to return the data through the Output parameters. If we don't want to return any data through the Output parameters then, The Output keyword should not be used.

If we want to return any data through the Output parameter:
Declare @P1 Varchar(50)
Exec Usp_Procedure1 @P1 Output
Select @P1
Go
- Output keywork should be used along with parameter @P1

 
If we don't want to return any data through the Output parameter:
Exec Usp_Procedure1 'SQL Server BI'
Go
- Output parameter should not be used when passing constant value instead of variable.

Conclusion:
1. Output keyword should not be used when we pass the constant value to the stored procedure directly instead of a variable.
Exec Usp_Procedure1 'SQL Server BI'
Go

2. Output keyword should be used when the value should be returned from the stored procedure through the variable.
Declare @P1 Varchar(50)
Exec Usp_Procedure1 @P1 Output
Select @P1
Go

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


When I run the following script... The error occurred

/*Creating Tables*/
USE [Database1]
GO
CREATE TABLE Tb_Sample1(
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Col1] [varchar](10) NULL,
[Col2] [datetime] NULL
) ON [PRIMARY]
GO

CREATE TABLE Tb_Sample2(
[Id] [int] FOREIGN KEY REFERENCES Tb_Sample1(Id) NULL,
[Col1] [varchar](50) NULL
) ON [PRIMARY]
GO

/*Inserting Records*/
Insert Tb_Sample1(Col1,Col2) Values('SQL Server',CURRENT_TIMESTAMP)
Go
Insert Tb_Sample2(Id,Col1) Values(1,'Administration')
Go

/*Using sub-query*/
SELECT a.* FROM Tb_Sample1 a with (Nolock)
WHERE a.Id =
(SELECT b.Id,b.Col1 FROM Tb_Sample2 b with (Nolock))

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

Reason:
Here, Sub query returns Two columns, These value being compared with one column of the mail query...

Alternate ways:
The query can be changed to any one of the following ways..

SELECT a.* FROM Tb_Sample1 a with (Nolock)
WHERE a.Id in
(SELECT b.Id FROM Tb_Sample2 b with (Nolock))

(or)

Select a.* from Tb_Sample1 a with (Nolock)
where exists
(Select 1 from Tb_Sample2 b with (Nolock) where a.Id = b.Id)

(or)

Select a.* from Tb_Sample1 a with (Nolock) join Tb_Sample2 b with (Nolock) on (a.Id = b.Id)
 
(or)

Select a.* from Tb_Sample1 a with (Nolock), Tb_Sample2 b with (Nolock) where a.Id = b.Id

Recommended way:
Select a.Id, a.Col1, a.Col2 from Tb_Sample1 a with (Nolock) join Tb_Sample2 b with (Nolock) on (a.Id = b.Id)