Modifying System Table
Quick Question: I want to change an execution time for 345 jobs and do not want to do it one by one in EM. I have changed the active_start_time and next_run_time in the sysschedules table but it is not showing up within the EM job folder under the next run date as being modified. Does anyone know what I am missing?
TIA,
Daimon
View Complete Forum Thread with Replies
Related Forum Messages:
Modifying Db Design (internal Message System)
Hi, I currently have an internal message system, and I want to modify the db design so users can create their own custom folders. Currently I have just this table in use, with the bolded column, the one I want to add. With this design, I am thinking of defaulting each "folderID" in this table to a value of 0, which will denote the standard inbox folder. I think this is better because I don't think its necessary or beneficial to have each user have their own row in this table just for their standard inbox. CREATE TABLE [dbo].[tblMessage]( [MessageID] [int] IDENTITY(1,1) NOT NULL, [MessageFrom] [int] NOT NULL, [MessageTo] [int] NOT NULL, [Message] [varchar](1500) NULL, [prevMessage] [varchar](500) NULL, [Subject] [varchar](50) NULL, [date] [smalldatetime] NULL, [Checked] [tinyint] NULL, [deletedbySender] [tinyint] NULL, [deletedbyRecipient] [tinyint] NULL, [IP] [varchar](15) NULL, [folderID] [int] NULL ) I am planning on adding a table like this below CREATE TABLE [dbo].[tblMessage_folders] ( [folderID] [int] IDENTITY(1,1) NOT NULL, [userID] [int] NOT NULL, [folderName] [varchar](50) NULL, [dateCreated] [smalldatetime] NULL, ) Any differing opinions, or anyone agreeing with me I would love to hear your opinions. I'm just want to be sure this doesnt create any problems I might not be seeing. Thanks once again!! mike123
View Replies !
Database Automatically Creates Xxx_Temp Table While Modifying / Updating Table Structure .
Hello friends, I am new to the SQL Server 2005 development. From last 1 week or so, i have been facing very strange problem with my sql server 2005s database which is configured and set on the hosting web server. Right now for managing my sql server 2005 database, i am using an web based Control Panel developed by my hosting company. Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database, it gives me error saying that, "There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors. Source: .Net SqlClient Data Provider". where xxx is the table name. I have done quite a bit research on the problem and have also searched on the net for solution but still the problem persist. Thanks in advance. Any help will be appreciated.
View Replies !
Modifying Table Column
Can someone tell me the easiest/best way to make a modification to a table where you change a column constraint from not null to null? Would you rename the table, create the new one with the different column and then transfer the old data into the new definition? thanks
View Replies !
Modifying Table Size
Hi guys, I am trying to modify my table sizes. I need to extend the size of certain columns in several tables and keep the data presently in those tables also. On my first attempt I successfully made the changes but had no data after dropping the original tables. Then I had to restore from a back up and I was back to where I started. Does anyone know a reliable way of doing this with out having to create another DB and transferring the data after making the size changes to my tables ? Thanks, Alan
View Replies !
Modifying SYSJOBSTEPS Table
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts? Thanks!
View Replies !
Modifying A Table From A View
I have two tables, a data table (MainTable) that contains a user initials and other empty rows and then i have a user table. I also have an inner join view with the initials from both tables as the joining field. I want the users to be able to modify the data table (MainTable) but also display the users name field. here is the sql of the view: SELECT dbo.MainTable.DateCreated, dbo.InitialsListing.Initials, dbo.MainTable.CustomerNumber, dbo.InitialsListing.AdvocateName, dbo.MainTable.Supervisor, dbo.MainTable.Complete, dbo.MainTable.FirstNotify, dbo.MainTable.SecondNotify, dbo.MainTable.ThirdNotify, dbo.MainTable.AdvocateInitials FROM dbo.InitialsListing INNER JOIN dbo.MainTable ON dbo.InitialsListing.Initials = dbo.MainTable.AdvocateInitials
View Replies !
Trouble Modifying A Table
Hi There, I am trying to add a new field to a table using enterprise manager by right clicking on the table, choosing design table and adding the new field/column. But when I choose save changes, I receive an error message: ------------------------------------------------------------ Errors were encountered during the save process. Some of the database objects were not saved. 'Comments' Table -Unable to modify table ODBC error: [Microsoft][ODBC SQL Server Driver][SQL SERVER] ... ... Cannot drop the table 'dbo.Comments' because it is being used for replication ----------------------------------------------------------- If it helps I am trying to add a binary (bit) field and a text field of 50 characters. I think the replication it refers to is a merge replication (not sure though is there a way i can find out in EM?) I have read some articles which refer to this problem and suggest using stored procedures but I really dont know how to do that, having never used them before. I need an idiots guide please! Let me know if I need to provide more info. Thanks.
View Replies !
Help On Comparing And Modifying A Resultset Or Table
HI everybody need help badly on the following have to recordsets with the following with the following SQL SELECT place, count(minus)as countminus order by place group by place THE RESULT WOULD BE PLACE countminus ABC 10 DEF 50 HIJ 5 KLM 2 MNO 0 MY OTHER QUERY IS SELECT plus, count(plus) as countplus GROUP BY plus ORDER BY plus THE RESULT WOULD BE: PLUS COUNTPLUS ABC 10 DEF 20 my problem is i need to add another column to the first resultset (if possible) or create a table with the same field and values of the first result set with the additional column OF THE second resultset COUNTPLUS... with this condition.. I need to loop with single row from the column PLACE on the first result set and compare it with the column PLUS on the second result set. if there is a value of the PLACE=PLUS get the corresponding value of the COLUMN COUNTPLUS AND WRITE it on the COUNTPLUS ON THE NEW TABLE.. ELSE THE VALUE IS 0 this i would do it up to the END OF FILE EOF... THE RESULT WOULD BE THIS..... PLACE countminus COUNTPLUS ABC 10 10 DEF 50 20 HIJ 5 0 KLM 2 0 MNO 0 0 I WAS WORKING ON THIS BUT COULDN'T GET THROUGH IT ANYTHING WOULD BE A BIG HELP FOR ME FROM YOU GUYS
View Replies !
Modifying Existing Table Schemas?
By default, I constructed my database using the dbo Schema. I have since created new Schemas, and want to change some of the tables from dbo. Is there a way to change the existing schema of a table without disrupting the data that is inside the table? For example: Current: dbo.Products Desired Change: NewSchemaName.Products Thanks.
View Replies !
Newbie: Modifying Table = Wrong Data In View?
Hi there, Completely new to the world of databases. I'm a designer who works primarily in Flash. In any case, I'm trying to manage an application that uses MS SQL and learn about the wonderful world of databases. Ok, I modified a table (e.g. I added a column called "Rate") that had associated views (created by another developer). Noticed that my application went a little wonky as some of my variables within my app took on the value of the data in the "Rate" column. I checked one of the views and noticed that a column within the view (e.g. TutorID) was assuming the values in the "Rate" column. Note: The column TutorID had been blank before the change to the table. I'm completely lost as to why this is happening. Do I need to rebuild the view? Can I just reset the original view? Thanks. Oh yeah, I'm using SQL4X Manager J from Mac Guru (if that helps).
View Replies !
Get &&"Invalid Cursor State&&" When Modifying A Table In SQL 2000
When I try to modify a table that I just created I get the following error message: - Unable to modify table ODBC error:[Mircrosoft][ODBC SQL Server Driver] Invalid cursor state. SP3 has been applied to SQL Server 2000. Can anyone help explain what is causing this error? There is sufficient space for the database and transaction log.
View Replies !
Trouble With Update Trigger Modifying Table Which Fired Trigger
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
View Replies !
System.Security.SecurityException: Request For The Permission Of Type 'System.Data.SqlClient.SqlClientPermission, System.Data
I have created a windows library control that accesses a local sql database I tried the following strings for connecting Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Trusted_Connection = true" Dim connectionString As String = "Data Source=localhostSQLEXPRESS;Initial Catalog=TimeSheet;Integrated Security=SSPI" I am not running the webpage in a virtual directory but in C:Inetpubwwwrootusercontrol and I have a simple index.html that tries to read from an sql db but throws the error System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.SqlClient.SqlConnection.PermissionDemand() at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection) at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, etc etc The action that failed was: Demand The type of the first permission that failed was: System.Data.SqlClient.SqlClientPermission The Zone of the assembly that failed was: Trusted I looked into the .net config utility but it says unrestricted and I tried adding it to the trusted internet zones in ie options security I think that a windows form connecting to a sql database running in a webpage should be simple to configure what am I missing?
View Replies !
Marking A Table As A System Table
Okay, most peoples answer to this may be "Gaaah. Why would you dothis?", or the like, but here's the question anyway:Are there any stability issues if I mark one of my user tables as asystem table (by switching xtype in sysobjects from 'S' to 'U')?I'm not doing this as "a cleved bit of security" or some such - myactual reason for doing this is so that some of my automatic generationtools do not process this particular table, and I want a method thatwill not mean updating each of the tools if I ever add another tablelike this.It APPEARS to work, based on a quick trial, but has anybody got anydirect experience of this? Any horror stories like "Well, it workedfine for two weeks, then it shot my co-workers, set fire to the companyaccounts, and urinated in a corner. Then things got worse"Also, yes, yes, yes, I do not necessarily expect this to work in futurereleases of SQL Server (currently on 2000), but I should avoid namingconflicts by the fact that the owner isn't dbo.Thanks in advance for any insights.
View Replies !
System Table Corrupt - PLEASE Help....
I have a corrupt syscolumns table and have no good backups :( I get message 7930, Level 16, State 1. Table Corrupt; keys in left child is not less than the parent key; check left child page 15491.... The end result is the appearance that a column is missing from one of my tables. In otherwords, I know the table is supposed to have col1, but when I select data from the table, col1 does not show up....dbcc checkdb gave me the error above. thanks in advance! Dean
View Replies !
Crosslinked System Table
I have recently run across an instance where a user table has a data page crosslinked with the sysprocedures table in a database. It doesn't seem to be causing much trouble except corrupting a couple of stored procedures (i.e. 605 errors) and corrupting the backups. We are looking for a reasonable approach to correct the problem. Is there any way to rebuild or selectively restore a system table (more specifically the sysprocedures table)? We have good scripts to rebuild all of the database objects if required but would prefer not to export and reimport all of the data as the database is about 900 meg and contains a bunch of non-text data. The option of last known good backup has been considered but we would prefer to understand how to correct it if it ever happens again. The procedure for correcting this problem in a user table (i.e. BCP out good data, drop table, recreate table, BCP in data) would work great if we could accomplish this on a system table. Any suggestions or tidbits of information are welcome and will be taken with a grain of salt + tested thoroughly. Thanks, Sean Engdahl
View Replies !
Trigger On System Table...
Hi, I want to create a trigger on sysfiles. I had logged in as sa account but able to create it... I am getting the below error... Server: Msg 229, Level 14, State 5, Procedure TAU_Sysfiles_WSCLog_Reminder, Line 65535 CREATE TRIGGER permission denied on object 'sysfiles', database 'WorldScope_Dev', owner 'dbo'. Can any body please help me how to go ahead creating trigger in the system tables... Thanks in Advance... -Mohit.
View Replies !
Sysmembers System Table
Hi everybody... I want know in witch one system table can I found the description for the memberid field in the sysmembers table I supose than memberid = 16384 is a db_owner, but I don't know what means all others numbers. Tks 4 help. Jack
View Replies !
System Table For DTS Task Information
Is there any system table in sql server 2000 where it stores the dts task information. I know about these three tables for dts and they donot have dts task information stored any where. select * from sysdtspackagelogselect * from sysdtssteplogselect * from sysdtspackages any solutions?
View Replies !
Simple System Table Question
Hi,Is there a way to hide the system tables created in each SQL serverdatabase (ie. dtproperties, sysindexes). It's not a big deal, but I'dlike to see only the tables I create.
View Replies !
Query Which System Table To Answer This
I have a DB with 1 default defined: UW_Zerodefault It simply puts a 0 into particular fields upon new record creation. Is there a query I can run against a particular system table to give me a list of fields this default is applied against in the DB? Thanks
View Replies !
Bitmaps For System Table Columns
Hello, I'm trying to find the differences between two of my databases. I've noticed that some of my stored procedures were created with ANSI_NULLS on in one database and off in another database. Turns out that a tool that one of the developers is using doesn't set that correctly. Anyway... I've figured out that in the column "status" in sysobjects a bit is used to store the ANSI_NULLS setting at the time the stored procedure was created. I've also noticed that when I set two stored procedures to both use ANSI_NULLS on they sometimes still have different values in "status". Does anyone know (or know where I can find out) the bitmapping for this column? In all of the Microsoft documentation the description for this column merely states that it is for "internal use". What a big help. A resource with all of the bitmap definitions in the system tables would really be great. Thanks, -Tom.
View Replies !
System Table Consistancy Error
ren writes "the error first appeared in error log as I/O error (bad page ID) detected during read at offset 0x0000007852a000 in file 'I:RD1DATA7RD1DATA7.ndf'.. DBCC checkdb return message Database 'dbname' consistency errors in sysobjects, sysindexes, syscolumns, or systypes prevent further CHECKDB processing. dbcc checktable ('syscolumns') returned Server: Msg 8928, Level 16, State 1, Line 1 Object ID 3, index ID 0: Page (10:621247) could not be processed. See other errors for details. Server: Msg 8939, Level 16, State 1, Line 1 Table error: Object ID 1185438913, index ID 0, page (10:621247). Test (!(m_flagBits & PG_ALIGNED4)) failed. Values are 16386 and 1185438913. Server: Msg 8940, Level 16, State 1, Line 1 Table error: Object ID 1185438913, index ID 0, page (10:621247). Test (IsAligned (m_freeData)) failed. Address 0x2ad is not aligned. DBCC results for 'syscolumns'. There are 11744838 rows in 188015 pages for object 'syscolumns'. CHECKTABLE found 0 allocation errors and 1 consistency errors in table 'syscolumns' (object ID 3). CHECKTABLE found 0 allocation errors and 2 consistency errors in table '(Object ID 1185438913)' (object ID 1185438913). repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKTABLE (RD1.dbo.syscolumns ). Since the repair option requires single user mode, it will have big impact on the user. Is the I/O error (bad page ID) on the file a hardware failure? If not, what would cause this problem. Thanks very much for your help"
View Replies !
Is There A System Table Containing The Information If A Package Has Been Successful
Hi all, Im not really sure if this is a PHP or and SQL problem but here goes. Im using MSSQL and have developed a webpage that enables users to run various PACKAGES manually, however I need to display if the package has been successfully run. Is there a system table that logs package information or is there a PHP function that I can use. Thanks P.s I know there are some system tables with the information for jobs but I do not want to create a job for each package.
View Replies !
System Stored Procedure Output Into Table
Hi, Is it possible to store the output of a SQL Server 7.0/6.5 System Stored Procedure (eg. xp_fixeddrives, sp_spaceused, etc.) in a table, possibly with a Select Into? All help will be greatly appreciated. Thanx in advance. Craig
View Replies !
SQL 2K System Table Changes (Unicode) And 6.5 Compat. Feedback Plz :)
Greetings all, I am looking for feedback on the following situation :) please leave your thoughts. Little history: 1)We are in the process of migrating some of our applications from SQL 6.5 to SQL 2k. 2)The application was written to access the system tables directly under 6.5 (don't look at me, I know microsofts standing on this, i'm just the dba not the dev) The situation is this: The SQL 6.5 to SQL 2k migration is going great, no problems there... The problem is the change to Unicode for system tables etc. So when they try to run the app it freaks out when it trys to access the data in the system tables getting unicode returned instead of text. I know Unicode is the underlying arcitecture of SQL 7.0, 2k and so on but does anyone know any possible way or trick to get around this without altering the application code? As a DBA I have not seen or heard of being able to do anything of the sort but I have to ask to make sure there is nothing I have over looked. This is more a matter of C.Y.A. ;) Unicode in SQL2K http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/sql_dataencoding.asp 6.5 vs 7.0 and the system table changes http://www.windowsitpro.com/Article/ArticleID/5658/5658.html?Ad=1
View Replies !
Index Problem In A System Table Needs To Be Fixed
I run dbcc checkdb on one of my databases and I get the following message.... Server: Msg 2511, Level 16, State 1, Line 0 Table Corrupt: Object ID 12, Index ID 0. Keys out of order on page (1:7364), slots 120 and 121. CHECKDB found 0 allocation errors and 1 consistency errors in table 'sysdepends' (object ID 12). CHECKDB found 0 allocation errors and 1 consistency errors in database 'DMBEN'. repair_fast is the minimum repair level for the errors found by DBCC CHECKDB (DMBEN ). I then run dbcc checkdb ('DMBEN',repair_rebuild) with no_infomsgs and I get the same messages...nothing was fixed. Any ideas what I can do now. I tried dropping and recreating the index but the problem is on a system database and SQL won't let me drop the index. Help.
View Replies !
Count Table Recs = To System Date
I need to count the records in a table with a datetime field equal to system datetime. It looks like it is trying to match time also since time is in field too. I just want to match date only. My sql is below. Does anyone have some suggestions on how to handle this? Date from code DateTime todaydt = DateTime.Now; Table Data format [cal_str_tm] [datetime] not null, Data in cal_str_tm field - 3/27/2008 9:43:16 PM Thanks, Ron ALTER PROCEDURE SP_DpCount ( @todaydt datetime ) AS SELECT @total = COUNT(cal_int_id) from dpcalldtl where (cal_callstat != 'COMPLETED' and cal_str_tm = @todaydt) RETURN
View Replies !
System Table For Data Sources In Job Step
Is the connection manager/connection string data that is visible in a SSIS Job Step on the Data sources tab stored in a system table or DMV? Perhaps the agent service parses the SSIS package in order to display this information? We're trying to identify all of the dependencies on a specific database and the only dependency type that we can't identify systematically is SSIS package data sources. We use package configurations (of type = SQL Server database) for some packages but not all packages are using this. Thanks, Michelle
View Replies !
Recovery Model Indicator - Which System Table?
Hi I hope you can help as I am really scratching my head on this one. I am pulling together an assessment of the Disaster Recovery readiness for an organisation I am working at. Part of the assessment I am doing is the recovery model of each of the databases. I have scripts that are already pulling lots of data from 40+ servers and 500+ databases. However, I cannot seem to find anywhere within the MASTER or MSDB or the database itself where the Recovery Model flag is held. Obviously I can right click on the database and click properties and it is there, but I need to automate this task (as it will probably be a weekly assessment). I have checked sysdatabases and almost every other table, but nothing obvious as to where this flag is. Any ideas?
View Replies !
|
TRACKER
