Home Forums SQL Server 2005 Administering How to Create Linked Server for a MySQL database using SQL Server Management Studio RE: How to Create Linked Server for a MySQL database using SQL Server Management Studio

  • I figured out how to do this using variables. I used the sp_executesql and was able to return the new identity so that I can use it throughout my stored procedure.

    DECLARE @SQLStatement nvarchar(max)

    DECLARE @ID as nvarchar(200)

    DECLARE @NewID as int

    SET @ID = 10

    SET @SQLStatement = 'INSERT INTO SQLServerTableName(Col1, Col2, Col3, Col4, Col5)

    SELECT * FROM OPENQUERY(LinkedServerName, ''SELECT Col1, Col2, Col3, Col4, Col5 FROM MySQLTableName WHERE ID = ' + Cast(@ID as varchar)+''' ) '

    SET @SQLStatement = @SQLStatement + ' SELECT @NewID = SCOPE_IDENTITY()'

    EXECUTE sp_executesql @SQLStatement, N'@NewID INTEGER OUT', @NewID OUT


    Wendy Schuman