Bizarre SQLCMD error

  • We have a bizarre thing happening with SQLCMD on SQL Server 2005. Most of the statements in a T-SQL file supplied on the command-line run fine, but sqlcmd won't run the following UPDATE:

    [font="Courier New"]

    ... statements ...

    GO

    UPDATE <table1>

    SET <table1>.<field1> = <table2>.<field1>,

    <table1>.<field2> = 'blah'

    FROM <table2>

    WHERE <table1>.<pk> = <table2>.<pk> AND

    <table2>.<pk> IS NOT NULL

    GO

    ...

    statements

    ...

    GO

    [/font]

    We've tried commenting out the bits of code before and after the UPDATE and the statement still doesn't work so it can't be anything to do with failure or success of previous / following statements, and it can't be anything to do with execution order of statements either.

    We've even gone so far as to look at the code in UltraEdit so we can see if any bizarre hex-characters have crept in and nobbled the script, but everything is hunky-dory.

    Has anyone else experienced anything like this before?

  • The error message would have helped..

    But, I seem to remember something about:

    SET <table1>.<field1> = <table2>.<field1>,<table1>.<field2> = 'blah'

    I think it should just be:

    SET <field1> = <table2>.<field1>,<field2> = 'blah'

    You have already told it to update table1.

    Just a thought..

    CEWII

  • There's no error message associated with the UPDATE statement, and the statement works just fine when copied to the SQL Server T-SQL Editor and executed.

  • So you are saying there was no error generated but the statement just didn't work?

    CEWII

  • Precisely.

  • It gets even stranger; if we add the following ...

    ... statements ...

    GO

    PRINT 'Unpredictable application behaviour is the bane of my life' <-----------------------------------

    UPDATE <table1>

    SET <table1>.<field1> = <table2>.<field1>,

    <table1>.<field2> = 'blah'

    FROM <table2>

    WHERE <table1>.<pk> = <table2>.<pk> AND

    <table2>.<pk> IS NOT NULL

    PRINT 'Unpredictable application behaviour is the bane of my life' <-----------------------------------

    GO

    ...

    statements

    ...

    GO

    The statements which previously were not run, are run.

  • So you had a GO statement following your update statement that didn't seem to run? That sort of rang a bell, if you didn't I can think of cases where it wouldn't be run. But if it is in the middle then I am not sure.

    CEWII

  • anything to do with the 500Mb limit on the sql script ?

    putting the "GO" in truncates the size of the script by breaking it up into smaller scripts

    http://www.red-gate.com/MessageBoard/viewtopic.php?t=8109

  • Hi - nice idea (I wasn't aware of that particular issue), but no - the script is far smaller than 1MB.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply