Categorie: Tooltip

Linked server setup – Back to basics (error 7399)

I recently received this question:

Why am I getting an error ‘Invalid authorization specification’. I used my SA account, but i keep getting the error that the credentials specified are incorrect.

There is a very simple answer to this question, which I will share with you.

The error you get when the credentials are invalid:LSSLinked server is a connection between two servers in your network. If you want to connect the servers, the specified credentials need to be known on both servers, So, unless you use the same password for sa acounts on all your network server, which is never recommended in the first place! I advise you to create a specific Linked server sql account, for example I created a LS_2014 account on both servers and specified on which databases the Linked server is allowed to read and select data, as you can see in the example below.



This keeps your databases secure and easier to manage.

You could also set it to use your current logged in account, but you need enough permissions on all servers and depending on who is using the SQL server, the linked server might not be available.

If you use the account specifically created for the Linked server and test the linked server account, the connection will be successful and you will see the table you granted read permissions on is available in the linked server dropdown.

Creating a linked server ´MySQL to MSSQL´(query the MySQL database without openquery function)

In addition to my previous linked server tutorials, I decided it is time to add MySQL to the linked server series.
In order to have the bug tracking application, Mantis migrated from linux and have it run on a windows environment, I wanted to create a replication between SQL2008 and MySQL, but then I thought, why not try out a virtual linked server again first, to test Mantis isntallation on a Windows based installation, since the online promise of Mantis on a MSSQl environment is not very promising. So today we will create a linked server from MySQl to MSSQL on a windows 2008R 64 bit environment.

Create DNS for MySQL

In order to do so, We first need to install the correct drivers in order to create a ODBC DSN, Just download the drivers from Mysql developers site and install them to your database server. DriversIf we see the listed drivers, it means we can create a new DSN, so open up the System DSN tab and ADD a new DSN, you must fill in the correct credentials, for example:ODBC_Connector

Data Source Name: Enter a describing name, so you can see what it does, you might have more linked servers or other connectors running on the same server.
Description: this isnt maditory, but if you want to be more specific, be my guest.
Server: in my case,it’s localhost, as this is a test server and MSSQL and MySQl are on the same server.
Insert username and password, when this is done. The database will display the possible databases you can connect to, in the dropdown.

Click OK and as you can see the System DSN has been added to ODBC.

Create new Linked Server

When this is done, it’s time to open up the MSSQl server and add a new linked server to the Server Objects.4_create_LInkedsname your linked server, I give it the same name as the SystemDSN. And choose the correct provider: Microsoft OLE DB Provider for ODBC Drivers. and datasource equels DSN name.
You need to fill in all the credentials for the provider string, for example:

DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=mantisbt; USER=user;PASSWORD=password;OPTION=3;

Note: meaning of OPTION=3 in the MySQL connection string:
Option=1 FLAG_FIELD_LENGHT: Do not Optimize Column Width
Option=2 FLAG_FOUND_ROWS: Return matching rows
Option=3 option 1 and 2 together

Now click OK, this is always the most fun part to me! when it says connection tot the linked server succeeded!

In addition to this, you can enable provider options on the SQLOLEDB, In my case I select the Dynamic Parameter and Allow inprocess.

Now, lets run the test and see if it connects with the databases, as you can see, it connects all the databases available on the MySQL server.

Connection test

But, most important, we can query it directly. Wheeee!

Linked server without OpenQuery function (Tip!)

Maybe you have read other MySQL linked server tutorials before this one and found out that you could only query the mysql database using the openquery() function or maybe that IS  the reason it brought you to this site. Extra, as in extra work, is never fun! With the correct ODBC driver and the right provider options, you can query the MySQl database, just like any other MSSQL database on your MSSQL server. Just follow the tutorial above and don’t forget to  enable the correct provider options. Cheers!

Tooltip: Adding a package to your Visual Studio project with PMC

Package Management Console is an easy way to insert a package to your project, in my case I wanted to convert my simple console app to an entity framework app with a database connection.

Here’s a simple walk through:

Go into Tools -> Library package manager -> Package manager console. The PMC opens up in the lower section of VS.

The following options are availablePMC1

Now type “Install-Package EntityFramework” and The EF package will be installed within your project.

Entity Framework has the following Cmdlets available.7

Now we just need to implement EF in our project

Using System.Data.Entity;

and add the references System.Data.Entity and Microsoft.Data.Entity.

Solution Explorer

Use of MAX data types when over >8000 characters. (error exceeds the maximum allowed for any data type (8000).

changes/upgrades in your favorite software, become real gems at the moment you run into a problem. Like, finding out a nvarchar doesn’t go over >8000.

Since SQL2005 the keyword ‘max’ was added for the datatypes nvar, varchar and varbinary. Before the introduction of  the (MAX) data types, 8000 was the absolute ‘max’, and your next best option was to use a text field, but a textfield exceeding 8000 will be handled as an out of row page and could not be used as a variable. Various of workarounds where available  online, but it was never pretty.

In SQL2005 the (n)var/char data types allows variables to be as big as the (n)text datatype can be. You can do anything to them that you could with regular varchar types, but behind the scenes they function like the text data type with values less than 8000 characters in-row and values greater than 8000 characters stored in out-of-row pages. These types can be declared as local variable.

Remember, if over 8000 characters needed,Us the (MAX) data type, SQL does not allow you to use f.e. VARCHAR(10000).



What if I still want to limit the entry ?
MAX only defines more then 8000 of the character input. If you want to set a maximum of characters, like 10.000, use a CHECK CONSTRAINT To create a column of nvarchar data type with a maximum of 10.000 characters, declare the column as nvarchar (MAX) and add a check constraint to the column by checking the length of the column and making sure it is less than or equal to 10.000 characters.

CREATE TABLE [dbo].[Testdb] ( [VarChar10000] VARCHAR(MAX) )
ALTER TABLE [dbo].[Testdb]     ADD CONSTRAINT [MaxLength10000]
CHECK (DATALENGTH([VarChar10000]) <= 10000)

What happpened to TEXT?
VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) are the replacements of TEXT, NTEXT and IMAGE data types. These datatypes are deprecated after, so avoid using these data types when using < SQL Server 2005.

Wanna see some numbers?

(I borrowed the calculations below from the Microsoft Knowledgebase)

The calculation for the maximum storage size for VARCHAR(MAX), NVARCHAR and VARBINARY(MAX) is 2^31-1 bytes (2,147,483,647 bytes or 2GB – 1 bytes).  The storage size is the actual length of data entered + 2 bytes.  The data entered can be 0 characters in length. Note: characters in an NVARCHAR data type uses two bytes, the actual maximum length for an NVARCHAR(MAX) data type is 1,073,741,822.

Total cloud control together with Oracle and Microsoft


Total cloud control – “Reduce downtime” and “pluggable database”


This is what the new oracle rdbms 12c is all about; It’s a business driven enterprise cloud management solution.

Two features highlighted:

Pdb pluggable database features: one instance for multiple databases saves lots of memory and you can upgrade multiple database instances at once.

Multitenancy* (the key ingredient for the cloud)  the pro’s of the  shared server technology is it’s efficiency with the guarantee of data isolation and discrete tenant performance management. db admins can discretely manage service levels and define resource  allocation and priority.

Oracle also promises 12c’s database disk I/O is reduced and uses fair memory usage which was not really the case with the absorbing powers of current popular oracle 11g, which only works well on a powerful x64.

The multitenancy* refers to the already growing SAAS community (software as a service) where many customers share the same application instance, but with separated data. Oracle pushes this approach from the application to the database!

So whats the fuzz about?

Oracle and Microsoft announced their partnerships along with some other (saas) companies today with software vendors that will, I quote “Reshape the cloud and reshape the perception of oracle technology in the cloud”.

Partnerships are no big news in the ever changing IT world, but Microsoft and Oracle being database competitors for a long time, this is big news. This could not only bring together the best of both worlds. But will this change the way we think an work with databases? Will oracle’s 12c become the big force behind the SQl Azure cloud services? I can’t wait to hear more about this!

I’m curious to hear your thoughts on this partnership.