|
given that the prog calculates the data to be inserted would you suggest i write all data to a file and then run a bcp to bulk insert?
i think writing it all to a memory buffer might be a bit resource gobbling no?
---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"
|
|
|
|
|
Lauren: Read my previus answer, where I said that I think that is better to use the command BULK INSERT....
Regards...
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
carlos i did read your post
my problem is that the data to be inserted are created one by one from calcs in the prog ... i would have to buffer them somewhere until ready to do a bulk insert no?
my issue was where does one buffer them?
thanks 
---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"
|
|
|
|
|
lauren wrote:
my problem is that the data to be inserted are created one by one from calcs in the prog
In this case the only that you can make is insert record by record, using the INSERT SQL Statement, Is better to use a Stored Procedure...
Bt if you have thousand of records for insert, may be you can make a file, an send it to the server, for make a BULK INSERT, because the BULK INSERT is for insert data Files into the database....
Regards!!!
Carlos Antollini.
Sonork ID 100.10529 cantollini
|
|
|
|
|
If you have any indexes on the table, drop them and recreate them after the insert. Make sure you aren't logging everything (in SQL Server) if you don't need to.
Andy Gaskell, MCSD
|
|
|
|
|
hi andy
no transaction loging is used on this piece and the only index is the primary key for the table
---
"every year we invent better idiot proof systems and every year they invent better idiots ... and the linux zealots still aren't being sterilized"
|
|
|
|
|
One note:
Stored Procedures are not compiled, they are simply stored directly on the database, not as a SQL statement within your application. The great flexibilty that is gained through Stored Procedures is that if something in your SQL statement needs to be changed, you don't have to recompile your project as long as you don't change the name of the Stored Procedure. You also can expose yourself to other process methods that a stored procedure can do. 
Nick Parker
|
|
|
|
|
<nitpick>
Actually stored procs are compiled. The database (in this case SQL Server 7) will create an optimised execution plan the first time the proc is run. This plan is then stored for later use (eliminating the need to recreate the plan again and again if it were a standard SQL statement).
There is one gotcha to this though; the proc is optimized for the way it is run the first time, if you have some conditionals inside the proc the optimization could be different depending on the branch that gets executed.
</nitpick>
If you use the Query Analyzer (provided in the client tools) you can view the execution plan for a given query. Without a copy in front of me I can't tell you how to view it though, I remember something about the view that allowed you to switch from grid to report though.
James
Sonork ID: 100.11138 - Hasaki
"Not be to confused with 'The VD Project'. Which would be a very bad pr0n flick.  " - Michael P Butler Jan. 18, 2002
|
|
|
|
|
Hallo
Deleting a record with
m_pRecordset->Delete(adAffectCurrent);
fails with the message
[Microsoft][ODBC dBASE Driver] Selection too complex
Whats wrong?
|
|
|
|
|
i´ve a problem with crecordset::update()!
i´ve created a user-dsn(excel 2000 derivied) and with the class-wizard a crecordset derivied.
if i call edit(); i get an error message that the datagroups are readonly, but i've used dynaset!
i need a solution!!! please
thanx
i´m german, i know the quality of my english...
|
|
|
|
|
This problem is usually related to the fact that you don't have an index for the table, but as you are using excel, I am not sure !!
|
|
|
|
|
I will give anyone 10 $US whose solution I can first install successfully. Believe me I am an Honest bloke.
SETTINGS:
Database name people.mdb (Access2000)
table name tblPeople
Database connection is OK.
I am using European Date convention DD/MM/YYYY
The whole database contains other values as well.
Using Visual Basic Script
TASK:
What I need is to convert the string e.g. 1-9 into a string such as (I am guessing) CurrentYear-(1-9) so if CurrentYear is 2002 then result string is 2001-1993 so the database returns everybody born between 1993-2001. Never mind month and day.
AutoID Name BirthDate
Automatic Number text text
I need to query the database above database from a default.asp file with the following multiple drop-down box.
1-9 10-15 15-17 18-2122-24 25-2728-30 30-35 35-40 40-45 45-100
P.S.: If you think I should do things differently, you may suggest your own solution. (e.g.: Full date of birth can be given.) DD/MM/YYYY
Thanks.
Las Bujdoso
|
|
|
|
|
First, keep your money
Dim strTemp
strTemp = "1-9" 'Replace with whatever numbers you want
Dim strArray
strArray = Split(strTemp, "-")
Dim lngResult
lngResult = Abs(CLng(strArray(0)) - CLng(strArray(1)))
Response.Write Year(Now) - CLng(strArray(0)) 'This will figure out the most recent year
Response.Write Year(Now) - lngResult - CLng(strArray(0)) 'This will figure out the older year
Good Luck!
Andy Gaskell, MCSD
|
|
|
|
|
I'm currently writing a reasonable sized website which will have a considerable number of backend web pages for remotely updating database rows.
Recently I upgraded to Access 2002 and discovered the JOY of Data Access Pages. Export your form or datasheet view as a webpage, stick it on the webserver, backend system done!
However the site calls for the reliability of SQL Server, and I've been told that using Access is just barking up the wrong tree. I have tried linking my exported Data Access Pages to the SQL server tables but my webhosting company (www.webtrader.com.au) tell me this isn't allowed and doesn't work.
Does anyone know of a nice, datasheet-like export util for SQL Server tables? Can I export Access forms and simply link them to the server-side SQL Server tables using a DSN/UID/PWD?
I'm stuck between finishing my web site now with Access, and possibly suffering in the future because of it, or implementing it properly on SQL Server but spending another few weeks slaving away at manually creating the backend system.
Any suggestions would be greatly appreciated.
|
|
|
|
|
Hi Everyone,
Please consider following problem.
I made an ActiveX Dll with VB,and the COM object has a method returning an ADODB.Recordset object.
On my client I do the following:
1. connect to my COM object
2. Retrieve the data in an OLE object
3. Convert the OLE object into an ADORESULTSET (SetRecordset())
4. Create a datawindow from the ADORESULTSET (CreateFrom())
Step 1 to 3 do not seem to cause any problem. However, the CreateFrom() in step 4 returns a -5.
Two questions:
1. Does anyone know what it means when CreateFrom( ) returns a -5 (besides that it didn't work )
2. How can I populate datastore from ADO Recordset orrectly?
All help is very much appreciated. A part of the actual code is pasted below.
Regards,
FinalSkyRen
OLEOBJECT loo_myComponent //my component
OLEOBJECT loo_ADOResultSet
ADORESULTSET lrs_ADOResultSet
/*
xxxx
HERE'S THE CODE TO CONNECT TO MY COMPONENT
xxxx
*/
/* RETRIEVE DATA IN OLE OBJECT*/
loo_ADOResultSet= loo_myComponent.GetADORecordSet( )
/* STORE DATA IN ADO RESULTSET
lrs_ADOResultSet= CREATE ADORESULTSET
ll_rc = lrs_ADOResultSet.SetRecordSet( loo_ADOResultSet)
// returns 1
/* POPULATE DATASTORE FROM ADO RESULTSET */
lds = CREATE datastore
ll_rc = lds.CreateFrom( lrs_ADOResultSet) // returns -5
FinalSkyRen
|
|
|
|
|
Hi,
i have a database with tables person,position and player.
Person Table Definition:
person_id -> unique id of the person
person_first_name -> person first name
person.person_last_name -> person last name
For instance:
1,John,Scotch
2,Paul,Whisky
Position Table Definition:
position_id -> unique position id
position_short -> short description of the position
position_long -> description of the position
For instance:
1,QB,Quarterback
2,WR,Wide Receiver
3,TE,Tight End
4,DE,Defensive End
5,DT,Defensive Tackle
6,CB,Cornerback
Player Table Definition:
player_id -> unique player id
player_person_id -> foreign key pointing to Person table
player_offense_position_id -> foreign key pointing to Position table
player_defense_position_id-> foreign key pointing to Position table
For instance:
1,1,3,4
2,2,2,6
Now i want to display the info like this:
Name Last Name Off Posit. Def Posit.
John Scotch Tight End Defensive End
Paul Whisky Wide Receiver Cornerback
My query succeeds when i only use both id's for the
position instead of the descriptive form:
select person.person_first_name, person.person_last_name,
player.player_offense_position_id,
player.player_defense_position_id
from person, player
where
player.player_person_id = person.person_id
It also works when i change 1 of the id's to the long description:
select person.person_first_name, person.person_last_name,
position.position_long,
player.player_defense_position_id
from person, player, position
where
player.player_person_id = person.person_id
and player.player_offense_position_id = position.position_id
But then only the foreign key of the offensive position is resolved.
When i try to have both offense and defensive id's as descriptions,
like this, it doesn't work:
select person.person_first_name, person.person_last_name,
position.position_long,
position.position_long,
from person, player, position
where
player.player_person_id = person.person_id
and player.player_offense_position_id = position.position_id
and player.player_defense_position_id = position.position_id
How can i replace both the offensive position id and the defensive
position id with their respective entries from the position table?
(i.e. resolve the foreign key and use the long description)
Also, is there a reference to the SQL syntax anywhere?
As you can see, i'm rusty on the sql part
BK
|
|
|
|
|
Try this:
select person.person_first_name, person.person_last_name, p1.position_long, p2.position_long from person inner join player on person.person_id = player.player_person_id inner join position p1 on player.player_offense_position_id = p1.position_id inner join position p2 on player.player_defense_position_id = p2.position_id
Andy Gaskell, MCSD
|
|
|
|
|
I am developing a VB application using ADO which polls an adabas d DB and an oracle DB every minute. It seems that this programm leaks some 15mb memory every day if I open and close the connection every minute. This behaviour doesn't occur when I open the connection just once and keep it alive. Am I doing something wrong? Is this a bug? Or is this "by design"?
Michael
NT 4.0 SP5 & SP6a
VB6 SP5
MDAC 2.7
adabas d 11.01
oracle 8.1.5
code snippet:
Public gobjOraConnection As New ADODB.Connection
Public gobjOraCommand As New ADODB.Command
Dim objOraRecordSet as ADODB.RecordSet
' <connectionstring> aufbauen
strConnectionString = "DSN=" & gstrOraService & ";UID=" & gstrOraUsername & ";PWD=" & gstrOraPasswort
' Verbindung herstellen
gobjOraConnection.ConnectionString = strConnectionString
gobjOraConnection.Open
Set gobjOraCommand.ActiveConnection = gobjOraConnection
' In der Oracle DB nach Aufträgen suchen
strSQL = "SELECT * FROM " & gstrOraTable & " WHERE XCHG_RESCODE IS NULL"
gobjOraCommand.CommandText = strSQL
' Ergebnisobjekt zuweisen
Set objOraRecordSet = gobjOraCommand.Execute("adCmdText")
<doing something="" with="" the="" recordset="" here="">
objOraRecordSet.Close
set objOraRecordSet = Nothing
Set gobjOraCommand.ActiveConnection = Nothing
gobjOraConnection.Close
|
|
|
|
|
Strangely enough, I had the same problem a while ago. A colleague of mine
had a similar problem a couple of years before that. Since we both work in
C/C++ I don't know if this will help or not:
My first attempt was like this:
bar::foo() {
ADO_DB db;
ADO_RS rs;
db.Open();
...
db.Close();
}
This leaked badly. The next attempt was:
bar::foo() {
ADO_DB* db = new ADO_DB;
ADO_RS* rs = new ADO_RS;
db->Open();
...
db->Close();
delete db;
}
This leaked less, I don't know why, but still it leaked.
My working code looks like this:
class bar {
ADO_DB* mdb;
}
bar::bar() {
mdb = new ADO_DB;
}
bar::foo() {
ADO_RS* rs = new ADO_RS;
db->Open();
...
db->Close();
}
Class 'bar' is instantiated once and remains active for the life of
my program. In this final form, I can open and close the database as
often as I like. The leak as near as I can tell occurs deep within ADO.
I'm not sure about the Public declaration in VB, but it seems your
code snippet is very close to my 'working' solution. Does your variable
gobjOraConnection exist for the duration of the program's execution?
>>>-----> MikeO
|
|
|
|
|
yes, gobjOraConnection exists for the whole life of my application. There's another things I noticed: If i close the connection, the memory needed doubles from 3.5mb to 7mb and then slowly comes down again. Strange ...
|
|
|
|
|
Instead of this:
>Public gobjOraConnection As New ADODB.Connection
>Public gobjOraCommand As New ADODB.Command
Try this:
Public gobjOraConnection As ADODB.Connection
Public gobjOraCommand As ADODB.Command
set gobjOraConnection = new ADODB.Connection
set gobjOraCommand = new ADODB.Command
Late binding is supposed to help conserve memory. The theory goes that Your program doesn’t have to check to see if you made an instance to your object every time you make a call to it.
Throwing in a few set = nothings never hurts.
set gobjOraCommand = nothing
set gobjOraConnection = nothing
Good luck!
|
|
|
|
|
We are using ADO(in a C++ env.)
to connect to diffent databases just
by replacing the provider string.
What we noticed is that the ADO/OleDB Provider for
JetEngine (4.0 and 3.51) (Access) is very, very slow.
We compared with the the ADO/OleDB Provider for
MS-SQL and we found a factor 60 !!!.
We tried with old DAO MFC - Classes with
Access-databases and the factor was 100 faster
than with ADO/OleDB with JetEngine.
Just using plain OleDB didnt make things much faster.
Do someone know more about slowliness of OleDB for
Jetengine? Are there alternative OleDB drivers
that are faster?
 zack
|
|
|
|
|
Try to use client cursors instead of the default serverside cursors if the .mdb file is on your local pc:
_RecordsetPtr pSet(__uuidof(Recordset));
pSet->CursorLocation = adUseClient;
pSet->Open(...);
|
|
|
|
|
Hi everyone
I'm not sure if this is possible, but I was wondering if there was a way for two threads to concurrently (and reasonably independently) share an SQL Server connection, such as the OLE DB templates CDataSource object? Presently, my attempts have been serialised internally by SQL Server, so that the net effect is that one of the requests must complete before the other can begin...
thanks to anyone who can offer some pointers!
nb
|
|
|
|
|
One possible way of implementing this is to use ADO and ADO Events.
Basically you execute a SQl statement and once the statement has been executed, you will recieve an EVENT that the execution has completed. This will allow you to run your second sql statement. knowing the first one has finished.
see the following for example
http://msdn.microsoft.com/library/default.asp?url=/library/en-
us/ado270/htm/mdevteventmodelxvc.asp
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
