 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server CE / May 2008OLEDB Error
I'm accessing SQL Compact 3.5 database using OLEDB provider. pConnection->Open(L"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=c:\\db.sdf;Persist Security Info=False", L"", L"", -1) I'm trying to execute simple "select" query. _RecordsetPtr pRS(__uuidof(Recordset)); HRESULT hr = S_OK; hr = pRS->raw_Open(_variant_t(L"SELECT [Table_1].[Field_1] FROM [Table_1]"), _variant_t(pConnection), adOpenDynamic, adLockOptimistic, adCmdText); But an error (hr = DB_E_ERRORSOCCURRED) is returned if [Table_1]. [Field_1] has type nvarchar(128 or more). There is no error if [Table_1].[Field_1] has type nvarchar(127 or less). How can I resolve this problem? Thanks Stepan, This is a bug. I ran into this 18 months ago or so, sorry to see this is still not fixed. See the discussion http://groups.google.com/group/microsoft.public.sqlserver.ce/browse_thread/threa d/93ae1aded52d6020/3d6f1b571ff3a0e2?lnk=gst&q=128+erik#3d6f1b571ff3a0e2 regards, Erik > I'm accessing SQL Compact 3.5 database using OLEDB provider. > [quoted text clipped - 18 lines] > > Thanks Hi Erik, Have you tried this again with the SQL Compact sp1 beta? http://www.microsoft.com/downloads/details.aspx?FamilyId=68539FAE-CF03-4C3B-AEDA -769CC205FE5F&displaylang=en Ginny Caughey Device Application Development MVP > Stepan, > [quoted text clipped - 29 lines] >> >> Thanks Hi Ginny, Downloaded the SP1 beta, installed, tested and... no luck. I can update a nVarchar column > 127 without problems, but a query that include this column fails. Also the SQLCE OleDB provider does not show up in the listed OleDB provider on the Datalink wizard window. Still needs a manual patch in the registry to 'see' the provider. this key needs to be added: [HKEY_CLASSES_ROOT\CLSID\{F49C559D-E9E5-467C-8C18-3326AAE4EBCC}\OLE DB Provider] @="SQL CE OleDB Provider" Erik > Hi Erik, > [quoted text clipped - 37 lines] >>> >>> Thanks Thanks for trying this, Erik. I'll pass it along. Ginny > Hi Ginny, > [quoted text clipped - 54 lines] >>>> >>>> Thanks > Thanks for trying this, Erik. I'll pass it along. OK, Great, thanks Erik > Ginny > [quoted text clipped - 56 lines] >>>>> >>>>> Thanks Hi Erik, I seem to already have that registry key in my registry. What does adding it do for you? By the way, the OleDb support in SQL Compact doesn't implement the full OleDb standard so I suspect that is why you don't see the same wizard behavior you get with other providers. Ginny > Hi Ginny, > [quoted text clipped - 54 lines] >>>> >>>> Thanks Hi Ginny, Perhaps you have added this key in the past yourself? If I remove the complete key and repait the SqlCE installation, the OLE DB Provider-entry it is not added. It is a 'known' issue: For the previous version: http://groups.google.com/group/microsoft.public.sqlserver.ce/browse_thread/threa d/239c0b4bc6af8e6/8894f7eeabff88a6?lnk=st&q=oledb+erik+registry#8894f7eeabff88a6 and for the current:" http://coding.derkeiler.com/Archive/Delphi/borland.public.delphi.database.ado/20 08-02/msg00075.html If you have this VO code: LOCAL oConn AS AdoConnection oConn := AdoDriverSelect(SELF) oConn:Open(NIL,NIL,NIL,NIL) You'll get the Datalink Property dialog with list of installed Ado/OleDBProviders. To have the SQL CE provider listed here, it is neccesary to add this key. This problem was there in the 3.0 version and in the current. Erik > Hi Erik, > [quoted text clipped - 63 lines] >>>>> >>>>> Thanks Erik, I never added the key manually. Perhaps it was added on my machine when I installed VS 2008 sp1 beta, although I don't recall having any problem doing this with other versons of VS either. I am able to use the Add Data Source and Add Connection wizards in VS to add a SQL Compact database as a data source, for example, or are you referring to some other functionality you get by adding this registry key yourself? I assume that by removing the key and then repairing the SQL Compact installation not fixing it, that VS installs that key so perhaps a repair of VS might return it after it's removed. (Note that VS 2008 only supports SQL Compact 3.5 with data tools while VS 2005 only supports SQL Compact 3.1, but you can use either version of SQL Compact programmatically.) Ginny > Hi Ginny, > [quoted text clipped - 89 lines] >>>>>> >>>>>> Thanks Erik, I just reread your message and see you are trying to access SQL Compact from Visual Objects. If you don't also have Visual Studio installed on the computer, I assume that is why you are seeing that the key is missing. Ginny > Hi Ginny, > [quoted text clipped - 89 lines] >>>>>> >>>>>> Thanks Ginny, > I just reread your message and see you are trying to access SQL Compact > from Visual Objects. If you don't also have Visual Studio installed on the > computer, I assume that is why you are seeing that the key is missing. I do have VS2008 on my PC, but installation of SP1 beta failed on my PC, so perhaps that is the difference. Erik Could be, Erik. In any case, as far as I know we probably shouldn't expect changes in OleDb for SQL Compact from this point on since managed code is really where we're all moving. For your specific situation of integrating with Visual Objects, I've had good success building COM shims in C# (or you could use VB) and exposing managed code functionality to unmanaged apps that way. Or maybe you already have a workaround that is working for you. Ginny > Ginny, > [quoted text clipped - 7 lines] > > Erik Hi Ginny, IMO,If it is there, it should work. And it sure is a limitation I cannot use nCharvar columns > 127 and I do not see an easy workaround to this. Imagine using SSCE for Word Mailmerge. Right now (with the registry patch) you can use the OleDB provider and use the SDF as datasource directly. I am sure Office integration is something to consider? Erik > Could be, Erik. In any case, as far as I know we probably shouldn't expect > changes in OleDb for SQL Compact from this point on since managed code is [quoted text clipped - 17 lines] >> >> Erik Erik, I can use SSCE for Word Mailmerge without having to add the registry patch. I think you just have a bad installation. For nvarchar columns > 127, why not use ADO.NET? You can't do it in pure unmanaged code because of the >127 char problem on the select statement, but it should work fine if you put you write a data access layer in managed code, expose it via COM, and then you can use the COM component from unmanaged code. Ginny > Hi Ginny, > [quoted text clipped - 28 lines] >>> >>> Erik Ginny > I can use SSCE for Word Mailmerge without having to add the registry > patch. I think you just have a bad installation. I have just installed both Office and SSCE on a virtual machine. Tried to create a new dce for Word and the SSCE OleDB provider is not listed. > For nvarchar columns > 127, why not use ADO.NET? You can't do it in pure > unmanaged code because of the >127 char problem on the select statement, > but it should work fine if you put you write a data access layer in > managed code, expose it via COM, and then you can use the COM component > from unmanaged code. That should work, but just is to much trouble. I now just tell my customer nCharVar columns > 127 are not supported due to a bug in the OleDB provider. By the time they really need it, I think I will have to write that data access layer i guess. I lived with it for 18 months and I think I can live with it just a little longer.... Erik > Ginny > [quoted text clipped - 30 lines] >>>> >>>> Erik Erik, Are you creating a mail merge project using VS and VSTO or just using Word? It is possible to use a SQL Compact data source for a VSTO mail merge app in VS 2008 on my machine. If that also works on your machine but setting up mail merge with Word alone (rather than using a VSTO app) doesn't work, then I'd assume that some of the functionality that Word needs from OleDb providers simply isn't there for SQL Compact. If that's the case, I would not expect that situation to change, so I'd recommend the VSTO app approach instead. Ginny > Ginny > [quoted text clipped - 55 lines] >>>>> >>>>> Erik Ginny, It works fine, but only when the registry is patched the OleDB provider is listed in the Dataconnection Properties dialog. If the [OLE DB Provider] entry is in your registry and as you said you did not add it manualy , it must be added by VS SP1 Beta. I am not using Word mailmerge with SSCE at the moment, but I was giving it as a comment from a end user perspective on <snip> ... since managed code is really where we're all moving. </snip> SSCE via OLEDB is not only something that can be usefull for Delphi, C++ or VO developers, but also for Office end users. Advanced Word users like to be able to connect to data using the Dataconnection Properties dialog also, it is not something for developers only. Erik > Erik, > [quoted text clipped - 68 lines] >>>>>> >>>>>> Erik Hi Erik, I see your point about end users and agree that would be nice. Ginny > Ginny, > [quoted text clipped - 86 lines] >>>>>>> >>>>>>> Erik |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.