passing more than 8,000 characters to a SP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

passing more than 8,000 characters to a SP

Author

Topic

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-08-07 : 16:29:13

Hi All,
how do i pass more than 8,000 varchar to a SP ???

thanks
Ehi

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-08-07 : 16:56:30

You could use a parameter of datatype VARCHAR(MAX) or TEXT.

CODO ERGO SUM

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-08-07 : 17:19:26

Well, how do i pass something like 80,000 characters to a SP ? Or more ?

Which is about 10 times the size of a max varchar field ?

thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-07 : 18:14:08

afrika,

varchar(max)'s "storage size is 2^31-1 bytes". That's way more than 8,000.

How many characters do you need?

Tara Kizer
http://weblogs.sqlteam.com/tarad/

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-08-08 : 08:30:49

Ok thanks,
will run a test and see.

Actually, am playing around with numbers on an sms gateway. Each number must have a maximum of 13 characters separated by a comma which makes it 14, So 500 numbers will be about 7000 characters.

We are looking to pass a maximum of about 10,000 numbers. Which will be about 140,000 characters.

Will run a test and see
thanks
Afrika

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-08 : 08:33:13

you can also use xml for this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-08-08 : 08:50:25

quote:
Originally posted by spirit1

you can also use xml for this.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

XML ??? how ?

We use XML on our frontend to talk to the remote server, but i recall you saying that XML in SQL 2K wasnt that reliable.

...but i ask, what are your suggestions.

thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-08 : 08:54:16

in sql 2k5 xml is totaly upgraded.
you now have a xml datatype.
this datatype for example has a nodes() function that can be used to change XML to relational data to which you can join to
all in one go.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2007-08-08 : 14:21:41

Oh brilliant.

Will get back to you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources