Procedure Time out: process

Showing posts with label process. Show all posts

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Our production SQL Server's CPU runs continuously at about
50%. Below is a copy of the running Process threads, taken
from SQL Spotlight. Can anyone help identify the
bottleneck and give recommendations for improvement
(besides improving queries)' Your help is greatly
appreciated!
Instance Thread ID % CPU % User % Kernel
Elapsed time Switches/sec Thread State
Thread Wait Reason
5 268 104.65 0 106.32 19d 10hrs 207
921.65 Waiting Waiting for a user request
0 0 52.92 0 52.92 147417d 19hrs
807.51 Running -
1 0 50.18 0 50.18 147417d 19hrs
471.7 Running -
3 0 48.15 0 48.15 147417d 19hrs
495.51 Running -
6 272 40.76 0 42.91 19d 10hrs 0
Waiting Waiting for a user request
2 0 36.47 0 36.47 147417d 19hrs
710.07 Running -
15 10268 3 594.66 405.72 3 188.94 9d
20hrs 215 661.8 Waiting Waiting for a user
request
5 344 1.91 0.36 1.55 19d 10hrs 1
134.55 Waiting Waiting for a user request
1 252 1 906.93 335.88 1 571.05 19d
10hrs 229 163.43 Waiting Waiting for an LPC
Receive notice
4 204 1 830.41 266.39 1 564.01 19d
10hrs 133 979.34 Waiting Waiting for an LPC
Receive notice
8 488 1 810.26 278.07 1 532.19 19d
10hrs 0 Waiting Waiting for an LPC Receive
notice
11 4196 1 683.68 288.32 1 395.36 17d
17hrs 197 806.72 Waiting Waiting for an LPC
Receive notice
12 1408 1 612.76 281.77 1 331 17d
17hrs 163 851.13 Waiting Waiting for an LPC
Receive notice
13 4264 1 596.43 286.41 1 310.02 17d
17hrs 135 660.06 Waiting Waiting for an LPC
Receive notice
0 11584 0.79 0.79 0 1d 6hrs
20.82 Waiting Waiting for a user request
4 276 0.36 0 0.36 19d 10hrs
58.45 Waiting Waiting for a user request
3 228 0.12 0 0.12 19d 10hrs 9
Waiting Waiting for an LPC Receive notice
10 2756 0.12 0 0.12 19d 8hrs
2.67 Waiting Waiting for a user requestCome and check ours. It is running at 90% most of the time.
CPU usage depends on the transactions occuring in the
Server (Unless there are other applications running on the
server). I wouldn't be too concern about it at 50%. If you
are, use the SQL Profiler to find out what processes using
the most CPU usage(the number on Profiler is in
miliseconds).
>--Original Message--
>Our production SQL Server's CPU runs continuously at
about
>50%. Below is a copy of the running Process threads,
taken
>from SQL Spotlight. Can anyone help identify the
>bottleneck and give recommendations for improvement
>(besides improving queries)' Your help is greatly
>appreciated!
>Instance Thread ID % CPU % User % Kernel
> Elapsed time Switches/sec Thread State
> Thread Wait Reason
>5 268 104.65 0 106.32 19d 10hrs 207
> 921.65 Waiting Waiting for a user request
>0 0 52.92 0 52.92 147417d 19hrs
> 807.51 Running -
>1 0 50.18 0 50.18 147417d 19hrs
> 471.7 Running -
>3 0 48.15 0 48.15 147417d 19hrs
> 495.51 Running -
>6 272 40.76 0 42.91 19d 10hrs 0
> Waiting Waiting for a user request
>2 0 36.47 0 36.47 147417d 19hrs
> 710.07 Running -
>15 10268 3 594.66 405.72 3 188.94 9d
> 20hrs 215 661.8 Waiting Waiting for a user
>request
>5 344 1.91 0.36 1.55 19d 10hrs 1
> 134.55 Waiting Waiting for a user request
>1 252 1 906.93 335.88 1 571.05 19d
> 10hrs 229 163.43 Waiting Waiting for an LPC
>Receive notice
>4 204 1 830.41 266.39 1 564.01 19d
> 10hrs 133 979.34 Waiting Waiting for an LPC
>Receive notice
>8 488 1 810.26 278.07 1 532.19 19d
> 10hrs 0 Waiting Waiting for an LPC Receive
>notice
>11 4196 1 683.68 288.32 1 395.36 17d
> 17hrs 197 806.72 Waiting Waiting for an LPC
>Receive notice
>12 1408 1 612.76 281.77 1 331 17d
> 17hrs 163 851.13 Waiting Waiting for an LPC
>Receive notice
>13 4264 1 596.43 286.41 1 310.02 17d
> 17hrs 135 660.06 Waiting Waiting for an LPC
>Receive notice
>0 11584 0.79 0.79 0 1d 6hrs
> 20.82 Waiting Waiting for a user request
>4 276 0.36 0 0.36 19d 10hrs
> 58.45 Waiting Waiting for a user request
>3 228 0.12 0 0.12 19d 10hrs 9
> Waiting Waiting for an LPC Receive notice
>10 2756 0.12 0 0.12 19d 8hrs
> 2.67 Waiting Waiting for a user request
>
>.
>|||However, low CPU usage can be because the machine is waiting for I/O requests a lot. Tuning the
queries, by for instance adding indexes so less I/O is used can be a real benefit in these cases.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"James" <anonymous@.discussions.microsoft.com> wrote in message
news:6e2901c48392$136f9480$a401280a@.phx.gbl...
> Come and check ours. It is running at 90% most of the time.
> CPU usage depends on the transactions occuring in the
> Server (Unless there are other applications running on the
> server). I wouldn't be too concern about it at 50%. If you
> are, use the SQL Profiler to find out what processes using
> the most CPU usage(the number on Profiler is in
> miliseconds).
>
> >--Original Message--
> >Our production SQL Server's CPU runs continuously at
> about
> >50%. Below is a copy of the running Process threads,
> taken
> >from SQL Spotlight. Can anyone help identify the
> >bottleneck and give recommendations for improvement
> >(besides improving queries)' Your help is greatly
> >appreciated!
> >
> >Instance Thread ID % CPU % User % Kernel
> > Elapsed time Switches/sec Thread State
> > Thread Wait Reason
> >5 268 104.65 0 106.32 19d 10hrs 207
> > 921.65 Waiting Waiting for a user request
> >
> >0 0 52.92 0 52.92 147417d 19hrs
> > 807.51 Running -
> >1 0 50.18 0 50.18 147417d 19hrs
> > 471.7 Running -
> >3 0 48.15 0 48.15 147417d 19hrs
> > 495.51 Running -
> >6 272 40.76 0 42.91 19d 10hrs 0
> > Waiting Waiting for a user request
> >
> >2 0 36.47 0 36.47 147417d 19hrs
> > 710.07 Running -
> >15 10268 3 594.66 405.72 3 188.94 9d
> > 20hrs 215 661.8 Waiting Waiting for a user
> >request
> >5 344 1.91 0.36 1.55 19d 10hrs 1
> > 134.55 Waiting Waiting for a user request
> >
> >1 252 1 906.93 335.88 1 571.05 19d
> > 10hrs 229 163.43 Waiting Waiting for an LPC
> >Receive notice
> >4 204 1 830.41 266.39 1 564.01 19d
> > 10hrs 133 979.34 Waiting Waiting for an LPC
> >Receive notice
> >8 488 1 810.26 278.07 1 532.19 19d
> > 10hrs 0 Waiting Waiting for an LPC Receive
> >notice
> >11 4196 1 683.68 288.32 1 395.36 17d
> > 17hrs 197 806.72 Waiting Waiting for an LPC
> >Receive notice
> >12 1408 1 612.76 281.77 1 331 17d
> > 17hrs 163 851.13 Waiting Waiting for an LPC
> >Receive notice
> >13 4264 1 596.43 286.41 1 310.02 17d
> > 17hrs 135 660.06 Waiting Waiting for an LPC
> >Receive notice
> >0 11584 0.79 0.79 0 1d 6hrs
> > 20.82 Waiting Waiting for a user request
> >
> >4 276 0.36 0 0.36 19d 10hrs
> > 58.45 Waiting Waiting for a user request
> >
> >3 228 0.12 0 0.12 19d 10hrs 9
> > Waiting Waiting for an LPC Receive notice
> >
> >10 2756 0.12 0 0.12 19d 8hrs
> > 2.67 Waiting Waiting for a user request
> >
> >
> >.
> >

Friday, March 23, 2012

Proclarity - Inatalation 64 bit problem!?

Hi guys...

During the Process of installation of software PROCLARITY Analytics Server 6,3, with a profile of bit schemes in architecture 64. I am with a problem:

"This installation will disable 64-bit Support on Internet Information Services (ISS). Existing 64-bit application will no longer function. Click YES to proceed or NO to cancel the installation."

How I can decide? To install the program without damages for the ISS of architecture 64 bit

I thank attention!

Not all components of SQL Server comes in 64bits but IIS comes in 64bits so I am assuming Proclarity like Reporting services require IIS to run but if it comes only in 32bits then IIS must run in WOW64 to run Proclarity. So if you need IIS in 64bits then you need a separate 32bits box to run SQL Server with Proclarity. Hope this helps.

|||

Hi Diogo,

We are using ProClarity 6.2 on 64-bit IIS boxes and we have to configure IIS to run in 32-bit mode in order to install the ProClarity application. It may be the same for ProClarity 6.3.

This is how you configure IIS to run in 32-bit mode on a 64-bit machine :

How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows

http://support.microsoft.com/default.aspx/kb/894435

Regards,

Paul

|||Did you ever get this problem resolved? When I attempt to install ProClarity Analytics Server 6.4 onto a 64-bit version of Windows, I'm immediately prompted with the following error:

Missing Product Requirements

The following requirements are missing:
- The following IIS Web Service Extensions must be set to allowed (see PAS Setup Guide);

Server Side Includes
Active Server Pages
All Unknown ISAPI Extensions

I'm unable to even begin the installation, never mind complete it.
|||

Hi Brian,

ProClarity does indeed require that you enable Server Side Includes, Active Server Pages and All Unknown ISAPI Extensions for the installation to proceed.

I haven't used version 6.4 but version 6.2 requires that you run IIS in 32-bit mode.

|||

I am running IIS 6.0 in 32-bit mode (per the methods described in these postings, as well as having enabled the three web extensions asked for. The odd part is, I attempted this install on a clean 32-bit Windows Server 2003 installation and received the same error. There must be something else that I'm missing in the installation process, but for the life of my, I can't figure it out. I even followed the step-by-step instructions as detailed in the ProClarity setup guide and can't get it to install.

|||

Brian,

OK, going back to the 64-bit installation, where exactly does the installation fail and with what error message ?

Have you enabled the 32-bit Asp.Net 2.0 web service extension ?

Paul

|||

I'm running "setup.exe" from within the \INSTALL\PAS folder, by I've also tried it from running "setup.exe" at the root of the ProClarity CD. It is failing as soon as I click "Next" when I choose to install:

Full product, which includes:

- Analytics Server software

- Analytics Server database

- Administration Tool

The error message is exactly:

Missing Product Requirements
The following requirements are missing:
- The following IIS Web Service Extensions must be set to allowed (see PAS Setup Guide);

Server Side Includes
Active Server Pages
All Unknown ISAPI Extensions

My only option at that point is to click the "Back" or "Finish" button.

I've enabled the 32-bit ASP.NET 2.0 web service extension as detailed in http://support.microsoft.com/default.aspx/kb/894435, as well as the the extensions listed in the error message.

|||

Brian,

Dumb question (but I've got to ask it) - are those extensions actually enabled in IIS ?

Is the status set to Allowed as opposed to Prohibited ?

I have seen this error message before during an installation but it was because the extensions weren't enabled.

Paul

|||

They are set to "Allowed".

|||

Hi Guys...

On the problem in the installation of the Proclarity in the environment 64bit′s (where the Proclarity automatically converts the IIS for 32bit′s), the only alternative was the creation of a virtual machine with the application all installed in 32bit′s.

Thanks for Attention ....

|||

Unfortunately I received nearly the same error message on just such a machine (a Windows Server 2003 R2 Standard OS installed in VMWare):

Missing Product Requirements
The following requirements are missing:
- The following IIS Web Service Extensions must be set to allowed (see PAS Setup Guide);

Server Side Includes
Active Server Pages

|||

Hi Brian,

This is not related to Proclarity but Win2003 installs IIS that can only render HTML by default so you need to remove the one you have added and when you add it the next time click on details and you will see most of the other Asp.net related components. If you did not see the Web Service stuff click on details again and you will see them. Hope this helps.

|||

I removed IIS from the 32-bit machine and reinstalled, making sure this time to select all of the components of IIS. After the installation, I checked the web service extensions and confirmed that they are all set to "Allowed". When I attempt to install ProClarity Analytics Server 6.3, I still get the same error message.

Scratching my head on this one. I have PAS 6.2 installed on another system (Win2003 R2 Standard 32-bit) and never had this issue. Could there be something wrong with the 6.3 version I downloaded from the MSDN site? I can understand this issue occurring on the 64-bit OS, but it should work just fine on the 32-bit one.

|||

Hi Brian,

If your virtual machine is Win2003 32bits as Diogo A. Di Pietro then I don't think allowed is needed because both are 32bits. Just a thought you may remove the allowed and try the installation again.

sql

Proclarity - Inatalation 64 bit problem!?

Hi guys...

During the Process of installation of software PROCLARITY Analytics Server 6,3, with a profile of bit schemes in architecture 64. I am with a problem:

"This installation will disable 64-bit Support on Internet Information Services (ISS). Existing 64-bit application will no longer function. Click YES to proceed or NO to cancel the installation."

How I can decide? To install the program without damages for the ISS of architecture 64 bit

I thank attention!

|||

Hi Diogo,

We are using ProClarity 6.2 on 64-bit IIS boxes and we have to configure IIS to run in 32-bit mode in order to install the ProClarity application. It may be the same for ProClarity 6.3.

This is how you configure IIS to run in 32-bit mode on a 64-bit machine :

How to switch between the 32-bit versions of ASP.NET 1.1 and the 64-bit version of ASP.NET 2.0 on a 64-bit version of Windows

http://support.microsoft.com/default.aspx/kb/894435

Regards,

Paul

|||Did you ever get this problem resolved? When I attempt to install ProClarity Analytics Server 6.4 onto a 64-bit version of Windows, I'm immediately prompted with the following error:

Missing Product Requirements

The following requirements are missing:
- The following IIS Web Service Extensions must be set to allowed (see PAS Setup Guide);

Server Side Includes
Active Server Pages
All Unknown ISAPI Extensions

I'm unable to even begin the installation, never mind complete it.
|||

Hi Brian,

ProClarity does indeed require that you enable Server Side Includes, Active Server Pages and All Unknown ISAPI Extensions for the installation to proceed.

I haven't used version 6.4 but version 6.2 requires that you run IIS in 32-bit mode.

|||

Brian,

OK, going back to the 64-bit installation, where exactly does the installation fail and with what error message ?

Have you enabled the 32-bit Asp.Net 2.0 web service extension ?

Paul

|||

Full product, which includes:

- Analytics Server software

- Analytics Server database

- Administration Tool

The error message is exactly:

Missing Product Requirements
The following requirements are missing:
- The following IIS Web Service Extensions must be set to allowed (see PAS Setup Guide);

Server Side Includes
Active Server Pages
All Unknown ISAPI Extensions

My only option at that point is to click the "Back" or "Finish" button.

I've enabled the 32-bit ASP.NET 2.0 web service extension as detailed in http://support.microsoft.com/default.aspx/kb/894435, as well as the the extensions listed in the error message.

|||

Brian,

Dumb question (but I've got to ask it) - are those extensions actually enabled in IIS ?

Is the status set to Allowed as opposed to Prohibited ?

I have seen this error message before during an installation but it was because the extensions weren't enabled.

Paul

|||

They are set to "Allowed".

|||

Hi Guys...

Thanks for Attention ....

|||

Unfortunately I received nearly the same error message on just such a machine (a Windows Server 2003 R2 Standard OS installed in VMWare):

Missing Product Requirements
The following requirements are missing:
- The following IIS Web Service Extensions must be set to allowed (see PAS Setup Guide);

Server Side Includes
Active Server Pages

|||

Hi Brian,

|||

Hi Brian,

If your virtual machine is Win2003 32bits as Diogo A. Di Pietro then I don't think allowed is needed because both are 32bits. Just a thought you may remove the allowed and try the installation again.

Wednesday, March 21, 2012

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.
There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting.
Of course indexing well is almost always the best path to follow.
|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...

I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure will show you the setting
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a écrit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!
> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time... :)
I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophe

processor maxed out block other process...

Hi,
I'm using a 4 Xeon Hyperthreaded server with Windows 2003 ent. and SQL
Server 2000 ent. (8.0.818)
Whenever a user launch a specific function, let's say a sp, he uses 100% of
one of the 8 CPUs during 30-40 minutes and the workload jump from one
processor to another (every time the sp makes a new query).
I read in here that most of the query, especially the simple ones or the
bulk ones run on only one processor, so I think the behaviour of the sp
described above is normal.
My problem is when a second user comes in and run another application,
running on another database, and run a big process (again a sp doing various
things during 30 minutes or so) I don't see 2 processors running at 100% but
only one, and the process time of the two sp running simultanneously is
almost doubled...
Maybe I have a performancebottleneck somewhere, but I don't see where, I
have some pretty good SCSI Disk, 6GB of Ram almost never used more than 30%.
Except upgrading my processors speed is there a way to have better
performance? Like forcing more parrallelism on some process or queries. I
read something about query plan complexity defining the level of parallelism
of a query, is there a way to tune this level?
Thanks in advance.
Best regard,
Christophe.There is a setting that adjusts the threshhold for parallism. sp_configure
will show you the setting.
Of course indexing well is almost always the best path to follow.|||"Ken Dutton" <fj60landcruiser@.yahoo.com> a crit dans le message de
news:505E0AD6-A9E0-4BE4-9214-1B3BC7BEE3B8@.microsoft.com...
> There is a setting that adjusts the threshhold for parallism.
sp_configure will show you the setting.
Thanks for your answer,
The threshold is currently at 3. I read in the BOL it's in millisecond, so I
don't think I can make it lower...
But that helped me see I didn't activate the AWE parameter on this server...
I was wondering why I never used more than 2GB!!

> Of course indexing well is almost always the best path to follow.
Yay!
That's what I'm telling my sql devs and db designers every morning, but that
doesn't seem to motivate them... I think a wooden club with long nails will
do the trick, but I'll try to coerce them into indexing their tables and
views one last time...

I'll do some trace and index tuning before, I'm not a monster... yet.
Anyway, thanks again.
Christophesql

Tuesday, March 20, 2012

Processing XML with defined namespaces

I'm trying use the SSIS XML Task to process some XML files that have namespaces and schemas defined . One of the things I am doing is trying to perform an XPATH query on the files. However, I'm unable to provide either a namespace manager or XsltContext to the XML Task and that causes the XML Task to not understand my XPATH queries.

For example, if my XML is:

<o:object id="12345" xmlns:o="urn:some-urn"/>

and my desired XPATH is "/o:object/@.id", I get the following error:

Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".

Of course, the XPATH "/object/@.id" returns nothing as expected.

Is there a way around this?

I'm having the same problem with .NET "System.Xml" have you found a solution to this?|||

I'm getting the same (Xml Task) error. I need my XPath to be /SOAP-ENV:Envelope/SOAP-ENV:Body.

The only way I could work around it was to write the XPath without using the namespace prefix like this:

/*[local-name() = 'Envelope']/*[local-name() = 'Body']

|||

A Barber wrote:
I'm having the same problem with .NET "System.Xml" have you found a solution to this?

what exactly is the problem that you're experiencing with system.xml?

Processing XML with defined namespaces

For example, if my XML is:

<o:object id="12345" xmlns:o="urn:some-urn"/>

and my desired XPATH is "/o:object/@.id", I get the following error:

Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".

Of course, the XPATH "/object/@.id" returns nothing as expected.

Is there a way around this?

I'm having the same problem with .NET "System.Xml" have you found a solution to this?|||

I'm getting the same (Xml Task) error. I need my XPath to be /SOAP-ENV:Envelope/SOAP-ENV:Body.

The only way I could work around it was to write the XPath without using the namespace prefix like this:

/*[local-name() = 'Envelope']/*[local-name() = 'Body']

|||

A Barber wrote:
I'm having the same problem with .NET "System.Xml" have you found a solution to this?

what exactly is the problem that you're experiencing with system.xml?

Processing XML with defined namespaces

For example, if my XML is:

<o:object id="12345" xmlns:o="urn:some-urn"/>

and my desired XPATH is "/o:object/@.id", I get the following error:

Error: 0xC002F304 at Query, XML Task: An error occurred with the following error message: "Namespace Manager or XsltContext needed. This query has a prefix, variable, or user-defined function.".

Of course, the XPATH "/object/@.id" returns nothing as expected.

Is there a way around this?

I'm having the same problem with .NET "System.Xml" have you found a solution to this?|||

I'm getting the same (Xml Task) error. I need my XPath to be /SOAP-ENV:Envelope/SOAP-ENV:Body.

The only way I could work around it was to write the XPath without using the namespace prefix like this:

/*[local-name() = 'Envelope']/*[local-name() = 'Body']

|||

A Barber wrote:
I'm having the same problem with .NET "System.Xml" have you found a solution to this?

what exactly is the problem that you're experiencing with system.xml?

Processing XML data in a SQL Server field

Has anyone cracked this? I want to process xml data which is contained within
a SQL server field. I'm using other fields in the table in the normal way.
The xml field actually contains numerous data values which I want to pass
into the the chart object.
So far there seems to be two options to me:
1) Export the entire row of the table to xml and then use a custom data
extension which knows about xml to read it. This route is described in Teos'
book.
2) Call out to a custom assembly and have it process the xml.
My views on the above are:
1) Seems clunky. Already have the data and now I need to export it to a file
and then read it back in?
2) Seems ok but how do I handle multiple items,, such as the data points?
Anyone have any other ideas before I wade in with my boots?!using a custom assembly works like a charm - We have entire XML docs stored
in blob fields and parse them on the fly to get the information we need form
them.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

A database administrator must process partitions and dimensions as part of a nightly extract, transform, and load (ETL) process. The ETL tool is not a SQL Server tool, and so the database administrator cannot use SQL Server Agent’s built-in support of XMLA scripts and cannot run a SQL Server 2005 Integration Services (SSIS) package. The database administrator wants an automated solution that utilizes the third-party tool. This means a command-line utility to run the XMLA script that can be called from the third-party tool. The database administrator downloads and compiles the ascmd command-line utility sample. The administrator can then use the ascmd command-line utility to process partitions and dimensions.

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

ascmd was added to the SQL Server samples in SP1, you can download the latest samples from here http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

How can I get the XMLA script to process a cube in SSAS 2000. If I can get this I would think I could make a SOAP call from my program to execute the XMLA. This would be great for me. I would have everything in one program.

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

The best work around I can think of in order to process AS 2000 objects using SOAP would be to write your own web service that used DSO to process the cube. You would need to have the web service running under a domain account that was a member of the OLAP Administrators group to get this working.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing SSAS2005 from DTS

Hi,

I have a requirement to process SSAS2005 cubes from DTS. Anyone know how to do this or does anyone have any recommendations?

I've done a search on this forum and came up with teh following which was no help at all unfortunately: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=198374&SiteID=1

-Jamie

Hi Jamie,

Will the ASCMD command-line utility (available under SQL Server Analysis Services Samples) serve your purpose (invoked within a DTS "Execute Process" task, of course)?

http://msdn2.microsoft.com/en-us/ms365187.aspx

SQL Server 2005 Books Online

Readme For Ascmd Command-line Utility Sample

...

Processing a Partition from a Third-Party Tool

|||

Deepak,

I'm certainly hoping so. Thanks for the reply.

-Jamie

|||

It works a treat. Thanks again!

XMLA - loving it!

cheers

-Jamie

|||

Did you download ASCMD.exe from this link or was it already on installed for you in the location specified in the ReadMe?

I've installed the developer edition on my machine and can't find the file in the location specified.

|||

We have not yet upgraded to 2005. Is there a similar tool for MSAS2000?

Thanks

|||http://msdn2.microsoft.com/en-us/library/ms133828.aspx|||

Momo:

|||Tried that. Contains sample code but not the actual exe?|||That's right, ascmd was only released as source code.|||

|||

Unfortunately I am pretty sure AS 2000 does not support the XMLA process command.

|||

Hi,

I tried to install the SSAS Samples but the SqlServerSamples.msi file available in the following link is corrupted and I can′t find another place to obtain it.

http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en

|||I just tried the download and it appears to work OK. Give the download another try, maybe there were some transient network issues.

Processing priority based on client connection?

Is there a way to set a process priority in SQL Server 2000 based upon the
client or connection type being used to access the data? For example, on a
particular database, I want to give the client application for which the
database was designed priority over other connections such as Microsoft
Access or Seagate Crystal Reports.
Can this be done?As far as I know, it is not possible to set priority based on the client =or the connection.
-- Keith
"Glen Appleton" <buglen@.hotmail.com> wrote in message =news:O432%23p2uDHA.1788@.tk2msftngp13.phx.gbl...
> Is there a way to set a process priority in SQL Server 2000 based upon =the
> client or connection type being used to access the data? For example, =on a
> particular database, I want to give the client application for which =the
> database was designed priority over other connections such as =Microsoft
> Access or Seagate Crystal Reports.
> > Can this be done?
> >

Processing of cube created via DSO in AS 2005 fails

We are testing an application originaly written for AS 2000 with AS

2005.
This application uses DSO from VB to create and process cubes. I have

gotten
over the major hurdles of getting DSO to work with AS 2005. However,

upon
creating of the cubes, I get errors processing it (even from

Management
Studio). Here's the code (abridged to remove error handling) that

we use to
create a cube:

Dim dsoCube As DSO.MDStore
Set

dsoCube = m_dsoDatabase.MDStores.AddNew(cubeName)

' set the cube's

description
dsoCube.Description = sCubeDesc

' set the cube's

datasource
dsoCube.DataSources.Add

m_dsoDatabase.DataSources(mvarDBName)

Dim dboStr As String

dboStr = sLQuote & "dbo" & sRQuote & "."
' set the source

table (fact table) for the cube
dsoCube.SourceTable = dboStr &

sLQuote & FACT_TABLE & sRQuote

dsoCube.EstimatedRows =

164558
' specify access permissions to the cube by adding roles to the

cube
Dim dsoCubeRole As DSO.role
Set dsoCubeRole =

dsoCube.Roles.AddNew("BSA Role")
dsoCubeRole.SetPermissions "Access",

"RW"

' create cube's measures
'
Dim dsoMeasure As

DSO.Measure
Set dsoCube = m_dsoDatabase.MDStores.Item(cubeName)

Set dsoMeasure = dsoCube.Measures.AddNew("BaseAmt")

' set the

measure's source column, data type and the formatting

dsoMeasure.SourceColumn = dsoCube.SourceTable & "." &

_
sLQuote & "baseamt" & sRQuote

dsoMeasure.SourceColumnType = adDouble
dsoMeasure.FormatString =

"Currency"
' this measure will be aggregated by summation

dsoMeasure.AggregateFunction = aggSum

Set dsoMeasure =

dsoCube.Measures.AddNew("Count")

' set the measure's source column,

data type and the formatting
dsoMeasure.SourceColumn =

dsoCube.SourceTable & "." & _
sLQuote

& "baseamt" & sRQuote
dsoMeasure.SourceColumnType =

adInteger
' this measure will be aggregated by summation

dsoMeasure.AggregateFunction = aggCount
Set dsoMeasure =

dsoCube.Measures.AddNew("TranNo")

' set the measure's source column,

data type and the formatting
dsoMeasure.SourceColumn =

dsoCube.SourceTable & "." & _
sLQuote

& "TranNo" & sRQuote
dsoMeasure.SourceColumnType =

adInteger

' this measure will be aggregated by summation

dsoMeasure.AggregateFunction = aggMax

' Create Calculated

members
Dim dsoCalculatedMember As DSO.Command
Set

dsoCalculatedMember = dsoCube.Commands.AddNew("CustAvgAmt")

' set the

command type
dsoCalculatedMember.CommandType = cmdCreateMember
'

set the MDX statement that defines the calculated member

dsoCalculatedMember.Statement = _
"Create Member Measures.[CustAvgAmt] As

" & _
"'avg({LastPeriods(3 ,

[bookdate].&[2003].&[4].&[10])},
measures.[baseamt])'"

Set dsoCalculatedMember = dsoCube.Commands.AddNew("CustAvgCnt")
' set the

command type
dsoCalculatedMember.CommandType = cmdCreateMember

' set the MDX statement that defines the calculated member

dsoCalculatedMember.Statement = _
"Create Member Measures.[CustAvgCnt] As

" & _
"'avg({LastPeriods(3 ,

[bookdate].&[2003].&[4].&[10])},
measures.[Count])'"

add the BookDate dimension
Dim dsoBookDateDim As DSO.Dimension
Set

dsoBookDateDim = dsoCube.Dimensions.AddNew("BookDate")

' add the

RecvPay dimension
Dim dsoRecvPayDim As DSO.Dimension
Set

dsoRecvPayDim = dsoCube.Dimensions.AddNew("RecvPay")

' get the list

of all tables used in this cube
' this list includes the fact table and

the dimension tables
' Note: Make sure that you do not repeat the same

table name twice.
dsoCube.FromClause = dsoCube.SourceTable & ", "

&
dsoBookDateDim.FromClause

dsoCube.joinClause =

dsoCube.joinClause & _
"(" & _
dboStr

& sLQuote & FACT_TABLE & sRQuote & "." & sLQuote

&
"bookdate" & sRQuote & _
" = " &

_
dboStr & sLQuote & "TblBookDate" & sRQuote &

"." & sLQuote &
"bookdate" & sRQuote & _

")"

dsoCube.SourceTableFilter = mvarFactTableFilter
' save the

cube definition in the metadata repository
dsoCube.Update

This

code runs fine, but during processing of the selected cube from
Management

Studio, the following error is displayed:
The Measures cube either does

not exist or has not been processed

There is no Measures cube, nor was

there ever. This does not happen when
processing cubes in AS 2000 or even in

AS 2005 if the cube was migrated from
AS 2000.

Can anybody help with

this?

Thanks in advance,
Boris Zakharin, MCAD
Metavante Risk and

Compliance

Hi Boris,

the problem is in the following line, which creates a calculated member. In AS2005 (as well as in AS2000) the member created in the cube scope should be prefixed with the cube name or CurrentCube, since Measures is the first name detected AS2005 treats it as a cube name. Pehaps AS2000 did not detected this error during the prosessing, but the calc member was disabled pr may be I'm wrong and CurrentCube was optional, but anyway, if you add CurrentCube to all calculated members it should start to process.

"Create Member Measures.[CustAvgAmt] As " & _
"'avg({LastPeriods(3 , [bookdate].&[2003].&[4].&[10])},
measures.[baseamt])'"

|||Thanks a lot, that worked

Processing Mining Models & Structures via SSIS

Still new to DM and SSIS...anyand all help is greatly appreciated!

In SSIS they say that you can use the Analysis Services Processing Task to process a mining model/mining structure, however, I do not see where you can give it a relational table to work off of. I know that I can use a data flow to do this but I wanted to go a different route if I could to process my models as I don't really necessarily need the data flow as what I am tring to do is pretty simple.

That brings me to a more general question, what is the best method for training your models using SSIS? I am building a new model everytime the package runs using some variables and the DDL task, running a query on it, and destroying it at the end of the package but I am having logistical problems training it outside of the data flow. I tried using the DM Query task but it requires that you output a result set and I am not sure if I can use it to create and process models.

I would think that they would just give you a DMX task similar to the SQL task but that does not seem to be the case. Also, when I browse the AS objects via the processing task I can only see the mining structures and not the mining models.

Am I just missing something here?

Cheers,

Dan Meyers

If you just want to process the mining models/structure, you should use the Analysis Services processing task. If you wnat more flexibility, you can use the Analysis Services Execute DDL task (you can do pretty much anything there). There is a feature called "out of line bindings" that you can specify if you want to proecess against different data than the model was bound to in the DSV - I'm not sure of the documentation level on this feature though.

You are right that you can't use the DM Query task for the reason you stated - you can use the DDL task as in this tip http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/1443.aspx to process using DMX, or execute any other non-rowset returning DMX statement.

On the point of what happens during the data flow execution, the model/structure is only created at design time. The processing happens at run time. This means that if you wanted to run this package against another server, you would first have to create the model on that server. The easiest method may be to use SSMS to script the structure to create and then use the DDL task again

HTH

-Jamie

|||

Interesting...I did not realize that you could use the DDL task to execute DMX statments. Makes perfect sense since it all gets broken down to XMLA anyway. This is exactly the answer I was looking for. You are like the Yoda of SQL DM. Once again, thanks for the help.

-Dan Meyers

|||

Jamie,

I have another more question on this...Do I have to build a new DSV for my mining model or can I just use the same one that I am using for my OLAP cubes?

|||You can use the same DSV - the DSV is simply a collection of tables/named queries that don't necessarily have to be related

Monday, March 12, 2012

Processing jam-up

We have a number of jobs that process cubes when table loads complete. The last step in each of these jobs is to reprocess a "verification cube", which contains as linked measures the record counts from ALL other cubes. (We have a report which compares this against a direct SQL record count from the tables).

Anyway, we appear to have jammed up our Analysis Server (and had to restart) because one completing job issued a reprocess command for this "verification cube" while it (the verification cube) was still reprocessing in response to a previous job's completion.

OK, babbling aside, within a job I need to be able to ask "Is the verification cube currently processing, and if not, reprocess it".

Is this trivial, and I am not thinking correctly ? Any suggestions will be appreciated.

Which version of SQL Server 2000 or 2005 you are talking about?

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

2005.

Just to mention . . . this has only happened once, and may just be one of those things . . . . But of course if we can better understand the problem and program to avoid it, that would be better.

|||

You should probably create a synchronization logic within your application and not let multiple jobs kick processing of the cube.

It is very hard to say whether processing of the cube is going on. The cube is not a single object it has multiple measure groups. Many processing commands, especially batch commands can include processing of any number of cube objects.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||Thanks Edward.

processing files with timestamp

hi guys,

i have a unique problem here.i get four different type of flat files,which i need to pick up and process parallelly,this i am doing using four different foreach containers for the four load process as i can get multiple files ( i had other issues like rollback etc for using forecah containers).

my problem is,my input file names will have format like this

yyyymmdd_salesdataforproduct_yyyymmdd_hhmmss.txt

here the first date is bussiness date and the second one is the sysytem date..if i get multiple file i have to proceess considering the second date.but by default the foreach loop is considering the first date,what can i do to ensure that only second is used to process my files.

thanks in advance

srikanth

If you want to sort the files by the second date, take a look at this thread:

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

processing each row in a multirow insert

I'm looking for a way to process each row in a insert ... select sentece. What i need is to update a column which is type int (not identity nor sequence) in a way it always get the max record + 1, in a sentence:

insert into my_table
(a,b,c,d)
select isnull((select max(id) from another_table),0) as a,
'b' as b, 'c' as c, 'd' as d
from table1 join table2 on t1=t2

the problem with such a sentence is the embbebed select is it is executed only once so it provides always the same value, i.e. assuming the subselect returns 7 and the join provides 2 rows, what would be inserted in my_table is:

a b c d
-- -- -- --
7 b c d
7 b c d

and not

7 b c d
8 b c d

what i expected

so what i looking for is a way to resolve such a sentence or at least some kind of fuction which generates a identity value in a similar way as identity() but being useful for no-identity tables. Maybe something similar to newid() but returning int value and it would be great if that value would be max(table_field)+1

any idea?i forgot to say don't want to use a cursor to process and insert each row|||This can be done using a single direct, if complicated, SQL statement. It would be simpler to insert your data into a temporary table with an autoincrement field, and then select from the temporary table, adding max(id) to the values in the autoincrement field.

Otherwise, you will need a select statement with a subquery that fabricates sequential values for your data (what order to you want them in? You have to choose an order with this method) and then add max(id) to these sequential values.

blindman

Monday, March 26, 2012

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Produciton SQL CPU running around 50%

Friday, March 23, 2012

Proclarity - Inatalation 64 bit problem!?

Proclarity - Inatalation 64 bit problem!?

Wednesday, March 21, 2012

processor maxed out block other process...

processor maxed out block other process...

processor maxed out block other process...

Tuesday, March 20, 2012

Processing XML with defined namespaces

Processing XML with defined namespaces

Processing XML with defined namespaces

Processing XML data in a SQL Server field

Processing SSAS2005 from DTS

Processing a Partition from a Third-Party Tool

Processing SSAS2005 from DTS

Processing a Partition from a Third-Party Tool

Processing priority based on client connection?

Processing of cube created via DSO in AS 2005 fails

Processing Mining Models & Structures via SSIS

Monday, March 12, 2012

Processing jam-up

processing files with timestamp

processing each row in a multirow insert

Procedure Time out

Blog Archive

About Me