I have setup a MYSQL server as a linked server in SQL SERVER 2000. I am able to select from a table using OPENQUERY as I understand that you can't use the 4 part qualifier to access an object from MYSQL as the ODBC driver does not expose the proper interface for this. The problem is that I cannot update,delete or insert into the OPENQUERY() table
eg.
UPDATE OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') SET id = 0
INSERT INTO OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') VALUES(0)
DELETE FROM OPENQUERY(MySQL_LinkedServer,'select id from msqlTableTest') WHERE id = 0
This is the OLEDB PROVIDER FOR ODBC error I am getting...
The requested operation could not be performed because the OLE DB provider 'MSDASQL' does not support the required transaction interface.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IUnknown::QueryInterface returned 0x80004002].
I know there are brilliant Professionals on this forum. Please tell me that this is just an MDAC upgrade or something that is not configured correctly...
Has anyone successfully used cherry's oledb provider for MYSQL to create a linked server from MS SQLserver 2005 to a Linux red hat platform running MYSQL.
I can not get it to work.
I've created a UDL which tests fine. it looks like this
[oledb]
; Everything after this line is an OLE DB initstring
- Microsoft SQL Server 2005 Standard Edition running on Microsoft Windows Server 2003 Standard Edition - Client Service for Netware installed. - Access 2000 database in a Novell Directory Services (NDS) Environment - SQL Server service running under local Administrator account.
Linked server configured as follow:
Linked server: MyDB Provider: Microsoft Jet 4.0 OLE DB Provider Product name: Access 2000 Data Source: UNC path to my Access database
Security: Admin with no password (default Access behaviour)
I can connect to my access database by following the next steps:
- Open Management studio on the SQL Server itself (where I'm logged in as Administrator) - Connect using Windows Authentication (Administrator) - Create a new query, run EXEC sp_tables_ex 'MyDB'
This will return all the tables from the Access database.
I can also make a view pointing to MyDB...MyTable.
Enough prove to me It can work....
So far so good
But now the tricky part.
When I login as 'sa', I can't connect and will receive the following message:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB" returned message "'Full UNC to my access database' is not a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides.".Msg 7303, Level 16, State 1, Procedure sp_tables_ex, Line 41Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MyDB".
(In real life I want to connect to my SQL 2005 Server from a ASP.NET (2.0) application using SQL authentication, but as long as I can't get the above working this is not important for now)
I have created a linked server to a MySQL database using a DSN. Does anyone know how to run a select statement against a table in the MySQL database? The usual 4 part naming convention does not work.
I can run SELECT * FROM OPENQUERY(Top50, 'SELECT * FROM orders') which does return records successfully. (Top50 being the name of the linked server)
My objective is to be able to insert records into the MySQL database and I am not sure how do to that using OPENQUERY. Any thoughts on that would be appreciated as well.
Anyone know how to go about getting data from MySql to MS SQL server? Im a little confussed as to what ODBC driver is needed and all that. Is it possible to this all through enterprise manager? Thanks a lot.
We are trying to do a linked server to MySQL from MS SQL2k. We downloaded MyODBC drivers, setup the system dsn successfully but then SQL errors out using both the GUI and the stored proc to add the linked server to mysql. Does anyone have a good site to reference or any words of advice. An hr or so of google didn't really give up any helpfully information.
I have a problem , I created a linked server to mysql with following parameters:
New linked server: ServerName
Server Type: Other data Source: Microsoft OLE DB Provider for ODBC Drivers.
In the Provider String:Driver={mySQL ODBC 3.51 driver};Server="ip";Port=3306;Option=17923;Stmt=;Database="database";Uid=root;Pwd="pwd"
And I created a ODBC with mysql, and connection worked sucessfully!
The connection worked corretly until I' ve running a transact query in sql query analyzer, thats connects to MySQL and...
when I try to connect in SQL Server 2000 standard Ed. in linked Server, the following message appears to me now, and before this I was able to connect!
Server: Msg 7399, Level 16, State 1, Line 11 OLE DB provider 'MSDASQL' reported an error. The provider reported an unexpected catastrophic failure. ODBC: Msg 0, Level 19, State 1 SqlDumpExceptionHandler: Process 169 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x8000ffff: The provider reported an unexpected catastrophic failure.
The query just make a select in MySQL, but I think that broke something...
I've installed MyODBC-3.51.06 and succsesfully created 2 odbc sources to the MySQL database with the ODBC data source administrator. One is a User DSN, the other a system DSN (Q1: what's the difference?) The test in that applet perfoms positively.
Running a dataimport of the tables using the import data option in enterprise manager does work as well.
Now for the part that gives me trouble. I would like to create a linked server using enterprise manager of SQL server 2000 (SQL 8.00.194) but I'm totaly stuck there. So Q2: how to get this set up?
I can create a linked server to another MsSQL from MsSQL,but encounter error when create a linked server to MySQL: Error 7399:OLE DB provider 'MSDASQL' reported an error. Data source name not found and no default driver specified. ......
I'm trying to use linked server to import big amount of data(around 9 million rows) from mysql to a sql 2005 table. I plan to create a stored procedure and use job agent to run it at off-peak time everyday. The SP is like:
..... Truncate table sqltblA;
Insert Into sqltblA(col1, col2, col3) Select col1, col2, col3 From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB') .....
But when I tried to CREATE the above SP from management studio, seems the sql server was trying to get all data from table mytblB and ended up with failure after several minutes hang. The error message is sth. like "running out memeory". BTW, during that several minutes hang, I can't query mytblB using mysql's tool as well. it seems to me that table got locked.
However if i try to change the returned result to 100,000 rows by changing script to
Insert Into sqltblA(col1, col2, col3) Select col1, col2, col3 From OpenQuery('mysql', 'Select col1, col2, col3 From mytblB Limit 100000') The SP could be created without any problem and running the SP could get the data in table sqltblA. But that's not what I want, I need all rows instead of a certain amount of rows.
Does anyone know what's going on there when I try to CREATE that SP and any solution to it?
Plus I tried to use SSIS as well. When I'm configuring the DataReader source, which uses ADO.NET's ODBC option to connect to mysql, if its sqlcommand is "select * from mytblB" without the Limit key word, then the configuration page will hang there as well and table mytblB is not accessible by using mysql tool. I'm just sick of wasting time on that and that's why I chose to use SP istead of SSIS. But same result. :-(
:confused: Hallo! I'm trying to link a MySql 's DB in SQLServer with ODBC: the operation was susseful but I'm not able to read and write/update the data. HELP ME PLEASE!!! silvia, Italy
We have SQL Server 2000 on a Windows 2000 server and a mySQL database running on a Windows 2000 server. We have used MyOLEDB driver(OLEDB) to link the mysql database. How can I access the data stored in a Linked server?
Any help would be appreciated. Thanks!!
this is our code¡G exec sp_addlinkedserver @server='OLEDB_test', @srvproduct=N'', @provider=N'MySQLProv', @datasrc=N'203.xx.xx.xx', @catalog = N'Store'
select * from OLEDB_test.Store.root.Books or select * from OLEDB_test.Store..Books
error message: Can't create OLE DB Provider 'MySQLProv' instance¡C OLE DB error trace [Non-interface error: CoCreate of DSO for MySQLProv returned 0x80040154]¡C
by the way¡G 1.We Create an ODBC System DSN that points to our MYSql server 2.We use OLEDB for ODBC to create a linked server to mySQL 3.We have got the query to work with openquery but we can't update¡Bdelete¡Bmodify the data stored in a Linked server
Hello,I have a problem. I've linked MySql server to MsSql, in MySql I have atable with Latvian data(character set is ucs2, ucs2_general_ci) andthe problem is that when I use openquery to read data from MySQLserver, some characters are not translated correctly! I receivequestion symbols instead of Latvian special characters.Maybe someone had this kind of problem with collation?
When executing a query which joins against a large resultset from a linked server, SQL Server reports the following error: Msg 7399, Level 16, State 1, Line 1 The OLE DB provider "MSDASQL" for linked server "foo" reported an error. The provider reported an unexpected catastrophic failure. Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "foo".
This is using SQL Server 2005 with SP2, and MyODBC version 3.51.16 (although previous versions also seem to have the same behavior).
Once the error occurs any subsequent query against the linked server (even something as simple as select top 3 * From foo...users) fails with the same error message. Queries using the OpenQuery syntax also fail with this error.
I've tried to determine if there are some error logs that might give me more insight into what's going on, but the above events don't seem to log to the ODBC trace logs. And I can't seem to figure out a way to trace the MSDASQL (i.e., oledb) events.
I initially thought there might be a problem with the MySQL myODBC driver, but the ODBC layer itself does not seem to have a problem. But the following steps indicate otherwise: 1. Create a new Linked Server to MySQL. Same error as above 2. Execute a query against the ODBC DSN directly, using a shell program. This works fine
I'm trying to clearly identify if the problem is with the MySQL driver or with the MSDASQL provider. It would appear that the latter is at fault, given the error message, and the lack of any ODBC logging.
Would anyone have any insights about what might be going on here, and if there is any possible resolution?
I have an internal Project Management and Scheduling app that I wrote internally for my company. It was written to use MySQL running on a Debian server, but I am going to move it to SQL Server 2000 and integrate it with our Accounting software. The part I am having trouble with is the user login portion. I previously used this:
PHP Code:
$sql = "SELECT * FROM users WHERE username = "$username" AND user_password = password("$password")";
Apparently the password() function is not available when accessing SQL Server via ODBC. Is there an equivalent function I could use isntead so the passwords arent plaintext in the database? I only have 15 people using the system so a blank pwd reset wouldn't be too much trouble.
Hi , On my Desktop i registered Production Server in Enterprise Manager on that Server if i go to SecurityLinked Servers There is another Server is already mapped, when i am trying to see the Tables under that one of the Linked Server i am getting the Error message saying that "Error 17 SQL Server does not exist or access denied"
if i went to Production Server location and if i try to see the tables i am able to see properly, no problems why i am not able to see from my Desk top i am using the sa user while mapping the Production Server on my DESKTOP using (ENTERPRISE MANAGER)
And i check the Client Network Utility in the Alias using Named Pipe only, i changed to TCP/IP still same problem What might the Problem how can i see the Tables in Linked Server from my DESKTOP
I am using Linked Server in SQL Server 2008R2 connecting to a couple of Linked Servers.
I was able to connect Linked Servers, but I cannot point to a specific database in a Linked Server, also, I cannot rename Linked Server's name.
How to point the linked server to a specific database? How to rename the Linked Server?
The following is the code that I am using right now:
USE [master] GO EXEC master.dbo.sp_addlinkedserver    @server = N'Machine123Instance456',    @srvproduct=N'SQL Server' ; GO EXEC sp_addlinkedsrvlogin 'Machine123Instance456', 'false', NULL, 'username', 'password' Â
hi all i try all date convertion function that u imagen in my asp.net project to insert a date to sqlserver datetime field but always sql server get the data as "1/1/1900 00:00:00" how can i fix that from sql server plz
I have Visual Studio 2005 Professional installed and SQL Server Express installed. In following along with the Microsoft Visual C# Step by Step book, there is a procedure on page xxi to get access to SQL Server Express. At the 2> prompt, however, when I type in go and then press enter, I get the following message:
Msg 15247 Level 16, State 1, Server MyServerNameSQLEXPRESS, Procedure sp_grantlogin, Line 13
User does not have permission to perform this action.
The computer is my own personal computer, with Window Vista installed and no previous versions of MSDE installed. I also have administrator persmissions. The only guidelines in the procedure regarding what to do in the event of an error message is to ensure that the commands were typed in correctly. What could be causing this message, since I do have administrator permissions?
For some reason I can't seem to be able to add SQL databases to VWD
I receive the following error message:
Connections to SQL Server Files (*.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL: http://go.microsoft.com/fwlink/?LinkId=49251
I have reinstalled SQL server but I still have the same problem, I tried tweaking some of the Server configurations without success.
I searched for hours on the web for answers but couldn't find any.
If anyone has any idea what could be the problem please help me.
My OS is Windows XP Professional SP2 IIS is installed.
I'm trying to configure a ReportServer in a Non-DefaltWebSite in my server. During the Reporting Server Configuration's process, everything works fine! All the informations are accept. But, when I try to access the address http://myserver.com/reportserver (or http://myserver.com/reports) a login message box is shown. I've already tried to enable a lot of user accounts, but with no success! I've read some articles that try to explain how to install a ReportServer in a Non-DefaultWebSite, but its doesn't work too.
Any sugestions?
My server is a Windows 2003 Standard Edition w/ SP1, IIS 6.0, SQL Server 2005 Express Edition Advanced Server.
Hi all, I am new to SQL Server but I'm trying to learn it.. :) I've created a database on my campus server. Now I want to configure it so that I can access the database from my laptop computer through internet. I have an account in the SQL server installed on my remote server. I can connect to the remote server through remote connection.
I'm new to SQL Server (and databases in general) so I don't know what question to ask. I'll just be explicit about what I want to see.
What I need is SQL Server Express to install silently embedded in my application's installation (The user may already have SQL Server Express installed).
Once this is complete, in the SQL Server Configuration Manager under SQL Server 2005 Network Configuration, there should be an entry: Protocols for SONICI with Shared Memory, Named Pipes, and TCP/IP all set to Enabled.
When logging into SQL Server Management Studio there should be a choice for COMPUTERNAMESONICI and there should be a new folder Program FilesMicrosoft SQL ServerMSSQL$SONICI.
Here's what my installer launches from the command prompt: SQLEXPR32.exe -q /norebootchk /qn reboot=ReallySuppress addlocal=all instancename=SONICI SQLAUTOSTART=1
It doesn't work. And if I remove the /qn and run it, I get the message: "Microsoft SQL Server 2005 Setup was unable to install on your computer."
The log file reads: Error: Action "LaunchLocalBootstrapAction" threw an exception during execution. Error information reported during run: "c:Program FilesMicrosoft SQL Server90Setup Bootstrapsetup.exe" finished and returned: 1603
I have no clue what is wrong and I can't find any helpful information on the web.
I have two webservers and another server where the database resides.. and in my webservers i have reporting serverices set up.. in my Report server configuration manager for Webserver 1, in the database setup tab i am specifying the remote the database server which i want to connect and i am providing the correct username and passwords to connect to the remote databse and when i specify the remote database username and password i can see all the databases listed in that server. So in server name i have specified the name of the sql server.. and in the Database name i have specified ReportServer and given credentails type as sql server credentials and given the correct username and password..
But when i give apply i am getting an warning or error saying that The database version (c.0.8.54) does not match the Report service installation. This version cannot be upgraded. You must use a different database.
and in internet explorer if i give http:\localhost eports I get an error saying The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is 'C.0.8.54'. The expected version is 'C.0.8.40'. To continue update the version of the report server database and verify access rights.
When i open the Reporting Services Configuration Manager on our newly installed SQL 2005 box i get a red X on "Web Service Identity".
The window in question states that "The Web Service runs under the ASP .NET Machine account under IIS 5 or ..." with what looks to be a text entry box that is grayed out with the label "ASP .NET Service Account. The text entry box that is grayed out is blank.
When the Apply button is pressed a Task Status box opens at the bottom that states "There was an error setting the identity for the web service. The previously set identity will still be used."
When the option for "Tell me more about the problem and how to resolve it." is pressed i get a dialog box that has two lines of text reading:
"ReportServicesConfigUI.WMIProvider.WMIProviderException: An unknown error has occured in the WMI provider. Error Code 80070005"
Our report server is non-functional at the moment, and we really need it to work ASAP. Any help would be appreciated, thanks!
*edit*
when i try to open http://localhost/reports i get the message that "The report server is not responding. Verify that the report server is running and can be accessed from this computer.
I have created profiles in my database as mentioned below. now i want to delete the profile created in database. can some one tell me how to proceed furthur.
i need to delete them permenantly. EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = 'MyMailAccount', @description = 'Mail account for Database Mail', @email_address = 'makclaire@optonline.net', @display_name = 'MyAccount', @username='makclaire@optonline.net', @password='abc123', @mailserver_name = 'mail.optonline.net' ----------------------------------------------------- EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'MyMailProfile', @description = 'Profile used for database mail' --------------------------------------------------------EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'MyMailProfile', @account_name = 'MyMailAccount', @sequence_number = 1
Attempting to link Access 2003 to SQL 2005, there is something keeping from doing so.
1 I setup the instance as sqlexpress.
2 I have a database shell there also.
3 It connects when I start the instance service.
4 My remote connections are configured to handle TCP/IP & Named Pipes.
Not in this order, but you get the idea. Now that I have these in place, I went first to the Access 2003 database to link the tables to this. When I came accross trouble in the form of need for a DSN to the domain for the things I created above.
In attempting to create one that looked functional, I came back to the same step. But, unsuccessful to link the tables again because browsing to that domain does not even show that particular file. This is the problem.
I do believe that once I am able to select a DSN, I will have a "healthy baby connection". I will then be the proud father of my first Server-side database setup!!!! But I am trying to get over these labor pains.
When I configure and test and OLEDB connection SQL Server 2005 keeps stopping with an error message as shown below. The problem seems to happen when I open the connection for ediing in Data Flow Window. There is a consistent pattern of errors (in there Event Log) relating to the Evnet Log prior to this error occurring.
Any idea what the problem is?
I have to say that my experience with SSIS has not been very encouraging so far.
Event Type: Error Event Source: MSSQL$SQL2005 Event Category: (2) Event ID: 17311 Date: 23/01/2007 Time: 2:06:41 PM Description: SQL Server is terminating because of fatal exception c0000005. This error may be caused by an unhandled Win32 or C++ exception, or by an access violation encountered during exception handling. Check the SQL error log for any related stack dumps or messages. This exception forces SQL Server to shutdown. To recover from this error, restart the server (unless SQLAgent is configured to auto restart).
We have push transaction replication from A database to 2 other B and C database. I have configured email to sent upon failure of subscription job of B and D database on Database A. Is this the way that we need to configure email to send when there is replication break up or failure.
Database is MS sql server 2008 R2 Publication Database: A Replication mode : Transaction Replication Replication used : Push Replication from database A Subscription Database: B and C
after i installed sql server 2005 DM add-ins, i tried using it with office 2007 so i choose (start---->all programs----->microsoft sql 2005 dm add-ins---->getting started) so the getting started wizzard was opened, i selected from it (use an existing instance of microsoft sql server 2005 analysis services that i adminster) because i already had a version of it installed on my computer. then i tried to connect to analysis services by selecting 'server name'=local host, an error appeared which is "unable to discover meta data on the server 'local host' . user noha needs to be adminstrator of the server to run the configuration tool". how can i be the adminstrator of the server?????????.........plz reply me soon,thanks in advance
