this may seem like a simple question, but I have a report/lease agreement I need to put together and wanted to know the simpliest way to add large amounts of text. Basically its all the legal stuff most leases include in the amount of some 14 pages.
Should this be just one long string-- or does ssrs have another way to format this
thanks as always
KM
I was wondering what is the best solution to store large amounts of text in a SQL 2000 field. This text will be entered into a multiline textbox.
ex) what data type?......should I use BLOBs?
Thanks,
Trey
I am running into a problem inserting large amounts of text into my table. Everything works well when I test with a few simple words but when I try to do a test with larger amounts of text (ie 35,000 characters) the appropriate field is left blank. The Insert still performs (all the other fields recieve their data, but the "Description" field is blank. I have tried this with both "text" and "ntext" datatypes. I am using a stored procedure with input parameters. As I mentioned, the query goes off flawlessly with small amounts of data (eg "Hi there!") but not with the larger amount.I check and the ntext field claims to be able to accept 1073741823 bytes of data. Is there some other thing I should consider with large amounts of text?
View 6 Replies View RelatedHi,
I was wondering if any one could help me, I need to store large amounts of data in my database, at present I have it set to nvchar (8000), I've looked around and noticed you can use text which stores up to 2 million, but is slow in displaying the information.
Any ideas or points in the right directions would be great.
Thanks
Does anyone have ideas on the best way to move large amounts of databetween tables? I am doing several simple insert/select statementsfrom a staging table to several holding tables, but because of thevolume it is taking an extraordinary amount of time. I consideredusing cursors but have read that may not be the best thing for thissituation. Any thoughts?--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict254055.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=877392
View 4 Replies View RelatedWe are looking to store a large amount of user data that will bechanged and accessed daily by a large number of people. We expectaround 6-8 million subscribers to our service with each record beingapproximately 2000-2500 bytes. The system needs to be running 24/7and therefore cannot be shut down. What is the best way to implementthis? We were thinking of setting up a cluster of servers to hold theinformation and another cluster to backup the information. Is thispractical?Also, what software is available out there that can distribute querycalls across different servers and to manage large amounts of queryrequests?Thank you in advance.Ben
View 10 Replies View RelatedI have a dataset with 300,000 records and I'm getting the following error with MS Reporting Services. "An error has occurred during report processing. Exception of type System.OutOfMemoryException was thrown. any help with this would be highly appreciated.
View 11 Replies View RelatedGreetings
I need to be able to graph roughly about 150 employees/ supervisor and their monthly cell phone usage in minutes. I understand that I will need to group this on say one graph for every ten employees so it doesn't look messy and cluttered. I have read some threads here but they dont seem to work for me.
So again each supervisor has 100+ subordinates and I need to graph theie phone usage by month
thoughts???
km
I was wondering what is the fastest way to UPDATE lots of recods. I heard the fastest way to perform lots of inserts in to use SqlCeResultSet. Would this also be the fastest way to update already existing records? If so, is this the fastest way to do that:
1. Create a SqlCeCommand object.
2. Set the CommandText to select the datat I want to update
3. Call the command object's ExecuteResultSet method to create a SqlCeResultSet object
4. Call the result set object's Read method to advance to the next record
5. Use the result set object to update the values using the SqlCeResultSet.SetValue method and the Update method.
6. repeat steps 4 and 5
Also I was wondering do call the SqlCeResultSet.Update method once per row, or just once? Also would it be possible and faster to wrap all that in a transaction?
Would parameterized updates be faster?
Any help will be appreciated.
I have been looking into mirroring a large amount of small databases approx 150 databases.
As I understand this won't be feasible because of the way mirroring threading works, http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=441900&SiteID=1
As I understand it for every database being mirrored sql will ping the mirror second, causing a network bottleneck?.
Also that the amount of threads generated for each mirrored database will cause also cause a bottleneck?
At the moment our database servers are under very little pressure and as an estimate use about 10% of the resources allocated to them such as CPU utilization, memory, disk IO and network. Our server hardware is Dual Quad core Xeons with 4 - 8 gig of memory and variety of 10k SCSCI raid configurations from raid 5 or 1,0 and sql 2005 32bit.
Ive done some calculations on the log file generation rate compared to network bandwidth there is more than enough network bandwidth.
Has anybody had any luck in mirroring many small databases?
My concerns is how much traffic is caused by the pinging of the mirror for each database?,
How many threads will the mirroring cause and what is the max amount of threads sql can handle?
How much memory will be consumed by each one of these mirroring threads?
p.s. my email was incorrect in the last mail.
Hi all,
is there a sql 2k thread. Am interseted in finding out what the largest database size of a sqlserver database people have worked with.
We have a 1.2 Terabyte db with about 150-200 million new rows being processed everyday. Would like to share some thoughts on this with other people who are working with this much data and what they are doing with it.
bhala
----------------------------------------
Please check us out at: http://www.bivision.org/bivision
I'm in the process of migrating a lot of data (millions of rows, 4GB+of data) from an older SQL Server 7.0 database to a new SQL Server2000 machine.Time is not of the essence; my main concern during the migration isthat when I copy in the new data, the new database isn't paralyzed bythe amount of bulk copying being one. For this reason, I'm splittingthe data into one-month chunks (the data's all timestamped and goesback about 3 years), exporting as CSV, compressing the files, and thenimporting them on the target server. The reason I'm using CSV isbecause we may want to also copy this data to other non-SQL Serversystems later, and CSV is pretty universal. I'm also copying in thisformat because the target server is remotely hosted and is notaccessible by any method except FTP and Remote Desktop -- nodatabase-to-database copying allowed for security reasons.My questions:1) Given all of this, what would be the least intrusive way to copyover all this data? The target server has to remain running and berelatively uninterrupted. One of the issues that goes hand-in-handwith this is indexes: should I copy over all the data first and thencreate indexes, or allow SQL Server to rebuild indexes as I go?2) Another option is to make a SQL Server backup of the database fromthe old server, upload it, mount it, and then copy over the data. I'mworried that this would slow operations down to a crawl, though, whichis why I'm taking the piecemeal approach.Comments, suggestions, raw fish?
View 2 Replies View RelatedI know the standard Microsoft recommendation is to make the pagefile at least 1.5 to 3 times larger then the amount of physical memory. However, if you're talking about a server with lots of memory such as 16GB or 32GB, would following this rule be unnecessary. With SQL 2000 running on Windows 2000 Server or Windows Server 2003 I typically see pagefile usage no more then 12% for a 2GB pagefile. Anything over 15% means I need to look at other indicators to see if a memory bottleneck has developed. If I have 32GB of physical memory and make the pagefile only 1.5 x 32GB I have a 48GB pagefile. 10% of this is 4.8GB, which I would hope I never see consumed.
Any thoughts?
Thanks, Dave
HiI have a VB.net web page which generates a datatable of values (3 columns and on average about 1000-3000 rows).What is the best way to get this data table into an SQL Server? I can create a table on SQL Server no problem but I've found simply looping through the datatable and doing 1000-3000 insert statements is slow (a few seconds). I'd like to make this as streamlined as possible so was wondering is there is a native way to insert all records in a batch via ADO.net or something.Any ideas?ThanksEd
View 1 Replies View RelatedHello,Currently we have a database, and it is our desire for it to be ableto store millions of records. The data in the table can be divided upby client, and it stores nothing but about 7 integers.| table || id | clientId | int1 | int2 | int 3 | ... |Right now, our benchmarks indicate a drastic increase in performanceif we divide the data into different tables. For example,table_clientA, table_clientB, table_clientC, despite the fact thetables contain the exact same columns. This however does not seem veryclean or elegant to me, and rather illogical since a database existsas a single file on the harddrive.| table_clientA || id | clientId | int1 | int2 | int 3 | ...| table_clientB || id | clientId | int1 | int2 | int 3 | ...| table_clientC || id | clientId | int1 | int2 | int 3 | ...Is there anyway to duplicate this increase in database performancegained by splitting the table, perhaps by using a certain type ofindex?Thanks,Jeff BrubakerSoftware Developer
View 4 Replies View RelatedHi guys
Code:
SELECT C.customerID, quantity, unitprice, (SELECT quantity * unitprice), OD.productID,
FROM customers C
INNER JOIN Orders O
ON C.customerID = O.customerID
INNER JOIN [order details] OD
ON O.orderID = OD.orderID
ORDER BY C.CustomerID
The Output looks a little like this:
Code:
customerID quantity unitprice productID
---------- -------- --------------------- --------------------- -----------
ALFKI 15 45.6000 684.0000 28
ALFKI 21 18.0000 378.0000 39
ANATR 1 28.8000 28.8000 69
ANATR 7 9.2000 64.4000 19
ANATR 10 34.8000 348.0000 72
ANTON 24 16.8000 403.2000 11
ANTON 15 46.0000 690.0000 43
AROUT 25 3.6000 90.0000 24
AROUT 16 19.0000 304.0000 36
BERGS 16 15.5000 248.0000 44
BERGS 15 44.0000 660.0000 59
BLAUS 3 10.0000 30.0000 21
BLAUS 21 34.0000 714.0000 60
BLONP 35 12.5000 437.5000 31
BLONP 15 19.5000 292.5000 57
BOLID 24 17.6000 422.4000 4
BOLID 16 15.6000 249.6000 57
BONAP 40 36.8000 1472.0000 43
BONAP 20 7.7500 155.0000 75
BONAP 8 30.0000 240.0000 7
BONAP 20 6.0000 120.0000 13
BONAP 20 25.0000 500.0000 6
BONAP 20 23.2500 465.0000 14
BONAP 10 9.2000 92.0000 19
BOTTM 16 24.8000 396.8000 10
BOTTM 9 46.0000 414.0000 43
BOTTM 30 4.5000 135.0000 24
BOTTM 21 49.3000 1035.3000 62
BOTTM 15 2.5000 37.5000 33
I would like to add the totals from each customerID and then show the customerID once with the final total amount.
The above SELECT statement was the closest I could come.
The help is always appreciated!
Justin
I'm trying to move my current use of an sql 2000 db to sql 2005.
I need to update a table definition (to change a field to an Identity)
I'm getting a dialog box (in SQL server management studio) on save saying :
'xxxx' table
- Saving Definition Changes to tables with large amounts of data could
take a considerable amount of time. While changes are being
saved, table data will not be accessible.
I press 'Yes' to the dialog box.
After 35 seconds, I get another dialog box saying:
'xxxx' table
- Unable to modify table.
Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Well, the server is responding and I can query that talbe and other, I
can add/delete rows to other columns. I can modify other
(smaller) tables.
Any ideas where I can change this timeout?
Daniel
Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:
Documents: - DocumentID (int, PK) - Title (varchar) - Content (text) - CategoryID (int, FK)
Categories: - CategoryID (int, PK) - CategoryName (varchar)
I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:
CREATE VIEW vw_DocumentsWITH SCHEMABINDING ASSELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryNameFROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID
GOCREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndexON vw_Documents(DocumentID)
But this gave me the error:
Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.
I tried converting the Content to a varchar(max) within my view but it still didn't like.
Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.
Hi everyone,
I'm extremely new to SQL so be nice
I am attempting to write a script to add onto the end of a text field the words " -- Disposed " (About 60 rows worth).
The field is a TEXT field, so unlike a varchar field I can't just use Update as shown below.
Code:
Update AR_Primary_asset
Set AR_Primary_asset.description = AR_Primary_asset.description + ' -- Disposed'
Where AR_Primary_Asset.ASSET_REF in ('1','2','4')
I found on the Mircosoft pages about UPDATETEXT, but this only seem to work to update one row (In the case below Asset_ref = 3, was the only row effected) .
Code:
DECLARE @Dispose binary(16)
SELECT @Dispose = TEXTPTR(DESCRIPTION)
FROM AR_PRIMARY_ASSET
WHERE AR_Primary_Asset.ASSET_REF in ('1','2','3')
UPDATETEXT AR_PRIMARY_ASSET.DESCRIPTION @ptrval null null ' -- Disposed'
So i wrapped it into a cursor, this worked on my test SQL server which runs SQL2005.
Code:
DECLARE @Dispose varbinary(16)
DECLARE cursor1 CURSOR FOR
SELECT TEXTPTR(DESCRIPTION)
FROM AR_Primary_Asset
Where AR_Primary_Asset.ASSET_REF in('1','2','3')
OPEN cursor1
FETCH NEXT FROM cursor1
INTO @Dispose
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATETEXT AR_Primary_Asset.DESCRIPTION @Dispose NULL NULL ' -- Disposed'
FETCH NEXT FROM cursor1
INTO @Dispose
END
CLOSE cursor1
DEALLOCATE cursor1
But when it was run on our SQL2000 server it gave the following error message
Quote: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals add, type equals text.
I've never used vars, cursors, updatetext or even text fields before. So maybe I am going about it totally the wrong way.
Is anyone able to tell me a better way to write this? or how to make it compatible to SQL2000?
IF NOT EXISTS (SELECT TOP 1 1 FROM dbo.syscolumns WHERE id = OBJECT_ID(N'dbo.Employee) and name = 'DoNotCall')
BEGIN
ALTER TABLE [dbo].[Employee] ADD [DoNotCall] bit not null Constraint DoNot_Call_Default DEFAULT 0
IF ( @@ERROR <> 0 )
GOTO QuitWithRollback
END
It just takes a LOT of time in SQL Server Management studio. I have to cancel the query and cancelling takes a whole lot time. I am using SQL Server 2008.
Hi,
I have one column which contains some large data more than 30000 characters. When I exported the data to Excel file this, some data in this column showed as #######. This # are giving the problem in SSIS package. If I delete that data(####) then SSIS package is working fine.
How to solve this problem.
Thanks in advance.
We have created a databse in SQL Server 7 for support issues.
We are having trouble with only one aspect of the databse and that is the body of the supported problem.
The databse is basically a Question/Answer Support database whch house Frequently Asked Questions.
When the user does a search on a specific problem they have a list of matching questions shows on the screen (links).
When the question is clicked on it shows the Questions with the Answer of possible fixes.
Our problem lies in the fact that SQL server is truncating the Answer portion. I have tried different Data Types with maximum lengths with no success it keeps truncating it.
Right now I am on Data Type nvarchar with a length of 4000.
If anyone has any pointers on how to solve this problem all input is appreciated. You can post here to the forum or e-mail me directly at jason@flnet.com
Thank You.
-JR
I am building a generic job site and well I have hit a speed bump. I need to store resumes in the database to be searched on. Well what is the best way to store these full text resumes? so that they can be easily searched on?
SQL 2005
Hi,
To store large amount of text which datatype is best whether text or varchar(max).
Thnaks
I'm trying to store a binary data file in my database. I've tried data types image, varchar(max) and text. I don't get error message on loading the data but as soon as the text file exceeds 32,000 bits a query returns an empty data set.
Is this a SSMS display problem and the data is really there? Or is this another one of Microsoft's memory bugs?
Hi,
I have a large text which contains special characters and I need to insert it in "ErvTxt" field which is a varchar field.
I get an error:
Error:Line 1: Incorrect syntax near '1'.
Unclosed quotation mark before the character string ', 19 )'.
At line 4467SQL statement: INSERT INTO tbl1 (ZPERS, Status, ErvTxt, VENTS, DNR, ANAME, Fall, Zust, GER, Empf, BET, SSK, GA, JAHR, PZ, LAUFNR, RVLAUFNR, EDAT, ETIME, VDAT, UTLAUFNR, Bew, AINR, ErvNr, PrintStatus, FristNr, HDAT, HTIME, RVExportiert, CrDat, CrPers, GATT, AZ, NR) VALUES ( 'STS', -1, '0123'1'00'0138-33/02(1)'J'F'452'R020311'KL01'ZB'01'C'00001'03'i'000000000'430200001'20030116'0905''000000460'20030116'1100'0242'3'15'00'00000001000'''-'00000000000100''''''''00100û¥00000000000100û¥00000000000100û¥0000000000010000000000015699200000'0000000363û¥00000000001000û¥00000000001000 rrrrrrrrrrrr ssss''RI20030'101012003010'1200301160'0''''''''''''''''''''', '', 0, '0138-33/02(1)', 'J', 'F', 97, 'R020311', 'KL01', 'ZB', 1, 3, 'i', 0, 430200001, '2003-01-16 12:00:00', '1899-12-30 09:05:00', NULL, 460, 0, 2927, 1744, 0, 0, '2003-01-16 12:00:00', '1899-12-30 11:00:00', NULL, '2003-01-16 04:04:04', 'STS', 'C', '00001', 19 )
Can anyone tell me what's wrong?
Thanks.
Hi!I want to store some really big text in my database (for my articles). The approximate size will be from 500 to 40000 characters. I was thinking of using the database 'text' datatype.I have heard that reading these text fields is slower and decreases the performance. Moreover is it advisable to index this for searching purposes?
View 4 Replies View RelatedHi All,
How do I input a large text page (notepad) into a SQL column. Or assign a pointer to the data. I've tried to use BOL (writetext) and to no avail, I guess I'm missing something. I'm just using EM and Query analyzer. I thought this should be easy. Image data should work the same way.
TIA,
Dave
I'm sorry, but I'm a definite newbie. How can I store really long pieces of text other than as the 'text' datatype, which is too short in SQLS7?
Please help! It's key to my site admin system for importing third party wysiwyg-generated pages into our system....
Thanks,
John E.
Hi:
I'm trying to transfer a table from SQL Server 7 database to another SQL server 7 database on
another server. This table has a text field with lots of data (~.5-1 G). I'm using the export wizard
and the transfer appears to complete successfully, but when I view it, the text field data has
been truncated.
Any ideas?
Thanks, Nicole Lane
I'm importing a large text field from an Excel spreadsheet into my Sql dbase using Enterprise Manager and I'm getting the error message "Data for source column 31 'fieldname' is too large for the specified buffer size." How do I go about changing the buffer size to allow for larger text fields? Thank you.
View 1 Replies View RelatedDoes anyone know how to write a stored procedure to insert a large amount of text into a column? SQL server BOL says that you cannot declare a local variable as a text, ntext or image. I need to write a stored procedure that captures a long string of data from a web server and store it in a table. Thanks for your help in advance.
View 1 Replies View Relatedhow do i insert a large chunk of text into a table column. my project is to build a news website. where people can go and read news articles. the articles are provided by the author in word format, so how do i insert that news article into the table's column? any help would be appreciated
thanks