net_address

Does anyone know who to translate the column net_address
from master..sysprocess into it's corresponding ip address?
Thanks,
David.use nbtstat -A ip address
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||I don't kon the ip address, all the information that I
have about these user is what I see in sysprocesses, and I
thought that I can find out the ip from the column
net_address in sysprocesses. Is there a way to do it?
>--Original Message--
>use nbtstat -A ip address
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||arp -a will map the mac addresses to IP address. However,
it's only local to the subnet so the clients would need to
be on the same subnet. In addition, if the users are going
through a web application, the client will be the web
server, not the users PC.
-Sue
On Thu, 18 Mar 2004 06:47:22 -0800, "David Velasco"
<anonymous@.discussions.microsoft.com> wrote:
>I don't kon the ip address, all the information that I
>have about these user is what I see in sysprocesses, and I
>thought that I can find out the ip from the column
>net_address in sysprocesses. Is there a way to do it?
>confers no rights.

nested update triggers

HI all,
I have a question about update triggers. I have a update trigger that
updates a 'dupdated' column to the current date and time. I this so that I
have an accurate record of when the row was updated. due to my mistake,
users where changing the dupdated column to cover their sloppiness.
My question is if I have an update command in an update trigger, will it
recursively call itself.
Thanks
RobertRobert Bravery wrote:

> HI all,
> I have a question about update triggers. I have a update trigger that
> updates a 'dupdated' column to the current date and time. I this so that I
> have an accurate record of when the row was updated. due to my mistake,
> users where changing the dupdated column to cover their sloppiness.
> My question is if I have an update command in an update trigger, will it
> recursively call itself.
> Thanks
> Robert
Not if you have set recursive triggers off (which is also the default
state):
ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
If you allow users to update tables directly then I wouldn't expect a
dependable audit trail. Triggers aren't the whole answer - a better
security implementation is the answer.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi,
Thanks David,
Yes, this table was created durring my learning process, I'm still learning
but was very very green at the time
Robert
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1139304916.973413.224930@.g44g2000cwa.googlegroups.com...
> Robert Bravery wrote:
>
I
> Not if you have set recursive triggers off (which is also the default
> state):
> ALTER DATABASE database_name SET RECURSIVE_TRIGGERS OFF;
> If you allow users to update tables directly then I wouldn't expect a
> dependable audit trail. Triggers aren't the whole answer - a better
> security implementation is the answer.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>sql

Nested Cursors

Morning everyone,

I have a sp that I've created that is to show me everyone table name and column name using nested cursors. However when I execute the procedure it doesn't show me the names, it just tells me the command completed successfully. Here is the code:

CREATE PROCEDURE uspSeeAllViews
AS
SET NOCOUNT ON
DECLARE @.strMessage VARCHAR(100)
DECLARE @.strColumn VARCHAR(100)
DECLARE @.strView VARCHAR(100)
DECLARE @.strCommand VARCHAR(250)

DECLARE crsViews CURSOR FOR

SELECT
name AS strView
FROM
sysobjects
WHERE
type = 'U'

OPEN crsViews
FETCH NEXT FROM crsViews INTO @.strView
WHILE @.@.FETCH_STATUS = 0 BEGIN

DECLARE crsColumns CURSOR FOR

SELECT
name AS strColumn
FROM
syscolumns
WHERE
name = @.strView

OPEN crsColumns
FETCH NEXT FROM crsColumns INTO @.strColumn
WHILE @.@.FETCH_STATUS = 0 BEGIN

PRINT @.strView + ':' + @.strColumn
FETCH NEXT FROM crsColumns INTO @.strColumn
END

CLOSE crsColumns
DEALLOCATE crsColumns

FETCH NEXT FROM crsViews INTO @.strView
END

CLOSE crsViews
DEALLOCATE crsViews

Thanks for looking, any ideas??Instead of
FETCH NEXT FROM crsViews INTO @.strView

try
FETCH crsViews INTO @.strView

I haven't used NEXT FROM before, and i think you might be skipping records.|||If you are in "grid" mode within Query Analyzer, you might want to check the messages tab.

Just FYI, you can combine both cursors into a single SELECT for much better performance and simplicity too.

-PatP|||Think of nesting cursors as the database equivalent of shoving a cigarette into a cigar. It ain't healthy.|||Why would you do this anyway??? I must be totally missing the boat on this one.

1. You created two cursors, which are horrible on processor efficiency to give you something that SQL Server already does for you with the

2. SELECT TABLE_NAME + ':' + COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
--What's the difference here?

If you ever do use cursors, which you should never do, you should have an overriding reason to do so.|||Never is a pretty strong word. I have five or six occasions when cursors were required...in the last ten years!

Cursors are a vb programmer's way of saying "Kilroy was here".|||Yeah, but when you have to take a column and execute its contents, then cursors are really nice. Other than that, I don't really have much use for them.

-PatP|||Yup. I've had to do that...about five or six times...

The one good thing about finding cursors in a client's code is that you can guarantee them that what you are developing will run faster than what they had. The assignment I'm on now is advising a client on moving OLAP onto a SQL Server platform. Their current system is based on DB2 and runs cobol code that creates and executes dynamic SQL loaded with cursor declarations.

Yeah, I think this is gonna be faster...

negative values...

calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

How could 'Cost' come negative..?|||How could 'Cost' come negative..?when the supplier pays you to take his product

next question: how can sale be 0?

answer: when you give your product away|||Sounds like an absolute value to me.

sale - abs(cost)|||calculating profit... how do I write an update query that will correct the gross profit calculated column for all negative qty transactions

Basically in the stored procedure that creates the table the query includes:

Profit = CASE Sale WHEN 0 THEN 0 ELSE (Sale - Cost) END,
which is wrong when sale and cost is negative the formula becomes
(-Sale + Cost)... I want it to be -(Sale-Cost) (where sale any cost ignores negative sign....

but i dont know to write this...any ideas?

Why would it be wrong? Sounds like simple accounting

My wife say I have a lot of negative values|||I worked my way around it... the profit was calculating correct, it was the Profit% that was wrong...soz:

UPDATE SALES
SET [Profit%] = [Profit%] * -1
FROM SALES
WHERE Qty < 0

So that when the profit% is negative when qty is negative... thanks :)|||So that when the profit% is negative when qty is negative... thanks :)you're welcome :)

you sell negative quantities?|||Those are called Returns!|||Those are called Returns!

yup :) :beer:|||Our sales monkeys are good at generating negative GP, too! :D

Negative values for RowModCtr col in sysobjects tbl

Could anyone, please, explain to me why I have negative values in
RowModCtr column in sysobjects table? I have tested that after I update
statistics the RowModCtr column is reset to 0. But why do I have
negative values in the first place? Thx."luke" <svatik@.hotmail.com> wrote in message
news:1116361464.490333.196030@.g49g2000cwa.googlegr oups.com...
> Could anyone, please, explain to me why I have negative values in
> RowModCtr column in sysobjects table? I have tested that after I update
> statistics the RowModCtr column is reset to 0. But why do I have
> negative values in the first place? Thx.

No idea; and I guess you mean sysindexes, not sysobjects? But Books Online
says that the value should be correct since the last time the statistics
were updated (see "sysindexes"), so you might want to look at
"sp_updatestats" and "update statistics". See also p.848 of "Inside SQL
Server 2000", which mentions rowmodctr, and says that when you update
statistics, it's set to zero.

If this doesn't help, I suggest you give some more details - what version of
MSSQL, why the sysindexes value is a problem for you etc.

Simon|||I too have seen negative numbers in the sysindexes table. After reasearching I found:

There is one row in SYSINDEXES for each index and statistics set (if the table does not have a clustered index then there is a row corresponding to the heap as well), and SQL Server 2000 maintains the amount of change for indexes and statistics separately (although this was not true in SQL Server 7.0). Throughout the INSERT, UPDATE, and DELETE queries performed in a table, the rowmodctr value is increased only for the index ID 0 or 1 (there is always only one on a table). For the rest of the indexes and statistics, it shows only a relative value that has to be added to the rowmodctr of the index 0 or 1 to get the true number of changed rows for this index.

For examples and more info go to the following site, the info is near the bottom of the article :)
http://msdn.microsoft.com/library/d...l/statquery.asp

Negative Numbers from Calculation

I am trying to get this calculation to work, but it keeps coming back with a negative number.

I think its something to do with making the

Column 1 & 2 are numeric figures then i calcating the datediff with another numeric figure. but i keep getting a negative answer? Any ideas?

Sum((isnull([Column1],0)) * ((isnull([Column2],0)) - cast(DATEDIFF(d,(isnull([date1],0)),(isnull([date2],0)))as float)/365.00)) as New Column

Here You can try this ...

Isnull(Sum([Column1]),0) * isnull([Column2],0)
- Isnull(ABS(DATEDIFF(d,[Date1],[Date2])),0)/365.00)) as New Column

You need not to apply Isnull inside the SUM function, by default the null values will be eliminated on aggergation.. But you can apply the ISNULL on the result of the SUM function..

Try to execute the following statement to get the 3 part of your expression which may help you where you missed your expression..

select

A = Isnull(Sum([Column1]),0) ,

B = isnull([Column2],0)),

C= Isnull(ABS(DATEDIFF(d,[Date1],[Date2])),0)/365.00))

as per your earlier expression the result = A * B - C => (A*B) - C is it correct?

or you want to achive A * (B-C) ... not clear buddy... try to find the result of the 3 expression and debug it..

|||

Can't seem to get this to work. Keep getting syntax error. Incorrect syntax near ')'.

|||

Try the following expressions...

Sum(isnull([Column1],0) * (isnull([Column2],0)
- Isnull(DATEDIFF(d,date1,date2),0)/365.00))

A= isnull([Column1],0) ,
B=isnull([Column2],0),
C=Isnull(DATEDIFF(d,date1,date2),0)/365.00

A= isnull([Column1],0) ,
isnull([Column2],0) - Isnull(DATEDIFF(d,date1,date2),0)/365.00

|||Spot on mate. Thanks. Worked a treat

Need your help to remove spaces in the column entries using SQL

Hi all,

I am new to these so plz never mind if this is funny.

here is my problem :

Table : moody

Column : Title

New column : Nospace

I have data in "Title" column of many rows which are normal sentence.

My requirment is to remove the "white space", +, | , ., / , ! @., $, %
etc special characters and fill it by ( hyphen) and put it in new
"Nospace" Column

Example :

I have : Hurray ! I won the Game

Needed : Hurray-I-won-the-Game

Can any body helpme in getting an SQL Query for this if possible

Thanks in AdvanceMake a list of all the "white space" characters.

Use REPLACE to change each of them to the dash character. You could
nest them, but it might be simpler to loop through them, changing one
white space character in each UPDATE.

Then reduce multiple dashes to one by using REPLACE(whatever, '--',
'-') until no more rows are updated.

Roy

On 21 Oct 2006 09:05:10 -0700, feucos@.gmail.com wrote:

Quote:

Originally Posted by

>Hi all,
>
>I am new to these so plz never mind if this is funny.
>
>here is my problem :
>
>Table : moody
>
>Column : Title
>
>New column : Nospace
>
>I have data in "Title" column of many rows which are normal sentence.
>
>My requirment is to remove the "white space", +, | , ., / , ! @., $, %
>etc special characters and fill it by ( hyphen) and put it in new
>"Nospace" Column
>
>Example :
>
>I have : Hurray ! I won the Game
>
>Needed : Hurray-I-won-the-Game
>
>Can any body helpme in getting an SQL Query for this if possible
>
>Thanks in Advance

|||Nesting the function calls is better because it gets done with ONE
update statement instead several.|||On 22 Oct 2006 05:12:00 -0700, "--CELKO--" <jcelko212@.earthlink.net>
wrote:

Quote:

Originally Posted by

>Nesting the function calls is better because it gets done with ONE
>update statement instead several.

That may very well prove to be an important advantage.

On the other hand, if you accept replacing them one at a time you
could put the characters to be replaced in a table. An advantage to
that would be that adding or subtracting from the set of characters
would be a simple INSERT or DELETE transaction. When nesting changes
to the list requires coding changes.

So, as so often is the case, It Depends.

Roy Harvey
Beacon Falls, CT|||On 21 Oct 2006 09:05:10 -0700, feucos@.gmail.com wrote:

(snip)

Quote:

Originally Posted by

>My requirment is to remove the "white space", +, | , ., / , ! @., $, %
>etc special characters and fill it by ( hyphen) and put it in new
>"Nospace" Column
>
>Example :
>
>I have : Hurray ! I won the Game
>
>Needed : Hurray-I-won-the-Game
>
>Can any body helpme in getting an SQL Query for this if possible

Hi feucos,

This is actually quite hard to achieve in straight SQL. Replacing the
various special characters with hyphens is easy, using a nested REPLACE
function - but that would leave you with 'Hurray--I-won-the-Game'.
Removing double hyphens is a lot harder, since there is no maximum
number of hyphens.

If you're on SQL Server 2005, I'd use a CLR user-defined function. Using
CLR means that yoou can use the power of regular expressions to do the
search and replace as quickly as possible.

For SQL Server 2000, you'll either have to use a T-SQL user-defined
function to loop over the characters in the string (but that will be
very slow), or use an awfully ugly but probably lots faster nested
REPLACE function like this:

REPLACE(REPLACE(REPLACE(...REPLACE(Title, ' ', '-'), '+', '-'), '|',
'-'), ....., '%', '-'), '---', '-'), '--', '-'), '--', '-'),
'--', '-')

This will handle series of up to 16 whitespace/special characters. Fill
in the appropriate amount of "REPLACE(" on the first series of dots, and
add "'#', '-')" for each special character to be replaced on the second
series of dots.

--
Hugo Kornelis, SQL Server MVP

Need Unicode question answered ASAP

I have a question regarding data types on Windows 2000 SVC Pk 4 & SQL Server
2000 Svc Pack 3a:
If I convert a char(20) Column to NChar, should I be using
Nchar (20) or Nchar(40)?
Thanks much.NCHAR(20). For Unicode data types nchar & nvarchar, the length refers to
the number of (2-byte) Unicode characters you can store, not the number
of bytes.
HTH,
Bart
--
Bart Duncan
Microsoft SQL Server Support
Please reply to the newsgroup only - thanks.
This posting is provided "AS IS" with no warranties, and confers no
rights.
Thread-Topic: Need Unicode question answered ASAP
thread-index: AcP66G4a/faD4EnrQkeJGb/105yR6Q==
X-Tomcat-NG: microsoft.public.sqlserver.server
From: "examnotes" <anonymous@.discussions.microsoft.com>
Subject: Need Unicode question answered ASAP
Date: Tue, 24 Feb 2004 07:11:08 -0800
Lines: 8
Message-ID: <D4B67A56-C3B4-4FC9-B1C0-AE6E88A01ADD@.microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.sqlserver.server
Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:330913
NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
X-Tomcat-NG: microsoft.public.sqlserver.server
I have a question regarding data types on Windows 2000 SVC Pk 4 & SQL
Server 2000 Svc Pack 3a:
If I convert a char(20) Column to NChar, should I be using
Nchar (20) or Nchar(40)?
Thanks much.

Need to sum columns

I need to create a view (I believe) that will take a column named
'accountbalance' for a group of records, and total (sum) up those entries...
can someone point me to a tutorial to learn how to do this? THanks!
Never mind.. I found a reference. thanks tho

Need to sum columns

I need to create a view (I believe) that will take a column named
'accountbalance' for a group of records, and total (sum) up those entries...
can someone point me to a tutorial to learn how to do this? THanks!Never mind.. I found a reference. thanks tho

Need to sum columns

I need to create a view (I believe) that will take a column named
'accountbalance' for a group of records, and total (sum) up those entries...
can someone point me to a tutorial to learn how to do this? THanks!Never mind.. I found a reference. thanks tho

Need to store duplicate values to DB

Hi,

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times. I tried to clear the memory that the same PKID has already been entered for which I wrote another SP.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spCRMPublisherSummaryUpdate](
@.ReportDate smalldatetime,
@.SiteID int,
@.DataFeedID int,
@.FromCode varchar,
@.Sent int,
@.Delivered int,
@.TotalOpens REAL,
@.UniqueUserOpens REAL,
@.UniqueUserMessageClicks REAL,
@.Unsubscribes REAL,
@.Bounces REAL,
@.UniqueUserLinkClicks REAL,
@.TotalLinkClicks REAL,
@.SpamComplaints int,
@.Cost int
)
AS
SET NOCOUNT ON

DECLARE @.PKID INT
DECLARE @.TagID INT

SELECT @.TagID=ID FROM Tag WHERE SiteID=@.SiteID AND FromCode=@.FromCode

SELECT @.PKID=PKID FROM DimTag
WHERE TagID=@.TagID AND StartDate<=@.ReportDate AND @.ReportDate< ISNULL(EndDate,'12/31/2050')
IF @.PKID IS NULL BEGIN
SELECT TOP 1 @.PKID=PKID FROM DimTag WHERE TagID=@.TagID AND SiteID=@.SiteID

DECLARE @.LastReportDate smalldatetime, @.LastSent INT, @.LastDelivered INT, @.LastTotalOpens Real,
@.LastUniqueUserOpens Real, @.LastUniqueUserMessageClicks Real, @.LastUniqueUserLinkClicks Real,
@.LastTotalLinkClicks Real, @.LastUnsubscribes Real, @.LastBounces Real, @.LastSpamComplaints INT, @.LastCost INT

SELECT @.Sent=@.Sent-Sent,@.Delivered=@.Delivered-Delivered,@.TotalOpens=@.TotalOpens-TotalOpens,
@.UniqueUserOpens=@.UniqueUserOpens-UniqueUserOpens,@.UniqueUserMessageClicks=@.UniqueUserMessageClicks-UniqueUserMessageClicks,
@.UniqueUserLinkClicks=@.UniqueUserLinkClicks-UniqueUserLinkClicks,@.TotalLinkClicks=@.TotalLinkClicks-TotalLinkClicks,
@.Unsubscribes=@.Unsubscribes-Unsubscribes,@.Bounces=@.Bounces-Bounces,@.SpamComplaints=@.SpamComplaints-SpamComplaints,
@.Cost=@.Cost-Cost
FROM CrmPublisherSummary
WHERE @.LastReportDate < @.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID

UPDATE CrmPublisherSummary SET
Sent=@.Sent,
Delivered=@.Delivered,
TotalOpens=@.TotalOpens,
UniqueUserOpens=@.UniqueUserOpens,
UniqueUserMessageClicks=@.UniqueUserMessageClicks,
UniqueUserLinkClicks=@.UniqueUserLinkClicks,
TotalLinkClicks=@.TotalLinkClicks,
Unsubscribes=@.Unsubscribes,
Bounces=@.Bounces,
SpamComplaints=@.SpamComplaints,
Cost=@.Cost,
TagID=@.TagID
WHERE ReportDate=@.ReportDate
AND SiteID=@.SiteID
AND TagPKID=@.PKID
END

ELSE

INSERT INTO CrmPublisherSummary(
ReportDate, SiteID, TagPKID, Sent, Delivered, TotalOpens, UniqueUserOpens,
UniqueUserMessageClicks, UniqueUserLinkClicks, TotalLinkClicks, Unsubscribes,
Bounces, SpamComplaints, Cost, DataFeedID, TagID)

VALUES(
@.ReportDate, @.SiteID, @.PKID, @.Sent, @.Delivered, @.TotalOpens, @.UniqueUserOpens,
@.UniqueUserMessageClicks, @.UniqueUserLinkClicks, @.TotalLinkClicks, @.Unsubscribes,
@.Bounces, @.SpamComplaints, @.Cost, @.DataFeedID, @.TagID)

SET NOCOUNT OFF

this is the one to clear:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[spCRMPublisherSummaryClear](
@.SiteID INT,
@.DataFeedID INT,
@.ReportDate SMALLDATETIME) AS

DELETE LandingSiteSummary
WHERE SiteID=@.SiteID AND ReportDate=@.ReportDate

but it doesnt seem to be working.

Please suggest.

avidyarthi:

I have written a stored procedure to store values from a report i generated to the DB. Now there is a column PKID which is the primary key but also needs to be repeated at times.

you cannot repeat a value in a column that is set as your primary key. If you need to repeat values in that column, then you need to remove its designation as your primary key.

|||

hey,

thanks i worked my way around it.