How to configure SQL Server for ASP.NET SQL Server Session State <sessionState mode="SQLServer" ../>

On your prompt (doesn’t matter if you’re on the SQL server machine or not), run the below command:

”system root”\Microsoft.NET\Framework\”version number”>aspnet_regsql.exe –S “server” -U sa -P “Password” -ssadd -sstype p

for eg:

C:\Windows\Microsoft.NET\Framework\v2.0.50727>aspnet_regsql.exe -S MySQLServerInstanceName -U sa -P password -ssadd -sstype p

OR

C:\WINNT\Microsoft.NET\Framework\v4.0.21006>aspnet_regsql.exe -S MySQLServerInstanceName -U sa -P password -ssadd -sstype p

Now you have ASPState database on your servers with the tables, stored procedures, jobs needed to handle SQL server mode session state.

.. more.

Table-Value and Temp Tables

Table-Value Parameters
Table-value parameters offer more flexibility and better performance than temporary tables to pass a list of parameters. Table-value parameters do not acquire locks for the initial population of data from a client and do not cause a statement to recompile.

Table-value parameters offer the following benefits:
·Provide a simple programming model.
·Enable inclusion of complex business logic in a single routine.
·Reduce round trips to the server.
·Include a table structure of different cardinality.
·Enable strongly typed and set-oriented queries.
·Enable the client to specify sort order and unique keys.

Table-value parameters have the following restrictions:
·Statistics are not maintained on columns of table-value parameters.
·Table-value parameters must be passed as input READONLY parameters to Transact-SQL routines.
·DML operations such as UPDATE, DELETE, or INSERT cannot be performed on a table-value parameter in the body of a routine.
·Table-value parameters cannot be used as the target of a SELECT INTO or INSERT EXEC statement. Table-value parameters can be in the FROM clause of SELECT INTO, or in the INSERT EXEC string or stored procedure.

Source: http://bit.ly/3rmMLK

.. more.

How to create SQL Server Login programmatically?

Q. How to create SQL Server Login programmatically?

I got this question from Daniweb C# forum, and of course when I need to do something against SQL Server object from .net I go to SMO (SQL Server Management Objects) which provides a great functionalities to do anything with SQL Server instead of going on SSMS and create some scripts then embedding them into stored procedure then call it…. off…

Anyway to avoid showing how much I’m talkative…

Create any C# project type (Windows, Class Library or even Console one), add reference to Microsoft.SqlServer.Smo, Microsoft.SqlServer.ConnectionInfo
and Microsoft.SqlServer.SqlEnum

Server sqlServerInstance = new Server(                 new Microsoft.SqlServer.Management.Common.ServerConnection(                 new System.Data.SqlClient.SqlConnection("Data Source=.;Initial Catalog=Master;Integrated Security=True")));// your connection string I place mine for illustration..             Login loginObj = new Login(sqlServerInstance, @"DomainName\UserName");             loginObj.DefaultDatabase = "Master";             loginObj.LoginType = LoginType.WindowsUser;             loginObj.Enable();             loginObj.Create("password"); //set the password             //there're many properties to do some tasks related to Login object...

If you used LoginType.WindowsUser, be sure to provide valid windows username and if you aren’t on Domain use the machine name instead.

If you need to create SQL login use LoginType.SqlLogin…

You can explore Login class more on http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.login.create.aspx

.. more.