Showing posts with label group. Show all posts
Showing posts with label group. Show all posts

Sunday, March 25, 2012

Calling SQL Rreporting Services

Hi guys,
I have reports that are pre-configured on a remote MS Reporting Server
(MSRS 2005). There is also a subscription that a group of subscribers
subscribes to so they can get the reports in question. I know that I
can schedule a report/group of reports to be delivered @. a certain
time, but what I really want to do is initiate the delivery (email in
this case) from an outside application. So my question is:
Is there any API call that I can use to do that (via RS WebService
perhaps?) and what is the [web]method that I need to call to do so?
Any help is greatly appreciated...
Thanks,
G.Hi,
Ys can be done. On SSRS help just search for
"ReportingService2005.CreateDataDrivenSubscription Method"
you can see some example written in c# as well. good one. I think this will
be suitable for your requirement.
Amarnath
"vajarov" wrote:
> Hi guys,
> I have reports that are pre-configured on a remote MS Reporting Server
> (MSRS 2005). There is also a subscription that a group of subscribers
> subscribes to so they can get the reports in question. I know that I
> can schedule a report/group of reports to be delivered @. a certain
> time, but what I really want to do is initiate the delivery (email in
> this case) from an outside application. So my question is:
> Is there any API call that I can use to do that (via RS WebService
> perhaps?) and what is the [web]method that I need to call to do so?
> Any help is greatly appreciated...
> Thanks,
> G.
>|||Thanks a lot! This is what I needed.
G.|||Here is the actual link :
http://msdn2.microsoft.com/en-US/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx

Monday, March 19, 2012

Calling all MSSQL 2005 Experts

Dear Group.

Wondered if any of you has any suggestion for the following?
Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, my
mistake was probably that I had installed Visual Studio 2005
Professional Eval before which installed an MSSQL Express instance.
Since then I didn't get 'Enterprise Manager' (excuse my ignorance, I
know it's called differently in2005 but it's late and I'm tired) for
MSSQL 2005 installed.Don't remember the error exactly but after some
trying I received some error during the MSSQL Management Studio (is
that right?) installation that it needs to be upgraded in some ini file
which I looked for to no avail. ANYWAY, after some desperate registry
deletes and following MS Kbase articles I arrived at: 'The installer
has encountered an unexpected error installing this package. This may
indicate a problem with the package. The error code is 2718.' during
the installation of SQL Server Backward-Compatibility files.
Which is of course complete rubbish. We all now that Microsoft has done
an awful job in getting SQL Express and SQL Server installed
side-by-side but it never has been as bad as this on any machine
before. Needless to say the package is fine as I've used it many times
before. Any suggestions?

Gratefully yours,

Martin

'Just wanna get on with work. Tired of starring at error messages.'(theintrepidfox@.hotmail.com) writes:

Quote:

Originally Posted by

Wondered if any of you has any suggestion for the following?
Trying to install SQL Server 2005 Eval on a 'clean' machine. Well, my
mistake was probably that I had installed Visual Studio 2005
Professional Eval before which installed an MSSQL Express instance.
Since then I didn't get 'Enterprise Manager' (excuse my ignorance, I
know it's called differently in2005 but it's late and I'm tired) for
MSSQL 2005 installed.Don't remember the error exactly but after some
trying I received some error during the MSSQL Management Studio (is
that right?) installation that it needs to be upgraded in some ini file
which I looked for to no avail. ANYWAY, after some desperate registry
deletes and following MS Kbase articles I arrived at: 'The installer
has encountered an unexpected error installing this package. This may
indicate a problem with the package. The error code is 2718.' during
the installation of SQL Server Backward-Compatibility files.
Which is of course complete rubbish. We all now that Microsoft has done
an awful job in getting SQL Express and SQL Server installed
side-by-side but it never has been as bad as this on any machine
before. Needless to say the package is fine as I've used it many times
before. Any suggestions?


It's very unclear what you have done, after messing with the registry your
machine may be beyond repair. Or may at least require a complete uninstall
of VS and SQL Server and back again.

But if you already had SQL Server Management Studio Express installed,
that may be the root of your problem, as you cannot have both SSMS Express
and the big SSMS installed side by side.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Thursday, March 8, 2012

call to xp_cmdshell from trigger problem

To any SQL Server MVP:
We have a problem that out group can not seem to resolve. We have a
database that is used to alert and calculate required amounts of assistance
to clients that monitor through a private frame relay WAN. When a problem
occurs client 1 enters the request at a workstation which updates the
database and causes a trigger to fire and starts a process to notify the
other clients that assistance is required. This portion has been in use for
several years but now need to send information to a control area system,
rather than single entity, that is in XML format so a program was created to
take the data and format in accordance to the XML Schema rules the area
used.
At first it failed because the trigger called the EXE but the tables were
locked by the first program called in the trigger. To resolve this and
allow the trigger to continue thus unlocking the tables we changed the
trigger to call a vbs script file rather than the EXE directly. This seemed
to resolve the situation with locked tables in the database but a new issue
has come up I can not figure out.
The EXE has to use a X509 digital certificate to pass the data to the
client's server. When I run the EXE from the command line it works, when I
use SQL Query Manager and run the trigger code (less the alert portion) it
works but when the trigger fires is fails to send the information. It builds
everything but the client never gets the XML stream? We currently use
simple text files to track where the program process is but this file
indicates it finishes properly and all cleanup of objects occurs (Code in
VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
indicate within the text file who started the program and this indicates the
"SQLProxy" account is who is running the EXE file. It almost seems that when
impersonate process occurs the local server does not treat the SQLProxy
account as the right one?
How can we test using SQL Query Manager the exact process as when the
trigger is fired from SQL Server?
Information:
ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called from
the trigger by the application user. This is what we created as instructed
by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
works because the first step in the trigger works.
CERTIFICATE: We logged on to the server using SQLProxy so the
certificate could be installed into the "My" store for the user. We could
move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
Hi Daniel,
I am just wondering under what user you did your test on query analyzer.
Have you tried the following to see what kind of result you get:
1. log in on the server by using your SQLProxy
2. Open query analyzer and connect to the server with Windows Authentication
3. try a test and see if the client gets the required info.
Sasan Saidi
Senior DBA
"Daniel Gard" wrote:

> To any SQL Server MVP:
> We have a problem that out group can not seem to resolve. We have a
> database that is used to alert and calculate required amounts of assistance
> to clients that monitor through a private frame relay WAN. When a problem
> occurs client 1 enters the request at a workstation which updates the
> database and causes a trigger to fire and starts a process to notify the
> other clients that assistance is required. This portion has been in use for
> several years but now need to send information to a control area system,
> rather than single entity, that is in XML format so a program was created to
> take the data and format in accordance to the XML Schema rules the area
> used.
> At first it failed because the trigger called the EXE but the tables were
> locked by the first program called in the trigger. To resolve this and
> allow the trigger to continue thus unlocking the tables we changed the
> trigger to call a vbs script file rather than the EXE directly. This seemed
> to resolve the situation with locked tables in the database but a new issue
> has come up I can not figure out.
> The EXE has to use a X509 digital certificate to pass the data to the
> client's server. When I run the EXE from the command line it works, when I
> use SQL Query Manager and run the trigger code (less the alert portion) it
> works but when the trigger fires is fails to send the information. It builds
> everything but the client never gets the XML stream? We currently use
> simple text files to track where the program process is but this file
> indicates it finishes properly and all cleanup of objects occurs (Code in
> VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
> indicate within the text file who started the program and this indicates the
> "SQLProxy" account is who is running the EXE file. It almost seems that when
> impersonate process occurs the local server does not treat the SQLProxy
> account as the right one?
> How can we test using SQL Query Manager the exact process as when the
> trigger is fired from SQL Server?
>
> Information:
> ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called from
> the trigger by the application user. This is what we created as instructed
> by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> works because the first step in the trigger works.
> CERTIFICATE: We logged on to the server using SQLProxy so the
> certificate could be installed into the "My" store for the user. We could
> move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
>
>
>
>
>
|||Sasan
We have tried all of the following:
1. Logged on to the Windows server as the Domain Administrator and
connecting to SQL Server using windows authentication.
2. Logged on to the Windows server as the SQLProxy Account and connecting
to SQL Server using windows authentication.
We did notice is logged on to the Windows server as either 1 or 2 and
logging onto the SQL Server as the application user it does not work.
Thanks
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Hi Daniel,
> I am just wondering under what user you did your test on query analyzer.
> Have you tried the following to see what kind of result you get:
> 1. log in on the server by using your SQLProxy
> 2. Open query analyzer and connect to the server with Windows
Authentication[vbcol=seagreen]
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
assistance[vbcol=seagreen]
problem[vbcol=seagreen]
for[vbcol=seagreen]
created to[vbcol=seagreen]
were[vbcol=seagreen]
seemed[vbcol=seagreen]
issue[vbcol=seagreen]
when I[vbcol=seagreen]
it[vbcol=seagreen]
builds[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
from[vbcol=seagreen]
instructed[vbcol=seagreen]
could[vbcol=seagreen]
|||Sasan
The application user is a SQL Server account and not a Windows user account.
We noticed the following behavior. If the application user account was
placed into the SQL System Administrator group the program runsa as the
Domain account for the SQL Server and if this user is not a in the
administrator group then the program runs as SQLProxy.
The more I mess with this the more I think the user running the application
after the call from SQL Server can not find the digital certificate in the
CURRENT_USER\My store. How does the impersonation really work with SQL
Server?
Thanks
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Hi Daniel,
> I am just wondering under what user you did your test on query analyzer.
> Have you tried the following to see what kind of result you get:
> 1. log in on the server by using your SQLProxy
> 2. Open query analyzer and connect to the server with Windows
Authentication[vbcol=seagreen]
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
assistance[vbcol=seagreen]
problem[vbcol=seagreen]
for[vbcol=seagreen]
created to[vbcol=seagreen]
were[vbcol=seagreen]
seemed[vbcol=seagreen]
issue[vbcol=seagreen]
when I[vbcol=seagreen]
it[vbcol=seagreen]
builds[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
from[vbcol=seagreen]
instructed[vbcol=seagreen]
could[vbcol=seagreen]
|||When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in which
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail.
Sasan
"Daniel Gard" wrote:

> Sasan
> The application user is a SQL Server account and not a Windows user account.
> We noticed the following behavior. If the application user account was
> placed into the SQL System Administrator group the program runsa as the
> Domain account for the SQL Server and if this user is not a in the
> administrator group then the program runs as SQLProxy.
> The more I mess with this the more I think the user running the application
> after the call from SQL Server can not find the digital certificate in the
> CURRENT_USER\My store. How does the impersonation really work with SQL
> Server?
> Thanks
> Dan
>
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Authentication
> assistance
> problem
> for
> created to
> were
> seemed
> issue
> when I
> it
> builds
> in
> to
> the
> when
> from
> instructed
> could
>
>
|||Sasan
I understand that and we have all of this working. The problem seems very
strange even in my book because I am not sure if the impersonation process
uses a modified SID from the one if you log on the the server using the same
account. Does it take the domain user and create a temporary local account?
If so then that is the problem and this user can not find the certificate.
We are going to try to use a public certificate store and see if the process
will complete.
The program we wrote runs no matter what method the difference is when run
from the command line or from SQL Query manager it finishes by sending XML
to the the client but if the same process runs from being called by the vbs
script that is called by the trigger the client get nothing.
Thanks
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> When xp_cmdshell is invoked by a user who is a member of the sysadmin
fixed
> server role, xp_cmdshell will be executed under the security context in
which[vbcol=seagreen]
> the SQL Server service is running. When the user is not a member of the
> sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail.
> Sasan
> "Daniel Gard" wrote:
account.[vbcol=seagreen]
application[vbcol=seagreen]
the[vbcol=seagreen]
analyzer.[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
use[vbcol=seagreen]
system,[vbcol=seagreen]
area[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
This[vbcol=seagreen]
new[vbcol=seagreen]
the[vbcol=seagreen]
works,[vbcol=seagreen]
portion)[vbcol=seagreen]
It[vbcol=seagreen]
use[vbcol=seagreen]
file[vbcol=seagreen]
(Code[vbcol=seagreen]
altered[vbcol=seagreen]
indicates[vbcol=seagreen]
that[vbcol=seagreen]
SQLProxy[vbcol=seagreen]
the[vbcol=seagreen]
called[vbcol=seagreen]
it[vbcol=seagreen]
|||Daniel, I am not sure if this can help in any ways but you might want to have
a quick look at it:
Article: Setting the Default Process Security Level Using VBScript
Link:
http://msdn.microsoft.com/library/de...v bscript.asp
Sasan
"Daniel Gard" wrote:

> Sasan
> I understand that and we have all of this working. The problem seems very
> strange even in my book because I am not sure if the impersonation process
> uses a modified SID from the one if you log on the the server using the same
> account. Does it take the domain user and create a temporary local account?
> If so then that is the problem and this user can not find the certificate.
> We are going to try to use a public certificate store and see if the process
> will complete.
> The program we wrote runs no matter what method the difference is when run
> from the command line or from SQL Query manager it finishes by sending XML
> to the the client but if the same process runs from being called by the vbs
> script that is called by the trigger the client get nothing.
> Thanks
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> fixed
> which
> account.
> application
> the
> analyzer.
> the
> the
> use
> system,
> area
> and
> the
> This
> new
> the
> works,
> portion)
> It
> use
> file
> (Code
> altered
> indicates
> that
> SQLProxy
> the
> called
> it
>
>
|||Sasan,
I'll look this over maybe there is something we missed in the vbs script
being called by the trigger.
Thank You,
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:BF67C18C-AEEC-408D-9185-86D4B5DC0772@.microsoft.com...
> Daniel, I am not sure if this can help in any ways but you might want to
have
> a quick look at it:
> Article: Setting the Default Process Security Level Using VBScript
> Link:
>
http://msdn.microsoft.com/library/de...v bscript.asp[vbcol=seagreen]
>
> Sasan
> "Daniel Gard" wrote:
very[vbcol=seagreen]
process[vbcol=seagreen]
same[vbcol=seagreen]
account?[vbcol=seagreen]
certificate.[vbcol=seagreen]
process[vbcol=seagreen]
run[vbcol=seagreen]
XML[vbcol=seagreen]
vbs[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
proxy[vbcol=seagreen]
proxy[vbcol=seagreen]
was[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
SQL[vbcol=seagreen]
message[vbcol=seagreen]
have a[vbcol=seagreen]
a[vbcol=seagreen]
updates[vbcol=seagreen]
notify[vbcol=seagreen]
been in[vbcol=seagreen]
was[vbcol=seagreen]
the[vbcol=seagreen]
tables[vbcol=seagreen]
this[vbcol=seagreen]
changed[vbcol=seagreen]
a[vbcol=seagreen]
to[vbcol=seagreen]
information.[vbcol=seagreen]
currently[vbcol=seagreen]
seems[vbcol=seagreen]
when[vbcol=seagreen]
know[vbcol=seagreen]
the[vbcol=seagreen]
We[vbcol=seagreen]
should.[vbcol=seagreen]

call to xp_cmdshell from trigger problem

To any SQL Server MVP:
We have a problem that out group can not seem to resolve. We have a
database that is used to alert and calculate required amounts of assistance
to clients that monitor through a private frame relay WAN. When a problem
occurs client 1 enters the request at a workstation which updates the
database and causes a trigger to fire and starts a process to notify the
other clients that assistance is required. This portion has been in use for
several years but now need to send information to a control area system,
rather than single entity, that is in XML format so a program was created to
take the data and format in accordance to the XML Schema rules the area
used.
At first it failed because the trigger called the EXE but the tables were
locked by the first program called in the trigger. To resolve this and
allow the trigger to continue thus unlocking the tables we changed the
trigger to call a vbs script file rather than the EXE directly. This seemed
to resolve the situation with locked tables in the database but a new issue
has come up I can not figure out.
The EXE has to use a X509 digital certificate to pass the data to the
client's server. When I run the EXE from the command line it works, when I
use SQL Query Manager and run the trigger code (less the alert portion) it
works but when the trigger fires is fails to send the information. It builds
everything but the client never gets the XML stream? We currently use
simple text files to track where the program process is but this file
indicates it finishes properly and all cleanup of objects occurs (Code in
VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
indicate within the text file who started the program and this indicates the
"SQLProxy" account is who is running the EXE file. It almost seems that when
impersonate process occurs the local server does not treat the SQLProxy
account as the right one?
How can we test using SQL Query Manager the exact process as when the
trigger is fired from SQL Server?
Information:
ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called from
the trigger by the application user. This is what we created as instructed
by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
works because the first step in the trigger works.
CERTIFICATE: We logged on to the server using SQLProxy so the
certificate could be installed into the "My" store for the user. We could
move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.Hi Daniel,
I am just wondering under what user you did your test on query analyzer.
Have you tried the following to see what kind of result you get:
1. log in on the server by using your SQLProxy
2. Open query analyzer and connect to the server with Windows Authentication
3. try a test and see if the client gets the required info.
Sasan Saidi
Senior DBA
"Daniel Gard" wrote:
> To any SQL Server MVP:
> We have a problem that out group can not seem to resolve. We have a
> database that is used to alert and calculate required amounts of assistance
> to clients that monitor through a private frame relay WAN. When a problem
> occurs client 1 enters the request at a workstation which updates the
> database and causes a trigger to fire and starts a process to notify the
> other clients that assistance is required. This portion has been in use for
> several years but now need to send information to a control area system,
> rather than single entity, that is in XML format so a program was created to
> take the data and format in accordance to the XML Schema rules the area
> used.
> At first it failed because the trigger called the EXE but the tables were
> locked by the first program called in the trigger. To resolve this and
> allow the trigger to continue thus unlocking the tables we changed the
> trigger to call a vbs script file rather than the EXE directly. This seemed
> to resolve the situation with locked tables in the database but a new issue
> has come up I can not figure out.
> The EXE has to use a X509 digital certificate to pass the data to the
> client's server. When I run the EXE from the command line it works, when I
> use SQL Query Manager and run the trigger code (less the alert portion) it
> works but when the trigger fires is fails to send the information. It builds
> everything but the client never gets the XML stream? We currently use
> simple text files to track where the program process is but this file
> indicates it finishes properly and all cleanup of objects occurs (Code in
> VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
> indicate within the text file who started the program and this indicates the
> "SQLProxy" account is who is running the EXE file. It almost seems that when
> impersonate process occurs the local server does not treat the SQLProxy
> account as the right one?
> How can we test using SQL Query Manager the exact process as when the
> trigger is fired from SQL Server?
>
> Information:
> ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called from
> the trigger by the application user. This is what we created as instructed
> by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> works because the first step in the trigger works.
> CERTIFICATE: We logged on to the server using SQLProxy so the
> certificate could be installed into the "My" store for the user. We could
> move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
>
>
>
>
>|||Sasan
We have tried all of the following:
1. Logged on to the Windows server as the Domain Administrator and
connecting to SQL Server using windows authentication.
2. Logged on to the Windows server as the SQLProxy Account and connecting
to SQL Server using windows authentication.
We did notice is logged on to the Windows server as either 1 or 2 and
logging onto the SQL Server as the application user it does not work.
Thanks
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Hi Daniel,
> I am just wondering under what user you did your test on query analyzer.
> Have you tried the following to see what kind of result you get:
> 1. log in on the server by using your SQLProxy
> 2. Open query analyzer and connect to the server with Windows
Authentication
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
> > To any SQL Server MVP:
> >
> > We have a problem that out group can not seem to resolve. We have a
> > database that is used to alert and calculate required amounts of
assistance
> > to clients that monitor through a private frame relay WAN. When a
problem
> > occurs client 1 enters the request at a workstation which updates the
> > database and causes a trigger to fire and starts a process to notify the
> > other clients that assistance is required. This portion has been in use
for
> > several years but now need to send information to a control area system,
> > rather than single entity, that is in XML format so a program was
created to
> > take the data and format in accordance to the XML Schema rules the area
> > used.
> >
> > At first it failed because the trigger called the EXE but the tables
were
> > locked by the first program called in the trigger. To resolve this and
> > allow the trigger to continue thus unlocking the tables we changed the
> > trigger to call a vbs script file rather than the EXE directly. This
seemed
> > to resolve the situation with locked tables in the database but a new
issue
> > has come up I can not figure out.
> >
> > The EXE has to use a X509 digital certificate to pass the data to the
> > client's server. When I run the EXE from the command line it works,
when I
> > use SQL Query Manager and run the trigger code (less the alert portion)
it
> > works but when the trigger fires is fails to send the information. It
builds
> > everything but the client never gets the XML stream? We currently use
> > simple text files to track where the program process is but this file
> > indicates it finishes properly and all cleanup of objects occurs (Code
in
> > VB6 and we use WinHTTP Request Object 5.1). The code has been altered
to
> > indicate within the text file who started the program and this indicates
the
> > "SQLProxy" account is who is running the EXE file. It almost seems that
when
> > impersonate process occurs the local server does not treat the SQLProxy
> > account as the right one?
> >
> > How can we test using SQL Query Manager the exact process as when the
> > trigger is fired from SQL Server?
> >
> >
> > Information:
> >
> > ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called
from
> > the trigger by the application user. This is what we created as
instructed
> > by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> > works because the first step in the trigger works.
> >
> > CERTIFICATE: We logged on to the server using SQLProxy so the
> > certificate could be installed into the "My" store for the user. We
could
> > move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
> >
> >
> >
> >
> >
> >
> >
> >
> >|||Sasan
The application user is a SQL Server account and not a Windows user account.
We noticed the following behavior. If the application user account was
placed into the SQL System Administrator group the program runsa as the
Domain account for the SQL Server and if this user is not a in the
administrator group then the program runs as SQLProxy.
The more I mess with this the more I think the user running the application
after the call from SQL Server can not find the digital certificate in the
CURRENT_USER\My store. How does the impersonation really work with SQL
Server?
Thanks
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Hi Daniel,
> I am just wondering under what user you did your test on query analyzer.
> Have you tried the following to see what kind of result you get:
> 1. log in on the server by using your SQLProxy
> 2. Open query analyzer and connect to the server with Windows
Authentication
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
> > To any SQL Server MVP:
> >
> > We have a problem that out group can not seem to resolve. We have a
> > database that is used to alert and calculate required amounts of
assistance
> > to clients that monitor through a private frame relay WAN. When a
problem
> > occurs client 1 enters the request at a workstation which updates the
> > database and causes a trigger to fire and starts a process to notify the
> > other clients that assistance is required. This portion has been in use
for
> > several years but now need to send information to a control area system,
> > rather than single entity, that is in XML format so a program was
created to
> > take the data and format in accordance to the XML Schema rules the area
> > used.
> >
> > At first it failed because the trigger called the EXE but the tables
were
> > locked by the first program called in the trigger. To resolve this and
> > allow the trigger to continue thus unlocking the tables we changed the
> > trigger to call a vbs script file rather than the EXE directly. This
seemed
> > to resolve the situation with locked tables in the database but a new
issue
> > has come up I can not figure out.
> >
> > The EXE has to use a X509 digital certificate to pass the data to the
> > client's server. When I run the EXE from the command line it works,
when I
> > use SQL Query Manager and run the trigger code (less the alert portion)
it
> > works but when the trigger fires is fails to send the information. It
builds
> > everything but the client never gets the XML stream? We currently use
> > simple text files to track where the program process is but this file
> > indicates it finishes properly and all cleanup of objects occurs (Code
in
> > VB6 and we use WinHTTP Request Object 5.1). The code has been altered
to
> > indicate within the text file who started the program and this indicates
the
> > "SQLProxy" account is who is running the EXE file. It almost seems that
when
> > impersonate process occurs the local server does not treat the SQLProxy
> > account as the right one?
> >
> > How can we test using SQL Query Manager the exact process as when the
> > trigger is fired from SQL Server?
> >
> >
> > Information:
> >
> > ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called
from
> > the trigger by the application user. This is what we created as
instructed
> > by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> > works because the first step in the trigger works.
> >
> > CERTIFICATE: We logged on to the server using SQLProxy so the
> > certificate could be installed into the "My" store for the user. We
could
> > move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
> >
> >
> >
> >
> >
> >
> >
> >
> >|||When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in which
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail.
Sasan
"Daniel Gard" wrote:
> Sasan
> The application user is a SQL Server account and not a Windows user account.
> We noticed the following behavior. If the application user account was
> placed into the SQL System Administrator group the program runsa as the
> Domain account for the SQL Server and if this user is not a in the
> administrator group then the program runs as SQLProxy.
> The more I mess with this the more I think the user running the application
> after the call from SQL Server can not find the digital certificate in the
> CURRENT_USER\My store. How does the impersonation really work with SQL
> Server?
> Thanks
> Dan
>
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> > Hi Daniel,
> >
> > I am just wondering under what user you did your test on query analyzer.
> >
> > Have you tried the following to see what kind of result you get:
> > 1. log in on the server by using your SQLProxy
> > 2. Open query analyzer and connect to the server with Windows
> Authentication
> > 3. try a test and see if the client gets the required info.
> >
> > Sasan Saidi
> > Senior DBA
> >
> > "Daniel Gard" wrote:
> >
> > > To any SQL Server MVP:
> > >
> > > We have a problem that out group can not seem to resolve. We have a
> > > database that is used to alert and calculate required amounts of
> assistance
> > > to clients that monitor through a private frame relay WAN. When a
> problem
> > > occurs client 1 enters the request at a workstation which updates the
> > > database and causes a trigger to fire and starts a process to notify the
> > > other clients that assistance is required. This portion has been in use
> for
> > > several years but now need to send information to a control area system,
> > > rather than single entity, that is in XML format so a program was
> created to
> > > take the data and format in accordance to the XML Schema rules the area
> > > used.
> > >
> > > At first it failed because the trigger called the EXE but the tables
> were
> > > locked by the first program called in the trigger. To resolve this and
> > > allow the trigger to continue thus unlocking the tables we changed the
> > > trigger to call a vbs script file rather than the EXE directly. This
> seemed
> > > to resolve the situation with locked tables in the database but a new
> issue
> > > has come up I can not figure out.
> > >
> > > The EXE has to use a X509 digital certificate to pass the data to the
> > > client's server. When I run the EXE from the command line it works,
> when I
> > > use SQL Query Manager and run the trigger code (less the alert portion)
> it
> > > works but when the trigger fires is fails to send the information. It
> builds
> > > everything but the client never gets the XML stream? We currently use
> > > simple text files to track where the program process is but this file
> > > indicates it finishes properly and all cleanup of objects occurs (Code
> in
> > > VB6 and we use WinHTTP Request Object 5.1). The code has been altered
> to
> > > indicate within the text file who started the program and this indicates
> the
> > > "SQLProxy" account is who is running the EXE file. It almost seems that
> when
> > > impersonate process occurs the local server does not treat the SQLProxy
> > > account as the right one?
> > >
> > > How can we test using SQL Query Manager the exact process as when the
> > > trigger is fired from SQL Server?
> > >
> > >
> > > Information:
> > >
> > > ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called
> from
> > > the trigger by the application user. This is what we created as
> instructed
> > > by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> > > works because the first step in the trigger works.
> > >
> > > CERTIFICATE: We logged on to the server using SQLProxy so the
> > > certificate could be installed into the "My" store for the user. We
> could
> > > move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
>
>|||Sasan
I understand that and we have all of this working. The problem seems very
strange even in my book because I am not sure if the impersonation process
uses a modified SID from the one if you log on the the server using the same
account. Does it take the domain user and create a temporary local account?
If so then that is the problem and this user can not find the certificate.
We are going to try to use a public certificate store and see if the process
will complete.
The program we wrote runs no matter what method the difference is when run
from the command line or from SQL Query manager it finishes by sending XML
to the the client but if the same process runs from being called by the vbs
script that is called by the trigger the client get nothing.
Thanks
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> When xp_cmdshell is invoked by a user who is a member of the sysadmin
fixed
> server role, xp_cmdshell will be executed under the security context in
which
> the SQL Server service is running. When the user is not a member of the
> sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail.
> Sasan
> "Daniel Gard" wrote:
> > Sasan
> >
> > The application user is a SQL Server account and not a Windows user
account.
> > We noticed the following behavior. If the application user account was
> > placed into the SQL System Administrator group the program runsa as the
> > Domain account for the SQL Server and if this user is not a in the
> > administrator group then the program runs as SQLProxy.
> >
> > The more I mess with this the more I think the user running the
application
> > after the call from SQL Server can not find the digital certificate in
the
> > CURRENT_USER\My store. How does the impersonation really work with SQL
> > Server?
> >
> > Thanks
> > Dan
> >
> >
> > "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> > news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> > > Hi Daniel,
> > >
> > > I am just wondering under what user you did your test on query
analyzer.
> > >
> > > Have you tried the following to see what kind of result you get:
> > > 1. log in on the server by using your SQLProxy
> > > 2. Open query analyzer and connect to the server with Windows
> > Authentication
> > > 3. try a test and see if the client gets the required info.
> > >
> > > Sasan Saidi
> > > Senior DBA
> > >
> > > "Daniel Gard" wrote:
> > >
> > > > To any SQL Server MVP:
> > > >
> > > > We have a problem that out group can not seem to resolve. We have a
> > > > database that is used to alert and calculate required amounts of
> > assistance
> > > > to clients that monitor through a private frame relay WAN. When a
> > problem
> > > > occurs client 1 enters the request at a workstation which updates
the
> > > > database and causes a trigger to fire and starts a process to notify
the
> > > > other clients that assistance is required. This portion has been in
use
> > for
> > > > several years but now need to send information to a control area
system,
> > > > rather than single entity, that is in XML format so a program was
> > created to
> > > > take the data and format in accordance to the XML Schema rules the
area
> > > > used.
> > > >
> > > > At first it failed because the trigger called the EXE but the tables
> > were
> > > > locked by the first program called in the trigger. To resolve this
and
> > > > allow the trigger to continue thus unlocking the tables we changed
the
> > > > trigger to call a vbs script file rather than the EXE directly.
This
> > seemed
> > > > to resolve the situation with locked tables in the database but a
new
> > issue
> > > > has come up I can not figure out.
> > > >
> > > > The EXE has to use a X509 digital certificate to pass the data to
the
> > > > client's server. When I run the EXE from the command line it
works,
> > when I
> > > > use SQL Query Manager and run the trigger code (less the alert
portion)
> > it
> > > > works but when the trigger fires is fails to send the information.
It
> > builds
> > > > everything but the client never gets the XML stream? We currently
use
> > > > simple text files to track where the program process is but this
file
> > > > indicates it finishes properly and all cleanup of objects occurs
(Code
> > in
> > > > VB6 and we use WinHTTP Request Object 5.1). The code has been
altered
> > to
> > > > indicate within the text file who started the program and this
indicates
> > the
> > > > "SQLProxy" account is who is running the EXE file. It almost seems
that
> > when
> > > > impersonate process occurs the local server does not treat the
SQLProxy
> > > > account as the right one?
> > > >
> > > > How can we test using SQL Query Manager the exact process as when
the
> > > > trigger is fired from SQL Server?
> > > >
> > > >
> > > > Information:
> > > >
> > > > ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be
called
> > from
> > > > the trigger by the application user. This is what we created as
> > instructed
> > > > by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know
it
> > > > works because the first step in the trigger works.
> > > >
> > > > CERTIFICATE: We logged on to the server using SQLProxy so the
> > > > certificate could be installed into the "My" store for the user. We
> > could
> > > > move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Daniel, I am not sure if this can help in any ways but you might want to have
a quick look at it:
Article: Setting the Default Process Security Level Using VBScript
Link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/setting_the_default_process_security_level_using_vbscript.asp
Sasan
"Daniel Gard" wrote:
> Sasan
> I understand that and we have all of this working. The problem seems very
> strange even in my book because I am not sure if the impersonation process
> uses a modified SID from the one if you log on the the server using the same
> account. Does it take the domain user and create a temporary local account?
> If so then that is the problem and this user can not find the certificate.
> We are going to try to use a public certificate store and see if the process
> will complete.
> The program we wrote runs no matter what method the difference is when run
> from the command line or from SQL Query manager it finishes by sending XML
> to the the client but if the same process runs from being called by the vbs
> script that is called by the trigger the client get nothing.
> Thanks
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> > When xp_cmdshell is invoked by a user who is a member of the sysadmin
> fixed
> > server role, xp_cmdshell will be executed under the security context in
> which
> > the SQL Server service is running. When the user is not a member of the
> > sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
> > account, which is specified using xp_sqlagent_proxy_account. If the proxy
> > account is not available, xp_cmdshell will fail.
> >
> > Sasan
> >
> > "Daniel Gard" wrote:
> >
> > > Sasan
> > >
> > > The application user is a SQL Server account and not a Windows user
> account.
> > > We noticed the following behavior. If the application user account was
> > > placed into the SQL System Administrator group the program runsa as the
> > > Domain account for the SQL Server and if this user is not a in the
> > > administrator group then the program runs as SQLProxy.
> > >
> > > The more I mess with this the more I think the user running the
> application
> > > after the call from SQL Server can not find the digital certificate in
> the
> > > CURRENT_USER\My store. How does the impersonation really work with SQL
> > > Server?
> > >
> > > Thanks
> > > Dan
> > >
> > >
> > > "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> > > news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> > > > Hi Daniel,
> > > >
> > > > I am just wondering under what user you did your test on query
> analyzer.
> > > >
> > > > Have you tried the following to see what kind of result you get:
> > > > 1. log in on the server by using your SQLProxy
> > > > 2. Open query analyzer and connect to the server with Windows
> > > Authentication
> > > > 3. try a test and see if the client gets the required info.
> > > >
> > > > Sasan Saidi
> > > > Senior DBA
> > > >
> > > > "Daniel Gard" wrote:
> > > >
> > > > > To any SQL Server MVP:
> > > > >
> > > > > We have a problem that out group can not seem to resolve. We have a
> > > > > database that is used to alert and calculate required amounts of
> > > assistance
> > > > > to clients that monitor through a private frame relay WAN. When a
> > > problem
> > > > > occurs client 1 enters the request at a workstation which updates
> the
> > > > > database and causes a trigger to fire and starts a process to notify
> the
> > > > > other clients that assistance is required. This portion has been in
> use
> > > for
> > > > > several years but now need to send information to a control area
> system,
> > > > > rather than single entity, that is in XML format so a program was
> > > created to
> > > > > take the data and format in accordance to the XML Schema rules the
> area
> > > > > used.
> > > > >
> > > > > At first it failed because the trigger called the EXE but the tables
> > > were
> > > > > locked by the first program called in the trigger. To resolve this
> and
> > > > > allow the trigger to continue thus unlocking the tables we changed
> the
> > > > > trigger to call a vbs script file rather than the EXE directly.
> This
> > > seemed
> > > > > to resolve the situation with locked tables in the database but a
> new
> > > issue
> > > > > has come up I can not figure out.
> > > > >
> > > > > The EXE has to use a X509 digital certificate to pass the data to
> the
> > > > > client's server. When I run the EXE from the command line it
> works,
> > > when I
> > > > > use SQL Query Manager and run the trigger code (less the alert
> portion)
> > > it
> > > > > works but when the trigger fires is fails to send the information.
> It
> > > builds
> > > > > everything but the client never gets the XML stream? We currently
> use
> > > > > simple text files to track where the program process is but this
> file
> > > > > indicates it finishes properly and all cleanup of objects occurs
> (Code
> > > in
> > > > > VB6 and we use WinHTTP Request Object 5.1). The code has been
> altered
> > > to
> > > > > indicate within the text file who started the program and this
> indicates
> > > the
> > > > > "SQLProxy" account is who is running the EXE file. It almost seems
> that
> > > when
> > > > > impersonate process occurs the local server does not treat the
> SQLProxy
> > > > > account as the right one?
> > > > >
> > > > > How can we test using SQL Query Manager the exact process as when
> the
> > > > > trigger is fired from SQL Server?
> > > > >
> > > > >
> > > > > Information:
> > > > >
> > > > > ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be
> called
> > > from
> > > > > the trigger by the application user. This is what we created as
> > > instructed
> > > > > by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know
> it
> > > > > works because the first step in the trigger works.
> > > > >
> > > > > CERTIFICATE: We logged on to the server using SQLProxy so the
> > > > > certificate could be installed into the "My" store for the user. We
> > > could
> > > > > move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||Sasan,
I'll look this over maybe there is something we missed in the vbs script
being called by the trigger.
Thank You,
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:BF67C18C-AEEC-408D-9185-86D4B5DC0772@.microsoft.com...
> Daniel, I am not sure if this can help in any ways but you might want to
have
> a quick look at it:
> Article: Setting the Default Process Security Level Using VBScript
> Link:
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wmisdk/wmi/setting_the_default_process_security_level_using_vbscript.asp
>
> Sasan
> "Daniel Gard" wrote:
> > Sasan
> >
> > I understand that and we have all of this working. The problem seems
very
> > strange even in my book because I am not sure if the impersonation
process
> > uses a modified SID from the one if you log on the the server using the
same
> > account. Does it take the domain user and create a temporary local
account?
> > If so then that is the problem and this user can not find the
certificate.
> >
> > We are going to try to use a public certificate store and see if the
process
> > will complete.
> >
> > The program we wrote runs no matter what method the difference is when
run
> > from the command line or from SQL Query manager it finishes by sending
XML
> > to the the client but if the same process runs from being called by the
vbs
> > script that is called by the trigger the client get nothing.
> >
> > Thanks
> >
> > "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> > news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> > > When xp_cmdshell is invoked by a user who is a member of the sysadmin
> > fixed
> > > server role, xp_cmdshell will be executed under the security context
in
> > which
> > > the SQL Server service is running. When the user is not a member of
the
> > > sysadmin group, xp_cmdshell will impersonate the SQL Server Agent
proxy
> > > account, which is specified using xp_sqlagent_proxy_account. If the
proxy
> > > account is not available, xp_cmdshell will fail.
> > >
> > > Sasan
> > >
> > > "Daniel Gard" wrote:
> > >
> > > > Sasan
> > > >
> > > > The application user is a SQL Server account and not a Windows user
> > account.
> > > > We noticed the following behavior. If the application user account
was
> > > > placed into the SQL System Administrator group the program runsa as
the
> > > > Domain account for the SQL Server and if this user is not a in the
> > > > administrator group then the program runs as SQLProxy.
> > > >
> > > > The more I mess with this the more I think the user running the
> > application
> > > > after the call from SQL Server can not find the digital certificate
in
> > the
> > > > CURRENT_USER\My store. How does the impersonation really work with
SQL
> > > > Server?
> > > >
> > > > Thanks
> > > > Dan
> > > >
> > > >
> > > > "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in
message
> > > > news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> > > > > Hi Daniel,
> > > > >
> > > > > I am just wondering under what user you did your test on query
> > analyzer.
> > > > >
> > > > > Have you tried the following to see what kind of result you get:
> > > > > 1. log in on the server by using your SQLProxy
> > > > > 2. Open query analyzer and connect to the server with Windows
> > > > Authentication
> > > > > 3. try a test and see if the client gets the required info.
> > > > >
> > > > > Sasan Saidi
> > > > > Senior DBA
> > > > >
> > > > > "Daniel Gard" wrote:
> > > > >
> > > > > > To any SQL Server MVP:
> > > > > >
> > > > > > We have a problem that out group can not seem to resolve. We
have a
> > > > > > database that is used to alert and calculate required amounts of
> > > > assistance
> > > > > > to clients that monitor through a private frame relay WAN. When
a
> > > > problem
> > > > > > occurs client 1 enters the request at a workstation which
updates
> > the
> > > > > > database and causes a trigger to fire and starts a process to
notify
> > the
> > > > > > other clients that assistance is required. This portion has
been in
> > use
> > > > for
> > > > > > several years but now need to send information to a control area
> > system,
> > > > > > rather than single entity, that is in XML format so a program
was
> > > > created to
> > > > > > take the data and format in accordance to the XML Schema rules
the
> > area
> > > > > > used.
> > > > > >
> > > > > > At first it failed because the trigger called the EXE but the
tables
> > > > were
> > > > > > locked by the first program called in the trigger. To resolve
this
> > and
> > > > > > allow the trigger to continue thus unlocking the tables we
changed
> > the
> > > > > > trigger to call a vbs script file rather than the EXE directly.
> > This
> > > > seemed
> > > > > > to resolve the situation with locked tables in the database but
a
> > new
> > > > issue
> > > > > > has come up I can not figure out.
> > > > > >
> > > > > > The EXE has to use a X509 digital certificate to pass the data
to
> > the
> > > > > > client's server. When I run the EXE from the command line it
> > works,
> > > > when I
> > > > > > use SQL Query Manager and run the trigger code (less the alert
> > portion)
> > > > it
> > > > > > works but when the trigger fires is fails to send the
information.
> > It
> > > > builds
> > > > > > everything but the client never gets the XML stream? We
currently
> > use
> > > > > > simple text files to track where the program process is but this
> > file
> > > > > > indicates it finishes properly and all cleanup of objects occurs
> > (Code
> > > > in
> > > > > > VB6 and we use WinHTTP Request Object 5.1). The code has been
> > altered
> > > > to
> > > > > > indicate within the text file who started the program and this
> > indicates
> > > > the
> > > > > > "SQLProxy" account is who is running the EXE file. It almost
seems
> > that
> > > > when
> > > > > > impersonate process occurs the local server does not treat the
> > SQLProxy
> > > > > > account as the right one?
> > > > > >
> > > > > > How can we test using SQL Query Manager the exact process as
when
> > the
> > > > > > trigger is fired from SQL Server?
> > > > > >
> > > > > >
> > > > > > Information:
> > > > > >
> > > > > > ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be
> > called
> > > > from
> > > > > > the trigger by the application user. This is what we created as
> > > > instructed
> > > > > > by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we
know
> > it
> > > > > > works because the first step in the trigger works.
> > > > > >
> > > > > > CERTIFICATE: We logged on to the server using SQLProxy so
the
> > > > > > certificate could be installed into the "My" store for the user.
We
> > > > could
> > > > > > move it to HKEY_LOCAL_MACHINE\Trusted People if you think we
should.
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

call to xp_cmdshell from trigger problem

To any SQL Server MVP:
We have a problem that out group can not seem to resolve. We have a
database that is used to alert and calculate required amounts of assistance
to clients that monitor through a private frame relay WAN. When a problem
occurs client 1 enters the request at a workstation which updates the
database and causes a trigger to fire and starts a process to notify the
other clients that assistance is required. This portion has been in use for
several years but now need to send information to a control area system,
rather than single entity, that is in XML format so a program was created to
take the data and format in accordance to the XML Schema rules the area
used.
At first it failed because the trigger called the EXE but the tables were
locked by the first program called in the trigger. To resolve this and
allow the trigger to continue thus unlocking the tables we changed the
trigger to call a vbs script file rather than the EXE directly. This seemed
to resolve the situation with locked tables in the database but a new issue
has come up I can not figure out.
The EXE has to use a X509 digital certificate to pass the data to the
client's server. When I run the EXE from the command line it works, when I
use SQL Query Manager and run the trigger code (less the alert portion) it
works but when the trigger fires is fails to send the information. It builds
everything but the client never gets the XML stream? We currently use
simple text files to track where the program process is but this file
indicates it finishes properly and all cleanup of objects occurs (Code in
VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
indicate within the text file who started the program and this indicates the
"SQLProxy" account is who is running the EXE file. It almost seems that when
impersonate process occurs the local server does not treat the SQLProxy
account as the right one?
How can we test using SQL Query Manager the exact process as when the
trigger is fired from SQL Server?
Information:
ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called from
the trigger by the application user. This is what we created as instructed
by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
works because the first step in the trigger works.
CERTIFICATE: We logged on to the server using SQLProxy so the
certificate could be installed into the "My" store for the user. We could
move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.Hi Daniel,
I am just wondering under what user you did your test on query analyzer.
Have you tried the following to see what kind of result you get:
1. log in on the server by using your SQLProxy
2. Open query analyzer and connect to the server with Windows Authentication
3. try a test and see if the client gets the required info.
Sasan Saidi
Senior DBA
"Daniel Gard" wrote:

> To any SQL Server MVP:
> We have a problem that out group can not seem to resolve. We have a
> database that is used to alert and calculate required amounts of assistanc
e
> to clients that monitor through a private frame relay WAN. When a problem
> occurs client 1 enters the request at a workstation which updates the
> database and causes a trigger to fire and starts a process to notify the
> other clients that assistance is required. This portion has been in use f
or
> several years but now need to send information to a control area system,
> rather than single entity, that is in XML format so a program was created
to
> take the data and format in accordance to the XML Schema rules the area
> used.
> At first it failed because the trigger called the EXE but the tables were
> locked by the first program called in the trigger. To resolve this and
> allow the trigger to continue thus unlocking the tables we changed the
> trigger to call a vbs script file rather than the EXE directly. This seem
ed
> to resolve the situation with locked tables in the database but a new issu
e
> has come up I can not figure out.
> The EXE has to use a X509 digital certificate to pass the data to the
> client's server. When I run the EXE from the command line it works, when
I
> use SQL Query Manager and run the trigger code (less the alert portion) it
> works but when the trigger fires is fails to send the information. It buil
ds
> everything but the client never gets the XML stream? We currently use
> simple text files to track where the program process is but this file
> indicates it finishes properly and all cleanup of objects occurs (Code in
> VB6 and we use WinHTTP Request Object 5.1). The code has been altered to
> indicate within the text file who started the program and this indicates t
he
> "SQLProxy" account is who is running the EXE file. It almost seems that wh
en
> impersonate process occurs the local server does not treat the SQLProxy
> account as the right one?
> How can we test using SQL Query Manager the exact process as when the
> trigger is fired from SQL Server?
>
> Information:
> ACCOUNT: SQLProxy is a domain account so xp_cmdshell can be called fro
m
> the trigger by the application user. This is what we created as instructed
> by SQLAgentCmdExec rules using xp_sqlagent_proxy_account and we know it
> works because the first step in the trigger works.
> CERTIFICATE: We logged on to the server using SQLProxy so the
> certificate could be installed into the "My" store for the user. We could
> move it to HKEY_LOCAL_MACHINE\Trusted People if you think we should.
>
>
>
>
>|||Sasan
We have tried all of the following:
1. Logged on to the Windows server as the Domain Administrator and
connecting to SQL Server using windows authentication.
2. Logged on to the Windows server as the SQLProxy Account and connecting
to SQL Server using windows authentication.
We did notice is logged on to the Windows server as either 1 or 2 and
logging onto the SQL Server as the application user it does not work.
Thanks
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Hi Daniel,
> I am just wondering under what user you did your test on query analyzer.
> Have you tried the following to see what kind of result you get:
> 1. log in on the server by using your SQLProxy
> 2. Open query analyzer and connect to the server with Windows
Authentication[vbcol=seagreen]
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
>
assistance[vbcol=seagreen]
problem[vbcol=seagreen]
for[vbcol=seagreen]
created to[vbcol=seagreen]
were[vbcol=seagreen]
seemed[vbcol=seagreen]
issue[vbcol=seagreen]
when I[vbcol=seagreen]
it[vbcol=seagreen]
builds[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
from[vbcol=seagreen]
instructed[vbcol=seagreen]
could[vbcol=seagreen]|||Sasan
The application user is a SQL Server account and not a Windows user account.
We noticed the following behavior. If the application user account was
placed into the SQL System Administrator group the program runsa as the
Domain account for the SQL Server and if this user is not a in the
administrator group then the program runs as SQLProxy.
The more I mess with this the more I think the user running the application
after the call from SQL Server can not find the digital certificate in the
CURRENT_USER\My store. How does the impersonation really work with SQL
Server?
Thanks
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Hi Daniel,
> I am just wondering under what user you did your test on query analyzer.
> Have you tried the following to see what kind of result you get:
> 1. log in on the server by using your SQLProxy
> 2. Open query analyzer and connect to the server with Windows
Authentication[vbcol=seagreen]
> 3. try a test and see if the client gets the required info.
> Sasan Saidi
> Senior DBA
> "Daniel Gard" wrote:
>
assistance[vbcol=seagreen]
problem[vbcol=seagreen]
for[vbcol=seagreen]
created to[vbcol=seagreen]
were[vbcol=seagreen]
seemed[vbcol=seagreen]
issue[vbcol=seagreen]
when I[vbcol=seagreen]
it[vbcol=seagreen]
builds[vbcol=seagreen]
in[vbcol=seagreen]
to[vbcol=seagreen]
the[vbcol=seagreen]
when[vbcol=seagreen]
from[vbcol=seagreen]
instructed[vbcol=seagreen]
could[vbcol=seagreen]|||When xp_cmdshell is invoked by a user who is a member of the sysadmin fixed
server role, xp_cmdshell will be executed under the security context in whic
h
the SQL Server service is running. When the user is not a member of the
sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
account, which is specified using xp_sqlagent_proxy_account. If the proxy
account is not available, xp_cmdshell will fail.
Sasan
"Daniel Gard" wrote:

> Sasan
> The application user is a SQL Server account and not a Windows user accoun
t.
> We noticed the following behavior. If the application user account was
> placed into the SQL System Administrator group the program runsa as the
> Domain account for the SQL Server and if this user is not a in the
> administrator group then the program runs as SQLProxy.
> The more I mess with this the more I think the user running the applicatio
n
> after the call from SQL Server can not find the digital certificate in the
> CURRENT_USER\My store. How does the impersonation really work with SQL
> Server?
> Thanks
> Dan
>
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:A3A3E257-2889-4C54-BB23-05B4B352F696@.microsoft.com...
> Authentication
> assistance
> problem
> for
> created to
> were
> seemed
> issue
> when I
> it
> builds
> in
> to
> the
> when
> from
> instructed
> could
>
>|||Sasan
I understand that and we have all of this working. The problem seems very
strange even in my book because I am not sure if the impersonation process
uses a modified SID from the one if you log on the the server using the same
account. Does it take the domain user and create a temporary local account?
If so then that is the problem and this user can not find the certificate.
We are going to try to use a public certificate store and see if the process
will complete.
The program we wrote runs no matter what method the difference is when run
from the command line or from SQL Query manager it finishes by sending XML
to the the client but if the same process runs from being called by the vbs
script that is called by the trigger the client get nothing.
Thanks
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> When xp_cmdshell is invoked by a user who is a member of the sysadmin
fixed
> server role, xp_cmdshell will be executed under the security context in
which[vbcol=seagreen]
> the SQL Server service is running. When the user is not a member of the
> sysadmin group, xp_cmdshell will impersonate the SQL Server Agent proxy
> account, which is specified using xp_sqlagent_proxy_account. If the proxy
> account is not available, xp_cmdshell will fail.
> Sasan
> "Daniel Gard" wrote:
>
account.[vbcol=seagreen]
application[vbcol=seagreen]
the[vbcol=seagreen]
analyzer.[vbcol=seagreen]
the[vbcol=seagreen]
the[vbcol=seagreen]
use[vbcol=seagreen]
system,[vbcol=seagreen]
area[vbcol=seagreen]
and[vbcol=seagreen]
the[vbcol=seagreen]
This[vbcol=seagreen]
new[vbcol=seagreen]
the[vbcol=seagreen]
works,[vbcol=seagreen]
portion)[vbcol=seagreen]
It[vbcol=seagreen]
use[vbcol=seagreen]
file[vbcol=seagreen]
(Code[vbcol=seagreen]
altered[vbcol=seagreen]
indicates[vbcol=seagreen]
that[vbcol=seagreen]
SQLProxy[vbcol=seagreen]
the[vbcol=seagreen]
called[vbcol=seagreen]
it[vbcol=seagreen]|||Daniel, I am not sure if this can help in any ways but you might want to hav
e
a quick look at it:
Article: Setting the Default Process Security Level Using VBScript
Link:
http://msdn.microsoft.com/library/d...ng_vbscript.asp
Sasan
"Daniel Gard" wrote:

> Sasan
> I understand that and we have all of this working. The problem seems very
> strange even in my book because I am not sure if the impersonation process
> uses a modified SID from the one if you log on the the server using the sa
me
> account. Does it take the domain user and create a temporary local account
?
> If so then that is the problem and this user can not find the certificate.
> We are going to try to use a public certificate store and see if the proce
ss
> will complete.
> The program we wrote runs no matter what method the difference is when run
> from the command line or from SQL Query manager it finishes by sending XML
> to the the client but if the same process runs from being called by the vb
s
> script that is called by the trigger the client get nothing.
> Thanks
> "Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
> news:7C116F51-C3BB-4ADD-96FC-9231D90287BE@.microsoft.com...
> fixed
> which
> account.
> application
> the
> analyzer.
> the
> the
> use
> system,
> area
> and
> the
> This
> new
> the
> works,
> portion)
> It
> use
> file
> (Code
> altered
> indicates
> that
> SQLProxy
> the
> called
> it
>
>|||Sasan,
I'll look this over maybe there is something we missed in the vbs script
being called by the trigger.
Thank You,
Dan
"Sasan Saidi" <SasanSaidi@.discussions.microsoft.com> wrote in message
news:BF67C18C-AEEC-408D-9185-86D4B5DC0772@.microsoft.com...
> Daniel, I am not sure if this can help in any ways but you might want to
have
> a quick look at it:
> Article: Setting the Default Process Security Level Using VBScript
> Link:
>
http://msdn.microsoft.com/library/d...ng_vbscript.asp[vbcol=seagreen]
>
> Sasan
> "Daniel Gard" wrote:
>
very[vbcol=seagreen]
process[vbcol=seagreen]
same[vbcol=seagreen]
account?[vbcol=seagreen]
certificate.[vbcol=seagreen]
process[vbcol=seagreen]
run[vbcol=seagreen]
XML[vbcol=seagreen]
vbs[vbcol=seagreen]
in[vbcol=seagreen]
the[vbcol=seagreen]
proxy[vbcol=seagreen]
proxy[vbcol=seagreen]
was[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
SQL[vbcol=seagreen]
message[vbcol=seagreen]
have a[vbcol=seagreen]
a[vbcol=seagreen]
updates[vbcol=seagreen]
notify[vbcol=seagreen]
been in[vbcol=seagreen]
was[vbcol=seagreen]
the[vbcol=seagreen]
tables[vbcol=seagreen]
this[vbcol=seagreen]
changed[vbcol=seagreen]
a[vbcol=seagreen]
to[vbcol=seagreen]
information.[vbcol=seagreen]
currently[vbcol=seagreen]
seems[vbcol=seagreen]
when[vbcol=seagreen]
know[vbcol=seagreen]
the[vbcol=seagreen]
We[vbcol=seagreen]
should.[vbcol=seagreen]

Friday, February 24, 2012

Calendar object for parameters

Hi,
I've some searching around this group but haven't found any good
answers on my question. There isn't an out of the box way of
attaching a calendar object to let's say a startdate parameter on a
report. I was reading you could accomplish this through the use of an
active x object. Has anyone attempted this? If not does anyone have
any idea of how it would be accomplished?You'd have to write a custom ASP.NET app with the calendar control and the
report in it, and pass the dates as parameters to the report.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"cordata5" <cordata5@.yahoo.com> wrote in message
news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> Hi,
> I've some searching around this group but haven't found any good
> answers on my question. There isn't an out of the box way of
> attaching a calendar object to let's say a startdate parameter on a
> report. I was reading you could accomplish this through the use of an
> active x object. Has anyone attempted this? If not does anyone have
> any idea of how it would be accomplished?|||I can't seem to get this to work.
In Report Manager I go to the folder I want and upload my aspx parameter
page with the calendars on it. The calendars do not show. Is the bin folder
needed? Where would I put it?
"Ravi Mumulla (Microsoft)" wrote:
> You'd have to write a custom ASP.NET app with the calendar control and the
> report in it, and pass the dates as parameters to the report.
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "cordata5" <cordata5@.yahoo.com> wrote in message
> news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> > Hi,
> > I've some searching around this group but haven't found any good
> > answers on my question. There isn't an out of the box way of
> > attaching a calendar object to let's say a startdate parameter on a
> > report. I was reading you could accomplish this through the use of an
> > active x object. Has anyone attempted this? If not does anyone have
> > any idea of how it would be accomplished?
>
>|||You should create your own web site. Do not mix your in with Reporting
Services, that is asking for trouble. Note that you also then need to
integrate with RS using either URL integration or webservices.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:D4AA33F6-E6CB-4806-8286-DD2C876B4F31@.microsoft.com...
> I can't seem to get this to work.
> In Report Manager I go to the folder I want and upload my aspx parameter
> page with the calendars on it. The calendars do not show. Is the bin
folder
> needed? Where would I put it?
> "Ravi Mumulla (Microsoft)" wrote:
> > You'd have to write a custom ASP.NET app with the calendar control and
the
> > report in it, and pass the dates as parameters to the report.
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> > "cordata5" <cordata5@.yahoo.com> wrote in message
> > news:b4f86058.0409301358.12c0ce47@.posting.google.com...
> > > Hi,
> > > I've some searching around this group but haven't found any good
> > > answers on my question. There isn't an out of the box way of
> > > attaching a calendar object to let's say a startdate parameter on a
> > > report. I was reading you could accomplish this through the use of an
> > > active x object. Has anyone attempted this? If not does anyone have
> > > any idea of how it would be accomplished?
> >
> >
> >

Sunday, February 19, 2012

Calculation in sql

We have a Result table with fields called QuestionID and Answer.
How can I get a query result where the result is group by the answer and its
percentage ?
Thanks.
For ex: Results table:
QuestionID Answer
1 aaa
1 aaa
1 ddd
2 yyyy
I want the result of the query for QuestionID = 1 to look like:
aaa 2 66.6666
ddd 1 33.3333
CREATE PROCEDURE GetResults
@.QuestionID numeric(9)
AS
SELECT Answer, COUNT(Answer) AS votes
FROM Results
WHERE (Question = @.QuestionID)
GROUP BY Answer
GOHello, fniles
Use something like this:
SELECT Answer,
COUNT(*) AS votes,
100. * COUNT(*) / (
SELECT COUNT(*)
FROM Results
WHERE QuestionID = @.QuestionID
) as Percentage
FROM Results
WHERE QuestionID = @.QuestionID
GROUP BY Answer
Razvan|||Thanks ! That works.
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1133464892.892475.83390@.z14g2000cwz.googlegroups.com...
> Hello, fniles
> Use something like this:
> SELECT Answer,
> COUNT(*) AS votes,
> 100. * COUNT(*) / (
> SELECT COUNT(*)
> FROM Results
> WHERE QuestionID = @.QuestionID
> ) as Percentage
> FROM Results
> WHERE QuestionID = @.QuestionID
> GROUP BY Answer
> Razvan
>

Thursday, February 16, 2012

Calculating totals for second group level

I have a report that shows Customers, Categories, and Quantity, grouped
by Customers and Categories. (I'm using the Northwind db to try to
figure this out.) I am able to get a grand total in the header for All
Customers and All Categories, but not for each of the categories.
Does anybody know how to do this?
Current Report:
Customer Category Qty
All Customers All Categories 51,317
Alf. Futterk. 174
Beverages 36
Condiments 44
Dairy 35
...
Desired Report:
Customer Category Qty
All Customers All Categories 51,317
Beverages 9,532
Condiments 5,298
Dairy 9,149
Alf. Futterk. 174
Beverages 36
Condiments 44
Dairy 35
...
rdl for existing report:
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini..."xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>3pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>16pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.3in</Height>
<CanGrow>true</CanGrow>
<Value>Northwind Subtotals</Value>
</Textbox>
<Table Name="table1">
<Style>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Customer"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Category"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<CanGrow>true</CanGrow>
<Value>="All Customers"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="All Categories"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<DataSetName>northwindsql</DataSetName>
<Top>0.3in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="shipname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>shipname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!shipname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>12pt</FontSize>
<Color>Green</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_shipname">
<GroupExpressions>
<GroupExpression>=Fields!shipname.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!shipname.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_CategoryName">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>shipname</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.125in</Width>
</TableColumn>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.49in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="northwindsql">
<rd:DataSourceID>c2582881-4536-427b-abea-b0fb61d2f245</rd:DataSourceID>
<DataSourceReference>northwindsql</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="northwindsql">
<Fields>
<Field Name="shipname">
<DataField>shipname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>northwindsql</DataSourceName>
<CommandText>SELECT Orders.shipname, Categories.CategoryName,
[Order Details].UnitPrice, [Order Details].Quantity, Orders.OrderID
FROM Orders
INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID
INNER JOIN Products
ON [Order Details].ProductID = Products.ProductID
INNER JOIN Categories
ON Products.CategoryID = Categories.CategoryID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>053a7cd6-e7c8-4554-b1a2-c2291033e518</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>Try this:
* in the second table header, add a list with a details grouping based on
categories
* put two textboxes in that list, one for the category name, the other for
the Sum(...)
I attached the updated report at the bottom of this posting.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jon" <jcrowell@.gmail.com> wrote in message
news:1128960913.001129.271160@.g49g2000cwa.googlegroups.com...
>I have a report that shows Customers, Categories, and Quantity, grouped
> by Customers and Categories. (I'm using the Northwind db to try to
> figure this out.) I am able to get a grand total in the header for All
> Customers and All Categories, but not for each of the categories.
> Does anybody know how to do this?
> Current Report:
> Customer Category Qty
> All Customers All Categories 51,317
> Alf. Futterk. 174
> Beverages 36
> Condiments 44
> Dairy 35
> ...
> Desired Report:
> Customer Category Qty
> All Customers All Categories 51,317
> Beverages 9,532
> Condiments 5,298
> Dairy 9,149
> Alf. Futterk. 174
> Beverages 36
> Condiments 44
> Dairy 35
> ...
=======================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
<Top>3pt</Top>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
<Top>Solid</Top>
</BorderStyle>
<FontSize>16pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.3in</Height>
<Value>Northwind Subtotals</Value>
<CanGrow>true</CanGrow>
</Textbox>
<Table Name="table1">
<Style>
<BorderWidth>
<Top>2pt</Top>
</BorderWidth>
<BorderColor>
<Top>DarkGoldenrod</Top>
</BorderColor>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Customer"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="Category"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Cornsilk</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Goldenrod</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Quantity</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>10</ZIndex>
<CanGrow>true</CanGrow>
<Value>="All Customers"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>="All Categories"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>8</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<Color>OliveDrab</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ColSpan>2</ColSpan>
<ReportItems>
<List Name="list1">
<ZIndex>6</ZIndex>
<Style>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
</Style>
<Grouping Name="list1_Details_Group">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="Quantity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>Quantity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
<Left>1.875in</Left>
</Textbox>
<Textbox Name="CategoryName_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>CategoryName_1</rd:DefaultName>
<Width>1.625in</Width>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
<Left>0.125in</Left>
</Textbox>
</ReportItems>
</List>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<DataSetName>northwindsql</DataSetName>
<Top>0.3in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="shipname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>shipname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!shipname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>12pt</FontSize>
<Color>Green</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_shipname">
<GroupExpressions>
<GroupExpression>=Fields!shipname.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!shipname.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.24in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>11pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CategoryName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>CategoryName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>#,###</Format>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_CategoryName">
<GroupExpressions>
<GroupExpression>=Fields!CategoryName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Visibility>
<ToggleItem>shipname</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
<Sorting>
<SortBy>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.125in</Width>
</TableColumn>
<TableColumn>
<Width>1.875in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.49in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>62d84756-f9a0-4007-a8f5-1a466039dfe4</rd:DataSourceID>
<DataSourceReference>Northwind</DataSourceReference>
</DataSource>
</DataSources>
<Width>5in</Width>
<DataSets>
<DataSet Name="northwindsql">
<Fields>
<Field Name="shipname">
<DataField>shipname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CategoryName">
<DataField>CategoryName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Orders.shipname, Categories.CategoryName,
[Order Details].UnitPrice, [Order Details].Quantity, Orders.OrderID
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID INNER JOIN
Products ON [Order Details].ProductID =Products.ProductID INNER JOIN
Categories ON Products.CategoryID =Categories.CategoryID</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>053a7cd6-e7c8-4554-b1a2-c2291033e518</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>