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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
343 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  
 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.08 seconds. Powered By: Snitz Forums 2000