sql server 2005 - Unicode Query in OpenRowSet -
i have stored procedure nvarchar parameters. want returned table using openrowset. when use following codes results incorrect
declare @id int declare @sql nvarchar(max) declare @p1 nvarchar(8) set @id=1 set @sql='select * openrowset( ''sqlncli'', ''driver={sql server};'', ''exec dbo.usp_test @p1 =''''' + @p1 + ''''')' exec(@sql)
the openrowset convert query varchar , doesn't execute procedure correctly. seems openrowset execute code this:
exec dbo.usp_test @p1='????????'
so results incorrect. there option in openrowset resolve problem?
when using nvarchar
data need add specific character (n
) before string values this:
declare @id int declare @sql nvarchar(max) declare @p1 nvarchar(8) set @id=1 set @sql = n'select * openrowset(''sqlncli'', ''driver={sql server};'', ''exec dbo.usp_test @p1 = n''' + @p1 + n''''')' exec(@sql)
note :
when have'''exec dbo.usp_test @p1 =''''' + 'p1' + ''''''
equal'exec dbo.usp_test @p1 =''p1''
wrong , should'''exec dbo.usp_test @p1 =''' + 'p1' + ''''''
'exec dbo.usp_test @p1 ='p1''
Comments
Post a Comment