web service XML display limitation

  • (2 Pages)
  • +
  • 1
  • 2

15 Replies - 1445 Views - Last Post: 17 July 2013 - 07:13 AM Rate Topic: -----

#1 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

web service XML display limitation

Posted 10 July 2013 - 06:49 PM

hi guys, i have a 42,009 records in my database and i am trying to display all of the records using web service XML. but only 5,000 records is the return. is there a limit in displaying records? thanks.
Is This A Good Question/Topic? 0
  • +

Replies To: web service XML display limitation

#2 MrShoes   User is offline

  • D.I.C Addict
  • member icon

Reputation: 331
  • View blog
  • Posts: 512
  • Joined: 13-June 12

Re: web service XML display limitation

Posted 11 July 2013 - 12:33 AM

Is this helpful?
Was This Post Helpful? 0
  • +
  • -

#3 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 11 July 2013 - 02:55 AM

could you give me another source? it's not working on me. but thank you. i really appreciate the help :)
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6282
  • View blog
  • Posts: 21,600
  • Joined: 05-May 12

Re: web service XML display limitation

Posted 11 July 2013 - 05:35 AM

By any chance is the web service that you are calling fronting a SharePoint list? If so, you'll have to get around the SharePoint list throttling that was turned on by default in SharePoint 2010 and later.

http://msdn.microsof...y/ff798465.aspx
Was This Post Helpful? 0
  • +
  • -

#5 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: web service XML display limitation

Posted 11 July 2013 - 08:09 AM

My guess, based on previous topics, is that this is a CRM Dynamics organization service. RetrieveMultiple calls for CRM only return 5000 results. This is to prevent people from bogging down the network and database.

So, it's technically possible to increase this limit. It's not supported, but you can increase it. I don't recommend doing this, of course. If you need to deal with large amounts of data, go to the source. As long as all you're doing is querying and not Creating/Updating/Deleting records, just connect to the OrgName_MSCRM database and get your info from there. The schema should be fairly easy to figure out (there's a <LogicalName>Base and a <LogicalName>ExtensionBase table for each entity, join them on the <LogicalName>Id field. <LogicalName> is the Entity's logical name).

However, if you feel like you must increase the SDK's retrieval limit, you can follow the instructions in this link:

http://www.interacti...rieval-via-sdk/

According to him, that increases the limit to 20000. You still won't be able to get your entire 42k recordset. So I still suggest going to the database.
Was This Post Helpful? 1
  • +
  • -

#6 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 11 July 2013 - 06:11 PM

View PostCurtis Rutland, on 11 July 2013 - 08:09 AM, said:

My guess, based on previous topics, is that this is a CRM Dynamics organization service. RetrieveMultiple calls for CRM only return 5000 results. This is to prevent people from bogging down the network and database.

So, it's technically possible to increase this limit. It's not supported, but you can increase it. I don't recommend doing this, of course. If you need to deal with large amounts of data, go to the source. As long as all you're doing is querying and not Creating/Updating/Deleting records, just connect to the OrgName_MSCRM database and get your info from there. The schema should be fairly easy to figure out (there's a <LogicalName>Base and a <LogicalName>ExtensionBase table for each entity, join them on the <LogicalName>Id field. <LogicalName> is the Entity's logical name).

However, if you feel like you must increase the SDK's retrieval limit, you can follow the instructions in this link:

http://www.interacti...rieval-via-sdk/

According to him, that increases the limit to 20000. You still won't be able to get your entire 42k recordset. So I still suggest going to the database.


thank you! now atleast i know that it has only 5,000 limitation. but could you help me a bit more about your recommendation in database? sorry, i really am a newbie as i said in my previous posts but posting here and you guys helping me out is great.

 public List<Barangay> PushBarangayLibrary()
        {

            ClientCredentials Credentials = new ClientCredentials();
            Credentials.UserName.UserName = "username";
            Credentials.UserName.Password = "password";

            Uri OrganizationUri = new Uri("http://escms-crm/eSCMS/XRMServices/2011/Organization.svc");
            Uri HomeRealmUri = null;

            Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;



            using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null))
            {

                IOrganizationService service = (IOrganizationService)serviceProxy;


                QueryExpression query = new QueryExpression
                {
                    EntityName = "new_lib_barangay",
                    ColumnSet = new ColumnSet("new_barangayname", "new_barangaycode", "new_citycode")
                };


                EntityCollection result1 = service.RetrieveMultiple(query);




                List<Barangay> barangayinfo = new List<Barangay>();


                foreach (var x in result1.Entities)
                {
                    Barangay barangay = new Barangay();
                    barangay.barangayname = x.Attributes["new_barangayname"].ToString();
                    barangay.BarangayCode = x.Attributes["new_barangaycode"].ToString();
                    barangay.CityCode = x.Attributes["new_citycode"].ToString();
                    barangayinfo.Add(barangay);

                }

                if (barangayinfo.Count == null)
                {
                    return null;
                }
                else
                {
                    return barangayinfo;
                }

            }


        }



here's my code and yes i am retrievemultiple. how can i change it to what you recommend? and where should i put the code? thanks a lot.
Was This Post Helpful? 0
  • +
  • -

#7 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 14 July 2013 - 07:50 PM

can someone please help me with this? thanks
Was This Post Helpful? 0
  • +
  • -

#8 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: web service XML display limitation

Posted 15 July 2013 - 07:03 AM

I guess first I'd need to know is this an "in the cloud" instance of CRM, or is it an on-premise install?
Was This Post Helpful? 0
  • +
  • -

#9 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 15 July 2013 - 08:22 PM

View PostCurtis Rutland, on 15 July 2013 - 07:03 AM, said:

I guess first I'd need to know is this an "in the cloud" instance of CRM, or is it an on-premise install?


it' on premise install.
Was This Post Helpful? 0
  • +
  • -

#10 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: web service XML display limitation

Posted 15 July 2013 - 08:49 PM

Well then, the database is on your servers. You look up a C# database tutorial, and you use that to query out your records. I've already explained which tables to look at. Just start digging in the DB until you get the query you need. Unfortunately, I can't show you sample code here. It's specific to what you need.
Was This Post Helpful? 0
  • +
  • -

#11 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 15 July 2013 - 08:50 PM

is this what i am going to do?
http://msdn.microsof...y/cc151204.aspx
Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: web service XML display limitation

Posted 15 July 2013 - 09:46 PM

You could try that, or you could query directly in the tables. As long as you're just doing SELECTs, you can't hurt anything (other than perhaps performance).
Was This Post Helpful? 0
  • +
  • -

#13 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 16 July 2013 - 12:59 AM

hi! i am trying this http://coderscooler....ng-ms-sql-in-c/ to solve my problem regarding the display limitation.

string connection = ConfigurationManager.AppSettings.Get("connection");
            SqlConnection myConnection = new SqlConnection(connection);
            //Authenticate using credentials of the logged in user;
            ClientCredentials Credentials = new ClientCredentials();
            Credentials.UserName.UserName = "sipag\\" + Username;
            Credentials.UserName.Password = Password;

            //This URL needs to be updated to match the servername and Organization for the environment.
            Uri OrganizationUri = new Uri("http://escms-crm/eSCMS/XRMServices/2011/Organization.svc");
            Uri HomeRealmUri = null;

            Credentials.Windows.ClientCredential = CredentialCache.DefaultNetworkCredentials;

            using (OrganizationServiceProxy serviceProxy = new OrganizationServiceProxy(OrganizationUri, HomeRealmUri, Credentials, null))
            {
                IOrganizationService service = (IOrganizationService)serviceProxy;

                SqlDataReader reader = null;
                List<Barangay> barangayinfo = new List<Barangay>();
                try
                {
                    if (myConnection.State.Equals(ConnectionState.Inactive))
                    {
                        myConnection.Open();
                    }

                    SqlCommand command = new SqlCommand("select new_BarangayCode, new_barangayname, new_CityCode from new_lib_barangay");
                    command.Connection = myConnection;
                    reader = command.ExecuteReader();

                    while (reader.Read())
                    {
                        Barangay barangay = new Barangay();
                        barangay.BarangayCode = reader["new_barangaycode"].ToString().Trim();
                        barangay.barangayname = reader["new_barangayname"].ToString().Trim();
                        barangay.CityCode = reader["new_citycode"].ToString().Trim();
                        barangayinfo.Add(barangay);
                    }
                }
                catch (Exception air)
                {
                    System.Console.WriteLine(air.ToString());
                    myConnection.Close();
                    return null;
                }
                myConnection.Close();
                return barangayinfo;
            }
        }



this is my code. but it doesn't return anything. and another thing in the link above it says that i need to configure something in web.config wherein i need to set something but my problem is i am using windows authentication and it doesn't require me a username or password. can someone help me out here? Thanks.
Was This Post Helpful? 0
  • +
  • -

#14 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 6282
  • View blog
  • Posts: 21,600
  • Joined: 05-May 12

Re: web service XML display limitation

Posted 16 July 2013 - 05:54 AM

It doesn't return anything? Or do you get the exception that was caught? If it doesn't return anything then there's a problem with the SQL query itself. If your getting an exception, the error message in the exception should tell you what the issue is. If you post that error, we maybe able to give you more detailed advice.
Was This Post Helpful? 0
  • +
  • -

#15 johnedcel   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 36
  • Joined: 01-July 13

Re: web service XML display limitation

Posted 16 July 2013 - 07:15 PM

i already find a solution with the problem after 4 days. it works fine when i test it in the server. but when i publish it and try at soap this error occur
Login failed for user 'SIPAG\ESCMS-AD$'.


can somebody help me with this?
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2