July 18, 2008 at 4:33 am
Hi all!
Is there a way to alter system stored procedures in SQL Server 2K5?
When we try to run the script generated in Management Studio by right-clicking on any sp we obtain this error message:
"Msg 208, Level 16, State 6, Procedure sp_getqueuedarticlesynctraninfo, Line 44
Invalid object name 'sys.sp_getqueuedarticlesynctraninfo'."
Manipulating system objects at that level seems to be crucial for our project. I'm wondering if any of you could have a answer or workaround for this issue?
Regards,
Vlad.
July 18, 2008 at 4:43 am
thiras (7/18/2008)
Hi all!Is there a way to alter system stored procedures in SQL Server 2K5?
No. Neither the system tables nor the system procedures can be modified in any way
Manipulating system objects at that level seems to be crucial for our project.
Um, why?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 5:01 am
Thank you for your quick response.
We are moving from transactional replication process in SS2k to SS2k5. In SS2k we had a bunch of altered system sps. We need to use the same process, but in SS2k5 and have a very short time to do all that.
An example of one sp that works in previous version is:
USE [master]
GO
/****** Object: StoredProcedure [sys].[sp_getqueuedarticlesynctraninfo] Script Date: 07/18/2008 10:18:53 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER procedure [sys].[sp_getqueuedarticlesynctraninfo]
(
@publication sysname -- publication - cannot be null
,@artid int -- article id - cannot be null
)
as
begin
set nocount on
declare @retcode int
,@owner sysname
,@synctraninsproc sysname
,@synctranupdproc sysname
,@synctrandelproc sysname
,@cftprocname sysname
,@articlename sysname
,@tablename sysname
--
-- security check - should be dbo or sysadmin
--
exec @retcode = sp_MSreplcheck_publish
if @@error != 0 or @retcode != 0
return 1
if (object_id(N'dbo.sysarticleupdates') is null
or object_id(N'dbo.syspublications') is null)
return 1
--
-- get the information needed for resultset
--
select schema_name(objectproperty(a.sync_ins_proc, 'SchemaId'))
,object_name(a.sync_ins_proc)
,object_name(a.sync_upd_proc)
,object_name(a.sync_del_proc)
,object_name(a.ins_conflict_proc)
,art.name
,object_name(art.objid)
from dbo.syspublications p
join dbo.sysarticles as art on p.pubid = art.pubid
join dbo.sysarticleupdates as a on art.artid = a.artid
where art.artid = @artid
and p.name = @publication
--
-- all done
--
return 0
end
Why it is not possible to do so? Is because of any SS2k5 internal issue (security, administration, etc.)?
Thanks in advance.
Vlad.
July 18, 2008 at 5:09 am
thiras (7/18/2008)
Why it is not possible to do so? Is because of any SS2k5 internal issue (security, administration, etc.)?Vlad.
Reliability. Modifying system objects cause assorted problems in SQL 2000 due to misuse. It's like allowing end users to modify the kernal of the windows OS
You didn't really answer my question. What problems are you trying to solve by modifying the system objects?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 18, 2008 at 5:17 am
there is one thing I can say about modifying system stored procedures:
it is a big nono[/b]
If you do it, sooner or later, you're going to pay for it.
You just don't or you accept a full rework at upgrade/sp/hotfix/... time.
It's a blessing sql2005 (and 2008 ?) no longer lets you make this error.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data and code to get the best help
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Who am I ? Sometimes this is me but most of the time this is me
July 18, 2008 at 5:23 am
You didn't really answer my question. What problems are you trying to solve by modifying the system objects?
For example, this sp is called by an replication agent which pass a wrong parameter value of @artid. So, we need to set another value for the parameter @artid. We only could reach so by forcing the parameter value from a sp.
I'm wondering if you have any sugestions?
Thanks in advance.
Vlad.
July 18, 2008 at 8:51 am
thiras (7/18/2008)
Hi all!Is there a way to alter system stored procedures in SQL Server 2K5?
When we try to run the script generated in Management Studio by right-clicking on any sp we obtain this error message:
"Msg 208, Level 16, State 6, Procedure sp_getqueuedarticlesynctraninfo, Line 44
Invalid object name 'sys.sp_getqueuedarticlesynctraninfo'."
Manipulating system objects at that level seems to be crucial for our project. I'm wondering if any of you could have a answer or workaround for this issue?
Regards,
Vlad.
WE HAVE A SOLUTION!!! (GRANDE DUARTE;))
If you want to force a execution of a system stored procedure, with another values for parameters which not the default ones, drop that sp and recreate with new values.
Want to know more? Ask me how... 😛
Regards,
Vlad.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply