Accessing _MobileAddress and _MobileSubscription Data Extensions
So, the field names are correct (though not a complete list). They contain an underscore before each of them in the Data Extension. This makes things difficult when pulling the fields out using SELECT * because fields in [user created] Data Extensions cannot have a leading underscore.
So if you want to pull all the records from _MobileAddress you will need the following query:
SELECT _CarrierID AS CarrierID,
_Channel AS Channel,
_City AS City,
_ContactID AS ContactID,
_CountryCode AS CountryCode,
_CreatedBy AS CreatedBy,
_CreatedDate AS CreatedDate,
_FirstName AS FirstName,
_IsHonorDST AS IsHonorDST,
_LastName AS LastName,
_MobileNumber AS MobileNumber,
_ModifiedBy AS ModifiedBy,
_ModifiedDate AS ModifiedDate,
_Priority AS Priority,
_Source AS Source,
_SourceObjectID AS SourceObjectID,
_State AS State,
_Status AS Status,
_UTCOffset AS UTCOffset,
_ZipCode AS ZipCode
FROM _MobileAddress
And all the records from _MobileSubscription can be pulled with the following query:
SELECT
_CreatedBy AS CreatedBy,
_OptOutMethodID AS OptOutMethodID,
_MobileNumber AS MobileNumber,
_OptInDate AS OptInDate,
_Source AS Source,
_OptOutStatusID AS OptOutStatusID,
_OptOutDate AS OptOutDate,
_ModifiedBy AS ModifiedBy,
_SourceObjectId AS SourceObjectId,
_SubscriptionDefinitionID AS SubscriptionDefinitionID,
_CreatedDate AS CreatedDate,
_OptInStatusID AS OptInStatusID,
_OptInMethodID AS OptInMethodID,
_ModifiedDate AS ModifiedDate
FROM _MobileSubscription
In case you're wondering about the data types and such:
_MobileAddress
| Field Name | Data Type (Length) | Required (Y/N) | Data Default |
| :- | :- | :-: | :- |
| _ContactID | Text | Y | |
| _MobileNumber | Text(15) | Y | |
| _Status | Text | N | |
| _Source | Text | N | |
| _SourceObjectId | Text(200) | N | |
| _Priority | Text | N | 1 |
| _Channel | Text(20) | N | |
| _CarrierID | Text | Y | 0 |
| _CountryCode | Text(2) | Y | |
| _CreatedDate | Date | Y | GETDATE() |
| _CreatedBy | Text | N | |
| _ModifiedDate | Date | Y | GETDATE() |
| _ModifiedBy | Text | N | |
| _City | Text(200) | N | |
| _State | Text(200) | N | |
| _ZipCode | Text(20) | N | |
| _FirstName | Text(100) | N | |
| _LastName | Text(100) | N | |
| _UTCOffset | Decimal(4,2) | N | 0 |
| _IsHonorDST | Boolean | N | false |
_MobileSubscription
| Field Name | Data Type (Length) | Required (Y/N) | Data Default |
| :- | :- | :-: | :- |
| _SubscriptionDefinitionID | Text(200) | Y | |
| _MobileNumber | Text(15) | Y | |
| _OptOutStatusID | Text | N | |
| _OptOutMethodID | Text | N | |
| _OptOutDate | Date | N | |
| _OptInStatusID | Text | Y | |
| _OptInMethodID | Text | N | |
| _OptInDate | Date | N | |
| _Source | Text | N | |
| _CreatedDate | Date | Y | GETDATE() |
| _CreatedBy | Text | N | |
| _ModifiedDate | Date | Y | GETDATE() |
| _ModifiedBy | Text | N | |
I did an SOAP API retrieve on the DataExtensionField
Object to get this info.