wetmatter nonsense

This video steps you through the update process of applying Service
Pack 2 to SQL Server 2005 very quickly and very straightforward.

Music by Kevin MacLeod

Loading mentions Retweet

This is a quick video that illustrates the upgrade process when
performing an inline upgrade of SQL Server 2000 to SQL Server 2005.

Music by Kevin MacLeod

Loading mentions Retweet

Evaluate Microsoft solutions with VHD Test Drive Program. Microsoft
SQL Server 2005 Enterprise Edition VHD - http://bit.ly/desMXv

Loading mentions Retweet

Recently I faced an issue with reporting services 2005 when attempting to deliver subscriptions to addresses outside of the organization. Internal addresses received the email based subscription deliveries without any questions. I must have checked and re-checked the settings using RSConfigTool about million times, looking for anything I might have overlooked. The error message I received was, "The e-mail address of one or more recipients is not valid". After some research (which lead me to a lot of dead ended forums) I read the phrase "email relay", that's when the gears started spinning.

I realized that the issue had nothing to do with the configuration of SQL Server Reporting Services; rather, the SMTP server! In order for the messages to be delivered outside of the organization the Reporting Services Server needed to be authorized so-to-speak. Unfortunately I don't have access to Exchange 2003 so I cannot provide screen shots, but for 2007 all you need to do is add the server's IP Address to the SMTP server’s receiver group in the HUB Transport configs.

Then to test your subscription without tweaking the schedule execution time just run the SQL job! First find out the name of the job by using the attached sql script, then execute the sp_start_job. If you have a named instance append $instancename to all three of ReportServer occurrences within the sql script (i.e. ReportServer$InstanceName) for MSSQL 2005. I believe for MSSQL 2008 you would append _InstanceName (i.e. ReportServer_InstanceName), but I am not certain. You should get the results similar to the screen shot attached.

   

Click here to download:

sqlshots_Delivering_subscripti.zip (54 KB)

Using the script with a default instance

 SELECT	
    sj.[name] AS [Job Name], 
    c.[Name] AS [Report Name], 
    c.[Path], 
    su.Description, 
    su.EventType, 
    su.LastStatus, 
    su.LastRunTime 
 FROM msdb..sysjobs AS sj INNER JOIN ReportServer..ReportSchedule AS rs 
    ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN 
    ReportServer..Subscriptions AS su 
    ON rs.SubscriptionID = su.SubscriptionID INNER JOIN 
    ReportServer..[Catalog] c 
    ON su.Report_OID = c.ItemID 

Using the script with a named instance

 SELECT	
    sj.[name] AS [Job Name], 
    c.[Name] AS [Report Name], 
    c.[Path], 
    su.Description, 
    su.EventType, 
    su.LastStatus, 
    su.LastRunTime 
 FROM msdb..sysjobs AS sj INNER JOIN ReportServer$InstanceName..ReportSchedule AS rs 
    ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN 
    ReportServer$InstanceName..Subscriptions AS su 
    ON rs.SubscriptionID = su.SubscriptionID INNER JOIN 
    ReportServer$InstanceName..[Catalog] c 
    ON su.Report_OID = c.ItemID 

Execute the report

 USE msdb EXEC sp_start_job @job_name = 'AF015D8B-D80D-4D2A-9808-CD1D519B3332' 

Correction
In the attached file listed below I have identified a typo. Currently it states on
Line 19: sp_start_sp (which is incorrect) it should be
Line 19: sp_start_job

Click here to download:

SELECT.Get_RSSubscription_Jobs_v0.02.sql (0 KB)

Loading mentions Retweet

In this particular scenario I needed to concatenate a set of fields into a single column. So essentially I needed to trim all the leading zeros from the street number field first (via my udf_TrimLeadingZeros), then pieced the street number, pre-direction, street name and suffix all into one field. However, every row did not have a pre-direction or a suffix; nonetheless, I needed everything formatted (evenly spaced between words) and displayed as a single string.

So I figured I would use a CASE expression (http://bit.ly/PiYQF) in conjunction with a String Concatenation (http://bit.ly/FJtq9) to achieve my end result. Typically I see a period (.) appended to the pre-direction, but in this case it wasn't required. However if you do need it simply add a period to line 14 between the ticks '. ' of the attached select script (see pre-direction with periods screen shot for details).

Pre-Direction without periods

Pre-Direction with periods

Click here to download:

CREATE.udf_TrimLeadingZeros_v0.01.sql (0 KB)

Click here to download:

SELECT.Trim-Case-ConCat_v0.01.sql (0 KB)

Loading mentions Retweet

With SQL Server 2005 you can install multiple instances of certain components to run concurrently on a system. These are known as the instance aware components.

• Database Engine
• Analysis Services
• Reporting Services

Loading mentions Retweet

You are the database administrator for a shipping company named Cargoflow. You are asked to create a database for the company's marketing department for trend analysis of shipments. This database will be bulk loaded with information from a data warehouse when it is first created. Data will be analyzed but not modified in any way. You are trying to decide on an appropriate recovery model for the database. Which recovery model should you implement for this new database? Choose the best option(s) from those listed below.

a) Full recovery
b) Bulk-logged recovery
c) Simple recovery
d) Warehouse recovery

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
The simple recovery model is the most appropriate recovery model to use in this scenario. Since the data in the database will never change, point-of-failure recovery is not necessary. This means that data in the transaction log is not critical to recovering the database and does not necessitate being backed up. The simple recovery model relies strictly on full and differential backups of the database to recover.

Correct Option(s):
c) Simple recovery

Incorrect Option(s):
a) Full recovery - The full recovery model is inappropriate in this scenario due to the unnecessary administrative overhead associated with transaction log backups.
b) Bulk-logged recovery - The bulk-logged recovery model is inappropriate in this scenario due to the unnecessary administrative overhead associated with transaction log backups.
d) Warehouse recovery - SQL Server 2005 does not support a warehouse recovery model.

Questions Provided by SkillSoft

Loading mentions Retweet

You are creating a new SQL Server 2005 database for Brocadero's sales department. To ensure maximum availability and reliability you decide to implement the database across multiple data files. When creating the data files you want to follow Microsoft's recommended best practices for naming. How should the primary and secondary data files be named? Choose the best option(s) from those listed below.

a) The primary data file should have an .mdf extension.
b) The primary data file should have an .ndf extension.
c) The secondary data file should have an .mdf extension.
d) The secondary data file should have an .ndf extension.

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
Microsoft's recommended best practices state that a heavily used database should store the database catalog in a primary data file and all data and objects in secondary data files for the best performance, availability, and reliability. Microsoft recommends that primary data files use the .mdf file extension, while secondary data files should use the .ndf extension.

Correct Option(s):
a) The primary data file should have an .mdf extension.
d) The secondary data file should have an .ndf extension.

Incorrect Option(s):
b) The primary data file should have an .ndf extension - Primary data files should use the .mdf extension.
c) The secondary data file should have an .mdf extension - Secondary data files should use the .ndf extension.

Questions Provided by SkillSoft

Loading mentions Retweet

You are the SQL Server administrator for your company. You have been assigned the task of installing Microsoft SQL Server 2005 Enterprise Edition on an existing server. This server has a 600 MHz Pentium III processor, 256 MB of RAM, 10 GB hard disk and Microsoft Windows 2000 Server with Service Pack 1 installed. All of the components installed are upgradeable if required. What components must you upgrade before installing SQL Server 2005 on this server? Choose the best option(s) from those listed below.

a) Processor
b) RAM
c) Hard disk
d) Operating System

Self Evaluation:
Compare your answer to the explanation and correct option(s) provided below.

Explanation:
Before installing SQL Server 2005 Enterprise Edition on the server, you would need to upgrade to 512 MB of RAM and install Service Pack 4 or later just to meet the minimum system requirements. Microsoft recommends the following system requirements for a 32 bit system:

Processor - 600 MHz Pentium III-compatible or faster processor; 1 GHz or faster processor recommended

Operating System - Microsoft Windows 2000 Server with Service Pack (SP) 4 or later; Windows Server 2003 Standard Edition, Enterprise Edition, or Datacenter Edition with SP 1 or later; Windows Small Business Server 2003 with SP 1 or later

Memory - 512 MB of RAM or more; 1 GB or more recommended

Hard Disk - Approximately 350 MB of available hard-disk space for the recommended installation

Correct Option(s):
b) RAM
d) Operating System

Incorrect Option(s):
a) Processor - The minimum requirement for a processor is a 600 MHz Pentium III-compatible; therefore, the processor in this scenario meets the minimum requirements.

c) Hard disk - The minimum requirement for a hard disk is approximately 350 MB of available hard-disk space; therefore, the available hard disk space in this scenario exceeds requirements.

Questions Provided by SkillSoft

Loading mentions Retweet

This topic can be an ongoing argument between the parties who swear by Stored Procedures (SP) and the parties that absolutely apose them. Each perspective make valid points, but it is entirely up to you to decide. If you love them that is great and if you hate them that is great too.

I have only been using (SPs) for a short time so I cannot complain, but then again I am not an expert. I do find that (SPs) help me a lot with my daily support. So let's run through an example of creating a simple (SP) that returns a simple result set. Then we will create a slightly more advanced (SP) that requires a variable in order to return a specific result set. Again these are simple examples!

Let's just say you have a table called "tbl_Contacts" and in it you have firstName, LastName, DOB, a computed column called AGE and a IsDeleted column with a BIT data type (0 being active and 1 being deleted).

CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName, lastName, CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0) --Returns all active records

To test your freshly created (SP) run the following:
EXEC sp_GetAllActiveContacts

If you do not have a table called tbl_Contacts don't worry just use the examples I posted below!

-- [ CREATE TABLE ]
CREATE TABLE [dbo].[tbl_Contacts]
(
 [ContactID] [int] NOT NULL,
 [firstName] [nvarchar](50) NOT NULL,
 [lastName] [nvarchar](50) NOT NULL,
 [IsDeleted] [bit] NULL CONSTRAINT [DF_tbl_Contacts_conDeleted] DEFAULT ((0)),
 [createDate] [datetime] NULL CONSTRAINT [DF_tbl_Contacts_conCreateDate] DEFAULT (getdate()),
 [DOB] [smalldatetime] NULL,
 [Age] AS (datediff(year,[DOB],getdate()))
)

-- [ CREATE RECORDS ]
INSERT INTO tbl_Contacts
VALUES (1,'Adam','Sandler',DEFAULT,DEFAULT,'9-9-1966')
INSERT INTO tbl_Contacts
VALUES (2,'Emmanuelle','Chriqui',DEFAULT,DEFAULT,'12-10-1977')
INSERT INTO tbl_Contacts
VALUES (3,'Rob','Schneider',DEFAULT,DEFAULT,'10-31-1963')
INSERT INTO tbl_Contacts
VALUES (4,'Val','Kilmer',DEFAULT,DEFAULT,'12-31-1959')
INSERT INTO tbl_Contacts
VALUES (5,'Megan','Fox',DEFAULT,DEFAULT,'5-16-1986')

-- [ VERIFY RECORDS ]
SELECT * FROM tbl_Contacts

-- [ CREATE STORED PROCEDURE ]
CREATE PROC sp_GetAllActiveContacts
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (IsDeleted = 0)

-- [ TEST STORED PROCEDURE ]
EXEC sp_GetAllActiveContacts

-- [ CREATE STORED PROCEDURE WITH VARIABLE ]
CREATE PROC sp_GetAllActiveContactsByID ( @ContactID INT )
AS
SELECT firstName,lastName,CONVERT(CHAR(10),DOB,101) AS DOB, AGE
FROM tbl_Contacts
WHERE (ContactID = @ContactID) AND (IsDeleted = 0)

-- [ TEST STORED PROCEDURE ]
DECLARE @ID INT
SET @ID = 1
WHILE @ID
BEGIN
   EXEC sp_GetAllActiveContactsByID @ID
   SET @ID = @ID +1
END

Loading mentions Retweet