create function sql server 2005 disable errors

Question

I'm trying to write a *.bat file which runs all sql-scripts in given folder (every file in this folder has a create function script):

for /r "%~dp0\Production\Functions" %%X in (*.sql) do ( 
   sqlcmd -S%1 -d%2 -b -i "%%X"
)

But some functions in the folder are depended on others. So I get Invalid object name error. Is there a way to disable this error?

Joe Stefanelli · Accepted Answer · 2010-12-20 15:22:13Z

1

Rename your files so that they're listed in the correct order of precedence. So, for example, if FuncA.sql uses FuncB.sql, then rename the files as 001-FuncB.sql, 002-FuncA.sql.

answered Dec 20, 2010 at 15:22

Joe Stefanelli

136k21 gold badges243 silver badges241 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

StuffHappens Over a year ago

This might help, but I'm looking for more "correct" solution if there's one. Thank you for answer.

Joe Stefanelli Over a year ago

You think ignoring/disabling errors is more "correct" than getting the order of precedence and object dependencies right?

StuffHappens Over a year ago

It's much more convenient. Imagine that I have 100 of scripts and I need to add one more with number 10. I have to rename 90 files. That's awful.

Joe Stefanelli Over a year ago

Simply leave some gaps in your numbering system. 0100-FuncB.sql, 0200-FuncA.sql, then you could add a 0150-FuncC.sql when the situation occurred.

Philip Kelley · Accepted Answer · 2010-12-20 15:31:53Z

It is not possible to disable errors generated by SQL when you run (what I think of as) code-based object: stored procedures, functions, views, triggers, and anything else that has to be the sole object of a batch submitted to SQL.

It is also awkward at best to work around this problem. Some options:

One way, as Joe Stefanelli recommends, is to name your files such that they get executed in proper order (by name, or perhaps by date created or something more esoteric).
Another way is to group related functions in single scripts, such that referenced objects must be created before referencing objects.
Or combine the above two, putting all your dependent objects in one script you can guarantee will always run first. Not so useful if your have nested references.
A last (and more kludgy) way is to iterate over your scripts several times (assuming your "create" script will properly deal with an object that already exists), until a given pass raises no errors.

For development purposes, we store code-based objects in individual files, but when it comes time to wrap the code up for push to Production systems, I glom the files together, test it, and shuffle the contents around and retest until no more errors are generated.

Collectives™ on Stack Overflow

create function sql server 2005 disable errors

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related