Bizarre SQLCMD error

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Bizarre SQLCMD error

Bizarre SQLCMD error

Rate Topic

Display Mode

Topic Options

Author

Message

simon.harpham

Posted Tuesday, February 09, 2010 10:13 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 09, 2010 2:54 PM
Points: 3, Visits: 5

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:

... 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

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?

Post #862581

Elliott Whitlow

Posted Tuesday, February 09, 2010 10:20 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #862586

simon.harpham

Posted Tuesday, February 09, 2010 10:36 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 09, 2010 2:54 PM
Points: 3, Visits: 5

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.

Post #862602

Elliott Whitlow

Posted Tuesday, February 09, 2010 1:27 PM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

CEWII

Post #862705

simon.harpham

Posted Tuesday, February 09, 2010 2:55 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, February 09, 2010 2:54 PM
Points: 3, Visits: 5

Precisely.

Post #862756

simonharpham

Posted Monday, February 15, 2010 5:34 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 3:31 AM
Points: 55, Visits: 39

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.

Post #865477

Elliott Whitlow

Posted Monday, February 15, 2010 9:06 AM

SSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 12:02 PM
Points: 5,854, Visits: 4,873

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

Post #865574

dave heywood

Posted Tuesday, March 16, 2010 9:19 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, March 16, 2010 9:17 AM
Points: 3, Visits: 4

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

Post #883889

simonharpham

Posted Tuesday, March 16, 2010 9:26 AM

Valued Member

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 3:31 AM
Points: 55, Visits: 39

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

Post #883897

« Prev Topic | Next Topic »

Permissions