Your Information Leaked?

Below is a good site which you can use to check whether your data was compromised in any data breach happened. This is free for personal use but for enterprises they can pay and use the service.

Have I Been Pwned: Check if your email has been compromised in a data breach

The site is the work of an Australian security researcher who is a Microsoft regional director named Troy Hunt. He is also a MVP and an internationally active speaker in web security space. 

Microsoft.Ace.OLEDB.12.0 Provider Not Registered

Recently while trying to import some data from Excel onto SQL on a new machine I got the following error at SQL Server Import and Export Wizard Step 2. After doing some searching found the solution is to install Office 2007 Data Connectivity Components System Driver.

If you need to  get it fixed use the following link to get it from Microsoft Download Center.

http://www.microsoft.com/en-us/download/details.aspx?id=23734

Importing Data into SQL

If you need to import data from a file, this can be achieved by using SQL Bulk Insert command. Recently I did use this method to import some 500 000 data from few comma separated value (CSV) files and thought to share it with you. What you need to remember is, if you are importing data from more than one file the data should have the same format through out the files.

This is a sample set of data I imported into my table from the file named File1.csv.

Login,Name,Date,Result,Pass

U0001,Roman Silva,1/10/2010 17:23,100,TRUE

U0002,Anthony Don,28/09/2010 10:01,70,TRUE

U0003,Saman Perera,16/09/2010 11:31,90,TRUE

U0004,Silvia Raz,26/09/2010 22:11,40,FALSE

U0005,Rebecca Maine,18/09/2010 11:30,100,TRUE

I used the following script to create a temporary table for my imported data.

1. SET ANSI_NULLS ON
2. GO
3.  
4. SET QUOTED_IDENTIFIER ON
5. GO
6.  
7. CREATE TABLE [dbo].[TABLENAME_ImportedData](
8.     [LoginId] [NVARCHAR](50) NOT NULL,
9.     [Name] [NVARCHAR](200) NULL,
10.     [Date] [DATETIME] NULL,
11.     [Result] [INT] NULL,
12.     [Pass] [NVARCHAR](8) NULL
13. ) ON [PRIMARY]
14.  
15. GO

To fetch data from the file I used the following script.

1. BULK INSERT [TABLENAME_ImportedData]
2. FROM 'D:\DataFiles\File1.csv'
3. WITH (
4.          FIELDTERMINATOR =',', -- Since my columns are seperated using commas (,).
5.          ROWTERMINATOR ='\n',  -- Since each data row is in its own line.
6.          FIRSTROW = 2          -- Since my first row is having column names. Please note FIRSTROW is not recommended to skip the column names.
7.       )

While running the script I faced an issue with the date since my server was set to US English as the default language. In US English the dates should be in MDY format. So I had 3 choices, either to change the date formats on my data files, change the server default language to another language which has its date format as DMY or change the date format of the server. I used the easy way to change the date format of the server by running  the following command.

1. SET DATEFORMAT dmy

If you like to change SQL server default language and need help please read my article on that.

Changing SQL Server Default Language

If you needed to change the default language of a SQL Server login you can do so by using sp_defaultlanguage command.

First determine the current default language by using the @@language variable as follows.

1. SELECT @@language

Then choose the language you want to change to from the available languages. You can find the available languages by the following command.

1. EXEC sys.sp_helplanguage

After selecting the language wanted to change, run the following command to change the default language, I am changing it to British using the following command.

1. DECLARE @Login NVARCHAR(30)
2. SET @Login = SYSTEM_USER
3. EXEC sp_defaultlanguage @Login, 'British'

Run the @@language to see whether the default language is changed as you wanted. Do not forget to use a new query window to see the changes.

Creating Database Table and Moving Data

When the need comes to port tables from one server / database to another server / database there is an easy way than creating the tables manually and importing data into table.

 

By using the following query you can import the table structure and the data of the required table.

1. SELECT *
2. INTO [DestinationServerName\SQLServerInstanceName].[DestinationDatabaseName].[OwnerName].[DestinationTableName]
3. FROM [SourceServerName\SQLServerInstanceName].[SourceDatabaseName].[OwnerName].[SourceTableName]

 

Using the above method you can transfer tables between different databases, Server instances or different database servers.

Consider the following example in which I am transferring the Customers table from Northwind database to my Test database.

 

1. SELECT *
2. INTO [Test].[dbo].[Customers]
3. FROM [Northwind].[dbo].[Customers]

 

Here SELECT statement will behave the same way as in a normal SELECT * FROM TableName statement meaning you can use all the techniques used in SELECT statements to filter the data needed to be ported. For example following query will only bring customers who are from UK.

1. SELECT *
2. INTO [Test].[dbo].[Customers]
3. FROM [Northwind].[dbo].[Customers]
4. WHERE Country = 'UK'

 

One thing to remember is, this method will not import the keys, meaning if you had a primary key set to CustomerId it will not exist in your new table. So you need to add the keys (primary and secondary) to the new table to match it with the source table. If you had identity specification turned on in your source table this will set it for you, so you do not need to set it again.

Business Data Catalog - BDC

As of Microsoft, Business Data Catalog is a new feature introduced with SharePoint 2007 which can be used to integrate business data from backend server {Line Of Business (LOB)} applications.

There are few good articles I found in the following web sites if you are interested to read more.

• http://msdn.microsoft.com/en-us/library/ms563661.aspx
• BDC Purpose and Technical Architecture
• Application Definition File (ADF) and its Development
• Developing an ADF to Connect to Web Services
• Consuming Business Data through Web Parts and SharePoint Lists
• Implementing Enterprise Search with Business Data
• Integrating User Profiles with Business Data
• Working with the BDC Object Model
• Using Actions in BDC
• http://blah.winsmarts.com/2007-4-SharePoint_2007__BDC_-_The_Business_Data_Catalog.aspx