|
Dears,
I'm using MS SQL Server 2005 and i'm inserting a time value which is ArrivalTime in column and at the end of the trip i insert another time value in another column which is leavingTime
And I want to save the diffrence between the two times in another Column TripTime
so the value in TripTime should be LeavingTime - ArrivalTime
How can this be done.
Thanks
|
|
|
|
|
To get the difference between 2 dates use the DATEDIFF function.
But there's no need to store the TripTime since you've already stored Leaving and Arrival. If you store it you are going to risk the data getting out of sync if there are any changes. Instead, whenever you need to get the tip time, just make a call to DATEDIFF using leaving and arrival times.
|
|
|
|
|
Well, it depends. If you want to be able to search on the trip time - find all trips where the trip time was longer than/shorter than x amount, then you might want to build a computed column and index the table on that computed column, although indexes on computed columns have a set of prerequisite option values which IIRC aren't the defaults, so you have to be a bit careful about setting the options to ensure that the index gets used.
If you can't guarantee the options then perhaps a trigger to compute and store this value would be appropriate.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Delete can be rolled back but Truncate cannot be rolled back.
Begin Transaction
Delete Del_Details
Rollback
and i'm getting my deleted records
but still when i use ....
Begin Transaction
Truncate Table Del_Details
Rollback
This command is also giving me my deleted records..
So where is the difference between Delete and Truncate in respect of Rollback.......Plz explain
|
|
|
|
|
in Sql Server 2000 may be true for other DB's but i have not looked into it, the delete command cheks for data integrety(sp?) and actually removes each record from the tree if there are no FK problems. Truncate basically just moves the pointer from the bottom of the tree to the top and forgets that there was ever any data in there, essientially removes any reference to where that was in memory and starts over. Therefore Rollback cant work beacuse there is nothing to rollback, there is no longer any reference to whatever was in that table. I dont know for sure how they store the data, but using the analogy of a bTree helps when i explain it to people at work. hopefully i explained that right.
|
|
|
|
|
BOL: The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.
If you are in a transaction, it probably does not reuse the pages until COMMIT. Also, truncate can not be run when foreign keys are present.
|
|
|
|
|
You are having trouble with this one aren't you, are you expecting a different response this week!
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
 When you asked this question before (^) several of us gave you the fullest explanation possible. If you don't believe us the try google or something, because no matter how many times you ask the real answer is going to be the same. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi all,
Am trying to upsize certain tables from MSAccess database to SQL database.I have a preexisting database with tables which are vacant in SQL.While i try to upsize the database tables from MSAccess 2003 to SQL server am getting an error.I tried to upsize three tables out of which one was upsized fine with all data imported with out errors(was not having foreign key relations).But the other two are shooting errors and not upsizing .
The error looks like "
"Server Error 3726:"Couldnot drop object 'tablname' because it is referenced by a Foreign key constraint."
I tried to remove all the foreign key connections in SQL tables and still getting the same error.
Since am doing this for the first time,am unsure what exactly is the way to go?
Any help will be welcome.
Many Thanks,
|
|
|
|
|
The following code is giving me the described error. I have included my debug trace also.
Apparently if the "LIKE" text is not found at all is causing this behavior.
How do I get around it?
Thanks for your help.
Vaclav
m_FDRecordset->Open(AFX_DB_USE_DEFAULT_TYPE,
"SELECT * FROM [Log] WHERE [Callsign] LIKE '" + text + "%'",
CRecordset::readOnly );
CString strSQL = m_FDRecordset->GetSQL( );
TRACE("\n SQL %s ",strSQL);
Debug trace
SQL SELECT * FROM `Log` WHERE `Callsign` LIKE 'AA1A%' Error: attempted to position cursor on empty recordset.
Attempt to scroll past end or before beginning of data.
First-chance exception in FD2008.exe (KERNEL32.DLL): 0xE06D7363: Microsoft C++ Exception.
ALWAYS CHECK RETRUN FROM FUNCTION
if(!m_FDRecordset->Open(AFX_DB_USE_DEFAULT_TYPE,
"SELECT * FROM [Log] WHERE [Callsign] LIKE '%" + text + "%' ",
CRecordset::readOnly ))
{
// ... handle error here
};
ALWAYS CHECK FOR EOF
if(m_FDRecordset->IsEOF())
{
// NO DATA FOUND
};
<div class="ForumMod">modified on Wednesday, May 21, 2008 12:27 PM</div>
|
|
|
|
|
Since it's tracing the SQL I would assume it's the statement after the TRACE which is causing the exception. You probably need to check IsBOF and IsEOF to ensure that some rows were returned before trying to reference any fields.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
Thanks - I guess I was "half a bubble off" looking at the wrong statement and assuming it was the Open recordset failing.
I guess I should have checked the return from Open!
Thanks again.
Vaclav
|
|
|
|
|
Find Top 5 Records without using the Top command..????
|
|
|
|
|
set rowcount 5
select xyz
from table
order by ....
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi,
I am using sql bulk copy to insert into table from txt file. The following code is is being used for row terminator but it does not work. it works with '\n'. But it should be work with Unix system as well.
@BQUERY AS nVARCHAR(250)
SET @BQUERY = 'BULK INSERT tblCustomer
FROM ''' + @FILEPATH + '''
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '','',
ROWTERMINATOR = ''\x0A''
)'
EXECUTE SP_EXECUTESQL @BQUERY
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Unix row terminators are not the same as windows. Windows uses \r\n, unix only uses \r
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have tested this too, still not working.
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
Try doing a hex dump of your data and see exactly what character(s) delimit the lines. You can use multiple characters in your format file as record delimiters, so you should be OK.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I have read the file using StreamReader it has given some info about file. File is encoded with UTF-8 with code page 65001. It shows \n as line terminator which is not work with SQL bulk Insert.
Shahzad Aslam
Software Engineer
Email: shehzadaslam@hotmail.com
|
|
|
|
|
I guess you tried
ROWTERMINATOR = ''\x0D''
(hex for \n)
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Hi Friends,
I am updating and inserting excel datas from sql stored procedure
alter PROCEDURE SP_ExcelMan @File_Name as varchar(50) = ''
AS
BEGIN
SET NOCOUNT ON
DECLARE @Cmd varchar(1000)
DECLARE @fn varchar(500)
DECLARE @provider varchar(100)
DECLARE @ExcelString varchar(100)
-- New File Name to be created
IF @File_Name = ''
Select @fn = 'D:\Test.xls'
ELSE
Select @fn = 'D:\' + @File_Name + '.xls'
-- FileCopy command string formation
SELECT @Cmd = 'Copy D:\Template.xls ' + @fn
-- FielCopy command execution through Shell Command
EXEC MASTER..XP_CMDSHELL @cmd, NO_OUTPUT
-- Mentioning the OLEDB Rpovider and excel destination filename
set @provider = 'Provider=Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;HDR=Yes;Database=' + @fn
-- Executing the OPENROWSET Command for copying the select contents to Excel sheet.
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT FirstName,LastName,Phone,Address,City,State,Zip FROM [Sheet1$]'')
select au_fname as FirstName,au_lname as LastName,phone,address,city,State,Zip from authors')
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT StoreId, OrderNo, OrderDate, Quantity FROM [Sheet2$]'')
select stor_id as StoreId,Ord_Num as OrderNo,Ord_Date as OrderDate,qty as Quantity from sales')
SET NOCOUNT OFF
END
But i am getting the error
Could not locate registry entry for OLE DB provider 'Provider=Microsoft.Jet.OLEDB.4.0'.
OLE DB error trace [Non-interface error: Provider not registered.].
Please help me to clear the error
Thanks in Advance
|
|
|
|
|
in one of my sp that returns refference cursors with some data this sp i want to call in another sp through execute immediate then how can i get the value or out put of the sp in this queery...how to assing the variable in the execute immediate uery
|
|
|
|
|
hi,
i m using this store procedure in my project
ALTER procedure getkeyname(@city sysname,@key varchar(50))
as
exec
('select distinct a.keywordname,a.keywordid from keyword As a INNER
JOIN ' + @city+ ' As b on a.keywordid=b.keywordid and
a.keywordname like ' + @key)
but i got an error like..
invalid column name that i entered in @key parameter
i want to pass my table name dynamically and parameter name but it accept only @city as a table name parameter and it treat @key as Colunm name like @city but i want to pass @key as parameter.
please help me
no knowledge in .net
|
|
|
|
|
Dyamic SQL - Something you have to be very careful in constructing because it is so easy to make a mistake and punch a big massive security hole in your database.
The way you have constructed the SQL make SQL Server interpret @key as a column name. You have to pass @key as a parameter. Actually, you should look up sp_executesql as it provides more protection against attack.
Now, as a further defence against attack you must check that @city relates to a real table first. You can do this by checking against INFORMATION_SCHEMA.TABLES like this:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @city)
BEGIN
-- The table exists, we can run the dynamic SQL
END
ELSE
BEGIN
-- The table does not exist. Perform error processing.
END
|
|
|
|
|
@key must be in quotes.
ALTER procedure getkeyname(@city sysname,@key varchar(50))
as
exec
('select distinct a.keywordname,a.keywordid from keyword As a INNER
JOIN ' + @city + ' As b on a.keywordid=b.keywordid and
a.keywordname like ''' + @key + '''')
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
