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
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
CERTIFIED EXPERT
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.
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
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
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
CERTIFIED EXPERT
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.
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.
Gain unlimited access to on-demand training courses with an Experts Exchange subscription.
Get AccessWhy Experts Exchange?
Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.
Jim Murphy
Programmer at Smart IT Solutions
When asked, what has been your best career decision?
Deciding to stick with EE.
Mohamed Asif
Technical Department Head
Being involved with EE helped me to grow personally and professionally.
Carl Webster
CTP, Sr Infrastructure Consultant
Did You Know?
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE
Ask ANY Question
Connect with Certified Experts to gain insight and support on specific technology challenges including:
- Troubleshooting
- Research
- Professional Opinions