This works perfectly fine on my system. But I've learned from one client that such MDB files cannot be opened using MS Access '97. Why they're still using this old version is a great question but the customer is always right, right?!
Anyhow, I thought that I could just alter the number "4.0" to "3.5" or "3.0" but it doesn't work on my machine. I got to wondering though if it might work on a client's machine if they had that version of OLEDB data access components installed.
So I'm wondering how I can programmatically test for which version(s) of OLEDB are available to use?
Robert Werner
http://PocketPollster.com
Vancouver, BC, Canada
I just finished an OLEDB interface written in VS2005 C++ that works with Sql Server Compact Edition 3.1. My team now wants me to make my interface compatible with Sql Server 2000, 2005, and 2005 Express. My question is...what header/lib files do I need installed and in my stdafx.h to pull this off? Is there a particular order I need to include these files in?
Right now I'm just including the following for SSCE:
#include <ssceerr30.h>
#include <ssceoledb30.h>
Once I get the right includes figured out, the next step is figuring out what CLSID's I should use in the CoCreateInstance call. I *think* I'm supposed to use CLSID_SQLOLEDB for 2000, CLSID_SQLNCLI for 2005, and I haven't a clue for 2005 Express. I could use some clarification on this as well.
Thanks a ton in advance...I've always been impressed by the MSDN boards...
Is SQL Everywhere supporting for Windows CE 4.2, Windows CE 5.0? What processors are supported by SQL Everywhere? Now we are using RC1 version SQL everywhere and when Release builds will be available? What is the purpose of the following SQL everywhere runtime binary dlls. · Sqlceca30.dll · Sqlcecompact30.dll · Sqlceer30en.dll · Sqlceme30.dll · Sqlceoledb30.dll · Sqlceqp30.dll · Sqlcese30.dll · System.Data.SqlServerCe.dll Thanks, Rajendran.
I haven't been able to find a DMX query which will spit out the cases which support a particular association rule. I was hoping it would work sort of like drillthrough but show only the cases supporting a particular rule. Am I missing something?
What I ended up doing was extracting the itemsets of the rule from the model's content then running a SQL query to retrieve the cases that contain both the left-hand and right-hand itemset of the rule. I'm hoping there's a better way.
set rsSearch = Server.CreateObject("ADODB.Recordset") rsSearch.CursorLocation = adUseClient rsSearch.LockType = adLockBatchOptimistic
sSql = "some sql stmt" 'this works fine on its own
rsSearch.Open sSql, cnTraining
set rsSearch.ActiveConnection = nothing
cnTraining.Close set cnTraining = nothing
%>
My goal is to get a disconnected recordset. The problem here occurs when i try to use the adUseClient value (3) for the CursorLocation. If I don't use a 3 in the CursorLocation it works fine. Hoewever i'm almost certain i have to use the 3 in order to disconnect the recordset. Any ideas? Is my connection string not set up properly to get a disconnected recordset?
Is it possible to access a Derrvied Column from an OLE-DB Command? I have to Update a Table with a join and i need from the source Table columns which have to be pivoted before i can use it in the update Command.
Trying to do a update/insert from SQL 2005 query to Access 2003 linked table.
In the Script Transformation I get this error.
Unable to cast COM object of type 'System.__ComObject' to class type 'System.Data.OleDb.OleDbConnection'. Instances of types that represent COM components cannot be cast to types that do not represent COM components; however they can be cast to interfaces as long as the underlying COM component supports QueryInterface calls for the IID of the interface.
Destinatoin Oledb connection is Native OLEDB Jet 4 to Access 2003 database.
Private sqlConn As OleDb.OleDbConnection
Private sqlCmd As OleDb.OleDbCommand
Private sqlParam As OleDb.OleDbParameter
Private connstring As String
Public Overrides Sub AcquireConnections(ByVal Transaction As Object)
I have a stored proc that runs and produces an Excel 2007 file. I can run it fine within SQL Server Management Studio. Basically, this is a partial code in the stored proc:
-- export sql server table data to excel 2007
insert into OPENROWSET('microsoft.ace.oledb.12.0',
'Excel 12.0;Database=D: emp est2007.xlsx;',
'SELECT * FROM LicensesrRegion') select * from Licenses
BUT when I have an execute SQL task to run that stored proc in an SSIS package, I received the following error: Ad hoc access to OLE DB provider 'microsoft.ace.oledb.12.0' has been denied. You must access this provider through a linked server.
Please advise.
PS: I have to run that stored proc in an SSIS package because the SSIS package does some prereq stuff ( create folder, copy template to new Excel output file) then finally calls the aforementioned stored proc.
We are experiencing failures when accessing a datatype="Attachments" field in a query in an MS Access 2007 database using ACE ODBC or OLEDB drivers.
We are using an MS Access 2007 database ACE ODBC/OLEDB drivers installed (i.e. Office 2007, which installs these drivers) DB contains a table with a field of type "Attachments" (which is new in MS Access 2007) DB contains a query that selects the fields of the above table
Using ACE ODBC or ACE OLEDB drivers to display the table works fine. The "Attachments" field is displayed as the file name of the attachment(s).
However, using either ACE ODBC or ACE OLEDB to display the query (i.e. NOT the table) results in either incorrect results or unexpected failures of the ODBC/OLEDB drivers.
Error using ODBC (using "ODBC Test"): ============================= select * from Query1 does not give an error, but displays a "1" for the Attachment field. select Attachments from Query1 gives the following error: stmt: szSqlState = "HY000", *pfNativeError = -3087, *pcbErrorMsg = 97, *ColumnNumber = -2, *RowNumber = -2 MessageText = "[Microsoft][ODBC Microsoft Access Driver] Reserved error (|); there is no message for this error."
Error using OLEDB (using "RowSetViewer"): ================================ select * from Query1 gives the following error: Interface: Unknown Result: 0x0004001 = E_NOTIMPL FormatMessage: €śNot implemented File: F:DepotSQLVaultmdac28sdkSamplesoledb owsetviewerSDKobji386CRowset.cpp Line: 616
select Attachments from Query1 gives the following error: Interface: IID_ICommand Result: 0x0004005 = E_FAIL IErrorInfo: [0x0000f3f1] €śUnspecified error€? File: F:DepotSQLVaultmdac28sdkSamplesoledb owsetviewerSDKobji386CCommand.cpp Line: 439
If it would help to analyze the problem, I have a folder containing all pertinent files (bare-bone database, tools, instructions) to reproduce this. I could attach it as a ZIP file, if requested.
I have a linked server defined on a SQL Server 2005 SP2 standard edition server using the IBMDASQL OLEDB driver. The linked server has been defined and working for months when used from a SQL Server authenticated session. I started converting our developers to Windows Authentication and access to the linked server is denied when used from a Windows Authenticated session. Here are the error messages:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "IBMDASQL" for linked server "DB2ARUBA" reported an error. Access denied.
Msg 7350, Level 16, State 2, Line 1
Cannot get the column information from OLE DB provider "IBMDASQL" for linked server "DB2ARUBA".
The linked server security is defined to login to the IBM iSeries using a fixed user and password regardless of the login using the linked server.
In order to analyze the problem, I created one sysadmin SQL Server authenticated login and one sysadmin Windows Authenticated login. The Windows Authenticated login refers to a domain login having administrator priviledges on the local SQL Server box.
So, when I connect to SQL Server using the SQL Authenticated login, a query against the linked server works. When I connect to SQL Server using the Windows Authenticated login, the exact same query against the linked server fails with the messages above.
I am try to transfer some tables data from one database server into another database server. I create a package in SSIS, and I use a variable to pass each table name. In Data flow, I use a OLEDB Source, but I cannot set the Data access mode to Table name or view name variable. Ever time, I will get this following error info "===================================
Error at Data Flow Task [OLE DB Source [31]]: A destination table name has not been provided.
(Microsoft Visual Studio)
===================================
Exception from HRESULT: 0xC0202042 (Microsoft.SqlServer.DTSPipelineWrap)
------------------------------ Program Location:
at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.ReinitializeMetaData() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowComponentUI.ReinitializeMetadata() at Microsoft.DataTransformationServices.DataFlowUI.DataFlowAdapterUI.connectionPage_SaveConnectionAttributes(Object sender, ConnectionAttributesEventArgs args)".
Some one can tell me what is the reason, or give me some examples.
We are setting up a new Reporting Services 2005 enterprise reporting tier that will support multiple developers, applications, and end users. We will have mirrored environments including development, test, and production each with their own database cluster, and reporting server.
We have multiple report developers who share a single Visual Studio solution which is saved in SourceSafe and is setup to have separate report projects for each business unit in the orgainzation. Each report project is mapped to a specific deployment folder matching the business unit. Using the Visual Studio Configuration Manager, we can simply flip to the envirnoment we want to deploy to and the reports are published to the correct environment and folder structure.
My problem lies with the common data sources. We are using a single master Common Data Sources folder to hold all of the data sources. The trick is that each and every reporting folder seems to have to have it's own copy of the data source in visual studio. There does not seem to be an easy way to change the data sources for the reports when you publish to various environment, i.e. development, test, production etc.
Ideally, we would have a single project for the common data sources that all reporting projects and associated folders would map to, and we would have a way to associate the appropriate data source for each environment when we deploy.
I'm looling for best practices on how to setup data sources for development and deployment in an enterprise environment that uses Visual Studio to develop and publish reports. We have 3 environments, and 6 data sources per environment and about 20 reporting folder / project in Visual Studio. That's 360 changes that have to be manged when deploying reports. Is there a best practices way to do this?
There has got to be a better way? Can anyone give me some insite into how to set this up?
I got an error when i do an OLE db Source pointing to an sql 2000 database and executing a sql query inside the OLE Source. The ole source will point to an OLE DB destination which is an sql 2005 database.
But i got the below error:
Error at Data Flow Task [OLE DB Destination [245]]: the column firstname cannot be processed because more than one code page (936 and 1252) are specified for it.
Error at Data Flow Task [DTS.Pipeline]: "component "OLE DB destination" (245)" failed validation and returned validation status "VS_ISBROKEN".
Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.
Error at Data Flow TaSK: There were errors during task validation.
We are in need of a solution to our problem. We are working for a customer and cannot use their database to work on during development. We have copied the layout and made a sample db for our development. Rather than writing down all the changes we make to our own db and then making the same changes to the customer's, is there a better way? Can we use something like sourcesafe? Thankyou
I've got a few SQL Servers at work which are development servers using SQL Server Standard Edition. I would like to downgrade them to SQL Server Developer Edition to save the Standard Edition licenses for production purposes. However, i do not wish to uninstall and reinstall SQL Server Developer Edition, restore all the dbs. Is there a way to change the version of the SQL Server ?
Hi All, Can we have 2 versions of SQL Server on the same server? Example, can I install SQL Server 2000 and 2005 on the same server? Please let me know about all the combinations possible/not possible.
Is there a way to do the following -- I need to be able to update a table based on a selection by a DDL. So here are my two update queries: 1. Update RequestData Set Approved = True Where DeptID = @DeptID2. Update RequestData Set Approved = True (Yes I need to be able to do all of the rows at once.) But it's all based on the selection of the DDL---I really don't want to have to create two seperate sql commands any ideas?
Is there a way to remove old versions of a DTS package without rewriting the entire package? These packages run fine but take up more space than I am willing to allocate.
I understand the SANs are only Supported by SQL Server 2000 Enterprise and no other versions of SQL, can anyone supply information to the contrary please?
This may seem like a wierd question, but can I have 6.5 and 7.0 running on the same machine? I need 6.5 for production builds and 7.0 for test. Is this possible? THanks
I can see that SQL server 2000 trial is available for download, but I need to try out 7 for a particular project at work. Is this available for trial download? (Has it ever been?)
Hi all... I'm a MySQL user and I want to switch to MS SQL Server, I heared that there is more than one type of MS SQL Server and one or two can be downloaded for free. Can someone tell me the different types of MS SQL Server and where can I download those free versions?
I'm running a SQL 7 db on a LAN in city A with other users accessing it fromanother city (city B) over a WAN. Obviously the WAN users are getting muchslower access to the data. One thing I was wondering was if it was possibleto have two versions of the database, one on each server, and have themautomatically synchronize as users at each location make changes. Or, ifthat's not possible, then at least have the city B users get any changesthat the city A users get as they are made.Thanks for any input.Neil
Is it possible to run both SQL200 standard and SQL2005 Express on thesame server? I need to test a database created in 2005 on ourproduction server but don't want to upgrade our "live" databases yet.
I uninstalled the 32-bit Standard version but during install of the 64-bit trial version I got an error that the integration services could not install because the 32-bit version of COM+ was already registered and you can't have both. Need some help how to resolve this issue. Thanks
I have a server with sql 2000 and 2005 installed on it. When I try to connect to the msde 2000 database with an app or with managment studio express I get the following error
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=-1&LinkId=20476"
But When I go through dos and type osql -S(local)database -E it connects to the correct msde instance. How can I fix this with out uninstalling sql server 2005????
