A couple of easy questions

Dear all,
1)
I was wondering myself, how many characters can handle Sql2k when you define
a field? Such limitation is common for any object? I mean, tables, indexes,
jobs.
I've got an ASP application which allow the user create fields and I would
need to know that rule in order to define a maxlenght value.
2)
I had in my EM remote servers added but today they are dissapeared although
now are currently up. Does anyone ever experienced such behaviour? It coul
d
be a shutdown of that server or even if that server is off from the client
vanishes...
Thanks a lot for your suggestions,
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)Hi
1)
Lookup in the BOL for maximum and minimum ovjects limits article
2)
I have not, but did you refresh your EM?
"Enric" <vtam13@.terra.es.(donotspam)> wrote in message
news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
> Dear all,
> 1)
> I was wondering myself, how many characters can handle Sql2k when you
> define
> a field? Such limitation is common for any object? I mean, tables,
> indexes,
> jobs.
> I've got an ASP application which allow the user create fields and I would
> need to know that rule in order to define a maxlenght value.
> 2)
> I had in my EM remote servers added but today they are dissapeared
> although
> now are currently up. Does anyone ever experienced such behaviour? It
> could
> be a shutdown of that server or even if that server is off from the client
> vanishes...
>
> Thanks a lot for your suggestions,
>
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''s hard to provide information
> without seeing the code. location: Alicante (ES)|||hi again Uri,
Yes, I did. How odd, isn't?
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:

> Hi
> 1)
> Lookup in the BOL for maximum and minimum ovjects limits article
> 2)
> I have not, but did you refresh your EM?
>
> "Enric" <vtam13@.terra.es.(donotspam)> wrote in message
> news:514627E4-4715-41C6-B92B-B8913DC31D94@.microsoft.com...
>
>|||
> Yes, I did. How odd, isn't?
Hi Enric, do you have 'Open object explorer and new query' in Tools/Options
dialog under Environment/General leaf?
I noticed that if object explorer is not mentioned in selected combo,
servers don't show up.
Peter|||Hi Rogas,
I don't see that option. From Entreprise Manager->Tools->Options I have two
leafs: General and Advanced. In General there is nothing related with you
said.
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''s hard to provide information
without seeing the code. location: Alicante (ES)
"Rogas69" wrote:

>
> Hi Enric, do you have 'Open object explorer and new query' in Tools/Option
s
> dialog under Environment/General leaf?
> I noticed that if object explorer is not mentioned in selected combo,
> servers don't show up.
> Peter
>
>

A Common Query Question!

Hi Guys,

I have a query that gives me multiple rows for every customer, where as my requirement is to have one row for each customer. Below are the details:

Customers Table

Cust_ID int (PK)

Cust_Name varchar(255)

Subscription_Status int

Cust_Subscription_Audit Table

ID Identity(PK)

Cust_ID int

Old_Subscription_Status int

New_Subscription_Status int

Audit_Date DateTime

Logins Table (one customer may have multiple logins )

ID Identity (PK)

Cust_ID int

login name varchar(256)

email_ID varchar(256)

F_Name varchar(50)

L_name varchar(50)

role int

Last_Modified DateTime

INDEXES

Logins Table

PK__Clustered Ascending

No Index on Audit Table

Customers Table

PK__Clustered_ Ascending

Below is the query that is being used to extract the data

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID,MAX(CSA.Audit_Date)

FROM Customers C

INNERJOIN Cust_Subscription_Audit CSA ON C.Cust_ID= CSA.Cust_ID

INNERJOIN Logins L ON L.Cust_ID= C.Cust_ID

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUPBY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

This gives me multiple rows for a customer. What I am looking for is

1) One row for each customer.(Query 1)

2) If possible can pick the row of my choice Like log in that was added first or last based on login or Modified date in login table.(Query 2)

3) If possible can pick the row by row number i.e. if there are multiple logins for a customer then the 2nd login. (Query 3)

Your help will be appreciated.

Thanks

-Leo

Can you post some DDL, including constraints and indexes, sample data and expected result, please?

AMB

|||

Hi ,

Thanks for your prompt reply. Here is the Sample Data

Customers Table

Cust_ID Cust_Name SubsStatus

1 abc corp. 7

2 Adventure Works Inc 1

3 MicroCorn Inc 2

4 Fabrikam 7

5 Goleeeee Inc 5

Logins Table

ID Cust ID Login Email f_Name l_Name Role Date_Modified

1 1 rob1 rob1@.hotmail.com Rob Haany 1 2006-02-28 13:24
2 2 scot1 scot1@.yahoo.com Scot claudia 1 2006-05-24 12:30
3 3 scot2 scot2@.hotmail.com Scot Roy 1 2006-05-24 12:30
4 4 rob NULL RON HARTON 1 2006-08-22 15:26
5 4 qsir qsir@.hotmail.com Q Sir 1 2006-09-17 12:23
6 5 nzaar nzaar@.hotmail.com nad zaar 3 2006-09-17 12:23
7 5 jluk jluk@.hotmail.com Ron Richard 1 2006-09-17 12:23
8 2 drase draze@.hotmail.com Dino Mosoli 1 2006-08-22 15:26
9 2 dlins David Lin 3 2006-12-28 13:24
10 4 dmay NULL David May 1 2006-05-24 12:30

Role 1 = admin

Role 3 = Primary Contact

ID Cust_ID Old_Subscription_Status New_Subscription_Status Audit_Date

1 1 3 7 2006-09-24 12:30

2 2 0 1 2006-03-24 02:30

3 3 1 2 2006-08-24 10:30

4 4 1 7 2006-11-24 12:30

5 5 3 5 2006-09-24 12:30

6 4 3 1 2006-10-24 12:30

7 3 0 1 2006-06-24 12:30

8 1 0 1 2006-02-24 12:30

9 1 1 3 2006-07-24 12:30

10 5 1 3 2006-07-24 12:30

The required result is

Cust_ID Cust_Name F_name L_Name Email Audit Date

1 abc Corp Rob Haany rob1@.hotmail.com 2006-09-24 12:30

2 Adventure Works Inc Scot claudia scot1@.yahoo.com 2006-03-24 02:18

3 MicroCorn Inc Scot Roy scot2@.hotmail.com 2006-08-24 10:28

4 Fabrikam Q Sir qsir@.hotmail.com 2006-11-24 12:30

5 Goleeee Inc Ron Richard jluk@.hotmail.com 2006-09-24 12:30

Right now what is happening is threre are more than one records for Cust_ID = 2. The basic question is (Query 1) is how can we restrict the result set to only one row per Cust_ID. So my other questions (Query 2, Query3) were how can we choose one of these records based on some criteria e.g. since more than one records are there for Cust_ID = 2, and I wana choose the one that is with earliest Date_Modified or 2nd earliest Modified date.

Thanks,

-Leo.

|||

hi Leo,

If you are using SQL server 2005 Try this:

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

CROSS APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID ) L

WHERE C.Subscription_Status >=1 AND L.Role= 1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||

Thank you very much for this nice hint. I tweeked the query little bit and it woked for me here is the modified query.

Code Snippet

SELECT C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID, MAX(CSA.Audit_Date)

FROM Customers C

INNER JOIN Cust_Subscription_Audit CSA ON C.Cust_ID = CSA.Cust_ID

OUTER APPLY (select top 1 * from Logins where Cust_ID=CSA.Cust_ID And [Role] = 1 ) L

WHERE C.Subscription_Status >=1

GROUP BY C.Cust_Name, C.Cust_ID, L.F_Name +' '+ L.L_Name, L.Email_ID

|||do you need OUTER APPLU here? OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function (pretty much like OUTER JOIN in case of normal table). So if there is no match in table 'Logins' the value of L.F_Name +' '+ L.L_Name AS [CustName], L.Email_ID will be NULL.

404 Access Denied

Any clues?
404 Access Denied
... omitted code...
m_credentials = New System.Net.NetworkCredential(Common.SRSUserName,
Common.SRSPassword, Common.SRSDomain)
_rs.Credentials = m_credentials
end sub
... omitted code...
Dim items() As CatalogItem
_rs.Url = Common.SRSServerName
items = _rs.ListChildren("/", True)
Line 208:"ildren",
RequestNamespace:="http://schemas.microsoft.com/sqlserver/2003/12/reporting/repo
rtingservices",
ResponseNamespace:="http://schemas.microsoft.com/sqlserver/2003/12/reporting/rep
ortingservices", Use:=System.Web.Services.Description.SoapBindingUse.Literal,
ParameterStyle:=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)> _
Line 209: Public Function ListChildren(ByVal Item As String, ByVal
Recursive As Boolean) As
<System.Xml.Serialization.XmlArrayAttribute("CatalogItems")> CatalogItem()
Line 210: Dim results() As Object = Me.Invoke("ListChildren", New
Object() {Item, Recursive})
Line 211: Return CType(results(0),CatalogItem())
Line 212: End FunctionIt was my web.config
I had mis-read that I should add to <identity impersonate="true"/> to the
authorization section, but "after" was where it belonged.
JeffP...
"JDP@.Work" <JPGMTNoSpam@.sbcglobal.net> wrote in message
news:eUBCRPTwEHA.536@.TK2MSFTNGP11.phx.gbl...
> Any clues?
> 404 Access Denied
> ... omitted code...
> m_credentials = New System.Net.NetworkCredential(Common.SRSUserName,
> Common.SRSPassword, Common.SRSDomain)
> _rs.Credentials = m_credentials
> end sub
> ... omitted code...
> Dim items() As CatalogItem
> _rs.Url = Common.SRSServerName
> items = _rs.ListChildren("/", True)
>
> Line 208:"ildren",
>
RequestNamespace:="http://schemas.microsoft.com/sqlserver/2003/12/reporting/repo
> rtingservices",
>
ResponseNamespace:="http://schemas.microsoft.com/sqlserver/2003/12/reporting/rep
> ortingservices", Use:=System.Web.Services.Description.SoapBindingUse.Literal,
> ParameterStyle:=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)> _
> Line 209: Public Function ListChildren(ByVal Item As String, ByVal
> Recursive As Boolean) As
> <System.Xml.Serialization.XmlArrayAttribute("CatalogItems")> CatalogItem()
> Line 210: Dim results() As Object = Me.Invoke("ListChildren", New
> Object() {Item, Recursive})
> Line 211: Return CType(results(0),CatalogItem())
> Line 212: End Function
>