Windows 8 Administrative Shares: 'Access is Denied'

SQL Server

http://www.computerperformance.co.uk/win8/windows8-administrative-shares.htm

Windows 8 Administrative Shares: 'Access is Denied'

For security reasons the built-in admin$ family of hidden shares has been disabled in Windows 8.

Windows 8 Administrative Shares

• Windows 8 Administrative Shares Access Denied
• Solution for Error Messages
• What Are Admin$ Shares?

Windows 8 Administrative Shares Access Denied

The situation: you try to connect to a network machine via a hidden share such as c$ or admin$ (\\MachineName\c$), and receive an error messages:

• Access is denied.
• The specified username is invalid.
• You may not have permission to use this network share.

Solutions for Access is Denied to Administrative Shares

1. One solution may be to accept the situation and abandon your attempt to connect via C$.  You could try remote desktop instead.  I say this not because the challenge is too difficult, but because the default is the securest configuration for remote user account control (UAC).  Once you enable these hidden admin$ shares then your machine can be attacked by hackers.  Indeed, that is why Microsoft removed this capability, even though it was popular with XP and Windows 98 users.

PowerShell

Scripts:
Robocopy-01.ps1

Robocopy_with_bar.ps1

Arrays

http://ss64.com/ps/

Arrays - http://ss64.com/ps/syntax-arrays.html

A PowerShell array holds a list of data items.
The data elements of a PowerShell array need not be of the same type, unless the data type is declared (strongly typed).

Creating Arrays
To create an Array just separate the elements with commas.

Create an array named $myArray containing elements with a mix of data types:

$myArray = 1,"Hello",3.5,"World"

or using explicit syntax:
$myArray = @(1,"Hello",3.5,"World")

To distribute the values back into individual variables:
$var1,$var2,$var3,$var4=$myArray

Create an array containing several numeric (int) values:

SQL Server Error Messages - Msg 306 - The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

SQL Server

http://www.sql-server-helper.com/error-messages/msg-306.aspx

SQL Server Error Messages - Msg 306 Error Message Server: Msg 306, Level 16, State 1, Line 1 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. Causes NTEXT data types are used for variable-length of Unicode data, TEXT data types are used for variable-length non-Unicode data while IMAGE data types are used for variable-length binary data.

One way of getting this error is when including a column of TEXT, NTEXT or IMAGE data type in the ORDER BY clause. To illustrate, here’s a script that will generate this error message:

CREATE TABLE [dbo].[BookSummary] (
    [BookSummaryID]     INT NOT NULL IDENTITY(1, 1),
    [BookName]          NVARCHAR(200),
    [Author]            NVARCHAR(100),
    [Summary]           NTEXT
)

SELECT * FROM [dbo].[BookSummary]
ORDER BY [Summary]

Msg 306, Level 16, State 2, Line 2
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.

Another way of getting this error is including a column of TEXT, NTEXT or IMAGE data type as part of a GROUP BY clause, as can be seen in the following script:

SQL Server 2012 Date Formats Using the FORMAT Function and CONVERT Function

SQL Server

http://www.sql-server-helper.com/sql-server-2012/format-function-vs-convert-function.aspx

SQL Server 2012 Date Formats Using the FORMAT Function and CONVERT Function One of the most frequently asked questions in SQL Server forums is how to format a datetime value or column into a specific date format. Here's a summary of the different date formats that come standard in SQL Server as part of the CONVERT function and the corresponding syntax using the new SQL Server 2012 FORMAT string function. Following the standard date formats are some extended date formats that are often asked by SQL Server developers, together with the corresponding syntax as well using the new SQL Server 2012 FORMAT string function.

It is worth to note that the outputs of these date formats are of VARCHAR data types already and not of DATETIME data type. With this in mind, any date comparisons performed after the datetime value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value. The SQL statements used below to return the different date formats use the SYSDATETIME() date function. The SYSDATETIME() function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The SYSDATETIME() function used below can be replaced by the GETDATE() or GETUTCDATE() functions. The results will be the same unless the date format includes the nanosecond portion of the time. To make the date format results consistent, the date and time used to generate the sample output is June 8, 2011 1:30:45.9428675 PM. Standard CONVERT Date Formats Date Format FORMAT Function CONVERT Function Sample Output Mon DD YYYY 1 HH:MIAM (or PM) SELECT FORMAT(SYSDATETIME(), 'Mon d yyyy h:mmtt') SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100) Jun 8 2011 1:30PM 1 MM/DD/YY SELECT FORMAT(SYSDATETIME(), 'MM/dd/yy') AS [MM/DD/YY] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY] 06/08/11 MM/DD/YYYY SELECT FORMAT(SYSDATETIME(), 'MM/dd/yyyy') AS [MM/DD/YYYY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY] 06/08/2011 YY.MM.DD SELECT FORMAT(SYSDATETIME(), 'yy.MM.dd') AS [YY.MM.DD] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD] 11.06.08 YYYY.MM.DD SELECT FORMAT(SYSDATETIME(), 'yyyy.MM.dd') AS [YYYY.MM.DD] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD] 2011.06.08 DD/MM/YY SELECT FORMAT(SYSDATETIME(), 'dd/MM/yy') AS [DD/MM/YY] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY] 08/06/11 DD/MM/YYYY SELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy') AS [DD/MM/YYYY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY] 08/06/2011 DD.MM.YY SELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11 DD.MM.YYYY SELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011 DD-MM-YY SELECT FORMAT(SYSDATETIME(), 'dd-MM-yy') AS [DD-MM-YY] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY] 08-06-11 DD-MM-YYYY SELECT FORMAT(SYSDATETIME(), 'dd-MM-yyyy') AS [DD-MM-YYYY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY] 08-06-2011 DD Mon YY 1 SELECT FORMAT(SYSDATETIME(), 'dd MMM yy') AS [DD MON YY] SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY] 08 Jun 11 1 DD Mon YYYY 1 SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy') AS [DD MON YYYY] SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY] 08 Jun 2011 1 Mon DD, YY 1 SELECT FORMAT(SYSDATETIME(), 'MMM dd, yy') AS [Mon DD, YY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY] Jun 08, 11 1 Mon DD, YYYY 1 SELECT FORMAT(SYSDATETIME(), 'MMM dd, yyyy') AS [Mon DD, YYYY] SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY] Jun 08, 2011 1 HH:MM:SS SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss') SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8) SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108) 13:30:45 Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1 SELECT FORMAT(SYSDATETIME(), 'MMM d yyyy h:mm:ss.ffffffftt') SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9) SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109) Jun 8 2011 1:30:45.9428675PM 1 MM-DD-YY SELECT FORMAT(SYSDATETIME(), 'MM-dd-yy') AS [MM-DD-YY] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY] 06-08-11 MM-DD-YYYY SELECT FORMAT(SYSDATETIME(), 'MM-dd-yyyy') AS [MM-DD-YYYY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY] 06-08-2011 YY/MM/DD SELECT FORMAT(SYSDATETIME(), 'yy/MM/dd') AS [YY/MM/DD] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD] 11/06/08 YYYY/MM/DD SELECT FORMAT(SYSDATETIME(), 'yyyy/MM/dd') AS [YYYY/MM/DD] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD] 2011/06/08 YYMMDD SELECT FORMAT(SYSDATETIME(), 'yyMMdd') AS [YYMMDD] SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD] 110608 YYYYMMDD SELECT FORMAT(SYSDATETIME(), 'yyyyMMdd') AS [YYYYMMDD] SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD] 20110608 DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1 SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy HH:mm:ss.fffffff') SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113) 08 Jun 2011 13:30:45.94286751 HH:MI:SS.NNNNNNN(24H) SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss.fffffff') AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)] 13:30:45.9428675 YYYY-MM-DD HH:MI:SS(24h) SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss') SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120) 2011-06-08 13:30:45 YYYY-MM-DD HH:MI:SS.NNNNNNN(24h) SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff') SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121) 2011-06-08 13:30:45.9428675 MM/DD/YY HH:MI:SS AM SELECT FORMAT(SYSDATETIME(), 'MM/dd/yy h:mm:ss tt') SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22) 06/08/11 1:30:45 PM YYYY-MM-DD SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd') SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23) 2011-06-091 HH:MI:SS (24h) SELECT FORMAT(SYSDATETIME(), 'HH:mm:ss') SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24) 13:30:45 YYYY-MM-DD HH:MI:SS.NNNNNNN SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd HH:mm:ss.fffffff') SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25) 2011-06-08 13:30:45.94286751 YYYY-MM-DDTHH:MM:SS:NNNNNNN SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-ddTHH:mm:ss.fffffff') SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126) 2011-06-08T13:30:45.9428675 DD Mon YYYY HH:MI:SS.NNNNNNNAM 1 SELECT FORMAT(SYSDATETIME(), 'dd MMM yyyy h:mm:ss.ffffffftt') SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130) 08 Jun 2011 1:30:45.9428675PM1 DD/MM/YYYY HH:MI:SS.NNNNNNNAM SELECT FORMAT(SYSDATETIME(), 'dd/MM/yyyy h:mm:ss.ffffffftt') SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131) 08/06/2011 1:30:45.9428675PM Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function, together with the corresponding FORMAT function. Extended Date Formats Date Format FORMAT Function CONVERT Function Sample Output YY-MM-DD SELECT FORMAT(SYSDATETIME(), 'yy-MM-dd') AS [YY-MM-DD] SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 20), 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 11), '/', '-') AS [YY-MM-DD] 11-06-08 YYYY-MM-DD SELECT FORMAT(SYSDATETIME(), 'yyyy-MM-dd') AS [YYYY-MM-DD] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 111), '/', '-') AS [YYYY-MM-DD] 2011-06-08 YYYY-M-D SELECT FORMAT(SYSDATETIME(), 'yyyy-M-d') AS [YYYY-M-D] SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M-D] 2011-6-8 YY-M-D SELECT FORMAT(SYSDATETIME(), 'yy-M-d') AS [YY-M-D] SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M-D] 11-6-8 M-D-YYYY SELECT FORMAT(SYSDATETIME(), 'M-d-yyyy') AS [M-D-YYYY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-D-YYYY] 6-8-2011 M-D-YY SELECT FORMAT(SYSDATETIME(), 'M-d-yy') AS [M-D-YY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-D-YY] 6-8-11 D-M-YYYY SELECT FORMAT(SYSDATETIME(), 'd-M-yyyy') AS [D-M-YYYY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D-M-YYYY] 8-6-2011 D-M-YY SELECT FORMAT(SYSDATETIME(), 'd-M-yy') AS [D-M-YY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D-M-YY] 8-6-11 YY-MM SELECT FORMAT(SYSDATETIME(), 'yy-MM') AS [YY-MM] SELECT RIGHT(CONVERT(VARCHAR(7), SYSDATETIME(), 20), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), SYSDATETIME(), 120), 3, 5) AS [YY-MM] 11-06 YYYY-MM SELECT FORMAT(SYSDATETIME(), 'yyyy-MM') AS [YYYY-MM] SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 120) AS [YYYY-MM] 2011-06 YY-M SELECT FORMAT(SYSDATETIME(), 'yy-M') AS [YY-M] SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY-M] 11-6 YYYY-M SELECT FORMAT(SYSDATETIME(), 'yyyy-M') AS [YYYY-M] SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY-M] 2011-6 MM-YY SELECT FORMAT(SYSDATETIME(), 'MM-yy') AS [MM-YY] SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), SYSDATETIME(), 5), 4, 5) AS [MM-YY] 06-11 MM-YYYY SELECT FORMAT(SYSDATETIME(), 'MM-yyyy') AS [MM-YYYY] SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 105), 7) AS [MM-YYYY] 06-2011 M-YY SELECT FORMAT(SYSDATETIME(), 'M-yy') AS [M-YY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M-YY] 6-11 M-YYYY SELECT FORMAT(SYSDATETIME(), 'M-yyyy') AS [M-YYYY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M-YYYY] 6-2011 MM-DD SELECT FORMAT(SYSDATETIME(), 'MM-dd') AS [MM-DD] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 10) AS [MM-DD] 06-08 DD-MM SELECT FORMAT(SYSDATETIME(), 'dd-MM') AS [DD-MM] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 5) AS [DD-MM] 08-06 M-D SELECT FORMAT(SYSDATETIME(), 'M-d') AS [M-D] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M-D] 6-8 D-M SELECT FORMAT(SYSDATETIME(), 'd-M') AS [D-M] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '-' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D-M] 8-6 M/D/YYYY SELECT FORMAT(SYSDATETIME(), 'M/d/yyyy') AS [M/D/YYYY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/D/YYYY] 6/8/2011 M/D/YY SELECT FORMAT(SYSDATETIME(), 'M/d/yy') AS [M/D/YY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/D/YY] 6/8/11 D/M/YYYY SELECT FORMAT(SYSDATETIME(), 'd/M/yyyy') AS [D/M/YYYY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D/M/YYYY] 8/6/2011 D/M/YY SELECT FORMAT(SYSDATETIME(), 'd/M/yy') AS [D/M/YY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D/M/YY] 8/6/11 YYYY/M/D SELECT FORMAT(SYSDATETIME(), 'yyyy/M/d') AS [YYYY/M/D] SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M/D] 2011/6/8 YY/M/D SELECT FORMAT(SYSDATETIME(), 'yy/M/d') AS [YY/M/D] SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M/D] 11/6/8 MM/YY SELECT FORMAT(SYSDATETIME(), 'MM/yy') AS [MM/YY] SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 3), 5) AS [MM/YY] 06/11 MM/YYYY SELECT FORMAT(SYSDATETIME(), 'MM/yyyy') AS [MM/YYYY] SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 103), 7) AS [MM/YYYY] 06/2011 M/YY SELECT FORMAT(SYSDATETIME(), 'M/yy') AS [M/YY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M/YY] 6/11 M/YYYY SELECT FORMAT(SYSDATETIME(), 'M/yyyy') AS [M/YYYY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M/YYYY] 6/2011 YY/MM SELECT FORMAT(SYSDATETIME(), 'yy/MM') AS [YY/MM] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 11) AS [YY/MM] 11/06 YYYY/MM SELECT FORMAT(SYSDATETIME(), 'yyyy/MM') AS [YYYY/MM] SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 111) AS [YYYY/MM] 2011/06 YY/M SELECT FORMAT(SYSDATETIME(), 'yy/M') AS [YY/M] SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY/M] 11/6 YYYY/M SELECT FORMAT(SYSDATETIME(), 'yyyy/M') AS [YYYY/M] SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY/M] 2011/6 MM/DD SELECT FORMAT(SYSDATETIME(), 'MM/dd') AS [MM/DD] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 1) AS [MM/DD] 06/08 DD/MM SELECT FORMAT(SYSDATETIME(), 'dd/MM') AS [DD/MM] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 3) AS [DD/MM] 08/06 M/D SELECT FORMAT(SYSDATETIME(), 'M/d') AS [M/D] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [M/D] 6/8 D/M SELECT FORMAT(SYSDATETIME(), 'd/M') AS [D/M] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '/' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [D/M] 8/6 MM.DD.YYYY SELECT FORMAT(SYSDATETIME(), 'MM.dd.yyyy') AS [MM.DD.YYYY] SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '.') AS [MM.DD.YYYY] 06.08.2011 MM.DD.YY SELECT FORMAT(SYSDATETIME(), 'MM.dd.yy') AS [MM.DD.YY] SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '.') AS [MM.DD.YY] 06.08.11 M.D.YYYY SELECT FORMAT(SYSDATETIME(), 'M.d.yyyy') AS [M.D.YYYY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.D.YYYY] 6.8.2011 M.D.YY SELECT FORMAT(SYSDATETIME(), 'M.d.yy') AS [M.D.YY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.D.YY] 6.8.11 DD.MM.YYYY SELECT FORMAT(SYSDATETIME(), 'dd.MM.yyyy') AS [DD.MM.YYYY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011 DD.MM.YY SELECT FORMAT(SYSDATETIME(), 'dd.MM.yy') AS [DD.MM.YY] SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11 D.M.YYYY SELECT FORMAT(SYSDATETIME(), 'd.M.yyyy') AS [D.M.YYYY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [D.M.YYYY] 8.6.2011 D.M.YY SELECT FORMAT(SYSDATETIME(), 'd.M.yy') AS [D.M.YY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [D.M.YY] 8.6.11 YYYY.M.D SELECT FORMAT(SYSDATETIME(), 'yyyy.M.d') AS [YYYY.M.D] SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M.D] 2011.6.8 YY.M.D SELECT FORMAT(SYSDATETIME(), 'yy.M.d') AS [YY.M.D] SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M.D] 11.6.8 MM.YYYY SELECT FORMAT(SYSDATETIME(), 'MM.yyyy') AS [MM.YYYY] SELECT RIGHT(CONVERT(VARCHAR(10), SYSDATETIME(), 104), 7) AS [MM.YYYY] 06.2011 MM.YY SELECT FORMAT(SYSDATETIME(), 'MM.yy') AS [MM.YY] SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 4), 5) AS [MM.YY] 06.11 M.YYYY SELECT FORMAT(SYSDATETIME(), 'M.yyyy') AS [M.YYYY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) AS [M.YYYY] 6.2011 M.YY SELECT FORMAT(SYSDATETIME(), 'M.yy') AS [M.YY] SELECT CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) + '.' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [M.YY] 6.11 YYYY.MM SELECT FORMAT(SYSDATETIME(), 'yyyy.MM') AS [YYYY.MM] SELECT CONVERT(VARCHAR(7), SYSDATETIME(), 102) AS [YYYY.MM] 2011.06 YY.MM SELECT FORMAT(SYSDATETIME(), 'yy.MM') AS [YY.MM] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 2) AS [YY.MM] 11.06 YYYY.M SELECT FORMAT(SYSDATETIME(), 'yyyy.M') AS [YYYY.M] SELECT CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YYYY.M] 2011.6 YY.M SELECT FORMAT(SYSDATETIME(), 'yy.M') AS [YY.M] SELECT RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) + '.' + CAST(MONTH(SYSDATETIME()) AS VARCHAR(2)) AS [YY.M] 11.6 MM.DD SELECT FORMAT(SYSDATETIME(), 'MM.dd') AS [MM.DD] SELECT RIGHT(CONVERT(VARCHAR(8), SYSDATETIME(), 2), 5) AS [MM.DD] 06.08 DD.MM SELECT FORMAT(SYSDATETIME(), 'dd.MM') AS [DD.MM] SELECT CONVERT(VARCHAR(5), SYSDATETIME(), 4) AS [DD.MM] 08.06 MMDDYYYY SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 101), '/', '') AS [MMDDYYYY] 06082011 MMDDYY SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 1), '/', '') AS [MMDDYY] 060811 DDMMYYYY SELECT FORMAT(SYSDATETIME(), 'ddMMyyyy') AS [DDMMYYYY] SELECT REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', '') AS [DDMMYYYY] 08062011 DDMMYY SELECT FORMAT(SYSDATETIME(), 'ddMMyy') AS [DDMMYY] SELECT REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', '') AS [DDMMYY] 080611 MMYYYY SELECT FORMAT(SYSDATETIME(), 'MMyyyy') AS [MMYYYY] SELECT RIGHT(REPLACE(CONVERT(VARCHAR(10), SYSDATETIME(), 103), '/', ''), 6) AS [MMYYYY] 062011 MMYY SELECT FORMAT(SYSDATETIME(), 'MMyy') AS [MMYY] SELECT RIGHT(REPLACE(CONVERT(VARCHAR(8), SYSDATETIME(), 3), '/', ''), 4) AS [MMYY] 0611 YYYYMM SELECT FORMAT(SYSDATETIME(), 'yyyyMM') AS [YYYYMM] SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 112) AS [YYYYMM] 201106 YYMM SELECT FORMAT(SYSDATETIME(), 'yyMM') AS [YYMM] SELECT CONVERT(VARCHAR(4), SYSDATETIME(), 12) AS [YYMM] 1106 Month DD, YYYY 1 SELECT FORMAT(SYSDATETIME(), 'MMMM dd, yyyy') AS [Month DD, YYYY] SELECT DATENAME(MONTH, SYSDATETIME())+ ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ', ' + DATENAME(YEAR, SYSDATETIME()) AS [Month DD, YYYY] June 08, 2011 1 Mon YYYY 1 SELECT FORMAT(SYSDATETIME(), 'MMM yyyy') AS [Mon YYYY] SELECT LEFT(DATENAME(MONTH, SYSDATETIME()), 3) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Mon YYYY] Jun 2011 1 Month YYYY1 SELECT FORMAT(SYSDATETIME(), 'MMMM yyyy') AS [Month YYYY] SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + DATENAME(YEAR, SYSDATETIME()) AS [Month YYYY] June 20111 DD Month 1 SELECT FORMAT(SYSDATETIME(), 'dd MMMM') AS [DD Month] SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME()) AS [DD Month] 08 June 1 Month DD 1 SELECT FORMAT(SYSDATETIME(), 'MMMM dd') AS [Month DD] SELECT DATENAME(MONTH, SYSDATETIME()) + ' ' + RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) AS [Month DD] June 08 1 DD Month YY 1 SELECT FORMAT(SYSDATETIME(), 'dd MMMM yy') AS [DD Month YY] SELECT CAST(DAY(SYSDATETIME()) AS VARCHAR(2)) + ' ' + DATENAME(MM, SYSDATETIME()) + ' ' + RIGHT(CAST(YEAR(SYSDATETIME()) AS VARCHAR(4)), 2) AS [DD Month YY] 08 June 111 DD Month YYYY 1 SELECT FORMAT(SYSDATETIME(), 'dd MMMM yyyy') AS [DD Month YYYY] SELECT RIGHT('0' + DATENAME(DAY, SYSDATETIME()), 2) + ' ' + DATENAME(MONTH, SYSDATETIME())+ ' ' + DATENAME(YEAR, SYSDATETIME()) AS [DD Month YYYY] 08 June 2011 1 Mon-YY 1 SELECT FORMAT(SYSDATETIME(), 'MMM-yy') AS [Mon-YY] SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), SYSDATETIME(), 6), 6), ' ', '-') AS [Mon-YY] Jun-08 1 Mon-YYYY 1 SELECT FORMAT(SYSDATETIME(), 'MMM-yyyy') AS [Mon-YYYY] SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), SYSDATETIME(), 106), 8), ' ', '-') AS [Mon-YYYY] Jun-2011 1 DD-Mon-YY1 SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yy') AS [DD-Mon-YY] SELECT REPLACE(CONVERT(VARCHAR(9), SYSDATETIME(), 6), ' ', '-') AS [DD-Mon-YY] 08-Jun-111 DD-Mon-YYYY 1 SELECT FORMAT(SYSDATETIME(), 'dd-MMM-yyyy') AS [DD-Mon-YYYY] SELECT REPLACE(CONVERT(VARCHAR(11), SYSDATETIME(), 106), ' ', '-') AS [DD-Mon-YYYY] 08-Jun-2011 1 1 To make the month name in upper case, simply use the UPPER string function.

Setting a Proxy Account to run SqlServer Integration Services(SSIS) 2012 Packages

SQL Server

https://gqbi.wordpress.com/2014/01/30/setting-up-a-proxy-account-to-run-sql-server-integration-services-ssis-2012-packages/

Below are the steps that we had to do in order for us to create and setup a Proxy Account to run our SSIS Project. As well as allowing the domain user to have access to the job so that they could create and edit the job which ran the SSIS Project.

NOTE: When setting up all the SQL Settings below you need to have DBA Access

NOTE: This is all completed on SQL Server 2012

Creating a Login for the user that is going to be used as the Credential and Proxy Account

The first thing that you need to do is to put the Domain account you are going to use, as a login on your SQL Server system.

This is so that they will be able to log into the SQL Server System.

1.       In SQL Server Management Studio (SSMS), click on Security, then Logins.

2.       Right click and select New Login

a.        

3.       Then in your Login – new Window where it says Login name put in your Domain Account you are going to use

a.        As with our example our Domain Account is the following

                                                               i.      DOMAIN\UserName

4.