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.

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.

   

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.

   

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.

   

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.

   

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.

   

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.

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.

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.

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)

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.

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

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

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 3^rd language in the list:

print(languages[3])

But it will throw the Indexerror. But why? Here, the index range of our list is 3 and we are trying to print the 3^rd language in the list. However, in our code, we are asking Python to print the 4^th 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 4^th value so Python throws Indexerror. Here, the proper code will be

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

print(languages[2])

IndexError in Negative Indexing

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

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

print(languages[-4])

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

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

print(languages[-3]) # which will show Python.

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

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.

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])

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])

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])

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])

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.

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)

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)

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)

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)

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)

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])

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])

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])

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

s = 'Python'

print(s[5])

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])

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

s = ('Asia', 'Europe')

print(s[1])

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.

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.

   

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.

   

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);
   },
   });
   });
   });

   

6. Once done, right-click the Html code and select Open with Live Server.

   

7. If you cannot see this option, then install the Live Server extension and open the code on the browser.

   

8. Once the browser is opened with the live server, right-click the empty space and select Inspect or press Ctrl + Shift + I together.

   

9. Go to Console from the top and then click on Send request button to see if it works or not.

   

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.

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.

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.

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

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”.

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

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.

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

select * from employee

The output will be like this.

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.

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

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

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:

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

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:

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.

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 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)

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.

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)

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

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.

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.

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”.

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.

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.

   

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.

   

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.

   

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.

   

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.

   

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”

   

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. 

   

6. Select “Port” under “Rule Type” and press “Next” button

   

7. Now select “Specific local ports” and set it to 1433

   

8. Now select “Allow the connection” in the “Action” dialog and press the Next button

   

9. Give the rule a “title” on this stage and press the “Finish” button.

   

10. Select “Custom rule” from “New rule” tab

    

11. Click “customize”

    

12. 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

    

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.

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

• 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')

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

select * from student

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 a 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'”

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:

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:

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.

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:

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

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:

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

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

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

Declare @date_time_value varchar(100)= '10/1/15 21:02:04'
select CONVERT(datetime2, @date_time_value, 3) as UK_Date_Time_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

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

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]

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.