Introducing support for MS SQL

Object oriented programming makes it all doable: Setting up a base class for a connection object, and deriving child classes for specific database vendors.

HeidiSQL now introduces experimental support for MS SQL in r3735. How that works? Seamless:
- In the session manager, chose the new network type "MSSQL" and specify the hostname, user and password. All other settings are unused for MS SQL.
- As HeidiSQL allows to have multiple connections open at once, you can of course connect to a MS SQL server and at the same time to some MySQL server.
- Table data can already be edited (INSERT, UPDATE, DELETE)
- Structure editors like the table editor are highly incomplete yet, just shows a part of the column definitions.
- Once all MySQL specific stuff is abstracted into the above mentioned objects, you will be able to do an SQL export from MS SQL to MySQL, and vice versa.

Getting curious? Feel free to update your build (Help > Check for updates).

I'm not an expert but SQL Server also uses a service or instance name (not sure about the exact name). We use it here at work since the same box hosts two versions of SQL Server (2000 and 2005). Actually, I cannot log into our server with HeidiSQL (SQL Error (18456): Error de inicio de sesión del usuario 'sa', which stands for "login error for user 'sa'"), maybe that's the reason.

I have successfully connected to a MS SQL 2008 with an instance name in the hostname: "COMPUTERNAME\ServerInstanceName", as well as one other 2008 server which does not make use of an instance name, connecting just to the computer name. Well, probably that new code is just 2008 compatible. We'll see.

That did the trick, thank you.

I admit I was sceptical about this feature but it's kind of hypnotic to browse the SQL Server tables using Heidi's lightning fast interface when you are used to sluggish SQL Server Management Studio Express.

This is awesome.

I love HeidiSQL but have to use SQL Server Management Studio at work. Until now! Can't wait until I can browse the MSSQL schemas in Heidi too.

Any chance that Oracle might be in the works too?

Schemas should be browsable already, or does it not work for you?

Oracle is just a small step away, driver and code from MS SQL can be reused so it's not a big deal. Just a bunch of SQL queries need adjustments.

Hello,
my goal was to have light-weighted client to connect to my MS SQL 2008 R2 server. I expected that heidisql could be a good choice.
I downloaded it , then overwrote existing heidisql.exe with heidisql.r3747.exe (r3747). First I didn't see as stated in instruction: "In the session manager, chose the new network type "MSSQL" " , so I just populate hostname, username and password but it failed to connect to my server. The error was:
'SQL Error (2013): Lost connection to MySQL server at 'reading initial communication packet'. system error:0
What I'm missing?

Thanks.

plz never mind my previous message. First I found type 'MSSQL', second it failed when I provided credentials like machine\administrator user. When I replaced the username for the user I created for MSSQL I was able to access my MS SQL. Great! Now I'll start to look what could I get there ..

Hey! That's really great!
It works like a charm.

many, many thanks!

anse: It doesn't work for me. When I connect to a MSSQL server, I get this error...

"SQL Error (208): Invalid object name 'sys.sysprocesses'"

But it doesn't seem to stop me from runing queries.

After connecting, when I look at the Host tab it shows the Database name but reports Size and everything else as 0. When I look at the Database tab, it shows nothing at all.

To fetch the objects shown in the database tab, HeidiSQL fires a quer like

SELECT * FROM "dbname"."sys"."objects" WHERE "type" IN ('P', 'U', 'V');

When the tab is empty this query did not result anything. Works on my own server, on another one in my company and on one users' server at least. Not sure why it does not return anything on your server - probably you can try to find out?

well it worked perfect for me on WinXP but it doesn't work on my RHEL6 64 bits Open Client system.
Here is what I did:
1. installed Wine
2. downloaded and unzipped HeidiSQL_6.0_Portable.zip
3. replaced heidisql.exe with heidisql.r3797.exe
4. started heidisql.exe
5. when I tried to establish connection to my MSSQL database I've got the error:

exception message : Error creating object. Please verify that the Microsoft Data Access Components 2.1 (or later) have been properly installed.

Could someone help me to explain if my steps were correct and also help to install MDAC? I'm not much familiar with linux install but need heidisql to run on my linux system.

Thanks in advance.

Try this winetricks script: http://forum.winehq.org/viewtopic.php?p=13959

hi anse, thanks for reply.
I tried your link but it looks that I can't start winetricks for some reason:
sh winetricks
------------------------------------------------------
wine cmd.exe /c echo '%ProgramFiles%' returned unexpanded string... do you own the parent of /root/.wine ?
------------------------------

anse - finally I fixed winetricks. Now I have to install MDAC using it but I can't find MDAC package. Could anyone point me where I could download it?
thanks

pls ignore my last request. I was able to locate, download and install MDAC. Now I can't run your application under RHEL6.
thanks a lot.

Hello!
I tried to connect MS SQL server with HeidiSQL, but got an "SQL Error (17)". I have 4034 release. I use Windows auth and tried different network types.
On other computer I can connect to this server with MS SQL Management Studio and same credentials.

Hi Anse,
Whats the status of the ms sql integration? It appears HeidiSQL will work with limited functionality on the same computer as MS SQL Server, however connecting over the network doesn't appear to work (Named/pipe and IP), reporting the same error as RomeroMsk.

MS SQL connecting should work out of the box. Are you able to check if the server is reachable somehow from your client computer? Could be it's behind a firewall or so.

I'm connecting to MS SQL sever using MS SQL Management Studio from that computer.

I can also agree, I am connecting with no problems using Named Pipes from MS SQL Management Studio. The Server is 2008 R2 64bit (running on MS Server 2008 64bit), and my client is Windows 7 Ultimate 64bit.

Hi, Been using Heidisql for a while now and finding the ability to use MSSQL was a bonus - been pleased.
I have 1 error I would like to have sorted please
When I export a MSSQL table and want to add data to the export I get an error and it does not export the table data
which is caused by this statement below

SELECT * FROM "WHM_SK"."ReportAddressHeader" LIMIT 0, 104857600;

obviously MSSQL does not understand "LIMIT 0, 104857600;"
is there any workaround or fixes coming soon?
Cheers
Paul

I guess it should be "SELECT TOP 104857600 * FROM ...", but ;S SQL does not support an offset as a second parameter here. This makes it impossible to fetch rows in steps. Probably you can help out with some SQL hints?

This is very true, however this is something in the code of the HeidiSQL program itself so I cannot do anything about it.
This is a useful feature and would like it to work if possible?

specify what sql to use; give an example. (SELECT with TOP and also an offset specification. for MSSQL)

Ah sorry here you go -

SELECT TOP 104857600 * FROM ReportAddressHeader;

or just

SELECT * FROM ReportAddressHeader;

google says: use
ORDER BY ... OFFSET n1 FETCH NEXT n2
here

@PCDEV: TOP is fine but AFAIK does not allow to specify an offset, which is necessary to produce "paged" output as used in heidisql. ORDER BY... should do it.

This works for me

SELECT *
FROM ReportAddressHeader
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

Very cool, at least for SQL Server 2012. Quite an old topic as you can see here. At least for MS SQL 2012 I'll give this OFFSET..FETCH syntax a try.

Indeed that also works well for all SQL versions

SELECT *
FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM ReportAddressHeader) t
WHERE RowNr BETWEEN 10 AND 20

I think offset..fetch method would be best if possible?
Thanks.

my sweet lord. MSSQL is really an amazing piece of, er, software .. NB the above syntax does of course NOT work on a MSSQL 2008 R2 server that I have access to.
the various contortions suggested in the stackoverflow thresd appear somewhat bizarre to me. to compare: mysql had the LIMIT clause since the middle ages (v3.23 = around 2001, AFAIR)...

Yes I totally agree with you the LIMIT has been there for what appears forever! 1999 I recall using it first time I am fairly sure.

Hello all,

I am having the same issue that PCDEV had, in that I am trying to export a table with data, but MSSQL doesn't recognize LIMIT. It appears we've identified the alternate code, however how can we utilize this within HeidiSQL for the purposes of exporting?

Thanks!

My apologies, I just realized that this was fixed in a later version of the product. I have since upgraded to the latest version and this is working. Brilliant! Thanks!

WOW!!! THANK YOU!

ya.. it was 3 years ago..... but I just realized..