Go and goto in one sql script gives error label not declared

[BF]

Hi,

I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.

So I read the version from a table and if it match I want to "Goto
Versionxxx"

Where Versionxxx: is set in the script with the right update script.

Whenever I have some script which need Go commands I get error in the
output that

A GOTO statement references the label 'Versionxxx' but the label has
not been declared.

But the label is set in the script by 'Versionxxx:'

Is there a way I can solve this easily?

Thanks in advance

[dmarkle]

Re: Go and goto in one sql script gives error label not declared

Here's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO') " in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle

http://www.markleconsulting.com/blog

BF wrote:

Hi,
>
I have a problem:
I am writing an update script for a database and want to check for the
version and Goto the wright update script.
>
So I read the version from a table and if it match I want to "Goto
Versionxxx"
>
Where Versionxxx: is set in the script with the right update script.
>
Whenever I have some script which need Go commands I get error in the
output that
>
A GOTO statement references the label 'Versionxxx' but the label has
not been declared.
>
But the label is set in the script by 'Versionxxx:'
>
Is there a way I can solve this easily?
>
Thanks in advance

[BF]

Re: Go and goto in one sql script gives error label not declared

Thanks for the quick respond.

The solution is not quite what I was hoping for.

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Grtx Bob

dmarkle schreef:

Here's the trick with "GO":
>
It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO') " in Query
Analyzer.)
>
Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.
>
So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.
>
-Dave Markle

http://www.markleconsulting.com/blog

>

[dmarkle]

Re: Go and goto in one sql script gives error label not declared

To be totally honest with you, I think the easiest/best way to solve
this would be to write a batch file that calls OSQL or SQLCMD against
the proper version of the file. Put your version-switching logic in
the batch file, and simply run OSQL on the appropriate files.

Some people execute their batches using sp_executesql, but it's really
messy and I don't really recommend it. Basically, using this method,
you'd be doing things like:

EXEC sp_executesql 'CREATE TABLE dbo.foo'
EXEC sp_executesql 'CREATE INDEX IX_xxx ON dbo.foo'
....

instead of:

CREATE TABLE dbo.foo
GO
CREATE INDEX IX_xxx ON dbo.foo
....

AFAIK, that's the only way to do what you want to do in 100% pure
T-SQL.

-Dave


BF wrote:

Thanks for the quick respond.
>
The solution is not quite what I was hoping for.
>
For each new version I create an update script, We have an app which
does that and there are lots of Go commands.
>
I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.
>
Grtx Bob
>
dmarkle schreef:

Here's the trick with "GO":

It's not actually a part of the T-SQL language. It's a batch
separator. (Don't believe me? Try running "exec('GO') " in Query
Analyzer.)

Think of it like this: Cut up your script into multiple files,
separated by the "GO" statement. Run each of these files individually,
but use the same connection. That's all "GO" does.

So you need to remove the "GO" batch separators in between your
statements that need to be run in the same batch.

-Dave Markle

http://www.markleconsulting.com/blog

[Erland Sommarskog]

Re: Go and goto in one sql script gives error label not declared

BF (bob@faessen.ne t) writes:

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

No there isn't. There are a lot of GO separators.

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.
>
For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.
>
When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)

You can write this simple script runner in about any language - except for
T-SQK.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[BF]

Re: Go and goto in one sql script gives error label not declared

Ok, great answers:

I build the updates with installshield 12 and I am no programmer so I
will go and try which will fit for me.

Probably I will place all scripts in the support dir from installshield
12 and run the from a vbscript with sqlcmd based on some tests.

I will try some things this week.

Thanks for the replies.

Grtx Bob


Erland Sommarskog schreef:

BF (bob@faessen.ne t) writes:

For each new version I create an update script, We have an app which
does that and there are lots of Go commands.

>
No there isn't. There are a lot of GO separators.
>

I want to have one update script for all versions of the app so we have
2.00 to 2.01 to 2.02 to 2.03 etc.

For each version I have a script and I want to lookup the version, if
version is 2.03 I can start updating from 2.03 to 2.04 with the goto I
can jump over all other updates because they are already done in the
past.

When I use different files I cannot easy control which files to
execute, or I have to run them from the main script.

>
Right. The best way is to solve this is to write a little script runner that
reads a suite of files, and from the file names decudes which version the
file applies to, and then runs the file if needed. Your script would have to
break the script apart on the "go" separator, but this is trivial stuff.
(Hint: don't worry about "go" being entwined in comments ot string literals.
The standard query tools don't do that either. But care about leading and
trailing blanks, and inconsistent use of upper/lowercase.)
>
You can write this simple script runner in about any language - except for
T-SQK.
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx