display SQL Server: row

Showing posts with label row. Show all posts

Monday, March 26, 2012

How do I know if a column is updated?

Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
JasonThis is a multi-part message in MIME format.
--020706030805090103030202
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
You could respond to the change in an UPDATE trigger. In the trigger
you can use the IF UPDATE()
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_7377.asp>
clause to determine if any particular column has been changed. For example,
CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable
FOR UPDATE AS
IF UPDATE(MyCol)
BEGIN
...
END
GO
where MyCol is a column in dbo.MyTable that you want to respond to
changes on.
--
*mike hodgson*
http://sqlnerd.blogspot.com
Jason Huang wrote:
>Hi,
>In SQL Server 2000, how do I know that a paricular column in a Table row is
>updated?
>e.g., in Customer(ID, Name, SSN, DoB, Address),
>the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
>where the '1234 Main Street' is updated.
>Thanks for help.
>
>Jason
>
>
--020706030805090103030202
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>You could respond to the change in an UPDATE trigger. In the
trigger you can use the <a
href="http://links.10026.com/?link=IF">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_7377.asp">IF
UPDATE()</a> clause to determine if any particular column has been
changed. For example, 
</tt>
<blockquote><tt>CREATE TRIGGER MyUpdateTrigger ON dbo.MyTable</tt> 
<tt>FOR UPDATE AS</tt> 
<tt> IF UPDATE(MyCol)</tt> 
<tt> BEGIN</tt> 
<tt> ...</tt> 
<tt> END</tt> 
<tt>GO</tt> 
</blockquote>
<tt>where MyCol is a column in dbo.MyTable that you want to respond to
changes on. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson 
<a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a>

</div>
 
 
Jason Huang wrote:
<blockquote cite="midu8m7SusVGHA.5592@.TK2MSFTNGP09.phx.gbl" type="cite">
<pre wrap="">Hi,
In SQL Server 2000, how do I know that a paricular column in a Table row is
updated?
e.g., in Customer(ID, Name, SSN, DoB, Address),
the (0001, John, 123456789,1980/01/02,'1234 Main Street'),
where the '1234 Main Street' is updated.
Thanks for help.
Jason
</pre>
</blockquote>
</body>
</html>
--020706030805090103030202--

How do I know if a column is updated?

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

Friday, March 23, 2012

How do I insert values into CE from a DataGridView row/rows?

Hi all... can someone tell me why this does not work and how to make it work?

SELECT SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM,
PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id,
create_date
FROM ProposalDetail
INSERT INTO temp
(
SQLKey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList,
PropDCost, PropDGMDollar, PropDGM, PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus,
create_timestamp, update_timestamp,update_originator_id, create_date
)
VALUES
(
'1', '3/20/2007 8:13:10 AM', 'SomeUser', 'b1', '4', '0761046', 'APP', 'Miscellaneous Options',
'54.000', '27.00', '27.00', '50.00', '54.00', '54.00', '54.00', '1', 'Active', '3/20/2007 8:13:10 AM', '3/20/2007 8:13:10 AM', '0',
'3/20/2007 8:13:10 AM'
)
WHERE (PropDNumb = 'b1') AND (PropDRevNumb = '1')

Thanks a ton!

figured it out and oddly enough this is solid and works great...

'SQL STATEMENT......

Dim conGridInsert As New SqlCeCommand

conGridInsert.CommandText = "INSERT INTO ProposalDetail" & _

"(SQLkey, CreateDateTime, Alias, PropDNumb, PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date)" & _

"SELECT SQLkey, CreateDateTime, Alias, PropDNumb, '" & ComboRevision.Text & "' as PropDRevNumb, PropDItem, PropDMfg, PropDCat, PropDList, PropDCost, PropDGMDollar, PropDGM, " & _

"PropDSellPrice, PropDXSellPrice, PropDItemTotal, PropDShipQty, PropDStatus, create_timestamp, update_timestamp, update_originator_id, " & _

"create_date" & _

" " & _

" FROM ProposalDetail ProposalDetail_1 " & _

" " & _

" WHERE (PropDNumb = '" & txtProposalNum.Text & "') AND (PropDRevNumb = '" & OldRevNumber & "')"

conGridInsert.Connection = CnGridInsert

CnGridInsert.Open()

conGridInsert.ExecuteNonQuery()

CnGridInsert.Close()

Wednesday, March 21, 2012

How do I get the value of a column in the last row of my table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Hard to get the last row in a relational database. Last means many things: last inserted, last in clustered index, last in another sort, last in a particular index, last in the data page.

Does your table have an Identity column, timestamp, CreatedDate that defaults to getDate()? You need something.

|||

In relational databases the data is by definition unordered, therefore the last data row entered is functionally the same as the first row entered.

That being said, if you have an inserted date/time column you could select from the table where the column equals the max(datetime) value in the table.

How do I get the value of a column in the last row of a table?

With out using @.@.identity or count(*) how do i retrieve a value in a column in the last row of my table.

here is the situation. ASP.net project has several sessions open. a user needs to get the value of a column in the last row inserted in a particular table.

Select CallID from Calls where 'it is the last row inserted'

thanks in advance.

you can email me at !cbmorton!@.!gmail.com!

Chris Morton

Define last row?

maybe this will do what you want but it is kind of dangerous because you might pick up the inserted row from another connection

Select Max(CallID) from Calls

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||i do want to pick up the last inserted row from another connection|||

There is no "last" row because SQL Server stores data as it comes in within its own storage schema. There is only a last row if you do a order of the query executed. There could be also a last *physical* row if the table includes a clustered index, which is physically ordered.

But if you want to have the last row in a resultsset, you have to order it backwards and get the TOP 1

e.g. SELECT TOP 1 SomeColumn From SomeTable Order by SomeOtherorthesamecolumn DESC

BTW, this is a public newsgroups, as long as you have MSN Alerts activated you will get a notice everytime a new answer arrives. Private communication should only be done if the thread is extended immensly due to details asking and answering back and forth, but also then the answer and the solution should be posted back here, to help other which might be in the same situation with a similar question.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

how do I get the uniqueidentifier of just inserted row?

Hello there!

it was a while since i studied SQL and that brings us to my problem...

I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().

later in the SP i need that uniqueidentifier value? how do I get it?

I tried this:


CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier = newid,
@.strHeader nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400),
@.strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @.uidArticleId,@.strHeader,@.strAbstract,@.strText,@.dt
Date,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAutho
rEmail,@.strKeywords)declare @.uidCategoryId uniqueidentifier
EXEC spGetCategoryId @.strCategoryName, @.uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@.uidArticleId, @.uidCategoryId)

But i get an error when I EXEC the SP like this:


EXEC spInsertNews
@.strHeader = 'Detta är den andra nyheten',
@.strAbstract = 'dn första insatt med sp:n',
@.strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@.dtDate = '2003-01-01',
@.dtDateStart = '2003-01-01',
@.dtDateStop = '2004-01-01',
@.strAuthor = 'David N',
@.strAuthorEmail = 'david@.davi.com',
@.strKeywords = 'nyhet, blajblaj, blaj'

the errormessage is: Syntax error converting from a character string to uniqueidentifier.

does anyone have a sulution to this problem?
Can I use something similar to the @.@.IDENTITY?
I will be greatful for any ideas...

thanks
/David, SwedenTry this

Declare @.seed int

set @.seed = @.@.Identity

return @.seed

Sam|||Hi,

Though you have default specified in your table as newid() , i would supress the default and generate a newid() in the procedure itself and force that in the Insert statement.

This way, you don't have to go back to the table to find out the last added newid() as you yourself are generating it in you proecure.

Regards,
Navneet|||I posted the same questioned and got back the following answer

or use ScopeIdentity. It returns the auto increment value in the current scope.

@.@.Identity can return a value from other tables.

Scope only returns what its in.|||thanks for the help... I solved it like this:

instead of having the SP recieve a parameter as uniqueidentifier
I created it inside the SP and gave it the value newid...

works fine, thanks|||::I posted the same questioned and got back the following answer
::
::or use ScopeIdentity. It returns the auto increment value in the current scope.
::
::@.@.Identity can return a value from other tables.

You may not have realized this - he is not using an identity field, so none of your solutions are relevant. I doubt it was teh same question, btw. YOu propably were using an identity field.|||The safest way is to use this T-SQL syntax after the INSERT query:

SET @.yourNewId = SCOPE_IDENTITY()|||::The safest way is to use this T-SQL syntax after the INSERT query:
::
::SET @.yourNewId = SCOPE_IDENTITY()

Really?

My documentation says that SCOPE_IDENTITY is for identity fields, not for GUID's.

Now, who is wrong? You or the documentation.|||oops, my mistake, read over the "GUID" part. I was thinking in int identity fields :-)

how do I get the uniqueidentifier of just inserted row?

Hello there!

it was a while since i studied SQL and that brings us to my problem...

I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().

later in the SP i need that uniqueidentifier value? how do I get it?

I tried this:

CREATE PROCEDURE spInsertNews
@.uidArticleId uniqueidentifier = newid,
@.strHeader nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400),
@.strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @.uidArticleId,@.strHeader,@.strAbstract,@.strText,@.dt Date,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAutho rEmail,@.strKeywords)

declare @.uidCategoryId uniqueidentifier
EXEC spGetCategoryId @.strCategoryName, @.uidCategoryId OUTPUT

INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@.uidArticleId, @.uidCategoryId)

But i get an error when I EXEC the SP like this:

EXEC spInsertNews
@.strHeader = 'Detta r den andra nyheten',
@.strAbstract = 'dn frsta insatt med sp:n',
@.strText = 'hr kommer hela nyhetstexten att st. Hr fr det plats 2000 tecken, dvs fler n vad jag orkar skriva nu...',
@.dtDate = '2003-01-01',
@.dtDateStart = '2003-01-01',
@.dtDateStop = '2004-01-01',
@.strAuthor = 'David N',
@.strAuthorEmail = 'david@.davi.com',
@.strKeywords = 'nyhet, blajblaj, blaj'

the errormessage is: Syntax error converting from a character string to uniqueidentifier.

does anyone have a sulution to this problem?
Can I use something similar to the @.@.IDENTITY?
I will be greatful for any ideas...

thanks
/David, SwedenNever mind...

i solved it.

Here's the working code...

CREATE PROCEDURE spInsertNews
@.strHeader nvarchar(300),
@.strAbstract nvarchar(600),
@.strText nvarchar(4000),
@.dtDate datetime,
@.dtDateStart datetime,
@.dtDateStop datetime,
@.strAuthor nvarchar(200),
@.strAuthorEmail nvarchar(200),
@.strKeywords nvarchar(400),
@.strCategoryName nvarchar(200) = 'nyhet'
AS
DECLARE @.uidArticleId uniqueidentifier
SET @.uidArticleId = newid

INSERT INTO tblArticles
VALUES( @.uidArticleId,@.strHeader,@.strAbstract,@.strText,@.dt
Date,@.dtDateStart,@.dtDateStop,@.strAuthor,@.strAutho
rEmail,@.strKeywords)

declare @.uidCategoryId uniqueidentifier
EXEC spGetCategoryId @.strCategoryName, @.uidCategoryId OUTPUT

INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@.uidArticleId, @.uidCategoryId)sql

How do I get the row number?

I have tryed to find a way to include the row number in a querry but without result.

I have a table select some rows ordered and now I will add a column called rank

How do I get the rownumber in the field rank ?

Thanks in advance

Jan OThe way I'd suggest assigning a row number to your resultset is to INSERT your resultset into a temporary table or table variable which has an IDENTITY column, something like this:


-- set up a table variable to hold the resultset
DECLARE @.myTable (Rank int identity(1,1) primary_key,
 ColumnA varchar(20),
 ColumnB varchar(20)
 )-- insert the resultset into the table variable
INSERT INTO
 @.myTable
(
 ColumnA,
 ColumnB
)
SELECT
 ColumnA,
 ColumnB
FROM
 someTable
ORDER BY
 someCriteria
-- return the resultset to the calling program in rank order, including the rank column
SELECT
 Rank,
 ColumnA,
 ColumnB
FROM
 @.myTable
ORDER BY
 Rank

Terri|||Thanks,

Have hoped not to do it so, a lot of other calculation dependences.

Jan|||Well, an alternative (which performs poorly) would be at the very bottom of this link:Returning a Row Number in a Query It involves using a subquery for every row in your resultset and could be horrendous performance-wise.

Can you just assign the rank in the front end?

Terri|||I agree with Terri. If possible, assign in the front end. I have used the temp table solution before, as well. It really depends on why you need to have the rownumber. If it is just for display purposes, it should be no problem to create on the front end when binding your resultset.|||Thanks again.

I have started to solve the "problem" in the front end.
I have a datagrid with paging so I have a few lines left.
Got it working whitout paging , but I had prefered asystem rownr from SQL.

But you cant get everything for free :)

Thanks
Jan

Monday, March 12, 2012

How do I get CanGrow to work for a textbox in a table?

I have a very basic table that I want to use as a subreport. It has only one row and one column, and it's textbox value is:

Fields!VendorType.Value & ": " & Fields!VendorName.Value & " " & Fields!VendorContactName.Value & IIF(Fields!VendorPhone.Value > " "," " & Fields!VendorPhone.Value,"")

The Data uses a parameter from the parent report to get the values, and there are usually several vendors for each report. The textbox in the row has it's 'CanGrow' property set to Yes, because sometimes these values are too long for one line. But when I run the report, it does not grow. it just truncates the value.

Any ideas? what can I do?

If you just render the table as a stand-alone report, do the textboxes grow? Which renderer are you using? Remember textboxes can only be grown vertically, not horizontally.|||

Well, I was working up some examples for you when I realized that I was not getting the data I thought I was. So, it is working. Sorry! But thanks.

how do I get a trigger just to copy the inserted row.

HI I have a trigger which I want to copy any rows in the MSmerge_history into a archieve table. I am using this trigger:

CREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp],

How this copy the entire contence of the table into the archive each time someone insert a row. How do I get it to only insert the row which had triggered the insert? EdCREATE TRIGGER History_replication ON
[dbo].[MSmerge_history]
FOR INSERT
AS
INSERT [dbo].[MSmerge_history_archive]
(
agent_id,
runstatus,
start_time,
[time],
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp] ,
updateable_row
)
SELECT
agent_id,
runstatus,
start_time,
[time] ,
duration,
comments,
delivery_time,
delivery_rate,
publisher_insertcount,
publisher_updatecount,
publisher_deletecount,
publisher_conflictcount,
subscriber_insertcount,
subscriber_updatecount,
subscriber_deletecount,
subscriber_conflictcount,
error_id,
[timestamp]
FROM inserted

Inserted is virtual table used by triggers.|||Excellent thanks I've got that working now, Ed

Friday, March 9, 2012

How do I find the max row size..?

Hi,

How do I find the max row size for a particular table?
This was the error I recieved while execting my proc with the relevant
i/p I need to:

"cannot sort a row of size 8192, which is greater than the allowable
maximum of 8094"

I also understand that the max bytesize of a row is 8060 bytes.But
whtz this 8094?

TIA,
SeethaSeetha (seethakn@.yahoo.com) writes:
> How do I find the max row size for a particular table?
> This was the error I recieved while execting my proc with the relevant
> i/p I need to:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094"
> I also understand that the max bytesize of a row is 8060 bytes.But
> whtz this 8094?

I don't know, but I would guess this is a about a worktable that SQL
Server sets up internal, and for such a table the limit might be somewhat
higher.

Rather than scrutinizing tables, you should probably look at the query
that gives the error.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||seethakn@.yahoo.com (Seetha) wrote in message news:<edf58070.0402162306.37ab77df@.posting.google.com>...
> Hi,
> How do I find the max row size for a particular table?
> This was the error I recieved while execting my proc with the relevant
> i/p I need to:
> "cannot sort a row of size 8192, which is greater than the allowable
> maximum of 8094"
> I also understand that the max bytesize of a row is 8060 bytes.But
> whtz this 8094?
> TIA,
> Seetha

Each datapage can actually 8192 bytes. This is because each kb is 1024
bytes. The Microsoft documentation specifies that a page header
contains 96 bytes of overhead. This is for keeping track of the page
within the system, kind of like a File Allocation Table on your hard
drive. This leaves 8096 bytes for data and row offsets (pg 247, Inside
SQL Server 2000). You get 8094 because each row has a 2 byte offset.
There is a decent article that goes into more detail at
"http://www.sqlservercentral.com/columnists/sjones/pagesize_printversion.asp"
if you're interested. Also, "Inside SQL Server 2000" by Kalen Delaney
is a great resource.

--Bryan

Monday, March 26, 2012

How do I know if a column is updated?

How do I know if a column is updated?

How do I know if a column is updated?

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

Friday, March 23, 2012

How do I insert values into CE from a DataGridView row/rows?

Wednesday, March 21, 2012

How do I get the value of a column in the last row of my table?

How do I get the value of a column in the last row of a table?

how do I get the uniqueidentifier of just inserted row?

how do I get the uniqueidentifier of just inserted row?

How do I get the row number?

Monday, March 12, 2012

How do I get CanGrow to work for a textbox in a table?

how do I get a trigger just to copy the inserted row.

Friday, March 9, 2012

How do I find the max row size..?

display SQL Server

Blog Archive

About Me