Certification link - Microsoft

New SQL Server 2008 Exam are all set under MCTS and MCITP.

MCTS: SQL Server 2008, Business Intelligence Development and Maintenance
MCTS: SQL Server 2008, Database Development
MCTS: SQL Server 2008, Implementation and Maintenance

Please find the link :http://www.microsoft.com/learning/mcp/mcts/default.mspx

World of Business Intelligence - >>>>>

Hi all ,

Microsoft Business Intelligence Products Link:

http://www.microsoft.com/bi/products/default.aspx

1] Microsoft Office performance Point Server 2007 – BI Tool.

2] Microsoft Office SharePoint Server 2007 – Supports lot BI features .

3]SQL Server 2005/2008 – Has complete got BI Suite.


Thanks & Regards,
Naveen Mukkasa

Shrinking Transcation Log FIle - SQL Server

Different ways to Shrink file
1]
USE Database Name
GO
DBCC SHRINKFILE(logfile name, 1)
BACKUP LOG databasename WITH TRUNCATE_ONLY
DBCC SHRINKFILE(logfile_name, 1)

2]
sp_dboption [AdventureWorks], 'trunc. log on chkpt.', 'true'
dbcc shrinkfile ([AdventureWorks_Log])

3] Eliminate the log file completely
Sometimes we just do not need the big log file. For example, I have 40GB log file. I am sure I do not need this log file and want to get rid of it completely to free up the hard drive space. The logic is
a. Detach the database
b. Rename the log file
c. Attach the database without the log file
d. Delete the log file
Let’s say, the database name is testDev. In the SQL Server Management Studio,
Highlight the database-> Tasks->Detach..-> Click OK Go to log file folder -> rename the testDev_log.ldf to be like testDev_log-aa.ldf, Highlight Databases->Attach…-> Click Add -> add the database testDev, highlight the log file and click the ‘Remove’ button. This means you only attach testDev.mdf After this is done, you can verify the contents of the attached database and then delete the log file.

Good link - Script Repository

http://www.microsoft.com/technet/scriptcenter/scripts/sql/default.mspx?mfr=true

Cursor to fetch all Procedure text in Particular DB

DECLARE @name sysname, @sqlcmd NVARCHAR(400)
DECLARE mycur CURSOR for
SELECT name FROM sys.sysobjects WHERE type ='p'
OPEN mycur
FETCH NEXT FROM mycur INTO @Name
WHILE @@Fetch_Status = 0
BEGIN
SELECT @sqlcmd = N'exec sp_helptext @name'
EXECUTE sp_executesql @sqlcmd, N'@name sysname', @name
FETCH NEXT FROM mycur INTO @name
END
CLOSE mycur
DEALLOCATE mycur

SQL Cmd

SQL command syntax to generate script for store proc. c:\sql.sql is the i/p file.

sqlcmd -E -i c:\sql.sql -o c:\proc.sql

SQL Server 2005 Bug ?????????????????

Hi all ,

Found one Interesting & weird behavior in SQL Server 2005 . This might be a bug in SQL server 2005 . Thought of sharing with you please have a look at it.

Database Generation Wizard:

Right click on the database -> Tasks ->Generate Scripts -> continue clicking Next (select the DB and things…).

Stop here .. please read this msdn page http://msdn.microsoft.com/en-us/library/ms186472.aspx/ms186472.aspx

Include Descriptive Headers

When True, descriptive comments are added to the script separating the script into sections for each object. Default is False.

But according to Below diagram the default option is true ?????? This Question is to Microsoft . I had logged this Issue in Microsoft site. Waiting
For their reply, also this might have fixed in Latest service pack (Have to check this).











Because of this SQL Server behaves differently when it is true/false . Say if I Generate scripts 4 times to a file, SQL will create the same Store Proc 4 times in the file.
Change the option [Include Descriptive Headers ] to false continue taking the script for 4 times you will find the same proc 4 times in the scripts while if you set this option to true as shown aboveIt will overwrite the previous script without any duplication

DB Back-Up Script

Below is the script to back up the databases..

DECLARE @DBname VARCHAR(50) -- database name
DECLARE @Filepath VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
SET @Filepath = 'D:\Backup\'
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('Naveen','Ki','msdb','Chan')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @Filepath + @DBname + '_' + @fileDate + '.BAK'
BACKUP DATABASE @DBname TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @DBname
END
CLOSE db_cursor
DEALLOCATE db_cursor

Retrieve all Stro Proc Names

Syntax:
SELECT * from SYS.Procedures

Hi all

Hi Guys,
This is space for all those who Learn & Master Database .Lets share our ideas and knowledge.
Keep visiting this blog for more Database related Information (MS SQL,ORACLE)

Nav