Tag: SQL server

How to get SQL Data in chunks from remote server

OFFSET FETCH is a feature added to the ORDER BY clause beginning with the SQL Server 2012 edition. It can be used to extract a specific number of rows starting from a specific index.

Here is a simple example;

SELECT * FROM [dbo].[tblSample] 
order by SampleId 
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY

This instruct SQL Server to start from first record and return 10 records only.

Let’s write a dynamic sql to get data in batches; For example we have 7 records from database and we would like to get 3 records on each round trip;

DECLARE @RowCount INT, @RowIncrement INT, @RowsInChunk INT, @RemainingRows INT, @SourceQuery NVARCHAR(255)
DECLARE @RowIncrementValue INT, @BatchValue INT
DECLARE @SQL_Select NVARCHAR(4000);
DECLARE @RowIncrementCheck INT;

SET @RowCount = 7;
SET @RowIncrement = 0;
SET @RowsInChunk = 3;

--initial value
SET @RowIncrementValue = @RowIncrement
SET @BatchValue = @RowsInChunk

--loop through all records
WHILE (@RowIncrement <= @RowCount)
BEGIN

	--prepare SQL statement
	SET @SQL_Select =
	'SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  ' + CAST(@RowIncrementValue AS NVARCHAR) + ' ROWS ' + CHAR(13) +
	'FETCH NEXT ' + CAST(@BatchValue AS NVARCHAR) + ' ROWS ONLY'

	--EXECUTE sp_executesql @SQL_Select
	PRINT @SQL_SELECT

	SET @RowIncrement = @RowIncrement + @RowsInChunk
	SET @RowIncrementValue = @RowIncrement

	--calculate remaining rows
	SET @RemainingRows = @RowCount - @RowIncrement
	PRINT 'Remaining Rows ' +  CAST(@RemainingRows AS NVARCHAR)
	IF (@RemainingRows <= @RowsInChunk) AND (@RowIncrement <= @RowCount)
	BEGIN
		--short circuit this loop
		SET @BatchValue = @RemainingRows
		SET @RowIncrement = @RowCount
	END
END

Here is the result of 3 dynamic sql for 7 records;

SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  0 ROWS 
FETCH NEXT 3 ROWS ONLY
SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  3 ROWS 
FETCH NEXT 3 ROWS ONLY
SELECT *
	FROM [dbo].[tblSample] 
	order by SampleId 
	OFFSET  6 ROWS 
FETCH NEXT 1 ROWS ONLY

In the query above, OFFSET 0 is used to skip 0 rows and FETCH 3 ROWS ONLY is used to extract only 3 rows.

To get additional information about the ORDER BY clause and OFFSET FETCH feature, refer to the official documentation: Using OFFSET and FETCH to limit the rows returned.

Resources

Getting data from remote server

Remove all spaces from strings using sql

I wanted to remove following spaces and special characters from these strings;

'a. Personnel '
'j.  Indirect Charges '

I tried to use following SQL to get rid of all spaces;

SELECT DISTINCT TRIM(LOWER(REPLACE(BudgetCategories, ' ', ''))) BudgetCategories
FROM [dbo].[MyTable]

Still the trailing spaces were there. I guess there is a special character involved, so i tried this one;

SELECT DISTINCT LTRIM(RTRIM(LOWER((REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(BudgetCategories, ' ', ''), CHAR(10), CHAR(32)),CHAR(13), CHAR(32)),CHAR(160), CHAR(32)),CHAR(9),CHAR(32))))))
FROM [dbo].[MyTable]

This did the trick and I was able to get this output;

'a.personnel'
'j.indirectcharges'

References

https://stackoverflow.com/questions/21585914/trim-spaces-in-string-ltrim-rtrim-not-working

SQL Server Transaction block

This is suggested code block for creating stored procedures in SQL Server;

BEGIN TRY
	BEGIN TRANSACTION
	--All your insert/update/delete/merge goes in here
	COMMIT TRANSACTION
END TRY

BEGIN CATCH
	SELECT ERROR_NUMBER() AS ErrorNumber
	,ERROR_MESSAGE() AS ErrorMessage
	 ROLLBACK TRANSACTION
END CATCH

While manipulating multiple tables, the transaction would be roll back if there is any error.

Drop orphan users – internal.object.permissions

I did a database restore from development to staging. Couldn’t connect to database using user login, Adam. Selected General tab on properties by clicking on Database->Security->User->Adam. The User type is “SQL user without login”. It seems restore process didn’t connect to the logins.

I try to drop the user;

DROP USER [qsadb]
GO

I keep getting this error;

  • Msg 208, Level 16, State 1, Procedure ddl_cleanup_object_permissions, Line 8 [Batch Start Line 2]
  • Invalid object name ‘internal.object_permissions’.

So, this is an orphan user and issue is with permissions. This needs to be fixed. But how?

Here are the steps that I have used;

The problem is that the user in the database is an “orphan”. This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-language) that has to match as well.

This used to be a pain to fix, but currently (SQL Server 2000, SP3 and up) there is a stored procedure that does the heavy lifting.

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'AUTO_FIX', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'AUTO_FIX', 'user', 'login', 'password'