SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
SA 7.1 - SQL Editor file format?
Goto page 1, 2  Next
 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
SA 7.1 - SQL Editor file format?
Author Message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post SA 7.1 - SQL Editor file format? Reply with quote
It seems that the editor saves procedure text with different new line settings/coding.
After saving a procedure, when (re)opening in Management Studio I get the warning line endings are not consistent.

When I look at the options in the File menu, I can change many settings, but they don't seem to work or to be saved.
How can I configure SQL Editor so it will always use Windows new line settings (CR/LF)?
Sat Jan 03, 2015 12:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Do you get this warning if you save a new file in Management Studio then open in SA SQL Editor?
Mon Jan 05, 2015 11:34 am View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
I did different tests.
When working within Management Studio everything I tried worked fine.
The saved SQL script within the SQL server was the correct Windows end of line format.
When working in the standalone SQL Editor during the edits the end of line format was fine.
When saving the procedure the saved SQL It was using Unix End of line format.
So when opening this saved procedure within Management Studio , using the standaard GUI I got the warning the end of file format was NOT consistentent wit the Windows end of line.
When just working with the SQL Editor this is NOT much of a problem, but wen saving scripts to files or copying text to another text Editor It gets the wrong layout.

I should expect the default setting within Windows will comply with Windows standards.
But when I want the correct end of line in a saved text file I must change the setting within the file menu.

If you need script examples please let me know.
Mon Jan 05, 2015 5:52 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I'm unable to reproduce this issue.

Here are my current settings in SQL Editor

File encoding -> ANSI
File format -> Default
Character set -> Default
Code page -> Default


The obscure part here is that defaults can be different on different systems, On my I believe the above is the same as

File encoding -> ANSI
File format -> Windows (CR\NL)
Character set -> Western
Code page -> Western European


Now, if you open your file in Noted after saving it in SQL Editor, how does it look in Notepad?
Tue Jan 06, 2015 2:29 am View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
I agree with your comments on the working of the (default) settings.
Saving to a file with the default settings in SQL Editor (mine are the same as yours) remains the correct end of line format.
On my system it is only when creating or modifying the stored procedure on the SQL server that the end of line is changed.
In SQL Editor I changed view settings to show control characters.
After saving a stored procedure and reopening it within SQL Editor I see the change in end of line from Windows to Unix.
And here it gets a little stranger, to me it shows that the default settings of the Editor are mostly Windows.
When I open the code by using "Show DDL" the line endings are again Windows and when using "Edit procdure" the line endings are Unix.
From within MS Mangement Studio I get the same behavior.
I know for sure that line endings of the saved SQL text in SQL server are Unix.
Getting the procedure text with this SQL
SELECT definition
FROM sys.all_sql_modules AS asm
WHERE asm.[object_id] = OBJECT_ID ('Myproc')
Gives an output when displayed with showing control characters that line endings are Unix format instead of Windows.
Tue Jan 06, 2015 6:46 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
That's weird. How do you connect to the database in SQL Editor? Do you use ADO.NET or ODBC connection? If ODBC, which driver and version do you use?
Wed Jan 07, 2015 10:34 am View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
I used both drivers, for ODBC I use the SQL native client 11 (MS SQL 2012). They behave identical. I use a Dutch version of Windows 8.1with standard Dutch setting. I have two different Systems, also no difference in behavior. At this moment I am working with locally installed SQL servers. Later this week I could test with network SQL servers, than I van test what gets send over the Network. Wen you need test sequences for analyzing this behavior, let me know.
Wed Jan 07, 2015 4:58 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
I think the issue there might be with different encoding and code pages, not different line breaks. SQL Editor uses ANSI Western European encoding - codepage 1252, while SSMS uses Unicode - codepage 1200. I tried experimenting with different settings and encoding and yet, still unable to reproduce it.
Thu Jan 08, 2015 2:07 am View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
This is probably difficult to simulate, because it may be some settings within Windows I am not fully aware off.
When there is no soultion I can build an command script to reset EOL to Windows.
Even then SQL Editor is of so much value to me the extra command script is not really an issue.

Tested the difference in saving a stored procedure in SSMS or SQL Editor

SQL in SSMS:
USE [TestSnippets]
GO
/****** Object: StoredProcedure [dbo].[PX_give_returcode] Script Date: 8-1-2015 21:33:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PX_give_returcode](@Option INT = 0)
AS
BEGIN
IF @Option = 9
BEGIN
RETURN N'KLM'
END
ELSE
BEGIN
RETURN @Option
END
END

SQL in SQL Editor
USE [TestSnippets]
GO
/****** Object: StoredProcedure [dbo].[PX_give_returcode] Script Date: 8-1-2015 21:33:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[PX_give_returcode](@Option INT = 0)
AS
BEGIN
IF @Option = 9
BEGIN
RETURN N'QRS'
END
ELSE
BEGIN
RETURN @Option
END
END


Using Wireshark to trace network packets.
Both tools send UNICODE data from the editor to the database server
Some hex explanation
Windows CR/LF = 00 0d 00 0a
Unix LF = 00 0a
<TAB> END = 00 09 00 45 00 4e 00 44
Packet 1 – SSMS – Alter procedure

Packet 2 – SQL Editor





Thu Jan 08, 2015 6:14 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Thank you. Your screenshots are very helpful. Also very helpful to see that the changes are actually visible on the wire before they reach the database. I'm not sure if that's actually caused by the SQL Editor or some middleware interface. Let me run it by our development team. I hope they can clarify the issue.
Fri Jan 09, 2015 1:16 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
We have developed a fix for this issue, which we will have available in the next maintenance version, sorry I don't have a specific release date for the that version, likely sometime in February. If you are interested in testing it earlier, please email to support and ask for the latest private build. Please reference issue #SA0025619 and your current license key number.
Mon Jan 19, 2015 10:53 am View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
Thank you for a great support.
I will aks for the fix to test It.
Thu Jan 22, 2015 5:46 pm View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
I still have version 7.1 246, the update check says that there is no newer version available
Do you have any idea when a update is available that fixes this issue?
Mon Apr 27, 2015 8:46 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7833

Post Reply with quote
Hello. Version 7.2 has been released. There is a fix for the issue discussed in this topic.
Wed May 06, 2015 12:36 am View user's profile Send private message
sqltogo



Joined: 02 Jul 2014
Posts: 38
Country: Netherlands

Post Reply with quote
Downloaded the update, I want to let you know it works fine, thank you.
Wed May 06, 2015 3:36 pm View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.