Sql Auto grow: column

Showing posts with label column. Show all posts

Thursday, March 22, 2012

automatical calculation of a field value

Tuesday, March 20, 2012

Automatic statistics on compound index

I just read a note in a book that says that SQL Server 2000 only
automatically updates the statistics on just the first column of a compound
index (provided automatic statistics feature is turned on). It suggests
that the DBA should create statistics on the other columns.
I'd never heard that before, and can't seem to find that little fasinating
tidbit in any other source.
Can anybody confirm that this is true?"Emmet Gray" <egray1@.hot.rr.com> wrote in message
news:uc9EhPDXDHA.2464@.TK2MSFTNGP09.phx.gbl...
> I just read a note in a book that says that SQL Server 2000 only
> automatically updates the statistics on just the first column of a
compound
> index (provided automatic statistics feature is turned on). It suggests
> that the DBA should create statistics on the other columns.
> I'd never heard that before, and can't seem to find that little fasinating
> tidbit in any other source.
> Can anybody confirm that this is true?
>
That's correct - this is a situation where you may want to consider manually
statistics on columns other than the first column of a composite index.
Steve

automatic sequence number by id

Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
Edgar
Hi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL, [Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code] =
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:

> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar
|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why does this data need to be stored, when you could always retrieve Seq at
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>
|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David
|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:

>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>
|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
sql

automatic sequence number by id

Hi,
Is it possible to have SQL server automatically generate a sequence number
based on another column, both forming the table's primary key. So another
kind of auto-increment field.
What is mean is something like this:
Code Seq Name ...
A100 1 a
A100 2 b
A100 3 c
G432 1 x
G432 2 y
H008 1 p
H008 2 q
H008 3 r
...
Thanks a lot for your help.
EdgarHi
In SQL 2005 when retrieving data you can use the ROWNUMBER function see
http://msdn2.microsoft.com/en-us/library/ms189798.aspx, but you could not
store them. You could use a subquery when inserting the records
e.g.
CREATE TABLE mytable ( [Code] CHAR(4) NOT NULL, [Seq] INT NOT NULL,
[Name]
CHAR(1) )
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'A100', 1, 'a'
UNION ALL SELECT 'A100', 2, 'b'
UNION ALL SELECT 'A100', 3, 'c'
UNION ALL SELECT 'G432', 1, 'x'
UNION ALL SELECT 'G432', 2, 'y'
UNION ALL SELECT 'H008', 1, 'p'
UNION ALL SELECT 'H008', 2, 'q'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H008', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H008'),0), 'r'
SELECT * FROM MyTable
INSERT INTO MyTable ( [Code], [Seq], [Name] )
SELECT 'H010', ISNULL( ( SELECT COUNT(*)+1 FROM mytable WHERE [Code]
=
'H010'),0), 'g'
SELECT * FROM MyTable
This could be incorporated into an INSTEAD OF TRIGGER
John
"Edgar" wrote:

> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||Why does this data need to be stored, when you could always retrieve Seq at
query time?
The problem with storing it in the table is that now it has to be
maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you are
mising Seq=1 for that combination. If A100 has 80,000 rows and you need to
decrease all of their Seq values by 1, that becomes a very, very, very
expensive delete operation.
A
"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
> Hi,
> Is it possible to have SQL server automatically generate a sequence number
> based on another column, both forming the table's primary key. So another
> kind of auto-increment field.
> What is mean is something like this:
> Code Seq Name ...
> A100 1 a
> A100 2 b
> A100 3 c
> G432 1 x
> G432 2 y
> H008 1 p
> H008 2 q
> H008 3 r
> ...
>
> Thanks a lot for your help.
> Edgar|||I need the sequence numbers, because they indicate the order of the records
related to their parent record.
Thanks,
Edgar
"Aaron Bertrand [SQL Server MVP]" wrote:

> Why does this data need to be stored, when you could always retrieve Seq a
t
> query time?
> The problem with storing it in the table is that now it has to be
> maintained. DELETE table WHERE Code = 'A100' AND Name = 'a' and now you a
re
> mising Seq=1 for that combination. If A100 has 80,000 rows and you need t
o
> decrease all of their Seq values by 1, that becomes a very, very, very
> expensive delete operation.
> A
>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:8178F22E-B631-413B-8BF1-32CB0E6B06B3@.microsoft.com...
>
>|||"Edgar" <Edgar@.discussions.microsoft.com> wrote in message
news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>I need the sequence numbers, because they indicate the order of the records
> related to their parent record.
>
> --
Ok, that's legit. You can just use an IDENTITY column for the sequence
numbers. They won't be sequential, and they won't start over for each
parent, but they will give you the relative ordering
EG
Code Seq Name ...
A100 1132 a
A100 1314 b
A100 5991 c
G432 7202 x
G432 82929 y
H008 1002 p
H008 89231 q
H008 999231 r
David|||David,
Thanks for your help. Good suggestion.
I will create the normal 1, 2, 3 when i retrieve the data.
(But it would be a nice addition to the product :-))
Thanks,
Edgar
"David Browne" wrote:

>
> "Edgar" <Edgar@.discussions.microsoft.com> wrote in message
> news:ADBF8CBC-78F0-4E89-AF0A-318BC806A56B@.microsoft.com...
>
> Ok, that's legit. You can just use an IDENTITY column for the sequence
> numbers. They won't be sequential, and they won't start over for each
> parent, but they will give you the relative ordering
> EG
> Code Seq Name ...
> A100 1132 a
> A100 1314 b
> A100 5991 c
> G432 7202 x
> G432 82929 y
> H008 1002 p
> H008 89231 q
> H008 999231 r
> David
>|||Hi Edgar
If you do that then the sequencing may not reflect the true order in which
they were inserted, for example if an entry is deleted subsequent entries
will be moved up. If you are ok with this then using the identity is ok, you
may also want to only allocate the sequence number on the client which would
save you doing the subquery.
John
"Edgar" wrote:
[vbcol=seagreen]
> David,
> Thanks for your help. Good suggestion.
> I will create the normal 1, 2, 3 when i retrieve the data.
> (But it would be a nice addition to the product :-))
> Thanks,
> Edgar
>
> "David Browne" wrote:
>

Monday, March 19, 2012

Automatic Fields

Hi,

it's possibile to define table fields for automatic Last_Update_Date and for
Creation_date using column formula??

Thanks !!!hmario wrote:

Quote:

Originally Posted by

it's possibile to define table fields for automatic Last_Update_Date and for
Creation_date using column formula??

You can create regular columns, and write (fairly simple) insert and
update triggers to populate them appropriately. I don't know a simpler
way to do it.|||Hi.

Ok, I do it with triggers, Thanks !!

"Ed Murphy" <emurphy42@.socal.rr.comha scritto nel messaggio
news:PqH6h.705$Fg.383@.tornado.socal.rr.com...

Quote:

Originally Posted by

hmario wrote:
>

Quote:

Originally Posted by

>it's possibile to define table fields for automatic Last_Update_Date and
>for Creation_date using column formula??

>
You can create regular columns, and write (fairly simple) insert and
update triggers to populate them appropriately. I don't know a simpler
way to do it.

Thursday, March 8, 2012

Automated birthday email

I have a SQL server 2005 database. I want to send a user an email when it's his birthday. His birthdate is stored in the "birthdate" column...
How can I automate this?

You can use xp_sendmail to send the birthday emails. Please follow these steps to configure:

1. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Surface Area Configuration'

2. Click 'Surface Configuration for Features'

3. Select the SQL2005 instance (MSSQLSERVER in my testing); explore 'Database Engine'->go to 'OLE Automation'-> select the 'Enable OLE Automation' checkbox; enable SQL Mail in the same way; Click 'OK' to save changes (seeServerConfig.jpg)

4. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'Configuration Tools'->'SQL Server Configuration Manager'

5. Go to 'SQL Server 2005 Services, double click on 'SQL Server (InstanceName)', in the 'Log On' panel, choose 'This account' to use a domain account to logon (fareast\t-leijie in my testing); Click 'OK' to save change (see ConfigLogonAccount.jpg)

6. Restart SQL2005 instance service.

7. Install a default mail client if you do not have yet (OutLook is good), make a mail profile ('iori' in my testing) using the domain account to send emails, make sure emails can be sent using successful the account.

8. Click 'Start'->'All Programs'->'Microsoft SQL Server 2005'->'SQL Server Management Studio'

9. Choose your instance, explore 'Management'->'Legacy'->'SQL Mail', double click to set Properties, choose the profile used for SQLMail; Click 'OK' to save change.

And you also need a continous running script to detect the "birthdate" column and send mail, such as:

DECLARE @.email varchar(100)
WHILE(1=1)
BEGIN
If exists(select email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
BEGIN
select @.email=email from tbl_Birthday where DATEDIFF(dd,birthdate,getdate())=0
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = N'Happy Birthday my dear firend!';
END
END

|||Thanks for the explanation!

You made such a detailed list and still I don't completely understand it...;)

A few more questions:

1. I want the script to run once every day at 05:00 AM (to keep db load to a minimum)
2. I want to use a format in HTML for my message, this must contain something like: dear <name>, we like to congratlate you with your birthday. Also this entire email must be made up nicely with tables and pictures (which can be just static). Should I just put my entire email in 1 string and replace the <name> part with the username or can I import a .html file and THEN replace the <name> tag with the username? I like the second solution better...but do you know how to achieve this?

Wow, long questions...:p...hope you can help me out!

Thanks!|||It's my pleasure to discuss with you:)

1. You can schedule a job at 05:00 AM every day to accompish this, just open Enterprise Manager, explorerMySQLInstance->'Management'->'SQL Server Agent'->right click 'Jobs'->'New Job', then go to 'Steps' pannel to new a step. Choose 'T-SQL' as step type, and copy your T-SQL script used for sending email to the 'Command' field (as you'll schedule the script to run once a day, let's remove the 'WHILE(1=1)' loop). Then go to 'Schedules' pannel to schedule your job step. You can press F1 for detailed help. Sorry I wrote a wrong script in my previous post, for the case that there may be more than 2 persons have same birhday:

declare @.email varchar(100), @.name varchar(100)

select email,name into #tbl
from tbl_Birthday
where month(birthdate)=month(getdate())
and day(birthdate)=day(getdate())
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
WHILE (@.email is not null)
BEGIN
EXEC master.dbo.xp_sendmail
@.recipients = @.email,
@.subject = N'Happy Birthday!',
@.message = @.name,
@.attachments='c:\happyBirth.html'--you have use attachment if you want HTML feature
DELETE FROM #tbl whereemail=@.email
select @.eamil=email,@.name='Happy Birthday'+name+'!' from #tbl
END
DROP TABLE #tbl

2. See the script above. You can customize the email message by adding the name, but need attchments to add HTML feature.

Wednesday, March 7, 2012

Automate Row Update and send email?

I'd like to update rows matching a certain criteria each night automatically and then send an email.

I have a table with a Status column and an Expire Date column. I want to update rows where the Status = Open and the Expire Date < The current Date by changing the Status to Closed then send an HTML email. Do I have to write a program and schedule it to run or is there a way to do this in MS SQL? I looked at using a stored procedure but I don't see how to schedule one to run (other than at startup) and I'm not sure if the GETDATE function can be called.

Thanks for any help.

Joewindows service or a scheduled task.

sql -> a scheduled job.

It's not some procedure you can create or a function. You have to set it up on the main service to execute.|||create a SQL Server Agent Job to run the stored proc on a schedule...|||I have the same situation.
I am using Access, how can I do this?
Create a .vbs and schedule it with scheduled tasks?
I tried but i get an error in the first Dim statement.|||Thanks for the help. I ended up just creating another asp.net page to take care of it. Then I just run it as a scheduled task and kill it. I haven't learned enough about stored procedure to do it the otherway yet.

Joe

Saturday, February 25, 2012

automagical calculation of a field value

Hi All,
I have a table "AggregatedSales" which has a column called
"AggregatedSales.YearSum".
This column should contain a sum which can be derived directly from another
table called "Sales" by summing up all values of "Sales.Price" WHERE
"Sales.Year" = 2005.
Is it possible to have MS SQL Server automatically calculate the value for
"AggregatedSales.YearSum" when a new data record is inserted? I would like
to add a formula to the column "AggregatedSales.YearSum" that calculates the
value for this field depending on a SELECT-Statement with a SUM() - is that
or such like possible?
Any hints are highly appreciated!
Cheers, JanHi
You will need to do this though a trigger on the table where the insert gets
done.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>|||Hi Jan
You may possibly want to consider using a view for AggregatedSales!
John
"Jan R?sner" wrote:

> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from anothe
r
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates t
he
> value for this field depending on a SELECT-Statement with a SUM() - is tha
t
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
>|||"Jan Rsner" <jan.roesner@.web.de> wrote in message
news:egvP8h45FHA.2984@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> I have a table "AggregatedSales" which has a column called
> "AggregatedSales.YearSum".
> This column should contain a sum which can be derived directly from
> another
> table called "Sales" by summing up all values of "Sales.Price" WHERE
> "Sales.Year" = 2005.
> Is it possible to have MS SQL Server automatically calculate the value for
> "AggregatedSales.YearSum" when a new data record is inserted? I would like
> to add a formula to the column "AggregatedSales.YearSum" that calculates
> the
> value for this field depending on a SELECT-Statement with a SUM() - is
> that
> or such like possible?
> Any hints are highly appreciated!
> Cheers, Jan
>
Create a view rather than a summary table. You can consider making it an
indexed view. You should avoid storing calculated results whenever possible.
David Portas
SQL Server MVP
--

autoincrement question sql 2005 server

How add column autoincrement in sql server 2005 use SQL Server Management
Studio?

Tominfo (informatyk@.fitness[CUT]authority.pl) writes:

Quote:

Originally Posted by

How add column autoincrement in sql server 2005 use SQL Server Management
Studio?

For an existing column? That's a bit of work, because you need to copy
the table to a new definition. There is now ALTER TABLE command to add/drop
identiity. It may look simple in SSMS, but Mgmt Studio performs all that
drop affair behind your back. That would be OK if it knew how to do it,
but it doesn't, so if things go wrong you may mess up your database.

Of course, if you run SQL commands, you may mess the database on your
own as well, but at least you knew what you did.

--
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

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
You could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe
|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

AutoIncrement Primary Key

Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- GabeYou could use the IDENTITY property for an interger column for this. For
example:
CREATE TABLE x (i int IDENTITY(1, 1), j int)
Go
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
Is there anyway to auto increment the primary key column like you can in
access but for SQL 2000? Thanks,
- Gabe|||Nevermind, thanks.
"Gabe Matteson" <gmatteson@.inquery.biz.nospam> wrote in message
news:eRH90lEbFHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there anyway to auto increment the primary key column like you can in
> access but for SQL 2000? Thanks,
> - Gabe
>

auto-increment nvarchar column

Hi experts!

The situation:I have a table containing the MemberID and related member's info. The MemberID is the pk and should be incremented for every new member. However, the ID is in "nvarchar" type, since it consists of an "IM" prefix. I used the following stored procedure to insert new member to the table.

CREATE PROCEDURE dbo.spInsertNewMember
@.parMemberTitle nvarchar(2),
@.parMemberFirstName nvarchar(40),
@.parMemberLastName nvarchar(40)
AS
DECLARE @.LastMemberID AS nvarchar(16)
DECLARE @.NextMemberID AS nvarchar(16)
SET @.LastMemberID = (SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName) VALUES (@.NextMemberID, @.parMemberTitle, @.parMemberFirstName, @.parMemberLastName)
RETURN

I want to ask if this sp can handle any concurrent insert to the MemberID table.

Thanks.

Hi,

why arent you doing the evaluation in the INSERT statement rather than separate ? This would minimize the concurrency effect.

(SELECT ISNULL(MAX(MemberID),'IM000000') FROM MemberInfo (UPDLOCK))
SET @.xx = SET @.NextMemberID = 'IM' + RIGHT(1000000 + (CAST(RIGHT(@.LastMemberID,6) AS INT) + 1), 6)
INSERT INTO MemberInfo (MemberID, MemberTitle, MemberFirstName, MemberLastName)
SELECT 'IM'+ CAST(RIGHT(ISNULL(MAX(MemberID),'IM000000'),6) + 1 AS CHAR(6)),
@.parMemberTitle,
@.parMemberFirstName,
@.parMemberLastName
FROM MemberInfo

If you really want to be sure about this you have to specify a TABLOCK.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

Hi Jens,

Thanks for your suggestion...

Since I have been using mysql for quite a while and thus not familiar with mssql.

what I am really looking for is something similar to "SELECT .... FOR UPDATE" in mysql.

I will try your suggest that later.

Thanks

Autoincrement in varchar value

hi Guys,
I've tangled in serious problem,
Is there any way in which we can autoincrement in
varchar value like i've one column in my table called PayCardId that should
be in 9 digit say '900001@.@.@.', now requirement is this to increment in same
format but it should automatically save in Database, for example
900001@.@.@.
.
.
900100@.@.@.
900101@.@.@.
900102@.@.@.
When it completes it's hundred series it should come in
thousand series like 901001@.@.@. and then
901002@.@.@.
901003@.@.@.
so on and so for
I'm sure there should be any way to
increment this, but i'm not able to think it this time, Please help me ASAP
Any help would be appriciated
ThanksHi
I am not sure whether this code will help you. if this is the logic. u can
cast the final value as string and insert into database.
CREATE TABLE #TempTable (CustID VARCHAR(9))
INSERT INTO #TempTable (CustID) VALUES ('900001ABC')
INSERT INTO #TempTable (CustID) VALUES ('900002XYZ')
INSERT INTO #TempTable (CustID) VALUES ('900003ABC')
DECLARE @.intNewValue INT
SELECT @.intNewValue = CAST(LEFT(MAX(CustID),6) AS INT) FROM #TempTable
SELECT @.intNewValue
SET @.intNewValue = @.intNewValue + 1
SELECT @.intNewValue
Thanks,
Ciju
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||Thanks a lot Ciju, it was realy helpful for me, i've got a good idea from
this code
thanks a lot again for giving quick response,
may god bless you
"Manish Sukhija" wrote:

> hi Guys,
> I've tangled in serious problem,
> Is there any way in which we can autoincrement i
n
> varchar value like i've one column in my table called PayCardId that shoul
d
> be in 9 digit say '900001@.@.@.', now requirement is this to increment in sam
e
> format but it should automatically save in Database, for example
> 900001@.@.@.
> .
> .
> 900100@.@.@.
> 900101@.@.@.
> 900102@.@.@.
> When it completes it's hundred series it should come i
n
> thousand series like 901001@.@.@. and then
> 901002@.@.@.
> 901003@.@.@.
> so on and so for
> I'm sure there should be any way to
> increment this, but i'm not able to think it this time, Please help me ASA
P
> Any help would be appriciated
> Thanks
>|||If you can touch on the table design yet..
then try this.
create TABLE [char_increment] (
[a] AS cast(id_num as varchar) + '@.@.@.',
[id_num] [bigint] IDENTITY (900000, 1) NOT NULL ,
[fname] [varchar] (20)
)
insert into char_increment (fname)
values ('a')
insert into char_increment (fname)
values ('b')
insert into char_increment (fname)
values ('c')
insert into char_increment (fname)
values ('d')

Sunday, February 19, 2012

Auto_Increment?

Is there a way to add a column in my sql server db that is ging to auto increment? I would like to start at 000001 and work right on up. I can't see to find a way to do this. Thanks.Yes. Set that column as the "Identity column" for the table.|||you're the man!!! without having access to my DB now, is there a way to set the mask so I can have the leading zeros I would like?|||The identity column has to be of type int, so there is no such thing as "leading zeros", you can only set the initial value and the step. You should format the number in your application the way you want, or if you really insist on doing it in the DB, then do a view.|||

smalltalk:
The identity column has to be of type int, so there is no such thing as "leading zeros", you can only set the initial value and the step. You should format the number in your application the way you want, or if you really insist on doing it in the DB, then do a view.

awesome. The reason for the leading zeros is just for formatting consistancey, It's a ui thing rather than a database thing so I can just slap some 0's on there after I get the value from the database

Thursday, February 16, 2012

Auto update a field with the current date/time

How can I set a column in a table to auto update the date and time everytime something in that row is updated or when the row is first added?

Thanks ahead for the help,

Jason

Check out BOL to see if the timestamp column can solve your requirements.|||I want to be able to query on this and my understanding of timestamp is that it doesn't actually store dates. Is this incorrect?|||

That is correct. You can set the default value for a column to getdate() or getutcdate(). That takes care of the inserts (if the column isn't mentioned in the insert statement).

For the updates, you'll need to write an update trigger.

|||

jasonburrwc84:
I want to be able to query on this and my understanding of timestamp is that it doesn't actually store dates. Is this incorrect?

Trigger is covered in the thread below. Hope this helps.

http://forums.asp.net/thread/1071147.aspx

Auto SQL Mail

Hi,

I have to generate mails automatically based on databse (SQL SERVER) table,In that table we have expirydate as one column and

based on expirydate I have to generate the mails automatically,Please guide me to solve this issue.

where we have to run the stored procedure.

Do we have to use jobscheduler?

please guide me how to use it

Thanks in avance

regards,

Raja.

Hi,

check following links ...

you have to schedule store procedure ...which will send mail ...

http://support.microsoft.com/kb/312839

http://www.sqlteam.com/article/sending-smtp-mail-using-a-stored-procedure

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci1054928,00.html

Thursday, March 22, 2012

automatical calculation of a field value

automatical calculation of a field value

automatical calculation of a field value

Tuesday, March 20, 2012

Automatic statistics on compound index

automatic sequence number by id

automatic sequence number by id

Monday, March 19, 2012

Automatic Fields

Thursday, March 8, 2012

Automated birthday email

Wednesday, March 7, 2012

Automate Row Update and send email?

Saturday, February 25, 2012

automagical calculation of a field value

autoincrement question sql 2005 server

AutoIncrement Primary Key

AutoIncrement Primary Key

AutoIncrement Primary Key

AutoIncrement Primary Key

auto-increment nvarchar column

Autoincrement in varchar value

Sunday, February 19, 2012

Auto_Increment?

Thursday, February 16, 2012

Auto update a field with the current date/time

Auto SQL Mail

Sql Auto grow

Blog Archive

About Me