To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.
For this example I have created a… Read more
Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when… Read more
You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify… Read more
SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:
Here, I have tried changing data type for 'Name' column from NVARCHAR(50)… Read more
There are different options available when you want to execute a .sql script file on a server. You can open the script file in SQL Server Management Studio and execute it, or you can use SQLCMD to execute a script from command line. However, when you have large number of… Read more
When you create a new database in SQL Server without explicitly specifying database file locations, SQL Server created files in default location. This default location is configured when installing SQL Server.
If you need to change this default location once SQL Server is installed, you can change this in server… Read more
You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. You can also perform these operation using command line in Windows. For this you must run command prompt with Administrative privileges.
Method 1: Start/Stop SQL Server using NET command:
You can start/stop SQL Server from command… Read more
To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.
Method 1: To rename an object using SQL Server Management Studio:
Step 1. Right Click on the object in Object Explorer and select… Read more
Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.
You can find the creation time of database using sp_hepldb system procedure.
EXEC sp_helpdb 'SqlAndMe'
This will return database creation time along with other… Read more
SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is… Read more
Earlier on my blog I posted about how you can attach a database using T-SQL when no log file is available. You can catch that here. In this post we will see how it can be attached using GUI.
To attach a database with no .ldf file, follow below… Read more
In SQL Server you can enable a Trace Flag at session (effective for current session only) level and global level. If you are not sure which Trace Flags have been enabled you can use DBCC TRACESTATUS() command to get a list of enabled Trace Flags.
You can use WITH CHECKSUM option to perform checksum when backup is created. When used this verifies each page for checksum and torn page.
You can use it in a BACKUP command as below:
BACKUP DATABASE [SqlAndMe]
TO DISK = N'C:\SqlAndMe.bak'
Once the backup is created… Read more
Each login in SQL Server has a default database associated with it. When you login to SQL Server context is set to default database for login. This is set when login is created and it can be changed by using ALTER LOGIN command.
If for some reason the default… Read more
You can avoid executing a function when NULL values are passed as parameters. To achieve this you need to create function with RETURNS NULL ON NULL INPUT, this option introduced in SQL Server 2005.
You can use this while creating a function as below:
CREATE Read more
Similarities between DDL and DML triggers:
1. Both are created using similar T-SQL syntax
2. Both can run .NET code
3. You can create multiple DDL and/or DML triggers on an… Read more
To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.
To Open Log File Viewer,
1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History"… Read more
SQL Server stores all mails and attachments in msdb database. To avoid unnecessary growth of msdb database you should remove these mail history unless it is required for auditing or other purposes.
To check all mails processed by Database Mail, you can use sysmail_allitems catalog view:
SELECT COUNT(*) Read more
Generally it is considered good practice using two-part names for objects. It make the code more readable and avoids confusion if objects with similar names exists in different schemas. Some features requires that two-part naming must be used such as creating a view WITH SCHEMABINDING.
However, you can also… Read more
When connecting to a server from SSMS using Windows Authentication, you are limited to using only user which you used to log on to Windows. For example, if you login using User1 on Windows and launch SSMS, you can only use User1 to connect to a server from SSMS as… Read more