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 make a autonumber field in my table!

Hi
im new to ms sql server, having previously used mysql. How do i make a auto number field? What datatype shall i use for it? like autonumber for mysql.

Ive tried setting my primary key field touniqueidentifier data type but then i still need to manually add a guid key in there. i want it so it automatically generates a unique key everytime i add a new row. is this possible?!

hope someone can help!
thanks
SQL Server uses IDENTITY for the auto number, in SQL Server it is a property to the column it is not a column, there are many IDENTITY in SQL Server what you need is the property. The Uniqueidentifier is different that uses a GUID which is a 16bytes Binary data type in SQL Server while IDENTITY is INT. Run a search for SET IDENTITY property in SQL Server BOL (books online). Hope this helps.|||thanks didnt know it was that easy!

How do I lose the exponential display in Query Analyzer?

I have a SQL Server 2000 database with a table with a
field defined as simply [float]. I inserted several
values into the table for this field such as 0.0024.
My question is that when I query the table as follows:
select rcf from tableX
I get such values as 2.3999999999999998E-3. Is there
any reason why SQL Server simply does not display
0.0024? How can I prevent exponential display? Thanks!
GusFloat is an approximate data type and some values cannot be precisely
stored. QA is showing the actual value stored in the database. You can
cast the value to an exact type for display purposes:
DECLARE @.x float
SET @.x = 0.0024
SELECT @.x
SELECT CAST(@.x AS decimal(10,4))
See 'Approximate numeric data' in the SQL Server 2000 Books Online
<createdb.chm::/cm_8_des_04_82ic.htm> for more information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Gus" <gcoll@.yahoo.com> wrote in message
news:be7512a6.0309241846.4892c7ff@.posting.google.com...
> I have a SQL Server 2000 database with a table with a
> field defined as simply [float]. I inserted several
> values into the table for this field such as 0.0024.
> My question is that when I query the table as follows:
> select rcf from tableX
> I get such values as 2.3999999999999998E-3. Is there
> any reason why SQL Server simply does not display
> 0.0024? How can I prevent exponential display? Thanks!
> Gus

How do I loop through dataset then replace?

How would I loop through the rows of a dataset, then replace certain character in a certain column?

I have a database which has a date field formatted 23/08/2007, I wish to loop through the dataset containing the results from the dataset, change the format of the date to 23.08.2007 then store the value back into the dataSet, which is called 'dataSet', and the table is called 'News'.

Using C# by the way.

Thanks in advance

I suggest you to do this in database level though stored procedure that is dynamic (receive your changing criteria via input parameters).
Looping in stored procedures can be done though cursor (I know it is not recomended to use cursor in database for performance issue, but I think in this case it will be much better than looping into your application).

Tip:
You can get many date format for your DATETIME column in database. For example try: Convert(varchar(25), MyDateTimeColumn,113) or Convert(varchar(25), MyDateTimeColumn,111) [Used SQL Books Online for more samples and examples].

Good luck.

|||

Thanks

I'm new to this, so I'll read up on stored procedures, infact, I'm sure I have a whole book on SQL somewhere.

|||

While I read up on stored procedures can someone tell me how to do this in the application? The dataset will only contain 3 rows so it shouldnt have too much of an effect on the application.

|||

If it is just 3 records, then it will not affect the performance that much!

Here is the idea:
Let yourSqlDataAdapterfillyourDataSet.
Now, you have the data in the DataSet, loop into those records in DataSet.
Use String.Replace for replacing issue you have.

http://msdn2.microsoft.com/en-us/library/system.string.replace(VS.71).aspx

Good luck.

|||

What I wanted to do was get data from a database in date order, then alter the format of the date once it was in the dataset.

Instead of faffing about with it, I simply set the date in the database twice, i.e. Date, then a display date.

Now the data is sorted using the Date, but it's the displayDate field which displays the date to the user.

Thanks for all the help

How do I lock a record in ms-Sql 2000 in a Stored Procedure

Let say 3 person update the same record at the same time the count field must be increment 3.

I want to search for the record on id.
Lock the record so other users can't READ the record
Read the count field of the record
ad +1 to the count
update the record with the new count
unlock the record so other user can increment the count on the same record.

Is there a beter way to do this?

All the user will run the same StoredprocedureOriginally posted by ddp2307
Let say 3 person update the same record at the same time the count field must be increment 3.

I want to search for the record on id.
Lock the record so other users can't READ the record
Read the count field of the record
ad +1 to the count
update the record with the new count
unlock the record so other user can increment the count on the same record.

Is there a beter way to do this?

All the user will run the same Storedprocedure

Also, you could use optimistic concurrency - for details see BOL.

How do I link two different field lengths with the same data

Hi,
In Crystal Reports 8.5, I am linking one file to another but do not get report data. The reason is the fields I am using to link one string is 4 characters while the other is 6. Is there a way to get two fields containing the same data in different files but wth different field lengths to link. thank youBump. This post is old, but I'm needing to do basically the same thing. I've got Crystal Reports 11.

Thanks

How do I join two tables to get a row count?

I have two tables: Thread and Reply and they both have a field called UserID

I need to know the number of rows in both tables where UserID="Chris"

I can do this with two stored procedures and add the results together:

SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris'

SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris'

but there must be a better way. Can this be written as one stored procedure with some sort of join?

Thanks, Chris

Try something like this:

select
(select count(user_id) from table1 where userID = 'blah' )
+ (select count(user_id) from table2 where userID = 'blah' )
as totalCount

|||

you can do one stored proc, get the counts into 2 different variables and send them out as OUTPUT parameters, so you get all the data in 1 trip.

You can also do it in one query as follows:

SELECT COUNT(*) as Count1, (SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris') as Count2

FROM Thread WHERE Thread.UserID='Chris'

|||

Darrell,

That worked great. Thanks,

Chris

How Do i insert For XML Result in another table

Hi,
I have table with nText field and i want to insert 'for xml query' into that field, what is datatype for 'for xml quey result'
e.g (select * from publishers for XML auto elements)

JigarHi Jigar,

I'm not sure I understand. Do you mean that you want to save the results of that SELECT query, which returns XML, in the nText field? If so, nText is fine, since the data is text.

Or do you mean something else?

Don|||I have table with fields like
itemID int,
XML NText

is there any way i can insert through StoredProcedure .

my storedprocedure should do like this
--gets data from first table using for XML
--Insert XML data into another table as described above.

i can not find any way to handle this through storedprocedure

currently i am doing following using client

-- open datareader that featches xml from first table
-- save xml using another call to database.

i was wondering if there is any way to do that using same sproc.|||Well, I've been mulling this over without much of a solution. You could always use a cursor but that would be ugly, fragile, and slow.

I'll keep thinking about this. I'm sure I'm missing something simple.

Anyone else?

Don

How do I insert a word doc into a varbinary(max) field for full-text indexing

Need to start learning to use full-text and need to know how to insert word docs, emails, into the database. How is metadata retirieved from these sources and are they loaded into separate tables fields?

Thanks

Looks like I got my answer:

USE AdventureWorks GO CREATE TABLE myTable(FileName nvarchar(60), FileType nvarchar(60), Document varbinary(max)) GO INSERT INTO myTable(FileName, FileType, Document) SELECT 'Text1.txt' AS FileName, '.txt' AS FileType, * FROM OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB) AS Document GOThanks

How do I in Crystal 10

Using CRYSTAL 10.
A field titled INVOICE NUMBER always has *0000000 at the end. These are extraneous characters I do not need on the report. How do I remove them from the Invoice Number field. The field length is 25; and, again, I do not need the last eight characters.If it is a character field then u can use SQL expression , and builtin function substring.sql

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 the initals of a name in a report?

Hello all,
does anybody know the code to get the name initials for a field in a report?
e.g. username is 'John Miller' and i want only the initials 'JM'?
Thanks.Hey is your data going to be same meaning (firstname lastname) then can be
done using
= left(field!name, 1) + " " + mid(field!name, instr(" ", field!name) +1 ,1)
left will return the first letter and instring will search the space +1 so
the last name letter is pointed and length is 1 so it picks up the first
letter of the lastname.
Amarnath
"Joggel" wrote:
> Hello all,
> does anybody know the code to get the name initials for a field in a report?
> e.g. username is 'John Miller' and i want only the initials 'JM'?
>
> Thanks.
>

How do I get it to a precisions scale of .00?

I have set the output columns to decimal and data scale of 2. And have also set the field to be 0.00, and in the csv desination file it always puts .000000, How can I get it to be 0.00?

Thanks you for the help

Try creating a derived column cast to DT_NUMERIC and scale of 2 between your data source and destination. That should allow you to get 2 decimal places for any numeric input type. You can add all the columns you want to do this to in the one derived column task.

How do i get a count for one value and a different count from the same value?

I have a field, that display either Yes or no. I want to be able to put in a text box or in a table the count for the yes's and the count for the No's for that one column or various columns. Is this possible?

use a matrix or table

group by the yes/no field

in the value cell put Count(Fields!yes_no.Value)

How do I generate auto increment number in SQL Express?

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)?Thanks a lot.For your first question: In SQL Server, you can use data type int as your identity column and assign this column as indentity field from the property window. It will work like autonumber field in Access.|||

cckiat:

Hi, in Access, I can use an Auto-Increment number for my primary key field. May I know how do I do that in SQL Express? In addition, is there any tutorial on how to use SQL Express to generate customised unique numbers (such as membership number, Customer ID such as A001 where A is based on the customer's name while 001 is due to the fact that the customer is the first among those with names starting with A)? Thanks a lot.

IDENTITY is the auto increament in SQL Server it is a property of the column, the second one you described is a SEQUENCE it is in Oracle not SQL Server but it is similar to IDENTITY. Both are defined by ANSI SQL but Microsoft and Oracle choose to implement one and not the other. But you can use GUID in SQL Server to generate Unique numbers but it is a 16bytes Binary data type so use it with care. Hope this helps.

How do I exclude carriage returns as part of a IS NULL exclusion clause...

Hi

I have stupid users... who doesn't?! They have entered carriage returns as a whole value in some fields, that is, the field contains nothing more than a carriage return.

I really need to treat these cases as nulls and have successfully removed whole fields of nothing but spaces by using the LTRIM(RTRIM()) construct. Unfortunately, this doesn't deal with carraige returns. Even CASTing and CONVERTing to varchar and then using LTRIM(RTRIM()) doesn't work.

Does anyone know how I can elegantly get around this problem other than my best guess below:

Best guess pseudo code:
IF count of field is greater than 1 THEN probably a full sentence so ignore ELSE SUBSTRING first character and if CHAR(10, etc) then treat as NULL.

Here's some code that reconstructs the problem:
select datalength(char(13)) CarriageReturnVisible
, datalength(ltrim(rtrim(cast(char(13) as varchar)))) [This Don't Work]

Cheers - AndyThis is a really slippery slope, but if you are dealing with 8 bit ASCII, you could use:LIKE '%[!-~]%' to see if there are any printable characters (this expression ignores whitespace).

-PatP|||Very interesting and very cool (speaking purely as a geek!).

I got it to work by doing this:

and field like '%[a-z0-9]%'

This expression only shows fields that contain letters and/or numbers and excludes stupid entries like periods, comma's, carraige returns...

Thanks very much for your help!

Andy|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'|||i think that an enter is not just an carriage return but is also a line feed
so isnt is char(10) or Char(13) at the same time

i seem to remember something in 3g programming that looks like vbCRLF etc...

just askin'You are quite correct, systems that are derived from MS-DOS see 0x0d0a as a line end. The code that I proposed dodges that bullet altogether though, along with several others, and seems to have solved the problem better than I'd expected!

-PatP

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.
I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.
|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.

How Do I embed a RegEx in a Report Model

I am having trouble figuring out how to create a new expression-based field in a report model that relies upon the result of a regular expression. It looks like I cannot make calls to static methods in the .NET in a report model. Correct?

Here is my attempt at the expression I want:
=IF((System.Text.RegularExpressions.Regex.IsMatch(PreferedEmail)),True,False)

The error returned when I attempt to save the expression in Report Model Designer is "The following is character is not valid: ."

BTW, the message is copied verbatim. The poor grammer is not my fault.I take it that no news is very bad news on this front. There is no way to reference a static .NET method/object in a Report Model expression. That's a real shame.|||

Kevin,

You can use regular expression in reporting services for example:

=System.Text.RegularExpressions.Regex.Replace(Fields!Phone.Value, "(\d{3})[ -.]*(\d{3})[ -.]*(\d{4})", "($1) $2-$3")

Hammer

|||I'm going to go out on a limb here & guess that you've never tried that in a Report Model (SDML). You absolutely can do that in an expression embedded in a Report Definition (RDL). But the Report Model Designer will not allow you to save the expression.|||

You are correct -- referencing .NET methods from a report model expression is not supported. Depending on the report the user creates, report model expressions can potentially end up translated into SQL or MDX and embedded deep in some database query.

If you have VS and you're just using the expression as a surface expression in a particular report, you might save your report out as a file, load it up in Report Designer, and then add the expression there. I realize this doesn't give you anything in the model, however.

|||It's not the answer I wanted but now I understand why I can't have what I want. Thanks for the explanation.

How do I do an UPDATE in this complex query?

Hi,
I wrote this stored procedure that works, and returns what I want, but now I want to mark the "Active" field to 1 for each of the records returned by this. I have had no luck so far.
ALTER PROCEDURE [dbo].[SelectCurrent_acmdtn]
@.extractNum char(10)
AS
BEGIN
SET NOCOUNT ON;
SELECT id, efctv_from_dt, efctv_to_dt, modify_ts, extractno, Active, acmdtn_RECID
FROM (SELECT dbo.acmdtn.*, row_number() OVER (partition BY id
ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum) Rank
WHERE rn = 1
END
I have tried inserting Update between the 2 "WHERE" statements, but it returns an error
"Invalid column name 'rn'."
I have also tried opening the recordset in Access VB , but I am restricted to read-only. (acmdtn_RECID is the primary key)
I would prefer to have a stored procedure do this.
I can get it to work if I take out the parameter, but I need that part.
The purpose of this (if you care..) is I have a large amount of historical data (this is one of 42 tables) that I need to run reports on, but I need to run reports on the data "as of a certain date (or extractno)". This is data exported from another application that I only get flat files for, that I have imported into SQL Server tables. So, by running this procedure, I get all of the the latest "id" records as of the extractno (I get a new extract every day, with changes that were made the previous day). I want to mark these latest fields in the "Active" field so when I create reports, I can have them filter on this field.
Any help would be greatly appreciated.

I would suggest changing this stored procedure to a function or perhaps making a version of this that is a function. Hang on and I'll try to show you. Maybe something like:

create function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

|||

I mocked this up with this table and data:

create table dbo.acmdtn
( id integer,
efctv_from_dt datetime,
efctv_to_dt datetime,
modify_ts datetime,
extractno integer,
Active integer,
acmdtn_RECID integer
)
go

insert into acmdtn
select 1, '1/1/7', '2/1/7', '1/1/7', 1, 0, 1 union all
select 1, '2/1/7', '4/1/7', '1/15/7', 2, 0, 1 union all
select 1, '4/1/7', '1/1/8', getdate(), 3, 0, 1 union all
select 2, '1/1/7', '3/1/7', '1/1/7', 1, 0, 2 union all
select 2, '3/1/7', '7/1/7', '2/15/7', 2, 0, 2
select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 0 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 0 2
*/

I tested the UPDATE like this:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( SELECT id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID
FROM ( SELECT dbo.acmdtn.*,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum
) Rank
WHERE rn = 1
)

go

update selectCurrent_Acmdtn (1)
set Active = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 1
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 09:59:02.560 3 0 1
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 2
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 2
*/

Something to consider is the use of:

SELECT dbo.acmdtn.*,

This is dangerous because it is not intuitively obvious whether or not this statement will return all columns of the dbo.acmdtn table. This is because the meaning of this select statement is determined at function compile time and NOT at function execution time. Therefore, I strongly suggest that you alter this statement to explicitly list all columns returned by the select statement.

This problem comes into play whenever the structure of the table is altered because at that time the columns returned by this select are no longer the same as the columns contained in the table.

In addition, if the point of this function is only to perform the update then it would be better to eliminate from the select statement any columns that do not contribute to the update.

|||

Thanks alot for the reply. I'll give that a try.

(I think I screwed up a little because I posted this question multiple times. I kept getting a message that "the administrator may have deleted your post" and I couldn't find it on a search initially, so I kept on posting!)

The only thing that I didn't mention is that I have acmdtn_RECID as the primary key (It's an identy field that gets assigned when I do the import from the raw data, because the original raw data didn't have any primary keys).

Would that change your soloution any?

Thanks again.

|||

No, it will not really change this solution; however, you are correct in identifying that my test data would not be valid. It would be good to have an index based on extractno because this is what is used here for filtering the data.

Again, it would be good to eliminate the SELECT * syntax to pare down some of the data.

|||

Create a stored procedure to apply the UPDATE.

create PROCEDURE [dbo].[update_Current_acmdtn]

@.extractNum char(10)

AS

SET NOCOUNT ON;

with cte

as

(

SELECT

dbo.acmdtn.*,

row_number() OVER (partition BY id ORDER BY extractno, efctv_to_dt DESC, efctv_from_dt DESC, modify_ts DESC, acmdtn_RECID DESC) rn

FROM

dbo.acmdtn

WHERE

extractno > @.extractNum

)

update cte

set Active = 1

where rn = 1

return @.@.error

go

AMB

|||

Hunchback presents a good way of performing the update. I realized that I need to factor out a piece from my query so I amended my function similar to what Hunchback did. Also, I eliminated the SELECT * syntax:

alter function [dbo].[SelectCurrent_acmdtn]
( @.extractNum char(10)
)
returns table AS return
( select id,
efctv_from_dt,
efctv_to_dt,
modify_ts,
extractno,
Active,
acmdtn_RECID,
row_number() OVER
( partition BY id
ORDER BY extractno,
efctv_to_dt DESC,
efctv_from_dt DESC,
modify_ts DESC,
acmdtn_RECID DESC
) rn
FROM dbo.acmdtn
WHERE extractno > @.extractNum

)

go

update selectCurrent_Acmdtn (1)
set Active = 1
where rn = 1

select * from acmdtn

/*
id efctv_from_dt efctv_to_dt modify_ts extractno Active acmdtn_RECID
-- -- -- -- -- --
1 2007-01-01 00:00:00.000 2007-02-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 1
1 2007-02-01 00:00:00.000 2007-04-01 00:00:00.000 2007-01-15 00:00:00.000 2 1 2
1 2007-04-01 00:00:00.000 2008-01-01 00:00:00.000 2007-05-08 11:10:56.793 3 0 3
2 2007-01-01 00:00:00.000 2007-03-01 00:00:00.000 2007-01-01 00:00:00.000 1 0 4
2 2007-03-01 00:00:00.000 2007-07-01 00:00:00.000 2007-02-15 00:00:00.000 2 1 5
*/

Thanks, Hunchback. :-)

|||

Thanks alot guys,

I got it to work.