Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Creating code objects within a single script Expand / Collapse
Author
Message
Posted Monday, April 1, 2013 8:11 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:12 AM
Points: 58, Visits: 243
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
Post #1437448
Posted Monday, April 1, 2013 8:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
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
Post #1437463
Posted Monday, April 1, 2013 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:12 AM
Points: 58, Visits: 243
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.
Post #1437476
Posted Monday, April 1, 2013 9:14 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
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
Post #1437485
Posted Monday, April 1, 2013 9:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:39 PM
Points: 12,923, Visits: 32,313
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1437491
Posted Monday, April 1, 2013 9:56 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:12 AM
Points: 58, Visits: 243
Thanks for replying...it gives me more options to consider.

~ Jeff
Post #1437508
Posted Monday, April 1, 2013 10:07 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:12 AM
Points: 58, Visits: 243
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
Post #1437513
Posted Monday, April 1, 2013 11:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 7:12 AM
Points: 31,284, Visits: 15,746
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
Post #1437539
Posted Monday, April 1, 2013 11:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 18, 2014 9:12 AM
Points: 58, Visits: 243
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
Post #1437542
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse