Tag: SQL server

If application doesn’t run and complains about permissions e.g. Execute permission, we need to check schema permissions; One of the reason, this happens when we restore database.

Suppose our user name is FMUser and dataase name is FMStoreDev. Under Login Properties of FM User – Security;

UNDER DATABASE -> USER -> SECURITY -> FMUser, CHECK THIS (in case SPROC does not run);

On Securables tab, click Search;

Select first option and click ok;

Click on object types and select schema;

Click on Browse and select your schema;

Select FM schema and grant execute permission to FMUser;

Since this is schema bound, go to securityàschema. Schema name should be your custom schema and owner should be dbo;

Click on Permissions tab. Make sure execute permission is selected for FM user;

These changes will help to solve database execute permission problem.

This is an alternative of remapping the users (to restore permission) after restoring DB;

USE FMStoreDev;  
GO  
EXEC sp_change_users_login 'Update_One', 'FMUser', 'FMUser';  
GO  

Here is a short example. Let’s say we have this sample JSON output. We would like to split it on “}” characters;

DECLARE @json NVARCHAR(MAX) = '[{
  "text": "Books",
  "nodes": [{
	"text": "Programming Books",
	"rackNumber": "1",
	"moreInfo": [{
			"text": "C# book",
			"price": "$49.99"
		  },
		  {
			"text": "React book",
			"price": "$40.99"
		  }
		]},
		{
	"text": "Database Books",
	"rackNumber": "2",
	"moreInfo": [{
			"text": "SQL Server 2022",
			"price": "$52.99"
  		  },
		  {
			"text": "Maria database",
			"price": "$20.99"
		  }
		]}
	]
}]
'

SQL Server has some handy functions, STRING_Split, to split and to re-create, STRING_AGG. I am going to demo it here;

----split strings to make changes
IF OBJECT_ID('tempdb..#ADDF') IS NOT NULL DROP TABLE #ADDF
;WITH CTE AS
 (
	SELECT value  from STRING_Split(@json, '}')
 )
 SELECT * INTO #ADDF FROM CTE;
 //show me the JSON. The string should be split in two parts
 SELECT * FROM #ADDF

 //TODO..do whatever you want to do with the string JSON array

--return modified string. You should see a single line JSON output here 
SELECT STRING_AGG(value, '}') AS jsonBack
FROM #ADDF;

Happy coding.

SQL Server has a pretty good support for JSON. If there is a special character in JSON, then it will throw this error;

JSON path is not properly formatted. Unexpected character ‘#’ is found at position 2

To produce this, here is an example

DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'

-- Print the current JSON
PRINT @data

-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @data=
 JSON_MODIFY(
  JSON_MODIFY(@data,'$.Contractor', JSON_VALUE(@data,'$.#Name')),
  '$.#Name',
  NULL
 )
-- Print the new JSON
PRINT @data

I didn’t find any built-in support to handle these characters. The workaround I found is to simply replace special characters. Here is how;

DECLARE @data NVARCHAR(50)='{"#Name":"Shahzad"}'
DECLARE @cleanData NVARCHAR(50) = REPLACE(@data, '#Name', 'Name');
-- Print the current JSON
PRINT @data

-- Rename the key (by copying the value to a new key, then deleting the old one)
SET @cleanData=
 JSON_MODIFY(
  JSON_MODIFY(@cleanData,'$.Contractor', JSON_VALUE(@cleanData,'$.Name')),
  '$.Name',
  NULL
 )
-- Print the new JSON
PRINT @cleanData

And the output is;

{“#Name”:”Shahzad”}
{“Contractor”:”Shahzad”}

References

https://learn.microsoft.com/en-us/sql/t-sql/functions/json-modify-transact-sql?source=recommendations&view=sql-server-ver16

Database Guides

Lets take a look at two queries using CURSORS, the first one will use the FORWARD_ONLY type cursor, and the second will use the FAST_FORWARD type cursor. These two types sound very similar, but perform quite differently.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;
 
SET @PeoplePhoneCursor = CURSOR FORWARD_ONLY FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;
 
OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT ISNULL(@firstName, '') + ' ' +
           ISNULL(@middleName, '') + ' ' +
           ISNULL(@lastName, '')  +
           ' Phone: ' + ISNULL(@phone, '') ;
     FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

Now for the FAST_FORWARD CURSOR example. Notice only one line has changed, that’s the line that says “SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR”.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;
 
-- HERE IS WHERE WE SET THE CURSOR TO BE FAST_FORWARD
SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;
 
OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
     PRINT ISNULL(@firstName, '') + ' ' +
           ISNULL(@middleName, '') + ' ' +
           ISNULL(@lastName, '')  +
           ' Phone: ' + ISNULL(@phone, '') ;
     FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

The FORWARD_ONLY CURSOR takes 4 times the time as the FAST FORWARD CURSOR, and the number continues to widen as the number of times the cursor loops is executed.

FAST FORWARD CURSORS are usually the fastest option with SQL Server. There may be cases where another option may work better, but the FAST FORWARD CURSOR is a good place to start if you must use a CURSOR.

The best practice is to avoid cursor but sometime they are inevitable. The alternatives could be temp tables, while loops etc.

Recently I had a problem where i need to search for;

1. Foo
2. foo

SQL Server default behavior is to do case insensitive search. so if run a query like this;

IF OBJECT_ID('tempdb..#FooTable') IS NOT NULL DROP TABLE #FooTable
SELECT x.* 
INTO #FooTable
FROM
(
	SELECT 1 AS Id, 'Foo' AS UserName
	UNION ALL
	SELECT 2 As Id, 'foo' AS UserName
) x

I will get two records back.

To do a case sensitive search, do this;

SELECT UserName
FROM #FooTable
WHERE 1=1
AND UserName = 'foo' COLLATE SQL_Latin1_General_CP1_CS_AS

Result would be;

For join operations, let’s create another table;

--Now add a second table, say salary
IF OBJECT_ID('tempdb..#FooSalaryTable') IS NOT NULL DROP TABLE #FooSalaryTable
SELECT x.* 
INTO #FooSalaryTable
FROM
(
	SELECT 1 AS Id, 'Foo' AS FooId, 4000 AS UserSalary
	UNION ALL
	SELECT 2 As Id, 'foo' As FoodId, 6000 AS UserSalary
) x

SELECT * FROM #FooSalaryTable

This is join query;

--example with join
SELECT 
	x.UserName, y.UserSalary
FROM #FooTable x
JOIN #FooSalaryTable y on x.UserName = y.FooId COLLATE SQL_Latin1_General_CP1_CS_AS
WHERE 1=1
AND x.UserName = 'foo' COLLATE SQL_Latin1_General_CP1_CS_AS

and results;

For more info, follow these articles;

https://stackoverflow.com/questions/1831105/how-to-do-a-case-sensitive-search-in-where-clause-im-using-sql-server

https://www.mssqltips.com/sqlservertip/1032/case-sensitive-search-on-a-case-insensitive-sql-server/

https://stackoverflow.com/questions/17172175/add-column-to-temp-table-invalid-column-name-decimal