1

I'm writing an extended stored procedure for SQL Server in C++.

The user can provide OUT parameters which I can set using srv_paramsetoutput(). However, this seems to always fail when I try to set TEXT, VARCHAR(max) or NVARCHAR(max). Note that VARCHAR(max) is referred to internally as SRVBIGVARCHAR.

Is there a common mistake I could be making or are these datatypes inaccessible to xprocs when used as output parameters?

Robin
  • 698
  • 6
  • 25
  • Extended procedures are deprecated and will never support the new MAX types. Use CLR procedures. – Remus Rusanu Nov 05 '12 at 17:00
  • I love C# more than C++ (of course), but the install experience is better for xprocs. Plus, there's so much third party software that depends on them that there'll be around for a few years hopefully. – Robin Nov 13 '12 at 11:06
  • how can you say xp install is better than assembly? *XPs are not contained in db*. They don't travel with backup/restore, they're lost on failover! – Remus Rusanu Nov 13 '12 at 11:14
  • Ah, I hadn't considered db-scoped XPs. If the XP is actually going to work on data, then yes, I would go CLR. However, if my XP is doing something unrelated to a specific DB (like backup or monitoring software) then I'd have to arbitrarily choose master or have them make a special DB just to contain the mods. Additionally, despite the fact XPs are more dangerous, the t-sql to install one looks less scary than for CLR procs. – Robin Nov 13 '12 at 11:52

1 Answers1

0

The maximum length value that an extended stored procedure can return is 8000 (for Varbinary or Varchar) or 4000 (for nvarchar).

Neil Weicher
  • 2,370
  • 6
  • 34
  • 56
  • This question was about using 'max' datatypes in xprocs, not the maximum lengths that basic string types can have. – Robin Oct 07 '13 at 13:08
  • I have tried to do that, but it appears to be not supported, even though it is defined in the C/C++ Header Files. – Neil Weicher Oct 15 '13 at 08:42
  • Yes, that's what I found. I just put up with it and changed the SQL-side to use nvarchar(n) rather than nvarchar(max) before later deciding to move code out of SQL Server entirely! – Robin Oct 15 '13 at 10:59