Monday, November 29, 2010

Sql Server 2008 : Generate Data Script


SQL SERVER 2008: HOW  TO CREATE TABLE DATA SCRIPT ???

(NOTE: There is no such facility in Sql Server 2005. But still you can create table data script http://connectsql.blogspot.com/2009/09/generate-tables-data-script.html)

Sql Server Management Studio 2008 provides easy approach to generate your data script. The resultant script will be in INSERT INTO statements format.


To achieve our goal, first right click on your desired database; select “Tasks”, then select “Generate Scripts …”

Click on your desired database name from given list and click “Next” button.

Next options page is import. Turn off all options except following and then click “Next” button
“Schema qualify object name” (for two part name i.e. schemaname0.tablename),
“Script USE DATABASE” (I will give an additional line of USE YourDatabaseName before your data script)
“Script Data” (To generate data script only)

Turn on check box of “tables” and click “Next” button
 
 
Select your desired tables or you can select all tables by click on SELECT ALL button, given on bottom of the page. Click “Next” button twice to continue.

Let it work till it generates success message. Click “Close” button to complete.








Friday, November 26, 2010

Sql Server Management Studio: Select /Edit Top N Rows

Like many useful enhancements in Sql Server Management Studio 2008, "Select Top N Rows" and "Edit Top N Rows" options are added. Now you can execute select (from your desired table)query by just right click on desired table and click "SELECT TOP 1000 ROWS". Or you can edit top 200 number of row by clicking on "SELECT TOP 200 ROWS".


Question arises, How to change default number of rows i.e. 1000 for select and 200 for edit. Simple:

Select TOOLS from your main menu and click OPTIONS, move cursor to SQL SERVER OBJECT EXPLORER


By changing values for "Value for Edit top rows command" or "Value for Select top rows command", to your desired one, will change your default settings.

Keep in mind that setting value 0 (ZERO) for both options mean ALL TABLE ROWS.

Wednesday, November 24, 2010

Invalid use of a side-effecting operator 'OPEN MASTER KEY' within a function

Recently i have tried to create a simple function which should take a single nvarchar parameter as input and should return me its varbinary version, after encryption.When i executed my script, it failed with an error:


Msg 443, Level 16, State 14, Procedure fnc_EncryptNVarcharData, Line 7
Invalid use of a side-effecting operator 'OPEN MASTER KEY' within a function.
Msg 443, Level 16, State 14, Procedure fnc_EncryptNVarcharData, Line 8
Invalid use of a side-effecting operator 'OPEN SYMMETRIC KEY' within a function.
Following is the script, which i had used.
CREATE FUNCTION [dbo].[fnc_EncryptNVarcharData] ( @value nvarchar(2000) )
RETURNS VARBINARY(2000)
AS BEGIN

    DECLARE @EncryptedValue VARBINARY(2000)
  
    OPEN MASTER KEY DECRYPTION BY PASSWORD = 'iwillwin'   
    OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert   
    DECLARE @KeyGuid AS UNIQUEIDENTIFIER   
    SET @KeyGuid = key_guid('UserKey')   
   
    SELECT  @EncryptedValue = encryptByKey(Key_GUID('UserKey'), @value)
   
    RETURN ( @EncryptedValue )
   
   END
 Problem occurs when you use OPEN MASTER KEY or OPEN SYMMETRIC KEY inside your function.
Solution is very simple.Remove these problematic lines from your function and write before using this function.

New script for this function would be like this


CREATE FUNCTION [dbo].[fnc_EncryptNVarcharData] ( @value nvarchar(2000) )
RETURNS VARBINARY(2000)
AS BEGIN

    DECLARE @EncryptedValue VARBINARY(2000)
  
    --OPEN MASTER KEY DECRYPTION BY PASSWORD = 'iwillwin'   
    --OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert   
    DECLARE @KeyGuid AS UNIQUEIDENTIFIER   
    SET @KeyGuid = key_guid('UserKey')   
   
    SELECT  @EncryptedValue = encryptByKey(Key_GUID('UserKey'), @value)
   
    RETURN ( @EncryptedValue )
   
   END

And now when i like to use this function, i will mention OPEN MASTER KEY and OPEN SYMMETRIC KEY statements, first.


OPEN MASTER KEY DECRYPTION BY PASSWORD = 'iwillwin'   
OPEN SYMMETRIC KEY UserKey DECRYPTION BY CERTIFICATE Usercert
   
SELECT  CONVERT(nvarchar(200), DECRYPTBYKEYAUTOCERT(CERT_ID('Usercert'), NULL,                                               [dbo].[fnc_encryptNVarchardata](N'ConnectSql')))


Thursday, November 11, 2010

Get Tables List With Dependent Tables Name

Following script help you create all of your database tables list along with their dependent tables name in comma separated format.




DECLARE @mastertable VARCHAR(100)
DECLARE @TableCompleteName VARCHAR(100)
DECLARE @tablesname VARCHAR(1000)

CREATE TABLE #temptable
    (
      tablecompletename VARCHAR(100),
      tablename VARCHAR(1000)
    )


DECLARE tmp_cur CURSOR static
    FOR SELECT  s.name + '.' + o.name,
                o.name
        FROM    sys.objects o
                INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
        WHERE   type = 'U'
        ORDER BY s.name,
                o.name
     
OPEN tmp_cur
      --FETCH
     
FETCH FIRST FROM tmp_cur INTO @TableCompleteName, @mastertable
WHILE @@FETCH_STATUS = 0
    BEGIN


        SELECT  @tablesname = COALESCE(@tablesname + ',', '') + s.name + '.'
                + OBJECT_NAME(FKEYID)
        FROM    SYSFOREIGNKEYS
                INNER JOIN sys.objects o ON o.object_id = SYSFOREIGNKEYS.fkeyid
                INNER JOIN sys.schemas s ON s.schema_id = o.schema_id
        WHERE   OBJECT_NAME(RKEYID) = @mastertable
                 
        INSERT  INTO #temptable
                (
                  tablecompletename,
                  tablename
                )
                SELECT  @TableCompleteName,
                        COALESCE(@tablesname, '')
        SELECT  @tablesname = NULL
        FETCH NEXT FROM tmp_cur INTO @TableCompleteName, @mastertable
    END
   
SELECT  tablecompletename AS TableName, tablename AS DependentTables
FROM    #temptable

DROP TABLE #temptable

CLOSE tmp_cur
DEALLOCATE tmp_cur

Friday, November 5, 2010

Database Backups History

-- Following is a simplest way to get backups history for required database
DECLARE @db_name VARCHAR(100)
SELECT @db_name = DB_NAME()



SELECT TOP (50)
s.database_name,
m.physical_device_name,
cast(CAST(s.backup_size / 1000000 AS INT) as varchar(14))
+ ' ' + 'MB' as bkSize,
CAST(DATEDIFF(second, s.backup_start_date,
s.backup_finish_date) AS VARCHAR(4)) + ' '
+ 'Seconds' TimeTaken,
s.backup_start_date,
CAST(s.first_lsn AS varchar(50)) AS first_lsn,
CAST(s.last_lsn AS varchar(50)) AS last_lsn,
CASE s.[type]
WHEN 'D' THEN 'Full'
WHEN 'I' THEN 'Differential'
WHEN 'L' THEN 'Transaction Log'
END as BackupType,
s.server_name,
s.recovery_model
FROM msdb.dbo.backupset s
inner join msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id
WHERE s.database_name = @db_name
ORDER BY backup_start_date desc,
backup_finish_date