January 29, 2015 at 2:28 pm
On a related topic to my recent troubles, I have a Linked server updating MySQL via SQL 2005 Linked server. I have it working manually when I run it, or when it runs as a scheduled job.
But if the stored procedure gets invoked by the website application it fails. I trapped the errors in a TRY-CATCH block:
What permission might a web based, SQL login need ??
ERROR_NUMBER() = 7303
ERROR_SEVERITY(), = 16
ERROR_STATE() = 1
ERROR_PROCEDURE() = My_SP_Name
ERROR_LINE() = 90
ERROR_MESSAGE() = Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MYSQL_WEB_PROD".
I see some 2008 references to a folder needing permissions, but I don't have the same files on my 2005 server.
The stored procedure loads some variables from MSSQL tables, then inserts the variables into the MySQL table, so this is going from MSSQL to MySQL. Snippet:
set @cmd = ' INSERT INTO web_prod.wp_users
(id, user_login, user_pass, user_nicename, user_email, user_url, user_registered, user_activation_key, user_status, display_name)
VALUES (''' + @id + ''' , ''' + @Email1 + ''' , ''' + @userpass + ''' , ''' + @mem_nickname + ''' , ''' +
@Email1 + ''' , ''' + @url + ''' , ''' + @getdate-2 + ''' , ''' +
@SubDir + ''' , ''' + @ID + ''' , ''' + @mem_Name +''')'
EXECUTE (@cmd) AT MYSQL_WEB_PROD
Properties of the MSDASQL provider ?
Thoughts ??
January 30, 2015 at 4:54 pm
Server is running SQL 2005, so please post any replies in a new thread I created in the 2005 forum:
http://www.sqlservercentral.com/Forums/Topic1656437-146-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply