|
|
Shah Shehpori wrote:
Friends,
In an application developed in Visual C++, i used ADO to connect to database. This application runs round the clock. Application establish five connections to the database server and we call stored procedures from SQL server which are very small.
The problem is that our application runs normally for about 13 or 15 hours, after that application starts getting Timeout Error from ADO (SQL server). This error can occur any time, sometimes it occurs just after 4 hours. On netstat at command prompt, i can see that all five connections to database server are in "Established" state. I am unable to find the reason of this error. I changed the operating system, i run both application and SQL server on same machine and on different machines, run my tests on different networks;...but nothing worked for me. Our tables are not too big.
In my experience working with databases, long held connections are not reliable. I've worked with DB2, Oracle, SQL Server, Sybase, and all of them are simply not adequate for long term connections. My suggestion would be creating a connection pool and work with disconnected client-side recordsets, which is, btw, the way server applications normally work.
ORACLE One Real A$#h%le Called Lary Ellison
|
|
|
|
|
 I have 3 tables:
table1:
DateMod (smalldatetime)
StId
Eid
table2:
DateMod (smalldatetime)
StId
Wid
table3:
stid
I am selecting the max(Wid) and max(eid) based onstid:
select max(Wid),date
from table2 a,table 3
where a.stid=b.stid
group by a.stid
union
select max(eid),date
from table1 a,table 3
where a.stid=b.stid
group by a.stid
At this point I need to compare the dates between the results
(if sid listed in both results) and select the most resent value
from one of the tables for each stid.
How do I do this?
|
|
|
|
|
If this is against SQL Server or Oracle, you could create a stored proc to do this in two passes or get real complex using subqueries in one pass.
TWO PASS:
create procedure GetLastModDate
AS
declare @tStidDates (stid int NULL,
LastModDate smalldatetime NULL)
set nocount on
insert @tStidDates
(
stid,
LastModDate
)
select
A.stid,
MAX(A.Wid)
from
table2 a,
table 3 b
where
a.stid = b.stid
group by
a.stid
insert @tStidDates
(
stid,
LastModDate
)
select
A.stid,
MAX(A.eid)
from
table1 a,
table 3 b
where
a.stid = b.stid
group by
a.stid
select
stid,
MAX(LastModDate)
from
@tStidDates
group by
stid
order by 1
onwards and upwards...
|
|
|
|
|
for some reason SQL is not taking this line:
declare @tStidDates (stid int NULL,
LastModDate smalldatetime NULL)
|
|
|
|
|
Sorry, it should be
declare @tStidDates table (stid int NULL,
LastModDate smalldatetime NULL)
onwards and upwards...
|
|
|
|
|
How do I define my database connection path with out defining the entire path(ex. c:\program files\blah blah\data). I have the database stored in a directory \data\testdb.mdb just off the main program directory. I can not seem to make it read just the realative path. It is making me define the entire path. I have done this in the past but can not remember. Thanks for you help!
|
|
|
|
|
I need to programatically change an ODBC connection. So I was wondering, where is the ODBC connection 'stored'? Is it in the registry, a file somewhere, or what? My feeble efforts haven't been able to turn up anything useful... Thanks 
Paul 
Computers let you make more mistakes than any other invention in history...with the possible exception of handguns and tequila.
modified 18-Jul-18 11:59am.
|
|
|
|
|
OK found it thanks - in the registry... For those of you that were wondering:
HKLM\SOFTWARE\ODBC\ODBC.INI\<conn_name_here>
Paul
Computers let you make more mistakes than any other invention in history...with the possible exception of handguns and tequila.
modified 18-Jul-18 11:59am.
|
|
|
|
|
Yes. For more info on using the setup/config API, see ODBCINST.H.
onwards and upwards...
|
|
|
|
|
Hiya I am using ADO and need to be able to detect if a table already exists.
If the table exists, I need to drop the table and create this new table.
This is my code so far:
// create table and don't allow any zero value records
sqlcmd.Format( "CREATE TABLE Pxhsinv (Invoice varchar(30) NOT NULL,Invoice_Record varchar(8000) NOT NULL)" );
// execute the sql string
pConnection->Execute(_bstr_t(sqlcmd), RecordsAffected, 1);
// empty string
sqlcmd.Empty();
How do I detect if the table exists already..
Thanks.
|
|
|
|
|
I think that if the table already exists, the database will throw an error. Could you trap it?
|
|
|
|
|
Run the Query -
Select name from sysobjects where name like 'Pxhsinv' and type = 'U'
If this query gives some record in the resultset, the table is present else not.
Gaurav
|
|
|
|
|
I have a customer with a database problem. On a peer-to-peer network they run a point-of-sale system with the database on one host. This software is written such that each host accessing the database must have a drive mapped to the database home folder. A problem occurs when the data host is booted up after the clients - the share is not available, and so nothing works until they reboot everything. Manually remapping the drive is confusing for users, and I often find 5 drives mapped to the same share on a host by the time they call me.
I'd like to take a crack at rewriting the whole system (it's fairly simple - Inventory and Sales only, no G/L) and want to circumvent this problem. What is the proper way to implement the data access such that, if the database is unavailable at startup, it can reliably continue to check for it each time a user attempts access?
"The Lion shall lie down with the Lamb;
but the Lamb will not get much sleep..."
Lazarus Long
|
|
|
|
|
It depends on how you connect to the database and what kind of database it is.
onwards and upwards...
|
|
|
|
|
Obviously, and the selection of platform will depend on what techniques are most reliable and what supports them. The question remains, what is best?
"The Lion shall lie down with the Lamb;
but the Lamb will not get much sleep..."
Lazarus Long
|
|
|
|
|
In my SQL Db I have a view, which provides for me a well defined interface to obtain this infomation. Alas, this view pulls a set of data which is in format of
TestName | SectionName | QuestionText
---------+-------------+-------------
SAT | Reading | Who is cap. kirk
SAT | Reading | ...
SAT | Reading | Blah
SAT | MATH | 10+12
SAT | MATH | 10+1
SAT | MATH | 8==7
And I load this into a single table in a dataset using the fill command. Then we create the filter object. And he is the issue, How do I databind an object to the section Name column and only show the unique/ distint values?
From previous posts on other sites dataView.RowFilter() is not an option, it only supports 'where clause' like functionality. The only thing that I can not change is the information that is piped to me from the database (that is a well defined and documented interface.) Thus I need to work with the data set information some how (can I convert it xml, would that help any?)
|
|
|
|
|
you can issue a second statement
SELECT DISTINCT SectionName FROM myView
|
|
|
|
|
Right O, but that causes another trip to the database, and I already have it in memory, is there a better way?
|
|
|
|
|
Use a Hashtable, make the SectionName the key.
|
|
|
|
|
I am not a DBA and only know enough SQL to get the job done. Well, most of the time. The rest of the time I wing it and it normally works fine. This time though I am stuck and need your wisdom 
Ok I have three tables, Companies, Mailshots and Emails.
In Emails is the Status column which can contain three possible values; Sent, Failed, In Queue.
I need a view which will show me the number of Sent, Failed and In Queue rows for all Mailshots for all Companies.
So the result should be something like this:
Company | Mailshot | Sent | Failed | In Queue
---------------------------------------------
ACME | ACME101 | 100 | 10 | 150
ACME | ACME102 | 300 | 10 | 250
ACME | ACME103 | 0 | 0 | 350
I have been trying to wrap my head around CUBE and PIVOT in SQL but alas God never intended for my brain to grow so big, so I can't (wrap around that is.)
Any help, however obvious, is greatly appreciated. Thank you. *slinks off back to HTML and CSS*
Paul Watson
Bluegrass
Cape Town, South Africa
Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"
|
|
|
|
|
select
C.CompanyName,
S.MailShotDesc,
SUM(case E.Status when 'S' then 1 else 0 end) 'Sent',
SUM(case E.Status when 'F' then 1 else 0 end) 'Failed',
SUM(case E.Status when 'Q' then 1 else 0 end) 'In Queue'
from
Company C,
MailShot S,
EMails E
where
S.CompanyKey = S.CompanyKey and
E.MailShotKey = S.MailShotKey
group by
C.CompanyName,
S.MailShotDesc
order by 1, 2
onwards and upwards...
|
|
|
|
|
This format is the simple way to cross-tab detail records with different statuses.
onwards and upwards...
|
|
|
|
|
Thank you very much basementman, much appreciated and it works like a bomb
Paul Watson
Bluegrass
Cape Town, South Africa
Robert Edward Caldecott wrote:
My father-in-law calls yer man bits "weasels"
|
|
|
|
|
Friends i made a trigger in SQL server. The purpose of this trigger is very simple i.e to find out the number of records updated in the table and then print it. For this purpose i am using:
select @count = count(*) from updated
This is working fine in my home computer, but i am getting error in my office computer that:
Invalid object 'updated'
i.e SQL server is unable to find the table 'updated' which is its own internal table.
How can i get rid of this error.
Thanks
Imtiaz
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
