Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating an Expression to Modify a Date Field

In my Derived Column Transformation Editor I have something like this:

DAY([Schedule]) + MONTH([Schedule]) + YEAR([Schedule])

where [Schedule] is a database timestamp field from a OLEDB Datasource.

I want to produce a string something like: "DD/MM/YYYY"

using the expression above, I get something really wierd like "1905-07-21 00:00:00"

Help much appreciated!

Hey Jhon,

DAY, MONTH and YEAR functions return integers; so if you evaluate for example 1905-07-21 with the expression you posted you will get 1933 (1905+7+21), so that weird date you are getting may be the translation of that integer into a date data type.

If all what you want is a string with the DD/MM/YYYY format;I would use an expression like:

(DT_STR,2,1252)DAY([Schedule]) +"/"+ DT_STR,2,1252)MONTH([Schedule]) +"/"+ DT_STR,4,1252)YEAR([Schedule])

keeping the datatype of the derived column as DT_STR. You coud use DT_date or DT_DBDATE data types but that would put back the time part.

Rafael Salas

|||Thanks!... I'll try it|||

I'd like to add a couple of things to Rafael's suggestion.

First, I'd recommend using DT_WSTR for all of the internal operations, since all binary string operations occur as DT_WSTR anyway (DT_STR operands are implicitly cast). If you need a DT_STR result, you could wrap a DT_STR cast around the entire expression.

Second, if you want to ensure that you always get a fixed number of digits (that is, single digit days or months are padded with zeros) you can use a construct like the following for each of the three components:

RIGHT("0" + (DT_WSTR,2)DAY([Schedule]), 2)

Thanks
Mark

|||Perfect! Thanks!|||

I ended up with this. Thanks for the great help!

RIGHT("0" + (DT_WSTR,2)DAY(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,2)MONTH(Schedule),2) + "/" + RIGHT("0" + (DT_WSTR,4)YEAR(Schedule),4)

|||

One quick suggestion... you might want to change that last portion to have 3 zeros in the string literal, though you might never see a 1 or 2 digit year anyway, so it may not matter:

RIGHT("000" + (DT_WSTR,4)YEAR(Schedule),4)

Creating a Unique Index

Hi

I tried the following from the help file...

When you create or modify a unique index, you can set an option to
ignore duplicate keys. If this option is set and you attempt to create
duplicate keys by adding or updating data that affects multiple rows
(with the INSERT or UPDATE statement), the row that causes the
duplicates is not added or, in the case of an update, discarded.

For example, if you try to update "Smith" to "Jones" in a table where
"Jones" already exists, you end up with one "Jones" and no "Smith" in
the resulting table. The original "Smith" row is lost because an
UPDATE statement is actually a DELETE followed by an INSERT. "Smith"
was deleted and the attempt to insert an additional "Jones" failed.
The whole transaction cannot be rolled back because the purpose of
this option is to allow a transaction in spite of the presence of
duplicates.

But when I did it the original "Smith" row was not lost.

I am doing something wrong or is the help file incorrect.

DanThose paragraphs are referring to the IGNORE_DUP_KEYS option which is not
the default when creating an index. Did you specify the IGNORE_DUP_KEYS
option on your CREATE INDEX statement?

Why do you want to ignore duplicate keys in this way? Typically, it would be
better to put the code to ignore duplicates in your INSERT or UPDATE
statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
IGNORE_DUP_KEYS option is a little strange and very non-standard and
non-relational as this article explains.

--
David Portas
----
Please reply only to the newsgroup
--|||Hi

"Did you specify the IGNORE_DUP_KEYS"

Yes I did.

The issue I have is using a update statement with a table that has
IGNORE_DUP_KEYS index as the help file says --

"if you try to update "Smith" to "Jones" in a table where "Jones"
already exists, you end up with one "Jones" and no "Smith" in the
resulting table. The original "Smith" row is lost because an UPDATE
statement is actually a DELETE followed by an INSERT"

But when I try this, the original "Smith" row is not lost...

So am I doing something wrong or is the help file wrong.

Could you give it a try?

Thanks

Dan

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<lI-dnYK39q4-Y1yi4p2dnA@.giganews.com>...
> Those paragraphs are referring to the IGNORE_DUP_KEYS option which is not
> the default when creating an index. Did you specify the IGNORE_DUP_KEYS
> option on your CREATE INDEX statement?
> Why do you want to ignore duplicate keys in this way? Typically, it would be
> better to put the code to ignore duplicates in your INSERT or UPDATE
> statement rather than use the IGNORE_DUP_KEYS option. The behaviour of the
> IGNORE_DUP_KEYS option is a little strange and very non-standard and
> non-relational as this article explains.|||You're right. The UPDATE statement described should produce an error
("Cannot insert duplicate key"). The RTM version of Books Online is wrong
and that page has been changed in the latest version:

http://msdn.microsoft.com/library/e...uniqueindex.asp

--
David Portas
----
Please reply only to the newsgroup
--

Creating a server wide read only account

I need to start training someone on SQL Server 2000, but do not want them to
be able to modify any data or structures. Is there a better way of creating
a server wide read only account besides assigning them to the db_datareader
role in every database?
Thanks.Hi,
There is no server side role for this, Alternate approach is given below,
1. Create a Login named 'reader'
2. Create the user named 'reader' in Model database
3. Assign db_datareader role to 'reader' user
THis will create a reader user for all the new databases.
Note:
You will have to manually create this user in all existing databases.
Thanks
Hari
MCDBA
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>|||better still, why dont you install a msde training instance on a new box
(preferably) and give them sysadmin to it, you could then make a copy of
live data from one to the other and not worry that they are going to destroy
something valuable, and still provide them with the full access that they
are going to eventually have (one day) on the "live" instance.
Br,
Mark Broadbent
mcdba,mcse+i
=======================
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>

Creating a server wide read only account

I need to start training someone on SQL Server 2000, but do not want them to
be able to modify any data or structures. Is there a better way of creating
a server wide read only account besides assigning them to the db_datareader
role in every database?
Thanks.Hi,
There is no server side role for this, Alternate approach is given below,
1. Create a Login named 'reader'
2. Create the user named 'reader' in Model database
3. Assign db_datareader role to 'reader' user
THis will create a reader user for all the new databases.
Note:
You will have to manually create this user in all existing databases.
Thanks
Hari
MCDBA
"Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>|||better still, why dont you install a msde training instance on a new box
(preferably) and give them sysadmin to it, you could then make a copy of
live data from one to the other and not worry that they are going to destroy
something valuable, and still provide them with the full access that they
are going to eventually have (one day) on the "live" instance.
--
Br,
Mark Broadbent
mcdba,mcse+i
======================="Mike" <Mike.Holzwarth@.Comcast.net> wrote in message
news:uHt3$NrAEHA.2212@.TK2MSFTNGP10.phx.gbl...
> I need to start training someone on SQL Server 2000, but do not want them
to
> be able to modify any data or structures. Is there a better way of
creating
> a server wide read only account besides assigning them to the
db_datareader
> role in every database?
>
> Thanks.
>
>sql

Creating a search engine

I'm not sure if this is the appropriate forum to ask this question; however, here goes.

I'm being asked to create/modify a search engine for our Web site. Beyond trying to enhance the speed, I've been asked to add spelling and punctuation checks into the search text.

Right now, all that's being done is a simple like statement.

What's the best method to query a database?

For example, if someone typed in "A and W" or "A/Ws" to get the value"A & W's", how would I make the link? Is there a database table that exists that I could reference to replace & with and (or vice versa) , etc... Is there a good place to start?

I suggest that you look into using the SQL Server full text indexing.

|||

Delayed response (I'm finally getting back to this issue).

So I've tried to create a stored procedure that does both a ContainsTable search and a FreeTextTable search. This seems like it's going to be a pig of a procedure. (Plus I get duplicate records that I need to filter out). The results are pretty good, but I'm worried about performance. Anyone have any experience in using these options? Any good best practices I should be looking out for?

Creating a new login with limited rights

what combonation do you use to create a user with rights to add alertsand to
create backup jobs. This user should be able to modify databases or other u
sers.Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.|||-- Hari wrote: --
Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.
Bummer, Since I'm new at this my role as DBA needs to be limited until I lea
rn more. What is the posibility of creating a user on our live server that c
an creat backup jobs, assign notification in the jobs created, and to view t
he event long on the s
erver itself, not the SQL log. Also, I want to limit the ability to change a
ny settings as far as the databases are concerned.
My login I created contains the following.
No server roles are selected
All databases are selected
Public-is checked
db_securityadmin is checked
db_backupoperator is checked