Sql Auto grow: value

Showing posts with label value. Show all posts

Tuesday, March 27, 2012

Automaticaly popluating Current Date in a Db Field

Hi,
I have a field in he db called LogDate. I have made it a timestamp type. Is
there a formula or a default value i can set for this field in the table
design view so that everytime a record is added, this field is automaticalll
y
populated with teh current date?
Thanks
--
pmudDid youy read anything in the SQL Server documentation about the timestamp
datatype? In spite of its poor name, it has nothing to do with date or
time.
Try the following instead:
CREATE TABLE dbo.MyStuff
(
StuffName NVARCHAR(32) PRIMARY KEY,
CreatedDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);
GO
INSERT dbo.MyStuff(StuffName) SELECT N'foo';
WAITFOR DELAY '00:00:01';
INSERT dbo.MyStuff(StuffName) SELECT N'bar';
GO
SELECT StuffName, CreatedDate FROM dbo.MyStuff;
DROP TABLE dbo.MyStuff;
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
> Hi,
> I have a field in he db called LogDate. I have made it a timestamp type.
> Is
> there a formula or a default value i can set for this field in the table
> design view so that everytime a record is added, this field is
> automaticallly
> populated with teh current date?
> Thanks
> --
> pmud|||First, you need to define the field as a datetime data type. The timestamp
data type is used for concurrency checking using optomistic locking and does
not represent an actual date and time.
I typically use a default value of CURRENT_TIMESTAMP to put the current date
and time in a field.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
> Hi,
> I have a field in he db called LogDate. I have made it a timestamp type.
> Is
> there a formula or a default value i can set for this field in the table
> design view so that everytime a record is added, this field is
> automaticallly
> populated with teh current date?
> Thanks
> --
> pmud|||Hi Aaron and Geoff,
Thanks for the reply. I made it a datetiem, and in the default value, I used
the function, GetDate() , and it worked. :)
pmud
"Geoff N. Hiten" wrote:

> First, you need to define the field as a datetime data type. The timestam
p
> data type is used for concurrency checking using optomistic locking and do
es
> not represent an actual date and time.
> I typically use a default value of CURRENT_TIMESTAMP to put the current da
te
> and time in a field.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:19D4287C-0ED6-401A-AE61-9B0F0FB5EDAB@.microsoft.com...
>
>|||Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
(cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely
equivalent for SQL Server, except using getdate() will provoke the "Wrath of
CELKO" (tm). :)
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"pmud" <pmud@.discussions.microsoft.com> wrote in message
news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
> Hi Aaron and Geoff,
> Thanks for the reply. I made it a datetiem, and in the default value, I
> used
> the function, GetDate() , and it worked. :)
>
> --
> pmud
>
> "Geoff N. Hiten" wrote:
>|||Hi geoff,
I had saved my tiable with GetDate() as the default value adn data type as
datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as suggested
by you, but when i click on Save , the default value automatically changes t
o
GetDate() . What do you suggest for this? Can this be a problem?
Thanks
--
pmud
"Geoff N. Hiten" wrote:

> Good. Just FYI, getdate() is the T-SQL specific function. The ANSI
> (cross-platform) standard is CURRENT_TIMESTAMP. They are absolutely
> equivalent for SQL Server, except using getdate() will provoke the "Wrath
of
> CELKO" (tm). :)
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "pmud" <pmud@.discussions.microsoft.com> wrote in message
> news:996A9A56-738B-4C38-8BE3-F89BBB68BBD2@.microsoft.com...
>
>|||> I had saved my tiable with GetDate() as the default value adn data type as
> datetime. Now I changed teh default value to CURRETNT_TIMESTAMP as
> suggested
> by you, but when i click on Save , the default value automatically changes
> to
> GetDate() . What do you suggest for this? Can this be a problem?
This is Enterprise Manager playing tricks on you. My suggestion is to stop
using Enterprise Manager for these things, use Query Analyzer and learn the
T-SQL equivalent(s).
A|||Ok. Thanks Aaron. I will try through Quey Analyzer.
--
pmud
"Aaron Bertrand [SQL Server MVP]" wrote:

> This is Enterprise Manager playing tricks on you. My suggestion is to sto
p
> using Enterprise Manager for these things, use Query Analyzer and learn th
e
> T-SQL equivalent(s).
> A
>
>|||> Ok. Thanks Aaron. I will try through Quey Analyzer.
If you use Enterprise Manager to "verify" be aware that it may still want to
convert CURRENT_TIMESTAMP to GETDATE().

Thursday, March 22, 2012

automatical calculation of a field value

Sunday, March 11, 2012

Automatic Email subject

Good Morning,

I have created a varible in a report which displays as a text box i.e contains data like "LWD Apps 450" where the value will change each day. The report is emiled to a list of users, is there a way to automatically enter this value so that it is in the subject bar when the email is automatically sent?

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

The only way I can think to modify the subscription on the fly would be to each day programmatically create a new subscription with a subject. Here's an msdn article with sample code:
http://msdn2.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createsubscription.aspx

In the code, you would perform whatever operation you needed to get the same information that the RDL is getting. Then instead of this line:
extensionParams(4).Value = "@.ReportName was executed at @.ExecutionTime"
you would use this line:
extensionParams(4).Value = "LWD Apps " & variableThatIJustRetrieved

You would set the report to run once and the time to run right then (or a minute in the future). You would also want to delete the subscription after it has run, so that you would have a clean slate for tomorrow's run.

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Automatic Email subject

Good Morning,

Thanks in advance.

Steve

Hi Steve,

The entry form for subscriptions only allows for two variables (@.ReportName and @.ExecutionTime).

It's definitely kludgy though. Does anyone else have any thoughts?
-Jessica

Thursday, March 8, 2012

Automated testing | How to "force" GETDATE() function to return specific value?

Hello,

Our QA team have running a lot of test scripts (for automated regression
testing), they run them on the different databases (Oracle/MS SQL).
Several of those tests are dependent on the current date/time. In order to
be
able to use them efficiently, we changed the current date/time on the QA
database server to a specific date/time before starting the scripts, so we
are sure the test scripts always run in the same environment.

Resetting the date/time of the database server gives us more and more
problems (OS problems, backup/ virusscan, ...).

It is possible to fix the problem with SYSDATE function on Oracle by setting
FIXED_DATE init parameter.

Is it possible to 'change' the current date/time on 'database' level,
instead of on OS level for MSSQL2000?
Do you know other means to do such things?

Thanks in advance,

Konstantin"Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> Hello,
> Our QA team have running a lot of test scripts (for automated regression
> testing), they run them on the different databases (Oracle/MS SQL).
> Several of those tests are dependent on the current date/time. In order to
> be
> able to use them efficiently, we changed the current date/time on the QA
> database server to a specific date/time before starting the scripts, so we
> are sure the test scripts always run in the same environment.
> Resetting the date/time of the database server gives us more and more
> problems (OS problems, backup/ virusscan, ...).
> It is possible to fix the problem with SYSDATE function on Oracle by
setting
> FIXED_DATE init parameter.
>
> Is it possible to 'change' the current date/time on 'database' level,
> instead of on OS level for MSSQL2000?
> Do you know other means to do such things?
> Thanks in advance,
> Konstantin

I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
return the same value every time it's called? If so, I don't believe there's
any way to do this in SQL Server. You could create your own function called
dbo.getdate(), and use that, but then it wouldn't be a valid test of your
'real' code.

In any case, it's not clear why you would want to always test with the same
datetime value - you wouldn't prove anything except that your code works
with one particular datetime, and that probably isn't desirable. It might be
better to put some work into establishing what the test results should be
for a given datetime input value, and validate your code that way.

If I've misunderstood, or if this doesn't help, perhaps you can clarify
exactly what you want getdate() to return, and how you want to use it.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:3fc25218$1_3@.news.bluewin.ch...
> "Konstantin Zakharenko" <kzakharenko@.infopulse.com.ua> wrote in message
> news:bpt1s9$ctp$1@.snoopy.infopulse.com.ua...
> > Hello,
> > Our QA team have running a lot of test scripts (for automated regression
> > testing), they run them on the different databases (Oracle/MS SQL).
> > Several of those tests are dependent on the current date/time. In order
to
> > be
> > able to use them efficiently, we changed the current date/time on the QA
> > database server to a specific date/time before starting the scripts, so
we
> > are sure the test scripts always run in the same environment.
> > Resetting the date/time of the database server gives us more and more
> > problems (OS problems, backup/ virusscan, ...).
> > It is possible to fix the problem with SYSDATE function on Oracle by
> setting
> > FIXED_DATE init parameter.
> > Is it possible to 'change' the current date/time on 'database' level,
> > instead of on OS level for MSSQL2000?
> > Do you know other means to do such things?
> > Thanks in advance,
> > Konstantin
> I don't know what FIXED_DATE does in Oracle - does it force SYSDATE to
> return the same value every time it's called? If so, I don't believe
there's
> any way to do this in SQL Server. You could create your own function
called
> dbo.getdate(), and use that, but then it wouldn't be a valid test of your
> 'real' code.
> In any case, it's not clear why you would want to always test with the
same
> datetime value - you wouldn't prove anything except that your code works
> with one particular datetime, and that probably isn't desirable. It might
be
> better to put some work into establishing what the test results should be
> for a given datetime input value, and validate your code that way.
> If I've misunderstood, or if this doesn't help, perhaps you can clarify
> exactly what you want getdate() to return, and how you want to use it.
> Simon

Thank you for replay.

Yes. You are right. The FIXED_DATE lets you set a constant date that SYSDATE
will always return instead of the current date.

The main problem is that if you take a fixed test database, and run for
example a report, the output of that report can be (very) different if you
run this report today vs. the run you will do tomorrow vs. ... Other typical
example: our application (financial one) reacts differently if you want to
create some entities with dates in the past (different execution flow).

We are using Rational Robot in the automated testing. It validates that all
system reaction and outcome is identical to the reference run (verification
points). If the 'current date/time' is changed since the 'reference' run,
you get a lot of (unnecessary) errors/warnings and failed verification
points. That's why we need to run test scripts with specific datetime value.

Of course, automated tests are not intended to replace all other tests,
their purpose to supplement another tests. Automated test scripts are mainly
used for regression testing. Since it is very difficult and time-consuming
to make Robot test scripts time independent it is acceptable for us to
execute tests with fixed datetime.

Unfortunately, calling of new dbo.getdate() function will cause modification
of application. It is time-consuming and task. That's why our main goal is
to solve the problem by means of Oracle/MSSQL parameters. Moreover, your
solution with dbo.getdate() will not work in a case when we need correct
datetime (not fixed).

CREATE FUNCTION dbo.getdate()
RETURNS datetime AS
BEGIN
-- return cast ('2003-01-01' as datetime) -- It is OK.
return getdate() -- It is NOT OK.
END

The GETDATE() is a nondeterministic function and it is not allowed to use it
in user-defined functions.

If we will not solve the problem by configuring MSSQL database (I think it
is most likely) our solution will be to create the following view:

CREATE VIEW v_nondeterministic AS SELECT getdate() AS getdate;

And to use SELECT getdate FROM v_nondeterministic where required.

Best regards,

Konstantin

Saturday, February 25, 2012

automagical calculation of a field value

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

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

Autoincrement in varchar value

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

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

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

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
Check out SCOPE_IDENTITY() in the BOL.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***
|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***Check out SCOPE_IDENTITY() in the BOL.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.codecomments.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.

> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.codecomments.com ***

Autoincrement ID as return value

Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.developersdex.com ***Check out SCOPE_IDENTITY() in the BOL.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
Hi,
what is the best way for insert some row to table and return ID of that
row, which is autoincrement?
Is it necessery lock table/row, or transaction is enough good solution,
or..?
Thanks,
Jovo
*** Sent via Developersdex http://www.developersdex.com ***|||> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
Generated identity values are often returned back to applications with
SELECT SCOPE_IDENTITY(). An stored procedure output parameter that returns
SCOPE_IDENTITY() is another method, which is handy if you need to use the
value in Transact-SQL scripts.
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
The assigned value is visible only within the current session scope so you
don't need to be concerned with insert concurrency.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jovo Mirkovic" <nospam@.sezampro.yu> wrote in message
news:OIdGb%23EKIHA.2268@.TK2MSFTNGP02.phx.gbl...
> Hi,
> what is the best way for insert some row to table and return ID of that
> row, which is autoincrement?
> Is it necessery lock table/row, or transaction is enough good solution,
> or..?
> Thanks,
> Jovo
> *** Sent via Developersdex http://www.developersdex.com ***

Monday, February 13, 2012

Auto Reload of report on parameter selection?

this might be a stupid question.

I have a parameter which has a default value set to it. when a second value is selexcted is it necessary to click the view report button.

Could it be set to auto reload on dropdown selection.

I am using reporting Services 2000

Thanks.

Hey guys,

Would there be a runtime load function available for the above problem.

thanks

Sunday, February 12, 2012

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?
You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:

>How do I do to give a column an integer type and setting it to auto increme
nt
>so that I don't have to give this value every time I insert something in th
e
>table?

Auto increment

How do I do to give a column an integer type and setting it to auto incremen
t
so that I don't have to give this value every time I insert something in the
table?Hi
See an IDENTITY property
CREATE TABLE #Test
(
col1 INT NOT NULL IDENTITY(1,1),
col2 CHAR(1)
)
INSERT INTO #Test (col2) VALUES ('A')
"Joachim" <Joachim@.discussions.microsoft.com> wrote in message
news:319993D2-2CE4-47BE-A8C7-BB2064E6EFB0@.microsoft.com...
> How do I do to give a column an integer type and setting it to auto
increment
> so that I don't have to give this value every time I insert something in
the
> table?

Auto increment

How do I do to give a column an integer type and setting it to auto increment
so that I don't have to give this value every time I insert something in the
table?You might want to read about identity columns on the BOL.
Aramid
On Wed, 6 Apr 2005 02:59:08 -0700, "Joachim"
<Joachim@.discussions.microsoft.com> wrote:
>How do I do to give a column an integer type and setting it to auto increment
>so that I don't have to give this value every time I insert something in the
>table?

auto increase text!

Hi,
I have an initial value like this:
'TEST0001'
I want to write a sql script that will automatically take that initial value
and keep adding 1 to it and insert into a table for me until it get to
'TEST9999'.
my table should now store:
TEST0001
TEST0002
...
...
TEST0010
TEST0011
...
...
TEST9999
How do I do this?
Thanks,
Tom ddeclare @.seed int
set @.seed = 1
while @.seed < 10000
begin
insert into tableName select 'TEST' + right('000' + cast(@.seed as
varchar(4)), 4)
set @.seed = @.seed + 1
end
"tom d" <tomd@.discussions.microsoft.com> wrote in message
news:1DB51C59-3F93-476E-91CE-6E7B4C7E7259@.microsoft.com...
> Hi,
> I have an initial value like this:
> 'TEST0001'
> I want to write a sql script that will automatically take that initial
> value
> and keep adding 1 to it and insert into a table for me until it get to
> 'TEST9999'.
> my table should now store:
> TEST0001
> TEST0002
> ...
> ...
> TEST0010
> TEST0011
> ...
> ...
> TEST9999
> How do I do this?
> Thanks,
> Tom d
>

Friday, February 10, 2012

Auto generating numeric column

Hi There,

I want to create a column that usto numbers upto a specified value, and the resets. I've tried using the identity column and then using DBCC CHECKIDENT, but this doesnt doesn't have the desired affect. Here's an example of what I'm after.

AutoRow
1
2
3
4 --- reset
1
2
3
4 ---reset

Any help would be great,

many thanks

Stuart

An indentity field is going to require unique values, so reseting it isnt going to work, your going to throw an SQL Exception.

Are you trying to accomplish this just on the SQL server, or can you do it as part of your application?

|||Unfortuanatley it needs to be done on the server|||

Check this sample to see whether it helps:

DROP TABLE tbl_testIden
go

CREATE TABLE tbl_testIden (id int identity(1,1), name sysname)
GO

CREATE TRIGGER trg_tsetIden ON tbl_testIden FOR INSERT,UPDATE
AS
IF (IDENT_CURRENT('tbl_testIden')>=4)
DBCC CHECKIDENT('tbl_testIden',RESEED,0)
GO

DECLARE @.i INT
SET @.i=1
WHILE (@.i<100)
BEGIN
INSERT INTO tbl_testIden(name) SELECT 'Person#'+CONVERT(VARCHAR(8),@.i)
SET @.i=@.i+1
END
go
SELECT * FROM tbl_testIden

|||

Hi There,

I managed to get it working using the Row_Number function. It has some very useul derivetives also.

Row_Number simply creates and incremental list which is reset when thepartiation by value changes.

ROW_NUMBER()OVER(PARTITIONBY ReqPointIdORDERBY ReqPointIdDESC)AS RowGroup

I'll look at your approach as it alows me to specify and upper limit.

Many thanks for your time

Regards

Stuart

|||

You are looking for a combination of ROW_NUMBER and %, but without more of the query, I can't help you. Generically...

SELECT (RowGroup % 4)+1 As AutoGen, more columns here

FROM (

SELECT ROW_NUMBER() OVER (something) AS RowGroup, more columns here

) t1

You may even be able to combine the two, but I'm not familiar enough with ROW_NUMBER to say that would work, but if it does... Then...

SELECT (ROW_NUMBER() OVER (something) % 4)+1 AS RowGroup, more columns here

FROM somewhere

should work as well. I think most people (myself included) were confused because you said you wanted an autogenerated column. That tends to lead people to believe you are speaking of a table column, not a field in a resultset.

Tuesday, March 27, 2012

Automaticaly popluating Current Date in a Db Field

Thursday, March 22, 2012

automatical calculation of a field value

automatical calculation of a field value

automatical calculation of a field value

Sunday, March 11, 2012

Automatic Email subject

Automatic Email subject

Thursday, March 8, 2012

Automated testing | How to "force" GETDATE() function to return specific value?

Saturday, February 25, 2012

automagical calculation of a field value

Autoincrement in varchar value

Autoincrement ID as return value

Autoincrement ID as return value

Autoincrement ID as return value

Monday, February 13, 2012

Auto Reload of report on parameter selection?

Sunday, February 12, 2012

Auto increment

Auto increment

Auto increment

Auto increment

auto increase text!

Friday, February 10, 2012

Auto generating numeric column

Sql Auto grow

Blog Archive

About Me