SQL Server 2008

[wire]

---

...nice nice bro. Please post ari ang link sa codes arun ako ma-test para SQL to excel.

free ra na bro... e enable ang OLE AUTOMATION PROCEDURES og xp_cmdshell human padagana dayon ang create table og kato stored proc.

naa naka code mo generate og excel? attach sa email. naa ko 2 ka sql script unsaon pag generate og excel file. if wala ka ako e post diri...

sauna kay mostly naka excel man ang report ako himoon.then pag balhin nako diri kay dili man mahimo og mag excel kay naa man template. naa sad mga graph og formula.ako ge buhat karon diri kay generate og report pero wla na naka excel file . naka table na cya kay sauna limited ra man ang nvarchar(900).karon naa naman nvarchar(max) sa 2008.di na ma putol ako report. d na sad ko ma hasol generate og excel then save sa directory nya e attach dayon sa email. If you want tagaan tika sample.

share pud nya bro unsa na imo gepang himo. As of now, ako ge balik tuon ang SQL Reporting Services. 4 years nako la ka gamit. kalimot nako.

[Deadstring67]

Mao ni bro. ni work na cya sa 2000 and 2005. dont know sa 2008. la nako nag try, then sa last nga ge post nako naa man to how to email.


SQL Database Mail - Send Emails with Attachment

EXEC sp_send_dbmail
@recipients='wire@istorya.net;deadstring67@istorya .net',
@subject='Test Attachment',
@body='An attachement has been included in this email.',
@file_attachments= 'C:\Istorya.xls;C:\Istorya2.xls'

1.

--This will create a stored procedure that will trigger to Create Excel spreadsheets via ADODB

CREATE PROCEDURE spExecute_ADODB_SQL
@DDL VARCHAR(2000),
@DataSource VARCHAR(100),
@Worksheet VARCHAR(100)=NULL,
@ConnectionString VARCHAR(255)
= 'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=%DataSource;
Extended Properties=Excel 8.0'
AS
DECLARE
@objExcel INT,
@hr INT,
@command VARCHAR(255),
@strErrorMessage VARCHAR(255),
@objErrorObject INT,
@objConnection INT,
@bucket INT

SELECT @ConnectionString
=REPLACE (@ConnectionString, '%DataSource', @DataSource)
IF @Worksheet IS NOT NULL
SELECT @DDL=REPLACE(@DDL,'%worksheet',@Worksheet)

SELECT @strErrorMessage='Making ADODB connection ',
@objErrorObject=NULL
EXEC @hr=sp_OACreate 'ADODB.Connection', @objconnection OUT
IF @hr=0
SELECT @strErrorMessage='Assigning ConnectionString property "'
+ @ConnectionString + '"',
@objErrorObject=@objconnection
IF @hr=0 EXEC @hr=sp_OASetProperty @objconnection,
'ConnectionString', @ConnectionString
IF @hr=0 SELECT @strErrorMessage
='Opening Connection to XLS, for file Create or Append'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Open'
IF @hr=0 SELECT @strErrorMessage
='Executing DDL "'+@DDL+'"'
IF @hr=0 EXEC @hr=sp_OAMethod @objconnection, 'Execute',
@Bucket out , @DDL
IF @hr<>0
BEGIN
DECLARE
@Source VARCHAR(255),
@Description VARCHAR(255),
@Helpfile VARCHAR(255),
@HelpID INT

EXECUTE sp_OAGetErrorInfo @objErrorObject, @source output,
@Description output,@Helpfile output,@HelpID output
SELECT @strErrorMessage='Error whilst '
+COALESCE(@strErrorMessage,'doing something')+', '
+COALESCE(@Description,'')
RAISERROR (@strErrorMessage,16,1)
END
EXEC @hr=sp_OADestroy @objconnection
GO

---------------------------------------------------------------------------------------------------------

spExecute_ADODB_SQL @DDL='drop table Istoryan',
@DataSource ='c:\Istorya.xls'
----------------------------------------------------------------------------
--the excel file will have been created on the Database server of the
-- database you currently have a connection to
spExecute_ADODB_SQL @DDL='Create table Istoryan(membername Text, Gender Text, Status Text)',
@DataSource ='C:\Istorya.xls'
---------------------------------------------------------------------------------------------------------
--create a temporary table #tabletemp and putting all the data of istorya_member_table Table
select membername, Gender,[Status] into #tabletemp from istorya_member_table

--insert all the data in the temporary table #tabletemp to Istorya.xls
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Istorya.xls;',
'SELECT * FROM [Istoryan]') select * from #tabletemp

--drop the temporary table
drop table #tabletemp
---------------------------------------------------------------------

----EXCEL TO SQL
--
drop table #tabletemp
select *
into #tabletemp FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\Istorya.xls;HDR=YES',
'SELECT * FROM [Istoryan]')
select * from #tabletemp

--do the voodoo
-- use insert having the value of #tabletemp

--------------------------------------------------------------------------------------------------


2.
DECLARE @FilenameNAME VARCHAR(100)
DECLARE @QUERY VARCHAR (100)
DECLARE @SubjectTitle VARCHAR (100)

SET @SubjectTitle = 'SQL to EXCEL'

SET @FilenameNAME = 'C:\Istorya2.xls'

SET @QUERY ='select membername, Gender,[Status] into from istorya_member_table'

EXEC sp_makewebtask
@OUTPUTFILE = @FNAME,
@QUERY = @QUERY,
@colheaders =1,
@FixedFont=0,@lastupdated=0,@resultstitle= @SubjectTitle

[digitalsuperman]

sad to say wala nay Profiler sa SQL 2012 gihimo na nilang extended events which is lahi ra kaayo dagway.

[wire]

...nice bro, super-comprehensive with snapshot... I have lot of things had learned on you bro... Before, If I focus on this stuff I think (hehehee) I am a super-savvy on this thing like you hehehe...

...Good Job bro!

How to Configure the Global Profile in Database Mail

Method 1: Configure a Default or Global Database Mail profile with the Wizard

1. Connect to the target SQL Server with SSMS (SQL Server Management Studio)
2. Expand the Management section
3. Right-click on Database mail and select Configure Database Mail



4. From the Wizard, click on Next >


5. From the Task screen, select Manage Profile Security and click on Next >


6. From the Manage Profile Security screen, select the target profile, then click in the Default Profile tab to activate the drop-down.
Select Yes fromt the drop-down then click on Next >


7. From the Complete the Wizard screen, click on Finish



Method 2: Configure a Default or Global Database Mail profile with [sysmail_update_principalprofile_sp]

1. To display or verify the status Database Mail profiles, execute the following stored procedure from within SSMS:
exec msdb.dbo.sysmail_help_principalprofile_sp
As you can see, the is_default for all the profiles is set to 0. This means that NONE of the profiles is set as the default or global profile.


2. To configure a profile as the default/global one, execute the following stored procdure, specifying the appropriate parameters:



3.You can now verify that the profile has been configured as the Global or Default Database Mail Profile


Source:
How to Configure the Global Profile in Database Mail

[Deadstring67]

...nice bro, super-comprehensive with snapshot... I have lot of things had learned on you bro... Before, If I focus on this stuff I think (hehehee) I am a super-savvy on this thing like you hehehe...

...Good Job bro!

thanks bro... share2x lang gud ta og mga na maohan. daghan pa ko lacking sa ako sql programming og admin. mao na ako ako target next year.

[Deadstring67]

How to configure SQL Reporting Services

click to download the video

Source:
10 Minutes to configure Reporting Services 2008 | Vidhya Sagar – Blog

[Deadstring67]

Creating Basic Reports from an SQL Server Analysis Services Cube

[Deadstring67]

Database Mirroring Advantages & Disadvantages

Advantages Database Mirroring:

1.Database Mirroring architecture is more robust and efficient than Database Log Shipping. It can be configured to replicate the changes synchronously to minimized data loss.
2.It has automatic server failover and client failover mechanism.
3.Configuration is simpler than log shipping and replication, and has built-in network encryption support (AES algorithm).
4.Because propagation can be done asynchronously, it requires less bandwidth than synchronous method (e.g. host-based replication, clustering) and is not limited by geographical distance with current technology.
5.Database mirroring supports full-text catalogs.
6.Does not require special hardware (such as shared storage, heart-beat connection) and clusterware, thus potentially has lower infrastructure cost
Disadvantages of Database Mirroring:

1.Potential data lost is possible in asynchronous operation mode. RTO will vary and depend on several factors, such as propagation interval time and bandwidth speed.
2.Mirror server/database is not available for user operation.
3.It only works at database level and not at server level. It only propagates changes at database level, no server level objects, such as logins and fixed server role membership, can be propagated.
4.Automatic server failover may not be suitable for application using multiple databases.

[Deadstring67]

Script to Check SQL Server Connectivity

Use this VB script to check SQL Server connectivity. You can add N number of SQL Server instances to verify it’s status.


Usage:

1.Download the sqlserverconnectivity.zip file and extract all the files to a folder
2.Make sure connectivity.vbs, serverlist.txt and model.xls files exists in the same folder
3.Open serverlist.txt file and add server name in each line.
4.Execute connectivity.vbs file, it will generate a new excel file with the status
5.In the excel created you can get the status in “ServerStatus” sheet and connectivity error details in “Error” sheet.

Success Report



Failure Report

[Deadstring67]

Script to Retrieve Security Information – SQL Server 2005 and above


Works With

SQL Server 2005
SQL Server 2008 and
SQL Server 2008 R2

Script to find server level logins and role assigned

SELECT a.name as LoginName,a.type_desc AS LoginType, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
WHEN b.securityadmin=1 THEN 'securityadmin'
WHEN b.serveradmin=1 THEN 'serveradmin'
WHEN b.setupadmin=1 THEN 'setupadmin'
WHEN b.processadmin=1 THEN 'processadmin'
WHEN b.diskadmin=1 THEN 'diskadmin'
WHEN b.dbcreator=1 THEN 'dbcreator'
WHEN b.bulkadmin=1 THEN 'bulkadmin'
ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a JOIN master..syslogins b ON a.sid=b.sid WHERE a.type <> 'R' AND a.name NOT LIKE '##%'

Script to find database users and roles assigned

DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY DBName

Script to find Object level permission for user databases

DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object, permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table