TechShizz | All posts tagged 'SQL'

Allowing remote connection to a SQL Server

From the SQL Server Configuration Manager:
1. Make sure that the TCP/IP protocol is enabled.
2. In the properties of the RCP/IP protocol, ensure that the IP address that clients are connecting to is "Enabled" and "Active"
3. Ensure that IPALL settings are 0 and 1433.
4. Ensure the following rules are setup in Windows Firewall:

Outbound TCP 1433-1434: Allow
Outbound TCP 1433-1434: Allow
Inbound UDP 1433-1434: Allow
Inbound UDP 1433-1434: Allow

SQL: Alter Views

In this example, we alter a view so that instead of dislpaying all colums in the Employees table, we now only show the EmployeeID, FirstName and LastName. This is specified under the "SELECT DISTINCT" part.

Here is the View before the following script is executed.

Alter table - Table before script executed
See Creating Views to see the view created before this was run.

USE
TechShizz
GO

ALTER VIEW vEmployeesWithSales
AS

    SELECT DISTINCT
        Employees.EmployeeID,
        FirstName,
        LastName
    FROM
        Employees
            JOIN
        Sales ON Employees.EmployeeID =Sales.EmployeeID

GO

Next we can re-name the view by using the following command:

sp_rename 'vEmployeesWithSales','Employees'

Once renamed and altered the table should look like this once executed:

Alter table - Table after script executed

Another Example

We could alter the view so that we have a view for the entire Emplyees table.
USE TechShizz
GO

ALTER VIEW vEmployees
AS

    SELECT DISTINCT
        *
    FROM
        Employees
GO

Alter table - Table after 2nd script executed