Friday, 20 October 2017

How long is a .NET DateTime/TimeSpan tick?

The tick is the unit of granularity for the .NET DateTime and TimeSpan value types.
It has the following common conversions:
1 tick = 100 nanoseconds
       = 0.1 microseconds
       = 0.0001 milliseconds
       = 0.0000001 seconds

Fractional Seconds in Time Values

MySQL 5.7 has fractional seconds support for TIMEDATETIME, and TIMESTAMP values, with up to microseconds (6 digits) precision:
  • To define a column that includes a fractional seconds part, use the syntax type_name(fsp), where type_name is TIMEDATETIME, or TIMESTAMP, and fsp is the fractional seconds precision. For example:
    CREATE TABLE t1 (t TIME(3), dt DATETIME(6));
    The fsp value, if given, must be in the range 0 to 6. A value of 0 signifies that there is no fractional part. If omitted, the default precision is 0. (This differs from the standard SQL default of 6, for compatibility with previous MySQL versions.)
  • Inserting a TIMEDATE, or TIMESTAMP value with a fractional seconds part into a column of the same type but having fewer fractional digits results in rounding, as shown in this example:
    mysql> CREATE TABLE fractest( c1 TIME(2), c2 DATETIME(2), c3 TIMESTAMP(2) );
    Query OK, 0 rows affected (0.33 sec)
    
    mysql> INSERT INTO fractest VALUES
         > ('17:51:04.777', '2014-09-08 17:51:04.777', '2014-09-08 17:51:04.777');
    Query OK, 1 row affected (0.03 sec)
    
    mysql> SELECT * FROM fractest;
    +-------------+------------------------+------------------------+
    | c1          | c2                     | c3                     |
    +-------------+------------------------+------------------------+
    | 17:51:04.78 | 2014-09-08 17:51:04.78 | 2014-09-08 17:51:04.78 |
    +-------------+------------------------+------------------------+
    1 row in set (0.00 sec)
    No warning or error is given when such rounding occurs. This behavior follows the SQL standard, and is not affected by the server's sql_mode setting.
  • Functions that take temporal arguments accept values with fractional seconds. Return values from temporal functions include fractional seconds as appropriate. For example, NOW() with no argument returns the current date and time with no fractional part, but takes an optional argument from 0 to 6 to specify that the return value includes a fractional seconds part of that many digits.
  • Syntax for temporal literals produces temporal values: DATE 'str'TIME 'str', and TIMESTAMP 'str', and the ODBC-syntax equivalents. The resulting value includes a trailing fractional seconds part if specified. Previously, the temporal type keyword was ignored and these constructs produced the string value. See Standard SQL and ODBC Date and Time Literals

Thursday, 19 October 2017

Powershell DB Restore

# PSHELL 3
$ErrorActionPreference = "Stop"

#SDK version config
$sdkversion = 100
$sdkpath = "D:\Folding Space\SDK\SDK\Assemblies\";

#load SMO types
Add-Type -Path "$sdkpath\Microsoft.SqlServer.Smo.dll"
Add-Type -Path "$sdkpath\Microsoft.SqlServer.SmoExtended.dll"

$today = Get-Date
$datepart = $today.Year.ToString() + "_" + $today.Month.ToString("00") + "_" + $today.Day.ToString("00");

$path1 = "\\server-sql02\g$\SQLBackups\db1*" + $datepart + "*.bak"
$path2 = "\\server-sql03\G$\SQLBackup\db2*" + $datepart + "*.bak"
$path3 = "\\server-sql03\G$\SQLBackup\db3*" + $datepart + "*.bak"

write "Emptying Backup Directory"
Remove-Item –path D:\Backups\* -include *.bak

write "Copying Backup Files to Backup Directory"
Copy-Item \\server-sql01\e$\newSQLBackupLocation\iprnt*.bak D:\Backups\db4.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db5*.bak D:\Backups\IPSA_MSCRM.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db6*.bak D:\Backups\JANE_IPSA.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db7*.bak D:\Backups\JANE_MEMBERS.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db8*.bak D:\Backups\JANE_STAFF.bak
Copy-Item \\server-sql01\e$\newSQLBackupLocation\db9*.bak D:\Backups\DYNAMICS.bak

Copy-Item $path1 D:\Backups\IPSA-MASTER.bak
Copy-Item $path2 D:\Backups\IRIS_MSCRM.bak
Copy-Item $path3 D:\Backups\IPSAGateway.bak

$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server
$srv.ConnectionContext.LoginSecure = $false
$srv.ConnectionContext.Login = "username"
$srv.ConnectionContext.Password = "password"
$srv.ConnectionContext.ServerInstance = "db server"

##databases
$target = $srv.ConnectionContext

#$tx = $target.Parent.ConnectionContext.BeginTransaction()
$sql = (gc '.\DB Restore.sql' -Raw)   
write "Restoring databases from backups"
$target.ExecuteNonQuery($sql)
#$target.Parent.ConnectionContext.CommitTransaction()

write "Generating new output for data migration"
$process = New-Object System.Diagnostics.Process
$process.StartInfo.FileName = "D:\DataMigrator.exe";
$process.StartInfo.UseShellExecute = $false
$process.Start()
if ($process.Start())
{
    $output = $process.StandardOutput.ReadToEnd()
}




SQL Restore Script:

ALTER DATABASE db1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db2 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db3 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db4 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db5 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db6 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db7 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db8 SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE db9  SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


RESTORE DATABASE db1 FROM DISK = 'D:\Backups\db1.bak' WITH REPLACE;  
RESTORE DATABASE db2 FROM DISK = 'D:\Backups\db2.bak' WITH REPLACE;  
RESTORE DATABASE db3 FROM DISK = 'D:\Backups\db3.bak' WITH REPLACE;  
RESTORE DATABASE db4 FROM DISK = 'D:\Backups\db4.bak' WITH REPLACE;  
RESTORE DATABASE db5 FROM DISK = 'D:\Backups\db5.bak' WITH REPLACE;  
RESTORE DATABASE db6 FROM DISK = 'D:\Backups\db6.bak' WITH REPLACE;  
RESTORE DATABASE db7 FROM DISK = 'D:\Backups\db7.bak' WITH REPLACE;  
RESTORE DATABASE db8 FROM DISK = 'D:\Backups\db8.bak' WITH REPLACE;  
RESTORE DATABASE db9 FROM DISK = 'D:\Backups\db9.bak' WITH REPLACE;  



Thursday, 12 October 2017

Remove time from SQL Date

Convert(Date, Current_Timestamp) as datewithnotime

SQL Database Restore

ALTER DATABASE [DEV-MASTER] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


RESTORE DATABASE JANE_IPSA FROM DISK = 'D:\Backups\DB_backup_2017_10_11_190026_1796108.bak' WITH REPLACE; 

Tuesday, 10 October 2017

ASP.NET MVC 4 partial view

Your call:
@Html.Partial("_StatePartial")
will render the view, but it will not call the action. You would only use this if you were seeting up all your view data in your parent page action. In your case you need to use this:
@Html.Action("_StatePartial")
This will call the action first to retrieve and execute the view.




Because Html.Partial directly renders a partial view with the passed model (optionally). I mean directly that it's not going though the MVC lifecycle, only acts like rendering a "template".
If you want to run a child action, which is going through the entire lifecycle (acts like a real request) then use Html.Action method. This way your controller action will be invoked, including the routing system, filters, etc.
One should consider which method he should use, because the Html.Action is much slower of course (even if most likely the bottleneck will not be here). I used to think this way: for reusing views use Html.Partial, for reusing both business logic and view use Html.Action.