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.