Create an data type with identity(1, 1) property

Hello!

Code Snippet

--I'm trying this command:
create type int8 from tinyint identity(1,1) not null

But, an error occurs: don't possible create an data type with identity property....... Correct?

How to create an data type with identity property? The 'sys.sp_addextendedproperty' SP is the solution? An example, please?

Bye!

The IDENTITY property is linked to a table -as well as a datatype.

CREATE TABLE MyTable

( RowID int IDENTITY,

MyDate varchar(20)

)

You cannot simply CREATE TYPE or DECLARE a variable and also have an IDENTITY property for the type/variable.

|||another example?

execute('
select
identity(int,1,1) as id,
sum(trunk.count) as count,
sum(trunk.duration) as duration,
sum(trunk.charge1) as charge1,
trunk.trunk,
trunk.currency
into
#utilisation
from
(
select
count(local.callid) as count,
sum(local.duration) as duration,
sum(isnull(local.charge1,''0'')) as charge1,
local.trunk,
local.currency
from
tt32.dbo.trunk t,
database.dbo.local01 local
where
local.trunk = t.trunk
group by

local.trunk,
local.currency
)trunk
group by

trunk.trunk,
trunk.currency

--showing the result
select
id,
isnull(count,''0'') as count,
duration,
isnull(charge1,''0'') as charge1,
trunk,
currency
from #utilisation
|||: (

Ok. Thanks!

Bye!

Create a variable type TABLE

I’ve got some tables with the year is part of the name, for example: TABLE2006, TABLE2007, etc.. .The year of the name of table I will read in the table INSERTED of my Trigger : I nead to create a trigger where I update those tables :

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TRIGGER [TESTE]

ON[dbo].[TABTESTE]

FOR INSERT

AS

DECLARE

@.YearTablenvarchar(4),

@.IdClientINT,

@.MyTableTABLE

(

IdClientINT,

SituNVARCHAR(50)

)

BEGIN

SET NOCOUNT ON;

SELECT @.YearTable = SITUACAO, @.IdClient = IdClient FROM INSERTED

SET @.MyTable = 'TABLE' & @.YearTable

UPDATE@.MyTable

SET

Situ= 'X'

WHEREIdClient = @.IdClient

END

GO

Erros:

Msg 156, Level 15, State 1, Procedure TESTE, Line 9

Incorrect syntax near the keyword 'TABLE'.

Msg 137, Level 15, State 1, Procedure TESTE, Line 17

Must declare the scalar variable "@.MyTable".

Msg 1087, Level 15, State 2, Procedure TESTE, Line 18

Must declare the table variable "@.MyTable".

I don't have much experience working with triggers but here's a shot...

-Try changing the @.MyTable to varchar(100)

-Declare another variable @.sql varchar(500)

set @.sql = 'update ' + @.MyTable + ' set situ = '''X''' where IdClient = ' + @.IdClient

exec (@.sql)

You may need to play with the number of single quotes around X to get the string to build correctly.

|||

can you explain what are you trying to do with this statement.

SET @.MyTable = 'TABLE' & @.YearTable

Are you trying to concatenate the string?

|||It seems to me that he thinks that TABLE variables are some kind of references/pointers or interfaces, and that he can use TABLE var with the existing table ('TABLE' & @.YearTable) of the identical structure as TABLE var.
I guess that he, in fact, wants to create and execute some dynamic SQL string based on the inserted values.|||TABLE var is a table just like any "normal" table, it resides in the memory or in tempdb. It si ont some kind of reference!
You can't set it to a string, just like you can't do that with any "normal" table.

Are you trying to update the appropritate table ('TABLE' & @.YearTable) depending on the inserted value (@.YearTable = SITUACAO ... FROM INSERTED)?

|||

hi MauricioBogo,

declare @.MyTable Table

(

IdClient int,

Suit nvarchar(50)

)

after you declare @.MyTable as a "Table"

in this Transaction, the @.MyTable is a "local database object" Already.

not a variable value.

this is why you can't do this → " SET @.MyTable = 'TABLE' + @.Yeartable "

and update @.MyTable .

you can try this, as below:

declare @.sSQL varchar(Max)

declare @.Mytable varchar(255)

select @.yeartable = SITUACAO , @.IDClient = IDClient From Inserted

set @.MyTable = 'TABLE' + @.Yeartable

set @.sSQL = 'update ' + @.MyTable + ' set Situ ='X'
exec (@.sSQL)

--or--

or add any script you need.

try it.

hoping this can help.

Best Regrads,

Hunt.

|||

Yes, I'm trying to concatenate the string, but I already changed to + . Right ?

Thanks

|||

Hunt

Ok it works, thanks

Mauricio

|||

Table variables need to be declare alone:

DECLARE

@.YearTable nvarchar(4),

@.IdClient INT

DECLARE

@.MyTable TABLE

(

IdClient INT,

Situ NVARCHAR(50)

)

sql

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
Mike
Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike
|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

create a new column

HI,
I have a table ONE have the following two columns
[DATE] [datetime]
[NUM] [float]
I want to create a new column DATE1=DATE+NUM, date type is datetime.
Could anyone give me a help?
Thanks,
MikeHi.
What do you want exactly with adding NUM to DATE? Can you get some example?
Do you want to add day, month, year, or something else?
I suppose you want something like this:
create table ONE
(
[DATE] datetime,
[NUM] float
)
INSERT INTO ONE values (getdate(), 2)
INSERT INTO ONE values (getdate(), 3)
SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE])
SELECT * FROM ONE
If you want that your data in DATE1 column must be stored in your table (not
just computed every time you working something with that column) use
PERSISTED column as follows:
ALTER TABLE ONE
ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
Mladen.|||Hi,
I've tried the following code. But it did not work. Please let me help
me out. Thanks!
create table test
(
value float
)
insert into test
values(17188)
exec sp_help test
declare @.startdate datetime
set @.startdate='1/1/1960'
alter table test
add value1 as value+@.startdate
Mike
Michael wrote:
> HI,
> I have a table ONE have the following two columns
> [DATE] [datetime]
> [NUM] [float]
> I want to create a new column DATE1=DATE+NUM, date type is datetime.
> Could anyone give me a help?
> Thanks,
> Mike|||Thanks a lot!
mladjo wrote:
> Hi.
> What do you want exactly with adding NUM to DATE? Can you get some example?
> Do you want to add day, month, year, or something else?
> I suppose you want something like this:
> create table ONE
> (
> [DATE] datetime,
> [NUM] float
> )
> INSERT INTO ONE values (getdate(), 2)
> INSERT INTO ONE values (getdate(), 3)
> SELECT [DATE],dateadd(dd, NUM, [DATE]) FROM ONE
>
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE])
> SELECT * FROM ONE
>
> If you want that your data in DATE1 column must be stored in your table (not
> just computed every time you working something with that column) use
> PERSISTED column as follows:
> ALTER TABLE ONE
> ADD DATE1 AS dateadd(dd, NUM, [DATE]) PERSISTED
> Mladen.

Create a Formula to Represent Shift

I have 4 fields that I need to create a formula on that shows as Shift 1 and Shift 2 the fields are

Type: String

Shift1begins="06:45" Shift1Ends="19:15"

Shift2begins="18:45" Shift2Ends="07:15"

My intention is to create a formula for a report so that I can use the Shift1 and Shift 2 as parameter choices in my report. Can you help?

Thanks!Can you give alittle more information as to what you need the formulas to do? Do you want to pass the work "Shift1" and have Crystal know that it means between 06:45 and 19:15? Or do you want to pass a time and have Crystal tell you what shift it fall under? Or something totally different?|||My report is a productivity report on our Therapists. Some work from 6:45 am to 7:15P this is shift 1. Shift 2 works from 6:45 pm to 7:00 am.

There are 4 fields that represent the times that are in shift 1 and 2 the beginning time and the endtime. My report has a date parameter that allows me to pull a range of data for our Therapists but it doesn't pull the correct data for the night shift because there is no way to specify the shift times. The report consists of

Therapists Name
Dates Worked
Daily Productivity for each day
Productivity Average for each month.

Day shift works fine cause it doesn't span two days. Night shift is inaccurate because it only calculates the time for the one date entered and doesn't allow for the time that scales both days. So I figured that if I set up a parameter based on the shift that this would work. I added the table that has the field in it to the report (which is a canned report anyway) and tried to create a parameter but I am not doing it correctly and that is what I need help with. Thanks!|||Originally posted by Shiloh917
My report is a productivity report on our Therapists. Some work from 6:45 am to 7:15P this is shift 1. Shift 2 works from 6:45 pm to 7:00 am.

There are 4 fields that represent the times that are in shift 1 and 2 the beginning time and the endtime. My report has a date parameter that allows me to pull a range of data for our Therapists but it doesn't pull the correct data for the night shift because there is no way to specify the shift times. The report consists of

Therapists Name
Dates Worked
Daily Productivity for each day
Productivity Average for each month.

Day shift works fine cause it doesn't span two days. Night shift is inaccurate because it only calculates the time for the one date entered and doesn't allow for the time that scales both days. So I figured that if I set up a parameter based on the shift that this would work. I added the table that has the field in it to the report (which is a canned report anyway) and tried to create a parameter but I am not doing it correctly and that is what I need help with. Thanks!

Hi,

If your fields contains datatime datatype you can calculate enddatetime - startdatetime that returns the value of number of seconds difference.|||No that doesn't work. I have date parameters that I have tried changing to "datetime" datatype but it works fine for day shift but on night shift it will not calculate correctly. You see our Therapists on Night shift come in at 6:45 pm and they get off at 7:15 am so they may come in for example on 02/10/04 and their shift will end on 02/11/04. That is why I figured if I use the shift field which is already a pre-filled value at 6:45 pm and 7:15am to represent shift 2 (made up of of two fields which say Shift2Begins and shift2Ends) that I might be able to get the correct data. I guess there is just know way to do this then. So thank you anyway. I appreciate your efforts.

Shiloh|||Originally posted by Shiloh917
No that doesn't work. I have date parameters that I have tried changing to "datetime" datatype but it works fine for day shift but on night shift it will not calculate correctly. You see our Therapists on Night shift come in at 6:45 pm and they get off at 7:15 am so they may come in for example on 02/10/04 and their shift will end on 02/11/04. That is why I figured if I use the shift field which is already a pre-filled value at 6:45 pm and 7:15am to represent shift 2 (made up of of two fields which say Shift2Begins and shift2Ends) that I might be able to get the correct data. I guess there is just know way to do this then. So thank you anyway. I appreciate your efforts.

Shiloh

use this following functions in the crystal report formula field.

datediff ("h", DateTimeValue (2004,04,26,20,0,0),DateTimeValue (2004,04,27,1,20,0))

Create a database with unicode characers

Hi,

My database i have to store arabic as well as english. I read in the forum, it says we have to use nchar or nvarchar as data type for the column to store unicode data.

But i have to create only the database with unicode character set.Because the software am gng to install itself creates the table for the database.

Any solutions?

Regs,

Priya

unfortunately, there isn't a way. if the table is created with varchar, the column is defined as varchar.|||

English and Arabic characters are included in the 1256 codepage and you don’t have to use “Unicode” if the target machines have Arabic support correctly. Make sure your regional settings are set to Arabic and that the language of non-Unicode programs are Arabic. For more details check Arabic support in Windows.

Create a database with unicode characers

Hi,

My database i have to store arabic as well as english. I read in the forum, it says we have to use nchar or nvarchar as data type for the column to store unicode data.

But i have to create only the database with unicode character set.Because the software am gng to install itself creates the table for the database.

Any solutions?

Regs,

Priya

unfortunately, there isn't a way. if the table is created with varchar, the column is defined as varchar.|||

English and Arabic characters are included in the 1256 codepage and you don’t have to use “Unicode” if the target machines have Arabic support correctly. Make sure your regional settings are set to Arabic and that the language of non-Unicode programs are Arabic. For more details check Arabic support in Windows.

Create a column with binary data type

Hi, in reference to the article:

http://aspnet.4guysfromrolla.com/articles/103002-1.2.aspx

I would like to know how to create a 'password' column where the data type is a binary type of length 16
This is my table:

CREATE TABLE Staff
(
Namevarchar (50)PRIMARYKEY,
Password ?? NOT NULL,
Role varchar (50)NOT NULL
)

Please review my code. Thanks in advance for helping me out here!
-Gabian-binary(16)

Check SQL Server's Books Online that's installed with Sql Server for reference. It has a lot of knowledgebase.|||Hi,

Thanks man. I realised the mistake in the create table was in the Primary key. Because of that, I thought that there was a problem with the statement with binary. (First time creating a table with binary value here)

I have downloaded the online book. Thanks for your advice!
-Gabian-sql

Create "Object Type" in sql server

I'm facing problem while creating object type in sql server.
ex:sql query
"create type student as object ( name varchar2 ( 12) , no number ( 5))" is working fine in Oracle where 'student' can be used in any table as datatype, but its not working in sqlserver. Please can you help me how to create object type in sql server.

Thanks and Regrads,

Suzan:

To create a user defined datatype you need to use the sp_addtype procedure. You might want to look this up in books online. There is an example of use on this page:

http://msdn2.microsoft.com/en-us/library/aa259606(SQL.80).aspx

|||

Thanks , but what i am looking for is different.

i am looking for somting in sql server similar to Oracle OBJECT type

CREATE TYPE Pet_t AS OBJECT (
tag_no INTEGER,
name VARCHAR2(60),
MEMBER FUNCTION set_tag_no (new_tag_no IN INTEGER)
RETURN Pet_t
);

is there a way in sql server to do so....

Thanks

|||

If you're using SQL Server 2005 then you can create CLR user-defined types.

Check out this BOL link for more info:

http://msdn2.microsoft.com/en-us/library/ms131120.aspx

Chris

|||

i have found that this is possible using SQL server 2005, but i need a way to do it in sql server 200.

is it possible to do so?

suzan

|||

Nope.. It is not posible in SQL Server 2000.

SQL SERVER 2000 uses UDT as synonyms for pre-defined types (example - PhoneNumber := Varchar(24) ,etc).

The possible alternate solution is storing your data as BINARY. The issue here is you wont retrive your data on SQL statements (not visible on your QA), you have to depend on your UI/BL to retrive and view the data.

Other simple solution may be storing the data as XML.

|||Thanks ...|||Thanks , but do u have a link to where i can find how to store the data as xml

Counting issue in table

I have a table with the following data (sample)

Type Year Price
-- -- --
A 00 100.00
A 00 200.00
A 01 105.00
A 01 105.00-
B 00 100.00
B 00 200.00
B 01 105.00
B 02 00.00

I need to establish a Type Count. The business rule to do the count is -

For a particular type, take a single year and add up the price. If the price is greater than zero then count = 1 else count = 0. After completing the counts for all the years for a particular type, add up the counts.

Based on the business rule for the above sample data I should have the following count.

Count -
Type A = 1 (For Year 00, price = 300.00 so count = 1 and for year 01, price = .00 (105 + 105.00 - ) and so count = 0. add counts to get a total of 1)

Type B = 2 (For Year 00, price = 300.00 so count = 1 and for year 01, price = 105 and so count = 1 and for year 02 price = 00 so count = 0. add counts to get a total of 2)

How do I implement this count using a query. I am free to add any indicator columns that may be required to perform the counting.

Let me know.

Thanks

VivekUSE Northwind
GO

CREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
GO

INSERT INTO myTable99(Type, [Year], Price)
SELECT 'A', '00', 100.00 UNION ALL
SELECT 'A', '00', 200.00 UNION ALL
SELECT 'A', '01', 105.00 UNION ALL
SELECT 'A', '01', -105.00 UNION ALL
SELECT 'B', '00', 100.00 UNION ALL
SELECT 'B', '00', 200.00 UNION ALL
SELECT 'B', '01', 105.00 UNION ALL
SELECT 'B', '02', 00.00
GO

SELECT Type
, [Year]
, SUM(Price) AS SUM_Price
, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
FROM myTable99
GROUP BY Type, [Year]
GO

DROP TABLE myTable99
GO|||Thanks Brett. That helps.|||Can I play too? I added a snippet that I think answers the original questionCREATE TABLE myTable99(Type char(1), [Year] char(2), Price money)
GO

INSERT INTO myTable99(Type, [Year], Price)
SELECT 'A', '00', 100.00 UNION ALL
SELECT 'A', '00', 200.00 UNION ALL
SELECT 'A', '01', 105.00 UNION ALL
SELECT 'A', '01', -105.00 UNION ALL
SELECT 'B', '00', 100.00 UNION ALL
SELECT 'B', '00', 200.00 UNION ALL
SELECT 'B', '01', 105.00 UNION ALL
SELECT 'B', '02', 00.00
GO

SELECT Type
, [Year]
, SUM(Price) AS SUM_Price
, CASE WHEN SUM(Price) > 0 THEN 1 ELSE 0 END AS COUNT_Price
FROM myTable99
GROUP BY Type, [Year]

SELECT type, Sum(price_count)
FROM (SELECT type, [year]
, CASE WHEN 0 < Sum(price) THEN 1 ELSE 0 END AS price_count
FROM myTable99
GROUP BY type, [year]) AS a
GROUP BY type

GO

DROP TABLE myTable99
GO-PatP

counting distincts

This won't work...
SELECT count(distinct z.id, c.alias, c.type)
FROM customer c JOIN zsource z ON c.id=z.id
How can I count the number of distinct combinations of 3 separate fields?This should work :
SELECT count(*) FROM (
SELECT distinct z.id, c.alias, c.type
FROM customer c JOIN zsource z ON c.id=z.id) a
-Nags
"Dave" <dave@.nodomain.tv> wrote in message
news:#H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
> This won't work...
> SELECT count(distinct z.id, c.alias, c.type)
> FROM customer c JOIN zsource z ON c.id=z.id
> How can I count the number of distinct combinations of 3 separate fields?
>|||This is one way. There could be other better ways.
select count(RecCount)
from (select count(*) as RecCount
from customer c JOIN zsource z ON c.id=z.id
group by z.id, c.alias, c.type) d
"Dave" <dave@.nodomain.tv> wrote in message
news:%23H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
> This won't work...
> SELECT count(distinct z.id, c.alias, c.type)
> FROM customer c JOIN zsource z ON c.id=z.id
> How can I count the number of distinct combinations of 3 separate fields?
>|||Thanks much
"Nags" <nags@.DontSpamMe.com> wrote in message
news:O4lRaPcFEHA.2560@.TK2MSFTNGP12.phx.gbl...
> This should work :
> SELECT count(*) FROM (
> SELECT distinct z.id, c.alias, c.type
> FROM customer c JOIN zsource z ON c.id=z.id) a
> -Nags
> "Dave" <dave@.nodomain.tv> wrote in message
> news:#H8rx9bFEHA.2576@.TK2MSFTNGP11.phx.gbl...
fields?
>