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]

Invalid byte 1 of 1-byte UTF-8 sequence

Error:
Sorry, an error occured loading the new transformation from the repository:

Error reading information from input stream
Invalid byte 1 of 1-byte UTF-8 sequence

Why:
I am modifying transformation XML file and instead of writing sql in "table input" step i directly replace query in xml file with in xml SQL tags.

As you know guys if we paste sql from UI in "Table input" step then kettle automatically convert special characters in SQL query to some readable form, already mentioned in other post read below link

Link 1 
Replace Special Char from string for "table input step" kettle transformation
https://j4info.blogspot.com/2016/02/replace-special-char-from-string-for.html

Link 2
Function to process each character of String [SQL server]
http://j4info.blogspot.com/2016/02/function-to-process-each-character-of.html

Solution :
On other hand if you would like to modify XML file from transformation then you have you replace special character in some format (see above link in the same article).

In my case there was same space like character in sql query (not space in actual,select char(160)).Space can be identify in notepad plus plus shown as small orange dot in place of space if clicked on "Show all Character" button.

But my space is showing space only means it is some special kind of character and we need to replace it " " so that kettle can read it.