Thursday, June 6, 2013

[MS SQL Server] SSRS report Memory limit

[MS SQL Server] SSRS report Memory limit

SSRS report Memory limit

Posted: 21 May 2013 08:15 PM PDT

I want limit report server for cpu and memory.Thanks in Advance.

Access to Snapshot only

Posted: 06 Jun 2013 03:42 AM PDT

I want to give a group of users access to run select queries on a database snapshot but not the live database, but snapshots are Read-Only and that includes the security configuration.One solution might be to run a script that1) Adds the users to the live database db_datareader role.2) Creates the database snapshot3) Drops the users from the live database db_datareader role.Is there a cleaner way to do this?

Create Job to run indefinitely

Posted: 05 Jun 2013 11:59 PM PDT

Hi experts,Can anyone please help me create a job that should run indefinitely i mean the the script should keep on running.Thanks in Advance

OpenXML Query !

Posted: 06 Jun 2013 01:17 AM PDT

Folks:I need help with XML query. I have this XML data and I'm using OPENXML to shred the data into table. For each ModelValue (/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue) I'm writing 12 insert statements (in this example I have only 3 insert statements) and each insert takes around like 2 to 3 seconds because of the huge XML data which has 46K lines (attached example has less data). Can this be done in a better way? Will cross apply be faster? I have never used cross apply before so any help on the query will be appreciated.Thanks ![code="sql"]DECLARE @ADataOutputImportEntries XMLSET @ADataOutputImportEntries='<DataOutputEntries> <ARun> <JobId>9390678</JobId> <JobEntry>1088801</JobEntry> <SKey>Tester=1.000/EquationTesting=Zero</SKey> <Label>TESTING</Label> <SecXml> <Sec> <ADate>2012-03-30</ADate> <SId>10508</SId> <Name>320John</Name> <Ticker /> <Cip>328T7</Cip> <SDate>2012-01-30</SDate> <SRateArr> <SRateArrValues> <CollType>All</CollType> <GroupNumber>0</GroupNumber> <GroupName>TOTAL</GroupName> <ModelValue> <Date>3/1/2012</Date> <CurrentToThirtyTo59>0.0442587393797255</CurrentToThirtyTo59> <CurrentToVoluntaryPrepay>0.00112852461455603</CurrentToVoluntaryPrepay> <ThirtyTo59ToCurrent>0.239157153759315</ThirtyTo59ToCurrent> <ThirtyTo59ToSixtyTo89>0.241455128769983</ThirtyTo59ToSixtyTo89> <NinetyPlusToInvoluntaryPrepay>0.0179558248028735</NinetyPlusToInvoluntaryPrepay> <CurrentToCurrent>0.954612736005718</CurrentToCurrent> <ThirtyTo59ToThirtyTo59>0.518541993321672</ThirtyTo59ToThirtyTo59> <SixtyTo89ToSixtyTo89>0.340082560007853</SixtyTo89ToSixtyTo89> <NinetyPlusToNinetyPlus>0.83600521461896</NinetyPlusToNinetyPlus> <SixtyTo89ToCurrent>0.103180895929781</SixtyTo89ToCurrent> <NinetyPlusToCurrent>0.0441401509179685</NinetyPlusToCurrent> <SixtyTo89ToNinetyPlus>0.298778375525492</SixtyTo89ToNinetyPlus> </ModelValue> <ModelValue> <Date>4/1/2012</Date> <CurrentToThirtyTo59>0.0499544306845915</CurrentToThirtyTo59> <CurrentToVoluntaryPrepay>0.00109431768188259</CurrentToVoluntaryPrepay> <ThirtyTo59ToCurrent>0.199535150055279</ThirtyTo59ToCurrent> <ThirtyTo59ToSixtyTo89>0.273414973899818</ThirtyTo59ToSixtyTo89> <NinetyPlusToInvoluntaryPrepay>0.0176055701572801</NinetyPlusToInvoluntaryPrepay> <CurrentToCurrent>0.948678229151541</CurrentToCurrent> <ThirtyTo59ToThirtyTo59>0.525995695441001</ThirtyTo59ToThirtyTo59> <SixtyTo89ToSixtyTo89>0.337274044725917</SixtyTo89ToSixtyTo89> <NinetyPlusToNinetyPlus>0.836278799696028</NinetyPlusToNinetyPlus> <SixtyTo89ToCurrent>0.0814687575120575</SixtyTo89ToCurrent> <NinetyPlusToCurrent>0.0441429483508975</NinetyPlusToCurrent> <SixtyTo89ToNinetyPlus>0.337497252914493</SixtyTo89ToNinetyPlus> </ModelValue> </SRateArrValues> <SRateArrValues> <CollType>FloatingRate</CollType> <GroupNumber>6</GroupNumber> <GroupName>2A</GroupName> <ModelValue> <Date>6/1/2012</Date> <CurrentToThirtyTo59>0.0611408854165271</CurrentToThirtyTo59> <CurrentToVoluntaryPrepay>0.000678007870239213</CurrentToVoluntaryPrepay> <ThirtyTo59ToCurrent>0.163265030897998</ThirtyTo59ToCurrent> <ThirtyTo59ToSixtyTo89>0.327657418802434</ThirtyTo59ToSixtyTo89> <NinetyPlusToInvoluntaryPrepay>0.015565453655582</NinetyPlusToInvoluntaryPrepay> <CurrentToCurrent>0.937548253411662</CurrentToCurrent> <ThirtyTo59ToThirtyTo59>0.50798882336555</ThirtyTo59ToThirtyTo59> <SixtyTo89ToSixtyTo89>0.341381810295919</SixtyTo89ToSixtyTo89> <NinetyPlusToNinetyPlus>0.845436300707964</NinetyPlusToNinetyPlus> <SixtyTo89ToCurrent>0.0623208735561162</SixtyTo89ToCurrent> <NinetyPlusToCurrent>0.0408626114855406</NinetyPlusToCurrent> <SixtyTo89ToNinetyPlus>0.387547418575595</SixtyTo89ToNinetyPlus> </ModelValue> <ModelValue> <Date>7/1/2012</Date> <CurrentToThirtyTo59>0.0620749359821378</CurrentToThirtyTo59> <CurrentToVoluntaryPrepay>0.000688643874234245</CurrentToVoluntaryPrepay> <ThirtyTo59ToCurrent>0.163887436671472</ThirtyTo59ToCurrent> <ThirtyTo59ToSixtyTo89>0.326071061950598</ThirtyTo59ToSixtyTo89> <NinetyPlusToInvoluntaryPrepay>0.0152817269357896</NinetyPlusToInvoluntaryPrepay> <CurrentToCurrent>0.936225626828455</CurrentToCurrent> <ThirtyTo59ToThirtyTo59>0.508565470087825</ThirtyTo59ToThirtyTo59> <SixtyTo89ToSixtyTo89>0.342158892295477</SixtyTo89ToSixtyTo89> <NinetyPlusToNinetyPlus>0.845490061979147</NinetyPlusToNinetyPlus> <SixtyTo89ToCurrent>0.0622535698764131</SixtyTo89ToCurrent> <NinetyPlusToCurrent>0.0408551960013352</NinetyPlusToCurrent> <SixtyTo89ToNinetyPlus>0.38723632596392</SixtyTo89ToNinetyPlus> </ModelValue> </SRateArrValues> </SRateArr> </Sec> </SecXml> </ARun></DataOutputEntries>' CREATE TABLE #tempTblTransRateArr( Label VARCHAR(50), ADate DATETIME, GroupId INT, CollType VARCHAR(50), SKey VARCHAR(500), BeginState VARCHAR(50), EndState VARCHAR(50), [Date] DATETIME, TransRate FLOAT) DECLARE @hdoc INT EXEC sp_xml_preparedocument @hdoc OUTPUT, @ADataOutputImportEntries INSERT INTO #tempTblTransRateArr SELECT Label, ADate, GroupId, CollType, SKey, 'Current' as BeginState, 'ThirtyTo59' as EndState, [Date], TransRate FROM OPENXML(@hdoc, '/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue', 2) WITH ( Label VARCHAR(50) '../../../../../Label', ADate DATETIME '../../../ADate', GroupId INT '../GroupNumber', CollType VARCHAR(50) '../CollType', SKey VARCHAR(500) '../../../../../SKey', [Date] DATETIME 'Date', TransRate FLOAT 'CurrentToThirtyTo59') INSERT INTO #tempTblTransRateArr SELECT Label, ADate, GroupId, CollType, SKey, 'Current' as BeginState, 'VoluntaryPrepay' as EndState, [Date], TransRate FROM OPENXML(@hdoc, '/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue', 2) WITH ( Label VARCHAR(50) '../../../../../Label', ADate DATETIME '../../../ADate', GroupId INT '../GroupNumber', CollType VARCHAR(50) '../CollType', SKey VARCHAR(500) '../../../../../SKey', [Date] DATETIME 'Date', TransRate FLOAT 'CurrentToVoluntaryPrepay') INSERT INTO #tempTblTransRateArr SELECT Label, ADate, GroupId, CollType, SKey, 'ThirtyTo59' as BeginState, 'Current' as EndState, [Date], TransRate FROM OPENXML(@hdoc, '/DataOutputEntries/ARun/SecXml/Sec/SRateArr/SRateArrValues/ModelValue', 2) WITH ( Label VARCHAR(50) '../../../../../Label', ADate DATETIME '../../../ADate', GroupId INT '../GroupNumber', CollType VARCHAR(50) '../CollType', SKey VARCHAR(500) '../../../../../SKey', [Date] DATETIME 'Date', TransRate FLOAT 'ThirtyTo59ToCurrent') EXEC sp_xml_removedocument @hdoc SELECT * FROM #tempTblTransRateArr DROP TABLE #tempTblTransRateArr[/code]

Centralised Backup Reporting

Posted: 05 Jun 2013 07:49 PM PDT

Hi All,I work with an environment where we have hundreds of Server's, many with common user databases, others with uncommon databases and checking that database maintenance, in particular database backups are taking place, is becoming quite a challenge. We have our own in-house alerting system that alerts us to jobs that have failed, which we can view on a centralised monitoring system, but, what we're really after is something centralised, that will report what databases have not been backed up for x amount of days, with the aim of highlighting backup jobs that may have been disabled or simply don't exist. I've looked at Idera's Backup Status, which is contained within its SQL Admin Toolset, and it's close to the mark, but, unfortunately you can't filter out specific user databases that aren't backed up on purpose and in our case, we have a number of databases that are the same, on all servers and thus we don't back them up as we can always get copy of the database from another server. Hopefully I've explained the situation well, any suggestions would be appreciated.Thanks, Col
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog