Coding - Appuals Tech from the Experts Fri, 01 Sep 2023 03:38:27 +0000 en-US hourly 1 https://wordpress.org/?v=6.4.4 Fix: “Copilot is not available in your location” GitHub Issue https://appuals.com/copilot-is-not-available-issue/?utm_source=rss&utm_medium=rss&utm_campaign=copilot-is-not-available-issue https://appuals.com/copilot-is-not-available-issue/#respond Fri, 01 Sep 2023 03:38:27 +0000 https://appuals.com/?p=360478 Is the copilot not available when using GitHub Copilot in Visual Studio Code? In some cases, the on-screen message states that GitHub Copilot is unavailable in your location, while in others, it indicates unavailability in your region. This error prevents them from using GitHub Copilot at all. This issue transpires in both Visual Studio Code …

The post Fix: “Copilot is not available in your location” GitHub Issue appeared first on Appuals.

]]>
Is the copilot not available when using GitHub Copilot in Visual Studio Code? In some cases, the on-screen message states that GitHub Copilot is unavailable in your location, while in others, it indicates unavailability in your region. This error prevents them from using GitHub Copilot at all. This issue transpires in both Visual Studio Code and Visual Studio.

What is GitHub Copilot?

GitHub Copilot is an AI pair programmer that helps users write code a lot faster and easier, making suggestions with individual lines and whole functions instantly. This AI tool is an extension for Visual Studio Code, Visual Studio, and more, and can provide suggestions for numerous languages and frameworks like Python, JavaScript, TypeScript, C#, C++, and more.

What causes this issue?

In most cases, this issue appears after an update has been installed. That means that the update might’ve come with a few errors. It might also be caused by the fact that you were signed out of your account, or your subscription has expired.

The cause might also be a file problem, which affects the program and doesn’t let it run properly. Files can become corrupted after using the program for some time, or after installing an update.

This issue can occur due to Visual Studio Code, which, in some situations, requires administrator privileges to function properly. VSCode might also have some files that are affecting you.

How can I fix this issue?

To fix this issue, there are a few things that you can do that are related to the program. These methods are easy to follow and will fix your issue. Here is how you can fix the Copilot is Not Available issue:

1. Make sure that you are signed in and subscribed

You need to start by making sure that you are signed in. This issue could appear when you’ve been logged out of your account. This can happen when you haven’t used the tool for some time and the login has expired, or if you haven’t saved your login information.

To verify if you are signed into your GitHub Copilot account, click on your profile picture located in the bottom left side of your Visual Studio Code screen. You will be able to see if you are logged in or not. If not, click on the Sign in button from the upper side and enter your credentials.

Looking if you are still logged in or not
Looking if you are still logged in or not

Besides this, you should also verify if your subscription is still active and if you haven’t missed a payment. This error could appear if the subscription has expired. Go to the Settings and access the Billing section. You will be able to see the current status of your account.

If you are logged in and the subscription is active but you still can’t use the GitHub Copilot, the cause for this issue is something else.

2. Reinstall an older version of GitHub Copilot

You should also try to install an older version of GitHub Copilot to see if that will fix your issue. This issue might’ve appeared after installing the latest update, meaning that the update came with some issues that are affecting you.

You can simply do this from the extension tab. Access GitHub Copilot and install the older version, then restart the app to ensure that the changes have been applied.

Here is how you can do this:

  1. Start by accessing the GitHub Copilot first page.
  2. Now you will be able to see the Uninstall button. Click on the error next to it to extend the drop-down menu.
  3. Click on the Install Another Version button.
    Installing an older version of GitHub Copilot
    Installing an older version of GitHub Copilot
  4. Choose the version that you’ve used before the current update and install it.
  5. Once the version has been installed, you need to restart the Visual Studio Code. Click on the Reload Required button and VSCode will restart automatically.
    Restarting Visual Studio Code
    Restarting Visual Studio Code
  6. Once VSCode has been reloaded completely, test it to see if the error has been fixed.

3. Update Visual Studio Code and reload it

Another factor that might cause your GitHub Copilot to not work is the Visual Studio Code being outdated. When an update for VSCode appears, you should install it as fast as you can to avoid issues like this one.

It was reported by many users that this issue is most likely caused by the fact that the Visual Studio Code is outdated. If that is the case, many other errors could appear because the extension is optimized for the latest version of VSCode.

All you have to do is to open the Visual Studio Code and check for updates. Install them and restart the application.

Here are the steps on how to do this:

  1. Start by opening the Visual Studio Code.
  2. Click on the Settings(gear icon) button located at the bottom left side.
  3. Now you need to click on the Check for Updates button. The scan will let you know if there are updates available or not.
    Checking for updates
    Checking for updates
  4. If there are updates available, wait until the installation is complete.
  5. After the update is installed, the last thing you need to do is to restart the Visual Studio Code.
  6. When it restarts completely, test it to see if the GitHub Copilot is still unavailable.

4. Enable Copilot Globally (Visual Studio only)

You should also ensure that the GitHub Copilot is enabled from the Visual Studio settings. This issue can appear when the Copilot is not enabled from the settings.

This can happen when you’ve manually disabled this setting, or when some issues have done it. All you need to do is go to Visual Studio and access the Settings of your GitHub Copilot, where you can enable it.

Note: Before going to the guide, you must know that these steps are only for Visual Studio users. If you are using Visual Studio Code, go to the last method.

Here is a guide that will show you how to do this:

  1. While you are inside Visual Studio, click on the Tools button from the upper side of the screen.
  2. Now open the Options.
    Opening the Visual Studio options
    Opening the Visual Studio options
  3. Look inside the left-sided list for GitHub. When you see it, click on it and access the Copilot options.
  4. Under the General section, you will be able to see the Enable Globally setting. Make sure that it is set to True for the GitHub Copilot to be enabled.
    Enabling the GitHub Copilot globally
    Enabling the GitHub Copilot globally
  5. Click on OK to save the changes and test Visual Studio to see if the Copilot is still unavailable.

5. Open Visual Studio Code as an administrator

The GitHub Copilot is an unavailable issue might be also caused by the lack of administrator privileges. To be able to work properly, you should give permission to Visual Studio Code to use all the files that it needs. This way it will be able to use the features and extensions that you want.

Some extensions in VS Code, like GitHub Copilot, require elevated permissions to access certain system resources or perform specific tasks. Running VS Code as an administrator ensures that you won’t encounter permission issues.

To simply run the program as an administrator, all you need to do is right-click on the Visual Studio Code icon and click on Run as administrator. You will then be prompted by the User Account Control. Confirm that you give permission to it by clicking on Yes.

Running Visual Studio Code as an administrator
Running Visual Studio Code as an administrator

If you see that you are now able to use the GitHub Copilot properly, you can set the program to always run as an administrator without repeating this process every time.

You need to right-click on the VSCode icon and click on Properties. Go to the Compatibility section and look inside the Settings tab. Click on the box associated with Run this program as an administrator. Click on Apply to save the changes and OK to close the Properties.

Running VSCode as an administrator
Running VSCode as an administrator

Now test again to see if the issue is not appearing anymore.

The post Fix: “Copilot is not available in your location” GitHub Issue appeared first on Appuals.

]]>
https://appuals.com/copilot-is-not-available-issue/feed/ 0
How to Solve Python Indexerror: List Index Out of Range? https://appuals.com/python-indexerror-list-index-out-of-range/?utm_source=rss&utm_medium=rss&utm_campaign=python-indexerror-list-index-out-of-range https://appuals.com/python-indexerror-list-index-out-of-range/#respond Fri, 28 Apr 2023 10:05:33 +0000 https://appuals.com/?p=330050 IndexError: ‘list index out of range’ is a Python error that occurs when attempting to access a list item outside the range of the list. In Python, list indexes are used to access or perform actions on list items. For example, you can print them or iterate through them using loops. In simple terms, if …

The post How to Solve Python Indexerror: List Index Out of Range? appeared first on Appuals.

]]>
IndexError: ‘list index out of range’ is a Python error that occurs when attempting to access a list item outside the range of the list. In Python, list indexes are used to access or perform actions on list items. For example, you can print them or iterate through them using loops.

Indexerror: List Index Out of Range
Indexerror: List Index Out of Range

In simple terms, if a list has 5 items and you try to use the 10th item in a list in Python, it will return an IndexError: list index out of range. Usually, these errors are easy to troubleshoot but require a bit of code debugging.

Creating lists in Python.

In Python, a list is created by

  1. Giving a name to the list
  2. A space followed by the assignment operator i.e., =.
  3. A space followed by the opening square bracket.
  4. Add the first list item in double quotes (not inverted commas).
  5. A comma and space should follow the second list item in double quotes. Then, you can continue with other list items. At the end of the list, enter the closing square bracket.

For example, to create and print a list of languages in Python:

languages = ["Python", "Java", "JavaScript"]
print(languages)
Create and Print a Languages List in Python
Create and Print a Languages List in Python

The above code will create a list with the name of languages and contains three entries. First Python, second Java, and third JavaScript.

Check the Length of a Python List

To check the length of the languages list, let us use the length function. To do so, enter the following code:

languages = ["Python", "Java", "JavaScript"]

languages_length = len(languages)

print(languages_length)

This will return a value of 3.

Use the Length Command to Find the Length of the List and Print it
Use the Length Command to Find the Length of the List and Print it

Indexing in Python Lists

In a Python list, each item can be accessed by its index number. Python, like other modern-day languages, is a zero-indexed language i.e., it will start its count from zero (not 1). So, a list with three items will have an index of 0, 1, and 2.

Let us continue with our languages example. To access all its items, use the following code:

languages = ["Python", "Java", "JavaScript"]
 
print(languages[0]) # This will return Python

print(languages[1]) # This will return Java 

print(languages[2]) # # This will return JavaScript
Printing the Value in the Language List Using Their Index Number
Printing the Value in the Language List Using Their Index Number

Index Range in a Python List

So, it can be easily said that the index range of a list in Python will be 0 to n-1, where n is the total items in the list. In the above example, the index range will be 0 to 2 (3-1).

Negative Indexing in a Python List

You can also use negative indexing to access a Python list item. The last item will have a -1 index, the second last will have a -2 index, and so on. For example

languages = ["Python", "Java", "JavaScript"]

languages[-1] # this will return the last item which is JavaScript

languages[-2] # this will return the 2nd last item which is Java

languages[-3] # this will return the third last item which is Python
Print the Values of the Languages List Using Their Negative Index
Print the Values of the Languages List Using Their Negative Index

Index Range in Negative Indexing

In the negative indexing of a Python list, the index range will be -1 to -n, where n is the total values present in the list. In our languages list, the negative index range will be -1 to -3 as the length of our list is 3.

Indexerror: List Index Out of Range

Usually, new entrants to the Python world face the Indexerror when, in their code, they start indexing at 1, not zero. Let us clear this by an example. Look at the languages list below:

languages = ['Python', 'Java', 'JavaScript']

It has 3 languages, first Python, second Java, and third JavaScript. Let us try to print the 3rd language in the list:

print(languages[3])
IndexError When Printing the Value of Index 3 of the Languages List
IndexError When Printing the Value of Index 3 of the Languages List

But it will throw the Indexerror. But why? Here, the index range of our list is 3 and we are trying to print the 3rd language in the list. However, in our code, we are asking Python to print the 4th language which is not present and out of the range of the list index. How?

Index 0 = Python

Index 1 = Java

Index 2 = JavaScript

There is no 4th value so Python throws Indexerror. Here, the proper code will be

languages = ['Python', 'Java', 'JavaScript']

print(languages[2])
Print the Index Value of 2 of the Languages List
Print the Index Value of 2 of the Languages List

IndexError in Negative Indexing

If we try to print the -4th language in our languages list, we will get the IndexError.

languages = ['Python', 'Java', 'JavaScript']

print(languages[-4])
8. IndexError When Printing -4th Index Value of the Languages List
IndexError When Printing -4th Index Value of the Languages List

This will throw index error because our index range is -1 to -3, so, there is no -4th value. Here, the proper code would be

languages = ['Python', 'Java', 'JavaScript']

print(languages[-3]) # which will show Python.
Printing the Value of -3rd Index of the Languages List
Printing the Value of -3rd Index of the Languages List

IndexError in Python Loops

Loops in Python, like any other programming language, keep on running until a certain condition is met. A loop in Python will throw an IndexError if the condition specifying the loop involves a list but that condition is invalid as per the list.

IndexError in a While Loop

Look at the code below for a While Loop in Python:

languages = ['Python', 'Java', 'JavaScript']

i = 0

while i <= len(languages):

    print(languages[i])

    i += 1
IndexError in a While Loop of Python
IndexError in a While Loop of Python

Here, we are asking Python to make a languages list and then declare a variable i with a starting value of 0. Then we announced our while loop and ask the code to be executed till i variable is less than or equal to the length of the list i.e., 3. But this throws an IndexError. Why?

Our list here has an index range of 0 to 2 (3-1).

Index 0 = Python

Index 1 = Java

Index 2 = JavaScript

But there is nothing in Index 3. But our code is asking the Python compiler to keep on running till the variable i equals 3 which is not possible, hence the out-of-range error.

So, let’s modify our code to run the while loop till the range equals 2, not 3.

languages = ['Python', 'Java', 'JavaScript']

i = 0

while i < len(languages):

    print(languages[i])

    i += 1

So, we removed the equal part and used the condition to run till the variable i is less than 3. This loop will stop after 2 and thus the loop will execute without any issue.

Setting the While Loop to Run Till the Variable i is Less Than the Lengty of the Languages List
Setting the While Loop to Run Till the Variable i is Less Than the Lengty of the Languages List

IndexError in the Range Function of a For Loop

The range() function in a Python for loop takes in one integer number for which the counting will stop. The counting in a Range function starts at position 0, then increments by 1 after each iteration, and the number where counting will stop but this number is not included.

The textbook form of a Range() function syntax will be:

range(start, stop, step).

Start: is an optional integer value that indicates the beginning of a series. Its default value is zero if it is left blank.

Stop: is a mandatory integer value that indicates the point at which the execution of the range function should end. This values itself is not included in the execution.

Step: is an optional value that is used to increase the increment after each iteration. If left blank, its default value is one.

But generally, it is used as:

range(stop)

For example, range(4) indicates that the For loop will start from 0 and end when the count reaches 4 i.e., the loop will stop after 3.

Let’s continue with our example of languages, enter the following code:

languages = ["python", "Java", "JavaScript"]

for name in range(4):

print(languages[name])
IndexError in the Range Function of a For Loop
IndexError in the Range Function of a For Loop

This will again throw an indexerror because the range function will keep on running till the index range of 3 is executed and the count reaches 4. Here, we are asking the interpreter to print the values at index 0, 1, 2, and 3. But the index range is 3.

Index 0 = Python

Index 1 = Java

Index 2 = JavaScript

So, nothing to execute at Index 3, hence the error. We can correct it by amending our code as:

languages = ["python", "Java", "JavaScript"]

for name in range(3):

print(languages[name])
Use the Range Function to Print All the Values in the Range
Use the Range Function to Print All the Values in the Range

Another way to correct this is by using the length function inside the range function.

languages = ["python", "Java", "JavaScript"]

for name in range(len(languages)):

print(languages[name])
Use the Length Command in the Range Function of a For Loop
Use the Length Command in the Range Function of a For Loop

Here, we have instructed the interpreter to stop the count when the length of the languages list (i.e., 3) is reached but not included. So, the interpreter stops after executing the index range of 2 which is a valid entry.

But if you pass a +1 argument to the length function, then it will again cause an indexerror. Look at the code below:

languages = ["python", "Java", "JavaScript"]

for name in range(len(languages)=1):

print(languages[name])
Adding a Plus One to the Length of the List Causes IndexError
Adding a Plus One to the Length of the List Causes IndexError

Here, again the count number of the range function reaches four (length of languages list 3+1=4) which is not present, hence the error.

IndexError Due to a Modified List When Iterated

Until now, the list was kept static but if your code removes or adds elements to a list when iterated over it, then that could cause indexerror. Let’s clarify the concept with the following example:

J = [1, 2, 4, 0, 0, 1]

for i in range(0, len(J)):

       if J[i]==0:

           J.pop(i)

Here, J.pop(i) removes zeros from the list and decreases the list size, and in the next iteration, the index range of the list decreases to 4 but the code is looking for len(J)-1 = 6-1 = 5 which is not present, hence the error.

IndexError in a Modified List After Iteration
IndexError in a Modified List After Iteration

You can fix this by using a list compression and the following code will do the trick.

J = [1,2,3,0,0,1] 

J = [x for x in J if x != 0]

print(J)
Use the List Compression to Clear the IndexError
Use the List Compression to Clear the IndexError

The second list compresses the original list after removing zeros. Another form of this code could be:

J = [1, 2, 4, 0, 0, 1]

for i in range(len(J)-1, -1, -1):

if J[i] == 0:

J.pop(i) 

print(J)
Decrease the Loop Range After Every Iteration to Solve the IndexError
Decrease the Loop Range After Every Iteration to Solve the IndexError

Here is a second example:

colors = ['blue', 'red', 'orange', 'blue', 'green']

for i in range(len(colors)):

    if colors[i] == 'blue':

        del colors[i]

print(colors)
IndexError in the Colors List When Removing Blue
IndexError in the Colors List When Removing Blue

We are trying to remove the blue from the colors list and then print the list but it is throwing an indexerror. This is occurring because the del command is changing the length of the list while in the loop.

At the start of the loop, the length of the colors list is 5, so the range() function generates the index of the list as [0, 1, 2, 3, 4]. With each iteration of the code, the length of the list is decreasing but the index generated by the loop remains 5, and eventually, we will run into an indexerror.

This can be solved by using list compression.

colors = ['blue', 'red', 'orange', 'blue', 'green']

colors = [color for color in colors if color != 'blue']

print(colors)
Use the List Compression to Clear the IndexError
Use the List Compression to Clear the IndexError

Another form of this code could be:

colors = ['blue', 'red', 'orange', 'blue', 'green']

for i in range(len(colors)-1, -1, -1):

if colors[i] == 'blue':

del colors[i] 

print(colors)
Decrease the Index Range With Each Iteration to Clear the IndexError on the List
Decrease the Index Range With Each Iteration to Clear the IndexError on the List

Empty Lists

There can be cases where a list might be empty or got empty after each iteration of the code. When these empty lists are called in your code, these will cause indexerror: List Index Out of Range. Look at the code below:

languages = []

print(languages[0])
IndexError Due to An Empty List
IndexError Due to An Empty List

This will throw an indexerror as the list is empty and there is no value at index 0. You can cover this by using IF. The code will be as:

languages = []

if languages == []:

   print('Empty list')

else:

   print(languages[0])
Use the IF Statement to Check if the List is Empty
Use the IF Statement to Check if the List is Empty

IndexError in a Python String

This error can also occur with Python strings. Let us get it cleared by using an example.

s = 'Python'

print(s[6])
IndexError in a Python String
IndexError in a Python String

This will throw an indexerror. In this example, we are trying to print the 6th character in the string but instructing the interpreter to print the 7th character which is not present. How? Our 6th character will be indexed as 5 (count from 0 to 5). So, the code should be

s = 'Python'

print(s[5])
Print the 5th Character of a String
Print the 5th Character of a String

IndexError in a Python Tuple

An IndexError can also occur in a Python tuple. Let us look at the following example:

s = ('Asia', 'Europe')

print(s[2])
IndexError in a Python Tuple
IndexError in a Python Tuple

This will throw an indexerror as we want to call a second entry but eventually call a non-present 3rd entry. The correct code would be:

s = ('Asia', 'Europe')

print(s[1])
Print the 2nd Value of a Python Tuple
Print the 2nd Value of a Python Tuple

Check for a Library or IDE Bug

If your code is 100% correct but you are still facing an indexerror, then the issue could be a bug in a Python library or IDE. In the recent past, Manim has been reported to throw an indexerror even with its test examples. In such a case, either check your code on another machine or use an online Python compiler for testing.

Causes of IndexError: List Index Out of Range

So, in a nutshell, we can say that an Indexerror: List Index out of Range mainly occurs when:

  1. You try to access an index that is not present in a list.
  2. You are using invalid indexes in your loops.
  3. When you specify a range that is beyond the index range of a list when using the range() function.
  4. A library or IDE bug.

Method to Solve an IndexError

We have discussed the matter in quite detail but these are all sample codes and you have to dig your code to clear the error. The best method is to note down the line where you are facing the error and just before that, add a print command. This way you will easily be able to note down the issue.

So, readers if you are still confused about the IndexError, you are more than welcome in the comments section.

The post How to Solve Python Indexerror: List Index Out of Range? appeared first on Appuals.

]]>
https://appuals.com/python-indexerror-list-index-out-of-range/feed/ 0
Fix: TypeError: $.ajax is not a function While using the ajax function https://appuals.com/typeerror-ajax-is-not-a-function-while-using-the-ajax-function/?utm_source=rss&utm_medium=rss&utm_campaign=typeerror-ajax-is-not-a-function-while-using-the-ajax-function https://appuals.com/typeerror-ajax-is-not-a-function-while-using-the-ajax-function/#respond Sat, 26 Nov 2022 12:48:36 +0000 https://appuals.com/?p=288353 The TypeError: $.ajax is not a function occurs when you use a slim version of jQuery that does not include the ajax function. To solve this error, you will have to use a regular version of jQuery instead of using the slim version. If you face any error that indicates that it is not a …

The post Fix: TypeError: $.ajax is not a function While using the ajax function appeared first on Appuals.

]]>
The TypeError: $.ajax is not a function occurs when you use a slim version of jQuery that does not include the ajax function. To solve this error, you will have to use a regular version of jQuery instead of using the slim version.

TypeError: $.ajax is not a function
TypeError: $.ajax is not a function

If you face any error that indicates that it is not a function, that means the function you are trying to call is not defined in your code. That’s why you keep getting this error. We have given the instruction with the example, so you can follow them to fix this error:

  1. First of all, go to the jQuery website and copy the <script> tag of the minified jQuery version or take it from the below.
    <script src="https://appuals.com/wp-content/litespeed/localres/aHR0cHM6Ly9jb2RlLmpxdWVyeS5jb20vjquery-3.6.1.min.js" integrity="sha256-o88AwQnZB+VDvE9tvIXrMQaPlFFSUTR+nldQm1LuPXQ=" crossorigin="anonymous"></script>
  2. Now replace it with your jQuery script tag.
    Changing src value of jQuery script tag
    Changing the src value of the jQuery script tag
  3. After that, If you have already working on a project getting this error, then skip these steps and try to check if the error is fixed.
  4. Create a button with a named Send request and then create a script tag with the src of index.js.
    Creating a button and Script tag
    Creating a button and Script tag
  5. Make a file with the name index.js and paste the following code to make a request.
    $(document).ready(function () {
    $('#btn').click(function () {
    $.ajax({
    url: 'https://api.github.com/users/hadley/orgs',
    dataType: 'json',
    success: function (data) {
    console.log(data);
    },
    });
    });
    });
    Requesting the data through the api using an ajax function
    Requesting the data through the api using an ajax function
  6. Once done, right-click the Html code and select Open with Live Server.
    Opening a file with a live server
    Opening a file with a live server
  7. If you cannot see this option, then install the Live Server extension and open the code on the browser.
    Installing a Live Server extension
    Installing a Live Server extension
  8. Once the browser is opened with the live server, right-click the empty space and select Inspect or press Ctrl + Shift + I together.
    Opening an Inspect Window on the browser
    Opening an Inspect Window on the browser
  9. Go to Console from the top and then click on Send request button to see if it works or not.
    Calling an Api to receive the data
    Calling an Api to receive the data
  10. If it is working, you will see the data in the console, which is an output of the function that you have fetched through the API.

The post Fix: TypeError: $.ajax is not a function While using the ajax function appeared first on Appuals.

]]>
https://appuals.com/typeerror-ajax-is-not-a-function-while-using-the-ajax-function/feed/ 0
How to Fix ‘Error R10 (Boot Timeout)’ on Heroku? https://appuals.com/how-to-fix-error-r10-boot-timeout-on-heroku/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-fix-error-r10-boot-timeout-on-heroku https://appuals.com/how-to-fix-error-r10-boot-timeout-on-heroku/#respond Sun, 13 Oct 2019 13:54:41 +0000 https://appuals.com/?p=140984 Heroku is a cloud platform-based service which provides users with a platform to manage run and develop applications without having to build and maintain infrastructure.  It has been in development since 2007 and now it supports a ton of languages. Quite recently, a lot of reports have been coming in of an “Error R10 (Boot …

The post How to Fix ‘Error R10 (Boot Timeout)’ on Heroku? appeared first on Appuals.

]]>
Heroku is a cloud platform-based service which provides users with a platform to manage run and develop applications without having to build and maintain infrastructure.  It has been in development since 2007 and now it supports a ton of languages. Quite recently, a lot of reports have been coming in of an “Error R10 (Boot timeout) -> Web process failed to bind to $PORT within 60 seconds of launch” Error.

Error R10 (Boot timeout) -> Web process failed to bind to $PORT within 60 seconds of launch

In this article, we will discuss some of the reasons due to which this error is triggered and also provide you with viable solutions to fix it completely. Keep in mind to follow the instructions carefully and accurately to avoid conflict.

What Causes the “Error R10(Boot Timeout)” on Heroku?

After receiving numerous reports from multiple users, we decided to investigate the issue and devised a set of solutions to fix them completely. Also, we looked into the reasons due to which it is triggered and listed them as follows.

  • Timelimit: There is a time limit for the amount of time that the application takes to startup. When this time limit is breached, the error might be triggered. Therefore, the startup of the application must be reduced to a time limit lower than the time limit or to extend the time limit.
  • Invalid Procfile: In some cases, there might be an issue with the Procfile where it might not be configured properly. If the Procfile isn’t configured properly, this error might be triggered.
  • Loading Gems: There might be an increased number of Gems being loaded at the startup of the application. Therefore, you must limit the number of Gems to as low as possible at startup. This can be done without affecting the load up of Gems.

Now that you have a basic understanding of the nature of the problem, we will move on towards the solutions. Make sure to implement these in the specific order in which they are listed to avoid conflict.

Solution 1: Increasing Time Limit

The time limit for the Bootup of the application can be increased. We will be increasing it using a “Heroku Forward” file which also contains the instructions for achieving this higher timelimit. Download this file which contains all the important files that you need to increase the time limit. It also includes a Readme file that contains the instructions for implementing it.

Solution 2: Changing Procfile Settings

In some cases, the issue might be triggered due to an invalid configuration with the “Procfile”. Therefore, in this step, we will be changing a line in the Procfile. For that:

  1. Open the Procfile.
  2. There should be a line in the Procfile similar to the following one.
    web: bundle exec thin start
  3. Change this line to the following one.
    web: bundle exec thin start -p $PORT
  4. Save your changes and check to see if the issue persists.

Solution 3: Trimming Gems at Startup

It is possible that extra Gems are being loaded at the startup of the application. Therefore, in this step, we will be identifying and trimming the extra Gems at startup. For that:

  1. Download this file and read the instructions to identify extra Gems being loaded at startup.
  2. Add the following line to the Gems that are extra and don’t need to be loaded at startup.
    require => false
  3. This will prevent that Gem from being loaded at startup.
  4. Check to see if the issue persists.

The post How to Fix ‘Error R10 (Boot Timeout)’ on Heroku? appeared first on Appuals.

]]>
https://appuals.com/how-to-fix-error-r10-boot-timeout-on-heroku/feed/ 0
Fix: ‘Column is invalid in the select list’ https://appuals.com/how-to-fix-the-error-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggregate-function-or-the-group-by-clause/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-fix-the-error-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggregate-function-or-the-group-by-clause https://appuals.com/how-to-fix-the-error-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggregate-function-or-the-group-by-clause/#comments Thu, 10 Oct 2019 05:20:38 +0000 https://appuals.com/?p=140193 The error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause” mentioned below arises when you execute “GROUP BY” query, and you have included at least one column in the select list that is neither part of the group by clause nor …

The post Fix: ‘Column is invalid in the select list’ appeared first on Appuals.

]]>
The error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause” mentioned below arises when you execute “GROUP BY” query, and you have included at least one column in the select list that is neither part of the group by clause nor it is contained in an aggregate function like max(), min(), sum(), count() and avg(). So to make the query work we need to add all non-aggregated columns to either group by clause if feasible and does not have any impact on results or include these columns in a suitable aggregate function, and this will work like a charm. The error arises in MS SQL but not in MySQL.

Error “Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”

Two keywords “Group by” and “aggregate function” have been used in this error. So we must understand when and how to use them.

Group by clause:

When an analyst needs to summarize or aggregate the data such as profit, loss, sales, cost, and salary, etc. using SQL, “GROUP BY” is very helpful in this regard. For example, to sum up, daily sales to show to senior management. Similarly, if you want to count the number of students in a department in a university group along with aggregate function will help you attain this.

Group by Split-Apply-Combine strategy:

Group by uses “split-apply-combine” strategy

  • The split-phase divides the groups with their values.
  • The apply phase applies the aggregate function and generates a single value.
  • The combined phase combines all values in the group as a single value.
“SPLIT_APPLY_COMBINE” strategy sample

In the figure above we can see that the column has been split into three groups based on first column C1, and then aggregate function is applied on grouped values.  At last combine-phase assigns a single value to each group.

This can be explained using the example below. First, create a database named “appuals”.

Database creation

Example:

Create a table “employee” using the following code.

USE [appuals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[employee](
            [e_id] [int] NOT NULL,
            [e_ename] [varchar](50) NULL,
            [dep_id] [int] NULL,
            [salary] [int] NULL,
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED
(
            [e_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
Employee table creation

Now, Insert data into the table using the following code.

Insert into employee(e_id,e_ename,dep_id,salary)
values(101,'Sadia',1,6000),
(102,'Saba',1,5000),
(103,'Sana',2,4000),
(104,'Hammad',2,3000),
(105,'Umer',3,4000),
(106,'Kanwal',3,2000)

The output will be like this.

Data insertion into table “employee”

Now select data from the table by executing the following statement.

select * from employee

The output will be like this.

The output from the employee table.

Now group by the table according to department id.

select dep_id, salary from employee group by dep_id

Error: Column ’employee.sallary’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The error mentioned above arises because the “GROUP BY” query is executed and you have included “employee.salary” column in the select list which is neither part of the group by clause nor included in an aggregate function.

Error “Column ’employee.salary’ is invalid in the select list because it is not contained in
either an aggregate function or the GROUP BY clause.”

Solution:

As we know that “group by” return single row, so we need to apply an aggregate function to columns not used in group by clause to avoid this error. Finally, apply group by and an aggregate function to find the average salary of the employee in each department by executing the following code.

select dep_id,avg(salary) as average_sallary from employee group by dep_id
Find the average salary of the employee in each department

Furthermore, if we depict this table according to split_apply_combine structure it will look like this.

“SPLIT-APPLY-COMBINE” strategy applied on employee table to find department wise average salary

The figure above shows that first of all, the table is grouped into three groups according to department id, then aggregate avg() function is applied to find aggregate mean value of salary, which is then combined with department id. Thus the table is grouped by department id and salary is aggregated department wise.

Aggregate functions:

  • Sum(): Returns total of each group or sum
  • Count(): Returns no of rows in each of the group.
  • Avg(): Returns mean or an average of each group
  • Min(): Returns minimum value of each group
  • Max(): Returns max value of each group.

The logical description of the use of group by and aggregate functions together:

Now we will understand the use of “group by” and “aggregate functions” logically via an example.

Create a table named “people” in the database by using the following code.

USE [appuals]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[people](
            [id] [bigint] IDENTITY(1,1) NOT NULL,
            [name] [varchar](500) NULL,
            [city] [varchar](500) NULL,
            [state] [varchar](500) NULL,
            [age] [int] NULL
) ON [PRIMARY]
GO
Table creation

Now insert data into the table using the following query.

insert into people(name, city, state,age)
values
('Meggs', 'MONTEREY','CA',20),
('Staton','HAYWARD', 'CA',22),
('Irons', 'IRVINE'  ,'CA',25)
('Krank',  'PLEASANT',  'IA',23),
('Davidson' ,'WEST BURLINGTON',        'IA',40),
('Pepewachtel' ,'FAIRFIELD' ,'IA',35)
('Schmid', 'HILLSBORO',  'OR',23),
('Davidson'  ,'CLACKAMAS', 'OR',40),
('Condy','GRESHAM','OR',35)

The output will be like:

Data insertion into a table named “people”

If the analyst needs to know no of residents and their age in the different states. The following query will help him in getting the required results.

select  age, count(*) as no_of_residents from people group by state

Error: Column ‘people.age’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

On execution of the above-mentioned query, we came across the following error

“Msg 8120, Level 16, State 1, Line 16 Column ‘people.age’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause”.

This error arises because the “GROUP BY” query is executed and you have included “‘people. age” column in the select list which is neither part of the group by clause nor included in an aggregate function.

Grouping by state arises an error

Logical description and Solution:

This is not a syntax error but it’s a logical error. As we can see that the column “no_of_residents” is returning just a single row, now how can we return the age of all residents in a single column? We can have a list of the age of people separated by commas or the average age, minimum or maximum age. Thus we need more information about the “age” column. We must quantify what we mean by the age column. By age what we want to be returned. Now we can change our question with more specific information about the age column like this.

Find no of residents along with the average age of residents in each state. Considering this we have to modify our query as shown below.

select  state,avg(age) as Age,count(*) as no_of_residents from people group by state

This will execute without errors and the output will be like this.

Query to find no of residents along with the average age of residents in each state.

So it’s also crucial to think logically about what to return in the select statement.

Moreover, the following points should be considered in mind while using the “group by” to avoid errors.

  • GROUP BY clause comes after the where clause and before the order by clause.
  • We can use where clause to eliminate rows before applying the “group by” clause.
  • If a grouping column contains a null row, that row comes as a group in itself. Moreover, if a column contains more than one null they are put into a single null group as shown in the following example.

Group by and NULL values:

First, add another row into the table named “people” with the “state” column as empty/null.

insert into people(name, city, state,age) values ('Kanwal' ,'GRESHAM' ,'',35)
Adding NULL/empty value to the column on which group by clause need to be applied

Now execute the following statement.

select state,avg(age) as Age,count(*) as no_of_residents from people group by state

The following figure shows its output. You can see empty value in the state column is considered as a separate group.

Empty value in the column on which group by have been applied is considered as a single group

Now increase no null rows by inserting more rows into the table with null as a state.

insert into people(name, city, state,age)
values ('Kanwal' ,'IRVINE' ,'NULL',35), ('Krank', 'PLEASANT', 'NULL',23)
Inserting NULL value in the column on which “group by” have been applied.

Now again execute the same query to select output. The result set will be like this.

“Null” value in the column on which group by have been applied is considered as a single group

We can see in this figure that an empty column is considered as a separate group and the null column with 2 rows is considered as another separate group with two no of residents. This is how “group by” works.

The post Fix: ‘Column is invalid in the select list’ appeared first on Appuals.

]]>
https://appuals.com/how-to-fix-the-error-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-either-an-aggregate-function-or-the-group-by-clause/feed/ 2
[Solved] SQL Server ‘Network-related or instance-specific error’ https://appuals.com/how-to-fix-the-error-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-fix-the-error-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server https://appuals.com/how-to-fix-the-error-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/#comments Sat, 05 Oct 2019 11:52:48 +0000 https://appuals.com/?p=140618 “A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft …

The post [Solved] SQL Server ‘Network-related or instance-specific error’ appeared first on Appuals.

]]>
“A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (Provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)”.

A network-related or instance-specific error occurred while establishing a connection to SQL Server.

The article discusses a thorough list of troubleshooting methods that can be used to connect to the SQL server. First of all, we will discuss problems that arise when you need to connect to the remote server using IP address as this is the most common cause. These steps are written in “SQL Server 2008 R2″ on “Windows 10″, but they can be used on other versions too with minor amendments.

The error usually means that the “SQL server is not found” or “TCP port is either unknown or wrong”, or it can be blocked by the “firewall”.

Method 1: Gather information about the instance of the SQL Server.

In this section, we will discuss ways to check either the instance of the SQL Server is working or not, along with methods to fix it if it is not working.

Step 1. Check if an instance of SQL Server is installed and is working or not

First of all login to the computer hosting the SQL server instance.  Now, follow these steps to open Services in Windows.

  1. Click on the “Start menu” and then point to “All programs”.
  2. Now point to the SQL Server, and then point to “Configuration tools”
  3. Click “SQL Server Configuration Manager”.
  4. Now select “SQL Server services” and check in the right pane whether the instance of a database engine is running or not.
  5. Moreover, this can be opened directly by typing “services.msc” in the RUN and click OK. The following screen appears.
Opening services by typing “services.msc” in the RUN box.

Now, check if the database engine has been configured to accept remote connections. To check this, follow the following steps.

  1.  After services have been opened you can see database engine in the right pane. The “MSSQLSERVER” is a default unnamed instance. A default instance can only be one.
  2. In the case of the “SQL Express”, default instance will be “SQLEXPRESS” unless it is renamed by someone during installation.
  3. Check if the instance you are trying to connect has the same name as given in the services.
  4. Also, confirm if the status of the instance is “RUNNING”.
  5. Moreover, if you are trying to connect to named instant, then double-check if “SQL Server Browser service” is already running. Thus you need to check if the “SQL Server Browser service” is started on the server on which SQL Server is installed.
  6. In case, the database engine is not running then you need to restart it. So to start the “Database Engine”, in the right pane, right-click on the “Database Engine” (“MSSQLSERVER” default one), and then click “Start”.
Check if the “SQL Server Browser service” is already running.

Step 2. Obtain the IP address of the computer.

To do this follow these steps.

  1. First of all, from the start menu, click “RUN” and type “cmd” and press ok.
  2. In command prompt window type “ipconfig” and note down IPV4 and IPV6 addresses. People mostly use IPV4 address.
Get IPv4 address

Step 3. Get the TCP port number used by the SQL server

Follow the following steps to get TCP port number used by the SQL server

  1. Using “SQL Server Management Studio” (SSMS) connect to the instance of SQL server
  2. From “object explorer” expand “Management”, expand “SQL server log” and click on the current log on which you have to apply filter.
  3. To apply filer click apply filter and type ” server is listening on” in Message contains text box. Click apply filter and press ok.
    Applying filter ” server is listening on”
  4. A message like “server is listening on [‘any’ <ipv4> 1433]” should be shown. The message shows that the SQL Server instance is listening on all computers with IP address IPv4 and TCP port is 1433 (default).
  5. For more than one instance TCP port will be different for each instance.
    Message showing server is listening on IPv4 and port 1433
  6. If it is not a case then click “All programs”, point to MS SQL server configuration tools, “SQL server configuration management” , and right-click “TCP\IP” and click enable and restart SQL server to let changes create impact.

Method 2: Enabling protocols for port 1433

Connecting to the “Database Engine” from another computer is not allowed in many “SQL Server” implementations unless an administrator utilizes “Configuration Manager” to allow it. The following steps should be followed to do this.

  1. Click on “Start menu” and then point to “All programs”
  2. Point towards the “SQL Server 2008 R2”
  3. Point towards “Configuration tools”, and after this click “SQL Server Configuration Manager”.
  4. Expand “SQL Server Network Configuration”.
  5. Select “protocols for MSSQL server”. Click on “TCP\IP” in the right panel.
    Opening “Protocol Tab”
  6. In the tab “protocol” set enable as “yes”.
  7. Choose the “IP Address tab” from the window and set “TCP Port” equal to “1433″ in the “IP All” entry.
    Set port no in “IP Address tab”
  8. Now restart database engine to make changes leave their impact. To do this from the left pane, select SQL server services and then from right pane right-click database engine instance and press “restart”.

Method 3: Create a Firewall exception

Sometimes Windows firewall turns on and blocks links from another computer. To fix it follow these steps.

  1. Click “Start” and start typing “Firewall.cpl” in the run box.
    Opening “Firewall.cpl”
  2. You get the “configuration frame” for Windows Firewall by running the “firewall.cpl” command. You may turn the firewall “on/off” with exceptions and other settings applied here. Check the firewall status and turn it on to activate it if the firewall is off. If you’ve just turned this on, your firewall will block any “SQL Server” connection request to your computer at this point. Through making certain exceptions, you’d need to configure the firewall to allow access to a SQL Server database engine.
  3. Click on “Advanced Settings”
    Click on advanced settings option to open firewall rules
  4. We need to learn about the ports used for “SQL Server” and the “SQL Server Browser” feature when dealing with “SQL Server” firewall configurations. Both are involved in setting up a “firewall” for the “SQL Server” . It would, therefore, be necessary to go separately through both concepts.
  5. You may permit or block traffic attempts that meet the requirements in the rule to access the computer. By default  “inbound traffic” is blocked, you need to establish “inbound rule” to allow traffic to reach the computer. Tap the Inbound Rules from the left pane of the “Windows Firewall with Advanced Security” and click the New Rule from the “Actions” window. 
    Selecting New Rule from the “Actions” window.
  6. Select “Portunder “Rule Typeand pressNext” button
    Selecting “port” option
  7. Now select “Specific local ports” and set it to 1433
    set “specific local port” to 1433
  8. Now select “Allow the connection” in the “Action” dialog and press the Next button
    selecting “Allow the connection”
  9. Give the rule atitle” on this stage and press the “Finish” button.
    Give a title to the rule
  10. Select “Custom rule” from “New rule” tab
    Select “Custom rule” from “New rule” tab
  11. Click “customize”
    Click “customize”
  12. Select “Database Engine Instance Service” from the “Customize Service Settings” under “Apply to this service” and click the “OK” button
    Select “Database Engine Instance Service” from the “Customize Service Settings” under “Apply to this service” and click the “OK” button
  13. Give the rule a name and click finish
    Give a title to the new rule
  14. Also add “sqlservr.exe” typically located in “C:\Program Files (x86)\Microsoft SQL Server\MSSQL.x\MSSQL\Bin” (or check your actual folder path) to the path, check your installs for the actual folder path) and port whose default value is “1433”. Also, check your connection string.

Method 4: Check Local connection

One of the reasons for this error is if we provide the wrong server name, this will result in an error. As seen in the figure below provided server name is “DESKTOP-UD88TLT1” whereas accurate server name is “DESKTOP-UD88TLT”. So it will be unable to connect to the server which will result in an error “cannot connect to server”. This is the most basic reason for error, so we should check it first if working locally.

The error arises while locally connecting to SQL server with the wrong server name In case you are using express edition following your server name, add “\SQLEXPRESS” as seen in the figure below.

Locally connecting to SQL server while using express edition

The post [Solved] SQL Server ‘Network-related or instance-specific error’ appeared first on Appuals.

]]>
https://appuals.com/how-to-fix-the-error-a-network-related-or-instance-specific-error-occurred-while-establishing-a-connection-to-sql-server/feed/ 2
How to Split a String by a Delimited Char in SQL Server? https://appuals.com/how-to-split-a-string-by-a-delimited-char-in-sql-server/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-split-a-string-by-a-delimited-char-in-sql-server https://appuals.com/how-to-split-a-string-by-a-delimited-char-in-sql-server/#respond Fri, 27 Sep 2019 19:21:30 +0000 https://appuals.com/?p=139201 In this article, we will discuss several ways to split the delimited string value. It can be achieved using multiple methods including. Use of STRING_SPLIT function to split the string Create a user-defined table-valued function to split the string, Use XQuery to split the string value and transform a delimited string into XML First of …

The post How to Split a String by a Delimited Char in SQL Server? appeared first on Appuals.

]]>
In this article, we will discuss several ways to split the delimited string value. It can be achieved using multiple methods including.

  • Use of STRING_SPLIT function to split the string
  • Create a user-defined table-valued function to split the string,
  • Use XQuery to split the string value and transform a delimited string into XML

First of all, we need to create a table and insert data into it which will be used in all three methods. The table should contain a single row with field id and string with delimiter characters in it. Create a table named “student” using the following code.

CREATE TABLE student
  (
     ID INT IDENTITY (1, 1),
     student_name VARCHAR(MAX)
  )

Insert student names separated by commas in a single row by executing the following code.

INSERT INTO student (student_name)
VALUES ('Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad')
Table creation and data insertion

Verify if data has been inserted into the table or not using the following code.

select * from student
Verify if data has been inserted into the “student” table

 

Method 1: Use STRING_SPLIT function to split the string

In SQL Server 2016, “STRING_SPLIT” function was introduced which can be used with compatibility level 130 and above. If you use the 2016 SQL Server version or higher you can use this build-in function.

Furthermore “STRING_SPLIT” inputs a string that has delimited sub-strings and inputs one character to use as the delimiter or separator. The function outputs a single-column table whose rows contain the sub-strings. The name of the output column is “Value”. This function gets two parameters. The first parameter is a string and the second one is delimiter character or separator based on which we have to split the string. The output contains a single-column table in which substrings are present. This output column is named “Value” as we can see in the figure below. Moreover, the “STRING SPLIT” table_valued function returns an empty table if the input string is NULL.

Compatibility level of the database:

Each database is connected with compatibility level. It enables the database’s behavior to be compatible with the particular SQL Server version it runs on.

Now we will call “string_split” function to split string delimited by commas. But the compatibility level was less than 130 hence following error was raised. “Invalid object name ‘SPLIT_STRING'”

Error arises if compatibility level of database is lower than 130 “Invalid object name split_string”

Thus we need to set the database compatibility level to 130 or higher. So we will follow these step to set the compatibility level of the database.

  • First of all set database to “single_user_access_mode” by using the following code.
ALTER DATABASE <[database_name]> SET SINGLE_USER
  • Secondly, change the compatibility level of the database by using the following code.
ALTER DATABASE <[database_name]> SET COMPATIBILITY_LEVEL = 130
  • Put the database back to multi-user access mode by using the following code.
ALTER DATABASE <[database_name]> SET MULTI_USER
USE [master]
GO
ALTER DATABASE [bridge_centrality] SET SINGLE_USER
ALTER DATABASE [bridge_centrality] SET COMPATIBILITY_LEVEL = 130
ALTER DATABASE [bridge_centrality] SET MULTI_USER
GO

The output will be:

Change compatibility level to 130

Now run this code to get the required result.

DECLARE @string_value VARCHAR(MAX) ;
SET @string_value='Monroy,Montanez,Marolahakis,Negley,Albright,Garofolo,Pereira,Johnson,Wagner,Conrad'
SELECT * FROM  STRING_SPLIT (@string_value, ',')

Output for this query will be:

Output from “split_string” build_in function

Method 2: To split the string, create a user-defined table-valued function

Certainly, this traditional method is supported by all versions of SQL Server. In this technique we will create user-defined function to split the string by delimited character using “SUBSTRING” function, “CHARINDEX” and while loop. This function can be used to add data to the output table as its return type is “table”.

CREATE FUNCTION [dbo].[split_string]
(
    @string_value NVARCHAR(MAX),
    @delimiter_character CHAR(1)
)
RETURNS @result_set TABLE(splited_data NVARCHAR(MAX)
)
BEGIN
    DECLARE @start_position INT,
            @ending_position INT
    SELECT @start_position = 1,
            @ending_position = CHARINDEX(@delimiter_character, @string_value)
    WHILE @start_position < LEN(@string_value) + 1
            BEGIN
        IF @ending_position = 0 
           SET @ending_position = LEN(@string_value) + 1
        INSERT INTO @result_set (splited_data) 
        VALUES(SUBSTRING(@string_value, @start_position, @ending_position - @start_position))
        SET @start_position = @ending_position + 1
        SET @ending_position = CHARINDEX(@delimiter_character, @string_value, @start_position)
    END
    RETURN
END

Now execute the script below to call a split function to split string by delimiter character.

DECLARE @student_name VARCHAR(MAX);
DECLARE @delimiter CHAR(1);
SET @delimiter=','
SET @student_name =(SELECT student_name FROM   student)
SELECT * FROM dbo.split_string(@student_name, @delimiter)

The result set will be like this.

Result set from “split_string” table-valued function

Method 3: Use XQuery to split the string value and transform a delimited string into XML

As user-defined functions are resource exhaustive so we must avoid these functions. Another option is built-in “string_split” function but this function can be used for database for which compatibility level is 130 or higher. So here comes another solution to solve this difficult task. A string can be splitted using the following XML.

DECLARE @xml_value AS XML,
        @string_value AS VARCHAR(2000),
        @delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT student_name FROM student)
SET @delimiter_value =','
SET @xml_value = Cast(( '<studentname>'
                  + Replace(@string_value, @delimiter_value, '</studentname><studentname>')
                  + '</studentname>' ) AS XML)
SELECT @xml_value

The output for this query will be:

Step 1 for splitting a string using XML

If you want to view the whole XML file. Click on the link. Once you have clicked the link code will look like this.

An XML file containing individual nodes of string to be split

Now XML string should be processed further. Finally, we will use “x-Query” to query from the XML.

DECLARE @xml_value AS XML,
        @string_value AS VARCHAR(2000),
        @delimiter_value AS VARCHAR(15)
SET @string_value=(SELECT student_name FROM student)
SET @delimiter_value =','
SET @xml_value = Cast(( '<studentname>'
                  + Replace(@string_value, @delimiter_value, '</studentname><studentname>')
                  + '</studentname>' ) AS XML)
SELECT   x.m.query('.').value('.', 'VARCHAR(15)') AS VALUE
FROM   @xml_value.nodes('/studentname') AS x(m)

The output will be like this:

Use of “XQuery” to query from the XML

The post How to Split a String by a Delimited Char in SQL Server? appeared first on Appuals.

]]>
https://appuals.com/how-to-split-a-string-by-a-delimited-char-in-sql-server/feed/ 0
‘Conversion failed when converting date and time’ [Error Solved] https://appuals.com/how-to-fix-conversion-failed-when-converting-date-and-or-time-from-character-string-error/?utm_source=rss&utm_medium=rss&utm_campaign=how-to-fix-conversion-failed-when-converting-date-and-or-time-from-character-string-error https://appuals.com/how-to-fix-conversion-failed-when-converting-date-and-or-time-from-character-string-error/#respond Sun, 22 Sep 2019 10:44:48 +0000 https://appuals.com/?p=136173 There are many instances when dates and times do not appear in the format you want it to be, nor does a query output fit the needs of the viewers. There are several SQL Server’s built-in features to format the date string according to your need but for the string to be interpreted by SQL …

The post ‘Conversion failed when converting date and time’ [Error Solved] appeared first on Appuals.

]]>
There are many instances when dates and times do not appear in the format you want it to be, nor does a query output fit the needs of the viewers. There are several SQL Server’s built-in features to format the date string according to your need but for the string to be interpreted by SQL Server and to avoid conversion errors it should be in a proper format. When we try to convert date or time from character string following error arises sometimes. “Conversion failed when converting date and/or time from character string.”

date time conversion error
Figure 1: Date and/or time conversion error from the character string

The error mentioned above normally arises when the date literal is not proper and cannot be converted from the string into DateTime or date. This error results due to a number of reasons, which we will discuss in detail along with the solution set.

Example 1:

United Kingdom Date and time notation display the date using the day-month-year format (January 10, 2015 or 10/1/2015) which we can achieve using SQL Server built_in feature “convert” function with formatting style 103.

Here in the example below we can see that the provided date string is in the wrong format. First, it is providing the month, then days and at last year which is wrong and cannot be interpreted by SQL Server which results in an error. The correct format for UK style date conversion using “103” date style is “dd/mm/yyyy”.

Wrong Format:

Declare @date_time_value varchar(100)= '10/16/2015 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as UK_Date_Time_Style
Figure 2: Wrong Date Format resulting in an error

Correct Format:

The British and French date format is 103 = “dd/mm/yyyy” or 3=” dd/mm/yy”. Here 103 and 3 are date styles.

Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 103) as Date_Time_Style
Figure 3: Correct Date Format with “dd/mm/yyyy” British/ French date style
Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 3) as UK_Date_Time_Style
Figure 4: Correct Date Format with “dd/mm/yy” British/ French date style

Example 2:

Sometimes string to date conversion in SQL server results in error, not because of the date or time formats used, rather it’s because you’re attempting to store incorrect information that’s not acceptable to the scheme.

Wrong Date:

The reason for the following error is mere that in the year 2019 there is no such date as “29 February” because it is not a leap year.

Declare @date_time_value varchar(100)= '2019-02-29 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value
Figure 5: Error raised as 2019 is not a leap year, so it does not have 29 Feb as date

Correct One:

Declare @date_time_value varchar(100)= '2019-02-28 21:02:04'
select cast(@date_time_value as datetime2) as date_time_value
Figure 6: Correct date

ISO 8601 Date Format:

Although numerous formats are available for manipulating date values, when working for a global/ international mass, it can be a usability issue to choose a datetime representation. So culture-specific date/time literals should be avoided. If we consider this date “03/08/2018”, it will be interpreted in following different ways in different regions of the world.

  • In UK style it is interpreted as “8th of March 2018”
  • In European style it is interpreted as “3rd of August 2018”

Luckily, there is one alternative in the international date format developed by ISO. The global standard ISO 8601 format “YYYY-MM-DDThh: mm: ss” is a more language-independent option for string literals and it addresses all these issues. Whereas, “yyyy” is the year, “mm” is month and “dd” is day. So the date “8th of March 2018”in international ISO format is written as “2018-03-08”. Thus ISO format is the best choice for date representation.

Declare @date_time_value varchar(100)= '2019-03-28 21:02:04'
select convert(datetime2,@date_time_value,126) as [yyyy-mm-ddThh:mi:ss.mmm]
Figure 7: International Standard ISO 8601 date format

Recommendations:

Hopefully, this article will assist to relieve the confusion I have frequently seen in the community about date/time values. However, it is recommended that never store dates in text-type (varchar, char, nvarchar, nchar,or text) Always store date value in DATE, DATETIME and preferably DATETIME2 (provides more precision) type columns and, leave the date information formatting to the user interface layer instead of being retrieved from the database.

The post ‘Conversion failed when converting date and time’ [Error Solved] appeared first on Appuals.

]]>
https://appuals.com/how-to-fix-conversion-failed-when-converting-date-and-or-time-from-character-string-error/feed/ 0