"Rob Meade" <robb.meade@NO-SPAM.kingswoodweb.net> wrote in message
news:jC_Ad.10$7M2.1@text.news.blueyonder.co.uk...
> Hi all,
>
> I've been having problems with an over night job since the 20/21st
> December,
> basically the import had been failing because of a problem with a view.
>
> Turns out that SQL Server wasn't able to determine which views should be
> created first, ie, which are dependant on other views - as a result it
> tries
> to create a view which references one that hasn't been created yet :o/
>
> I see in the wizardy thing in Enterprise Manager that there's a Script
> File
> location field, when I used the wizard originally I set it to save to a
> dts - this is what I have at the moment, a DTS and a job. The script file
> location is pointing to C:\Program Files\Microsoft SQL Server\80\Tools
> with
> then LOADS of files in there...
>
> My question is about the ordering of the objects it creates....If SQL
> Server
> can't determine this itself, I assume I'll have to amend the script file
> it
> creates - however, does it create these files every time it's run? If so -
> how can I possible have the script file correct prior to execution?
>
> If anyone can help me with this I would be grateful - the import had been
> running successfully for well over a month and I was chuffed to have
> automated something!
>
> Thanks in advance for any help.
>
> Regards
>
> Rob
>
>

> I am not sure why you are scripting and creating views in a DTS job? The
> order is probably alphabetical and assuming that you are dropping the
views
> (singularly) before creating each view, you could probably run the script
> twice which may result in no errors.

"Rob Meade" wrote:

> "John Bell" wrote ...
>
> > I am not sure why you are scripting and creating views in a DTS job? The
> > order is probably alphabetical and assuming that you are dropping the
> views
> > (singularly) before creating each view, you could probably run the script
> > twice which may result in no errors.
>
> Hi John,
>
> I probably didn't explain myself very clearly - sorry - the procedure I
> carried out initially was to perform an import from a remote SQL Server to
> my own. As I went through the wizard at the end it asks you if you would
> like to schedule it / save it - so I did, this in turn then created a DTS
> package and a scheduled job for me.
>
> When I look at the dts I can open it up and it basically gives me the import
> wizard screen again where I can set all of the objects to import and so on.
>
> On the window for the wizard at the bottom there's a field for the 'script
> directory' - I hadn't ever changed this or done anything with it - but when
> I looked in this directory there were lots of files, and having removed them
> all and run the dts again - they all re-appeared - so its obviously creating
> these files, then executing the scripts to do the import (I'm assuming).
>
> I didn't check to see if it was doing them alphabetically, but have got
> around the problem for now by splitting it all into pieces...the first dts
> gets the tables and stored procedures, the second gets the 1 view which
> needs to be done before one of the others, then the last dts gets all of the
> rest of the views. All wrapped up in one job that fires them off one after
> the other...its working, but just feels like it could have been achieved
> better...I could of obviously changed the view which is dependant on the
> other view to use a load of joins instead to achieve the same result, but
> that seemed like for more work that was warranted....
>
> Regards
>
> Rob
>
>