T-SQL and CLR types do not match

As you know in SQL Server 2005 you can reference to .NET classes doing some operations that in T-SQL is realy hard to perform. For example if you need to format very long and 'complicated' string value , so by using .NET String method you can easily achive it. But I'd like to show you another example actually posted by Itzik Ben-Gan on his website ,so I just modified his C# code to VB.NET that is invoking the ToString method of the SqlDateTime datatype with a format string.

Imports Microsoft.SqlServer.Server
Imports System.Data.SqlClient
Public Class Valid
"<"SqlFunction(DataAccess:=DataAccessKind.Read, IsDeterministic:=True)">" _
Public Shared Function fn_format_datetime(ByVal dt As SqlTypes.SqlDateTime, _
ByVal formatstring As SqlTypes.SqlString) As String
If dt.IsNull Or formatstring.IsNull Then
Return formatstring.IsNull()
Else
Return dt.Value.ToString(formatstring.Value)
End If

End Function

End Class

I assume you are familiar with .NET technology , so I build a solution and try to reference to this function within my database.I created ASSEMBLY without problem but when I tried to create a function I got the error "CREATE FUNCTION for "fn_format_datetime1" failed because T-SQL and CLR types for return value do not match."

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as VARCHAR(100))RETURNS VARCHAR(100)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

As you see T-SQL datatype VARCHAR(n) does not match .NET STRING class. I should use NVARCHAR(n) datatype to get it working, did not know it.

CREATE ASSEMBLY dateformat1 FROM 'C:\dateformat1 .dll'
with permission_set = SAFE
CREATE FUNCTION fn_format_datetime(@dt as datetime,
@formatstring as NVARCHAR(4000))RETURNS NVARCHAR(4000)
AS EXTERNAL NAME dateformat1.[dateformat1.classname].fn_format_datetime

--Usage
SELECT
dbo.fn_format_datetime(GETDATE(), 'yyyyMMdd'),
dbo.fn_format_datetime(GETDATE(), 'yyyymmdd');

Trigger's definition

I have had a clinet who wanted to script out the source of triggers on database level, so I'm talking about SQL Server 2005. Well as you probably know you can easily script the objects by using Tasks ---> Generate Scripts.. and moreover if you installed SP2 you will be able to script the object per file.

I would like to show you more simple way. You join two system tables on object_id column where sys.sql_modules table has a definition column which my client wanted to get.The WHERE condition restricts the ouptupt for triggers that were designed on database level.

SELECT definition FROM sys.sql_modules sm
INNER JOIN sys.triggers st ON
sm.object_id=st.object_id
WHERE parent_class_desc ='DATABASE'

Is Linked Server available?

In SQL Server 2000 we have been checking an availability of linked server by using
xp_cmdshell along with 'ping' command.It worked fine but what does SQL Server 2005 propose to us?
There is a system stored procedure (sp_testlinkedserver )that accepts name of linked server as a parameter and returns the status of server.

Take a look at the script below.

DECLARE @srvr NVARCHAR(128), @statusval INT;
SET @srvr = 'Linked_Server';
BEGIN TRY
EXEC @statusval = sp_testlinkedserver @srvr;
END TRY
BEGIN CATCH
SET @statusval = SIGN(@@ERROR);
END CATCH;
IF @statusval <> 0
RAISERROR('Unable to connect to linked server. Try later!', 16, 2 );

Enabling AWE on Cluster environment

I know many people ask this question. Recently I had a client wants me to enable AWE on the cluster which has two nodes (Active/Passive). He had only one instance of SQL Server 2000 (SP4) and 8 GB RAM and /3GB in the Boot.INI.

1)
Add /PAE parameter to Boot.INI file. PAE is the added ability of the IA32 processor to address more than 4 GB of physical memory.
2)
You need to install hotfix on SQL Server instance from Microsoft (http://support.microsoft.com/kb/899761) that fixs the bug "Not all memory is available when AWE is enabled".
3)
Make sure that domain account SQL Server is running under is added to "Lock pages in memory" local group policy.
4)
Finally we configured 6 GB for SQL Server as it was business requirements.

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'awe enabled', 1
RECONFIGURE
GO
sp_configure 'max server memory', 6144
RECONFIGURE

5)Restart OS

Note: we need to run those steps on Active node where the SQL Server instance is running on. When it failed over but the binaries are loaded on all
nodes.

Linked servers and an Identity property

Someone in sqlserver.programming group asked nice question about getting an IDENTITY from the linked server. I came up with the following solution. I assume that there is a table on the linked server that has one column defined as indentity property.

CREATE FUNCTION dbo.fn_get_remoteidentity()
RETURNS INT
AS
BEGIN
RETURN(
SELECT row_id
FROM OPENQUERY(
[put here your server name],
'SET NOCOUNT ON;
INSERT INTO dbname..usertable DEFAULT VALUES
ROLLBACK;
SELECT SCOPE_IDENTITY() AS row_id;') AS Der)
END
Another question was how to insert an INDENTITY property in the table on remote(linked) server?
Well in that case you will need to build dynamic SQL along with SET IDENTITY_INSERT
command.

DECLARE @sql NVARCHAR(400)
SELECT @sql = '
CREATE TABLE test(id INT IDENTITY(1,1))
SET IDENTITY_INSERT test ON
INSERT test DEFAULT VALUES
SET IDENTITY_INSERT test OFF
SELECT id FROM test'
--Usage
EXEC [LinkedServer].master.dbo.sp_executesql @sql

Global stored procedure

I have my doubts that many of us have been used this system stored procedure(sp_MS_marksystemobject) to resolve the problems. But it exists and gives you an ability to return the data in the scope of selected database. Well, across number of databases having one table with the same structure and being in the scope of desired database you'll return the data (by using the following stored procedure) from the table located in desired database.To make the stored procedure to recognize the 'desired' data we use sp_MS_marksystemobject system stored procedure. BTW, it works as well as on SQL Server 2000 and on SQL Server 2005(SP2).

USE master
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('master')
go
CREATE PROC sp_test AS
SELECT * FROM test
GO
USE northwind
CREATE TABLE test (c1 VARCHAR(50))
INSERT test VALUES('northwind')
USE pubs
CREATE TABLE test(c1 VARCHAR(50))
INSERT test VALUES('pubs')
USE pubs
EXEC sp_test --returns 'master'
USE master
EXEC sp_MS_marksystemobject sp_test
USE pubs
EXEC sp_test --returns 'pubs'
USE northwind
EXEC sp_test --returns 'northwind'

Find specific data type of the column

Recently i was told to return tables which have FLOAT datatype defined on columns.The version of the product was SQL Server 2000 ,however it will work on SQL Server 2005 as well, so take a look at very very simple script.

SELECT columns.TABLE_NAME ,COLUMN_NAME
FROM information_schema.columns columns
JOIN information_schema.tables tables
ON tables.table_name = columns.table_name
WHERE tables.table_type = 'base table'
AND columns.data_type = 'float'
ORDER BY columns.table_name, columns.column_name