DISABLE and ENABLE TRIGGER in SQL

ALTER TABLE "table name" DISABLE TRIGGER  "Trigger Name"

ALTER TABLE  "table name"  ENABLE TRIGGER "Trigger Name"

Parser Error Message: Authentication to host '' for user '' using method 'mysql_native_password' failed with message: Access denied for user ''@'User' (using password: NO)

Issue:

Parser Error Message: Authentication to host '' for user '' using method 'mysql_native_password' failed with message: Access denied for user ''@'LBSBLRDEVSRV001.lotex.loc' (using password: NO)

Source Error: 

Line 283: <siteMap> Line 284: <providers> Line 285: <add name="MySqlSiteMapProvider"  type="MySql.Web.SiteMap.MySqlSiteMapProvider, MySql.Web,  Version=6.9.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d"  connectionStringName="LocalMySqlServer" applicationName="/" /> Line 286: </providers> Line 287: </siteMap>

--------------------------------------------------------------------------------------------------------------------------

Solution : - 

commented the below  tag in C:\Windows\Microsoft.NET\Framework\v4.0.30319\Config\ machine. config

<providers>

<add name="MySqlSiteMapProvider" type="MySql.Web.SiteMap.MySqlSiteMapProvider, MySql.Web, Version=6.9.6.0, Culture=neutral, PublicKeyToken=c5687fc88969c44d" connectionStringName="LocalMySqlServer" applicationName="/"/>

</providers>

how to Find and remove uncommitted or open transactions in SQL !

Write  DBCC OPENTRAN in the query window and execute

If open  transaction's available   Result will be 

Then

KILL server process ID

Example:  KILL 53 

STRING FUNCTIONS in SQL

Example SQL String Function - ASCII
- Returns the ASCII code value of a keyboard button and the rest etc (@,R,9,*) .
Syntax - ASCII ( character)
SELECT ASCII('a') -- Value = 97
SELECT ASCII('b') -- Value = 98
SELECT ASCII('c') -- Value = 99
SELECT ASCII('A') -- Value = 65
SELECT ASCII('B') -- Value = 66
SELECT ASCII('C') -- Value = 67
SELECT ASCII('1') -- Value = 49
SELECT ASCII('2') -- Value = 50
SELECT ASCII('3') -- Value = 51
SELECT ASCII('4') -- Value = 52
SELECT ASCII('5') -- Value = 53 




Example SQL String Function - SPACE 
-Returns spaces in your SQL query (you can specific the size of space). 
Syntax - SPACE ( integer)
SELECT ('SQL') + SPACE(0) + ('TUTORIALS')
-- Value = SQLTUTORIALS
SELECT ('SQL') + SPACE(1) + ('TUTORIALS')
-- Value = SQL TUTORIALS 




Example SQL String Function - CHARINDEX
-Returns the starting position of a character string.
Syntax - CHARINDEX ( string1, string2 [ , start_location ] ) 
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial') 
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 20) 
-- Value = 27
SELECT CHARINDEX('SQL', 'Well organized understand SQL tutorial', 30)
-- Value = 0 (Because the index is count from 30 and above)




Example SQL String Function - REPLACE
-Replaces all occurrences of the string2 in the string1 with string3.
Syntax - REPLACE ( 'string1' , 'string2' , 'string3' )
SELECT REPLACE('All Function' , 'All', 'SQL')
-- Value = SQL Function




Example SQL String Function - QUOTENAME
-Returns a Unicode string with the delimiters added to make the input string a valid Microsoft® SQL Server™ delimited identifier.
Syntax - QUOTENAME ( 'string' [ , 'quote_character' ] ) 
SELECT QUOTENAME('Sql[]String')
-- Value = [Sql[]]String]




Example SQL String Function - STUFF
- Deletes a specified length of characters and inserts string at a specified starting index.
Syntax - STUFF ( string1 , startindex , length , string2 ) 
SELECT STUFF('SqlTutorial', 4, 6, 'Function')
-- Value = SqlFunctional
SELECT STUFF('GoodMorning', 5, 3, 'good')
-- Value = Goodgoodning




Example SQL String Function - LEFT
-Returns left part of a string with the specified number of characters.
Syntax - LEFT ( string , integer) 
SELECT LEFT('TravelYourself', 6) 
-- Value = Travel
SELECT LEFT('BeautyCentury',6) 
-- Value = Beauty




Example SQL String Function - RIGHT
-Returns right part of a string with the specified number of characters.
Syntax - RIGHT( string , integer)
SELECT RIGHT('TravelYourself', 6)
-- Value = urself
SELECT RIGHT('BeautyCentury',6)
-- Value = Century




Example SQL String Function - REPLICATE
-Repeats string for a specified number of times.
Syntax - REPLICATE (string, integer)
SELECT REPLICATE('Sql', 2) 
-- Value = SqlSql




Example SQL String Function - SUBSTRING
-Returns part of a string.
Syntax - SUBSTRING ( string, startindex , length )
SELECT SUBSTRING('SQLServer', 4, 3) 
-- Value = Ser




Example SQL String Function - LEN
-Returns number of characters in a string.
Syntax - LEN( string) 
SELECT LEN('SQLServer')
-- Value = 9




Example SQL String Function - REVERSE
-Returns reverse a string.
Syntax - REVERSE( string)
SELECT REVERSE('SQLServer') 
-- Value = revreSLQS




Example SQL String Function - UNICODE
-Returns Unicode standard integer value.
Syntax - UNICODE( char) 
SELECT UNICODE('SqlServer') 
-- Value = 83 (it take first character)
SELECT UNICODE('S')
-- Value = 83




Example SQL String Function - LOWER
-Convert string to lowercase.
Syntax - LOWER( string )
SELECT LOWER('SQLServer') 
-- Value = sqlserver




Example SQL String Function - UPPER
-Convert string to Uppercase.
Syntax - UPPER( string ) 
SELECT UPPER('sqlserver') 
-- Value = SQLSERVER




Example SQL String Function - LTRIM
-Returns a string after removing leading blanks on Left side.
Syntax - LTRIM( string )
SELECT LTRIM(' sqlserver')
-- Value = 'sqlserver' (Remove left side space or blanks)




Example SQL String Function - RTRIM 
-Returns a string after removing leading blanks on Right side.
Syntax - RTRIM( string )
SELECT RTRIM('SqlServer ')
-- Value = 'SqlServer' (Remove right side space or blanks)

Bat File To execute SQL _ Scripts ".bat"

@echo off
set USERNAME=sa
set PASSWORD=password
set DB= Database
set SERVER=server


REM setlocal
REM SET SQLCMD=sqlcmd -S<servername\instancename> -d<database name> -E
REM for %%d in (*.sql) do %SQLCMD% -i%%d
REM endlocal


setlocal



SET SQLCMD=sqlcmd /S %SERVER% /U %USERNAME% /P "%PASSWORD%" /d %DB%
for %%d in (Tables\*.sql) do %SQLCMD% -i%%d -o%%d.log

SET SQLCMD=sqlcmd /S %SERVER% /U %USERNAME% /P "%PASSWORD%" /d %DB%
for %%d in (Data\*.sql) do %SQLCMD% -i%%d -o%%d.log

SET SQLCMD=sqlcmd /S %SERVER% /U %USERNAME% /P "%PASSWORD%" /d %DB%
for %%d in (SP\*.sql) do %SQLCMD% -i%%d -o%%d.log



endlocal


Pause

Date Formatting in C#

// create date time 2008-03-09 16:05:07.123
DateTime dt = new DateTime(2008, 3, 9, 16, 5, 7, 123);

String.Format("{0:y yy yyy yyyy}", dt);  // "8 08 008 2008"   year
String.Format("{0:M MM MMM MMMM}", dt);  // "3 03 Mar March"  month
String.Format("{0:d dd ddd dddd}", dt);  // "9 09 Sun Sunday" day
String.Format("{0:h hh H HH}",     dt);  // "4 04 16 16"      hour 12/24
String.Format("{0:m mm}",          dt);  // "5 05"            minute
String.Format("{0:s ss}",          dt);  // "7 07"            second
String.Format("{0:f ff fff ffff}", dt);  // "1 12 123 1230"   sec.fraction
String.Format("{0:F FF FFF FFFF}", dt);  // "1 12 123 123"    without zeroes
String.Format("{0:t tt}",          dt);  // "P PM"            A.M. or P.M.
String.Format("{0:z zz zzz}",      dt);  // "-6 -06 -06:00"   time zone
// date separator in german culture is "." (so "/" changes to ".")
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9/3/2008 16:05:07" - english (en-US)
String.Format("{0:d/M/yyyy HH:mm:ss}", dt); // "9.3.2008 16:05:07" - german (de-DE)
// month/day numbers without/with leading zeroes
String.Format("{0:M/d/yyyy}", dt);            // "3/9/2008"
String.Format("{0:MM/dd/yyyy}", dt);          // "03/09/2008"

// day/month names
String.Format("{0:ddd, MMM d, yyyy}", dt);    // "Sun, Mar 9, 2008"
String.Format("{0:dddd, MMMM d, yyyy}", dt);  // "Sunday, March 9, 2008"

// two/four digit year
String.Format("{0:MM/dd/yy}", dt);            // "03/09/08"
String.Format("{0:MM/dd/yyyy}", dt);          // "03/09/2008"

String.Format("{0:t}", dt);  // "4:05 PM"                         ShortTime
String.Format("{0:d}", dt);  // "3/9/2008"                        ShortDate
String.Format("{0:T}", dt);  // "4:05:07 PM"                      LongTime
String.Format("{0:D}", dt);  // "Sunday, March 09, 2008"          LongDate
String.Format("{0:f}", dt);  // "Sunday, March 09, 2008 4:05 PM"  LongDate+ShortTime
String.Format("{0:F}", dt);  // "Sunday, March 09, 2008 4:05:07 PM" FullDateTime
String.Format("{0:g}", dt);  // "3/9/2008 4:05 PM"                ShortDate+ShortTime
String.Format("{0:G}", dt);  // "3/9/2008 4:05:07 PM"             ShortDate+LongTime
String.Format("{0:m}", dt);  // "March 09"                        MonthDay
String.Format("{0:y}", dt);  // "March, 2008"                     YearMonth
String.Format("{0:r}", dt);  // "Sun, 09 Mar 2008 16:05:07 GMT"   RFC1123
String.Format("{0:s}", dt);  // "2008-03-09T16:05:07"             SortableDateTime
String.Format("{0:u}", dt);  // "2008-03-09 16:05:07Z"            UniversalSortableDateTime

SQL Date Format !

Recover unsaved SQL query scripts

 Use <your database>
SELECT execquery.last_execution_time AS [Date Time], execsql.text AS [Script] FROM sys.dm_exec_query_stats AS execquery
CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) AS execsql
where execsql.text like '% something you remember or commd this where condition %'
ORDER BY execquery.last_execution_time DESC