Importing Excel Data into SQL Server

Posted by Dan | Posted in Databases, SQL Server | Posted on 06-11-2014

0

Sometimes it can be a pain. Why? Because SSMS (or should I say SSIS) uses the Windows Excel driver, and it infers data types of the columns incorrectly, and will choke when you import. Sometimes it will work, sometimes not. So you have to import it as text. However, first you have to re-save the Excel file as a CSV (which will by default use commas as delimiters, and quotes for qualifiers).

So…

Step 1:
In Excel, save the file as a CSV, in this case, file.csv.

Step 2:
In SSMS, under the Import wizard, choose “Flat File Source:

Step 3:
Leave everything default, except use a one quote in the qualifier textbox:

STEP 4:
In the Advanced section, select all the columns and make sure the OutputColumnWidth is something big enough, larger than the size of the largest column. In this case, we’ll make it 150. Make sure the DataType is string [DT_STR]

Step 5:
Leave the rest of the settings except the target database/table where you’re importing, and Finish the import.

Step 6:
Done.

Show Network Requests in Chrome from Silverlight / Flash

Posted by Dan | Posted in C#, Chrome, Web Development | Posted on 06-11-2014

0

The latest versions of Chrome don’t show http requests from Silverlight. To view it, run the browser with the following switch:

-disable-direct-npapi-requests

Check out more details.

Better SSMS Objects Management with SQLTreeo

Posted by Dan | Posted in Databases, SQL Server | Posted on 06-11-2014

0

This is so cool and handy. You can hide and organize database objects (procs, tables, views, databases) in folders. This organization is saved locally to your machine, on a the server level, via extended properties. If you decide to save the info on the server, someone else who has the extension will view it organized accordingly.

Check it out.

How to Copy the Command Output to Windows Clipboard

Posted by Dan | Posted in Automation / Scripting, Systems | Posted on 06-11-2014

0

This is just a quick little tip. You can pipe the results of a console command to the clipboard by:

dir /w | clip

Then in windows just paste away!

Getting Intellisense from JavaScript File

Posted by Dan | Posted in JavaScript, Web Design | Posted on 06-11-2014

0

Let’s say you have main.js and you want intellisense from objects defined in BankAccount.js. You would have to do the following in main.js:

/// <reference path="BankAccount.js" />
 
function run() {
 
  var ba = new BankAccount();
 
}

and BankAccount.js is just:

function BankAccount() {
 
  var total = 0;
 
  this.GetTotal = function () {
    return total;
  }
 
  this.Deposit = function (n) {
    this.total = n;
  }
 
}

This is provided that both .js files are in the same directory – but they don’t have to be – use relative path?

Learn more about it here: http://msdn.microsoft.com/en-us/library/bb385682.aspx

Restoring an MDF/LDF from Another Machine

Posted by Dan | Posted in SQL Server | Posted on 09-08-2013

0

If you need to restore an MDF/LDF from one machine (source) into another machine (target), you copy the source’s MDF/LDF file to the target machine. Then start the target server in single user mode. Then run SSMS as Administrator on the target machine and restore the files. If you don’t run it as Admin, you’ll get the error listed here:

http://stackoverflow.com/questions/2330439/access-is-denied-when-attaching-a-database

http://stackoverflow.com/questions/4661013/sql-server-attaching-database-access-denied

Who’s Locking The Table

Posted by Dan | Posted in SQL Server | Posted on 09-08-2013

0

This is the script I use to view who/what is locking the table in SQL Server:

SELECT  l.request_session_id AS SessionID,
        o.Name AS 'Table Locked',
        DATEDIFF(SECOND, AT.Transaction_begin_time, GETDATE()) AS 'Duration (Seconds)',
        FORMAT(AT.Transaction_begin_time, 'yyyy-mm-dd h:mm:ss tt') 'Started On',
        es.login_name 'User',
        es.[program_name] 'Program',
        COUNT(*) AS Locks
FROM    sys.dm_tran_locks l
        JOIN sys.partitions p ON p.hobt_id = l.resource_associated_entity_id
        JOIN sys.objects o ON o.OBJECT_ID = p.OBJECT_ID
        JOIN sys.dm_exec_sessions es ON es.session_id = l.request_session_id
        JOIN sys.dm_tran_session_transactions st ON es.session_id = st.session_id
        JOIN sys.dm_tran_active_transactions AT ON st.transaction_id = AT.transaction_id
WHERE   resource_database_id = DB_ID()
        AND o.Type = 'U'
GROUP BY AT.Transaction_begin_time,
        l.request_session_id,
        o.Name,
        es.login_name,
        [program_name]

Filesharing and transfering across EC2 Instances

Posted by Dan | Posted in AWS | Posted on 09-08-2013

0

By default, the port is off for file transfer. You have to enable the ports (on both instances you want to share across) on the servers:

139
445

Delete TFS Cache from Local Machine

Posted by Dan | Posted in Visual Studio | Posted on 09-08-2013

0

A lot of times, TFS acts flaky and the best thing to do is to clear the local cache.

DELETE THE CONTENTS OF:

C:\Users\[User]\AppData\Local\Microsoft\Team Foundation\5.0\Cache

Rename Computer Name in Case TFS Gives you Workspace Problems

Posted by Dan | Posted in Visual Studio | Posted on 09-08-2013

0

When you are setting up TFS through Visual Studio, and are trying to map a drive, TFS may give you an error saying that you can’t and that profile exists on the TFS server. It may be because the account+computername+workspace (they’re all bound) is already on the server.

The way around this is to rename the machine. This may have happened because the virtual machine was copied.

Be aware: IF YOU DO THIS, other programs that have the computer name saved to it under privileges, like SQL Server, may have to be changed. In SQL Server, you may have to login via the sa account or through single-user mode.