Sql Stop Logging: time

Showing posts with label time. Show all posts

Friday, March 30, 2012

How to trim off the time part of a DateTime field

I have a tabls of SQL 2000, about the data of student.
The data is migrate from another databse,
There is a field called birthday, it stores the data with the formate like:
1994/11/03 AM 02:09:00
When I bind the field to datagrid, how can I suppress the time part , or
how can I trim the time part form database?Hi,
You can try as
select convert(varchar(10), getdate(), 102)
thanks and regards
Chandra
"ad" wrote:
> I have a tabls of SQL 2000, about the data of student.
> The data is migrate from another databse,
> There is a field called birthday, it stores the data with the formate like:
> 1994/11/03 AM 02:09:00
> When I bind the field to datagrid, how can I suppress the time part , or
> how can I trim the time part form database?
>
>|||You can also check for available types o
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
thanks and regards
Chandra
"Chandra" wrote:
> Hi,
> You can try as
> select convert(varchar(10), getdate(), 102)
> thanks and regards
> Chandra
>
> "ad" wrote:
> >
> > I have a tabls of SQL 2000, about the data of student.
> > The data is migrate from another databse,
> > There is a field called birthday, it stores the data with the formate like:
> > 1994/11/03 AM 02:09:00
> >
> > When I bind the field to datagrid, how can I suppress the time part , or
> > how can I trim the time part form database?
> >
> >
> >|||I have study the article you mentioned, but I still cann't figure out how to
do.
If the field name is Birthday, could you give me a exmaple?
"Chandra" <Chandra@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
:BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
> You can also check for available types on
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
> thanks and regards
> Chandra
> "Chandra" wrote:
> > Hi,
> > You can try as
> > select convert(varchar(10), getdate(), 102)
> >
> > thanks and regards
> > Chandra
> >
> >
> > "ad" wrote:
> >
> > >
> > > I have a tabls of SQL 2000, about the data of student.
> > > The data is migrate from another databse,
> > > There is a field called birthday, it stores the data with the formate
like:
> > > 1994/11/03 AM 02:09:00
> > >
> > > When I bind the field to datagrid, how can I suppress the time part ,
or
> > > how can I trim the time part form database?
> > >
> > >
> > >|||E.g.
SELECT convert(varchar(10), birthday, 102) FROM YourTableName
Regards
Steen
ad wrote:
> I have study the article you mentioned, but I still cann't figure out
> how to do.
> If the field name is Birthday, could you give me a exmaple?
>
> "Chandra" <Chandra@.discussions.microsoft.com> ¼¶¼g©ó¶l¥ó·s»D
>> BD3DB546-9F3D-491B-8596-6F2E0ABCA705@.microsoft.com...
>> You can also check for available types on
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_2f3o.asp
>> thanks and regards
>> Chandra
>> "Chandra" wrote:
>> Hi,
>> You can try as
>> select convert(varchar(10), getdate(), 102)
>> thanks and regards
>> Chandra
>>
>> "ad" wrote:
>>
>> I have a tabls of SQL 2000, about the data of student.
>> The data is migrate from another databse,
>> There is a field called birthday, it stores the data with the
>> formate like: 1994/11/03 AM 02:09:00
>> When I bind the field to datagrid, how can I suppress the time
>> part , or how can I trim the time part form database?

Monday, March 26, 2012

How to track db growth over a period of time

Hello All
Can anyone tell me how I would go about tracking the database/data table
growth over a period of time
thanksHave a look at http://vyaskn.tripod.com/track_sql_...file_growth.htm
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:e3nU3Zk$EHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello All
> Can anyone tell me how I would go about tracking the database/data table
> growth over a period of time
> thanks
>|||"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:e3nU3Zk$EHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello All
> Can anyone tell me how I would go about tracking the database/data table
> growth over a period of time
> thanks
>
One source of this information is the SQL Server log files (not related to
transaction log files). Each time a backup occurs, the size in blocks (1
block = 8192 bytes) is written to the log file. These files are just text
files which are usually stored somewhere under the SQL Server installation
directory and have names like ERRORLOG.x.
Regards,
Simon

How to track db growth over a period of time

Hello All
Can anyone tell me how I would go about tracking the database/data table
growth over a period of time
thanks
Have a look at http://vyaskn.tripod.com/track_sql_d...ile_growth.htm
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:e3nU3Zk$EHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello All
> Can anyone tell me how I would go about tracking the database/data table
> growth over a period of time
> thanks
>
|||"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:e3nU3Zk$EHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello All
> Can anyone tell me how I would go about tracking the database/data table
> growth over a period of time
> thanks
>
One source of this information is the SQL Server log files (not related to
transaction log files). Each time a backup occurs, the size in blocks (1
block = 8192 bytes) is written to the log file. These files are just text
files which are usually stored somewhere under the SQL Server installation
directory and have names like ERRORLOG.x.
Regards,
Simon

How to track db growth over a period of time

Hello All
Can anyone tell me how I would go about tracking the database/data table
growth over a period of time
thanksHave a look at http://vyaskn.tripod.com/track_sql_database_file_growth.htm
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:e3nU3Zk$EHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello All
> Can anyone tell me how I would go about tracking the database/data table
> growth over a period of time
> thanks
>|||"Rahul Chatterjee" <rahul@.benesysinc.com> wrote in message
news:e3nU3Zk$EHA.3424@.TK2MSFTNGP11.phx.gbl...
> Hello All
> Can anyone tell me how I would go about tracking the database/data table
> growth over a period of time
> thanks
>
One source of this information is the SQL Server log files (not related to
transaction log files). Each time a backup occurs, the size in blocks (1
block = 8192 bytes) is written to the log file. These files are just text
files which are usually stored somewhere under the SQL Server installation
directory and have names like ERRORLOG.x.
Regards,
Simon

Monday, March 19, 2012

how to target the file name with date variable

My client will upload a file to a FTP folder regularly, but the time patten is unknown, only thing i know is he'll use today's date as the file name. for example, he uploaded a file 'wk070705_id.txt' today, but i don't know when the next file will be uploaded. so how can i write a package like, eveytime when he upload a new file (for example if he upload a file 'wk070708_id.txt'), i can import the data into my table? i am a very newbie of SSIS, it would be perfect if i can get a full script for this. thanks in advance with appreciation!

ps: the date in file name is yymmdd

One way i can think of is to make use of gateway service to poll that directory and as soon as file arrives, parse the file name and load the corresponding package.|||

If you have normal file system access to the folder then you can use this task -http://www.sqlis.com/23.aspx

If you only have FTP access then polling is the way to go. You can request the file, but nee dto make the parameter dynamic based on todays date, for which you can use a property expression. Have a look in Books Online for the references to property expressions in SSIS they are what make it flexible and dynamic.

Some example date expressions - http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html

How to take off the time part of the parameter

Hello,

How can I display data that only compare the date part (ignoring the time part) between the value of the date parameter and the database?. It displays data only if I pass the whole date (date and time)

I tried with the following query in the report services 2000, but it even didn't pass the query parse

SELECT * FROM table

WHERE (CONVERT(CHAR(8), table.PRODUCTION_DATE, 112)
= CONVERT(CHAR(8), @.parameterDate, 112))

Any ideas will be appreciated.

Thanks,

Marco

What are the data types of the column and the parameter, and what error do you get when you say it doesn't get past the parse step?|||

The message is ADO error: Line 1: Incorrect syntax near ')'

If I replace @.parameterDate with a string e.g. '' The parse is ok

I tested the same query in RS 2005 and it passes the parse

Both the column and the parameter are datetime data types

Thanks

|||

Just in case someone has the same problem:

I realized that the workaround to this is to change the sql expression entering two dates as parameters, fromDate and toDate and using the where clause as follows:

where databaseDate => @.fromDate and databaseDate <= @.toDate

regards

Marco

Friday, March 9, 2012

How to sum the time and place at the last registry

Hi,

I have the following problem... In my table I store calling registries...and when a call is transfered to another branch the last brach have to receive all the time...

Só I have the following table

Code Snippet

CREATE TABLE [BILHETES_PROCESSAR] (
[COD_CLIENT] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[DATE_TIME] [datetime] NOT NULL ,
[NRTELEFONE] [varchar] (255) COLLATE Latin1_General_CI_AS NOT NULL ,
[BRANCH] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[TIPO_ORIGINAL] [varchar] (255) COLLATE Latin1_General_CI_AS NULL ,-- Tells if is a originate call "2" or received a tranfer "6"
[TRONCO] [varchar] (255) COLLATE Latin1_General_CI_AS NULL , -- The port of the router through the call have been made
[TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that is going to receive this call
[ORIGEM_TRANSF] [varchar] (50) COLLATE Latin1_General_CI_AS NULL ,--The branch that sent the call

[VALOR_TEMPO] [money] NULL ,
[ID_TRANSF] [uniqueidentifier] NULL ,--ID for the branch that originated the call
[ID_REGISTRO] [uniqueidentifier] NOT NULL --ID of the registry
) ON [PRIMARY]
GO

For this table I can have the following data

Code Snippet

1002 2007-07-30 17:52:34.000 01191084426 236 2 2 9 210 NULL 15.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4
1002 2007-07-30 17:53:17.000 01191084426 210 2 6 9 214 236 63.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 D680C38D-C217-461A-8207-9FC367FBB51C
1002 2007-07-30 17:54:30.000 01191084426 214 2 6 9 NULL 210 50.0 C1AF0190-B1EB-4DEB-993A-D64D6C9F7CD4 24B3073B-F45F-4E87-9796-55F37847473D

So I got this results running this query..

Code Snippet

UPDATE P SET P.TRANSF=P2.BRANCH,
P.ID_TRANSF=
CASE
WHEN P.TIPO_ORIGINAL = '2' THEN P.ID_REGISTRO
WHEN P2.ID_TRANSF IS NOT NULL THEN P2.ID_TRANSF
ELSE '1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'
END
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P.TRANSF IS NULL

UPDATE P2 SET P2.ORIGEM_TRANSF=P.BRANCH, P2.ID_TRANSF=
CASE
WHEN P.ID_TRANSF IS NOT NULL THEN P.ID_TRANSF
ELSE P.ID_REGISTRO
END
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (1, 5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (2, 4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P2.ORIGEM_TRANSF IS NULL

UPDATE P2 SET P2.ID_TRANSF=P.ID_TRANSF
FROM BILHETES_PROCESSAR P INNER JOIN BILHETES_PROCESSAR P2
ON
P.COD_CLIENTE=P2.COD_CLIENTE AND
P.NRTELEFONE=P2.NRTELEFONE AND
P.TRONCO=P2.TRONCO
WHERE
P2.DATE_TIME >= P.DATE_TIME
AND (
(P.TIPO_ORIGINAL IN (5, 7) AND P2.TIPO_ORIGINAL IN (5, 7)) OR
(P.TIPO_ORIGINAL IN (4, 6, 8) AND P2.TIPO_ORIGINAL IN (4, 6, 8))
)
AND P2.BRANCH <> P.BRANCH
AND P2.ID_TRANSF='1C4CCB4E-9229-4A6C-A6A4-B881A01EEACF'

This query has only one problem if the user has made a 4o transfer th2 two last registry will have wrong "transf" and "origem_transf" values... but this is not my big problem...

The big problem is that I need to sum all it "valor_tempo" put it on the last registry... that is the one with the code "24B3073B-F45F-4E87-9796-55F37847473D" and then set the "valor_tempo" to 0 to the other registry...

Did you get the ideia ?

Thanks

Hi,

I solved the problem ... But it's taking tooooooooooo much time...like 7 hours to run on all registries...is there a faster way to do that ?

Code Snippet

DECLARE @.REGS_TRANSF as UNIQUEIDENTIFIER
DECLARE @.REG_ATUALIZAR as UNIQUEIDENTIFIER
DECLARE @.VALOR_TEMPO_TOTAL AS MONEY
DECLARE CUR_REGTRANSF CURSOR FOR SELECT ID_TRANSF FROM BILHETES_PROCESSAR WHERE ID_TRANSF IS NOT NULL GROUP BY ID_TRANSF

OPEN CUR_REGTRANSF

FETCH NEXT FROM CUR_REGTRANSF INTO @.REGS_TRANSF
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.REG_ATUALIZAR=(SELECT ID_REGISTRO FROM BILHETES_PROCESSAR WHERE ID_TRANSF=@.REGS_TRANSF AND TRANSFERIDO IS NULL)
SET @.VALOR_TEMPO_TOTAL=(SELECT SUM(VALOR_TEMPO) FROM BILHETES_PROCESSAR WHERE ID_REGISTRO <> @.REG_ATUALIZAR AND ID_TRANSF=@.REGS_TRANSF)
UPDATE BILHETES_PROCESSAR SET VALOR_TEMPO=VALOR_TEMPO+@.VALOR_TEMPO_TOTAL WHERE ID_REGISTRO=@.REG_ATUALIZAR
UPDATE BILHETES_PROCESSAR SET VALOR_TEMPO=0 WHERE ID_REGISTRO <> @.REG_ATUALIZAR
FETCH NEXT FROM CUR_REGTRANSF INTO @.REGS_TRANSF
END

CLOSE CUR_REGTRANSF
DEALLOCATE CUR_REGTRANSF

Here is the code that I used....some fields has diff names...cause we changed...

Wednesday, March 7, 2012

How to store/display more than one tables/recordset

see the follow code:
can return more than one recordset/table
the datagrid can show more than one table at a time.
1.the problem is how to store more than one table in a dataset at a time.
2.see sql(2),sql(3), the new table 't1' is empty, after exec sql(3) will meet a "System.Data.OleDb.OleDbException",the sql(2) can pass without return recordset, but sql(3) return a null recordset(maybe). What should I do.
thanks.

Public Function m_execsql()
Dim connStr As String
connStr = "User ID=sa;Password=sa;Initial Catalog=Northwind;Provider=""SQLOLEDB.1"";Data Source=MINGLEI"
conn = New System.Data.OleDb.OleDbConnection(connStr)
conn.Open()
Dim sql(3) As String
sql(0) = "select * from products;"
sql(1) = "select * from orders;"
sql(2) = "create table t1;"
sql(3) = "seletc * from t1;"
Dim i As Integer
For i = 0 To sql.GetUpperBound(0) - 1
Try
da = New OleDb.OleDbDataAdapter(sql(i), conn)
Catch e As Exception
MessageBox.Show(e.Message)
End Try
'Try
ds = New DataSet("dsTable")
dt = New DataTable
da.Fill(ds)
'grd.SetDataBinding(ds, i.ToString)
grd.DataSource = dt
'Catch eds As Exception
' MessageBox.Show(eds.Message)
'End T
Next
conn.Close()
conn = Nothing
End Functioniqueen, you can use multiple data adapters to fill one dataset or if your sql statement returned more than one result set.

Here's more from MS KB:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconpopulatingdatasetfromdataadapter.asp

You may want post in .Net section too...

How to store very large document data?

Dear all,

I am facing a problem while storing Very large document data to a single image field in SQL Server 2000. It takes very much time for documents in size upto 10 MB. And its not possible to store data more than 10 MB. If I try it gives "Connection Write (Wrapper Write())" message with state 01000 and native 4.

I have used SQLPutData and SQLParamData for this purpose.

Is there any efficient way to do this? My concern is for less time.

Thanks in advance,
AvneeshI need urgent help regarding above problem. Would somebody please send some suggestions ASAP.

Thanks in advnace,
Avneesh

How to store OR get Time value in a column

How can i store the time value in a column. DateTime stores dd/mm/yyy hh:mm:ss , i want only hh:mm:ss , and i also may compare the the values. And also how can i get current time to a column value, something like getdate() ?

MasterG wrote:
How can i store the time value in a column. DateTime stores dd/mm/yyy hh:mm:ss , i want only hh:mm:ss , and i also may compare the the values. And also how can i get current time to a column value, something like getdate() ?

Hi MasterG,

You can use the following T-SQL to get hh:mm:ss from a datetime value;

CONVERT(CHAR(8), GETDATE(), 108)

To insert the time into a column, you could use;

INSERT
MyTable (TheTime)
VALUES
CONVERT(CHAR(8), GETDATE(), 108)

You may want to check out Books Online for further information, I also found this article which should be really interesting to you; http://sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk
|||

Thanks for great solution. This one is very helpfull

Happy Coding...

How to store only date in MS SQL Server 2000

hi..

i am taking date and time from user as input in different screen.

some times i need only date to be stored and some time only time.

as we have data type datetime in MS SQL Server 2000, it takes both date and time in one field.

so how to take only date or only time from user and store in database.

You should split it into 2 fields... one for date and one for time. If they are only providing a time and not a date sometimes then it should not be split. Don't know why youd wanna do this though|||

You can give a datetime field a date, but it will assume you mean midnight on that date.

You can give a datetime field a time, but it will assume you mean 1/1/1900 at that time.

Of course, you can give it both as well.

If you need to know whether a datetime that is exactly midnight is both a date and a time, or just a date, you'll need to either use another field to flag it as such, or know by the field it is in that it always is supposed to be "date-only", or you can store it in a different datatype (varchar).

Same for time.

Friday, February 24, 2012

how to stop the query

Hi All,
I am executing one query in sql analyzer. It was taking too much of time.I am not able to stop the query using stop button,it was not responding.
Many users are working with that database at the same time,not possible to stop services(agent or server) also.

Any help from you.

Regards
Bharat

Quote:

Originally Posted by bharadwaj

Any help from you.

Regards
Bharat

You could try to disconnect... FILE >> Disconnect

Sunday, February 19, 2012

How to Stop Logging

Hi,

I am doing some resource hungry tasks (some extraction and loading
through DTS), for which each time the SQL Server Log files gets filled
up!

Is there any way to stop the logging (like as during restore)?

Thanks in advance.

-surajits_laha@.rediffmail.com (Surajit Laha) wrote in message news:<ba8ee108.0401290212.6defeb00@.posting.google.com>...
> Hi,
> I am doing some resource hungry tasks (some extraction and loading
> through DTS), for which each time the SQL Server Log files gets filled
> up!
> Is there any way to stop the logging (like as during restore)?
> Thanks in advance.
> -surajit

You can't disable logging, but you could set the database recovery
model to simple during the load. You might also be able to load in
batches, instead of in a single transaction, which should require less
log space. The DTS Transform Data Task allows you to specify a batch
size, for example.

Simon

Friday, March 30, 2012

How to trim off the time part of a DateTime field

How to trim off the time part of a DateTime field

How to trim off the time part of a DateTime field

Monday, March 26, 2012

How to track db growth over a period of time

How to track db growth over a period of time

How to track db growth over a period of time

Monday, March 19, 2012

how to target the file name with date variable

How to take off the time part of the parameter

Friday, March 9, 2012

How to sum the time and place at the last registry

Wednesday, March 7, 2012

How to store/display more than one tables/recordset

How to store very large document data?

How to store OR get Time value in a column

How to store only date in MS SQL Server 2000

Friday, February 24, 2012

how to stop the query

Sunday, February 19, 2012

How to Stop Logging

Sql Stop Logging

Blog Archive

About Me