Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.

Error
C:\Users\*******>sqlcmd -S ServerName -d master  -U sa
Password: Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

Solution
I am using "sqlcmd -S ServerName -d master  -U sa" command to connect cmd with SQL Server. I observed that the command was correct but spell mistake occur in Server Name, So when ever you guys get error like this, check your server name once in command

Msg 102, Level 15, State 1, Server *********, Line 1

This error is received when changing database using "use" command on sqlcmd window. I have verified that database name is exists and there is no spell mistake but still error is received. Doing google search nothing getting from articles. After relaxation of some time i have noticed the error carefully it is saying syntax error near '2019'

That mean something fishy in database name itself.


1> use 2019_database
2> go
Msg 102, Level 15, State 1, Server *********, Line 1
Incorrect syntax near '2019'.
1> use [2019_database]
2> go
Changed database context to '2019_database'.
1>

Solution:
use solid bracket around the database backup name

Do/Donts

• Best practice to keep database name starting from alphabet a-z
• If Database name start from Numeric then donot forget to use solid brackets around the database name
• Always use "GO" on sqlcmd to execute query

How to execute query on CMD

Yes title is correct, you can execute queries on CMD for SQL Server. See below syntax showing how to connect with  CMD and enter in SQL Prompt to execute queries.

Step 1 - Open  CMD
Step 2 - Connect
C:\Users\*****>SQLCMD -S IP_Address\Instance_Name -U sa -P SQL_Server_Password
1>
2> select * from master.test
3> Go

SQL Server - Set Default Time stamp in date

SQL Server - Set Default Time stamp in date

Another story :)
some time we need to add timestamp along with the date, or we need to replace existing timestamp with custom timestamp
Yes this is doable and it is very easy.

Here i am going to show a example from AdventuresWorks database, object will be user person.person and fieldname is createdDate.

1. I am converting first datetime to date only
2. Append date with default timestamp

This will work on both either on date field or on datetime field

 select cast(createddate as date) ,DATEADD(day, DATEDIFF(day, 0, cast(createddate as date) ), '11:00:00.000') from [Person].[Person]