The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

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.

Problems like this one have been discussed here now and then, and Reg's general workaround as stated here is obviously helpful, i.e. within passthrough mode:

  • creating a stored procedure that does contain the desired statements as dynamic SQL (i.e. using execute immediate)
  • call that stored procedure
  • drop that stored procedure

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

  • it's not really obvious which statements are "modified" in the parsing phase (as they are silently accepted by the consolidated),
  • it's somewhat difficult/error-prone to turn existing DDL scripts into dynamic SQL - or say, it's at least wayyy easier to just wrap an existing, "locally-tested" DDL script with a "passthrough .. " / "passthrough stop" bracket -,
  • in contrast, using SQL Remote with remotes from different versions is not that difficult.

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

Volker%20Barth's gravatar image

Volker Barth
29.5k291441646
accept rate: 32%

Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×106
×73
×40

question asked: 29 Jan '13, 08:07

question was seen: 721 times

last updated: 29 Jan '13, 08:07