Need to sum columns: order

Showing posts with label order. Show all posts

Wednesday, March 28, 2012

Network Configuration...

I am not able to view the Network Configuration window.
What special permission(s) do I need in order to view the
information? I need to find out what port my instance is
on.Typically, the admin on the machine would be viewing/chaning the ports SQL
is listening on.
You need to be able to read the following registry key;
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLServer\SuperSocketNet
Lib\Tcp
After SQL Server starts, you can determine what port it is using by
reviewing the SQL Errorlogs and the NT Application Eventlogs.
Also, running netstat -an will give you the port information as well.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you very much! I found my port information. I do
have another question. Do you know if there is a way to
change the port to the default port 1433? Do you see any
problems with doing this?

quote:

>--Original Message--
>Typically, the admin on the machine would be

viewing/chaning the ports SQL

quote:

>is listening on.
>You need to be able to read the following registry key;
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer\MSSQLSe

rver\SuperSocketNet

quote:

>Lib\Tcp
>After SQL Server starts, you can determine what port it

is using by

quote:

>reviewing the SQL Errorlogs and the NT Application

Eventlogs.

quote:

>Also, running netstat -an will give you the port

information as well.

quote:

>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

confers no rights.

quote:

>
>.
>

|||Question:
Do you know if there is a way to
change the port to the default port 1433? Do you see any
problems with doing this?
Answer:
You can use the Server Network Utility to change the port. The only
problem with doing this is that then you also would need to modify each
client to connect to the new port. All clients will by default send
traffic to 1433. If you change this, then each client would need an alias
created to reflect the change, or you would need to modify the connection
string. Ex.
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=False;User ID=myuser;Initial Catalog=Northwind;Data
Source=MySQLServerName,4500;Network Library=DBMSSOCN
(Where 4500 is the new port)
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thank you!

quote:

>--Original Message--
>Question:
>Do you know if there is a way to
>change the port to the default port 1433? Do you see

any

quote:

>problems with doing this?
>Answer:
>You can use the Server Network Utility to change the

port. The only

quote:

>problem with doing this is that then you also would need

to modify each

quote:

>client to connect to the new port. All clients will by

default send

quote:

>traffic to 1433. If you change this, then each client

would need an alias

quote:

>created to reflect the change, or you would need to

modify the connection

quote:

>string. Ex.
>Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security

quote:

>Info=False;User ID=myuser;Initial Catalog=Northwind;Data
>Source=MySQLServerName,4500;Network Library=DBMSSOCN
> (Where 4500 is the new port)
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and

confers no rights.

quote:

>
>.
>

|||You're welcome.
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Friday, March 23, 2012

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

SET @.outputXML = (
SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)
Its always erroring with the following information
"Msg 1086, Level 15, State 1, Line 16
The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it."

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

set @.outputXML =
(
select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from
(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping) as A
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid WHERE batchID = 32 Order by 3,4,5,6 FOR XML EXPLICIT)

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

(SELECT 1 as TAG, NULL as PARENT, BatchID as [Batch!1!id], NULL as [Sequence!2!id],NULL as [Step!3!id], NULL as [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID, NULL,NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID, NULL FROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32) as A
Order by 3,4,5,6 FOR XML EXPLICIT)
SELECT @.outputXML

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

The FOR XML clause is invalid in views, inline functions, derived tables, and subqueries when they contain a set operator. To work around, wrap the SELECT containing a set operator using derived table syntax and apply FOR XML on top of it.

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

|||I had the exact same error. I solved it in the same way as you did , but my problem is that the query doesnt always run as i expect it to. Every so often Sql server throughs me back this error.
The original query worked without the error. All I did was wrap it in another select, exactly is done in the solution on this thread

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.|||Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.|||You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML asXML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

(SELECT 1 as TAG,NULLas PARENT, BatchID as [Batch!1!id],NULLas [Sequence!2!id],NULLas [Step!3!id],NULLas [Device!4!DeviceName]FROM BatchDeviceMapping WHERE BatchID = 32
UNION
SELECT 2 as Tag, 1 as Parent, BatchID, SequenceID,NULL,NULLFROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 3 as Tag, 2 as Parent, BatchID, SequenceID, StepID,NULLFROM BatchDeviceMapping WHERE batchID = 32
UNION
SELECT 4 as Tag, 3 as Parent, BatchID, SequenceID, StepID, Device.DeviceName FROM BatchDeviceMapping
JOIN Device Device on BatchDeviceMapping.Deviceid = device.deviceid where batchid = 32)as A
Orderby 3,4,5,6 FORXMLEXPLICIT)
SELECT @.outputXML

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from(SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] =MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUPBY ObjectType, ParentId

HAVING ObjectType = 3

)as A FORXMLAUTO,TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

|||Is it possible because of bad data? Since the error seems to be random, I would check the data or any other query which gets executed before this query that might change the assumptions your current query is making.|||You are getting the error because of ordering of the data.After applying the order by clause, the result set should have the parent tag ID appear before the child tag id.You can verify that by executing the query without the for xml clause.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

Nested XML with XML Explicit

I am using SQL Server 2005.

I want to assign the result of a SELECT FOR XML EXPLICIT statement having an order by clause to a XML Variable such as

DECLARE @.outputXML as XML

Please note: The select query is working fine with FOR XML EXPLICIT and Order By Clause - The problem is with assigning the result of SELECT to the variable.

Any help would be appreciated.

Thanks,
Loonysan

See if this works

DECLARE @.outputXML as XML

|||

Thanks... This modified query from yr logic works..

DECLARE @.outputXML as XML

set @.outputXML =

(select TAG,PARENT,[Batch!1!id],[Sequence!2!id],[Step!3!id],[Device!4!DeviceName] from

Thanks,
Loonysan

|||Hi,

I'm looking for a similar solution but for SQL Server 2000. Any idea ?

(The error in SQL2000 is "Incorrect syntax near 'XML'.")

Thanks
Sylvain
|||Can you give more details? The problem was not a specific one to SQL 2K5.|||

I am using SQL 2005.

I am trying to assign the result of FOX XML AUTO to a xml variable which looks something like this:

SELECT @.auditXML = (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

FOR XML AUTO, TYPE)

It gives me this error

Msg 1086, Level 15, State 1, Procedure sf_department_openCloseFolder, Line 312

I have exactly the same problem as discussed in this thread. The query works fine on its own. It is only the assignment which throws this error.

Can somebody please help? I am stuck mid-way.

Thankyou,

Umaima

|||

Try this:

SELECT @.auditXML =(select objectid,ObjectType,ParentId from (SELECT ObjectId, ObjectType, ParentId

FROM @.PermissibleChildren AS Children WHERE ObjectType = 2

UNION

SELECT [ObjectId] = MAX(ObjectId), [ObjectType] = ObjectType, [ParentId] = ParentId

FROM @.PermissibleChildren AS Children

GROUP BY ObjectType, ParentId

HAVING ObjectType = 3

) as A FOR XML AUTO, TYPE)

|||

This post was extremely useful, I was trying to insert xml into a table variable where the xml was result of for XML explicit statement

Thanks

6833 Parent tag ID 1 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.

Does anybody know why that would be the case.

Wednesday, March 21, 2012

Nested Select to Join three tables into one result set

I'll simplify the table structure that I've inherited in order to try to
explain what I need.
Three tables - ISSUES, USERS and ASSIGN:
ISSUES
IDRecord - Primary Key
Description
DateEntered
USERS
IDRecord- Primary Key
LastName
FirstName
ASSIGN
IDRecord - Primary Key
IDDefRec - matches to IDRecord in ISSUES
IDUser - matches to IDRecord in USERS
What I want is a result set for all ISSUES entered after 7/1/2005 (for
example) that includes all of the columns from ISSUES and the FirstName and
LastName of the last user assigned to the ISSUE. The ASSIGN table can
contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
So I figure I just need to get the TOP 1 of the ASSIGN table that matches
the ISSUE and get the corresponding USER name. I just can't figure out how
to do it in one SELECT statement.
JeffWhat the first rule of a data model' A data element has one and only
one name in a schema. So what is this magical "record_id" that appears
to be everywhere?
And why don' t you know that a row and record are totally different
concepts? Why don't you use ISO-8601 Standard date formats? Why did
you put the qualifier in the front of the names, in violation of the
ISO-11179 rules for metadata?
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
If you knew what a key was, followed ISO Standards, and underdstood DRI
action your non-existent DDL would look like this:
Why is there no resolution date in your issues? The model of time in
SQL is durations, not single dates.
CREATE TABLE Issues
(issue_nbr INTEGER NOT NULL PRIMARY KEY,
issue_description VARCHAR(255) NOT NULL);
Create a dummy user zero called "To Be Determined" or '{{TBD}}' for
when an issue arrived if you don't assign them immediately.
CREATE TABLE Users
(user_id INTEGER DEFAULT 0 PRIMARY KEY,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(20) NOT NULL)
CREATE TABLE Assignments
(issue_nbr NOT NULL
REFERENCES Isuses (issue_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
user_id INTEGER DEFAULT '{{TBD}}' NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (issue_nbr, user_id)
assigned_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
resolved_date DATETIME, -- null means still open);
CHECK (assigned_date <= resolved_date));
Now you have a whole tracking history.
SELECT @.my_date, I1.issue_nbr, I1.issue_description,
U1.user_id, U1.last_name, U1.first_name
FROM Issues AS I1, Assignments AS A1, Users AS U1
WHERE @.my_date BETWEEN A1.assigned_date AND A1.resolved_date
AND U1.user_id = A1.user_id
AND A1.issue_nbr = I1.issue_nbr;|||> So what is this magical "record_id" that appears
> to be everywhere?
> And why don' t you know that a row and record are totally different
> concepts? Why don't you use ISO-8601 Standard date formats? Why did
> you put the qualifier in the front of the names, in violation of the
> ISO-11179 rules for metadata?
> Why is there no resolution date in your issues? The model of time in
> SQL is durations, not single dates.
>
It appears that you missed the part where I said that I INHERITED this
structure. This is an application that the school district I work for
purchased and I have NO control over its structure. It is what it is. I
simply need to know if, given the structure that I laid out, is there a way
to return for each item in the ISSUES table beyond a parameterized date the
first and last name of the User last assigned to the Issue in the ASSIGN
table as well as all of the details of that Issue.
js|||You are screwed. Would you like an expert witness for the lawsuit?|||> You are screwed. Would you like an expert witness for the lawsuit?
;}
I guess I'll just write a stored procedure to move the records to a temp
table then and look up the User name against the Temp result set. Just was
looking for a quicker way.
js|||SELECT I.*, U.FirstName, U.LastName
FROM Issues I
INNER JOIN
(SELECT IDDefRec,
MAX(IDUser) As IDUser
FROM Assign
GROUP BY IDDefRec) A
ON I.IDRecord = A.IDDefRec
INNER JOIN USERS U
ON A.IDUser=U.IDRecord
--The above example just get the max of userid. To get the last assigned
userid, you have to add another column
in the ASSIGN table to keep track of the time of assignment.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Jeff Swanberg" <jswanberg@.swanbergcomputing.com> wrote in message
news:%23GMlmu$jFHA.3580@.TK2MSFTNGP09.phx.gbl...
> I'll simplify the table structure that I've inherited in order to try to
> explain what I need.
> Three tables - ISSUES, USERS and ASSIGN:
> ISSUES
> IDRecord - Primary Key
> Description
> DateEntered
> USERS
> IDRecord- Primary Key
> LastName
> FirstName
> ASSIGN
> IDRecord - Primary Key
> IDDefRec - matches to IDRecord in ISSUES
> IDUser - matches to IDRecord in USERS
>
> What I want is a result set for all ISSUES entered after 7/1/2005 (for
> example) that includes all of the columns from ISSUES and the FirstName
> and LastName of the last user assigned to the ISSUE. The ASSIGN table can
> contain many rows per ISSUE as subsequent USERS are assigned to the ISSUE.
> So I figure I just need to get the TOP 1 of the ASSIGN table that matches
> the ISSUE and get the corresponding USER name. I just can't figure out
> how to do it in one SELECT statement.
> Jeff
>
>|||>> I'll just write a stored procedure to move the records [sic] to a temp table t
hen and look up the User name against the Temp result set. <<
That will not work. The schema does not show when someone was assigned
to an issue, only when the issue was first entered. Created on Monday,
assigned to Tom on Tuesday, handed off to Wendy on Wednesday and thrown
to Thomas on Thursday.
The design is flawed.|||> You are screwed. Would you like an expert witness for the lawsuit?
I'm not sure that someone that is mentally unstable would qualify as an expe
rt
witness. ;->
Thomas|||On Mon, 25 Jul 2005 09:03:17 -0700, "Thomas Coleman" <replyingroup@.anywhere.
com>
wrote:
in <eeuGRJTkFHA.1444@.TK2MSFTNGP10.phx.gbl>

>I'm not sure that someone that is mentally unstable would qualify as an exp
ert
>witness. ;->
>
>Thomas
I sure hope your insults are tongue in ch

because they have no place in a
professional newsgroup. Or are you NOT a professional?
Stefan Berglund|||> I sure hope your insults are tongue in ch

because they have no place in ad">
> professional newsgroup. Or are you NOT a professional?
My sententious observations about Don Celko's behavior are as tongue and che
ek
as his remarks. ;->
Thomas

Monday, February 20, 2012

Need to track SQL activity

Hi all -
We are running a CRM application on a SQL 2K SP3a box. Through SQL, are
there any logs that I can go back to in order to see what data was viewed,
exported and by whom, for a specific date?
Thanks - MikeTransaction logs contain all the data modification information, but SQL
Server doesn't provide a friendly way to get at that information (DBCC LOG,
and fn_dblog are available).
Another approach would be to use Profiler or server side trace procedures to
capture the information you are after. For additinoal information on server
side traces, see:
http://vyaskn.tripod.com/server_sid..._sql_server.htm
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"HelpPls" <smd6169@.hotmail.com> wrote in message
news:uaczuKYeEHA.2532@.TK2MSFTNGP09.phx.gbl...
> Hi all -
> We are running a CRM application on a SQL 2K SP3a box. Through SQL, are
> there any logs that I can go back to in order to see what data was viewed,
> exported and by whom, for a specific date?
> Thanks - Mike
>
>

Wednesday, March 28, 2012

Network Configuration...

Friday, March 23, 2012

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Nested XML with XML Explicit

Wednesday, March 21, 2012

Nested Select to Join three tables into one result set

Monday, February 20, 2012

Need to track SQL activity

Need to sum columns

Blog Archive

About Me