Category: SQL

NET : Create Dynamic SqlCommand Parameters

string IDs = “2055,1644,5889”;
List<string>
IDNumbers = IDs.Split(‘,’).ToList<string>();
StringBuilder sb = new
StringBuilder();
cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
sb.Append(“SELECT
* FROM TABLE WHERE (“);
//build parameters
foreach (string idNumber in IDNumbers)
{
sb.Append(“TABLE.ID
= “ + “:parm” +
idNumber.ToString());
sb.Append(” OR
“);
}
sb.Remove(sb.Length – 3, 3);
sb.Append(“)
“);
foreach (string idNumber in IDNumbers)
{
cmd.Parameters.Add(new
OracleParameter(“parm” +
idNumber.ToString(), idNumber));
}
cmd.ExecuteNonQuery();

SQL : Get Last Update TimeStampe For A Table

I have some Oracle tables that have no datestamp on them
that would display the last update of that row. 
Here is a query for that:
SELECT SCN_TO_TIMESTAMP( ORA_ROWSCN )
FROM YOURTABLE;
Sample output would look something like:
4/16/2014 9:01:26.000000000 AM
Of course, you can add a where clause to determine update
for your particulars.
For more information: SCN_TO_TIMESTAMP

SQL Server : Using Quotes In Linked Server Queries

Doing an OpenQuery across a linked server can get ugly very fast and it is often best to avoid them.
The basic syntax of an OpenQuery is :
SELECT * FROM OPENQUERY(YourLinkedServerName, ‘SELECT FIELD1, FIELD2 FROM YOURTABLE’)
From a coding perspective, this gets ugly if you have to incorporate string parameters; thus, utilizing many quotes.  For Example:
SELECT * FROM OPENQUERY(YourLinkedServerName, ‘SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = ”ID105” ‘)
Microsoft made this easier with the built-in stored procedure that allows you to avoid multi-layered quotes:  sp_executesql
The above can become:
DECLARE @PARAM char(5)
SELECT  @PARAM = ‘CA105’
EXEC YourLinkedServer.master.dbo.sp_executesql
N’SELECT FIELD1, FIELD2 FROM YOURTABLE WHERE FIELD1 = @p1′,
N’@p1 char(5)’,
@PARAM
But, what if your WHERE clause becomes:
WHERE FIELD2 in (”ID105”,”ID107”,”ID109”,”ID112”,”ID116”,
”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z”,”ID103”,”ID121”,”ID401”,”ID403”,”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,
”ID413”)
There are multiple ways to go about this but here is one cheap method for dealing with the quotes:
DECLARE @TSQL NVARCHAR(2000);
DECLARE @CODES NVARCHAR(500);
DECLARE @OPENQUERY NVARCHAR(100);
DECLARE @LINKED_SQL NVARCHAR(2700)
SET @CODES = ”’ID105”,”ID107”,”ID109”,”ID112”,”ID116”,
”ID119”,”ID400”,”ID404”,”ID101”,”ID102”,”ID115”,”ID106”,”ID111”,”ID117”,”ID412”,”654Z ”,”ID103”,”ID121”,”ID401”,”ID403”,”ID407”,”ID108”,”ID113”,”ID114”,”ID120”,”ID123”,”ID405”,”ID409”,”ID100”,”ID110”,”ID124”,”ID408”,”ID104”,”ID118”,”ID406”,
”ID413”’;
SELECT @TSQL = ‘SELECT … WHERE FIELD2 IN (‘+ @CODES + ‘)’  
— CLEAN UP THE QUOTES FOR THE OPENQUERY
—————————————————–                 
SET @TSQL = REPLACE(@TSQL, ””,”””)  ;
SET @TSQL = @TSQL + ”’)’;
SET @OPENQUERY = ‘SELECT * FROM OPENQUERY(GHDB, ”’;
SET @LINKED_SQL = (@OPENQUERY+@TSQL)
EXEC sp_executesql @LINKED_SQL

SQL: Set Up Email Alert When Partition Size Reaches Threshold

-- =============================================
-- Query to determine if a partition's size may
-- be reaching a critical maximum
-- Note: Currently uses SP_SEND_DBMAIL instead of XP_SENDMAIL
-- =============================================

DECLARE @DbName VARCHAR(25)
DECLARE @sql VARCHAR(100)
DECLARE @ThresholdCountMsg INT
DECLARE @Subject VARCHAR(100)

SET @DbName = DB_NAME(); --get name of current database to use in email alert

SET @Subject = 'Partitioning Threshold Alert On ' + @DbName + '!'

SET @ThresholdCountMsg = 14000000;  -- set partition row count

IF OBJECT_ID('tempdb..##yourPartitionSizeCheck') IS NOT NULL
BEGIN
DROP TABLE ##yourPartitionSizeCheck
END;
 

--use global temp table for email alert

CREATE TABLE ##yourPartitionSizeCheck
(
table_name varchar(20),
partition_number int,
row_count int
);

 

INSERT INTO ##yourPartitionSizeCheck

SELECT b.name AS 'table_name',
partition_number,
a.[rows] AS 'row_count'
FROM sys.partitions AS a INNER JOIN
sys.objects AS b ON a.object_id = b.object_id
INNER JOIN sys.schemas AS c On c.schema_id = b.schema_id              
WHERE LOWER(b.name) = 'yourPartitionedTableName'     
and a.index_id < 2
and a.[rows] > @ThresholdCountMsg; 

IF @@ROWCOUNT > 0

EXEC MSDB.DBO.SP_SEND_DBMAIL
@profile_name = 'YourSqlmail',
@recipients = 'your.email@yourcompany.com',
@subject = @Subject,
@body = 'The following partitions may be approaching performance degradation: ',   

@execute_query_database = @DbName,       

@query = 'SELECT table_name, partition_number, row_count FROM ##yourPartitionSizeCheck';