Category: Data Design

All about Azure Databases, Azure single database, Azure managed instance, Azure Cosmos database, Azure VM based databases

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

SSIS Foreach Loop Folder as variable

I need to set the Folder in Foreach loop to a variable location. I tried just adding the location to my package configuration, and it worked.

To make Folder dynamic so that it can get values from variable, Create a variable;

Click on Expressions under Foreach Loop Editor

This setting will take precedence to the folder value in Folder text box. Optionally you can set the FileSpec to a user variable pointing to the files (which will take precedence to the Files in Files text box.

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'

SSIS Script component – Get Blob Data column

Recently I have to move varbinary(max) data from one database to another database using script component.

When dealing with varbinary(max), there are two scenarios:

  • the length of the data is moderate
  • the length of the data is big

GetBytes() is intended for the this scenario, when we are using CommandBehaviour.SequentialAccess to ensure that we are streaming the data, not buffering it. In particular, in this usage we would usually be writing (for example) in a stream, in a loop. For example:

// moderately sized buffer; 8040 is a SQL Server page, note
byte[] buffer = new byte[8040]; 
long offset = 0;
int read;
while((read = reader.GetBytes(col, offset, buffer, 0, buffer.Length)) > 0) {
    offset += read;
    destination.Write(buffer, 0, read); // push downstream
}

However! If we are using moderately sized data, then use this code:

byte[] data = (byte[])reader[col];

Obviously the output column data type would be “image [DT_IMAGE]” in script component.

Finally to make it part of output;

OutputBuffer.FileData.AddBlobData((byte[])sqlReader["FileData"]);

Sources

https://stackoverflow.com/questions/11135245/what-length-parameter-should-i-pass-to-sqldatareader-getbytes