New Tables and Stored procedures are saving as System Tables

When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?
Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>

New Tables and Stored procedures are saving as System Tables

When I try to create a new table/stored procedure, MSDE is saving it as
system table/procedure. And this is happening to all the databases on that
instance.
I checked the sysobjects table. The new tables are saved as Type 'U'.
But their status is in -ve nos.
What I can do to fix that?Hi
You should not rely on using the "FOR MS USE" objects/columns. What does
OBJECTPROPERTY(id,'IsSystemTable') return?
John
"Ganesh" <Ganesh@.discussions.microsoft.com> wrote in message
news:45F7565B-FC35-437E-88CC-30E896875DF0@.microsoft.com...
> When I try to create a new table/stored procedure, MSDE is saving it as
> system table/procedure. And this is happening to all the databases on that
> instance.
> I checked the sysobjects table. The new tables are saved as Type 'U'.
> But their status is in -ve nos.
> What I can do to fix that?
>
>

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.Hi
"ginarunco@.hotmail.com" wrote:

> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.
Hi
"ginarunco@.hotmail.com" wrote:

> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John
sql

new stored procedures created as type system instead of user

I am running SQL 2000 SP4. When I create a new stored procedure it is
being created as type system instead of user. How can I make it so
every new sp I create is type user instead of system? Thanks in
advance.Hi
"ginarunco@.hotmail.com" wrote:
> I am running SQL 2000 SP4. When I create a new stored procedure it is
> being created as type system instead of user. How can I make it so
> every new sp I create is type user instead of system? Thanks in
> advance.
>
Run DBCC TRACESTATUS ( 1717 )
If the flag is on, run
EXEC sp_MS_upd_sysobj_category 2
Check the SQL Server Startup parameters to make sure it doesn't turn this
trace flag on at startup.
John

new stored procedure problem...

using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.

CREATE PROCEDURE isp_ap_load_checks
@.p_comp char(2),
@.p_vend char(6),
@.p_user char(12),
@.p_date1 char(8),
@.p_date2 char(8)
as

if (@.p_user <= '')
begin
set @.p_user = 'system'
end

if (@.p_date1 <= '00000000')
begin
set @.p_date1 = '00000000'
end

if (@.p_date2 <= @.p_date1)
begin
set @.p_date2 = '99999999'
end

delete from XAPCHECKS
where xapck_comp = @.p_comp and xapck_vend = @.p_vend and xapck_user = @.p_user

delete from XAPCHKDETAIL
where xapcd_comp = @.p_comp and xapcd_vend = @.p_vend and xapcd_user = @.p_user

insert into XAPCHECKS
select apph_comp, apph_vend, @.p_user,
max(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0')), apph_payck, chm_type, chm_stat, apt_bank,
apph_paymnts, apph_stat

from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id

insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id

from APPHISTF LEFT JOIN APTRANF on apt_comp = apph_comp and apt_vend = apph_vend and
apt_type = apph_type and apt_id = apph_id
LEFT JOIN APBANKF ON apb_code = apt_bank
left join CHMASTF on chm_comp = apb_comp and chm_acct = apb_cash and chm_no = apph_payck
where (apph_comp = @.p_comp) and (apph_vend = @.p_vend) and
(apph_payck > 0 and
(str(yy,4) + replace(str(mm,2),' ','0') + replace(str(dd,2),' ','0') > '00000000'))
group by apph_comp, apph_vend, apph_payck, chm_type, chm_stat, apph_paymnts, apph_stat, apph_type, apt_bank, apph_id

GOPost the DDL for XAPCHKDETAIL.|||here is the sql script for the table...
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[XAPCHKDETAIL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[XAPCHKDETAIL]
GO

CREATE TABLE [dbo].[XAPCHKDETAIL] (
[xapcd_comp] [char] (2) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_vend] [char] (6) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_user] [char] (12) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_check] [int] NOT NULL ,
[xapcd_chk_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapck_check_status] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_bank] [char] (5) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
[xapcd_type] [char] (1) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[xapcd_id] [char] (18) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,
[A4GLIdentity] [numeric](9, 0) IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
GO

CREATE UNIQUE CLUSTERED INDEX [IXAPCHKDETAIL0] ON [dbo].[XAPCHKDETAIL]([xapcd_comp], [xapcd_vend], [xapcd_user], [xapcd_check], [xapcd_chk_type], [xapck_check_status], [xapcd_bank]) ON [PRIMARY]
GO|||Visit http://support.microsoft.com/kb/878501 , an article from Microsoft to resolve the problem.

Cheers,
--Riaz

using tables with identity columns and trying to create a stored procedure to load records into 2 tables.
here is what i am trying to do. but when i try to save it i get
"Error 8101: An explicit value for the identity column in table 'XAPCHKDETAIL' can only be specified when a column list is used and IDENTITY_INSERT is ON"
Do i have to break this down into 2 SP's or can i do it some how with this one.
Also, how can i get the XAPCHECKS table to only load the DISTINCT values from the APPHISTF. the APPHISTF will possible have multiple check numbers that are the same but i only need that row in the XAPCHECKS table once.|||Change this:
insert into XAPCHKDETAIL
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...

...to this:
insert into XAPCHKDETAIL
([xapcd_comp],
[xapcd_vend],
[xapcd_user],
[xapcd_check],
[xapcd_chk_type],
[xapck_check_status],
[xapcd_bank],
[xapcd_type],
[xapcd_id])
select apph_comp, apph_vend, @.p_user, apph_payck, chm_type, chm_stat, apt_bank, apph_type, apph_id
...

ALWAYS enumerate your column names.|||Is there a way that you know of to only load up a distinct record into the XAPCHECKS table. The APPHISTF can have many ID's associated with a single check.
apphistf:
ID 1 check 1
ID 2 check 1
ID 3 check 1
ID 4 check 2
ID 5 check 3
ID 6 check 3
...
what i am trying to accomplish is to get a recap for a vend by check date, check number that doesnt have to tie back to the ID, that is where the XAPCHKDETAIL comes in.|||Either SELECT DISTINCT, or use an aggregate query with MAX or MIN to select one of the IDs to insert.

New stored procedure call

Hi,
Actually the stored procedure ' Update ' is being called when ever I
click new button or edit button . But when i click the save button in the
grid the record is commited. But it is taken care by framework.Now when i
click the save button i must get the message as the record saved.
Thanks,
Jyothsna.Hi Jyothsna
Probably u are using a stored procedure to update the records. Introduce a
return parameter to the stored procedure and return a certain number when th
e
record is sucess and some number when record fails ex. 1 and -1 respectively
try to catch the return value when the button is clicked. Based on the
result display the message.
best Regards,
Chandra
http://chanduas.blogspot.com/
---
"Jyothsna" wrote:

>
> Hi,
> Actually the stored procedure ' Update ' is being called when ever
I
> click new button or edit button . But when i click the save button in the
> grid the record is commited. But it is taken care by framework.Now when i
> click the save button i must get the message as the record saved.
> Thanks,
> Jyothsna.
>
>

New session inside SP?

Hi:
I want to open a new session/connection inside the execution of a stored procedure. Is this possible ?
I ask this because I need a new sesssion with its own transaction.
Thanks,
Rui FerreiraThere are ways to do this, but none of them are pretty. They all involve breaking at least one of the fundamental rules about transaction managment.

Can you explain what you are trying to do in more detail? If I knew more about what you wanted, I could give you a clearer, simpler answer than trying to list all of the methods with their pros and cons for you to sort through!

-PatP|||xp_cmdshell, osql will creat their own spid...

Mostly they've been a royal pain in the donkey....|||xp_cmdshell, osql will creat their own spid...

Mostly they've been a royal pain in the donkey....Yeah, that too.

Just from a security standpoint, the OLE objects are much better. The problem with all of the solutions to this kind of problem is that they have so many caveats and potential "tiger trap" situations embedded in them that there isn't a simple answer.

-PatP|||There are ways to do this, but none of them are pretty. They all involve breaking at least one of the fundamental rules about transaction managment.

Can you explain what you are trying to do in more detail? If I knew more about what you wanted, I could give you a clearer, simpler answer than trying to list all of the methods with their pros and cons for you to sort through!

-PatP

I have a stored procedure that generates sequential IDs based on an auxiliar table. I want to include this SP in its own transaction to avoid concurrency problems I'm having now. When one transaction that uses the SP lasts a little longer the table is locked too much time.
I hope my explanation helps...

Thanks,
Rui Ferreira|||What sort of concurrency problems are you having? This sounds like a bad thing to me to try to sidestep any concurrency issues, because if they are causing problems now I think that side-stepping them will cause even bigger problems.

-PatP|||Sounds like the original design is the bigger problem...

Want to show us some code?

new problem

I got the following error message when I do an insert trigger, can anyone
please help? Thanks.
Server: Msg 8115, Level 16, State 8, Procedure ADM_APRN_ins_upd_trg, Line
108
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.Pls post "ADM_APRN_ins_upd_trg" code, or continue using
imagination. Thanks.
Regards,
Alfred.
>--Original Message--
>I got the following error message when I do an insert
trigger, can anyone
>please help? Thanks.
>Server: Msg 8115, Level 16, State 8, Procedure
ADM_APRN_ins_upd_trg, Line
>108
>Arithmetic overflow error converting numeric to data type
numeric.
>The statement has been terminated.
>
>.
>|||Hello Alfred,
There is very little we can do here.
The problem as the error states on in line 108. I am going
to assume that it is some sort of maths function being
carried out. Whatever it is been converted into does not
like it.
Could you post that line and the 5 lines before and after
it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Pls post "ADM_APRN_ins_upd_trg" code, or continue using
>imagination. Thanks.
>Regards,
>Alfred.
>>--Original Message--
>>I got the following error message when I do an insert
>trigger, can anyone
>>please help? Thanks.
>>Server: Msg 8115, Level 16, State 8, Procedure
>ADM_APRN_ins_upd_trg, Line
>>108
>>Arithmetic overflow error converting numeric to data
type
>numeric.
>>The statement has been terminated.
>>
>>.
>.
>

New Database Name not visible while DSN setup

Hi,
I have taken backup(connectdb.bak) from production and
created the database in test server. For that I used
following procedure,
In sql query analyzer, I executed the following two steps
consecutively.
1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak'
2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak' WITH
MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
This created a new database "newdb" in test server from
the back up file connectdb.bak.
I want to connect to this database.
Now while going through SQL server DSN configuration,
there comes an option where we get the following option,
"change the default database to"
While clicking in this box,we get a drop down listing of
all the databases. Here I am stuck. Ideally the "newdb"
database which I have created from connectdb.bak file,
should be visible here so that I can choose to connect to
it. But it's not visible here and unless it's visible here
I can't do anything. The otherdatabases which were present
in the test server since beginning are visible accept this
one.
Please help. It's urgent
regards,
Vishal
Check to see if the login/user for whom you are setting up
the DSN is a user in the database that has been restored. It
sounds like they users could have been orphaned.
You can find a good article on orphaned users and
troubleshooting techniques, links to KB articles at:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
-Sue
On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have taken backup(connectdb.bak) from production and
>created the database in test server. For that I used
>following procedure,
>In sql query analyzer, I executed the following two steps
>consecutively.
>1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak'
>2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak' WITH
>MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
>MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
>This created a new database "newdb" in test server from
>the back up file connectdb.bak.
>I want to connect to this database.
>Now while going through SQL server DSN configuration,
>there comes an option where we get the following option,
>"change the default database to"
>While clicking in this box,we get a drop down listing of
>all the databases. Here I am stuck. Ideally the "newdb"
>database which I have created from connectdb.bak file,
>should be visible here so that I can choose to connect to
>it. But it's not visible here and unless it's visible here
>I can't do anything. The otherdatabases which were present
>in the test server since beginning are visible accept this
>one.
>Please help. It's urgent
>regards,
>Vishal
>
|||If this is a new serer then the user exists in the
database but not in master. You first need to create the
login. Then synch up the users with sp_change_users_login
>--Original Message--
>Check to see if the login/user for whom you are setting up
>the DSN is a user in the database that has been restored.
It
>sounds like they users could have been orphaned.
>You can find a good article on orphaned users and
>troubleshooting techniques, links to KB articles at:
>http://vyaskn.tripod.com/troubleshoo...phan_users.htm
>-Sue
>On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
><anonymous@.discussions.microsoft.com> wrote:
steps
to
here
present
this
>.
>

New Database Name not visible while DSN setup

Hi,
I have taken backup(connectdb.bak) from production and
created the database in test server. For that I used
following procedure,
In sql query analyzer, I executed the following two steps
consecutively.
1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak'
2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
\Backup\connectdb.bak' WITH
MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
This created a new database "newdb" in test server from
the back up file connectdb.bak.
I want to connect to this database.
Now while going through SQL server DSN configuration,
there comes an option where we get the following option,
"change the default database to"
While clicking in this box,we get a drop down listing of
all the databases. Here I am stuck. Ideally the "newdb"
database which I have created from connectdb.bak file,
should be visible here so that I can choose to connect to
it. But it's not visible here and unless it's visible here
I can't do anything. The otherdatabases which were present
in the test server since beginning are visible accept this
one.
Please help. It's urgent
regards,
VishalCheck to see if the login/user for whom you are setting up
the DSN is a user in the database that has been restored. It
sounds like they users could have been orphaned.
You can find a good article on orphaned users and
troubleshooting techniques, links to KB articles at:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
-Sue
On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
<anonymous@.discussions.microsoft.com> wrote:

>Hi,
>I have taken backup(connectdb.bak) from production and
>created the database in test server. For that I used
>following procedure,
>In sql query analyzer, I executed the following two steps
>consecutively.
>1. RESTORE FILELISTONLY FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak'
>2. RESTORE DATABASE newdb FROM DISK = 'c:\Mssql7
>\Backup\connectdb.bak' WITH
>MOVE 'connectdb_Data' TO 'c:\test\newdb.mdf',
>MOVE 'connectdb_log' TO 'c:\test\newdb_log.ldf'
>This created a new database "newdb" in test server from
>the back up file connectdb.bak.
>I want to connect to this database.
>Now while going through SQL server DSN configuration,
>there comes an option where we get the following option,
>"change the default database to"
>While clicking in this box,we get a drop down listing of
>all the databases. Here I am stuck. Ideally the "newdb"
>database which I have created from connectdb.bak file,
>should be visible here so that I can choose to connect to
>it. But it's not visible here and unless it's visible here
>I can't do anything. The otherdatabases which were present
>in the test server since beginning are visible accept this
>one.
>Please help. It's urgent
>regards,
>Vishal
>|||If this is a new serer then the user exists in the
database but not in master. You first need to create the
login. Then synch up the users with sp_change_users_login
>--Original Message--
>Check to see if the login/user for whom you are setting up
>the DSN is a user in the database that has been restored.
It
>sounds like they users could have been orphaned.
>You can find a good article on orphaned users and
>troubleshooting techniques, links to KB articles at:
>http://vyaskn.tripod.com/troublesho...rphan_users.htm
>-Sue
>On Thu, 1 Apr 2004 08:40:14 -0800, "vishal kumar"
><anonymous@.discussions.microsoft.com> wrote:
>
steps
to
here
present
this
>.
>