All Articles - SQL Authority with Pinal Dave

SQL SERVER – 2005 Enable CLR using T-SQL script

May 5, 2007

CLR, SQL Advanced Option, SQL Scripts, SQL Server Security

Before doing any .Net coding in SQL Server you must enable the CLR. In SQL Server 2005, the CLR is OFF by default. This is done in an effort to limit security vulnerabilities. Following is the script which will enable CLR. EXEC sp_CONFIGURE 'show advanced options' , '1'; GO RECONFIGURE;…

SQL SERVER – UDF – User Defined Function to Find Weekdays Between Two Dates

May 5, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL DateTime, SQL Function, SQL Scripts, SQL Utility

Following user defined function returns number of weekdays between two dates specified. This function excludes the dates which are passed as input params. It excludes Saturday and Sunday as they are weekends. I always had this function with for reference but after some research I found original source website of…

SQL SERVER – Fix : Error : Msg 7311, Level 16, State 2, Line 1 Cannot obtain the schema rowset DBSCHEMA_TABLES_INFO for OLE DB provider SQLNCLI for linked server LinkedServerName

May 4, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Error Messages, SQL Scripts, SQL Server Security, sqlcmd

You may receive an error message when you try to run distributed queries from a 64-bit SQL Server 2005 client to a linked 32-bit SQL Server 2000 server or to a linked SQL Server 7.0 server. Error: The stored procedure required to complete this operation could not be found on…

SQL SERVER – Download SQL Server Management Studio Keyboard Shortcuts (SSMS Shortcuts)

May 4, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Download, SQL Shortcut

Download SQL Server Management Studio Keyboard Shortcuts I have received many emails appreciating my article Query Analyzer Shortcuts and requesting same for SQL Server Management Studio Keyboard Shortcuts. I see frequent downloads of the PDF generated by SQLAuthority for the same on server. There is original article on MSDN site.…

SQL SERVER – DBCC Commands to Free SQL Server Memory Caches

May 3, 2007

Pinal Dave

SQL Tips and Tricks

SQL Memory, SQL Scripts, SQL Server, SQL Server DBCC, SQL Stored Procedure

Lots of people do not know that following command can be very helpful to clear your memory caches of SQL Server. I have often seen people restarting their entire system to clear the memory caches.

SQL SERVER – FIX : ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup

May 2, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Backup and Restore, SQL Data Storage, SQL Error Messages

ERROR 1101 : Could not allocate a new page for database because of insufficient disk space in filegroup . Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. Fix/Workaround/Solution: Make sure there is…

SQL SERVER – 2005 TOP Improvements/Enhancements

May 2, 2007

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Scripts

SQL Server 2005 introduces two enhancements to the TOP clause. 1) User can specify an expression as an input to the TOP keyword. 2) User can use TOP in modification statements (INSERT, UPDATE, and DELETE). Explanation : User can specify an expression as an input to the TOP keyword. In…

SQL SERVER – User Defined Functions (UDF) to Reverse String – UDF_ReverseString

May 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

SQL Function, SQL Scripts, SQL String

UDF_ReverseString UDF_ReverseString User Defined Functions returns the Reversed String starting from certain position. First parameters takes the string to be reversed. Second parameters takes the position from where the string starts reversing. Script of UDF_ReverseString function to return Reverse String. CREATE FUNCTION UDF_ReverseString ( @StringToReverse VARCHAR(8000), @StartPosition INT ) RETURNS…

SQL SERVER – Copy Column Headers in Query Analyzers in Result Set

May 1, 2007

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

Best Practices

Copy Column Headers in Query Analyzers in Result Set. In Query Analyzer go to Menu >> Tools >> Options >> Results Select Default results Target: Results to Text Results output format:(*): Tab Delimited Print column headers(*): Checkbox ON(check) Reference : Pinal Dave (https://blog.sqlauthority.com)