Automating Daily Database Inserts...Contd

I used thetutorial on creating a windows service. i was able to succesfully create a service. however, after going through the whole tutorial i realized this is something that is always running. i need to run the service only once a day ( usually past midnight).

does anyone know how i can configure it so it runs only at a specified time and not always.

thanks.The service will always be running since that's how services work. The service periodically checks every Timer.Internval milliseconds whether something should be run. When it actually runs something, like a report at midnight, is up to your code.

If you are concerned about the resources used by the service you can open up the Windows Task Manager and look under the Processes tab. My own similar scheduler uses a negligible amount of CPU. It barely ticks over a few seconds per 24 hours.|||so how do i set it to run at a certain time..all my calculations r dependent on the date functions...so i need the service to run after 12 midnight. so how do i compare the timer to the time of the day...
do you know of any tutorial or some sample...

thanks McMurdoStation|||The service is always running (that is the beauty of it). You need to code it such that it looks at the time of day and runs your process when required. In your case, you can have it check the date periodically, and then whenever the date changes, run the process.|||

// C# but this should give you the general idea
// Declare class level variable to hold date process last run
private DateTime lastRunDate = System.DateTime.Today;
// then handle the event the timer generates when each Timer.Interval has elapsed
private void timer1_Elapsed(object sender, System.Timers.ElapsedEventArgs e) {
DateTime today= System.DateTime.Today;
if( today > lastRunDate){
lastRunDate= today ;
RunYourStuff();
}
}

|||thanks both of you. i understand it better now.
however, i dont know what i messed up. i have been fiddling with it for some time now. i had already created 2-3 windows applications- one for creating a bunch of html pages , another for opening each of these files in a word app and printing it. now i was trying to merge all these processes into one windoes service. so i cut/pasted some code..etc. now it throws an error :
"
cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."

i am pretty sure its not the code. also when i went to administrative tols -> services -> and browsed for my "Service1"... it shows up in the list but its not started. i was trying to start it. it says "

"The service1 on local computer started ans then stopped. some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts Service."

can you help me figure this out...

thanks in advance|||anyone..|||Did you create the installer for the service? I think that article talks about that too. After you create and run the installer then you can start the service from the Services Manager (if it didn't start automatically).

If the service is crashing still then add some code to write out the event log so that you can figure out where it is when it crashes.|||actually i was able to start the service...i can see it from the admin tools -> services

i tried to schedule it using windows scheduler and when the program ran at the specified time
.. it kept throwing the error :

"cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."

i dont understand why we have to create a set up project..
when i schedule the task.. i should select the windowsservice1.exe right ?
i googled around for some time, but most of the articles are too brief..

thanks|||You do not have to schedule the service to run. It is designed to CONSTANTLY run, with or without a user logged in. You need to check to make sure the service continues running (if it stops running, there is a problem in your code) and if it is running, internally in the code, you need to make it do what you want to do periodically (whatever period you need).|||when i tried to build the solution i get this error:

WARNING: This setup does not contain the .NET Framework which must be installed on the target machine by running dotnetfx.exe before this setup will install. You can find dotnetfx.exe on the Visual Studio .NET 'Windows Components Update' media. Dotnetfx.exe can be redistributed with your setup.

i really need some help in getting this running.

doug, like you said i removed it from the scheduled events. i coded it as :

Dim lastrun = System.DateTime.Now.Hour
Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
 Dim today As DateTime = System.DateTime.Today
 If today.Hour > lastrun Then
 lastrun = today
 'add monthly charges
 Call addmonthlycharges()
 'create the html statements
 Call createstmts()
 'print the stmts
 Call printstmts()
 End If
 End Sub

so it will run every hour ( for now ) though it needs to run once a day.
i was trying to debug the program but it keeps throwing back the error :
"cannot start service from command line or a debugger. A Windows Service must first be installed (using instalutil.exe) and then started with the Server Explorer, Windows Services Administrative tool or the NET START command."

thanks|||someone...?|||anyone......|||The warning message about dotnetfx isn't a big deal. Presumably you have the DotNet framework already on your computer so it won't matter. It would only become an issue if you want to deploy your service on a server that doesn't already have DotNet. You can worry about that later...

To install the service follow the directions on that articlehttp://authors.aspalliance.com/hrmalik/articles/2003/200302/20030203.aspx">starting on this page.

You've already done this given the dotnetfx warning message. After it has built the install file (something.msi) right click on the installer project in the solutions explorer and select "Install" from the pop-up list. Either that or navigate to the something.msi file it created an double-click.

Follow the usual instructions for the install wizard.

After the install is done go to the services manager, look up the service you just installed, and start it. In principal, it should then start working and running your update at midnight.|||i can see the status of the service as "started" under services, but its not doing anything.. the prog is actually supposed to create a new folder and a few html files inside the folder and also print them.

heres the entire code :

Public Sub New()
 MyBase.New()
 ' This call is required by the Component Designer.
 InitializeComponent()
 ' Add any initialization after the InitializeComponent() call
 If Not EventLog.SourceExists("MySource") Then
 EventLog.CreateEventSource("MySource", "MyNewLog")
 End If
 EventLog1.Source = "MySource"
 EventLog1.Log = "MyNewLog"
 End Sub
 Dim FileExists As Boolean
 Dim lblmessage As String = Now()
 Dim lastrun = System.DateTime.Now.Hour
 Protected Overrides Sub OnStart(ByVal args() As String)
 ' Add code here to start your service. This method should set things
 ' in motion so your service can do its work.
 EventLog1.WriteEntry("Starting")
 Timer1.Start()
 End Sub
 Protected Overrides Sub OnStop()
 ' Add code here to perform any tear-down necessary to stop your service.
 EventLog1.WriteEntry("Stopping")
 Timer1.Stop()
 End Sub
 Private Sub Timer1_Elapsed(ByVal sender As System.Object, ByVal e As System.Timers.ElapsedEventArgs) Handles Timer1.Elapsed
 Dim today As DateTime = System.DateTime.Today
 If today.Hour > lastrun Then
 lastrun = today
 'add monthly charges
 Call addmonthlycharges()
 'create the html statements
 Call createstmts()
 'print the stmts
 Call printstmts()
 End If
 End Sub

i am sure theres no prob with the code, since i had the same code in a windows application and it runs fine. i was just trying to automate it so it runs by itself everyday...

i'd really appreciate any help in this..
thanks

Automatic Field Entry

Hi,

I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:

ID
UserId
Date
Description

(UserId is a foreign key to asp_net_Users as I am supporting user accounts)

Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).

Can anyone point me in the right direction on the steps needed to create this scenario?

Thanks for any help

Daniel

I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

|||

wtroom:

I'm a bit confused on how you plan on getting the UserID? Is this actually coming from the membership provider in asp.net or are you storing it somewhere else? As far as the Date field you can set the default value of that field on the SQL side to getDate(). This will populate the field with the timestamp whenever you enter in a row into the database. Even though you set a default value you can always pass in your own date/time to your UPDATE statement if you want if you need to edit it.

To be fully accurate, getDate() returns a datetime datatype value that is set to the current date and time.

It's dangerous to mix in the word "timestamp" into such a discussion because timestamp is also a sql server datatype. A timestamp datatype does NOT contain a date or a time and so cannot be used for this purpose.

|||

Hi,

Thanks for your help. The UserId is coming from the Membership provider in asp.net.

I'm relatively new to ASP.NET and am struggling to implement the getDate() on the SQL side. I have created the SQL Datasource to return the table and have then tried to add a WHERE statement.

I assume that the column needs to Date and the Operator = however I am unsure on the control and parameter properties. I have played around and tried Control and QueryString for the Control property and then added getDate() to the Parameter Property Default Value however this just breaks the Details view which is attached to the SQL Datasource.

Any more help would be greatly appreciated.

Thanks

Daniel

|||thanks for your help but I managed to fix this problem.

for the username I created a label with the Id UserIdValue and deleted the text and turned visible to false.

I then added code for the label onload

 Protected Sub UserIdValue_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles UserIdValue.Load UserIdValue.Text = Membership.GetUser().UserName.ToString() End Sub

This made the label display my username when logged on.

The next part for the detailsview code for iteminserting I added

Protected Sub DetailsView1_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles DetailsView1.ItemInserting
e.Values("Writtenby") = Membership.GetUser().UserName
e.Values("Date") = DateTime.Now
End Sub

I then deleted Writtenby and Date fields from the details view.

Now when I insert and enter only title and description the Writtenby and Date fields are automatically updated.

Daniel

Automatic content expiry

Hi All,

I have a job coming up which involves me creating a vacancy system for our website.

I (think) I'll only need one table:

ID (PK)
JobTitle
JobLocation
Salary
Hours
JobDescription
ValidFrom (Date/Time)
ValidTo (Date/Time)

I'll display this data using a C#.Net repeater but I was wondering how I can automatically strip the page of the jobs that have gone past their expiry date? Is is straightforward to achieve?

Thanks in advance,
Brett

Add a where condition to your select statement to bring only the valid Jobs e.g.:

select * from Jobs where getDate() < ValidTo

Hope this was helpful

Eyad Salamin

|||

Thanks Eyad,

I didn't think it would be that simple!

Brett

Auto-Increment of varchar primary key

Hi All
I am looking for a bit of advice.
I am in the process of creating a database in which it has been decided that
all primary keys are going to varchar(40). Not my decision, but anyway.
When inserting into each table it will be possible to specify a value for
the primary, but if not specified a value should be auto-generated. That
means that the values in the primary key field can be a mixture of both
numbers and letters, but if auto-generated it should just be a number.
What be the best way to make this autogenerated values if no value is being
specified in the insert?
TIA
KlausDepends really, if auto-generated does it just need to be a number? Any old
number, or a specific format and range?
I always recommend putting a surrogate key on the tables and use that as the
foriegn key and inside the application (not for display purposes, but for
use as the value in a listbox for instance), that can be a int column with
the IDENTITY property, not null and have a unique constraint on it.
You could set the value of the primary key to that if not specified, that
would save calculating a new unique number.
Otherwise, you could use an 'instead of' trigger, for example...
Instead of using MAX, you could take the value from a table that holds the
last number used.
create table testtrg (
mycol int not null unique
)
go
insert testtrg ( mycol ) values ( 1 )
go
create trigger trgTestTrg on testtrg instead of insert
as
begin
if @.@.rowcount = 0
return
declare @.nextid int
begin tran
set @.nextid = ( select max( mycol )
from testtrg with (tablockx) )
set @.nextid = isnull( @.nextid, 0 ) + 1
insert testtrg values( @.nextid )
commit tran
end
go
-- Note, inserting 1 but it already exists so should give a key violation,
-- but the instead of trigger code kicks in and gives the next id.
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
insert testtrg ( mycol ) values( 1 )
select * from testtrg
go
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"Klaus" <Klaus@.discussions.microsoft.com> wrote in message
news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
> Hi All
> I am looking for a bit of advice.
> I am in the process of creating a database in which it has been decided
> that
> all primary keys are going to varchar(40). Not my decision, but anyway.
> When inserting into each table it will be possible to specify a value for
> the primary, but if not specified a value should be auto-generated. That
> means that the values in the primary key field can be a mixture of both
> numbers and letters, but if auto-generated it should just be a number.
> What be the best way to make this autogenerated values if no value is
> being
> specified in the insert?
> TIA
> Klaus
>|||Thanks a lot, Tony. That was very helpfull.
I will create a unique field on each of my tables. The value for this will
be auto-generated using identity. A trigger will then keep an eye on the
inserts. If no value is being specified for the Primary key, the Identity
value will be copied into the varchar(40) primary key field.
-- Klaus
"Tony Rogerson" wrote:

> Depends really, if auto-generated does it just need to be a number? Any ol
d
> number, or a specific format and range?
> I always recommend putting a surrogate key on the tables and use that as t
he
> foriegn key and inside the application (not for display purposes, but for
> use as the value in a listbox for instance), that can be a int column with
> the IDENTITY property, not null and have a unique constraint on it.
> You could set the value of the primary key to that if not specified, that
> would save calculating a new unique number.
> Otherwise, you could use an 'instead of' trigger, for example...
> Instead of using MAX, you could take the value from a table that holds the
> last number used.
> create table testtrg (
> mycol int not null unique
> )
> go
>
> insert testtrg ( mycol ) values ( 1 )
> go
>
> create trigger trgTestTrg on testtrg instead of insert
> as
> begin
> if @.@.rowcount = 0
> return
>
> declare @.nextid int
>
> begin tran
>
> set @.nextid = ( select max( mycol )
> from testtrg with (tablockx) )
>
> set @.nextid = isnull( @.nextid, 0 ) + 1
>
> insert testtrg values( @.nextid )
>
> commit tran
>
> end
> go
>
> -- Note, inserting 1 but it already exists so should give a key violation
,
> -- but the instead of trigger code kicks in and gives the next id.
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> insert testtrg ( mycol ) values( 1 )
> select * from testtrg
> go
>
>
> --
> Tony Rogerson
> SQL Server MVP
> http://sqlserverfaq.com - free video tutorials
>
> "Klaus" <Klaus@.discussions.microsoft.com> wrote in message
> news:FE28E558-F88F-4A9F-9AAA-40837A9966E9@.microsoft.com...
>
>|||The second part of Tony's point should not be lost. You should use that int
key as the FK for relationships with other tables. If you need to show your
client the benefit of using an int instead of a varchar(40). Load up a
couple of tables with some test data. Perform join's using varchar(40) as
the keys and then the same using int as the key. The performance difference
is noticable.
So use an int (or even bigint) PK, put a unique constraint on the
varchar(40) column and for all business logic purposes, the varchar(40) fiel
d
is the "key". But behind the scenes in the database the far more efficient
int is the key.
John Scragg
"Klaus" wrote:
> Thanks a lot, Tony. That was very helpfull.
> I will create a unique field on each of my tables. The value for this will
> be auto-generated using identity. A trigger will then keep an eye on the
> inserts. If no value is being specified for the Primary key, the Identity
> value will be copied into the varchar(40) primary key field.
> -- Klaus
> "Tony Rogerson" wrote:
>|||Also, if you need the data in a varchar(40) field you can use a calculated
column (if they dont need to enter it).
I concurr with Tony & John, if you're doing joins, definately use the INT
field as the joining field, joining on varchar fields gets very slow at
medium to high data volumes.
create table ( id int identity(1,1) primary key , myPK AS cast( ID as
varchar(40)) )
"John Scragg" <JohnScragg@.discussions.microsoft.com> wrote in message
news:EDB86798-0F96-415A-9D8D-733ED2E0CA02@.microsoft.com...
> The second part of Tony's point should not be lost. You should use that
int
> key as the FK for relationships with other tables. If you need to show
your
> client the benefit of using an int instead of a varchar(40). Load up a
> couple of tables with some test data. Perform join's using varchar(40) as
> the keys and then the same using int as the key. The performance
difference
> is noticable.
> So use an int (or even bigint) PK, put a unique constraint on the
> varchar(40) column and for all business logic purposes, the varchar(40)
field
> is the "key". But behind the scenes in the database the far more
efficient
> int is the key.
> John Scragg
> "Klaus" wrote:
>
will
Identity
Any old
as the
for
with
that
the
violation,
decided
anyway.
value for
That
both
number.
is

Auto-Increment

I'm creating a new SQL 2005 Express database and want to have a Customers
table. How do I auto-increment the CustomerID field? Programming in VB.Net
2005.
I'm new to SQL, so go easy on me please ;-)
TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================Check out IDENTITY in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Burtamus" <burtamus2003@.REMOVETHISyahoo.com> wrote in message
news:efRoosvYGHA.3392@.TK2MSFTNGP03.phx.gbl...
I'm creating a new SQL 2005 Express database and want to have a Customers
table. How do I auto-increment the CustomerID field? Programming in VB.Net
2005.
I'm new to SQL, so go easy on me please ;-)
TIA, Burt
===============================
There's nothing so permanent as
a temporary solution - Me.
===============================|||>> How do I auto-increment the CustomerID field [sic]? Programming in VB.Net2005.
<<
Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS.
What you want is a relational key that you can verify and validate.
Auto-incrementing has to do with the internal state of the hardware and
not the data model. This is why there are industry standard codes, why
websites use email addreses, etc.|||> Auto-incrementing has to do with the internal state of the hardware and
> not the data model.
And what about all that Date and Codd say on SURROGATE KEYS then?
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145391251.621343.288390@.u72g2000cwu.googlegroups.com...
> Let's get back to the basics of an RDBMS. Rows are not records; fields
> are not columns; tables are not files; there is no sequential access or
> ordering in an RDBMS.
> What you want is a relational key that you can verify and validate.
> Auto-incrementing has to do with the internal state of the hardware and
> not the data model. This is why there are industry standard codes, why
> websites use email addreses, etc.
>|||>> And what about all that Date and Codd say on SURROGATE KEYS then? <<
Codd defined them as created by the systrem, and NEVER exposed to the
user. Think of indexes or hashing as the same kind of creature. I
assume that Date feels the same way, but he seldom gets even that close
to implementation considerations.|||> Codd defined them as created by the systrem, and NEVER exposed to the
> user.
You DO NOT need to expose a SURROGATE KEY to a USER!!!

> Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
Thats your interpretation and not anybody elses. Its the view point of a
purist without regard for practical application of the logical model.
The use of the IDENTITY property as a SURROGATE KEY is fine so long as you
consider the implementation of your logical model specifically if the
database is in a distributed environment.
The use of surrogates with an application (note: the database is just one
component of your application) helps us get round many problems such as a)
performance, b) where the NATURAL KEY (note, there is no such thing as a
relational key) is composite and c) where the NATURAL KEY may or is prone to
changing which would cause dramatic concurrency and consistency problems.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1145454973.944139.253670@.e56g2000cwe.googlegroups.com...
> Codd defined them as created by the systrem, and NEVER exposed to the
> user. Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
>|||--CELKO-- wrote:
> Codd defined them as created by the systrem, and NEVER exposed to the
> user. Think of indexes or hashing as the same kind of creature. I
> assume that Date feels the same way, but he seldom gets even that close
> to implementation considerations.
I think you assume wrong. Date agrees with most of the rest of us, that
a surrogate key (like all keys) is part of the logical model. He says
so explicitly in Introduction to Database Systems and differentiates
them from tuple IDs, which are indeed an implementation feature.
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/ms130214(en-US,SQL.90).aspx
--

auto_create & update statistics is null

Does this have the effect of not creating any statistics for the db and therefore forces queries to FTS rather than use an index?
How can I tell if stats exist for the tables/indexes?
Thanks,
MikeBetter to leave the option as it is by default, and also make sure to run SP_UPDATESTATS to update statistics and also generate DBCC checks to keep up the performance.

Use SP_HELPSTATS to return the information on stats.

Auto numbering field similar to

I've been using MS Access 2000 for a while and have recently switched to MS
SQL 2000. When creating a primary key I am used to MS Access ability to auto
matically enter a number in the ID field when I enter data into my tables.
Does MS SQL have a feature similar to this? I checked all the data types and
the only thing that I see that is close to autonumber is uniqueidentifier. Is
that the same thing?
Walker_Michael wrote:
> I've been using MS Access 2000 for a while and have recently switched
> to MS SQL 2000. When creating a primary key I am used to MS Access
> ability to auto matically enter a number in the ID field when I enter
> data into my tables. Does MS SQL have a feature similar to this? I
> checked all the data types and the only thing that I see that is
> close to autonumber is uniqueidentifier. Is that the same thing?
No, not really. What you want is an IDENTITY column (attached to a
numeric data type) as in:
Create Table Customers (
CustID INT IDENTITY NOT NULL )
Unique identifiers can be used as well, but you need to generate the
number manually using the newid() function. They consists of a 16-byte
hexadecimal number (GUID). Some SQL Server users use them as keys. They
are used frequently in replication. The INT IDENTITY can accommodate
more than 2 Billion values and is only 4-bytes as opposed to 16.
The return the last identity value inserted, you should use
scope_identity(). From a stored procedure, you could use:
Create Proc dbo.UpdateCustomer
@.CustID INT OUTPUT,
@.CustName VARCHAR(50
as
Begin
If @.CustID IS NOT NULL
Update dbo.Customers
Set CustName = @.CustName
Where CustID = @.CustID
Else
Begin
Insert dbo.Customers (
CustName)
Values (
@.CustName )
Set @.CustID = SCOPE_IDENTITY()
End
End
To call this procedure:
Declare @.CustID INT
Exec dbo.UpdateCustomer @.CustID OUTPUT, 'David Gugick'
Select @.CustID
David Gugick
Imceda Software
www.imceda.com

auto increment in SQL

Hi.
Am creating a database in SQL and one of the field of my table is customer_id. I want this customer_id to be auto generated and auto increment. I was reading about Identity but it was too complicated to grasp. So how can that be done in a very simple way.
Thanks

As you create a table you specify the identity attribute. Then whenever you insert a row into the table, you will not need to specify a value for the identity column rather the database will do it for you.

Code Snippet

CREATE TABLE customers ( cust_id int identity(1,1), Name char(20) )

The first value is the seed and the second value is the increment. The seed is what value the sequence starts at and the increment is how much the identity column gets incremented after each insert.

Code Snippet

insert customers (Name) values ('Bill')
insert customers (Name) values ('Hank')
insert customers (Name) values ('Joe')
select *
from customers

Notice how you didn't need to specify a value for the identity column.

Code Snippet

cust_id Name
--
1 Bill
2 Hank

3 Joe

Bill gets an id of 1 since he was the first row inserted and thus gets the seed value. The identity value is now at 1. Hank gets a 2 because he gets the current identity value + increment value (1+1). The identity value is now at 2. Joe gets a 3 because he gets the current identity value + increment value (2+1).

|||Hi.
Thanks for replying.
Well to be more precise i am creating the SQL database within Visual Basic Express Edition(http://msdn2.microsoft.com/en-us/library/ms172599(VS.80).aspx).
As such am not typing any code, just creating table like in access.
So how should i do from there. Dont know where to find the coding when i have creating the table.|||

I believe that if you examine the properties for each column (in the lower part of the table design window), you will see that there is a property for IDENTITY. Set that property to Yes/True.

|||Hi.
thanks for replying. so stupid from my part.

Auto ID column or Natural Primary Key?

Hi,
a newbie question:
i am creating a table on sql server 2005 and I am wondering what the best
practices are re the ID/PK column.
Is that good to create an AutoID column and set it as the PK?
Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
+ FEE_ID
I am struggling to understand the benefits of using an Auto ID column
because it won't prevent me to insert duplicates, right?
Thanks for ur help,
Chris*If* you decide for a surrogate key, you still have to protect the natural k
ey (UNIQUE constraint).
Very important.
Having said that, there has been endless discussions regarding whether it is
a good idea to use
surrogate keys. For this newsserver, you will manly find them in .programmin
g.
One of the benefits of using surrogate key is that the referencing tables fo
reign keys will be
smaller (one columns typically an int) compared to several columns. This can
work the other way as
well, as if you do carry the natural key to the referencing table, you might
get rid of some joins.
But this can potentially be seen as a type of not-normalized database as you
store facts redundantly
(whether of not you want to call a natural key a fact or not is open for dis
cussion, partly related
to whether the value has business meaning or not).
Another benefit of surrogate keys is that when the natural key changes (whic
h they tend to do, being
natural keys), you don't have to deal with cascading operations.
I suggest you Google and read the newsgroup archives for arguments from both
sides.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
practices are re the
> ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_COD
E + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column beca
use it won't prevent me
> to insert duplicates, right?
> Thanks for ur help,
> Chris
>|||As Tibor mentions, this is a highly debated subject. And there are many
good, strong arguments on both sides.
It boils down to 'purity' of theory against 'utility' of practice.
Many would argue that a SSN is a 'natural' key for employee records. That
all employees must have one, and that it must be unique. Others would argue
that SSN is nothing more than a surrogate key, having absolutely nothing to
do in directly describing the entity other than being an artifact (surrogate
key) from another database system And so it goes...
I'm with Tibor on the point that using a surrogate key, often an IDENTITY
field, allows greater concentrations of key=value pairs in indexes and tends
to increase performance.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBJPYXfzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> *If* you decide for a surrogate key, you still have to protect the natural
> key (UNIQUE constraint). Very important.
> Having said that, there has been endless discussions regarding whether it
> is a good idea to use surrogate keys. For this newsserver, you will manly
> find them in .programming.
> One of the benefits of using surrogate key is that the referencing tables
> foreign keys will be smaller (one columns typically an int) compared to
> several columns. This can work the other way as well, as if you do carry
> the natural key to the referencing table, you might get rid of some joins.
> But this can potentially be seen as a type of not-normalized database as
> you store facts redundantly (whether of not you want to call a natural key
> a fact or not is open for discussion, partly related to whether the value
> has business meaning or not).
> Another benefit of surrogate keys is that when the natural key changes
> (which they tend to do, being natural keys), you don't have to deal with
> cascading operations.
> I suggest you Google and read the newsgroup archives for arguments from
> both sides.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
> news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
>|||Christophe Leroquais wrote:
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
>
Spot on. But you'd be amazed how many people don't get it!
So if you do use an IDENTITY column (the proper name for an
auto-incrementing surrogate key in SQL Server) make sure you also
declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
constraint is semantically equivalent to UNIQUE NOT NULL so exactly
which key is declared as PRIMARY KEY is pretty unimportant. The crucial
point is that constraints exist for all the relevant keys.
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/ms130214(en-US,SQL.90).aspx
--|||> A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant.
Just to emphasize the similarity between PK and UQ constraint: A foreign key
can refer to either a
PK column (combination) *or* s UQ column (combination). One could argue that
it is rather strange
that we have do decide that one of the candidate keys is ... more important
than the other candidate
keys and make it the PK, but that is how the SQL standard is defined.
Unfortunately, most tools expose the PK very clearly, while UQ constraints a
ren't as clearly exposed
in the tools. This can be one for the reasons why we so many times see table
s with identity columns
being the PK and no other key constraints declared. This, of course, doesn't
prevent duplicates of
the business entity at all.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1157142615.776514.268060@.m79g2000cwm.googlegroups.com...
> Christophe Leroquais wrote:
> Spot on. But you'd be amazed how many people don't get it!
> So if you do use an IDENTITY column (the proper name for an
> auto-incrementing surrogate key in SQL Server) make sure you also
> declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant. The crucial
> point is that constraints exist for all the relevant keys.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Just to emphasize the similarity between PK and UQ constraint: A foreign
> key can refer to either a PK column (combination) *or* s UQ column
> (combination). One could argue that it is rather strange that we have do
> decide that one of the candidate keys is ... more important than the other
> candidate keys and make it the PK, but that is how the SQL standard is
> defined.
Your sir are in danger of getting a headache from your own common sense
http://racster.blogspot.com|||> Your sir are in danger of getting a headache from your own common sense
LOL!
Now, whether to take that as a compliment or insult is still for the jury to
decide. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:OEVy4FszGHA.772@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Your sir are in danger of getting a headache from your own common sense
> http://racster.blogspot.com
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0IC0cszGHA.4204@.TK2MSFTNGP04.phx.gbl...
sense[vbcol=seagreen]
> LOL!
> Now, whether to take that as a compliment or insult is still for the jury
to decide. :-)
Trust your instincts.
I think your common sense is telling you that the big picture of
keys/indexes
is filled with non sense
Q: What is the scope of a key?
Q: What is the scope of an index?
Just doing my job
best,
steve|||See the following address for a related discussion...
http://forums.oracle.com/forums/thr...ssageID=1473614
"Christophe Leroquais" wrote:

> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
> practices are re the ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_COD
E
> + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
> Thanks for ur help,
> Chris
>
>

Auto ID column or Natural Primary Key?

Hi,
a newbie question:
i am creating a table on sql server 2005 and I am wondering what the best
practices are re the ID/PK column.
Is that good to create an AutoID column and set it as the PK?
Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
+ FEE_ID
I am struggling to understand the benefits of using an Auto ID column
because it won't prevent me to insert duplicates, right?
Thanks for ur help,
Chris*If* you decide for a surrogate key, you still have to protect the natural key (UNIQUE constraint).
Very important.
Having said that, there has been endless discussions regarding whether it is a good idea to use
surrogate keys. For this newsserver, you will manly find them in .programming.
One of the benefits of using surrogate key is that the referencing tables foreign keys will be
smaller (one columns typically an int) compared to several columns. This can work the other way as
well, as if you do carry the natural key to the referencing table, you might get rid of some joins.
But this can potentially be seen as a type of not-normalized database as you store facts redundantly
(whether of not you want to call a natural key a fact or not is open for discussion, partly related
to whether the value has business meaning or not).
Another benefit of surrogate keys is that when the natural key changes (which they tend to do, being
natural keys), you don't have to deal with cascading operations.
I suggest you Google and read the newsgroup archives for arguments from both sides.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best practices are re the
> ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column because it won't prevent me
> to insert duplicates, right?
> Thanks for ur help,
> Chris
>|||As Tibor mentions, this is a highly debated subject. And there are many
good, strong arguments on both sides.
It boils down to 'purity' of theory against 'utility' of practice.
Many would argue that a SSN is a 'natural' key for employee records. That
all employees must have one, and that it must be unique. Others would argue
that SSN is nothing more than a surrogate key, having absolutely nothing to
do in directly describing the entity other than being an artifact (surrogate
key) from another database system And so it goes...
I'm with Tibor on the point that using a surrogate key, often an IDENTITY
field, allows greater concentrations of key=value pairs in indexes and tends
to increase performance.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uBJPYXfzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> *If* you decide for a surrogate key, you still have to protect the natural
> key (UNIQUE constraint). Very important.
> Having said that, there has been endless discussions regarding whether it
> is a good idea to use surrogate keys. For this newsserver, you will manly
> find them in .programming.
> One of the benefits of using surrogate key is that the referencing tables
> foreign keys will be smaller (one columns typically an int) compared to
> several columns. This can work the other way as well, as if you do carry
> the natural key to the referencing table, you might get rid of some joins.
> But this can potentially be seen as a type of not-normalized database as
> you store facts redundantly (whether of not you want to call a natural key
> a fact or not is open for discussion, partly related to whether the value
> has business meaning or not).
> Another benefit of surrogate keys is that when the natural key changes
> (which they tend to do, being natural keys), you don't have to deal with
> cascading operations.
> I suggest you Google and read the newsgroup archives for arguments from
> both sides.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Christophe Leroquais" <c.le_roq@.caramail.com> wrote in message
> news:44f86ccb$0$27383$ba4acef3@.news.orange.fr...
>> Hi,
>> a newbie question:
>> i am creating a table on sql server 2005 and I am wondering what the best
>> practices are re the ID/PK column.
>> Is that good to create an AutoID column and set it as the PK?
>> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID +
>> CLASSE_CODE + FEE_ID
>> I am struggling to understand the benefits of using an Auto ID column
>> because it won't prevent me to insert duplicates, right?
>> Thanks for ur help,
>> Chris
>|||Christophe Leroquais wrote:
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
>
Spot on. But you'd be amazed how many people don't get it!
So if you do use an IDENTITY column (the proper name for an
auto-incrementing surrogate key in SQL Server) make sure you also
declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
constraint is semantically equivalent to UNIQUE NOT NULL so exactly
which key is declared as PRIMARY KEY is pretty unimportant. The crucial
point is that constraints exist for all the relevant keys.
--
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/ms130214(en-US,SQL.90).aspx
--|||> A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant.
Just to emphasize the similarity between PK and UQ constraint: A foreign key can refer to either a
PK column (combination) *or* s UQ column (combination). One could argue that it is rather strange
that we have do decide that one of the candidate keys is ... more important than the other candidate
keys and make it the PK, but that is how the SQL standard is defined.
Unfortunately, most tools expose the PK very clearly, while UQ constraints aren't as clearly exposed
in the tools. This can be one for the reasons why we so many times see tables with identity columns
being the PK and no other key constraints declared. This, of course, doesn't prevent duplicates of
the business entity at all.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1157142615.776514.268060@.m79g2000cwm.googlegroups.com...
> Christophe Leroquais wrote:
>> I am struggling to understand the benefits of using an Auto ID column
>> because it won't prevent me to insert duplicates, right?
> Spot on. But you'd be amazed how many people don't get it!
> So if you do use an IDENTITY column (the proper name for an
> auto-incrementing surrogate key in SQL Server) make sure you also
> declare the natural key as UNIQUE and NOT NULL. A PRIMARY KEY
> constraint is semantically equivalent to UNIQUE NOT NULL so exactly
> which key is declared as PRIMARY KEY is pretty unimportant. The crucial
> point is that constraints exist for all the relevant keys.
> --
> 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/ms130214(en-US,SQL.90).aspx
> --
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
> Just to emphasize the similarity between PK and UQ constraint: A foreign
> key can refer to either a PK column (combination) *or* s UQ column
> (combination). One could argue that it is rather strange that we have do
> decide that one of the candidate keys is ... more important than the other
> candidate keys and make it the PK, but that is how the SQL standard is
> defined.
Your sir are in danger of getting a headache from your own common sense:)
http://racster.blogspot.com|||> Your sir are in danger of getting a headache from your own common sense:)
LOL!
Now, whether to take that as a compliment or insult is still for the jury to decide. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve Dassin" <steve@.nospamrac4sql.net> wrote in message
news:OEVy4FszGHA.772@.TK2MSFTNGP05.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:%234YS6plzGHA.4976@.TK2MSFTNGP02.phx.gbl...
>> Just to emphasize the similarity between PK and UQ constraint: A foreign key can refer to either
>> a PK column (combination) *or* s UQ column (combination). One could argue that it is rather
>> strange that we have do decide that one of the candidate keys is ... more important than the
>> other candidate keys and make it the PK, but that is how the SQL standard is defined.
> Your sir are in danger of getting a headache from your own common sense:)
> http://racster.blogspot.com
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O0IC0cszGHA.4204@.TK2MSFTNGP04.phx.gbl...
> > Your sir are in danger of getting a headache from your own common
sense:)
> LOL!
> Now, whether to take that as a compliment or insult is still for the jury
to decide. :-)
Trust your instincts.
I think your common sense is telling you that the big picture of
keys/indexes
is filled with non sense:)
Q: What is the scope of a key?
Q: What is the scope of an index?
Just doing my job:)
best,
steve|||See the following address for a related discussion...
http://forums.oracle.com/forums/thread.jspa?messageID=1473614
"Christophe Leroquais" wrote:
> Hi,
> a newbie question:
> i am creating a table on sql server 2005 and I am wondering what the best
> practices are re the ID/PK column.
> Is that good to create an AutoID column and set it as the PK?
> Otherwise my 'natural' PK is formed with the 'nuplet' FUND_ID + CLASSE_CODE
> + FEE_ID
> I am struggling to understand the benefits of using an Auto ID column
> because it won't prevent me to insert duplicates, right?
> Thanks for ur help,
> Chris
>
>

Auto generated CRUD in Sql 2005 issue

Here is our problem. If you right click on a table in Management studio it gives you the option of creating The Delete, insert, select and Update stored procedures for any table. The problem is that the auto generation script includes the database name in the stored procedures. So if we have a database called DB_DEV and we move the stored procedures over to database DB_QA these stored procedures are now trying to access the wrong database. Is there a way to make sure that the database name is not included?

If I am reproducing your steps correctly, I see that SSMS will 'auto-magically' write a query for one of the CRUD actions -but it's not a stored procedure.

You may wish to combine that action with using a Stored Procedure template -but as far as I can determine, you'll have to manually remove the dbname.

|||

Could you please post the script you see, and the version of SQL Server you are using?

You can try it on a simple table and not necessary your primary one.

When I try to reproduce your problem, the CRUD script created has a "use [<dbname>]" at the beginning of it. If this is the case for you, you can simply remove this line from the script and it will be applicable to any database.

|||Why not use Edit / Find and Replace after you generate the script?

Auto generate IDs in MS SQL SERVER 2005

Hello,

I m creating forms in ASP.Net 2005 using C# language.

I'musing Microsoft SQL Server 2005 and my IDs are in A001, A002, A003...and so on. Howcan I auto generate this IDs? Like A001 +1=A002? Please help...

In SQL server 2005 which datatype i should select and how can i code in ASP.NET with C#??

On button click event the data is inserted and been shown onthe grid..

Thanks

identity columns use only a few datatypes such as the numeric operators (tinyint, int etc.), and GUID (for unique id's). AFAIK, you can't generate ID's in that form. I would recommend that you either use an integer column, and create a combined field e.g. "select 'A' + nameofidcolumn as businessid" , or else don't use identity columns and write the value for your required id into the table directly via custom code.

Cathal