Monday, March 26, 2012
How to use two aggregate functions in RS 2005
table.
Basically it is (Sum of (Sum of X)).
How do we get around this?
Please let me know.
ThanksWhat might work for you is to add a calculated field. On the field list
where you drag and drop from just do a right mouse click, add a field (or
something like that). Then you can do a sum of that.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>I am not able to use two aggregate functions to display a value in a
> table.
> Basically it is (Sum of (Sum of X)).
> How do we get around this?
> Please let me know.
> Thanks
>|||Hi Bruce,
Thank you very much for your reply.
So you mean to say that I need to add that field as a calculated field
instead of a normal field?
Thanks
Anup
Bruce L-C [MVP] wrote:
> What might work for you is to add a calculated field. On the field list
> where you drag and drop from just do a right mouse click, add a field (or
> something like that). Then you can do a sum of that.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >I am not able to use two aggregate functions to display a value in a
> > table.
> > Basically it is (Sum of (Sum of X)).
> >
> > How do we get around this?
> > Please let me know.
> >
> > Thanks
> >|||If you add a calculated field to your dataset (this is from the list of
field, I am not talking about a sql statement here) you can have the
calculated field be an aggregate. Then you can aggregate the calculated
field, getting around your problem. I do this to make things simplier too.
Try it as I mentioned and see if it makes sense in your situation. Adding a
field manually to the field list returned by the query is not very
discoverable.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Anup" <anupkm@.gmail.com> wrote in message
news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> Hi Bruce,
> Thank you very much for your reply.
> So you mean to say that I need to add that field as a calculated field
> instead of a normal field?
> Thanks
> Anup
> Bruce L-C [MVP] wrote:
>> What might work for you is to add a calculated field. On the field list
>> where you drag and drop from just do a right mouse click, add a field (or
>> something like that). Then you can do a sum of that.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Anup" <anupkm@.gmail.com> wrote in message
>> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
>> >I am not able to use two aggregate functions to display a value in a
>> > table.
>> > Basically it is (Sum of (Sum of X)).
>> >
>> > How do we get around this?
>> > Please let me know.
>> >
>> > Thanks
>> >
>|||Thanks Bruce. Here is my situation:
I need to display the "Double Sum" Value in the footer of a table.The
details of this table is in a different level of grouping and footer is
at a different level of grouping.
Thanks
Anup
Bruce L-C [MVP] wrote:
> If you add a calculated field to your dataset (this is from the list of
> field, I am not talking about a sql statement here) you can have the
> calculated field be an aggregate. Then you can aggregate the calculated
> field, getting around your problem. I do this to make things simplier too.
> Try it as I mentioned and see if it makes sense in your situation. Adding a
> field manually to the field list returned by the query is not very
> discoverable.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Anup" <anupkm@.gmail.com> wrote in message
> news:1165432174.432662.162930@.f1g2000cwa.googlegroups.com...
> > Hi Bruce,
> >
> > Thank you very much for your reply.
> >
> > So you mean to say that I need to add that field as a calculated field
> > instead of a normal field?
> >
> > Thanks
> > Anup
> >
> > Bruce L-C [MVP] wrote:
> >> What might work for you is to add a calculated field. On the field list
> >> where you drag and drop from just do a right mouse click, add a field (or
> >> something like that). Then you can do a sum of that.
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Anup" <anupkm@.gmail.com> wrote in message
> >> news:1165428357.174972.171260@.l12g2000cwl.googlegroups.com...
> >> >I am not able to use two aggregate functions to display a value in a
> >> > table.
> >> > Basically it is (Sum of (Sum of X)).
> >> >
> >> > How do we get around this?
> >> > Please let me know.
> >> >
> >> > Thanks
> >> >
> >
Monday, March 12, 2012
How to use ODBC SQLSetConnectAttr from C# ?
Hi,
I'm having problems dealing with some large datatypes in a C# application using odbc.
Basically certain datatypes, such as db2 CLOB, are not recognized as 'valid' by the odbc driver.
It seems the fix is to use the SQLSetConnectAttr function using the appropriate key/value pair to 'map' those to datatypes known to ODBC but I'm not sure how to use it in C# code. The template seems to be more an unmanaged C type API with handles being passed etc...
SQLRETURN SQLSetConnectAttr (
SQLHDBC ConnectionHandle, /* hdbc */
SQLINTEGER Attribute, /* fOption */
SQLPOINTER ValuePtr, /* pvParam */
SQLINTEGER StringLength); /* fStrLen */
I tried creating an OdbcCommand with that function as the command text but without success.
Could somebody drop a line with a C# example of how to do that ?
Thank you,
MM
I don't think you can get to ODBC connection attributes if you're using the ODBC managed provider.
Have you considered using the LongDataCompat keyword in the db2cli.ini config file? You should be able to achieve this via the drivers setup dialog via the ODBC control panel when you configur the DSN rather than editing the file directly. It maps to SQL_ATTR_LONGDATA_COMPAT which is the attribute you're probably trying to set programatically.
|||Yes,
I did try that, actually followed the instructions of the ibm db2 boulder site and edited the file directly. Unfortunately it didn't work.
Changing the parameter in the DSN does work, but :
1- You have to do it for each DSN (and I'm dealing with numerous DBs)
2- The piece of code I wanted to use it for is to be deployed on machines I do not control and it would be better to let the app handle the data type rather than having the user fiddle with their DSN config.
Any suggestion as to how to do that ?
Thanks,
MM
|||The next thing to try would be setting the attribute via a connection string keyword. The ODBC managed provider will pass this though to the driver, so this is a fairly safe and clean approach where the driver provides an appropriate keyword.
Failing this - and only if you're absolutely desperate - you might try reflection with the ODBC managed provider to try to gain access to the underlying ODBC connection handle. This would be completely unsupported (if it worked at all) and there is no guarantee that it wouldn't change in the future.
|||Chris,
I have tried passing the info as an extra connection attribute at the end of the connection string, to no avail.
But I was really poking around for a solution, so I'm not sure I used a correct syntax. Also I'm not sure if the placement matters (ie after the password...). I just stuck it at the end.
Would you know what the correct syntax is ?
Thanks,
MM
|||It should be 'LongDataCompat=1' and can appear anywhere in the connection string, so the end would be fine. Connection string items are seperated by semi-colons. Is this what you tried?|||Chris,
It worked, you're a genius!
I had tried adding ';SQL_ATTR_LONGDATA_COMPAT=SQL_LD_COMPAT_YES'
I thought LONGDATACOMPAT=1 was to be used in the db2cli.ini file and SQL_ATTR... = SQL_LD... was the key/value pair to be used in the connection string based on the info off the IBM DB2 website.
I still don't understand why it's not but at least it's working!
Thank you,
MM
|||Hi MM,
My reading was that you can use ini file entries in connection strings. If there's a conflict the connection string wins.
I'm pleased to hear you app is working.
How to use ODBC SQLSetConnectAttr from C# ?
Hi,
I'm having problems dealing with some large datatypes in a C# application using odbc.
Basically certain datatypes, such as db2 CLOB, are not recognized as 'valid' by the odbc driver.
It seems the fix is to use the SQLSetConnectAttr function using the appropriate key/value pair to 'map' those to datatypes known to ODBC but I'm not sure how to use it in C# code. The template seems to be more an unmanaged C type API with handles being passed etc...
SQLRETURN SQLSetConnectAttr (
SQLHDBC ConnectionHandle, /* hdbc */
SQLINTEGER Attribute, /* fOption */
SQLPOINTER ValuePtr, /* pvParam */
SQLINTEGER StringLength); /* fStrLen */
I tried creating an OdbcCommand with that function as the command text but without success.
Could somebody drop a line with a C# example of how to do that ?
Thank you,
MM
I don't think you can get to ODBC connection attributes if you're using the ODBC managed provider.
Have you considered using the LongDataCompat keyword in the db2cli.ini config file? You should be able to achieve this via the drivers setup dialog via the ODBC control panel when you configur the DSN rather than editing the file directly. It maps to SQL_ATTR_LONGDATA_COMPAT which is the attribute you're probably trying to set programatically.
|||Yes,
I did try that, actually followed the instructions of the ibm db2 boulder site and edited the file directly. Unfortunately it didn't work.
Changing the parameter in the DSN does work, but :
1- You have to do it for each DSN (and I'm dealing with numerous DBs)
2- The piece of code I wanted to use it for is to be deployed on machines I do not control and it would be better to let the app handle the data type rather than having the user fiddle with their DSN config.
Any suggestion as to how to do that ?
Thanks,
MM
|||The next thing to try would be setting the attribute via a connection string keyword. The ODBC managed provider will pass this though to the driver, so this is a fairly safe and clean approach where the driver provides an appropriate keyword.
Failing this - and only if you're absolutely desperate - you might try reflection with the ODBC managed provider to try to gain access to the underlying ODBC connection handle. This would be completely unsupported (if it worked at all) and there is no guarantee that it wouldn't change in the future.
|||Chris,
I have tried passing the info as an extra connection attribute at the end of the connection string, to no avail.
But I was really poking around for a solution, so I'm not sure I used a correct syntax. Also I'm not sure if the placement matters (ie after the password...). I just stuck it at the end.
Would you know what the correct syntax is ?
Thanks,
MM
|||It should be 'LongDataCompat=1' and can appear anywhere in the connection string, so the end would be fine. Connection string items are seperated by semi-colons. Is this what you tried?|||Chris,
It worked, you're a genius!
I had tried adding ';SQL_ATTR_LONGDATA_COMPAT=SQL_LD_COMPAT_YES'
I thought LONGDATACOMPAT=1 was to be used in the db2cli.ini file and SQL_ATTR... = SQL_LD... was the key/value pair to be used in the connection string based on the info off the IBM DB2 website.
I still don't understand why it's not but at least it's working!
Thank you,
MM
|||Hi MM,
My reading was that you can use ini file entries in connection strings. If there's a conflict the connection string wins.
I'm pleased to hear you app is working.
Friday, February 24, 2012
How to use CDO to send mail with attachment
sp_send_cdosysmail.
This work fine using our mailrelay, but there seems to be no clear way to
add an attachment.
I've tried a simple:
EXEC @.hr = sp_OAMethod @.iMsg, 'AttachFile', NULL, @.Attachment
but that just causes an unknown name error.
I've also tried creating an attachment object but I can't seems to assciate
it with the original cdo.message:
EXEC @.hr = sp_OASetProperty @.iMsg, 'MailFormat', @.Body
EXEC @.hr = sp_OACreate 'CDO.Attachment', @.iATT OUT
EXEC @.hr = sp_OASetProperty @.iATT, 'Position', 0
EXEC @.hr = sp_OASetProperty @.iATT, 'Type', @.CdoFileData
EXEC @.hr = sp_OASetProperty @.iATT, 'Name', @.Attachment
EXEC @.hr = sp_OAMethod @.iMsg, 'CDO.Message.Attachment.Update', null,@.iATT
I've tried variations on creation of the original object and methods, but I
basically just guessing!
I've read somewhere that the file has to be accessable as a http path. This
is purely a SQL 2000 server, no extras like IIS or any form of exchange
client.
Has anyone sent attachments using SQL server and CDO? Can you please offer
some assisance.
Thanks in advance.Typically, I've found out what I need to know about 30mins after this post.
Why doesn't it happen 30 minutes after you first start looking!
Ha|||Hi, Phil
Would you mind sharing your idea about 'CDO sending mail with attachment'?
1. What is the syntax of CDO with attachment?
2. How to use the result of query as attachment?
Thanks a lot!
Tianya
"Phil_Edney" wrote:
> I have a stored proc thats basically a copy of the script from MS to creat
e
> sp_send_cdosysmail.
> This work fine using our mailrelay, but there seems to be no clear way to
> add an attachment.
> I've tried a simple:
> EXEC @.hr = sp_OAMethod @.iMsg, 'AttachFile', NULL, @.Attachment
> but that just causes an unknown name error.
> I've also tried creating an attachment object but I can't seems to assciat
e
> it with the original cdo.message:
> EXEC @.hr = sp_OASetProperty @.iMsg, 'MailFormat', @.Body
> EXEC @.hr = sp_OACreate 'CDO.Attachment', @.iATT OUT
> EXEC @.hr = sp_OASetProperty @.iATT, 'Position', 0
> EXEC @.hr = sp_OASetProperty @.iATT, 'Type', @.CdoFileData
> EXEC @.hr = sp_OASetProperty @.iATT, 'Name', @.Attachment
> EXEC @.hr = sp_OAMethod @.iMsg, 'CDO.Message.Attachment.Update', null,@.iA
TT
> I've tried variations on creation of the original object and methods, but
I
> basically just guessing!
> I've read somewhere that the file has to be accessable as a http path. Thi
s
> is purely a SQL 2000 server, no extras like IIS or any form of exchange
> client.
> Has anyone sent attachments using SQL server and CDO? Can you please offer
> some assisance.
> Thanks in advance.|||I found a solution at the following web site.
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=20649
"Tianya" wrote:
> Hi, Phil
> Would you mind sharing your idea about 'CDO sending mail with attachment'?
> 1. What is the syntax of CDO with attachment?
> 2. How to use the result of query as attachment?
> Thanks a lot!
> Tianya
>
> "Phil_Edney" wrote:
>