Transact SQL – Non-ANSI Joins

Background

Trying to see which SQL modules uses legacy Non-ANSI SQL Joins.

Discovery

There are a couple of pathways we can use to find sql code that relies on Non-Ansi SQL Joins.

The mediums we will use are :-

  1. Code
    • Execute Code
  2. Dynamic Management Views
    • System Catalog Views
      • sys.sql_Modules
  3. SQL Server Profiler

Code

Code Execution

SQL

SQL
DECLARE	@return_value int

EXEC	@return_value = [dbo].[sp_get_bottom2]

SELECT	'Return Value' = @return_value

GO
Output
Output – Text
Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 8 [Batch Start Line 2]
Incorrect syntax near '*='.

Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 17 [Batch Start Line 2]
Incorrect syntax near '*='.

Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 26 [Batch Start Line 2]
Incorrect syntax near '*='.

Explanation
  1. Msg 102
    • Msg 102, Level 15, State 1, Procedure dbo.sp_get_bottom2, Line 8 [Batch Start Line 2]
      Incorrect syntax near ‘*=’.

Dynamic Management Views

System Catalog Views

sys.sql_modules

SQL

select *

from   sys.sql_modules tblSSM

where
        (
               (tblSSM.definition like '%*=%' )
            or (tblSSM.definition like '%=*%' )
        )
Output

NonAnsiJoins.20190221.0755AM

SQL Server Profiler

Trace Definition

Outline

  1. Deprecation
    • Deprecation Announcement
    • Deprecation Final Support

Image

sqlServerProfiler.traceProperties.eventsSelection.20190221.0800AM.PNG

Trace Events

Image

sqlServerProfiler.traceEvents.01.traceEvents.20190221.0804AM.PNG

Explanation

  1. Found
    • Deprecation Final Support

Transact SQL :- Error – Msg 15138 – “The database principal owns a schema in the database, and cannot be dropped”

Background

Cleaning up a database as we move it from Development to Production.

Recreate

Drop User

SQL


if user_id('LAB\daniel') is not null
begin

	exec sp_droprolemember
			  @rolename = 'db_owner'
			, @membername = 'LAB\daniel'

	drop user [LAB\daniel];

end
go

Error

Msg 15138

Error Text


Msg 15138, Level 16, State 1, Line 115

The database principal owns a schema in the database, and cannot be dropped.

Error Image

TheDatabasePrincipalOwnsASchemaIntheDatabase.20190204.1119AM.PNG

 

Troubleshoot

Metadata

sys.schemas

SQL


select
          [schema]
            =tblSS.[name]

        , tblSS.schema_id

        , tblSS.principal_id

        , [principal]
            = user_name(tblSS.principal_id)

		, [principalIsFixedRole]
			= tblSDP.is_fixed_role

		, [principalType]
			= tblSDP.[type_desc]

		--, tblSDP.*

from   sys.schemas tblSS

inner join sys.database_principals tblSDP

		on tblSS.principal_id = tblSDP.principal_id 

order by
    user_name(tblSS.principal_id) asc

Output

sys.schemas.20190204.1134AM.PNG

Explanation

  1. Schema
    • db_datareader
      • The owner for the db_datareader schema has been assigned to a database account, other than itself

Remediate

Change Schema Owner

ALTER AUTHORIZATION

SQL


ALTER AUTHORIZATION ON SCHEMA::db_datareader
   TO [db_datareader]
   ; 

Transact SQL – Drop Temp Table if it it exists

Background

Reviewing some Transact SQL Code and saw a code block that works well in exception handling, but can have a bit of side effect in Transact SQL.

Code

Original Code

SQL


BEGIN TRY

    DROP TABLE #pollingData;

END TRY
BEGIN CATCH
END CATCH;

Explanation

When the temp table does not exist, an error is raised.

Because the drop table is enclosed in a try/catch block the error is gracefully handled by the system.

Noise

But, yet there is a bit of silent noises.

SQL Server Profiler

Image

Tabulated
  1. Event
    • Exception
      • Error :- 3701
      • Severity :- 11
      • State :- 5
    • User Error Message
      • Error :- 3701
      • Severity :- 11
      • State :- 5

Trace Events

Image

Explanation
  1. Events
    • objectName = error_reported
      • eventData
      • error Number :- 3701
      • severity :- 11
      • message :- Cannot drop the table ‘#pollingData’, because it does not exist or you do not have permission.
      • sqlStatement :- empty

Revised Code

Check If Table Exists, before attempt to drop

SQL

BEGIN TRY

	if object_id('tempdb..#pollingData') is not null
	begin
		DROP TABLE #pollingData;
    end 

END TRY

BEGIN CATCH

END CATCH;

Drop Table, If Exists

In MS SQL Server v2016 and later versions, we can use the new “drop object if exists” conditional statement…

SQL


drop table if exists #pollingData;

Other Errors

There are other errors that can be avoided with dropping an object only upon validation that it exists.

SET XACT_ABORT ON;

If you use the set xact_abort on directive, your code will abort upon running into the error mentioned above.

Error Message

Msg 3930, Level 16, State 1 ..
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

SQL Server – Number of Error Log Files

 

Background

Trying to review error logs on one of our SQL Server Instances and discovered that it is likely that the error logs we might have to mine is so far back and we might no longer have it.
And, so took to see if we have properly calibrated the Number of Error Logs Files to keep before rollover.

 

Review

GUI

SQL Server Management Studio ( SSMS )

Screen Shot

configuresqlservererrorlogs

 

Explanation

  1. In the screenshot above we can see that the Number of Errors has not been explicit set and so the system will use the default value of 6
  2. To set it we can check the “Limit the number of error log files before they are recycled” and provide a number to set it to

 

Script

For automation, script is a bit better and here is one …

Code

Stored Procedure

Stored Procedure – errorLog.sp_ConfigureNumberofErrorLogFiles


set nocount on;
go

USE [master]
GO

if schema_id('errorLog') is null
begin
	
	exec('create schema [errorLog] authorization [dbo] ')

end
go

if object_id('[errorLog].[sp_ConfigureNumberofErrorLogFiles]') is null
begin

	exec('create procedure [errorLog].[sp_ConfigureNumberofErrorLogFiles] as ')

end
go

alter procedure [errorLog].[sp_ConfigureNumberofErrorLogFiles] 
(
	  @numberofErrorLogsDesired int = 99
	, @scriptOnly bit = 0
)
as 
begin

	set nocount on;
	set XACT_ABORT on;

	declare @strNumberofErrorLogsCurrent	nvarchar(60)
	declare @iNumberofErrorLogsDefault		int
	declare @iNumberofErrorLogsCurrent		int
	declare @numberofErrorLogsMax			int

	declare @strLog							varchar(120)
	
	/*
		Set default values
	*/
	set @numberofErrorLogsMax = 99
	set @strNumberofErrorLogsCurrent = null
	set @iNumberofErrorLogsDefault = -1
	set @iNumberofErrorLogsCurrent = @iNumberofErrorLogsDefault

	if (@numberofErrorLogsDesired > 99)
	begin

		set @numberofErrorLogsDesired = @numberofErrorLogsMax

		set @strLog = 'Maximum Number of errors specified is : ' 
						+ cast(@numberofErrorLogsDesired as varchar(10))
						+ ' but max allowed is '		
						+ cast(@numberofErrorLogsMax as varchar(10))
						+ '!'
						+ ' will use ' 
						+ cast(@numberofErrorLogsMax as varchar(10))

		print @strLog

	end	

	/*
		Read Registry Value
			HKEY_LOCAL_MACHINE
			Branch : Software\Microsoft\MSSQLServer\MSSQLServer
			Item   :- NumErrorLogs
	*/
	exec master.dbo.xp_instance_regread
			  N'HKEY_LOCAL_MACHINE'
			, N'Software\Microsoft\MSSQLServer\MSSQLServer'
			, N'NumErrorLogs'
			, @iNumberofErrorLogsCurrent output

	/*
		If Number of Error Logs is set, then  convert to string
	*/
	if (@iNumberofErrorLogsCurrent is not null )
	begin


		/*
			If Number of Current Logs has been set
		*/
		if (
				(@iNumberofErrorLogsCurrent != @iNumberofErrorLogsDefault )
			)
		begin

			print '@iNumberofErrorLogsCurrent : ' 
					+ cast(
							isNull(@iNumberofErrorLogsCurrent, '')
								as varchar(10)
						  )


		end

		set @strNumberofErrorLogsCurrent
				= cast
					(
						isNull(@iNumberofErrorLogsCurrent, -1)
						as varchar(10)
					)


		/*
			If String is not null
		*/
		if (
				( @strNumberofErrorLogsCurrent is not null )
		   )
		begin

			set @strLog = '@strNumberofErrorLogsCurrent : ' 
								+ @strNumberofErrorLogsCurrent

			print @strLog

		end


	end -- if (@iNumberofErrorLogsCurrent is not null )


	/*
		If Number of Errors Logs is not set to what we desire, then review it
	*/
	if (
			( @iNumberofErrorLogsCurrent != @numberofErrorLogsDesired )
		)
	begin

		if (@scriptOnly = 0)
		begin

			set @strLog = 'Adjusting Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )
						+ ' .. '

			print @strLog


			EXEC master.dbo.xp_instance_regwrite 
						  N'HKEY_LOCAL_MACHINE'
						, N'Software\Microsoft\MSSQLServer\MSSQLServer'
						, N'NumErrorLogs'
						, REG_DWORD
						, @numberofErrorLogsDesired

			set @strLog = 'Adjusted Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )

			print @strLog

		end --script only
		else
		begin

			set @strLog = 'Skipping Adjustment of Number of Error Logs from ' 
						+ convert(varchar(10), @iNumberofErrorLogsCurrent )
						+  ' to '
						+ convert(varchar(10), @numberofErrorLogsDesired )

			print @strLog

		end
	end

end

GO


Invoke


use [master]
go

declare @numberofErrorLogsDesired int
declare @scriptOnly				  bit

set @numberofErrorLogsDesired = 99
set @scriptOnly =0

exec [errorLog].[sp_ConfigureNumberofErrorLogFiles] 
		  @numberofErrorLogsDesired = @numberofErrorLogsDesired
		, @scriptOnly = @scriptOnly

Output

RegQueryValueEx() returned error 2 – The System cannot find the file specified

Image

cannotfindthefilespecified

Textual
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
@strNumberofErrorLogsCurrent : -1
Adjusting Number of Error Logs from -1 to 99 .. 
Adjusted Number of Error Logs from -1 to 99

Explanation

By default, the value is missing and so when we invoke master.dbo.xp_instance_regread to read it, we receive a warning message that reads “RegQueryValueEx() returned error 2, ‘The system cannot find the file specified.’

 

Registry

The actual registry branch where the information is kept is dependent on the version of the SQL Server Engine and the Instance Name

v2012

Here us what things look list for a SQL Server 2012 Instance, before setting the value

By Default

Image

v2012-default

Explanation

  1. This registry branch above is for a SQL Server v2012 instance
  2. The branch is HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQLServer

 

Post Setting

Once setting the value for Number of Error Logs to keep our registry will look like what we have set below.

v2012-set

 

Source Control

GitHub

Uploaded to github here

SQL Server – Changing Sql Instance Collation – via sqlservr/-q – Little Traps

Preface

In an earlier post we spoke about discovering that we can change our SQL Server Instance by restarting SQL Server with the /q option.

That post is here.

 

Real life experience

If truth be told, our first use-case was a recently installed SQL Server instance without any user databases.

In this post we will cover our experience with another newly built SQL Instance, but on which we will be restoring user database as part of a robust response to a server crash.

Our platform team raised up a new VM Instance, assigned server names and IP Addresses to it, and added it to our Active Directory team.

We installed SQL Server and applied the latest Service Packs.

During SQL Server install, upon being asked for the new server’s collation, we cheated and entered the Collation of another server from the same user community.

We went on to restore databases from the Production DB.  And, we knew that we had to re-align the SQL Server Logins.

We assessed each restored databases and ran a script that transverses the contextual database sysusers table and link each user to the login.

BTW, the SP applied against each user is the sp_change_users_login.

Applying that script unearth our misguidedness.

 

Error

Error Image

cannotresolvecollationconflict

 

Error Textual


Msg 468, Level 16, State 9L

Cannot resolve the collation conflict between Latin1_General_BIN and Latin1_General_100_BIN2 in the equal to operation. 

 

Little Traps

Knew right away that I had chosen the wrong collation during the install.

And, will have to transition over to the right collation.

There are a couple of little traps that one needs to look out for.

 

Read Only Databases

If we have read only databases on the SQL Instance, we can not continue.

Here is the error message.

Error

Error Image

readonlydb-20170103-1037pm

Error Text


2017-01-03 22:33:48.55 spid34s     [INFO] HkCheckpointCtxtImpl::StartOfflineCkpt(): Database ID: [22]. Starting offline checkpoint worker thread on a hidden SOS scheduler.

2017-01-03 22:33:48.55 spid34s     CHECKDB for database 'ppsivr_20161214' finished without errors on 2016-12-28 03:04:32.197 (local time). This is an informational message only; no user action is required.

2017-01-03 22:33:48.55 spid8s      Warning ******************

2017-01-03 22:33:48.55 spid8s      Attempting to change default collation to Latin1_General_100_BIN2.

2017-01-03 22:33:48.55 spid8s      Error: 5804, Severity: 16, State: 1.

2017-01-03 22:33:48.55 spid8s      Character set, sort order, or collation cannot be changed at the server level because at least one database is not writable.
                                   Make the database writable, and retry the operation.

2017-01-03 22:33:48.55 spid8s      Error: 3416, Severity: 20, State: 1.

2017-01-03 22:33:48.55 spid8s      The server contains read-only files that must be made writable before the server can be recollated.

2017-01-03 22:33:48.55 spid8s      SQL Server shutdown has been initiated

2017-01-03 22:33:48.56 spid8s      SQL Trace was stopped due to server shutdown.
                                   Trace ID = '1'. This is an informational message only; no user action is required.

 

 

In Memory Databases

If we have in-memory databases on the SQL Instance, we also can not continue.

Here is the error message.

Error

Error Image

memoryoptimizeddb_20170103_1112pm

 

Error Text


.
2017-01-03 23:07:47.03 spid8s       index restored for rbpivr1.gen_edi_stats_loc_time.
2017-01-03 23:07:47.09 spid8s       index restored for rbpivr1.br_logon.
2017-01-03 23:07:47.79 spid8s       index restored for rbpivr1.it_prp.
2017-01-03 23:10:04.93 spid8s       index restored for rbpivr1.event_log_bkp_chs.
2017-01-03 23:10:06.57 spid8s       index restored for rbpivr1.it_retire_election.
2017-01-03 23:10:06.61 spid8s       index restored for rbpivr1.gen_ua_appgroup.
2017-01-03 23:10:07.46 spid8s      Error: 41317, Severity: 16, State: 4.
2017-01-03 23:10:07.46 spid8s      A user transaction that accesses memory optimized tables or natively compiled procedures cannot access more than one user dat
abase or databases model and msdb, and it cannot write to master.
2017-01-03 23:10:09.54 spid8s      Error: 3434, Severity: 20, State: 1.
2017-01-03 23:10:09.54 spid8s      Cannot change sort order or locale. An unexpected failure occurred while trying to reindex the server to a new collation. SQL
 Server is shutting down. Restart SQL Server to continue with the sort order unchanged. Diagnose and correct previous errors and then retry the operation.
2017-01-03 23:10:09.54 spid8s      SQL Server shutdown has been initiated
2017-01-03 23:10:09.55 spid8s      SQL Trace was stopped due to server shutdown.
 Trace ID = '1'. This is an informational message only; no user action is required.


 

Validate

Code

Validate that in-memory tables exists on the cited database, please issue a query against the sys.tables table and look for cases where the is_memory_optimized is 1.


use [--database=--]
go


select 
		  tblSS.[name]
		, tblSO.[name]
		, tblSO.is_memory_optimized
from   sys.tables tblSO

inner join sys.schemas tblSS

		on tblSO.[schema_id] = tblSS.[schema_id]

where  tblSO.[type] = 'U'

and   tblSO.is_memory_optimized = 1

 

Output

validateinmemorytablesexist

 

 

Remediate

Make a list of the identified databases, their and log files, detach the databases, change the collation, restart the SQL Instance, re-attach the detached databases.

Please keep in mind that you really need to write down the database names and make a good list of the datafiles, as you will need that information when trying to re-attach the databases.

 

Transact SQL – Error – “Arithmetic overflow error converting numeric to data type numeric”

Background

Revising a SQL Code snippet for finding Bookmark Lookups and ran into an error stating:

Msg 8115, Level 16, State 1
Arithmetic overflow error converting numeric to data type numeric

Code

Original Code

Here is the original code …


set NUMERIC_ROUNDABORT ON;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as decimal(30, 0)
										--as float
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as decimal(30, 0)
												--as numeric(30, 1)
												--as float
										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc


 

Error Message


Msg 8115, Level 16, State 1, Line 13
Arithmetic overflow error converting numeric to data type numeric.

Remediation

To remediate please try one of the options stated below:

  1. Set “Numeric Abort off”
    • set NUMERIC_ROUNDABORT OFF
  2. Cast as float

Set Numeric Abort Off

Code


--set NUMERIC_ROUNDABORT ON;
set NUMERIC_ROUNDABORT OFF;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as decimal(30, 0)
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as decimal(30, 0)

										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc




 

Output

SetNumericRunAbortOff

 

Cast as float

Code



set NUMERIC_ROUNDABORT ON;
--set NUMERIC_ROUNDABORT OFF;
go

declare @ratioBookmarkLookup decimal(10, 4)
declare @MaxNumberofRecords int

set @ratioBookmarkLookup = 80.00
set @MaxNumberofRecords = 10

;with cte
as
(

	select top ( @MaxNumberofRecords )

              objectName  
                = OBJECT_NAME(tblI.object_id)

            , tblI.name

            , tblI.index_id

            , tblI.is_disabled
  
            , tblINC.user_seeks

            , tblINC.user_scans

			, [clusteredIndex]
				= tblIUSCLCI.name

            , [userLookupsPK]
				= tblIUSCL.user_lookups 
  
            , [ratioBookmarkLookup]
				= 
					cast
					(
						( 
							cast
								(
									tblIUSCL.user_lookups 
										as float
								)
						)
						/
						( 
							NULLIF
								(
									cast
										( 
											(tblINC.user_seeks + tblINC.user_scans ) 
												as float
										)
									, 0
								)
						) 
						as decimal(30, 4)
				   )  

    from  sys.objects tblO
  
          inner join sys.indexes tblI
  
             on   tblO.object_id = tblI.object_id
  
          inner join sys.dm_db_index_usage_stats tblINC
  
             on   tblI.object_id = tblINC.object_id
             and  tblI.index_id = tblINC.index_id
  
          inner join sys.dm_db_index_usage_stats tblIUSCL
  
             on   tblINC.object_id = tblIUSCL.object_id
             and  tblINC.index_id != 1                    
             and  tblIUSCL.index_id = 1 

          inner join sys.indexes tblIUSCLCI
  
             on    tblIUSCL.object_id = tblIUSCLCI.object_id
             and   tblIUSCL.index_id = tblIUSCLCI.index_id
			   
    where  tblO.[type] = 'U'
  
    and    tblINC.[index_id] != 1
  
    /*
        Non-Clustered Index --> user seeks + user scans
    */
    and     (
                ( 
					(tblINC.user_seeks + tblINC.user_scans) 
						> 0 
				)
            )
  
    -- clustered index lookups occured            
    and     (tblIUSCL.user_lookups != 0)



)

select *

from   cte

where  (

			([ratioBookmarkLookup] >= @ratioBookmarkLookup )

	   )

order by
		[ratioBookmarkLookup] desc



 

Output

CastAsFloat

 

 

Summary

Of the two corrective choices, casting to float is the path that localizes the problem to the specific arithmetic that is failing and so I will suggest you choose it over the “set option” that works more at module level.

Running Big SQL Files against SQL Server – Day 2

Background

Quick follow-up to our last post.  In the post we discussed that we were not able to use sqlcmd.exe to post a relatively big file to SQL Server.

File Stats

Here is the file over EmEditor:

EmEditor-FileProperties

The File’s Size is 175 MB, and it has 160, 000 lines of SQL Statements.

 

Tools

SQLCMD.EXE

StackOverflow.net

Read more online and found this hint from “How do you import a large MS SQL .sql file?“.  Here is the Link.

Takuro suggested adding the -a option.  And, passing in the max value, which is 32767.

Code


@echo on
setlocal DisableDelayedExpansion
 
set "_APP_NAME=sqlcmd.exe"
 
if exist "D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn" (
    set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"
)else if exist "C:\Program Files\Microsoft SQL Server\90\Tools\binn" (
    set "_APP_FOLDER=C:\Program Files\Microsoft SQL Server\90\Tools\binn"	
)else if exist "D:\Program Files\Microsoft SQL Server\90\Tools\binn" (
    set "_APP_FOLDER=D:\Program Files\Microsoft SQL Server\90\Tools\binn"
)else (
    goto BINARY_NOT_FOUND
)
 
set "_APP_FULLPATH="%_APP_FOLDER%\%_APP_NAME%""
set "_user=dbuser"
set "_password=s#mplep1"
 

set "_folderOutput=\\APPSERVER\d$\DBScripts\ProductionToDR\sqlfiles"
set "_filename=dbo.PaymentCharges-full-v3.sql"
set "_filenameFull=%_folderOutput%\%_filename%"

echo _filenameFull %_filenameFull%
 
set "_destinationServer=DBSERVERDR"
set "_destinationDatabase=HRDB"
 
set "_destinationuser=%_user%"
set "_destinationpassword=%_password%"

@REM This option corresponds to an environment variable SQLCMDPACKETSIZE.
set "SQLCMDPACKETSIZE=32767"

@REM -b
@REM Exit on error

time /T

%_APP_FULLPATH%^
    -S %_destinationServer% -d%_destinationDatabase%  ^
    -U %_destinationuser% -P%_destinationpassword%  ^
    -i %_filenameFull% ^
	-a %SQLCMDPACKETSIZE% ^
	-b 

time /T
goto completed
     
:BINARY_NOT_FOUND
echo %_APP_NAME% not found!
  
:completed
setlocal EnableDelayedExpansion


Explanation

  1. Environment Variables
    • SQLCMDPACKETSIZE
      • set SQLCMDPACKETSIZE=32767
  2.  Argument
    • -a option
      • In our case we are passing in the %SQLCMDPACKETSIZE% argument

Options

 

Argument Explanation
-a Option a is used to specify a packet size in the range of 512 through 32767. It has no visible effect on the output.
Increased packet size can enhance performance of a large script execution by packing more data in the envelope.
However, the provider can choose not to grant this request, in which case SQLCMD defaults to the server default packet size.
This option corresponds to an environment variable SQLCMDPACKETSIZE.
-b Terminate batch job if there is an error

 

Listening

Listening to Mali Music – Beautiful @MaliMusic