EppesConsulting Blog

I’ve found this page very
handy when it comes to writing LINQ queries.

From this site:

select @@VERSION

exec sp_server_info

SELECT SERVERPROPERTY(‘ProductLevel’)

Solution 1:

Select Table_name as “Table Without PK”
From Information_schema.Tables
Where Table_type = ‘BASE TABLE’ and
Objectproperty (Object_id(Table_name), ‘TableHasPrimaryKey’) = 0 and

Objectproperty (Object_id(Table_name), ‘IsUserTable’) = 1

Solution 2:

Select [name] as “Table Without PK”

from SysObjects where xtype=’U’ and
id not in
(
Select parent_obj from SysObjects where xtype=’PK’
)

It threw me for a loop when a Cisco CallManager version of a SQL server was on an
abnormal port (not 1433).  It took me a while to find it, but if you want to
connect to a non-standard TCP/IP port from SQL Management Studio, simply connect to
the hostname then a comma with the port number.

Example:  MySqlServer.MyDomain.com,3821

I stole this information from here.

I don’t really set up database servers that often, but every time I do, I struggle
to remember how to set up email functionality.  In SQL 2000 it was “SQL Mail”. 
In SQL 2005, it’s “Database Mail”.

Here’s a good howto on how to set
up SQL 2005 Database Mail.

How do you transfer logins from one SQL server to another:

http://support.microsoft.com/kb/246133/

How do you fix orphaned SQL server users:

http://www.fileformat.info/tip/microsoft/sql_orphan_user.htm

Ever been annoyed that your LINQ connection string isn’t stored in the web.config
file?  Here’s how to fix that:

http://blog.newguid.net/post/Linq-to-Sql-Change-connectionstring-to-load-from-Webconfig.aspx

To me, this is almost a necessity to make applications deployable between environments.

Ever wondered how to bulk script data for a database?  This would be really handy
for default table data during an install, or to copy a database you only have user
level access to.  Enter sp_generate_inserts:

http://vyaskn.tripod.com/code.htm

I needed to re-encrypt (MD5 hash actually) a whole bunch of passwords in a database
table the other day.  As MSSQL doesn’t have anything built-in (that I could find)
to accomplish this, I was about to write a quick LINQ/C# program to do it for me. 
Then I found this example:

http://www.tek-tips.com/viewthread.cfm?qid=1268144&page=1

Adding my compiled assembly to SQL Server and then adding a function that called it
was straight forward:

CREATE FUNCTION [inet].[fn_Md5Hash]

(@clear_text nvarchar(50))

RETURNS nvarchar(100)

AS

EXTERNAL NAME IssueTracker.StoredFunctions.Md5Hash

GO

If anything, it reminded me of calling COM objects from .NET.  The one thing
I forgot when I moved from my development database to my production database: you
need to enable CLR code to do this.  Here’s how:

http://social.msdn.microsoft.com/Forums/en-US/sqlnetfx/thread/2a8af45a-e933-4c5a-8435-575cec61a663/

Oh yeah, and this requires MSSQL 2005 or newer.

http://technet.microsoft.com/en-us/library/aa544774.aspx

Much easier than having to keep the old copy of SQL 2000 around.