A link to undocumented SQL Server 2000 system sp

Posted: February 22, 2008 | Author: | Filed under: SQL Server | Leave a comment
I need this link to try some of them from time to time.
http://www.sqlservercentral.com/articles/Administering/sql2000/521/

Mixed type column import from Excel to SQL Server(IMEX cure)

Posted: February 13, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment
When you import data from excel to SQL Server, the mixed column will skip one type of data because of the use of TypeGuessRows  in the background. By seeting the property IMEX to 1 in the connection string, this issue can be corrected. Here is the information about IMEX I grabbed from connectionstrings.com.:
"IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative. Source: http://www.connectionstrings.com/?carrier=excel
 
The connection string I used for my SSIS package to loop through a folder to upload multiple excel files is:
 "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @[User::myFileName] + ";Extended Properties=\"Excel 8.0;IMEX=1;HDR=YES\";"
myFileName is a pakage variable in my SSIS pakage for looping(I tested this in a test SSIS pakage under "bioData1" at my office computer.
 

Concatenate strings with space from one column into single row

Posted: February 12, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

This link provides couple of solutions to concatenate strings from a column into one single row. I like the For XML one with SQL Server 2005. However, you need to modify a little bit to concatenate columns with sentences in them.

(http://forums.asp.net/t/1217410.aspx)

Solution:

I append a tild"~" to your traceMessage column first(you can choose other symbol if that works for you):

SELECT *,

TraceMessages = REPLACE(

( SELECT traceMessage + ‘~’ AS [data()] FROM

TraceMessage

WHERE TraceMessage.applicationLogID = ApplicationLog.applicationLogID

ORDER BY traceMessageID

FOR XML PATH (”) ), ‘~’, ‘;’)

FROM ApplicationLog

ORDER

BY applicationLogID

Show border for the selected row in GridView

Posted: February 1, 2008 | Author: | Filed under: ASP.NET 2 | Leave a comment

I have seen a solution provided by Mike Banavige in this thread(http://forums.asp.net/p/1211169/2134768.aspx#2134768).

Here is a variation of that solution for selected row with the addition of OnPreRender event:

Define CSS classes for the border of selected row’s cells and add the CSS classes in the OnPreRender event.

<head runat="server">
<title>SamplePage</title>
<style type="text/css">

.selectedRow td{border-top:3px solid yellow;border-bottom:3px solid yellow;}
.selectedRow td.first {border-left:3px solid yellow;}
.selectedRow td.last {border-right:3px solid yellow;}
   </style>
</head>

<asp:GridView ID="GridView1" runat="server" OnPreRender="GridView1_OnPreRender" …>

Code:

 protected void GridView1_OnPreRender(object sender, EventArgs e)
    {
        GridView gv = (GridView)sender;
       
        gv.Style["border-collapse"] = "seperate"; //add seperate to the style
        gv.Columns[0].ItemStyle.CssClass = "first";
        gv.Columns[gv.Columns.Count - 1].ItemStyle.CssClass = "last";
        gv.SelectedRowStyle.CssClass = "selectedRow";
       
    }
(Copied from this thread: http://forums.asp.net/t/1212856.aspx)

Column Name Issue when export from SQL Server to Excel

Posted: February 1, 2008 | Author: | Filed under: SQL Server 2005 | Leave a comment
If column name or alias has special character( for example # sign) in it, it will be replaced with a period in the column name when the table is exported to excel.
There is a workaround for this: export the data to a flat CSV file and keep the first row as header. It will keep the # sign in the column name.You can use or save this csv file to the format you want.