INFORMATION信息中心

信息中心,了解医汇通最新动态、优惠活动及网络营销方案,资讯!



在线咨询

常用的数据库脚本

作者:佚名  来源:本站原创  发布时间:2015年05月12日  点击数:

清空数据库:
truncate table ZB_Area;
truncate table ZB_Departments;
truncate table ZB_Diseases;
truncate table ZB_Doctor;
DELETE FROM Dood_FinanceAudit;
DELETE FROM Dood_OnlineAsk;
DELETE FROM Dood_OnLineList;
truncate table Dood_OperatorLog;
truncate table Dood_ProcessStatus;
DELETE FROM Dood_Complaint;
DELETE FROM Dood_Reception;
DELETE FROM Dood_Replies;
truncate table ZB_SiteGroup;
DELETE FROM Dood_SMS;
DELETE FROM Dood_Cooperation;
DELETE FROM Dood_CouCondition;
DELETE FROM Dood_Coupon;
DELETE FROM Dood_Remittance;
DELETE FROM Dood_Strategical;
DELETE FROM [Dood_Schedule];
DELETE FROM [Dood_Photo];
DELETE FROM [Dood_Report];
DELETE FROM [Dood_ReportType];
DELETE FROM [Dood_Surgery];
DELETE FROM [Dood_CallRecord];
DELETE FROM Dood_Consultation;
DELETE FROM Dood_Customer;
UPDATE Dood_AutoNumber SET NumberValue ='00001';
truncate table Dood_Admin;
INSERT INTO Dood_Admin (UserName, [PassWord], RoleID, GroupID, Preferences, Status) VALUES (N'admin', N'21232F297A57A5A743894A0E4A801FC3', 1, 1, N'{"appearance":{"fontColor":"333333","themeId":1,"taskbarTransparency":79},"background":{"color":"f9f9f9","wallpaperId":11,"wallpaperPosition":"center"},"launchers":{"autorun":["remind"],"quickstart":[],"shortcut":["consultation","customer","replies","receptions","receptioncount","diagnosis","financeaudit","complaint","dialogue","assess","schedule","marketplace","cooperation","strategical","data_dictionary","callrecord","financereport","market_analy","sms","systemconfig","adminmanage","dictionary","announce","remind","operatorlog","regtemplate","preferences","myprofile","notebook","about"]}}',1);

DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='ddba05ef-c09b-438b-8f27-dbea50986128'  --所属医院
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='ebf9fed2-2efb-4078-b064-442cf58d20e2'  --接诊医生
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='c02a84ed-b6f0-486e-82e0-27e481a27308'  --咨询病种
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='23e7f57d-6d67-4e3e-ac72-481608fef916'  --咨询科室
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='2bee85da-6531-4cad-85cc-25fe06916152'  --区域(一级)
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='df68f979-db90-4e5e-badf-a44e75bbdef6'  --区域(二级)
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='44e6c907-f095-4f4c-a3e4-f00bfbb755dc'  --站群
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='66377a42-2a16-4954-9133-632807835a8c'  --排期项目
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='2cece957-e848-40ba-8149-2b9c4e94ef6f'  --排期子项目
DELETE FROM [dbo].[Dood_Dictionary] WHERE DicCategoryID='6967f915-8782-4dcb-86ac-db9dce3c5453'  --排期时段

 www.doodnet.net

 

 

 

---2005压缩数据库
declare @dbmane varchar(255);
SET @dbmane= db_name();
backup log @dbmane with NO_LOG;
backup log @dbmane with TRUNCATE_ONLY;
DBCC SHRINKDATABASE(@dbmane);


---2008压缩数据库
declare @dbmane varchar(255);
SET @dbmane= db_name();
EXECUTE('ALTER DATABASE '+ @dbmane +' SET RECOVERY SIMPLE;');
--收缩日志文件
EXECUTE('DBCC SHRINKFILE ('+ @dbmane +' , 0, TRUNCATEONLY);');
EXECUTE('ALTER DATABASE '+ @dbmane +' SET RECOVERY FULL;');DOODNET.NET

 

 

批量更新月份:
declare @month int
SET @month = 2
UPDATE Dood_Dialogue SET CountTime = DATEADD(mm, @month, CountTime)
UPDATE Dood_SMS SET SendTime =DATEADD(mm, @month, SendTime) WHERE (SendTime IS NOT NULL);
UPDATE Dood_Replies SET RepCreatTime =DATEADD(mm, @month, RepCreatTime);
UPDATE Dood_SMS SET RecordTime =DATEADD(mm, @month, RecordTime);
UPDATE Dood_Replies SET ReplyTime =DATEADD(mm, @month, ReplyTime);
UPDATE Dood_Reception SET ReceptionTime =DATEADD(mm, @month, ReceptionTime);
UPDATE Dood_ProcessStatus SET ActionTime =DATEADD(mm, @month, ActionTime);
UPDATE Dood_FinanceAudit SET AuditTime =DATEADD(mm, @month, AuditTime);
UPDATE Dood_Customer SET RecordTime =DATEADD(mm, @month, RecordTime);
UPDATE Dood_Customer SET LastRepTime =DATEADD(mm, @month, LastRepTime) WHERE (LastRepTime IS NOT NULL);
UPDATE Dood_Customer SET LastRecTime =DATEADD(mm, @month, LastRecTime) WHERE (LastRecTime IS NOT NULL);
UPDATE Dood_Consumption SET ConsumptionTime =DATEADD(mm, @month, ConsumptionTime), StatisticalTime =DATEADD(mm, @month, StatisticalTime);
UPDATE Dood_Consultation SET ConsultTime =DATEADD(mm, @month, ConsultTime);
UPDATE Dood_Consultation SET AgreedTime =DATEADD(mm, @month, AgreedTime) WHERE (AgreedTime IS NOT NULL);
UPDATE Dood_Consultation SET AgreeCreatTime =DATEADD(mm, @month, AgreeCreatTime) WHERE (AgreeCreatTime IS NOT NULL);
UPDATE Dood_Consultation SET RemindTime =DATEADD(mm, @month, RemindTime) WHERE (RemindTime IS NOT NULL);
UPDATE Dood_Complaint SET ComTime = DATEADD(mm, @month, ComTime);
UPDATE Dood_Complaint SET ProcessTime =DATEADD(mm, @month, ProcessTime) WHERE (ProcessTime IS NOT NULL);
UPDATE Dood_Complaint SET AuditTime =DATEADD(mm, @month, AuditTime) WHERE (AuditTime IS NOT NULL);
UPDATE Dood_Cooperation SET CooCreateDate =DATEADD(mm, @month, CooCreateDate);
UPDATE Dood_CouCondition SET ConUseTime =DATEADD(mm, @month, ConUseTime);
UPDATE Dood_Coupon SET CouCreatTime =DATEADD(mm, @month, CouCreatTime);
UPDATE Dood_Remittance SET RemCreateTime =DATEADD(mm, @month, RemCreateTime);
UPDATE Dood_Remittance SET RemTime =DATEADD(mm, @month, RemTime);
UPDATE Dood_Strategical SET StrCreatTime =DATEADD(mm, @month, StrCreatTime);
UPDATE Dood_Strategical SET StrStartTime =DATEADD(mm, @month, StrStartTime);
UPDATE Dood_Schedule SET SchCreateTime =DATEADD(mm, @month, SchCreateTime);
UPDATE Dood_Schedule SET SchStartDate =DATEADD(mm, @month, SchStartDate);
UPDATE Dood_Schedule SET SchEndDate =DATEADD(mm, @month, SchEndDate);
UPDATE Dood_Schedule SET SchLockTime =DATEADD(mm, @month, SchLockTime) WHERE (SchLockTime IS NOT NULL);
UPDATE Dood_Schedule SET SchRecordTime =DATEADD(mm, @month, SchRecordTime) WHERE (SchRecordTime IS NOT NULL);
UPDATE Dood_Photo SET PhoCreateTime =DATEADD(mm, @month, PhoCreateTime);
UPDATE Dood_CallRecord SET Rec_RingTime =DATEADD(mm, @month, Rec_RingTime), Rec_RecordTime =DATEADD(mm, @month, Rec_RecordTime);

批量更新日期:
declare @day int
SET @day = 1
UPDATE Dood_Dialogue SET CountTime = DATEADD(dd, @day, CountTime);
UPDATE Dood_SMS SET SendTime =DATEADD(dd, @day, SendTime) WHERE (SendTime IS NOT NULL);
UPDATE Dood_SMS SET RecordTime =DATEADD(dd, @day, RecordTime);
UPDATE Dood_Replies SET RepCreatTime =DATEADD(dd, @day, RepCreatTime);
UPDATE Dood_Replies SET ReplyTime =DATEADD(dd, @day, ReplyTime);
UPDATE Dood_Reception SET ReceptionTime =DATEADD(dd, @day, ReceptionTime);
UPDATE Dood_ProcessStatus SET ActionTime =DATEADD(dd, @day, ActionTime);
UPDATE Dood_FinanceAudit SET AuditTime =DATEADD(dd, @day, AuditTime);
UPDATE Dood_Customer SET RecordTime =DATEADD(dd, @day, RecordTime);
UPDATE Dood_Customer SET LastRepTime =DATEADD(dd, @day, LastRepTime) WHERE (LastRepTime IS NOT NULL);
UPDATE Dood_Customer SET LastRecTime =DATEADD(dd, @day, LastRecTime) WHERE (LastRecTime IS NOT NULL);
UPDATE Dood_Consumption SET ConsumptionTime =DATEADD(dd, @day, ConsumptionTime), StatisticalTime =DATEADD(dd, @day, StatisticalTime);
UPDATE Dood_Consultation SET ConsultTime =DATEADD(dd, @day, ConsultTime);
UPDATE Dood_Consultation SET AgreedTime =DATEADD(dd, @day, AgreedTime) WHERE (AgreedTime IS NOT NULL);
UPDATE Dood_Consultation SET AgreeCreatTime =DATEADD(dd, @day, AgreeCreatTime) WHERE (AgreeCreatTime IS NOT NULL);
UPDATE Dood_Consultation SET RemindTime =DATEADD(dd, @day, RemindTime) WHERE (RemindTime IS NOT NULL);
UPDATE Dood_Complaint SET ComTime = DATEADD(dd, @day, ComTime);
UPDATE Dood_Complaint SET ProcessTime =DATEADD(dd, @day, ProcessTime) WHERE (ProcessTime IS NOT NULL);
UPDATE Dood_Complaint SET AuditTime =DATEADD(dd, @day, AuditTime) WHERE (AuditTime IS NOT NULL);
UPDATE Dood_Cooperation SET CooCreateDate =DATEADD(dd, @day, CooCreateDate);
UPDATE Dood_CouCondition SET ConUseTime =DATEADD(dd, @day, ConUseTime);
UPDATE Dood_Coupon SET CouCreatTime =DATEADD(dd, @day, CouCreatTime);
UPDATE Dood_Remittance SET RemCreateTime =DATEADD(dd, @day, RemCreateTime);
UPDATE Dood_Remittance SET RemTime =DATEADD(dd, @day, RemTime);
UPDATE Dood_Strategical SET StrCreatTime =DATEADD(dd, @day, StrCreatTime);
UPDATE Dood_Strategical SET StrStartTime =DATEADD(dd, @day, StrStartTime);
UPDATE Dood_Schedule SET SchCreateTime =DATEADD(dd, @day, SchCreateTime);
UPDATE Dood_Schedule SET SchStartDate =DATEADD(dd, @day, SchStartDate);
UPDATE Dood_Schedule SET SchEndDate =DATEADD(dd, @day, SchEndDate);
UPDATE Dood_Schedule SET SchLockTime =DATEADD(dd, @day, SchLockTime) WHERE (SchLockTime IS NOT NULL);
UPDATE Dood_Schedule SET SchRecordTime =DATEADD(dd, @day, SchRecordTime) WHERE (SchRecordTime IS NOT NULL);
UPDATE Dood_Photo SET PhoCreateTime =DATEADD(dd, @day, PhoCreateTime);
UPDATE Dood_CallRecord SET Rec_RingTime =DATEADD(dd, @day, Rec_RingTime), Rec_RecordTime =DATEADD(dd, @day, Rec_RecordTime);

 DooDnet.Net

 DOODNET.NET

DELETE FROM [Dood_Reception] WHERE ReceptionID IN(SELECT b.ReceptionID  FROM [Dood_ProcessStatus] A right OUTER JOIN [Dood_Reception] B ON A.RunningNumber = B.RunningNumber  And DATEDIFF(ss,ReceptionTime,ActionTime)=0 WHERE StatusID IS NULL) ---清理无状态的分诊记录来自:广州多度软件公司.


/****** 更新预约记录时间 ******/
UPDATE [Dood_Consultation] SET [AgreeCreatTime] = (SELECT TOP 1 [ActionTime] FROM dbo.Dood_ProcessStatus WHERE RunningNumber=[Dood_Consultation].RunningNumber AND Process=2) WHERE [AgreedTime] IS NOT NULL;DOODNET.NET


/*自动重置权限*/
CREATE TRIGGER [UPDATE_Dood_Roles_Reset] ON dbo.Dood_Roles
FOR UPDATE
AS
UPDATE Dood_Roles SET
[Data] = '{"customer":["viewCustomers","addCustomer","editCustomer","promotionCustomer","clearDataCustomer","resourceCustomer","handoverCustomer","emptyhandoverCustomer","appointCustomer","importCustomer","exportCustomer"],"consultation":["viewConsultations","addConsultation","editConsultation","exportConsultation"],"replies":["viewReplies","addReplie","editReplie","editWReplie","deleteReplie","deleteWReplie","exportReplie"],"receptions":["viewReceptions","viewConsultation","addReception","editReception","modifyReceptionTime","deleteReception","exportReception"],"receptioncount":["viewReceptioncount"],"diagnosis":["viewDiagnosis","viewOTDiagnosis","viewConsultation2","exportDiagnos"],"financereport":["viewFinanceReport"],"financeaudit":["viewFinanceAudit","ReceptionAudit","exportAudit","viewConsumptions","addConsumption","editConsumption","deleteConsumption","exportConsumption"],"callrecord":["viewCallrecords","addCallrecord","editCallrecord","editScoreCallrecord","deleteCallrecord"],"complaint":["viewComplaints","addComplaint","editComplaint","deleteComplaint","exportComplaint"],"dialogue":["viewDialogue","addDialogue","editDialogue","deleteDialogue"],"remind":["viewAppointmentRemind","exportAppointmentRemind","viewReplieRemind","exportReplieRemind"],"assess":["viewDepartmentAssessByNet","viewPersonalAssessByNet","exportAssessByNet","viewReplieAll","viewRepliePersonal","exportReplieAll"],"market_analy":["marketAnalyClientType","marketAnalyLevels","marketAnalyDepartment","marketAnalyDisease","marketAnalyReceptionDepartment","marketAnalyReceptionDoctor","marketAnalySource","marketAnalyProvince","marketAnalyCity","marketAnalyChannel","marketAnalyDeveloper","marketAnalyConsultTool","marketAnalyMarket","marketAnalySiteGroup","marketAnalyKeyWord"],"marketplace":["viewMarketplaces","addMarketplace","banAddMarketplace","addMYCustomerMarketplace","editMarketplace","editMYMarketplace","editNGMarketplace","banEditMarketplace","deleteMarketplace","deleteMYMarketplace","deleteNGMarketplace","exportMarketplace"],"cooperation":["viewCooperations","addCooperation","editCooperation","deleteCooperation","importCooperation","exportCooperation","viewRemittances","addRemittance","editRemittance","deleteRemittance","importRemittance","exportRemittance"],"strategical":["viewStrategicals","addStrategical","editStrategical","deleteStrategical","viewCoupons","addCoupon","editCoupon","deleteCoupon","viewCouConditions","addCouCondition","deleteCouCondition","exportCouCondition"],"schedule":["viewSchedules","addSchedule","deleteSchedule","viewSchedulesList","editScheduleList","cancelScheduleList","clearlSchedule","addInterval","deleteInterval"],"sms":["sendsms","viewSmsReceive","viewSMSManages","SMSAccount","viewSMSTemplates","addSMSTemplate","editSMSTemplate","deleteSMSTemplate","signSMS","cleanSMS","deleteSms"],"adminmanage":["viewGroups","addGroup","editGroup","deleteGroup","viewRoles","addRole","editRole","deleteRole","viewRoleModules","editRoleModules","viewMembers","addMember","editMember","deleteMember"],"dictionary":["viewDicCategorys","editDicCategory","viewDictionarys","addDictionary","editDictionary","deleteDictionary"],"systemconfig":["loadconfig","saveBase","saveSMS","savePhoto"],"announce":["viewAnnounces","addAnnounce","editAnnounce","deleteAnnounce"],"operatorlog":["viewOperatorlog","deleteOperatorlog","exportOperatorlog"],"regtemplate":["loadRegTemplate","saveRegTemplate","SendToZoosNet"],"photo":["viewPhotos","upLoadPhoto","deletePhoto","editPhoto"],"preferences":["saveAppearance","saveAutorun","saveBackground","saveQuickstart","saveShortcut","viewThemes","viewWallpapers"],"myprofile":["loadProfile","saveProfile","savePwd"],"notebook":["loadNotebook","saveNotebook"],"about":[]}'
FROM  [dbo].[Dood_Roles]  where RoleID=1

 

/*姓名更新为拼音*/
UPDATE [dbo].[ZB_Customer]
   SET [FullName] = substring([FullName],1,1)+UPPER(dbo.GetPy(substring([FullName],2,20)))

 


/*重命名帐号*/
declare @OldUserName nvarchar(50),@NewUserName nvarchar(50),@Version int
SET @OldUserName = '原名'
SET @NewUserName = '新名'
SELECT @Version = count(*) FROM [dbo].[Dood_Customer]DOODNET.NET

update [dbo].[Dood_Admin] set [UserName]=@NewUserName where [UserName]=@OldUserName
update [dbo].[Dood_Consultation] set [ConsultOperator]=@NewUserName where [ConsultOperator]=@OldUserName
update [dbo].[Dood_Consumption] set [Creator]=@NewUserName where [Creator]=@OldUserName
update [dbo].[Dood_Customer] set [Developer]=@NewUserName where [Developer]=@OldUserName

来自:广州多度软件公司.

if exists(select * from syscolumns where id=object_id('Dood_Customer')) begin
update [dbo].[Dood_Customer] set [Manager]=@NewUserName where [Manager]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_Announce') begin
update [dbo].[Dood_Announce] set [Author]=@NewUserName where [Author]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_Complaint') begin
update [dbo].[Dood_Complaint] set [ComOperator]=@NewUserName where [ComOperator]=@OldUserName
update [dbo].[Dood_Complaint] set [AuditOperator]=@NewUserName where [AuditOperator]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_CallRecord') begin
update [dbo].[Dood_CallRecord] set [Rec_Operator]=@NewUserName where [Rec_Operator]=@OldUserName
update [dbo].[Dood_CallRecord] set [Rec_GraOpe]=@NewUserName where [Rec_GraOpe]=@OldUserName
endDOODNET.net

if exists(SELECT name FROM sysobjects where name= 'Dood_Cooperation') begin
update [dbo].[Dood_Cooperation] set [CooCreator]=@NewUserName where [CooCreator]=@OldUserName
update [dbo].[Dood_Cooperation] set [CooPrincipal]=@NewUserName where [CooPrincipal]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_CouCondition') begin
update [dbo].[Dood_CouCondition] set [ConPersonnel]=@NewUserName where [ConPersonnel]=@OldUserName
endDooDnet.Net

if exists(SELECT name FROM sysobjects where name= 'Dood_Coupon') begin
update [dbo].[Dood_Coupon] set [CouPersonnel]=@NewUserName where [CouPersonnel]=@OldUserName
update [dbo].[Dood_Coupon] set [CouCreator]=@NewUserName where [CouCreator]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_Photo') begin
update [dbo].[Dood_Photo] set [PhoCreator]=@NewUserName where [PhoCreator]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_Remittance') begin
update [dbo].[Dood_Remittance] set [Remitter]=@NewUserName where [Remitter]=@OldUserName
update [dbo].[Dood_Remittance] set [RemOperator]=@NewUserName where [RemOperator]=@OldUserName
end

DOODNET.NET

if exists(SELECT name FROM sysobjects where name= 'Dood_ReportType') begin
update [dbo].[Dood_ReportType] set [ReptDoctor]=@NewUserName where [ReptDoctor]=@OldUserName
endDOODNET.NET

if exists(SELECT name FROM sysobjects where name= 'Dood_Schedule') begin
update [dbo].[Dood_Schedule] set [SchDoctor]=@NewUserName where [SchDoctor]=@OldUserName
update [dbo].[Dood_Schedule] set [SchCreator]=@NewUserName where [SchCreator]=@OldUserName
update [dbo].[Dood_Schedule] set [SchOperator]=@NewUserName where [SchOperator]=@OldUserName
end

if exists(SELECT name FROM sysobjects where name= 'Dood_Strategical') begin
update [dbo].[Dood_Strategical] set [StrPlotter]=@NewUserName where [StrPlotter]=@OldUserName
update [dbo].[Dood_Strategical] set [StrCreator]=@NewUserName where [StrCreator]=@OldUserName
end

DOODNET.NET

if exists(SELECT name FROM sysobjects where name= 'Dood_Surgery') begin
update [dbo].[Dood_Surgery] set [SurDoctor]=@NewUserName where [SurDoctor]=@OldUserName
update [dbo].[Dood_Surgery] set [SurOperator]=@NewUserName where [SurOperator]=@OldUserName

end

www.doodnet.net

update [dbo].[Dood_Dialogue] set [Consultant]=@NewUserName where [Consultant]=@OldUserName
update [dbo].[Dood_FinanceAudit] set [AuditOperator]=@NewUserName where [AuditOperator]=@OldUserName
update [dbo].[Dood_OperatorLog] set [Operator]=@NewUserName where [Operator]=@OldUserName
update [dbo].[Dood_Reception] set [ReceptionDoctor]=@NewUserName where [ReceptionDoctor]=@OldUserName
update [dbo].[Dood_Reception] set [ReceptionOperator]=@NewUserName where [ReceptionOperator]=@OldUserName
update [dbo].[Dood_SMS] set [Operator]=@NewUserName where [Operator]=@OldUserName
update [dbo].[Dood_Replies] set [ReplyOperator]=@NewUserName where [ReplyOperator]=@OldUserName
update [dbo].[Dood_Replies] set [RepCreator]=@NewUserName where [RepCreator]=@OldUserName
UPDATE [dbo].[Dood_Dictionary] SET [DicName] = @NewUserName where [DicName]=@OldUserName来自:广州多度软件公司.

 


/*批理更新咨询科室,当病种移动到其它科室下面后操作*/
UPDATE [dbo].[Dood_Consultation] SET [ConsultDepartment] = (SELECT [Dep] FROM [dbo].[Dood_Diseases] where [Title]=[ConsultDisease])
 WHERE RunningNumber in(SELECT C.RunningNumber FROM Dood_Consultation AS C INNER JOIN Dood_Diseases AS D ON C.ConsultDisease = D.Title
WHERE (C.ConsultDisease <> N'其它') AND (C.ConsultDisease <> N'') AND (C.ConsultDisease is not null) AND (D.Dep is not null))


/*更新HIS会员卡号到CRM中*/

declare @CardID nvarchar(10),@ID nvarchar(10), @count int,@i int
SET @i = 1
SELECT  @count = count(*) FROM [DoodSoft].[dbo].[Dood_Customer]医汇通--医院大营销管理系统doodnet

update [DoodSoft].[dbo].[Dood_Customer] set [CardID]=''
update [TAOHIS2010].[dbo].[tb_GG4010] set BRMM='123456'DOODNET.net

while @count>=@i
beginwww.doodnet.net

SELECT top 1 @ID = ID FROM [DoodSoft].[dbo].[Dood_Customer] WHERE CardID='' order by RecordTime desc
SELECT top 1 @CardID = CKHM FROM [TAOHIS2010].[dbo].[tb_GG4010] WHERE len(CKHM)=6 and BRMM='123456' order by jlsj desc来自:广州多度软件公司.

update [DoodSoft].[dbo].[Dood_Customer] set [CardID]=@CardID where CardID='' and ID=@ID
update [TAOHIS2010].[dbo].[tb_GG4010] set BRMM='000000' where CKHM=@CardID

DooDnet.Net

SET @i = @i + 1
end


/*从旧库中恢复数据到当前库中*/
UPDATE [DoodSoft].[dbo].[Dood_Customer]
   SET [Developer] = (SELECT top 1 [Developer] FROM [DOODOLD].[dbo].[Dood_Customer] where ID=[DoodSoft].[dbo].[Dood_Customer].ID)
      ,[Manager] = (SELECT top 1 [Manager] FROM [DOODOLD].[dbo].[Dood_Customer] where ID=[DoodSoft].[dbo].[Dood_Customer].ID)
      ,[LastModify] = getdate()
 WHERE [RecordTime]>='2013-01-01 00:00:00' and [RecordTime]<='2013-02-28 23:59:59' and Developer='资源存储站'


UPDATE [DoodSoft].[dbo].[Dood_Customer]
   SET [Developer] = A.[Developer]
      ,[Manager] = A.[Manager]
      ,[LastModify] = getdate()
from [DoodSoft2].[dbo].[Dood_Customer] A
 WHERE [DoodSoft].[dbo].[Dood_Customer].ID=A.ID and (A.[Developer]='吕明' or A.[Manager]='吕明')
 
 来自:广州多度软件公司.

针对sql2000升级到sql2005后出现的数据库架构不一样的情况,可以在 查询分析器里或存储过程里执行以下语句:
EXEC sp_MSforeachtable @command1="EXEC SP_ChangeObjectOwner '?','dbo'"
该语句调用系统存储过程 sp_MSforeachtable 批量修改数据表的架构名为 dbo 。

 

重设密码
UPDATE [DoodSoft].[dbo].[Dood_Admin] SET [PassWord] = '21232F297A57A5A743894A0E4A801FC3' WHERE [UserName]='admin'


/*非法关机修复数据库((数据库可以访问时)*/
ALTER DATABASE [数据库名] SET SINGLE_USER
GO
DBCC CHECKDB('数据库名',repair_allow_data_loss) WITH TABLOCK
GO
ALTER DATABASE [数据库名] SET MULTI_USER

www.doodnet.net

 DOODNET.net

/*非法关机修复数据库(数据库无法访问时)*/
解决步骤:
A.使用默认方式建立一个供恢复使用的数据库(如test)。
B.停止数据库实例服务。
C.将刚才生成的数据库的日志文件test_log.ldf删除,用要恢复的数据库mdf文件覆盖刚才生成的数据库数据文件test_data.mdf。
D.启动数据库服务器。此时会看到数据库test不可访问。

--设置为紧急状态
ALTER DATABASE [数据库名] SET EMERGENCY
 --设置为单用户模式
 ALTER DATABASE [数据库名] SET SINGLE_USER

--重建日志文件
ALTER DATABASE [数据库名] REBUILD LOG ON (NAME=dbname_log, FILENAME='E:\数据库名_Log')
--检查修复数据库并重建日志文件
dbcc checkdb('数据库名',REPAIR_ALLOW_DATA_LOSS)
--恢复成多用户模式
ALTER DATABASE [数据库名] SET MULTI_USER
--恢复正常模式
ALTER DATABASE [数据库名] SET ONLINE

 

DOODNET.NET

相关文章

2009-2017 DoodSoft All Rights Reserved. 广州多度软件科技有限公司 版权所有 软件企业编号:粤Q-2010-0181| 粤ICP备12024082号-3