SQLGear

We have moved to cloud to leverage the benefits of Agile infrastructure​. We can scale our infrastructure​ based on current load they are serving. It gives us ability to meet the hardware need during peak hours and also to save on cost during off-peak hours. But often we do not know in advance when ​​​​will our resource be overwhelmed by user requests. Take the example of a database in cloud. We can scale it, but scaling during a business hour will disconnect the users, at least for a fraction of a moment. Assume our database was already on high usage, which automatically triggered the scale activity, means many users are connected and all of their connections are impacted due to scale up (or down). Some users might retry, but some might not and contact the support to look into the issue. We do not want to give a bad user experience to our customers, hence avoiding scaling activities during business hours. But this does not allow us to fully utilize the scaling capabilities of cloud ...

Recently I started facing following error while trying to connect to one of my Windows server using RDP. An authentication error has occurred. The function requested is not supported. This was because of the recent patch related to security "Security Update for Microsoft Windows (KB4093112)" installed on the client but related equivalent patch not installed on server. The server was not patched with updates but the client was and hence the issue. So I uninstalled the patch from the client, then when tried to RDP the server and I was able to. I then patched the server with necessary updates and later applied the updates the client as well.

Hardware requirements: RaspberryPi SD Card (Usually included when you buy RaspberryPi) SD Card Reader (Required if your PC does not have one) WiFi Router to get your RaspberryPi connected to PC (optionally with SD Card Reader) Software requirements: Raspbian SD Card Formatter Win32DiskImager Angry IP Scanner PuTTY VNC Viewer Steps to setup RaspberryPi in headless mode: Format SD Card using SD Card Formatter Copy the OS iso image to SD card using Win32DiskImager Create below files on root folder in SD Card Blank file named "ssh" without any extension in the root folder of SD card "wpa_supplicant.conf" with following lines of code in the root folder of SD card: Make sure to replace the country, ssid and psk values correctly in the above file. Remove the card and insert it into RaspberryPi and boot it up. Run Angry IP Scanner and scan for ip range for ip addresses of WiFi. If your router IP is 192.168.0.1 then scan for the range 192.168.0.0 to 192.168.0...

While creating the build of a SQL server database project you could face following error: Error Deploy72002: Unable to connect to master or target server 'DatabaseName'. You must have a user with the same password in master or target server 'DatabaseName'. This error could be caused by the user account used in publish profile with could have sysadmin privileges. Since sysadmin users on server are mapped with user "dbo" in database hence this error. You can create or use another user with db_owner permission on the database. This could resolve this issue.

If your Azure database is experiencing deadlocks or below error Transaction (Process ID) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction then you can see them by executing following query in the master database:

If while building a database project face this error: Severity Code Description Project File Line Suppression State Error SQL71501: Function: [schema].[object] has an unresolved reference to Schema [schema]. DBProject.Database D:\TFS\path\DBProject.Database\schema\Functions\schema.object.sql Then this error might be because you have object definition referring to the schema under which they are supposed to be created but there is not script to create that schema. Suppose your schema name is empschema then you should add file smpschema.sql to the project with following code: CREATE SCHEMA empschema AUTHORIZATION dbo;

I often see developers assigning values to variables from the same table but different rows based on the row ID. The values are mostly from the same column. What I see is the table is queries multiple times and could impact performance. Below is the sample query and its execution plan. DECLARE @Method1 nvarchar(50); DECLARE @Method2 nvarchar(50); SET @Method1=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=1); SET @Method2=(SELECT DeliveryMethodName FROM [Application].[DeliveryMethods] WHERE DeliveryMethodID=2); Instead, we can do it as in below query: This can also be used while fetching values from an XML column or variable based on a node.

Mostly we write dynamic queries when we try to write the stored procedure which filters data from the column selected by the user or when the user decides on which column to sort the data or when criteria to filter data are multiple as like or equals or does not equal. Below query will give an idea on how to achieve this without writing a dynamic query.

What does this error mean: Error SQL72014: .Net SqlClient Data Provider: Msg 102, Level 15, State 1, Line 10 Incorrect syntax near 'GENERATED'. CREATE TABLE [dbo].[TableName] (....... On your SQL Azure database from where the export is taken, the table for which the error is shown has system versioning turned on. Following is the code to turn off the system versioning on the table: In case if you want to drop the columns associated with versioning you may run following code to first drop the constraint on both the columns and then drop the columns: ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [DF_SysEnd]; ALTER TABLE [dbo].[TableName] DROP CONSTRAINT [DF_SysStart]; ALTER TABLE [dbo].[TableName] DROP COLUMN [SysStartTime]; ALTER TABLE [dbo].[TableName] DROP COLUMN [SysEndTime]; Note: The constraint names and column names could be different in your case.

To know how many transactions are occuring on a Server instance following is the script: Make sure you run them multiple times as each time the result will be different based on the load running during the time you capture it.

So your blog has structured data and now want to make sure that the web crawlers should also understand that you have placed structured data on your blog. The best way to make sure that is to test your blog URL using  https://search.google.com/structured-data/testing-tool . This is not a validator of the tags in your blog but a tool, using which Google makes sure it can read structured data from the page and if required can appear as a  rich card  in a search result. If you have a blog on Blogger and have validated it using the Google structured data testing tool, there are chances that you might have seen below error: The property postId is not recognised by Google for an object of type BlogPosting. Below are the steps to how to remove postId related error appearing during Structured data testing. Go to  Blogger.com Select the Blog in which you want to make the change. Click "Template" in the left menu. Click on "Edit HTML" Once the HTML editor appears...

When working with Azure you may require copying a file to and fro between Azure storage. Now when the Azure Price are reduced , you too might want to move your files to Azure. Copying a file to Azure is easy. You will need to have Az Copy   installed. Once you have installed it you can use the command prompt to copy files to Azure. You must execute AzCopy executable file from the directory in which it is installed. Below is the code to copy "sample.txt" from the local machine to Azure Storage. The code required access key of the storage the file is copied to or from. I use the above code in a batch file so I have mentioned the code to move to the "C:\Program Files (x86)\Microsoft SDKs\Azure\AzCopy" folder because by default the Az Copy tool is installed in that folder. The code also requires access key of the storage where the file is copied to or from. This access key can be found in Azure. Below screenshot shows you how to get the storage access key in Azure po...

Here is the sample HTML file with javascript to store data in HTML local storage. You can run it here. Click the below button to add the lines for text1, text2 and text3 in local storage. Add This is text1. Sample text2. Text3 example. If you are using Google Chrome below are the steps to check if the data was stored. Click Menu > More Tools > Developer Tools (Ctrl + Shift + I) Click "Application" In the left pane under Storage navigate to Local Storage > http://SQLGear.blogspot.in In the right pane, you could find the data you just inserted against the key "List".

Below is the code of a simple HTML page used to fetch JSON data. I am using https://jsonplaceholder.typicode.com/ to fetch JSON data from. You can test it here.

Suppose you have the following HTML Code and you want to loop through each <li> tag under the top <ul> or <ol> tag: <ul id=" idOfTheListTag "> <li><ul> <li><input checked="1" type="checkbox" /></li> <li>A1</li> <li>B1</li> <li>C1</li> <li>D1</li> </ul> </li> <li><ul> <li><input checked="1" type="checkbox" /></li> <li>A2</li> <li>B2</li> <li>C2</li> <li>D2</li> </ul> </li> <li><ul id="menu"> <li><input checked="1" type="checkbox" /></li> <li>A3</li> <li>B3</li> <li>C3</li> <li>D3</li> </ul> </li> </ul> To loop through all the <li> tags below is the javascript function:

You can find many articles on the internet for moving to SQL Database but very less on moving to in-premise from SQL Azure . SQL Azure is a good product but sometimes you will need your database to move or copied over to in-premise SQL Server for a task. SQL Azure constantly updating itself and if your in-premise SQL Server in not of the latest version you might face error while trying to import it. This happens when developers are developing on SQL Azure so the code they had written was not tested for compatibility with in-premise. If you start importing the .bacpac file you might face only one error at a time. You fix that in SQL Azure and again start and export and then try importing it and again you face an error with another object. You will face only one error at a time even the database has more than one compatibility issues. So how to know how many such issues are there in SQL Azure database? To find that you have to do this check. Even before you start to export your...

Recently while changing the pricing tier of one of the Azure databases I faced following error: Operation name: Update SQL database Error code: 45197 Message: A system maintenance operation is in progress on server 'servername' and database 'databasename.' Please wait a few minutes before trying again. I tried it again and again but was facing the same message. Ultimately it was resolved by the Microsoft Product Group team efficiently and in due time. There could be different reasons for the issue to occur but this was caused because of following reason as per Microsoft: When a database’s performance tier is changed, the database is moved from a container with the old performance tier to a container with the new performance tier.  The old container is then cleaned up at the end of the process.  In this case, cleaning up the old container was not making progress. It was mitigated by the MS support team by  manually forcing the cleanup to occur. As of no...

So your blog has structured data and now want to make sure that the web crawlers should also understand that you have placed structured data on your blog. The best way to make sure that is to test your blog URL using  https://search.google.com/structured-data/testing-tool . This is not a validator of the tags in your blog but a tool, using which Google makes sure it can read structured data from the page and if required can appear as a  rich card  in a search result. If you have a blog on Blogger and have validated it using the Google structured data testing tool, there are chances that you might have seen below error: A value for the headline field is required. Below are the steps to how to remove headline  related error appearing during Structured data testing. Go to  Blogger.com Select the Blog in which you want to make the change. Click "Template" in the left menu. Click on "Edit HTML" Once the HTML editor appears, click inside the ...

So your blog has structured data and now want to make sure that the web crawlers should also understand that you have placed structured data on your blog. The best way to make sure that is to test your blog URL using  https://search.google.com/structured-data/testing-tool . This is not a validator of the tags in your blog but a tool, using which Google makes sure it can read structured data from the page and if required can appear as a  rich card  in a search result. If you have a blog on Blogger and have validated it using the Google structured data testing tool, there are chances that you might have seen below error: A value for the url field is required. Below are the steps to how to remove url  related error appearing during Structured data testing. Go to  Blogger.com Select the Blog in which you want to make the change. Click "Template" in the left menu. Click on "Edit HTML" Once the HTML editor appears, click inside the editor and press Ctr...

So your blog has structured data and now want to make sure that the web crawlers should also understand that you have placed structured data on your blog. The best way to make sure that is to test your blog URL using  https://search.google.com/structured-data/testing-tool . This is not a validator of the tags in your blog but a tool, using which Google makes sure it can read structured data from the page and if required can appear as a  rich card  in a search result. If you have a blog on Blogger and have validated it using the Google structured data testing tool, there are chances that you might have seen below error: A value for the image field is required. Below are the steps to how to remove image  related error appearing during Structured data testing. Go to  Blogger.com Select the Blog in which you want to make the change. Click "Template" in the left menu. Click on "Edit HTML" Once the HTML editor appears, click inside the editor and pre...