September 17, 2004 at 2:43 pm
Hello.
I am creating a DTS package that, amongst other thing, creates some tables. For some reason, the tables that are created are of type 'System' instead of type 'User'. I have never encountered this before and would appreciate any guidance.
Here is an example of one of the create statements:
CREATE TABLE [dbo].[Stations] (
[station_id] [int] IDENTITY (1, 1) NOT NULL ,
[station_number] [int] NOT NULL ,
[station_name] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
Seems standard enough to me.
Thanks for your help,
Chris
September 20, 2004 at 8:00 am
This was removed by the editor as SPAM
September 20, 2004 at 10:51 am
The sys owner is a flag in sysobjects. See if xtype of your sysobjects is set to S or U. Check if the allow update flag is set to 1 using sp_configure. If it is set it back to 0.
Run a Profiler to trap all the queries your DTS runs to see if something that should not gets run. Start a the beginning, the connection to your server for the DTS. There is a procedure you can call to flag an object as sys, that is propably what gets called. What is the user to log to SQL Server your DTS package is using. Did you try with other user...
September 20, 2004 at 12:00 pm
That is strange. Under what account does the DTS package run? Is is a sysadmin? Is your DB owned by sa? Does this happen if you run them interactively instead of scheduled?
September 20, 2004 at 9:58 pm
Thank you Andre and Steve for the replies. Well, I think you have both guided me towards where the problem is, but I am not sure how to further test it. The issue (I think) has nothing to do with the sysobjects table, but rather with the user accounts involved. I am using Enterprise Manager installed on my computer to manipulate the database on a different computer. Maybe I have incorrect permissions or ??? How can I narrow this down?
Thanks again!
Chris
September 22, 2004 at 9:45 am
Also, Steve, this does happen if I run the DTS interactively, or even just create a new table manually through enterprise manager. I am sure that you have a good guess when you mentioned DB owners/sysadmin/sa, but I just don't know how to debug this.
Thanks for your responses.
September 22, 2004 at 10:23 am
Nevermind. I found the answer to my problems in a thread on google groups (search for "Why is the IsMsShipped bit set?" in microsoft.public.sqlserver.programming). I still don't know how/why this setting was changed, but running the following command fixes the problem:
EXEC master.dbo.sp_MS_upd_sysobj_category 2
Apparently, the IsMSShipped bit was being set in the sysobjects status column for all new tables. Not sure why though. I don't think there were changes to the system, so maybe this is a bug?
Thanks for your help.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply