rhoenig

I'm trying to return data from a SQL 2000 db using a BDC. I can import it successfully and it looks like it's going to work but I get an error...

Input string had an incorrect format.

The column headers are there and it looks like it connects but won't return any data. The only thing I've found is where I shoudl clear the inputs but I don't have any inputs. I'm just doing a Select * from table and want to return the whole recordset for now. Once I can do that then I'll worry about putting in parameters.

Here is the Application Definition I'm passing... Any suggestions

<Code Snippet>
< xml version="1.0" encoding="utf-8" standalone="yes" >
<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsiTongue TiedchemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database"
Version="1.0.0.0" Name="EmployeeSearch"
xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">
<LobSystemInstances>
<LobSystemInstance Name="EmployeeSearch">
<Properties>
<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>
<Property Name="AuthenticationMode" Type="System.String">WindowsCredentials</Property>
<Property Name="SsoApplicationId" Type="System.String">Employee</Property>
<Property Name="RdbConnection Data Source" Type="System.String">SERVERNAME</Property>
<Property Name="RdbConnection Initial Catalog" Type="System.String">DATABASENAME</Property>
<Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>
</Properties>
</LobSystemInstance>
</LobSystemInstances>
<Entities>
<Entity EstimatedInstanceCount="10000" Name="Employees">
<Properties>
<Property Name="Emp Name" Type="System.String">Name</Property>
</Properties>
<Identifiers>
<Identifier Name="Emp ID" TypeName="System.String" />
<Identifier Name="Emp Name" TypeName="System.String" />
</Identifiers>
<Methods>
<Method Name="GetEmployees">
<Properties>
<Property Name="RdbCommandText" Type="System.String">
SELECT * FROM Employees
</Property>
<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>
</Properties>
<FilterDescriptors>
<FilterDescriptor Type="Wildcard" Name="Emp Name">
<Properties>
<Property Name="UsedForDisambiguation" Type="System.Boolean">true</Property>
</Properties>
</FilterDescriptor>
</FilterDescriptors>
<Parameters>
<Parameter Direction="Return" Name="Employees">
<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="EmployeeDataReader">
<TypeDescriptors>
<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EmployeeDataRecord">
<TypeDescriptors>
<TypeDescriptor TypeName="System.String" IdentifierName="Emp ID" Name="Emp ID">
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">ID</LocalizedDisplayName>
</LocalizedDisplayNames>
</TypeDescriptor>
<TypeDescriptor TypeName="System.String" Name="Emp Name" >
<LocalizedDisplayNames>
<LocalizedDisplayName LCID="1033">Name</LocalizedDisplayName>
</LocalizedDisplayNames>
<Properties>
<Property Name="DisplayByDefault" Type="System.Boolean">true</Property>
</Properties>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</TypeDescriptors>
</TypeDescriptor>
</Parameter>
</Parameters>
<MethodInstances>
<MethodInstance Name="ProductFinderInstance" Type="Finder" ReturnParameterName="Employees" />
<MethodInstance Name="ProductSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="Employees" />
</MethodInstances>
</Method>
</Methods>
</Entity>
</Entities>
</LobSystem>

</Code Snippet>

I'm also getting the following error in my error logs:

Error: Wildcard FilterDescriptor with Id '752' is associated with '0' TypeDescriptors. It can be associated with only one.

Thanks
Robert




Re: SharePoint - Business Data Catalog Input string error. BDC

Vansen

The advice i have for you is the following:

Delete your FilterDescriptor - as long as you have no input parameter you don't need one anyway. And it looks like that is a problem for BDC.

Another thing is your statement. If you do a "select *" you would have to define every single Column that gets returned by the Query (as far as i know !). So try to make you SQL Statement more specific. Do select the Primary Key Column only at first - In your return Parameter define only that single column.

... try again ...

Oliver





Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

Ok I removed the filterdiscriptor and changed the SQL statment. Still getting the error and now getting no header.

Here's the code again.

<code snippet>

< xml version="1.0" encoding="utf-8" standalone="yes" >

<LobSystem xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsiTongue TiedchemaLocation="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog BDCMetadata.xsd" Type="Database"

Version="1.0.0.0" Name="EmployeeSearch"

xmlns="http://schemas.microsoft.com/office/2006/03/BusinessDataCatalog">

<LobSystemInstances>

<LobSystemInstance Name="EmployeeSearch">

<Properties>

<Property Name="DatabaseAccessProvider" Type="System.String">SqlServer</Property>

<Property Name="AuthenticationMode" Type="System.String">WindowsCredentials</Property>

<Property Name="SsoApplicationId" Type="System.String">Employee</Property>

<Property Name="RdbConnection Data Source" Type="System.String">LSSINTRA</Property>

<Property Name="RdbConnection Initial Catalog" Type="System.String">Employees</Property>

<Property Name="SsoProviderImplementation" Type="System.String">Microsoft.SharePoint.Portal.SingleSignon.SpsSsoProvider, Microsoft.SharePoint.Portal.SingleSignon, Version=12.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c</Property>

</Properties>

</LobSystemInstance>

</LobSystemInstances>

<Entities>

<Entity EstimatedInstanceCount="10000" Name="Employees">

<Identifiers>

<Identifier Name="Emp ID" TypeName="System.String" />

</Identifiers>

<Methods>

<Method Name="GetEmployees">

<Properties>

<Property Name="RdbCommandText" Type="System.String">SELECT [Emp ID] FROM Employees</Property>

<Property Name="RdbCommandType" Type="System.Data.CommandType">Text</Property>

</Properties>

<Parameters>

<Parameter Direction="Return" Name="Employees">

<TypeDescriptor TypeName="System.Data.IDataReader, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" IsCollection="true" Name="EmployeeDataReader">

<TypeDescriptors>

<TypeDescriptor TypeName="System.Data.IDataRecord, System.Data, Version=2.0.3600.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" Name="EmployeeDataRecord">

<TypeDescriptors>

<TypeDescriptor TypeName="System.Int32" IdentifierName="Emp ID" Name="Emp ID" />

</TypeDescriptors>

</TypeDescriptor>

</TypeDescriptors>

</TypeDescriptor>

</Parameter>

</Parameters>

<MethodInstances>

<MethodInstance Name="ProductFinderInstance" Type="Finder" ReturnParameterName="Employees" />

<MethodInstance Name="ProductSpecificFinderInstance" Type="SpecificFinder" ReturnParameterName="Employees" />

</MethodInstances>

</Method>

</Methods>

</Entity>

</Entities>

</LobSystem>

</code snippet>

Any other suggestions Is it possible it doesn't like my field name Emp ID. I didn't create the db but wish they would have not used spaces.

Thanks

Robert






Re: SharePoint - Business Data Catalog Input string error. BDC

Vansen

Ok, did you try to execute the query form another application Just to make sure it works.

What is the exact error message you are getting. Is the error coming form the SQL Server or does BDC generate it actually

You could try rewrite your select Statement: SELECT [Emp ID] as EmpId from Employee. The Field EmpId would be the one you have to use inside your Parameter. That should overcome the problems that BDC might have with spaces...





Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

I tried modifying the SQL statement to be

SELECT [Emp ID] as EmpID FROM Employees

Still getting the same error. I did modify the parameter also to reflect this change. I don't get any other errors I checked the event logs and The Sharepoint logs and the only other one I can see if KeyNotFoundException but it doesn't look like it's related to this issue...






Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

I just looked at the table and there is no key field. Would this be a problem Or do I just need to change my definition a bit




Re: SharePoint - Business Data Catalog Input string error. BDC

Vansen

hmmm, I'm not sure but i think the table you are querying needs to have a unique key. The SDK says the following :

In the Business Data Catalog, Entity instances should have a persistent identity, such as a primary key, so that you can search for and locate them using the key (identifier).

It should be enough if the column is unique. Could you check for that

Could you post the error message that the log file says The KeyNotFoundException, too

I had real real big problems with KeyNotFoundExceptions in the past.......





Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

The column is not unique either. I'll have to get something added for that I think. That sounds like it's the problem. I'll get the full error and post it also.


Thanks for your help.






Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

From what I can tell here is the error I'm getting.

Exception System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.ThrowHelper.ThrowKeyNotFoundException() at System.Collections.Generic.Dictionary`2.get_Item(TKey key) at Microsoft.SharePoint.Portal.WebControls.BusinessDataListWebPart.CreateChildControls()






Re: SharePoint - Business Data Catalog Input string error. BDC

Erulu

I'm a BDC newbie, too but with some (more or less painful) experience. I'll try to give you some hints where my stumbling blocks were. I'm not sure if it works and everything is really necessary but try this:

- Is [Emp ID] a primary key If not: Add a primary key to your DB (I've had serious trouble with uniqueidentifier so I won't use this data type!)

- Test the sql statement (e.g. in the Management Studio). Does it return a correct result set

- decrease (and later increase) the complexity of the ADF and the database





Re: SharePoint - Business Data Catalog Input string error. BDC

Vansen

Hi,

i would suggest you create an appropiate Table in your Database with a primary key. Then do only tweak the your xml as far as you have to and give it a try. I would also agree to Erulu that that might be the problem. I had some hard time myself to figure out what exactly a KeyNotFoundException means...

Have a try,

Oliver





Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

I'm now seeing this in my logs.

Exception handed to HandleRuntimeException.HandleException System.FormatException: The username returned by the Single Sign-on Provider is not of the form 'Domain\User'. at Microsoft.Office.Server.ApplicationRegistry.Infrastructure.WindowsAuthenticator.ExecuteAfterLogonUser(Object[] args, ISsoProvider ssoProvider, String ssoApplicationId, Boolean useSensitiveSsoCreds) at Microsoft.Office.Server.ApplicationRegistry.Infrastructure.WindowsAuthenticator.ExecuteAfterLogonUser(Object[] args, ISsoProvider ssoProvider, String ssoApplicationId) at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbConnectionManager.GetConnection() at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(MethodInstance methodInstance, LobSystemInstan...
04/02/2007 11:57:24.92* w3wp.exe (0x0E80) 0x0D4C SharePoint Portal Server Business Data 79fm High ...ce lobSystemInstance, Object[]& args) at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.DataClass.ExecuteInternal(LobSystemInstance lobSystemInstance, LobSystem lobSystem, MethodInstance methodInstanceToExecute, Method methodToExecute, ParameterCollection inputParameters, Object[]& overrideArgs) at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Entity.ExecuteInternal(LobSystemInstance lobSystemInstance, LobSystem lobSystem, MethodInstance methodInstanceToExecute, Method methodToExecute, ParameterCollection inputParameters, Object[]& overrideArgs) at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Entity.FindFiltered(FilterCollection filterCollection, LobSystemInstance lobSystemInstance) at Microsoft.SharePoint.Portal.WebControls.BdcDataSource...
04/02/2007 11:57:24.92* w3wp.exe (0x0E80) 0x0D4C SharePoint Portal Server Business Data 79fm High ...View.GetList(Entity entity) at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.GetEntityInstances(Entity entity) at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.ExecuteSelect(DataSourceSelectArguments args) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal() at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator() at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator(IDataSource datasource, Boolean originalData) at Microsoft.SharePoint.WebPartPages.DataFormWebPart.GetXPathNavigator(String viewPath) at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndP...
04/02/2007 11:57:24.92* w3wp.exe (0x0E80) 0x0D4C SharePoint Portal Server Business Data 79fm High ...erformTransform()

I recreated the db on the server that sharepoint is running on with only 2 of the columns. One of which is a pk field. Any suggestions

Robert






Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

I'm now seeing this in my logs.

Exception handed to HandleRuntimeException.HandleException System.FormatException: The username returned by the Single Sign-on Provider is not of the form 'Domain\User'. at Microsoft.Office.Server.ApplicationRegistry.Infrastructure.WindowsAuthenticator.ExecuteAfterLogonUser(Object[] args, ISsoProvider ssoProvider, String ssoApplicationId, Boolean useSensitiveSsoCreds) at Microsoft.Office.Server.ApplicationRegistry.Infrastructure.WindowsAuthenticator.ExecuteAfterLogonUser(Object[] args, ISsoProvider ssoProvider, String ssoApplicationId) at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbConnectionManager.GetConnection() at Microsoft.Office.Server.ApplicationRegistry.SystemSpecific.Db.DbSystemUtility.ExecuteStatic(MethodInstance methodInstance, LobSystemInstan...
04/02/2007 11:57:24.92* w3wp.exe (0x0E80) 0x0D4C SharePoint Portal Server Business Data 79fm High ...ce lobSystemInstance, Object[]& args) at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.DataClass.ExecuteInternal(LobSystemInstance lobSystemInstance, LobSystem lobSystem, MethodInstance methodInstanceToExecute, Method methodToExecute, ParameterCollection inputParameters, Object[]& overrideArgs) at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Entity.ExecuteInternal(LobSystemInstance lobSystemInstance, LobSystem lobSystem, MethodInstance methodInstanceToExecute, Method methodToExecute, ParameterCollection inputParameters, Object[]& overrideArgs) at Microsoft.Office.Server.ApplicationRegistry.MetadataModel.Entity.FindFiltered(FilterCollection filterCollection, LobSystemInstance lobSystemInstance) at Microsoft.SharePoint.Portal.WebControls.BdcDataSource...
04/02/2007 11:57:24.92* w3wp.exe (0x0E80) 0x0D4C SharePoint Portal Server Business Data 79fm High ...View.GetList(Entity entity) at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.GetEntityInstances(Entity entity) at Microsoft.SharePoint.Portal.WebControls.BdcDataSourceView.ExecuteSelect(DataSourceSelectArguments args) at System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigatorInternal() at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator() at Microsoft.SharePoint.WebControls.SingleDataSource.GetXPathNavigator(IDataSource datasource, Boolean originalData) at Microsoft.SharePoint.WebPartPages.DataFormWebPart.GetXPathNavigator(String viewPath) at Microsoft.SharePoint.WebPartPages.DataFormWebPart.PrepareAndP...
04/02/2007 11:57:24.92* w3wp.exe (0x0E80) 0x0D4C SharePoint Portal Server Business Data 79fm High ...erformTransform()

I recreated the db on the server that sharepoint is running on with only 2 of the columns. One of which is a pk field. Any suggestions

Robert






Re: SharePoint - Business Data Catalog Input string error. BDC

rhoenig

I got this working by using the BDC Meta Man utility.

Check it out here http://www.bdcmetaman.com/default.aspx

Very cool. I literally had it done in 5 minutes.

Robert






Re: SharePoint - Business Data Catalog Input string error. BDC

Vansen

Hi Robert,

at last Wink What table did you make it work against - the first one without the key Can you spot a difference that might have been the problem I'm just curios to find out about that keynotfoundexception...

Regards