I have a distributed application that uses SQL Express, I've written an auto updater for the application that is working great. My problem is deploying Either, Schema Change or modifying/adding a Stored Proc. I am currently using SMO and this is not very reliable, or maybe I am not using it correctly. If I need to deploy Database change, alter a table, modify stored proc ect, what is the best way to do this? I currently create a SP file that smo consumes, but I am finding it runs on some and not on others. Any help would be greatly appreciated.
How about sqlcmd, could one use sqlcmd and consume a sp file? does it support return values so an autoupdater would get confirmation if a script ran? How does everyone else here manage remote SQL Express schema changes, when not on the local network or wan. I have to believe their is a somewhat simple way to accomplish this.