can we increment the column --autimaticallyis there any possiblity with tiggers

hi,
i am a beginner to ms sql server2000
i have a table
create table ddd (a int, b int)
by table structure is a b
now when i enter a value in b column suppose '2' in column b
bext time when i insert a value in the column a i have to get the value
in b as 3 is thi spossible with triggers
insert into gdg values (1,2)
a b
1 2
insert into gdg (a) values(2)
a b
2 3--> i have to get this 3 automatically
is there any method to get this
pls help me
satishis this homework?sql

Can we include a variable field value on the table header.

Can we include a variable field value on the table header.

I am doing a page break on every date change and also set the property of he table header to repeat on each page. By doing this ,I am able to get the table header on each page even on page break. I want the changed date to be displayed on the every page break and in the table header.

Please help.

You should be able to do this without issue. Assuming you have a field which you are pulling from that shows the date, you could do something like this as an expression in the table header.

="Report Header - " & Fields!Date.Value

Hope this helps.

Can we get the Next Row Value ?

In SSRS 2005 we have First,Last and Previous functions which get the related values based on the use. I want to compare with the NEXT row value and print it according to my requirement.

My question is How do I get the Next Row Value. Please Help.

Could be tricky, however try using a protected variable in the Code view... something like this (untested):

protected myprevValue as string

public function setString(myString as string) as string

myprevValue = myString
return ""

end function

public function getString() as string
return myprevValue

end function

Then you use a hidden text box with the expression =setString(myString)

If you have multiple values or want to lookup by row number you can use a dictionary object to store and retrieve them.

No guarantees on this and placement of the text boxes are key. I have not tried this with getting the Next value however it did work for doing things like lookups against data outside of the current dataset.

cheers,

Andrew

|||

Hello Andrew,

I tried the way you explained but I was able to get the previous row value only. Is there any other way wherein we can get the Next row's field value.

Please help,

|||

Looking into the future is tough.

You have to iterate through the set once to store the values in a lookup dictionary object, and then iterate again to retrieve. This becomes unmanageable with many fields... hopefully you're only looking up a couple of values here. I have not tried this rownumber method - I was using it to look up descriptive names for keys.

1st table - will contain the set function in a textbox expression passing in the row number and value. This table can be hidden.

=setValue(RowNumber(Nothing),myfield.value)

2nd table - will contain the get function passing in the row number.

=getValue(RowNumber(Nothing)

Code Below

public dictLookup as new System.Collections.Generic.Dictionary(Of String, String)

Function setValue(value as object, value2 as object) as object

dictLookup.Add(value, value2)

return value

End Function

Function getValue(value as object) as object

If dictLookup.ContainsKey(value)

return dictLookup(value)

else

return value

end if

End Function

Can we call functions that assign a value to a field

Can we call functions that assign a value to a filed. For example, we use
oracle sequence to assign a value to fields. Since SQLSvr does not have
sequences, can a function be used instead to assign a unique value in BCP
tool.JP
What is the version are you using?
Can you be more specific?
How about using an IDENTITY Properies?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:59C56298-7B6D-4A63-B2F7-EE4063CBA6B4@.microsoft.com...
> Can we call functions that assign a value to a filed. For example, we use
> oracle sequence to assign a value to fields. Since SQLSvr does not have
> sequences, can a function be used instead to assign a unique value in BCP
> tool.|||Hi,
The version used by us is SQL SERVER 2005 (9.0)
Since identity property can assign value to only one field, we are looking
for an alternative as we have to more than one field where the sequnece need
s
be set.
We are using the BCP.exe utiliy for inserting the flat file data into the
table.
So at the same time we need the sequence to be generated for some of the
fields in the Format file for the table columns.
"Uri Dimant" wrote:

> JP
> What is the version are you using?
> Can you be more specific?
> How about using an IDENTITY Properies?
>
>
> "JP" <JP@.discussions.microsoft.com> wrote in message
> news:59C56298-7B6D-4A63-B2F7-EE4063CBA6B4@.microsoft.com...
>
>

Can we allow NULL value to nested key?

Hi, all here,

I am having a problem with NULL value for the nested key of the nested table. Is it possible to allow ''null'' value to nested key? If so, how can we deal with that? As the column I am using for nested key contains null values, thus the process failed. Could please any expert here give me any advices for that?

Really need help for that.

With best regards,

Yours sincerely,

You cannot have a NULL value as nested key. The simple way to work around is to replace your nested table, in the data source view, with a named query which filters out the NULL columns. I think replacing the table with a query is an option if you right click on the table in the Data Source View Designer

|||

Hi, Bogdan, thank you very much for your guidance.

With best regards,

Yours sincerely,

Can VB ADO Command pass Null value to SP

Dear all,
When calling SP from VB client,It seems like can't pass null as
parameter value, Did anybody happen same issue?
Cheers,
Robert Song
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
See my reply to your other message and download the ADO stored
procedure add-in to generate the client code.
--Mary
On Wed, 29 Sep 2004 13:01:52 -0700, robert song
<robert.song@.modelsoftcorp.com> wrote:

>Dear all,
>When calling SP from VB client,It seems like can't pass null as
>parameter value, Did anybody happen same issue?
>Cheers,
>Robert Song
>*** Sent via Developersdex http://www.codecomments.com ***
>Don't just participate in USENET...get rewarded for it!

Can Use Fieids of diffirent datasets in a matrix ?

Can Use Fieids of diffirent datasets in a matrix ? SUM(Fields!a.value) is invalid?
Field a is in the other dataset!No, fields from multiple datasets are not allowed in a single dataregion. This would require a cross-dataset join, which is not supported. It is something we would like to add at some point in the future.|||The last message on this thread is from late 2005. Has this feature (cross-dataset join) being supported now? Thanks|||This relates to a similar post of mine...

Is it not possible to dynamically decide which dataset a table will use ?

sql

Can Use Fieids of diffirent datasets in a matrix ?

Can Use Fieids of diffirent datasets in a matrix ? SUM(Fields!a.value) is invalid?
Field a is in the other dataset!No, fields from multiple datasets are not allowed in a single dataregion. This would require a cross-dataset join, which is not supported. It is something we would like to add at some point in the future.|||The last message on this thread is from late 2005. Has this feature (cross-dataset join) being supported now? Thanks|||This relates to a similar post of mine...

Is it not possible to dynamically decide which dataset a table will use ?

Can Timestamp column value overflow?

Hi,
I am having a table with a timestamp column. The data in this table is
assumed to accumulate over a period of time. On an avarage every second a
row is inserted into this table.
I using time stamp column for two reasons:
- Ensure that the value of timestamp is ever incrementing.
- Time stamp value should be unique.
Issue: Is there any probability of timestamp getting overflow over a period
of time? If there is a overflow what is the behavior of SQL Server in such
cases?
If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique value for 8 bytes size column.
A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
Thanks in advance.
Pushkar> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
I don't remember the exact calculation, but I think you have to do 10,000
inserts a second for 122 years or something like that, to exceed the upper
bound of a BIGINT. So, you're probably safe. If you have anything other
than this table in the database, you're going to run out of available disk
space on the planet before you use up all of the unique, ever-incrementing
BIGINT values available to you.
Just my opinion.
A|||Consider using an identity column of datatype 4 byte int.
"Pushkar" <pushkartiwari@.gmail.com> wrote in message
news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is
> assumed to accumulate over a period of time. On an avarage every second a
> row is inserted into this table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a
> period of time? If there is a overflow what is the behavior of SQL Server
> in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
> incrementing unique value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
> the maximum range.
>
> Thanks in advance.
> Pushkar
>|||> Issue: Is there any probability of timestamp getting overflow over a period of time? If t
here is a
> overflow what is the behavior of SQL Server in such cases?
The way I remember it:
Even when this was internally a 6 byte size (some earlier version), you coul
d do something like 100
transactions per second for over 100 years before overflow. And it is now 8
byte.
And well before such a theoretic overflow would happen, SQL Server would log
an error and not permit
any more updates in the database, would such a (theoretic) overflow occur. T
his I recall reading in
Books Online, some earlier version.
OK, did some calculations. See code below. If you do 1 transaction per secon
d, it will "overflow"
after 146,235,604,338 years. Or, put another way, if you do 1 million transa
ctions per second, it
will overflow after 146,235 years. Say you plan for a life span of 100 years
for the database, you
would have to do 1 billion transactions per second. TSQL calculations:
DECLARE @.noPossibleValues numeric(38,0)
DECLARE @.secondsPerYear int
SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
SET @.secondsPerYear = 60*60*24*365
SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
YearsIfMillionTransactionsPerSecond
And here is some old text that I found on the subject:
"Maximum Value
--
Timestamps increase until the maximum value that can be stored in 6
bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached,
the database
will not permit any more updates.
A 935 warning message is generated when there are only 1,000,000
timestamp values left in the database.
The only way to start over is to copy out all of the data with BCP
and to re-create the database; dumping and restoring will not help.
This is not a major concern because at 100 transactions per second,
2**48 will not wrap for more than 100 years."
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Pushkar" <pushkartiwari@.gmail.com> wrote in message news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.p
hx.gbl...
> Hi,
> I am having a table with a timestamp column. The data in this table is ass
umed to accumulate over
> a period of time. On an avarage every second a row is inserted into this
table.
> I using time stamp column for two reasons:
> - Ensure that the value of timestamp is ever incrementing.
> - Time stamp value should be unique.
> Issue: Is there any probability of timestamp getting overflow over a perio
d of time? If there is a
> overflow what is the behavior of SQL Server in such cases?
> If there can not be overflow, then how SQL Server manages to generate ever
incrementing unique
> value for 8 bytes size column.
> A bigint Identity column gives arithmetic overflow error if value exceeds
the maximum range.
>
> Thanks in advance.
> Pushkar
>|||Oops, I did 2**62 instead of 2**64. Means my numbers were on the conservativ
e side... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message
news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
> The way I remember it:
> Even when this was internally a 6 byte size (some earlier version), you co
uld do something like
> 100 transactions per second for over 100 years before overflow. And it is
now 8 byte.
> And well before such a theoretic overflow would happen, SQL Server would l
og an error and not
> permit any more updates in the database, would such a (theoretic) overflow
occur. This I recall
> reading in Books Online, some earlier version.
> OK, did some calculations. See code below. If you do 1 transaction per sec
ond, it will "overflow"
> after 146,235,604,338 years. Or, put another way, if you do 1 million tran
sactions per second, it
> will overflow after 146,235 years. Say you plan for a life span of 100 yea
rs for the database, you
> would have to do 1 billion transactions per second. TSQL calculations:
> DECLARE @.noPossibleValues numeric(38,0)
> DECLARE @.secondsPerYear int
> SET @.noPossibleValues = POWER(CAST(2 AS numeric(38,0)), 62)
> SET @.secondsPerYear = 60*60*24*365
> SELECT @.noPossibleValues/@.secondsPerYear AS YearsIfOneTransactionPerSecond
>
> SELECT @.noPossibleValues/(CAST(@.secondsPerYear AS bigint) * 1000000) AS
> YearsIfMillionTransactionsPerSecond
>
> And here is some old text that I found on the subject:
> "Maximum Value
> --
> Timestamps increase until the maximum value that can be stored in 6
> bytes (2**48) is reached (8 bytes, nowadays). When this maximum is reached
, the database
> will not permit any more updates.
> A 935 warning message is generated when there are only 1,000,000
> timestamp values left in the database.
> The only way to start over is to copy out all of the data with BCP
> and to re-create the database; dumping and restoring will not help.
> This is not a major concern because at 100 transactions per second,
> 2**48 will not wrap for more than 100 years."
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Pushkar" <pushkartiwari@.gmail.com> wrote in message
> news:%23fMDNZ0UGHA.5044@.TK2MSFTNGP09.phx.gbl...
>|||Thanks a lot. Now I can safely use it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uurqw78UGHA.4660@.tk2msftngp13.phx.gbl...
> Oops, I did 2**62 instead of 2**64. Means my numbers were on the
> conservative side... :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:e4gVkw0UGHA.1160@.TK2MSFTNGP09.phx.gbl...
>sql

Can this be done??

Hi,
Can anyone give me some advice on how I can accomplish the following.

I have a table that has a value like the following "2010302NOV01222004"

The above value is made of of 3 distinct values
They are:
Employee Code - 2010302
Course Code - Nov012
Quarter and Year: 22004

In another table I have a set of values that relate to the middle part of the above value (Course Code), i would like to return the course name that relates to the course code from the other table.

I have been able to extract the coursecode using the following code but can't see how to pass vthe value to the courseType table to return my CourseName value.

<code>
SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12)
</Code
I would need to do this from a stored procedure.

Regards..
Peter.

You can join your Leave table to the CourseType table in order to pull the CourseName for every Training field value using something like the following:

SELECT Leave.Training, CourseType.CourseCode, CourseType.CourseName
FROM Leave JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

I'm not sure what you're trying to accomplish with the stored proc, so I won't go into details on using parameters, etc...

|||Jason,
Thanks for the reply, what i am trying to accopmlish is:

1. I have a table that lists all the leave employees take, this includes any training. I need all records returned from the leave table and those that have entries in the training filed of the leave table require the courseName to be returned from the Course type Table.

The training is listed in the leave table as described previously and I need to extract the training CourseID from that field, as you saw "2010302NOV01222004" is in the training field in the leave table.

I need to extract "NOV012" from that filed and get the coursename (Novell iChain 2.2) returned from the Course Type table, if the field is null then ignore it.

Hope this expalins better what i am trying to accomplish.

Regards..
Peter.|||

Try executing the following to see if it doesn't give you exactly what you asked for:

SELECT Leave.*, CourseType.CourseCode, CourseType.CourseName
FROM Leave LEFT JOIN CourseType
ON SUBSTRING(dbo.Leave.Training, 8, LEN(dbo.Leave.Training) - 12) = CourseType.CourseCode

|||Jason,
Thanks that has hit the nail on the head.. Exactly what i needed...

Regards..
Peter

Can this be done?

Hello,

I have been trying to figure out what seems to be a simple task:

I have a dataset (of 10 rows) and a text box. You are to display the value found in the 3rd row, first column of my dataset into that textbox. You may not alter the dataset.

I want to know how this can be done. Is there a way to loop through the dataset programmatically in a function?

I also tried this: The below code sets the value for MYTEXTBOX. If the value in “MyField” of my dataset = “0.0”, I want MYTEXTBOX to hold the value of “MyRelatedField” (so another field within the same row of my dataset). Otherwise, DO NOTHING!!!!!, but because all functions MUST return a value, I attempted to return the value of MYTEXTBOX—hence setting it equal to what it already is, but this give me #ERROR!

=iif(Fields!MyField.Value = "0.0",Fields!MyRelatedField.Value, ReportItems!MYTEXTBOX.Value)

I have read the wrox reporting service book from front to back, and what seems like pie is giving me a grief—any help, link or advice would be so much appreciated!...much thanks, John

It could be done through some custom code. Store the data set element access code in a little class and refer it from the report

http://msdn2.microsoft.com/en-us/library/ms155798.aspx

|||

Johnny606 wrote:

I have a dataset (of 10 rows) and a text box. You are to display the value found in the 3rd row, first column of my dataset into that textbox. You may not alter the dataset.

I want to know how this can be done. Is there a way to loop through the dataset programmatically in a function?

What about saving the first dataset into a multivalue report parameter (make it internal or hidden if you do not want users to see it)
Make a 2nd dataset that selects top 3 from the multivalue parameter.

The textfield value should then be something like =Last(Fields!<nameOfField>.Value, "<name of 1st dataset>")

That way you have not changed the first dataset and you get the correct value in your text box

I cannot test it at the moment so it might not work (I'm in the middle of installing the SP2 CTP and have a little time for surfing while it's doing its thing) :-)

Can this be Done?

Hi, just wondering if something like this can be done

procedure [dbo].[UpdateStringColumn]
@.KeyValue int,
@.Column varchar(50),
@.value float,
@.TableName varchar(30),
@.KeyName varchar(30)
as
begin
exec('UPDATE '+@.TableName+'
Set '+@.column+ ' = '+@.value+'
where '+@.TableName+'.'+@.KeyName+' = '+@.KeyValue+'' )
end

If I can get this to work I only need a few stored procedures to update any table column combination, one for each @.Value datatype

Well, it can be done. But it is a bad idea. Using dynamic SQL like this has lot of implications - maintainability, security risks, performance problems, manageability etc. See below link for some discussion:

http://www.sommarskog.se/dynamic_sql.html

Other things to ponder: Why do you want to have single SP to update data? And what happens if you want to update multiple columns in a table? What happens if the data type of each column in different? What happens if there is some function in the client that need to only update one column and another function that updates entire row? What happens if you need to perform additional validations for updates on some tables? What happens if there are some users who can only update some tables? How will you manage the permissions since with dynamic SQL you need to grant all the required permissions to users directly?

Can the "money" datatype be constrained to two decimal places?

I have a column that is a "money" datatype, which returns a four decimal
place value. It is a bit of a hassle to format this for display. Is there
anyway to default this to two decimal places?No, the datatype cannot be changed.
Look at CAST and CONVERT and you will see that there is a style that you can
use with CONVERT to return the value with 2 decimal places. (Or you could
stop using money and use DECIMAL if that meets your need.)
Russell Fields
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>|||No, change to decimal as Russell suggests... But the benefit of using money
is that the front-end application should be able to recognize and format the
data to 2 decimals using localization... Many folks need the 4 decimals for
increased accuracy, then round for display...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>

Can the "money" datatype be constrained to two decimal places?

I have a column that is a "money" datatype, which returns a four decimal
place value. It is a bit of a hassle to format this for display. Is there
anyway to default this to two decimal places?No, the datatype cannot be changed.
Look at CAST and CONVERT and you will see that there is a style that you can
use with CONVERT to return the value with 2 decimal places. (Or you could
stop using money and use DECIMAL if that meets your need.)
Russell Fields
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>|||No, change to decimal as Russell suggests... But the benefit of using money
is that the front-end application should be able to recognize and format the
data to 2 decimals using localization... Many folks need the 4 decimals for
increased accuracy, then round for display...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Top Gun" <nfr@.nospam.com> wrote in message
news:OyqMHnk8DHA.2540@.TK2MSFTNGP11.phx.gbl...
> I have a column that is a "money" datatype, which returns a four decimal
> place value. It is a bit of a hassle to format this for display. Is there
> anyway to default this to two decimal places?
>

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Can SQL Profiler Track a Field in a Table?

Greetings,
I have a field, "MyDate", that is being updated everytime a trigger
runs. MyDate is supposed to update with a getdate() value. However, i
look at the data, and i see some null values in there!
Is there a way I can use profiler to track when MyDate changes value,
and what value is updating MyDate?
I tried to have the trigger dump all MyDate values into a table, and i
have access to that data, but i still don't know WHY there are some NULL
values in there!
Thanks,
Don
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Profiler can track the execution of statements, but cannot be used to track
the actual value of variables and the like. If your trigger is supposed to
update the column, then I would suspect faulty trigger logic. Have you
considered disallowing null for the column? Another alternative is to
create a separate trigger, mark it to execute last, and do nothing but check
for NULL in the inserted/updated rows (with a corresponding
raiserror/rollback). This would at least allow you to figure out what is
causing the problem.
"don larry" <donlarry17@.hotmail.com> wrote in message
news:e$Dg$gcoEHA.868@.TK2MSFTNGP10.phx.gbl...
> Greetings,
> I have a field, "MyDate", that is being updated everytime a trigger
> runs. MyDate is supposed to update with a getdate() value. However, i
> look at the data, and i see some null values in there!
> Is there a way I can use profiler to track when MyDate changes value,
> and what value is updating MyDate?
> I tried to have the trigger dump all MyDate values into a table, and i
> have access to that data, but i still don't know WHY there are some NULL
> values in there!
> Thanks,
> Don
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Can someone explain why this stored proc does not work?

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).Value
How about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegr oups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
|||Hi
"jbonifacejr" wrote:

> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sA rtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sR etArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John

Can someone explain why this stored proc does not work?

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName = @.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
--
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName => @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John

Can someone explain why this stored proc does not work?

Sorry if this angers anyone. I'm posting here and to the .NET group. I
am unable to get a return value from a stored procedure in .NET using
the following Sproc and .NET code
Here is the code in my stored proc.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
-- Add the parameters for the stored procedure here
@.sArtist varchar(50),
@.sRetArtist bigint OUTPUT
AS
BEGIN
SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
@.sArtist)
END
I am calling it like this...
Dim iArtSer As Integer
Dim sProc As ADODB.Command
sProc = New ADODB.Command
sProc.CommandText = "sprocRetArtistSerial"
sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
sProc.ActiveConnection = oConn 'the connection is open and
global
sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
ADODB.DataTypeEnum.adVarChar,
ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
ADODB.DataTypeEnum.adBigInt,
ADODB.ParameterDirectionEnum.adParamOutput))
sProc.Execute()
iArtSer = sProc("@.sRetArtist).ValueHow about making your statement this:
SELECT @.sRetArtist = artSerial FROM tblArtists WHERE artName = @.sArtist
Also, try to post a bit more information than you did. Did the sproc
compile? Does it give you the right output if you execute it in query
analyzer (this would narrow the problem down to database or .NET stuff too)?
Do you get an error when calling it from .NET?
TheSQLGuru
President
Indicium Resources, Inc.
"jbonifacejr" <jbonifacejr@.hotmail.com> wrote in message
news:1177732180.342492.313570@.n59g2000hsh.googlegroups.com...
> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>|||Hi
"jbonifacejr" wrote:

> Sorry if this angers anyone. I'm posting here and to the .NET group. I
> am unable to get a return value from a stored procedure in .NET using
> the following Sproc and .NET code
>
> Here is the code in my stored proc.
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> ALTER PROCEDURE [dbo].[sprocRetArtistSerial]
> -- Add the parameters for the stored procedure here
> @.sArtist varchar(50),
> @.sRetArtist bigint OUTPUT
> AS
> BEGIN
> SET @.sRetArtist = (SELECT artSerial FROM tblArtists WHERE artName =
> @.sArtist)
> END
>
> I am calling it like this...
> Dim iArtSer As Integer
> Dim sProc As ADODB.Command
> sProc = New ADODB.Command
> sProc.CommandText = "sprocRetArtistSerial"
> sProc.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
> sProc.ActiveConnection = oConn 'the connection is open and
> global
> sProc.Parameters.Append(sProc.CreateParameter("@.sArtist",
> ADODB.DataTypeEnum.adVarChar,
> ADODB.ParameterDirectionEnum.adParamInput, 50, "Iron Maiden"))
>
> sProc.Parameters.Append(sProc.CreateParameter("@.sRetArtist",
> ADODB.DataTypeEnum.adBigInt,
> ADODB.ParameterDirectionEnum.adParamOutput))
> sProc.Execute()
> iArtSer = sProc("@.sRetArtist).Value
>
You don't say if you get an error and what it is?
Have you run the procedure with the given parameters and got a value back?
I think you would need
Set sProc = New ADODB.Command
There aer missing quotes in:
iArtSer = sProc("@.sRetArtist).Value
John

Can someone explain this strange value of @@ROWCOUNT?

I have noticed the following strange situation, but cannot find any explanation. The @.rows variable is 1 , even though the @.@.ROWCOUNT value just after the select statement is 0. (I have checked this by stepping through the SP).

update Products set ParentId = @.parent where ParentId = @.lastParentId
set @.err = @.@.ERROR
set @.rows = @.@.ROWCOUNT

However, if I set @.rows before @.err then the @.rows is correctly set to 0. (as in script below).

update Products set ParentId = @.parent where ParentId = @.lastParentId
set @.rows = @.@.ROWCOUNT
set @.err = @.@.ERROR

Can someone please explain why this is happenning?

In the first instance, @.@.ROWCOUNT is reporting on the statement immediately above it, which is the @.@.ERROR check. Please realize, however, that in your second instance, your @.@.ERROR check is now happening against your @.@.ROWCOUNT check.

To get around this issue, it's better practice to use a statement like this,making sure to reference @.@.ERROR and @.@.ROWCOUNT in this specific order:

SELECT @.err = @.@.ERROR, @.rows = @.@.ROWCOUNT

|||

Thanks for the explanation.

In your answer, why should the order of @.@.ERROR and @.@.ROWCOUNT matter, since both are now in 'one' statement, and therefore, both are reporting on what occurred in a previous statement? 'select @.err=@.@.ERROR, @.rows = @.@.ROWCOUNT' is after all a single statement.

|||

sun21170:

In your answer, why should the order of @.@.ERROR and @.@.ROWCOUNT matter, since both are now in 'one' statement, and therefore, both are reporting on what occurred in a previous statement? 'select @.err=@.@.ERROR, @.rows = @.@.ROWCOUNT' is after all a single statement.

Thank you, you are correct, the order does not matter!