New look

With extra time on my hands, I am finally had time to use new template designer with Blogger.  Finally, adjustable width templates w/o any of the hassle.  Not sure if I completely like the new look yet.

FYI, to anyone whose is interested, the mineral featured on the blog banner is a mineral species named Wulfenite. 

So you want useless mineral trivia for 1000.....

Wulfenite was discovered as a mineral species before the element Tungsten was discovered. 
Kind of interesting that in the pre1800s, natural substances commonly contained elements still undetermined.

DotNetZip Library

In my quest for application development using as many free and open source libraries as possible (due largely to the small audience of my mineral collecting software...)

DotNetZip Library - DotNetZip is an easy-to-use, FAST, FREE class library and toolset for manipulating zip files or folders. Zip and Unzip is easy: with DotNetZip, .NET applications written in VB, C# - any .NET language - can easily create, read, extract, or update zip files. For Mono or MS .NET.

Find Replace Characters in Text field in SQLServer 2000 database

Based on article posted at http://sqlserver2000.databases.aspfaq.com/how-do-i-handle-replace-within-an-ntext-column-in-sql-server.html

I needed to write a find and replace for CHAR(146) ` in a text field.  Above article for fort nText and the same solution for text worked with nText with the following changes:
   -  VARCHAR(32) from nVARCHAR(32)
   -  use @lenOldString = DATALENGTH(@oldString) instead of SET @lenOldString = DATALENGTH(@oldString)/2.

DECLARE
@TextPointer BINARY(16),
@TextIndex INT,
@oldString VARCHAR(32),
@newString VARCHAR(32),
@lenOldString INT,
@currentDataID INT;

SET @oldString = '’';
SET @newString = '''';

IF CHARINDEX(@oldString, @newString) > 0
BEGIN
PRINT 'Quitting to avoid infinite loop.';
END
ELSE
BEGIN

--Need the for nText fields
--SET @lenOldString = DATALENGTH(@oldString)/2

--Use this for text fields
SET @lenOldString = DATALENGTH(@oldString)

DECLARE irows CURSOR
LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
SELECT
DataID
FROM
dbo.tbData
WHERE
PATINDEX('%'+@oldString+'%', TextData) > 0;

OPEN irows;

FETCH NEXT FROM irows INTO @currentDataID;
WHILE (@@FETCH_STATUS = 0)
BEGIN

SELECT
@TextPointer = TEXTPTR(TextData),
@TextIndex = PATINDEX('%'+@oldString+'%', TextData)
FROM
dbo.tbData
WHERE
DataID = @currentDataID;

SELECT @TextPointer, @TextIndex

WHILE
(
SELECT
PATINDEX('%'+@oldString+'%', TextData)
FROM
dbo.tbData
WHERE
DataID = @currentDataID
) > 0
BEGIN


SELECT
@TextIndex = PATINDEX('%'+@oldString+'%', TextData)-1
FROM
dbo.tbData
WHERE
DataID = @currentDataID;

UPDATETEXT dbo.tbData.TextData @TextPointer @TextIndex @lenOldString @newString;
END
FETCH NEXT FROM irows INTO @currentDataID;
END

CLOSE irows;
DEALLOCATE irows;


END

One year later and crystal reports still suck.

Here I am a year later revisiting report changes I made almost one year to the day in 2009.  For those in working as a software developer you might be able to guess why I am doing this again one year later, it is because the product has not been released yet even though much of the development was completed 12 months ago!  Okay, I can tell myself I am fine with this (yeah right), let's get the part that I forgot from a year ago.  BTW, I had created a document a year ago to detail how the changes were being made so if someone else (again yeah right) had to make similar changes, this hypothetical person might learn from my pain but I missed this little gem....

Here is the dialog to Set database Location for a crystal report.
Nothing here to dislike.... oh wait
If you highlight database connection in the top tree and the then highlight the live connection in the lower tree and then click Update.  Works fine.

Reverse the order, highlight the live connection and the connection you want to change and then click Update - Does not work.....

Verdict: This sucks.....

Create and populate a DataTable manually.

Create and populate a DataTable manually.

DataTable table = new DataTable();
table.TableName = "SelectedMinOcc";
table.Columns.Add("ID", typeof(Int32));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("LAT", typeof(double));
table.Columns.Add("LONG", typeof(double));

foreach (DataGridViewRow row in dgvSearchResults.SelectedRows)
{
table.Rows.Add(Convert.ToInt32(row.Cells["ID"].Value)
, Convert.ToString(row.Cells["Name"].Value)
, Convert.ToDouble(row.Cells["LAT"].Value)
, Convert.ToDouble(row.Cells["LONG"].Value)
);
}

Read Windows Registry settings for VB and VBA Projects

My continuing exercise in comparative languages VB6 vs C#

Read Windows Registry settings for VB and VBA Projects

VB6

GetSetting("DRC", "Config", "DRC_CD")

C#

using Microsoft.Win32;

RegistryKey DRCKEY = Registry.CurrentUser.OpenSubKey("Software\\VB and VBA Program Settings\\DRC\\Config");
object CDPath = DRCKEY.GetValue("DRC_CD");

Here is something I have not done in awhile - Cmd batch file

Recently, I have to simply deployment of a services patch with a batch file to uninstall current servce.exe (written in 1.1 .ner framework

: DATE: 11/17/2020 ENG: GLK  
: This batch file uninstalls the Fusion RIS Delivery Service that supports Esker 3.5 FP1

echo.
echo uninstall existing Fusion RIS Delivery Service
%WINDIR%\Microsoft.NET\Framework\v1.1.4322\InstallUtil.exe DeliveryService.exe /u

echo.
IF EXIST EDDSAT.dll echo Unregistering EDDSAT.dll
IF EXIST EDDSAT.dll %WINDIR%\SYSTEM32\RegSvr32.exe EDDSAT.dll /u 

And then  I used this batch to register the service again. 

 : DATE: 11/17/2020 ENG: GLK 
: This batch file installs the new Fusion RIS Delivery Service that supports Esker 5.0

echo.
echo installing new Fusion RIS Delivery Service
%WINDIR%\Microsoft.NET\Framework\v2.0.50727\InstallUtil.exe DeliveryService.exe

echo.
echo Registering EDDSAT.dll
%WINDIR%\SYSTEM32\RegSvr32.exe EDDSAT.dll

 

Host file location in Windows

Try to locate any existing hosts file on your computer: 
 

Windows 95/98/Me c:\windows\hosts

Windows NT/2000/XP Pro  c:\winnt\system32\drivers\etc\hosts

Windows XP Home c:\windows\system32\drivers\etc\hosts

 

(you may need administrator access for Windows NT/2000/XP)

Change Context menu items on the fly

The example using the contextMenuStrip on a winforms form.
Purpose is to use a single contextStripMenu but have the list of menu items change based in the control associated with.

cmsMap = ContextMenuStrip control which is as the contextmenu for tabcontrol1 and drcMapControl1.

void cmsMap_Opening(object sender, CancelEventArgs e)
{
   //Set all visible to all menu items to false
   (sender as ContextMenuStrip).Items["moveTabsToTopToolStripMenuItem"].Visible = false;
   (sender as ContextMenuStrip).Items["moveTabsToBottomToolStripMenuItem"].Visible = false;
   (sender as ContextMenuStrip).Items["addLabelToolStripMenuItem"].Visible = false;

  //Based on the sender.SourceControl make specific menu items visible.
  switch ((sender as ContextMenuStrip).SourceControl.Name)
  {
    case "drcMapControl1":
     (sender as ContextMenuStrip).Items["addLabelToolStripMenuItem"].Visible = true;
        break;
    case "tabControl1":
     (sender as ContextMenuStrip).Items["moveTabsToTopToolStripMenuItem"].Visible = true;
     (sender as ContextMenuStrip).Items["moveTabsToBottomToolStripMenuItem"].Visible = true;
     switch (tabControl1.Alignment)
     {
         case TabAlignment.Bottom:
        (sender as ContextMenuStrip).Items["moveTabsToBottomToolStripMenuItem"].Visible = false;
               break;
         case TabAlignment.Top:
         (sender as ContextMenuStrip).Items["moveTabsToTopToolStripMenuItem"].Visible = false;
                break;
         default:
               break;
    }
        break;
    default:
         break;
    }
}

List all constraints for a table in SQL 2005/2008

--Find Constraints for a table
SELECT *
FROM sys.all_objects
WHERE type in ('F','PK') and parent_object_id in (
SELECT object_id from sys.all_objects WHERE Type= 'U' and name= 'tbPatients')

Query tables containing same column name

I use this type of query all the time...

SELECT o.name, o.xtype, c.name, c.length, c.type
FROM sysobjects o
INNER JOIN syscolumns c on o.id = c.id
WHERE ((c.name ='CPTCode' AND o.xtype='U') OR o.name = 'tbcdCPTCodes' and c.name ='Code')
ORDER by o.name

Read SQL blob as byte array and save to File

This is an example that fetches a report binary stored as a blob in a SQL server table.

tbBillingPaperReport: SQL Server table where report binary is stored in column Report file.
Writes files root folder the current user document and settings folder
example:  C:\Documents and Settings\Gkindel\

using System;
using System.IO;
using System.Data.SqlClient;

private const string _SQLConnectionString = "Data Source={0};Initial Catalog={1};User ID={2};Password={3};";

SqlConnection db = new SqlConnection(string.Format(_SQLConnectionString,"ServerName", "DBName", "user", "password"));

db.Open();

SqlCommand cmd = new SqlCommand("SELECT ReportID, FileVersion, ReportName, ReportFile FROM tbBillingPaperReport", db);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataTable table = new DataTable();
adapter.Fill(table);

string savePath = Directory.GetParent(Environment.GetFolderPath(Environment.SpecialFolder.Personal)).ToString();                    

foreach (DataRow row in table.Rows)
{
     byte[] report = row["ReportFile"] as byte[];
     string filename = string.Format("0}\\{1}.rpt",savePath,row["ReportName"]);                           

     BinaryWriter writer = new BinaryWriter(File.Open(filename,FileMode.Create));
     writer.Write(report);
     writer.Close();
}

Strawberry Ho Ho Cake

Here is the cake Joanne made for girls for their 7th birthday.
And yes Brian is was as good as it looks!

The Gates of Hell located in Kyzylgar, Turkmenistan | Atlas Obscura | Curious and Wondrous Travel Destinations

Excerpt from:
The Gates of Hell located in Kyzylgar, Turkmenistan | Atlas Obscura | Curious and Wondrous Travel Destinations
In the hot, expansive Karakum desert in Turkmenistan, near the 350 person village of Derweze, is a hole 328 feet wide that has been on fire, continuously, for 38 years. Known as the Darvaza Gas Crater or the "Gates of Hells" by locals, the crater can be seen glowing for miles around.
The hole is the outcome not of nature but of an industrial accident. In 1971 a Soviet drilling rig accidentally punched into a massive underground natural gas cavern, causing the ground to collapse and the entire drilling rig to fall in. Having punctured a pocket of gas, poisonous fumes began leaking from the hole at an alarming rate. To head off a potential environmental catastrophe, the Soviets set the hole alight. The crater hasn't stopped burning since.
Though little information is available about the fate of the Soviet drilling rig, presumably it is still down there somewhere, on the other side of the "Gates of Hell."

Largest Hailstones ever to fall in US

Biggest hail ever fell in Vivian South Dakota on 23 July 2010.

A quick way to copy a table row

From http://www.devx.com/tips/Tip/32233

Because I keep looking this up. 

A Quick Way to Copy DataRow
Instead of copying DataRow column by column, the following code copies data in one line from the source to the destination row:

DataTable dtDest = new DataTable();
dtDest = dsActivity.Tables[0].Clone();
foreach(DataRow dr in dsSrc.Tables[0].Rows)
{
DataRow newRow = dtDest .NewRow();
newRow.ItemArray = dr.ItemArray;
dtDest.Rows.Add(newRow);
}

Note: The ImportRow method does the same thing, except that the RowState of source is preserved in the destination, whereas NewRow sets RowState to Added.

Generate Script to drop all foreign key constraints in a database

From http://www.sqlservercentral.com/scripts/Replication/31530/

SET NOCOUNT ON

print 'USE ' + DB_NAME()
print ''

-- Generate Drops for All Foreign Keys in Database
print '-- Drop Foreign Keys'
print ''

select distinct 'ALTER TABLE [dbo].[' + object_name(fkeyid) +
'] DROP CONSTRAINT ' + object_name(constid) +
CHAR(13) + CHAR(10) + 'go'
from sysforeignkeys
go

Employer is closing Hudson, Ohio

Hello All:

  I found yesterday that the office I work is closing.  I have an option to relocation to Wisconsin.
  So I am currently looking a position where my background in SQL Server, MS Access, VB6 and C# could be useful.

  Please pass along any opportunities you think I might be interested in.

Thanks
Gary Kindel

Never use Windows FixBoot!

I recently had a slave IDE ntfs drive parition go alittle south where Windows XP recognized the entire drive but saw it as Raw formatted partition.  Unfortunately, I did not realize until it was too late that fixBoot from XP recovery console creates a FAT12 Boot partition.  So now, I hosed up a perfectly good hard drive with 70 GB of family photos. 

Looking through my   software collection, I had an old copy spinrite 3.1 that had helped me recover files from a FAT32 drive 5 years ago.  I purchased and downloaded Spinrite 6.0 from Home of Gibson Research Corporation .  Spinrite failed to recognize my ~190 GB IDE drive and saw it as an oversided 128 GB drive.  Too nervous to proceed, I downloaded a demo copy of Partition Table Doctor from ptdd.com.  It saw the drive correctly as a 190 GB NTFS drive with a corrupted boot sector.  but of course the demo version would not fix the problem, had to purchase the full version.

But when I followed the link to purchase it, I found it was no longer being sold.

Partition Table Doctor Personal Edition	Single License	End of Marketing EASEUS aims to offer more professional data recovery software Data Recovery Wizard other than Partition Table Doctor now!
Partition Table Doctor Unlimited Edition	Unlimited License
Partition Recovery For Windows (Partition Table Doctor Windows version)	Single License
Partition Recovery Bootable CD (Partition Table Doctor DOS version)	Single License

Rats!  But I discovered that is was bundled and sold here:

Personal Data Recovery Package

Contains Partition Table Doctor and Data Recovery Wizard to recover data from disaster suffering partition or disk. The final tools for protecting your data from being lost. Single License.

Buy Now
Read More

It cost me $90 instead of just $45 but the software did work!  and I did get a refund for spinrite 6.0.

And of course, I immediately backed up my 70 Gbs of photos with only losing 3-4 photos.

 

So, don't use FixBoot!

 

Updating MS Access Linked Tables - A better solution

I added DAO 3.6 reference to my project and used the following code

Decide to go this route because MS Jet 4.0/DAO 3.6 are part of the operating system and are in Windows 2000, XP, Vista and Windows 7. They are updated by Windows Update and the security patches are applied as appropriate.

        public static void RelinkDAOTables(string MDBfile, string filepath, string sql)
        {
            DataTable linkedTables = TableFromMDB(MDBfile, sql);

            dao.DBEngine DBE =new dao.DBEngine();
            dao.Database DB = DBE.OpenDatabase(MDBfile, false, false, "");
            foreach (DataRow row in linkedTables.Rows)
            {
                dao.TableDef table = DB.TableDefs[row["Name"].ToString()];
                table.Connect = string.Format(";DATABASE={0}{1} ;TABLE={2}", filepath, row["database"],row["LinkedName"]);
                table.RefreshLink();
            }      
           
        }

Updating MS Access Linked Tables

Currently, I am trying to convert a VB6 application that uses a single MS Access database with links to 7 others  access databases using DAO linked tables to C# and ADO.NET.  For now, I am want to keep MS access for data storage.  One of the things I need is code to modify linked tables in MS Access database using C#.  Here is what I found so far:


from www.johnsoer.com -  Updating MS Access Links

ADODB.Connection Con = new ADODB.Connection();
ADOX.Catalog Cat = new ADOX.Catalog();

Con.Open(connectionString, null, null, 0);
Cat.ActiveConnection = Con;
Cat.Tables[LinkedTableName].Properties["Jet OLEDB:Link Datasource"].Value = LinkedDatabaseLocation;
Con.Close();

Query Analyzer

Tutorals

• http://www.quackit.com/sql_server/tutorial/sql_query_analyzer.cfm
• http://www.uncg.edu/cmp/DatabaseProjects/Query%20Analyzer/QueryAnalyzer.htm

Short-cut keys

CTRL-SHIFT-F2         -- Clear all bookmarks. 
CTRL+F2               -- Insert or remove a bookmark (toggle). 
F2                    -- Move to next bookmark. 
SHIFT+F2              -- Move to previous bookmark. 
ALT+BREAK             -- Cancel a query. 
CTRL+O                -- Connect. 
CTRL+F4               -- Disconnect. 
CTRL+F4               -- Disconnect and close child window. 
ALT+F1                -- Database object information. 
CTRL+SHIFT+DEL        -- Clear the active Editor pane. 
CTRL+SHIFT+C          -- Comment out code. 
CTRL+C or Ctrl+Insert -- Copy
CTRL+X or Shift+Del   -- Cut
SHIFT+TAB             -- Decrease indent. 
CTRL+DEL              -- Delete through the end of a line in the Editor pane. 
CTRL+F                -- Find. 
CTRL+G                -- Go to a line number. 
TAB                   -- Increase indent. 
CTRL+SHIFT+L          -- Make selection lowercase. 
CTRL+SHIFT+U          -- Make selection uppercase. 
CTRL+V or Shift+Insert -- Paste. 
CTRL+SHIFT+R          -- Remove comments. 
F3                    -- Repeat last search or find next. 
CTRL+H                -- Replace. 
CTRL+A                -- Select all. 
CTRL+Z                -- Undo. 
F5 or Ctrl + E        -- Execute a query. 
F1                    -- Help for Query Analyzer. 
SHIFT+F1              -- Help for the selected Transact-SQL statement. 
F6                    -- Switch between query and result panes. 
Shift+F6              -- Switch panes. 
CTRL+W                -- Window Selector. 
CTRL+N                -- New Query window. 
F8                    -- Object Browser (show/hide). 
F4                    -- Object Search. 
CTRL+F5               -- Parse the query and check syntax. 
CTRL+P                -- Print
CTRL+D                -- Display results in grid format. 
CTRL+T                -- Display results in text format. 
CTRL+B                -- Move the splitter. 
CTRL+SHIFT+F          -- Save results to file. 
CTRL+R                -- Show Results pane (toggle). 
CTRL+S                -- Save
CTRL+SHIFT+INSERT     -- Insert a template. 
CTRL+SHIFT+M          -- Replace template parameters. 
CTRL+L                -- Display estimated execution plan. 
CTRL+K                -- Display execution plan (toggle ON/OFF). 
CTRL+I                -- Index Tuning Wizard. 
CTRL+SHIFT+S          -- Show client statistics 
CTRL+SHIFT+T          -- Show server trace. 
CTRL+U                -- Use database

Table Variable Example

DECLARE @ProductTotals TABLE
(
ProductID int,
Revenue money
)

VB6 Form_queryUnload Event unloadmode argument

From Microsoft

Visual Basic Reference

QueryUnload Event

See Also    Example    Applies To
Occurs before a form or application closes. When an MDIForm object closes, the QueryUnload event occurs first for the MDI form and then in all MDI child forms. If no form cancels the QueryUnload event, the Unload event occurs first in all other forms and then in an MDI form. When a child form or a Form object closes, the QueryUnload event in that form occurs before the form's Unload event.

Syntax

Private Sub Form_QueryUnload(cancel As Integer, unloadmode As Integer)
Private Sub MDIForm_QueryUnload(cancel As Integer, unloadmode As Integer)
The QueryUnload event syntax has these parts:

Part	Description
cancel	An integer. Setting this argument to any value other than 0 stops the QueryUnload event in all loaded forms and stops the form and application from closing.
unloadmode	A value or constant indicating the cause of the QueryUnload event, as described in Return Values.

Return Values

The unloadmode argument returns the following values:

Constant	Value	Description
vbFormControlMenu	0	The user chose the Close command from the Control menu on the form.
vbFormCode	1	The Unload statement is invoked from code.
vbAppWindows	2	The current Microsoft Windows operating environment session is ending.
vbAppTaskManager	3	The Microsoft Windows Task Manager is closing the application.
vbFormMDIForm	4	An MDI child form is closing because the MDI form is closing.
vbFormOwner	5	A form is closing because its owner is closing.

Get file name from path

I cannot remember this....

 System.IO.Path.GetFileName(filepath)

its IO.Path not IO.Directory or IO.File

The Visual Basic UFL that implements this function is missing (or U2lcom.dll is missing).

VB DLL which contains the implementation of the User Defined Function was also installed/registered properly.

For example:   CRUFL_RIS.dll used by FusionRIS

 

Detect Database Blocking Issue with SQL Server Dynamic Management View

Great example how to create a block and then query for it.    (original article)

Detect Database Blocking Issue with SQL Server Dynamic Management View

By : Kasim Wirama, MCITP, MCDBA

DBA has responsibility to maintain database from backup-restore, data integrity until smooth database daily operation. Most common problem regarding to database operation especially busy database activity is blocking and deadlocking. For this posting, I show you how to detect blocking on your SQL Server by using DMV (Dynamic Management View).

DMV has been introduced since SQL Server 2005. DMV for detecting blocking is sys.dm_tran_locks. Many advantage you use the DMV over sp_lock, sp_who2, sp_who and the like. Its advantages are that the DMV could show you resource location and you could export resultset for further analysis.

Back to sys.dm_tran_locks, you can detect blocking by joining to the DMV itself as query shown below
SELECT
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL))

To see blocking scenario you can create sample table below :

USE tempdb
GO
CREATE TABLE test (cola varchar(10));
GO
INSERT INTO test VALUES (‘a’);

Open first connection (in SSMS open new window designer), and issue then execute query below :
USE tempdb
GO
BEGIN TRAN
UPDATE test SET cola = ‘b’;
WAITFOR DELAY ’00:10:00’;
ROLLBACK TRAN

Open second connection, issue the below :

USE tempdb
GO
SELECT * FROM test;

Execute the DMV then it display 2 records, to display object name getting involved in blocking, you can issue query below :

SELECT
db_name(l.resource_database_id) as [database name],
CASE l.resource_type
  WHEN ‘object’ then object_name(l.resource_associated_entity_id)
  WHEN ‘database’ then ‘databae’
  ELSE
    CASE WHEN l.resource_database_id = db_id() THEN
       (SELECT object_Name(object_id) FROM sys.partitions WHERE hobt_id = l.resource_associated_entity_id)
    ELSE NULL
    END
END,
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL));

If you need to know what queries getting involved with blocking you can get query information from sys.dm_exec_requests and DMV function : sys.dm_exec_sql_text. Here is the DMV query :

SELECT
db_name(l.resource_database_id) as [database name],
CASE l.resource_type
  WHEN ‘object’ then object_name(l.resource_associated_entity_id)
  WHEN ‘database’ then ‘databae’
  ELSE
    CASE WHEN l.resource_database_id = db_id() THEN
       (SELECT object_Name(object_id) FROM sys.partitions WHERE hobt_id = l.resource_associated_entity_id)
    ELSE NULL
    END
END,
( SELECT t.[text]
  FROM sys.dm_exec_requests r
  CROSS APPLY sys.dm_exec_sql_text (r.sql_handle) AS t
  WHERE r.session_id = l.request_session_id
),
l.*
FROM sys.dm_tran_locks as l
JOIN sys.dm_tran_locks as l1
ON l.resource_associated_entity_id = l1.resource_associated_entity_id
WHERE l.request_status <> l1.request_status
AND
( l.resource_description = l1.resource_description OR (l.resource_description IS NULL AND l1.resource_description IS NULL)).

Slow Text queries in SQL Server 2008

From StackOverflow.Net   (http://blog.stackoverflow.com/2008/10/adde-a-second-server/)

   From the article:

      While many queries are faster under SQL Server 2008, and the tooling is dramatically and indisputably better (intellisense for queries!), there is one downside for us: SQL Server 2008 is slower at full-text search operations than SQL 2005. That’s kind of a bummer because we rely heavily on full text. I saw a glimpse of this in my initial testing, when I had both servers up side-by-side with the same data, running the same queries, to see how much faster it would be (new versions are supposed to be faster, right?). My experiments showed that any full-text queries I tried were inexplicably slower on 2008, but we thought it had to do with different query plans, and was something we could work around.

   StackOverflow admin tried the workaround fix shown below but haven’t seen much improvement .

  

http://sqldev.wordpress.com/2008/09/16/sql-server-2008-full-text-slowness/

  

From the article:

It appears to happen if:

• You have AUTO tracking on your full text indexes; although we changed it to manual and still had this issue.
• You experience Full-text queries taking a long time to execute; normally when updates are happening at the same time so you might only see this in production.
• One or more of your queries are complicated or take some time to complete.

You can check to see if your system is slow due to this by:

• SELECT * FROM sys.dm_os_wait_stats statement , it shows very high wait times some of the locks.
• Running Sp_who2; it should consistently show that the full-text gather is blocking full-text queries and, in turn, is being blocked by the queries.

The current work around for this issue is to use a global trace flag microsoft kindly enabled on the RTM build.  To use this, type the following Transact-SQL statement:

DBCC TRACEON (7646, -1)

This has a minor side-effect.. which may or may not be important in your organisation. Certainly in ours, it is not. Your full text result may be ever so slightly out of date – e.g. you might return or not return a particular document from the index. However, this issue relates to the dirtyness of an index in terms of 10’s of milliseconds. Important? Unlikely….

         

    I tested the workaround for the fulltext query fix.

    On a large client DB, I ran the following query:

UPDATE tbExams

SET BusinessNotes=''

WHERE ExamID=

  Several times and the average execution time 3-4 secs

I applied DBCC TRACEON (7646, -1)

 

And then I ran the query again against different exams and the execution time dropped to 0-1 secs

Additional:  SQL Server 2008 must have AWE enabled to use all of the available memory the OS reports.
                   I assume this is necessary for 32/64 bit.

SQL SERVER – Get Last Running Query Based on SPID

Article link

SQL SERVER – Get Last Running Query Based on SPID

July 19, 2009 by pinaldave

We often need to find the last running query or based on SPID need to know which query was executed. SPID is returns sessions ID of the current user process. The acronym SPID comes from the name of its earlier version, Server Process ID.

To know which sessions are running currently, run the following command:

SELECT @@SPID
GO

In our case, we got SPID 57, which means the session that is running this command has ID of 57.

Now, let us open another session and run the same command. Here we get different IDs for different sessions.

In our case, we got SPID 61. Please note here that this ID may or may not be sequential.

In session with SPID 61, we will run any query. In session with SPID 57, we will see which query was run in session with SPID 61.

Let us run a simple SELECT statement in session with SPID 61 and in session with SPID 57 run the following command.

DBCC INPUTBUFFER(61)
GO

Now, here in DBCC command we have passed the SPID of previous session; we will see the text below. The following image illustrates that we get the latest run query in our input buffer.

There are several ways to find out what is the latest run query from system table sys.sysprocesses.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO


The following image portrays that we get the latest run query in our input buffer.

Please note that in any session there may be multiple running queries, but the buffer only saves the last query and that is what we will be able to retrieve.
There is one more way to achieve the same thing – using function fn_get_sql

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 61
SELECT TEXT
FROM ::fn_get_sql(@sqltext)
GO

All the three methods are same but I always prefer method 2 where I have used sys.sysprocesses.
Today, we have explored a very simple topic. Let me know if you find it useful.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Using linked servers between SQL 2008 (64bit) and SQL 2000 (32bit)

***IMPORTANT: If the client using DSI Ellgence will require a linked server connection from SQL Server 2008 (x64) to SQL Server 2009 (x86 32bit)
You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server.  

If this occurs: Please review and follow the steps provided  in Microsoft Knowledge base article: http://support.microsoft.com/kb/906954 
To to resolve this problem, manually run the Instcat.sql script that is included with SQL Server 2000 SP3 or SP4 on the 32-bit SQL Server 2000 server.

Using PropertyGrid in C#

Stupidly easy.

I am going to stop creating generic UI to display data and use the PropertyGrid as my new default to display object properties

PropertyGrid1.SelectedObject = myObject;

Arrays in C#

Switching between C# and VB6 totally messing up my thinking about arrays.....

Examples of C# Arrays

int[] numbers = new int[5] {1, 2, 3, 4, 5};
string[] names = new string[3] {"Matt", "Joanne", "Robert"};

2010 Winter Hike at Hocking Hills State Park

On saturday, a couple friends and I attended the 45th annual winter hike in Hocking Hills State Park. This is by far my favorite spot in Ohio. I had no idea how many people were going to be at the event -- over 4500 hikers. Here is the line to start the hike at ~10am.The hike was 6 miles from Old Man' Cave visitor, past Cedar Falls to Ash Cave.
The path ran along the backside of the campground along Rose Lake shown.The half way point was Cedar Falls.The Friends of Hocking Hills and the Logan Kiwanas provided a bean soup lunch complete with miffin and hot chocolate.The hike ended at Ash Cave which is the largest rock shelter in Ohio.
The ice mound at the base of the small waterfall was amazing!Forunately there were free shuttle buses available for the trip back the car!

Coolest Mars Image Ever!

From Discover Magazine

To the dust cloud in the central lower left quadrant. Estimated to be 10 meters across. Makes the tree like sand structures at least 30 meters tall!

Append to a text blob in SQL 2008

From StackOverflow
Wish I could remember this without google...

update 
  tablename
set
  fieldname = convert(nvarchar(max),fieldname) + 'appended string'

SharpMap patch

Something novel, fixed my first bug in open source software: SharpMap
Adding this control as part of my DRC rewrite in C#


Index: Trunk/SharpMap/Data/Providers/DbaseReader.cs
===================================================================
--- Trunk/SharpMap/Data/Providers/DbaseReader.cs    (revision 61030)
+++ Trunk/SharpMap/Data/Providers/DbaseReader.cs    (working copy)
@@ -417,7 +417,16 @@
         {
             baseTable = new FeatureDataTable();
             foreach (DbaseField dbf in DbaseColumns)
-                baseTable.Columns.Add(dbf.ColumnName, dbf.DataType);
+            {
+                try
+                {
+                    baseTable.Columns.Add(dbf.ColumnName, dbf.DataType);
+                }
+                catch(DuplicateNameException)
+                {
+                    baseTable.Columns.Add(string.Format("{0}_1",dbf.ColumnName), dbf.DataType);
+                }
+            }
         }

         internal FeatureDataTable NewTable