I've recently come accross a typical SQL Remote schema upgrade problem:
Using ALTER TABLE to modify the definition of an existing column (simply to increase the size of a varchar column) using v8 syntax (i.e. using the "modify" keyword works in the v12 consolidated and in v10 and v12 remotes, but fails with v8 remotes. The following syntax
ALTER TABLE dbo.MyTable MODIFY MyColumn varchar(120) NULL;
is sent as
ALTER TABLE dbo.MyTable ALTER MyColumn varchar(120) NULL;
which is not valid syntax for v8.
Using dynamic SQL ensures that the syntax is not parsed and modified locally, and the stored procedure is necessary as passthrough mode doesn't allow "direct" dynamic SQL statements.
That being said, I'd suggest to provide a better solution, since
So I would suggest to add an option to the PASSTHROUGH statement to preserve the syntax as entered. Then , unless one would enter syntax that is not allowed for any of the according remotes, it should work without pitfalls...
(Aside: I'm full aware that this suggestion does sound rather naive - feel free to explain why it would be hard to implement...)
asked 29 Jan '13, 08:07