Object Exists in SQL Server 2000 Missing in SQL Server 2005 master.dbo.spt_datatype_info Table
I am running this SQL Statement to execute against SQL Server 2000, it ran fine, but when i ran it against SQL2005 it gives me an error saying missing object : master.dbo.spt_datatype_info, could anyone let me know what is the equivalent table of the above in SQL 2005 and how do i get this query executed against SQL2005.
The application basically allows any developers to type in the column/table comments /desciption and generate .html file and finally .chm file for all database objects.
Including the sql script for your review . let me know anyone can help me on this.
"SELECT OBJECTID = o.id, SPECIFIC_CATALOG = db_name(), SPECIFIC_SCHEMA = user_name(o.uid), SPECIFIC_NAME = o.name, ROUTINE_TYPE = convert(nvarchar(20), o.xtype), CHARACTER_MAXIMUM_LENGTH = convert(int, OdbcPrec(c.xtype, c.length, c.xprec) + spt_dtp.charbin), NUMERIC_PRECISION = c.xprec, NUMERIC_PRECISION_RADIX = spt_dtp.RADIX, NUMERIC_SCALE = c.scale, DATETIME_PRECISION = spt_dtp.SQL_DATETIME_SUB, CREATED = o.crdate, LAST_ALTERED = o.crdate FROM sysobjects o LEFT OUTER JOIN (syscolumns c JOIN master.dbo.spt_datatype_info spt_dtp ON c.xtype = spt_dtp.ss_dtype AND (spt_dtp.ODBCVer is null or spt_dtp.ODBCVer = 2) AND (spt_dtp.AUTO_INCREMENT is null or spt_dtp.AUTO_INCREMENT = 0)) ON (o.id = c.id AND c.number = 0 AND c.colid = 0), master.dbo.syscharsets a_cha /* charset/1001, not sortorder */ WHERE o.xtype IN ('P','FN','TF','IF','TR') AND permissions(o.id) != 0 AND o.category <> 2 /* AV: exclude system objects */ AND a_cha.id = isnull(convert(tinyint, CollationProperty(c.collation, 'sqlcharset')), convert(tinyint, ServerProperty('sqlcharset')))"
Error :
System.Data.SqlClient.SqlException was unhandled
Class=16
ErrorCode=-2146232060
LineNumber=1
Message="Invalid object name 'master.dbo.spt_datatype_info'."
Number=208
Procedure=""
Server="(local)"
Source=".Net SqlClient Data Provider"
State=1
StackTrace:
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at DBdoc.frmMain.ReadRawMetadata() in C:\DBdoc\DBdoc\frmMain.vb:line 710
at DBdoc.frmMain.cmdAnalyse_Click(Object sender, EventArgs e) in C:\DBdoc\DBdoc\frmMain.vb:line 630
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(Form mainForm)
at DBdoc.frmMain.Main() in C:\DBdoc\DBdoc\frmMain.vb:line 7
at System.AppDomain.nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
Regards
Ram
parshu1@vsnl.com
