Introducing support for MS SQL

ansgar posted 7 years ago in News
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).
kalvaro posted 7 years ago
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.
ansgar posted 7 years ago
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.
kalvaro posted 7 years ago
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.
tmod posted 7 years ago
This is awesome.happy

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?
ansgar posted 7 years ago
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.
susja posted 7 years ago
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?

susja posted 7 years ago
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 ..
uem1977 posted 7 years ago
Hey! That's really great!
It works like a charm.

many, many thanks!
tmod posted 7 years ago
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.
ansgar posted 7 years ago
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?
susja posted 7 years ago
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
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.
ansgar posted 7 years ago
Try this winetricks script:
susja posted 7 years ago
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 ?
susja posted 7 years ago
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?
susja posted 7 years ago
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.
RomeroMsk posted 6 years ago
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.
mrforsythexeter posted 6 years ago
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.
ansgar posted 6 years ago
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.
RomeroMsk posted 6 years ago
I'm connecting to MS SQL sever using MS SQL Management Studio from that computer.
mrforsythexeter posted 6 years ago
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.
PCDEV posted 5 years ago
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?
ansgar posted 5 years ago
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?
PCDEV posted 5 years ago
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?
jfalch posted 5 years ago
specify what sql to use; give an example. (SELECT with TOP and also an offset specification. for MSSQL)
PCDEV posted 5 years ago
Ah sorry here you go -

SELECT TOP 104857600 * FROM ReportAddressHeader;

or just

SELECT * FROM ReportAddressHeader;
jfalch posted 5 years ago
google says: use
jfalch posted 5 years ago
@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.
PCDEV posted 5 years ago
This works for me

FROM ReportAddressHeader
ansgar posted 5 years ago
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.
PCDEV posted 5 years ago
Indeed that also works well for all SQL versions

FROM (SELECT ROW_NUMBER() OVER(ORDER BY id) RowNr, id FROM ReportAddressHeader) t

I think offset..fetch method would be best if possible?
jfalch posted 5 years ago
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)...
PCDEV posted 5 years ago
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.
everydayfam posted 5 years ago
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?

everydayfam posted 5 years ago
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!
axar posted 4 years ago
axar posted 4 years ago
ya.. it was 3 years ago..... but I just realized.. happyhappy

Please login to leave a reply, or register at first.