|
Thank you very much for your comment. But in technically, if you want install it and meet the problem like i post in the previous thread, what is the best solution to solve it?
|
|
|
|
|
Hello
I seriously need help on this. Please take time listen to my problem.
We have a SQL 2000 database running for a year. the main table called 'datatable' has grown to 1.8GB. Now we want to change the old database name to a different name without losing all the data in it. We made full backup of the old database and create a new empty database and restore the old database to it.
After I done that the program that populates the "datatable" in the newly restored database always give me error (IDispatch error #3121: [Microsoft][ODBC SQL Server Driver]Timeout expired :Microsoft OLE DB Provider for ODBC Drivers)
when rs->Open("DataTable", _variant_t((IDispatch *)conn,true), adOpenKeyset, adLockOptimistic,adCmdTable); excuted.
I have tried to recreate table index. It still doesn't work.
The datatable has first column "ID" as integer AutoNumber.
If I truncate the table, the program works fine. If I delete most of the data
for example only keep last a few hundred rows, the program also works.
If I don't use rs->open and rs->addNew() method to insert data, instead of using a simple insert SQL query, it works fine.
I believe the problem is that when we do restore something got messed up, but
how? If the datatable is too big and need more time to process when we call rs->open, but why the old database doesn't have this problem. The same program are running on those two databases. Any ideas?
Please help me out.
|
|
|
|
|
Opening a 1.8GB table is not a good idea either. USe WHERE clause to filter the records you need.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
hello Farhan
I don't quite understand.
If I use a where clause for example select * from datatable
where id = (select max(ID) from datatable), the rs->Open method will be okay. However, later on I do
rs->addNew() after addNew method, I do "if (FAILED(rs->Update()))" to commite the insertion. It will return me false means that it cannot update to datatable.
I don't know how to put a where clause there in order to do a insertion. Any ideas?
Thank you
|
|
|
|
|
AddNew()/Update() is a very inefficient way of adding new records, specially in a big table. I suggest you use INSERT query to insert new records.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
I understand AddNew() and Update is not a good way to
insert into a large table.
However, my question is why this method works fine with
the original table.
For the restored new table it refused to work.
|
|
|
|
|
Look for physical and logical fragmentation. Physical fragmentation can be fixed by using a good defragmenter. Database files fragmentation can be viewed and fixed using DBCC commands. For example
DBCC SHOWCONTIG(DataTable)
Also, check if antivirus or malware is blocking access to data files (very rare possibility).
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hello Farhan
I tried
DBCC DBREINDEX (datatable)
DBCC SHOWCONTIG(datatable)
It does reduce some fragmentation of the database file.
This is the message I got
DBCC SHOWCONTIG scanning 'DataTable' table...
Table: 'DataTable' (607341228); index ID: 1, database ID: 10
TABLE level scan performed.
- Pages Scanned................................: 4100
- Extents Scanned..............................: 514
- Extent Switches..............................: 513
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.81% [513:514]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.39%
- Avg. Bytes Free per Page.....................: 703.8
- Avg. Page Density (full).....................: 91.31%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
It seems even better than the production database, but still
the program works on the production database only, and give me the same timeout expired error when tried on the new database which is restored from the production database.
Any ideas ?
Thank you very much
|
|
|
|
|
Please post connection strings for both database.
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Might have been just an error in your explanation but, your old table name was “datatable” right? This table was deleted after backing up the data right?
Could the problem be you failed to update your open command? It looks like your attempting to open a connection to the old table.
rs->Open("DataTable", _variant_t((IDispatch *)conn,true), adOpenKeyset, adLockOptimistic,adCmdTable);
Anther shot in the dark here, is your autonumber column a 32 bit int? How many rows do you have in that table?
int
Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
ZeePain! wrote: This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.
thedailywtf.com[^]
|
|
|
|
|
Thank you for your reply
Let me explain
I have a production live database called FASD. Now I make backup of the database. It will create a back file in harddisk.
Then, I create a brand new database called POMS. On this database, I restore FASD's backup file to it. The steps are
All tasks -> Restore Database -> Restore: From device -> Select device -> Add -> Pick a File name (the database backup file) On the Option tab click Force restore over existing database click box.
It is just like create a copy of production database FASD, and the new name of the database is called POMS. That's it.
Every table in FASD database should be exactly the same in POMS database.
The autonumber column is a 32 bit int. Currently we only have
140000 rows.
One thing I want to mention is that each row contains a binary column. The column stores picture binary data.
The datatype for this column is image. This why the database
is 18GB.
Any idea?
|
|
|
|
|
Do FASD and POMS reside on the same machine? If yes, then, are database files on the same volume (or HDD or partition)?
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Yes FASD and POMS reside on the same machine, same volume (D:\ drive)
Do you know how to look at those database's logic file name?
I notice that when I do the restore, I need changed the physical data file name, and physical log file name to the new physical data and log file name.
For example I need to change D:\MSSQLDATA\MSSQL\Data\FASD.mdf to D:\MSSQLDATA\MSSQL\Data\POMS.mdf and D:\MSSQLDATA\MSSQL\Data\FASD_log.ldf
to D:\MSSQLDATA\MSSQL\Data\POMS_log.ldf
however I am not be able to change to logic file name.
The restore utility read those infomation from the backup file.
|
|
|
|
|
flyflyfly wrote: Do you know how to look at those database's logic file name?
Run select * from sysfiles
IMHO, having same logical file names is not an issue here. Check for the differences (FASD and POMS) in 'Recovery Model', triggers, Locks (Under Management->Current Activity->Locks/Object in EM).
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hi all,
I have datetime field in database. When I am getting records older than
specific date that I passed in parameter I can not get records that
differs only in Time part of the datetime value.
Looks like comparison of Time portion in query failed.
I am using ACCESS database and ADO OleDbCommand. When in debug in Visual Studio QuickWatch clearly can see that the record that am going to get is older than datetime in TimestampParam by 10 sec (for example). If I substract 1 day from the
database field value I am getting the record. It is definetly time
portion comparison failed.
These are some lines from my code to show how I am reading database
OleDbDataAdapter adapter = new OleDbDataAdapter();
myCommand.CommantText = "SELECT [NetLog].* FROM [NetLog] WHERE
Timestamp < TimestampParam"
adapter.SelectCommand = myCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
int iNumRows = adapter.Fill( dataSet );
I would appreciate any advice
Thanks,
Roman
|
|
|
|
|
Is possible to keep MSDE on my machine, and install MS SQL EXPRESS 2005?
I want to keep both engines.
|
|
|
|
|
Yes it is possible. I am doing so right now on my machine  . Along with MySQL and Firebird.
|
|
|
|
|
The code below should insert this record into the database, I don't the error. I drops through and run the line that enables the btnAdd.Enable = true. How do I fix it?
private void SaveRecord()
{
string strSQL;
if(btnSave.CommandArgument == "Add")
{
strSQL =
"INSERT INTO CoinCollection " +
" (ID, TypeOfCoin, YearOfCoin, StateOfCoin, DateReceive)" +
"VALUES " +
" (@ID, @TypeOfCoin, @YearOfCoin, @StateOfCoin, @DateReceive)";
}
else
{
// The user is updating an existing item
strSQL =
"UPDATE CoinCollection " +
" SET TypeOfCoin = @TypeOfCoin, " +
" YearOfCoin = @YearOfCoin, " +
" StateOfCoin = @StateOfCoin, " +
" DateReceive = @DateReceive " +
"WHERE ID = @ID";
}
SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand cmSQL = new SqlCommand(strSQL, conn);
// Add all the requuired SQL Parmeters.
cmSQL.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = Convert.ToInt32(tbID.Text);
cmSQL.Parameters.Add(new SqlParameter("@TypeOfCoin", SqlDbType.VarChar, 50)).Value = tbToc.Text;
cmSQL.Parameters.Add(new SqlParameter("@YearOfCoin", SqlDbType.VarChar, 50)).Value = tbYoc.Text;
cmSQL.Parameters.Add(new SqlParameter("@StateOfCoin", SqlDbType.VarChar, 50)).Value = tbSoc.Text;
cmSQL.Parameters.Add(new SqlParameter("@DateReceive", SqlDbType.VarChar, 50)).Value = tbDr.Text;
try
{
conn.Open();
cmSQL.ExecuteNonQuery();
strMsg = "Coin successfully saved to the database.";
}
catch(Exception exp)
{
strErrorMsg = "Database error! Coins not saved to database. Error Message: " + exp.Message;
}
finally
{
conn.Close();
btnAdd.Enabled = true;
}
}
bhumber
|
|
|
|
|
the btnAdd.Enabled = true; line is in the finally block so it will always run. You might want to put that in the try block.
Also if you do this:
int intAffectedRecords = cmSQL.ExecuteNonQuery();
you can get the count of affected records so you could then say if there were records affected the button would be enabled:
btnAdd.Enabled = (intAffectedRecords > 1) ? true : false;
You can also just use the Exception exp's ToString method to see the entire error message:
Response.Write(exp.ToString());
Dirk Watkins
|
|
|
|
|
i have some confusion. Scenario is that for an Employee to
start and end job the following fields are available;
Field DataType
===== ========
Emp_Id Foreign Key}
DateTime PrimaryKey }----> Composite Primary key
CheckInTime DateTime
CheckOutTime DateTime
InOutStatus bit
HoursWorked ?--------> What shoulud be DataType?
When an Employee Checks in All fields are set and
InOutStatus is set to 1 Except CheckOutTime and
HoursWorked.When The Employee checks out InOutStatus is set
to 0,CheckOutTime is set and then i have to calculate the
HoursWorked (TimeDuaration). Constarins are CheckOutTime is
always greater than CheckInTime.(There may be different
dates for them as employee gets check in ,Nov 29,2005 on
time 17:00 and checksout , Nov 30,2005 on time 03:30).Now
according to calculation he worked for 10 hours and 30
minutes .How to calculate this hours worked(only time
duration and to which datatype it should be saved in sql)
as well as the Stored Procedure for this whole
process.Whenever Employee comes his Emp_Id is only known.
Thnx in Advance.
|
|
|
|
|
If minute level precision is fine then save the number of minutes and use int as datatype.
Declare @dt1 datetime, <br />
@dt2 datetime<br />
<br />
Set @dt1 = GetDate()<br />
Set @dt2 = DateAdd(yyyy, 1, @dt1)<br />
<br />
select DATEDIFF(mi, @dt1, @dt2)
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
Hello developer, i don't become the primary key back.
Whats wrong? The returnvalue is always sero.
C#-Code:
class vers
{
OleDbConnection connection = new OleDbConnection(ConnectionString);
....
public void Eintragen(sql)
{
OleDbCommand myOleDbCommand = new OleDbCommand(sql, connection); myOleDbCommand.ExecuteNonQuery();
myOleDbCommand = new OleDbCommand("SELECT @@IDENTITY", connection);
return Convert.ToInt32(myOleDbCommand.ExecuteNonQuery());
}
}
|
|
|
|
|
This will only work once a new identity has been created or changed should only be used within a stored procedure.
Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.
INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'
|
|
|
|
|
In sql database , I have two tables named Emp_Info and Emp_Experience
the structure of table is like this;
Emp_Info
========
Emp_Id PrimaryKey(Auto)
Emp_Name
Emp_Age
Emp_Experience
==============
Exp_Id PrimaryKey(Auto)
Emp_Id ForeignKey
Exp_Detail
Exp_Year
. . . . ... and so on
Working on a single form, All these fields have to be enterd and are saved against one button click.The Problem is how can i get the Emp_Id for Emp_Experience table to save Experince and Emp_Info paralelly.The relationship btw Emp_Info and Emp_Experience is one to many. that is, Against one Employee there may be none or many Experiences.
in the scenario, i have to add Experiences first and then save the Employee? What 2 do? any guide line, help .....>?
i need its stored procedure for saving Experiences.
Thnx in Advance
|
|
|
|
|
Use something like,
1. Insert Into Emp_Info............
2. Store newly created auto generated id in a local variable as,
Select @newId = @@IDENTITY
3. Insert Into Emp_Experience....... and use @newId as fk here
Farhan Noor Qureshi
if (this == this) thow this;
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
