distal-attribute
distal-attribute
distal-attribute
distal-attribute

Introducing support for MS SQL

User, date Message
Written by ansgar
3 years ago
Category: News
4801 posts since Fri, 07 Apr 06
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).
Written by kalvaro
3 years ago
564 posts since Thu, 29 Nov 07
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.
Written by ansgar
3 years ago
4801 posts since Fri, 07 Apr 06
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.
Written by kalvaro
3 years ago
564 posts since Thu, 29 Nov 07
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.
Written by tmod
3 years ago
2 posts since Sun, 03 Apr 11
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?
Written by ansgar
3 years ago
4801 posts since Fri, 07 Apr 06
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.
Written by susja
3 years ago
6 posts since Sun, 03 Apr 11
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.
Written by susja
3 years ago
6 posts since Sun, 03 Apr 11
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 ..
Written by uem1977
3 years ago
3 posts since Wed, 13 Apr 11
Hey! That's really great!
It works like a charm.

many, many thanks!
Written by tmod
3 years ago
2 posts since Sun, 03 Apr 11
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.
Written by ansgar
3 years ago
4801 posts since Fri, 07 Apr 06
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?
Written by susja
3 years ago
6 posts since Sun, 03 Apr 11
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.
Written by ansgar
3 years ago
4801 posts since Fri, 07 Apr 06
Try this winetricks script: http://forum.winehq.org/viewtopic.php?p=13959
Written by susja
3 years ago
6 posts since Sun, 03 Apr 11
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 ?
------------------------------
Written by susja
3 years ago
6 posts since Sun, 03 Apr 11
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
Written by susja
3 years ago
6 posts since Sun, 03 Apr 11
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.
Written by RomeroMsk
2 years ago
2 posts since Thu, 02 Feb 12
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.
Written by mrforsythexeter
2 years ago
26 posts since Wed, 24 Nov 10
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.
Written by ansgar
2 years ago
4801 posts since Fri, 07 Apr 06
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.
Written by RomeroMsk
2 years ago
2 posts since Thu, 02 Feb 12
I'm connecting to MS SQL sever using MS SQL Management Studio from that computer.
Written by mrforsythexeter
2 years ago
26 posts since Wed, 24 Nov 10
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.
Written by PCDEV
2 years ago
6 posts since Fri, 17 Aug 12
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
Written by ansgar
2 years ago
4801 posts since Fri, 07 Apr 06
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?
Written by PCDEV
2 years ago
6 posts since Fri, 17 Aug 12
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?
Written by jfalchMoney, Euro
2 years ago
354 posts since Sat, 17 Oct 09
specify what sql to use; give an example. (SELECT with TOP and also an offset specification. for MSSQL)
Written by PCDEV
2 years ago
6 posts since Fri, 17 Aug 12
Ah sorry here you go -

SELECT TOP 104857600 * FROM ReportAddressHeader;

or just

SELECT * FROM ReportAddressHeader;
Written by jfalchMoney, Euro
2 years ago
354 posts since Sat, 17 Oct 09
google says: use
ORDER BY ... OFFSET n1 FETCH NEXT n2
here
Written by jfalchMoney, Euro
2 years ago
354 posts since Sat, 17 Oct 09
@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.
Written by PCDEV
2 years ago
6 posts since Fri, 17 Aug 12
This works for me

SELECT *
FROM ReportAddressHeader
ORDER BY ID
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;
Written by ansgar
2 years ago
4801 posts since Fri, 07 Apr 06
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.
Written by PCDEV
2 years ago
6 posts since Fri, 17 Aug 12
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.
Written by jfalchMoney, Euro
2 years ago
354 posts since Sat, 17 Oct 09
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)...
Written by PCDEV
2 years ago
6 posts since Fri, 17 Aug 12
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.
Written by everydayfam
1 year ago
6 posts since Thu, 31 Jan 13
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!
Written by everydayfam
1 year ago
6 posts since Thu, 31 Jan 13
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!
Written by axar
6 months ago
2 posts since Fri, 18 Oct 13
WOW!!! THANK YOU!
Written by axar
6 months ago
2 posts since Fri, 18 Oct 13
ya.. it was 3 years ago..... but I just realized.. happyhappy
 

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