CODEWITHSUNDEEP
stored-proceduressql-server-2012ssrs-2012reportbuilder3.0
300
300

Reputation: 1031

Is this a bug: stored procedure runs fine without parameters, causes error when parameters are added?

I have a stored procedure created in MySQL DB. This database is accessed as linked server through Microsoft SQL Server 2012 using Microsoft SQL Server Management Studio. The provider selected while creating linked server is "Microsoft OLE DB Provider for ODBC Drivers".

When I run following as text from Report builder 3.0 it runs fine and fetch data.

EXEC('CALL storedProcedureName(''string1'', ''string2'', ''string3'')') AT LinkedServerName;

But when I try to replace string1, string2, string3 with parameter name parameter1, parameter2, parameter3 as:

EXEC('CALL storedProcedureName(@parameter1, @parameter2, @parameter3)') AT LinkedServerName;

I get error:

Could not execute statement on remote server 'LinkedServerName'.
(Microsoft SQL Server, Error: 7215)

And when I try:

EXEC('CALL storedProcedureName('@parameter1', '@parameter2', '@parameter3')') AT LinkedServerName;

I get the prompt to enter values for parameter1, parameter2, parameter3. But when I enter the values and click ok, I get error:

Incorrect syntax near '@parameter1'. (Microsoft SQL Server, Error: 102)

Question: Am I missing something in syntax or is this a bug?

The linked server has:

"RPC" and "RPC out" set to True.

And the OLEDB provider has:

  1. Enabled allow inprocess
  2. Enabled dynamic parameter

Upvotes: 0

Views: 1183

Answers (2)

Cliff Buckley
Cliff Buckley

Reputation: 11

I know this is an older question but the accepted answer is open to SQL Injection and I think it's important to offer a more secure method.

You really want to use a parameterized query

EXEC('CALL storedProcedureName(?,?,?)',@parameter1, @parameter2, @parameter3) AT LinkedServerName;

I know this works for oracle and it should work for MySql as well.

Upvotes: 1

Jesse
Jesse

Reputation: 873

I believe you have to call it like the below. So params become strings wrapped in single quotes:

EXEC('CALL storedProcedureName('''+@parameter1+''', '''+@parameter2+''', '''+@parameter3+''')') AT LinkedServerName;

Upvotes: 1

Related Questions