I have got data like this below in effective date column
2008-08-05 19:18:13.000
2008-08-05 19:17:10.000
Expected output:
2008-08-05 19:17:10
I need to truncate the milliseconds and insert the same into datetime column.
I tried as below
select convert(datetime,substring(convert(varchar,getdate(),20),1,20))
Thanks in advance
With a database size of almost 2 GB, I run the 'truncate table eventlog command' which completes successfully, but the database size only decreases by about 10 MB so stays too large - indeed the number of rows in the eventlog table is minimal, but the otehr tables in this database don't show such an amount of tables large enough to cause the size issue either. What could be the reason and how can I reduce it (possibly truncating another table but then which one, how could I determine which is too large and needs truncating?).
View 3 Replies View RelatedHi
Does anybody know while loading data from text file into sql server, how
can we ignore milliseconds.
regards
JK
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
Hi,
i am trying to add milliseconds to a time. For example if i have a time of 01:01:05:000 and i want to add 0.297 milliseconds to it i use the following simplified query
Code:
SELECT CONVERT(nvarchar(20), DATEADD(ms, 0.297, '00:01:05:000'), 14) AS Expr1
However instead of getting 01:01:05:0.297 i get 01:01:05:000. Can somebody please tell me what i am doing wrong.
Thanks in advance.
why the results I receiving for Seconds is different? I get the same MS results.
Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64)
Jun 28 2012 08:36:30
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
--Returns 1 second
SELECT RunTime_SEC = DATEDIFF(SECOND, '2015-02-09 13:34:13.977', '2015-02-09 13:34:14.230')
,RunTime_MS = DATEDIFF(ms, '2015-02-09 13:34:13.977', '2015-02-09 13:34:14.230')
--Returns 0 second
SELECT RunTime_SEC = DATEDIFF(SECOND, '2015-02-09 13:30:30.147', '2015-02-09 13:30:30.400')
,RunTime_MS = DATEDIFF(ms, '2015-02-09 13:30:30.147', '2015-02-09 13:30:30.400')
hi ppls..
we have sql server 2000 EM. we received daily xml files and we insert into our database.there is one column Date_T having data type datetime.till date we recieved this records from xml as '03/23/2004 12:23:34:956' but due to some duplicate isssue we now want to modified this column to recieve as milliseconds like '03/23/2004 12:23:34:956232' now my point is wheather sql server handle this kind of milliseconds..please help me out as early as possible..
T.I.A
Papillon
Hi!
I have the difference between two dates in milliseconds. I want to convert this difference to the format hh:mm:ss.mmm, without the date. So, if the difference is bigger then one day, I would like to show it like this, for example: 36:25:14.047
How can I do this?
Thank you!
I've been working on a project to ensure that accross our entire data warehouse everything is at the same accuracy level as far as time - migrating everything to use the full hh:mis.mmm. Some places were using hh:mis:mmm (colon instead of decimal point) and many places not using milliseconds.
The SQL server portion went essentially without issue - however SSIS is not cooperating. For example I have data I am importing from a file that is in format: hh:mis (no milliseconds) that I need to compare to data from SQL (now containing full milliseconds) - matching on time ranges. Previously this was done by converting both to the "database time" datatype in SSIS and comparing. Now when converting the cTime to database time SSIS fails and complains "The value could not be converted because of a potential loss of data".
I don't want to lop off the milliseconds becuase that could create rounding errors.
I don't like it but the only option I can think of is keeping everything in string and comparing the strings... then I run into the issue of making sure to convert all sources of data into the exact same hh:mis:mmm format in text.
Is there an easier way? I know some people use "milliseconds since midnight" and so forth but that would require major reworking of the data warehouse and many packages.
Hi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but it€™s €œ000€?.
Hi,
I tried entering this value "8/24/2006 1:35:00.127 PM" with 127 as the milliseconds in a datetime field, but encountered error saying inconsistent datatype ...
Anyone knows how to store datetime value with milliseconds in the SQL database?
Thanks
Hello all.I am attempting to insert a row into a table with a datetime column:When the insert statement contains a value for the millisecond portionof the data time column: ie. {ts '2003-11-05 12:02:43:2960'}I get 'Syntax error converting datetime from string'When I insert a value like: {ts '2003-11-05 12:02:43'}with no millisecond value it succeeds.Any help would be appreciated.Thanks
View 5 Replies View RelatedI'm trying to save a datetime value from vb.net to a sql server.I'm using this code: Dim dt As DateTime = DateTime.Now.ToString("dd/MM/yyyy HH:mm:ss")
After I save this value to Database.But on sql server management studio , I see that the field's value still has the milliseconds .
why does select @@lock_timeout return -1. Shouldn't this return lock timeout in milliseconds?
View 2 Replies View RelatedI want to execute a paramterized ADO insert command into a SQL Server DATETIME column without losing the milliseconds. I can accomplish this without parameters, but that isn't what I want. Any suggestions?
It is OK that DATETIME only has a resolution of 3.33 milliseconds.
See the attached code:
Code Snippet
#include <string>
#include <iostream>
#import "C:Program FilesCommon FilesSystemadomsado15.dll" rename( "EOF", "ADOEOF" )
int main( int argc, char* argv[] )
{
::CoInitialize( NULL );
try
{
ADODB::_ConnectionPtr connection;
connection.CreateInstance(__uuidof( ADODB::Connection ) );
std::string connectionString;
connectionString.append( "Provider=SQLOLEDB;" );
connectionString.append( "Data Source=HPSERV1;" ); // Choose your server/instance.
connectionString.append( "Initial Catalog=tempdb;" );
connectionString.append( "Integrated Security=SSPI;" );
connection->ConnectionTimeout = 10;
connection->Open(
_bstr_t( connectionString.c_str() ),
_bstr_t( "" ),
_bstr_t( "" ),
ADODB::adOpenUnspecified );
std::string sqlStatement;
sqlStatement = "DROP TABLE TestTable1";
try
{
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
}
catch( const _com_error& )
{
// Ignore errors as table probably doesn't exist.
}
sqlStatement = "CREATE TABLE TestTable1 ( ColInt INT NOT NULL PRIMARY KEY, ColDate DATETIME )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
// Works (datetime resolution is 3.33 milliseconds so rounds to .347)
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 1, '2007-12-28 20:05:16.345' )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
// Works (NULL is inserted)
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 2, NULL )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
// Works (datetime resolution is 3.33 milliseconds so rounds to .347)
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( 3, CONVERT( DATETIME, '2007-12-28 20:05:16.345' ) )";
connection->Execute( _bstr_t( sqlStatement.c_str() ), NULL, ADODB::adExecuteNoRecords );
// ====================================================================
unsigned int colInt = 3;
sqlStatement = "INSERT INTO TestTable1 ( ColInt, ColDate ) VALUES ( ?, ? )";
ADODB::_CommandPtr command;
// ====================================================================
// Fails (Operand type clash: ntext is incompatible with datetime)
colInt++;
command.CreateInstance( __uuidof( ADODB::Command ) );
command->ActiveConnection = connection;
command->CommandType = ADODB::adCmdText;
command->CommandText = _bstr_t( sqlStatement.c_str() );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adInteger,
ADODB::adParamInput,
4,
_variant_t( colInt ) ) );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adBSTR,
ADODB::adParamInput,
-1,
_bstr_t( "2005-10-25 09:10:11.012" ) ) );
try
{
command->Execute( NULL, NULL, ADODB::adCmdText );
}
catch( const _com_error& e )
{
std::cout << "Error at colInt=" << colInt << " (ADODB::adBSTR and _bstr_t):"
<< " HRESULT = " << e.Error() << ": " << e.Description() << ""
<< " SQL statement: " << command->CommandText << std::endl;
}
// ====================================================================
// Fails (Operand type clash: text is incompatible with datetime)
colInt++;
command.CreateInstance( __uuidof( ADODB::Command ) );
command->ActiveConnection = connection;
command->CommandType = ADODB::adCmdText;
command->CommandText = _bstr_t( sqlStatement.c_str() );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adInteger,
ADODB::adParamInput,
4,
_variant_t( colInt ) ) );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adChar,
ADODB::adParamInput,
-1,
_bstr_t( "2005-10-25 09:10:11.012" ) ) );
try
{
command->Execute( NULL, NULL, ADODB::adCmdText );
}
catch( const _com_error& e )
{
std::cout << "Error at colInt=" << colInt << " (ADODB::adChar and _bstr_t):"
<< " HRESULT = " << e.Error() << ": " << e.Description() << ""
<< " SQL statement: " << command->CommandText << std::endl;
}
// ====================================================================
// Fails (A datetime is inserted to table but milliseconds are zeroed)
colInt++;
::SYSTEMTIME sysNow;
::GetSystemTime( &sysNow );
double myTime;
::SystemTimeToVariantTime( &sysNow, &myTime );
// SystemTimeToVariantTime strips milliseconds, so we'll add some more
// back in as we're testing insert of milliseconds.
double desiredMilliseconds = 456;
myTime += desiredMilliseconds / 24.0 / 3600.0 / 1000.0;
command.CreateInstance( __uuidof( ADODB::Command ) );
command->ActiveConnection = connection;
command->CommandType = ADODB::adCmdText;
command->CommandText = _bstr_t( sqlStatement.c_str() );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adInteger,
ADODB::adParamInput,
4,
_variant_t( colInt ) ) );
command->Parameters->Append( command->CreateParameter(
_bstr_t(),
ADODB::adDate,
ADODB::adParamInput,
-1,
_variant_t( myTime, VT_DATE ) ) );
try
{
command->Execute( NULL, NULL, ADODB::adCmdText );
}
catch( const _com_error& e )
{
std::cout << "Error at colInt=" << colInt << " (ADODB::adDate and _variant_t VT_DATE):"
<< " HRESULT = " << e.Error() << ": " << e.Description() << ""
<< " SQL statement: " << command->CommandText << std::endl;
}
}
catch ( _com_error& e )
{
std::cout << "Unexpected error: "
<< e.Description() << std::endl;
}
return 0;
}
I am writing a report that Queries a SQL DB using 'SQL Server Business Intelligence Development Studio'. I have a field in the DB called duration and it is in milliseconds. I am trying to find an easy way to convert the format from Milliseconds to HH:MM:SS.Nearest I can get is the following for the field:
=Int( ((Fields! DURATION.Value/1000) / 60) / 60) & ":" & Int(((((Fields!DURATION.Value/1000) / 60) / 60) - Int(((Fields!DURATION.Value/1000) / 60) / 60)) * 60)
The output is in HH:MM. One issue with this is if the MM is say :03, it prints as :3. I lose the leading 0 so 9:03 (9hrs and 3 minutes) prints as 9:3. Where as 9:30 (9 hrs and 30 minutes) prints as 9:30 as it should.
I The requirement is to unload all columns data into csv file using bcp with pipe delimiter, but the condition is to remove milliseconds part of a datetime column.
Ex:
2014-02-19 17:12:14.967 remove .967 from data while unloading into csv.
Hi all...
Please forgive the elementary nature of my question, but could someone please explain the differences between these two database backup types:
1. Log backup
2. Log backup no truncate
From what I understand and have read, the "no truncate" backup method keeps the entire transaction log indefinitely. Using the truncation method, the transaction log is either 1) compressed or 2) cleaned up so that any completed transactions are removed from the log. Which one of these is true?
And, for the big question: is it better to run a backup of the transaction log with truncation or not? Our current backup scheme is similar to the following:
Full backup every 24 hours
transaction log backup every hour with no truncation
Should we insert a truncation backup somewhere in here? What is the danger of removing (or compressing) parts of the transaction log? Will this affect the restore process?
Thanks in advance!
When you view the Extended Events "Watch Live Data" is the duration in milliseconds or microseconds? I'm assuming it's milliseconds, but if you look at the timestamp difference from start to complete it doesn't add up to the duration amount? It looks like it's just the difference between the timestamps?
View 5 Replies View RelatedI'm running into a constant issue of SQL Server modifying themillisecond part of a timestamp insert from another application. Theapplication inserts timestamp which includes a millisecond portion as astring (varchar). But when an SQL Server moves this data to anothertable (for reporting), the string is inserted in a datetime field, themillisecond field invariably changes by 1-2 milliseconds for everysingle data point inserted. Given the time critical nature of this data(to a millisecond), its almost impossible to avoid this other than toleave the data as string type. But this drives the analytical reportingfolks wild as report queries based on time criteria are getting messedup. Any ideas how to force SQL Server not to mess around with themillisecond value? Does this problem exist with SQL Server 2005 as well?
View 2 Replies View RelatedHi, I am trying to derive a column from:
mm/dd/yyyy hh:mms.fff to dt_dbtimestamp as:
(dt_dbtimestamp)(colum_name,1, 23) when I include the period and three digits for milliseconds the package fails if I leave it out it is successfull. I need to include milliseconds for my datawarehouse project. I tried many different ways and always with failure
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,23) fails
01/23/2007 12:23:15.234 is the date(example) derived: (dt_dbtimestamp)(column_name,1,19) succeeds
Thanks
Start End Duration
11/20/2007 12:05:57 PM
11/20/2007 12:08:06 PM
00:02:09.6570000
Duration =(Fields!end.Value-Fields!startdate.value) i get the output as shown above.
I need to round that output to 2 decimal places and display it as 00:02:09.65
Any ideas?
I need to set a variable to datetime and time to exact milliseconds in SQL server in stored procedure.
Example:
set MyUniqueNumber = 20071101190708733
ie. MyUniqueNumber contains yyyymmddhhminsecms
Please help, i tried the following:
1. SELECT CURRENT_TIMESTAMP; ////// shows up with - & : , I want single string as in above example.2.
select cast(datepart(YYYY,getdate()) as varchar(4))+cast(datepart(mm,getdate()) as char(2))+convert(varchar(2),datepart(dd,getdate()),101 )+cast(datepart(hh,getdate()) as char(2))+cast(datepart(mi,getdate()) as char(2))+cast(datepart(ss,getdate()) as char(2))+cast(datepart(ms,getdate()) as char(4))
This one doesnot display day correctly, it should show 01 but shows 1
DECLARE @datetimeoffset datetimeoffset(3)
DECLARE @datetime datetime
SELECT @datetimeoffset = '2012-11-08T17:22:13.575+00:00'
SELECT @datetime = @datetimeoffset
SELECT @datetimeoffset AS '@datetimeoffset ', @datetime AS 'datetime'
__________________________________________________ ___________
Result of above SQL is
@datetimeoffset datetime
2012-11-08 17:22:13.575 +00:002012-11-08 17:22:13.577
__________________________________________________ ____________
The result should be '2012-11-08 17:22:13.575', why the milliseconds value is incorrect
Hi,
I am trying to access a date column up to millisecond precession. So I cast date to as follows:
Code BlockCONVERT(varchar(23),CREATE_DATE,121)
I get millisecond part as a result of query but it€™s €œ000€?.
When I try to test the format by using getDate instead of DateTime column I get right milliseconds.
CONVERT(varchar(23),GetDate(),121) --Gives right milliseconds in return
i read that i can Truncate a table even if child table has no records so i tries to disable constraints but still can't get it to workCannot truncate table 'InventoryPC' because it is being referenced by a FOREIGN KEY constraint. disabling code on lines 9-13 and enabling codes on 36-40 1 ALTER PROCEDURE dbo.RevertDB
2
3 /* Reverts Database to original "Clean" State */
4 AS
5 SET NOCOUNT OFF
6 DECLARE @Log AS varchar(MAX), @RowsInDB AS int
7 SET @Log = 'RevertDB Started at ' + CAST(GETDATE() AS varchar(50)) + ''
8
9 /* *** Disable Constraints *** */
10 ALTER TABLE Booking NOCHECK CONSTRAINT ALL
11 ALTER TABLE InventoryPC NOCHECK CONSTRAINT ALL
12 ALTER TABLE PC NOCHECK CONSTRAINT ALL
13 ALTER TABLE Platform NOCHECK CONSTRAINT ALL
14
15 /* *** Start Truncates *** */
16 TRUNCATE TABLE Booking
17 SET @Log = @Log + 'Trucate Table Booking - Done' + ''
18 SET @RowsInDB = (SELECT COUNT(BookingID) FROM Booking)
19 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
20
21 TRUNCATE TABLE InventoryPC
22 SET @Log = @Log + 'Trucate Table InventoryPC - Done' + ''
23 SET @RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)
24 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
25
26 TRUNCATE TABLE PC
27 SET @Log = @Log + 'Trucate Table PC - Done' + ''
28 SET @RowsInDB = (SELECT COUNT(PCID) FROM PC)
29 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
30
31 TRUNCATE TABLE Platform
32 SET @Log = @Log + 'Trucate Table Platform - Done' + ''
33 SET @RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)
34 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + ''
35
36 /* *** Enable Constraints *** */
37 ALTER TABLE Booking WITH CHECK CHECK CONSTRAINT ALL
38 ALTER TABLE InventoryPC WITH CHECK CHECK CONSTRAINT ALL
39 ALTER TABLE PC WITH CHECK CHECK CONSTRAINT ALL
40 ALTER TABLE Platform WITH CHECK CHECK CONSTRAINT ALL
41
42 SET @Log = @Log + '*** End Truncates ***' + ''
43 /* *** End Truncates *** */
44
45 /* *** Start Insert Platform *** */
46 SET @Log = @Log + 'Start Insert Platform' + ''
47
48 EXEC dbo.InsertPlatform 'Windows XP SP2 Professional Edition', 'Some description for Windows XP SP2 Professional Edition over here …'
49 EXEC dbo.InsertPlatform 'Windows Vista Ultimate', 'See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance.Website: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'
50 EXEC dbo.InsertPlatform 'Apple Mac OS X Tiger', 'Some description for Apple Mac OS X Tiger over here …'
51 EXEC dbo.InsertPlatform 'Apple Mac OS X Leopard', 'Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace.Finder: Browse your files like you browse your music with Cover Flow.Time Machine: See how your system looked on any given day and restore files with aWebsite: http://www.apple.com/macosx/leopard/features/'
52 EXEC dbo.InsertPlatform 'Red Hat Linux', 'Some description for Red Hat Linux over here …'
53
54 SET @Log = @Log + 'Rows In Platform: ' + (SELECT COUNT(PlatformID) FROM Platform) + ''
55 /* *** Start Insert PC *** */
56 SET @Log = @Log + 'Start Insert PC' + ''
57
58 DECLARE @WinXP int, @WinVista int, @OSXTiger int, @OSXLeopard int, @RedHat int
59 SET @WinXP = (SELECT PlatformID FROM Platform WHERE Title = 'Windows XP SP2 Professional Edition')
60 SET @WinVista = (SELECT PlatformID FROM Platform WHERE Title = 'Windows Vista Ultimate')
61 SET @OSXTiger = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Tiger')
62 SET @OSXLeopard = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Leopard')
63 SET @RedHat = (SELECT PlatformID FROM Platform WHERE Title = 'Red Hat Linux')
64
65 EXEC dbo.InsertPC 'Fusion PC One', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '1GB Dual Channel DDR2 667 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @WinXP
66 EXEC dbo.InsertPC 'Fusion PC Two', 'Description here ...', 'Intel Core2 Duo E6850 3 GHz 1333MHz', '2GB Dual Channel DDR2 800 SDRAM', '240GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '24" 3000:1 Wide Screen LCD', @WinVista
67 EXEC dbo.InsertPC 'Fusion PC Three', 'Description here ...', 'AMD Athlon 64 X2 Dual Core 6000+ 3 GHz', '2GB Dual Channel DDR2 667 SDRAM', '240GB SATA2 NCQ HDD', 'ATI Radeon Cross Fire 2900 256MB GDDR3', '24" 3000:1 Wide Screen LCD', @WinVista
68 EXEC dbo.InsertPC 'Fusion X1', 'Description here ...', 'Intel Core2 Extreme Q6850 3 GHz 1333MHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '30" 3000:1 Wide Screen LCD', @OSXLeopard
69 EXEC dbo.InsertPC 'Fusion X2', 'Description here ...', 'AMD Athlon 64 FX 74 3 GHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3', '30" 3000:1 Wide Screen LCD', @WinVista
70 EXEC dbo.InsertPC 'Fusion Tiger 1', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '2GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3 SLI', '22" 3000:1 Wide Screen LCD', @OSXTiger
71 EXEC dbo.InsertPC 'Fusion Linux 1', 'Description here ...', 'AMD Athlon 64 X2 6000+ 3 GHz', '1GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @RedHat
72
73 SET @Log = @Log + 'Rows In PC: ' + (SELECT COUNT(PCID) FROM PC) + ''
74
75 /* *** Start Insert Inventory *** */
76 SET @Log = @Log + 'Start Insert Inventory' + ''
77
78 DECLARE @F1 int, @F2 int, @F3 int, @FX1 int, @FX2 int, @FT1 int, @FR1 int
79 SET @F1 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC One')
80 SET @F2 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Two')
81 SET @F3 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Three')
82 SET @FX1 = (SELECT PCID FROM PC WHERE Title = 'Fusion X1')
83 SET @FX2 = (SELECT PCID FROM PC WHERE Title = 'Fusion X2')
84 SET @FT1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Tiger One')
85 SET @FR1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Linux One')
86
87 EXEC dbo.InsertInventory 10, @F1, 2.5, 'iCluster Fusion One'
88 EXEC dbo.InsertInventory 10, @F2, 2.5, 'iCluster Fusion Two'
89 EXEC dbo.InsertInventory 10, @F3, 2.5, 'iCluster Fusion Three'
90 EXEC dbo.InsertInventory 6, @FX1, 6, 'iCluster Fusion X1'
91 EXEC dbo.InsertInventory 6, @FX2, 6, 'iCluster Fusion X2'
92 EXEC dbo.InsertInventory 10, @FT1, 3, 'iCluster Fusion Tiger One'
93 EXEC dbo.InsertInventory 30, @FR1, 2, 'iCluster Fusion Linux One'
94
95 SET @Log = @Log + 'Rows In Inventory: ' + (SELECT COUNT(InventoryID) FROM InventoryPC) + ''
96
97 RETURN @Log
98
I would like to ask if I backuped the transaction log already, SQL will truncate the transaction log automatically or not. If not what should I do?
View 1 Replies View RelatedIs there an ISQL statement to truncate the transaction log??? A Stored Procedure maybe?
View 4 Replies View RelatedI have two tables in my SQL CE database. tblTempData and tblBatchData. When the user wants to move the data from 'temp' into 'batch' I would like to truncate the 'temp' table. When I do this I get the unable to parse query error.
strSQL = "TRUNCATE TABLE tblTempData"
goADOcn.Execute (strSQL)
Is there something that I am doing wrong, or is TRUNCATE not supported in SQLCE 2000?
can TRuncate statement be rolled back ?
View 5 Replies View RelatedDoes anyone know how to truncate log without grow up space used?
Thanks
Sbahri
Hi there,
I have a simple stored procedure that will truncate the log file but I now need to make this dynamic so that this proc can be run with any DB name. I know you can look up the logical filename in sysfiles but I'm not too sure how to write this procedure so that it will simply truncate the log file of the currently selected DB.
This is what I have so far which as you can see is hardcoded:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE TruncateLog AS
BACKUP LOG [FICaches] with TRUNCATE_ONLY
DBCC SHRINKFILE (FICaches_log, 50)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Any help would be greatly appreciated!
Thanks
NewToSql
Hi
I have a Log which is 15 gb. Recovery mode is simple.
What command can I use to truncate this log? Or is it best to shrink it?
Thanks