Category Archives: MSSQL

MSSQL

Excel – VLOOKUP

Okay if you read this blog you’ll know I’m more of a DB geek than an Excel user but I’m getting tired of every time that I want to do a quick sub-out of data having to load not one but two files into the DB and then write the query to join the two and get what I need. It’s a great way to go but awfully time consuming if it’s not going to be built into a SSIS pack or something of the sort.
So that brings us to the old standard Excel. Actually Excel is awfully handy on the export side of things but using it to import data from a DB or even flat file.
In this case we’ve got two sheets representing two tables with a common key.

  • =VLOOKUP(F2,States!A$2:B$38,2)
  • The $ keeps the range in the middle from moving when you populate down.
  • In this case this is done in G2 where F2 is being looked up.
  • The range is on a second sheet from A2 to B38 where items in A relate to F and the B value will be returned for G.
  • Remember to have the columns in the same order to pull this off.

Just to note, if you are looking at using a field with a tilde, you have to escape it, which looks like this:

=VLOOKUP(SUBSTITUTE(B2,”~”,”~~”),Sheet1!A$2:B$234,2,FALSE)

MSSQL

SQL – Got People Data?

So how many time did you think to yourself, wouldn’t it be nice to have a repository of names an addresses so I can load for testing? If you’re normal you’ve never asked yourself that. If you’re a computer geek it crosses your mind from time to time. Microsoft has generously given us the Adventure Works database and this is perfect for just such repository. You can find the DB on Microsoft’s site and here is a bit of SQL to get you started.

SELECT top 1000
	   replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as Ident
	  --,p2.rowguid
      --,isnull(p2.[Title], '') as Title
      ,isnull(p2.[FirstName], '') as FirstName
      ,isnull(p2.[MiddleName], '') as MiddleName
      ,isnull(p2.[LastName], '') as LastName
      --,isnull(p2.[Suffix], '') as Suffix
	  ,replace(pa.AddressLine1, ',', '-') as AddressLine1
	  ,isnull(pa.AddressLine2, '') as AddressLine2
	  ,pa.City
	  --,sp.StateProvinceCode as State
	  --,sp.CountryRegionCode as Country
	  ,pa.PostalCode
	  ,replace(pe.EmailAddress, '@adventure-works.com', '@invalidemail.com') as EmailAddress
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 1), '') as CellPhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 2), '') as HomePhone
	  ,isnull((select pp.PhoneNumber where pp.PhoneNumberTypeID = 3), '') as WorkPhone
	  ,Lower(LEFT(p2.FirstName, 1) + p2.LastName) as Username
	  ,'taleo123' as Pswd
  FROM [Person].[Person] p2
  join Person.BusinessEntityAddress ba on (p2.BusinessEntityID = ba.BusinessEntityID)
  join Person.Address pa on (ba.AddressID = pa.AddressID)
  join Person.StateProvince sp on (sp.StateProvinceID = pa.StateProvinceID)
  join Person.EmailAddress pe on (p2.BusinessEntityID = pe.BusinessEntityID)
  join Person.PersonPhone pp on (p2.BusinessEntityID = pp.BusinessEntityID)
  where sp.CountryRegionCode = 'US'
order by LastName, FirstName
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>