Difference between max length and length in DataType Schema

Using OleDb I can retrieve the Database Native DataTypes Schema with LoadSchema("DataTypes")

In the CreateParameters column some variable fields such as varchar have CreateParametera of 'max length' while nchar has a CreateParameters of 'length'.

Can someone differentiate between 'length' and 'max length' for me?

TVM

BOL: "nchar [ ( n ) ]

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes. The SQL-2003 synonyms for nchar are national char and national character.

varchar [ ( n | max ) ]

Variable-length, non-Unicode character data. n can be a value from 1 through 8,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size is the actual length of data entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for varchar are char varying or character varying."

so max length for nchar is 4,000.

for varchar(max) max length is 2^31-1 bytes depending on platform.

Thanks.

Determining output schema for a stored procedure

How would I go about retrieving this information without actually executing
the stored proc? Similar I guess to the .NET DataAdapter generation in the
configuration wizard. I've been tasked with scripting some home grown tests
against a rapidly changing set of procs. It was easy determining the parms
to setup for a call but without actually executing the proc I don't see how
to get the output schema...
SQL 2005 SP1/ WinXP SP2
Thanks all....It's not possible, unfortunately. The best things you can do are:
- Make sure to keep output tables CONSISTENT, and not change output format
depending on input arguments. For instance, the following would be a
worst-practice:
IF @.ReturnCounts = 0
BEGIN
SELECT
CustomerId,
CustomerName
FROM Customers
END
ELSE
BEGIN
SELECT
C.CustomerId,
C.CustomerName,
COUNT(*) AS SalesCount
FROM Customers C
JOIN Sales S ON C.CustomerId = S.CustomerId
END
- Document outputs as well as possible, and write unit tests that verify
them. Remember that each stored procedure's inputs and outputs define an
interface, and although there is no enforceable contract, you can enforce an
implied contract via tests.
... By the way, how are you planning to dynamically test? Are you just
looking for exceptions? How will you know if your test is actually
successful?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O%23QpqTwcGHA.1792@.TK2MSFTNGP03.phx.gbl...
> How would I go about retrieving this information without actually
> executing
> the stored proc? Similar I guess to the .NET DataAdapter generation in
> the
> configuration wizard. I've been tasked with scripting some home grown
> tests
> against a rapidly changing set of procs. It was easy determining the
> parms
> to setup for a call but without actually executing the proc I don't see
> how
> to get the output schema...
> SQL 2005 SP1/ WinXP SP2
> Thanks all....
>

Determining output schema for a stored procedure

How would I go about retrieving this information without actually executing
the stored proc? Similar I guess to the .NET DataAdapter generation in the
configuration wizard. I've been tasked with scripting some home grown tests
against a rapidly changing set of procs. It was easy determining the parms
to setup for a call but without actually executing the proc I don't see how
to get the output schema...
SQL 2005 SP1/ WinXP SP2
Thanks all....It's not possible, unfortunately. The best things you can do are:
- Make sure to keep output tables CONSISTENT, and not change output format
depending on input arguments. For instance, the following would be a
worst-practice:
IF @.ReturnCounts = 0
BEGIN
SELECT
CustomerId,
CustomerName
FROM Customers
END
ELSE
BEGIN
SELECT
C.CustomerId,
C.CustomerName,
COUNT(*) AS SalesCount
FROM Customers C
JOIN Sales S ON C.CustomerId = S.CustomerId
END
- Document outputs as well as possible, and write unit tests that verify
them. Remember that each stored procedure's inputs and outputs define an
interface, and although there is no enforceable contract, you can enforce an
implied contract via tests.
... By the way, how are you planning to dynamically test? Are you just
looking for exceptions? How will you know if your test is actually
successful?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:O%23QpqTwcGHA.1792@.TK2MSFTNGP03.phx.gbl...
> How would I go about retrieving this information without actually
> executing
> the stored proc? Similar I guess to the .NET DataAdapter generation in
> the
> configuration wizard. I've been tasked with scripting some home grown
> tests
> against a rapidly changing set of procs. It was easy determining the
> parms
> to setup for a call but without actually executing the proc I don't see
> how
> to get the output schema...
> SQL 2005 SP1/ WinXP SP2
> Thanks all....
>