Title Page Background
HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 3424 AND i.igate IN ('usahud','azhud','ushud') AND m.expertType = 'agent' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'azhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 3424 AND i.igate IN ('usahud','azhud','ushud') AND m.expertType = 'lender' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'azhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1; HFQUERY SELECT e.Slogan AS expert_slogan, e.expertIdNum, m.expertname AS expertname2, m.expertpos, i.igate_index, m.datestamp, s.state, REPLACE(s.county, '.', '') AS county, m.locationID, e.name AS expertname, e.company AS company, e.phonetext1 AS expert_pt1, e.phonetext2 AS expert_pt2, e.phonenum1 AS expert_ph1, e.phonenum2 AS expert_ph2, e.address AS expert_address, e.email AS expert_email, e.bannerphoto AS expert_photo, e.webpage AS webAddress, e.bannerlogo AS expert_logo FROM mimian m, igate i, states s, bannersNew e, ExpertAdminInfo eai WHERE m.igate = i.igate AND m.locationID = s.locationID AND e.ExpertID = m.expertName AND eai.username = e.expertid AND s.locationid = 3424 AND i.igate IN ('usahud','azhud','ushud') AND m.expertType = 'other' ORDER BY 1, IF(i.igate = 'usahud', 0, 1), IF(i.igate = 'azhud', 0, 1), IF(i.igate = 'ushud', 0, 1), IF(m.expertpos = 'P', 0, IF(m.expertpos = 'PD', 1, 2)), m.priority, m.datestamp DESC LIMIT 0, 1;

2251 Upper Middle Creek Rd
Sevierville, TN 37876

« Prev
Next »
Currently Displayed Property Photo


Property Details


List Price: $874,000
Beds: 4
Baths: 3
Status: Active
SqFt: 2042
Agency: TN Smoky Mtn Realty, LLC

Map Details Show Map | Show StreetView




Description/Comments


Lot Size: --
Property Type: Residential
Year Built: 2007
Notes: This 4 Bed 3 Bath resort cabin in Pigeon Forge has everything you need and more! "The Majestic View" boasts enough space to sleep up to 10 people and is within 15 mins of Dollywood and all Major Pigeon Forge attractions. You have a cozy deck entry with a grill perfect for when you want to throw on some burgers and still chat with everyone inside on the main level. Your Main level is an open concept with the living room and kitchens so you can relax in front of the gas fireplace to watch a movie or read and still carry conversation with all prepping for dinner. Down the hall, turn left and enter the dining room which has a large table and 6 chairs, so you'll have plenty of space for everyone, and in the corner a tabletop arcade table for some family fun. Down the hall you have a queen bedroom with private tv. You also have a full bathroom on this level centrally located making it convenient for everyone. The upper loft features the King Master with floor to ceiling windows showcase a gorgeous mountain view, being the perfect picture to start your day. Relax in fully luxury at night with the in room jacuzzi to soak those muscles after sightseeing. Downstairs there is plenty to offer as well. Choose from playing a round of pool or darts, or take a long soak in the hot tub before turning in. On this level you will also have you will have two more bedrooms and another full bath. One bedroom is a king room with its own tv and the other has a bunk bed and futon also offering a foosball coffee table and tv. Perfect for an all night hang out for the night owls of the group. This cabin has so much to offer and a great location that will have your vacation being field with wonderful memories.
MlsNumber: 257911


Listing Provided By: TN Smoky Mtn Realty, LLC, original listing
Name: TN Smoky Mtn Realty, LLC
Phone: (865) 280-2569
Office Name: TN Smoky Mtn Realty, LLC
Office Phone: (865) 280-2569
Agent Name: Auggie Florian
Disclaimer: Copyright © 2024 Great Smoky Mountains Association of Realtors. All rights reserved. All information provided by the listing agent/broker is deemed reliable but is not guaranteed and should be independently verified.


USHUD.com on the Go!

Foreclosure Mobile App
Ushud Foreclosure iPhone App
Ushud Foreclosure Android App