Modify data structure

I would like to change the data type from date time to timestamp and
nvarchar(14) to nvarchar(20).
Are there any impact to change a live database?
Thanks millions in advance,Hi ,
nvarchar(14) to nvarchar(20) shouldn't be a problem
But
a timestamp is not a date value. It is a binary value which is linked to
the current row. So changing that will clase a problem to your clients.
Before changing
Thisis from BOL
timestamp is a data type that exposes automatically generated binary
numbers, which are guaranteed to be unique within a database. timestamp is
used typically as a mechanism for version-stamping table rows. The storage
size is 8 bytes.
Remarks
The Transact-SQL timestamp data type is not the same as the timestamp data
type defined in the SQL-92 standard. The SQL-92 timestamp data type is
equivalent to the Transact-SQL datetime data type.
kind regards
Greg O
Need to document your databases. Use the first and still the best AGS SQL
Scribe
http://www.ag-software.com
"Souris" <Souris@.discussions.microsoft.com> wrote in message
news:70B6B3CC-1490-4C00-8232-DB0486C5D0AE@.microsoft.com...
>I would like to change the data type from date time to timestamp and
> nvarchar(14) to nvarchar(20).
> Are there any impact to change a live database?
> Thanks millions in advance,

modified date on table after alter command

Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.
There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah

modified date on table after alter command

Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modife
d
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shahsql

modified date on table after alter command

Using SQL Server 2000 - Trying to find a modified date for table after
issuing an alter table command. Sysobjects has crdate I can not find modifed
date.
Need ithe nfo for SOX to prove that no tables have been modified unless
approved.There is no buitlin facility to find table schema is altered in 2000.
you have to create your own script or use thire party tool for
monitoring schema changes in SQL Server 2000.
If you are using SQL Server 2005 sys.objects has modified_date column
which shows information when object was last modified.
Regards
Amish Shah

Modified Date of a file

Hi All,
Think this must be pretty easy to do but can't find the commands anywhere... how can i use T-SQL to find the modified date of a particular file (say c:\temp\test.out) ?
Thanks
JohnYou could use extended sp xp_getfiledetails or xp_cmdshell to check info about file. If you need use this information late - save it in table.

insert mytable--table has to have structure according to sp recordset.
master..xp_cmdshell "dir c:\myfile.zip"

insert mytable--table has to have structure according to sp recordset.
master..xp_getfiledetails "c:\myfile.zip"|||Thanks Snail, worked a treat

Modified Date For Stored Procedures

In SQL Server is there a way to know when a procedure was last
modified? I only see the "Create Date" column on the Enterprise
Manager.

Thanks Experts!jjone99 (jjone99@.hotmail.com) writes:
> In SQL Server is there a way to know when a procedure was last
> modified? I only see the "Create Date" column on the Enterprise
> Manager.

No, in SQL 2000 there is not.

This is addressed in the next version of SQL Server, currently in beta.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Modification date of objects

Hello there
Is there a way to know whan was the last time i've mofied objects? tables,
views, store procedures ect...?You can determine when they were created (crdate in sysobjects) but not
when they were modified. Use a source control system for that.
David Portas
SQL Server MVP
--|||That information is not available from SQL Server. If you really need it you
can use a product like Lumigent's LogExplorer to trail through your
transaction logs, but the best thing to do is use a Source Control system
like SourceSafe.
Jacco Schalkwijk
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:OrVEKZIKFHA.2212@.TK2MSFTNGP12.phx.gbl...
> Hello there
> Is there a way to know whan was the last time i've mofied objects? tables,
> views, store procedures ect...?
>
>|||What is Source Control System?
And how can i get it and use it?
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:uIW6CkIKFHA.576@.TK2MSFTNGP15.phx.gbl...
> That information is not available from SQL Server. If you really need it
you
> can use a product like Lumigent's LogExplorer to trail through your
> transaction logs, but the best thing to do is use a Source Control system
> like SourceSafe.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
> news:OrVEKZIKFHA.2212@.TK2MSFTNGP12.phx.gbl...
tables,
>|||A Source Control program is an application that maintains a code
repository, records changing versions of code and logs who makes
changes and when. You can put into source control any stored procedure
scripts and DDL scripts for your database. Some form of source control
is pretty much essential if you have more than 1 or 2 people cutting
code. Of course, if you give DDL admin rights to your developers then
they still need to be disciplined enough to ensure they put every
change into source control...
SourceSafe is MS's source control offering and it's included with the
Enterprise editions of VisualStudio, or available as a separate
purchase. There are alternative products available though and you can
Google for those.
David Portas
SQL Server MVP
--

Modelling a Time dimension with date & time & time zone info

Hello,

How would you model a time dimension such that data in transactional source can be viewed from different time zones?

Basically I need not only the date but also the time (minute granularity is enough).
for instance, if we have a datetime like
1st jan 2005 at 01:45 AM in GMT stored in the db,
a user from US would see it as 31 Dec 2004 at 08:45 PM in GMT-5 time zone and someone in Asia would see the same transaction at 1st Jan 2005 6:45 AM in GMT+5 timezone.

This way the aggregates would change according to where the data is viewed from, but that is what my users want...

I considered having several time dimensions (one per time zone), but I already have time playing 8 different roles in my cube, so 8*24 time dimensions seems a bit akward...

Thanks

Were you able to find out anything on this?

|||Not sure if this is even more awkward.
Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||

You might be interested to read this entry on my blog, where I discuss this problem:

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!367.entry

|||

I think the adding additional columns to you time table and treating them as translations of the time member names would a solution worth looking at. This gives you the benifit of also being able to give formats that are cultur specific. On the downside, your timezone is based on your UI culture which doesn't work for timezones within the same country.

For populating the localized time columns you could write some managed code that uses .Net to convert to the date/time to the appropriate timezone and then format in the desired culture.

Modelling a Time dimension with date & time & time zone info

Hello,

How would you model a time dimension such that data in transactional source can be viewed from different time zones?

Basically I need not only the date but also the time (minute granularity is enough).
for instance, if we have a datetime like
1st jan 2005 at 01:45 AM in GMT stored in the db,
a user from US would see it as 31 Dec 2004 at 08:45 PM in GMT-5 time zone and someone in Asia would see the same transaction at 1st Jan 2005 6:45 AM in GMT+5 timezone.

This way the aggregates would change according to where the data is viewed from, but that is what my users want...

I considered having several time dimensions (one per time zone), but I already have time playing 8 different roles in my cube, so 8*24 time dimensions seems a bit akward...

Thanks

Were you able to find out anything on this?

|||Not sure if this is even more awkward.
Suppose you have original table having columns:
GMTDate (KeyColumn)
GMTMinute (calculated column from GMTDate or populated by algorithm)
GMTHour (calculated column from GMTDate or populated by algorithm)
GMTDay (...)
GMTMonth (...)
GMTYear (...)
That original table is probably populated by some time generation code you have. So we can extend that code to generate more columns or create calculated columns on DSV:
GMTPlus1Minute
GMTPlus1Hour
GMTPlus1Day
GMTPlus1Month
GMTPlus1Year
We can create this way other sets of columns. Note that we do not need columns like GMTPlus1Minute, GMTPlus2Minute ,... We can have just Minute column. But i suppose later we can not have shared Minute attribute.
When we create time dimension we would have key attribute bound to GMTDate. The fact tables would also contain GMTDate and we will use those in the relationships. Other columns would be used for attributes used in different hierarchies.
USA Time Hierarchy
"Year" level <- GMTPlus5Year attribute
"Month" level <- GMTPlus5Month attribute
"Day" level <- GMTPlus5Day attribute
"Hour" level <- GMTPlus5Hour attribute
"Minute" level <- GMTPlus5Minute attribute
It really depends what user interface you have and how efficient it can hide different things (is it easier to hide dimensions than hierarchies or it does not matter).
|||

You might be interested to read this entry on my blog, where I discuss this problem:

http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!367.entry

|||

I think the adding additional columns to you time table and treating them as translations of the time member names would a solution worth looking at. This gives you the benifit of also being able to give formats that are cultur specific. On the downside, your timezone is based on your UI culture which doesn't work for timezones within the same country.

For populating the localized time columns you could write some managed code that uses .Net to convert to the date/time to the appropriate timezone and then format in the desired culture.

sql

MM/DD instead of MM/DD/YYYY?

Is it possible to display a date field as just MM/DD instead of the full MM/DD/YYYY?

Yes, just go to the format property of the textbox in which this date is displayed and enter MM/dd

|||That was embarrassingly easy. Thanks for the help.