Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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?
Isn't the disk file better for your scripts?
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial