2

I have a SQL cmd script that looks like this:

:setvar DatabaseName testDb
:On Error Exit
--break point on next line
Declare @DBName varchar(255), @DBPath varchar(255), @LogName varchar(255), @LogPath varchar(255) 
IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)')
BEGIN
USE [$(DatabaseName)];
select @DBName = name, @DBPath = physical_name from sys.database_files where type_desc = 'ROWS'
select @LogName = name , @LogPath = physical_name from sys.database_files where type_desc = 'LOG'
END

Print @dbname
Print @dbpath

When I try to debug the above script in SQL Server Management Studio, the line that is highlighted as the line that is currently being executed, doesn't seem to be the one that is executing. And based on looking at the locals window and when the @dbName gets set, the debugger seems to be off by 4 lines (when the highlighted line is the declare line, the @dbname gets set with the value from the database).

Wondering if it is because the debugger is skipping my setvar statements. Also is there any work-around. It is extremely hard to debug a large script when the current line is not the current line!!!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Raj Rao
  • 8,872
  • 12
  • 69
  • 83

2 Answers2

1

This might be somewhere where LINENO comes in handy. When I use LINENO and then double click on the error message SSMS takes me to the right place.

:setvar DatabaseName testDb
:On Error Exit
LINENO 3
--break point on next line
Declare @DBName varchar(255), @DBPath varchar(255), @LogName varchar(255), @LogPath varchar(255) 
IF EXISTS (SELECT 1 FROM [master].[dbo].[sysdatabases] WHERE [name] = N'$(DatabaseName)')
BEGIN
    USE [$(DatabaseName)];
    select @DBName = name, @DBPath = physical_name from sys.database_files where type_desc = 'ROWS'
    select @LogName = name , @LogPath = physical_name from sys.database_files where type_desc = 'LOG'
END

Print @dbname
Print @dbpath
Community
  • 1
  • 1
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
0

I have found the issue is related to the length of the statements. You will find if you use spaces instead of tabs for spacing it will increase the likelihood of encountering this issue.

Try doing a replace all of 5 spaces to a tab character. This will substantially reduce the size of your statements. (BTW I replace 5 spaces with a tab character by copying the entire procedure to Word and then replacing ' ' with ^t using the replace command.

Then I copy it back. To note, it may not be just spaces that cause the issue. It could be any statement that is overly long (like a long select statement or table create). Hope this helps

Blair Kjenner
  • 1