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

Displaying data by hour

I needed to display record count hour by hour for a 24 hour period, displaying just the hour and record count for that hour, without the date part but ordered not from 0 to 23 but from current hour, i.e. if it is currently 9:28am, then the data should be displayed from 9am today up to 8am tomorrow. Here is what I came up with:

SELECT CONVERT(VARCHAR(8),MyDateField,101) + ' ' + CONVERT(VARCHAR(2),[MyDateField],108) as TDate,    
DATEPART(hh,MyDateField) AS Hour,
COUNT(*) AS NumberOfRecordsPerHour    
FROM MyTable
WHERE DATEDIFF(hh, MyDateField, GETDATE())<= 24  
GROUP BY CONVERT(VARCHAR(8),MyDateField,101) + ' ' + CONVERT(VARCHAR(2),MyDateField,108), DATEPART(hh,MyDateField)  
ORDER BY TDate ASC 

VBScript equivalent to F5 key

I spend a considerable amount of time trying to come up with VBScript equivalent of F5 key for refreshing the desktop and as it turn out, SendKey "{f5}" method alone does not do the job. Below is the script, utilizing Windows Explorer shell command file (.scf)
that worked like a charm:

' Create explorer command file to toggle desktop window 
 Set objFSO = CreateObject("Scripting.FileSystemObject") 
 strFile= objFSO.BuildPath(objFSO.GetSpecialFolder(2), objFSO.GetTempName &".scf") 
 With objFSO.CreateTextFile(strFile, True) 
 .WriteLine("[Shell]") 
 .WriteLine("Command=2") 
 .WriteLine("[Taskbar]") 
 .WriteLine("Command=ToggleDesktop") 
 .Close 
 End With 
  
 ' Toggle desktop and send F5 to refresh desktop
 With CreateObject("WScript.Shell") 
 .Run """" & strFile & """" 
 WScript.Sleep 100 
 .Sendkeys "{F5}" 
 End With 
 ' Delete command file 
objFSO.DeleteFile strFile 

How to display html page inside an iFrame via link

Displaying a page inside iFrame via link is as simple as setting Target property of a link to the iframe name. The important part is to make sure iframe has both "id" and "name" attributes.

<a href="http://elena-sqldba.blogspot.com" target="myFrame">Elena's Blog</a>

<iframe id="myFrame" runat="server" width="500" name="myFrame">
        
</iframe>