SQL Server 2008

[Deadstring67]

---

Just want to share this with you bros. Please also share some of your stuff regarding SQL Server.

Been using profiler for debugging/tracing some queries.



For more about Profiler visit http://www.techrepublic.com/article/...ofiler/5054787

Setting up SQL Server 2008 Express with Profiler

Here are the steps for these installations:

Developer or Standard Edition of SQL Server 2008 R2:

Trial Edition: Download Details - Microsoft Download Center - Microsoft® SQL Server 2008 R2 Evaluation : Trial Experience for Developers

- Click on the Eval X86 Executable link to download SQLFULL_x86_ENU.exe.

- Execute the file to extract contents to an installation directory, then run the following from an admin command prompt:

setup.exe /FEATURES=Tools /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=MSSQLSERVER /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /IACCEPTSQLSERVERLICENSETERMS

- This will install only the tools for SQL Server, including SQL Profiler.

SQL Server 2008 R2 Express Edition (database engine only):

Download Details - Microsoft Download Center - Microsoft SQL Server 2008 R2 - Express

- Execute SQLEXPR32_x86_ENU.exe, then after the main screen is shown, copy the contents of the temporary directory to an installation directory. Run the following from an admin command prompt:

setup.exe /FEATURES=SQLEngine /Q /INDICATEPROGRESS /ACTION=Install /INSTANCENAME=SQLEXPRESS /BROWSERSVCSTARTUPTYPE=Automatic /AGTSVCACCOUNT=”NT AUTHORITY\NETWORK SERVICE” /SQLSYSADMINACCOUNTS=”BUILTIN\Users” /IACCEPTSQLSERVERLICENSETERMS

- This will install the database engine and add BUILTIN\Users to the sysadmin role

The complete list of SQL Server installation commands is available here How to: Install SQL Server 2008 R2 from the Command Prompt

Source:
Setting up SQL Server 2008 Express with Profiler | Tony Sneed's Blog

[Deadstring67]

Setup Database Mail in SQL Server 2008

Configure Database Mail

1. Launch the SQL Server Management Studio.


2. Expand the Management folder, right click Database Mail and click Configure Database Mail.


3. In the Database Mail Configuration Wizard window, click Next.


4. In the Select Configuration Task page, click Next. When asked to enable the Database Mail feature click
Yes.


5. In the New Profile page, fill in the Profile name and click Add.


6. In the New Database Mail Account window, fill in your mail server details and click OK.


7. In the New Profile page, click Next.


8. In the Manage Profile Security page, click Next.


9. In the Configure System Parameters page, click Next.


10. In the Complete the Wizard page, click Finish.


11. In the Configuring page, click Close.


Testing Database Mail


1. In the SQL Server Management Studio, expand the Management folder, right click Database Mail and click Send Test E-Mail.


2. In the Send Test E-Mail window, fill in the To field with your email address and click Send Test E-Mail.


3. Check your email, you should see a new mail from Database Mail.


4. If you have not received any mail, check the Database Mail Log. Launch the SQL Server Management Studio, expand the Management folder, right click Database Mail and click View Database Mail Log.
Using Database Mail


Type the command below in a query window.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Alert',
@recipients = 'johndoe@acme.local',
@subject = 'Test Mail',
@body = 'This is a test message'

The sp_send_dbmail stored procedure has a lot more parameter but those used above should be enough to get you started.

Source:
Setup Database Mail in SQL Server 2008

[Deadstring67]

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]

I have this simple sample TSQL emailer query

e paste lang ani nga site ang query para ma format:
Instant SQL Formatter

Set nocount on

declare @ctr as int
declare @count as int
declare @mailBody as varchar(max)
declare @Istorya as varchar(max)
declare @message as varchar(max)
declare @mailSubject as varchar(500)

declare @member as varchar(50)
declare @status as varchar(50)
declare @gender as varchar(50)


SELECT @ctr=count(member)
FROM istorya_member_table
where status='NEW'

set @Istorya=''
set @message=''
set @count=0

SET @mailSubject = 'New Istoryan Member ' +CONVERT(VARCHAR(10), GETDATE()-1, 120)
SET @mailBody ='<html>
<body>
<font style="font-size: 10px; color: #000000; font-family: verdana,arial,tahoma;">
Good Day,
<br><br>

[message]

<br><br><br>
</font>
<div align="left">
<font size="1" face="tahoma">
* Dont reply to this message; it is been sent by an automated program. <br>
<hr width="80%" size="1">
</font>
</div>
</body>
</html>'


if @ctr>0
begin

set @message='<b>Please see below</b>.
<br><br>
<table border="1">
<tr>
<th>Member Name</th>
<th>Gender</th>
<th>Status</th>

</tr>
[Istorya]
</table>'



DECLARE @Status_cursor CURSOR


SET @Status_cursor = CURSOR FAST_FORWARD

FOR


SELECT member,status,gender
FROM istorya_member_table
where status='NEW'
ORDER BY member asc


OPEN @Status_cursor

FETCH NEXT FROM @Status_cursor INTO @member,@status,@gender
WHILE @@FETCH_STATUS = 0

BEGIN

set @count=@count+1

set @Istorya = @Istorya + '<tr>' +'<td>'+ @member +'</td><td> '+ @status +'</td><td>' + @gender + '</td></tr>'

FETCH NEXT FROM @Status_cursor INTO @member,@status,@gender
END


CLOSE @Status_cursor

DEALLOCATE @Status_cursor

set @mailBody = Replace(@mailBody,'[message]',@message)
set @mailBody = Replace(@mailBody,'[Istorya]',@Istorya)

--print @mailbody
end
else
begin
set @message='<b>No record for today</b>.'
set @mailBody = Replace(@mailBody,'[message]',@message)
end

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'deadstring67@istorya.net',
@body_format=html,
@body = @mailBody,
@subject = @mailSubject

[Deadstring67]

Check this site also

How to convert from string to datetime?
SQL Datetime Conversion - String Date Convert Formats - SQLUSA

SQL String Function
SQL Tutorials: SQL String Functions

[Deadstring67]

Creating a SQL Server Agent Job

Old version of SQL.. i think its SQL Server 2000 but same procedure with higher version

1. From the "SQL Server Agent" node, right click on the "Jobs" node, and select "New Job":



2. Complete the details in the "General" tab:



3. Complete the details in the "General" tab:


4.From the "Steps" tab, click "New"

5. Complete the details for this step. In this example, we are using the dtsrun utility to execute a DTS package:



6. From the "Schedules" tab, click "New Schedule"
7. Give this schedule a name and specify the schedule type:


8. If you need to set a recurring schedule, click "Change" (from the previous screen) and complete the details:


Your SQL Agent Job has now been set up and is ready to run every morning at 2am, but don't forget to check that the SQL Server Agent Service is running!.

Source: SQL Server Agent Jobs

[Deadstring67]

Modifying your SQL Agent Job

Old version of SQL.. i think its SQL Server 2000

You can view your SQL Agent Job under the "Jobs" node. If you need to modify your SQL Agent Job, you can do so by right clicking on the job, then selecting "Properties". You can also run your job (by selecting "Start Job"), view its history, disable it, and more.




By looking at the screen shot above, I can tell that the SQL Server Agent Service is not running. The icon is what gives it away. Here's what the icon should look like:

Running
Not Running

Source:
SQL Server Agent Jobs

[Deadstring67]

For XP and SQL Server Lower Version

instead of using exec msdb.dbo.sp_send_dbmail
you can also use EXEC master..xp_smtp_sendmail

Installation:
To install XPSMTP follow these instructions:
1. For SQL Server 7.0 download XPSMTP70.ZIP and unzip the files
For SQL Server 2000, download XPSMTP80.ZIP and unzip the files

2. Copy xpsmtpXX.dll into the SQL Server BINN directory. For SQL Server 7.0 copy XPSMTP70.DLL, for SQL Server 2000 copy XPSMTP80.DLL

For SQL Server 7.0 the default installation location is "C:\MSSQL7\BINN"
For SQL Server 2000 the default location is "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"

3. Register the extended stored procedure using OSQL or SQL Query Analyzer by executing:
-- SQL Server 7.0 install
exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp70.dll'
-- SQL Server 2000 install
exec sp_addextendedproc 'xp_smtp_sendmail', 'xpsmtp80.dll'

4. Grant rights to the correct set of users using OSQL or SQL Query Analyzer by executing:
grant execute on xp_smtp_sendmail to public
By default only the member of the sysadmin role have execution rights on the XP after it is being registered

Syntax:
EXEC master..xp_smtp_sendmail
@TO = 'deadstring67@istorya.net',
@from = 'mokiloo@istorya.net',
@subject = 'test email.',
@message = 'this is a test message',
@server = '10.10.10.0'

[Deadstring67]

Returning Data
Stored Procedures: Returning Data - SQLTeam.com

Date Time Conversion
SQL Datetime Conversion - String Date Convert Formats - SQLUSA

String Function
SQL Tutorials: SQL String Functions

How to rename a table or column using T-SQL
How to rename a table or column using T-SQL in Microsoft SQL | Dusty Reagan

View
SQL: VIEWS

Dynamic SQL
The curse and blessings of dynamic SQL

[nogiebear_04]

bro onsa on pag manual backu-up sa database? name sa ako database 7hsawater.bak? onsai command back-apan then e restore to other server..