Can SQL Express reside on the same server as MSDE and both run at the same time (different apps)?
I have one app that runs on MSDE and a new to be developed app by a vendor that needs SQL Express.
Thanks
JB
Yes, that is possible. When you start the SQL 2005 Express setup, it will detect the MSDE and ask if you wish to upgrade or install a new instance. Choose a new instance.
You may wish to read in Books Online about the topic: "Instance Naming Page"
Here's some code I've written which flattens a CellSet into a datatable. I looked for a long time to find similar code but could not find any. Can I please have some thoughts on optimisation/elegance etc... I'm a bit new to .NET so any advice would be great!
Thanks.
Code Snippet
Public Function GetFlatData() As DataTable
'If (Me.Cells.Count = 1) Then
' Return New DataTable()
'End If
Dim table As DataTable = New DataTable()
Dim headers As ArrayList = New ArrayList()
For a As Integer = 0 To (Me.Axes.Count - 1)
For i As Integer = 0 To (Me.Axes(a).Set.Hierarchies.Count - 1)
Dim h As Hierarchy = Me.Axes(a).Set.Hierarchies(i)
If (Not h.Name.Contains("Measure")) Then
table.Columns.Add(h.Name)
End If
Next i
Next a
Dim items As ArrayList = New ArrayList()
For x As Int32 = 0 To (Me.Axes(0).Set.Tuples.Count - 1)
Dim tx As Tuple = Me.Axes(0).Set.Tuples(x)
Dim itemBase As CellItem = New CellItem()
For Each mx As Member In tx.Members
If (mx.LevelName.Contains("Measure")) Then
If table.Columns.IndexOf(mx.Caption) = -1 Then
table.Columns.Add(New DataColumn(mx.Caption, GetType(Double)))
End If
itemBase.AddMeasure(mx)
Else
itemBase.AddAttribute(mx)
End If
Next
If (Me.Axes.Count > 1) Then
For y As Int32 = 0 To (Me.Axes(1).Set.Tuples.Count - 1)
Dim item As CellItem = itemBase.Clone(True)
Dim ty As Tuple = Me.Axes(1).Set.Tuples(y)
Dim memy As Member
For Each memy In ty.Members
If (memy.LevelName.Contains("Measure")) Then
item.AddMeasure(memy)
Else
item.AddAttribute(memy)
End If
Next
item.ValueCell = Me.Cells.Item((y * Me.Axes(0).Set.Tuples.Count) + x)
'item.ValueCell = Me.CellGrid(x, y)
items.Add(item)
Next
Else
' for 1 axis cellsets
itemBase.ValueCell = Me.Cells.Item(x)
items.Add(itemBase)
End If
Next
'' might have no measures need to cater
For i As Integer = 0 To (items.Count - 1)
Dim row As DataRow = table.NewRow()
Dim attributes As ArrayList = (CType(items(i), CellItem)).Attributes
For j As Integer = 0 To (attributes.Count - 1)
Dim member As Member = CType(attributes(j), Member)
row(j) = member.Caption
Next
Dim measures As ArrayList = (CType(items(i), CellItem)).Measures
If Not measures Is Nothing Then
' each CellItem should only have one measure
For j As Integer = 0 To (measures.Count - 1)
Dim member As Member = CType(measures(j), Member)
Dim value As Cell = (CType(items(i), CellItem)).ValueCell
If Not (String.IsNullOrEmpty(value.Value)) Then
'need to set appropriate type accroding to cell
'table.Columns[member.Caption].DataType = value.CellProperties.
row(member.Caption) = value.Value
Else
row(member.Caption) = DBNull.Value
End If
Next
End If
table.Rows.Add(row)
Next
Return table
End Function
#End Region
#Region " Private Classes "
Private Class CellItem
Private _measures As ArrayList
Private _attributes As ArrayList
Private _valueCell As Cell
Public Property Attributes() As ArrayList
Get
Return _attributes
End Get
Set(ByVal value As ArrayList)
Me._attributes = value
End Set
End Property
Public Property Measures() As ArrayList
Get
Return _measures
End Get
Set(ByVal value As ArrayList)
Me._measures = value
End Set
End Property
Public Property ValueCell() As Cell
Get
Return _valueCell
End Get
Set(ByVal Value As Cell)
_valueCell = Value
End Set
End Property
Public Sub New()
_attributes = New ArrayList()
_measures = New ArrayList()
End Sub
Public Sub New(ByVal attributes As ArrayList, ByVal measures As ArrayList, ByVal valueCell As Cell)
Me.Attributes = attributes
Me.Measures = measures
Me.ValueCell = valueCell
End Sub
Public Sub AddAttribute(ByVal o As Object)
_attributes.Add(o)
End Sub
Public Sub AddMeasure(ByVal o As Object)
_measures.Add(o)
End Sub
Public Function Clone(ByVal isDeep As Boolean) As Object
If (isDeep) Then
Dim item As CellItem = New CellItem(Me.Attributes.Clone(), Me.Measures.Clone(), Me.ValueCell)
Return item
Else
Return New CellItem(Me.Attributes, Me.Measures, Me.ValueCell)
End If
End Function
|||Your code flattens a CellSet object. Given an AdomdCommand object, calling ExecuteCellSet method returns a CellSet object, calling ExecuteReader method returns an AdomdDataReader object which is a flattened recordset.
I am trying to build a report table based on user supplied criteria at run time. The user may or may not enter criteria into one or more fields. I used the COLAESCE as follows (the temp vars may be passed valid data or left null by the user):
select * from dbo.employee
where LastName>=COALESCE(@.ln,lastname) andLastName<=COALESCE(@.ln2,lastname) andFirstName>=COALESCE(@.fn,firstname) andFirstName<=COALESCE(@.fn2,firstname) andhiredate>=COALESCE(@.hire,hiredate) andhiredate<=COALESCE(@.hire2,hiredate) andcheckdate>=COALESCE(@.chk,checkdate) andcheckdate<=COALESCE(@.chk2,checkdate)
The problem comes when I want to return rows that include columns that may be null. For example the CHECKDATE col might be the date the employee was reviewed and for new employees it may be null. I still want to return that row.
I had thought of creating default values for every column when the user adds a row to a table. I can set all char fields = ' ' and int fields = 0, but what is a valid default value for a date type col that won't cause problems when other procs try to grab the field and use it?
Or is there a better way to use the COALESCE function?
Thanks all!
I would add a third parameter to the coalesce function. For instance COALESCE(@.var,FieldName,'1/1/1900'). The third field would be the "default" value if the first two return null. HTH.
-Chris
|||If you couldn't choose some value as "empty", you could add additional parameter like @.ln_is_empty and then use something like (@.ln_is_empty=1 or (Lastname>=@.ln and @.ln_is_empty=0) )|||Assuming that the query is contained within a stored procedure then you would have to start by declaring another input parameter per search term to indicate whether the WHERE condition for the relevant column should check for NULL or whether the search term should be ignored (i.e. equal to itself), currently it seems that you have no way to distinguish between the two.
Once this has been done you can use:
WHERE ((checkdate >= COALESCE(@.chk,checkdate) and checkdate <= COALESCE(@.chk2,checkdate))
OR (@.checkdateisnull = 1 AND checkdate IS NULL))
As an aside, with a query such as the one you have presented you are unlikely to see great performance. It might be better to dynamically create and execute a SQL string inside the stored procedure, forget using COALESCE inside the SQL string and include only what's actually needed in the WHERE clause - using COALESCE in the way that you have done is likely to lead to table / index scans and gives little scope for performance improvements by indexing.
Chris
|||There are better ways to write this query than using COALESCE with column names. The above usage will negate use of any indexes on the columns. So you will be pretty much scanning the entire table for any combination of parameters. See the link below for various techniques that will help you solve the problem. Look for my name to see some techniques that use COALESCE/ISNULL but with better results. Erland also covers in detail other techniques that will help you get the best results.
http://www.sommarskog.se/dyn-search.html
|||Thanks!!!
I'll be studying that document for quite some time!
