Friday, September 20, 2013

[T-SQL] Out of memory error

[T-SQL] Out of memory error

Out of memory error

Posted: 20 Sep 2013 01:01 AM PDT

I have a procedure, in which returns many results in the grid(over 1 million) and when I run it I get the out of memory error. I tried running the script with the "discard results after execution" box checked and not only was I able to process many more records, but the execution time was cut down from about an hour to 5 minutes. The checkbox I was referring to is located under Tools > Options > Query Results > SQL Server > Results to Grid > Discard results after execution. Is there an equivalent T-SQL command that can perform the same configuration? Having this box checked still processes the script, but doesn't produce any results in the grid. The script now runs so much faster and memory isn't getting used up by the massive amounts of rows that were previously being returned. Since this is will be run in production, i would like to include this option in the procedure/ script instead of selecting it from GUI.

Value updated in a cursor

Posted: 19 Sep 2013 11:56 PM PDT

One of my Transfer Document SSRS report, consists of a table report item showing 3 columns, Item, ItemFrom , Itemcontent.If it displays, 'n 'records, for the first record, ItemFrom column shows a correct value and rest of the records showing incorrect value. I went through the SP of this. It consists of cursors for updating and uses several variables. I set the variables to blank after declaring to show the correct value.Any other idea is highly appreciated.In the SP, the part is shown below:DECLARE @containerCode NVARCHAR(MAX)DECLARE @containerKey NVARCHAR(MAX)DECLARE @storingEventModifiedOn DATETIMEDECLARE @containerFrom NVARCHAR(MAX) SET @containerCode = '' SET @containerKey = '' SET @containerFrom = '' OPEN @containersFromStoringEventsCursorFETCH @containersFromStoringEventsCursor INTO @containerCode, @containerKey, @storingEventModifiedOn, @containerFromWHILE(@@FETCH_STATUS = 0)BEGIN DECLARE @filteredSpecimens NVARCHAR(MAX) EXEC [dbo].[eusp_e5_eSM_SE_ChainOfCustody002_SubReport_GetFilteredSpecimensInContainer] @storingEventModifiedOn, @containerKey, @studyIdentifier, @destinationKey, @filteredSpecimens OUTPUT -- We must not report containers that contain no moved specimen. IF @filteredSpecimens != '' BEGIN INSERT INTO @Results SELECT item = @containerCode, itemFrom = @containerFrom, itemContent = @filteredSpecimens ORDER BY item ASC END FETCH @containersFromStoringEventsCursor INTO @containerCode, @containerKey, @storingEventModifiedOn, @containerFromENDCLOSE @containersFromStoringEventsCursorSELECT * FROM @Results GROUP BY itemFrom,item,itemContent

Number Of Columns Per Table

Posted: 20 Sep 2013 12:39 AM PDT

Happy Friday EveryoneI am playing around with some code, and trying to learn some things. If you execute this query against any database, it will return the Rows Counts per table, and the Number of columns in said table. I am trying to code this to return only the MAX number, or the highest number of the Number Of Columns.[code="sql"]SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS TableName, i.rows, c.column_id AS NumberOfColumnsFROM sys.tables t JOIN sys.sysindexes i ON t.object_id = i.id AND i.indid < 2JOINsys.columns cONt.object_id = c.object_id ORDER BY i.rows ASC[/code]Results for only one of the tables,[quote]TableName rows NumberOfColumnsq_tx_adh_stat 0 1q_tx_adh_stat 0 2q_tx_adh_stat 0 3q_tx_adh_stat 0 4q_tx_adh_stat 0 5q_tx_adh_stat 0 6q_tx_adh_stat 0 7q_tx_adh_stat 0 8q_tx_adh_stat 0 9q_tx_adh_stat 0 10[/quote]I am trying to write the query that will return only this:[quote]TableName rows NumberOfColumnsq_tx_adh_stat 0 10[/quote]I have tried a few different things, like using a CTE, using a HAVING clause, MAX() and some other ways, but I am obviously doing something incorrectly. I would like the MAX number of columns listed for each table that the query returns.Thank you in advance for all your assistance, comments and suggestionsAndrew SQLDBA

help to get the output with the help of trigger

Posted: 19 Sep 2013 11:18 PM PDT

i have a table with records create table rstbljobs(AutoID identity(1,1),AssignedToRstEmpID int,priorityid int,Jobstatusid int)Insert into rstbljobs values ( 147,202,374)Insert into rstbljobs values ( 169,214,374)Insert into rstbljobs values ( 170,202,374)Insert into rstbljobs values ( 176,202,374)Insert into rstbljobs values ( 182,202,374)Insert into rstbljobs values ( 147,214,374)Insert into rstbljobs values ( 183,202,374)Insert into rstbljobs values ( 170,214,374)Insert into rstbljobs values ( 182,214,374)Insert into rstbljobs values ( 170,202,374)Insert into rstbljobs values ( 147,214,374)Insert into rstbljobs values ( 147,202,374)Insert into rstbljobs values ( 169,202,374)Insert into rstbljobs values ( 169,214,374)i have another table UsersRSDeskCreate table UsersRSDesk(userid int,IsRecruit bit,status bit,noofhighprtjobs varchar(50))The records are insert into UsersRSDesk values (147,1,0,null)insert into UsersRSDesk values (169,1,0,null)insert into UsersRSDesk values (170,1,0,null)insert into UsersRSDesk values (176,1,0,null)insert into UsersRSDesk values (182,1,0,null)insert into UsersRSDesk values (183,1,0,null)The query is as follows:select AssignedToRstEmpID,COUNT(AssignedToRstEmpID)as Noofcount from RStblJobs group by AssignedToRstEmpIDfrom the above query i want the noofcount from rstbljobs to be inserted into usersrsdesk.noofhighprtjobs column.Whenever i update rstbljobs table then also the same count should be reflected here.

Rapid Growth Of Data and Purging Issues

Posted: 19 Sep 2013 06:56 PM PDT

Hi,I tried a few different things which though work well but needs atleast few minutes of downtime so I am coming to my favorite place to seek opinion.We have a gaming website which is open 24*7. The data in the tables grow rapidly. The problem is that when I try to delete the data, it does work and in 15 minutes or so, I can delete the data accumulated for over 24 hours but this does hamper transactions and because delete locks down the table, this process although working well does not suit my current requirements. A senior developer tried deleting row by row so that only rowlocks are taken but that is not purging data fast.If somebody has worked in such an environment before, please give your suggestions and inputs on how to take this further.ThanksChandan Jha

Better way to write this query

Posted: 19 Sep 2013 12:31 PM PDT

I have inherited this query that is comparing data from one table to another. It is using a combination of columns to "build" a unique column that gets inserted into the other table should it not exist. The query is below [code="sql"]INSERT INTO ReportRecipientsSELECT rs.[ReportID] ,rs.[ATID] ,rs.[AID] ,rs.[RecipientId] ,rs.[Source] ,rs.[Frequency] ,rs.[DeliveryMethod] ,'NEW RECORD' ,CONVERT(VARCHAR(10),rs.[ATID]) + CONVERT(VARCHAR(10),rs.[AID]) + CONVERT(VARCHAR(10),rs.[ReportID]) + CONVERT(VARCHAR(10),rs.[Frequency]) + CONVERT(VARCHAR(10),rs.[RecipientId]) + CONVERT(VARCHAR(10),rs.[DeliveryMethod]) ,rs.[scheduleid] ,rs.FeeAmount ,rs.FeeTranCode ,0FROM [dbo].[ReportSchedules] rs WHERE CONVERT(VARCHAR(10),rs.[ATID]) + CONVERT(VARCHAR(10),[AID]) + CONVERT(VARCHAR(10),rs.[ReportID]) + CONVERT(VARCHAR(10),rs.[Frequency]) + CONVERT(VARCHAR(10),rs.[RecipientId]) + CONVERT(VARCHAR(10),rs.[DeliveryMethod]) NOT IN (SELECT DISTINCT [id] from dbo.ReportRecipients)[/code]I realize the WHERE clause poses an issue due to the non-sargable concatenated columns. The problem with this query is that in both tables, there are millions of rows. In it's current state it takes about 2 minutes to run, generating 4 million reads.The "CONVERT(VARCHAR(10),[ATID]) + CONVERT(VARCHAR(10),[AID]) + CONVERT(VARCHAR(10),[ReportID]) + CONVERT(VARCHAR(10),[Frequency]) + CONVERT(VARCHAR(10),a.[RecipientId]) + CONVERT(VARCHAR(10),a.[DeliveryMethod])" business creates the "ID" column in the other table...which when new rows are added into the main table, it uses the combination of the CONVERTS to match against the "ID" column to determine if it's a new record or not.Here's some sample data and DDL:[code="sql"]/* Create Main source table */CREATE TABLE [dbo].[ReportSchedules]( [ScheduleId] [int] NOT NULL, [ReportID] [int] NULL, [ATID] [int] NULL, [AID] [int] NULL, [RecipientId] [int] NULL, [Source] [varchar](5) NULL, [Frequency] [tinyint] NULL, [DeliveryMethod] [tinyint] NULL, [FaxTime] [varchar](50) NULL, [FeeAmount] [money] NULL, [FeeTranCode] [char](5) NULL, CONSTRAINT [csPk_ReportSchedules] PRIMARY KEY CLUSTERED ( [ScheduleId] ASC ) ON [PRIMARY]) ON [PRIMARY]GO/* Populate Main source table with some data */INSERT INTO ReportSchedulesSELECT 1,34,51,7301,7481,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 2,34,51,7271,7451,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 3,24,51,7302,7482,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 4,24,51,7273,7453,'CI',2,1,'Not Specified',NULL,NULL UNION ALLSELECT 5,24,51,9905,10395,'CI',2,1,'Not Specified',NULL,NULL/* Create table to compare against source table */CREATE TABLE [dbo].[ReportRecipients]( [ScheduleID] [bigint] IDENTITY(1,1) NOT NULL, [ReportID] [int] NOT NULL, [ATID] [int] NOT NULL, [AID] [int] NOT NULL, [RecipientId] [int] NOT NULL, [Source] [varchar](5) NOT NULL, [Frequency] [tinyint] NOT NULL, [DeliveryMethod] [tinyint] NOT NULL, [FaxTime] [varchar](40) NULL, [ID] [varchar](60) NOT NULL, [CCScheduleID] [varchar](10) NULL, [FeeAmount] [money] NULL) ON [PRIMARY]ALTER TABLE [dbo].[ReportRecipients] ADD [FeeTranCode] [char](6) NULLALTER TABLE [dbo].[ReportRecipients] ADD [FeeFired] [bit] NULLPRIMARY KEY CLUSTERED ( [ScheduleID] ASC) ON [PRIMARY]GO/* Insert sample data */INSERT INTO ReportRecipients SELECT 34,51,7301,7481,'CI',2,1,'Not Specified',51730134274811,6229,NULL,NULL,0 UNION ALL SELECT 34,51,7271,7451,'CI',2,1,'Not Specified',51727134274511,6231,NULL,NULL,0 UNION ALL SELECT 24,51,7302,7482,'CI',2,1,'Not Specified',51730224274821,6234,NULL,NULL,0 UNION ALL SELECT 24,51,7273,7453,'CI',2,1,'Not Specified',51727324274531,6235,NULL,NULL,0 [/code]This will set up the example so there is a new record to insert into the ReportRecipients table when the first query (the first SQL above) is run

Please Please help on this Trigger

Posted: 19 Sep 2013 06:05 AM PDT

In my organization they create the database through the application and I want to create the trigger to fire the backup whenever the new database are created. I have created the following script. It's creating the backup when I manually create the database, but the trigger is not firing when the database is created by application. Do I need to change the script or anything?CREATE TRIGGER TRG_BackupNewDatabase ON ALL SERVER FOR CREATE_DATABASE AS declare @database sysname, @event_data XML = EVENTDATA(), @folder nvarchar(4000), @file nvarchar(4000) SET @database = @event_data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'sysname') set @folder = 'X:\Backups\' + @database set @file = @folder + '\' + @database + '.bak' if exists (select * from sys.databases where name = @database and source_database_id is null) BEGIN EXEC master.dbo.xp_create_subdir @folder COMMIT BACKUP DATABASE @database to disk=@file raiserror( 'You can ignore the error message which says that the transaction ended within the trigger.', 16,1) END GO

T SQL advice sp_MSforeachdb

Posted: 19 Sep 2013 07:12 AM PDT

Hello everyone.Can anyone please offer me some advice on this query I am trying to put together?I want the query to be run on each database on an instance.I am stuck on getting it to output database name.EXEC sp_MSforeachdb 'USE ? SELECT ''?'','Count' = COUNT(*), 'Type' = CASE type WHEN 'C' THEN 'CHECK constraints' WHEN 'D' THEN 'Default or DEFAULT constraints' WHEN 'F' THEN 'FOREIGN KEY constraints' WHEN 'FN' THEN 'Scalar functions' WHEN 'IF' THEN 'Inlined table-functions' WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraints' WHEN 'L' THEN 'Logs' WHEN 'P' THEN 'Stored procedures' WHEN 'R' THEN 'Rules' WHEN 'RF' THEN 'Replication filter stored procedures' WHEN 'S' THEN 'System tables' WHEN 'TF' THEN 'Table functions' WHEN 'TR' THEN 'Triggers' WHEN 'U' THEN 'User tables' WHEN 'V' THEN 'Views' WHEN 'X' THEN 'Extended stored procedures' END, GETDATE()FROM sysobjectsGROUP BY typeORDER BY typeGOmany thanks for any advice offered.

Send Alert when no log backup completed in 24 hours

Posted: 04 Dec 2012 05:09 AM PST

Dear Forum, I'm sure someone has completed this type of request, but here is the back ground. I have a thrid party tool to complete all forms of backups on SQL databases. I have recently found that from time to time this 3rd party backup tool will drop a backup schdule. For example I have a instance where Transaction Log backups have not occured in months. So now I must Mirco manage the backups across some 600 instances. My questions does any have the code or an example on how send or create an alert if a database has not had a log backup with in the last 24 hours?All Enviroments are either 2K8 or 2K8R2.Any help or direction would be gladly execpted.Fishing for help..:hehe:

add bold in for xml type

Posted: 15 Nov 2011 01:31 AM PST

Hello,I have a query that returns in xml format the contents of a table and I want to bold lines where run_status = 0the initial query is :[code="sql"]set @tableHTML = N'<table border="1">' + N'<tr><th></th>' + N'<th>srvname</th>' + N'<th>jobname</th>' + N'<th>date</th>' + N'<th>duration</th>' + N'<th>status</th></tr>' + CAST(( select td = t.num, '', td = t.srvname, '', td = t.jobname, '', td = t.run_datetime, '', td = t.run_duration, '', td = t.run_status from @t t where t.run_datetime > dateadd(dd, -1, GETDATE()) order by t.run_status, t.srvname FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>';[/code]so I wrote[code="sql"]select td = '<B>' + convert(varchar(3), t.num) + '</B>', '', td = '<B>' + t.srvname + '</B>', '', td = '<B>' + t.jobname + '</B>', '', td = '<B>' + t.run_datetime + '</B>', '', td = '<B>' + t.run_duration + '</B>', '', td = '<B>' + convert(varchar(3), t.run_status) + '</B>'from @t twhere t.run_datetime > dateadd(dd, -1, GETDATE()) and t.run_status = 0order by t.run_status, t.srvname[/code]but <B> is replaced by "& l t ; B & g t ;" (without spaces : same here I cant write it!!! :-D)So how can I doThank you for your help.

calculating a median for groups

Posted: 14 Nov 2011 04:49 AM PST

I have the following table that I need to get a median for one of the columns mytableState City NumberCA LA 20CA LA 25CA LA 15CA SD 35CA SD 12CA SD 17NY NYC 20NY NYC 22NY NYC 8NY NYC 36NY NYC 15What I want is to create a new table that has the median for thecolumn Number like this:State City MedianCA LA 20CA SD 17NY NYC 20Any help would greatly be appreciated.

WAITFOR DELAY - Shortest Delay Possible?

Posted: 19 Sep 2013 01:48 AM PDT

This is baffling me!I have a proc that will call another proc to clear a record (if one exists) before adding a new one. Both procs work fine and the table ends up correct. However, I've noticed that the proc that clears the record logs that it executed at exactly the sime timeas the new record. This means that I can't see the correct order in my log table. Obviously, as the clearing proc runs BEFORE the proc that adds the new record, it should write it's log record first. The clearing proc runs at least three statements before the second one.No problem I thought, I'll just drop a WAITFOR DELAY '00:00:00.001' after the clearing proc so that they are at least a millisecond apart and that will fix it.Trouble is, it doesn't. The still come in at exactly the same time. If I make the delay a second, it works, but that's crap.I tried:select GETDATE()waitfor delay '00:00:00.001'select GETDATE()And I seem to get some spurious results for anything less than 10 miliseconds.Any ideas? Have I found a bug? Do I win a prize?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog