Thursday 25 December 2008

ORA-12154 when using EF Oracle Provider

When using EFOracleProvider, ORA-12154 might occur if there are 2 Oracle clients installed on your machine. The problem is that EFOracleProvider is looking for for tnsnames.ora in client_2 folder instead of client_1. The workaround is copy tnsnames.ora to client_2 folder as well.

Thursday 11 December 2008

Find stored procedures/functions that contain some text in SQL Server

DECLARE @text AS nvarchar(4000)
SET @text = '%text to find%'
SELECT name, xtype FROM sysobjects o
  INNER JOIN syscomments c ON o.id = c.id
  WHERE c.text like @text


* This source code was highlighted with Source Code Highlighter.

Create CLR function in SQL Server 2005

-- Drops existing aggregate function, remove this line if not needed
DROP AGGREGATE StringConcat
GO
-- Drops existing assembly, remove this line if not needed
DROP ASSEMBLY SqlServerHelper
GO
CREATE ASSEMBLY SqlServerHelper from '\\AssemblyPath\SqlServerHelper.dll'
WITH PERMISSION_SET = SAFE
GO
EXEC sp_configure 'show advanced options' , '1';
go
reconfigure;
go
EXEC sp_configure 'clr enabled' , '1'
go
reconfigure;

CREATE AGGREGATE StringConcat (@value varchar(4000)) RETURNS nvarchar(4000)
EXTERNAL NAME SqlServerHelper.Concatenate

-- Sample execution of CRL aggregate method
select top 10 dbo.StringConcat(ExternalCode)
from decodeValues group by DirectionCode

* This source code was highlighted with Source Code Highlighter.

Find foreign key of table in SQL Server

DECLARE @ObjectName varchar(50)
SET @ObjectName = 'Customers'

SELECT f.name AS ForeignKey,
  OBJECT_NAME(f.parent_object_id) AS TableName,
  COL_NAME(fc.parent_object_id,
  fc.parent_column_id) AS ColumnName,
  OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,
  COL_NAME(fc.referenced_object_id,
  fc.referenced_column_id) AS ReferenceColumnName
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
  ON f.OBJECT_ID = fc.constraint_object_id
WHERE OBJECT_NAME(f.referenced_object_id) = @ObjectName


* This source code was highlighted with Source Code Highlighter.