You can use Activator.CreateInstance() method to create objects from String type of Assembly Name. The following code stub demonstrate how to use that method.
namespace ActivatorTest
{
public interface IActBase
{
void Execute();
}
}
namespace ActivatorTest
{
public class ActChild1 : IActBase
{
public void Execute()
{
Console.WriteLine("Activator Child 1");
}
}
}
namespace ActivatorTest
{
public class ActChild2 : IActBase
{
public void Execute()
{
Console.WriteLine("Activator Child 2");
}
}
}
namespace ActivatorTest
{
public class Program
{
static void Main(string[] args)
{
const string AssemblyName = "ActivatorTest.ActChild2";
//The AssemblyName can be loaded through the configuration based
// on your requirement. Here its hard coded.
IActBase instance = (IActBase)Activator.CreateInstance(Type.GetType(AssemblyName));
instance.Execute();
}
}
}
For More Info:- http://www.knowdotnet.com/articles/activator_createinstance.html
Fixing SSIS Deployment Issues
SQL Server Integration Service (SSIS) is a platform for building enterprise level data integration and transformations solutions in Microsoft SQL Server. For example updating a SQL Database from an external source such as XML Source, Excel source and there are many more.
There are enough articles available on internet regarding SSIS package development and deployment, but hard to find exact solution regarding SSIS issues.
The purpose of this article is to provide solutions regarding few SSIS deployment issues in SQL Server 2005.
Issue 1:-
Error Message:-
The SQL Job fails with following Error:-
“The command line parameters are invalid. The step failed.”……
Description:-
This error occurred due to SQL Server 2005 Agent Bug. When you deployed your DTSX package as a SQL Server job and define DataSources [Ex:- DB Connection, Excel Connection] in SQL Job property window, it will automatically generate a Command Line string. [You can view it by Job Properties: - Edit "Step":- "General":- Select "Command line" Window]
When you try to run the SQL Job, the generated Command Line string cannot be recognized due to Command line String validation failure.
Solutions:-
1) The slashes should be backslashes and the quotes need to be escaped (i.e. \").
2) Use a Config file for your DTSX package. You need to parameterize all DataSource Connections and define them in a Config file. So you don’t need to edit “DataSources“under SQL Job Properties. This will avoid generating unnecessary Command Line String under SQL Job. [Recommended]
This Bug has been fixed in MS SQL Server 2008.
Issue 2 (In 64 Bit Machine):-
Error Message:-
The SQL Job fails with following Error:-
- Pre-execute (Error)
Messages
Error 0xc0202009: {F1B3B35C-FAE3-48F6-A169-4E4D8D99F9B6}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)……………………………………………
Description:-
The above error occurred due to limitation of 64 Bit environment since some .NET Framework data providers and some native OLE DB providers may not be available in 64-bit versions. When you try to execute DTSX Package as “SQL Server Integration Services Package” type in SQL Job window, by default it will try to use DTExec.exe available in 64 Bit environment. So it gets failed if your DTSX package contains any OLE DB/etc connections which are not available in 64 Bit environment.
Solution:-
Change SSIS package the setting to False from
Visual Studio Solution->Properties->Debug->64Bit .
Additionally you need to run you’re your DTSX package by using the 32 Bit DTExec.exe executable by default available in "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" path.
In order run it as a SQL Job you need to schedule it as an Operating System Command by changing the SQL Job Properties-> Steps -> Edit Etep-> Change the Type as “Operation System (CmdExec) ”
And the Command should be similar to following sample Command:-
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "C:\ImportClientInfoPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /CONFIGFILE "C:\ImportClientInfo.dtsConfig"
There are enough articles available on internet regarding SSIS package development and deployment, but hard to find exact solution regarding SSIS issues.
The purpose of this article is to provide solutions regarding few SSIS deployment issues in SQL Server 2005.
Issue 1:-
Error Message:-
The SQL Job fails with following Error:-
“The command line parameters are invalid. The step failed.”……
Description:-
This error occurred due to SQL Server 2005 Agent Bug. When you deployed your DTSX package as a SQL Server job and define DataSources [Ex:- DB Connection, Excel Connection] in SQL Job property window, it will automatically generate a Command Line string. [You can view it by Job Properties: - Edit "Step":- "General":- Select "Command line" Window]
When you try to run the SQL Job, the generated Command Line string cannot be recognized due to Command line String validation failure.
Solutions:-
1) The slashes should be backslashes and the quotes need to be escaped (i.e. \").
2) Use a Config file for your DTSX package. You need to parameterize all DataSource Connections and define them in a Config file. So you don’t need to edit “DataSources“under SQL Job Properties. This will avoid generating unnecessary Command Line String under SQL Job. [Recommended]
This Bug has been fixed in MS SQL Server 2008.
Issue 2 (In 64 Bit Machine):-
Error Message:-
The SQL Job fails with following Error:-
- Pre-execute (Error)
Messages
Error 0xc0202009: {F1B3B35C-FAE3-48F6-A169-4E4D8D99F9B6}: An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft JET Database Engine" Hresult: 0x80004005 Description: "Unspecified error".
(SQL Server Import and Export Wizard)
Error 0xc020801c: Data Flow Task: The AcquireConnection method call to the connection manager "DestinationConnectionExcel" failed with error code 0xC0202009.
(SQL Server Import and Export Wizard)……………………………………………
Description:-
The above error occurred due to limitation of 64 Bit environment since some .NET Framework data providers and some native OLE DB providers may not be available in 64-bit versions. When you try to execute DTSX Package as “SQL Server Integration Services Package” type in SQL Job window, by default it will try to use DTExec.exe available in 64 Bit environment. So it gets failed if your DTSX package contains any OLE DB/etc connections which are not available in 64 Bit environment.
Solution:-
Change SSIS package the setting to False from
Visual Studio Solution->Properties->Debug->64Bit .
Additionally you need to run you’re your DTSX package by using the 32 Bit DTExec.exe executable by default available in "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" path.
In order run it as a SQL Job you need to schedule it as an Operating System Command by changing the SQL Job Properties-> Steps -> Edit Etep-> Change the Type as “Operation System (CmdExec) ”
And the Command should be similar to following sample Command:-
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe" /FILE "C:\ImportClientInfoPackage.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E /CONFIGFILE "C:\ImportClientInfo.dtsConfig"
XSLT: Useful Method implementations for XML Transformation
XSL stands for EXtensible Stylesheet Language, and is a style sheet language for XML documents. XSLT stands for XSL Transformations.
XSLT is used for the transformation of XML documents into other XML documents. In details the original xml document will not be changed; rather, a new XML document will be created according to specified schema document (XSLT Document) based on the data content of an existing Original Xml doc.
Following sample codes are very useful for you to write Common XML Transformation Component.
The following method used to create an Xml Document by Serializing the given Object in to a memory stream. The passed Object should be a XML [Serializable] class.
/// -summary
/// Used to serialize given object to a XML Document that is used for XML Transformation
/// -summary
/// "templateDto"- Serializable Object
/// "mailType"- Enum used to identify particular mail category
/// returns-Transformed HTML message
public static string GenerateMailContent(Object templateDto,MailType mailType)
{
if (templateDto == null)
{
throw new CbsException(BaseErrorCode.ArgumentIsNull);
}
XmlDocument mailContent = new XmlDocument();
//Initialize Memery stream by loading Memory stream size from Config file.
MemoryStream memoryStream =
new MemoryStream(int.Parse(CbsConfigurationManager.GetConfigurationSection
(CbsConfigurationConstants.CbsConfigurationSection, CbsConstant.MemoryStreamSize),
CbsCultureManager.NumberFormat));
XmlSerializer xmlSerializer = new XmlSerializer(templateDto.GetType());
// Serialize object to a memory stream.
xmlSerializer.Serialize(memoryStream, templateDto);
// Set seeker at begining of stream
memoryStream.Seek(0, 0);
// Load memory stream to xml document
mailContent.Load(memoryStream);
// Transform mailContent XML to a specified format.
return TransformData(mailType, mailContent);
}
The following method is used to transform given XML document according to specified XSLT document and return the newly generated document.
/// -summary
/// Used to create the XML content of the Email using XSLT template.
/// -summary
/// param name="mailType"- Email Category
/// param name="xmlContent"-XML document content.
/// returns- XML content
public static string TransformData(MailType mailType, XmlDocument xmlContent)
{
// Load XSL transformation
XslCompiledTransform xform = new XslCompiledTransform();
// Load Absolute path for the XSLT Mail Template folder.
string mailTemplatePath =
CbsConfigurationManager.GetConfigurationSection
(CbsConfigurationConstants.CbsConfigurationSection, CbsConstant.EmailTemplatesPath);
// Switch for different XSLT mail template documents.
switch (mailType)
{
case MailType.RarSwitch:
xform.Load(mailTemplatePath + PollerSwitchXslt);
break;
case MailType.DeliveryUnsucesfull:
xform.Load(mailTemplatePath + DeliveryUnsucesfullStoresXslt);
break;
case MailType.HpSwitch:
xform.Load(mailTemplatePath + RiDMSwitchXslt);
break;
}
XmlNodeReader reader = new XmlNodeReader(xmlContent);
XmlUrlResolver resolver = new XmlUrlResolver();
// Execute and Cache results.
XmlDocument resultsDoc = new XmlDocument();
XPathNavigator resultsNav = resultsDoc.CreateNavigator();
// using makes sure that we flush the writer at the end
using (XmlWriter writer = resultsNav.AppendChild())
{
xform.Transform(reader, null, writer, resolver);
}
resultsNav.MoveToChild(XPathNodeType.Element);
return resultsNav.OuterXml;
}
XSLT is used for the transformation of XML documents into other XML documents. In details the original xml document will not be changed; rather, a new XML document will be created according to specified schema document (XSLT Document) based on the data content of an existing Original Xml doc.
Following sample codes are very useful for you to write Common XML Transformation Component.
The following method used to create an Xml Document by Serializing the given Object in to a memory stream. The passed Object should be a XML [Serializable] class.
/// -summary
/// Used to serialize given object to a XML Document that is used for XML Transformation
/// -summary
/// "templateDto"- Serializable Object
/// "mailType"- Enum used to identify particular mail category
/// returns-Transformed HTML message
public static string GenerateMailContent(Object templateDto,MailType mailType)
{
if (templateDto == null)
{
throw new CbsException(BaseErrorCode.ArgumentIsNull);
}
XmlDocument mailContent = new XmlDocument();
//Initialize Memery stream by loading Memory stream size from Config file.
MemoryStream memoryStream =
new MemoryStream(int.Parse(CbsConfigurationManager.GetConfigurationSection
(CbsConfigurationConstants.CbsConfigurationSection, CbsConstant.MemoryStreamSize),
CbsCultureManager.NumberFormat));
XmlSerializer xmlSerializer = new XmlSerializer(templateDto.GetType());
// Serialize object to a memory stream.
xmlSerializer.Serialize(memoryStream, templateDto);
// Set seeker at begining of stream
memoryStream.Seek(0, 0);
// Load memory stream to xml document
mailContent.Load(memoryStream);
// Transform mailContent XML to a specified format.
return TransformData(mailType, mailContent);
}
The following method is used to transform given XML document according to specified XSLT document and return the newly generated document.
/// -summary
/// Used to create the XML content of the Email using XSLT template.
/// -summary
/// param name="mailType"- Email Category
/// param name="xmlContent"-XML document content.
/// returns- XML content
public static string TransformData(MailType mailType, XmlDocument xmlContent)
{
// Load XSL transformation
XslCompiledTransform xform = new XslCompiledTransform();
// Load Absolute path for the XSLT Mail Template folder.
string mailTemplatePath =
CbsConfigurationManager.GetConfigurationSection
(CbsConfigurationConstants.CbsConfigurationSection, CbsConstant.EmailTemplatesPath);
// Switch for different XSLT mail template documents.
switch (mailType)
{
case MailType.RarSwitch:
xform.Load(mailTemplatePath + PollerSwitchXslt);
break;
case MailType.DeliveryUnsucesfull:
xform.Load(mailTemplatePath + DeliveryUnsucesfullStoresXslt);
break;
case MailType.HpSwitch:
xform.Load(mailTemplatePath + RiDMSwitchXslt);
break;
}
XmlNodeReader reader = new XmlNodeReader(xmlContent);
XmlUrlResolver resolver = new XmlUrlResolver();
// Execute and Cache results.
XmlDocument resultsDoc = new XmlDocument();
XPathNavigator resultsNav = resultsDoc.CreateNavigator();
// using makes sure that we flush the writer at the end
using (XmlWriter writer = resultsNav.AppendChild())
{
xform.Transform(reader, null, writer, resolver);
}
resultsNav.MoveToChild(XPathNodeType.Element);
return resultsNav.OuterXml;
}
WMI Queries on Win32 Environment
Windows Management Instrumentation (WMI) is the infrastructure for management data and operations on Windows-based operating systems. You can write WMI scripts or applications to automate administrative tasks on remote computers but WMI also supplies management data to other parts of the operating system and products.
Sample WMI Query to Uninstall Softwares in Win32 Environment.
ObjectQuery oq = new ObjectQuery("Select * from Win32_Product where Name like 'Microsoft Office%'");
ManagementObjectSearcher allProducts = new ManagementObjectSearcher(oq);
ManagementObjectCollection MngObjeColl = allProducts.Get();
foreach (ManagementObject product in MngObjeColl)
{
foreach (PropertyData property in product.Properties)
{
Console.WriteLine("{0} = {1}", property.Name, property.Value);
}
// Uninstall the poduct.
object res = product.InvokeMethod("Uninstall", null);
Console.WriteLine("-Successfully Uninstalled-\n");
}
You can find sample available Win32 Classes:-
http://msdn.microsoft.com/en-us/library/aa394084(VS.85).aspx
Sample WMI Query to Uninstall Softwares in Win32 Environment.
ObjectQuery oq = new ObjectQuery("Select * from Win32_Product where Name like 'Microsoft Office%'");
ManagementObjectSearcher allProducts = new ManagementObjectSearcher(oq);
ManagementObjectCollection MngObjeColl = allProducts.Get();
foreach (ManagementObject product in MngObjeColl)
{
foreach (PropertyData property in product.Properties)
{
Console.WriteLine("{0} = {1}", property.Name, property.Value);
}
// Uninstall the poduct.
object res = product.InvokeMethod("Uninstall", null);
Console.WriteLine("-Successfully Uninstalled-\n");
}
You can find sample available Win32 Classes:-
http://msdn.microsoft.com/en-us/library/aa394084(VS.85).aspx
Reading and Writing Files in C#
string writeFilePath = @"C:\PlugIn\Dimuthu.txt";
string readFilePath = @"C:\PlugIn\Nuwan.txt";
/// Used to read from one file and write to an another file using
/// Byte Array.
FileStream openFile = new FileStream(readFilePath, FileMode.Open, FileAccess.ReadWrite);
FileStream writeFile = new FileStream(writeFilePath, FileMode.Create, FileAccess.Write);
BufferedStream outFile = new BufferedStream(writeFile);
int count;
byte[] buffer;
buffer = new byte[4096];
while ((count = openFile.Read(buffer, 0, buffer.Length)) > 0)
{
outFile.Write(buffer, 0, count);
outFile.Flush();
}
/// Used to write a Large File for 20 seconds.
/// This will be helpful for performance testing.
FileStream fileStream = new FileStream(writeFilePath, FileMode.Create, FileAccess.Write);
DateTime end = DateTime.Now.AddSeconds(20.0);
StreamWriter file = new StreamWriter(fileStream);
while (DateTime.Now < end)
{
file.WriteLine(count);
file.Flush();
Console.WriteLine(count);
count++;
}
file.Close();
/// Used to create a zero byte text file.
string folderPath = @"C:\Test";
Directory.CreateDirectory(folderPath);
File.Create(folderPath + "\\Game.txt", 1);
string readFilePath = @"C:\PlugIn\Nuwan.txt";
/// Used to read from one file and write to an another file using
/// Byte Array.
FileStream openFile = new FileStream(readFilePath, FileMode.Open, FileAccess.ReadWrite);
FileStream writeFile = new FileStream(writeFilePath, FileMode.Create, FileAccess.Write);
BufferedStream outFile = new BufferedStream(writeFile);
int count;
byte[] buffer;
buffer = new byte[4096];
while ((count = openFile.Read(buffer, 0, buffer.Length)) > 0)
{
outFile.Write(buffer, 0, count);
outFile.Flush();
}
/// Used to write a Large File for 20 seconds.
/// This will be helpful for performance testing.
FileStream fileStream = new FileStream(writeFilePath, FileMode.Create, FileAccess.Write);
DateTime end = DateTime.Now.AddSeconds(20.0);
StreamWriter file = new StreamWriter(fileStream);
while (DateTime.Now < end)
{
file.WriteLine(count);
file.Flush();
Console.WriteLine(count);
count++;
}
file.Close();
/// Used to create a zero byte text file.
string folderPath = @"C:\Test";
Directory.CreateDirectory(folderPath);
File.Create(folderPath + "\\Game.txt", 1);
Base64 Encoding and Decoding
Following Base64Encode() method can be used Encode any file in to a Base64 string and write it in to a given file path. The Base64Decode() method can be used to generate original file from given Base64 String.
Basically Base64 can be used to encode a any file such as jpeg, avi, dat, txt, etc in to a String format.
private string Base64Encode()
{
string strBase64;
FileStream fileStream = new FileStream(msiFilePath, FileMode.Open, FileAccess.Read,
FileShare.Read);
byte[] buffer = new byte[fileStream.Length];
fileStream.Read(buffer, 0, buffer.Length);
strBase64 = Convert.ToBase64String(buffer);
return strBase64;
}
private void Base64Decode(string filePath, string base64String)
{
FileStream fileStream = null;
try
{
byte[] plugInObjectBytes = Convert.FromBase64String(base64String);
fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write);
fileStream.Write(plugInObjectBytes, 0, plugInObjectBytes.Length);
fileStream.Flush();
}
catch (Exception ex)
{
if (File.Exists(filePath))
{
fileStream = null;
File.Delete(filePath);
}
throw;
}
finally
{
if (fileStream != null)
{
fileStream.Close();
}
}
}
Basically Base64 can be used to encode a any file such as jpeg, avi, dat, txt, etc in to a String format.
private string Base64Encode()
{
string strBase64;
FileStream fileStream = new FileStream(msiFilePath, FileMode.Open, FileAccess.Read,
FileShare.Read);
byte[] buffer = new byte[fileStream.Length];
fileStream.Read(buffer, 0, buffer.Length);
strBase64 = Convert.ToBase64String(buffer);
return strBase64;
}
private void Base64Decode(string filePath, string base64String)
{
FileStream fileStream = null;
try
{
byte[] plugInObjectBytes = Convert.FromBase64String(base64String);
fileStream = new FileStream(filePath, FileMode.Create, FileAccess.Write);
fileStream.Write(plugInObjectBytes, 0, plugInObjectBytes.Length);
fileStream.Flush();
}
catch (Exception ex)
{
if (File.Exists(filePath))
{
fileStream = null;
File.Delete(filePath);
}
throw;
}
finally
{
if (fileStream != null)
{
fileStream.Close();
}
}
}
Stored Procedures Quick Reference [SQL Server 2005]
SCOPE_IDENTITY and @@IDENTITY return the last identity values that are generated in any table in the current session.
-Get Currtent Date-
GETDATE()
-GET UTC Date
GETUTCDATE()
-Get UTC DATE WITHOUT TIME
cast(floor(cast(GETDATE() as float)) as datetime)
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
-Return a Parameter back-
@bnGroupId bigint OUT,
SET @bnGroupId = SCOPE_IDENTITY()
-SQL SURFACE to enable remote logging-
1) Select Start Menu>SQL Server 2005>Configuration Tools>Surface area Configuration
2) Click Serface area Connection
3) Select Using Both TCP/IP and named piped
Network Configuration
1)Select Start Menu>SQL Server 2005>Configuration Tools>Server Configuration Manager
2)Click Nenwork Configuration
3)Select Protocol for SQL Server
4)Enable TCP/IP
How to test Romtote Logging to SQL
SQLCMD -E -S CT-MCDDEVSQL\SQLEXPRESSCLIENT,2301
-The SQL EXIST Statement-
IF EXISTS ( SELECT * FROM [tblData]
WHERE MessageId]=@MessageId
AND [RecipId]=@vcRecipId
AND [TypeId]= @TypeId
AND @vcStatus = @vcPendingStatus
)
BEGIN
--query
END
-How to Catch Sql Errors-
DECLARE @vcErrorNumber varchar(10)-- hold the error number
BEGIN TRY
INSERT INTO [tblDestinationInfoClientInfo]/--------/
END TRY
BEGIN CATCH
SET @vcErrorNumber = ERROR_NUMBER()
-- primary key vialation error number
IF (@vcErrorNumber = '2627')
BEGIN
/--------------/
END
END CATCH
-Define Transactions-
BEGIN TRAN
COMMIT TRAN
ROLLBACK TRAN
-How to Retrieve All Specific Table Columns from SysTable-
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp')
--sysobjects table contains all tables information
SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp'
In SQL Server the is a unique identification number for every tables. So we retrieve specified User Defiened table ID.
--syscolumns table contains all tables column information
We can retrieve specific table Columns by passing specific table Id.
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = /*Table Id*/
-Define a Cursor [Column Cursor]-
DECLARE @fieldname varchar(200)
DECLARE temptable_fields_cursor CURSOR FOR
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblClientRelationships')
OPEN temptable_fields_cursor
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname
WHILE @@FETCH_STATUS = 0
BEGIN
----implementation----------
--------------
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname
END --End While--
CLOSE temptable_fields_cursor
DEALLOCATE temptable_fields_cursor
-Create & Execute a Dynamic Query-
SET @SqlQuery = 'Select * From Emp'
EXEC (@SqlQuery )
-Define SQL Paging for a .NET GridView.-
declare @nPageNum INT
declare @nPageSize INT
set @nPageNum=1
set @nPageSize=10;
Note:-
--Following Select statement result will be stored in to 'Search' variable and we should need to retrieve data from 'Search' in the next statement.
--We can query 'Search' only one time.
WITH Search AS
(
SELECT rownum, Records.[Name] as PersonName , Records.[Age] as PersonAge ,Records.[ID], Records.[Bdate]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID,Bdate) AS rownum ,
dbo.Person.[Name] , dbo.Person.[Age] ,dbo.Person.[ID]
,dbo.Person.[Bdate] FROM dbo.Person)
AS Records
)
SELECT PersonName,PersonAge,Search.[ID],Search.[Bdate],(SELECT COUNT(*) FROM Search) AS RecordCount
FROM Search
WHERE rownum BETWEEN (@nPageNum-1)*@nPageSize+1 AND @nPageNum*@nPageSize
ORDER BY Search.[Bdate]
-SQL SERVER – 2005 Locking-
ROWLOCK
Use row-level locks when reading or modifying data.
PAGLOCK
Use page-level locks when reading or modifying data.
TABLOCK
Use a table lock when reading or modifying data.
DBLOCK
Use a database lock when reading or modifying data.
UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.
XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is
no longer required.
NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.
Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'
http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/
View SQL Server Connected Users:-
sp_who or sp_who2
Get SQL users that are connected and how many sessions they have
SELECT login_name, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name
-Get Currtent Date-
GETDATE()
-GET UTC Date
GETUTCDATE()
-Get UTC DATE WITHOUT TIME
cast(floor(cast(GETDATE() as float)) as datetime)
When SET NOCOUNT is ON, the count is not returned. When SET NOCOUNT is OFF, the count is returned.
-Return a Parameter back-
@bnGroupId bigint OUT,
SET @bnGroupId = SCOPE_IDENTITY()
-SQL SURFACE to enable remote logging-
1) Select Start Menu>SQL Server 2005>Configuration Tools>Surface area Configuration
2) Click Serface area Connection
3) Select Using Both TCP/IP and named piped
Network Configuration
1)Select Start Menu>SQL Server 2005>Configuration Tools>Server Configuration Manager
2)Click Nenwork Configuration
3)Select Protocol for SQL Server
4)Enable TCP/IP
How to test Romtote Logging to SQL
SQLCMD -E -S CT-MCDDEVSQL\SQLEXPRESSCLIENT,2301
-The SQL EXIST Statement-
IF EXISTS ( SELECT * FROM [tblData]
WHERE MessageId]=@MessageId
AND [RecipId]=@vcRecipId
AND [TypeId]= @TypeId
AND @vcStatus = @vcPendingStatus
)
BEGIN
--query
END
-How to Catch Sql Errors-
DECLARE @vcErrorNumber varchar(10)-- hold the error number
BEGIN TRY
INSERT INTO [tblDestinationInfoClientInfo]/--------/
END TRY
BEGIN CATCH
SET @vcErrorNumber = ERROR_NUMBER()
-- primary key vialation error number
IF (@vcErrorNumber = '2627')
BEGIN
/--------------/
END
END CATCH
-Define Transactions-
BEGIN TRAN
COMMIT TRAN
ROLLBACK TRAN
-How to Retrieve All Specific Table Columns from SysTable-
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp')
--sysobjects table contains all tables information
SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblEmp'
In SQL Server the is a unique identification number for every tables. So we retrieve specified User Defiened table ID.
--syscolumns table contains all tables column information
We can retrieve specific table Columns by passing specific table Id.
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = /*Table Id*/
-Define a Cursor [Column Cursor]-
DECLARE @fieldname varchar(200)
DECLARE temptable_fields_cursor CURSOR FOR
SELECT [name] AS FieldName
FROM syscolumns
WHERE id = ( SELECT id
FROM sysobjects
WHERE type = 'U'
AND [NAME] = 'tblClientRelationships')
OPEN temptable_fields_cursor
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname
WHILE @@FETCH_STATUS = 0
BEGIN
----implementation----------
--------------
FETCH NEXT FROM temptable_fields_cursor INTO @fieldname
END --End While--
CLOSE temptable_fields_cursor
DEALLOCATE temptable_fields_cursor
-Create & Execute a Dynamic Query-
SET @SqlQuery = 'Select * From Emp'
EXEC (@SqlQuery )
-Define SQL Paging for a .NET GridView.-
declare @nPageNum INT
declare @nPageSize INT
set @nPageNum=1
set @nPageSize=10;
Note:-
--Following Select statement result will be stored in to 'Search' variable and we should need to retrieve data from 'Search' in the next statement.
--We can query 'Search' only one time.
WITH Search AS
(
SELECT rownum, Records.[Name] as PersonName , Records.[Age] as PersonAge ,Records.[ID], Records.[Bdate]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID,Bdate) AS rownum ,
dbo.Person.[Name] , dbo.Person.[Age] ,dbo.Person.[ID]
,dbo.Person.[Bdate] FROM dbo.Person)
AS Records
)
SELECT PersonName,PersonAge,Search.[ID],Search.[Bdate],(SELECT COUNT(*) FROM Search) AS RecordCount
FROM Search
WHERE rownum BETWEEN (@nPageNum-1)*@nPageSize+1 AND @nPageNum*@nPageSize
ORDER BY Search.[Bdate]
-SQL SERVER – 2005 Locking-
ROWLOCK
Use row-level locks when reading or modifying data.
PAGLOCK
Use page-level locks when reading or modifying data.
TABLOCK
Use a table lock when reading or modifying data.
DBLOCK
Use a database lock when reading or modifying data.
UPDLOCK
UPDLOCK reads data without blocking other readers, and update it later with the assurance that the data has not changed since last read.
XLOCK
Use exclusive locks instead of shared locks while reading a table, and use hold locks until the end of the statement or transaction.
HOLDLOCK
Use a hold lock to hold a lock until completion of the transaction, instead of releasing the lock as soon as the required table, row, or data page is
no longer required.
NOLOCK
This does not lock any object. This is the default for SELECT operations. It does not apply to INSERT, UPDATE, and DELETE statements.
Examples:
SELECT OrderID
FROM Orders (WITH ROWLOCK)
WHERE OrderID BETWEEN 100
AND 2000
UPDATE Products (WITH NOLOCK)
SET ProductCat = 'Machine'
WHERE ProductSubCat = 'Mac'
http://msdn.microsoft.com/en-us/library/aa213026(SQL.80).aspx
http://blog.sqlauthority.com/2007/04/27/sql-server-2005-locking-hints-and-examples/
View SQL Server Connected Users:-
sp_who or sp_who2
Get SQL users that are connected and how many sessions they have
SELECT login_name, count(session_id) as session_count
FROM sys.dm_exec_sessions
GROUP BY login_name
Subscribe to:
Posts (Atom)
