SQL Server and Microsoft IT blog

Transact-SQL

Clustered index in SQL Server

What is a SQL Server clustered index ? The definition of a SQL Server index is a structure, i.e. a file, stored physically in a disk. It’s like the master data file and the log data file that composes a database. Basically, to find the associated lines or rows faster, the index stores one or more key columns. So what’s the definition of a

SQL Server tables

Check if table exists in SQL Server

How to Check if table exists in SQL Server before to delete it ? How to drop it only if the existence check shows it already exists in the database to avoid errors, using the DROP TABLE keyword. Indeed, when running long SQL scripts or running scripts twice, it is much better to test the table existence. Indeed, we highly recommend testing the table

Powershell

Copy a file with PowerShell

How to copy a file to another folder with windows PowerShell copy script? There are several efficient options to copy Windows files from the PS command prompt. In PowerShell scripting, use the Copy-Items command in a Windows PowerShell prompt. It is also possible to copy from the GUI, but this option is very manual. Indeed, it is always possible to copy files and folders

SQL Server error

Database does not exist error in sp_MSforeachdb

How to fix the database does not exist error in sp_MSforeachdb ? With MS SQL Server, when using the sp_MSforeachdb system stored procedure, the system displays this error “Database does not exist. Make sure that the name is entered correctly.”. But only in specific cases and for certain databases. The stored procedure displays the error because the database tries to access a database that

IT Concepts

Definition of Big Data and its concepts

Definition of Big Data and its concepts, marketing among others. Big Data Analytics is data that is larger than the capacity of commonly used software tools. These tools are used to capture, manage and process data in an acceptable timeframe, i.e. quantities of data that traditional BI tools cannot handle. This data is constantly growing, from a few terabytes to several petabytes. MIKE 2.0

SSIS

Excel column data type in SSIS import into SQL Server

How to Detect the Excel column data type with SSIS import into SQL Server ? The integration of Excel files via SSIS requires you to freeze the data type in the OLEDB component. This is because the Excel data type is determined in SSIS by sampling the first 8 rows of the default column in the OLEDB component for Excel sources. Therefore, to integrate

SSMS

Import data from Excel to SQL Server with SSMS

In this SQL Server tutorial, here is how to Import data from an Excel file to a database, with the SQL Server wizard, without SQL code. To do this, use the Data Import Wizard from SQL Server Management Studio. In this example, here is how to import data from a source Excel file to a SQL Server target table. To Import an Excel file

SQL Server DB

Download SQL Server 2019 developer free edition (MSSQL)

Where to download the MS SQL Server 2019 developer free edition ? It’s the free edition and contains the full set of available features. It also provides a real option for evaluation for developments, tests, and proof of concepts. Nevertheless, it is not for use in a production environment. Indeed, SQL developers must install the DBMS tool locally to understand its architecture and operation.

Articles populaires

Transact-SQL

Violation of Unique Key constraint with SQL Server

How to avoid Violation of Unique Key constraint with SQL Server ? Insert or update data in an SQL Server table with a simple query? Here are two simple solutions to execute an update or insert and avoid errors. The SQL Server error ” Violation of UNIQUE KEY constraint . Cannot insert duplicate key in object . The duplicate key value is ” because

SQL Server error

Arithmetic overflow error converting expression

With SQL Server, how to avoid this error : “Arithmetic overflow error converting expression to data type int”. Let’s insert some data into a table using a stored procedure for example. Facing this SQL Server error message can be disturbing. Even if SQL conversion errors are classical ones, finding the solution and apply the industry best practices to avoid them is very useful. In

Excel Tips

How to pivot Excel rows into columns ?

How to pivot the rows of an Excel table into columns? It’s relatively simple. You just need to select the area you want to rotate, in our example it is an Excel table with financial amounts and months. On the other hand, the special paste of the copied data is the Excel option to rotate the rows into columns. How to pivot the rows

Excel Tips

Disable the direct edit option in Excel

With Microsoft Excel, how to disable the direct edit option in Excel to edit spreadsheet cells? That is, the function of automatically modifying the content of a cell when it contains a formula? Indeed, by clicking on a cell containing a formula, this click sends you back to the first cell quoted in this formula. Indeed, this is very practical, but it can also

Excel Tips

Create a pivot table with Excel

How to create an Excel pivot table to analyse data? When analysing a lot of data, it can be difficult to analyse all the information in your spreadsheet. Especially large tables with several thousand rows. Pivot tables make spreadsheets more manageable by summarising the data and allowing it to be arranged in different ways. How do you answer the question “How much does each

Excel Tips

How to unhide an Excel spreadsheet ?

With Microsoft Excel, how to unhide an Excel spreadsheet ? I.e. make a hidden worksheet visible? A hidden Excel worksheet is handy in a shared workbook to not allow all users to change data or settings. For example, if the Excel workbook is locked with a password, then the hidden data is protected from changes. How to display a hidden Excel worksheet in a

T-SQL

Transact-SQL

Implicit conversion from data type XML to NVARCHAR is not allowed error

How to convert XML into NVARCHAR with SQL Server? To avoid the SQL Server error conversion from XML to NVARCHAR. The exact message is “Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query.”. With SQL Server data, it is useful to combine numerical columns, alphanumeric strings and XML typed columns.   In addition, to

Transact-SQL

Violation of Unique Key constraint with SQL Server

How to avoid Violation of Unique Key constraint with SQL Server ? Insert or update data in an SQL Server table with a simple query? Here are two simple solutions to execute an update or insert and avoid errors. The SQL Server error ” Violation of UNIQUE KEY constraint . Cannot insert duplicate key in object . The duplicate key value is ” because

Transact-SQL

Calculate Sql Server time difference in hours minutes and seconds

How to calculate a Sql Server time difference hours minutes and seconds with datediff ? The SQL Server function DATEDIFF() allows to calculate the difference between two timestamps, but only in one unit. For example, only seconds, or only minutes or only hours. This SQL Server script uses the datediff function and calculate in hours, minutes, and seconds. So, calculating the difference between a

MS-DOS

MS-DOS

Rename multiple files at the same time with Windows

How to rename Rename multiple files with Windows at the same time ? One solution is to use third party software or the F2 option in the GUI, but the latter is not as flexible as scripting. However, it is simple and effective to use a cmd request. Here are different command line options to rename a series of several files in batch and

MS-DOS

Clear the command line screen prompt

How to clear the Windows command line screen prompt ? MS-DOS allows to manage the Windows system in a very effective way, for example by using scripting to automate tasks. After running multiple commands on the black dos screen, it is very convenient to clear the old messages. How to clear the Windows command line screen prompt ? Use the cls command. The cls

MS-DOS

Delete Windows files recursively in cmd

How to delete files recursively in Windows cmd? I.e. how to delete all files in a folder and its subfolders. Use the del batch command line and the recursive option. The Windows delete command provides this useful option automate the deletion process. For example, if you have a huge amount to delete but need to keep the folder and subfolder’s structure. How to delete

SSIS

SSIS

Excel column data type in SSIS import into SQL Server

How to Detect the Excel column data type with SSIS import into SQL Server ? The integration of Excel files via SSIS requires you to freeze the data type in the OLEDB component. This is because the Excel data type is determined in SSIS by sampling the first 8 rows of the default column in the OLEDB component for Excel sources. Therefore, to integrate