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>

How to create a slide show of external pages inside a DIV

The code below unables you to display various external pages inside a DIV and switching them every 15 seconds


First add a DIV to your html page

<div id="contentarea"></div>

Then save jquery.js file in the dame directory as your html page and add reference to jquery to the head section of your page:

<script type="text/javascript" src="jquery.js"></script>

Then add the following script to the head section:

<script type="text/javascript"> 
<!-- 
  
 // Place the urls in this array 
 var urls = new Array("page1.html", "http://elena-sqldba.blogspot.com", "http://www.google.com"); 
 // Set the number of seconds you want to view each page
 var viewfor = 15; 
 var mark=1;
   
   
         $(document).ready(function(){
          initSrc();
         });

 function changeurl(){   
         ajaxpage(urls[mark], 'contentarea');
  mark+=1;// increments the mark variable (array element) 
  if (mark==urls.length){ 
   mark=0;// returns to urls[0] at the end of the array count 
  } 
 }  
  
   
 function initSrc(){
  ajaxpage(urls[0], 'contentarea');
  window.setInterval("changeurl()", viewfor * 1000);   
        }
            

 function ajaxpage(url, containerid){
      var page_request = false
      if (window.XMLHttpRequest) // if Mozilla, Safari etc
           page_request = new XMLHttpRequest()
      else if (window.ActiveXObject){ // if IE
          try {
               page_request = new ActiveXObject("Msxml2.XMLHTTP")
          } 
          catch (e){
               try{
                   page_request = new ActiveXObject("Microsoft.XMLHTTP")
               }
               catch (e){}
          }
      }
      else
          return false

      page_request.onreadystatechange=function(){
          loadpage(page_request, containerid)
      }
    
      page_request.open('GET', url, true)
      page_request.send(null)

 }

 function loadpage(page_request, containerid){
      if (page_request.readyState == 4 && (page_request.status==200 || window.location.href.indexOf("http")==-1)){
       var divElement = document.getElementById(containerid);
          divElement.innerHTML = '';
       
          try {
             divElement.innerHTML = page_request.responseText;
          }
          catch (e) {
             // IE fails unless we wrap the string in another element.
             var wrappingElement = document.createElement('div');
             wrappingElement.innerHTML = page_request.responseText;
             divElement.appendChild(wrappingElement);
          }        
         } //end if
 }
// --> 
</script>

How to load external pages into a DIV

Loading external html pages into a DIV without having to reload the browser or using iFrames is possible using AJAX and JQuery.

First add a DIV to your html page

<div id="contentarea"></div>

Then save jquery.js file in the dame directory as your html page and add reference to jquery to the head section of your page:

<script type="text/javascript" src="jquery.js"></script>

Then add the following script to the head section:

<script type="text/javascript"> 
<!-- 
    
         $(document).ready(function(){
          ajaxpage("http://elena-sqldba.blogspot.com", 'contentarea');
         });


 function ajaxpage(url, containerid){
      var page_request = false
      if (window.XMLHttpRequest) // if Mozilla, Safari etc
           page_request = new XMLHttpRequest()
      else if (window.ActiveXObject){ // if IE
          try {
               page_request = new ActiveXObject("Msxml2.XMLHTTP")
          } 
          catch (e){
               try{
                   page_request = new ActiveXObject("Microsoft.XMLHTTP")
               }
               catch (e){}
          }
      }
      else
          return false

      page_request.onreadystatechange=function(){
          loadpage(page_request, containerid)
      }
    
      page_request.open('GET', url, true)
      page_request.send(null)

 }

 function loadpage(page_request, containerid){
      if (page_request.readyState == 4 && (page_request.status==200 || window.location.href.indexOf("http")==-1)){
       var divElement = document.getElementById(containerid);
          divElement.innerHTML = '';
       
          try {
             divElement.innerHTML = page_request.responseText;
          }
          catch (e) {
             // IE fails unless we wrap the string in another element.
             var wrappingElement = document.createElement('div');
             wrappingElement.innerHTML = page_request.responseText;
             divElement.appendChild(wrappingElement);
          }        
         } //end if
 }


 
// --> 
</script>

SQL Server DBA Duties

DAILY

Action: Check Network Connectivity
Reason: To check that hardware & server is up.
To check that IP address & name have not been changed.
Gives early warning if server fails.
Checks IP address & name resolution (sometimes a problem with wins, dns, lmhosts).
Method: 1. Ping sql servers every 15 mins with IP Sentry.
2. Use batch file to ping servers.
3. Use a server monitoring tool.

Action: Check SQL services
Reason: To check that SQL server is available.
To check MSSQLserver & SQLexecutive/agent, DTS services are running.
To check that we can connect.
Method: SEM:
Green lights if SEM, or connect to each server by clicking on the ‘+’ for each server & open sql executive.

Action: Check Scheduled Tasks/ Jobs
Reason: Backups, DBCC checks, etc. are run overnight as scheduled tasks.
Method: SEM: Highlight server, servers | scheduled tasks

Action: Check dba_tools..scheduledtasklog
Reason: If get a failed scheduled task.
Some DBA’s create an error log table, which gives more detail on errors during task execution
Method: SEM: Open ISQL/W, Select * from dba_tools..scheduledtasklog.

Action: Check DBCC output
Reason: DBCC commands check DB integrity & consistency.
Finds errors in the structure of the server & databases.
If more than the usual ‘msg’ string errors, check BOL.
Method: Create desktop or folder shortcuts to point to the dbcc output text files.
Check date when checks done.

Action: Check Hard Disk Space
Reason: If system drives run low they crash.
Backup devices expand dynamically & will fail if insufficient space.
Need to know which partition is the system & which partition holds the backup devices.
Method: Windows explorer: Check drive properties
SEM: Check database devices, even if automatic.
ISQL/W: Create stored procedure or batch file, using xp_fixeddrives for each server.

Action: Check Database & Transaction Log Space
Reason: If DB or txn log space runs out, then transactions will fail. Ver 6.5
Method: SEM: Databases x2 click the DB
ISQL/W: sp_spaceused
www.SQLserverPortal.com
3rd party tools for SQL server

Action: Check Event Logs
Reason: OS errors are logged here & some SQL errors are logged here.
OS & SQL usually warns you before a problem becomes critical.
Method: Control panel, admin tools\event viewer\

Action: Check SQL Error Logs
Reason: SQL errors are logged here, SQL usually warns you before a problem becomes critical.
Method: SEM: servers | error log, scroll to bottom (ctrl+end) = most recent.
If get errors, tap in number into BOL.

WEEKLY

Action: Save all server configuration info.
Reason: You need more than just the db backup to rebuild a SQL server.
Method: ISQL/W: Create stored procedures to get config info. etc & output to txt files.

MONTHLY



Action: Do a test restore of a db backup
Reason: Sometimes backups don’t work eg due to errors found by DBCC commands.
Method: Create a test device, restore from tape to a bkup device, restore, then run DBCC checks on it.

How to modify Desktop Wallpaper using VBScript

From my experience, the best way to change Desktop wallpaper is via vbs file.

below is the code for the vbs file that assumes path to the wallpaper will be paaed as an argument to the file below:

Set oShell = CreateObject("WScript.Shell")
Set oSHApp = CreateObject("Shell.Application")

if WScript.Arguments.Count > 0 then
 
 'Set Wallpaper path
 sWallPaper = WScript.Arguments(0)

 ' Update Wallpaper in registry
 oShell.RegWrite "HKCU\Control Panel\Desktop\Wallpaper", sWallPaper

 'Refresh Screen by running Control Panel application. 
 'If the application is already open, it will activate the running instance.
 oSHApp.ControlPanelItem cstr("desk.cpl")

 ' Loop and wait until Display Properties is loaded.
 Do Until oShell.AppActivate ("Display Properties")
 Loop
 oShell.SendKeys "{DOWN}{UP}{TAB 3}~"
end if

The code above works like a charm, I find it to be a lot more efficient than running a batch file with the following code, which often does not refresh the Desktop after modifying the registry:

reg add "HKCU\Control Panel\Desktop" /v Wallpaper /t REG_SZ /d %1 /f
RUNDLL32.exe user32.dll,UpdatePerUserSystemParameters

Cannot modify desktop wallpaper

If, when you go to Properties -> Desktop on your machine, Desktop Wallpaper Browse button is greyed out and you can't change wallpaper, right-click on Start button, type in regedit and click OK to open your registry. Navigate to

HKEY_CURRENT_USER\Software\Microsoft\Windows\CurrentVersion\Policies\System

and delete Wallpaper key.

If Wallpaper key is not there, check the same location for ActiveDesktop folder. If it is there - delete it. Now restart the computer and it should work

JQuery: Setting image properties

The following code sets the src and alt attributes for all images

$("img").attr({ src: "test.jpg", alt: "Test Image" });

If you want to det just the src attribute, use:

$("img").attr("src","test.jpg");

If you want to set Title attribute of an image to reflect image filename, use:

$("img").attr("title", function() { return this.src });

JQuery: $(document).ready() vs. $(window).load()

$(document).ready() executes when the DOM is fully loaded (ex.: an 'element')

$(document).ready() is ideal for one-time initialization routines

$(window).load() executes when the complete page, including all fames, images and objects, is loaded

Suppose you have an iFrame

Code below will find your iFrame and insert the text specified into the body of the document

$(document).ready(function(){ 
    $('#frameID').load(function(){ 
        $('#frameID').contents().find('body').html('My new contents'); 
    }); 
}); 

How to access contents of an iFrame from JQuery

Suppose you have an iFrame

   <iframe id="iframeID" ...></iframe>

Your iFrame contains div with id=”myDiv”:

   <div id="myDiv">DIV contents here</div>

You can retrieve contents of a div using the following line of code:

   $('#iframeID').contents().find('#myDiv').html(); 

Error when exporting data to Excel

While exporting data from SQL Server 2005 to Excel, I got an error "Columns "Field1" and "F1" cannot convert between unicode and non-unicode string data types". I was exporting data returned by a particular view. In order to fix the issue I had to modify the view, utilizing CAST function to cast the columns to their existing data type. Weird, huh?

Here is my original view. Please note that Field1 and Field2 are of type varchar(5) and varchar(100) respectively:

CREATE VIEW [dbo].[myView]
AS
SELECT Field1,
Field2
FROM Table1

Here is what I modified my view to and what worked for me:

CREATE VIEW [dbo].[myView]
AS
SELECT CAST(Field1 As Varchar(50)) as Field1, 
CAST (Field2 as Varchar(100)) As Field2
FROM Table1

How to delete duplicate records from SQL Server table

This can be done in 4 simple steps, utilizing temp tables.

First you need to identify duplicate records and insert them into a temp table:

INSERT INTO #mytemptbl
SELECT * FROM Table1
GROUP BY field1, field2, field3
Having COUNT(*) > 1

Then delete those records from the original table:

DELETE FROM Table1
FROM Table1
INNER JOIN #mytemptbl
ON  Table1.field1 = #mytemptbl.field1
AND Table1.field2 = #mytemptbl.field2
AND Table1.field3 = #mytemptbl.field3

Next re-insert records from temp table into the original table:

INSERT INTO Table1
SELECT * FROM #mytemptbl

And finally drop temp table:

DROP TABLE #mytemptbl

How to get sql table column names based on column field value

There is no easy way to retrieve a list of columns from a particular SQL Server table based on the column values. The only way is to create a user-defined function or a stored procedure and then retrieve its results.

Here is an example of stored procedure that returns a comma separated list of table columns that contain a particular value.

CREATE PROCEDURE [dbo].[usp_FindColumnsContainingTheValue](@table_name nvarchar(50), @val nvarchar(50))
AS
BEGIN
DECLARE @column_name nvarchar(50)
DECLARE @column_list nvarchar(1000)
DECLARE @count int
DECLARE @sql nvarchar(1000)

DECLARE my_cursor CURSOR FOR
SELECT column_name 
FROM information_schema.columns
WHERE table_name=@table_name
AND data_type IN('nvarchar','varchar', 'ntext', 'nchar')

OPEN my_cursor

FETCH NEXT FROM my_cursor
INTO @column_name

SET @column_list=''

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql=N'SELECT @countOUT=COUNT(*) FROM ' + @table_name + ' WHERE ' + @column_name + '=' + '''' + @val + ''''

EXEC sp_executesql 
        @query = @sql, 
        @params = N'@countOUT INT OUTPUT', 
        @countOUT = @count OUTPUT

 IF @count > 0 
 BEGIN
  IF LEN(@column_list) > 0
  BEGIN
   SET @column_list=@column_list + ',' + @column_name
  END
  ELSE
  BEGIN
   SET @column_list= @column_name
  END


 END
     FETCH NEXT FROM my_cursor
     INTO @column_name
END

CLOSE my_cursor
DEALLOCATE my_cursor
SELECT @column_list

END

As you can see, the stored procedure I wrote takes two parameters - table name and a value.

Now, as a way to illustrate on how this stored procedure can be used, I will take Northwind database table Orders and will use the above procedure to retrieve all the columns that contain value 'Brazil'.

EXECUTE dbo.usp_FindColumnsContainingTheValue 'Orders', 'Brazil'

Will return a single value 'ShipCountry', for it's the only column that contains a value 'Brazil' in one of the rows.