Database Overwritten: statement

Showing posts with label statement. Show all posts

Monday, March 19, 2012

Database master key

For encytion in database, the following statement is correct?

I found it in msdn book.

The database master key is not mandatory if you want to encrypt data.

I think the SQL Server Security forum is more appropriate for your question.

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

Yes that is correct. The database master key is not required for data encryption.

Sung

|||

When I tried creating certificate without having database master key, it complained.

After I created, it worked fine.

|||

If you don't have a database master key, you will need to specify password encryption. You can use the following to create the certificate:

create certificate <cert_name> encryption by password = '<your_password>' with subject = '<subject>'

For symmetric keys, asymmetric keys, and certificates, you can always optionally specify and use a password for encryption instead of using the encryption hierarchy. Please check BOL for the proper syntax. They should be similar to " ENCRYPTION BY PASSWORD = '<password>' ". This is very useful for restricting access to this data from database owner (who otherwise automatically has rights to the database master key).

We also have two built-ins that allow you to completely bypass using keys: EncryptByPassPhrase and DecryptByPassPhrase. This is NOT the recommended method for encrypting data, but it may be useful depending on your use scenarios.

Hope this helps, please let me know if you have any further questions,

Sung

Saturday, February 25, 2012

Database Mail question?

Hello All!

So I learned this cool thing today called Database Mail. I have a statement that will allow me to perform a query, and send a email with the results in a table form in a HTML format. What I would like to do is place more than one table. I have several tables to send, I don't want to send 5 emails. I want to send 1 email with 5 tables in it. Here some code!

TIA!

Rudy

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

YOu can just combine them. You can also use UNION/All to combine them into a single query.

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

|||

Thanks Oj! It worked great!

Rudy

Database Mail question?

Hello All!

TIA!

Rudy

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired.</H1><body bgcolor=White><table border = 2><tr><th>I-9</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

YOu can just combine them. You can also use UNION/All to combine them into a single query.

DECLARE @.xml NVARCHAR(MAX)DECLARE @.body NVARCHAR(MAX)

BEGIN

SET @.xml =CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Active_Orders WHERE (Service_Date_Time <= dateadd( Month, -9, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

+CAST(( SELECT Service_Date_Time AS 'td'

FROM [Pharm Test Local].dbo.Archive_Orders WHERE (Service_Date_Time <= dateadd( Month, -2, getdate())) GROUP BY Service_Date_Time FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @.body ='<html><H1>Records that have expired1.</H1><body bgcolor=White><table border = 2><tr><th>Workers Comp</th></tr>' SET @.body = @.body + @.xml +'</table></body></html>'

EXEC msdb.dbo.sp_send_dbmail

@.recipients=N'jrudolf@.ikon.com',

@.body= @.body,

@.body_format = 'HTML',

@.Subject='HR Records to delete',

@.profile_name = 'Ikon'

end

|||

Thanks Oj! It worked great!

Rudy

Monday, March 19, 2012

Database master key

Saturday, February 25, 2012

Database Mail question?

Database Mail question?

Database Overwritten

Blog Archive

About Me