Monthly Archives: November 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.
>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.
>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’)))
>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
>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
Connect SQL 