Excluding repeated values in a sum

The scenario is as follows:

I have rows coming from the db including:
Contract Number, Contract Name, owner and Actions associated with each contract.

SQL statement brings back:
Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
1234 123453 $50 Bob y
534232 5464634211 $30 Harry z

The problem is that each contract can have multiple actions associated with it...
There ideal output would be:

Contract Number Contract Name Sales Owner Action
1234 123453 $50 Neil x
Bob y
534232 5464634211 $30 Harry z
Total: $80

Basically I need to hide and not include repeated items based on a contract number... one idea I had was creating a group based on contract number and then display info in the header and then only owner and actions in the detail section.. The problem is Totals... how can I can it to avoid count the duplicated values..

Any help would be greatly appreciated.

Thanks,
Neil

Could you sum the totals and divide by the rowcount? This would give an accurate total for the repeated values within the group, but I haven't tested it to know if you can accurately retrieve a grand total.|||Otherwise known as the AVG function. Guess I'm not thinking to well this morning. With that being the case, there would be an issue with the Grand Total.|||Hi Simone,

Yeah the grand total would not work for that, otherwise it would work I guess.. any other ideas?

Thanks,
Neil
|||

Hello Neil,

One thing you could try is to create a public variable and a function to increment your variable (via your code window), then in each detail row, increment if the previous record's contract number is different from the current number.

In each detail row, call your function to increment the value. =Code.IncrementValue(IIf(Previous(Fields!ContractNum.Value) <> Fields!ContractNum.Value, Fields!ContractNum.Value, 0))

In your total textbox, display your variable. =Code.ContractTotal

I haven't tried this, but hopefully it will get you started.

Hope this helps.

Jarret

Excessive Page Faults?

I am noticing that an many of my servers (Server 2003 EE SP2 R2), that the
MsMpEng.exe and MOMHost.exe processes are creating a significant number of
page faults.
For example, on my SQL 2000 SP4 server with 4GB RAM which has been up for 19
days has nearly 19M page faults on MsMpEng and over 16M faults on MOMHost.
Another example is my Exchange 2003 SP2 with 4GB RAM which has also been up
for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults on
MOMHost.
Keep in mind that the "M" with the number is million.
We only recently started using Forefront on our servers, and this appears to
be a relatively new issue, and I would like to know if others have been
experiencing this as well.
RichardThis is not an exchange issue...
But take a look at
http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
"Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that the
>MsMpEng.exe and MOMHost.exe processes are creating a significant number of
>page faults.
> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up for
> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
> MOMHost.
> Another example is my Exchange 2003 SP2 with 4GB RAM which has also been
> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults on
> MOMHost.
> Keep in mind that the "M" with the number is million.
> We only recently started using Forefront on our servers, and this appears
> to be a relatively new issue, and I would like to know if others have been
> experiencing this as well.
> Richard|||Thanks for the link to the article. This does give some additional
information, however the problem in the article points more to high CPU
utilization rather than high number of page faults. Perhaps since this also
includes the MOM components, I should try posting in that forum as the
Forefront group is fairly new?
Richard
"Betelgeuse" <betelgeuse@.news.postalias> wrote in message
news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
> This is not an exchange issue...
> But take a look at
> http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
>
> "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
> news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
>>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that the
>>MsMpEng.exe and MOMHost.exe processes are creating a significant number of
>>page faults.
>> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up for
>> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
>> MOMHost.
>> Another example is my Exchange 2003 SP2 with 4GB RAM which has also been
>> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults on
>> MOMHost.
>> Keep in mind that the "M" with the number is million.
>> We only recently started using Forefront on our servers, and this appears
>> to be a relatively new issue, and I would like to know if others have
>> been experiencing this as well.
>> Richard
>|||Dear Richard,
Thank you for posting here.
I understand that the process MsMpEng.exe and MOMHost.exe processes are
creating a significant number of page faults on several servers.
According to your description, it appears that the issue should not on the
SQL Server side. Based on my experience, this behavior can occur due to a
Memory Leak but sometimes it is normal for some processes. In order to
ensure the current status, I would like to confirm the following
information before going further:
1. Has the performance on these severs been affected due to this issue?
2. Please also use the Performance Monitor utility to monitor the stability
of memory pages used by these two processes.
To do so, please refer to the following steps:
--
a. Enter the Control Panel -> Administrative Tools -> Performance.
b. Select "Process" in the "Performance object" selection
c. Add the following counter for the two processes (MsMpEng.exe and
MOMHost.exe) in question:
- Working Set (The Working Set is the set of memory pages touched recently
by the threads in the process)
- Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
virtual address space the process is using)
NOTE: You can also add some main process for this server. E.X: sqlservr.exe
process
d. If the indication goes very smoothly, it indicates that the memory usage
of these processes is stable. In opposition, the memory leak issue may
exist.
At the same time, on the SQL Server side, we can attempt to adjust the
memory to see if it helps. Below are the detail steps:
--
a. Open the SQL Server Management Studio
b. Right click on the SQL Server instance->choose Properties.
c. Click on the Memory tab on the left panel, adjust the memory for your
SQL Server.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
<OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
| Subject: Re: Excessive Page Faults?
| Date: Thu, 11 Oct 2007 12:56:40 -0700
| Lines: 1
| Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=response
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| Importance: Normal
| X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
| X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
| X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
| Newsgroups:
microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
t.public.sqlserver.server
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
microsoft.public.sqlserver.server:27713
microsoft.public.exchange.admin:47476
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Thanks for the link to the article. This does give some additional
| information, however the problem in the article points more to high CPU
| utilization rather than high number of page faults. Perhaps since this
also
| includes the MOM components, I should try posting in that forum as the
| Forefront group is fairly new?
|
| Richard
|
| "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
| news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
| > This is not an exchange issue...
| >
| > But take a look at
| > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
| >
| >
| > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
| > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
| >>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that
the
| >>MsMpEng.exe and MOMHost.exe processes are creating a significant number
of
| >>page faults.
| >>
| >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up
for
| >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
| >> MOMHost.
| >>
| >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
been
| >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M faults
on
| >> MOMHost.
| >>
| >> Keep in mind that the "M" with the number is million.
| >>
| >> We only recently started using Forefront on our servers, and this
appears
| >> to be a relatively new issue, and I would like to know if others have
| >> been experiencing this as well.
| >>
| >> Richard
| >
| >
||||Dear Richard,
We just want to say hi and check if there is anything further we can do for
you. If there is, please don't hesitate to let us know.
Regards,
Sophie Tan, MCSE 2000
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
| X-Tomcat-ID: 94083421
| References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
<OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
<84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: v-adamqu@.online.microsoft.com (Adams Qu [MSFT])
| Organization: Microsoft
| Date: Fri, 12 Oct 2007 07:35:19 GMT
| Subject: Re: Excessive Page Faults?
| X-Tomcat-NG: microsoft.public.sqlserver.server
| Message-ID: <8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| Lines: 132
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:27758
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
| Dear Richard,
|
| Thank you for posting here.
|
| I understand that the process MsMpEng.exe and MOMHost.exe processes are
| creating a significant number of page faults on several servers.
|
| According to your description, it appears that the issue should not on
the
| SQL Server side. Based on my experience, this behavior can occur due to a
| Memory Leak but sometimes it is normal for some processes. In order to
| ensure the current status, I would like to confirm the following
| information before going further:
|
| 1. Has the performance on these severs been affected due to this issue?
|
| 2. Please also use the Performance Monitor utility to monitor the
stability
| of memory pages used by these two processes.
|
| To do so, please refer to the following steps:
| --
| a. Enter the Control Panel -> Administrative Tools -> Performance.
| b. Select "Process" in the "Performance object" selection
| c. Add the following counter for the two processes (MsMpEng.exe and
| MOMHost.exe) in question:
|
| - Working Set (The Working Set is the set of memory pages touched
recently
| by the threads in the process)
| - Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
| virtual address space the process is using)
|
| NOTE: You can also add some main process for this server. E.X:
sqlservr.exe
| process
|
| d. If the indication goes very smoothly, it indicates that the memory
usage
| of these processes is stable. In opposition, the memory leak issue may
| exist.
|
| At the same time, on the SQL Server side, we can attempt to adjust the
| memory to see if it helps. Below are the detail steps:
| --
| a. Open the SQL Server Management Studio
| b. Right click on the SQL Server instance->choose Properties.
| c. Click on the Memory tab on the left panel, adjust the memory for your
| SQL Server.
|
| Have a nice day!
|
| Best regards,
|
| Adams Qu
| MCSE, MCDBA, MCTS
| Microsoft Online Support
|
| Microsoft Global Technical Support Center
|
| Get Secure! - www.microsoft.com/security
| =====================================================| When responding to posts, please "Reply to Group" via your newsreader so
| that others may learn and benefit from your issue.
| =====================================================| This posting is provided "AS IS" with no warranties, and confers no
rights.
|
| --
| | From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| | References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
| <OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
| | Subject: Re: Excessive Page Faults?
| | Date: Thu, 11 Oct 2007 12:56:40 -0700
| | Lines: 1
| | Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | format=flowed;
| | charset="iso-8859-1";
| | reply-type=response
| | Content-Transfer-Encoding: 7bit
| | X-Priority: 3
| | X-MSMail-Priority: Normal
| | Importance: Normal
| | X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| | X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| | X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
| | X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
| | X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
| | Newsgroups:
|
microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
| t.public.sqlserver.server
| | Path: TK2MSFTNGHUB02.phx.gbl
| | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
| microsoft.public.sqlserver.server:27713
| microsoft.public.exchange.admin:47476
| | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| | X-Tomcat-NG: microsoft.public.sqlserver.server
| |
| | Thanks for the link to the article. This does give some additional
| | information, however the problem in the article points more to high CPU
| | utilization rather than high number of page faults. Perhaps since this
| also
| | includes the MOM components, I should try posting in that forum as the
| | Forefront group is fairly new?
| |
| | Richard
| |
| | "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
| | news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
| | > This is not an exchange issue...
| | >
| | > But take a look at
| | > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
| | >
| | >
| | > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
| | > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
| | >>I am noticing that an many of my servers (Server 2003 EE SP2 R2),
that
| the
| | >>MsMpEng.exe and MOMHost.exe processes are creating a significant
number
| of
| | >>page faults.
| | >>
| | >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been
up
| for
| | >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
| | >> MOMHost.
| | >>
| | >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
| been
| | >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M
faults
| on
| | >> MOMHost.
| | >>
| | >> Keep in mind that the "M" with the number is million.
| | >>
| | >> We only recently started using Forefront on our servers, and this
| appears
| | >> to be a relatively new issue, and I would like to know if others
have
| | >> been experiencing this as well.
| | >>
| | >> Richard
| | >
| | >
| |
|
||||Sorry for the long delay in replying to this post. With the fires here is
SoCal, things have been fairly hectic.
Following your suggestions, I am verified that there is not a memory leak
with regards to these processes. Therefore, I am left with the simple
concern of the performance of Forefront on the Server platform. I will raise
this issue in the Forefront forums.
I do have a recurring issue with timeouts to the database that I am
troubleshooting, and it was this issue that prompted my looking at the
paging performance. I have consistently believed that the cause if these
timeouts is due to our network configuration, but for the sake of
completeness, I have been verifying our SQL setup. I will post regarding
those timeouts in another thread.
Sophie Tan, if you see this, I wanted to thank you for your follow up post
as well. At this time, I would consider this thread closed.
Richard Perry
"Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
news:8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl...
> Dear Richard,
> Thank you for posting here.
> I understand that the process MsMpEng.exe and MOMHost.exe processes are
> creating a significant number of page faults on several servers.
> According to your description, it appears that the issue should not on the
> SQL Server side. Based on my experience, this behavior can occur due to a
> Memory Leak but sometimes it is normal for some processes. In order to
> ensure the current status, I would like to confirm the following
> information before going further:
> 1. Has the performance on these severs been affected due to this issue?
> 2. Please also use the Performance Monitor utility to monitor the
> stability
> of memory pages used by these two processes.
> To do so, please refer to the following steps:
> --
> a. Enter the Control Panel -> Administrative Tools -> Performance.
> b. Select "Process" in the "Performance object" selection
> c. Add the following counter for the two processes (MsMpEng.exe and
> MOMHost.exe) in question:
> - Working Set (The Working Set is the set of memory pages touched recently
> by the threads in the process)
> - Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
> virtual address space the process is using)
> NOTE: You can also add some main process for this server. E.X:
> sqlservr.exe
> process
> d. If the indication goes very smoothly, it indicates that the memory
> usage
> of these processes is stable. In opposition, the memory leak issue may
> exist.
> At the same time, on the SQL Server side, we can attempt to adjust the
> memory to see if it helps. Below are the detail steps:
> --
> a. Open the SQL Server Management Studio
> b. Right click on the SQL Server instance->choose Properties.
> c. Click on the Memory tab on the left panel, adjust the memory for your
> SQL Server.
> Have a nice day!
> Best regards,
> Adams Qu
> MCSE, MCDBA, MCTS
> Microsoft Online Support
> Microsoft Global Technical Support Center
> Get Secure! - www.microsoft.com/security
> =====================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> =====================================================> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> --
> | From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
> | References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
> <OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
> | Subject: Re: Excessive Page Faults?
> | Date: Thu, 11 Oct 2007 12:56:40 -0700
> | Lines: 1
> | Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain;
> | format=flowed;
> | charset="iso-8859-1";
> | reply-type=response
> | Content-Transfer-Encoding: 7bit
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | Importance: Normal
> | X-Newsreader: Microsoft Windows Live Mail 12.0.1365
> | X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
> | X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
> | X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
> | X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
> | Newsgroups:
> microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
> t.public.sqlserver.server
> | Path: TK2MSFTNGHUB02.phx.gbl
> | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
> microsoft.public.sqlserver.server:27713
> microsoft.public.exchange.admin:47476
> | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | Thanks for the link to the article. This does give some additional
> | information, however the problem in the article points more to high CPU
> | utilization rather than high number of page faults. Perhaps since this
> also
> | includes the MOM components, I should try posting in that forum as the
> | Forefront group is fairly new?
> |
> | Richard
> |
> | "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
> | news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
> | > This is not an exchange issue...
> | >
> | > But take a look at
> | > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
> | >
> | >
> | > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
> | > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
> | >>I am noticing that an many of my servers (Server 2003 EE SP2 R2), that
> the
> | >>MsMpEng.exe and MOMHost.exe processes are creating a significant
> number
> of
> | >>page faults.
> | >>
> | >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been up
> for
> | >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults on
> | >> MOMHost.
> | >>
> | >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
> been
> | >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M
> faults
> on
> | >> MOMHost.
> | >>
> | >> Keep in mind that the "M" with the number is million.
> | >>
> | >> We only recently started using Forefront on our servers, and this
> appears
> | >> to be a relatively new issue, and I would like to know if others have
> | >> been experiencing this as well.
> | >>
> | >> Richard
> | >
> | >
> |
>|||Dear Richard,
Thank you for posting back.
From your last post, we are glad to hear that the memory leak issue does
not exist after we verified manually on the server.
I also understand that another issue with timeouts to the database is
experienced. To help you resolve the issue more efficiently, I also would
like to share with you the following troubleshooting information. You can
try the following points before submitting the new thread.
Reference Information on the second issue
--
Based on my experience, the time-out connection issue can be concluded to
be caused by one of the following two factors commonly:
a. Caused by the network configuration (as you mentioned)
b. The second cause is that the SQL Server is running on a heavy load at
that time.
c. Application or hard driver factors.
Please use SQL Profiler and Perfmon to monitor your SQL Server to see what
happens if the connection timeout occurs.
I also would like to provide the following links for your reference:
HOW TO: Troubleshoot Application Performance with SQL Server
http://support.microsoft.com/kb/224587/en-us
Troubleshooting Performance Problems in SQL Server 2005
http://www.microsoft.com/technet/prodtechnol/sql/2005/tsprfprb.mspx
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/kb/224453/en-us
Hope above information is helpful. Once we see your new thread, we will be
glad to follow up and help you. If you have any other questions or
concerns, please do not hesitate to contact us. It is always my pleasure to
be of assistance.
Have a nice day!
Best regards,
Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support
Microsoft Global Technical Support Center
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.
--
| From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
<OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
<84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
<8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl>
| Subject: Re: Excessive Page Faults?
| Date: Wed, 31 Oct 2007 16:54:13 -0700
| Lines: 2
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=original
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| Importance: Normal
| X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| Message-ID: <#82$XlBHIHA.4768@.TK2MSFTNGP03.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: 209.242.152.130
| Path: TK2MSFTNGHUB02.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.server:29221
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| Sorry for the long delay in replying to this post. With the fires here is
| SoCal, things have been fairly hectic.
|
| Following your suggestions, I am verified that there is not a memory leak
| with regards to these processes. Therefore, I am left with the simple
| concern of the performance of Forefront on the Server platform. I will
raise
| this issue in the Forefront forums.
|
| I do have a recurring issue with timeouts to the database that I am
| troubleshooting, and it was this issue that prompted my looking at the
| paging performance. I have consistently believed that the cause if these
| timeouts is due to our network configuration, but for the sake of
| completeness, I have been verifying our SQL setup. I will post regarding
| those timeouts in another thread.
|
| Sophie Tan, if you see this, I wanted to thank you for your follow up
post
| as well. At this time, I would consider this thread closed.
|
| Richard Perry
|
|
| "Adams Qu [MSFT]" <v-adamqu@.online.microsoft.com> wrote in message
| news:8LjAyJKDIHA.4664@.TK2MSFTNGHUB02.phx.gbl...
| > Dear Richard,
| >
| > Thank you for posting here.
| >
| > I understand that the process MsMpEng.exe and MOMHost.exe processes are
| > creating a significant number of page faults on several servers.
| >
| > According to your description, it appears that the issue should not on
the
| > SQL Server side. Based on my experience, this behavior can occur due to
a
| > Memory Leak but sometimes it is normal for some processes. In order to
| > ensure the current status, I would like to confirm the following
| > information before going further:
| >
| > 1. Has the performance on these severs been affected due to this issue?
| >
| > 2. Please also use the Performance Monitor utility to monitor the
| > stability
| > of memory pages used by these two processes.
| >
| > To do so, please refer to the following steps:
| > --
| > a. Enter the Control Panel -> Administrative Tools -> Performance.
| > b. Select "Process" in the "Performance object" selection
| > c. Add the following counter for the two processes (MsMpEng.exe and
| > MOMHost.exe) in question:
| >
| > - Working Set (The Working Set is the set of memory pages touched
recently
| > by the threads in the process)
| > - Virtual Bytes (Virtual Bytes is the current size, in bytes, of the
| > virtual address space the process is using)
| >
| > NOTE: You can also add some main process for this server. E.X:
| > sqlservr.exe
| > process
| >
| > d. If the indication goes very smoothly, it indicates that the memory
| > usage
| > of these processes is stable. In opposition, the memory leak issue may
| > exist.
| >
| > At the same time, on the SQL Server side, we can attempt to adjust the
| > memory to see if it helps. Below are the detail steps:
| > --
| > a. Open the SQL Server Management Studio
| > b. Right click on the SQL Server instance->choose Properties.
| > c. Click on the Memory tab on the left panel, adjust the memory for your
| > SQL Server.
| >
| > Have a nice day!
| >
| > Best regards,
| >
| > Adams Qu
| > MCSE, MCDBA, MCTS
| > Microsoft Online Support
| >
| > Microsoft Global Technical Support Center
| >
| > Get Secure! - www.microsoft.com/security
| > =====================================================| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| > =====================================================| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| > --
| > | From: "Richard Perry" <RichardPerry@.newsgroup.nospam>
| > | References: <3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com>
| > <OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl>
| > | Subject: Re: Excessive Page Faults?
| > | Date: Thu, 11 Oct 2007 12:56:40 -0700
| > | Lines: 1
| > | Message-ID: <84D91F2A-E4A6-4933-B34B-72879DD28277@.microsoft.com>
| > | MIME-Version: 1.0
| > | Content-Type: text/plain;
| > | format=flowed;
| > | charset="iso-8859-1";
| > | reply-type=response
| > | Content-Transfer-Encoding: 7bit
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | Importance: Normal
| > | X-Newsreader: Microsoft Windows Live Mail 12.0.1365
| > | X-MimeOLE: Produced By Microsoft MimeOLE V12.0.1365
| > | X-MS-CommunityGroup-PostID: {84D91F2A-E4A6-4933-B34B-72879DD28277}
| > | X-MS-CommunityGroup-ThreadID: 3BFD2358-2798-4A29-B54A-EA90AD883293
| > | X-MS-CommunityGroup-ParentID: 47993B93-4F51-47EE-BE41-7DBD1C4B8AF7
| > | Newsgroups:
| >
microsoft.public.exchange.admin,microsoft.public.security.forefront,microsof
| > t.public.sqlserver.server
| > | Path: TK2MSFTNGHUB02.phx.gbl
| > | Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.security.forefront:71
| > microsoft.public.sqlserver.server:27713
| > microsoft.public.exchange.admin:47476
| > | NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| > | X-Tomcat-NG: microsoft.public.sqlserver.server
| > |
| > | Thanks for the link to the article. This does give some additional
| > | information, however the problem in the article points more to high
CPU
| > | utilization rather than high number of page faults. Perhaps since this
| > also
| > | includes the MOM components, I should try posting in that forum as the
| > | Forefront group is fairly new?
| > |
| > | Richard
| > |
| > | "Betelgeuse" <betelgeuse@.news.postalias> wrote in message
| > | news:OBAYHbCDIHA.3712@.TK2MSFTNGP02.phx.gbl...
| > | > This is not an exchange issue...
| > | >
| > | > But take a look at
| > | > http://geekswithblogs.net/Coleman/archive/2007/03/26/109906.aspx
| > | >
| > | >
| > | > "Richard Perry" <RichardPerry@.newsgroup.nospam> wrote in message
| > | > news:3BFD2358-2798-4A29-B54A-EA90AD883293@.microsoft.com...
| > | >>I am noticing that an many of my servers (Server 2003 EE SP2 R2),
that
| > the
| > | >>MsMpEng.exe and MOMHost.exe processes are creating a significant
| > number
| > of
| > | >>page faults.
| > | >>
| > | >> For example, on my SQL 2000 SP4 server with 4GB RAM which has been
up
| > for
| > | >> 19 days has nearly 19M page faults on MsMpEng and over 16M faults
on
| > | >> MOMHost.
| > | >>
| > | >> Another example is my Exchange 2003 SP2 with 4GB RAM which has also
| > been
| > | >> up for 19 days has nearly 182M faults on MsMpEng and nearly 16M
| > faults
| > on
| > | >> MOMHost.
| > | >>
| > | >> Keep in mind that the "M" with the number is million.
| > | >>
| > | >> We only recently started using Forefront on our servers, and this
| > appears
| > | >> to be a relatively new issue, and I would like to know if others
have
| > | >> been experiencing this as well.
| > | >>
| > | >> Richard
| > | >
| > | >
| > |
| >
|

Exception.Data[key]=value

Hi.

Is it possible to transmit custom data from SQL 2005 to the client via Exception.Data[key]=value?

The idea
I have a number of SPs that accept userId parameter which must be verified. I have written a simple CLR SP (check_userid_valid) which performs the verification of the userId parameter's value. If the parameter's value is considered invalid an exception is thrown. When the exception (System.Exception) is instantiated additional data is added via the Data property of the exception:

System.Exception ex = new System.Exception();
ex.Data["Source"] = "check_userid_valid"

So, any SP that calls the check_userid_valid with invalid userId is expected to "crash" and the exception with all the additional data is expected to be propagated back to the client which, in turn, could read the data.
Unfortunately, it seems that the ex.Data contains only entries put by the MS SQL 2005 server itself, eliminating the rest.

The question is: how can I supply additional exception data with the exception I do throw from my CLR code on the server-side, so that is can be consumed at the client-side.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CustomException()
{
try
{
//try code goes here
System.Exception firstEx = new System.Exception("first exception");
throw firstEx;
}
catch (System.Exception ex)
{

}
finally {
System.Exception secondEx = new System.Exception("check_userid_valid");
throw secondEx;
}
}
};

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

Exception while calling the Webservice from CLR

Hi,

I created a method in the webservice which will take productid as input parameter and return the product number, productname, and vendor account number and vendor name. I was able to run the web service successfully. And also created the assemblies and sp using these assembly.

At the final execution i am getting some security exception

The following is the exception I am getting….

CREATE PROCEDURE GetProductVendorDetails(@.ProductID int)

AS

EXTERNAL NAME GetProductVendorAssembly.StoredProcedures.CallWebService

GO

EXECUTE GetProductVendorDetails 2

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidOperationException: There is an error in XML document (1, 281). > System.Security.SecurityException: That assembly does not allow partially trusted callers.

System.Security.SecurityException:

at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read2_ProductVendorInfo(Boolean isNullable, Boolean checkType)

at Microsoft.Xml.Serialization.GeneratedAssembly.XmlSerializationReader1.Read9_Item()

at Microsoft.Xml.Serialization.GeneratedAssembly.ArrayOfObjectSerializer5.Deserialize(XmlSerializationReader reader)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

System.InvalidOperationException:

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle, XmlDeserializationEvents events)

at System.Xml.Serialization.XmlSerializer.Deserialize(XmlReader xmlReader, String encodingStyle)

at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall)

at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters)

at CLRWebServiceProject.LocalWebService.ProductVendorInfoService.GetProductVendorDetails(Int32 ProductID)

at StoredProcedures.CallWebService(Int32 ProductID)

.

My Web Service Method ….

[WebMethod]

private void GetProductDetails(int ProductID)

{

// String ProductVendorDetail="";

//Set the connection string for the database

string connectionstring = "Server=PC013584;Database=AdventureWorks;User=Raj;Password=password";

//Create Connection and open it

SqlConnection conn = new SqlConnection(connectionstring);

conn.Open();

//Create the command object

SqlCommand comm = new SqlCommand();

comm.Connection = conn;

comm.CommandText = "SELECT P.ProductID as ProductID,P.Name as ProductName,P.ProductNumber as ProductNumber,V.AccountNumber as VendorAccountNumber,V.Name VendorName"

+ " FROM Production.Product P "

+ " INNER JOIN Purchasing.ProductVendor PV ON (PV.ProductID = P.ProductID) "

+ " INNER JOIN Purchasing.Vendor V ON(V.VendorID = PV.VendorID) "

+ " WHERE P.ProductID =" + ProductID.ToString();

SqlDataReader thisReader = comm.ExecuteReader();

while (thisReader.Read())

{

//Console.WriteLine(myReader["Column1"].ToString());

//Console.WriteLine(myReader["Column2"].ToString());

pvinfo.ProductID = Int32.Parse(thisReader["ProductID"].ToString());

pvinfo.ProductName = thisReader["ProductName"].ToString();

pvinfo.ProductNumber = thisReader["ProductNumber"].ToString();

pvinfo.VendorAccountNumber = thisReader["VendorAccountNumber"].ToString();

pvinfo.VendorName = thisReader["VendorName"].ToString(); ;

}

thisReader.Close();

conn.Close();

}

[WebMethod]

public ProductVendorInfo GetProductVendorDetails(int ProductID)

{

GetProductDetails(ProductID);

ProductVendorInfo pvi = new ProductVendorInfo();

pvi.ProductID = pvinfo.ProductID;

pvi.ProductName = pvinfo.ProductName;

pvi.ProductNumber = pvinfo.ProductNumber;

pvi.VendorAccountNumber = pvinfo.VendorAccountNumber;

pvi.VendorName = pvinfo.VendorName;

return pvi;

}

My CLR Procedure code is as follows….

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void CallWebService(int ProductID)

{

// Put your code here

ProductVendorInfoService S = new ProductVendorInfoService();

S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

pvi = S.GetProductVendorDetails(ProductID);

String PN = pvi.ProductName;

String PNum = pvi.ProductNumber;

String VANum = pvi.VendorAccountNumber;

String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"

+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

};

Can you help what exactly this error relates/ pointing to? Am i doing any mistake while creating the procedure?

Thanks

Raj

Is your sgen:ed assembly strongly named, by any chance? If so, sign your clr assembly with the same key.

Actually, try and sign your sqlclr assmembly anyway.

Niels
|||

It is already have strong key name...

I added "Integrated Security=true" in the connection string then the security exception was solved..

Still i am getting the exception:

Msg 6522, Level 16, State 1, Procedure GetProductVendorDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductVendorDetails":

System.InvalidCastException: Unable to cast object of type 'System.Data.SqlTypes.SqlInt32' to type 'System.IConvertible'.

System.InvalidCastException:

at System.Convert.ToInt32(Object value)

at StoredProcedures.CallWebService(SqlInt32 ProductID)

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using CLRWebServiceProject.LocalWebService;

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void CallWebService(SqlInt32 ProductID)
{
// Put your code here

S = new ProductVendorInfoService();
S.UseDefaultCredentials = true;

ProductVendorInfo pvi = new ProductVendorInfo();

int ProdID = System.Convert.ToInt32(ProductID);

pvi = S.GetProductVendorDetails(ProdID);

String PN = pvi.ProductName;
String PNum = pvi.ProductNumber;
String VANum = pvi.VendorAccountNumber;
String VN = pvi.VendorName;

using (SqlConnection cn = new SqlConnection("context connection=true"))
{
string query = "INSERT INTO dbo.ProductVendorDetail(ProdcutID,ProductName,ProductNumber,VendorAcccountNumber,VendorName)"
+" VALUES ('"+ProductID+","+PN+","+PNum+","+VANum+","+VN+"')";

using (SqlCommand insertCommand = new SqlCommand(query, cn))
{
cn.Open();
insertCommand.ExecuteNonQuery();
cn.Close();
}
}

}

};

the exeception seems to be related to conversion..... :-(

|||In your call to Convert.ToInt32 you send in ProductId, which is of type SqlInt32. ToInt32 does not take SqlInt32. I don't really understand why you call ToInt32 in this scenario. Why don't you just do:

int ProdId = ProductId.Value;

All SqlTypes do have a Value property which gives you back the underlying CLR type. Just make sure that ProductId is not NULL before you do this.

Niels
|||

hi,

The following is the CLR code and i was able to compile and create the sp from the assembly

CREATE PROCEDURE GetProductSuppliersDetails(@.Product int)

AS

EXTERNAL NAME GetProductSupplierAssembly.StoredProcedures.GetSuppliers

GO

and on execution

EXECUTE GetProductSuppliersDetails 1

i am getting the following exception

Msg 6522, Level 16, State 1, Procedure GetProductSuppliersDetails, Line 0

A .NET Framework error occurred during execution of user-defined routine or aggregate "GetProductSuppliersDetails":

System.NullReferenceException: Object reference not set to an instance of an object.

System.NullReferenceException:

at StoredProcedures.GetSuppliers(Int32 ProductID)

.

Is there any thing wrong in the code ....

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using TestProject.ProductSupplier;

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service S = new Service();

S.UseDefaultCredentials = true;

DataSet ds = new DataSet();

ds = S.GetProductSupplier(ProductID);

DataTable dt = new DataTable();

dt = ds.Tables["ProductSuppliers"];

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

foreach (DataRow row in dt.Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProdcutID,ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('";

int cCount = 0;

foreach (DataColumn col in dt.Columns)

{

if (dt.Columns.Count < cCount)

{

query += row[col.ColumnName].ToString() + ",";

}

else

{

query += row[col.ColumnName].ToString() + "')";

}

cCount++;

}

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

};

Thanks in Advance...

|||Well, that error message is really hard to interpret, can't you debug into the CLR method and see where the exception happens? Alternatively, you could refactor the code into a console application and just check and see what happens.

I would look closer at where you assign the data table to a table from the dataset, are you sure there exists a table in the DataSet called "ProductSuppliers"?

Niels

|||

Issue solved . I got it corrected.

public static void GetSuppliers(int ProductID)

{

// Put your code here

Service objService = new Service();

objService.UseDefaultCredentials = true;

DataSet dsProdSupply = objService.GetProductSupplier(ProductID);

//using (SqlConnection cn = new SqlConnection("Server=PC013584;Database=NorthWing;User=Raj;Password=password;Integrated Security=SSPI"))

using (SqlConnection cn = new SqlConnection("context connection=true"))

{

if (dsProdSupply != null)

{

if (dsProdSupply.Tables[0] != null)

{

foreach (DataRow drProdSupply in dsProdSupply.Tables[0].Rows)

{

string query = "INSERT INTO dbo.ProductSupplier(ProductName,CompanyName,ContactName,ContactTitle,Address,City)"

+ " VALUES ('" + drProdSupply["ProductName"].ToString() + "','" + drProdSupply["CompanyName"].ToString() + "','" + drProdSupply["ContactName"].ToString() + "','" + drProdSupply["ContactTitle"].ToString() + "','" + drProdSupply["Address"].ToString() + "','" + drProdSupply["City"].ToString() + "')";

/*

int cCount = 0;

foreach (DataColumn dcProdSupply in dsProdSupply.Tables[0].Columns)

{

if (dsProdSupply.Tables[0].Columns.Count < cCount)

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + ",";

}

else

{

query += "'" + drProdSupply[dcProdSupply.ColumnName].ToString() + "')";

}

cCount++;

}* */

using (SqlCommand insertCommand = new SqlCommand(query, cn))

{

cn.Open();

// Console.WriteLine(row[col]);

insertCommand.ExecuteNonQuery();

cn.Close();

}

}

}

}

}

Thanks To Karthik Who helped me in correcting the issue...

excel source not reading newly/appended rows

I am trying to ETL from excel source to SQL table. When I created the package my excel had certain number of rows. Later if I add (append) few more rows in the spreadsheet ... the ssis package (also in excel source preview) not reading the newly added rows. Suprisingly it is reading new rows if they are inserted in between existing rows.

Any idea why ?

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.
|||

JayH wrote:

Just a random thought, but are there possibly any hidden rows between the original data and your appended data? Check to make sure the row numbers are contiguous.

No hidden rows... I m experiencing this with couple of packages having excel as source.

|||Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?|||

Ted Lee - MSFT wrote:

Does it work if you delete and recreate the Excel source and connection manager after adding the rows to the end?

Yes it works if I do that...

|||

My problem is solved. Under name of the excel sheet in excel source editor two items appear one with original name of the excel file and another one with $ sign appended to excel file name. If I select the one with $ sign it worked for me.

Any idea what both items mean?

Thanks

|||

See the following in Books Online for the Excel Source:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10is_0evalplan/html/e66349f3-b1b8-4763-89b7-7803541a4d62.htm

"In Excel, a worksheet or range is the equivalent of a table or view. The list of available tables in the Excel Source and Destination editors displays existing worksheets (identified by the $ sign appended to the worksheet name, such as Sheet1$) and named ranges (identified by the absence of the $ sign, such as MyRange). For more information, see the Usage Considerations section."

|||

Thanks Ted...

Excel rendering 65536 row limit?

I am experiencing a problem when we are rendering a large report in Excel
format, I get the following error thrown by the ExcelRenderer:
Number of rows in the excel sheet exceeded the limit of 65536 rows.
Has anyone seen this issue? I searched this forum and the SP2 docs.
Thanks,
MikeSilly question: does the report actually consist of more than 65536 rows?
Mike G.
"Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in message
news:641C0BE6-689C-42E8-BF13-724232C11278@.microsoft.com...
>I am experiencing a problem when we are rendering a large report in Excel
> format, I get the following error thrown by the ExcelRenderer:
> Number of rows in the excel sheet exceeded the limit of 65536 rows.
> Has anyone seen this issue? I searched this forum and the SP2 docs.
> Thanks,
> Mike|||Thats an Excel limitation - it may have been increased with 2003
"Mike G." wrote:
> Silly question: does the report actually consist of more than 65536 rows?
> Mike G.
> "Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in message
> news:641C0BE6-689C-42E8-BF13-724232C11278@.microsoft.com...
> >I am experiencing a problem when we are rendering a large report in Excel
> > format, I get the following error thrown by the ExcelRenderer:
> >
> > Number of rows in the excel sheet exceeded the limit of 65536 rows.
> >
> > Has anyone seen this issue? I searched this forum and the SP2 docs.
> >
> > Thanks,
> > Mike
>
>|||That is a limit in Excel, usually we have to have clients use a csv files
and load them into Access.
"Espo" <Espo@.discussions.microsoft.com> wrote in message
news:B29123AD-7272-41F9-8D21-5915F7AA57A0@.microsoft.com...
> Thats an Excel limitation - it may have been increased with 2003
> "Mike G." wrote:
> > Silly question: does the report actually consist of more than 65536
rows?
> >
> > Mike G.
> >
> > "Mike Sandwick" <MikeSandwick@.discussions.microsoft.com> wrote in
message
> > news:641C0BE6-689C-42E8-BF13-724232C11278@.microsoft.com...
> > >I am experiencing a problem when we are rendering a large report in
Excel
> > > format, I get the following error thrown by the ExcelRenderer:
> > >
> > > Number of rows in the excel sheet exceeded the limit of 65536 rows.
> > >
> > > Has anyone seen this issue? I searched this forum and the SP2 docs.
> > >
> > > Thanks,
> > > Mike
> >
> >
> >

Excel reader issues

Hi,

I need to load data from excel files which will be provided by a number (around 100 monthly) of external suppliers, so we don't get 100% control over the files themselves.

What my solution involves is copying the excel file to a common name (e.g. supplierExcel.xls), turning this into a pipe delimited txt and then loading the txt. I had trouble switching files when trying to load directly from excel.

All these files should arrive in the same format of 36 fields and of course in the right order; there will be rejections if they fail.

I've come across a problem extracting the data from excel where I'm getting 'the value could not be converted because of a potential loss of data' on field 1. It only happens on excel files where there is a quote mark as the first character and I have loaded other files quite happily without the quotemark.

Has anyone seen this before? Is this a known issue and how can I get around it, without recourse to manually changing the individual files?

Thanks

nathan

Hi,

It is difficult to answer, you should be more specific on a couple of things.

- Why cannot you load direct from Excel. this could be the only fix you need, You could use the File System task.

- How do you convert the Excel to text

- Do you use table load or SQL Command

- Do you need the quote when it is the initial char? In Excel it means you want to force the cell content to be text.

If you do not need the quote, strip it from the file.

declare @.sometext as varchar(255)

set @.sometext = '''There is an initial '' in this test string'

select substring(@.sometext, patindex('''%',@.sometext)+1,255)

if you need te quote, try to replace it by 3 quotes or make sure the entry is not too long, May be you could do this

declare @.sometext as varchar(255)

set @.sometext = '''There is an initial '' in this test string'

select case left(@.sometext,1) when char(39) then char(39) + char(39) + @.sometext else @.sometext end

.Philippe