|
adOpenForwardOnly, adLockReadOnly AND make sure connection and recordset have CursorLocation set to adUseServer (adUseClient will always be slower since it ignores the adOpenForwardOnly), adLockReadOnly minimizes the cursor overhead and produces a "firehose" cursor with SQL. Records will not actually be fetched until you position to them with movenext, and you can't move backwards...but should be **MUCH** faster.
|
|
|
|
|
A couple things I can tell you about this.
1) If your table does not have a primary key in SQL Server, opening the table can take some time.
2) If the table has a primary key but it is not a clustered key you should try using a where clause such as "SELECT * FROM table_name WHERE PK_FIELD > 0". This forces SQL server to use an index even if you are getting everything this will often produce better results.
3) Try using a SELECT TOP XXX instead of select all.
|
|
|
|
|
I'm not sure of the extent of the application you are working on, however if you are trying to return an entire table into a recordset (i.e. - especially if it contains 600,000 records) you may want to consider other ways of displaying your data. Look at it this way. Your user can't see all 600,000 records at once.
Nick Parker
The goal of Computer Science is to build something that will last at least until we've finished building it. - Unknown
|
|
|
|
|
hello taualex
have you create the index for your table?
if you have not,then you must create it,
when you create it,you can shrink the time to about 1/5
open you sqlserver,right click to show manager index,
add the index on the fields which you search for it
|
|
|
|
|
Use index in your database and try to use ADO.NET for
data communication. Bear in mind that don't use DataSet if you wanna use ADO.NET.
|
|
|
|
|
I believe the only way to fix this is to use an ODBC driver, which, the last time I used one (several years ago), you had direct control over how many records you wanted to retrieve at a time. In other words, you could send the query and the retrieve records at your own consumption rate.
Marc
|
|
|
|
|
I am Vietnamese. I am using Unicode and Database to design my Website. But I have problem. I use Unicode for Vietnamese , when I input into Form and Submit , but my data saved in MS Access 2000 not correct that I input into Form. Why ? How do I can correct this mistake.
Example :
I am input : Phương Thanh
Database Access 2000 saved : Phỳòng Thanh
Thank you.
|
|
|
|
|
I can try list:
1:
if you have use the :enctype="multipart/form-data"
if you have,delete it
2:
you can
use asp:
<%response.write request("yourelement")%>
to test if the request("yourelement") is not correct
try it,and ask me your result,friends!;P
|
|
|
|
|
I started using ADO.NET to access data from SQL database in my ASP.NET applications, however, those commands I used so far is to talk to an existed database. If I want to create a new database, is there any easy way to do it in ADO or sth. else? Thanks a lot!
|
|
|
|
|
Assuming you are connected to the server as a user that has the rights to create a database (and next some tables)
CREATE DATABASE <database name>; look up in Books Online the syntax for CREATE DATABASE to see all the different settings you can specify. While you're looking that up, look up CREATE TABLE too, because you'll need some tables added in order to do much with that database
James
Sig code stolen from David Wulff
|
|
|
|
|
This is a sql command, and I normally use ADO's object SQLcommand and its method "command.ExecuteNonQuery()" to execute the required sql command in my C# program or ASP.NET c# server code. However, that SQLcommand object requires connection first, which needs to have a existed DB available. So in this case, sqlcommand may not be a good choice to run that creation command. Any ideas any other object I can use in my C# program? Thanks a lot!
|
|
|
|
|
You can connect to SQL server without using a specific database.
If you specify CATALOG in your connection string, remove it; and you should be ok. The account you use to connect to SQL Server (either Windows authentication or username/password authentication) needs to have permissions to create databases.
James
Sig code stolen from David Wulff
|
|
|
|
|
I am using ADO and have the following queries in my database
Query: GetPW
SELECT [PW].[Number], [PW].[Status] FROM PW
WHERE [PW].[id]=CCID;
Query: GetPR
SELECT [PR].[Number], [PR].[Status] FROM PR
WHERE [PR].[id]=CCID;
Query: GetPWPR
PARAMETERS CCID Long;
SELECT *
FROM GetPW LEFT JOIN GetPR ON [GetPW].[Number]=[GetPR].[Number] ORDER BY [GetPW].[Number];
When I execute query "GetPWPR" from within access, It works fine. (ie Prompts for CCID input value and returns records from PR and PW as expected)
However when I execute using ADO::Execute command it fails with the error "IDispatch error #3127" (DB_E_NOTABLE)
Any clues ?
Thanks
|
|
|
|
|
Does anyone know how to programmatically produce the OLEDB connection dialog box so that my application can ask for a database and then get its ConnectionString.
I'm using C#
Thanks,
Dave.
|
|
|
|
|
You must use the Microsoft OLE DB Service Component 1.0 . It's a COM object.
Jason Gerard
|
|
|
|
|
Cheers, I'll have a look.
Dave.
|
|
|
|
|
I have a MS-SQL 2000 db with a timestamp field. What is the best type in c# to use to store the value of a timestamp?
The Doc's suggest that it is an 8 Byte number and that it should be stored in a DateTime. Is this correct?
Thanks
Stephen.
|
|
|
|
|
This is the situation. We have an online SQL server powering a web site, and then another SQL server powering a back-office system. The web site SQL server is online all the time while the back-office SQL server only ever comes online when the users dial up.
When the users dial up we want to synch the two databases to contain identical data. Now while the back-office SQL server was offline the users could have entered in many records as well as many records being recorded on the web site SQL server. Obviously these records have unique IDs and primary keys associated with them, so we need to maintain relationships etc.
Neither is the "central" server so we cannot just replicate one to the other.
Does any body have any pointers as to what the most reliable method is of achieving this? Any links to tutorials, walk throughs, forums etc. on the subject would be much appreciated, ta.
|
|
|
|
|
Maybe I'm not thinking about this correctly, but if you are using SQL Server 7+ can't you use Merge replication. With merge replication you don't need a "master" db. One DB has to be the publisher and others can be subscribers. With merge replication you can enter data in any of the DB's and have it replicated to the others.
|
|
|
|
|
Matt Gullett wrote:
Maybe I'm not thinking about this correctly, but if you are using SQL Server 7+ can't you use Merge replication
No you are thinking about it perfectly well. Merge replication is what I am setting up, but I thought it best to ask you guys if there were better alternatives.
Thanks
|
|
|
|
|
Has anyone ever created a DTS package, then saved it to a .bas file to be used in VB? We are doing that on a project at work where we are having some issues. The DTS script is making a connection to a database and then specifying a flat file as another source. The idea here is to pump the data (160,000 records) over to the database into a table. While this is an extremely efficient method one of our users can not do this. The pump basically fails. No data gets moved at all. I know the connection is established through SSPI however I am not sure what could be going wrong. This may seem vague, but has anyone run into issues like this before?
Nick Parker
If the automobile had followed the same development as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year killing everyone inside.
-Robert Cringely
|
|
|
|
|
I have a DTS package that was failing; but i created a user on the SQL database with just enough permissions and modified the DTS to use that authentication instead of Windows Authentication, it worked.
It could be the Windows login that he is trying to do this from does not have suffcient rights.
I am yet to figure out the problem in the other case.
I do not know whether it would apply to jour scenario. I never created a VB DTS package
Thomas
modified 29-Aug-18 21:01pm.
|
|
|
|
|
Thomas George wrote:
i created a user on the SQL database with just enough permissions and modified the DTS to use that authentication instead of Windows Authentication, it worked.
Could you show how you switched the authentication to SQL from NT, I'm not sure how I can convert this. I tried to switch it over to an ADODB connection, however it wouldn't take that and we are going to SQL Server.
Set oConnection = goPackage.Connections.New("SQLOLEDB")
oConnection.ConnectionProperties("Integrated Security") = "SSPI"
oConnection.ConnectionProperties("Persist Security Info") = False
oConnection.ConnectionProperties("Initial Catalog") = "[OurTableName]"
oConnection.ConnectionProperties("Data Source") = "[OurServerName]"
oConnection.ConnectionProperties("Application Name") = "DTS Designer"
oConnection.Name = "[OurServerName]"
oConnection.ID = 1
oConnection.Reusable = True
oConnection.ConnectImmediate = False
oConnection.DataSource = "[OurServerName]"
oConnection.ConnectionTimeout = 60
oConnection.Catalog = "Reference"
oConnection.UseTrustedConnection = True
oConnection.UseDSL = False
Nick Parker
If the automobile had followed the same development as the computer, a Rolls-Royce would today cost $100, get a million miles per gallon, and explode once a year killing everyone inside.
-Robert Cringely
|
|
|
|
|
Actually, you can dump any DTS out to VB... It's cryptic as hell to read if you have a complex one cause DTS doesn't choose damnes worth a darn. In a cast like yours, I usually embed some kind of logging mechanism into the DTS package to get the returned status codes. And security can be a problem for running stuff, but it's usually only if run manually. The agent running stuff is usually set to go from install to run against any db.
Mark
|
|
|
|
|
Can anyone suggest me the line of codes and the data type of the field through which we can store files in an SQl Server Database through an ODBC Recordset
Samir Sood
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
