To fetch SQL Agent JOBs Info.,

To fetch SQL JOBs' information from the current Instance

Use Master
Go 
EXECUTE msdb.dbo.sp_sqlagent_refresh_job
Go

The EXECUTE permission was denied on the object 'xp_sqlagent_is_starting', database 'mssqlsystemresource', schema 'sys'

The following Error occurs when execute the following statement

Declare @retval Int
EXECUTE master.dbo.xp_sqlagent_is_starting @retval OUTPUT
Select @retval

Error:
Msg 229, Level 14, State 5, Procedure xp_sqlagent_is_starting, Line 1
The EXECUTE permission was denied on the object 'xp_sqlagent_is_starting', database 'mssqlsystemresource', schema 'sys'.

It means, The current user doesn't have EXECUTE permission for the object 'xp_sqlagent_is_starting'.

To listout the permissions for the Object in current user/login:

Select * from fn_my_permissions('xp_sqlagent_is_starting','Object')
Go

It should return the following permissions(EXECUTE)

If not. Give the EXECUTE permission for the Object and for the User! (Login into SysAdmin login)
1. Login into SysAdmin role login (i.e: sa)

2. Execute the following statement to provide proper permission to execute the object.

Grant Execute On master.dbo.xp_sqlagent_is_starting to <YourLogin>
Go

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)

Column, parameter, or variable #1: Cannot find data type XYZ

The following Error occurred when we refer a user defined datatype which was created in another database. Even in different database or creating temporary tables ( #tables (or) ##tables ). Temporary tables created in Tempdb database.

/*Creating User defined datstype in DB1 database*/
Use DB1
Go
Create Type UDT_EMail From NVarchar(100)
Go

/*Refering the type when creating Temporary table*/
Create Table #Table1
(
Id Int Identity(1,1),
EMail UDT_EMail
)
 
The following error occurred
Msg 2715, Level 16, State 7, Line 1
Column, parameter, or variable #2: Cannot find data type UDT_EMail.
 
#Table1 will be created in Tempdb database, So, It could not find any data type in Tempdb database. But, It can be refered by Table variable..

/*Refering the user defined type in Table varible*/ 
Use DB1
Go
Declare @Tbl_Variable Table
(
EMail UDT_EMail
)

Is there any way to refer the Type in another database using three part naming convension ?

/*Refering the type using three part naming convension*/
Use DB1
Go
Create Table #Table11
(
Id Int Identity(1,1),
EMail DB1.dbo.UDT_EMail
)

The following error occurred
 
Msg 117, Level 15, State 2, Line 4
The type name 'DB1.dbo.UDT_EMail' contains more than the maximum number of prefixes. The maximum is 1.

Because, The type allows only 1 prefix, not morethan 1.

Note: No way to refer the User defined type in Tempdb (or) another database unless the Type created in that particular database itself. Even the type was created in Master database.

Hour, Minute & Second between two date

To calculate Hour(s), Minute(s) & Second(s) between two dates.

--Creating Function
If OBJECT_ID('UFN_HourMinuteSecond') Is Not Null
Drop Function dbo.UFN_HourMinuteSecond
Go
Exec(
'Create Function dbo.UFN_HourMinuteSecond
(
@StartDateTime DateTime,
@EndDateTime DateTime
) Returns Varchar(10)
As
Begin

Declare @Seconds Int,
@Minute Int,
@Hour Int,
@Elapsed Varchar(10)

Select @Seconds = ABS(DateDiff(SECOND ,@StartDateTime,@EndDateTime))

If @Seconds >= 60
Begin
select @Minute = @Seconds/60
select @Seconds = @Seconds%60

If @Minute >= 60
begin
select @hour = @Minute/60
select @Minute = @Minute%60
end

Else
Goto Final
End

Final:
Select @Hour = Isnull(@Hour,0), @Minute = IsNull(@Minute,0), @Seconds = IsNull(@Seconds,0)
select @Elapsed = Cast(@Hour as Varchar) + '':'' + Cast(@Minute as Varchar) + '':'' + Cast(@Seconds as Varchar)

Return (@Elapsed)
End'
)

--Using the Function (Two Datetime Input parameters)
Select dbo.UFN_HourMinuteSecond ('2011-06-24 00:54:36.503', '2011-06-24 02:11:02.563') [Hour:Minute:Second]
Go

--Result