SQL Server Administration Blog Sites
Blog Sites https://blog.sqlauthority.com/
Creating Linked Servers
Link Server with a Specific Name ARCHIVE -> MyDB03 ARCHIVE is the link server name. MyDB03 is the actual database server name. Sources: https://docs.microsoft.com/en-us/sql/relational-databases/linked-servers/linked-servers-database-engine?view=sql-server-2017
RPC and RPC Out option on a SQL Server linked-server
The first RPC setting is mainly for a legacy feature called Remote Server, which is documented here. http://msdn.microsoft.com/en-us/library/ms190187.aspx You probably will not be using remote servers in SQL Server 2005 -SQL Server 2014 versions. If you do happen to use “remote servers”, the RPC setting enables a certain security feature. Sources:
Rounding in SQL
Example: Source: https://docs.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql?view=sql-server-2017
SQL Server DateTime Type
Data type Comments Time Only keeps track of nanoseconds in a day. There is no reference to a date.Is stored using between 3 and 5 bytes. Date Only keeps track of days, starting 01/01/01 to 31/12/9999.Takes 3 bytes for storage. SmallDateTime Can be used to store dates between 01/01/1900 and 06/06/2079.Also allow to keep track... » read more
SQL Server Setup Error – There was an error generating the XML document. Error code 0x84B10001.
Error: C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\Summary.txt If we look at the start place of the error, it says System.Security.Cryptography.CryptographicException. This means that there are some issues while encryption of the data. And the real error message is “The requested operation cannot be completed. The computer must be trusted for delegation and the current user account... » read more
Transfer Files Using Remote Desktop
By default, Windows Dedicated Servers have no FTP server installed or setup. The easiest way to securely transfer files between the server and your computer is use Remote Desktop. Click the Start menu on your PC and search for Remote Desktop Connection. Launch Remote Desktop Connection and click on Show Options. Select the Local Resources tab and click More. Under Drives, check the... » read more
Copying Data from One Database to Another
First setup a link server to the other database.
What is best tool to compare two SQL Server databases (schema and data)?
Microsoft Visual Studio Community Edition (Free) has a tool called Data Compare and Schema Compare. Visual Studio -> Tools -> SQL Server Red-Gate http://www.red-gate.com/products/sql-development/sql-compare/ http://www.red-gate.com/products/sql-development/sql-data-compare/
RedGate Error: We don’t have DMV queries available for SqlServer2014
Error: We don’t have DMV queries available for SqlServer2014 Fix: Upgrade to RedGate version 12+. SQL Server 2016 is only supported by SQL Compare 12. Sources: https://forum.red-gate.com/discussion/81091/does-red-gate-compare-engine-11-1-0-2-work-with-microsoft-sql-server-2016-13-0-4202-2-x64