How do I make a field automatically get its values from another fi

Hi,
In my table I have a "PaymentDate" field which is used to store payment
schedules for our clients. I want to add a new field in the same table and
call it "UpdatedPaymentDate" field as most of the time our clients don't
stick to original payment schedules.
How do I make the default value of this new "UpdatedPaymentDate" field to be
the values in the "PaymentDate" field? Do I have to use a trigger for this?
Is there a way to do this without using triggers?
If necessary I'll post the table script but I don't it's necessary for this
simple question. Both fields are in the same table and their data type is
smalldatetime for both.
--
Thanks,
SamSam wrote:
> Hi,
> In my table I have a "PaymentDate" field which is used to store payment
> schedules for our clients. I want to add a new field in the same table and
> call it "UpdatedPaymentDate" field as most of the time our clients don't
> stick to original payment schedules.
> How do I make the default value of this new "UpdatedPaymentDate" field to
be
> the values in the "PaymentDate" field? Do I have to use a trigger for this
?
> Is there a way to do this without using triggers?
> If necessary I'll post the table script but I don't it's necessary for thi
s
> simple question. Both fields are in the same table and their data type is
> smalldatetime for both.
> --
> Thanks,
> Sam
I'm assuming you'll use stored procs for your inserts of course. So use
an optional parameter and assign the default in the proc:
CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
GO
CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
(
@.PaymentDate SMALLDATETIME,
@.UpdatedPaymentDate SMALLDATETIME = NULL
)
AS
INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
GO
EXEC dbo.usp_PaymentScheduleInsert
@.PaymentDate = '2006-04-30T00:00:00.000' ;
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
--

How do I make a field automatically get its values from anothe

David,
Thanks for your response. We're using an Access Form as a front-end to enter
data into this SQL Server table. So I'm not really using a storedproc to
enter data. Would using a trigger the only way to handle this then?
Thanks,
Sam
"David Portas" wrote:

> Sam wrote:
> I'm assuming you'll use stored procs for your inserts of course. So use
> an optional parameter and assign the default in the proc:
> CREATE TABLE dbo.PaymentSchedule (PaymentDate SMALLDATETIME NOT NULL,
> UpdatedPaymentDate SMALLDATETIME NOT NULL /* ... key? */);
> GO
> CREATE PROCEDURE dbo.usp_PaymentScheduleInsert
> (
> @.PaymentDate SMALLDATETIME,
> @.UpdatedPaymentDate SMALLDATETIME = NULL
> )
> AS
> INSERT INTO dbo.PaymentSchedule (PaymentDate, UpdatedPaymentDate)
> VALUES (@.PaymentDate, COALESCE(@.UpdatedPaymentDate,@.PaymentDat
e));
> GO
> EXEC dbo.usp_PaymentScheduleInsert
> @.PaymentDate = '2006-04-30T00:00:00.000' ;
>
> --
> 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
> --
>Sam (Sam@.discussions.microsoft.com) writes:
> Thanks for your response. We're using an Access Form as a front-end to
> enter data into this SQL Server table. So I'm not really using a
> storedproc to enter data. Would using a trigger the only way to handle
> this then?
Yes, but I guess David's hint is that you should start using stored
procedures.
The trigger would look like:
CREATE TRIGGER sams_trigger ON tbl FOR INSERT AS
UPDATE tbl
SET updatedpaymentdate = i.paymentdate
FROM tbl
JOIN inserted i ON tbl.pkcol = i.pkcol
However, judging from the narrative, it seems to me that it would be
better to leave the column NULL. I'm assuming then that when the data is
entered, there has been no update to the payment date yet.
Then again, it was not clear to me whether this column is intended to
catch the date the client actually paid, or if this is a date agreeed-on
beforehand as the new date for the payment.
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.mspxsql

How do I keep a report from automatically running?

This is a multi-part message in MIME format.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Perhaps this is just behavior in the dev environment, but if I provide = default values for all of my paramters, the report runs automatically = when I switch to preview.
Is there a way to provide defaults, but still require the user to click = view before the report runs?
Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Perhaps this is just behavior in the = dev environment, but if I provide default values for all of my paramters, = the report runs automatically when I switch to preview.

Is there a way to provide defaults, but = still require the user to click view before the report runs?

Thanks.
--=_NextPart_000_000A_01C75C1E.0326B0B0--On Mar 1, 5:23 pm, <rlrc...@.newsgroups.nospam> wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks.
As far as I know, there is not a way to keep the report from
automatically running without removing at least one of the parameter's
default values.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||You can probably create a parameter and hide it...and then not provide it
default value.
-
HelpSeeker
"rlrcstr@.newsgroups.nospam" wrote:
> Perhaps this is just behavior in the dev environment, but if I provide default values for all of my paramters, the report runs automatically when I switch to preview.
> Is there a way to provide defaults, but still require the user to click view before the report runs?
> Thanks|||Hi rlrcstr,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

How do I identify whether a column is set up to automatically increment

How do I programmatically identify whether a column is set up to automatically increment? I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.Check out syscolumns in bol. The value is stored in status as hex 0x80 or 128 (decimal) as a bit flag. Also, you might be able to use the colstat field in syscolumns (1 = identity) but I have not found supported documentation - which means it may be that today but not tomorrow.|||Q1 How do I programmatically identify whether a column is set up to automatically increment?
I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.

A1 Use sp_columns (check the Type_Name result set column). For example:

exec sp_columns
@.table_name = 'YourTable',
@.column_name = 'MysteryColumn'

You could also use the third result set of sp_help (or sp_columns more generally):

USE pubs
EXEC sp_help jobs
EXEC sp_columns jobs
EXEC sp_columns @.table_name = 'jobs', @.column_name = 'job_id'

-- compare to (no identity column)
EXEC sp_help authors
EXEC sp_columns authors|||The sp_columns/sp_help just uses the syscolumns database - so if you are looking for a specific answer use the bit flag from syscolumns. Using sp_columns/sp_help adds another layer of complexity that you can pull directly from syscolumns. As a matter of fact, sp_help uses the colstat column to determine an identity column - again this is undocumentated but used with sp_help.

You can use the following as a template:

select a.name, b.name from syscolumns as a inner join sysobjects as b on a.id = b.id where a.status & 0x80 > 0

It will return the column name and object name that has an identity field. You will need to fine tune this for you scenario - but you will be able to return a count or other specific information directly.

Good luck.|||RE:
The sp_columns/sp_help just uses the syscolumns database - so if you are looking for a specific answer use the bit flag from syscolumns. Using sp_columns/sp_help adds another layer of complexity that you can pull directly from syscolumns. As a matter of fact, sp_help uses the colstat column to determine an identity column - again this is undocumentated but used with sp_help.

Selecting directly from system tables, (and not isolating user stored procedurees / applications from changes to underlying system tables in any way) may add multiple layers of "complexity" (in the form of maintenance checks and tasks to perform with every Sql Server service pack), as well. Such practices have also resulted in worse, (in the form of addressing / correcting corrupt data, and troubleshooting stored procedurees / applications that "mysteriously" began to malfunction and generate corrupt data and / or corrupt existing data), following the application of Sql Server upgrades that alter system tables.

Adding a layer of abstraction is exactly the point. Doing so in an organized manner often provides significant benefits in regard to minimizing support and maintenance resource use, and costs (especially in relation to addressing and correcting corrupt data, which may cause a business irreparable damage). If selecting directly from system tables is unavoidable (or using sp_columns/sp_help adds "too much" complexity); consider centralizing maintenance issues by providing your own private level of abstraction e.g.(sp_TableIdentityColumns).

Specifically: Consider creating your own user special stored procedures / functions (that select directly from system tables) and calling them in any other user stored procedures and applications. That way, (when Sql Server upgrades, service pack, or hot fix changes alter the underlying system tables radically), you need only change a few user special stored procedures / functions (rather than every procedure / application that calls / uses them).|||Its always recommended to lookup at INFORMATION SCHEMA VIEWS and not to query against SYSTEM tables. REfer to Books online for more information .

HTH
Originally posted by RickLambert
How do I programmatically identify whether a column is set up to automatically increment? I am looking for a field in the syscolumns table which identifies whether a referenced column is not updatable because it set up to auto increment. Thanks.|||RE: Its always recommended to lookup at INFORMATION SCHEMA VIEWS and not to query against SYSTEM tables. REfer to Books online for more information .
HTH

That would normally have been one reccomendation / suggestion; but, I do not know of any Information_Schema View (COLUMNS, TABLES, and TABLE_CONSTRAINTS, etc.) that provides identity column information?

Could you please share where identity column information is available in the Information_Schema views?? (If it is there, I would appreciate knowing where it may be found. - Thanks.)

Information_Schema views:

CHECK_CONSTRAINTS
COLUMNS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAINS
DOMAIN_CONSTRAINTS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
VIEWS
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE|||Thank you everyone for your response. This web site is a most impressive resource! So, I think I will use the ColStat=1, since this appears to be the most straightforward approach and my paranoia level is not very high. My objective is to create a view which contains a list of all columns and their characteristics, for the purpose of programmatically building insert, update, and delete stored procedures. Thanks again!|||I am curious if you are accessing this data exclusively in sql server or are you going to have an application access this data - say through visual basic or c++. Also, is it possible that you would like to have this functionality accessible to all databases or will it be isolated to one ?|||Hi rnealejr:

I am using an MS Access Data Project with SQL Server data to build SQL Server-specific stored procedures. However stored procedure naming and the parameters passed will remain constant regardless of the underlying database.

So I could use a similar approach to create Oracle stored procedures which would be referenced identically in code; just the connect string would change.

Similarly, the process of building the stored procedures is not hard-coded. A table contains the db-specific syntax for each type of stored procedure (insert-update-delete). Then this is is used by the sp-building routine which uses ADO to cycle through each of the rows in the view containing all the column characteristics of every table of the current database.

By the way, is there an easy way to determine the unique identifier of each table?

-RAL|||Since you are using ADO you could use the information from the provider and determine whether a column is an identity column (know that with ADO the provider has a wealth of information that may not be obvious) - but that may not be appropriate in this case. How do you compile these dynamic stored procedures ? Or is it just the syntax you are dynamically creating ? Can you give an example of the process ?

My other suggestion is to create an Information_Schema view - this would allow you to store the view in one location but run it in the context of the any (current) database.

Are you referring to the uniqueidentifier data type ?|||You can use the Information_Schema.columns view. Look under the DATA_TYPE column.|||Originally posted by rnealejr
Since you are using ADO you could use the information from the provider and determine whether a column is an identity column (know that with ADO the provider has a wealth of information that may not be obvious) - but that may not be appropriate in this case. How do you compile these dynamic stored procedures ? Or is it just the syntax you are dynamically creating ? Can you give an example of the process ?

My other suggestion is to create an Information_Schema view - this would allow you to store the view in one location but run it in the context of the any (current) database.

Are you referring to the uniqueidentifier data type ?

By unique identifier I meant primary key. I think this can be extracted using a view joining sysObjects-sysIndexes-sysIndexKeys.

Not sure how ADO would know how to build these stored procedures without reference to an appropriate view.

Here is an example of the syntax stored in the table referenced by the ADO code:

Create Procedure s_Insert_<<TableName>>
(
<<ParameterList>>
)

As
set nocount on

Insert Into dbo.<<TableName>> (
<<FieldList>>

) Values (

<<ValueList>>
)
return|||Originally posted by rnealejr
You can use the Information_Schema.columns view. Look under the DATA_TYPE column.

I am not familiar with how to access this programmatically.|||RE:
By unique identifier I meant primary key. I think this can be extracted using a view joining sysObjects-sysIndexes-sysIndexKeys.
...
Information_Schema.columns view. Look under the DATA_TYPE column.
I am not familiar with how to access this programmatically.


Q1 [How may one identify ALL unique (and candidate keys, including compound keys) columns in a table?]
A1 MS Sql Server 2k and earlier implement unique columns at the DBMS level via indices. (Looking at a table object's indices is therefore a good way to find columns that are implemented as such using built in DBMS methods. However privately maintained unique columns that do not use built in DBMS functionality to guarantee unique row values may not necessarily be identifiable using this approach.)

An example (to identify ALL unique columns (candidate keys, including compound keys):
Use Northwind
Go
exec sp_HelpIndex @.objname = 'Orders'
---

Q2 [RE: The Information_Schema.columns view; I am not familiar with how to access this programmatically?]
A2 For an example, run:

Use Northwind
Go
Select TABLE_NAME, COLUMN_NAME, DATA_TYPE
From [Information_Schema].[columns]
Where
[TABLE_NAME] = 'Orders'|||Thanx, DBA!|||Thanx, DBA!
You are welcome; hopefully some of it will help you create more robust apps.|||In a similar question, how do I find out if there are any table(s) that using IDENTITY column or numerical column as an IDENTITY, and using it to figure out if the column will reach the Max. value (like the SSN) very soon ?

Thanks|||You can use the following:

DBCC CHECKIDENT ('table_name', NORESEED)

What do you mean by max value - 2,147,483,647 ? Which data type are you using for your identity int or bigint (ss2k only) ?|||or use IDENT_CURRENT('table_name')|||Thanks rnealejr. I am aware of the DBCC CHECKIDENT and IDENT_CURRENT function, but what if the column is NOT employed the IDENTITY but other numeric data type (int or bigint) ? For example, the PurchaseOrder column is using INT as data type and it started at seed 2,000,000,000 (2 billion), and increment by 1000 ... I wanted to find out if such column is exists and how soon it will reach the Max value of INT.

Thanks|||So you have other columns that are not identity columns but numeric columns and need to check and see if you are near the cap for that data type - is this an accurate picture ?|||You got that 100% corrected. :-)

Thanks rnealejr|||So you have 2 options:

1. Search through every table for int/bigints and compare against max value.
2. Create a table of only the columns you need to check.

So which one do you want to do ?|||I can find the IDENT column with this script:

select b.name 'Table Name', a.name 'Column Name'
from syscolumns as a inner join sysobjects as b on a.id = b.id
where b.type = 'u'
and a.status & 0x80 > 0

and I needed to resolve the option#1 you described.

Regards with kindly,

Dam234|||It will not matter whether a column is ident or not - you will be searching for all int/bigint which will include identity columns as well.

Let me see what I can scratch up.|||Portions of this may (or may not) exactly address the issue(s).

However, perhaps some of the following may be helpful (if a bit
repetitive):

1) An Identity column in MS Sql Server 2k may be defined using
any of the following types: (filling the range for a numeric / decimal beginning from - 10^38 +1 to 10^38 - 1 would take a while given 1++ )

[bigint]
[int]
[tinyint]
[numeric]
[decimal]

2) For columns that do use built in DBMS functionality a recommendation for finding out information about candidate
keys, including compound keys is to use exec sp_HelpIndex
@.objname = 'TargetTableName'; as MS Sql Server 2k and earlier
implement unique columns at the DBMS level via indices. Looking
at a table object's indices is a means of identifying columns
that are implemented as such using built in DBMS methods.
(Privately maintained unique columns that do not use built in
DBMS functionality to guarantee unique row values may not
necessarily be identifiable using this approach.)

3) For columns that do not use built in DBMS functionality to
guarantee unique row values writing custom functions / stored
procedures may be necessary. This would include columns that are
not DBMS supported Identity columns per se, that are instead
maintained by user created "identity type" functionality. To
determine if such columns are near the limit for the "custom
data type" would obviously depend not only on the current value
and absolute limit of the underlying "type", but also the
private algorithm itself. (A private algorithm may increment,
decrement etc., by different intervals and may or may not
recycle previously used / deleted values and may or may not be
limited to decimal representations, e.g. hex or greater bases could be used.).

4) An example (to identify candidate keys, including compound
keys that DO use built in DBMS methods):
Use Northwind
Go
exec sp_HelpIndex @.objname = 'Orders'

5) A recommended general means for finding out information about DBMS Identity columns is to use sp_columns, and / or sp_help, and /
or as rnealejr has noted, DBCC CHECKIDENT ('table_name', NORESEED)
and / or IDENT_CURRENT('table_name').

For example:

USE pubs
EXEC sp_help jobs
EXEC sp_columns jobs
EXEC sp_columns @.table_name = 'jobs', @.column_name = 'job_id'
Select IDENT_CURRENT('jobs') As 'IdentCurrent'
Go
DBCC CHECKIDENT ('jobs', NORESEED)

6) More specific means for finding out information about DBMS Identity
columns:

If the additional overhead involved (in the form of maintenance)
is acceptable, one may create user special stored procedures /
functions to provide a variety of additional information about
table objects with identity columns. (Similar to
sp_TableIdentityColumnMetaData and
fn_TableIdentityColumnsMetaData posted here, or
sp_TableIdentityColumns and fn_TableIdentityColumns, posted
earlier in this thread.)

Note: When Sql Server upgrades, service pack, or hot fix changes
alter the underlying system tables involved such user special
stored procedures / functions (that select directly from system
tables) may very well require modification to continue to run
and / or return correct result sets.|||Thanks DBA. I already know how to identify IDENT column, the other
numerical columns are the one that I have to deal with.sql

How do I get SQL to run jobs automatically

Hi All,
We are putting in a SQL 2000 Server and my boss wants to know how we can run "Determine how SQL-Server runs jobs automatically". Any help or KB articles would be appreciated.
Thanks,
JohnSQL Server scheduled jobs are run by the SQl Server Agent. Check Books
OnLine for more info.
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"John Chase" <johnc@.hcd.net> wrote in message
news:3A34B4A6-BF80-469C-8BEC-B2D284A97094@.microsoft.com...
> Hi All,
> We are putting in a SQL 2000 Server and my boss wants to know how we can
run "Determine how SQL-Server runs jobs automatically". Any help or KB
articles would be appreciated.
> Thanks,
> John|||There is a service that gets installed with SQL Server called the SQL Server
Agent. Once this service is started, you can go into Enterprise
Manager ->your server->Management->SQL Server Agent->Jobs and create a new
job, and schedule it. Look in Books on Line once you have it installed, or
search the KB for SQL Server Agent. That should get you started.
Jackie
"John Chase" <johnc@.hcd.net> wrote in message
news:3A34B4A6-BF80-469C-8BEC-B2D284A97094@.microsoft.com...
> Hi All,
> We are putting in a SQL 2000 Server and my boss wants to know how we can
run "Determine how SQL-Server runs jobs automatically". Any help or KB
articles would be appreciated.
> Thanks,
> John