August 14, 2014 at 9:24 am
What is the correct syntax to use when passing parameters to a stored procedure through OPENQUERY?
an example..
SELECT * FROM OPENQUERY ([10.100.1.1], 'exec mydb.dbo.mysp @p1=mytable, @p2=0.0025,@p3 =''3476,6673,3321''')
i have tried different combinations like placing the sql in quotes, the sp parameters in quotes, with and without prefixing each parameter with @parametername=
I also have to pass a parameter which is a comma-separated string @p3
Many thanks!
August 14, 2014 at 9:40 am
Have you tried using a variable to contain the EXEC statement? Also, what results are you getting or not getting? Any errors ?
EDIT: Hold the phone... NIX the variable. BOL says you can't do that. Looks like we're back to "What problem are you actually having?"
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 14, 2014 at 9:51 am
That looks fine, unless you're trying to reference the return values, in which case you need to add an alias after the OPENQUERY(...) AS alias_name.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 14, 2014 at 10:41 am
here is a full example
declare @table_name varchar(100) = 'GLOBAL'
declare @date varchar(100) = 'OBJECTIDLIST'
declare @reduce varchar(10) = '0.0025'
declare @oid nvarchar(max) = '3476,6673'
declare @cmd nvarchar(max)
declare @sql nvarchar(max)
--set @sql = 'exec sde_Stage.dbo._aaa ' + '''' + @table_name + '''' + ',' + '''' + @date + '''' + ',' + '''' + @reduce + '''' + ',' + '''' + @oid + ''''
set @sql = 'EXEC sde_Stage.dbo._aaa ' + char(39) + char(39) + '@table_name = ' + @table_name + char(39) + char(39)+ ',' + char(39)+ char(39) + '@date =' + @date + char(39) + char(39) + ','
+ char(39) + char(39) + '@reduce = '+ @reduce + char(39) +char(39) + ',' + char(39)+ char(39) + '@oid ='+ @oid + char(39) + char(39)
print @sql
SET @cmd = N'SELECT * FROM OPENQUERY ([10.100.1.1], ' + char(39) + @sql + char(39) + ')'
print @cmd
EXEC sp_executesql @cmd
the output from this is:
EXEC sde_Stage.dbo._aaa ''@table_name = GLOBAL'',''@date =OBJECTIDLIST'',''@reduce = 0.0025'',''@oid =3476,6673''
SELECT * FROM OPENQUERY ([10.100.1.1], 'EXEC sde_Stage.dbo._aaa ''@table_name = GLOBAL'',''@date =OBJECTIDLIST'',''@reduce = 0.0025'',''@oid =3476,6673''')
Msg 137, Level 15, State 2, Line 1
Must declare the scalar variable "@reduce".
If the SP is instead run on the server 10.100.1.1 instead of through OPENQUERY it works using:
exec sde_Stage.dbo._aaa 'GLOBAL','OBJECTIDLIST','0.0025','3476,6673'
and returns a table
August 14, 2014 at 11:36 am
You don't want quotes around the variable names, just the values, if they are not numeric:
SELECT * FROM OPENQUERY ([10.100.1.1], 'exec mydb.dbo.mysp @p1=''mytable'', @p2=0.0025,@p3 =''3476,6673,3321''')
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 14, 2014 at 12:20 pm
Thank you All.
This is SQL query that is getting executing in L1 (linked server).
DELETE a
FROM L2.dbname.dbo.Remote_Table1 a
WHERE a.ID = @ID
AND EXISTS
(SELECT b.invoiceId
FROM Local_Table1 b
INNER JOIN Local_Table2 c ON c.Idno = b.Idno
WHERE a.ProcessID = @ProcessID
AND b.invoiceId = a.invoiceId
)
I replaced this code was
Exec L2.dbname.dbo.delete_remote_table1 @ID, @invoiceIds
Note:
@ID INT, @invoiceIds VARCHAR(MAX)
Remote_Table1 is a heavily used table lot of DML operations happens.
Thank you.
August 14, 2014 at 1:18 pm
I don't believe you can use varchar(max) across linked servers; try varchar(8000).
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 14, 2014 at 1:21 pm
ScottPletcher (8/14/2014)
I don't believe you can use varchar(max) across linked servers; try varchar(8000).
You can. I do it to replace the XML, which you can't pass.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
August 15, 2014 at 2:28 am
thanks for the help so far. I have tried reducing nvarchar(max) to nvarchar(4000), quotes around parameter values except for @reduce with no quotes
declare @table_name varchar(100) = 'GLOBAL'
declare @date varchar(100) = 'OBJECTIDLIST'
declare @reduce varchar(10) = '0.0025'
declare @oid nvarchar(4000) = '3476,6673'
declare@cmd nvarchar(4000)
declare @sql nvarchar(4000)
set @sql = 'EXEC sde_Stage.dbo._aaa ' + '@table_name = ' + char(39) + char(39) + @table_name + char(39) + char(39)+ ',' + '@date = ' + char(39)+ char(39) + @date + char(39) + char(39) + ','
+ '@reduce = ' + @reduce + ',' + '@oid ='+ char(39)+ char(39) + @oid + char(39) + char(39)
print @sql
SET @cmd = N'SELECT * FROM OPENQUERY ([10.100.1.1], ' + char(39) + @sql + char(39) + ')'
print @cmd
EXEC sp_executesql @cmd
this is the latest syntax error:
EXEC sde_Stage.dbo._aaa @table_name = ''GLOBAL'',@date = ''OBJECTIDLIST'',@reduce = 0.0025,@oid =''3476,6673''
SELECT * FROM OPENQUERY ([10.100.1.40], 'EXEC sde_Stage.dbo._aaa @table_name = ''GLOBAL'',@date = ''OBJECTIDLIST'',@reduce = 0.0025,@oid =''3476,6673''')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'OBJECTIDLIST'.
August 15, 2014 at 6:35 am
Try this:
declare @table_name varchar(100) = 'GLOBAL'
declare @date varchar(100) = 'OBJECTIDLIST'
declare @reduce varchar(10) = '0.0025'
declare @oid nvarchar(4000) = '3476,6673'
declare@cmd nvarchar(4000)
declare @sql nvarchar(4000)
set @sql = 'EXEC sde_Stage.dbo._aaa ' + '@table_name = ' + char(39) + @table_name + char(39)+ ',' + '@date = ' +
char(39) + @date + char(39) + ',' + '@reduce = ' + @reduce + ',' + '@oid =' + char(39) + @oid + char(39)
print @sql
SET @cmd = N'SELECT * FROM OPENQUERY ([10.100.1.1], ' + char(39) + @sql + char(39) + ')'
print @cmd
EXEC sp_executesql @cmd
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2014 at 7:25 am
sgmunson (8/15/2014)
Try this:
declare @table_name varchar(100) = 'GLOBAL'
declare @date varchar(100) = 'OBJECTIDLIST'
declare @reduce varchar(10) = '0.0025'
declare @oid nvarchar(4000) = '3476,6673'
declare@cmd nvarchar(4000)
declare @sql nvarchar(4000)
set @sql = 'EXEC sde_Stage.dbo._aaa ' + '@table_name = ' + char(39) + @table_name + char(39)+ ',' + '@date = ' +
char(39) + @date + char(39) + ',' + '@reduce = ' + @reduce + ',' + '@oid =' + char(39) + @oid + char(39)
print @sql
SET @cmd = N'SELECT * FROM OPENQUERY ([10.100.1.1], ' + char(39) + @sql + char(39) + ')'
print @cmd
EXEC sp_executesql @cmd
thanks for the suggestion. but reducing the number of quotes only moves the syntax error onto another parameter:
EXEC sde_Stage.dbo._aaa @table_name = 'GLOBAL',@date = 'OBJECTIDLIST',@reduce = 0.0025,@oid ='3476,6673'
SELECT * FROM OPENQUERY ([10.100.1.1], 'EXEC sde_Stage.dbo._aaa @table_name = 'GLOBAL_GDE_PG090L_POLYGON',@date = 'OBJECTIDLIST',@reduce = 0.0025,@oid ='3476,6673'')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'GLOBAL'.
i tried a few other things like quotes around all/none, swapping the parameter order around, and changing the comma delimiter to something else for @oid. each time the inccorect syntax only moves onto different parameter!
August 15, 2014 at 8:30 am
Alex R (8/15/2014)
this is the latest syntax error:
EXEC sde_Stage.dbo._aaa @table_name = ''GLOBAL'',@date = ''OBJECTIDLIST'',@reduce = 0.0025,@oid =''3476,6673''
SELECT * FROM OPENQUERY ([10.100.1.40], 'EXEC sde_Stage.dbo._aaa @table_name = ''GLOBAL'',@date = ''OBJECTIDLIST'',@reduce = 0.0025,@oid =''3476,6673''')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'OBJECTIDLIST'.
The EXEC by itself is invalid syntax, because of the double quotes, but the "SELECT * FROM ..." syntax is fine. A syntax check works fine in SQL2008.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
August 15, 2014 at 9:28 am
Okay... got it solved. Try the following:
declare @table_name AS varchar(100) = 'GLOBAL'
declare @date AS varchar(100) = 'OBJECTIDLIST'
declare @reduce AS varchar(10) = '0.0025'
declare @oid AS nvarchar(4000) = '3476,6673'
declare@cmd AS nvarchar(4000)
declare @sql AS nvarchar(4000)
set @sql = 'EXEC sde_Stage.dbo._aaa ' + '@table_name = ''' + @table_name + ''', @date = ''' +
@date + ''', @reduce = ' + @reduce + ', @oid = ''' + @oid + ''''
print @sql
SET @cmd = N'SELECT * FROM OPENQUERY ([10.100.1.1], ''' + REPLACE(@sql, '''', '''''') + ''')'
print @cmd
EXEC sp_executesql @cmd
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 15, 2014 at 9:38 am
sgmunson (8/15/2014)
Okay... got it solved. Try the following:
declare @table_name AS varchar(100) = 'GLOBAL'
declare @date AS varchar(100) = 'OBJECTIDLIST'
declare @reduce AS varchar(10) = '0.0025'
declare @oid AS nvarchar(4000) = '3476,6673'
declare@cmd AS nvarchar(4000)
declare @sql AS nvarchar(4000)
set @sql = 'EXEC sde_Stage.dbo._aaa ' + '@table_name = ''' + @table_name + ''', @date = ''' +
@date + ''', @reduce = ' + @reduce + ', @oid = ''' + @oid + ''''
print @sql
SET @cmd = N'SELECT * FROM OPENQUERY ([10.100.1.1], ''' + REPLACE(@sql, '''', '''''') + ''')'
print @cmd
EXEC sp_executesql @cmd
thanks, i have tried this but this is the error now..
EXEC sde_Stage.dbo._aaa @table_name = 'GLOBAL', @date = 'OBJECTIDLIST', @reduce = 0.0025, @oid = '3476,6673'
SELECT * FROM OPENQUERY ([10.100.1.1], 'EXEC sde_Stage.dbo._aaa @table_name = ''GLOBAL'', @date = ''OBJECTIDLIST'', @reduce = 0.0025, @oid = ''3476,6673''')
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'OBJECTIDLIST'.
August 15, 2014 at 9:45 am
Are you sure that the @date parameter should have a character value of OBJECTIDLIST ? That's not a terribly well-named parameter if it should. I know that the code attempts to see if I have a linked server when I try to execute it, and I only fail on the fact that I don't have that linked server, nor that stored procedure. Please verify the value for that parameter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply