I am using code below:

DECLARE @SQL NVARCHAR(1000), @ParmDefinition VARCHAR(100)
SET @ParmDefinition = N'@loginname varchar(30)'
SET @SQL = N'select * from tUser where loginname=@loginname'
EXEC sp_executesql @SQL, @ParmDefinition, @loginname = 'admin'

But this code returns Error numbered 214!
I need to use sp_executesql through the parameters like above. But
this sends an error.
What should I do?
Please help me, guys!

Orgil

Both the @stmt and the @params parameters of the sp_executesql procedure must 
be unicode, but you declared the variable that you pass as @params as 
varchar(100). Use nvarchar instead and make sure the variable is large enough 
to hold the entire parameter definition string.


ML

---
Matija Lah, SQL Server MVP
http://milambda.blogspot.com/

orgilhp (orgilhp@gmail.com) writes:
> I am using code below:
> 
> DECLARE @SQL NVARCHAR(1000), @ParmDefinition VARCHAR(100)
> SET @ParmDefinition = N'@loginname varchar(30)'
> SET @SQL = N'select * from tUser where loginname=@loginname'
> EXEC sp_executesql @SQL, @ParmDefinition, @loginname = 'admin'
> 
> But this code returns Error numbered 214!
> I need to use sp_executesql through the parameters like above. But
> this sends an error.
> What should I do?

Hm, did you try reading the error message:

  Server: Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1
  Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

But you are passing a varchar value.

Yeah, normally you have a implicit conversion from varchar to nvarchar,
but sp_executesql is a not a T-SQL procedure, but a kind of extended stored
procedure, and the rules for these are stricter.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx