|
You might want to take this to the design forum for a wider response.
Ever since I first heard of XML, way back in the 90s, I figured you should be able to use XSLT to format a report and then chuck an xml data file at it for generic type reports. Never followed up on it as I loathe XML.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you! I posted it in the design and architecture forum. I did not know it was there.
You can never try. You either do it or you don't.
|
|
|
|
|
Hi everyone.
For my current employer I've developed a few systems and deployed them. The deployment of the databases themselves has always been fairly easy. I use migratordotnet to update the schema as I continually develop these systems. That bit works great for me and I've never had any problems.
The problem I am starting to have though is that as the number of releases comes down now to maybe 3 or 4 a year. The schema structures are usually vastly different and this gives me a real headache when I need to migrate the data from a previous version to the latest schema. At the moment to do this I'm writing custom migrations for each release. However the database now has over 60 tables so these migrations are getting very long and more and more bugs are starting to appear.
So my question is how does everyone else tackle this problem? Are there industry standard approaches that I'm not aware of? Are the standard tools that I'm not using that help with this?
Any help / links to docs would be great!
Phil
|
|
|
|
|
The basic problem with your predicament, is that even if you could find a third party tool to do the migrations for you, you would still always have to do the mapping and possible conversions, which I suppose are the main part of the work anyway.
I think your best bet is to either start designing your database preemptively, or employ an add-and-recycle strategy when writing applications (i.e. as necessary, just only add new tables and/or columns, and as much as possible rename and/or convert existing tables and/or columns). This way you can just leave the existing databases be, and maybe clean them up a little every once in a while when you have time.
Admittedly, I may not have understood the full depth of your plight.
My advice is free, and you may get what you paid for.
|
|
|
|
|
No I think you've hit the nail on the head. I've asked a few friends who work in different businesses what they do and they say the same that basically i have to write a migration or only add to the schema (which I hadn't thought of as an approach so thanks for that).
Oh well I'll struggle with these queries then.
Thanks
Phil
|
|
|
|
|
This may or may not be of use to you, but for example our SAP system uses a control database.
In other words, you could make one database that just contains information about the schemas of your other application databases. This can help you keep better track of what different columns and formats, etc, you have, it can help you make development charts, upgrade / migration plans, and you can use it to run database checks (see if an application database has the correct schema, and such), and you can use it to build and store migration queries (or templates) there.
Another nice advantage is, if you ever have to build multiple language GUIs (english and spanish for example), you can use it to store common translations for columns and controls.
Good luck with it.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Phillip Donegan wrote: maybe 3 or 4 a year
I would think if you are deploying a major schema change (vastly different) every 3-4 months then you must have an extremely dynamic business model or somebody got the design wrong, and continues to get it wrong.
I usually work on single project contracts and have almost always used the migrate method, a PITA but then the business does not expect to repeat this, ever. However I am currently in a position where I am using the incrementing schema as the changes are fairly minor over a long period. The problem I find is the business does not necesarily keep up with the changes, be prepared for, why is this info not being up dated anymore type questions.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
You know how it is, a request for a change or enhancement comes in, that requires some new tables. Do this 5 or 6 times and you spot a better way of doing it so decide to change the schema, then your old data is screwed to you have to write a monster migration!!
|
|
|
|
|
I now find I have quite a few "attribute" tables in my schemas. Customer now wants 2nd mobile - add a phone attribute linked to the customer table.
I have one app that is ALL attributes, it tends to get nasty when trying to retrieve data, monstrous number of joins. Which goes to prove you can go too far down that path.
|
|
|
|
|
Hello,
I write a query (combine of 2 queries using UNION):
query1
UNION
query2
I want the result of query 2 (there is only one result in query2) will be placed in the last row of the results of the big query.
I mean the results of query1 will be in the first rows and the result of the query2 will be in the last row.
How can i do it?
|
|
|
|
|
Example
select * from table1 <br />
union<br />
select * from table2
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
OK,
I know that.
But the problem is that the results are mixed.
I want the results of the second query will be the last (in the last rows of the result big table)
|
|
|
|
|
Remember that SQL unions are based on set theory, so if value A is from table 1 and value A is also from table 2 you would only get one value A is your result set. By introducing the sort indicator that "Ashfield" suggests, you would get 2 values of A.
You would get 1,A and 2,A.
I'm not sure if this would impact your algorithm, but I just wanted to make you aware of it.
Cheers,
David
|
|
|
|
|
The easiest way I know of is to introduce a sorting column
select '1' as seq,* from table1
union
select '2' as seq from table2
order by seq
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Good Day All
I have SQL 200 and a SQL 2008 in one Machine. At First there was SQL 2000 and i install SQL 2008. Now we have a ilog Application written in C++. am not sure what that developer uses to connect to the SQL. Now the Application can connect to SQL 2000 but not to SQL 2008.
I had a thought that the Default instance still points to SQL 2000
I dont know what is wrong
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Yes... If you have already installed SQL server 2000, the default instance will be held by it. You can only install 2008 as Named instance(Only if you dont configure it to uninstall 2000 default instance).
I think it points to the default instance, can you check the connectionstring of the application to trace how they are connecting. 
|
|
|
|
|
Thank you , The Problem was not on the SQL side. we have a C++ App and the Developer is developing in his way and i think the Connection string is hard coded and that is a bad idea. but the problem is now resolved. i had to check the registry to resolve it.
Thanks
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
All,
I have been reading and trying diffrent ways to get this to work but the gist is I am trying to create a temp table that is slecting cloumns from multiple tables
I cant see how a join would work and I have tried pass the columns from table2 as a varible..no luck
The goal is to create one big table I can export off to a file
I am using SQL 2008 Ent.
Here is what I have:
BEGIN
If NOT Exists(Select * from tempdb..sysobjects Where id = object_id('tempdb.dbo.#temp'))
SELECT firstname,lastname, INTO #TEMP FROM Table1
Select email, username into #temp from Table2
END
Regards,
Hulicat
|
|
|
|
|
Do you get any error message? Can you explain better your problem?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
Thanks I think I got it...I was trying to get one result set from multiple tables
I needed to use a cross join....
Regards
Regards,
Hulicat
|
|
|
|
|
;with cte as(select t1.*, t2.* from table1 t1 cross join table2 t2)
select * from cte
OR
;with cte as(select t1.*, t2.* from table1 t1 ,table2 t2)
select * from cte
Niladri Biswas
|
|
|
|
|
Ok, if anyone knows anything about the subject please help.
Here's the setup: I have written a dll using vs 2005 that reads data from an excel file. It works fine when I reference the dll in my windows app...but when I create the assembly/function combo in SQL I get an OleDB Unspecified error. The weird thing is that it was working on Monday, then when I came back in on Tuesday the server gave me weird errors and now the conn.Open throws an exception I cannot figure it out: (Thanks in advance for any help!)
Here is the error when I try to call the Function in SQL
Declare @a int
set @a = (Select dbo.ExcelTester())
A .NET Framework error occurred during execution of user-defined routine or aggregate "ExcelTester":
System.Data.OleDb.OleDbException: Unspecified error
System.Data.OleDb.OleDbException:
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ChargeCardCom.ChargeCardCom.ExcelTester()
Here are the errors that happened on the server Tuesday:
AppDomain 122 (KWCDB_WBU.dbo[runtime].121) is marked for unload due to common language runtime (CLR) or security data definition language (DDL) operations.
and
AppDomain 130 (KWCDB_WBU.dbo[runtime].129) is marked for unload due to memory pressure.
and
AppDomain KWCDB_WBU.dbo[ddl].131 was unloaded by escalation policy to ensure the consistency of your application. Out of memory happened while accessing a critical resource.
|
|
|
|
|
|
Thank you for your reply Abhishek...I will post some code shortly (I have to log in to work as I'm at home now). Basically the ExcelTester (at this point) just creates an OleDB Connection to an excel file and attempts to open it. This is not my first CLR function integration project and I had the program working almost perfectly by Monday afternoon. Then I came in on Tuesday and started cleaning up some comments, re built the project and once again attempted to create the assemblies. It was at this time I began receiving the errors listed in my post and basically I spent all week trying to make it work again. It would probably be much easier to figure out if OleDB was not returning Unspecified Error when I catch the exception. The whole thing is weird and I'm sure it is something simple that I am just overlooking.
Basically, the function just returns a string "Success" if it works and "Failure" if it doesn't (or an Integer 0 if it works and -1 if not..I've tried many variations). It is breaking on the conn.Open and I feel pretty certain the connection string is okay. If I remove the .Open call the function works perfectly and I'm able to create the assemblies and function and call it with no problem.
|
|
|
|
|
Abhishek,
Here is the class I am using (along with my AssemblyInfo file). The only section at this point I am worrying about is the ExcelTester...I had the chgCardRead working by calling it in SQL on Monday, but again, something has gone wrong and I'm at a loss. Also, I am creating the assemblies as Unsafe already. Like I said before, it worked just fine and then just stopped and I have no idea why. Thank you for your assistance.
Option Strict Off
Imports System.Data.OleDb
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Reflection
Imports System.Data
Imports System.IO
Imports System.Runtime.InteropServices
Imports System
Imports Microsoft.Win32
Imports System.Data.OleDb.OleDbPermission
<ComClass(ChargeCardCom.ClassId, ChargeCardCom.InterfaceId, ChargeCardCom.EventsId)> _
Public Class ChargeCardCom
#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "2DF8ED1A-55D0-4EC1-AF8E-1611FF6C631E"
Public Const InterfaceId As String = "14CA7221-FBFD-4D6C-92B3-E60EAD6B48D7"
Public Const EventsId As String = "F994EEB1-4001-444C-BF01-09EA090B7CA6"
'Public Const ClassId As String = "d05374f7-f2e0-4c82-badc-2f5f93c6f1b1"
'Public Const InterfaceId As String = "b4d276c1-6548-4d70-9c05-e9e1e034c55a"
'Public Const EventsId As String = "0d3e3508-c31e-45df-b41a-b4bd0997fcb4"
#End Region
' A creatable COM class must have a Public Sub New()
' with no parameters, otherwise, the class will not be
' registered in the COM registry and cannot be created
' via CreateObject.
Public Sub New()
MyBase.New()
End Sub
Public Shared Function ExcelTester() As String
Log("in tester")
Dim xlconn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\10098886-1509.xls;Extended Properties=""Excel 8.0;HDR=No;IMEX=1""")
Dim xlda As New OleDbDataAdapter("select * from [Small Batch$]", xlconn)
Dim xldt As New DataTable
Log("about to open")
xlda.Fill(xldt)
Log("opened")
xlda.Dispose()
xlconn.Close()
End Function
'<Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillCardReadRows")> _
'Public Shared Function GetChargeData_InitMethod(ByVal Solution As String, ByVal Batch As String, _
' ByVal Charge As String, ByVal sqlinstance As String, ByVal sqldatabase As String) As IEnumerable
' 'Public Shared Function InitMethod(ByVal logname As String) As IEnumerable
' 'Dim err3() As String = {"-1~InInitMethod~0~0~0"}
' 'Return New ArrayList(err3)
' Try
' 'Return New EventLog(logname, Environment.MachineName).Entries
' WriteToLogFile("In GetChargeData_InitMethod ")
' 'Dim err3() As String = {"-1~1WroteToFile~0~0~0"}
' 'Return New ArrayList(err3)
' 'If sqlinstance.Length = 0 Then
' ' Dim err1() As String = {"-1~SQLServerNotFound~0~0~0"}
' ' Return New ArrayList(err1)
' ' Exit Function
' 'End If
' 'If sqldatabase.Length = 0 Then
' ' Dim err2() As String = {"-1~DataBaseNotFound~0~0~0"}
' ' Return New ArrayList(err2)
' ' Exit Function
' 'End If
' 'strSQLServer = sqlinstance 'regKeySubFolder.GetValue("data source").ToString()
' 'strSQLServerDatabase = sqldatabase 'regKeySubFolder.GetValue("initial catalog").ToString()
' 'Dim err3() As String = {"-1~2SetDBInfo~0~0~0"}
' 'Return New ArrayList(err3)
' 'calling this here so that any future errors wille be logged
' 'modErrorLog.SetConnectionParameters(sqldatabase, sqlinstance)
' Dim arr1() As String = chgCardRead(Solution, Batch, Charge, sqlinstance, sqldatabase)
' WriteToLogFile("After chgCardRead call from GetChargeData_InitMethod, there are " & CStr(arr1.Length) & " arrays lines returned")
' 'WriteToLogFile("arr1 in InitMethod " & arr1(0).ToString)
' Return New ArrayList(arr1)
' Catch ex As Exception
' WriteToLogFile("In InitMethod Error Handler ")
' 'modErrorLog.SetConnectionParameters(sqldatabase, sqlinstance)
' modErrorLog.LogErrorToSQL(ex.Message, "GetChargeData_InitMethod")
' Dim arr1() As String = {"-1~ExcInInitMethod~0~0~0"}
' Return New ArrayList(arr1)
' End Try
'End Function
''Public Function chgCardRead(ByVal Solution As String, ByVal Batch As String, ByVal Charge As String) As DataSet
'Public Shared Function chgCardRead(ByVal Solution As String, ByVal Batch As String, ByVal Charge As String, _
' ByVal sqlinstance As String, ByVal sqldatabase As String) As String()
' 'Need to assign so there is no null reference
' 'Dim ResultsArray() As String = {"-1~" & "strSearchMaterial" & "~" & "strSearchBatch" & "~~0~0~0"}
' Dim ResultsArray() As String = {"-1~ChargeNotFound~0~0~0"}
' 'Dim Charge1 As Integer = Convert.ToInt32(Charge)
' 'Dim Charge9 As Integer = (Convert.ToInt32(Charge) + 9).ToString()
' 'Dim intChgRow As Integer = 0
' 'Dim intChgCol As Integer = 0
' 'Dim myDataset As New DataSet()
' Dim fileCheck As String = ""
' 'Dim ChargeNumbers(9) As String
' 'Dim retVal As Double = Charge Mod 10
' 'If retVal <> 0 Then
' ' Dim SCharge As String = Charge.Substring(0, Charge.Length - 1)
' ' Charge = SCharge & "0"
' 'End If
' 'For i As Integer = Convert.ToInt32(Charge.Substring(Charge.Length - 1, 1)) To 9
' ' ChargeNumbers(i) = (Convert.ToInt32(Charge) + i).ToString()
' 'Next
' Try
' If sqlinstance.Length = 0 Then
' Dim err1() As String = {"-1~SQLServerNotFound~0~0~0"}
' 'Return New ArrayList(err1)
' Return err1
' 'Exit Function
' End If
' If sqldatabase.Length = 0 Then
' Dim err2() As String = {"-1~DataBaseNotFound~0~0~0"}
' Return err2
' 'Exit Function
' End If
' strSQLServer = sqlinstance 'regKeySubFolder.GetValue("data source").ToString()
' strSQLServerDatabase = sqldatabase 'regKeySubFolder.GetValue("initial catalog").ToString()
' If Not InitializeApplicationSettings() Then
' 'insert interface error
' ResultsArray(0) = "-1~ErrSettingSettings~0~0~0"
' Return ResultsArray
' End If
' strNVPPath = strNVPPath & Solution & "-" & Batch & ".xls"
' fileCheck = Dir(strNVPPath)
' If fileCheck = "" Then
' 'myDataset = Nothing
' 'Return myDataset
' 'insert interface error
' ResultsArray(0) = "-1~FileDNE~0~0~0"
' Return ResultsArray
' 'Exit Function
' End If
' 'strNVPPath = strNVPPath & Solution & "-" & Batch & ".xls"
' Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
' "Data Source=" & strNVPPath & ";" & _
' "Extended Properties=""Excel 8.0;HDR=Yes; IMEX=1"""
' 'Dim strConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
' ' strNVPPath & ";Extended Properties=""Excel 8.0;HDR=No;"""
' Dim conn1 As New System.Data.OleDb.OleDbConnection(strConn)
' conn1.Open()
' Dim cmd1 As New System.Data.OleDb.OleDbCommand("SELECT * FROM [" & _
' strNVPSpreadSheet & "$] WHERE " & strChargeColumn & " BETWEEN '" & _
' Charge & "' AND '" & CStr(CInt(Charge) + 9) & "'", conn1)
' Dim rdr As OleDbDataReader = cmd1.ExecuteReader
' If Not rdr.HasRows Then
' 'return error with mt data array
' Return ResultsArray
' End If
' Dim intRowCounter As Integer = 0
' Dim strReturnValues
' Do While rdr.Read()
' strReturnValues = ""
' For i As Integer = 0 To rdr.FieldCount - 1
' If i = strMaterialIDCol Or i = strRMNameCol Or i = strQtyCol Or i = strUOMCol Or i = strToleranceCol Then
' Console.Write(rdr.Item(i) & "~")
' strReturnValues &= rdr.Item(i) & "~"
' End If
' Next
' Console.WriteLine(vbCrLf)
' ReDim Preserve ResultsArray(intRowCounter)
' ResultsArray(intRowCounter) = strReturnValues
' intRowCounter += 1
' Loop
' 'If intRowCounter = 0 Then
' ' 'return error
' 'End If
' rdr.Close()
' conn1.Close()
' 'Dim a() As String = ResultsArray.Split("~")
' Return ResultsArray
' Catch ex As Exception
' LogErrorToSQL(ex.Message, "chgCardRead")
' Dim ErrorArray() As String = {"-1~ErrorInChgCardRead~0~0~0"}
' Return ErrorArray
' End Try
'End Function
'Public Shared Sub FillCardReadRows(ByVal obj As Object, <Out()> ByRef MaterialID As SqlString, <Out()> ByRef RMName As SqlString, <Out()> ByRef Quantity As SqlString, <Out()> ByRef UOM As SqlString, <Out()> ByRef Tolerance As SqlString)
' 'Public Shared Sub FillRow(ByVal obj As Object, <Out()> ByRef timeWritten As SqlDateTime, <Out()> ByRef message As SqlChars, <Out()> ByRef category As SqlChars, <Out()> ByRef instanceId As Long)
' Try
' WriteToLogFile("In FillCardReadRows")
' Dim array As String = CType(obj, String)
' WriteToLogFile(array.ToString)
' Dim a() As String = array.Split("~")
' MaterialID = New SqlString(a(0))
' RMName = New SqlString(a(1))
' Quantity = New SqlString(a(2))
' UOM = New SqlString(a(3))
' Tolerance = New SqlString(a(4))
' 'qualityinspect = New SqlString(a(5))
' 'blocked = New SqlString(a(6))
' 'Dim eventLogEnTry As EventLogEntry = CType(obj, EventLogEntry)
' 'timeWritten = New SqlDateTime(eventLogEnTry.TimeWritten)
' 'message = New SqlChars(eventLogEnTry.Message)
' 'category = New SqlChars(eventLogEnTry.Category)
' 'instanceId = eventLogEnTry.InstanceId
' Catch ex As Exception
' modErrorLog.LogErrorToSQL(ex.Message, "FillCardReadRows")
' WriteToLogFile("In FillCardReadRows Error Handler ")
' Dim array As String = CType(obj, String)
' Dim a() As String = array.Split("~")
' MaterialID = New SqlString(a(0))
' RMName = "ErrFillCardReadRows"
' Quantity = New SqlString(a(2))
' UOM = New SqlString(a(3))
' Tolerance = New SqlString(a(4))
' End Try
'End Sub
Public Shared Sub Log(ByVal msg As String)
Dim SW As StreamWriter = File.AppendText("C:\jeffcccom.txt")
SW.WriteLine(msg & " : " & Now())
SW.Flush()
SW.Close()
End Sub
End Class
Imports System
Imports System.Reflection
Imports System.Runtime.InteropServices
' General Information about an assembly is controlled through the following
' set of attributes. Change these attribute values to modify the information
' associated with an assembly.
' Review the values of the assembly attributes
<Assembly: AssemblyTitle("ChargeCardCom")>
<Assembly: AssemblyDescription("")>
<Assembly: AssemblyCompany("")>
<Assembly: AssemblyProduct("ChargeCardCom")>
<Assembly: AssemblyCopyright("")>
<Assembly: AssemblyTrademark("")>
<Assembly: ComVisible(True)>
'The following GUID is for the ID of the typelib if this project is exposed to COM
<Assembly: Guid("8068fffd-907a-44ec-b51c-a9dc0e7b3ecf")>
'<Assembly: Guid("1306f111-0f59-4a60-a826-78034ab8651d")>
' Version information for an assembly consists of the following four values:
'
' Major Version
' Minor Version
' Build Number
' Revision
'
' You can specify all the values or you can default the Build and Revision Numbers
' by using the '*' as shown below:
' <Assembly: AssemblyVersion("1.0.*")>
<Assembly: AssemblyVersion("1.0.0.0")>
<Assembly: AssemblyFileVersion("1.0.0.0")>
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
