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

I forgot to mention that I am using SQL server 2012

OK, so you have following command on two lines:

SELECT * FROM SomeTable
 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.

pcelba,

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

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.