SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Return current sql line number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

samtwilliams
Starting Member

18 Posts

Posted - 08/16/2013 :  12:52:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 08/16/2013 :  12:58:20  Show Profile  Reply with Quote
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 - 08/16/2013 :  13:07:32  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/16/2013 :  13:48:53  Show Profile  Reply with Quote
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 - 08/16/2013 :  14:10:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 08/18/2013 :  04:33:37  Show Profile  Reply with Quote
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

USA
328 Posts

Posted - 08/19/2013 :  17:46:39  Show Profile  Reply with Quote
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

Edited by - lazerath on 08/19/2013 17:50:42
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000