Dump out Commerce Server Site configurations by SQL Query

  • Article

 

 

Commerce Server 2002/2007 site has plenty of configuration sitings, it is not easily to verify them one by one in Commerce Server management UI after migrating or database restoring, especially for some Connection Strings.

 

I wrote one SQL query to dump out the information from database directly, this will help admin to quick identify the site settings:

 

For Commerce Server 2002, if the site is StaterSite, run the SQL query against the MSCS_Admin database:

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

select * from ResourceProps where i_ResourceID in

 

(

select I_ResourceID from SiteResources where i_SiteID in ( select i_SiteID from

sites where s_Name='StaterSite')

)

 

select * from ResourceProps where i_ResourceID in

(

select i_ResourceID from Resources where s_DisplayName like 'Profiles%StaterSite%'

)

 

select * from StaterSite_commerce.dbo.SourceDef

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

 

 

If this is Commerce Server 2007 and site name is StaterSite, run the SQL query against the MSCS_Admin database:

 

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

select * from ResourceProps where i_ResourceID in

 

(

select I_ResourceID from SiteResources where i_SiteID in ( select i_SiteID from

sites where s_Name='StaterSite')

)

 

select * from ResourceProps where i_ResourceID in

(

select i_ResourceID from Resources where s_DisplayName like 'Profiles%StaterSite%'

)

 

select * from StaterSite_profiles.dbo.SourceDef

 

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

 

Regards,

Freist Li