Wednesday, July 24, 2013

[SQL Server 2008 issues] DB Backup Script

[SQL Server 2008 issues] DB Backup Script

DB Backup Script

Posted: 23 Jul 2013 04:48 PM PDT

i am using a very simple script to take back up of a TEST Database on production server.SCRIPT IS:use masterbackup database bharti_testto disk=N'E:\Reports'with copy_only;Now,,, this script is running sucessfully. but back up file is not in the desired the folder i.e. E:\ReportsNeed Help,,,, ASAP...!!!! :-)

grant access to all databases

Posted: 17 Nov 2010 06:04 PM PST

hello experts, i have around 600 databases in my server, a user need select access of all the databases. will i have to go one by one in all the dbs and create tht user and give datareader role to him. or is thr any shorter way to do so????thanks in advance

RAID 5 Vs RAID 10 Performance Questions

Posted: 23 Jul 2013 08:55 AM PDT

I'm guessing this could lead to a lot of theory and complex answers, however, I'm curious on your thoughts between RAID 5 and RAID 10. Yes, I realize that RAID 10 is faster than RAID 5, but is it fast enough to justify the potential cost hit (i.e. from what I've researched, 4k vs $10k). We are currently running a large set of databases with about equal distribution between reads and writes, leaning slightly towards writes. Figure into this about 1 million+ rows written per day. I am currently experiencing IO issues and have had some success by cleaning up indexes and performing other maintenance tasks. If I can prove that there will be a significant performance gain, it's possible we would consider switching to Raid 10. Any thoughts, experience, good article references?Thanks.

debugging question about non-yielding scheduler issue

Posted: 20 Feb 2013 12:08 PM PST

Hi all, One of our SQL servers keeps crashing and briefly analyzed the dump file and found this:Child-SP RetAddr Call Site00000081`683fd0e8 000007fe`fda84bf6 ntdll!NtCreateFile+0xa00000081`683fd0f0 00000000`76c718ed KERNELBASE!CreateFileW+0x2cd*** ERROR: Symbol file could not be found. Defaulted to export symbols for sqlservr.exe - 00000081`683fd250 00000000`01f72617 kernel32!CreateFileWImplementation+0x7d00000081`683fd2b0 00000000`01f7513f sqlservr!GetIUMSForMsxml+0x8055f300000081`683fd340 00000000`00c53aa0 sqlservr!GetIUMSForMsxml+0x80811b00000081`683fd390 00000000`00c54a5a sqlservr+0xbd3aa000000081`683fd3e0 00000000`00c5547b sqlservr+0xbd4a5a00000081`683fd420 00000000`00c550e1 sqlservr+0xbd547b00000081`683fd4f0 00000000`01989cc5 sqlservr+0xbd50e100000081`683fd580 00000000`000c67a4 sqlservr!GetIUMSForMsxml+0x21cca100000081`683fd5d0 00000000`001027b4 sqlservr+0x467a400000081`683fd600 00000000`00146525 sqlservr+0x827b400000081`683fd660 00000000`001460db sqlservr+0xc652500000081`683fd690 00000000`00101e05 sqlservr+0xc60db00000081`683fd730 00000000`001021d8 sqlservr+0x81e0500000081`683fd760 00000000`0014cef5 sqlservr+0x821d800000081`683fd8f0 00000000`015b7c97 sqlservr+0xccef500000081`683fd990 00000000`015b7f0b sqlservr!_____SQL______Process______Available+0x713d4700000081`683fda80 00000000`01527c45 sqlservr!_____SQL______Process______Available+0x713fbb00000081`683fdab0 00000000`006eacba sqlservr!_____SQL______Process______Available+0x683cf500000081`683fea60 00000000`000ec3bc sqlservr+0x66acba00000081`683febe0 00000000`000ed3ab sqlservr+0x6c3bc00000081`683fed10 00000000`0012a4c2 sqlservr+0x6d3ab00000081`683ff370 00000000`0008f280 sqlservr+0xaa4c200000081`683ff580 00000000`0008ee3e sqlservr+0xf28000000081`683ff690 00000000`0008eb3b sqlservr+0xee3e00000081`683ff6f0 00000000`001b5caa sqlservr+0xeb3b00000081`683ff760 00000000`001b5e25 sqlservr+0x135caa00000081`683ff840 00000000`001b567d sqlservr+0x135e2500000081`683ff880 00000000`001b6586 sqlservr+0x13567d00000081`683ff930 00000000`74b137d7 sqlservr+0x13658600000081`683ff9d0 00000000`74b13894 msvcr80!_callthreadstartex+0x1700000081`683ffa00 00000000`76c7652d msvcr80!_threadstartex+0x8400000081`683ffa30 00000000`7736c521 kernel32!BaseThreadInitThunk+0xd00000081`683ffa60 00000000`00000000 ntdll!RtlUserThreadStart+0x1I couldn't find anything related with this in anywhere so wonder if anyone knows about this.Can someone help me on this?Thanks much!!

Need procedure to delete .bak

Posted: 23 Jul 2013 05:03 AM PDT

Hi ,I had scheduled backups in Express edition 2008 using cmd and taskmanager. But I wantedto have an automated process to delete the .bak files rather doing manually. Please suggest me some steps or procedure.

checkpoint for committed transactions..

Posted: 23 Jul 2013 08:11 AM PDT

HiCan some body say that Is Checkpoint will write pages only from committed transactions...please help me on this.

TempDB: need to resize Data File

Posted: 23 Jul 2013 05:59 AM PDT

Hello EveryoneData File for TempDB is 32 GB big in File size and it uses only 13.5 MB so I need to re size the data file to 1GB. This is production server so please help me to do so with out restarting server.[code="plain"]name filename FileSizeMB SpaceUsedMB FreeSpaceMBtempdev S:\Data\tempdb.mdf 32617.81 13.44 32604.38templog S:\Data\templog.ldf 672.25 456.84 215.41[/code]Thanks for Your Help

Utilizing Fake Partitioning with a View

Posted: 23 Jul 2013 08:40 AM PDT

So you are running MS SQL non enterprise edition, meaning you can not utilize true partition tables. You have a 1 billion record sales fact table that contains 5 years of data called Sales_Table. You decide to break this table into 5 different ones, one for each year :[size="1"][b]Sales_Table_Y01Sales_Table_Y02Sales_Table_Y03Sales_Table_Y04Sales_Table_Y05[/b][/size]Under the assumption that each table is properly indexed, you now need to create a one location for existing code to hit all the data, thus we think of a view. So you make a view called Sales_Table so past code is seemless. The question at hand, is how to construct this view so it runs better or just as good as the 1 billion record table. Keep in mind throwing union statements such as the following will not allow for schemabinding to index the view:[b][size="1"]select col1, col2 from Sales_Table_Y01union allselect col1, col2 from Sales_Table_Y02union allselect col1, col2 from Sales_Table_Y03union allselect col1, col2 from Sales_Table_Y04union allselect col1, col2 from Sales_Table_Y05[/size][/b]Thanks in advance.

Looking for clean way to aggregate the same column multiple times in my query

Posted: 23 Jul 2013 08:00 AM PDT

Looking for tips on how best to approach the following query - 10million rows. Subquery selects, sum, CTE, over with Partionioning, etc . Is there a better way to do this in 2012? using Windowing - My code base is 2008. [code="sql"]SELECT[DateofScan],[DeviceType] ,count ofdistinct [PlugInID],count ofdistinct [PlugInID] that are Critical,count ofdistinct [PlugInID] where [VulNotDate] is NULL --'1900-01-01',Count ofdistinct (PlugInID] where [VulNotDate] <= 90 daysofSan,Count ofdistinct (PlugInID] where [VulNotDate] > 90 daysofSanFROM [TCH_Metrix].[dbo].[VulMgmt]GROUP BYDateofScan, DeviceType[/code]

Indexes

Posted: 23 Jul 2013 08:35 AM PDT

Hi All,I have a table [BusinessProcesses] with BpId as primary/clustered key/index on it.I have the following index on column CorrespondingContractNumber:[code="sql"]CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber] ON [dbo].[BusinessProcesses]([CorrespondingContractNumber] ASC)[/code]I execute:dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber')/*All density Average Length Columns8.592541E-05 1.272107 CorrespondingContractNumber1.788491E-08 5.272107 CorrespondingContractNumber, BPId*/I create the next index:[code="sql"]CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber2] ON [dbo].[BusinessProcesses]([CorrespondingContractNumber] ASC)INCLUDE(BpId)[/code]I execute:dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber2')/*All density Average Length Columns3.906723E-06 1.311324 CorrespondingContractNumber*/The second index is more selective.I execute this example query:[code="sql"]select CorrespondingContractNumber from BusinessProcesseswhere CorrespondingContractNumber is not null[/code]and the second index is being used by the query optimizer. Conclusion: The second index (IBPCorrespondingContractNumber2) is narrower and the optimizer simply uses it.Then i create[code="sql"]CREATE NONCLUSTERED INDEX [IBPCorrespondingContractNumber3] ON [dbo].[BusinessProcesses]( [CorrespondingContractNumber] ASC, bpid asc)[/code]dbcc show_statistics('dbo.BusinessProcesses','IBPCorrespondingContractNumber3')/*All density Average Length Columns3.906723E-06 1.311324 CorrespondingContractNumber1.788491E-08 5.311324 CorrespondingContractNumber, BPId*/I execute this example query:[code="sql"]select CorrespondingContractNumber from BusinessProcesseswhere CorrespondingContractNumber is not null[/code]Now the optimizer uses IBPCorrespondingContractNumber3 which is not narrower thatn IBPCorrespondingContractNumber2I need someones of you to put here your thinking about this...Thank you in advance,IgorMi

Stored procedure really slow..Sugestions?

Posted: 23 Jul 2013 06:28 AM PDT

[code="xml"]USE []GOALTER PROCEDURE [dbo].[NIC_ENTERPRISE_PatientActivityLogSearch] @lUser INT, @szFirst VARCHAR(20) = '', @szLast VARCHAR(40) = '', @szChartNum VARCHAR(10) = NULL, @SDate DATETIME = NULL, @EDate DATETIME = NULL, @szIdentifierValue VARCHAR(50) = '', @lIdentifierType AS INT = NULL, @nSex AS INT = NULL, @bSearchInactive AS BIT = 0, @szPhoneArea VARCHAR(10) = '', @szPhone1 VARCHAR(10) = '', @szPhone2 VARCHAR(10) = '', @DayDOB VARCHAR(10) = NULL, @MonthDOB VARCHAR(10) = NULL, @YearDOB VARCHAR(10) = NULL, @lEnterprise INTAS /** if there is any aduit record in DB for current Enterprise, the sp will check aduit, otherwise ignore the aduit check **/ if exists (select lid from MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) where luserAction=83 and lUserActionType=5 and lEnterprise=@lenterprise) update MAP_AuditActionTypeTableToMappingTable set bIsavailable=1 where luserAction=83 and lUserActionType=5 and lEnterprise=@lenterprise if exists (select lid from MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) where luserAction=84 and lUserActionType=5 and lEnterprise=@lenterprise) update MAP_AuditActionTypeTableToMappingTable set bIsavailable=1 where luserAction=84 and lUserActionType=5 and lEnterprise=@lenterpriseDECLARE @sSQLSelect NVARCHAR(4000)DECLARE @sSQLSearch NVARCHAR(4000) SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ COMMITTED SET @sSQLSelect = '' SET @sSQLSearch = '' IF @sDate IS NOT NULL AND @sDate <> '' AND @eDate IS NOT NULL AND @eDate <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND UALog.dDateOfAction BETWEEN ''' + CONVERT(VARCHAR, @SDate) + '''' + ' AND '+'''' + CONVERT(VARCHAR, @eDate ) + '''' END IF @szLast IS NOT NULL AND @szLast <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND p.szLast LIKE ''' + @szLast + '%''' END IF @szFirst IS NOT NULL AND @szFirst <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND p.szFirst LIKE ''' + @szFirst + '%''' END IF @szChartNum IS NOT NULL AND @szChartNum <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND p.szChartNum LIKE ''' + @szChartNum + '%''' END /* Gender check*/ IF @nSex IS NOT NULL BEGIN SET @sSQLSearch = @sSQLSearch + ' AND p.nSex = ' + CONVERT(VARCHAR, @nSex) END /* Check for date of birth */ IF @YearDOB IS NOT NULL AND @YearDOB <> '' AND @MonthDOB IS NOT NULL AND @MonthDOB <> '' AND @DayDOB IS NOT NULL AND @DayDOB <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND p.dDOB= ''' + @YearDOB + '-' + @MonthDOB + '-' + @DayDOB + ''' ' END ELSE BEGIN /* Year part of Date of Birth */ IF @YearDOB IS NOT NULL AND @YearDOB <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND Year(p.dDOB)= ''' + @YearDOB + '%'' ' END /* Month part of Date of Birth */ IF @MonthDOB IS NOT NULL AND @MonthDOB <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND Month(p.dDOB)= ''' + @MonthDOB + '%'' ' END /* Day part of Date of Birth */ IF @DayDOB IS NOT NULL AND @DayDOB <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND Day(p.dDOB)= ''' + @DayDOB + '%'' ' END END /* First part of Phone number */ IF @szPhoneArea IS NOT NULL AND @szPhoneArea <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND a.szPhoneArea LIKE ''' + @szPhoneArea + '%''' END /* Second part of Phone number */ IF @szPhone1 IS NOT NULL AND @szPhone1 <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND a.szPhone1 LIKE ''' + @szPhone1 + '%''' END /* Third part of Phone number */ IF @szPhone2 IS NOT NULL AND @szPhone2 <> '' BEGIN SET @sSQLSearch = @sSQLSearch + ' AND a.szPhone2 LIKE ''' + @szPhone2 + '%''' END IF EXISTS ( SELECT TOP 1 lid FROM MAP_AuditActionTypeTableToMappingTable WITH (NOLOCK) WHERE lEnterprise = @lEnterprise ) BEGIN --with audit record IF @lUser = 0 BEGIN IF @szIdentifierValue = '' BEGIN SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALog.dDateOfAction AS dAction, UALC.szComment, mapAudit.bisavailable FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction --Added by Roger for Audit AND mapAudit.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) +' AND mapAudit.lUserActionType = UALog.lUserActionType ' + ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog WHERE 1=1 ' + @sSQLSearch + ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) + ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END ELSE BEGIN SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, UALog.dDateOfAction AS dAction, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALC.szComment, mapAudit.bIsavailable FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction --Added by Roger for Audit AND mapAudit.lEnterprise = ' + CAST( @lEnterprise as varchar ) +' AND mapAudit.lUserActionType = UALog.lUserActionType ' +' INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient AND e.lUserIdentifierType = ' + CAST (@lIdentifierType AS VARCHAR) + ' AND e.nStatus = 1 ' + ' AND e.sValue = ''' + @szIdentifierValue +'''' + ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog WHERE 1=1 ' + @sSQLSearch + ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) + ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END END ELSE BEGIN IF @szIdentifierValue = '' BEGIN SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALog.dDateOfAction AS dAction, UALC.szComment, mapAudit.bIsavailable FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction --Added by Roger for Audit AND mapAudit.lEnterprise = '+ CAST (@lEnterprise AS VARCHAR) + ' AND mapAudit.lUserActionType = UALog.lUserActionType ' SET @sSQLSelect = @sSQLSelect + ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog ' SET @sSQLSelect = @sSQLSelect + ' WHERE map.lUser = '+ CAST( @lUser AS VARCHAR)+ @sSQLSearch + ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) + ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END ELSE BEGIN SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALog.dDateOfAction AS dAction, UALC.szComment, mapAudit.bIsavailable FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient AND e.lUserIdentifierType = ' + CAST (@lIdentifierType AS VARCHAR) + ' AND e.nStatus = 1 ' + ' AND e.sValue = ''' + @szIdentifierValue +'''' +' LEFT JOIN MAP_AuditActionTypeTableToMappingTable mapAudit WITH (NOLOCK) ON mapAudit.lUserAction = UALog.lUserAction AND mapAudit.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) +' AND mapAudit.lUserActionType = UALog.lUserActionType ' SET @sSQLSelect = @sSQLSelect + ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog ' SET @sSQLSelect = @sSQLSelect + + ' WHERE map.lUser = ' + CAST (@lUser AS VARCHAR) + @sSQLSearch + ' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) + ' AND ISNULL(mapAudit.bIsavailable, 1) = 1 ' + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END END END --with audit record ELSE BEGIN --without audit record IF @lUser = 0 BEGIN IF @szIdentifierValue = '' BEGIN SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALog.dDateOfAction AS dAction, UALC.szComment FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog WHERE 1=1 ' + @sSQLSearch +' AND o.lEnterprise = ' + CAST( @lEnterprise AS VARCHAR) + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END ELSE BEGIN PRINT 'SIX' SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, UALog.dDateOfAction AS dAction, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALC.szComment FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient AND e.lUserIdentifierType = ' + CAST (@lIdentifierType AS VARCHAR) + ' AND e.nStatus = 1 ' + ' AND e.sValue = ''' + @szIdentifierValue +'''' + ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog WHERE 1=1 ' + @sSQLSearch +' AND o.lEnterprise = ' + CAST(@lEnterprise AS VARCHAR) + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END END ELSE BEGIN IF @szIdentifierValue = '' BEGIN PRINT 'SEVEN' SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, UALog.dDateOfAction AS dAction, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALC.szComment FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog WHERE map.lUser = '+ CAST( @lUser AS VARCHAR)+ @sSQLSearch +' AND o.lEnterprise = ' + CAST(@lEnterprise AS VARCHAR) + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END ELSE BEGIN SET @sSQLSelect = @sSQLSelect + ' SELECT UALog.*, p.szFirst, p.szLast, p.szChartNum, o.szOfficeName, UA.szAction, UAT.szType + '' '' + UA.szAction AS szFullDescription, u.szLast AS szUserLast, u.szFirst AS szUserFirst, UALog.dDateOfAction AS dAction, UALC.szComment FROM UserActionLog UALog WITH (NOLOCK) INNER JOIN UserAction UA WITH (NOLOCK) ON UALog.lUserAction = UA.lID INNER JOIN UserActionType UAT WITH (NOLOCK) ON UAT.lid = UALog.lUserActionType INNER JOIN Map_UserToOffice map WITH (NOLOCK) ON UALog.lMap_UserToOffice = map.lID INNER JOIN Office o WITH (NOLOCK) ON map.lOffice = o.lID INNER JOIN [User] u WITH (NOLOCK) ON map.lUser = u.lID INNER JOIN Patient p WITH (NOLOCK) ON UALog.lPatient = p.lID LEFT JOIN Map_PatientToOffice mapPO WITH (NOLOCK) ON o.lID = mapPO.lOffice AND p.lID = mapPO.lPatient INNER JOIN Address a WITH (NOLOCK) ON p.lAddress = a.lID INNER JOIN UserIdentifier e WITH (NOLOCK) ON p.lid = e.lPatient AND e.lUserIdentifierType = ' + CAST ( @lIdentifierType AS VARCHAR ) +' AND e.nStatus = 1 ' + ' AND e.sValue = ''' + @szIdentifierValue +'''' + ' LEFT JOIN UserActionLogComment UALC WITH (NOLOCK) ON UALog.lid = UALC.lUserActionLog WHERE map.lUser = '+ CAST( @lUser AS VARCHAR)+ @sSQLSearch +' AND o.lEnterprise = ' + CAST(@lEnterprise AS VARCHAR) + ' ORDER BY dAction' exec sp_executesql @sSQLSelect END END END --without audit record[/code]

Page level compression - Indices/Partitions/Heap

Posted: 23 Jul 2013 04:49 AM PDT

I have read a lot of articles on compression but i couldn't find a definitive answer for these:i) if a table has no clustered index and has non-clustered indices, do i need to compress the table first and then the nc indices?ii)if a table has clustered index and has non-clustered indices, would just compressing the clustered index also compress the nc index?iii) When compressing the clustered index, can this be with ONLINE=ON option?

After Insert Trigger won't fire - Same Trigger exists on 35 other tables working perfectly - Why won't THIS one fire?

Posted: 23 Jul 2013 04:19 AM PDT

[b]AFTER INSERT TRIGGER WON'T FIRE[/b]The same trigger exists on 35 other tables working perfectly. Why won't this one fire?USE [UVAHS_Metadata]GO/****** Object: Trigger [mdl].[Item_After_Insert_Trgr] Script Date: 07/23/2013 13:14:41 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Trigger: Item_After_Insert_Trgr ALTER TRIGGER [mdl].[Item_After_Insert_Trgr] ON [mdl].[Item]AFTER INSERTASBEGINUPDATE mdl.ItemSET Item.Item_Updt_Dtm = GETDATE (), Item.Item_Add_User_PK = ( SELECT UVAHS_User_PK FROM mdl.UVAHS_User WHERE (UVAHS_User.UVAHS_User_Id = LTRIM(RTRIM(REPLACE(SYSTEM_USER,'HSCDOM\', ' '))) )), Item.Item_Updt_User_PK = ( SELECT UVAHS_User_PK FROM mdl.UVAHS_User WHERE (UVAHS_User.UVAHS_User_Id = LTRIM(RTRIM(REPLACE(SYSTEM_USER,'HSCDOM\', ' '))) ))FROM inserted iWHERE Item.Item_PK = i.Item_PKEND

Page Compression - Syntax

Posted: 23 Jul 2013 05:24 AM PDT

is this syntax right for compression. I have a table which is partitioned, has a clus idx and two nc index.. i want enable page level compression on all indices and partitions..is this syntax right? Thanks[code="other"]ALTER INDEX [PK_CluIndx] ON [dbo].[MyTable] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF ) GOALTER INDEX [Nc_1] ON [dbo].[MyTable] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF )ALTER INDEX [NC_2] ON [dbo].[MyTable] REBUILD PARTITION = ALL WITH ( DATA_COMPRESSION = PAGE, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = ON, SORT_IN_TEMPDB = OFF ) [/code]

Viewing an xml plan as a graphical plan

Posted: 22 Jul 2013 09:22 PM PDT

I am having trouble viewing the query plan as a graphical plan. I am running 2008 r2 sp1 on the servers and i am using ssms version 10.50.2550.0 on my laptopI have a query which returns the plans in the cache and it outputs a link such as:<ShowPlanXML xmlns="http://schemas.microsoft.........>which i click on and i should get a graphical representation in management studio right ? I was about to post that all i get is the raw xml code when i click on these links, however i have just clicked on one of them and it has returned me the graphical execution plan as expected ? The query has returned 500 rows ( i had to stop it running as it was taking a long time) and up to now 99% of the links just return the raw xml and not the graphical one. Why are they not all returning me the graphical version of the plan ?Can somebody explain why this is ?

Errors while querying over VPN connection after moving to Windows 7 64-bit

Posted: 23 Jul 2013 04:02 AM PDT

I am having trouble executing queries on a new Windows 7 64-bit machine. The target database is accessed through a Cisco VPN tunnel. I have tried to use SSMS from the original install and from an Express installer. I also tried both 32 and 64 bit installs of SSMS. All result in the same errors.My errors are very similar to the ones found in this [url=http://connect.microsoft.com/SQLServer/feedback/details/361659/ssms-bad-client-side-errors]posting[/url]. But I have issues while I am connected to the VPN connection. I am not sure how to troubleshoot this as the error changes. However, if I query to a local database, I don't get the same error all the time, but they seem to occur when the query is returning many rows. I do have success when the queries are simple and only affect very few rows.My local database is running 10.0.1600 and the remote database is running 10.0.4000, and this is working with existing XP machines.

Snapshot Replication Error

Posted: 23 Jul 2013 02:52 AM PDT

Hey guys,So I have setup replication between our source production database to replicate to our reporting database so we are not hitting our production database for reporting...I set this up 2 months ago and everything was running fine...Until about 5 days ago, it started failing every night, when I go to look at the job history and see the big red X and look at the error, it gives me this:MessageExecuted as user: TRH\mssql. String or binary data would be truncated. [SQLSTATE 22001] (Error 8152). The step failed.For some reason I was under the impression it was failing during the distribution step of replication...I know what that error means but I started digging...This was yesterday - I created a backup of the source, and restored it onto reporting...Was going to see if that helped the issue...I check this morning and nope same failure, same message...So I have this redgate tool SQL Compare...It compares the schema's of the two databases! Once it finished, it showed no differences and 22723 identical objects!When looking back at the error, the light bulb came on and was like this is failing on the snapshot portion of replication! Now it makes no sense to me why I would get that error when trying to create a snapshot??? Its not pushing data anywhere so I dont know why this error would be thrown...Looking at the job history and expanding down on the today's date with big red X, I noticed some of the lines had a blue curved arrow and some of the lines have a the successful green play button, but looking at the blue curved arrow line, I noticed this:2013-07-23 07:17:43.55 [8%] The replication agent had encountered an exception.2013-07-23 07:17:43.55 Source: Replication2013-07-23 07:17:43.55 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException2013-07-23 07:17:43.55 Exception Message: Failed to read column data2013-07-23 07:17:43.55 Message Code: 02013-07-23 07:17:43.55 2013-07-23 07:17:43.55 [8%] The replication agent had encountered an exception.2013-07-23 07:17:43.55 Source: Replication2013-07-23 07:17:43.55 Exception Type: Microsoft.SqlServer.Replication.NativeSqlConnectionException2013-07-23 07:17:43.55 Exception Message: Failed to read column data2013-07-23 07:17:43.55 Message Code: 02013-07-23 07:17:43.55Now let me say this again, there was not a red X on this line, just the blue curved arrow! and the line above it is a successful green one...Then it 2 lines above it is when I see the red X errors with the String or Binary data would be truncated...Details of the Snapshot Replication:Source to ReportingA bunch of articles - like at least 10,000Security Settings - Run under the SQL Server Agent service account , Connect to the publisher by impersonating the process accountRuns every morning around 3 amCan anyone give me any insight as to what might be going on here?

Query taking non-consistent time to execute

Posted: 22 Jul 2013 08:19 PM PDT

I have a query which takes large time to execute sometimes.When i checked the speed it was taking 15 seconds.But actually it should run faster than that.When i again checked the query it again executed with in 11 sec.Then i tried removing cast used in query and when executed it ran just in 8 seconds.But when i again the original query(with cast) it takes less time.I checked in several computers running the query.But in some cases i get output just in 1 or 2 seconds also.So same query takes different time to execute in different pc and in same pc also time required is not consistent.a So there is no consistency with the time taken to execute.So i am not able to find why this happens?Below is the query i used to test[code="sql"]SELECT [customer].[Customer name],[customer].[Sl_No],[customer].[Id]FROM [company].dbo.[customer]WHERE ( Charindex('9000413237',CAST([company].dbo.[customer].[Phone no] AS VARCHAR(MAX)))>0 ) [/code]Here customer table has about 30 columns.In that 'Customer name' is of datatype varchar(255),'Sl_No' is of int,'Id' is of int and 'Phone no' is of varchar(255).

differences

Posted: 22 Jul 2013 08:52 PM PDT

what are the differences while installing sql 2005 vs sql 2008 ?

Conditional Join

Posted: 22 Jul 2013 08:28 PM PDT

Hi,I have 3 tables TableA, TableB, TableC.I have to update data in TableA with TableB or TableCIf there is data at least one row in TableB , I have to completely ignore TableC and do a INNER JOIN between TableA & TableB.If there is no data at all in TableB, Then I have to join with TableC and has to update TableA.Plz help...

exclude specific tables during the database restore

Posted: 22 Jul 2013 09:41 PM PDT

Hi All,Is there a way to exclude specific tables during the SQL database restore?Thanks

view pdf in report

Posted: 22 Jul 2013 07:48 PM PDT

HIPlease assist. I have a table with PDF files. I would like to display these files in a report.i have the following table:Num Type FileSource1 pdf 0x25044462D ........... I would like to view the pdf file in a report.I've tried the following:1. Add table, replace textbox with rectangle and add image. Image source is from Database and MIME type is PNG. When I run the report I get the following error: "An error ocurred during client rendering. Parameter is not valid".2. Tried to create a Hyperlink to open PDF - Textbox Properties, Action, Go to URL, Select URL = FileSource. The hyperlink does not work.Please HELP!!! :w00t:Thank you, Monique
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog