credentials type - service credentials missing

Hi All,

On my 64bit itanium cluster, how come when I open up my Reporting Services Configuration Manager and go to the "Database Setup", under "Credentials Type" I am missing the option "Service Credentials"? This is how I have my dev server set up and would like it to be the same.

This was due to us connecting to the individual node rather than to the cluster when opening up the reporting services configuration manager.

creation of table that type of format........

hello all
i want to create a phone table and it contains two fields empid ,ph.
the phone table following format:

Phone table
-------------
empid ph
-- ----------
office Mobile home
--- --- ---
100 9380768532 98455555 98822213

--------------

i want above type of format and then how to insert into values that
phone table . please help me.surya (suryaitha@.gmail.com) writes:
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:
> Phone table
> -------------
> empid ph
> -- ----------
> office Mobile home
> --- --- ---
> 100 9380768532 98455555 98822213
> --------------
> i want above type of format and then how to insert into values that
> phone table . please help me.

The INSERT statement would be:

INSERT phonetable (empid, office, mobile, home)
VALUES (100, '9380768532', '98455555', '98822213')

If you are using some application environment, it is not unlikely that
the Client API offers some interface that constructs the INSERT statement
under the covers, but gives you a "nicer" interface.

The table design as such is not unquestionable. You have indicated
that office, modbile and home are infact subfields of ph, but there
is no such thing in a database table.

Depending on your requirements, it may be better to do:

CREATE TABLE phonetypes
(phonetype char(3) NOT NULL,
phonetypename varchar(20) NOT NULL,
CONSTRAINT pk_phonetypes PRIMARY KEY(phonetypeid))

INSERT phonetypes (phonetypes, phonetypename)
VALUES ('OFC', 'Office')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('MOB', 'Mobile')
INSERT phonetype (phonetypes, phonetypename)
VALUES ('HOME', 'Home')

CREATE TABLE phonenumbers
(empid int NOT NULL,
phoneno varchar(200) NOT NULL,
phonetype char(3) NOT NULL,
isdefault bit NOT NULL,
CONSTRAINT pk_phonenumbers PRIMARY KEY(empid, phoneno),
CONSTRAINT fk_phonetype FORIEGN KEY (phonetype)
REFERENCES phonetypes (phonetype),
CONSTRAINT fk_employees (empid)
REFERENCES employees(empid))

There would be a trigger on phonenumbers, that enforces that isdefault may
be 1 for at most one combination of (empid, phoneno).

This design buys you more flexibility. Some people have more than one mobile
phone. It also permits you to add other telephone types such as FAX or
IP telephone like Skype. Since on services like Skype you don't have
traditional telephone numbers, but, as I understand it, something that looks
more like an email address, I've made phoneno varchar(200).

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"surya" <suryaitha@.gmail.com> wrote in news:1144477944.400250.34550
@.z34g2000cwc.googlegroups.com:

> hello all
> i want to create a phone table and it contains two fields empid ,ph.
> the phone table following format:

Aren't you going to fail your class if you don't
do your own homework?

Creating table in tempdb using user-defined type

I understand that to create a temp table in tempdb using a user-defined
type, the udt must be defined in Model. However, this doesn't work in
my situation, due to security limitations being placed on the Model
database. (I am using SQL Server 2000).
I'm wondering if there is any viable workaround, short of of using
fixed datatypes when creating the temp table.
For example, say I want to create a temp table as follows:
CREATE TABLE #MyTable(
MyColumn ut_MyColumnType
)
As noted, the above will not work unless ut_MyColumnType is defined in
Model, which can't be done in my situation.
Since I know that ut_MyColumnType is Varchar, I tried using the
COL_LENGTH function to retrieve the size from a column in a non-temp
table that I know is defined using ut_MyColumn type (is there some way
to get the size directly from ut_MyColumnType') into the variable
@.MyColumnLength, and then doing:
CREATE TABLE #MyTable(
MyColumn Varchar(@.MyColumnLength)
)
But this unfortunately doesn't work (Error 170: Incorrect syntax near
'@.MyColumnLength').
Is there any reasonable way to create a temp table using either a
user-defined type not defined in Model, or some substitute method as I
was trying above'IraG wrote:
> I understand that to create a temp table in tempdb using a
> user-defined type, the udt must be defined in Model. However, this
> doesn't work in my situation, due to security limitations being
> placed on the Model database. (I am using SQL Server 2000).
> I'm wondering if there is any viable workaround, short of of using
> fixed datatypes when creating the temp table.
> For example, say I want to create a temp table as follows:
> CREATE TABLE #MyTable(
> MyColumn ut_MyColumnType
> )
> As noted, the above will not work unless ut_MyColumnType is defined in
> Model, which can't be done in my situation.
> Since I know that ut_MyColumnType is Varchar, I tried using the
> COL_LENGTH function to retrieve the size from a column in a non-temp
> table that I know is defined using ut_MyColumn type (is there some way
> to get the size directly from ut_MyColumnType') into the variable
> @.MyColumnLength, and then doing:
> CREATE TABLE #MyTable(
> MyColumn Varchar(@.MyColumnLength)
> )
> But this unfortunately doesn't work (Error 170: Incorrect syntax near
> '@.MyColumnLength').
> Is there any reasonable way to create a temp table using either a
> user-defined type not defined in Model, or some substitute method as I
> was trying above'
You need to use dynamic sql in order to extract the data type and length
of the udt. That's easy enough. The problem is that you are creating a
temp table and because you need to use dynamic sql, the scope of the
temp table is only valid inside the dynamic sql call. Once that call
returns, the temp table is gone. If you were creating a permanent table,
you could do it.
For example, the following fails on the select of the temp table:
exec sp_addtype 'test_type', 'varchar(123)', 'NOT NULL'
go
exec sp_help 'test_type'
create proc dbo.create_temp_table
as
Begin
create table #udt (
[name] varchar(255),
[type] varchar(255),
[length] int,
[precision] int,
[scale] int,
[nullable] varchar(3),
[default_name] varchar(255),
[rule_name] varchar(255),
[collation] varchar(255) )
insert into #udt
exec sp_help 'test_type'
declare @.type varchar(255)
declare @.length int
declare @.new_table nvarchar(1000)
Select TOP 1
@.type = [type],
@.length = [length]
From #UDT
Set @.new_table = 'Create Table #test_table (my_col ' + @.type + '(' +
CAST(@.length as VARCHAR(4)) + '))'
Print @.new_table
Exec (@.new_table)
Select * from #test_table -- fails
Drop table #udt
End
go
exec dbo.create_temp_table
go
exec sp_droptype 'test_type'
go
David Gugick
Imceda Software
www.imceda.com|||Can you create a stored procedure which creates your udt's and mark the proc
as startup
(sp_procoption)?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"IraG" <ijgla@.clas.ucsb.edu> wrote in message
news:1116459324.112987.289060@.g47g2000cwa.googlegroups.com...
>I understand that to create a temp table in tempdb using a user-defined
> type, the udt must be defined in Model. However, this doesn't work in
> my situation, due to security limitations being placed on the Model
> database. (I am using SQL Server 2000).
> I'm wondering if there is any viable workaround, short of of using
> fixed datatypes when creating the temp table.
> For example, say I want to create a temp table as follows:
> CREATE TABLE #MyTable(
> MyColumn ut_MyColumnType
> )
> As noted, the above will not work unless ut_MyColumnType is defined in
> Model, which can't be done in my situation.
> Since I know that ut_MyColumnType is Varchar, I tried using the
> COL_LENGTH function to retrieve the size from a column in a non-temp
> table that I know is defined using ut_MyColumn type (is there some way
> to get the size directly from ut_MyColumnType') into the variable
> @.MyColumnLength, and then doing:
> CREATE TABLE #MyTable(
> MyColumn Varchar(@.MyColumnLength)
> )
> But this unfortunately doesn't work (Error 170: Incorrect syntax near
> '@.MyColumnLength').
> Is there any reasonable way to create a temp table using either a
> user-defined type not defined in Model, or some substitute method as I
> was trying above'
>|||If you only care about the type, precision and scale and don't want to
preserve rules or defaults in the temp table then you could do it from
an existing column in the current database:
EXEC sp_addtype x_type, 'INTEGER', 'NOT NULL'
GO
CREATE TABLE foo (col x_type)
/* Create a temp table */
SELECT col
INTO #foo
FROM foo
WHERE 1=0
SELECT * FROM #foo
DROP TABLE foo
EXEC sp_droptype x_type
User-defined types are virtually obsolete. In my view there is no good
reason to use them and they come with quite a few divantages. Books
Online recommends using CHECK constraints and DEFAULT constraints
instead and I would suggest you follow that advice if you can. Those
alternatives are more powerful and flexible.
David Portas
SQL Server MVP
--|||David,
Thanks for the great suggestion (SELECT col INTO #foo FROM foo WHERE
1=0). (I would mention that I actually thought of the same thing lying
in bed last night, but I know that nobody would believe me even though
it's true..)
Regarding UDTs being virtually obsolete: where exactly does BOL
recommend using CHECKs and DEFAULTs instead? I'm not clear on how using
these constraints can be used to insure uniformity when defining
columns in different tables.
I realize that UDTs can be a pain when they need to be redefined, but
at least they give you something to get a handle on in that
circumstance. And there is always the hope that future releases of SQL
Server will be more supportive of UDT redefinition.
Would anyone know if SQL Server 2005 offers any better support for UDT
redefinition?|||http://msdn.microsoft.com/library/d...br />
20qb.asp
"Rules are a backward-compatibility feature that perform some of the
same functions as CHECK constraints. CHECK constraints are the
preferred, standard way to restrict the values in a column. CHECK
constraints are also more concise than rules; there can only be one
rule applied to a column, but multiple CHECK constraints can be
applied. CHECK constraints are specified as part of the CREATE TABLE
statement, while rules are created as separate objects and then bound
to the column."
Similar story for Defaults. IMO the advantages of CHECK and DEFAULT
constraints far outweigh the tiny benefit of giving a shorthand name to
a datatype. A data dictionary, good change control procedures and a
quick cut-and-paste between CREATE TABLE statements are a surer way to
guarantee consistency of data elements.
Technically, although Rules and Defaults are deprecated I don't think
Types are (yet) but AFAIK there is no new functionality around them in
2005. Yukon has a new model for UDTs using the CLR.
David Portas
SQL Server MVP
--

Creating relationships between 2 databases

Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?
This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully.
I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?
So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL.

Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.

? No, it is not possible. If your only goal is to get around the 4gb limit, then my suggestion is that you upgrade to Workgroup Edition, which has no such limitation. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:d2141cdb-833b-49ba-8bc4-98feb1cd8a83@.discussions.microsoft.com... Is it possible to create relationships and enforce constrains thereof between two fields with the same data type from 2 different databases ( i.e. 2 different .mdf files ) altogether, attached to an instance of SQL Server 2005 Express ?This would help overcome the limitation of the 4GB database size it imposes, by splitting the info required into different databases, rather than just different tables. All the relationships could be then created and a program could be created using Visual Basic or C# or C++ to access all the data meaningfully. I also say this, because in a scenario like where some common piece of data, like contact info, has to be shared for different purposes, which really require two different databases, and the 2 databases cannot be integrated into one, but have to stay separate, would lead to duplication of data, where the same contact info would have to be entered and stored twice. Now, instead, if we create three databases in all, with the third one being the contact info database, we can have the 2 original databases drawing all their required info from the contact database, there need not be any duplication of data. Actually, this is the very purpose of the RDBMS model, isn't it, to reduce unneccesary replicaion ?So, is there any way to do it, because I tried creating relationships between two tables from 2 different databases in SQL Server Management Studio Express, and I was unable to find any option that would allow me to do the same. I just use the GUI tools in it, not T-SQL. Mind you, you are speaking to a rookie here, who has absolutely no programming knowlwdge. I have just migrated here from MS Access, and I currently am using Managemnt Studio Express and Visual Basic 2005 Express and I just know how to use the GUI tools in all these programs. I do not know any T-SQL or where to issue T-SQL commands from.|||

No NNTP User,

my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that.

Can you think of any workaround ?

|||? You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:7915ff66-3526-4b90-aea2-8762ed87d8dc@.discussions.microsoft.com... No NNTP User, my only goal is not to get around the 4GB limit. As I mentioned in the later part of my post, I also want to 'borrow' the data from one completely different database in another database. I want to eliminate duplication of data in the 2 separate databases by borrowing common data from one db. That's the main reason I wanted to do that. Can you think of any workaround ?|||To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||

You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution.

I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?

|||? Actually, you only need distributed transactions if you're going across servers or instances. Cross-database work in the same instance will not need to make use of a distributed transaction. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <bass_player@.discussions.microsoft.com> wrote in message news:793732c8-c600-43b9-8e1a-294100137ac6@.discussions.microsoft.com...To add on to NNTP User, you can also wrap them in distributed transactions. Just make sure the design of one table in one database is appropriate for "virtually" creating a relationship between the other table on the other database.|||? Unfortunately, you can't do most of this stuff via the UI. DML is Data Manipulation Language; DML triggers fire on DML events such as inserting, updating, or deleting data. I recommend that you pick up a good basic SQL book to start with. I'm not sure if there are any out for SQL Server 2005 yet, but you might just start with a SQL Server 2000 one. I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson. -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <Rishi Khetan@.discussions.microsoft.com> wrote in message news:b6185a4a-9770-456b-86e3-feacd72df877@.discussions.microsoft.com... you can also wrap them in distributed transactions. You can enforce cross-database relational integrity using DML triggers. But putting everything in the same database and using schemas to seperate logical boundaries is a much better solution. I'm afraid I've lost you guys, I think I really need to go and learn some T-SQL first, as somebody else suggested to me a while back. Meanwhile, could you please give me the above directions again using the GUI tools of Management Studio Express and VB Express ? Also, what are 'distributed transactions', bass_player ? And how do I use DML triggers, NNTP user ? Isn't DML short for Data Markup Language or something ?|||

Rishi, it is not possible to enforce foreign key relationship across database. NNTP is right, you can use triggers. Triggers are programs which runs when there is a new rows created in a table. Or when there is a row deleted from a table. Or when a row is updated. That's DML trigger, there is something else called DDL trigger, which is a new feature in 2005, but let's not talk about this right now.
Compared to foreign key (FK), this DML trigger has a performance issue, it will make the insert (or delete or update) slower than usual. But it has advantages: it is more flexible than FK. A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.
Vincent

|||

A short answer to your question is that yes you can span across several databases of 4 GB each to create a larger system.

But for that, I would have to write code, wouldn't I ? I mean, I cannot do all this stuff using the GUI ?

Anyway, I wouldn't want to implement a design which slows down the performance of the database much. Thanks for all the help guys.

Rishi.

|||

Say, is there anyway I can create the relationship in the front end application using VB Express ?

Because I really want to put all the info into one application. Its like having everything under one roof. A complete ERP solution ( for small proprietory concerns with no employees, not counting peons ). I have seen many organisations use different software for different purposes and I did not like the idea. For instance, they use different software for keeping track of their inventory, employee details (which covers employee attendance, performance, incentives etc.), financial records etc. Now for example, I have the inventory software, which has a separate table which stores employee names, and this is used so that the sales reports show the employee wise sales, but all the main employee information is stored in the database of another application altogether. Now, suppose I am the manager, and I am going through the sales by emp report, and I suddenly feel I want to know more details about the emp, so in this scenario, I would have to quit the inventory program, fire up the employee program, and then look up the employee's name, and then pull up the emp's info. Now wouldn't it be nice if I had both the databases linked in the front end application, so I could directly pull up the employee's info ? Why I don't want to put both these separate pieces of info in the same database is again performance, and I need to clarify this point - If I have both the inventory and the employee databases from the above example consolidated into a single database, would it slow down the performance of the database as it grows larger in size ? Or would it offer performance benefits if they are kept in separate dbs and are linked through the front end.

I know the idea given to me above about using DML triggers would considerably slow down the database, as the program would have to scan every record every time a record is created or updated or deleted. So I have scrapped the plan of using DML triggers ( after learning DML of course )

Any ideas about using the VB Express created front end application though ?

|||

>>I've heard good things about "Murach's SQL For SQL Server" by Bryan Syverson.

Anyone know if there's going to be a SQL Server 2005 version of this book? I just "discovered" it last night at B&N; it looks like a great book. However, there's nothing on Amazon.com nor the publisher's site about an upcoming revised edition.

Creating one flat file per record in the data flow

I have a table that holds in each record an image (varbinary(max) actually), a text reference for the image and a MIME type for the image. I need to read this table and for each record that has been created since the last run, I need to create a file with the image as the content, the mime type as the file extension and the text reference as the file name. There will be one file created per record found by the data flow source.

I was assuming that I could use the flat file destination and manipulate the file naming using the contents of each record in the flow but am completely stumped on how to achieve this.

Does anyone have any ideas?

thanks

Have a look at the export column transform.|||cheers, it worked great.....

Creating object type

How to create object type in sql server,
i.e in Oracle we can directly create an object TYPE and we can use it in other applications.
What's the equivalent of this object Type in SQL Serverdo you mean a user defined type?|||Yes, a user defined type|||have a look at udts in BOL:

for CLR based types (2005 only): http://msdn2.microsoft.com/en-us/library/ms131106.aspx

for SQL based types:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_addp_584l.asp (2000 syntax, deprecated in 2005 - there you should use CREATE TYPE instead)|||I was going to bring up CLR triggers here but I am not sure I want all of that application code running under my sql service. The book I am writing makes a fair case for some things .Net does faster than sql like string manipulation, running totals and such but I am not sure I am sold yet.

Creating dates from table columns

Hi, i have a table with 3 ints that are used to store dates. The
datetime data type is not used because this data comes from an old
AS400 server.

I need to be able to use those 3 columns to build dates within a query
and be able to use them to compare themselves to other dates

Let's say the table has the following values:

myday mymonth myyear
23 5 2006

and suppose i want to do a query that displays all rows with date
greater than '20060520'

Here is the query i have tried:

select
cast(myday as varchar(2))+'/'+cast(mymonth as
varchar(2))+'/'+cast(myyear as varchar(4))
from mytable

That query returns the string '23/5/2006' yet i can't use it to compare
it with '20060520'

Is there a way i can do this in a simple query?
This is on sql server 2000This seems to work but it is way ugly and depends heavily on the date
format:

select
cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
from mytable
where cast(cast(mymonth as varchar(2))+'/'+cast(myday as
varchar(2))+'/'+cast(myyear as varchar(4)) as datetime)
between '20060520' and '20061231'

Also, i tried to use an alias on the select but then it doesn't get
recognized on the where so i had to type it full again.

Anyone knows a better way? Asp has a dateserial() method that
constructs a date given a month , day and year, but sql server doesn't
seem to have anything like that as a sql date function.|||fjleon@.gmail.com wrote:
> Hi, i have a table with 3 ints that are used to store dates. The
> datetime data type is not used because this data comes from an old
> AS400 server.

You mean via a linked server or do you import it? If it's the latter
then preferably fix the dates at import before they get into the
database.

Try:

SELECT myday, mymonth, myyear,
DATEADD(DAY,myday,
DATEADD(MONTH,mymonth,
DATEADD(YEAR,myyear-2000,'19991231')))
FROM mytable ;

This returns a DATETIME, not a string.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||> Try:
> SELECT myday, mymonth, myyear,
> DATEADD(DAY,myday,
> DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myyear-2000,'19991231')))
> FROM mytable ;
> This returns a DATETIME, not a string.

Seems to be one month forward:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

10520062006-06-10 00:00:00.000
20520062006-06-20 00:00:00.000

I had to substract a month:

SELECT myday, mymonth, myyear
DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
DATEADD(YEAR,myear-2000,'19991231') ) )
FROM mytable

I am guessing this only works from y2k forward. I don't quite
understand why i have to substract a month, but at least it works, and
it's a lot cleaner than casting around.|||Do you know how to use that constructed date in a WHERE? dateadd....
as mydate where '20060501' <=p for example doesn't work

The weird thing is that in sql analizer it shows mydate as the name of
the column but if i use it in the where it fails|||(fjleon@.gmail.com) writes:
> Seems to be one month forward:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> 10 5 2006 2006-06-10 00:00:00.000
> 20 5 2006 2006-06-20 00:00:00.000
> I had to substract a month:
> SELECT myday, mymonth, myyear
> DATEADD(DAY,myday,DATEADD(MONTH,mymonth-1,
> DATEADD(YEAR,myear-2000,'19991231') ) )
> FROM mytable
> I am guessing this only works from y2k forward.

No, it works for dates in the 1900s as well.

> I don't quite understand why i have to substract a month,

When you've added 5 months, you are at the end of May. Now you
add some days. That brings you into June.

> Do you know how to use that constructed date in a WHERE? dateadd....
> as mydate where '20060501' <=p for example doesn't work
> The weird thing is that in sql analizer it shows mydate as the name of
> the column but if i use it in the where it fails

It's not weird at all. You cannot use a column alias defined in a query
anywhere else in the query, except in the ORDER BY clause. However, you
can use a derived table - a query within the query. See the script
below. I've also modofied David's expression in a way that I think is
more robust.

CREATE TABLE fjleon(myday int NOT NULL,
mymonth int NOT NULL,
myyear int NOT NULL)

INSERT fjleon (myday, mymonth, myyear)
VALUES (23, 5, 2006)
INSERT fjleon (myday, mymonth, myyear)
VALUES (12, 7, 1996)
INSERT fjleon (myday, mymonth, myyear)
VALUES (29, 2, 2004)
INSERT fjleon (myday, mymonth, myyear)
VALUES (1, 3, 2004)
go
SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon
go
SELECT myday, mymonth, myyear, mydate
FROM (SELECT myday, mymonth, myyear, mydate =
DATEADD(DAY, myday - 1,
DATEADD(MONTH, mymonth - 1,
DATEADD(YEAR, myyear-2000, '20000101') ) )
FROM fjleon) AS x
WHERE mydate > '20040101'
go
DROP TABLE fjleon

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thank you both, this works nice.