Sql Script Doesn't Execute But Function Succeeds

[Yort]

Hi All,

I'm having a really strange behaviour with the Microsoft Sql Server Script command. I should mention this command used to work in my existing script, but it has suddenly stopped and I have no idea why.

The script command itself uses variables to specify the username, password, server and instance. I've put a messagebox command in to display the contents of these variables before the script command executes, and they appear to be right. Apart from that, the script command also has the 'create database is absent' checkbox checked, and it will successfully create a blank database if it doesn't exist - so the command must be executed, and the connection successful.

My problem is the script itself appear not to run. There are two reasons for this;

1. The schema created by the script does not appear in the database.
2. During the install I can usually see lines of text from the script file as they are executed, this is no longer appearing for the script file in question.
3. The script takes almost no time to run, in fact I suspect the short delay is purely down to creating the database.

The script command itself is using the #FILESCRIPT# syntax, i.e

#FILESCRIPT#$SUPPORTDIR$\\OTSControlDatabase.sql

I have tried putting a messagebox in to display the result of $SUPPORTDIR$\\OTSControlDatabase.sql, and the result displayed does point to a valid copy of the script file. I've also manually executed the script file (from the supportdir while the install was stopped on the messagebox) and the script executes without error in Sql Management Studio.

I have setup the script command to place the 'result' into a variable, and the variable returns 'Success', but the script still doesn't appear to be applied.

I have tried renaming the file in case there was some weird reserved name thing going on. I've also placed two script commands in the code instead of one, the first runs a blank script and has the 'create database' checkbox ticked, and the second just executes the script against the specified database but doesn't have the 'create database' flag set. Still no joy.

I have other scripts in the install (recently added), which do execute (they insert data into the tables created by the first script). These obviously fail because the first script hasn't run and created the required tables, but I can see the script text in the progress dialog as the installer tries to execute them. I do not get this same behaviour on the first script, which is why I suspect it isn't being executed.

I have tried turning on logging for the install, but it doesn't appear to log errors returned from the database engine (BTW guys - this would be a hugely useful feature, and would probably reduce the number of posts about the sql script function).

One final thing. I noticed my messagebox that displayed the result of $SUPPORTDIR$\\OTSControlDatabase.sql ended up showing a double \\ between the end of the path and the start of the file name, i.e
c:\\documents and settings\\user\\local settings\\temp\\blah\\blah\\\\myscript.sql.

I tried changing the script so it just used $SUPPORTDIR$OTSControlDatabase.sql and this removed the double \\ displayed in the messagebox, but didn't fix the problem with the script not executing.

Are we supposed to put \\ between $SUPPORTDIR$ and our script file name, or not ? Or do we need to do some scripting to check whether or not the path ends with \\ ? The help seems to suggest that a \\ is required, but the double \\ in my messagebox bothers me.

If anyone has any ideas on why this behaviour is occuring, or how I can enable some sort of useful logging of the errors, that would be great.

Thanks.

PS: Some more detail. I've just run Sql Profiler against my test server while running the install. At the point where my (failing) script executes, the command sent to Sql Server is

exec ÿþU

I can only assume from this that the script file is not being found/read properly, but I have absolutely no idea why. Any ideas, anyone ?

[Yort]

Ok, so after hours of debugging I finally give up and post my problem, then solve it myself 1/2 an hour later ! Typical, eh ?

Turns out the last time the script was created, it was written as a unicode text file. It wasn't supposed to be, and I didn't realise this had happened. In any case, as soon as I converted the script back to an ASCII text file and rebuilt the install, it all worked fine.

So the moral of the story is - don't save your Sql scripts in Unicode format.