This is one of those things that get me from time to time. For some reason a module install fails in the Sql Data Provider, and you can't work out why. The timespan between each happening is just enough for me to forget the reason and send me on a fruitless chase. So I'm going to commit the answer to blog and solve it once and for all.
Here's the scenario: You've developed a DNN module and you're happy with the way it is working. You've found all the obvious bugs and are moving towards starting the packaging and install process. So you script up all your (working) Sql into a SqlDataProvider file, put it in the module and try an install.
Either the install script fails with a mysterious script syntax error (what the...?) or it fails once you are running a piece of code.
If you're anything like me you delve deep into the T-SQL code, repackage everything, retest everything and get stumped. Then you start up the Sql Profiler and carefully examine the execution as it happens.
Then you discover, that for some weird reason, all the '+' characters in your script have been removed.
Thinking there is some heinous bug in DNN you start madly thrashing Google looking for an answer. Surely someone has hit this problem before. I have even tried searching out for alternative T-SQL syntax for add and concatenate, the two usual uses of the '+' operator (hint: there are no alternatives)
Depending on your search engine skills, you'll eventually happen across the reason: You need to save the SqlDataProvider files in UTF-8. Depending on which text/script editor you used to create the files, chances are they're not in UTF-8.
The solution? Resave your .SqlDataProvider files in UTF-8, repackage and try your install again. I'll bet it works now !
And try and remember it next time you create a new module.