Helpful line numbers



  • I'm testing an SQL script I wrote a while back to create a new database for SQL Server 2008. The script fails with an error message stating "Msg 213, Level 16, State 1, Line 37: Column name or number of supplied values does not match table definition." So I go to line 37 and it's a blank line in the script. SQL Server Management Studio allows you to select part of a script and execute it without running the rest of the script, so I dropped the database and then highlighted each table definition or insert statement one-at-a-time and ran them sequentially to find out which one was causing the error. The line number where the problem actually was? Line 338. 



  • Is it possible that the code after your (blank) line 38 was wrapped in some block structure like a BEGIN TRANSACTION…END TRANSACTION? I've had SQLSMS report something similar to me in the past, sending me miles away from the real source of the error condition. This was explained to me as happening because the error message comes direct from the SQLS engine, which isn't as 'insightful' as SQLSMS and can 'sometimes' treat a Big Block as one line for this purpose.


  • BINNED

    Next time, double-click the error message. That should at least get you in the general vicinity of the error.



  • It meant line 37 in the batch, not the script.  Every GO terminates a batch.  Just double-click it.  SSMS knows what batches it submitted and which batch produced that error, so it can properly interpret the line number.  The message was from the server - from its perspective, the batches submitted had no relationship to each other.



  •  Godamn SQL Server... I recently managed to set it up to replicate a database (could they POSSIBLY add more crap to that process?!) and yesterday I decided to change the database name. I dropped the publication, renamed the database and setup the publication again, except now the subscribers could no longer subscribe. Spent hours going through the 5 billion ACLs in there until I realized the database login I was using no longer had my database as its default. Why the hell would it lose that setting? I didn't drop and recreate the database, I simply renamed it. Surely they link logins to databases via some sort of internal id and not with their name, right? RIGHT?



  • Even stranger is why replication cares about the default database of a login.  I'll bet the problem was actually the subscription holding the old database name.  If you connect to a SQL Server and specify a non-existent database in the connection string, you get the standard "you don't have access" error message, exactly as if you'd specified a valid database that you don't have access to.

    As for adding more stuff, sure they can.  I am currently working on a performance problem with replication and Microsoft support told me that the query that's taking all the CPU time was added as a hotfix to address an issue replicating to Windows CE.  I wish they would simply un-hotfix it.



  • Just wait until you hit a bug in a trigger. The error message doesn't give any indication that triggers were involved - it just complains that there was an error on the line with the insert/update/delete which caused the trigger to be executed.



  • @pjt33 said:

    Just wait until you hit a bug in a trigger. The error message doesn't give any indication that triggers were involved - it just complains that there was an error on the line with the insert/update/delete which caused the trigger to be executed.
    Yet another reason to never use triggers.


Log in to reply