How to configure Column default value in SQL Server in design View.

Recently I migrated my MS ACCESS database to SQL Database. The migration is a success until a user tried to print a report from her MS Access frontend. All the computation in a report is blank. It confusing because the code for the computation is correct and the field to calculate has a value and it returns no error only its show blank.

When I compare the field from my old previous MS Access database to new SQL database I now see what causes of that blank computation results. It seems that in my old database, any field with numeric data types has a default value of zero if the field hasn't specify any value, but in my new database it will be null. And so I need to change it to zero if value is not specify. In my old database the datatypes is numeric but when I converted it to SQL it become FLOAT data types.

 What I did to fix this is to enter a default value in all of the fields that have a FLOAT data types. I enter the value zero(0) in all float data types if the field does not contain any number, so in case the field has no value it should have zero in that field instead of null. When I do that MS access can now compute and all the computation in reports or form are fix.

This is how to add a default value in a column in SQL Server using design View.
So every time a new record is inserted it automatically add the default to the field you specify.

1. Login to your SQL Server using SQL Management Studio.
2. In your Object Explorer expand to your database and to your desired table.
3. right click on your table then select Design
4. click on choose the column you want to add default value
5. then on column properties, navigate to default Value or Binding then enter the value you want.

Goodluck!

How to connect to SQL Server from another computer without joining a domain.

We have a small database in our office using SQL Database as backend and Microsoft Access as front-end. For the past years, we are using Windows server 2000 where the SQL Data is stored. All of the front-end workstations are connected to Windows server via a domain active directory.

If I we established a new database workstation we need to connect to a domain for our MS access Database to able connect to SQL Database via ODBC Connection.

But how to access SQL Server Database from a remote computer without joining a domain?

The simple answer is to create an SQL login and use SQL authentication to connect to SQL Server.

Use that SQL Login in your connection string, and if you're using ODBC connection just like our database use SQL Authentication instead of Windows Authentication.

To create SQL Server Login for SQL Server Authentication.

1. In SQL Server Management Studio (SSMS), Open Object Explorer and expand the folder of the server instance (in my case its SQLEXPRESS) in which to create the new login.
2. right-click the Security folder, point to New and then click Login
3. On the General page, enter a username for the new login in the Login name box.
4. Select SQL Server Authentication.
5. Enter a password for that username.
6. Choose the password policy options that should be applied to the new login. It's recommended to use enforcing password policy.
7. click OK.

In my first time connecting via SQL Server Authentication, I encountered a firewall problem. So make sure the port 1433 and 1434 are open in your firewall. This two port are the ports uses by SQL Server to communicate to another computer.

To open a port in your Windows firewall.

1. Navigate to Control Panel, System, and Security and Windows Firewall.
2. Select Advanced settings and highlight Inbound Rules in the left pane.
3. Right-click Inbound Rules and select New Rule.
4. Add the port you need to open and click Next.
5. Add the protocol (TCP or UDP) and the port number into the next window and click Next.
1. TCP Port 1433 - sqlserver
2. UDP Port 1434 - sqlbrowser
6. Select Allow the connection in the next window and hit Next.
7. Select the network type as you see fit and click Next.
8. Name the rule something meaningful and click Finish.

Goodluck!

How to fix SQL Server "MSSQLSERVER" service failed to start.

I installed SQL Server 2014 in windows 10, I having trouble starting SQL Server express, giving me the message "The SQL Server (MSSQLSERVER) service failed to start due to the following error: The service did not respond to the start or control request in a timely fashion."

This error happened because of the SQL Server unable to find your login ID credentials or you used the account which is not authorized to start the SQL services.

To fix this error you need to set authorized user to start the service. Now we use local account set to start the sql service.

1. Administrative Tools > Services > SQL Server(MSSQLSERVER or your SQL server instance name)
2. right click on MSSQLSERVER then select properties.
3. move to Log On, then set the Log On option to the local system account.
4. Click apply.

Also, set the start mode to automatically.

Then restart your computer.

Cannot connect to SQL Server Express from other computer.

Lately, I have a task to install an SQL server on Windows 10 and Nine clients are mix with windows 7 and 10. I used SQL  Server express 2014 as my back-end database and c#, Ms Access through ODBC as my front-end. And now my Database is running smoothly, but before this I countered a problem connecting to database server from other computer on my network. I'm sharing this so if anyone encounter the same can look into this.

1. Make sure SQL server is running - To check if it is running on Run type services.msc then find SQL Server see if it is running and set to automatically. Else click start or restart.
2. Make sure both database server and clients are on the same network domain or work group. To change the work group or domain. Right click This PC(my computer) click properties then on computer name tab you can change the workgroup or join the domain.
3. Firewall Settings - Check the windows firewall settings of the computer where the SQL server express is installed, usually SQL Server port is 1433 make sure it is allowed by your firewall. Disable the windows firewall and try to connect again.
4. Enable remote connections in your SQL Server Instance - SQL Server Express is set to allow only local connections as default. You need to allow remote connections for the other computer in your network to access your database. 
1. Enable TCIP Connection - Go to the Computer where SQL server is installed then on Start > Programs > Microsoft SQL server > SQL Server Configuration Manager.  On SQL Network configuration > Protocol for SQLEXPRESS(your instance name) then enable TCP/IP.
2. Allow network connection on the SQL server instance - Open SQL Management studio then log-in using your authentication, right click on your server name then click Properties, click on connections check on allow network connections to this server, Finally set 600 on remote query time out.

Hope it helps.