Problem with connection to Sql Server 2008 with NHibernate

Got exception:

----> NHibernate.ADOException : Could not execute query
----> NHibernate.ADOException : cannot open connection
----> NHibernate.ADOException : Could not create connection from Driver
----> System.Data.SqlClient.SqlException : A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)


Enabling Remote Connections in SQL Server 2008 - remembered from before that the SQL Server Surface Configuration Tool is not part of 2008.

It has been replace with the SQL Server Configuration Manager. I personally find the old version to be more user-friendly, but either way, it gets the job done. To enable remote connections in SQL Server 2008:

1. Open the SQL Server Configuration Manager (Start > SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager
2. Navigate to the SQL Server Network Configuration > Protocols for MSSQLSERVER node in the tree view
3. Enable TCP/IP and Named Pipes (you’ll be warned that these changes will not apply until you the service is shut down)
4. Restart the SQL Server Service

This did not work :(

Checked the application log and found this:

Login failed for user 'exampleLogin'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ]

Open DB in Microsoft Management Studio, click properties and select Security Page and change option.

Another restart of the sql service is required.

:)

XPath/XQuery SQL counting repeatable nodes in SQL Server 2005

XML is represented in a field, vchr_Xml on table A, trying to count entities - can use xpath in the sql/xquery to get the count of repeatable nodes.

select convert(int, convert(varchar(max),
convert(xml,{a.ntext_xmlRepresentation).query('count(/Entities/Entity)')))

from [DB].dbo.tbl_A A where A.uid_id = [GUID]

Useful system views on sql server - there are loads but here are the useful ones

all abbreviated sys.*

sys.sql_logins Lists all users who can access the database.
sys.columns Lists all columns on database
sys.tables Lists all tables on database
sys.assemblies Lists all assemblies on database
sys.foreign_keys Lists all foreign keys on database
sys.triggers Lists all triggers on database
sys.procedures Lists all procedures on database