SQL server connection: run

Showing posts with label run. Show all posts

Sunday, March 11, 2012

Another newbie question

What is the role of the SQL agent and what is the difference whether it will
run automatically or manually (I am trying to run the replication wizard)
Thanks,
Shmuel
There are several agents associated with replication and they are
essentially jobs fundamental to replication. Have a look in BOL for
replication,agents for a description of each agent. Running continuously
will help you work with miniumum latency, but you will not then have control
over when the load is placed on your server, and connectivity is mandatory.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||i'm kinda new at this too... but it seems to me that automatic means the same thing as scheduled... you can have it run once a day at a certain time, etc... manual means you want to control if and when it runs.
Andrea Garcia
Yahoo IM: mmmmojobootay
CS degree arriving in: July 2005
www.millionformarriage.com
"S Shulman" <smshulman@.hotmail.com> wrote in message news:uQ8Z4J0IFHA.2648@.TK2MSFTNGP14.phx.gbl...
What is the role of the SQL agent and what is the difference whether it will
run automatically or manually (I am trying to run the replication wizard)
Thanks,
Shmuel
|||sql server agent is the component of sql server which runs all of your
jobs. If you configure it to run automatically it will start whenever
you reboot your machine. If you configure it to run manually you will
have to manually start it.
S Shulman wrote:
> What is the role of the SQL agent and what is the difference whether it will
> run automatically or manually (I am trying to run the replication wizard)
> Thanks,
> Shmuel
>

Another nested SQL question

I am trying to get a nested SQL statement to work in my main SQL report code below. I can successfully run the nested code by itself and the main code by itself, however, I am having some trouble getting them to work together. [Note: the chart_components table and the episodes tables can be linked via the episode_key field ]

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

See the much simpler version of the query that gets the Chart_Comp_Date from my reply in your other thread. If you want just the date in the SELECT list then you need to select only that column. You are selecting the episode_key and the date. This will raise errors. So fix it like:

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

You have no correlation between the query in the SELECT list and the tables in the FROM clause. You need to reference the EPISODE_KEY from one of the outer tables also like below. Otherwise, you will get errors depending on the data.

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

select max(c1.status_date)as Chart_Comp_Dt,
c1.chart_component_ke,
c2.episode_type as Visit_Type,
c1.deficiency_type,
c1.deficiency_status,
c1.episode_key,
c2.account_number as Account_No,
c2.medrec_no as MRN,
c2.episode_date as Disch_Date,
c4.patientname as Patient_Name,
MAX(c3.event_date) as ABSCOMPDT
from srm.chart_components c1, srm.episodes c2, srm.event_history c3, dbo.PtMstr c4
where c1.EPISODE_KEY = c2.EPISODE_KEY
and c2.EPISODE_KEY = c3.ITEM_KEY
and c2.ACCOUNT_NUMBER = c4.accountnumber
and c2.episode_date between @.StartDate and @.EndDate
and c2.episode_type IN(@.visittype)
group by c1.episode_key,c1.chart_component_ke,c1.deficiency_type,
c1.deficiency_status,c1.episode_key,c2.account_number,c2.episode_type,
c2.medrec_no,c2.episode_date,c4.patientname
having (c2.episode_date < max(c1.status_date)) and
count(*) = sum(case c1.deficiency_status when 'C' then 1 end)
order by c2.episode_date desc

Another nested SQL question

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

Another nested SQL question

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--Begin nested query

(SELECT et1.episode_key, MAX(et1.status_date)

FROM srm.chart_components et1, srm.chart_components et2

WHERE et1.episode_key = et2.episode_key

AND et1.chart_component_ke = et2.chart_component_ke

AND et1.deficiency_type = et2.deficiency_type

AND et1.deficiency_status = et2.deficiency_status

AND et1.status_date = et2.status_date AND et2.deficiency_status = 'C'

GROUP by et1.episode_key

HAVING COUNT(et1.episode_key) = COUNT(et2.episode_key)) AS Chart_Comp_Date

-- End nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

Code Snippet

-- Begin nested query:

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

Thanks. The code below reflects your code snippet. However, I am receiving the following error when I run it.

"The multi-part identifier "c.deficiency_status" could not be bound."

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

-- begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

--end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

I had a mistake in the alias inside the case expression. Change c.deficiency_status to c1.deficiency_status.

|||

I just noticed that I get the same error is I run your code snippet (see below) just by itself.

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

|||

I made the change above and and now receiving this error.

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Warning: Null value is eliminated by an aggregate or other SET operation.

My code follows:

SELECT

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE AS Visit_Type,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') AS Patient_Name,

srm.EPISODES.EPISODE_DATE AS DISCHDT,

MAX(srm.EVENT_HISTORY.EVENT_DATE) AS ABSCOMPDT,

--begin nested query

(select max(c1.status_date)

from srm.chart_components as c1

group by c1.episode_key

having count(*) = sum(case c1.deficiency_status when 'C' then 1 end)) as Chart_Comp_Date

-- end nested query

FROM srm.cdmab_base_info INNER JOIN

srm.EPISODES INNER JOIN

srm.PATIENTS INNER JOIN

srm.ITEM_HEADER ON srm.PATIENTS.PATIENT_KEY = srm.ITEM_HEADER.LOGICAL_PARENT_KEY ON

srm.EPISODES.EPISODE_KEY = srm.ITEM_HEADER.ITEM_KEY INNER JOIN

srm.PATIENT_VISIT ON srm.EPISODES.EPISODE_KEY = srm.PATIENT_VISIT.EPISODE_KEY ON

srm.cdmab_base_info.EPISODE_KEY = srm.EPISODES.EPISODE_KEY INNER JOIN

srm.EVENT_HISTORY ON srm.EPISODES.EPISODE_KEY = srm.EVENT_HISTORY.ITEM_KEY INNER JOIN

srm.CHART_COMPONENTS ON srm.CHART_COMPONENTS.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

WHERE srm.CHART_COMPONENTS.DEFICIENCY_STATUS = 'C'

AND srm.EPISODES.EPISODE_DATE Between '08/06/2007' and '08/13/2007'

Group by

srm.EPISODES.MEDREC_NO,

srm.EPISODES.ACCOUNT_NUMBER,

srm.EPISODES.EPISODE_TYPE,

Isnull(ltrim(rtrim(srm.patients.patient_lname)) + ', ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_fname)) + ' ' ,'')

Isnull(ltrim(rtrim(srm.patients.patient_mname)) + ' ','')

Isnull(ltrim(rtrim(srm.patients.patient_sname)), '') ,

srm.EPISODES.EPISODE_DATE,

srm.EVENT_HISTORY.EVENT_DATE,

srm.CHART_COMPONENTS.STATUS_DATE

|||

Code Snippet

(select max()

...

where c1.EPISODE_KEY = srm.EPISODES.EPISODE_KEY

having count(*) .... ) as Chart_Comp_Date

|||

Sometimes you can look at a problem for too long and not see the answer right in front of you. I can't believe I missed this! Thank you Umachandar for all your help with this query. I appreciate it.

|||

My thanks to Umachandar, Arnie and Shawn for their help on this problem. Below is the code I ended up using in the event someone else finds themselves in a similar situation.

Thursday, March 8, 2012

Another licensing question

I have a software (weblogic) that users (I have 2500 users) connect to.
Behind Weblogic I run a SQL 2000 server standard edition.
It is only Weblogic that connects to SQL (require 5 connections)
My question is, I was told to buy a SQL processor license instead of a User
or Device CAL?
Why spend 15000$ for a processor license when I can only get 5 CALS ?
Thanks for any help
JP
JP Breton wrote:
> I have a software (weblogic) that users (I have 2500 users) connect
> to.
> Behind Weblogic I run a SQL 2000 server standard edition.
> It is only Weblogic that connects to SQL (require 5 connections)
> My question is, I was told to buy a SQL processor license instead of
> a User or Device CAL?
> Why spend 15000$ for a processor license when I can only get 5 CALS ?
> Thanks for any help
> JP
Q. How do I license SQL Server 2000 CALs in a multiplexed
environment?
A. In most cases Microsoft requires a CAL for every device that
accesses or uses the services of SQL Server 2000. If you are unsure
whether a CAL is required, you should contact your Microsoft sales
representative or account manager. Inquires can be directed to Microsoft
Licensing by calling (800)426-9400.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Another licensing question

I have a software (weblogic) that users (I have 2500 users) connect to.
Behind Weblogic I run a SQL 2000 server standard edition.
It is only Weblogic that connects to SQL (require 5 connections)
My question is, I was told to buy a SQL processor license instead of a User
or Device CAL?
Why spend 15000$ for a processor license when I can only get 5 CALS '
Thanks for any help
JPJP Breton wrote:
> I have a software (weblogic) that users (I have 2500 users) connect
> to.
> Behind Weblogic I run a SQL 2000 server standard edition.
> It is only Weblogic that connects to SQL (require 5 connections)
> My question is, I was told to buy a SQL processor license instead of
> a User or Device CAL?
> Why spend 15000$ for a processor license when I can only get 5 CALS '
> Thanks for any help
> JP
Q. How do I license SQL Server 2000 CALs in a multiplexed
environment?
A. In most cases Microsoft requires a CAL for every device that
accesses or uses the services of SQL Server 2000. If you are unsure
whether a CAL is required, you should contact your Microsoft sales
representative or account manager. Inquires can be directed to Microsoft
Licensing by calling (800)426-9400.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Another licensing question

I have a software (weblogic) that users (I have 2500 users) connect to.
Behind Weblogic I run a SQL 2000 server standard edition.
It is only Weblogic that connects to SQL (require 5 connections)
My question is, I was told to buy a SQL processor license instead of a User
or Device CAL?
Why spend 15000$ for a processor license when I can only get 5 CALS '
Thanks for any help
JPJP Breton wrote:
> I have a software (weblogic) that users (I have 2500 users) connect
> to.
> Behind Weblogic I run a SQL 2000 server standard edition.
> It is only Weblogic that connects to SQL (require 5 connections)
> My question is, I was told to buy a SQL processor license instead of
> a User or Device CAL?
> Why spend 15000$ for a processor license when I can only get 5 CALS '
> Thanks for any help
> JP
Q. How do I license SQL Server 2000 CALs in a multiplexed
environment?
A. In most cases Microsoft requires a CAL for every device that
accesses or uses the services of SQL Server 2000. If you are unsure
whether a CAL is required, you should contact your Microsoft sales
representative or account manager. Inquires can be directed to Microsoft
Licensing by calling (800)426-9400.
David Gugick
Quest Software
www.imceda.com
www.quest.com

Another error

I was trying the run the OPENROWSET stmt from QA and now I
am getting a new error:
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
error. The provider did not give any information about the
error.
OLE DB error trace [OLE/DB
Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The
provider did not give any information about the error.].
I don't know what I did because the query ran fine
before. Here's the stmt:
select * into nashMainMailing from OPENROWSET
('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=E:\200418112958.xls', [Data$])

quote:

>--Original Message--
>First, it is NOT the server / database setting - it is

the setting in effect

quote:

>when you create / alter the proc. The best way to do

this is via QA where

quote:

>you EXPLICITLY set the options needed. Using EM is an

easy way to create

quote:

>obscure problems since you can't be certain as to what

options are in effect

quote:

>at any given point in time. Learn to do everything via

QA and you will be

quote:

>better off in the long term.
>Secondly, ANSI_WARNINGS is not a setting "saved" with the

procedure. The

quote:

>setting is evaluated when the proc is executed, so your

connection must be

quote:

>setting this off. Often this is set off because

developers don't want to

quote:

>deal with the "null value eliminated from aggregate"

message. However, you

quote:

>can set this within the procedure AFAIK.
>As an aside, you can use profiler to watch the exact

commands used by EM.

quote:

>Give it a try - and be amazed.
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:025a01c3d62a$f3e04e80$a501280a@.phx.gbl...
have[QUOTE]
my[QUOTE]
ANSI_NULLS[QUOTE]
not[QUOTE]
altered.[QUOTE]
executed.[QUOTE]
SET[QUOTE]
message[QUOTE]
message "Heterogeneous[QUOTE]
options[QUOTE]
reissue[QUOTE]
don't[QUOTE]
>
>.
>

Now you are just confusing things. My suggestion. Get your query working
within query analyzer as a script. Once that works, create a stored
procedure (don't know exactly what this buys you but that is your issue) to
do the same. Then work on getting the procedure to run.
Also, why are you using sp_sqlexec? It is my understanding that this was
deprecated in v7. After looking at the source, you would be better off IMHO
using "exec (<your string> )" since that is all the procedure does. However,
that brings up the issue of separate batches. So we're back to my advice
from above. Get the basic import query working first, then "improve" it bit
by bit to match your requirements.
"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
news:051a01c3d63d$787d1bc0$a301280a@.phx.gbl...[QUOTE]
> I was trying the run the OPENROWSET stmt from QA and now I
> am getting a new error:
> OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an
> error. The provider did not give any information about the
> error.
> OLE DB error trace [OLE/DB
> Provider 'Microsoft.Jet.OLEDB.4.0'
> IDBInitialize::Initialize returned 0x80004005: The
> provider did not give any information about the error.].
> I don't know what I did because the query ran fine
> before. Here's the stmt:
> select * into nashMainMailing from OPENROWSET
> ('Microsoft.Jet.OLEDB.4.0',
> 'Excel 8.0;Database=E:\200418112958.xls', [Data$])
>
> the setting in effect
> this is via QA where
> easy way to create
> options are in effect
> QA and you will be
> procedure. The
> connection must be
> developers don't want to
> message. However, you
> commands used by EM.
> have
> my
> ANSI_NULLS
> not
> altered.
> executed.
> SET
> message
> message "Heterogeneous
> options
> reissue
> don't|||I have a tendancy to do that

I was trying to get my
query working in QA when I got the new error message.
What I have found is that somehow SQL doesn't have access
to the temp dir anymore. I don't know how that would have
changed but I will continue working on it.
Regarding sp_sqlexec, it is deprecated. However, I
couldn't get it to work any other way. Doing something
like exec "myquery" results in the following error.
The name "myquery" is not a valid identifier.
However, I think your right and I need to find a better
solution. BOL recommends "Remove or comment out all
references to sp_sqlexec." OK that does me no good. I
need an alternative.
Oh well. Thanks again for all your help.

quote:

>--Original Message--
>Now you are just confusing things. My suggestion. Get

your query working

quote:

>within query analyzer as a script. Once that works,

create a stored

quote:

>procedure (don't know exactly what this buys you but that

is your issue) to

quote:

>do the same. Then work on getting the procedure to run.
>Also, why are you using sp_sqlexec? It is my

understanding that this was

quote:

>deprecated in v7. After looking at the source, you would

be better off IMHO

quote:

>using "exec (<your string> )" since that is all the

procedure does. However,

quote:

>that brings up the issue of separate batches. So we're

back to my advice

quote:

>from above. Get the basic import query working first,

then "improve" it bit

quote:

>by bit to match your requirements.
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:051a01c3d63d$787d1bc0$a301280a@.phx.gbl...
now I[QUOTE]
the[QUOTE]
via[QUOTE]
the[QUOTE]
message[QUOTE]
recreated[QUOTE]
ideas?[QUOTE]
SET[QUOTE]
effect.[QUOTE]
and[QUOTE]
consistent[QUOTE]
OPENROWSET[QUOTE]
>
>.
>

|||To execute the query contained in a string, you need to use the correct
syntax
declare @.lc_command varchar(4000)
set @.lc_command = 'select * from ... '
exec (@.lc_command) -- the parentheses are VERY important
In general, when MS deprecates something, it usually offers upgrade
suggestions. In BOL, there is a "what's new" section that covers the
changes between versions. In the simplest case, you could merely extract
the code in the procedure and use it (see above - your syntax is the
problem). MS has also provided a new procedure which is much more
sophisticated - sp_executesql .
One last comment. Unless you are certain about your design, it is often
more useful to post the "what" of the problem, including the "how" that you
are currently using. In this case, you are trying to import data from an
excel file into the database using the most problem-prone features. Does it
need to be dynamic? Does it need to be a stored procedure? Are you certain
that SQL Server (and the account under which it runs) has access to the
file? Have you specified the location of the file correctly (remember, the
server is accessing the file and all paths are relative to that computer)?
One last comment - really. Search the NGs (particularly .programming).
Most issues have been covered to some degree in the past (along with
solutions, design ideas, and a lot of code!).
"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
news:008601c3d6d0$568c9020$a301280a@.phx.gbl...[QUOTE]
> I have a tendancy to do that

I was trying to get my
> query working in QA when I got the new error message.
> What I have found is that somehow SQL doesn't have access
> to the temp dir anymore. I don't know how that would have
> changed but I will continue working on it.
> Regarding sp_sqlexec, it is deprecated. However, I
> couldn't get it to work any other way. Doing something
> like exec "myquery" results in the following error.
> The name "myquery" is not a valid identifier.
> However, I think your right and I need to find a better
> solution. BOL recommends "Remove or comment out all
> references to sp_sqlexec." OK that does me no good. I
> need an alternative.
> Oh well. Thanks again for all your help.
> your query working
> create a stored
> is your issue) to
> understanding that this was
> be better off IMHO
> procedure does. However,
> back to my advice
> then "improve" it bit
> now I
> the
> via
> the
> message
> recreated
> ideas?
> SET
> effect.
> and
> consistent
> OPENROWSET|||I posted this reply earlier but it is still not showing up
so hear we go again.
I was running my query in QA when I got the error
message. Some searching indicates that there may be a
permissions problem in the temp dir. However, I don't
know how that changed from the morning when the query ran
fine. So I will continue working with my query in QA.
Regarding sp_sqlexec, it is deprecated. However, I
couldn't get exec ("my string") to work before because I
didn't have the parens. Thank you so much!!! The BOL only
says "Remove or comment out all references to sp_sqlexec"
without showing what an alternative should be.
Thanks again

quote:

>--Original Message--
>Now you are just confusing things. My suggestion. Get

your query working

quote:

>within query analyzer as a script. Once that works,

create a stored

quote:

>procedure (don't know exactly what this buys you but that

is your issue) to

quote:

>do the same. Then work on getting the procedure to run.
>Also, why are you using sp_sqlexec? It is my

understanding that this was

quote:

>deprecated in v7. After looking at the source, you would

be better off IMHO

quote:

>using "exec (<your string> )" since that is all the

procedure does. However,

quote:

>that brings up the issue of separate batches. So we're

back to my advice

quote:

>from above. Get the basic import query working first,

then "improve" it bit

quote:

>by bit to match your requirements.
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:051a01c3d63d$787d1bc0$a301280a@.phx.gbl...
now I[QUOTE]
the[QUOTE]
via[QUOTE]
the[QUOTE]
message[QUOTE]
recreated[QUOTE]
ideas?[QUOTE]
SET[QUOTE]
effect.[QUOTE]
and[QUOTE]
consistent[QUOTE]
OPENROWSET[QUOTE]
>
>.
>

|||Does it need to be dynamic? Yes.
Does it need to be a stored procedure? Yes.
Are you certain that SQL Server (and the account under
which it runs) has access to the file? No.
Have you specified the location of the file correctly
(remember, the server is accessing the file and all paths
are relative to that computer)? Yes.
Thanks for the tips. I'm off to search the NG for
something like "importing an excel spreadsheet into SQL".

quote:

>--Original Message--
>To execute the query contained in a string, you need to

use the correct

quote:

>syntax
>declare @.lc_command varchar(4000)
>set @.lc_command = 'select * from ... '
>exec (@.lc_command) -- the parentheses are VERY important
>In general, when MS deprecates something, it usually

offers upgrade

quote:

>suggestions. In BOL, there is a "what's new" section

that covers the

quote:

>changes between versions. In the simplest case, you

could merely extract

quote:

>the code in the procedure and use it (see above - your

syntax is the

quote:

>problem). MS has also provided a new procedure which is

much more

quote:

>sophisticated - sp_executesql .
>One last comment. Unless you are certain about your

design, it is often

quote:

>more useful to post the "what" of the problem, including

the "how" that you

quote:

>are currently using. In this case, you are trying to

import data from an

quote:

>excel file into the database using the most problem-prone

features. Does it

quote:

>need to be dynamic? Does it need to be a stored

procedure? Are you certain

quote:

>that SQL Server (and the account under which it runs) has

access to the

quote:

>file? Have you specified the location of the file

correctly (remember, the

quote:

>server is accessing the file and all paths are relative

to that computer)?

quote:

>One last comment - really. Search the NGs

(particularly .programming).

quote:

>Most issues have been covered to some degree in the past

(along with

quote:

>solutions, design ideas, and a lot of code!).
>"shiggins_dev" <shiggins_dev@.yahoo.com> wrote in message
>news:008601c3d6d0$568c9020$a301280a@.phx.gbl...
access[QUOTE]
have[QUOTE]
that[QUOTE]
would[QUOTE]
message[QUOTE]
about[QUOTE]
error.].[QUOTE]
is[QUOTE]
do[QUOTE]
an[QUOTE]
what[QUOTE]
with[QUOTE]
your[QUOTE]
both[QUOTE]
procedure.[QUOTE]
of[QUOTE]
ANSI_NULLS is[QUOTE]
stored[QUOTE]
ANSI_WARNINGS[QUOTE]
proc[QUOTE]
>
>.
>

Wednesday, March 7, 2012

another deadlock question

Is there a way to run Profiler to trap just deadlock info and not all the
info on the box? I set Error 1205 in the filters but still I got lots of
extra info.
--
SQL2K SP3
TIA, ChrisRChris,
Profiler allows you to trace the following deadlock related events:
- Lock:Deadlock
- Lock:Deadlock Chain
Personally, I find the deadlocking info you can get in the errorlog from
trace flag 1205 much more helpful. To turn on this traceflag you just run:
DBCC TRACEON (3605, 1205, -1)
1205 will return info regarding the nodes involved in a deadlock
(when/if they happen). 3605 will log that info to the errorlog. -1
will apply the trace flags to all sessions (i.e. at the server level)
rather than just for the current connection. Turning on the trace flags
this way, however, is not permanent. They'll be reset (i.e. turned off)
again the next time the you stop & restart SQL. To turn them on at
start time, add the following startup parameters to the server using SQLEM:
-T3605 -T1205
Unfortunately these particular trace flags are not documented in SQL BOL
(but they're pretty well known). But BOL does have a little info about
DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
options).
Cheers,
Mike
ChrisR wrote:
> Is there a way to run Profiler to trap just deadlock info and not all the
> info on the box? I set Error 1205 in the filters but still I got lots of
> extra info.
> --
> SQL2K SP3
> TIA, ChrisR
>|||> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
> > Is there a way to run Profiler to trap just deadlock info and not all
the
> > info on the box? I set Error 1205 in the filters but still I got lots of
> > extra info.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >|||The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
the extra information: 1205 export the analysis for every deadlock search,
which is a lock scan looking for a loop, regardless if it finds one. Only
use 1205 if you are have serious deadlock issues and need preemptive
information; otherwise, 1204 should give you sufficient information from any
detected deadlock candidates.
Sincerely,
Anthony Thomas
"ChrisR" <bla@.noemail.com> wrote in message
news:u8DRDRk5EHA.1396@.tk2msftngp13.phx.gbl...
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
> > Is there a way to run Profiler to trap just deadlock info and not all
the
> > info on the box? I set Error 1205 in the filters but still I got lots of
> > extra info.
> >
> > --
> > SQL2K SP3
> >
> > TIA, ChrisR
> >
> >|||Yep, I always get 1204 & 1205 mixed up - I usually just turn them on one
at a time and you can tell in about 3 seconds which is the right one by
looking at the errorlog (I was going from memory this time). But, yes,
1204 is the traceflag I meant (otherwise, with 1205, the errorlog fills
up rapidly with fairly useless deadlock cycle detection crap).
My apologies for the errata.
Basically, Chris, profiler will not give you enough info to troubleshoot
your deadlocks (at least not without tracing pretty much everything,
which makes finding the info you're after very difficult). You're
better off turning on the traceflag and checking the errorlog next time
a deadlock occurs.
(It's been my observation that the 2 most common questions on
microsoft.public.sqlserver.server are 1) resolving/troubleshooting
deadlocks and 2) shrinking the transaction log.)
Cheers,
Mike.
AnthonyThomas wrote:
> The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
> the extra information: 1205 export the analysis for every deadlock search,
> which is a lock scan looking for a loop, regardless if it finds one. Only
> use 1205 if you are have serious deadlock issues and need preemptive
> information; otherwise, 1204 should give you sufficient information from any
> detected deadlock candidates.
> Sincerely,
>
> Anthony Thomas
>

another deadlock question

Is there a way to run Profiler to trap just deadlock info and not all the
info on the box? I set Error 1205 in the filters but still I got lots of
extra info.
SQL2K SP3
TIA, ChrisR
Chris,
Profiler allows you to trace the following deadlock related events:
- Lock:Deadlock
- Lock:Deadlock Chain
Personally, I find the deadlocking info you can get in the errorlog from
trace flag 1205 much more helpful. To turn on this traceflag you just run:
DBCC TRACEON (3605, 1205, -1)
1205 will return info regarding the nodes involved in a deadlock
(when/if they happen). 3605 will log that info to the errorlog. -1
will apply the trace flags to all sessions (i.e. at the server level)
rather than just for the current connection. Turning on the trace flags
this way, however, is not permanent. They'll be reset (i.e. turned off)
again the next time the you stop & restart SQL. To turn them on at
start time, add the following startup parameters to the server using SQLEM:
-T3605 -T1205
Unfortunately these particular trace flags are not documented in SQL BOL
(but they're pretty well known). But BOL does have a little info about
DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
options).
Cheers,
Mike
ChrisR wrote:
> Is there a way to run Profiler to trap just deadlock info and not all the
> info on the box? I set Error 1205 in the filters but still I got lots of
> extra info.
> --
> SQL2K SP3
> TIA, ChrisR
>
|||> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:[vbcol=seagreen]
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
the[vbcol=seagreen]
|||The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
the extra information: 1205 export the analysis for every deadlock search,
which is a lock scan looking for a loop, regardless if it finds one. Only
use 1205 if you are have serious deadlock issues and need preemptive
information; otherwise, 1204 should give you sufficient information from any
detected deadlock candidates.
Sincerely,
Anthony Thomas

"ChrisR" <bla@.noemail.com> wrote in message
news:u8DRDRk5EHA.1396@.tk2msftngp13.phx.gbl...
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
Thanks Mike. Im using these. The problem is that I'm still getting back more
info than I like. Which is why Im trying to filter.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:ug60Q4i5EHA.3336@.TK2MSFTNGP11.phx.gbl...
> Chris,
> Profiler allows you to trace the following deadlock related events:
> - Lock:Deadlock
> - Lock:Deadlock Chain
> Personally, I find the deadlocking info you can get in the errorlog from
> trace flag 1205 much more helpful. To turn on this traceflag you just
run:
> DBCC TRACEON (3605, 1205, -1)
> 1205 will return info regarding the nodes involved in a deadlock
> (when/if they happen). 3605 will log that info to the errorlog. -1
> will apply the trace flags to all sessions (i.e. at the server level)
> rather than just for the current connection. Turning on the trace flags
> this way, however, is not permanent. They'll be reset (i.e. turned off)
> again the next time the you stop & restart SQL. To turn them on at
> start time, add the following startup parameters to the server using
SQLEM:[vbcol=seagreen]
> -T3605 -T1205
> Unfortunately these particular trace flags are not documented in SQL BOL
> (but they're pretty well known). But BOL does have a little info about
> DBCC TRACEON, DBCC TRACEOFF & DBCC TRACESTATUS (as well as SQL startup
> options).
> Cheers,
> Mike
> ChrisR wrote:
the[vbcol=seagreen]
|||Yep, I always get 1204 & 1205 mixed up - I usually just turn them on one
at a time and you can tell in about 3 seconds which is the right one by
looking at the errorlog (I was going from memory this time). But, yes,
1204 is the traceflag I meant (otherwise, with 1205, the errorlog fills
up rapidly with fairly useless deadlock cycle detection crap).
My apologies for the errata.
Basically, Chris, profiler will not give you enough info to troubleshoot
your deadlocks (at least not without tracing pretty much everything,
which makes finding the info you're after very difficult). You're
better off turning on the traceflag and checking the errorlog next time
a deadlock occurs.
(It's been my observation that the 2 most common questions on
microsoft.public.sqlserver.server are 1) resolving/troubleshooting
deadlocks and 2) shrinking the transaction log.)
Cheers,
Mike.
AnthonyThomas wrote:
> The deadlock reporting traceflag is 1204 NOT 1205. That is the source of
> the extra information: 1205 export the analysis for every deadlock search,
> which is a lock scan looking for a loop, regardless if it finds one. Only
> use 1205 if you are have serious deadlock issues and need preemptive
> information; otherwise, 1204 should give you sufficient information from any
> detected deadlock candidates.
> Sincerely,
>
> Anthony Thomas
>

Another Date time question

I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.This is a multi-part message in MIME format.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
convert (char (8), getdate(), 112)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Charlie" <ckerns@.hotmail.com> wrote in message =news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index (Primary key). I need to insert only the date part(not the time) so that when I run my DTS package it only inserts one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.
--=_NextPart_000_030E_01C37B89.95A72100
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try:
convert (char (8), getdate(), 112)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Charlie" wrote in =message news:446c01c37baa$88=07dfa0$a601280a@.phx.gbl...I have a datetime column. This column has an index (Primary key). I =need to insert only the date part(not the time) so that when I run my DTS =package it only inserts one date (TODAY's DATE) without the time. How =can I insert today's date with only date part ?Thanks.

--=_NextPart_000_030E_01C37B89.95A72100--|||This will do it:
SELECT CAST(CONVERT(char, CURRENT_TIMESTAMP, 112) AS datetime)
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Charlie" <ckerns@.hotmail.com> wrote in message
news:446c01c37baa$8807dfa0$a601280a@.phx.gbl...
I have a datetime column. This column has an index
(Primary key). I need to insert only the date part(not the
time) so that when I run my DTS package it only inserts
one date (TODAY's DATE) without the time.
How can I insert today's date with only date part ?
Thanks.

Saturday, February 25, 2012

Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
Josh
Hi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:

> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>
|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.
|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:

> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>
|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:[vbcol=seagreen]
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poor
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most common
causes of intermittently slow queries.
John
"JXStern" wrote:

> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>

Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
JoshHi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:

> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:

> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:[vbcol=seagreen]
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
>|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poo
r
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most commo
n
causes of intermittently slow queries.
John
"JXStern" wrote:

> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
>

Anomalous heavy reads when heavily loaded

In a large-scale tuning exercise we are repeatedly seeing anomalous
behaviors - small (or large) SPs that run fine on all data values when
run as single transactions, but which repeatably runs with 1000x more
reads in at least one place on a trace.
We are producing the load by reruning a trace via profiler, so we
expect the test box to be maxed out, and it is.
When I say "repeatedly", I have not verified that it's always the same
transactions that are running badly. Yes, of course it *may* be the
overall state of the database at that point in time that "is the
problem", but at least one of these SPs is a fairly simple piece of
business that shouldn't be that sensitive to ANY possible data
configurations.
It has been suggested that it is an optimizer FEATURE that it will
produce different plans when it sees the system is heavily loaded. I
was not aware of that. Can anybody tell me just what it has to see to
decide on this load? I heard it might be CPU, and synthesized some
bogus CPU loads to see if that would make my little SPs misbehave, but
so far that has not worked - the SP (the simplest one of about a dozen
I've seen doing this so far) insists on running in a normal 100 reads
instead of the 1.7m reads it displays occassionally in the trace.
Just looking for anyone who has even seen similar anomalies when
testing under load, I'm not even asking for further diagnostics,
though anything you have I'd like to hear.
Thanks.
JoshHi
Have you read the section on parameter sniffing in Ken Henderson's
"The Guru's Guide to SQL Server Architecture and Internals" ISBN
0-201-70047-6
also check out the post http://tinyurl.com/983uf
John
"jxstern" wrote:
> In a large-scale tuning exercise we are repeatedly seeing anomalous
> behaviors - small (or large) SPs that run fine on all data values when
> run as single transactions, but which repeatably runs with 1000x more
> reads in at least one place on a trace.
> We are producing the load by reruning a trace via profiler, so we
> expect the test box to be maxed out, and it is.
> When I say "repeatedly", I have not verified that it's always the same
> transactions that are running badly. Yes, of course it *may* be the
> overall state of the database at that point in time that "is the
> problem", but at least one of these SPs is a fairly simple piece of
> business that shouldn't be that sensitive to ANY possible data
> configurations.
> It has been suggested that it is an optimizer FEATURE that it will
> produce different plans when it sees the system is heavily loaded. I
> was not aware of that. Can anybody tell me just what it has to see to
> decide on this load? I heard it might be CPU, and synthesized some
> bogus CPU loads to see if that would make my little SPs misbehave, but
> so far that has not worked - the SP (the simplest one of about a dozen
> I've seen doing this so far) insists on running in a normal 100 reads
> instead of the 1.7m reads it displays occassionally in the trace.
> Just looking for anyone who has even seen similar anomalies when
> testing under load, I'm not even asking for further diagnostics,
> though anything you have I'd like to hear.
> Thanks.
> Josh
>|||On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Have you read the section on parameter sniffing in Ken Henderson's
>"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>0-201-70047-6
>also check out the post http://tinyurl.com/983uf
I'm aware of sniffing, but I don't see how it can be that when the
same parameters run later give (very!) different runtimes.
(benchmark process does not show or validate results).
J.|||Hi
Have you looked at the locking events in SQL Profiler?
John
"jxstern" wrote:
> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Have you read the section on parameter sniffing in Ken Henderson's
> >"The Guru's Guide to SQL Server Architecture and Internals" ISBN
> >0-201-70047-6
> >also check out the post http://tinyurl.com/983uf
> I'm aware of sniffing, but I don't see how it can be that when the
> same parameters run later give (very!) different runtimes.
> (benchmark process does not show or validate results).
> J.
>|||Have not done that, but I'm curious, could that possibly explain the
situation?
What lock-checking we've done in the way of investigating performance
has shown locking and waits to be very rare, almost impossible it
should correspond to the incidences we're seeing here.
J.
On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
<jbellnewsposts@.hotmail.com> wrote:
>Hi
>Have you looked at the locking events in SQL Profiler?
>John
>"jxstern" wrote:
>> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
>> <jbellnewsposts@.hotmail.com> wrote:
>> >Have you read the section on parameter sniffing in Ken Henderson's
>> >"The Guru's Guide to SQL Server Architecture and Internals" ISBN
>> >0-201-70047-6
>> >also check out the post http://tinyurl.com/983uf
>> I'm aware of sniffing, but I don't see how it can be that when the
>> same parameters run later give (very!) different runtimes.
>> (benchmark process does not show or validate results).
>> J.
>>|||Hi
The abnormal number of reads would not be more likely to be a poor query
plan, but if you have ruled out recompiles/parameter sniffing/missing or poor
stats/fragmented indexes, you need to look elsewhere and although it may not
explain the higher number of reads locking/blocking is one of the most common
causes of intermittently slow queries.
John
"JXStern" wrote:
> Have not done that, but I'm curious, could that possibly explain the
> situation?
> What lock-checking we've done in the way of investigating performance
> has shown locking and waits to be very rare, almost impossible it
> should correspond to the incidences we're seeing here.
> J.
>
> On Thu, 20 Oct 2005 23:27:02 -0700, John Bell
> <jbellnewsposts@.hotmail.com> wrote:
> >Hi
> >
> >Have you looked at the locking events in SQL Profiler?
> >
> >John
> >
> >"jxstern" wrote:
> >
> >> On Thu, 20 Oct 2005 00:50:03 -0700, John Bell
> >> <jbellnewsposts@.hotmail.com> wrote:
> >> >Have you read the section on parameter sniffing in Ken Henderson's
> >> >"The Guru's Guide to SQL Server Architecture and Internals" ISBN
> >> >0-201-70047-6
> >> >also check out the post http://tinyurl.com/983uf
> >>
> >> I'm aware of sniffing, but I don't see how it can be that when the
> >> same parameters run later give (very!) different runtimes.
> >>
> >> (benchmark process does not show or validate results).
> >>
> >> J.
> >>
> >>
>

Announcing SQL Server Everywhere Edition

Today we are announcing SQL Server Everywhere Edition, a light weight database designed for client applications that run from desktops to mobile devices. Pl see the section 'Dynamic Applications' in the Microsoft's Data Platform Vision and Roadmap from Paul Flessner, Sr VP, Microsoft.

Microsoft’s Data Platform Vision and Roadmap: Your Data, Any Place, Any TimeMicrosoft’s Data Platform Vision and Roadmap: Your Data, Any Place, Any Time

http://www.microsoft.com/sql/letter.mspx

The new product is based on the familiar SQL Server Mobile Edition.

-Durga Gudipati

Program Manager, SQL Server Everywhere Edition

We are in the very begining of migration to 3rd party solution for storing user documents. We currently use SQLCE2.0 and we were terribly disapointed that MS decided not to license its interoperability of SQLCE3.0 databases across all platforms.

I need to know the licensing policy of the new product before May 16th. Is it going to be royalty-based or similar to SQL Mobile (i.e. virtually free)?

|||

Can you please provide more information than this letter

|||

some more info but not much

http://blogs.msdn.com/search/SearchResults.aspx?q=SQL+Server+Everywhere+Edition&o=Relevance

|||

SQL Everywhere (SQL Ev) will be free on desktops, laptops/tablets and mobile devices running MS OS.

The first CTP will be in summer and release will be by end of the year.

Once released, SQL Everywhere replaces SQL Mobile.

-DurgaG

Annotated Schema Not Working

When I attempt to use Annotated Schemas, either the
Northwind examples or one's I have written myself they
will not run. If I run templates that don't use the
Annotated Schemas or URL queries everything works fine,
but, the Annotated Schemas don't. The errors that come
back are page cannot be found when I add an XML Path to
the URL query to the annotated schema. Can anybody help,
I have tried to get this working for a couple of weeks?
I am about ready to give up.
A couple of things to check (assuming you're talking about using a schema
through an IIS virtual root):
Have you enabled XPath Queries in the virtual root?
Have you put the schema in a virtual name of type "schema"?
Graeme Malcolm
Principal Technologist
Content Master Ltd.
www.contentmaster.com
www.microsoft.com/mspress/books/6137.asp
"Mike" <mojodallas@.msn.com> wrote in message
news:1c5f01c4465e$5a415e00$7d02280a@.phx.gbl...
> When I attempt to use Annotated Schemas, either the
> Northwind examples or one's I have written myself they
> will not run. If I run templates that don't use the
> Annotated Schemas or URL queries everything works fine,
> but, the Annotated Schemas don't. The errors that come
> back are page cannot be found when I add an XML Path to
> the URL query to the annotated schema. Can anybody help,
> I have tried to get this working for a couple of weeks?
> I am about ready to give up.

Friday, February 24, 2012

Annotated Schema Not Working

Thursday, February 16, 2012

Analysis Services Rolling Date reports

I have a Time and Billing OLAP cube which I'm running RS against. I'm
stumped on a date issue.
How can I run a report which selects the last 7 days worth of data and rolls
forward each week? I've tried using an MDX query with the Tail funtion.
This pulls the last date which has data in it. (Effectively yesterday's
date) When I run it in MDX builder it gives me the desired results. However
when I use it in VS.Net to build my report, it pulls the last 7 days over,
but it's static and doesn't roll forward.
I can make a report with a date drop down parameter which is described in
the AS and RS article on Technet, but I'd really like the parameter to be
automatically selected based off of the date when the report is run.
My date formats in my cube are [Year].[Quarter].[Month].[Day]
Can anybody help me with this?
Thanks, MattTry setting defaults for your start and end date parameters. For
example, you can set the end date to
DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
parameters in subscription creation, it may look like they are
hard-coded, but they aren't as long as the default checkbox is selected.|||Thanks for the suggestion... I think I'm close...
The problem is that my date needs to be in a [Time].[FY Calendar].[All
Time].[Year].[Quarter].[Month].[Day] format.
As you probably can tell, I'm a newbie to VB Scripting. How can I convert
your suggestion "DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy")" to my cube
format? I think if I can do that, I can get it to work.
Thanks,
Matt
"Kenny" wrote:
> Try setting defaults for your start and end date parameters. For
> example, you can set the end date to
> DateTime.Now.AddDays(-1).ToString("MM/dd/yyyy") and the start date to
> DateTime.Now.AddDays(-7).ToString("MM/dd/yyyy"). When you see the
> parameters in subscription creation, it may look like they are
> hard-coded, but they aren't as long as the default checkbox is selected.
>

Monday, February 13, 2012

Analysis Services Processing task: logging and error handling

I have an Analysis Services Processing Task in my SSIS package. I run the SSIS package using SQL Server job, the running of the package is a job step.

When I process manually the analysis services objects (in practise cubes) using dtexec utility I get a lot of log. In case the processing fails I get error messages that quite well describe the error. But when I run the job the only information I get in the job log is that the job step failed. I know the failure happens in the Analysis Services Processing Task.

Is there any way in SSIS to get a) the log of the Analysis Services processing or b) the error messages of the Analysis Services processing? Or should the processing be done some other way than I've been doing?

JM_F wrote:

I have an Analysis Services Processing Task in my SSIS package. I run the SSIS package using SQL Server job, the running of the package is a job step.
When I process manually the analysis services objects (in practise cubes) using dtexec utility I get a lot of log. In case the processing fails I get error messages that quite well describe the error. But when I run the job the only information I get in the job log is that the job step failed. I know the failure happens in the Analysis Services Processing Task.
Is there any way in SSIS to get a) the log of the Analysis Services processing or b) the error messages of the Analysis Services processing? Or should the processing be done some other way than I've been doing?

I recommend you take a read of this:

Scheduled packages
http://wiki.sqlis.com/default.aspx/SQLISWiki/ScheduledPackages.html

-Jamie

|||

I have the Analysis Services Processing Task in the middle of the SISS package, like this:

Task A: execute SQL
Task B: script task
Task C: Analysis Services Processing task
Task D: script task

And the job I have only contains the SSIS package.

By following the idea in the link you sent, I'd have three job steps like this:

1st step: a new SSIS package consisting of tasks A and B
2nd step: analysis services processing using CmdExec
3nd step: a new SSIS package consisting of task D

This doesn't seem to be a clean solution, since I end having three physical SSIS packages to perform a logical work of one package.

What came to my mind was to use Script Task and Analysis Management Objects (AMO) to process the Analysis Services database objects. I guess AMO library should give better means for error handling and logging than Analysis Services Processing Task.

|||

I think we may be misunderstanding each other here. The point of my earlier post was to alert you that the output when running the job using SSIS subsystem isn't very good so you should use cmdexec instead.

On another note, what sort of logging are you doing from within your package?

-Jamie

|||

Jamie Thomson wrote:

On another note, what sort of logging are you doing from within your package?

Actually I'm doing logging to an application specifig log table and currently not using SSIS package logging at all.

Sunday, March 11, 2012

Another newbie question

Another nested SQL question

Another nested SQL question

Another nested SQL question

Thursday, March 8, 2012

Another licensing question

Another licensing question

Another licensing question

Another error

Wednesday, March 7, 2012

another deadlock question

another deadlock question

Another Date time question

Saturday, February 25, 2012

Anomalous heavy reads when heavily loaded

Anomalous heavy reads when heavily loaded

Anomalous heavy reads when heavily loaded

Announcing SQL Server Everywhere Edition

Annotated Schema Not Working

Friday, February 24, 2012

Annotated Schema Not Working

Thursday, February 16, 2012

Analysis Services Rolling Date reports

Monday, February 13, 2012

Analysis Services Processing task: logging and error handling

SQL server connection

Blog Archive

About Me