Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate Rows

Hi

I have a table similar to the following:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

I need the following output:

Date ID Name Job Number JobType

12/12/2007 123456 Fred Smith 111111 Full Day

12/12/2007 654321 Bob Blue 222222 Half Day AM

12/12/2007 654321 Bob Blue 333333 Half Day PM

Now before you say the output is the same . It isn't! There are only 2 records in the output. The italic lines are one record, with a carriage return linefeed between each piece of data. So for job number the field is equal to 111111 + CHAR(10) + CHAR(13) + 222222

Could someone please point me in the right direction?

Cheers

You could to use SELECT FOR XML PAHT with empty tag:

Code Snippet

create table t2

(

Date datetime,

ID int,

Name varchar(20),

JobNumber varchar(20),

JobType varchar(20)

)

go

insert into t2 values('12/12/2007', 123456,'Fred Smith','111111','Full Day')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 222222','Half Day AM')

insert into t2 values('12/12/2007', 654321,'Bob Blue',' 333333','Half Day PM')

select

replace( (SELECT name + '##' FROM t2 as d where d.ID=m.ID FOR XML PATH('')), '##', char(10)+char(13) ) as CName

,ID from t2 m group by ID

|||Hi Kosinsky,
Your querry is not working in SQL200 is it for SQL 2005 or it will run properly in sql2000 also if not then wht will be the querry for sql2000,

I got the following error when i am trying to run your select querry in sql2000

Code Snippet

Server: Msg 170, Level 15, State 1, Line 1Line 1:

Incorrect syntax near 'XML'.

|||

My query use SELECT FOR XML PATH. Its SQL Server 2005 feature.

For SQL Server 2000 you could use FOR XML RAW and two additional replaces:

Code Snippet

select

replace

(

replace

(

replace( (SELECT name as t FROM t2 as d where d.ID=m.ID FOR XML RAW('t')), '"/><t t="', char(10)+char(13))

,'<t t="',''

)

,'"/>',''

)

,ID

from t2 m group by ID

|||Hi,
Still its giving me the same error.?

Code Snippet

Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near 'XML'.

|||

Sorry, but me solution doesn't work on SQL Server 2000. Because FOR XML is not valid in subselections

|||

Thanks for the replies Konstantin Kosinsky, but I'm also running SQL Server 2000.

Does anyone have any other ideas on how to achieve this please?

Cheers

|||

From what I can tell, all the easy solutions for this are in SQL2005. SQL2000 solutions are much messier. Try searching this forum for words like aggregate and concatenate. There are a few that might help, like this one:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=125302&SiteID=1

Note Umachandar's solution I think should work on 2000. MRys', while much neater, relies on having 2005.

Good luck.|||

Cheers Cringing Dragon, great find. I used the post on the link you provided by Umachandar Jayachandran - MS.

The SQL of which is:

Code Snippet

select t3.id
, substring(
max(case t3.seq when 1 then ',' + t3.comment else '' end)
+ max(case t3.seq when 2 then ',' + t3.comment else '' end)
+ max(case t3.seq when 3 then ',' + t3.comment else '' end)
+ max(case t3.seq when 4 then ',' + t3.comment else '' end)
+ max(case t3.seq when 5 then ',' + t3.comment else '' end)
, 2, 8000) as comments
-- put as many MAX expressions as you expect items for each id
from (
select t1.id, t1.comment, count(*) as seq
from your_table as t1
join your_table as t2
on t2.id = t1.id and t2.comment <= t1.comment
group by t1.id, t1.comment
) as t3
group by t3.id;

Thank you all for your help.

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,
This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:

> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>
|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Concatenate nuimbers

I have two fields that have numbers company and employee, I want to
concatenate these numbers so it it takes the comapny number (9000) and
employee number (116258) and makes 9000116258.
When i use this query
(select company+employee as uid from cr_staging) it adds the numbers.
I also tried
SELECT Company, Employee, Company & ' ' & Employee AS uid
FROM cr_staging
but it gives me an error invalid operator for data type.
Is there any suggestions on this problem?
Thanks,This will work:
select CONVERT(varchar(10),company) + convert(varchar(10),employee) from
cr_staging
"Eric" wrote:
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||+ adds numbers when they are numbers (int, numeric...)
+ combines varchar data
You need to convert the columns to varchar before you "add" them.
SELECT CONVERT(varchar(20),company) + CONVERT(varchar(20),employee)
FROM cr_staging
--
Keith
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:9862CFBF-3B7A-43B0-9A33-136F2B228A05@.microsoft.com...
> I have two fields that have numbers company and employee, I want to
> concatenate these numbers so it it takes the comapny number (9000) and
> employee number (116258) and makes 9000116258.
> When i use this query
> (select company+employee as uid from cr_staging) it adds the numbers.
> I also tried
> SELECT Company, Employee, Company & ' ' & Employee AS uid
> FROM cr_staging
> but it gives me an error invalid operator for data type.
> Is there any suggestions on this problem?
>
> Thanks,
>|||Assuming that your fields are defined as INT and that you want to keep the
same number of digits (4 + 6), try this:
select
(
RIGHT('0000' + CAST(Company AS varchar(4)), 4)
+
RIGHT('000000' + CAST(Employee AS varchar(6)), 6)
)
as UID
from cr_staging
This will give you the result: '9000116258'
HTH,
Robert

Concatenate multiple reports into one pdf

Is there any way to do the above? I need to supply a number of "management accounts" reports individually during the month, and then the accountant needs to run them all into a single pdf at the end of the month for the board report.

Thanks in advance

Combining reports into one report directly is not a current feature. One option you could try would be to create a master report that uses the Sub Report feature of RS. A simplistic view would be to create the master report that looks like Subreport\Pagebreak\Subreport\Pagebreak\etc...

|||Thanks - I had a feeling that was the case. I have lots of info in the header, so I will have to strip that out.

Concat with Auto-increment column

I have a column with consist of customer number.e.g.
c001
c002
c003

How do I add the character "c" to the auto-incremental number everything I add?

You mean you want to add "c" to your identity column? Well, you cannot prefix an identity column because the column must be numeric - how is it going to increment if it's alpha.

However, you can create a compute column that does the prefixing for you.

e.g.

Code Snippet

create table t(pk int identity primary key, custid as 'c'+right(1000+pk,3));

insert t default values;

insert t default values;

insert t default values;

select * from t;

|||

You can try this :

--create a function in that you can make all the conact or incrementations you need

USE [test]

GO

CREATE FUNCTION [dbo].[ConcAuto](@.incr INT)

RETURNS varchar(11)

AS

BEGIN

DECLARE @.Result varchar(11)

SET @.Result = 'C'+cast(@.incr as varchar(10))

RETURN

(

@.Result

)

END

GO

--then create a column , col, as computed column

CREATE TABLE [dbo].[aa](

[id] [int] IDENTITY(1,1) NOT NULL,

[col] AS ([dbo].[ConcAuto]([id])),

[name] [nchar](10) NULL

) ON [secondary]

GO

computing for the number of hours from 2 date attributes

Hi,

I want to build a calculated fields that compute for the number of hours between 2 date attributes.

let's say I have 2 dates:

1/1/2007 15:30

1/2/2007 9:10

It will give me 18 hours.

cherriesh

Try

VBA!DateDiff('h', DateValue1, DateValue2)

|||

Depend where you want ir and the importance for your project...

You can do that calculation inside a report, in a cube... or you can create it using SQL statment in a named calculation in teh datasourceview in Analysis Services...

helped?

Regards!

|||

Problem resolved?

regards!!

Computed field references

I am currently developing a stored procedure that includes a number of computed fields. Is it possible to reference a computed value, (eg. FLdA), or do I need to CREATE a temp file and then reference the FldA and FldB values. I have simplified my code, it is much more extensive in that there are numerous WHEN clauses attached to each FldA and FldB computation.

SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN FldA * CurValue
WHEN ... THEN FldB * CurValue
etc.I'm not sure I understand the question...Do you want to reference the value again inside the sproc?

Then Yes...use a local table variable...

If it's being part of a result set being passed back, then you're already refrencing it...

I'm confused...|||I want to reference the value within the sproc and pass only those records where the OldValue is not equal to the NewValue. In the case I mentioned, I am trying to reference FldA and FldB to compute the NewValue from within the same SELECT stmt, but SQL does not let me reference the FldA and FldB computed values. Is that as clear as mud?|||Reference them, where? In the same query? Or later on in the sproc.

If it's later on in the sproc

SELECT <whatever> INTO #TEMP FROM <whatever>

Then just query the local temp table...

Is that what you mean?|||I'm trying to reference them in the same query.

The INSERT .. INTO stmt seems cumbersome as it appears I would have to define each field as part of the CREATE TABLE stmt. Can't see why it doesn't just pickup the data types from the TABLE.|||Well it's not data type is it...it's column names

Well do this...Keep your computed stuff isolated...and join to a derived table

SELECT * FROM (SELECT <your derived columns> FROM table join table ect) AS A
LEFT JOIN B ON a.key = b.key
WHERE <now you can reference the derived column name> = 'bananas'

Whatever...

I fyou make the derivation this derived table you'll be able to reference the column names you made up...|||Why so complicated?

select * from (
SELECT FldA = CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END),
FldB = CASE ....
NewValue = CASE
WHEN ... THEN CASE
WHEN ... THEN CurQty * 1.5
WHEN ... THEN CurQty * 1.75 ELSE 0 END * CurValue
WHEN ... THEN CASE .... * CurValue
) x
where OldValue != NewValue

In other words, instead of trying to reference FldA, use its CASE...END when calculating NewValue. Same with FldB.|||I had mentioned earlier that the code was simplified. The CASE logic is fairly complex, could be up to 20 lines of code. That would mean that I would have to repeat the code everytime the field ('FldA') was referenced. I may just leave the logic in VBA code as it seems a lot easier to manipulate fields in code. My goal was to restrict the query ouput lines so the Access code would run quicker.|||Thanks Brett ... I'll give it a go.|||Here's a model

USE Northwind
GO
SELECT SUM(OutOfBusinessDays) AS VacationDays
FROM (
SELECT ShipLate-ShipDelay AS OutOfBusinessDays
FROM (
SELECT DATEDIFF(dd,OrderDate,ShippedDate) As ShipDelay
, DATEDIFF(dd,OrderDate,RequiredDate) As ShipLate
FROM Orders
) AS XXX
) AS DerivedTableName

Complicated count w/ subquery

Site Patient Visit
Number Number Number
1 1 1
1 1 2
1 2 1
1 3 1
2 1 1
2 2 1
3 1 1
3 1 2

Site Number 1 has 2 visit 1 and 1 both (visit 1 and visit 2)
Site number 2 has 2 visit 1
site number 3 has 1 both (visit 1 and visit 2)

I am really confused as to how to accomplish this in one query. I can query it sep, but not together. Ultimately, I am looking for either visit 1, visit 2, or both, but there should not be duplicates (that is the problem I am having because once counted, it should not appear in any other count)Perhaps if you asked a question the forum would be more responsive.|||Post the query you are currently using. At a guess I would say you are missing some Group By clause.

Mary

complex report

have the following data from a query
age sex
39 M
48 F
14 M
etc...
need to be displayed as
Age Group Number of Male Number of Female Total in Age Group
0 -2 2 3
5
3 - 9 24 33
57
....
70 and above 12 11
23
Total Males ##
Total Females ##
Total
##
Any suggestions would be helpful.This question is better geared towards a SQL forum rather then reporting
services.
Search online for the SQL syntax of Case. You'll want to create a select
statment where you do something similar to this.
SELECT '0-2' as Age Group, SUM(CASE age>0 AND age < 2 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>0 AND age < 2 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 0 AND age < 2) as Total in Age Group
UNION
SELECT '3-9' as Age Group, SUM(CASE age>3 AND age < 9 AND sex = "M", 1, 0
END) as Number of Male, SUM(CASE age>3 AND age < 9 AND sex = "F", 1, 0 END)
as Number of Female, SUM(CASE age > 3 AND age < 9) as Total in Age Group
And so on and so forth for each of the age/sex ranges.
-Michael Abair
Programmer Analyst
Chicos FAS Inc
"Chop" <Chop@.discussions.microsoft.com> wrote in message
news:0D4FB1BC-D77D-4E6B-A728-B798B6EAF6E4@.microsoft.com...
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.|||You also have the option of creating fields within RS; for the values
use:
iif(fields!age.value >=0 and fields!age.value < 3, "0 - 2",
iif(fields!age.value < 10, "3-9", ...))
Then use a matrix item and use the calculated field as your row group,
sex as your column group.
Chop wrote:
> have the following data from a query
> age sex
> 39 M
> 48 F
> 14 M
> etc...
> need to be displayed as
> Age Group Number of Male Number of Female Total in Age Group
> 0 -2 2 3
> 5
> 3 - 9 24 33
> 57
> ....
> 70 and above 12 11
> 23
> Total Males ##
> Total Females ##
> Total
> ##
> Any suggestions would be helpful.

Completed(?) and urgent Triggers and Transactions question

I use the VB.NET transaction to update an sql server 2000 database. I call a number of stored procedures within this transaction.

The stored procedures will update tables. These tables use triggers..

My question is, when does the trigger get called? Is it after the each stored procedure, or is it after the whole transaction?

Jag

the trigger will execute when

- new records is being inserted into the table

- records get update

- records get deleted

from the table

|||

In my opinion triggers fire as soon as the database updated, and transaction update your table as soon as a query fires. Having triggers do too much is a typical mistake. Triggers should be
left to handle only simple tasks.

cheers

|||

How would you define too much?

At the moment, one of my trigger calls a stored procedure that is doing about 4 selects on a table (one with an MAX()), and maybe a 2 updates.

The stored procedure should be optimised, so it should case too much problems later (?)

EDIT: I am expecting the number of selects to increase to about 10, and the number of updates to 3. The selects will be small selects that mostly pull out ids.

Jag

|||

Hi,

The trigger will be fired immediately after the the update was executed. And the transaction will continue after trigger gets executed.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

|||

What I was afraid of was that the triggers would not get fired during the transaction, and would be fired after the commit. But after a little testing, I found this was not true.

I am still trying to find out how much I can put into a trigger before a performance becomes an issue. I am not expecting heavy usage - I'm not even expecting moderate usage on the site. Maybe about 10 inputs/edits a day, that's it.

I have written a stored procedure and the trigger calls this if needed. The stored procedure will have upto 20 select statements to pull out values from the database, and then a couple of updates. Is this too much? I don't see it being too much as I am not expecting too much input to be going on.

Jag

|||

Just another quick question.

Same example - there is a transaction with a number of updates/inserts. The tables these apply to have triggers on then. The triggers are fired as soon as the update/insert happens, and not at the end of the transaction.

My question is, if the transaction fails and the changes are reversed, will the changes made by the triggers also be reversed. Common sense says that it should happen, but I need to ensure that it does.

Thanks in advance

Jag

Complete list of error codes

Does anyone know where to obtain a complete list of error codes which can occur in SQL Server ? For example : errror number 547 is a foreign key violation - where can I get a complete list of these numbers and there descritions ?

I can't find it in BOL or anywhere

In BOL type in 'errors-SQL Server' in the search box in the index tab and they are listed starting with Error 10008 (DB-Library)

you can also take a look here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_syserrors1a_10mx.asp

Denis the SQL Menace

http://sqlservercode.blogspot.com/

Compilations vs recompilations/sec

I see high number of compilations/sec and not recompilations/sec ? Whats the
difference between the two.. in other words when does it compile vs when
does it recompile ? I could not get a good feel about this even after
skimming through this article
http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? Can someone give me the SQL for when it may
recompile in the above condition ? Also will this show as recompilation/sec
or compilation/sec in perfmon ?Are you having performance issues, or is this a general question?
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||First, thanks for the link, it was good reading. However, I would hardly
consider it useful if you're only going to skim it.
Clearly, the queries on your system is either not getting cached, or plans
are timing out and being removed from the cache (I don't know those
specifics about SQL Server). But again I must ask why you are asking the
question in the first place.
The plan/procedure/query cache is part of the Query Optimizer and is quite
possibly the single most complicated portion of a database engine. It is
also an area that DBA's seldom have to muck with, with the exception of some
basic understanding.
So, I must ask why you are asking the question in the first place? Are you
having performance problems, or are you just curious?
Jay
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||I am seeing high compilations/sec on one of our SQL Servers as high as 500
and zero recompilations/sec. No one is complaining yet, but curious to know
why compile vs recompile..
"JayKon" <spam@.nospam.org> wrote in message
news:uUkmBPe4HHA.2312@.TK2MSFTNGP06.phx.gbl...
> First, thanks for the link, it was good reading. However, I would hardly
> consider it useful if you're only going to skim it.
> Clearly, the queries on your system is either not getting cached, or plans
> are timing out and being removed from the cache (I don't know those
> specifics about SQL Server). But again I must ask why you are asking the
> question in the first place.
> The plan/procedure/query cache is part of the Query Optimizer and is quite
> possibly the single most complicated portion of a database engine. It is
> also an area that DBA's seldom have to muck with, with the exception of
> some basic understanding.
> So, I must ask why you are asking the question in the first place? Are you
> having performance problems, or are you just curious?
> Jay
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||Well try not to "skim" next time since this article is very specific about
what recompilation is. But in a nut shell a recompile is when the plan is in
cache and it gets invalidated for one of the many reasons the article
explains so that the next time a user tries to use that plan it must be
recreated or recompiled. A compile is when it never was in cache to begin
with. If you have lots of compiles it means you have lots of adhoc queries
and sql server is either not caching them at all or you have so many that
they don't stay in cache long enough to be reused. I would read the article
several times in depth as it is one of the very best articles on cache
behavior out there.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Hassan" <hassan@.hotmail.com> wrote in message
news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>I see high number of compilations/sec and not recompilations/sec ? Whats
>the difference between the two.. in other words when does it compile vs
>when does it recompile ? I could not get a good feel about this even after
>skimming through this article
> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that contains a literal larger than 8 KB. Suppose that this batch creates
> a temporary table, and then inserts 20 rows in that table. The insertion
> of the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? Can someone give me the SQL for when it may
> recompile in the above condition ? Also will this show as
> recompilation/sec or compilation/sec in perfmon ?
>
>
>|||Thanks Andrew.
What about this statement ? Can you help me here ?
In addition, it states "Note in particular that the query plans for the
batch need not have been cached. Indeed, some types of batches are never
cached, but can still cause recompilations. Take, for example, a batch that
contains a literal larger than 8 KB. Suppose that this batch creates a
temporary table, and then inserts 20 rows in that table. The insertion of
the seventh row will cause a recompilation, but because of the large
literal, the batch is not cached."
What does a literal mean ? can you give an example of a literal ?
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
> Well try not to "skim" next time since this article is very specific about
> what recompilation is. But in a nut shell a recompile is when the plan is
> in cache and it gets invalidated for one of the many reasons the article
> explains so that the next time a user tries to use that plan it must be
> recreated or recompiled. A compile is when it never was in cache to begin
> with. If you have lots of compiles it means you have lots of adhoc queries
> and sql server is either not caching them at all or you have so many that
> they don't stay in cache long enough to be reused. I would read the
> article several times in depth as it is one of the very best articles on
> cache behavior out there.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even after
>>skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch creates
>> a temporary table, and then inserts 20 rows in that table. The insertion
>> of the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>|||A literal is an actual value. I'm not going to give you an actual example
because I would have to type in more than 8000 characters!
But suppose you have a table with a column of varchar(max). A query like the
following would be an example of one with a literal longer than 8K:
UPDATE mytable
SET bigcolumn = 'Some very very long string that is longer than 8000
characters ....'
WHERE key_column = 42
The plan for the above query would not be cached.
Of course I could have made the column nvarchar(max) and then I would only
have to type in 4001 characters, but that is still to many for me to type
right now.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
> Thanks Andrew.
> What about this statement ? Can you help me here ?
> In addition, it states "Note in particular that the query plans for the
> batch need not have been cached. Indeed, some types of batches are never
> cached, but can still cause recompilations. Take, for example, a batch
> that
> contains a literal larger than 8 KB. Suppose that this batch creates a
> temporary table, and then inserts 20 rows in that table. The insertion of
> the seventh row will cause a recompilation, but because of the large
> literal, the batch is not cached."
> What does a literal mean ? can you give an example of a literal ?
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>|||Kalen,
What about
UPDATE mytable
SET bigcolumn = 42
WHERE key_column = 'Some very very long string that is longer than 8000
characters ....'
Would the plan for this query not be cached too ?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>A literal is an actual value. I'm not going to give you an actual example
>because I would have to type in more than 8000 characters!
> But suppose you have a table with a column of varchar(max). A query like
> the following would be an example of one with a literal longer than 8K:
> UPDATE mytable
> SET bigcolumn = 'Some very very long string that is longer than 8000
> characters ....'
> WHERE key_column = 42
> The plan for the above query would not be cached.
> Of course I could have made the column nvarchar(max) and then I would only
> have to type in 4001 characters, but that is still to many for me to type
> right now.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://sqlblog.com
>
> "Hassan" <hassan@.hotmail.com> wrote in message
> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons the
>> article explains so that the next time a user tries to use that plan it
>> must be recreated or recompiled. A compile is when it never was in cache
>> to begin with. If you have lots of compiles it means you have lots of
>> adhoc queries and sql server is either not caching them at all or you
>> have so many that they don't stay in cache long enough to be reused. I
>> would read the article several times in depth as it is one of the very
>> best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ? Whats
>>the difference between the two.. in other words when does it compile vs
>>when does it recompile ? I could not get a good feel about this even
>>after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this batch
>> creates a temporary table, and then inserts 20 rows in that table. The
>> insertion of the seventh row will cause a recompilation, but because of
>> the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>|||No, this would give you an error, because key columns cannot be longer than
900 bytes.
But if the WHERE included a non-key column that was compared to a literal
longer than 8000 bytes, it is the same as the example I gave. A literal
anywhere in the query that is longer than 8000 bytes will keep the plan from
being cached.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Hassan" <hassan@.hotmail.com> wrote in message
news:u4qDLJi4HHA.3684@.TK2MSFTNGP02.phx.gbl...
> Kalen,
> What about
> UPDATE mytable
> SET bigcolumn = 42
> WHERE key_column = 'Some very very long string that is longer than 8000
> characters ....'
>
> Would the plan for this query not be cached too ?
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u6sueOh4HHA.1208@.TK2MSFTNGP05.phx.gbl...
>>A literal is an actual value. I'm not going to give you an actual example
>>because I would have to type in more than 8000 characters!
>> But suppose you have a table with a column of varchar(max). A query like
>> the following would be an example of one with a literal longer than 8K:
>> UPDATE mytable
>> SET bigcolumn = 'Some very very long string that is longer than 8000
>> characters ....'
>> WHERE key_column = 42
>> The plan for the above query would not be cached.
>> Of course I could have made the column nvarchar(max) and then I would
>> only have to type in 4001 characters, but that is still to many for me to
>> type right now.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://sqlblog.com
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:%23mYZNJh4HHA.5844@.TK2MSFTNGP02.phx.gbl...
>> Thanks Andrew.
>> What about this statement ? Can you help me here ?
>> In addition, it states "Note in particular that the query plans for the
>> batch need not have been cached. Indeed, some types of batches are never
>> cached, but can still cause recompilations. Take, for example, a batch
>> that
>> contains a literal larger than 8 KB. Suppose that this batch creates a
>> temporary table, and then inserts 20 rows in that table. The insertion
>> of
>> the seventh row will cause a recompilation, but because of the large
>> literal, the batch is not cached."
>> What does a literal mean ? can you give an example of a literal ?
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:eOU1RDh4HHA.5740@.TK2MSFTNGP04.phx.gbl...
>> Well try not to "skim" next time since this article is very specific
>> about what recompilation is. But in a nut shell a recompile is when the
>> plan is in cache and it gets invalidated for one of the many reasons
>> the article explains so that the next time a user tries to use that
>> plan it must be recreated or recompiled. A compile is when it never was
>> in cache to begin with. If you have lots of compiles it means you have
>> lots of adhoc queries and sql server is either not caching them at all
>> or you have so many that they don't stay in cache long enough to be
>> reused. I would read the article several times in depth as it is one
>> of the very best articles on cache behavior out there.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Hassan" <hassan@.hotmail.com> wrote in message
>> news:efT12rd4HHA.5212@.TK2MSFTNGP04.phx.gbl...
>>I see high number of compilations/sec and not recompilations/sec ?
>>Whats the difference between the two.. in other words when does it
>>compile vs when does it recompile ? I could not get a good feel about
>>this even after skimming through this article
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
>> In addition, it states "Note in particular that the query plans for
>> the batch need not have been cached. Indeed, some types of batches are
>> never cached, but can still cause recompilations. Take, for example, a
>> batch that contains a literal larger than 8 KB. Suppose that this
>> batch creates a temporary table, and then inserts 20 rows in that
>> table. The insertion of the seventh row will cause a recompilation,
>> but because of the large literal, the batch is not cached."
>> What does a literal mean ? Can someone give me the SQL for when it may
>> recompile in the above condition ? Also will this show as
>> recompilation/sec or compilation/sec in perfmon ?
>>
>>
>>
>>
>>
>

Compbined into one select?

Hi all,

I have the following:

select count([Account Number]) AS UNDER25_12_SINGLE from OH_UNDER25_12MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_12_MULTIPLE from OH_UNDER25_12MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_36_SINGLE from OH_UNDER25_36MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_36_MULTIPLE from OH_UNDER25_36MONTHS where AccountCount > 1
select count([Account Number]) AS UNDER25_60_SINGLE from OH_UNDER25_60MONTHS where AccountCount = 1
select count([Account Number]) AS UNDER25_60_MULTIPLE from OH_UNDER25_60MONTHS where AccountCount > 1

Is there anyway to combined them into one query? So I get one result?

Thanks,

Kentry the case construct

Syntax case
when search_condition then expression
[when search_condition then expression]...
[else expression]
end
case and values syntax:
case expression
when expression then expression
[when expression then expression]...
[else expression]
end
Parameters case
begins the case expression.
when
precedes the search condition or the expression to be compared.|||Can you provide a small example to make it a little clearer?

Thanks for the reply!

Ken|||Originally posted by GA_KEN
Can you provide a small example to make it a little clearer?

Thanks for the reply!

Ken
I'd have prefered to start with all the data in one table, but this will do it (if I haven't made any mistakes):

select sum(UNDER25_12_SINGLE) AS UNDER25_12_SINGLE
, sum(UNDER25_12_MLTIPLE) AS UNDER25_12_MULTIPLE
, sum(UNDER25_36_SINGLE) AS UNDER25_36_SINGLE
, sum(UNDER25_36_MLTIPLE) AS UNDER25_36_MULTIPLE
, sum(UNDER25_60_SINGLE) AS UNDER25_60_SINGLE
, sum(UNDER25_60_MLTIPLE) AS UNDER25_60_MULTIPLE
from
(
select sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_12_SINGLE
, sum( case when AccountCount > 1 then 1 else 0 end) AS UNDER25_12_MULTIPLE
, 0 AS UNDER25_36_SINGLE
, 0 AS UNDER25_36_MULTIPLE
, 0 AS UNDER25_60_SINGLE
, 0 AS UNDER25_60_MULTIPLE
from OH_UNDER25_12MONTHS
UNION ALL
select 0 AS UNDER25_12_SINGLE
, 0 AS UNDER25_12_MULTIPLE
, sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_36_SINGLE
, sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_36_MULTIPLE
, 0 AS UNDER25_60_SINGLE
, 0 AS UNDER25_60_MULTIPLE
from OH_UNDER25_36MONTHS
UNION ALL
select 0 AS UNDER25_12_SINGLE
, 0 AS UNDER25_12_MULTIPLE
, 0 AS UNDER25_36_SINGLE
, 0 AS UNDER25_36_MULTIPLE
, sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_60_SINGLE
, sum( case when AccountCount = 1 then 1 else 0 end) AS UNDER25_60_MULTIPLE
from OH_UNDER25_36MONTHS
);

Compatibility issues between SQL 7.0 and SQL 2K

Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
Sundar
Upgrade your code. SUSER_ID is a hold-over from SS 6.5, and before. With
SS 7.0, SIDs replaced SUIDs. SS 7.0 continued to support the function but
SS2K has dropped it.
However, both SS 7.0 and SS2K support SUSER_SID. So, there's your common
point. Make the switch.
Other option is to write your code with path logic using SELECT @.@.VERSION to
tell you which command to execute. However, SUID is an incompatible
attribute with SID. So, it sort of depends on how you are using it.
Sincerely,
Anthony Thomas

"Sundar" <Sundar@.discussions.microsoft.com> wrote in message
news:40344016-CAE0-4437-923A-3852D86CD3C8@.microsoft.com...
Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
Sundar

Compatibility issues between SQL 7.0 and SQL 2K

Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
SundarUpgrade your code. SUSER_ID is a hold-over from SS 6.5, and before. With
SS 7.0, SIDs replaced SUIDs. SS 7.0 continued to support the function but
SS2K has dropped it.
However, both SS 7.0 and SS2K support SUSER_SID. So, there's your common
point. Make the switch.
Other option is to write your code with path logic using SELECT @.@.VERSION to
tell you which command to execute. However, SUID is an incompatible
attribute with SID. So, it sort of depends on how you are using it.
Sincerely,
Anthony Thomas
"Sundar" <Sundar@.discussions.microsoft.com> wrote in message
news:40344016-CAE0-4437-923A-3852D86CD3C8@.microsoft.com...
Hi,
We have an application that caters to both SQL 7.0 and SQL 2k.
We use 'suser_id' to get the user Login's identification number. But this
fails in SQL 2k, coz SQL 2k supports only 'suser_sid'.
Is there anyother way out?
TIA
Sundar

Comparison SQL Server and Active Directory

Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:
> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:
> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John

Comparison SQL Server and Active Directory

Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!
Hi
"Kai" wrote:

> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data from
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts all
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of both
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John
|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.
|||Hi
"Kai" wrote:

> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will try
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John

Comparison SQL Server and Active Directory

Hi all,
we develop some asp.net 2.0 workflows which need to access several data from
our Active Directory (e.g. Telephone number, city etc.). We wonder whether
there is a recommendation how to do it best: Shall we access the data from
Active Directory directly or is it better to have a daily job which puts all
the necessary data into a SQL 2000 database and we access the SQL database
then? Especially I'm interested about the effect on the performance of both
our workflows and also our network environment or else. Are there any
official comparisons / recommendations or what are your personal opinions
about it?
Many thanks!Hi
"Kai" wrote:

> Hi all,
> we develop some asp.net 2.0 workflows which need to access several data fr
om
> our Active Directory (e.g. Telephone number, city etc.). We wonder whether
> there is a recommendation how to do it best: Shall we access the data from
> Active Directory directly or is it better to have a daily job which puts a
ll
> the necessary data into a SQL 2000 database and we access the SQL database
> then? Especially I'm interested about the effect on the performance of bot
h
> our workflows and also our network environment or else. Are there any
> official comparisons / recommendations or what are your personal opinions
> about it?
> Many thanks!
This will depend on several factors such as your AD design, network, number
of enquiries, how much latency you can have for changes. I don't know of any
comparison regarding speed or effect, but if there was, there would be the
caveat that it was on their setup and may not be reproducable on other
environments; therefore you could only really ascertain the impact by trying
it yourself and doing some controlled load testing.
If there was a significant number of enquiries then I would recommend using
SQL Server to store a copy of the information especially if you can afford a
higher latency for updated information.
John|||Hi John,
okay, many thanks. Actually I don't have exact details of our Active
Directory Setup, I hope our network admins did a good job :-) So we will try
with Active Directoy and see about the performance.|||Hi
"Kai" wrote:

> Hi John,
> okay, many thanks. Actually I don't have exact details of our Active
> Directory Setup, I hope our network admins did a good job :-) So we will t
ry
> with Active Directoy and see about the performance.
There are plenty of resources regarding extracting data from from AD and
also using ADSI for a linked server, so if it doesn't work it would be quite
easy to get the information e.g. http://www.rlmueller.net/freecode3.htm
John

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

name: initials

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?