Quick answers to unusual problems - C# and Sql Server

1. Does ever happened to you to try to import with DTS or SSIS an .xls file into SQL, and you receive an error something like data was truncated?
Of course, you will change the data type of destination columns to be ”nvarchar(max)”, but unfortunately you still receive the same error message the and the import it’s not done. Well, the explanation is that there is an optimization in Microsoft Excel ODBC driver: Your data may be truncated to 255 characters if the first 8 records for the field(s) being truncated contain 255 or fewer characters. The full Microsoft explanation you can find at :
http://support.microsoft.com/kb/189897/EN-
 
2. Sometime you will use a text editor (by example FCK) to insert data into database. If the stored procedure which executes the insert operation into database contains dynamic sql, and your text to be inserted contain some special characters like ‘, you will receive sql exception. To ovoid this, you have o correct your stored procedure like below.
 set @DefaultValue = replace(@DefaultValue,'''','''''''+''')

3. Sometimes you have bind a grid view to an sql data source; the grid view has the the facility to delete a row, you have created a template column for delete and put there an image button with the command name “Delete”. Until now all works fine, but after a while you will notice that the delete operation no longer works. If you enable delete operation on grid view, the operation will work but you want to have template column with an image button. To have this, you have to add SET NOCOUNT ON in your stored procedure

4. If you want to design the database for a business of type CRM where you have contacts of type companies and persons, never use to split the tables into 3: Contacts, Persons and Companies. This is a grave architectural problem witch will slow done your system. Just create a table contacts, which have a column of type ContactType to differentiate companies of persons.

5. If you manipulate xml column in some sql script, try to avoid at minimum possible to work with .xml column. Better, move your .xml data into a variable of type table, and after that work with this table variable; do this, specially when .xml data it’s not very big but you process this for many times.

6. Be aware that if you want to take the report header image into .rdlc (reports made with Reporting Server, local or remote) from database you can simple bind the image of .rdlc to a column of byte[] comming from database. In the Crystal Report this is not possible;  here you have to convert the byte[] into text, to put this into an invisible text in the report, and after that to bind the image to the content of the hiddden text box by converting to byte[].

7. When you start to develop a web application be very carefull about each page size. You have to put short names to your controls, especially for all place holders, and for the the .css. The web application performance is so dependent of the page size and of the band width, so you have to carrefull starting from the beginning.