Author |
Message |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
SA 7.1 - SQL Editor file format? |
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
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
![](http://s9.postimg.org/e2umtwmx7/Test_Saving_Proc_SQLEditor.jpg)
|
|
Thu Jan 08, 2015 6:14 pm |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
Thank you for a great support.
I will aks for the fix to test It.
|
|
Thu Jan 22, 2015 5:46 pm |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
SysOp
Site Admin
Joined: 26 Nov 2006 Posts: 7849
|
|
|
|
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 |
|
![](templates/GreenTech/images/spacer.gif) |
sqltogo
Joined: 02 Jul 2014 Posts: 38 Country: Netherlands |
|
|
|
Downloaded the update, I want to let you know it works fine, thank you.
|
|
Wed May 06, 2015 3:36 pm |
|
![](templates/GreenTech/images/spacer.gif) |
|