collation conflict resolution

I have a number of databases with quite similar structure, but with
different COLLATION
settings. Unfortunately when the structure (including keys and constraints)
has been created
no one took care of COLLATION at all.
At the moment statements like:
--
SELECT
. . .
(case FieldA
when 1 then (select Source1 from Table1 WHERE ...)
when 2 then (select Source2 from Table2 WHERE ...)
end) as Result,
--
gives COLLATION CONFLICT because Table1 and Table2 have different collation
setting.
But for a half of number of tables it works fine.
What aa I asking about: How to change COLLATION SETTING "on fly" for
different tables.fields ?
May be someone knows any fancy tools or "script wizards" to do so?
Please don't waste this newsgroup space with offering me to insert "COLLATE
DATABASE_DEFAULT"
to each statement which causes such problem. Ok?
Thanks a lot!
GnumHi,
CREATE VIEW FROM Table2
ADD COLLATE AFTER EACH FIELDS.
> when 1 then (select Source1 from Table1 WHERE ...)
(select Source1 from View1 WHERE ...)
--
SHINICHI YONEDA MXL04371@.nifty.ne.jp
Microsoft Most Valuable Professional
MVP for SQL Server 2002-2004
"Gnum" <no-email@.mail.boo> wrote in message
news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> I have a number of databases with quite similar structure, but with
> different COLLATION
> settings. Unfortunately when the structure (including keys and
constraints)
> has been created
> no one took care of COLLATION at all.
> At the moment statements like:
> --
> SELECT
> . . .
> (case FieldA
> when 1 then (select Source1 from Table1 WHERE ...)
> when 2 then (select Source2 from Table2 WHERE ...)
> end) as Result,
> --
> gives COLLATION CONFLICT because Table1 and Table2 have different
collation
> setting.
> But for a half of number of tables it works fine.
> What aa I asking about: How to change COLLATION SETTING "on fly" for
> different tables.fields ?
> May be someone knows any fancy tools or "script wizards" to do so?
> Please don't waste this newsgroup space with offering me to insert
"COLLATE
> DATABASE_DEFAULT"
> to each statement which causes such problem. Ok?
>
> Thanks a lot!
> Gnum
>|||Thank you very much for so big
MISUNDERSTANDING!
Have you ever read the question up to the end?
I've written there "
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?"
I need a help, but not a collection of stupid wisdom-like erudition esseys!
Thanks!
"Shinichi Yoneda" <mxl04371@.nifty.ne.jp> wrote in message
news:OOcY6TlvDHA.2880@.tk2msftngp13.phx.gbl...
> Hi,
> CREATE VIEW FROM Table2
> ADD COLLATE AFTER EACH FIELDS.
> > when 1 then (select Source1 from Table1 WHERE ...)
> (select Source1 from View1 WHERE ...)
> --
> SHINICHI YONEDA MXL04371@.nifty.ne.jp
> Microsoft Most Valuable Professional
> MVP for SQL Server 2002-2004
> "Gnum" <no-email@.mail.boo> wrote in message
> news:OTaDHbgvDHA.1340@.TK2MSFTNGP09.phx.gbl...
> > I have a number of databases with quite similar structure, but with
> > different COLLATION
> > settings. Unfortunately when the structure (including keys and
> constraints)
> > has been created
> > no one took care of COLLATION at all.
> > At the moment statements like:
> > --
> > SELECT
> > . . .
> > (case FieldA
> > when 1 then (select Source1 from Table1 WHERE ...)
> > when 2 then (select Source2 from Table2 WHERE ...)
> > end) as Result,
> > --
> > gives COLLATION CONFLICT because Table1 and Table2 have different
> collation
> > setting.
> > But for a half of number of tables it works fine.
> >
> > What aa I asking about: How to change COLLATION SETTING "on fly" for
> > different tables.fields ?
> > May be someone knows any fancy tools or "script wizards" to do so?
> >
> > Please don't waste this newsgroup space with offering me to insert
> "COLLATE
> > DATABASE_DEFAULT"
> > to each statement which causes such problem. Ok?
> >
> >
> > Thanks a lot!
> >
> > Gnum
> >
> >
>

Coexisting SQL Reader & UPDATE

In my current application, I have an administration form that fills in labels and checked states via data entered into the database using a similar user input field. What the admin page does is it first lists all the record names in a listview, then on select, it fills in the form based on what the records contain. This means labels text change, and check states change based on the string "True" or "False". This was done using the SQL Reader command.

Within the same form, the read checkboxes are editable via the admin. When the admin edits the controls, he will click the update button at the bottom and the database will UPDATE .. WHERE UserName = (Scalar for ListBox1.SelectedValue)

I've used the exact same UPDATE command in my form for the user, except the only difference was @. the WHERE clause-- I had it updating based on a GUID. I know my SQL statement is correct, but it just won't update the data.

Is it possible that the READER, which starts (and closes) on pageload cannot coexist within the same form as the UPDATE code?

My code is incredibly long, so for the purposes of a short post I'm not including any bit of it-- but if you would like to see it, just let me know.

Why do you need to use a GUID? Why not use an identity integer primary key? Update will then be a simple stored procedure and a bit of wrapper code.

|||

I thought that too, but the GUID's working fine on the other pages-- thats not what I have a problem with. I'm modifying an existing structure, and if the GUID scheme works, I'd rather not make more work for myself.

On the form in question, GUID doesn't even exist as an instance on it. ListBox1.SelectedValue is what I'm trying to get as my WHERE arguement-- and it does pass, no errors generated, but the form does not update.

|||

When you run it in debug mode with your breakpoints set, where does it stop at? When you click update does it call to execute your sql statement?

|||

when I set breakpoints, I have it set at the NonQuery, and everything inputs correctly: my SQL statement is properly formed down to the ListBox selected value translating into an EXISTING column name (eg, Current user is Test, Test is databound to listbox. Test's data is displayed, modified, then "updated". Break. SQL statement reads 'UPDATE Login SET ... WHERE UserName = 'Test', properly formed and existing), the ExecuteNonQuery goes through, there are no errors generated. and the redirect coded after the update processes. Display test's data again, and no changes were made.

|||

Figured it out.

I made two functions, "ReadMe()" and "UpdateMe()", and called them on load and on button click. However, although all my statements were correct, ButtonClick calls a postback, and with the reader executing onpostback, the database automatically rolled back the variables.

Solution: I changed the ReadMe() function to execute on ListBox1.SelectedIndex changed, and the update was successful.

Whoops!

Code to flatten a CellSet into a datatable

Hi,

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

AS 2005 can return query result as a flattened rowset instead of cellset. If you can take advantage of the feature then you don't have to do it yourself.|||The code transforms a cellset into a datatable that resembles a standard denormalized fact table.
How do you do that? Is there a feature in MDX to allow this?

|||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.