DB Engine :: Memory Table Not Cleared If Created Inside A While Loop

Oct 29, 2015

If I create a memory table inside a while loop, I expect that every loop the memory table is (newly) created and thus empty.

But when I test this, I see that on the second, third, ... run the inserted data in the memory table is still present and not cleared.

This can be reproduces with the following code:

DECLARE @tbl TABLE
( [id] BIGINT )
DECLARE @tbl2 TABLE
( [value] BIGINT )
INSERT INTO @tbl
VALUES (1), (2)

[Code] ....

/*
Expectation: twice 11 and 12 in @tbl2
Actual result: three times 11 and 12
*/
SELECT *
FROM @tbl2

Is this a wrong assumption or is this a bug in SQL Server? (Tested on: SQL Server 2014 and 2008).

View 2 Replies


ADVERTISEMENT

DB Engine :: When Is Log File Cleared

Jun 9, 2015

Recovery model: Simple with Full backups

begin transaction
begin try
        insert into [EthnicRace] select * from [OEPRD].[CIS_Source_Data].[dbo].[EthnicRace];
        insert into [MilestoneOwner] select * from [OEPRD].[CIS_Source_Data].[dbo].[MilestoneOwner];
        insert into [PS_ACAD_CALTRM_TBL] select * from [OEPRD].[CIS_Source_Data].[dbo].[PS_ACAD_CALTRM_TBL];
        insert into [PS_ACAD_DEGR] select * from [OEPRD].[CIS_Source_Data].[dbo].[PS_ACAD_DEGR];
end try

A job on our production server goes out to Oracle and gets raw data from our student system once per night. A job on our development server then uses the above coding to copy that raw data from production to the tables in our development system. My question is, will the log continue to grow until the "end try" is encountered or is it cleared at the end of each INSERT statement?

View 5 Replies View Related

SQL Server Memory Not Cleared Automatically

Mar 3, 2008



I have installed sql server 2005 in cluster. after all the process is finished the memory usage is showing 2 gb where as in the activity monitor there is no process running. what should i do to clear the memory automatically.

If i stop and start the serverice it is cleared, nor do i want to run any command to clear the memory.

View 1 Replies View Related

Insert Into Table Inside For Loop

Feb 6, 2015

I wanted to insert values in columns as explained in below ex.

I am having a table that contains Column1,Column2,Column3,......,Column10.

Inside my for loop, i am getting Column1 value then Column2 then Column3 values and so on till Column10.

My requirement is that on each iteration,I wanted to insert value of Column1 in field Column1, value of Column2 in field Column2 and so on.

View 3 Replies View Related

DB Engine :: Alert When Database Table Is Created

Jul 24, 2015

I use below trigger to email me when a database is created or dropped.
  
CREATE  TRIGGER [DDL_CREATE_DATABASE_EVENT]
ON ALL SERVER
FOR CREATE_DATABASE
AS
DECLARE @bd VARCHAR(MAX)
DECLARE @tsql VARCHAR(MAX)
SET @tsql = EVENTDATA().value

[Code] ...

Is there a way we can get notification when a table is created or altered or dropped ?

View 3 Replies View Related

Cant See Db Created Inside VS 2005?

May 29, 2006

When I create a new Sql Sever 2005 express database inside of the VS 2005 Pro IDE I cant see the database from the Management Tool (Sql Srvr Express Management Studio). Is this normal? I can "attach" it and see it, but I wanted to make sure my environment was working correctly.

Thanks in advance for any input.



Tim

View 3 Replies View Related

Loop Inside SP

Feb 1, 2004

hello,

anyone for help?
what's the syntax of for.next, do while loop in Stored Proc?

ur help is much appreciated!


thanks,

View 2 Replies View Related

Loop Inside Of A Cursor

Oct 23, 2000

I have a loop(while) statement I need to run inside a cursor statement. The loop creates records based on a frequency. The cursor and the loop work but the problem is that the cursor only reads the first record, runs the loop, but then ends. I am pasting the code below. Any help appreciated

declare dbcursor cursor for select uniq_id,account_id,created_by,encounter_id, start_date,date_stopped,sig_codes, ndc_id,modified_by from patient_medication where convert(datetime,start_date) = '10/20/2000' and date_stopped is not null
open dbcursor fetch next from dbcursor into @uniqid,@account_id,@createid,@entcid, @sdate, @edate ,@sig_code, @ndcid, @modid
while (@@FETCH_STATUS <> -1)
begin
select @freq = SIG.sig_frequency FROM SIG where SIG.SIG_KEY = @sig_code
set @hfreq = @freq if @freq = 9 set @freq = 1 set @nodays = datediff(day, @sdate - 1, @edate)
while @cnter < @nodays
begin
while @fcnter < @freq + 1 begin insert into PATIENT_MEDICATION_DISPERSAL_ (uniq_id,account_id, occurance_id, encounter_id, ndc_id, ddate, frequency, sig_code,disp_create_id, disp_mod_id) values (@uniqid,@account_id,@fcnter, @entcid, @ndcid, @sdate, @freq, @sig_code,@createid, @modid )
set @fcnter = @fcnter + 1
set @erdate = @sdate

END
if @hfreq = 9
begin set @fcnter = 1
set @sdate = @sdate + 2
Set @cnter = @cnter + 2
end
else
begin
set @fcnter = 1
set @sdate = @sdate + 1
Set @cnter = @cnter + 1
end
end
end
close dbcursor
deallocate dbcursor

View 2 Replies View Related

Loop Inside View

Apr 4, 2007

Hello,

is it possible to build a loop for the following statement?


CREATE VIEW vwObjects as (

Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union

Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union

Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)



I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.

Thank you!

View 5 Replies View Related

For Each Loop Container Do Nothing Inside

Aug 27, 2006

Hi,

A very strange thing happened to me. I have a package that includes two For each loop containers. Each container has script tasks, sequence containers, etc. Both are Foreach ADO Enumerator basis. It works without any problems until I changed the position of one of them in the Control flow and added some code in the script task. After these changes I executed the package and both of For each loop containers did not execute the tasks inside of them, any task. However the execution color on the containers was green (success). How can it be?

Your help is much welcome. Thanks in Advance.

João Cruz









View 2 Replies View Related

DB Engine :: Server Log Is Not Created

May 13, 2015

I have SQL 2008 R2 version. The server has enough disk space where the SQL is running. But the log file is not refreshed. in other words a new sql server log file does not create a new file if the old one is full.

View 4 Replies View Related

Problem With Transaction Inside Loop

May 6, 2008

Hi,

When i execute the following set of statements only 8 is getting inserted into table instead 6 and 8.

Create Table BPTest(id int)
Declare @Id Int
Set @Id = 0
While (@Id < 10)
Begin
begin tran
Insert into BPTest values (@id)
if(@Id > 5)
begin
if(@Id % 2 = 0)
begin
print 'true' print @Id
commit tran
end
else
begin
print 'false' print @Id
rollback tran
end
end
Set @Id = @Id + 1
End
Select * from BPTest
drop table BPTest

Please let me know the reason for this.

Thanks in advance

Regards,
K. Manivannan

View 1 Replies View Related

Variable Inside A Nested Loop

Jul 20, 2005

I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 3 Replies View Related

Problem With Union Inside A While Loop

Feb 16, 2008



im trying to do this



declare @count int

set @count=0

while @count<4

begin

set @count=@count+1

select *

from dbo.Categories

where CategoryPID=-1

union()

end



and i get a error
this is not the original code but i want to union all select statements
please help !!!

View 10 Replies View Related

DB Engine :: Trigger When A New Database Gets Created

Oct 22, 2015

I am looking to create a server wide trigger which will fire whenever a database gets created, I would like to know if there are any gotchas or anything that I need to look out for/test specifically.

The idea is to automatically grant some permissions and take some other actions whenever a database gets created.

View 6 Replies View Related

How To Check Out EOF Inside A Foreach Loop Container?

Apr 12, 2007

Hi everyone,

I've got a Foreach loop container which uses a Foreach ADO Enumerator and works fine.

But problem comes when I launch inside the general loop another Sql Task (select) which sometimes has rows and sometimes hasn't. When it have rows everything is fine the workflow follows fine but when it has not.



I obtain this error (obvioulsy)



[Execute SQL Task] Error: An error occurred while assigning a value to variable "GZon": "Single Row result set is specified, but no rows were returned.".



How to deal with that?



Thanks indeed

View 5 Replies View Related

Execute Sql Task Inside For Each Loop Containter

Feb 15, 2008

Hi, I'm trying to loop thru a table and insert records into another table in ssis. So far I have been able to get the data using a execute sql task set up to store the full result set into a variable called data. I then drug a foreach loop container out and selected the foreach ADO enumerator and used my variable data as the ADO object source variable. I then set up a new variable under variable mappings with index 0 to get the collection values. How do I take that variable and update another table using another sql task inside the foreach loop container? Is this possible?

thanks,

View 8 Replies View Related

XML Document Could Not Be Created Because Server Memory Is Low.

Dec 17, 2007

The app users getting terminated after getting the following message.

Exception: XML document could not be created because server memory is low. Use sp_xml_removedocument to release XML documents

The SQL platform is SQL 2005 64bit version running on Windows 2003 SP2.

Was wondering if anyone else has had issues like this and if so what could be the possible resolution.

Thanks,

vkumar

View 1 Replies View Related

Pass Filename To Flatfilesource Inside Foreach Loop

Jul 26, 2007



Hi,
I am using a foreach loop to go through the .txt files inside a folder.
Using a variable I can pickup the filenames the loop is going through.
At present there is a sql task inside the foreach loop which takes the filename as a parameter and passes this filename to a stored procedure.
Now I would like to add one extra step before this sql task. Would like to have a dataflow with flatfile source which connects to oledb destination.

The question is:
While in the loop, how is it possible to pass the filename to the flatfile source using the FileName variable which I have created?

Please note, this is a different question to my other post.

Many Thanks

View 6 Replies View Related

Data Flow Inside For Each Loop - Error Handling

Jun 23, 2006

Hopefully this is an easy question:

Inside of a for each loop (looping through an ADO record set of objects to import) I have a data flow task (along with many other processes).... if the dataflow task suceeds I log success in a table. If it errors I want it to fail the dataflow task (which will fire off my Event Handler for that data flow and log the failure, email etc) BUT I want it to continue the loop - I can't seem to figure out how to get the data flow object not to fail the whole loop. If any other objects inside the foreach, other than the data flow, fail I would like the whole loop to fail. Also if possible (but not a requirement) I would like it to have a threshold where if the data flow fails X variable times it will fail the package.

I am having difficulty how to not fail the loop when the import data fails..... just looking for a simple "on error next" type logic for that specific object in the foreach but not the rest. Thanks in advance for the help/advice.

View 4 Replies View Related

XML Task Inside Foreach Loop Container Had To Fail!!!

Aug 9, 2007



Hi,

I am using an XML task for validating the XML data against the Schema XSD. I have more XML Files with same the schema. I have to used a for loop container which has an XML task for validate XML. The loop container gets the XML File into variables name "XMLFileName" which the loop current file, in turn, used by XML Task for validation.

XML task is configured in the option "Validate". I have provided the XML Data in variable name "XMLFileName" also get the name from the loop container and XSD file content File Connection. XML Task stored the result in the another variable name "OKFormat". FailOnValidationFail property set to false.

It had the error when I run the package, the error msg as below:


Error: 0xC002F304 at XML Task, XML Task: An error occurred with the following error message: "Data at the root level is invalid. Line 1, position 1.".

Error: 0xC002928F at XML Task, XML Task: Property "New Source" has no source Xml text; Xml Text is either invalid, null or empty string.

Task failed: XML Task

After that, I had to change the source type from Variable to File Connection, and had to test fixed to some xml file it had ok for validate the XML file againt XSD.

I don't know this is the wrong setting or bug of SSIS, anyone can guide me through.

View 7 Replies View Related

Reporting Services :: Report Server DB Getting Created On DB Engine

Nov 26, 2015

I've installed Reporting Services 2008R2 STD edition on my local system. I was trying to configure the Report Server, I am able to connect to the Report Server but the ReportDB and ReportTempDB are getting created in Database Engine instead of Report Server, when I connect to Report Server from SSMA this is what I see (Attached) Screen 1.

The screen2 is of Reporting Service Config Manager, When I connect to the report server using the URL what I am seeing is blank last screen shot attached.

View 4 Replies View Related

SSIS - How To Set ServerName And DatabaseName At Run Time Inside FOREACH LOOP?

Jan 14, 2006

I am new to SSIS world, so my question is very basic.

Setup:

In a company I work for we have 12 SQL servers each running between 1 and 3 databases with anywhere between 10 to 20 tables. I need to query some of these tables and merge results to the destination database.

The list of all these tables is stored in the separate table <SOURCES> of the following format [ServerName,DatabaseName,TableName]. Tables of my interest have identical structure (same columns) accross servers and databases.

Question:

How can I loop over servers and databases specified in <SOURCES> to run otherwise identical query against these tables?

I can easily retrieve [ServerName,DatabaseName,TableName] from <SOURCES> as string variables using FOREACH loop. The problem is now - how do I use string variables to set up Server, Database and Table name at run-time?

Thank you

 

 

 

 

 

 

View 4 Replies View Related

DB Engine :: How To Find List Of Indexes On Tables On Which Views Has Been Created

Aug 28, 2015

The views are in XYZ production database and user needs the list of indexes on the tables on which the views has been created.

query to find list of indexes on the tables on which the views has been created.

View 4 Replies View Related

SQL Server 2012 :: Write A Loop On Result Of First Query Inside A Stored Procedure

Jan 23, 2015

I have to write a Stired Procedure with the following functionality.

Write a simple select query say (Select * from tableA) result is

ProdName ProdID
----------------------
ProdA 1
ProdB 2
ProdC 3
ProdD 4

Now with the above result, On every record I have to fire a query Select SUM(sale), SUM(scrap), SUM(Production) from tableB where ProdID= ["ProdID from above query"].How to write this query in a Stored Procedure so that I can get the required SUM columns for all the ProdID's from first query?

View 2 Replies View Related

Foreach Loop Runs Out Of Memory

Apr 2, 2007

Hi



I have a for each loop which steps through an ado recordset (approx. 5,000 rows), this passes two variables to an SQL statement which populates a second recordset (normally 8 to 10 rows). I use the second recordset in a dataflow task which was a simple Script which returns approximately 30 rows for inclusion in my destination table. The package runs for a while OK, although the loop appears to execute slowly, then I get the below message constantly repeated in the debug window.


[DTS.Pipeline] Information: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 174 buffers were considered and 174 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
I have 2gb of virtual memory on my machine, and the recordsets are relatively small. Have I missed a seting some where?

View 17 Replies View Related

Memory Leak In For Loop Container

Mar 13, 2008


Hi,
I am using a For Loop container with a script inside it in My SSIS package. Inside the script I am type casting one of the object variables in a class object of XMLReader Type. I am using CType Function for it. The For Loop is resulting in a memory leak and the references are not getting deleted even if they go out of scope.
Does anyone have faced similar issue?
Please guide.


Thanks.

View 15 Replies View Related

DB Engine :: Memory Grant To CHECKDB

May 16, 2015

How to accurately track how much memory was granted to a DBCC checkdb during it's execution? 

I'd like to do this without continuously querying dm_exec_query_memory_grants, if at all possible - but if that's the only option.

View 7 Replies View Related

DB Engine :: How Server Memory Paging Out

Nov 23, 2015

Is there any easy way to detect SQL Server memory is getting paged out? Any DMV query or tips to confirm it is paging out.

My SQL Server Version is: Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)

View 5 Replies View Related

DB Engine :: How To Configure TempDB In Memory

Jul 9, 2015

Is it available by default or it has to be Configured. 

If yes then how to configure Tempdb in Memory for SQL 2012 SP2 CU1? 

View 5 Replies View Related

DB Engine :: Min Memory Per Query Server

May 6, 2015

Is there has any SQL query  to check running out of "min memory per query SQL Server"(default value is 1024KB)?

View 2 Replies View Related

OLAP Source In For Loop Container = Memory Leak

Feb 21, 2008

Hi,

I think I found a bug or something, I have created a package like this

For Loop Container

OLE DB source, Sql Variable the query is an openquery with an MDX query
Script Component (just ToString("+00,-00") transformations)
OLE DB destination
End loop


the MDX query is changed within the variable which is evaluated as expression.

when I run that package the process msmdsrv.exe starts consuming more and more memory.
I don't know if it's the analysis services cache that gets bigger or something else. but if it is, how do I clear it in SSIS?

just in case, the mdx query goes like this:


select {[Datos Resumen Mensual]}

on columns,

[Cuenta].[Cuenta].&[variable1]:[Cuenta].[Cuenta].&[variable2] on rows

from [Bd Rtd]

WHERE ([Tiempo].[Mes].&[2007-09-01T00:00:00],[Unidad Negocio].[Unidad Negocio].&[1])

thanks

View 7 Replies View Related

DB Engine :: Set Maximum Server Memory By Using Scripts

Apr 28, 2015

How to set maximum server memory by using sql scripts in sql server 2014?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved