Insert into Table with Default Values only

Sometimes we may need to insert a row into Table with Default Values and don't want to provide column list. Use below Query:


INSERT INTO CustomerMaster Default Values


If some columns do not have default values assigned than NULL will be inserted. And if there is a column with no Default value and has NOT NULL constraint, then Insert Statement will fail.

Pause or Resume a Database Mirroring Session

We can pause and resume any Database Mirroring any time. Pausing mirroring preserves the mirroring state. Sometime pausing is very useful to improve the performance of the principal server. Pausing sets the state of the mirroring session to SUSPENDED, and the mirror database no longer updated with the principal database. 

We should resume the mirroring session quickly because as long as the session is paused then transaction log cannot be truncated. If the session is paused for a long time the log file may be grow large causing the database unavailable.

When a session is paused the  principal database is remains available.

Steps to pause and resume a database mirroring session follow below steps:

1. In Object explorer, connect to the principal server and click the server name to expand.
   
   
2. click on the Database and select the database.
   
   
3. Now right click the database, select Tasks, and then click on Mirror. Now, it will opens the Mirroring page of the Database Properties dialog box.
   
   
4. To pause the session, click Pause.
   A Confirmation message will come, if you click Yes, the session is paused, and the button changes to Resume.
   
   For more information about the impact of pausing a session, see Pausing and Resuming Database Mirroring.
5. To resume the session, click Resume.
   
   Ref: MSDN

Number to Word Function

CREATE FUNCTION NumToWords_Ver2(@num numeric)
RETURNS varchar(1000)
AS


BEGIN
DECLARE @cNum varchar(20)
SET @cNum = @num
DECLARE @len int
SET @len = LEN(@num)
DECLARE @place int
SET @place = 0
DECLARE @digit varchar(1), @tens varchar(2)
DECLARE @res varchar(1000)
DECLARE @nullStr int
SET @res = ''
DECLARE @tblNum TABLE(Num int, NumStr varchar(20))
INSERT INTO @tblNum
SELECT 1, ' One' UNION
SELECT 2, ' Two' UNION
SELECT 3, ' Three' UNION
SELECT 4, ' Four' UNION
SELECT 5, ' Five' UNION
SELECT 6, ' Six' UNION
SELECT 7, ' Seven' UNION
SELECT 8, ' Eight' UNION
SELECT 9, ' Nine' UNION
SELECT 10, ' Ten' UNION
SELECT 11, ' Eleven' UNION
SELECT 12, ' Twelve' UNION
SELECT 13, ' Thirteen' UNION
SELECT 14, ' Fourteen' UNION
SELECT 15, ' Fifteen' UNION
SELECT 16, ' Sixteen' UNION
SELECT 17, ' Seventeen' UNION
SELECT 18, ' Eighteen' UNION
SELECT 19, ' Nineteen' UNION
SELECT 20, ' Twenty' UNION
SELECT 30, ' Thirty' UNION
SELECT 40, ' Fourty' UNION
SELECT 50, ' Fifty' UNION
SELECT 60, ' Sixty' UNION
SELECT 70, ' Seventy' UNION
SELECT 80, ' Eighty' UNION
SELECT 90, ' Ninety'
DECLARE @hundred varchar(200)
SET @hundred = ''
DECLARE @nStr varchar(20)
SET @place = @len
WHILE @place > 0


BEGIN
SET @place = @place - 1
SET @nStr = NULL
SET @digit = SUBSTRING(@cNum, @len-@place, 1)
IF (@place+1) % 3 = 1 --One's place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr
IF LEN(@hundred) > 0
SET @res = @res + @hundred + CASE @place / 3
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Qwadrillion'
WHEN 6 THEN ' Quintillion'
END
SET @hundred = ''
END
IF (@place+1) % 3 = 0 --Hundred's place
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit
IF @nStr IS NOT NULL
SET @hundred = @nStr + ' Hundred'
SET @tens = SUBSTRING(@cNum, @len-@place+1, 2)
IF LEN(@hundred) > 0 AND (@tens = '' OR @tens = '00')
SET @res = @res + @hundred + CASE (@place+1) / 3
WHEN 0 THEN ''
WHEN 1 THEN ''
WHEN 2 THEN ' Thousand'
WHEN 3 THEN ' Million'
WHEN 4 THEN ' Billion'
WHEN 5 THEN ' Trillion'
WHEN 6 THEN ' Qwadrillion'
WHEN 7 THEN ' Quintillion'
END
ELSE
SET @res = @res + @hundred
SET @hundred = ''
END
ELSE IF (@place+1) % 3 = 2 --Ten's place
BEGIN
SET @tens = SUBSTRING(@cNum, @len-@place, 2)
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @tens
IF @nStr IS NULL
BEGIN
SELECT @nStr = NumStr FROM @tblNum WHERE Num = @digit * 10
SET @digit = SUBSTRING(@cNum, @len-@place+1, 1)
SELECT @nStr = @nStr + NumStr FROM @tblNum WHERE Num = @digit


END
SET @hundred = @nStr
IF LEN(@hundred) > 0
SET @res = @res + @hundred + CASE (@place+1) / 3
WHEN 0 THEN ''
WHEN 1 THEN ' Thousand'
WHEN 2 THEN ' Million'
WHEN 3 THEN ' Billion'
WHEN 4 THEN ' Trillion'
WHEN 5 THEN ' Qwadrillion'
WHEN 6 THEN ' Quintillion'
END
SET @place = @place - 1
SET @hundred = ''
END


END
RETURN @res
END

Not associated with a trusted SQL Server connection

When you're setting up a new MS-SQL connection, you might get the following error:
Not associated with a trusted SQL Server connection


What this means is that you have a logon that was defined in MSSQL - but you also have MSSQL set to only use Windows logon accounts.


To fix this, load up the SQL management tool. Expand out the SQL Server Group. When you click on SQL Server Group in the left window, it'll show an entry in the right window. Click on that and get its properties. Under "security", set authentication to allow SQL Server logins.

Errors after Running DBCC Checkdb Command

In an MSSQL corruption problem, you might see error messages after running DBCC Checkdb command. Such situations render MDF files inaccessible, which can cause grave data loss in organization that might even halt some important operations. For recapturing proper execution of work again, you must invest in a high-performing, reliable and result-oriented MSSQL repair tool. One such tool is SysTools SQL recovery software.

MSSQL Server: MSSQL Server is a highly reliable Microsoft product. SQL (Structured Query Language) is a database Server application used by most of the organizations to manage a lot of crucial data properly. It is a useful database management tool that helps to query databases and to manage information stored in already constructed databases. The information in MSSQL Server is saved in files with .mdf file extension.
MSSQL Corruption: At times, SQL MDF files can get corrupted due to some unforeseen and uninvited reasons. At this time, the search begins for an MSSQL database repair solution.
Errors after Running DBCC Checkdb Command: When there is an MSSQL corruption problem, you might come across error messages after running DBCC Checkdb command. Some of the common error messages that emerge on your computer screen after such corruption are as follows:
• Table error: Object ID 0, index ID 0, page ID (1:105). The PageId in the page header = (0:0).
• File: , line=466 Failed Assertion = '0'.
• Table Corrupt.
• Conflict occurred in database‘db_name’, table 'table_name', column 'column_name'
• Memory or buffer error, space provided to read column is too small.
• Internal errors
Repair MSSQL Database – Bring Back Proper Functioning of Work: Such situations of MSSQL corruption render MDF files inaccessible, which can create grave data loss scenarios in the organization that might even halt some of the important operations completely. For recapturing proper execution of work again, you must invest in an MSSQL repair tool. Such corruption problems are required to be resolved instantly to carry out all operations appropriately again and this signifies the importance of SQL database repair tools.
SQL Server Database Recovery: Using a third-party software tool to repair SQL corrupted MDF files and to recover MSSQL database elements is an ideal way to approach such problematic corruption situation. This is because an outside tool would ensure a quick, efficient and professional way to repair corrupt SQL database, provided you choose a high-performing, reliable and result-oriented MSSQL restore database tool. One such tool is SysTools SQL recovery software. This software is not just efficient, well-performing, result-yielding; but also, easy-to-use at the same time. This simple and intuitive MSSQL database repair solution guarantees you high-end results and great recovery outcomes. SysTools SQL recovery software supports most SQL Server versions. So, if you are an SQL 2000 user, then this product can be an apt answer to your question – “How to repair MSSQL 2000 database?”


ArticleSource: ArticlesAlley.com