Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Creating code objects within a single script


Creating code objects within a single script

Author
Message
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
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
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36162 Visits: 18751
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
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
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36162 Visits: 18751
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14965 Visits: 38984
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
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
Thanks for replying...it gives me more options to consider.

~ Jeff
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
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
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36162 Visits: 18751
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.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
jhager
jhager
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 322
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search