File x.ps1 cannot be loaded because running scripts is disabled on this system.

When you load a script file in PowerShell ISE and try to run that script, probably you will get this error:

File x.ps1 cannot be loaded because running scripts is
disabled on this system. For more information, see about_Execution_Policies at
http://go.microsoft.com/fwlink/?LinkID=135170. + CategoryInfo: SecurityError: (:) [], ParentContainsErrorRecordExcepti on + FullyQualifiedErrorId : UnauthorizedAccess

This is because the script is blocked from execution due to the execution policy. So you need to set it according to your requirements. For Example I set it as :
Set-ExecutionPolicy -Scope Process -ExecutionPolicy RemoteSigned
And I get my script worked, it allows the execution of script only for current process.

The Scope parameter could have following valid values:

• Process: The execution policy affects only the current Windows PowerShell process.
• CurrentUser: The execution policy affects only the current user.
• LocalMachine: The execution policy affects all users of the computer.

The default is LocalMachine.

References:  https://technet.microsoft.com/en-us/library/hh849812.aspx

Call inline C# from PowerShell

Windows PowerShell is a shell for task automation and configuration management. The shell is based on the .NET framework and it includes a command-line shell and a scripting language. Features include built-in commands (cmdlets) to manage computers in your network. Cmdlets perform common system administration tasks such as managing the registry; managing event logs; controlling services and processes; and using Windows Management Instrumentation.

Apart from shell's cmdlets, there is also flexibility to use C# code, because the shell is based on .Net framework. In this post I demonstrate a way to call inline C# code from the PowerShell script. Following is the script to do so.

$Source = @"
namespace TestNamespace
{
    public static class MyClass
    {
        public static string GetMessage()
        {
            return "Text from C#";
        }
      
        public static int Add(int a, int b)
        {
            return a + b;
        }
    }
}
"@

Add-Type -TypeDefinition $Source -Language CSharp

[TestNamespace.MyClass]::GetMessage()

[TestNamespace.MyClass]::Add(3, 5)

Let review this script:

• The first statement here you see that we put our C# code in a variable named $source (in PowerShell we define variable by placing $ sign with it).
• Next we call PowerShell cmdlet Add-Type, here we define -TypeDefinition is our C# class code, and telling the shell that this script is actually C# code defined by the argument -Language CSharp

    These two steps are required, lets say, to register our C# code, so PowerShell can understand it.
    Now its the time to actually call our code and that is pretty straight forward.

• [TestNamespace.MyClass]::GetMessage() will call our Method which only returns a string.
• [TestNamespace.MyClass]::Add(3, 5) will accept two integer parameters and return the output as integer.

So here we get C# action in PowerShell.

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation'

While restoring SQL backup file from one machine to another, I got this error message:

System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.x\MSSQL\DATA\x.ldf'. (Microsoft.SqlServer.Smo)

Here is what solution I found for this problem.
Carefully check the path showing in error, while backup file is created at first machine it stores the file path of that machine. Now when you are restoring on new machine if the path is same (i.e. server name, and also version etc.) then you get the file restore successfully otherwise you will get this error. So simply change the path according to new machine in "Restore As" option, and you will get fix to this error.

SQL Server Indexes - Definition

One of the most important technique to high performance queries in a SQL Server database is the index. Like we have index in books for fast searching, indexes speed up the querying process by providing fast access to rows in the data tables.

Indexes are of two types, clustered and nonclustered indexes.

Clustered Index

A clustered index is an index that stores the actual data. It is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages. A clustered index stores the actual data rows at the leaf level of the index. An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view. In addition, data in a table is sorted only if a clustered index has been defined on a table. A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Non-Clustered Index

A non-clustered index is just a pointer to the data. It is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Unlike a clustered indexed, the leaf nodes of a nonclustered index contain only the values from the indexed columns and data row pointers , rather than contain the data rows themselves. This means that the query engine must take an additional step in order to locate the actual data.

A row pointer depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.

VisualStudio 2010, Define shortcut keys to Enable/Disable All Breakpoints

In Visual Studio, you can create or delete a single breakpoint by hitting F9, and can delete all breakpoints by Ctrl+Shift+F9 shortcut. It does not provide shortcut keys to disable or enable all breakpoints, but you can define that by yourself.

• Go to Tools > Options popup.
• Enter "DisableAllBreakpoints" in the textbox for Show commands containing:.
• I am using Global option from User new shortcut in: combobox.
• Set focus on Press shortcut keys textbox and hit your shortcut keys you want to set. (e.g. I am using Alt+1)
• Press Assign button
• Now press OK and use your new shortcut keys.(Make sure your newly created shortcut key is selected in Shortcuts for selected command) combobox while hitting OK button.

Similary you can set shortcut key for EnableAllBreakPoints or any other command.

SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

I have installed SQL Server and Management Studio on new machine and facing issues while connecting to SQL Server through remote machines on my local area network. After thorough searching and experimenting I have got resolved the error. Following are the steps(not necessary all) to resolve the issue.

SQL Server should be running

Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node SQL Server 2005 Services, under SQL Server Configuration Manager parent node. SQL Server's state should be Running. SQL Server Browser service should also be Running (you can Start/Stop by right click on service name)

Enable TCP/IP in SQL Server Configuration

Go to All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager. Select node Protocols for SQLServer(your server's instance name) , under SQL Server 2005 Network Configuration.
TCP/IP's state should be Enabled. (can be Enabled/Disabled by right click on Protocol Name)

App Exception for SQL Server TCP Port in Windows Firewall

Go to Conctrol Panel > Windows Firewall > Exceptions (tab). Click button Add Port, enter name and port number 1433. Make sure your entered name is displaying in Programs and Services section, and should be checked while pressing OK button on the tab.

App Exception for sqlbrowser.exe in Windows Firewall

In Firewall window > Exceptions tab, click on Add Program. Locate the sqlbrowser.exe by Browse button. Click OK. At my system, it is located at C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe.

App Exception for sqlservr.exe in Windows Firewall

Similarly add sqlservr.exe in Firewall Exceptions. At my system, it is located at C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn\sqlservr.exe.

Enable Remote Connection

Right click on SQL Server main node in Management Studio, and go to Properties. Select Connections page from Select a Page section. Allow remote connections to this server should be checked.

References

• http://blog.sqlauthority.com/2009/05/21/sql-server-fix-error-provider-named-pipes-provider-error-40-could-not-open-a-connection-to-sql-server-microsoft-sql-server-error/
• http://msdn.microsoft.com/en-us/library/cc646023.aspx

MS sql what is CTE? how to write recusive query in sql?

A common table expression (CTE) can be thought of as a temporary result set defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.

Pseudocode and semantics

The recursive CTE structure must contain at least

one anchor member

The first invocation of the recursive CTE consists of one ( or more) CTE_query_definition(s). Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

• one recursive member.

  The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

The following pseudocode shows the components of a simple recursive CTE that contains a single anchor member and single recursive member.

WITH cte_name ( column_name [,...n] )
AS
(
CTE_query_definition –- Anchor member is defined.
UNION ALL
CTE_query_definition –- Recursive member is defined referencing cte_name.
)
-- Statement using the CTE
SELECT *
FROM cte_name

Example

Let's demontrate the Category table example, we have table with different categories, sub-categories, sub-sub-categroies etc... to Nth level.

We have the following data in our Category table:

CategoryID	Name	ParentID
1	Office	NULL
2	Computer	NULL
3	Books	NULL
4	Office Accessories	1
5	Furniture	1
6	Decoration Items	1
7	LCDs	2
8	CPUs	2
9	Keyboards	2
10	Urdu	3
11	Programming	3
12	ASP.Net	11
13	C#	11
14	VB.Net	11
15	Java	11
16	PHP	11
17	Poetry	10
18	Stories	10
19	Novels	10
20	Kids	18
21	Funny	18

We are interested to get the full category names (from parent category to last child category, e.g. Books > Programming > C# ), we acheive the result implementing the same CTE pseudocode.

WITH CategoryList
AS
(
-- Anchor Query Member
SELECT parent.CategoryID, CONVERT(VARCHAR(50), Parent.Name) as Name, parent.ParentID
FROM Category as parent
WHERE parent.ParentID IS NULL

UNION ALL

-- Recursive Query Member
SELECT child.CategoryID, CONVERT(VARCHAR(50), CL.Name + ' > ' + child.Name) as Name, child.ParentID
FROM Category as child
INNER JOIN CategoryList as CL ON child.ParentID = CL.CategoryID -- Can be think of as the termination condition
WHERE child.ParentID IS NOT NULL
)
SELECT *
FROM CategoryList

That's simple, you will get the required output as:

CategoryID	Name	ParentID
1	Office	NULL
2	Computer	NULL
3	Books	NULL
10	Books > Urdu	3
11	Books > Programming	3
12	Books > Programming > ASP.Net	11
13	Books > Programming > C#	11
14	Books > Programming > VB.Net	11
15	Books > Programming > Java	11
16	Books > Programming > PHP	11
17	Books > Urdu > Poetry	10
18	Books > Urdu > Stories	10
19	Books > Urdu > Novels	10
20	Books > Urdu > Stories > Kids	18
21	Books > Urdu > Stories > Funny	18
7	Computer > LCDs	2
8	Computer > CPUs	2
9	Computer > Keyboards	2
4	Office > Office Accessories	1
5	Office > Furniture	1
6	Office > Decoration Items	1

References

http://msdn.microsoft.com/en-us/library/ms175972.aspx