How to tracking trigger

Hi guys,
I'm using trigger to write multiple files (5-6 file on multiple directories)
using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
I've found that sometime it does not output all the files it should be
written (almost the last file). So I'd like to check what happen on my
trigger. Could you please suggest me?
Thanks in advance,
Thana N.Thana
Run SQL Server Profiler to capture SP:StmtCompleted event.
"Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I'm using trigger to write multiple files (5-6 file on multiple
directories)
> using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> I've found that sometime it does not output all the files it should be
> written (almost the last file). So I'd like to check what happen on my
> trigger. Could you please suggest me?
> Thanks in advance,
> Thana N.
>|||Uri,
Is there anyway to track the historical for this? Because it is running on
the production and I cannot have a right to run Profiler for this (my sql
user is not in a group of sysadmin).
Thanks for reply,
Thana N.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ONJrz0S9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thana
> Run SQL Server Profiler to capture SP:StmtCompleted event.
>
> "Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
> news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> > Hi guys,
> >
> > I'm using trigger to write multiple files (5-6 file on multiple
> directories)
> > using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> >
> > I've found that sometime it does not output all the files it should be
> > written (almost the last file). So I'd like to check what happen on my
> > trigger. Could you please suggest me?
> >
> > Thanks in advance,
> > Thana N.
> >
> >
>

How to tracking trigger

Hi guys,
I'm using trigger to write multiple files (5-6 file on multiple directories)
using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
I've found that sometime it does not output all the files it should be
written (almost the last file). So I'd like to check what happen on my
trigger. Could you please suggest me?
Thanks in advance,
Thana N.Thana
Run SQL Server Profiler to capture SP:StmtCompleted event.
"Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> Hi guys,
> I'm using trigger to write multiple files (5-6 file on multiple
directories)
> using xp_cmdshell (invoke echo "" > path) on SQL Server 2000.
> I've found that sometime it does not output all the files it should be
> written (almost the last file). So I'd like to check what happen on my
> trigger. Could you please suggest me?
> Thanks in advance,
> Thana N.
>|||Uri,
Is there anyway to track the historical for this? Because it is running on
the production and I cannot have a right to run Profiler for this (my sql
user is not in a group of sysadmin).
Thanks for reply,
Thana N.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:ONJrz0S9DHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thana
> Run SQL Server Profiler to capture SP:StmtCompleted event.
>
> "Thana N." <n-thana-n0reply@.h0tmail.c0m> wrote in message
> news:eDiBAnS9DHA.2412@.TK2MSFTNGP09.phx.gbl...
> directories)
>

how to throw Custom Error instead of Default SQL Server Error

Hi guys!
i've very basic knowledge of DB as i'm a software developer i dont know
much about the DB other then writing Queries.
now, the problem is, i'm in a situation, where i need to throw a custom
error whenever some specific error occure while deleting the record
from the table.
the Scenrio is, like this:
i've table that has refencial intigrity constraint. it disallow to
delete the parent row if it is refered in the child table twice. this
is prefectly alright. all i want to do is, whenever such situation
occurs i want to throw my custom error instead of the sql server's
default error msg.
any idea,suggestion are most welcome.
Database : Sql Server 2000
thanks,
LuckyIf you are using the insert statement within a stored procedure, you
can use the @.@.ERROR variable and RAISERROR with your custom message.
Regards
Manisha Gandhi
lucky wrote:
> Hi guys!
> i've very basic knowledge of DB as i'm a software developer i dont know
> much about the DB other then writing Queries.
> now, the problem is, i'm in a situation, where i need to throw a custom
> error whenever some specific error occure while deleting the record
> from the table.
> the Scenrio is, like this:
> i've table that has refencial intigrity constraint. it disallow to
> delete the parent row if it is refered in the child table twice. this
> is prefectly alright. all i want to do is, whenever such situation
> occurs i want to throw my custom error instead of the sql server's
> default error msg.
> any idea,suggestion are most welcome.
> Database : Sql Server 2000
> thanks,
> Lucky|||thanks for your replay,
but the situation is, after i implemented, what u suggested, now it
throws 2 errors. one is my custom error and another is default SQL
Server error.
how can i stop sql server to throw it's own an error?
Lucky|||You cannot prevent a raised error from being returned to the client in SQL
2000. SQL 2005 provides a try/catch construct that can meet your
requirement.
Hope this helps.
Dan Guzman
SQL Server MVP
"lucky" <tushar.n.patel@.gmail.com> wrote in message
news:1164629710.750116.166010@.f16g2000cwb.googlegroups.com...
> thanks for your replay,
> but the situation is, after i implemented, what u suggested, now it
> throws 2 errors. one is my custom error and another is default SQL
> Server error.
> how can i stop sql server to throw it's own an error?
> Lucky
>

How to terminate the client connections manually?

Hi guys,

You know when restoring a database in SQL Server, exclusive access is required. With sp_who I can see the clients. But I don't know how to terminate the client connections easily. Usually I have to stop the server and restart it. I DO think it's too troublesome. Do you have any ideas? Is there any sp or command, or tool that may help? How do you deal with this issue?

Thanks!

Have a look at the kill command.|||Refer to Books Online, Topic: KILL Statement

How to tell SQL Server to use the default constraint name when adding one?

Hi guys,

I have this problem. I want to add a new primary key to a table but i want the name of the contstraint to be generated by the system. I have this TSQL code.

ALTER TABLE TableTest
ADD CONSTRAINT PRIMARY KEY (C1)


Reading the BOL, it says that if you don't supply a name for the constraint it generates one. But I get this error "Incorrect syntax near the keyword 'PRIMARY'".

If I add a name to the constraint, it works fine.
I'm using SQL Server 2000

Thanks
DarkneonAt least you have read and tried. Enclosed is a script that creates a table we use to trace deadlocks. The first created the primary key as it creates the table. The second alters the table after creation to add the primary key. Note that the constraint is named PK_tbl_trace_deadlock:



CREATE TABLE [dbo].[tblm_trace_deadlock] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[import_id] [int] NOT NULL ,
[BinaryData] [image] NULL ,
[DatabaseID] [int] NULL ,
[NTUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[EventSubClass] [int] NULL ,
[ObjectID] [int] NULL ,
[IndexID] [int] NULL ,
[IntegerData] [int] NULL ,
[ServerName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EventClass] [int] NULL ,
[Mode] [int] NULL ,
[DBUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tbl_trace_deadlock] PRIMARY KEY CLUSTERED
(
[row_id]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblm_trace_deadlock]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblm_trace_deadlock]
GO

CREATE TABLE [dbo].[tblm_trace_deadlock] (
[row_id] [int] IDENTITY (1, 1) NOT NULL ,
[import_id] [int] NOT NULL ,
[BinaryData] [image] NULL ,
[DatabaseID] [int] NULL ,
[NTUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NTDomainName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HostName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ClientProcessID] [int] NULL ,
[ApplicationName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoginName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPID] [int] NULL ,
[Duration] [bigint] NULL ,
[StartTime] [datetime] NULL ,
[EndTime] [datetime] NULL ,
[EventSubClass] [int] NULL ,
[ObjectID] [int] NULL ,
[IndexID] [int] NULL ,
[IntegerData] [int] NULL ,
[ServerName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EventClass] [int] NULL ,
[Mode] [int] NULL ,
[DBUserName] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblm_trace_deadlock] WITH NOCHECK ADD
CONSTRAINT [PK_tbl_trace_deadlock] PRIMARY KEY CLUSTERED
(
[row_id]
) ON [PRIMARY]
GO|||Hey tomh53,

Thanks for your input, but it's not quite what I am looking for. The script you submited creates a primary key with the name you supplied to it. I want to use the name that is automatically generated by the system. For example, in Entreprise Managed, when you set a primary key it generates a name for you. I want it to do the same thing, except the primary key is set programatically.

Here's what the BOL says:

"
constraint_name

Is the new constraint. Constraint names must follow the rules for identifiers, except that the name cannot begin with a number sign (#). If constraint_name is not supplied, a system-generated name is assigned to the constraint."

But like I said, it generates an error instead of a name for me.

Darkneon|||Why would you want to allow abiguity to exist when you have an opportunity to make something meaningful out of the constraint naming process. Allow me to quote from Inside SQL Server 200 by Kalen Delaney regarding system generated constraint names:


The advantage of explicitly naming your constraint rather than using the system-generated name is greater clarity. The constraint name is used in the error message for any constraint violation, so creating a name such as CUSTOMER_PK probably makes more sense to users than a name such as PK__customer__cust_i__0677FF3C. You should choose your own constraint names if such error messages are visible to your users.

The first two characters (PK) show the constraint typePK for PRIMARY KEY, UQ for UNIQUE, FK for FOREIGN KEY, and DF for DEFAULT. Next are two underscore characters, which are used as a separator. (You might be tempted to use one underscore to conserve characters and to avoid having to truncate as much. However, it's common to use a single underscore in a table name or a column name, both of which appear in the constraint name. Using two underscore characters distinguishes the kind of a name it is and where the separation occurs.)
Next comes the table name (customer), which is limited to 116 characters for a PRIMARY KEY constraint and slightly fewer characters for all other constraint names. For all constraints other than PRIMARY KEY, there are then two more underscore characters for separation followed by the next sequence of characters, which is the column name. The column name is truncated to five characters if necessary. If the column name has fewer than five characters in it, the length of the table name portion can be slightly longer.

And finally, the hexadecimal representation of the object ID for the constraint (68E79C55) comes after another separator. (This value is used as the id column of the sysobjects system table and the constid column of the sysconstraints system table.) Object names are limited to 128 characters in SQL Server 2000, so the total length of all portions of the constraint name must also be less than or equal to 128.|||But if you insist in shooting yourself in the foot, here it is:



CREATE TABLE [dbo].[bozo] (
row_id int IDENTITY (1, 1) NOT NULL ,
clownname varchar(20) NOT NULL ,
PRIMARY KEY CLUSTERED (row_id)
)
GO|||You should choose your own constraint names if such error messages are visible to your users.

This was the primary reason we did not mind using system generated names.
But it's ok now, I've talked with me collegue and we decided that we are going to give a name to the constraints.

By the way, the code snippet you provided is not good when you have a already existing table which is missing a primary key. Anyways, if someone knows the answers to my question, I am still interested. Otherwise, I close the discussion to my original question here.

Darkneon|||Look at the last two statements of the large block of code. The first one creates the table without a PK. The second one alters the table to add a PK. To take the small snippet, create the table, and then add the PK is:



CREATE TABLE [dbo].[bozo] (
row_id int IDENTITY (1, 1) NOT NULL ,
clownname varchar(20) NOT NULL
)
GO

alter table bozo
add
PRIMARY KEY CLUSTERED (row_id)
GO|||Thanks,

I tested it and it worked, with the name generated and everything. It is always good to know how to do stuff :D

Darkneon

How to suppress a group in reporting services and still show the detail for the group

Hi, Guys...
I have a problem in suppressing the group in reporting services. When i
set the visibility of the group with expression to false then the group
include the detail will be suppressed. How can i suppress the group
without suppress the detail of the group?
Thank you
Regards,
SumardiDid you set up 2 groups or just one?
What is the structure of your data?
regards,
Stas K.

how to sum up this thing?

hi guys.
in my select statement, i have this
CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid

the problem that i faced is, i need to sum up another time according to the year. the above statement will shoe the amount paid. i need to sum up the amount for each year. i've tried to use sum function, but it gives me error. Please help me to solve this problem. Thanks for all advise. I would appreciate it very much.

The simplified query....

(SELECT Convert(varchar,ISNULL(SUM(TC.amount),0)) FROM tb_payment AS TC
WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL) as AmountPaid

Here you can use join rather subquery, if you post the full query i may help you to tune..

|||
SELECT TOP 5 tr.year, CASE WHEN CONVERT(int,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) IS NULL THEN '0' ELSE CONVERT(varchar,(SELECT SUM(TC.amount) FROM tb_payment AS TC WHERE TC.transactionid = TA.TransactionID AND TC.deletedby IS NULL)) END AS AmountPaid, tr.edemedpoints, tr.adjustedpoints, tr.expiredpoints

FROM tb_pointtransactions AS TA
JOIN tb_transactionsummaries tr on tr.transactionid=TA.transactionid
JOIN tb_memberships m ON TA.membershipid = m.membershipid
JOIN tb_users u ON u.UserID = m.UserID
LEFT JOIN tb_salesstatus s ON s.id = TA.salesstatus
INNER JOIN tb_saletransactions AS TB ON TA.TransactionID = TB.TransactionID AND TA.deletedby IS Null
WHERE membershipid = '1' ORDER BY year

i'm really appreciate your help, ManiD. I'm still a newbie in programming field. With your help, my learning path will be wonderful. Thank you very much.

How to store/retrieve image/file in sql db

Hi Guys,
I want to employee details in one table along with his photograph and resume
.
How can I achieve this? I'm fresher and I don't have any basic idea about th
is.
Can somebody lead me to a worked out example.
If appropriate how to use WRITETEXT/READTEXT to achieve this task?
thanks in advance.
M.L.SrinivasYou can start here:
http://support.microsoft.com/kb/308042
or
You can use TextCopy.exe in the \bin folder.
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
-oj
"M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
news:9fa72100.0502102147.1a18787@.posting.google.com...
> Hi Guys,
> I want to employee details in one table along with his photograph and
> resume.
> How can I achieve this? I'm fresher and I don't have any basic idea about
> this.
> Can somebody lead me to a worked out example.
> If appropriate how to use WRITETEXT/READTEXT to achieve this task?
> thanks in advance.
> M.L.Srinivas|||I have gone threw this tutorial. But when I retrieve the image from the DB.
The image is only 1B, and I cannot open it. The filename is correct.
Whats wrong here?
-- Kenneth
"oj" wrote:

> You can start here:
> http://support.microsoft.com/kb/308042
> or
> You can use TextCopy.exe in the \bin folder.
> -- OJ: TEXTCOPY example
> -- Loading files into db &
> -- exporting files out to folder
> --
> --
> --TEXTCOPY IN
> --
> --create tb to hold data
> create table tmp(fname varchar(100),img image default '0x0')
> go
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass'
> --specify desired folder
> set @.path='c:\winnt'
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> --insert filenames into tb
> insert tmp(fname)
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> go
> select * from tmp
> go
> --
> --TEXTCOPY OUT
> --
> declare @.sql varchar(255),
> @.fname varchar(100),
> @.path varchar(50),
> @.user sysname,
> @.pass sysname
> set @.user='myuser'
> set @.pass='mypass,'
> --specify desired output folder
> set @.path='c:\tmp'
> set @.sql='md ' + @.path
> --create output folder
> exec master..xp_cmdshell @.sql
> --loop through and insert file contents into tb
> declare cc cursor
> for select fname from tmp
> open cc
> fetch next from cc into @.fname
> while @.@.fetch_status=0
> begin
> set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
> /d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
> set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
> print @.sql
> exec master..xp_cmdshell @.sql ,no_output
> fetch next from cc into @.fname
> end
> close cc
> deallocate cc
> set @.sql='dir ' + @.path + '*.bmp /c /b'
> exec master..xp_cmdshell @.sql
> go
> drop table tmp
> go
>
> --
> -oj
>
> "M.L.Srinivas" <mlsrinivas@.indiatimes.com> wrote in message
> news:9fa72100.0502102147.1a18787@.posting.google.com...
>
>

how to store my results to another table

Hi guys,

now i was able to get the results of the query i made. and the remaining problem now is how can i store the results to my database?

here's the my code:

protected void btnGenerate_Click(object sender, EventArgs e)
{
_con = new SqlConnection(database.evgConnection());
_comm = new SqlCommand("EV_usp_SearchNoticeLocation", _con);
_comm.CommandType = CommandType.StoredProcedure;
_comm.Parameters.AddWithValue("@.Locationid", ddlLocation.SelectedValue);
_adapt = new SqlDataAdapter(_comm);
DataSet objDataset = new DataSet();
_adapt.Fill(objDataset);
GridView1.DataSource = objDataset;
GridView1.DataBind();

foreach(DataRow dr in objDataset.Tables[0].Rows)
{
Response.Write(dr["Docownerid"].ToString());
}
}

how can i store all the Docownerid to another database table?

you can write simple query which will create a new table in sql server from the select query result.

SelectfiledName intoNewTableName fromExistingTable.

i think this will help you.

Sanjay

How to stop data redundancy

Hey guys...i've got a problem wif my stored procedures...in which my page keep repeating the same data...so to counter this problem i use SELECT DISTINCT instead of just SELECT..but the problem is when i change SELECT into SELECT DISTINCT...the page will not be display,page error...for your information my stored procures was auto generate from some security sofware...so can guys help me out with my code...

SELECT DISTINCT t.TargetID,
'V' RecordType,
t.TargetDNSName [Target DNS Name],
t.TargetIPAddress [Target IP],
t.TargetIPDisplay [Target IP Display],
t.TargetOSName,
t.TargetOSRevisionLevel,
v.SecChkID,
v.Severity,
sc.TagName [Tag Name],
sc.ChkBriefDesc [Tag Brief Desc],
sc.ChkName [Tag Chk Name],
CONVERT(NVARCHAR(4000),sc.ChkDetailDesc) [Tag Detail Desc],
CONVERT(NVARCHAR(4000),r.RemedyDesc) [Remedy],
o.ObjectID,
o.ObjectTypeDesc [Object Type Desc],
o.ObjectName [Object Name],
s.SensorDataID,
a.AttributeName,
a.AttributeValue,
NULL [Port],NULL [Service Name],NULL [Protocol]

FROM #Vulns v
INNER JOIN TargetHost t (NOLOCK)
ON v.TargetID = t.TargetID
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
INNER JOIN ObjectView o (NOLOCK)
ON v.ObjectID = o.ObjectID
LEFT OUTER JOIN SensorData1 s WITH (NOLOCK, INDEX(SensorData1_AK3))
ON v.ObservanceID = s.ObservanceID
AND s.Cleared = 'n'
LEFT OUTER JOIN SensorDataAVP a (NOLOCK)
ON s.SensorDataID = a.SensorDataID
AND a.AttributeValue IS NOT NULL
AND a.AttributeValue != ''
UNION ALL

In the FROM clause, please notice that there is an INNER JOIN statement that has no ON condition but was directly followed a LEFT OUTER JOIN as specified on this line:
INNER JOIN (SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)

I don't know if you have intended to do this, but this statement looks weird. Please try to review this clause.

|||

Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID

|||

selipeh wrote:

Thanks Alvin, i get u...but if u see it clearly...it's was already on at the fourth lines

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID

That is not the correct syntax for SQL Server as far as I amaware. You say you are getting an error. Please provide the errortext.|||thanks tmorton,actually there is no error...the code was fine...the only problem is when it comes to display process...it won't display anything just "page cannot be display"...but when i delete the DISTINCT it will display my .aspx page with a lot of data redundancy....|||What happens when you run your stored procedure through Query Analyzer?|||

i check in sql analyzer...but there was no error...just simply it won't display anythings,but when i delete DISTINCT...it will be fully functioning with data redundancy

|||Well, that makes no sense. The removal of the DISTINCT keyword inand of itself would not cause a query to stop returning results. My guess is that more changed than the removal of the DISTINCT keyword.|||

As far as I am aware the query isn't correct either.

INNER JOIN(SecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID)
ON v.SecChkID = sc.SecChkID
should be

INNER JOIN(SELECT * FROMSecurityChecks sc (NOLOCK)
LEFT OUTER JOIN Remedies r (NOLOCK)
ON sc.SecChkID = r.SecChkID) somename
ON v.SecChkID = sc.SecChkID

|||On top of that, I'm not sure what the UNION ALL is supposed to be doing at the end of the query.|||The UNION ALL operator is used in old style ANSI SQL JOINs and that is the reason for the duplicates because the UNION operator performs an implict distinct by removing duplicates but UNION ALL leaves duplicates in. I think the UNION ALL operator could be changed to one or two AND operators. Hope this helps.|||

I know what UNION ALL does, but it isn't syntatically correct as the last thing in a query. UNION ALL *MUST* be followed by a second query in which case it does caddre said. It combines the output of both queries and doesn't remove the duplicates.

Toss this in Query Analyzer:

SELECT '1'
UNION ALL

You get a syntax error.

|||You get syntax error because there is no UNION in you query but the poster's UNION ALL is at the end of 5 JOIN operations and 3 AND operators so SQL Server maybe ignoring some of the JOINS because the CONVERT sometimes makes tables UNION compatible and running UNION ALL instead. I am not saying the query cannot be modified but it works now so replace the UNION ALL with AND operators to start.|||

3rd syntax error:

INNER JOIN TargetHost t (NOLOCK)

should be

INNER JOIN TargetHost t WITH (NOLOCK)

Infact that's done a lot in the query, and they are all wrong. The query as posted won't run period, and should toss up an error. If it is not, it's only because with so many syntax errors you have thoroughly confused the T-SQL parser.

|||The poster said the code runs, you are running DML(data manipulation language) without DDL(data definition language). That is the reason SQL forums always ask for DDLs so when the person post some DDL you can run it in RedGate or BMC. I have TOAD here because I run Oracle.