Friday, July 19, 2013

[SQL Server 2008 issues] Update Problem

[SQL Server 2008 issues] Update Problem

Update Problem

Posted: 18 Jul 2013 06:22 PM PDT

helloi want to update a data in grid viewi have three columns in grid view first two column are shift id and date these two column are taken from table 1the third column is shift column this column are taken from table 2now i want to update a data where shift id and shift column are sametable 2 have these columnshiftshiftidand table 1 has these columnshift id,dateplease can u provide me a update query syntaxthanks

database.in simple recovery

Posted: 18 Jul 2013 03:20 PM PDT

hi guys, i have a reporting database in simple recovery mode, i need to.make.a full backup to be able to restore it in my development environment. Since my database.is.in.simple recovery mode is.it necesary for.me to also.create a log backup?,i read somewhere i need to do it to clear out the log and.prevent it from growing

SSRS requirement query help

Posted: 18 Jul 2013 06:14 PM PDT

Material Container InformationEAS Material container code <MaterialsItemsContainers.materialItemContainerCode> Date received <MaterialsItemsContainers.receptionDate>Registered by <MaterialsItemsContainers.receivedBy> Received from <MaterialsItemsContainers.supplier> (cf. DMS_03)Gross amount rec'd <MaterialsItemsContainers.grossAmount> Gross amount unit <MaterialsItemsContainers.grossAmountUnit>Net amount rec'd <MaterialsItemsContainers.netAmount> Net amount unit <MaterialsItemsContainers.netAmountUnit>Site <MaterialsItemsContainers.site> Other reference <MaterialsItemsContainers.otherReference>Comments I have a report requirement like this:I am pretty new to this. If the requirement was like this fashion below,EAS Material container code I Registered by I Gross amount rec'd I Net amount rec'd I Site ie. The fields in listed horizontally, I know how to do the report, but it is in a vertical listing fashion. How to do that part?. Please help on the query part to achieve this.

Identity property

Posted: 17 Jul 2013 11:04 PM PDT

Hi,I have a table with some columns likeTable name:Mydatacolumns:sid,sname,coursewith some data.so, can i add a Identity property to the sid column after data inserted in to the table?

New server. How to migrate?

Posted: 17 Jul 2013 08:10 PM PDT

My web site uses MS SQL Server. I ordered a new server for DB and want to migrate from old one. Both servers have the same [b]MS SQL Server 2008 Web Edition[/b]The problem is that database is about 500GB. Simple attach/detach procedure will take about an HOUR to copy database files. I'm not very happy that my site will be offline during such a long period of time. I need a solution that leads to 1-2 min of site unavailability maximum.Is there a way to migrate database on new server online?What I think should be possible: I would like to perform some setup on new server while old database is online. After that setup two databases are continuously syncronized. Then I just swith site to new DB.How to implement that scenario?Looking forward for an advice.

Memory Hogger

Posted: 18 Jul 2013 02:54 AM PDT

I'm running Sql Server 2008 R2. Resource center says i have 200mb available on a 12 gb drive. Doing the math i can see what is being used in the resource list, it only adds up to 2gb. So my question is where is the other 10 gb going? Is there a way i can see what is using it? Or a setting i can set to avoid this? One thing i did do is sql management studio, properties and memory and set minimun and maximum to 6 gb each. This freed up some but over the last two weeks it was all hogged up again.

Any suggestion on tunning this Stored procedure

Posted: 18 Jul 2013 07:56 AM PDT

USE [myNIC_PROD]GO/****** Object: StoredProcedure [dbo].[NIC_REPORTS_GetReferal] Script Date: 07/18/2013 20:51:38 ******/SET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[NIC_REPORTS_GetReferal]@lPhysician int,@lOffice int,@SDate datetime,@EDate datetimeASSET TRANSACTION ISOLATION LEVEL READ COMMITTEDDECLARE @lEnterprise intSELECT @lEnterprise=lEnterprise FROM Office WHERE lID=@lOffice/*select b.szlast + ', ' + b.szfirst as szPhysicianName, c.szlast + ', ' + c.szfirst as szPatientName, a.dDateCreated, a.dCheckForReport, a.nPriority from consultants a, physician b, patient c where a.lphysician = b.lid and a.lphysician in (select lPhysician from map_physiciantooffice where loffice = @lOffice) and c.lid in (select lpatient from address where lid = a.lpatientaddress)and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 order by a.dDateCreated, szPatientName, a.nPriority desc*/select a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ap.szfirst + ' ' + ap.szlast as szphysicianname, ap.szorganization as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN alliedHealthProviders AP ON a.lAlliedProvider=AP.lid LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere a.nRecordStatus=1 AND DATEDIFF(d, @SDate, a.dDateCreated) >= 0 AND DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, r.szfirst + ' ' + r.szlast as szphysicianname, r.szdepartment as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToRolodex d ON a.lID = d.lConsultants JOIN Rolodex r ON r.lID = d.lRolodex LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directoryAB ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directoryab' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directorybc ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directorybc' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directorynb ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directorynb' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directoryns ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directoryns' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directoryon ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directoryon' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLUNIONselect a.dDateCreated, a.dCheckForReport, a.nPriority, p.szfirst + ' ' + p.szlast as szpatientname, ds.szfirst + ' ' + ds.szlast as szphysicianname, '' as szfacility, phys.szFirst + ' ' + phys.szLast AS szOrderedByfrom consultants a INNER JOIN Map_ConsultationToProgressNote map ON a.lID=map.lConsultant INNER JOIN ProgressNote PN ON map.lProgressNote=PN.lID INNER JOIN Office o ON PN.lOffice=o.lID Join patient p on p.lid=PN.lPatient JOIN Map_ConsultantsToDirectory d ON a.lID = d.lConsultants JOIN state s on s.lid = d.lDirectoryState JOIN directorysk ds on ds.lid = d.lDirectory LEFT JOIN Physician phys ON a.lPhysician=phys.lIDwhere s.szDirectoryTableName = 'directorysk' and a.nRecordStatus=1 and DATEDIFF(d, @SDate, a.dDateCreated) >= 0 and DATEDIFF(d, @EDate, a.dDateCreated) <= 0 AND o.lID=@lOffice AND a.dLastPrinted IS NOT NULLorder by szPatientName, a.dDateCreated, szfacility, a.nPriority desc

SQL Service Restarted - Fault with MSVCR80.dll

Posted: 10 May 2013 12:30 AM PDT

Hi All,Our production SQL Server service stopped and restarted automatically in 2 mins. It is SQL Server 2008 R2 Enterprise on Windows Server 2008 R2 Enterprise. Active Passive cluster. It did not fail over. SQL Restarted and no errors logged in SQL Error log. However I do see below errors in Windows application log.Faulting application name: sqlservr.exe, version: 2009.100.4266.0, time stamp: 0x5063d9bcFaulting module name: MSVCR80.dll, version: 8.0.50727.6195, time stamp: 0x4dcdd833Exception code: 0xc0000409Fault offset: 0x000000000000caddFaulting process id: 0x2b14Faulting application start time: 0x01ce493690d265c8Faulting application path: C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn\sqlservr.exeFaulting module path: C:\Windows\WinSxS\amd64_microsoft.vc80.crt_1fc8b3b9a1e18e3b_8.0.50727.6195_none_88e41e092fab0294\MSVCR80.dllReport Id: dd42d838-b902-11e2-b963-d4ae52e84a03Fault bucket , type 0Event Name: BEX64Response: Not availableCab Id: 0Problem signature:P1: sqlservr.exeP2: 2009.100.4266.0P3: 5063d9bcP4: MSVCR80.dllP5: 8.0.50727.6195P6: 4dcdd833P7: 000000000000caddP8: c0000409P9: 0000000000000000P10: Analysis symbol: Rechecking for solution: 0Report Id: dd42d838-b902-11e2-b963-d4ae52e84a03Report Status: 4Any suggestions please?

Stored procedure working very slow

Posted: 18 Jul 2013 06:17 AM PDT

CREATE PROCEDURE dbo.NIC_CPP_LABRESULTS_GetReviewedPatientLabTests_AOS@lPatient int,@nRecordStatusint,@nClassLevel int=5,@nSecurityLevelint=0,@lMap_UserToOfficeint=0ASSET TRANSACTION ISOLATION LEVEL READ COMMITTEDBEGINDeclare @lUser int,@lUserActionMappingTable intselect @luser=lUser from map_userToOffice where lid=@lMap_UserToOfficeset @lUserActionMappingTable=32--Disable the AOS SECURITY FOR CPPif @nSecurityLevel=2 set @nSecurityLevel=1if @lMap_UserToOffice>0 and @nSecurityLevel>=2 -- if there is a user and masking is on(bit 1=enabled masking)begin------------- declare @dCurDate datetime, @lOffset int--set @dCurDate=getDate()-- may need to adjust this for users timezoneexec SESSION_GetUserDate @lUser =@luser ,@dUserDate =@dCurDate OUTPUT,@lOffset=@lOffset OUTPUT--This gets the date in the users timezone/*Get the permission ID for this section....*/declare @lCustodianPermission intselect @lCustodianPermission =lid from Custodian_Permissions where szcode='CPP'declare @PhysiciansAccess table (lPhysician int primary key)insert into @PhysiciansAccess (lPhysician)select m.lPhysician from map_UserToCustodian minner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian=m.lid and cp.lCustodianpermission=@lCustodianPermissionwhere m.lUser=@luser and m.nRecordStatus=1 and m.bAllPatient=1 and isNull(m.dStartDate,'1900-01-01')<=@dCurDate and isnull(m.dEndDate,'9000-01-01')>=@dCurDateUnionselect lPhysician from map_UserToCustodian minner join map_UserToCustodian_Per_patient p on p.lmap_UserToCustodian=m.lidinner join map_UserToCustodian_permissions cp on cp.lmap_UserToCustodian_Per_Patient=p.lid and lCustodianpermission=@lCustodianPermissionwhere lUser=@luser and m.nRecordStatus=1 and p.nRecordStatus=1 and bAllPatient=0 and p.lPatient=@lPatientand isNull(p.dStartDate,'1900-01-01')<=@dCurDate and isNull(p.dEndDate,'9000-01-01')>=@dCurDate--only get if the expiry dates are valid--select * from @PhysiciansAccessdeclare @OfficeAccess table (lOffice int primary key)/*go get all offices that the patient is a member offilter out offices that this patient has a custodain and that custodain did not allow access to this userNote: there is no need to filter by section for this query. The Section has been filtered above, and is used in the @PhysiciansAccess listNOTE: the default values for office is -1 if the information was recorded without an office. This means that it is always accessable.*/ insert into @OfficeAccess (lOffice) values(0)insert into @OfficeAccess (lOffice) select map.loffice from map_PatientToOffice mapinner join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.lofficeinner join @PhysiciansAccess PA on PA.lphysician=mapc.lPhysicianwhere map.lpatient=@lPatient--and PA.lPhysician is not nullunion select map.loffice from map_PatientToOffice map left join map_PatientAtOfficeToCustodian mapc on mapc.lpatient=map.lpatient and mapc.loffice=map.loffice where map.lpatient=@lPatient and isNull(mapc.lPhysician,0) <=0-----------SELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected,Case when exists (select * from mask_role inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRoleinner join map_userTOoffice map on map.lid=mapRole.lMap_userToOfficewhere mask_role.nMappingTable=@lUserActionMappingTableand map.lUser=@luserand mask_role.lRowID=p.lidand mask_role.nMaskStatus=1)THEN 1WHen Mu.lid is not nullTHEN 1else 0ENDas bMaskFROM [PhysicianLabReportsTests] a JOIN [PhysicianLabReports] p ON p.lID=a.lLabReportJOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTestleft Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1left join @PhysiciansAccess PA on (PA.lPhysician=p.lPhysician) --LEFT Join for all the physicains the user has access to ALL patient data recorded under that physicianleft join @OfficeAccess O on O.loffice=p.loffice--LEFT Join for all the offices the user has access to ALL patient data in that officeWHERE (p.lPatient=@lPatientAND p.bSignedOff=1AND p.szFiledBy<>'auto'AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusAND y.bSignedOff=1)) = 0)or (p.lPatient=@lPatient-- AND p.bSignedOff=1AND p.szFiledBy='auto'AND a.bAdd2CPP = 1AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusand Z.bAdd2CPP = 1-- AND y.bSignedOff=1)) = 0)and isnull(PA.lPhysician,O.lOffice) is not null--Only return records if EITHER of the left joins returned a matching recordORDER BY b.szDescriptionendelse if @lMap_UserToOffice>0 and @nSecurityLevel>=1 -- if there is a user and masking is on(bit 1=enabled masking)beginSELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory, p.dDateCollected,Case when exists (select * from mask_role inner join map_UserAtOfficeToRole mapRole on mapRole.lUserRole=mask_Role.lRoleinner join map_userTOoffice map on map.lid=mapRole.lMap_userToOfficewhere mask_role.nMappingTable=@lUserActionMappingTableand map.lUser=@luserand mask_role.lRowID=p.lidand mask_role.nMaskStatus=1)THEN 1WHen Mu.lid is not nullTHEN 1else 0ENDas bMaskFROM [PhysicianLabReportsTests] a JOIN [PhysicianLabReports] p ON p.lID=a.lLabReportJOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTestleft Join Mask_User MU on MU.nMappingTable=@lUserActionMappingTable and MU.lUser=@luser and MU.lRowID=p.lid and MU.nMaskStatus=1WHERE (p.lPatient=@lPatientAND p.bSignedOff=1AND p.szFiledBy<>'auto'AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusAND y.bSignedOff=1)) = 0)or (p.lPatient=@lPatient-- AND p.bSignedOff=1AND p.szFiledBy='auto'AND a.bAdd2CPP = 1AND a.nRecordStatus=@nRecordStatusand p.nclassificationlevel>=@nClassLevelAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusand Z.bAdd2CPP = 1-- AND y.bSignedOff=1)) = 0)ORDER BY b.szDescriptionendelsebeginSELECT a.*, b.szDescription, p.dDateOrdered, p.dDateSignedOff, p.szLaboratory,0 as bMask, p.dDateCollectedFROM [PhysicianLabReportsTests] a JOIN [PhysicianLabReports] p ON p.lID=a.lLabReportJOIN [LaboratoryTest] b ON b.lID=a.lPhysicianLabTestWHERE (p.lPatient=@lPatientAND p.bSignedOff=1AND p.szFiledBy<>'auto'AND a.nRecordStatus=@nRecordStatusAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusAND y.bSignedOff=1)) = 0)or (p.lPatient=@lPatient-- AND p.bSignedOff=1AND p.szFiledBy='auto'AND a.bAdd2CPP = 1AND a.nRecordStatus=@nRecordStatusAND DateDiff(d, p.dDateCollected, (SELECT Max(y.dDateCollected)FROM [PhysicianLabReports] yJOIN [PhysicianLabReportsTests] z ON z.lLabReport=y.lIDWHERE y.lPatient=@lPatientAND z.lPhysicianLabTest=a.lPhysicianLabTestAND z.nRecordStatus=@nRecordStatusand Z.bAdd2CPP = 1-- AND y.bSignedOff=1)) = 0)ORDER BY b.szDescriptionendENDGO

Automate restore script

Posted: 18 Jul 2013 05:07 AM PDT

Hi,We have full, differential and log backups into different folders as below:1. Z:\Backups\db_name\full\DB_name_FULL_20130714_013225.bak -- Weekly once2. Z:\Backups\db_name\diff\DB_Name_DIFF_20130717_190949.bak --Daily once3. Z:\Backups\db_name\log\DB_Name_LOG_20130718_110116.trn --every 15 minsRestore:1) I'm able to restore full and diff with no difficulties2) As log backups are running every 15 mins and there are hundreds of files to restore and it's taking hours to restore one-by one.Is there any scripts which will give the auto generated scripts for at least log backups?How you guys do these kind of restores in enterprise level organizations?Thanks

join tables on columns that have different data

Posted: 18 Jul 2013 03:15 AM PDT

I have two tables that have a column representing the same value, but entered differently and I need to join them in a query. What I mean is this:tableA has a column called 'city' and many rows with a value of 'Boston'tableB has a column called 'city' and many rows with a value of 'BO' (BO stands for Boston in this system)I need to join on these columns, but I cannot do:join on tableA.city = tableB.city because the values are not the same. But they represent the same so there has to be a way to do this join.Any help?

Query help

Posted: 18 Jul 2013 03:17 AM PDT

I need to run this query in 100 databases. How to make this as dynamic script and run in all databases at once and get results?SELECT * FROM Docs WHERE (VirusStatus > 0) AND (VirusStatus IS NOT NULL)

SSIS in cluster

Posted: 12 Jul 2013 03:53 AM PDT

I have two nodes nodeA and nodeB on a sql server on a fail over clustered environment. Each of these nodes have two sql server databases that I am interested in. I need to deploy SSIS packages to this cluster environment using XML configuration and file system deployment. Now in the config file i am not quite sure which node names(server names) i should use because the nodes name should switch when a failure occurs? Please advise.

HEAP

Posted: 18 Jul 2013 02:21 AM PDT

Hi,I am looking for a script that will give all Heaps (user tables only ) in a sql server 2008 database. I have the following script but getting all of the tables/objects. Thanks.use AdventureWorksGOSELECT SCHEMA_NAME(o.schema_id) AS [Schema],object_name(A1.object_id ) AS [Table Name] , B1.rows ,user_seeks ,user_scans,user_lookups ,user_updates ,last_user_seek ,last_user_scan ,last_user_lookupFROM sys.indexes A1INNER JOIN sys.objects o ON A1.object_id = o.object_idINNER JOIN sys.partitions B1 ON A1.object_id = B1.object_id AND A1.index_id =B1.index_idLEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON A1.object_id =ius.object_id AND A1.index_id = ius.index_idWHERE A1.type_desc = 'HEAP'ORDER BY rows desc

How to dynamically create my FILEGROUP during a restore?

Posted: 18 Jul 2013 12:22 AM PDT

How can I dynamically create my FILEGROUP during a restore?My FILELISTONLY renders[font="Courier New"]LogicalName PhysicalName Type FileGroupName=========== ============ ==== ============= MyDBData_Primary G:\SQLData\Archive_Primary.mdf D PRIMARYMyDBData_1 G:\SQLData\MyDB_1.ndf D DataFileGroupMyDBData_2 G:\SQLData\MyDB_2.ndf D DataFileGroupMyDBLog L:\SQLLogs\MyDB.ldf L NULL[/font]How can I tweak the following RESTORE command to dynamically generate the FileGroupName called 'DataFileGroup' listed above?RESTORE DATABASE MyDB FROM DISK='H:\SQLBACKUPS\MyDB.bak' WITH RECOVERY, REPLACE, MOVE 'MyDBData_Primary' TO 'E:\SQLData\MyDB_Primary.mdf', MOVE 'MyDBData_1' TO 'E:\SQLData\MyDB_1.ndf', MOVE 'MyDBData_2' TO 'E:\SQLData\MyDB_2.ndf', MOVE 'MyDBLog' TO 'F:\SQLLogs\MyDB.ldf'GO

Force SQL jobs to run serially, not in parallel

Posted: 17 Jul 2013 10:24 PM PDT

In SQL 2008 R2 (sp2) our vendor requires that thier 14 SQL database FULL backups execute in a sequential order. (to preserve data integrity in the event of a restore) EG. DB_1 gets backed up, when finished, DB_2 gets backed up, etc. (thes 14 FULL Backups cannot run in parallel)I'm familiar with starting a job via: EXEC msdb.dbo.sp_start_job @ScheduledJobName --- but thinking, if I submit 14 of these EXEC msdb.dbo.sp_start_job statements -- they'll initiate asynchronously and run in paralllel.Anyone have a script or suggestion which will run jobs, serially, waiting for the previous job to complete? thx

Active Users

Posted: 17 Jul 2013 08:19 PM PDT

Hi,How do i find out the number of users having an active transaction at the moment by using T-SQL Statement

SQL Replication details

Posted: 17 Jul 2013 07:36 PM PDT

HiCan anyone point me to any DMV or anything else that can provide a history of when information was replicated? I need to try and find out when the busiest times of replication are.Thanks
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog