Use Of Sys.syscomments
May 19, 2008Hi,
What is the use of sys.syscomments ?
Cheers,
Mathi
India.
ADVERTISEMENT
SYSCOMMENTS
May 30, 2001Hey Folks,
I am trying to replicate a a development database into a production database.
The production database has a different name from the developmet and I want to
find a way of re-creating the Stored procedures from SYSCOMMENTS.
I keep getting garbled ouput from select text from syscomments.
Can anybody please tell me how to extract Stored Procedures' text
from system table SYSCOMMENTS
TIA
Syscomments / Sysdepends
Aug 20, 2007Quick question relating to the above tables.
I am currently experiencing an issue where the syscomments and sysdepends tables are increasing exponentially.
For example, i have a database with data segment of 666 MB (maybe its the number!) and the syscomments is 254MB and sysdepends is 325MB. all the other tables are remaining static, and over my sample period have not increased. Also there have been no new procedures or views created on this database, so I am not sure why these two tables are increasing so much.
Does anyone have any ideas as to what is causing this? or have any pointers for things i should be checking?
Many thanks in advance.
Mark
Syscomments.text And Visual Basic 6
May 8, 2008Has anyone experienced problems calling stored procedures that references syscomments.text from Visual Basic 6?
I have a stored procedure that finds all tables referenced by a specific stored procedure. It works well when I call it from SQL Server Management Studio, but there are cases where it does not find the tables when I call it from VB 6.
I've isolated the failure it to a specific condition in the WHERE clause:
SELECT @howfar = so.id
FROM SYSOBJECTS so
LEFT OUTER JOIN syscomments sc on so.ID = sc.ID
Where lower(so.name) = lower('storedProcName')
AND lower(sc.TEXT) LIKE '%' + lower('update') + ' ' + lower('tableName') + '%'
The call from VB works (finds the record) if
" AND lower(sc.TEXT) LIKE '%' + lower('update') + ' ' + lower('tableName') + '%' "
is omitted, and fails (does not find any records, no error is raised) if that condition is included.
I should also add that the VB Call does work for some combinations of stored procedures and tables.
The old SET NOCOUNT ON trick does not make any difference.
SQL7 Upgrade Error/Problem (syscomments)
Jul 14, 1999I have a SQL6.5 sp5a, NT4 sp5, BDC, IE5 server that I have installed SQL7
on. My installation is set to mixed security.
Everytime I try to run the upgrade wizard I get the following error/problem
right after the "Checking 6.x syscomments for corruption and Verifying 6.x
data base logins etc.
Summary of warnings (if any) and choices:
=============================================
The following syscomments entries are invalid on your 6.x SQL Server. It is
recommended that you fix these problems before you continue.
=============================================
********************************
master
********************************
********************************
Inconsistency Report For Database: master.
********************************
dbo.sp_db_upgrade2
Procedure
Occurs 28 times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_fallback_activate_svr_db
Procedure
Occurs three times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_password
Procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_recompile
Procedure
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
dbo.sp_rename
Procedure
Occurs 11 times
[Microsoft][ODBC SQL Server Driver][SQL Server]Ad-hoc updates to system
catalogs not enabled. System Administrator must reconfigure system to allow
this.
--------------------------------
=============================================
The following script will be created as upgrade.ini and run into the script
interpreter when you press finish.
=============================================
[Options]
Method=1
Perform a CRC Check=0
Source=CCIDEV
Destination=CCIDEV
Export Server Startup Options=
Import Server Startup Options= -T1808
SQLServer70Path=C:MSSQL7SourcePassword=066ce2f9c0e 4ba
DestinationPassword=00
MasterPath=C:MSSQLDATAMASTER.DAT
VerswitchOldVer=65
Code Page=1252
Override Server Code Page=0
QuotedIdentifiers=0
ObjectDumpPath=c:mssql7upgradeCCIDEV_071399_163117 Ansi Nulls=0
[Tasks]
Export and Import via Named Pipe=0
Marking database upgrade status=0
Export Logins=0
Export Database Owners=0
Import Logins=0
Creating Databases=0
Setting Database Options=0
Export Database Objects=0
Import Database Objects=0
Export SQL Executive Settings=0
Preparing MSDB for Upgrade=0
Import SQL Executive Settings=0
Export Server Settings from Master=0
Modifying Scripts=0
Import Server Settings from Master=0
Preparing SQL-DMO for upgrade=0
[Databases]
006CCIDATA=0
003model=0
005msdb=0
001master=0
I don't find anything about this anywhere except the upgrading to SQL7 white
paper, it mentions that the text for objects must be intact in the
syscomments system table and that logins must exist for every database user,
but that doesn't really tell me how to fix this.
Has any one seen these errors or can point me
to where on Microsoft's site you can now EASILY get KB info for SQL Server
7?
Syscomments Table Text Field Does Not Allow More Than 8060 Chars
Oct 18, 2006Hello,
I have a table in SQL Server 2000 which has few triggers. When I try to update a record in that table, I get following message:
Warning: The table '[TABLE_NAME]' has been created but its maximum row size (17275) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.
where the data I am updating is well less than the field limit. The triggers are stored in syscomments table and when I dropped these triggers, the update statements were executed without this message. Is there anyway I can change the data type of field text in syscomments from nvarchar to ntext?
Thanks
Din