how to preserve CR/LF when saving a script to the database

Hi SQL server experts,
I want to write SQL server scripts in the management studio, and after testing in in rollback mode, I want to save it in the database (as script) to execute it later programatically. The problem is that LF (CR/LF) is not saved, causing the script to fail later when I try to execute.... Is there a way to handle this issue. I am using SQL server 2012. Thank you experts
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fmichailAuthor Commented:
I forgot to mention that I am using SQL server 2012
Any Stored procedure or function is stored with CR/LF. How do you store your scripts? What is the column data type for your scripts storing?
Isn't the disk file better for your scripts?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OK, so you have following command on two lines:

 WHERE SomeCol = 'XYZ'

To store above text into the data table named YourScripts you may use following command:

INSERT INTO YourScripts (id, script)
  VALUES (1, 'SELECT * FROM SomeTable' + CHAR(13)+CHAR(10) + ' WHERE SomeCol = ''XYZ'' ')

Note also the apostrophes duplication.
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

fmichailAuthor Commented:

I have an application using one database, and I have multiple clients buying the application, and each has his own copy of the database. If I have new release which needs updated database (added stored procedures, and altered functions, and altered tables, and new views, and changed master data...etc). I do my updates using a script that I create and I want to save it in a database (for the specific version) so that when one of my clients wants the upgrade I run an application which picks the required scripts one after one (starting from the client current version until the application latest version) and run them one after the other to refresh the database to be ready for the current latest version. It is like saving them in individual files one for each version, but instead of reading each script from a file, I try to read it from a table record.

Given this, when I write the script in the editor panel, it runs fine, but if I save it in a database for further use and try to read it from database it looses the CR/LF and accordingly its execution fails.

Thanks pcelba
fmichailAuthor Commented:
Thanks pcelba,

I know what you mean, I agree with you that using files will be better, and I started to feel that is a better option.... I believe that the automation of this process could even be dangerous, if the script is partially executed (the script will have many [GO] statements, so I believe the TRANSACTION cannot span GO and accordingly the script can be partially executed and that is really dangerous... Is that correct pcelba?.

I will go with your file option, and the process will be attended process to do it step at a time, where I can take precautions to avoid problems... Thanks pcelba
You are welcome!

We are using a small C# application which looks into the predefined folder and checks all the scripts inside. Then it compares whether the disk file is changed comparing its check sum stored in the database. All new and updated script files are read, split to batches derived by GO commands and executed in the current database. Script files do have predefined prefix, e.g. TAB_ for table definitions, COL_, SP_, FN_, FILL_, etc. This allows to execute table creations first, followed by columns updates with SP definitions at the end.

Particular script may fail but the DB update continues. This may bring some successive errors but after the first failed script fix the process can run again and again without problems. Successful scripts are not repeated. Of course, each script contains a part which checks whether the created/updated object exists or not just to be sure we don't do anything unwanted...

We have just one script for each SP and other db objects but all script versions are also stored in the versioning system like SVN or Git. This allows to return to the previous version (after old DB restore) and/or check for script changes between versions etc.

To use C# or any other language application for scripts storing to the database is also an option. In such case C# can handle CR-LF w/o problems. Versioning depends on your decision.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.