- SQL Server 2005
-
CRM database
select * from CompanyName_MSCRM.dbo.ResourceBase
note that r & u are variables
select r.ResourceId, r.OrganizationId, r.BusinessUnitId, r.Name, u.FullName
from CompanyName_MSCRM.dbo.ResourceBase as r left outer join CompanyName_MSCRM.dbo.SystemUserBase as u on r.ResourceId = u.SystemUserId
where r.ObjectTypeCode = 8
order by r.Name
backup database northwind to disk = 'c:\northwind.bak'
drop database Northwind //removes database
restore database northwind from disk = 'c:\northwind.bak'
SP_HELPDB Northwind //shows the history of the files being read back in from the .bak file
USE MASTER;
RESTORE filelistonly
from disk = 'c:\offlineexport.bak'
restore database Northwindrep
from disk = 'c:\northwind.bak'
with move 'Northwind' to 'c:\Northwindrep.mdf',
move 'Northwind_log' to 'c:\Northwindrep.ldf'
restore database OfflineExport from disk = 'c:\OfflineExport.bak'
insert into MSCRM_MSDE.dbo.OfflineQueue(ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName)
select ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName from OfflineExport.dbo.OfflineQueue
delete from MSCRM_MSDE.dbo.OfflineQueue
select min(ActionDate) from OfflineExport.dbo.OfflineQueue
select min(ActionDate) from OfflineExport.dbo.OfflineQueue AS earliest
select ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName from OfflineExport.dbo.OfflineQueue
where ( ActionDate = (select min(ActionDate) from OfflineExport.dbo.OfflineQueue) )
insert into MSCRM_MSDE.dbo.OfflineQueue(ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName)
select ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName from OfflineExport.dbo.OfflineQueue
where ( ActionDate = (select min(ActionDate) from OfflineExport.dbo.OfflineQueue) )
insert into MSCRM_MSDE.dbo.OfflineQueue(ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName)
select ObjectId,ActionDate,CommandId,Data,ParentId,HttpHeader,Url,ObjectTypeCode,State,OutlookSyncState,MethodName from OfflineExport.dbo.OfflineQueue
where ( RowNumber < 230 )
delete from OfflineExport.dbo.OfflineQueue
where ( RowNumber < 230 )
select ObjectId,ActionDate from MSCRM_MSDE.dbo.OfflineQueue
156 rows
- - - - - - -
NOT WORKING PARTS OF SQL CODE
DECLARE @earliestDate
where ( ActionDate = '15/10/2008 20:29:56')
where (ObjectId = c431452c-89ef-dc11-b501-0018de67e701)? ?
- - - - - - -
Example: What customers have never ordered anything from us?
SELECT customers.* FROM customers LEFT JOIN orders ON customers.customer_id = orders.customer_id WHERE orders.customer_id IS NULL
- - - - - - -
EXEC msdb.dbo.sp_delete_database_backuphistory @database_name = N'test'
GO
USE [master]
GO
/****** Object: Database [test] Script Date: 11/28/2008 10:27:04 ******/
DROP DATABASE [test]
GO