woensdag 28 maart 2012

25 ways creating a table in SQL Server 2012

Introduction
In this blogpost I'll describe the different ways to create a table in SQL Server 2012 (and before). Mostly, creating a table can be done with the CREATE TABLE Statement but there are other ways too. For instance with the SELECT INTO or a virtual table. I've gathered a number of  ways to create a table:
1) Create table with a Primary key (columnlevel).
2) Create table with a Primary key (tablelevel).
3) Create table with a foreign key (column level).
4) Create table with a foreign key (table level).
5) Create table with a DEFAULT.
6) Create table with a CHECK.
7) Create table with a ON DELETE CASCADE Foreign Key relation.
8) Create table with a ON DELETE SET NULL Foreign Key relation.
9) Create table with Row compression.
10) Create table with a sparse column.
11) Use a SEQUENCE object as an alternative for the identity (autonumbering).
12) Create a table with  FILESTREAM.
13) Create a table with a Filetable.
14) Create a Table with a partition.
15) Create a temporary table.
16) SELECT INTO.
17) Create a computed column based table.
18) Create a persisted computed column based table.
19) Create a table with a GUID.
20) Declare a TABLE variable.
21) Creating a Table in a specific Filegroup.
22) Creating a table with an XML column typed to an XML schema collection.
23) CREATE A Table with a Index with a couple of options.
24) CREATE TABLE with an attribute with specific collation.
25) Create a Derived Table.

Creating table examples


1) Create table with a Primary key (columnlevel)
The following CREATE TABLE statement creates a simple table with a primary key at column level.


CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL PRIMARY KEY, 
[CustomerTypeDescription] NVARCHAR(50) NULL
)


2) Create table with a Primary key (tablelevel)
The following CREATE TABLE statement creates a simple table with a primary key at table level.


CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL, 
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID])
)

3) Create table with a foreign key (column level) 
The following CREATE TABLE statement creates a simple table with a primary key at columnlevel  and a foreign key relation (also at column level) to another table.

CREATE TABLE [dbo].[Order]
(
[OrderID] INT NOT NULL PRIMARY KEY,

[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]),

[Ordernumber] NCHAR(10) NULL, 

[OrderAmount] MONEY NULL, 

)

4) Create table with a foreign key (table level)
The following CREATE TABLE statement creates a simple table with a primary key at columnlevel  and a foreign key relation to another table. 

CREATE TABLE [dbo].[Order]
(
[CustomerID] INT NOT NULL PRIMARY KEY, 
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL, 
CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID])
)

5) Create table with a DEFAULT
In the next CREATE TABLE Statement a default is introduced.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL DEFAULT 'UNKNOWN'
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
)

6) Create table with a CHECK
With the Check constraint you can control the values that are entered. In this case the amount entered should be greater than 0.

CREATE TABLE [dbo].[Order]
(
[OrderID] INT NOT NULL PRIMARY KEY,
[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]),
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL
CONSTRAINT chk_OrderAmount CHECK ([OrderAmount]>0)
)

7) Create table with a ON DELETE CASCADE Foreign Key relation
You can extend the forign key relation constraint with some more sophisticated action. The ON DELETE specifies what action happens to rows in the table, if those rows have a referential relationship and the referenced row is deleted from the parent table. With the CASCADE option the corresponding rows are deleted from the referenced table.

CREATE TABLE [dbo].[Order]
( 
[OrderID] INT NOT NULL PRIMARY KEY,
[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]) ON DELETE CASCADE,
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL CONSTRAINT chk_OrderAmount CHECK ([OrderAmount]>0)
)

8) Create table with a ON DELETE SET NULL Foreign Key relation
In the next CREATE TABLE statement The record in the referenced table is not deleted, but the Forign key   field is set to NULL. Off course, the Foreign key field should be NULL.

CREATE TABLE [dbo].[Order]
( 
[OrderID] INT NOT NULL PRIMARY KEY,
[CustomerID] INT NOT NULL CONSTRAINT [FK_Order_ToTable] FOREIGN KEY ([CustomerID]) REFERENCES [Customer]([CustomerID]) ON DELETE SET NULL,
[Ordernumber] NCHAR(10) NULL, 
[OrderAmount] MONEY NULL CONSTRAINT chk_OrderAmount CHECK ([OrderAmount]>0)
)

9) Create table with Row compression
With row compression you can reduce the size of the database. In addition, data compression improves the I/O because of less data that is stored in the pages and the query needs fewer pages to load.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL DEFAULT 'UNKNOWN' CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
)
WITH (DATA_COMPRESSION = ROW)

10) Create table with a sparse column
Sparse columns are ordinary columns that have an optimized storage for null values.  

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
    [CustomerTypeDescription] NVARCHAR(50) SPARSE NULL
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
)


11) Use a SEQUENCE object as an alternative for the identity (autonumbering)
SEQUENCE is an object that has a start value, increment value and end value defined and this sequence can be added to a column. A SEQUENCE can be shared between multiple tables in contrast with the autonumbering option of a field.

DROP SEQUENCE [dbo].[Sequence_Numbering]
GO


CREATE SEQUENCE [dbo].[Sequence_Numbering]
AS BIGINT
START WITH 5
INCREMENT BY 3
NO MAXVALUE
NO CYCLE
CACHE 10
GO


DELETE FROM [dbo].[CustomerType]


SELECT NEXT VALUE FOR [dbo].[Sequence_Numbering]


Insert into [dbo].[CustomerType] ([CustomerTypeID], [CustomerTypeDescription]) VALUES  (NEXT VALUE for [dbo].[Sequence_Numbering],'Small')


Insert into [dbo].[CustomerType] ([CustomerTypeID], [CustomerTypeDescription]) VALUES  (NEXT VALUE for [dbo].[Sequence_Numbering],'Medium')


Insert into [dbo].[CustomerType] ([CustomerTypeID], [CustomerTypeDescription]) VALUES  (NEXT VALUE for [dbo].[Sequence_Numbering],'Large')


SELECT * FROM [dbo].[CustomerType]


12) Create a table with  FILESTREAM
FILESTREAM is introduced in SQL Server 2008 and enables storing blobs in SQL Server instead of storing it seperately on the file system.

CREATE TABLE [dbo].[Customer]
(
[CustomerID] INT NOT NULL PRIMARY KEY, 
[CustomerName] NVARCHAR(50) NULL, 
[CustomerAddress] NVARCHAR(50) NULL, 
[CustomerTypeID] SMALLINT NULL, 
[CustomerGender] CHAR(1) NULL, 
[CustomerPictureID] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE,
[CustomerPicture] VARBINARY(MAX) FILESTREAM NULL
CONSTRAINT [FK_Customer_ToTable] FOREIGN KEY (CustomerTypeID) REFERENCES [CustomerType](CustomerTypeID)  ON DELETE CASCADE
)

13) Create a table with a FILETABLE
FILETABLE is new in SQL Server 2012. File Tables are special tables which can be used to store your files and documents and access them from windows application as if they are stored in the file system. File Tables are built on top of File Stream feature. 

-- Creating a new FileTable  
 CREATE TABLE [dbo].[CustomerFiles]   
 AS FILETABLE ON [PRIMARY]   
 FILESTREAM_ON [FG_2]
 WITH (  
    FILETABLE_DIRECTORY = N'CustomerFileStreamGroup',  
    FILETABLE_COLLATE_FILENAME = DATABASE_DEFAULT  
 )  
 GO 

14) Create a Table with a partition
To improve the scalability and manageability of large tables you can implement the partition functionality. This enables you to manage (Very) Large Database in a more flexible way.

--- Step 2 : Create Partition Range Function
CREATE PARTITION FUNCTION CREATETable_PartitionFunc (int)
AS RANGE LEFT FOR
VALUES (2);
GO


CREATE PARTITION SCHEME CREATETable_PartitionSchema
AS PARTITION CREATETable_PartitionFunc
TO ([PRIMARY], FG_1);
GO


DROP TABLE [dbo].[CustomerType]
GO


CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] INT NOT NULL, 
    [CustomerTypeDescription] NVARCHAR(50) NULL
CONSTRAINT PK_CustomerTypeID PRIMARY KEY([CustomerTypeID]) 
) ON  CREATETable_PartitionSchema ([CustomerTypeID]);

15) Create a temporary table
Creating a temporary table can become very handy in case you want to store temporary results. The table is created in the Tempfb database and will be cleaned when the session is closed.

CREATE TABLE #tmp
(
[tmpID] INT NOT NULL, 
[tmpDescription] NVARCHAR(50) NULL


16) SELECT INTO 
With SELECT INTO a copy of a table is created.

SELECT * 
INTO CustomerTypeCOPY 
FROM [CustomerType]

17)  Create a computed column based table
A computed column is computed from an expression that can use other columns in the same table. This is introduced in SQL Server 2005. If it's not defined as persisted then it's a virtual column. The data is not stored on disk.

CREATE TABLE CustomerType2
(TypeID INT,
TypeDescription VARCHAR(50),
TypeDescription2 VARCHAR(50),
Concatenated as (TypeDescription  + TypeDescription2) 
)

18) Create a persisted computed column based table
This property for computed columns has been introduced in SQL Server 2005 and onwards. If  persisted property is set active then the data of the computed column will be stored on disk.

CREATE TABLE CustomerType2
(TypeID INT,
TypeDescription VARCHAR(50),
TypeDescription2 VARCHAR(50),
Concatenated as (TypeDescription  + TypeDescription2) PERSISTED
)

19) Create a table with a GUID.
Normally, we use UNIQUEIDENTIFIER column for PRIMARY KEY and we assign newid() as the DEFAULT value for the column. The ROWGUIDCOL is more efficient. It's used for FILESTREAM.


CREATE TABLE dbo.GuidCustomer
(Customerguid uniqueidentifier DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
 Customer_Name varchar(60)
 CONSTRAINT pkGuid PRIMARY KEY (Customerguid) );

20) Declare a TABLE variable
A table variable is much like a temporary table but there are differences.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL PRIMARY KEY, 
[CustomerTypeDescription] NVARCHAR(50) NULL
)

21) Creating a Table in a specific Filegroup 
In case you want to partition tables in different filegroups you can choose to use multiple filegroups.

CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL PRIMARY KEY, 
[CustomerTypeDescription] NVARCHAR(50) NULL
) ON FG2


22) Creating a table with an XML column typed to an XML schema collection
Below an table example with typed XML column.

CREATE TABLE HumanResources.EmployeeResumes 
   (LName nvarchar(25), FName nvarchar(25), 
    Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection) );


23) CREATE A Table with a Index with a couple of options
An example of some mixed  primary key options.


CREATE TABLE [dbo].[CustomerType]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) NULL,
CONSTRAINT [PK_CustomerTypex] PRIMARY KEY CLUSTERED
(
[CustomerTypeID] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
) ON FG2



24) CREATE TABLE with an attribute with different collation
Sometimes you want to collate a field in another collation than the database default or server default.


CREATE TABLE [dbo].[CustomerTypeCollate]
(
[CustomerTypeID] SMALLINT NOT NULL, 
[CustomerTypeDescription] NVARCHAR(50) COLLATE Latin1_General_CS_AS_KS_WS NULL 
)

25) Create a Derived Table
The last example is a derived table example.

SELECT 
Name
FROM (SELECT * 
FROM Customer  
WHERE Name LIKE 'Hen%') AS CustomerDerivedTable 
WHERE Gender = 'M'
ORDER BY Name

Conclusion
Pfew...That are a lot of ways to create a table. I've learned a couple of new things and one thing that i didn't expected was that the usage of persisted column can improve sql queries like a JOIN on a function isnull(field).

Greetz,

Hennie

vrijdag 23 maart 2012

Multiple Hash generator for SQL Server 2012

Introduction
In this blogpost I'll test the Multiple Hash generator Component for SQL Server 2012. This component calculates a hash value for one or more fields. With the Multiple Hash SSIS component you can generate all kind of different hashes: MD5, RipeMD160, SHA1, SHA256, SHA384 and SHA512. It's also possible to create multiple threading in case you have multiple cores on your system available.

Installation
The installation process starts with downloading the tool from Codeplex. An installer will install the component in the SSIS toolbox.


Next...


I Agree!


Close and after a refresh of the toolbox the component is finally there.



Working with Multiple Hash in SSIS
In this section I'll describe the testruns i've done with the Multiple Hash function. First i've created an testscript, i've tested an initial run and a second run. In the second run I've changed the order of the fields used for the hash calculation.

Testrun 1


USE [SSISMultipleHash]
GO


DROP TABLE [dbo].[IN]
DROP TABLE [dbo].[OUT]


CREATE TABLE [dbo].[IN](
[Test1] [nchar](10) NULL,
[Test2] [nchar](10) NULL,
[Test3] [tinyint] NULL
) ON [PRIMARY]
GO


CREATE TABLE [dbo].[OUT](
[Test1] [nchar](10) NULL,
[Test2] [nchar](10) NULL,
[Test3] [tinyint] NULL,
[Hash] [varchar] (50) NULL
) ON [PRIMARY]
GO


INSERT INTO [IN] VALUES (NULL, NULL, NULL)
INSERT INTO [IN] VALUES ('test', NULL, NULL)
INSERT INTO [IN] VALUES ('Test', NULL, NULL)
INSERT INTO [IN] VALUES ('test', 'test', NULL)
INSERT INTO [IN] VALUES ('Test', 'Test', NULL)
INSERT INTO [IN] VALUES ('test', 'tEst', NULL)
INSERT INTO [IN] VALUES ('test', 'test', 1)
INSERT INTO [IN] VALUES ('test', 'test', 11)

I've created the following SSIS package Data Flow Task:




These are the input fields of the Multiple Hash function:


The output fields are shown below. In this case there is only one output field "hash" and it uses all three of the input fields to calculate the hash. The sorting is Test1, Test2 and Test3




Resulting in




Testrun 2
In the next testrun i've changed the sequence of the fields. The Multiple hash function calculates now the fields in a different ordering. The sorting is now Test3, Test2 and Test1.




Resulting in:



The result of Multiple Hash generator component are different. The hash values were calculated because of change of order in the fields. In case of the three NULLs for all of the fields there isn't a difference between the testrun 1 and tesrun 2 (offcourse).


Conclusion
This seems a powerful third party component of SSIS for calculating hashvalues. The only concern I can imagine is the future support in SQL Server 2015 (or 2014?). But the code seems available on codeplex. But, knowledge on how to change the component is inevitable.

Greetz,

Hennie

donderdag 15 maart 2012

SQL Server Data Tools (SSDT)

Introduction
This week I've installed the new version of SQL Server 2012 RTM. I'm not going to show you the windows of the installation process of SQL Server 2012. You can find more info about the installationprocess in one of my former posts describing the installation of  CTP3 and RC0. The installation of RTM is mostly the same as RC0 and I haven't found  significant differences. The errors have disappeared and all works smoothly.

In this blogpost I'll describe the installation of SQL Server Data Tools and the creation a simple SSDT project with a table.

You can download the webinstallar at Microsoft.com. Below I'll show the installation of SSDT and the creation of a simple table with SSDT.

Installing SQL Server Data Tools
Double click on the ssdt.exe webinstaller.



Press install.


Pompidom...Accept





And  it's finished.



Press Finish.

Creating my first table with SSDT
In this paragraph I'll show you how to create a table with SSDT. Double click on the SQL Server Data Tools in the start menu and choose a SQL Server Database project.


In the solution explorer choose 'Add' and then 'Table':


Choose table (again?).


Design the table.


And then choose the option in the contextmenu in the solution explorer to publish.


Press Edit and enter a . for the servername and then press generate script (or you can choose to publish the code right away but don't forget to set to re-create the database)


Press Advanced.


And the script is generated:


Press now on the 'play button' and the script is executed.



And here is the result  in SQL Serve Management Studio. The table is created.

 

If you try to execute the SSDT code in SSMS errors appears. The code is not interchangable between  SSDT and SSMS.


The project folder contains the following files. I tried to open the DBMDL file but that is not  human readable format.


Conclusion
Wow! This tools looks promising for (BI, DB and Azure) developers. When you look at the videos on MSDN there seems a lot of things possible with this tool and it's more suitable for developers. The SSMS is more a management tool than a developer tool. The good thing is you can generate code for 2005, 2008, 2008 R2, 2012 and Azure.


I think i'm going to use this tool in all of my next projects.

Greetz,
Hennie

woensdag 14 maart 2012

Creating a linked server to a MS Access database with a Workgroup file (.MDW)

Introduction
In a former post i've shown how to open a MS Access database with the OPENDATASOURCE command but that is not always possible when the installation of SQL Server has a tight policy. SQL Server recommends using a linked server. In this post i'll explain the usage of a Linked Server in SQL Server for opening a MS Access file secured with a workgroup file (.mdw).

The problem
If the installation of SQL Server has tight security policy you'll recieve an error when you run the following OPENDATASOURCE code:



SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\Beveiliging.mdw";User ID=testuser;Password=test;')...test;
GO


------

Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

The solution
I've found some information about accessing a MS Access database with a workgroupfile (.mdw) on MSDN. It states the following : "To access a secured Access database, configure the registry (using the Registry Editor) to use the correct Workgroup Information file used by Access. Use the Registry Editor to add the full path name of the Workgroup Information file used by Access to this registry entry: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\SystemDB"

Hmmm. I think that administrators are not happy with this kind of solutions and what to do in case you have two MS Access databases with a workgroup file?



Change that into



Execute the following code

USE [master]
GO


EXEC master.dbo.sp_dropserver @server=N'LSTest', @droplogins='droplogins'
GO


EXEC sp_addlinkedserver
@server ='LSTest',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'E:\tmp\test.mdb'
go


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LSTest',@useself=N'FALSE',@locallogin=NULL,@rmtuser=Testuser,@rmtpassword=test


SELECT * FROM LSTEST...test
GO

Resulting in:


Conclusion
Well, I'm not really a fan of this solution. An administrator won't be happy when I suggest this solution. It seems to me that this works only for one MS Access database with a mdw file on a server. Not a practical solution when you have multiple MS Access databases with a workgroup file.


Greetz,
Hennie 

zondag 11 maart 2012

Download links of SQL Server 2012

A small post today.

Currently downloading SQL Server 2012 RTM Evaluation edition and i was just looking for downloads that comes together with SQL Server 2012 and Pinal just published a page with all kind of download links. Pinal keeps the page updated with new downloads when available. Very useful.

You can find this page here

Greetz
Hennie

donderdag 8 maart 2012

Importing data from MS Access into SQL Server with TSQL

Introduction
In my current client project i have to create an import procedure for data from a MS Access database. This MS Access database is protected with a Workgroup file (.mdw). The .mdw file stores usernames and passwords of users allowed to open a specific MS Access database and it must be linked to an Access database to be effective. In this blogpost i'll explain three methods of importing data from a MS Access database:
  1. Simple MS Access database import.
  2. MS Access database import with password.
  3. MS Access database import with an .mdw file.
For importing a MS Access file i'll be using the OPENDATASOURCE statement. This provides ad hoc connection information without using a linked server name. 

Simple MS Access database
I've created a small MS  Access database with two lines. See the screenshot below:




You can retrieve the data from the Access file with the following code:



sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb"')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go


Resulting in:






MS Access database import with password
The next step is to load data from the MS Access file that is protected with a password.  First open de MS Access database in an exclusive mode.


Below you can see how you set the password in MS Access database.



The next step is to execute the following code :


sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  


SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:Database Password=test;')...test;
GO


sp_configure 'show advanced options', 1 
reconfigure with override   
go


sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go

Resulting in 




MS Access database import with .mdw file
And now we come to the final case in which the MS Access database is protected with a workgroup file. This workgroup file enables securing the MS Access database with encrypted PID's, usernames and passwords. This generates a unique code that MS Access uses to determine the permissions

You can create a Workgroup file with the menuoption Tools in MS Access. It will show the following window: The text is in the dutch language..




Next.




Select the relevant database objects:


Next



Next



Next



Next


Next






The following files are created : Test.mdb, Test.snp, Test.ldb, beveiliging.mdw and test.bak.

If you use the wrong mdw file an error occurs.

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\wrong.mdw";User ID=testuser;Password=test;')...test;
GO



OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" returned message "Cannot start your application. The workgroup information file is missing or opened exclusively by another user.".
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".


If you use the right MDW file  the data is returned. In the below snippet you'll see a disadvantage of using the OPENDATASOURCE statement: The name and password is visible.



sp_configure 'show advanced options',1   
reconfigure with override   
go   
sp_configure 'Ad Hoc Distributed Queries',1   
reconfigure with override   
go  

SELECT * FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0','Data Source="E:\tmp\test.mdb";Jet OLEDB:System Database="E:\tmp\Beveiliging.mdw";User ID=testuser;Password=test;')...test;
GO

sp_configure 'show advanced options', 1 
reconfigure with override   
go

sp_configure 'Ad Hoc Distributed Queries', 0
reconfigure with override   
go

The result :







Conclusion
This blogpost is about reading data from a table in MS Access database, a MS Access database protected with a password, and a MS Access database protected with a mdw file.

The OPENDATASOURCE statement is less secure because the name and password should be entered in the statement. In one of the next blogposts i'll show the Linked server option.

Greetz

Hennie