SQL Server Database Publishing wizard

Microsoft has launched a RC1 Database Publishing wizard. This is pretty neat utility.
We can script the database objects ( tables , SPS,UDFs and others ) and also the DATA of the database. The DATA part of the scripting was particularly new.
http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard
has the free download.

ASPNet account and SQL Server

If you use Integrated Security = True in the DB connection string for a website, make sure that the machine's ASPNET account( machine name\aspnet account ) has access to the database.

Very simple information but effective at times.

Remote connections to SQL Server 2005 Express

Try connecting a SQL server 2005 Express server from a remote machine, you would get an error.

Error would be something like this
============================
Message : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 28 - Server doesn't support requested protocol)

This is because the default setting for SQL server 2005 Express allows local connections only.

For configuring the remote connections ,

1. go to the SQL Server Surface area configuration application and click on Remote connections,select Local and Remote connections
2. Restart the SQL Server service

SQL Server 2005 - Adding an user

Should be a very basic stuff for most.

But it took me sometime.Therefore blogging it so that I do not waste a minute in it later on

1. Add the user to the server level by ensuring that in the User Mapping section ( Right click Properties ) , the default schema for the databases is dbo.
2. The role membership in the User Mapping section ( Right click Properties ) should be db_owner
3. Go to the respective database such as Pubs where the user has been added say user1
4. Right click the properties of user1
5. Ensure that the section( Schemas owned by the user ) has the db_owner checked
6. Ensure that the section( Database role membership) has the db_owner checked

Local SQL Server 2005 DB and Enterprise DAB

Experiment1
============
Add a SQL database from the Solution explorer using Add New item.
Once you add it , the connection string would be present in the app.config
add name="My.MySettings.Database2ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=DataDirectory\Database2.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient"

Use the Enterprise Library DAB and change the default database to point to My.MySettings.Database2ConnectionString
dataConfiguration defaultDatabase="My.MySettings.Database2ConnectionString"

Experiment2
===========
You can also have a .MDF and a .LDF file in the file system as in
C:\Ambarish\Database2.mdf

Change the connection string in the app.config to
add name="My.MySettings.Database2ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Ambarish\Database2.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient"

=========================================================

Use the MVC pattern ( here only the controller code is present ) to connect to the DB

============================================
Public Sub GetTable1Data(ByRef ds As DataSet)
Dim db As Database
Dim strCommand As String
Dim dbCommand As DbCommand
Try
db = DatabaseFactory.CreateDatabase()
strCommand = "dbo.uspGetDataTable1"
dbCommand = db.GetStoredProcCommand(strCommand)
ds = db.ExecuteDataSet(dbCommand)
Catch ex As Exception
Throw ex
Finally
db = Nothing
End Try
End Sub

==============================

SQL Server Express XCOPY

The XCOPY feature of SQL Server Express is a good feature which would help applications to deploy databases very easily.
This is similar to deploying Microsoft Access databases

Sample code from the 101 VB samples (VB101SamplesWinForms\UsingDataGridView)
on how to connect to MDF files have been added
======================================================
Dim connectStringBuilder As New SqlConnectionStringBuilder()
connectStringBuilder.DataSource = ".\SQLEXPRESS"
connectStringBuilder.AttachDBFilename = "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_Data.mdf"
connectStringBuilder.IntegratedSecurity = True
connectStringBuilder.UserInstance = True
==========================================
Some very good links are as follows:

• http://weblogs.asp.net/jackieg/archive/2004/07/23/192255.aspx. Jackie's link also contains a link on how XCOPY works. This is also a good read
• http://msdn2.microsoft.com/en-us/library/ms165716.aspx . Also the named instances portion of the article is important
• http://msdn2.microsoft.com/en-us/library/ms143684.aspx has an article on User Instances. When we are setting the User Instance in the connection string to TRUE, we are using User Instances