How to select a comma separated list and use it in WHERE IN clause

First create a user-defined function that retrieves a comma separated list of IDs from one table using COALESCE built-in function, sort of like I did here

How to combine several rows in one row, separated by commas

CREATE FUNCTION [dbo].[GetListOfIDs] ( )
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RES VARCHAR(8000)
  SELECT @RES = COALESCE(@RES + ',','') + ID
  FROM Table1
  RETURN (@RES)
END

Then create a stored procedure that will retrieve all the data from another table, based on input list:

CREATE PROCEDURE [dbo].[usp_GetAllData]
@list varchar(500)
as
begin
declare @SQL varchar(600)
set @SQL = 'SELECT * FROM TABLE2 WHERE ID IN ('+ @list +')'
EXEC(@SQL)
end       

Then you will need one more function to tie it all together - use user-defined function to retrieve the list of IDs and then execute stored procedure and pass it the list:

CREATE PROCEDURE usp_RunProcedure
AS
BEGIN
DECLARE @ids varchar(600)  
SELECT @ids = dbo.GetListOfIDs()

EXEC dbo.usp_GetAllData @list=@ids
END

SQL Server 2005 Agent XPs disabled issue

Today when I got to work I got an e-mail sent to me through Database mail, notifying me that a number of SQL scheduled jobs failed. That's the message that I found in the logs:

[298] SQLServer Error: 15281, SQL Server blocked access to procedure 'dbo.sp_sqlagent_has_server_access' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. [SQLSTATE 42000] (ConnIsLoginSysAdmin)

When I logged in to database server, I saw that next to SQL Server Agent there is a note (Agent XPs disabled).

Agent XPs is an option to enable the SQL Server Agent extended stored procedures on this server. When this option is not enabled, the SQL Server Agent node is not available in SQL Server Management Studio Object Explorer.


The possible values are:

0, indicating that SQL Server Agent extended stored procedures are not available (the default).

1, indicating that SQL Server Agent extended stored procedures are available.


This setting can be changed without server stop or restart.

Aparently something happened, probably related to the Security policy, that changed the option from 1 to 0. So to resolve the issue I ran the code below to re-enable Agent XPs option by setting it back to 1.

USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Agent XPs', 1
GO
RECONFIGURE
GO

Another option is to re-enable it through SQL Server Surface Area Configuration tool.

Changing desktop wallpaper on Windows 7 programmatically

All the programmatic wallpaper changes from previous versions of Windows do not work on either Windows 7 or Vista. I have tried many different ways and many technologies untill i found a way that worked on Windows XP, Windows Vista, and Windows 7.

I created a dll using C#. Here is the C# code for it:

[DllImport("user32.dll", CharSet = CharSet.Auto)]
private static extern Int32 SystemParametersInfo(UInt32 action, UInt32 uParam, String vParam, UInt32 winIni);
private static readonly UInt32 SPI_SETDESKWALLPAPER = 0x14;
private static readonly UInt32 SPIF_UPDATEINIFILE = 0x01;
private static readonly UInt32 SPIF_SENDWININICHANGE = 0x02;

public void SetWallpaper(string path, ref string err)
{
    try
    {
        if (File.Exists(path))
        {
            SystemParametersInfo(SPI_SETDESKWALLPAPER, 3, path, SPIF_UPDATEINIFILE | SPIF_SENDWININICHANGE);
             err = string.Empty;

         }
         else
         {
              throw new Exception("Path '" + path + "' does not exist!");
         }
      }
      catch (Exception ex)
      {
           err = ex.Message;
      }

}

The function above accepts a file path of a wallpaper image as an argument along with a variable passed by reference that will return an error message in case of a failure. An empty string will be returned in case of a success.

Find all tables containing particular column

There are two ways to retrieve all the tables containing a particuler column

1) using INFORMATION_SCHEMA

SELECT * FROM INFORMATION_SCHEMA.COLUMNS As i WHERE i.column_name LIKE '%MyField%'

2) using SYS.TABLES

SELECT * FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%MyField%'

If you know the exact column name, then use = instead of LIKE to reduce the number of unrelated rows return