I am trying to remove a table from a Publication because I want to modify it.
I am going to the Articles tab in the Publication Properties dialog.
However, the checkbox is disabled, probably because the snapshot has run.
How can I modify the table or remove it from the Publication so that it can
be modified?
Environment:
Win2k3
Sql Server 2000
Merge Replication with ce devices
try sp_dropmergearticle
"Thirsh" <Thirsh@.discussions.microsoft.com> wrote in message
news:32274340-B26D-4534-A2F3-FBF6A9B49989@.microsoft.com...
>I am trying to remove a table from a Publication because I want to modify
>it.
> I am going to the Articles tab in the Publication Properties dialog.
> However, the checkbox is disabled, probably because the snapshot has run.
> How can I modify the table or remove it from the Publication so that it
> can
> be modified?
> Environment:
> Win2k3
> Sql Server 2000
> Merge Replication with ce devices
|||I gave it a try. Here's the result from the query analyzer:
Cannot drop article 'AppRoles' from publication 'TB_Tracker' because its
snapshot has been run and this publication could have active subscriptions.
"Hilary Cotter" wrote:
> try sp_dropmergearticle
> "Thirsh" <Thirsh@.discussions.microsoft.com> wrote in message
> news:32274340-B26D-4534-A2F3-FBF6A9B49989@.microsoft.com...
>
>
Showing posts with label table. Show all posts
Showing posts with label table. Show all posts
Thursday, March 29, 2012
Sunday, March 25, 2012
Cannot reference derived table from a derived table in a subquery?
The follow generates the error:
Server: Msg 207, Level 16, State 3, Line 19
Invalid column name 'UserID'.
The inner derived table ("dt") cannot reference the outer derived table
("ActiveUsers") ?
Nevermind the contrived example - it is only serving to illustrate the
failing. The real situation is entirely more complex.
IF OBJECT_ID('tempdb..#Users') IS NOT NULL
DROP TABLE #Users
IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
DROP TABLE #Logins
CREATE TABLE #Users (
UserID int,
Name varchar(50),
IsActive tinyint)
CREATE TABLE #Logins (
UserID int,
LoginDate datetime,
WasSecured tinyint)
SELECT
ActiveUsers.*,
( SELECT MAX(LoginDate)
FROM (
SELECT * FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) dt
) AS LastSecuredLogin
FROM (
SELECT *
FROM #Users
WHERE IsActive <> 0
) ActiveUsers
DROP TABLE #Users
DROP TABLE #LoginsTry,
SELECT
ActiveUsers.*,
l.dt AS LastSecuredLogin
FROM
#Users as ActiveUsers
left join
(
SELECT
#Logins.UserID,
MAX(LoginDate) as dt
FROM
#Logins
WHERE
#Logins.WasSecured <> 0
group by
#Logins.UserID
) as l
on l.UserID = ActiveUsers.UserID
WHERE
ActiveUsers.IsActive <> 0
go
AMB
"Ian Boyd" wrote:
> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||Right, in your correlated subquery, you have a derived table that references
something outside of the scope of the derived table. Basically you have to
think of derived tables as views. They cannot reference anything from the
current query.
Maybe I am being a bit of a thicky, but I don't see why:
( SELECT MAX(LoginDate)
FROM ( SELECT *
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0 ) dt
) AS LastSecuredLogin
cannot be rewritten as:
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
The only reason to do what you were doing (I think) is if you were going to
do an aggregate on an aggregate. The where clause and the groupings are
exclusive of one another, so it should work. Am I missing something?
Full Query:
SELECT
ActiveUsers.*,
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
FROM ( SELECT *
FROM #Users
WHERE IsActive <> 0 ) as ActiveUsers
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:u462TqHCFHA.3740@.TK2MSFTNGP09.phx.gbl...
> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||> Maybe I am being a bit of a thicky, but I don't see why:
Becuase this wasn't my situation. The two tables is a contrived example -
boiled down to still cause the problem.
The "inner" derived table query's 3 tables unioned to 3 more tables.
The "outer" derived table query 3 more separate tables.|||Ah, I thought you weren't going to give us a contrived example :) I see.
Anyhow, you cannot use derived tables like that. You might look at user
defined functions if Alejandro's suggestion doesn't work. You can do a lot
of
stuff in there. They work very well in the SELECT clause. Or post
your entire query and who knows, someone may have done the same thing in the
past.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23tnVDIICFHA.3740@.TK2MSFTNGP09.phx.gbl...
> Becuase this wasn't my situation. The two tables is a contrived example -
> boiled down to still cause the problem.
> The "inner" derived table query's 3 tables unioned to 3 more tables.
> The "outer" derived table query 3 more separate tables.
>
>|||The query has been restructured; and it eliminates the reference from one
derived table to the other.
> Or post your entire query and who knows, someone may have done the same
> thing in the past.
Absoutly not. Nobody has ever done anything like it. The query is 3 screens
long.
And i'm not going to be posting DDL, sample inserts, or explanations.
Or the query :)|||> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
Well, not sure how long a screen is, but I am sure you are unlikely to be
the first person to do "anything like it."
> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
Then good luck, can't help you out if we can't see what you are doing. :)
Either way, your derived table must be self contained.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:Oa3IGfICFHA.3644@.TK2MSFTNGP15.phx.gbl...
> The query has been restructured; and it eliminates the reference from one
> derived table to the other.
>
> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
>sql
Server: Msg 207, Level 16, State 3, Line 19
Invalid column name 'UserID'.
The inner derived table ("dt") cannot reference the outer derived table
("ActiveUsers") ?
Nevermind the contrived example - it is only serving to illustrate the
failing. The real situation is entirely more complex.
IF OBJECT_ID('tempdb..#Users') IS NOT NULL
DROP TABLE #Users
IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
DROP TABLE #Logins
CREATE TABLE #Users (
UserID int,
Name varchar(50),
IsActive tinyint)
CREATE TABLE #Logins (
UserID int,
LoginDate datetime,
WasSecured tinyint)
SELECT
ActiveUsers.*,
( SELECT MAX(LoginDate)
FROM (
SELECT * FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) dt
) AS LastSecuredLogin
FROM (
SELECT *
FROM #Users
WHERE IsActive <> 0
) ActiveUsers
DROP TABLE #Users
DROP TABLE #LoginsTry,
SELECT
ActiveUsers.*,
l.dt AS LastSecuredLogin
FROM
#Users as ActiveUsers
left join
(
SELECT
#Logins.UserID,
MAX(LoginDate) as dt
FROM
#Logins
WHERE
#Logins.WasSecured <> 0
group by
#Logins.UserID
) as l
on l.UserID = ActiveUsers.UserID
WHERE
ActiveUsers.IsActive <> 0
go
AMB
"Ian Boyd" wrote:
> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||Right, in your correlated subquery, you have a derived table that references
something outside of the scope of the derived table. Basically you have to
think of derived tables as views. They cannot reference anything from the
current query.
Maybe I am being a bit of a thicky, but I don't see why:
( SELECT MAX(LoginDate)
FROM ( SELECT *
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0 ) dt
) AS LastSecuredLogin
cannot be rewritten as:
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
The only reason to do what you were doing (I think) is if you were going to
do an aggregate on an aggregate. The where clause and the groupings are
exclusive of one another, so it should work. Am I missing something?
Full Query:
SELECT
ActiveUsers.*,
( SELECT max(loginDate)
FROM #Logins
WHERE #Logins.UserID = ActiveUsers.UserID
AND #Logins.WasSecured <> 0
) AS LastSecuredLogin
FROM ( SELECT *
FROM #Users
WHERE IsActive <> 0 ) as ActiveUsers
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:u462TqHCFHA.3740@.TK2MSFTNGP09.phx.gbl...
> The follow generates the error:
> Server: Msg 207, Level 16, State 3, Line 19
> Invalid column name 'UserID'.
> The inner derived table ("dt") cannot reference the outer derived table
> ("ActiveUsers") ?
> Nevermind the contrived example - it is only serving to illustrate the
> failing. The real situation is entirely more complex.
>
> IF OBJECT_ID('tempdb..#Users') IS NOT NULL
> DROP TABLE #Users
> IF OBJECT_ID('tempdb..#Logins') IS NOT NULL
> DROP TABLE #Logins
> CREATE TABLE #Users (
> UserID int,
> Name varchar(50),
> IsActive tinyint)
> CREATE TABLE #Logins (
> UserID int,
> LoginDate datetime,
> WasSecured tinyint)
> SELECT
> ActiveUsers.*,
> ( SELECT MAX(LoginDate)
> FROM (
> SELECT * FROM #Logins
> WHERE #Logins.UserID = ActiveUsers.UserID
> AND #Logins.WasSecured <> 0
> ) dt
> ) AS LastSecuredLogin
> FROM (
> SELECT *
> FROM #Users
> WHERE IsActive <> 0
> ) ActiveUsers
> DROP TABLE #Users
> DROP TABLE #Logins
>
>|||> Maybe I am being a bit of a thicky, but I don't see why:
Becuase this wasn't my situation. The two tables is a contrived example -
boiled down to still cause the problem.
The "inner" derived table query's 3 tables unioned to 3 more tables.
The "outer" derived table query 3 more separate tables.|||Ah, I thought you weren't going to give us a contrived example :) I see.
Anyhow, you cannot use derived tables like that. You might look at user
defined functions if Alejandro's suggestion doesn't work. You can do a lot
of
stuff in there. They work very well in the SELECT clause. Or postyour entire query and who knows, someone may have done the same thing in the
past.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:%23tnVDIICFHA.3740@.TK2MSFTNGP09.phx.gbl...
> Becuase this wasn't my situation. The two tables is a contrived example -
> boiled down to still cause the problem.
> The "inner" derived table query's 3 tables unioned to 3 more tables.
> The "outer" derived table query 3 more separate tables.
>
>|||The query has been restructured; and it eliminates the reference from one
derived table to the other.
> Or post your entire query and who knows, someone may have done the same
> thing in the past.
Absoutly not. Nobody has ever done anything like it. The query is 3 screens
long.
And i'm not going to be posting DDL, sample inserts, or explanations.
Or the query :)|||> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
Well, not sure how long a screen is, but I am sure you are unlikely to be
the first person to do "anything like it."
> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
Then good luck, can't help you out if we can't see what you are doing. :)
Either way, your derived table must be self contained.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ian Boyd" <ian.msnews010@.avatopia.com> wrote in message
news:Oa3IGfICFHA.3644@.TK2MSFTNGP15.phx.gbl...
> The query has been restructured; and it eliminates the reference from one
> derived table to the other.
>
> Absoutly not. Nobody has ever done anything like it. The query is 3
> screens long.
> And i'm not going to be posting DDL, sample inserts, or explanations.
> Or the query :)
>sql
Cannot Reference and SUM fields from Table Group in Footer Expression!
I'm gonna try this again, Microsoft, Please help!
There has to be a solution for this!!!! How can I get around the limitation in SSRS 2005 of being able to SUM on a Table's Group field from my group footer expression!
Keep in mind, I'm talking about a table here, not the global Report Footer, a Table object's footer!
It's driving me nuts
My table footer GrossGoal_gt 's field expression (non of these work and throw the error below):
=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")
neither does this:
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
neither does this:
=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:
Aggregate functions can be used only on report items contained in page headers and footers.
or this:
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
The field it's referencing's (textbox GrossGoal1) value is:
=((Fields!FeeGoal.Value) / Fields!FeeSchedule.Value) * 100
Error:
[rsFieldReference] The Value expression for the textbox ‘GrossGoal_gt’ refers to the field ‘GrossGoal1’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
and
[rsInvalidAggregateScope] The Value expression for the textbox ‘GrossGoal_gt’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
I did that! I set it to my Group name of my table!!!! Am I doing this wrong?
http://www.eggheadcafe.com/forums/ForumPost.asp?ID=59694&INTID=9
I am having to produce a stupid work around which is create a new dataset, tie it to a stored proc which then does some crazy calculations which is a headache in SQL which sums up my column's total using SQL in the stored proc instead of Reporting Services being able to reference my stupid Group field in the footer in order to sum for a grand total in the footer for that group column.
Related threads I have posted:
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/936792
Here's are other people with the same problem but no answers out there from my searches!
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882
http://www.sqlblogs.com/top/ng/group~24/~131100~__Scope-problem-with-SUM-on-group-footer/index.aspx
http://www.sqlblogs.com/top/ng/group~24/~130065~__MVPs-and-MS-support--Where-are-you~~~-on-dificults-questions/index.aspx
http://www.developmentnow.com/g/115_2005_2_0_0_455511/GroupingSum-Question.htm
http://www.sqlblogs.com/top/ng/group~24/~129523~__RunningValue-Error/index.aspx
Why doesn't this work MS ?
Scope
Because you may have multiple groups defined, you can specify the scope an aggregate function is to use. Scopes are names that you can pass to aggregate functions to indicate how you want to calculate the aggregates. For example, for tables that have more than one group defined, you may want to refer to an outergroup from an innergroup header row. The Nothing scope tells a function to use the outermost scope. For a simple table data region with no grouping, there is only one scope.
To identify scopes that are valid for a report item grouping, select the item, open the Properties dialog box, click the Groups tab, and view the list of group labels that can be used to identify scopes.
Why am I putting this in big letters? Because I'm about to pull my hair out!!!!
Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:
Report Functions
Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).
The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:
Copy Code
=Sum(Fields!LineTotal.Value, "Order")Based on your previous posts, the problem seems to be that each detail row of the table has an amount (not sure which data set field: maybe Fields!FeeGoal.Value?) and a percent (maybe Fields!FeeSchedule.Value?). Then there is a derived amount expression: "=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100" in a text box (GrossGoal1). What if, instead, you add a Calculated Field like GrossGoal to your data set, defined by the same expression:"=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100"? In that case, the detailed row amount can simply be: "=Fields!GrossGoal.Value", and both the group and footer expressions can be: "=Sum(Fields!GrossGoal.Value)".|||
Deepak, I've definitely tried that work around with little success if any. Most of the time, Visual Studio crashes when trying to SUM on a calculated Dataset field that is also very complex in itself!
Also Proj Fee (what I am referring to above for an example of what I'm currently stuck on)is not just a single value, it is a complicated calculation that includes many in it's expression. That's why this report is so crazy and why I really need to be able to bypass all these workarounds to be able to direcly reference fields from the footer and use aggregate functions on those fields.
As my report goes from right to left, the fields are not jus straight values, they have a ton of combined fields and calculatiosn within each field so each field really builds on the other...keep in mind this used to be a complex Excel Spreadsheet that they wanted me to automate!
the fact is, myself and others see this as a huge problem and limitatio that is keeping us from delivering highly complicated reporting via Reporting Services 2005. We do not want to create wacky ways around this....if that's the case, we might as well go back to Crystal at this point ! Yes, SSRS is great but this limitation is HUGE!
Here's Project Fee...yes, it's a nightmare and you'll see now why you can't just assume this work around or that when you get this complicated in your reports; We need to be able to SUM Group Header fields (not detail) in our Footer. I am not using the detail section of my Group, I don't need it.
ProjFee Expression is:
=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) + (SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)
You try putting this stripped down as a new calculated field in your dataset and then try doing a SUM on this in the footer of your table and watch Visual Studio Crash!!! It's loads of fun
|||
Here is my report file....open it, take a look at the last report at the bottom. The ProjFee in Footer..I want this to sum up my ProjFee Group Column:
http://photopizzaz.biz/myreport.zip
I've also tried these combinations using straight formulas (changing the SUM around in the past has worked so far up until now) with no luck...all the totals come out wrong:
http://www.photopizzaz.biz/calcs_tried_in_projfee_footer_expression.doc
What I'm trying to achieve like eveyrone else is to have success putting something like these into my ProjFee Footer textbox expression:
=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")
Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:
Error: Aggregate functions can be used only on report items contained in page headers and footers.
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
|||If anyone needs test data to test my report, I'd be happy to provide it so I can figure out this problem, just let me know. It will be non-confidential test data that will work.|||You may want to try using a custom function and a global variable to store a running total (sort of a SUMIF(value, groupvalue)) that you would create in the code window.
See http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Actually all of your complex calculations might be simplified by encapsulating in vb functions.
Here's more from Teo's book.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
|||Yes, but Andrew, I need the SUM in my footer to be summing up the group totals in my group field column...an outside function isn't gonna help me here because Commission % has to be used in the group calculation, I can't use Commission % in my GT because it's not possible to sum a bunch of % then do a calcuation like this.
Also, I was told that the group footer takes in the whole dataaset? not just the group totals.
Anyway, I think this is crazy first of all that I haven't had an MS staff reply to this problem, and second the documentation says we should be able to reference a groupname as the scope in a field expression from a table footer yet their errors tell a different story. Furthermore, there are at least from my searches, no examples of referencing a groupname or other names other than a dataset in BOL for doing what I want but the error says you should be able to. Where's the documentation on this scope? I only saw a brief comment and the most simplest example in BOL which isn't really helping anyone.
Can someone from MS please comment who is in the SSRS 2005 team!! A lot of use are wondering what the deal is here and have for quite some time.
|||Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:
Report Functions
Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).
The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:
Copy Code
=Sum(Fields!LineTotal.Value, "Order")|||
You should check that whether your sql or view have the column named "GrossGoal1".
|||
Sorry for being rude but I just said 1000 times, it's not a field from my dataset! It's a named textbox in my Report table's group. If you right-click a field in your group in your report, you have to name it something...i.e. textbox50 to something lik GROSSGOL
I want to do a SUM on that column textbox that resides in my table's group...not the Dataset!
Everyone keeps thinking I'm talking about a Dataset field, no, it's a Table Group Field!!
Create a table in SSRS 2005 OK. Then, create a group in that table. Ok, then add some fields to that group Header in your table. Rename one of them to GrossGoal by going to the properties of one of your table's textboxes (which contain expressions like referencing dataset fields!). Ok, then in your Table footer, try to do a SUM(name of the textbox in your group that you just renamed)
that's what I'm trying to do! Please Download and Look at my freakin .rdl that I provided, don't just guess what I'm trying to do here, I've provided probably 10x the required information! If you look at my .rdl, right click on the fields and see their properties, then look at the expression for the footer field! It's trying to reference the field above it which is a group field!
and one more time, because of the fact that I'm dealing with a commission %, simply pasting the same expression from my group to my footer will not work!! trust me...this is a very complex calculation that willl not come out right going that route like all the books tell you to do!
|||Looks like there is a confusion on the scope argument in aggregate functions. What the scope argument tells RS is to summarize all detail data in the specified scope, not summarizing all the aggregated values on these scopes.
For example, if you have a table (with table footer) and a table group. (looks like the site that host your report is currently down, so I can't look at your particular case). The hiearchy of the report looks like:
Report/DataSet
Table (table header/footer rows)
TableGroup (tablegroup header/footer rows)
In the TableGroup (group header/footer), you can define an aggregate with scope 1) none (the default is the current scope - TableGroup) or 2) TableGroup or 3) Table or 4) Nothing (the outermost scope, meaning the entire dataset, essentially the same as the table scope, in this case). If you are in the table footer, you can define an aggregate with scope 1) none (equivalent to table) or 2) Table or 3) Nothing. As you can see here, you can reach out to outer/containing scopes from the current location, but not the opposite way. This error message "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." basically means this.
From what you described in the posts, looks like you are trying to sum a set of calculated values (involve aggregates) from the group footers in your table footer. "Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release.
For now, there is a workaround for this that involves using custom code (similar to what Andrew has suggested) and can be quite tricky to get it to work. The idea is that you would pass a detail field value to as an argument to the custom function, do the calculation in the function, add it to a variable you define in the custom code, and at the end (in the table group), return the variable value which is the grand total you are looking for. Without looking at your particular scenario, I'm not entirely sure if this approach would work for you. But hopefully it can give you some pointer to start with if you want to try it.
Below is an old sample I provided to one of our customers to workaound a similar problem. Hope it's of some help to you.
<?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>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report1</Value>
</Textbox>
<Table Name="table1">
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
<CanGrow>true</CanGrow>
<Value>=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Freight">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Freight</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>OrderID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<DataSetName>Northwind</DataSetName>
<Width>2in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<Top>0.33in</Top>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Code.MyFunc(Nothing, Fields!Freight.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.21in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5b047e43-435e-4e68-bf7b-70eaf0f45c53</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code> Dim orderIDs As System.Collections.Hashtable
Dim total As Double
Function MyFunc(ByVal orderID As Object, ByVal freight As Object) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function
</Code>
<Width>5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Expr1">
<DataField>Expr1</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Orders.OrderID, Orders.Freight, [Order Details].OrderID AS Expr1, [Order Details].UnitPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (Orders.OrderID IN (10248, 10250))</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>1e9e64e8-3f34-4760-ae9a-0789ad4c44c7</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Fang Wang, this is a huge limitation, any idea when this will be supported? If it was, I would have been able to save literally a month worth of wasted time.
Basically I ended up having to do this sum in SQL which was painful because it included about 7 complicated SUMS before it which were wrapped up into this final Column SUm and I do not wish to have to code SQL like that again. On the other hand custom code as you stated is not easy and definitely something I would not wish on anyone just to sum up a Column in this situation.
thanks for th workaround and the FYI on the non-supported note though! Much appreciated. Lets hope for all of us (many who have posted this problem in other forums) that MS comes up wtih a SP which will fix this and provide this very powerful option in the future.
|||and another complaint, it is endless, this time with a matrix!
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882
|||First of all enough with the caps / bold and large text. We understand your problem, and quite frankly the world is not going to end.
Secondly, the solution (workaround) was provided and DOES work.
Please do some reading on how to use and control flow using custom code in rs.
Jon
|||
Not sure if you have gone through the entire solution above, however this is another possible solution if that didn't work (from my blog at http://sqlrs.blogspot.com)
One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.
What if you don't want to or can't?
Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.
Steps to lookup values from a reference table in SQL:
Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)
Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")
In the Code Properties window, try the following:
public dict as new System.Collections.Generics.Dictionary(Of System, System)
function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function
function getValue(value as object) as object
return dict(value)
end function
Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.
If you have properly bound a table to the first list control, you should be able to lookup results in the second table.
This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.
I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.
Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.
Oh, and Flavaflav, I really hope you don't wear a clock around your neck & have gold teeth.
cheers,
Andrew
Cannot Reference and SUM fields from Table Group in Footer Expression!
I'm gonna try this again, Microsoft, Please help!
There has to be a solution for this!!!! How can I get around the limitation in SSRS 2005 of being able to SUM on a Table's Group field from my group footer expression!
Keep in mind, I'm talking about a table here, not the global Report Footer, a Table object's footer!
It's driving me nuts
My table footer GrossGoal_gt 's field expression (non of these work and throw the error below):
=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")
neither does this:
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
neither does this:
=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:
Aggregate functions can be used only on report items contained in page headers and footers.
or this:
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
The field it's referencing's (textbox GrossGoal1) value is:
=((Fields!FeeGoal.Value) / Fields!FeeSchedule.Value) * 100
Error:
[rsFieldReference] The Value expression for the textbox ‘GrossGoal_gt’ refers to the field ‘GrossGoal1’. Report item expressions can only refer to fields within the current data set scope or, if inside an aggregate, the specified data set scope.
and
[rsInvalidAggregateScope] The Value expression for the textbox ‘GrossGoal_gt’ has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
I did that! I set it to my Group name of my table!!!! Am I doing this wrong?
http://www.eggheadcafe.com/forums/ForumPost.asp?ID=59694&INTID=9
I am having to produce a stupid work around which is create a new dataset, tie it to a stored proc which then does some crazy calculations which is a headache in SQL which sums up my column's total using SQL in the stored proc instead of Reporting Services being able to reference my stupid Group field in the footer in order to sum for a grand total in the footer for that group column.
Related threads I have posted:
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/936792
Here's are other people with the same problem but no answers out there from my searches!
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882
http://www.sqlblogs.com/top/ng/group~24/~131100~__Scope-problem-with-SUM-on-group-footer/index.aspx
http://www.sqlblogs.com/top/ng/group~24/~130065~__MVPs-and-MS-support--Where-are-you~~~-on-dificults-questions/index.aspx
http://www.developmentnow.com/g/115_2005_2_0_0_455511/GroupingSum-Question.htm
http://www.sqlblogs.com/top/ng/group~24/~129523~__RunningValue-Error/index.aspx
Why doesn't this work MS ?
Scope
Because you may have multiple groups defined, you can specify the scope an aggregate function is to use. Scopes are names that you can pass to aggregate functions to indicate how you want to calculate the aggregates. For example, for tables that have more than one group defined, you may want to refer to an outergroup from an innergroup header row. The Nothing scope tells a function to use the outermost scope. For a simple table data region with no grouping, there is only one scope.
To identify scopes that are valid for a report item grouping, select the item, open the Properties dialog box, click the Groups tab, and view the list of group labels that can be used to identify scopes.
Why am I putting this in big letters? Because I'm about to pull my hair out!!!!
Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:
Report Functions
Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).
The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:
Copy Code
=Sum(Fields!LineTotal.Value, "Order")Based on your previous posts, the problem seems to be that each detail row of the table has an amount (not sure which data set field: maybe Fields!FeeGoal.Value?) and a percent (maybe Fields!FeeSchedule.Value?). Then there is a derived amount expression: "=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100" in a text box (GrossGoal1). What if, instead, you add a Calculated Field like GrossGoal to your data set, defined by the same expression:"=(Fields!FeeGoal.Value / Fields!FeeSchedule.Value) * 100"? In that case, the detailed row amount can simply be: "=Fields!GrossGoal.Value", and both the group and footer expressions can be: "=Sum(Fields!GrossGoal.Value)".|||
Deepak, I've definitely tried that work around with little success if any. Most of the time, Visual Studio crashes when trying to SUM on a calculated Dataset field that is also very complex in itself!
Also Proj Fee (what I am referring to above for an example of what I'm currently stuck on)is not just a single value, it is a complicated calculation that includes many in it's expression. That's why this report is so crazy and why I really need to be able to bypass all these workarounds to be able to direcly reference fields from the footer and use aggregate functions on those fields.
As my report goes from right to left, the fields are not jus straight values, they have a ton of combined fields and calculatiosn within each field so each field really builds on the other...keep in mind this used to be a complex Excel Spreadsheet that they wanted me to automate!
the fact is, myself and others see this as a huge problem and limitatio that is keeping us from delivering highly complicated reporting via Reporting Services 2005. We do not want to create wacky ways around this....if that's the case, we might as well go back to Crystal at this point ! Yes, SSRS is great but this limitation is HUGE!
Here's Project Fee...yes, it's a nightmare and you'll see now why you can't just assume this work around or that when you get this complicated in your reports; We need to be able to SUM Group Header fields (not detail) in our Footer. I am not using the detail section of my Group, I don't need it.
ProjFee Expression is:
=(Round((((SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!NewCCs_Check.Value)) / Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day")) * (Sum(Fields!TotalPostingDays.Value, "TotalPostingDays") - Sum(Fields!CurrentPostingDay.Value, "Current_Posting_Day"))) + (SUM(Fields!PostedAmount_InHouse.Value) + SUM(Fields!OldPDs_Check.Value) + SUM(Fields!NewPDs_Check.Value) + SUM(Fields!OldCCs_Check.Value) + SUM(Fields!NewCCs_Check.Value)))) * (Fields!FeeSchedule.Value / 100)
You try putting this stripped down as a new calculated field in your dataset and then try doing a SUM on this in the footer of your table and watch Visual Studio Crash!!! It's loads of fun
|||
Here is my report file....open it, take a look at the last report at the bottom. The ProjFee in Footer..I want this to sum up my ProjFee Group Column:
http://photopizzaz.biz/myreport.zip
I've also tried these combinations using straight formulas (changing the SUM around in the past has worked so far up until now) with no luck...all the totals come out wrong:
http://www.photopizzaz.biz/calcs_tried_in_projfee_footer_expression.doc
What I'm trying to achieve like eveyrone else is to have success putting something like these into my ProjFee Footer textbox expression:
=SUM(Fields!GrossGoal1.Value, "CustomerNumber_Grp")
Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
=SUM(ReportItems!GrossGoal1.Value, "CustomerNumber_Grp") - this one then gives me yet a different error besides the scope error:
Error: Aggregate functions can be used only on report items contained in page headers and footers.
=RunningValue(ReportItems!ProjFee_AZ.Value,Sum,"CustomerNumber_Grp")
Error:The Value expression for the textbox has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set.
|||If anyone needs test data to test my report, I'd be happy to provide it so I can figure out this problem, just let me know. It will be non-confidential test data that will work.|||You may want to try using a custom function and a global variable to store a running total (sort of a SUMIF(value, groupvalue)) that you would create in the code window.
See http://www.yukonxml.com/chapters/apress/reportingservices/dotnet/
Actually all of your complex calculations might be simplified by encapsulating in vb functions.
Here's more from Teo's book.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/ERSCstCode.asp?frame=true
|||Yes, but Andrew, I need the SUM in my footer to be summing up the group totals in my group field column...an outside function isn't gonna help me here because Commission % has to be used in the group calculation, I can't use Commission % in my GT because it's not possible to sum a bunch of % then do a calcuation like this.
Also, I was told that the group footer takes in the whole dataaset? not just the group totals.
Anyway, I think this is crazy first of all that I haven't had an MS staff reply to this problem, and second the documentation says we should be able to reference a groupname as the scope in a field expression from a table footer yet their errors tell a different story. Furthermore, there are at least from my searches, no examples of referencing a groupname or other names other than a dataset in BOL for doing what I want but the error says you should be able to. Where's the documentation on this scope? I only saw a brief comment and the most simplest example in BOL which isn't really helping anyone.
Can someone from MS please comment who is in the SSRS 2005 team!! A lot of use are wondering what the deal is here and have for quite some time.
|||Microsoft states this below (http://msdn2.microsoft.com/en-us/library/ms157328.aspx) but it doesn't work for me when I try it (above) and I get those errors above when I try this for my table group:
Report Functions
Reporting Services provides additional report functions that you can use to manipulate data within a report. This section provides examples for two of these functions. For more information about report functions and examples, see Using Report Functions in Expressions (Reporting Services).
The Sum function can total the values in a grouping or data region. This function can be useful in the header or footer of a table group. The following expression displays the sum of data in the Order grouping or data region:
Copy Code
=Sum(Fields!LineTotal.Value, "Order")|||
You should check that whether your sql or view have the column named "GrossGoal1".
|||
Sorry for being rude but I just said 1000 times, it's not a field from my dataset! It's a named textbox in my Report table's group. If you right-click a field in your group in your report, you have to name it something...i.e. textbox50 to something lik GROSSGOL
I want to do a SUM on that column textbox that resides in my table's group...not the Dataset!
Everyone keeps thinking I'm talking about a Dataset field, no, it's a Table Group Field!!
Create a table in SSRS 2005 OK. Then, create a group in that table. Ok, then add some fields to that group Header in your table. Rename one of them to GrossGoal by going to the properties of one of your table's textboxes (which contain expressions like referencing dataset fields!). Ok, then in your Table footer, try to do a SUM(name of the textbox in your group that you just renamed)
that's what I'm trying to do! Please Download and Look at my freakin .rdl that I provided, don't just guess what I'm trying to do here, I've provided probably 10x the required information! If you look at my .rdl, right click on the fields and see their properties, then look at the expression for the footer field! It's trying to reference the field above it which is a group field!
and one more time, because of the fact that I'm dealing with a commission %, simply pasting the same expression from my group to my footer will not work!! trust me...this is a very complex calculation that willl not come out right going that route like all the books tell you to do!
|||Looks like there is a confusion on the scope argument in aggregate functions. What the scope argument tells RS is to summarize all detail data in the specified scope, not summarizing all the aggregated values on these scopes.
For example, if you have a table (with table footer) and a table group. (looks like the site that host your report is currently down, so I can't look at your particular case). The hiearchy of the report looks like:
Report/DataSet
Table (table header/footer rows)
TableGroup (tablegroup header/footer rows)
In the TableGroup (group header/footer), you can define an aggregate with scope 1) none (the default is the current scope - TableGroup) or 2) TableGroup or 3) Table or 4) Nothing (the outermost scope, meaning the entire dataset, essentially the same as the table scope, in this case). If you are in the table footer, you can define an aggregate with scope 1) none (equivalent to table) or 2) Table or 3) Nothing. As you can see here, you can reach out to outer/containing scopes from the current location, but not the opposite way. This error message "The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set." basically means this.
From what you described in the posts, looks like you are trying to sum a set of calculated values (involve aggregates) from the group footers in your table footer. "Aggregate of aggregates" is a feature we don't currently support. It's a high priority item on our wishlist for a future release.
For now, there is a workaround for this that involves using custom code (similar to what Andrew has suggested) and can be quite tricky to get it to work. The idea is that you would pass a detail field value to as an argument to the custom function, do the calculation in the function, add it to a variable you define in the custom code, and at the end (in the table group), return the variable value which is the grand total you are looking for. Without looking at your particular scenario, I'm not entirely sure if this approach would work for you. But hopefully it can give you some pointer to start with if you want to try it.
Below is an old sample I provided to one of our customers to workaound a similar problem. Hope it's of some help to you.
<?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>
<FontFamily>Times New Roman</FontFamily>
<BackgroundColor>Brown</BackgroundColor>
<BorderWidth>
<Bottom>3pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<FontSize>18pt</FontSize>
<TextAlign>Center</TextAlign>
<Color>White</Color>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<Height>0.33in</Height>
<CanGrow>true</CanGrow>
<Value>Report1</Value>
</Textbox>
<Table Name="table1">
<Style>
<BackgroundColor>White</BackgroundColor>
<BorderWidth>
<Top>3pt</Top>
</BorderWidth>
<BorderStyle>
<Top>Solid</Top>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>2pt</Bottom>
</BorderWidth>
<BorderColor>
<Bottom>DarkRed</Bottom>
</BorderColor>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>900</FontWeight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<Visibility>
<Hidden>true</Hidden>
</Visibility>
<CanGrow>true</CanGrow>
<Value>=Sum(Code.MyFunc(Fields!OrderID.Value, Fields!Freight.Value))</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Freight">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>Freight</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>OrderID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<DataSetName>Northwind</DataSetName>
<Width>2in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.21in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Freight.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_OrderID">
<GroupExpressions>
<GroupExpression>=Fields!OrderID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderID.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
<Top>0.33in</Top>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Code.MyFunc(Nothing, Fields!Freight.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>1in</Width>
</TableColumn>
<TableColumn>
<Width>1in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.21in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>5b047e43-435e-4e68-bf7b-70eaf0f45c53</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=.;initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Code> Dim orderIDs As System.Collections.Hashtable
Dim total As Double
Function MyFunc(ByVal orderID As Object, ByVal freight As Object) As Double
If (orderIDs Is Nothing) Then
orderIDs = New System.Collections.Hashtable
End If
If (orderID Is Nothing) Then
MyFunc = total
Else
If (Not orderIDs.Contains(orderID)) Then
total = total + freight
orderIDs.Add(orderID, freight)
End If
MyFunc = total
End If
End Function
</Code>
<Width>5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Expr1">
<DataField>Expr1</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Orders.OrderID, Orders.Freight, [Order Details].OrderID AS Expr1, [Order Details].UnitPrice
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order Details].OrderID
WHERE (Orders.OrderID IN (10248, 10250))</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<Description />
<rd:ReportID>1e9e64e8-3f34-4760-ae9a-0789ad4c44c7</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
Fang Wang, this is a huge limitation, any idea when this will be supported? If it was, I would have been able to save literally a month worth of wasted time.
Basically I ended up having to do this sum in SQL which was painful because it included about 7 complicated SUMS before it which were wrapped up into this final Column SUm and I do not wish to have to code SQL like that again. On the other hand custom code as you stated is not easy and definitely something I would not wish on anyone just to sum up a Column in this situation.
thanks for th workaround and the FYI on the non-supported note though! Much appreciated. Lets hope for all of us (many who have posted this problem in other forums) that MS comes up wtih a SP which will fix this and provide this very powerful option in the future.
|||and another complaint, it is endless, this time with a matrix!
http://database.ittoolbox.com/groups/technical-functional/sql-server-l/scope-problem-in-matrix-945882
|||First of all enough with the caps / bold and large text. We understand your problem, and quite frankly the world is not going to end.
Secondly, the solution (workaround) was provided and DOES work.
Please do some reading on how to use and control flow using custom code in rs.
Jon
|||Not sure if you have gone through the entire solution above, however this is another possible solution if that didn't work (from my blog at http://sqlrs.blogspot.com)
One common problem in reporting and BI solutions is how to incorporate data from both an OLAP cube and relational tables. The UDM in SQL 2005 attempts to solve this, however it really means you still need to build the information into your cubes and dimension attributes.
What if you don't want to or can't?
Reporting Services provides a Custom Code tab within the Report Properties page. You can access various VB.NET objects and system assemblies, and reference external assemblies. One of the internal assemblies is the Dictionary object.
Steps to lookup values from a reference table in SQL:
Drag a list onto the report.
Drag a textbox into the list, or a field from the relational dataset. Modify the textbox to contain =Code.setValue(Fields!KeyField.Value, Fields!ValueField.Value)
Create another list below. Drag another textbox into the list. Modify the textbox expression to hard-code the key for now. =Code.getValue("MyKey")
In the Code Properties window, try the following:
public dict as new System.Collections.Generics.Dictionary(Of System, System)
function setValue(value as object, value2 as object) as object
dict.Add(value,value2)
return value
end function
function getValue(value as object) as object
return dict(value)
end function
Afterwards, you can hide the list box (or table or whatever) that loads the variable with the setValue function. The dictionary still gets populated.
If you have properly bound a table to the first list control, you should be able to lookup results in the second table.
This can be applied in many scenarios, including adding relational reference data to MDX results, and creating a relationship between two separate datasets.
I'd be interested to know if anyone uses this. It seems to have many different applications. One could possibly involve showing two sets of information, for things like variances or budget vs. actual data. If a value doesn't exist in the dictionary, the original field could be returned. If it does exist, the adjustment could be returned.
Note that Generics is .NET 2.0 - for 2000 you may need to use a different syntax but the concept is the same. Basically you're using a dictionary object (could be a hash table or whatever) to store a value by a key. Then you're looking up that value in a table (or list or whatever) to do further calculations.
Oh, and Flavaflav, I really hope you don't wear a clock around your neck & have gold teeth.
cheers,
Andrew
Cannot read unicode data from SQL Server
Hi all,
I have a SQL Server 2000 running as the database and there
is a table contain some multi-language data, the table
structure is as follow:
CREATE TABLE mytable
(
english nvarchar(255),
japanese nvarchar(255),
chinese nvarchar(255)
)
The chinese column will store traditional chinese
characters while the japanese column will store japanese
character.
I have already imported all the record from an well-
defined Excel file to mytable, and I can read all the
chinese and japanese characters properly from Enterprise
manager and Query Analyzer, however, when I use ADO
recordset to get the data from mytable in the ASP, the
data cannot be shown properly, all the japanese and
chinese data is shown as question marks (ie,"'?").
Here is my ASP Code,
Set dbCon = Server.CreateObject("ADODB.Connection")
dbCon.Open "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=mydatabase;Data
Source=myserver"
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = 3
rs.CursorType = 3
rs.LockType = 3
rs.ActiveConnection = dbCon
rs.Open "Select * from mytable"
Even though I have changed the charset to "big5" in the
ASP, only chinese character can be shown properly, but not
japanese. I have already try all the charset in my ASP,
but none of them can show the japanese characters properly.
rmk:
1. I am running Traditional Chinese Windows 2k and in the
regional setting, I have set the default locate to
traditional chinese.
2. The collation of the my database is
SQL_Latin1_General_CP1_CI_AS
Is it the collation problem, window OS locate problem,data
type problem or the ASP coding problem ?
Anyone can help me ?
Many thanks
ongMake sure Japanses font is installed.
Do not use charset Big5. You want utf-8
Use the directive <%@. CodePage=65001 %> at the top of your asp page
"ong" <on_g@.sinatown.com> wrote in message
news:011c01c35012$1b1bb560$a001280a@.phx.gbl...
> Hi all,
> I have a SQL Server 2000 running as the database and there
> is a table contain some multi-language data, the table
> structure is as follow:
> CREATE TABLE mytable
> (
> english nvarchar(255),
> japanese nvarchar(255),
> chinese nvarchar(255)
> )
> The chinese column will store traditional chinese
> characters while the japanese column will store japanese
> character.
> I have already imported all the record from an well-
> defined Excel file to mytable, and I can read all the
> chinese and japanese characters properly from Enterprise
> manager and Query Analyzer, however, when I use ADO
> recordset to get the data from mytable in the ASP, the
> data cannot be shown properly, all the japanese and
> chinese data is shown as question marks (ie,"'?").
> Here is my ASP Code,
> Set dbCon = Server.CreateObject("ADODB.Connection")
> dbCon.Open "Provider=SQLOLEDB.1;Persist Security
> Info=False;User ID=sa;Initial Catalog=mydatabase;Data
> Source=myserver"
> Set rs = Server.CreateObject("ADODB.RecordSet")
> rs.CursorLocation = 3
> rs.CursorType = 3
> rs.LockType = 3
> rs.ActiveConnection = dbCon
> rs.Open "Select * from mytable"
>
> Even though I have changed the charset to "big5" in the
> ASP, only chinese character can be shown properly, but not
> japanese. I have already try all the charset in my ASP,
> but none of them can show the japanese characters properly.
> rmk:
> 1. I am running Traditional Chinese Windows 2k and in the
> regional setting, I have set the default locate to
> traditional chinese.
> 2. The collation of the my database is
> SQL_Latin1_General_CP1_CI_AS
>
> Is it the collation problem, window OS locate problem,data
> type problem or the ASP coding problem ?
> Anyone can help me ?
> Many thanks
> ong
>
I have a SQL Server 2000 running as the database and there
is a table contain some multi-language data, the table
structure is as follow:
CREATE TABLE mytable
(
english nvarchar(255),
japanese nvarchar(255),
chinese nvarchar(255)
)
The chinese column will store traditional chinese
characters while the japanese column will store japanese
character.
I have already imported all the record from an well-
defined Excel file to mytable, and I can read all the
chinese and japanese characters properly from Enterprise
manager and Query Analyzer, however, when I use ADO
recordset to get the data from mytable in the ASP, the
data cannot be shown properly, all the japanese and
chinese data is shown as question marks (ie,"'?").
Here is my ASP Code,
Set dbCon = Server.CreateObject("ADODB.Connection")
dbCon.Open "Provider=SQLOLEDB.1;Persist Security
Info=False;User ID=sa;Initial Catalog=mydatabase;Data
Source=myserver"
Set rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorLocation = 3
rs.CursorType = 3
rs.LockType = 3
rs.ActiveConnection = dbCon
rs.Open "Select * from mytable"
Even though I have changed the charset to "big5" in the
ASP, only chinese character can be shown properly, but not
japanese. I have already try all the charset in my ASP,
but none of them can show the japanese characters properly.
rmk:
1. I am running Traditional Chinese Windows 2k and in the
regional setting, I have set the default locate to
traditional chinese.
2. The collation of the my database is
SQL_Latin1_General_CP1_CI_AS
Is it the collation problem, window OS locate problem,data
type problem or the ASP coding problem ?
Anyone can help me ?
Many thanks
ongMake sure Japanses font is installed.
Do not use charset Big5. You want utf-8
Use the directive <%@. CodePage=65001 %> at the top of your asp page
"ong" <on_g@.sinatown.com> wrote in message
news:011c01c35012$1b1bb560$a001280a@.phx.gbl...
> Hi all,
> I have a SQL Server 2000 running as the database and there
> is a table contain some multi-language data, the table
> structure is as follow:
> CREATE TABLE mytable
> (
> english nvarchar(255),
> japanese nvarchar(255),
> chinese nvarchar(255)
> )
> The chinese column will store traditional chinese
> characters while the japanese column will store japanese
> character.
> I have already imported all the record from an well-
> defined Excel file to mytable, and I can read all the
> chinese and japanese characters properly from Enterprise
> manager and Query Analyzer, however, when I use ADO
> recordset to get the data from mytable in the ASP, the
> data cannot be shown properly, all the japanese and
> chinese data is shown as question marks (ie,"'?").
> Here is my ASP Code,
> Set dbCon = Server.CreateObject("ADODB.Connection")
> dbCon.Open "Provider=SQLOLEDB.1;Persist Security
> Info=False;User ID=sa;Initial Catalog=mydatabase;Data
> Source=myserver"
> Set rs = Server.CreateObject("ADODB.RecordSet")
> rs.CursorLocation = 3
> rs.CursorType = 3
> rs.LockType = 3
> rs.ActiveConnection = dbCon
> rs.Open "Select * from mytable"
>
> Even though I have changed the charset to "big5" in the
> ASP, only chinese character can be shown properly, but not
> japanese. I have already try all the charset in my ASP,
> but none of them can show the japanese characters properly.
> rmk:
> 1. I am running Traditional Chinese Windows 2k and in the
> regional setting, I have set the default locate to
> traditional chinese.
> 2. The collation of the my database is
> SQL_Latin1_General_CP1_CI_AS
>
> Is it the collation problem, window OS locate problem,data
> type problem or the ASP coding problem ?
> Anyone can help me ?
> Many thanks
> ong
>
Cannot Pull table with RDA
Dears,
I'm trying to set up a rda connection through the pocket pc emulator (standalone version 1.0) and my laptop which runs Windows Vista Home Premium. To start up the rda I first need to connect from my emulated device to my laptop which runs IIS 7.0 (complete installation with compatibility toward IIE 6.0). Connection to http://localhost from my laptop is working fine.
First of all I must say that now I am able to connect to SQL Server Express through SQL Server Management Studio Express using MobileUser as user id and pippo as password.
|||...done!
Now rda.pull is working properly: the table is downloaded correctly into the file ristobar.sdf.
As I can see on Query Analyzer all records are there.
Anyway I still got an issue regarding rda.dispose() : it crashes the emulator.
The pull command is executed as below:
breakpoint rda.dispose ()
I'm executing the following subroutine to manage the rda.pull and to catch possible exceptions. Unfortunately the emulator still get stuck where highlighted.
Thanks
Cristian
I'm trying to set up a rda connection through the pocket pc emulator (standalone version 1.0) and my laptop which runs Windows Vista Home Premium. To start up the rda I first need to connect from my emulated device to my laptop which runs IIS 7.0 (complete installation with compatibility toward IIE 6.0). Connection to http://localhost from my laptop is working fine.
I start the emulator (Pocket Pc with Windows Professional) and cradle the emulated device.
Connection starts and I'm able to browse from my pc the content of the emulated device or to synchronize it.
Then I run Internet Explorer on the emulator and try to connect to http://mycomputername (correct) but I receive a message which says that it cannot connect to the page I was looking for because connection was lost.
Any suggestion on how to solve this issue?
Kind regards and many thanks in advance
Cristian
It seems that now I can connect to http://mycomputername and I'm able to browse the net with my pocket pc emulator.
Anyway when I'm attempting to connect to htpp://mycomputername/subfolder/sqlcesa30.dll (the path is correct, I receive the following error message: "The page cannot be displayed or downloaded because the connection was lost. Check the connection and try later"..which is the same message I've been receiving before)
What shall I do?
Hi,
it seems that I can connect to http://mycomputernae and http://mycomputername/virtualfolder/sqlcesa30.dll only if my laptop is connected to the local network or to the internet.
Does anybody know why?
Thankyou
|||Hi,
I'm using the following code to extablish a rda between my laptop and windows mobile classic 6 emulator to pull down a table named customers from a db named ristobar, having configure an account to access to the database which has db_owner permissions (user id=MobileUser, password=pippo)
Anyway I always get the following exception:
Source : Microsoft SQL server 2005 Everywhere Edition
Native Error: 28627
Message: Failure to open SQL Server with given connect string
HResult: 80004005
Below follows IIS 7.0 logfile.
The Ip address of my laptop is 192.168.0.199 and its name is SKYWALKER
What am I doing wrong?
Thank you
BEholder
Public Class Rdademo
Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=\SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"
Dim rdaUrl As String = "http://192.168.0.199/ristobardb/sqlcesa30.dll" ' The ip is correct
Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf"
Private Sub RdaPull()
Try
If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My_ Documents\Rdadb.sdf")
Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
Dim rda As SqlCeRemoteDataAccess = Nothing
rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOnWithIndexes, "Error Table")
rda.Dispose()
MessageBox.Show("Done!")
Catch ex As System.Data.SqlServerCe.SqlCeException
MessageBox.Show(ex.Source)
MessageBox.Show(ex.NativeError)
MessageBox.Show(ex.Message)
MessageBox.Show(Hex(ex.HResult))
End Try
End Sub
IIS 7.0 Log:
Software: Microsoft Internet Information Services 7.0
#Version: 1.0
#Date: 2007-08-29 14:28:54
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
2007-08-29 15:21:33 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:35 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:38 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:40 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:43 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:47 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:49 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0|||
Change your OLEDB connectionstring:
Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"
or
Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=192.168.0.199;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"|||
Thanks for your reply Erik,
unfortunately it didn't work.
I tried both using Data Source=SKYWALKER and using Data Source=192.168.0.199 but I am receiving the same error.
Shall I use a particular port to connect to the SQL server?
By the way....reading other posts I noticed a couple of things: 1) shall I indicate the full path of the SQL server ? That is, if SQL Server Express is running on my machine (default name SQLEXPRESS): 192.168.0.199\SQLEXPRESS,1433 ? Port 1433 should be opened, is that right?
2) Shall I specify TCP/IP connection inside the connection string even if I am using a pocket pc emulator configured to use DMA access or it is not necessary?
Thank you for your help
Cristian
|||You did not mentions SQL Server Express... Yes, a connection string with IP address and \SQLEXPRESS,1433 is required and port 1433 must be open for connections.
It is not required to specify TCP/IP connection in the connectionstrin when cradled via DMA.
|||OK, it seems that somehow things are beginning to work, infact I am getting a new error message.First of all I must say that now I am able to connect to SQL Server Express through SQL Server Management Studio Express using MobileUser as user id and pippo as password.
I run the same code that i posted before, changed the connection string to
"Provider=sqloledb;Data Source=localhost\SQLEXPRESS;User ID=MobileUser;Password=pippo;Initial Catalog=ristobar;"
and I get the following error:
Number: 29022
Description:The version of the Microsoft OLE DB Provider for SQL Server is not correct. Install MDAC 2.8 or later.
HResult: 0x80004005
What do I have to install now?
Thanks for your kind help
Cristian
|||OK, now we are getting closer 
You need to install the latest version of the SQL Compact Server Tools, build 5300. See this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1368182&SiteID=1 for installtion tips
(install the msi from an elevated command prompt!)
The latest version of the tools can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=4e45f676-e69a-4f7f-a016-c1585acf4310&displaylang=en
After installation, the sqlcesa30.dll file should be build version 5300.
Also see this KB article, under the heading: Known issues for SQL Server Compact Edition 3.1 on Windows Vista
http://support.microsoft.com/?kbid=920700
|||Thanks again for your help.
I will try to install SQL Compact Server Tools at the link you specified from the command prompt (and will look to known issues on Vista)
Anyway I was pretty sure to have already installed that build...perhaps not in the right way.
If I am getting the same error after the installation sould I install MRDAC 2.8 or MRDAC 2.8 SP1 to solve this issue?
Cristian
|||MDAC is not supported on Vista, so no.|||So we must succeed|||...done!
Now rda.pull is working properly: the table is downloaded correctly into the file ristobar.sdf.
As I can see on Query Analyzer all records are there.
Anyway I still got an issue regarding rda.dispose() : it crashes the emulator.
The pull command is executed as below:
rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOff)
MessageBox.Show ("Done!")
breakpoint rda.dispose ()
If I remove the breakpoint or the corresponding line of program the emulator won't crash, otherwise It will got stuck and will have to be shut down.
What should it depend on? A misconfigured time-out on SQL Server Express or on IIS ?
Thank you!
Cristian
|||Try to catch any exceptions as per the pattern in this: http://msdn2.microsoft.com/en-us/library/swwy8y74.aspx
To get all the error information look at this sample: http://msdn2.microsoft.com/en-us/library/ms174079.aspx
|||Well, having read your suggestion I changed my code a little.I'm executing the following subroutine to manage the rda.pull and to catch possible exceptions. Unfortunately the emulator still get stuck where highlighted.
Thanks
Cristian
Private Sub RdaPull()
Dim rdaOleDbConnectionString As String = "Provider=sqloledb;Data Source=localhost\SQLEXPRESS;User ID=MobileUser;Password=pippo;Initial Catalog=ristobar;Persist Security Info=True;"
Dim rdaUrl As String = "http://192.168.0.199/ristobardb2/sqlcesa30.dll"
Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf"
Dim rda As SqlCeRemoteDataAccess = Nothing
Try
If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My Documents\Rdadb.sdf")
Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOff)
MessageBox.Show("Done!")
Catch ex As System.Data.SqlServerCe.SqlCeException
MessageBox.Show(ex.Source)
MessageBox.Show(ex.NativeError)
MessageBox.Show(ex.Message)
MessageBox.Show(Hex(ex.HResult))
Finally
rda.Dispose()
End Try|||
Is the sub that I posted well formed?
Cannot Pull table with RDA
Dears,
I'm trying to set up a rda connection through the pocket pc emulator (standalone version 1.0) and my laptop which runs Windows Vista Home Premium. To start up the rda I first need to connect from my emulated device to my laptop which runs IIS 7.0 (complete installation with compatibility toward IIE 6.0). Connection to http://localhost from my laptop is working fine.
First of all I must say that now I am able to connect to SQL Server Express through SQL Server Management Studio Express using MobileUser as user id and pippo as password.
|||...done!
Now rda.pull is working properly: the table is downloaded correctly into the file ristobar.sdf.
As I can see on Query Analyzer all records are there.
Anyway I still got an issue regarding rda.dispose() : it crashes the emulator.
The pull command is executed as below:
breakpoint rda.dispose ()
I'm executing the following subroutine to manage the rda.pull and to catch possible exceptions. Unfortunately the emulator still get stuck where highlighted.
Thanks
Cristian
I'm trying to set up a rda connection through the pocket pc emulator (standalone version 1.0) and my laptop which runs Windows Vista Home Premium. To start up the rda I first need to connect from my emulated device to my laptop which runs IIS 7.0 (complete installation with compatibility toward IIE 6.0). Connection to http://localhost from my laptop is working fine.
I start the emulator (Pocket Pc with Windows Professional) and cradle the emulated device.
Connection starts and I'm able to browse from my pc the content of the emulated device or to synchronize it.
Then I run Internet Explorer on the emulator and try to connect to http://mycomputername (correct) but I receive a message which says that it cannot connect to the page I was looking for because connection was lost.
Any suggestion on how to solve this issue?
Kind regards and many thanks in advance
Cristian
It seems that now I can connect to http://mycomputername and I'm able to browse the net with my pocket pc emulator.
Anyway when I'm attempting to connect to htpp://mycomputername/subfolder/sqlcesa30.dll (the path is correct, I receive the following error message: "The page cannot be displayed or downloaded because the connection was lost. Check the connection and try later"..which is the same message I've been receiving before)
What shall I do?
Hi,
it seems that I can connect to http://mycomputernae and http://mycomputername/virtualfolder/sqlcesa30.dll only if my laptop is connected to the local network or to the internet.
Does anybody know why?
Thankyou
|||Hi,
I'm using the following code to extablish a rda between my laptop and windows mobile classic 6 emulator to pull down a table named customers from a db named ristobar, having configure an account to access to the database which has db_owner permissions (user id=MobileUser, password=pippo)
Anyway I always get the following exception:
Source : Microsoft SQL server 2005 Everywhere Edition
Native Error: 28627
Message: Failure to open SQL Server with given connect string
HResult: 80004005
Below follows IIS 7.0 logfile.
The Ip address of my laptop is 192.168.0.199 and its name is SKYWALKER
What am I doing wrong?
Thank you
BEholder
Public Class Rdademo
Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=\SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"
Dim rdaUrl As String = "http://192.168.0.199/ristobardb/sqlcesa30.dll" ' The ip is correct
Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf"
Private Sub RdaPull()
Try
If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My_ Documents\Rdadb.sdf")
Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
Dim rda As SqlCeRemoteDataAccess = Nothing
rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOnWithIndexes, "Error Table")
rda.Dispose()
MessageBox.Show("Done!")
Catch ex As System.Data.SqlServerCe.SqlCeException
MessageBox.Show(ex.Source)
MessageBox.Show(ex.NativeError)
MessageBox.Show(ex.Message)
MessageBox.Show(Hex(ex.HResult))
End Try
End Sub
IIS 7.0 Log:
Software: Microsoft Internet Information Services 7.0
#Version: 1.0
#Date: 2007-08-29 14:28:54
#Fields: date time s-ip cs-method cs-uri-stem cs-uri-query s-port cs-username c-ip cs(User-Agent) sc-status sc-substatus sc-win32-status
2007-08-29 15:21:33 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:35 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:38 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:40 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:43 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:47 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0
2007-08-29 15:21:49 127.0.0.1 POST /ristobardb/sqlcesa30.dll - 80 - 127.0.0.1 SQLCEReplicationClient 200 0 0|||
Change your OLEDB connectionstring:
Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=SKYWALKER;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"
or
Dim rdaOleDbConnectionString As String = "Provider=SQLOLEDB;Data Source=192.168.0.199;Initial_ Catalog=ristobar;User ID=MobileUser;Password=pippo;"|||
Thanks for your reply Erik,
unfortunately it didn't work.
I tried both using Data Source=SKYWALKER and using Data Source=192.168.0.199 but I am receiving the same error.
Shall I use a particular port to connect to the SQL server?
By the way....reading other posts I noticed a couple of things: 1) shall I indicate the full path of the SQL server ? That is, if SQL Server Express is running on my machine (default name SQLEXPRESS): 192.168.0.199\SQLEXPRESS,1433 ? Port 1433 should be opened, is that right?
2) Shall I specify TCP/IP connection inside the connection string even if I am using a pocket pc emulator configured to use DMA access or it is not necessary?
Thank you for your help
Cristian
|||You did not mentions SQL Server Express... Yes, a connection string with IP address and \SQLEXPRESS,1433 is required and port 1433 must be open for connections.
It is not required to specify TCP/IP connection in the connectionstrin when cradled via DMA.
|||OK, it seems that somehow things are beginning to work, infact I am getting a new error message.First of all I must say that now I am able to connect to SQL Server Express through SQL Server Management Studio Express using MobileUser as user id and pippo as password.
I run the same code that i posted before, changed the connection string to
"Provider=sqloledb;Data Source=localhost\SQLEXPRESS;User ID=MobileUser;Password=pippo;Initial Catalog=ristobar;"
and I get the following error:
Number: 29022
Description:The version of the Microsoft OLE DB Provider for SQL Server is not correct. Install MDAC 2.8 or later.
HResult: 0x80004005
What do I have to install now?
Thanks for your kind help
Cristian
|||OK, now we are getting closer
You need to install the latest version of the SQL Compact Server Tools, build 5300. See this post:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1368182&SiteID=1 for installtion tips
(install the msi from an elevated command prompt!)
The latest version of the tools can be downloaded from http://www.microsoft.com/downloads/details.aspx?FamilyID=4e45f676-e69a-4f7f-a016-c1585acf4310&displaylang=en
After installation, the sqlcesa30.dll file should be build version 5300.
Also see this KB article, under the heading: Known issues for SQL Server Compact Edition 3.1 on Windows Vista
http://support.microsoft.com/?kbid=920700
|||Thanks again for your help.
I will try to install SQL Compact Server Tools at the link you specified from the command prompt (and will look to known issues on Vista)
Anyway I was pretty sure to have already installed that build...perhaps not in the right way.
If I am getting the same error after the installation sould I install MRDAC 2.8 or MRDAC 2.8 SP1 to solve this issue?
Cristian
|||MDAC is not supported on Vista, so no.|||So we must succeed|||...done!
Now rda.pull is working properly: the table is downloaded correctly into the file ristobar.sdf.
As I can see on Query Analyzer all records are there.
Anyway I still got an issue regarding rda.dispose() : it crashes the emulator.
The pull command is executed as below:
rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOff)
MessageBox.Show ("Done!")
breakpoint rda.dispose ()
If I remove the breakpoint or the corresponding line of program the emulator won't crash, otherwise It will got stuck and will have to be shut down.
What should it depend on? A misconfigured time-out on SQL Server Express or on IIS ?
Thank you!
Cristian
|||Try to catch any exceptions as per the pattern in this: http://msdn2.microsoft.com/en-us/library/swwy8y74.aspx
To get all the error information look at this sample: http://msdn2.microsoft.com/en-us/library/ms174079.aspx
|||Well, having read your suggestion I changed my code a little.I'm executing the following subroutine to manage the rda.pull and to catch possible exceptions. Unfortunately the emulator still get stuck where highlighted.
Thanks
Cristian
Private Sub RdaPull()
Dim rdaOleDbConnectionString As String = "Provider=sqloledb;Data Source=localhost\SQLEXPRESS;User ID=MobileUser;Password=pippo;Initial Catalog=ristobar;Persist Security Info=True;"
Dim rdaUrl As String = "http://192.168.0.199/ristobardb2/sqlcesa30.dll"
Dim localConnection As String = "Data Source=\My Documents\Rdadb.sdf"
Dim rda As SqlCeRemoteDataAccess = Nothing
Try
If System.IO.File.Exists("\My Documents\Rdadb.sdf") Then System.IO.File.Delete("\My Documents\Rdadb.sdf")
Dim engine As New SqlCeEngine
engine.LocalConnectionString = localConnection
engine.CreateDatabase()
engine.Dispose()
rda = New SqlCeRemoteDataAccess(rdaUrl, localConnection)
rda.InternetUrl = rdaUrl
rda.LocalConnectionString = localConnection
rda.Pull("customers", "select * from customers", rdaOleDbConnectionString, RdaTrackOption.TrackingOff)
MessageBox.Show("Done!")
Catch ex As System.Data.SqlServerCe.SqlCeException
MessageBox.Show(ex.Source)
MessageBox.Show(ex.NativeError)
MessageBox.Show(ex.Message)
MessageBox.Show(Hex(ex.HResult))
Finally
rda.Dispose()
End Try|||
Is the sub that I posted well formed?
Subscribe to:
Posts (Atom)