Category Archives: MSSQL

MSSQL

Joins explained

I’ve always had a devil of a time with which join does what. This graphic that I found on StackOverflow had the most amazing graphic that finally set it strait for me, hope it serves you as well.

sql_joins_explained

MSSQL

MSSQL – ROW_NUMBER

I had this come up when I was trying to insert the row number for the purpose of populating a SeqNo. This is what it looks like…

ROW_NUMBER() OVER (ORDER BY [Column Name]) as SeqNo

MSSQL

MSSQL charindex

Charindex is a great function for working with emails:

 

left(Email, charindex(‘@’, email) -1) + ‘@invalidemail.com’ as email,

MSSQL

Nmap to find MS SQL server

I’ve had to install several instance of SLQ Express recently and for the life of me I’m having a devil of a time getting external access to these instances.  Between the firewalls and SLQ networking protocols it’s hard to know if you did everything right and will be able to connect remotely with these installations.  Nmap can help, here is the syntax to look for them:

nmap -p T:1433 -sV pds-web -oG tcp_scan_results.txt

MSSQL

Truncating SQL 2008 Transaction Log

Use the following to truncate a log, the TestDbLog is the logical file name of the log file.

USE TestDb 
GO
ALTER DATABASE TestDb SET RECOVERY SIMPLE WITH NO_WAIT
DBCC SHRINKFILE(TestDbLog, 1)
ALTER DATABASE TestDb SET RECOVERY FULL WITH NO_WAIT
GO
MSSQL

Finding column info for a table

Ran across this little gem while working on some integrations, really comes in handy.

SELECT 
    c.name 'Column Name',
    t.Name 'Data type',
    c.max_length 'Max Length',
    c.precision ,
    c.scale ,
    c.is_nullable,
    ISNULL(i.is_primary_key, 0) 'Primary Key'
FROM    
    sys.columns c
INNER JOIN 
    sys.types t ON c.system_type_id = t.system_type_id
LEFT OUTER JOIN 
    sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id
LEFT OUTER JOIN 
    sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE
    c.object_id = OBJECT_ID('Candidate')
C# MSSQL

Writing form data to SQL DB with C#

In having to convert my current VB projects into C#, I ran into an issue trying to write to a DB from a form.  This blog opened my eyes to how this is accomplished and here is the code I used to get the unsubscribe feature to work.


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;

namespace Taleo_UnsubscribeJPN
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
DateTime tstamp = DateTime.Now;
String email = Request.QueryString["email"];
String atsid = Request.QueryString["atsid"];
lblEmail.Text = email;

}

protected void btnRemove_Click(object sender, EventArgs e)
{
String strBrowserInfo = (Server.HtmlEncode(Request.UserAgent));
String strIPAddress = (Server.HtmlEncode(Request.UserHostAddress));
String atsid = Request.QueryString["atsid"];
string connection_string = "Data Source=DB_Server;Initial Catalog=TaleoData;Persist Security Info=True;User ID=tdowner;Password=password";
string query = "insert into OptOutEmail (email, tstamp, processed, atsid, remote_addr, http_user_agent) values ('" + lblEmail.Text + "', '" + DateTime.Now + "', " + 0 + ", '" + atsid + "', '" + strIPAddress + "', '" + strBrowserInfo + "')";
SqlConnection connection1 = new SqlConnection(connection_string);

SqlCommand insertCommand = new SqlCommand(query, connection1);

connection1.Open();

insertCommand.ExecuteNonQuery();

connection1.Close();

Server.Transfer("Confirmation.aspx?email=" + lblEmail.Text);

}
}
}

MSSQL

Finding Size of Tables in MSSQL Database

I was trying to explain to my boss that the SQL database we’re using for the intranet was somewhere in the 400 MB range when he asked ‘What’s taking up all the space?’  I was a little taken aback, I mean databases collect data and that takes up space right?  Well it got me thinking what the hell is taking up all the space.  I found this great query that tells all:)

</pre>
SELECT
 t.NAME AS TableName,
 i.name AS indexName,
 SUM(p.rows) AS RowCounts,
 SUM(a.total_pages) AS TotalPages,
 SUM(a.used_pages) AS UsedPages,
 SUM(a.data_pages) AS DataPages,
 (SUM(a.total_pages) * 8) / 1024 AS TotalSpaceMB,
 (SUM(a.used_pages) * 8) / 1024 AS UsedSpaceMB,
 (SUM(a.data_pages) * 8) / 1024 AS DataSpaceMB
FROM
 sys.tables t
INNER JOIN
 sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
 sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
 sys.allocation_units a ON p.partition_id = a.container_id
WHERE
 t.NAME NOT LIKE 'dt%' AND
 i.OBJECT_ID > 255 AND
 i.index_id <= 1
GROUP BY
 t.NAME, i.object_id, i.index_id, i.name
ORDER BY
 OBJECT_NAME(i.object_id)
<pre>