April 25, 2017 at 3:55 pm
I recently moved my database from an older version of MS SQL Express to MS SQL Express 2008 R2. I have one field that stores a large amount of text and HTML markup that I later use to include in a web page via Coldfusion. This field is set to datatype varChar(max).
The data is written to the database with a simple UPDATE query as follows:
Note the CAST function was just added while trying to sort this out
UPDATE KeyPostings
SET
PostingDate = '#PostingDate#',
Posting = CAST('#Posting#' AS varChar(Max)),
Author = '#Author#',
PostingDescription = '#PostingDescription #',
MenuPost = '#MenuPost#',
GATAPost = '#GATAPost#',
GATAPosted = '#GATAPosted#',
AuthorUserID = '#AuthorUserID #'
WHERE PostingID = '#PostingID#'
I have verified that the entire data is not being stored by running a query in SQL Manager to retreive the Len() of the field "posting." Once maxed out at arond 69988 subsequent saving of additional data (added to the end of the existing data in our form field) does not increase the Len reported.
All help greatly appreciated!!
April 25, 2017 at 4:27 pm
we normally use varbinary(max) to store large amounts of data.
ie:
insert into schema0.table1( Column00_INT, Column01_VBMAX)
select 1 as Column00_INT, replicate('*', 100000000000)
Then when we read back we normally use something like the below to view data:
select Column00_INT, cast( Column01_VBMAX as varchar(max)) to view the data.
in SSIS pakages, we use blobs and cast to create lengthy formatted emails.
April 25, 2017 at 4:51 pm
spitfire677 - Tuesday, April 25, 2017 3:55 PMI recently moved my database from an older version of MS SQL Express to MS SQL Express 2008 R2. I have one field that stores a large amount of text and HTML markup that I later use to include in a web page via Coldfusion. This field is set to datatype varChar(max).
The data is written to the database with a simple UPDATE query as follows:Note the CAST function was just added while trying to sort this out
UPDATE KeyPostings
SET
PostingDate = '#PostingDate#',
Posting = CAST('#Posting#' AS varChar(Max)),
Author = '#Author#',
PostingDescription = '#PostingDescription #',
MenuPost = '#MenuPost#',
GATAPost = '#GATAPost#',
GATAPosted = '#GATAPosted#',
AuthorUserID = '#AuthorUserID #'
WHERE PostingID = '#PostingID#'I have verified that the entire data is not being stored by running a query in SQL Manager to retreive the Len() of the field "posting." Once maxed out at arond 69988 subsequent saving of additional data (added to the end of the existing data in our form field) does not increase the Len reported.
All help greatly appreciated!!
SSMS may not return all the data to the grid when data is retrieved. This is done in SSMS, all your data is still in the database. Try checking the length of the data in each row for that column.
Edit: Missed where you said you did the length. You may need to verify the load process and the source of the data.
April 25, 2017 at 4:53 pm
Also, could you post the DDL for the table?
April 25, 2017 at 4:55 pm
How did you upgrade the database?
April 25, 2017 at 5:57 pm
Lynn Pettis - Tuesday, April 25, 2017 4:55 PMHow did you upgrade the database?
Hello Lynn, Thanks for spending your time to help me with this!
The process of uploading the data is just a big text field on a Coldfusion web page that submits to a processing page with the query I posted above.
As for the DDL for the table I am not familiar with how to retrieve that but will learn about it tonight.
To move the database I just did a full backup under SQL Server 2007 Express and then restored the backup to SQL Server 2008 that was installed on our new VPS hosting..
April 25, 2017 at 7:17 pm
spitfire677 - Tuesday, April 25, 2017 3:55 PMI recently moved my database from an older version of MS SQL Express to MS SQL Express 2008 R2. I have one field that stores a large amount of text and HTML markup that I later use to include in a web page via Coldfusion. This field is set to datatype varChar(max).
The data is written to the database with a simple UPDATE query as follows:Note the CAST function was just added while trying to sort this out
UPDATE KeyPostings
SET
PostingDate = '#PostingDate#',
Posting = CAST('#Posting#' AS varChar(Max)),
Author = '#Author#',
PostingDescription = '#PostingDescription #',
MenuPost = '#MenuPost#',
GATAPost = '#GATAPost#',
GATAPosted = '#GATAPosted#',
AuthorUserID = '#AuthorUserID #'
WHERE PostingID = '#PostingID#'I have verified that the entire data is not being stored by running a query in SQL Manager to retreive the Len() of the field "posting." Once maxed out at arond 69988 subsequent saving of additional data (added to the end of the existing data in our form field) does not increase the Len reported.
All help greatly appreciated!!
Can you post the code that you're using to add the additional data to the end of the existing data, please. If it's from a variable or table, please be sure to list the datatype of that variable or the column.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 26, 2017 at 9:29 am
Jeff the query above is what posts the data. There are no variables or any concatenation involved. Whatever data is in the text field when the form is submitted just over-writes what ever was there when the record was opened.
April 26, 2017 at 10:05 am
Are you sure it is SQL that is cutting off the data and not your web process? I've seen oddities in websites where they had their email forms cutting off the end of the data and it turned out to be something in their java functions.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 26, 2017 at 10:11 am
Considering he mentioned Cold Fusion, that is quite possible. Disclaimer, the application to uses the primary database I support is written in Cold Fusion, and I have had issues with the application.
April 26, 2017 at 10:14 am
I don't think that is the case here. This was running fime for several years using SQL Server Express 2007. It stil does on the old server.
Also, no javascript , concatenation, or variables are involved in the process.
Lastly, saving additional (really just "larger") data did not result in an increase when checking by running a query in SQL Manager to return Len() on the field.
April 26, 2017 at 10:16 am
Lynn I was running CFMX7 on the old server. The new one is a VPS running CF10. I will start looking at CF to see if it could be the source of the problem.
April 26, 2017 at 10:19 am
spitfire677 - Wednesday, April 26, 2017 10:16 AMLynn I was running CFMX7 on the old server. The new one is a VPS running CF10. I will start looking at CF to see if it could be the source of the problem.
Check the configuration of Cold Fusion. Make sure it isn't defaulting to Unicode data types. The older version was all or nothing (this caused us headaches when they started supporting Unicode data and set this configuration option).
April 26, 2017 at 10:25 am
Another thing you could try is to load up profiler and see exactly what is being sent to the database. I would suggest extended events, but this is 2008 and in the newbies section and those are a bit harder to use in 2008.
I suspect that Lynn is correct though.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
April 26, 2017 at 10:40 am
Lynn Pettis - Wednesday, April 26, 2017 10:19 AMspitfire677 - Wednesday, April 26, 2017 10:16 AMLynn I was running CFMX7 on the old server. The new one is a VPS running CF10. I will start looking at CF to see if it could be the source of the problem.Check the configuration of Cold Fusion. Make sure it isn't defaulting to Unicode data types. The older version was all or nothing (this caused us headaches when they started supporting Unicode data and set this configuration option).
I don't know how to check this. Looked in CF Admin, and looked at settings for the datasource... nothing there.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply