Showing posts with label handle. Show all posts
Showing posts with label handle. Show all posts

Wednesday, March 7, 2012

Best Method for Handling Long Strings in Stored Procedures?

What is the best way to handle long strings in stored procedures, especially strings that my exceed 8000 characters?

I have a procedure that retrieves a couple groups of sales records that require follow-up for each salesman. This information is then formatted into the body of an HTML email (the number of data items for each quote require the use of tables to be readable in the email) by appending the necessary HTML and record data to a VARCHAR(8000) variable which is ultimately used as the body of the email.

The procedure functions great but I have encountered two unexpected issues I need some help resolving.

1. How can I handle strings longer than 8000 characters? It looks like if I have more than about 12 – 15 records returned by the time I add the HTML I am exceeding this limit and the balance is just truncated.

2. There seem to be "breaks" that are inserted in string/variable based on criteria I haven't figured out. Occasionally these occur in the middle of one of the HTML tags and break the corresponding rendering. I have tried, with limited to success to prevent this by inserting CHAR(10) characters at appropriate places to keep line lengths to about 80 characters but that consumes valuable character space and seems to only be about 60% effective. Can anyone enlighten me on what is causing this?

Thanks for your help.For item 1 use the ntext field type. You can store an unlimited length text value in an ntext field. You can set and get data with an ntext field just like you would with a varchar. You can't use ntext fields quite like you would a character fields (e.g., you can't use it in a WHERE clause) but if you just want to save and retrieve it you should be ok.

For item 2 I don't know, I haven't encountered that problem.|||I am using a variable to hold the string in the stored proc as it is assembled. It was my understanding that text and ntext fields could not be used for variables. Are you creating a cursor with an ntext field or what?

What I am trying to do it insert some text into the variable (header) then append some more text (section header) then I iterate throught the results from the select statement appending each returned record to the variable. Then I append more text (2nd section header) and again iterate throught those select results and append each. Then I return this now very fat variable to the calling proc which uses it as the body of the email.

Is there a better approach for this type of task? Basicaally I am trying to dynamically build an email body that can exceed 8000 characters with some regularity.

Thanks,

--Marshall|||It might be easier to do the string building from a calling program (C#, VB.Net). I don't know of a way to concatenate ntext fields in a sproc. When I have used ntext fields they just pass into and out of a sproc. The sproc itself doesn't manipulate them.

Sunday, February 12, 2012

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but don
t
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you

begin transaction in a sp or not

Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
doesn't handle transactions at all. We'll be moving to SQL 2005 soon but dont
know when. I know I can use Try...Catch in SQL 2005 to do transactions or
actually handle it form the application.
My question is, should I use 'BEGIN TRANSACTION' in a sp or it automatically
runs as a transaction? Thank youIf you want transactional control, then you have to include all data
modification operations inside BEGIN TRAN and COMMIT TRAN. The stored
procedure by itself will not run as a transaction. You have to check for
errors after each data modification operation and decide wheter to continue
or rollback transaction. @.@.TRANCOUNT also comes in handy while handling
transactions. See SQL Server Books Online for more information.
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||Try catch has nothing to do with if a transaction is used or not.Whether you
wrap your sp code in a transaction or not depends on what you are doing with
it. If it only does a single insert, update or delete then there is little
point to it. If you modify multiple tables and need them to be
transactionally consistant that is a different story. You might want to have
a looka t this:
http://www.sommarskog.se/error-handling-I.html
Andrew J. Kelly SQL MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||First, TRY...CATCH, in itself, has nothing to do with a TRANSACTION. In SQL
2005, TRY...CATCH allows efficient code management to determine if parts of
the TRANSACTION succeed or fail. And you can use a TRANSACTION in SQL 2000.
You are best served by limiting TRANSACTION to stored procedures ONLY when
transaction control is required for the business needs.
Not every action requires a TRANSACTION. A 'set' of actions (several
INSERT/UPDATE/DELETE statements) that need to be 'all or nothing' should be
explicitly designated to execute in the context of a TRANSACTION.
You may wish to read more about TRANSACTIONs in Books on Line.
--
Arnie Rowland
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you|||"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you
Tranactions scoping is part of the busness logic of the applciation, and
should be implemented wherever the business logic lives. Limiting
transactions to inside stored procedures doesn't work if the application
needs to compose multiple stored procedure invocations into a single atomic
business operation.
David|||To add to the other responses, I suggest you specify SET XACT_ABORT ON when
the application is oblivious to explicit transactions in stored procedures.
This will automatically rollback the transaction and abort the batch in most
cases and avoid problems related to an open transaction following a command
timeout.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"tpp" <tpp@.discussions.microsoft.com> wrote in message
news:AB989028-45EE-4D6E-A346-C16C08227C31@.microsoft.com...
> Hey. I've SQL 2000 ent. ed. right now. The application is vb .net and it
> doesn't handle transactions at all. We'll be moving to SQL 2005 soon but
> dont
> know when. I know I can use Try...Catch in SQL 2005 to do transactions or
> actually handle it form the application.
> My question is, should I use 'BEGIN TRANSACTION' in a sp or it
> automatically
> runs as a transaction? Thank you