CopySourceAsHtml (CSAH) Visual Sudio Add In

For those of us using Visual Studio there are some unique Addins that boost very much the productivity.
One of them is CopySourceAsHtml from Colin Coller that does exactly what it says,
it copies the source code with formatting included into the clipboard as HTML so that it can be pasted with full formatting.

I have tested this both in Visual Studio 2005 and 2008 and it is working perfect.

Here are some settings i prefer in order for the output to be perfect.



An example of copy pasting:
Original image:


Pasted html:

            string sDirectory = Path.GetDirectoryName(txtFileName.Text);

            string sFileName = Path.GetFileName(txtFileName.Text);

            string sNewFilePath = txtFileName.Text+"_old";

            try

            {

                File.Delete(sNewFilePath);

            }

            catch

            {

            }

            File.Move(txtFileName.Text,sNewFilePath);

            SqlCeEngine eng = new SqlCeEngine("Data Source=" + sNewFilePath);

            eng.Shrink();

            eng.Verify(VerifyOption.Enhanced);

            eng.Compact("Data Source=" + txtFileName.Text);

Reduce Sql Server Compact Database Size

In most of the cases, the use of Sql Server Compact edition has to do with a Desktop Application where size of the redistributed data do matter.
Having that in mind, we need a way to reduce the size of the database file, so that deployment procedures (possibly through ClickOnce Deployment) wont't need to download a huge amount of data.

As Microsoft states here data Compression on Sql Server Compact is not supported.

Trying to find an alternate way to reduce the database's size i ended up on these functions that Sql Server Compact engine offers through the SqlCeEngine class.
These are the Shrink and the Compact functions.
According to Microsoft:

Shrink: Reclaims wasted space in the SQL Server Mobile database by moving empty pages to the end of the file, and then truncating the file.

Compact:Reclaims wasted space in the SQL Server Mobile database by creating a new database file from the existing file. This method is also used to change the collating order, encryption, or password settings of the database.

Also:
Unlike the Compact method, the Shrink method does not create a temporary database file. Instead, all empty and unallocated pages are moved to the end of the file and are then truncated, reducing the overall database size.

One must note though that these functions do not compress data, they will just remove unnecessary space claimed from the database system when querying or changing database's schema.

Having that in mind here is a snippet of both the functions

            string sDirectory = Path.GetDirectoryName(txtFileName.Text);

            string sFileName = Path.GetFileName(txtFileName.Text);

            string sNewFilePath = txtFileName.Text+"_old";

            try

            {

                File.Delete(sNewFilePath);

            }

            catch

            {

            }

            File.Move(txtFileName.Text,sNewFilePath);

            SqlCeEngine eng = new SqlCeEngine("Data Source=" + sNewFilePath);

            eng.Shrink();

            eng.Verify(VerifyOption.Enhanced);

            eng.Compact("Data Source=" + txtFileName.Text);

Sql Server 2005 Excel Import Error

Today, the first day of the year,
unfortunately i am working on a project which needs to import a large amount of data from excel into an Sql Server 2005 database.

I selected the Sheet i wanted to import ...






















And these were the table's mappings ...



Notice the Desc field which displays 255 size.

After that when i tried to finish importing data i got the following errors:



Error 0xc020901c: Data Flow Task: There was an error with output column "Desc" (21) on output "Excel Source Output" (9). The column status returned was: "Text was truncated or one or more characters had no match in the target code page.". (SQL Server Import and Export Wizard)

Error 0xc020902a: Data Flow Task: The "output column "Desc" (21)" failed because truncation occurred, and the truncation row disposition on "output column "Desc" (21)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component. (SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source - Sheet1$" (1) returned error code 0xC020902A. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure. (SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread0" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled. (SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited. (SQL Server Import and Export Wizard)

Trying to figure out the problem i found this were Microsoft actually says that when importing data from Excel into Sql Server it checks by default only the first 8 rows of the Excel Sheet.
To change this you have to dig into registry and change the TypeGuessRows key in the path HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel,
as follows ...



I have changed this value from 8 to 0, which means 16384 rows to scan (if available).

Be aware, you should notice this:

"The valid range of values for the TypeGuessRows key is 0 to 16. However, if the value is 0, the number of source rows scanned is 16384. Keep in mind that a value of zero may cause a small performance hit if the source file is very large."

After these changes the mappings now for the over sized column "Desc" have changed to max as seen here ...


After that the data import worked just fine.
Performance was indeed much worse as Microsoft mentioned, but this is not such a problem.