Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Return current sql line number

Author  Topic 

samtwilliams
Starting Member

18 Posts

Posted - 2013-08-16 : 12:52:22
Hi All,

I am trying to debug a rather large scripts, i have a table where I am going to insert a timestamp and hopefully a line number.

My question is can sql return the current line number of the script? I know it does it when theres a problem so msut be possible via select?

Thanks
Sam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-16 : 12:58:20
Are you using try..catch?

If so, in the catch block you can use the ERROR_LINE() funciton to get a line number.
Go to Top of Page

samtwilliams
Starting Member

18 Posts

Posted - 2013-08-16 : 13:07:32
sadly not, what i want is to scan and replace every go statement in my script to add this extra line number insert. If i did it by hand it woudl take all year. my script contains all kinds from create tables to drop tabels to mvoe data etc. and i need to add timings in at every go.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-16 : 13:48:53
you've the option available in sql server management studio to insert line number.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

samtwilliams
Starting Member

18 Posts

Posted - 2013-08-16 : 14:10:13
That won't really solve my issue, I need to insert time stamps where ever there is a go in my script so I can work out the slowest parts
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-18 : 04:33:37
that you need to do in some scripting language like vb .net/c# .net. you can create a file object and then parse it and do the replace

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-19 : 17:46:39
Crazy idea, but we all know how to solve this if it was a SQL problem, so let's use the tools we have!

I took a sql script I had lying around, in this case it was a missing index query that calculates an improvement measure and sorts the output to show you the top 10 highest impact missing indexes on your system, and I just did a FIND & REPLACE on ' with ''. Then I put the result into my var @stringtoReplaceGOs. The rest is just simple sql to manipulate it.


DECLARE @segments table (id int identity(1,1),Segment varchar(max));
DECLARE @x xml;
DECLARE @stringtoReplaceGOs VARCHAR(MAX);
DECLARE @OutputScript VARCHAR(MAX);

SET @stringtoReplaceGos =
------------------------------------------------------------------------------------------
-- Paste your SQL script inside the string below, remember to replace ' with '' beforehand
------------------------------------------------------------------------------------------
'GO

SELECT top 10 REPLACE(REPLACE(mid.statement,''['',''''),'']'','''') as dbtable_name,
CONVERT(BIGINT,ROUND(migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans),0)) AS improvement_measure,
object_name(mid.[object_id], mid.database_id) as table_name,
''IDX_'' + LEFT (PARSENAME(mid.statement, 1), 32) + ''_'' + LEFT(REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'',''''),CASE CHARINDEX('','',REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'','''')) WHEN 0 THEN LEN(REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'','''')) ELSE CHARINDEX('','',REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'',''''))-1 END) + ''_NDX'' + CONVERT(VARCHAR(50),ROW_NUMBER() OVER (PARTITION BY mid.statement ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC)) AS INDEX_NAME,
''NONCLUSTERED'' AS INDEX_TYPE,
REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'','''') as index_columns,
REPLACE(REPLACE(mid.included_columns,''['',''''),'']'','''') as included_columns,
migs.user_seeks,
migs.user_scans,
0 as User_lookups,
'''' as User_updates,

''CREATE INDEX [IDX_'' + LEFT (PARSENAME(mid.statement, 1), 32) + ''_'' + LEFT(REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'',''''),CASE CHARINDEX('','',REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'','''')) WHEN 0 THEN LEN(REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'','''')) ELSE CHARINDEX('','',REPLACE(REPLACE(ISNULL(mid.equality_columns + CASE WHEN mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END,'''') + ISNULL (mid.inequality_columns, ''''),''['',''''),'']'',''''))-1 END) + ''_NDX'' + CONVERT(VARCHAR(50),ROW_NUMBER() OVER (PARTITION BY mid.statement ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC)) + '']''
+ '' ON '' + mid.statement
+ '' ('' + ISNULL (mid.equality_columns,'''')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN '','' ELSE '''' END
+ ISNULL (mid.inequality_columns, '''')
+ '')'' + ISNULL ('' INCLUDE ('' + mid.included_columns + '')'', '''') AS create_index_statement,

migs.*, db_name(mid.database_id), object_name(mid.[object_id], mid.database_id)


FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid
ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10 --and database_id = 10
ORDER BY --mid.statement ,
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC;
go
PRINT(''hi'');
go
PRINT(''more stuff!'');
GO
PRINT(''more stuff!'');
GO'
------------------------------------------------------------------------------------------
-- Your SQL Script should end here with a '
------------------------------------------------------------------------------------------


SELECT @X = CONVERT(XML,'<root><s>' + REPLACE(@stringtoReplaceGOs,'
','</s><s>') + '</s></root>')

INSERT INTO @segments(segment)
SELECT T.c.value('./text()[1]','varchar(max)') FROM @X.nodes('/root/s') T(c);

UPDATE seg
SET Segment = 'SELECT ' + CONVERT(VARCHAR(MAX),id) + ' as Line, GETDATE() as CurrentDateTime;'
FROM @segments as seg
WHERE Segment LIKE 'GO%';

SELECT @OutputScript=COALESCE(@OutputScript,'') + COALESCE(Segment,'') + '
'
FROM @segments
ORDER BY id;

SELECT @OutputScript;

EXEC (@OutputScript);


* EDIT: Added comments to clarify where the code goes
Go to Top of Page
   

- Advertisement -