I'm trying to create a proc that will drop and create a function

Question

I'm trying to create a proc that will drop and create a function

Viewing 5 posts - 16 through 19 (of 19 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

There are ways to put more robust error handling into scripts like that, but I'm not suggesting them because they'd take a lot of editing, and you don't have time/resources to do that right now from what I gather.

There are ways to handle errors and rollbacks/commits even across batches. Have you used RedGate SQL Compare? Take a look at how they do error handling in the scripts that generates and you'll see a working example of it. (If you haven't played with that, I think you can get a free trial copy, and you can take a look at it that way.) But that would require significant editing of your script files.

This situation is really something that has to be planned for and worked into the solution right from the beginning. Not sure how to handle it, coming in when the scripts have already been written and your go-live deadline is looming.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Snargables SSCrazy Eights Points: 8848 More actions · Answer 2

Oh, and as far as Joe goes. As my dad used to say. "Every a-hole's got an opinion." I thought it was funny. I sent the post to all my friends. It’s was classic. He actually stated I was a detriment to society. That’s good stuff.

Well, funny and disturbing at the same time. I looked him up. He knows his stuff. We can't deny that. The disturbing part is the fact that here’s a guy at the top of his field, an expert sql guy and he has nothing better to do but trolling the web searching for people to spew his elitist jargon upon. It’s sad really. I'd like to think I’d be fishing in Bermuda of diving somewhere sipping a cocktail w/ my beautiful wife and chocolate lab.

sqlPirate_Fl SSC-Addicted Points: 442 More actions · Answer 3

If you run SQLCMD via command line, you can set it to run as your user account (or the one you plan to make changes with). I believe the poster that referenced SQLCMD in this fashion, not via SSMS.

You still have a block against that?

Write a batch file with the following (assuming your BINN folder is in the path variable):

sqlcmd -U MyLogin -S <ComputerName>\<InstanceName> -i <MyScript.sql> -o <MyOutput.rpt>

create a separate line for each script. the rpt data will allow you to see the results of each step. I'm not batch champion but you could even incorporate some error logic I think to get it to stop in case you have problems with one line. You have access to google/bing/yahoobing so you can find that part out on your own.

If you're using a different user account and it's windows based, I'd open your command line via that account (shift right click, Run as Different User).

You could always test the technique by doing a simple select in your sql file...

Snargables SSCrazy Eights Points: 8848 More actions · Answer 4

actually. no. i shouldn't have any security issues running sql smd prompt manually. i was thinking of running it in script call or via job. I'll give that a try in a lil while.

thanks

GSquared SSC Guru Points: 260824 More actions · Answer 5

Snargables (1/16/2013)
actually. no. i shouldn't have any security issues running sql smd prompt manually. i was thinking of running it in script call or via job. I'll give that a try in a lil while.
thanks

If you can't, and you're the DBA, get it set up so you can.

Take a look at the uses of the "Dedicated Admin Connection" (DAC) for why it's important. There will be times when a more complex client won't be able to deal with an unstable SQL Server instance, but DAC + sqlcmd will be. Important to plan for that. (DAC can also be accessed via on-server-SSMS, but if the server is running out of RAM and/or CPU resources, the command prompt takes less resources and might work even if SSMS can't start.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon