Advertisements

Archive

Archive for September, 2011

SQL Query for truncate all tables in a database in SQL/SQL Azure

September 26, 2011 2 comments

How to delete or truncate all the data from all the tables in a database

 For clean up the database or switch to a new database, we may need to truncate or delete all tables in the database. Its not easy to select and delete all tables in a database as the database may having n number of tables. We can get all tables in a database from sys objects and we can apply delete or truncate script to each and every table from a single cursor.

 SQL script for delete/truncate all tables in a database in SQL/SQL Azure

 The following script truncates all tables in the selected database. If you want to delete all tables in the database we can edit this query with delete statement instead of truncate statement.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('U')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN
     Set @StringVal = 'truncate table ' + @DBObjName
       exec (@StringVal)           
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName

 How to drop all stored procedures (sps) and functions from a database

The following script drop all procedures and user defined functions from a database.

 DECLARE @GetObjectName AS CURSOR 
DECLARE @StringVal AS nvarchar(max) 
DECLARE @DBObjName AS nvarchar(100)
DECLARE @Type as Varchar(2)

SET @GetObjectName = CURSOR FOR 
Select type,Name from sys.sysobjects where type in('P','FN','TF')
AND Name not in ('DBM_EnableDisableAllTableConstraints')
OPEN @GetObjectName 
FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName

WHILE @@FETCH_STATUS = 0 
BEGIN

      IF @Type='P'
      BEGIN
            Set @StringVal = 'Drop Procedure ' + @DBObjName
            exec (@StringVal)
      END
      ELSE IF @Type='FN' OR @Type='TF'
      BEGIN
            Set @StringVal = 'Drop Function ' + @DBObjName
            exec (@StringVal)
      END

FETCH NEXT 
FROM @GetObjectName INTO @Type,@DBObjName
END 
CLOSE @GetObjectName 
DEALLOCATE @GetObjectName
Advertisements

How to find the size and cost of sql azure database

September 15, 2011 Leave a comment

SQL query to find the cost and size of sql azure database

 SQL  Azure database is a paid service as per the usage of the database. So we should have an idea regarding our usage of data in the sql azure database. Then only we can use it as cost effective product. So we need to regularly checking the size and cost of our sql azure database.

 Query to find the size and cost of SQL azure entire database

 The  following query will return the size and cost of the sql azure database.  The latest price of the azure storage can be seen here. http://www.microsoft.com/windowsazure/pricing/

 The following query will give the size and price of the database as per the following price:

 

DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

SELECT    (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes
 

Query to find size and cost of SQL Azure datbase as per indexes

 The following query will return the size and cost of sql azure dtabase’s indexes.

 DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats

DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )

DECLARE @CostPerByte float

SELECT    @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes SELECT idx.name,
SUM(reserved_page_count) * 8192 'bytes',
      (SUM(reserved_page_count) * 8192) * @CostPerByte 'cost'
FROM sys.dm_db_partition_stats AS ps
    INNER JOIN sys.indexes AS idx ON
idx.object_id = ps.object_id AND idx.index_id = ps.index_id
WHERE type_desc = 'NONCLUSTERED'
GROUP BY idx.name
ORDER BY 3 DESC
 

Query to find out the size and cost of each tables in SQL Azure database

 The following query return the cost per month per row for every table in the database.
DECLARE @SizeInBytes bigint
SELECT @SizeInBytes =
(SUM(reserved_page_count) * 8192)
    FROM sys.dm_db_partition_stats
DECLARE @Edition sql_variant
SELECT  @Edition =DATABASEPROPERTYEX ( DB_Name() , 'Edition' )
DECLARE @CostPerByte float
SELECT    @CostPerByte = (CASE
    WHEN @SizeInBytes/1073741824.0 < 1 THEN
(CASE @Edition WHEN 'Web' THEN 9.99 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 5 THEN
(CASE @Edition WHEN 'Web' THEN 49.95 ELSE 99.99 END)
    WHEN @SizeInBytes/1073741824.0 < 10 THEN 99.99 
    WHEN @SizeInBytes/1073741824.0 < 20 THEN 199.98
    WHEN @SizeInBytes/1073741824.0 < 30 THEN 299.97            
    WHEN @SizeInBytes/1073741824.0 < 40 THEN 399.96             
    WHEN @SizeInBytes/1073741824.0 < 50 THEN 499.95            
         END)  / @SizeInBytes
SELECT    
      sys.objects.name,
      sum(reserved_page_count) * 8192 'Bytes',
      row_count 'Row Count', 
      (CASE row_count WHEN 0 THEN 0 ELSE
       (sum(reserved_page_count) * 8192)/ row_count END)
        'Bytes Per Row',
      (CASE row_count WHEN 0 THEN 0 ELSE
       ((sum(reserved_page_count) * 8192)/ row_count)
        * @CostPerByte END)
        'Monthly Cost Per Row'
FROM    
      sys.dm_db_partition_stats, sys.objects
WHERE    
      sys.dm_db_partition_stats.object_id = sys.objects.object_id
GROUP BY sys.objects.name, row_count

How to create personal folder in outlook 2007/2010

September 14, 2011 1 comment

How to resolve outlook mailbox limit exceed issue?

 Commonly, if we are using OutLook 2007/2010 for manipulating our emails, it will gradually increasing the size of the server that stored our mails such as inbox,sent items, outbox etc.. If we are not care about this, outlook will throw error message like mailbox is full and cannot receive or send mail after this issue. To resolve this ‘mailbox limit full’ issue, we have to delete mails from server or archive it to the local drive.

To achieve this, Outlook providing an option to create personal folders in local disk and we can move mails from outlook server to this personal folder. Then mails are in our local disk and we can resolve the issue

Steps to create personal folder in the Outlook 2007/2010

 

  • On the File menu, point to New, and then click Outlook Data File.

 

  • To create a Microsoft Outlook Personal Folders file (.pst) that offers greater storage capacity for items and folders and supports multilingual Unicode (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) data, click OK.

For compatibility with earlier versions of Outlook, under Types of storage, click Microsoft Outlook 97-2002 Personal Folders File (.pst), and then click OK.

  • In the File name box, type a name for the file, and then click OK.

 

  • In the Name box, type a display name for the .pst folder.

 

  • Select any other options you want, and then click OK.

 

  • Drag any item from your current folders to the new folder. Press CTRL while dragging to copy items instead of moving them.

 

 

 Password Protection for personal folder

If you are creating a .pst file, you can add a password of up to 15 characters.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don’t mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

If you select the Save this password in your password list check box, make a note of the password in case you need to open the .pst on another computer. Select this check box only if your Microsoft Windows user account is password-protected and no one else has access to your computer account.

How to setup Godaddy domain for Microsoft Outlook 2007 in Windows 7 system

September 8, 2011 11 comments

Setup your Godaddy Email in Microsft OutLook

Some people are facing some issues is setup Outlook 2007 in Windows 7 machine for GoDaddy domain.  We are demonstrating here that steps by step process to setup Outlokk 2007 in Windows 7 system with screenshots.

Steps to setup Microsoft Outlook 2007 in Windows 7 for Godaddy domain

  1. Install Microsoft OutLook 2007
  2. Go to Tools > Account Settings and click new icon.
  3. Select First option (Microsoft Exchange,pop3.. ) and click next
  4. In the next screen you can fill your name, email id for user information
  5. For server information select pop3 as account type, pop.secureserver.net as incoming mail server and smtpout.secureserver.net as outgoing mail server
  6. For Logon Information please provide your domain emailed as username and also provide password for the same.
  7. After that you have to go to more settings… options
  8. In the first tab (General tab) please enter your domain emaild like info@example.com.
  9. In Outgoing Server tab, tick first checkbox (My outgoing server requires authentication) and also tick the option “Use same setting as my incoming mail server”.                                                                                                                                                 
  10. In the “connection” tab select option “connect using my local area network(LAN)”
  11. Advanced Tab : Enter 110 as Incoming server(pop3) and outgoing server as 465. And also select ssl for type of encryption.                                                                                                                                                                                                                                                                                
  12. After that we can test connection by clicking “Test Account Settings..” button. If the connection is fine, it will be shown as completed status for both send mail and receive mail.                                                                                                                                       

How to implement leading zero to the variable in the SQL Server/Azure

September 7, 2011 1 comment

Leading zero to the variable in SQL Server

To implement leading zero to the variable in SQL Server 2008, we can use following script. In some scenario we have to leading zero to the selected values from SQL server table as per the number of digits in the selected value.

 Create a table in SQL Server  

CREATE TABLE Employee(Code INT);

We can insert values in to the age column of the Employee table
    INSERT Employee VALUES(’13’);
    INSERT Employee VALUES(‘111′);
    INSERT Employee VALUES(’12’);
    INSERT Employee VALUES(‘322’);
    INSERT Employee VALUES(‘422’);

1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.
1 row(s) affected.

 Select Code from Employee table

 SELECT * FROM Employee;

 Code
13
111
12
322
422

5 row(s) affected.

 How to Format column value foor leading zero in SQL Server

Now we are going to implement leading zero to each code up to the 4 digits.

 SELECT RIGHT('0000'+ CONVERT(VARCHAR,Code),4) AS EmployeeCode FROM Employee;

 EmployeeCode
0013
0111
0012
0322
0422

 If we want to format with 6 digits can use following scripts

SELECT RIGHT('000000'+ CONVERT(VARCHAR,Code),6) AS EmployeeCode FROM Employee;
%d bloggers like this: