Memory Leak Problem... In SQL Server 2K
Jul 20, 2005
Hello,
I am having trouble with a production db server that likes to gobble
up memory. It seems to be a slow burn (maxing out over about an 18
hour time frame, before pegging both procs on the server and bringing
everything to a standstill). After viewing the trace logs, it appears
that all the SPIDs are being recycled - does this assert that
connections are being properly closed when the need for them has
ended? The code base is huge and quite messy, so it's difficult to
discern where the problem is just by looking at code, and we can't
seem to nail it down by looking at it, and I'm not sure what to look
for in the trace logs or perfmon.
Does anyone have any suggestions about what else might cause such a
problem?
Ryan
View 6 Replies
ADVERTISEMENT
Mar 20, 2008
Hello frnds Can Anybody explai what does it mean by question itself and how to resole it ?
View 2 Replies
View Related
Mar 20, 2006
I was doing a quick test with SQL Server Express and noticed that there is memory leak
Here is the pseudo code
do loop //1000 times
{
1. Open connection to the database
2. Build SQL statement
3. Construct SQLDataAdapter
4. Fill DataSet using ( adaptor.Fill(dataSet); )
5. Close connection to the database
6. Dispose whatever is disposable
}
When I ran this test I saw that SQLServer service memory usage is going up and never released
Is there something that I am doing wrong. If I ran same test against Access database then there is no problem
Thanks
View 1 Replies
View Related
Jul 23, 2005
I have a stand alone Java application that uses MS SQL Server 2000 andMicrosoft JDBC ODBC drivers to access it. Before I run the application,I open the task Manager to monitor the memory used by the SQL Server.Generally it begins 21 MB and very soon it reaches around 100 MB if Ido some operations in the application. The thing is that even if Iclose the application, the memory taken by SQL Server doesn't go down.What could be the reason behind it? Java code or MD drivers or SQLServer?I checked the code and we are closing the statements and result set andcommiting everywhere.Please let me know...I am trying to refactor as much as I can but stillout of luck.
View 12 Replies
View Related
Mar 20, 2006
I was doing a quick test with SQL Server Express and noticed that there is memory leak
Here is the pseudo code
do loop //1000 times
{
1. Open connection to the database
2. Build SQL statement
3. Construct SQLDataAdapter
4. Fill DataSet using ( adaptor.Fill(dataSet); )
5. Close connection to the database
6. Dispose whatever is disposable
}
When I ran this test I saw that SQLServer service memory usage is going up and never released
Is there something that I am doing wrong. If I ran same test against Access database then there is no problem
Thanks
View 1 Replies
View Related
Jul 20, 2005
Hi everybody !I´m maintaining a large intranet (approx 10000 concurrent users) running onone IIS box and one DB box with sqlserver 2000.Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed onthe db box.Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does not seem tobe using it all.Currently SQLSERVER 2000 crashes at least once a day.Its very weird, I run performance monitor with counters on, memory, diskusage, num users, locks and such.There is no indications in the counters before the crashes, they just happenvery sudden.Only indication is that sqlserver makes some huge jumps in memory usage andmostly the sqlserver then crashes an hour or 2 later.The only thing that peaks a lot are the locks/sec counter.My analysis of disk usage, queues etc. tells me i got no kind of i/obottlenecks.Can anybody give me a clue as to what i should do ?Best regards, Thue
View 19 Replies
View Related
Nov 20, 1998
Local SQL 6.5 SP4 on a Windows NT SP4 workstation (Pentium 350 / 128 MB).
SQL server has 32 MB of RAM. It starts well but begins continuously eating up virtual memory by 16k blocks in a second,
until it takes all virtual memory.
Server's shutdown generate following error in Event log System section:
Event ID: 7011
Source: Service Control Manager
Description: Timeout (120000 milliseconds) waiting for transaction response
When I start Server from command prompt with -f switch, it did not eat virtual memory,
and did not generate any error in Event log at shutdown.
Many thanks for any help.
mailto:andrejss@bank.lv
View 2 Replies
View Related
Jul 20, 2005
Can anyone give me some sugguestions here.Connection is declared at the start of the applicationSet rsFZReport.ActiveConnection = connThen a function repeatly opens recordsets like this -rsFZReport.Open sRS, , adOpenStatic, adLockReadOnly, adCmdText....reporting code etc...If rsFZReport.State > 0 ThenrsFZReport.CloseSet rsFZReport= NothingendifUsing VB Watch debugger, the close and set nothing do NOT release anymemory. It just keeps increasing. The application connects to multipledatabases and many tables, so its uses about 10Mb/minute.(I don't think its of relavance but Form.show uses memory but form.unloaddoesn't release any back)I've installed SP3a and MDAC 2.8 to no avail. Anyone have any ideas or knowwhat I can do?msado27.tlb 2.80.1022.0MSSQL Server SP3a (3 has a memory leak apparently)VB6, SP6Sqlsrv32.dll caused a leak on NT4 SP6, but upgrading it on my win2k SP4server/workstation (they are both and the same) to thisSqlsrv32.dll 2000.85.1022.0doesn't help.Provider=SQLOLEDB;Data Source=theServer;Initial Catalog=theDB;UserID=uid;Password=thepwd;OPTION=3;connect timeout=240;
View 5 Replies
View Related
Mar 30, 2005
Hello all,
I have recently encountered what looks like a memory leak within SQL server, but may be a number of other things as well - I'm hoping that some of you may have encountered this problem as well so I may be able to narrow down my list of possible culprits.
I noticed this morning that SQL Server was chewing up about 1.5 GB of memory - obviously way more than it should be. At first, I thought this may be because of a coding error, but I was unable to reproduce the problem on my development machine (running SQL Personal).
After re-starting the SQL service (which cleared up the memory use), I ran task manager on our SQL server machine, and noticed that when queries were executed (especially queries that returned fairly large recordsets), memory consumption used by the sql server process would jump up, but then would not be released (as my development machine was doing).
I'm not a DBA, so I'm hoping this may be happening because of some (unknown to me) option that is not currently enabled/disabled on the SQL Server. My only other thoughts at this time are that maybe it's a bad install of SQL, or perhaps bad RAM? Any supporting/disproving thoughts? Any similar situations?
Any help with this would be greatly appreciated.
Thank you in advance,
AtomicChip
View 2 Replies
View Related
Oct 26, 1999
We are running 2 SQL 7.0 servers which have IIS 4 also installed. The servers are mirror images
of each other and are configure the same(1 is test, the other is a production server) I have noticed that even if no one is on the production server the memory being used will peak
at almost 100% of the physical memory and peg the CPU at 35-40%. If I shut down the SQL server
or reboot NT altogether the memory used will start to be fairly close to the test server. It seeds
to take almost a week for this to happen again but it looks like a memory leak. Has anyone else
seen this?
View 1 Replies
View Related
Oct 18, 1999
I am running Back Office Server 4.5 service pack 5 and I have a memory leak with SQL 7.0. Anyone have any ideas?? I have a feeling it is related to having IIS 4.0 and SQL 7.0 on the same machine.
View 2 Replies
View Related
Jan 3, 2000
What is the best way to determine if there is a "memory leak" in
VB or stored procedures that is using SQL as the back end??
View 1 Replies
View Related
Oct 26, 1999
We are running 2 SQL 7.0 servers which have IIS 4 also installed. The servers are mirror images of each other and are configure the same(1 is test, the other is a production server)
I have noticed that even if no one is on the production server the memory being used will peak
at almost 100% of the physical memory and peg the CPU at 35-40%. If I shut down the SQL server
or reboot NT altogether the memory used will start to be fairly close to the test server. It seeds
to take almost a week for this to happen again but it looks like a memory leak. Has anyone else seen
this?
View 2 Replies
View Related
Oct 21, 1999
Hi Everyone,
We have a SQL server 6.5 machine that appears to be melting down. If I monitor the SQLSERVR process in Task Manager, I see the memory usage continue to climb. The number of threads and handles for this process also continue to climb. I am not able to see in SQL current activity what tasks might be doing this.
Does anyone have any idea on ideas to troubleshoot this? The SQL server supports an Intranet application that connects to it from IIS via ODBC.
Thanks in advance,
Ed
View 1 Replies
View Related
Jul 13, 1999
I am stumped on a apparent memory leak in SQL Server 6.5.
I am running a NT Server 4.0 with SQL Server 6.5sp3 and Cold Fusion Server
4.0. The database is used in tandem with CF4.0 to drive a website that
offers a meeting search utility. The LIVE server has been experiencing
enormous reductions in speed in the last week (the site has only been live
for about 9 days). When the server was stopped/restarted the problems would
disappear temporarily only to resurface within 1/2 hour.
On closer inspection of the of the Process tab in Task Manager SQLSERVR.EXE
' s Mem Usage climbs at a steady rate until it is utilizing so much of the
available memory that the Cold Fusion Server process ends up pegging out
(suspect because of forced page faults due to lack of memory resources?)
which in turn slows the entire website down to a crawl. Even after the
scripts have finished executing and SQLSERVR.EXE 's CPU% is 0 the memory
that was allocated during processing is not released.
I have a NT Server 4.0 development server running SQL Server 6.5sp5a and
CF4.0. I tried running same scripts on it as the live server and sure
enough found the same scenario.
I also have a NT 4.0 Workstation here at home which has SQL Server 6.5sp5a
and CF4.0. I have the DB replicated here. I can not reproduce the error on
this machine running the very same scripts that are being run against the
other two.
Any help/direction would be GREATLY appreciated!
Bryan Ziel
View 2 Replies
View Related
Nov 6, 2007
I have a CF .Net 2.0 SP2 app that I *think* is leaking memory. My application has a dozen or so forms, uses two compact flash cards (one for a custom radio and the other for a GPRS internet connection). We have both SQL CE 3.0 data files and xml files that are written to. And we are using log4net as our logging library. For communication with our custom CF card, we use the .NET SerialPort class. For GPRS communication we use HttpWebRequest class.
I've built a special test version of my app that accelerates the normal usage....basically accessing both CF slots continually, and showing a couple different forms.
After four or five days of my test app (which is about a year of real world usage), I see our SQL CE commands failing with "Not enough storage is available to complete this operation".
I've added some logging of the current memory using a p/invoke to GlobalMemoryStatus. What I see is that the MEMORYSTATUS dwAvailVirtual and dwAvailPhys are slowing decreasing over time, and when dwAvailVirtual reaches about 1 MB I start seeing the above errors.
I've started pulling things out of the app and still see the behavior. I've completely removed SQL CE and log4net and the same result happens.
I've done some profiling with .NET 2.0 SP2's Remote Performance Monitor, and what I see is that in a four hour test, the "Managed Bytes in Use After GC" and "Total Bytes in Use After GC" are not steadily going up. I see them fluctuate around the same levels. The heap snapshots are not showing me any red flags yet either. I see object counts going up and down over time. But in this same time period, I see that the values reported by GlobalMemoryStatus are gradually going up (about 1.5 MB more at the end of the test than the start). If I continue this test it eventually eats up all virtual memory in about 24 hours.
I'm going to continue pulling my application apart to narrow the problem down, but wanted to know if anyone out there has encountered similar behavior, and if you have, what did you find? Any ideas or tips on how to narrow this one down faster would be much appreciated.
Thanks!
View 1 Replies
View Related
Sep 2, 2006
Dear Support,
I have a website based on ASP which is stored within an SQL database, however I appear to be experiencing a memory leak.
I'm running Windows Server 2003 x64 SP1 and MS SQL 2000 SP4.
The process which is consuming all my memory is sqlservr.exe *32. The process when restarted is about 27MB in size and will quickly reach 150MB or more until my server has no more memory.
I have little knowledge of MS SQL, however from what I've read, SQL by default is designed to use as much memory as possible, but not to the extent that it kills my server. By stopping and starting the sqlservr.exe *32 process memory is restored. I've scheduled a batch file to do this every morning at 5am as a temporary workaround. If this batch file didn't run, eventually I get the error message "HTTP/1.1 New Session Failed" when visting my website. Through my server web tools I'm forced to reboot my server which restores functionality of my site. I have also tried editing the way SQL handles memory in the Enterprise Console to a fixed amount of 118MB. I restarted my server, yet after two days the sqlservr.exe *32 process was using more than the set amount.
How can I trace the source of the problem? It's been mentioned to me open database connections may be causing the leak. How can I verify this step by step?
Many thanks - Mike
View 4 Replies
View Related
Nov 19, 2007
I believe that I may have a memory leak. I loaded a job into a scheduling engine and had it run every 15 minutes over the weekend. It ran successfully everytime untill sunday mid-day. That is when I started getting memory issues.
OnError,BPATLQDDW,NT AUTHORITYSYSTEM,Populate Flat Files,{B9F25310-0573-4042-A3DB-D0AEFD89D64C},{04378062-78CB-465B-8AD0-05E048269768},11/19/2007 10:05:58 AM,11/19/2007 10:05:58 AM,-1073450990,0x,A buffer failed while allocating 6485064 bytes.
I also get this error:
OnError,BPATLQDDW,NT AUTHORITYSYSTEM,Populate Flat Files,{B9F25310-0573-4042-A3DB-D0AEFD89D64C},{9E929349-B0FE-4EBB-A353-EC61805B4C3C},11/18/2007 8:16:16 PM,11/18/2007 8:16:16 PM,-1073450991,0x,The system reports 91 percent memory load. There are 3925336064 bytes of physical memory with 341479424 bytes free. There are 2147352576 bytes of virtual memory with 218148864 bytes free. The paging file has 8057835520 bytes with 4688982016 bytes free.
Are there any known issues with memory leaks that may be causing this? Does anyone have any suggestions? I have allready attempted to adjust the size of my default buffer.
View 6 Replies
View Related
Apr 30, 2008
Hi,
I've encountered a problem regarding a buffer allocation failure whilst running a SSIS package. The package gets a recordset of values and then for each value it iterates through a for loop. Within the for loop it simply performs 5 data pumps, collecting data into staging tables and the processes the data. The staging tables are then cleared down and the loop restarts.
In the instance when we had the allocation failure we had an abnormally large number of values for which to iterate through the loop, which is why I think we don't generally see this problem.
I've been reading around an there are suggestions that there is a memory leak issue when using a for loop and this seems to tally with what I'm seeing.
Can anyone confirm that this is a problem and whether this has been fixed in any Service Packs.
We are currently running on 2005 9.00.3042.00 (32bit) - Standard Edition on Windows NT 5.2 - Service Pack 2
Thanks in advance for any information.
Regards,
Ian
View 5 Replies
View Related
Jan 4, 2000
Hi
I have recently installed SQL Server 7
I'm an NT and SQL newbie, so be gentle...
After 2 or 3 days the memory used by SQL is up in the 190MB region from 100MB startup (the actuall app database is still tiny) and NT comes up with a 'run out of virtual memory' error/message.
Why is SQL bloating? I'm not exactly doing much with it, and my DB apps are closing down properly (no recordsets / connections left open).
Help help help!
Also, if I have a date in a string format, whats the easiest way to get SQL to compare it to a date type in a where clause???? :-)
Thanks all.
PIII 256MB NT Server 4 option pack 4 service pack 4
View 3 Replies
View Related
Sep 2, 2004
I have saved a DTS package as a vb module (.bas) file and created and activex dll from it. When i use this dll in another program and watch the handles and memory usage of the process using task manager the handles and memory values keep increasing with every call to the DTS package. memory does not increase if the dts package has not been called.
I am using sql server 2000 and visual studio 6.0 (sp 4)
View 2 Replies
View Related
Jun 21, 2001
Last 2 nights (at night) my SQL Server has locked up, first night trying to back up MSDB(20 meg) and last night ran out of memory at 6:30 AM. No users on at either time, no jobs running on the second night. I was going to turn perfmon on tonite. Any input on what best to monitor?
View 2 Replies
View Related
Jul 20, 2005
We are using ODBC connection pooling with SQL Server 2000 v5 on Win 2000 v5sp4.Each time we get a connection, SQLConnect, we see the memory usage goup, about 52K. When we return the connection to the pool,SQLDisconnect, and after the time that the connection remains in poolexpires we see the memory usage drop by about 28K.Our basic steps for a select are:SQLAllocHandle(SQL_HANDLE_DBC, hGblEnv, &hdbc);SQLSetConnectAttr(hdbc, SQL_ATTR_LOGIN_TIMEOUT,(SQLPOINTER)lLoginTimeOut, 0);SQLSetConnectAttr(hdbc, SQL_ATTR_ACCESS_MODE,(SQLPOINTER)SQL_MODE_READ_ONLY, SQL_IS_POINTER);SQLConnect(hdbc,(SQLCHAR *)szDatabase, SQL_NTS,(SQLCHAR *)szUserId, SQL_NTS,(SQLCHAR *)szPassword, SQL_NTS);SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);// Build the select statementSQLExecDirect(hstmt, (SQLCHAR * )pszSQLStmt, strlen(pszSQLStmt));SQLFetch(hstmt);SQLFreeHandle (SQL_HANDLE_STMT, hstmt);SQLDisconnect(hdbc);Same memory leak occurs in DB2, but that's another forum.
View 1 Replies
View Related
Aug 1, 2006
In our current environment, we are running about 10,000 packages per day on a given 64 bit SSIS server.
The MSDtsSrvr.exe (SSIS service) process' memory working set size continues to grow until it consumes the memory on the machine.
Some of the packages do use a custom component. Could this be responsible for leaking memory in the MSDtsSrvr.exe process? Has a memory leak been confirmed in this process for the "first-party" components anyway?
View 1 Replies
View Related
Feb 27, 2007
Hi, there;
I have a couple of issue that really frustrates me. I asked a similar question before but I haven't resolved it yet.
My application was developed with VS2003, c#,CF1.0,Sp1.
I found that after my application runs for a couple of hours, my SqlCeDataAdapter.Fill() method throw exception "Error Code: 8007000E Message : Not enough storage is available to complete this operation.". I found a couple of threads on the website said that I have to dispose adapter object (including its command objects.) Yes, I did!!!. And I also dispose my SqlCeConnection before it is out of range and recreate it when I need it. See:http://www.tutorials-se.com/sqlserverce/Keeping-SqlCeConnection/
I also introduced hotfix from http://support.microsoft.com/Default.aspx?kbid=827837.
It looks like I tried everything, but I still has this exception.
At the same time, From thread "http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=426640&SiteID=1", Mr. Ilya said "native DLL (e.g. bar code scanner API) " could cause AV (access violation) issue. Yes, my application is running on Symbol 9062B device which use barcode scanner API, sometimes application will have "0xc0000005" exception when it exists (not when it is running)
A serious problem is that the laser randomly crash in the middle of the scanning without any exception. I am quite sure it is not code logic issue. This happens since I introduced two SqlCeConnections which point to two different .sdf files (run at background threads).
My impression is that the memory or stack are corrupted for some reason.
Hopefully I make myself clear.
So my question is:
1. Will Sp3 help this? (Of course I will do some test)
2. How can I know memory or stack are corrupted/How to trace.
Again, thanks Mr.Ilya, you do a great job here, you are really really helpful!
View 3 Replies
View Related
May 2, 2007
Hi,
Situation: I have a web application that is running on a server. IT has told me that the application I developed is taking up enormous amount of ram. It seems that the amount of ram that is taken up, is slowly increasing. About 10 megs per hour (memory leak). Furthermore, he has informed me that the ram is taken up by the sql. My senior developer told me that I probably have sqlconnections open that are not closed. I have checked through the application and it seems they are all closed properly. But I may have missed some, as the application is pretty big.
Questions:
1. Is there an application or piece of code that can monitor how many sql connections I have open during runtime?
2. Could there be any other common causes to such a kind of memory leak.
Any help with this matter would be very much appreciated.
Thank you for your time.
Sincerely,
Jeff
View 7 Replies
View Related
Dec 1, 1999
Hi all,
I have a couple of client programs continuously dumping data into a SQL 7.0 database. I use ODBC connections that remain open as long as the client programs are running.
The memory consumed by SQL grows until all the system memory is consumed at which point the clients freeze (SQL no longer responds). Restarting SQL Server "cures" the problem for a few hours.
I've tried limiting the amount of memory used by SQL, but this only accelerates the eventual client freeze-up. At the moment, I let SQL dynamically consume memory.
These same client programs ran fine under 6.5
Thanks for any help..
SQL 7.0/SP1
P3-500
256MB
View 3 Replies
View Related
Oct 5, 2005
Greetings all!
On one of our intranet SQL servers running under Windows 2000 SP 3,
MSsql 2000 SP4 seems to gradually "eat" away all available memory (with no obvious reason for it) until a certain limit is reached, forcing the server to slow down substantially since the OS has to SWAP continuously.
I would appreciate any suggestions at this point on how to tackle this problem :)
Thank you!
VincentJS
View 2 Replies
View Related
Dec 4, 2007
This doesn't seem to be documented anywhere.
When you call IRowsetIndex::GetIndexInfo, it allocates memory for an array of DBINDEXCOLUMNDESC structures, which the documentation says to free using IMalloc::Free. However it does not say to free the pColumnId member of each DBINDEXCOLUMNDESC (which is a DBID*), or the pointer members of pColumnId. I think that if you don't do this, you will have a memory leak.
I used the following code to completely free the DBINDEXCOLUMNDESC structures:
Code Block
inline void FreeDBIDMembers(DBID &dbid)
{
switch(dbid.eKind)
{
case DBKIND_GUID_NAME:
case DBKIND_NAME:
if(dbid.uName.pwszName != NULL)
CoTaskMemFree(dbid.uName.pwszName);
break;
case DBKIND_PGUID_NAME:
if(dbid.uName.pwszName != NULL)
CoTaskMemFree(dbid.uName.pwszName);
if(dbid.uGuid.pguid != NULL)
CoTaskMemFree(dbid.uGuid.pguid);
break;
case DBKIND_PGUID_PROPID:
if(dbid.uGuid.pguid != NULL)
CoTaskMemFree(dbid.uGuid.pguid);
break;
}
}
// Used to free memory allocated by IRowsetIndex::GetIndexInfo
inline void FreeDBINDEXCOLUMNDESC(DBINDEXCOLUMNDESC *pColDesc, ULONG nColumns)
{
if (!pColDesc)
{
return;
}
DBINDEXCOLUMNDESC *pColCur = pColDesc;
for(unsigned int i = 0; i < nColumns; ++i, ++pColCur)
{
if (pColCur->pColumnID)
{
FreeDBIDMembers(*pColCur->pColumnID);
CoTaskMemFree(pColCur->pColumnID);
}
}
CoTaskMemFree(pColDesc);
}
I used CoTaskMemFree rather than IMalloc::Free as I am working with Pocket PC. Thanks also to Joao Paulo Figueira who has documented a fix to a similar memory leak, which was my inspiration for finding and fixing this one! If you are using Figueira's CRowsetIndex, part of his adapted ATL OLEDB templates for Pocket PC, then you are likely to have this leaky code too.
Can anyone confirm this is correct?
View 1 Replies
View Related
Aug 13, 2007
Hi,
The server is on win 2003 server with sql server 2005 workgroup sp2
ram is 2 GB.
the SQL start with 100MB and 100MB VM and after two days of regular and not massive work
the SQL memory goes up to 1GB ram and 1GB VM
how can i know who is the process that cuase the SQL memory to incress
is it somthing usual for sql 2005 to go up to 2 GB mem?
View 1 Replies
View Related
Oct 9, 2007
Env. eVC++4.0 / SQLCE3.0 / PDA OS : WinCE5.0
I modifed some sample source (NorthwindDB and downloaded source... etc)
It looks like working very well.. but system memory continuously increase when execute query.
So, I tested memory usage after ConnectDB and DisConnectDB. (just connect/disconnect)
Result, memory increased similarity.
Before system memory usage : 13068 KB
1st.
After ConnectDB : 14160 KB
After DisconnectDB : 13156 KB (increase about 88KB)
2nd.
After ConnectDB : 14164 KB
After DisconnectDB : 13160 KB
3rd.
After ConnectDB : 14168 KB
After DisconnectDB : 13164 KB
I don't know what has caused the problem.
Needs your comment... Thanks in advance.
Code Block
// GLOBAL VARIABLE
// Provider Interfaces
IDBInitialize *pIDBInitialize = NULL;
IUnknown *pIUnknownSession = NULL;
IDBCreateSession *pIDBCreateSession = NULL;
IDBProperties *pIDBProperties = NULL;
// SQL query command text handle object
IDBCreateCommand *pIDBCrtCmd = NULL;
ICommandText *pICmdText = NULL;
HRESULT CPBCls::ConnectDB(LPTSTR lpszDBName)
{
HRESULT hr = NOERROR;
DBPROPSET dbpropset[2]; // Property Set used to initialize provider
DBPROP dbprop[2]; // property array used in property set to initialize provider
DBPROP sscedbprop[1];
// Create the SQL Server CE provider
hr = CoCreateInstance(CLSID_SQLSERVERCE_3_0,
0,
CLSCTX_INPROC_SERVER,
IID_IDBInitialize,
(void**)&pIDBInitialize);
if(FAILED(hr))
AfxMessageBox(_T("CoCreateInstance Failed!!"));
VariantInit(&sscedbprop[0].vValue);
VariantInit(&dbprop[0].vValue);
VariantInit(&dbprop[1].vValue);
// Initialize a property with name of database
dbprop[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
dbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[0].vValue.vt = VT_BSTR;
dbprop[0].vValue.bstrVal = SysAllocString(lpszDBName);
// Validation
if(NULL == dbprop[0].vValue.bstrVal)
{
hr = E_OUTOFMEMORY;
goto CleanExit;
}
// Specify the property with name of the database
dbprop[1].dwPropertyID = DBPROP_INIT_MODE;
dbprop[1].dwOptions = DBPROPOPTIONS_REQUIRED;
dbprop[1].vValue.vt = VT_I4;
dbprop[1].vValue.lVal = DB_MODE_READWRITE;
// Specify the property for TRANSACTION COMMIT MODE --------- it seems does not work
// sscedbprop[0].dwPropertyID = DBPROP_SSCE_TRANSACTION_COMMIT_MODE;
// sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
// sscedbprop[0].vValue.vt = VT_I4;
// sscedbprop[0].vValue.lVal = DBPROPVAL_SSCE_TCM_FLUSH;
sscedbprop[0].dwPropertyID = DBPROP_SSCE_FLUSH_INTERVAL;
sscedbprop[0].dwOptions = DBPROPOPTIONS_REQUIRED;
sscedbprop[0].vValue.vt = VT_I4;
sscedbprop[0].vValue.lVal = 1;
// Initialize the property set
dbpropset[0].guidPropertySet = DBPROPSET_DBINIT;
dbpropset[0].rgProperties = dbprop;
dbpropset[0].cProperties = sizeof(dbprop)/sizeof(dbprop[0]);
//Initialize property set DBPROPSET_SSCE_SESSION --------- it seems does not work
// dbpropset[1].guidPropertySet = DBPROPSET_SSCE_SESSION;
// dbpropset[1].rgProperties = sscedbprop;
// dbpropset[1].cProperties = sizeof(sscedbprop)/sizeof(sscedbprop[0]);
dbpropset[1].guidPropertySet = DBPROPSET_SSCE_DBINIT;
dbpropset[1].rgProperties = sscedbprop;
dbpropset[1].cProperties = sizeof(sscedbprop)/sizeof(sscedbprop[0]);
// Set the properties into the provider's data source object.
hr = pIDBInitialize->QueryInterface(IID_IDBProperties, (void **)&pIDBProperties);
if(FAILED(hr))
{
goto CleanExit;
}
hr = pIDBProperties->SetProperties(sizeof(dbpropset)/sizeof(dbpropset[0]), dbpropset);
if(FAILED(hr))
{
goto CleanExit;
}
// Free the used memory
SysFreeString(dbprop[0].vValue.bstrVal);
// Initialize the SQL Server CE provider.
hr = pIDBInitialize->Initialize();
if(FAILED(hr))
{
goto CleanExit;
}
// Create new database session...
hr = pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)&pIDBCreateSession);
if(FAILED(hr))
{
goto CleanExit;
}
hr = pIDBCreateSession->CreateSession(NULL, IID_IUnknown, &pIUnknownSession);
if (FAILED(hr))
{
goto CleanExit;
}
CleanExit:
// Release the used memory
VariantClear(&dbprop[0].vValue);
VariantClear(&dbprop[1].vValue);
VariantClear(&sscedbprop[0].vValue);
free(&dbprop[0].colid);
free(&dbprop[1].colid);
free(&sscedbprop[0].colid);
free(&dbpropset[0].rgProperties);
free(&dbpropset[1].rgProperties);
free(&dbpropset[0]);
free(&dbpropset[1]);
CoTaskMemFree(&dbpropset[0].rgProperties);
CoTaskMemFree(&dbpropset[1].rgProperties);
CoTaskMemFree(&dbpropset[0]);
CoTaskMemFree(&dbpropset[1]);
if(pIDBProperties != NULL)
{
pIDBProperties->Release();
pIDBProperties = NULL;
}
if(pIDBInitialize != NULL)
{
pIDBInitialize->Release();
pIDBInitialize = NULL;
}
if (FAILED(hr))
{
DisconnectDB();
}
return hr;
}
HRESULT CPBCls::DisconnectDB(void)
{
if(pIDBCreateSession != NULL)
{
pIDBCreateSession->Release();
pIDBCreateSession = NULL;
}
if(pIUnknownSession != NULL)
{
pIUnknownSession->Release();
pIUnknownSession = NULL;
}
if(pIDBProperties != NULL)
{
pIDBProperties->Release();
pIDBProperties = NULL;
}
if(pIDBCrtCmd != NULL)
{
pIDBCrtCmd->Release();
pIDBCrtCmd = NULL;
}
if(pICmdText != NULL)
{
pICmdText->Release();
pICmdText = NULL;
}
// Release interfaces
if(pIDBInitialize != NULL)
{
pIDBInitialize->Release();
pIDBInitialize = NULL;
}
return S_OK;
}
INT CPBCls::ReadSimpleData(ST_SIMPLE_DATA *strSimpleData, int *iDataCount)
{
HRESULT hr = NOERROR;
// for Rowset Process
INT iTotalRows=0;
CString temp;
CHAR *temp2;
ULONG lColumn = 0;
ULONG lNumCols = 0;
ULONG lCount = 0;
ULONG lNumRowsRetrieved = 0;
ULONG ConsumerBufColOffset = 0;
IAccessor *pIAccessor = NULL;
IColumnsInfo *pIColumnsInfo = NULL;
DBCOLUMNINFO *pDBColumnInfo = NULL;
DBBINDING *pBindings = NULL;
HACCESSOR hAccessor = NULL;
HROW hRows[10];
HROW *pRows = &hRows[0];
BYTE *pBuffer = NULL;
WCHAR *pStringsBuffer = NULL;
TCHAR szBuff [2048] = {NULL};
ConnectDB();
memset(szBuff, TEXT(' '), sizeof(szBuff));
wsprintf(szBuff,
TEXT("SELECT ID, Name FROM Sample_Table ORDER BY Name"));
hr = pIUnknownSession->QueryInterface(IID_IDBCreateCommand, (void**)&pIDBCrtCmd);
if (FAILED(hr))
{
goto CleanExit;
}
hr = pIDBCrtCmd->CreateCommand(NULL,
IID_ICommandText,
(IUnknown**)&pICmdText);
if(FAILED(hr))
{
goto CleanExit;
}
hr = pICmdText->SetCommandText(DBGUID_SQL, (LPTSTR)szBuff);
if(FAILED(hr))
{
goto CleanExit;
}
// Execute the SQL query statement
hr = pICmdText->Execute(NULL,
IID_IRowset,
NULL,
NULL,
(IUnknown **)&pIRowset);
if (FAILED(hr))
{
goto CleanExit;
}
//////////////////////////////////////////////////////////////////////////
// START Rowset Process
//////////////////////////////////////////////////////////////////////////
hr = pIRowset->QueryInterface(IID_IColumnsInfo, (void **)&pIColumnsInfo);
if(FAILED(hr))
{
goto CleanExit;
}
pIColumnsInfo->GetColumnInfo(&lNumCols,
&pDBColumnInfo,
&pStringsBuffer);
pIColumnsInfo->Release();
pBindings = new DBBINDING[lNumCols];
for(lCount=0; lCount<lNumCols; lCount++)
{
pBindings[lCount].iOrdinal = lCount+1;
pBindings[lCount].obValue = ConsumerBufColOffset;
pBindings[lCount].pTypeInfo = NULL;
pBindings[lCount].pObject = NULL;
pBindings[lCount].pBindExt = NULL;
pBindings[lCount].dwPart = DBPART_VALUE;
pBindings[lCount].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
pBindings[lCount].eParamIO = DBPARAMIO_NOTPARAM;
pBindings[lCount].cbMaxLen = 48;
pBindings[lCount].dwFlags = 0;
pBindings[lCount].wType = DBTYPE_WSTR;
pBindings[lCount].bPrecision = pDBColumnInfo[lCount].bPrecision;
pBindings[lCount].bScale = pDBColumnInfo[lCount].bScale;
// Compute the next buffer offset.
ConsumerBufColOffset += 48; //pDBColumnInfo[lCount].ulColumnSize;
};
hr = pIRowset->QueryInterface(IID_IAccessor,
(void **)&pIAccessor);
if(FAILED(hr))
{
goto CleanExit;
}
pIAccessor->CreateAccessor(DBACCESSOR_ROWDATA,
lNumCols,
pBindings,
0,
&hAccessor,
NULL);
// Get a set of 10 rows.
pIRowset->GetNextRows(NULL,
0,
10,
&lNumRowsRetrieved,
&pRows);
pBuffer = new BYTE[ConsumerBufColOffset];
// Display the rows.
while(lNumRowsRetrieved > 0)
{
for(lCount=0; lCount<lNumRowsRetrieved; lCount++)
{
memset(pBuffer, 0, ConsumerBufColOffset);
pIRowset->GetData(hRows[lCount], hAccessor, pBuffer);
// Insert item
// ID
temp = (LPTSTR)(LPCTSTR)&pBuffer[pBindings[0].obValue];
temp2 = StringToChar(temp);
strSimpleData[iTotalRows].iID = atoi(temp2);
// Walk through each columns...
for (lColumn=1; lColumn<lNumCols; lColumn++)
{
// Name
wcscpy(strSimpleData[iTotalRows].tcaName,(LPTSTR)(LPCTSTR)&pBuffer[pBindings[lColumn].obValue]);
}
// Update the total Row counter
iTotalRows++;
};
// Release the rows retrieved.
pIRowset->ReleaseRows(lNumRowsRetrieved,
hRows,
NULL,
NULL,
NULL);
// Get the next set of 10 rows.
pIRowset->GetNextRows(NULL,
0,
10,
&lNumRowsRetrieved,
&pRows);
}
*iDataCount = iTotalRows; // Return Total Data Count
CleanExit:
if (pStringsBuffer != NULL)
{
CoTaskMemFree(pStringsBuffer);
pStringsBuffer = NULL;
}
if (pBuffer != NULL)
{
CoTaskMemFree(pBuffer);
pBuffer = NULL;
}
if (pDBColumnInfo != NULL)
{
CoTaskMemFree(pDBColumnInfo);
pDBColumnInfo = NULL;
}
if (pBindings != NULL)
{
CoTaskMemFree(pBindings);
pBindings = NULL;
}
if (temp2 != NULL)
{
delete temp2;
temp2 = NULL;
}
if (pIColumnsInfo != NULL)
{
pIColumnsInfo->Release();
pIColumnsInfo = NULL;
}
if (pIAccessor != NULL)
{
pIAccessor->ReleaseAccessor(hAccessor,NULL);
pIAccessor->Release();
pIAccessor = NULL;
}
if (pIRowset != NULL)
{
pIRowset->Release();
pIRowset = NULL;
}
if (pIDBCrtCmd != NULL)
{
pIDBCrtCmd->Release();
pIDBCrtCmd = NULL;
}
if (pICmdText != NULL)
{
pICmdText->Release();
pICmdText = NULL;
}
DisconnectDB();
if(FAILED(hr))
{
return ERROR;
}
return OK;
}
...
...
...
View 2 Replies
View Related
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
Jan 4, 2008
when i'm consistantly updating database on the server, I've noticed that the memory usage goes higher and higher. How can I prevent this? Or is there a way to prevent this other than turning off the computer time to time?
thank you,
View 3 Replies
View Related
