Creating code objects within a single script

Question

Creating code objects within a single script

jhager

SSCrazy

Points: 2310
More actions
April 1, 2013 at 8:11 am

#394500

I need to deliver a large number of code objects (views and functions, not stored procedures) to our customers via a website. Due to dependencies of these objects, we want to have the script create these objects in a specific order. We also want to include error handling and success/failure reporting (as well as possible). Many of these code objects create statements are lengthy and contain many literals and concatenations.
Given these "specifications", as well as the limitations of creating code objects and error handling within T-SQL, I believe my choices are limited to 1) using dynamic SQL to create the objects, thereby allowing better error handling and "reporting" , or 2) plain coding, thereby allowing much easier coding, readability, and maintenance.
If anyone has another method that might work given the "specs", or a clever work-around for this, please let me know.
Thanks,
Jeff

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 1

Powershell will do this, and give you better error handling. However more development work.

The other thing to think about is using a product like SQLCompare to build your scripts. I know you might not have control of all versions that customers have, but you could license and programmatically run this as well.

http://www.red-gate.com/products/sql-development/sql-compare/

Disclosure: I work for Red Gate.

jhager SSCrazy Points: 2310 More actions · Answer 2

Thanks for the reply and the ideas. Unfortunately, we have limitations in many areas that I didn't include, but that your response has revealed.

-- We support SQL Server versions from 2005 on.

-- We do not specify what other software or options our customers must have, or have installed.

-- Most importantly: many of our customers have little or no skilled staff to even run these object creation scripts; in fact, in our original release a while ago, we only had 4 code objects, and we got a large number of calls with questions like:

?? What does the N mean before the database name, and do we need to keep it?

?? I keep getting a message that says "Don't create these objects in the master database"..what is that?

?? What's a view?

?? I get a message that says I don't have permissions to create the objects... can you give me permission?

etc.

Now you see why I need to keep this as simple as reasonably possible.

Another thought I had after reading your reply...is Export / Import a reasonable possiblity here? I've not got lots of experience with it in a SQL Server environment (used it as an Oracle DBA many years ago), so don't know the practical use of it.

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 3

Export/import, not sure what you mean?

The only way you do this in SQL is with data, not objects. If you "export" an object, you script it.

What I'd look for is automation, so clients don't need to know things. Give them a script they can run (or executable) that searches for objects, creating them if they're needed.

Lowell SSC Guru Points: 323518 More actions · Answer 4

it kind of sounds like the client has your application already in place, and when they upgrade, they need to run scripts too?

I know in our case, we've done it both ways: we made one application have the ability, besides the usual presentation app, to also have the ability to run scripts in a massive transaction, and then auto-upgrade itself (exe, dlls files).

for the clients that are more technically savvy, I created a Schemachecker app that lets them see if they are on the "right" version for the script they plan on running, and then we provide the scripts and .msi for an install/upgrade process as well.

probably the most frustrating thing for me as far as scripting DDL is circular references, where TableA references TableB, which eventually, indirectly or directly, references back to TableA again.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jhager SSCrazy Points: 2310 More actions · Answer 5

Thanks for replying...it gives me more options to consider.

~ Jeff

jhager SSCrazy Points: 2310 More actions · Answer 6

Thanks for replying...in actuality, we don't really have an "application"...our commercial (proprietary) software stores data within hierarchical files, which our customers cannot easily report from; a while back, a process was devised to selectively load some of that data into a SQL Server database (which is not completely relational; somewhat mimics the source).

Flash forward to today: the gov't has instituted a project that enables our customers to get $$$ if they can prove they're using software instead of manual or paper-based methods, so our customers want better, faster reporting capability. My company recently hired a few people (myself included) to write stored procedures to accomplish this.

Flash forward to the near future: the gov't has significantly changed specifications, such that we have to recode nearly everything; we've also gone to a more modular design, such that we have ~ 75 views and functions.

So, with recoding fast, lots more objects, some primitive customer sites / software, not a lot of varied experience in-house going "outside" SQL/T-SQL, we're very restricted as to what and how we can deploy our SQL Server code objects.

~ Jeff

Steve Jones - SSC Editor SSC Guru Points: 736210 More actions · Answer 7

I'd think about just using simple scripts. Write yourself, or use something like SQL Compare to generate the scripts.

You can then either build a system that looks for existing objects and only creates new ones if they don't exist (or alters), or give a series of scripts in some order, asking users to download them and run them in this order.

jhager SSCrazy Points: 2310 More actions · Answer 8

Sounds good...the "original" script was easy...the objects were all simple, so easy to make dynamic (just enclose in single quotes and EXECUTE...many more now, and with hard-coded XML elements, one is 600 lines long and must have 100 quoted "pieces" in it...take me days to convert to dynamic, probably mess it up anyway. Really leaning toward creating "plain", not so much error handling.

Thanks again for your assistance and wisdom,

~ Jeff