Hi,
Please could you tell me how big sql tables are when people refer to them as small, medium and large? Preferably in terms of disk space or rows (each row in my table will contain a standard length job advert and 20 additional columns with an average of 8 characters)
Thanks for your help! :-)
Stu
HI There,
Generally speaking, is it better to use a large or small stripe size for a Raid 5 array (4 drives) ? I would appreciate any specifics also.
Thanks in advance.
Charlie
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
when to use table variable and temp table. i told the interviewer that when rows is less like hundreds or thousand then use table variable else use temp table.After that he asked that what do u mean by less data or thousand rows may be there are multiple columns involved with that less rows and make a huge data set.
View 3 Replies View Related
Hello,
the application will add items into a "bag". That is, the items in one table will refer a record in another table. This will be done in timely manner -- with second or minute delays between adding a new item. There will be up to thouthand of items per bag. The option is to wait until a full bag accumulates and set up all the references at once by using
UPDATE items SET container_ref = bag WHERE id IN [...]
The disadvantage of such all-at-once I see is inability to encapsulate the functionality into a SP -- the problem is to pass a set of IDs. The advantage should be efficiency in terms of total SQL Server load. How mush would it be?
Hi Experts
We are debating what is best:
1. To combine all the company's data in one large database, and use schemas and file groups to create logical and physical distribution on drives and namespaces
or
2. Distribute the data into smaller databases with related data - eg. products and product description in one db, Customers in another and orders and orderlines in a third db.
Just what are the pros and cons?
regards
Jens Chr
System.OverflowException: Value was either too large or too small for an
Int32. Why does this error originate in the following line?"SqlCommand cmd = new SqlCommand("SELECT Count(*) FROM Contacts", conn)........ ..........DataSetContacts.ContactsRow row = ds.Contacts.NewContactsRow();..................row["ContactNumber"] = Convert.ToInt32(txtContactNo.Text);" ContactNumber field is SqlDbType.Int.
HiThis is a question of "what does it cost me".Lets say I have an integer value which would fit into a smallint fieldbut the field is actually defined as int or even larger as bigint.What would that "cost" me ? How would definitions larger than I need forthe values in the field affect me ?Its obvious that the volume of the database would grow but with the sizeof resources etc that we have nowadays disc space isn't a problem likeit used to be and i/o is much faster and many people would tell me "whocares" , or IS it a problem ?How does it affect performance of data retrieves ? Searches ? Updatesand inserts ? How would it affect all db access if tables are pointingat each other with foreign keys ?Thanks !David Greenberg
View 3 Replies View RelatedHi ,
Is there any method by which I can divide the large flat file into certain number of small files keeping the header in each of the sub files?
Regards,
Prash
Hi All!
I've got some data in a table called Dim.Sources that I generated by a program to generate data, so the characters are wierd, but it is in the database.
When I process the dimension, I get an error like: the size specified for the link is too small, and it will truncate one or more values of the column...
It is only for the name column, but not for the id column (I guess because in this column there are only numbers).
I'm getting nuts! please, any help will be much appreciated! No way to find any information on the internet about it!
I don't remember defining any link size!! Where can I change it?? What is it??? : )
Thank you all in advance!
This is a tough error....
The situation: we are trying to manage Stored Procedures (ALTER, CREATE, DROP statements) on one of our SQL2005 servers and this error spews out:
"Target string size is too small to represent the XML instance"
Now this script i wrote (simple alter statement adding a column) works on 4 other versions of SQL 2005, but when i try a certain server, i get that error
Searching Google, Yahoo, and MSN, all three point to one single instance of this error, right here on Don Keily's blog
http://www.sqljunkies.com/WebLog/donkiely/archive/2005/10/20.aspx
and unfortunately... no solution :(
Maybe in the 3 months since that post, someone might have run across that and knows how to fix?
I am trying to understand creating SQL Server projects and managed code. So I created a C# SQL Server Database project and named it "CSharpSqlServerProject1" and followed the steps in the following "How to: " from the Help files:
"How to: Create and Run a CLR SQL Server Stored Procedure "
I used the exact code in this "How to: " for creating a SQL Server managed code stored procedure (see below) in C#. However it didn't even compile! When I went to build the code I got the following error message:
"Error 1 Target string size is too small to represent the XML instance CSharpSqlServerProject1"
It does not give a line number or any further information! Since this is a Microsoft example I'm following I figure others must have run into this too. I can't figure out how to fix it!
Here's the code as copied directly from the howto:
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[SqlProcedure()]
public static void InsertCurrency_CS(
SqlString currencyCode, SqlString name)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
SqlCommand InsertCurrencyCommand = new SqlCommand();
InsertCurrencyCommand.CommandText =
"insert Sales.Currency (CurrencyCode, Name, ModifiedDate)" +
" values('" + currencyCode.ToString() +
"', '" + name.ToString() +
"', '" + System.DateTime.Now.ToString() + "')";
InsertCurrencyCommand.Connection = conn;
conn.Open();
InsertCurrencyCommand.ExecuteNonQuery();
conn.Close();
}
}
}
Thanks for any help you can give!
I have a small number of rows in a dataset, Table 1. There is a CLOB on a large dataset, Table 2. They join on a PK. I would like to retrieve this CLOB and add it to the data flow for Table1. In short I want to emulate the following:
Table 1: Small table without CLOB, 10 rows.
Table 2: Large table with CLOB, 10,000,000 rows
select CLOB
from table2
where pk = (select pk from table1)
I want this to return the CLOBs for the small number of rows in Table 1. The PK is indexed obviously so it should be a fast look up.
Table 1 and Table 2 live on different Oracle databases. How do I perform this operation efficiently in SSIS? It seems the Lookup and Merge Join wont do this.
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies View RelatedHi
I've been searching this site and the Web for info on an error message I get when importing from Access 2003 into SQL Server 2000.
'Data for Source Column 3('Col3') is too large for the specified buffer size'
A memo field in Access is larger than 255.
I have followed advice about putting the field to the first column. This doesn't work - the error just returns the new column number. In fact, I've tried just importing the first column - no good.
I am wary about making Registry changes as comments on the Web say this doesn't work either.
Does anybody have the solution for this.
Paul
I have set up transaction replication between two databases. Data from a table in the first database is replicated to the same table in another database.
The table at the publisher already has some data in it. The table at the subscriber is empty. When the replication is synchronizing, I get the following errors in the replication monitor:
*The process could not bulk copy into table "dbo"."virtualdatalocations_waitingqueues". (Source: MSSQL_REPL, Error number: MSSQL_REPL20037) Get help: http://help/MSSQL_REPL20037
*Field size too large
The table looks like this:
CREATE TABLE virtualdatalocations_waitingqueues (
dataid int ,
personid int ,
queueid int ,
CONSTRAINT FK_vw_dataid
FOREIGN KEY(dataid) REFERENCES datalocations(id) ON DELETE CASCADE ,
CONSTRAINT FK_vw_personid
FOREIGN KEY(personid) REFERENCES persons(id),
CONSTRAINT FK_vw_queueid
FOREIGN KEY(queueid)REFERENCES waitingqueues(id)
);
It used to run fine in the past. I couldn't find any help on google or on forums.
Any help or comments are greatly appreciated.
I developed a vb app that imports csv data into an sql server db. The original text file is 36.5mb. The db after import is 230mb and the log file is 555mb. Is this normal?
View 8 Replies View RelatedThanks in advance. What is maximum SQL Server database (*.mdf) file size with SQL Server 2000 as part of Microsoft Small Business Server 2000? (Database files were limited to 10 GB in SBS 4.5 with SQLServer 7.0... has this changed?).
View 1 Replies View RelatedI want to store a small cirle in a text field. Can anyone tell me how I can enter it in ascii code.
Thanks
I recently started using Differential backups. They are working but are growing in size a lot quicker than I expected.
The backups are growing by 2.5GB every day although the total size of all
transaction backups is under 350MB. I would have imagined that the total transaction log backups would be a good indicator of total database changes and therefore the differential backups would approach this figure.
Please explain!
My log file size is of 5 GB, I just wanna reduce this to some extent without adopting shrinking method. So is there any way to do the same ?
View 1 Replies View RelatedHello,
I have a issue with the drill down. In the report there is drill down in the Amount column. I am trying to pass the customer names in this drill down but there are more than 100 customers for that specific case and drill down is not able to pass all the customers.
Is there any other way to pass the large string in the drill down?
hi all,
my tlogs at the subscriber are growing very large
irregardless of my recovery mode. any help
using snapshot-push replication
thanks,
joey
Hello,
I am receiving the following error:
Column name or number of supplied values does not match table definition
I am trying to insert values into a temp table, using values from the table I copied the structure from, like this:
SELECT TOP 1 * INTO #tbl_User_Temp FROM tbl_User
TRUNCATE TABLE #tbl_User_Temp
INSERT INTO #tbl_User_Temp EXECUTE UserPersist_GetUserByCriteria @Gender = 'Male', @Culture = 'en-GB'
The SP UserPersist_GetByCriteria does a
"SELECT * FROM tbl_User WHERE gender = @Gender AND culture = @Culture",
so why am I receiving this error when both tables have the same
structure?
The error is being reported as coming from UserPersist_GetByCriteria on the "SELECT * FROM tbl_User" line.
Thanks,
Greg.
It is possible to find table size and in that table each row size.
View 4 Replies View RelatedSQL Server 7.0 (SP1)
Error:
------
OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.
Thanks for any input.
Hi
I want to store large files like pdf file,Html page,audio file in Sql Server database.How can i do it?
if somebody know then tell me as soon as possible.
Thanks in advance.
Bye
Hello there,I have and small excel file, which when I try to import into SQlServer will give an error "Data for source column 4 is too large forthe specified buffer size"I have four columns in the excel file, one of the column contains alarge chunk of data so I created a table in SQL Server and changed thetype of the field to text so I could accomodate this field but stillno luck.Any suggestions as to how to go about this.Thanks in advance,Srikanth pai
View 5 Replies View RelatedIf I join Table1 to Table2 with a WHERE condition, isit the same if I would join Table2 to Table1 consideringthat the size of the tables are different.Let's assume Table2 is much bigger than Table1.I've never used MERGE, HASH JOINs etc, do any ofthese help in this scenario?Thank you
View 3 Replies View RelatedHi,
I need a query to get table Definition in sql server 2005.
please help me out.
Raghu sunkara.
Hi,
We currently have a fairly new SQL server 2000 db (currently about 18mb is size) as a backend to an application (Navision). Performance seems to be below what it should be.
The db is increasing quite rapidly in size, with a lot of data scheduled to be loaded onto the db and also more and more shops and users coming onto the system with alot more transactions going onto the db.
The initial setup of the db has the database File properties set to "Automatically grow file" by "30%" and has an unrestricted file growth.
The server that the db sits on is high spec and very large disk space.
Because the database will be expanding alot and thus reaching its maximum space allocation and then performing a 30% increase in size (which I guess affects performance quite a bit??) quite regularly.
Is it best to set the intitial size of the db to a alot bigger size in the first place as we have large disk space availiable and also set the % increase bigger also.
any advice on best performance would be much appreicated.
Regards,
David
I've got two databases on the same server and replicate some tables from one database to another.The replication is configured so not to drop the table if it exists, but to delete the data based on the filter if one exists. There are two tables on the subscriber that have some extra columns.
I get "field size too large" error when trying to replicate them. Is there a workaround without having to make the publisher and the subscriber tables identical by schema?
Hi,
I have a problem to import xls file to sql table, using MS SQL 2000 server.
Actual main problem associated with it is xls file contain one colum having large amount of text which length is approximate 1500 characters.
I am trying to resolve it through like save xls to csv or text file then import but it also can not copy whole text of that column, like any column in xls having 995 characters then text or csv file contain 560 characater. So, it is also wrong.
thanks in advance, if any try to resolve