I am developing a simple ASP based form, that stores user info in MS SQL server. I have created a table in the SQL server to store the data and defined the body field with this line: `body char(255)`. The problem is that if the user inputs a string longer then 255 characters it gets choped off. How would you suggest solving this problem? Should I use `text` datatype instead?? Any comments are very appreciated !!!
View 2 RepliesFrom what I can see, the 'varbinary(max)' data type is not supported, and the 'image' data type is supposed to go away. Is there some other way to store large chunks (10MB to 100MB) of data into an SSEv DB?
If I have to use the 'image' data type to so this, does anyone have a code sample that would let me push an array() of numbers into an 'image' field, and unload an 'image' field into an array()?
TIA
Pat
hi..
I want to store a RMVB file to SQL SERVER 2000 ,and read from it,iwant to play the RMVB file in web,the size of the RMVB file is more than 300MB less 1G.the SQL Field Image can include it.
Now My Quesstion is How can i Store and Read the RMVB file from SQL Server2000?
I used SqlInsertCommand.ExecuteNoquery() in my program,but it Too slow,ao make a unknown error.
Thank you for your help.
In my Java application, I have a stream of character data in a java.io.Reader object.
I am using a PreparedStatement object to insert data into a table containing such a large object column (datatype - text). I am using the following API call:
PreparedStatement.setCharacterStream(colIndex, reader, size);
In order to find the size in the above statement, I read the stream and find the length.
Because of this I am getting the following error message and the data is not getting inserted:
Exception during insertion : Failed for MYTABLE Reason [Microsoft][SQLServer 2000 Driver for JDBC]Transliteration failed.
Is there any alternate method to handle this? Please help.
Hi gurus
i wud like to know abt the datatype by which i can store nealy 7k to 8k characters in it.. please help me
Thanks
shekhar
Hi,
To store large amount of text which datatype is best whether text or varchar(max).
Thnaks
Hi,I would like to know if anyone out there really uses SQLServer 2000 (which edition?) to hold the data for a datawarehouse? How much data does it handle efficiently?TIAFrank
View 1 Replies View RelatedI'm using OpenRowSet to import about 30 columns from a csv file with 190 columns with a format file. Ultimately, I want to put this in an SSIS Package. I am receiving the following error when trying to import date and decimal info.
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 990, column 64 (TOTALSALES). There are several similar errors. I looked at this line and it is 17873.34 so I am not seeing the problem. Every value in the column is either 0 or a 2 digit decimal value. If I change the SQL Column and format file to to NVARCHAR, it imports fine.
The existing format file and SQL Column looks as follows. There are multiple errors referring to different columns and all of them seem to be valid decimals. I am having the same issue with date fields that exist in the csv as 20130521. If I bring it in as text, it is fine.
<FIELD COLLATION="SQL_Latin1_General_CP1_CI_AS" MAX_LENGTH="12" TERMINATOR="," xsi:type="CharTerm" ID="64"/>
<COLUMN xsi:type="SQLDECIMAL" SCALE="2" PRECISION="15" NAME="TOTALSALES" SOURCE="64"/>
The SQL Column is defined as Decimal ((15,2), NULL))
I created a small csv file with representative decimal, date, integer and NVarchar fields and it imports into SQL fine as decimal and date info. The SQL Query used is pretty simple. Ultimately, I am planning to create a package that imports this data and joins to a production table based on values in the csv file. It will either update existing values in a Production Table or insert New Values
INSERT INTO Import.dbo.test1
SELECT *
FROM OPENROWSET(BULK 'C:ShareImport.csv',
FirstRow=2,
FORMATFILE='C:ShareImport.xml'
) AS t1;
I am assuming there is bad data in the csv file but I'm not sure how to identify it as my test file seems to bring in date values with a format of 20140923 and 2 digit decimal values and that is what exists in the line numbers being referenced. I've not used OpenRow Set for this purpose before. The only workaround I've found is to bring it all in as text and create additional fields so I can cast or convert the date values which I'd rather not do as this process seems to work in my small sample file.
Hi I've followed a tutorial on how to write and read varbinary(max) data to and from a database. But when i try to read the data i get the error that the data would be truncated, but only when the varbinary(max) is greater then 8kB. I've used a system stored procedure (sp_tableoption) to set the table that holds the data to store data outside rows. To select the data i'm using a stored procedure: SELECT imageData , MIMEType FROM Pictures WHERE (imageTitle = @imageTitle) And then using an .aspx page to Response.Write the data:Using conn As New sql.SqlConnection conn.ConnectionString = ConfigurationManager.ConnectionStrings("myConnectionString").ToString Dim getLogoCommand As New sql.SqlCommand getLogoCommand.CommandType = Data.CommandType.StoredProcedure getLogoCommand.CommandText = "GetPicture" getLogoCommand.Connection = conn Dim imageTitleParameter As New sql.SqlParameter("@imageTitle", Data.SqlDbType.NVarChar, 200) imageTitleParameter.Value = Request("imageTitle") imageTitleParameter.Direction = Data.ParameterDirection.Input getLogoCommand.Parameters.Add(imageTitleParameter) conn.Open() Using logoReader As sql.SqlDataReader = getLogoCommand.ExecuteReader logoReader.Read() If logoReader.HasRows = True Then Response.Clear() Response.ContentType = logoReader("MIMEtype").ToString() Response.BinaryWrite(logoReader("imageData")) End If End Using conn.Close() End Using Can anyone please help me with this?!
View 2 Replies View RelatedI am fetching large amount of data from teradata to sql server using linked server. I am facing below query:
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)
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 RelatedHi :
I want to transfer large data from access db into sql server compact , how can I do ? thanks a lot!
Hi all,
please, show me How to data is store on the server ?
thanks for reading.
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 RelatedIn a Library Management database we have these tables
1) Document ( DocNo , Doc_type , permalink,inDate)
2)Title(id, DocNo,Main_Title, Other_Title)
3)Author(id , Author_Name , Author_Family,Type--Like:main author , translator ,....)
4)Publisher(id,DocNo , Name,Publisedate,address)
5)Subject(id,DocNo,Subject)
6)Description(id,DocNo,ISBN,description)--one document may have some ISBN,etc
In document table I have 500,000 records.
I want to search a word in these tables ,for example i want to search 'Computer' ,this word may be in subject or title or description and etc. How can I do this with best performance?
I am trying to index dates to numbers with a large data set.
The first colums is index, the next is FactorsS, the next is value and the next is Date and the last is Lag.
Would it be difficult to write code that would determine the lag values. The lag value is based on the date value.
Index FactorS Value Date Lag
1 XYZ 2.3 12/31/2014 1
2 XYZ 1.4 12/30/2014 2
3 XYZ 3.3 12/29/2014 3
4 ABC 1.8 12/31/2014 1
5 ABC 2.2 12/30/2014 2
6 CBA 1.7 12/31/2014 1
7 CBA 1.8 12/30/2014 2
8 CBA 1.9 12/29/2014 3
9 CBA 2.1 12/28/2014 4
Hello,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 RelatedI have a large excel spreadsheet created by finance user that contains several decades worth of sales data.
Here is a small sample:
Guest Count
Unit ID1/2/2011 1/9/2011
3 0
7 0
8 0
90 0
151696 1202
222769 1914
232704 2110
250 0
282838 1882
331089 691
363581 3064
371469 1062
I need to get this data into an SQL table in the following form so I can use it to further manipulate the data and update several other tables. I am thinking that UNPIVOT or CROSS APPLY might be the way to go, but am not sure how to code it.
The desired output:
Unit IDDate Guest Count
31/2/2011 NULL
71/2/2011 NULL
81/2/2011 NULL
91/2/2011 0
151/2/2011 1696
and so on ......
The spreadsheet has 2900 columns and 3500 rows so performance is definitely a consideration as well.
I've installed the MDW (Mangement Data Warehouse) database on our central monitoring SQL Server. I've then added a number of servers to be monitored. The data is collected on the servers that are being monitored and uploaded to the central MDW Monitoring server.
On the servers that are being monitored, I'm seeing a large number (over 1000) of SPIDs being generated by 'SQL Server Data Collector'.
Is this normal behaviour? I've seen more blocking as a result of this.
Is there any way to reduce the number of SPIDs generated?
Hi, all experts here,
I am wondering if tempdb stores all results tempararily whenever I query a large fact table with over 4 million records which joins another dimension table? Since each time when I run the query, the tempdb grows to nearly 1GB which nearly runs out all the space on my local system drive, as a result the performance totally down. Is there any way to fix this problem? Thanks a lot in advance and I am looking forward to hearing from you shortly for your kind advices.
With best regards,
Yours sincerely,
security ids seem to be made up of at least 3 32 bit unsigned numbers and a few smaller numbers. We believe their lengths vary. We dont mind dropping the "S" from the front. What data type do you recommend be used for their storage? We expect only limited joins and user visibility on this column. We may wish to create an index on this column. We think varchar and varbinary are the two major choices.
what is the best practices to store Landset Data in SQL Server? volume of data should grow up to 100 TB
View 3 Replies View RelatedI am using Windows 2003 Server English Version. I wanna store the big-5data so I install the sql server 2000 as if i install it in the Windows2000 with Server Collation of the Chinese_Taiwan_Stroke_CL_AS.However, the data are stored into the database server in unicodeinstead of big-5 in that of windows 2000 OS.I would like to ask how i can set so that the Sql Server 2000 can storethe big-5 data
View 6 Replies View RelatedHi!
I have a table like this below and it doesn't only contain English Names but it also contain Chinese Name.
CREATE TABLE Names
(FirstName NVARCHAR (50),
LastName NVARCHAR (50));
I tried to view the column using SQL Query Analyzer, It didn't display Chinese Character.
I know that SQL Server 2005 is using UCS-2 Encoding and Chinese Character uses Double Byte Character Set (DBCS) Encoding.
I want to read the FirstName and LastName columns and display in Window Form Data Grid and ASP.NET Grid View.
I tried to use this code below and it didn't work. It convert some of the English Name to Chinese Character and it display the chinese character and some still in the original unreadable characters.
Does anybody know how to read those character from SQL Table and display the correct Chinese Character without converting the English Name into Chinese also?
Thanks
int codePage = 950;
StringBuilder message = new StringBuilder();
Encoding targetEncoding = Encoding.GetEncoding(codePage);
byte[] encodedChars= targetEncoding.GetBytes(str);
.
message.AppendLine("Byte representation of '" + str + "' in Code Page '" + codePage + "':");
for (int i = 0; i < encodedChars.Length; i++)
{
message.Append("Byte " + i + ": " + encodedChars);
}
message.AppendLine(" RESULT : " + System.Text.Encoding.Unicode.GetString(encodedChars));
Console.Writeline(message.ToString());
I have a combo box where users select the customer name and can eithergo to the customer's info or open a list of the customer's orders.The RowSource for the combo box was a simple pass-through query:SELECT DISTINCT [Customer ID], [Company Name], [contact name],City,Region FROM Customers ORDER BY Customers.[Company Name];This was working fine until a couple of weeks ago. Now wheneversomeone has the form open, this statement locks the entire Customerstable.I thought a pass-through query was read-only, so how does this do atable lock?I changed the code to an unbound rowsource that asks for input of thefirst few characters first, then uses this SQL statement as therowsource:SELECT [Customer ID], [Company Name], [contact name],City, Region Fromdbo_Customers WHERE [Company Name] like '" & txtInput & "*' ORDER BY[Company Name];This helps, but if someone types only one letter, it could still bepulling a few thousand records and cause a table lock.What is the best way to populate a large combo box? I have too muchdata for the ADODB recordset to use the .AddItem methodI was trying to figure out how to use an ADODB connection, so that Ican make it read-only to eliminate the locking, but I'm striking outon my own.Any ideas would be appreciated.Roy(Using Access 2003 MDB with SQL Server 2000 back end)
View 2 Replies View RelatedI need to store file(s) to SQL without streaming / reading at Server. I have created a Web API with AngularJS and SQL. e.g.
var fileType = httpRequest.Files[file].ContentType;
var fileStrm = httpRequest.Files[file].InputStream;
var fileSize = httpRequest.Files[file].ContentLength;
byte[] fileRcrd = new byte[fileSize];
var file_Name = Path.GetFileName(filePath);
fileStrm.Read(fileRcrd, 0, fileSize);
Is it possible to send file data to SQL (in bytes) without streaming / reading at server?I don't want to put a load on server for large files. just read the data and send them to SQL where SQL will do the streaming and store data as varbinary.
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
View 1 Replies View RelatedWe are storing changed data of tables into XML format for auditing purpose. The functionality is already achieved. We are using FOR XML Path clause to convert relational data of tables into XML format.
Now, a table is having column name with '(' . For example name of the column is, ColumnName(). In this case we can not convert into XML using For XML clause. Showing error as,
Column name 'columnName()' contains an invalid XML identifier as required by FOR XML; '(' (0x0028) is the first character at fault.
I'm using the XML Source to process a hierarchical set of XML. As such, the XML Source creates keys to maintain the hierarchy. This is very convenient, and keeps me from having to invent my own keys.
The problem is that the datatype of these keys defaults to DT_UI8. Which SQL Server 2005 datatype should I use to store these values in my staging tables? BIGINT corresponds to DT_I8, which can't accept DT_UI8 values.
how i can do a script to export tables,store procedures and data in SQL Server Management Studio Expres
I just arrive to do a script for tables and stores procedures
help please
I Have a table with #Sample like below
=================================
#Sample
id int,
SSN varchar(20),
State varchar(2)
Sample Data:
ID SSN STATE
1 999-000-000 AB
2 979-000-000 BC
3 995-000-000 CD
=================================
We used filter logic based on the SSN & State.
We are passing these values through variables like
Declare @State varchar(2)
Declare @SSN varchar(20)
While run time these values are lets suppose @SSN = '999-000-000' & @State='ABC'
Now the Result is displayed with the state data Like 'AB' only.
Output: 1 999-000-000 AB
instead it should give system generated error.
Here I have 2 Questions:
1. Why it is taking 1st 2 Charecters?
2. Why it does not have any system generated for length?
I can do validation with Length function for these 2 variables however if have 100 variables then it should not feasible case. So, what is the reason behind?
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string based
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
I have a table that contains file paths as
ServernamefolderAfilenameA
ServernameFolderBFilenameB
and I need a query to return
ServernamefolderA
ServernameFolderB
I tried
SELECT DISTINCT left(Source, charindex('', Source)- 0) AS String
FROM Table
But that removes everything after the first and I need it to return all data before the last