So every database i created has no system stored procedures. I didn't know how that could happen as I am not the person who installed sql2000. What should I do to get all the system stored procedures back?
TIA
Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.
For example, even this simple little guy:
CREATE PROCEDURE BOB
AS
PRINT 'BOB'
GO
Gets created as a system stored procedure.
Any ideas what would cause that and/or how to fix it?
Thanks,
Jason
Hi,
I plan to use my stored procedures to build the ad-hoc reporting model
Can someone please tell me where to use the OPENROWSET hack mentioned in this post (below) to create a datasource view for building the Report Model.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=345209&SiteID=1
Appreciate your help
Thanks.
Greetings:
I have MSSQL 2005. On earlier versions of MSSQL saving a stored procedure wasn't a confusing action. However, every time I try to save my completed stored procedure (parsed successfully ) I'm prompted to save it as a query on the hard drive.
How do I cause the 'Save' action to add the new stored procedure to my database's list of stored procedures?
Thanks!
We recently upgraded to SQL Server 2005. We had several stored procedures in the master database and, rather than completely rewriting a lot of code, we just recreated these stored procedures in the new master database.
For some reason, some of these stored procedures are getting stored as "System Stored Procedures" rather than just as "Stored Procedures". Queries to sys.Objects and sys.Procedures shows that these procs are being saved with the is_ms_shipped field set to 1, even though they obviously were not shipped with the product.
I can't update the sys.Objects or sys.Procedures views in 2005.
What effect will this flag (is_ms_shipped = 1) have on my stored procedures?
Can I move these out of "System Stored Procedures" and into "Stored Procedures"?
Thanks!
How to write Stored Procedures to load the Data Model from OLTP to DWH ?
View 9 Replies View RelatedAbout a year ago we inherited a SQL server (7.0) from another division of our company. The time has come to migrate the SQL Server functionality onto a new machine. Before I do this though, I need to figure out what the initial installation configuration was so I can set up the new server with the same specs. I am looking for a system stored procedure(s) that can tell me the following:
Case sensitivity, accent sensitivity, SQL build (SP), character set, etc. Basically I need to know what checkboxes were ticked during the initial set up of the server.
Any help would be greatly appreciated. TIA.
I am trying to edit a system stored(sp_add_dtspackage) procedure and cannot for the life of me find where to edit. This procedure does a check to keep users from saving changes to a package they do not own. I need users to be able to change the the packages when they are not always the one who created.
View 2 Replies View RelatedI have a SQL Server installation that is missing the stored procedures used for mail (xp_startmail, xp_stopmail, etc.). Is there a way that I can put these on the server without having to reinstall SQL? I tried to generate them from other servers but SQL won't allow you to do this with these particular stored procedures.
View 1 Replies View RelatedWhen I create a new database, the system stored procedures are missing;
dt_addtosourcecontrol_u
dt_checkinobject_u
etc.
I'm logged in as sa.
I need to create a stored procedure in the master database (yes, I know it's not that good of an idea). I'm working with SQL 2K5, SP2 Whenever I create it, it is marked as a system stored procedure no matter what I name it, what schema I put it in, or what user I use to create it (sysadmin or minimal permissions).
As soon as I create it, if I do any of the following, I can see it to be a system stored procedure and not a regular user sp.
1) SELECT * FROM sys.objects where is_ms_shipped = 1
2) SELECT * FROM sys.procedures where is_ms_shipped = 1
3) Looking in SSMS... There is a special folder for system stored procedures in SSMS, and mine is in there.
At least in my case, the only thing it hurts is that you have to be a sysadmin to execute that stored procedure (and I need to have a non sysadmin be able to execute it). Other than that, it executes normally when run by a sysadmin.
Any suggestions on why this is happening? It's only happening on 1 out of about 80 SQL servers we have.
I tried this morning to check some of the system stored procedures and ran into trouble. Only four of them executed: sp_alterdiagram, sp_creatediagram, sp_dropdiagram and sp_helpdiagramdefinition. I could not check all the rest, I did it selectively. The typical error message was: Invalid object on RETURN statement. Some had syntactical errors.
sp_ActiveDirectory_Obj
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_SCP
Invalid object name 'sys.sp_ActiveDirectory_SCP' Line 171
sp_ActiveDirectory_Start
Invalid object name 'sys.sp_ActiveDirectory_Start' Line 19
Invalid object name 'sys.sp_add_agent_parameter' Line 60
Invalid object name 'sys.sp_add_agent_profile' Line 123
Invalid object name 'sys.sp_add_data_file_recover_suspect_db' Line 17
sp_addalias
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 44
Incorrect syntax near '%'.
Msg 195, Level 15, State 10, Procedure sp_addalias, Line 64
'get_sid' is not a recognized built-in function name.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 78
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_addalias, Line 119
Incorrect syntax near '%'.
sp_bindefault
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 95
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 134
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 182
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 208
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 228
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 264
Incorrect syntax near '%'.
Msg 102, Level 15, State 1, Procedure sp_bindefault, Line 273
Incorrect syntax near '%'.
sp_databases
Invalid object name 'sys.sp_databases'. Line 6
sp_tables
Invalid object name 'sys.sp_tables'
Is there a chance that these errors are due to the fact that I executed them without parameters?
Thanks
I know there are a lot of undocumented system stored procedures such as xp_ntsec_enumdomains, xp_instance_regread etc, that exist on SQL Server.
Does anyone know of any good websites that contain descriptions of what these stored procedures do? In particular I know that a default installation of SQL Server 2000 leaves permission to execute many of these granted to public and I am interested in finding out what the implications of each one of these are? I have tried Googling this topic but there doesn't seem to me much or there (or what is there is in Chinese and I don't really want to click on to!)
Once again thanks for the help.
i found that some stored procedure get created automatically in sqlserver 2000 (system stored procedures) ,while doing my work i accidentally deleted those stored procedures can any body answer following questions1: why these stored procedures are there and automatically created2: what happen en if i deleted those stored procedures3: how to recreate those stored procedures with limited user permission thanks in advance
View 2 Replies View RelatedI recall that stored procedures created in the master database and having a name beginning with sp_ are available from all databases. However, I've found varying results when invoking such a procedure from a different database. For example:
DECLARE tables CURSOR FOR
select name from sysobjects
where type='U'
order by name
for read only
populates a cursor with the list of tables in the database from which you are running the procedure. However this query in the same stored procedure:
SELECT count(*)
FROM sysExcludeMaint
WHERE @tablename like tablename
fails with "Invalid object name 'sysExcludeMaint'." if the table sysExcludeMaint
doesn't exist in the master database (it does exist in the database from which I've invoked the proc). I'm not clear on why the different results. Anyone know?
A system stored procedure got accidentally deleted, and all backups aresince the stored procedure was deleted (wonderful!)Can the SQL for the stored procedure be extracted from another serverand loaded as opposed to removing everything and then rebuilding theserver?Thanks in advance!
View 1 Replies View RelatedHello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?
Thanks in advance.
how to implement the replication with the System Stored Procedures or the Transact-SQL ?
i want to encapsula my replication and make it be reusable and ease to use.So i want to do the replication in a script file.how can i do this?
Thanks.
Hi there
View 5 Replies View RelatedWhat system stored procedures have changed between SQL 2000 and SQL 2005?
View 4 Replies View RelatedHello,
We are trying to be proactive and stop a potential performance issue by reducing the number of recompiles in our SQL 2000 database application. This database is replicated. After viewing output from Profiler and PerfMon it seems that over 90% of the recompiles are due to system stored procedures generated by replication merge agents. Can anything be done about this?
Thanks
disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies View RelatedHi all,
I would like to enable users that do not belong to groups (server roles) such as sysadmin, serveradmin and don't have db permissions such as ddl_admin or db_owner to run some of the system stored procedures (such as sp_addumpdevice sp_configure sp_serveroption ...) and DBCC commands (such as DBCC CHECKFILEGROUP - requires ob_owner or sysadmin permission).
Is it possible to change permissions of SQL Server system stored procedures?
Is it possible to change permissions of SQL Server DBCC commands?
Thanks,
Assaf
Hi there,
My problem is : No de system stocked procedure in the system db "model".
What should I do to visualise theses sp.
any help will be appreciated.
Thanks in advance
Hi there,
My problem is : No de system stocked procedure in the system db "model".
What should I do to visualise theses sp.
any help will be appreciated.
Thanks in advance
I'm running SQL2005. The application running under SQL are a small accounting software and Microsoft WSUS. The file that is over 28G is the system model log file (modellog.ldf). I tried shrinking it and that only shrunk it by 2G. Is the model log file supposed to get that big, because the primary model data file is only 1Mg. How do I fix this? Can I delete the model log file?
Thanks
Willie
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?
I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!
View 11 Replies View RelatedQuestion: Is it feasible to use a star schema dimensional model for an OLTP system that incurs few (750 per day)Sales Orders transactions?
Background: My customer wants to replace an existing OLTP system database because it runs on Oracle and their in-house expertise is in SQL Server. The original database developers that designed the Oracle DB have apparently retired. The Oracle database has been over-normalized, to say the least. The number of sales orders being entered daily is small: about 500-750 per day. These entries are done at the five clerks' convenience, from a paper form, and are very unlikely to ever be entered in quick succession. Nothing else gets regularly entered into this database except for the occasional change to a customer, but new customers are very few and far between.
I've designed a star schema for the replacement database with the Sales Order Header and Sales Order detail table combined into a single 'fact' table, and I've introduced some duplication into dimension tables (like customer) in order to eliminate some of the joins (and confusion) that were built into the original database.
I've never tried this before. Is there any reason this would not or should not work?
I starting getting the error above today when attempting to deploy updated DSV's and Report models. I can't seem to locate any info about this error - seems like a generic one. We're on 2005 SP1 (think we're on build 2221). Any help is appreciated!
View 5 Replies View RelatedHi,
This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.
Thank you in advance for any help on this matter
Hi all
as like there is a query in oracle as
select * from tab
we get all tables
Like wise is their any query in sql server 2005
b'coz
I want all system procedures
as like sp_password or sp_help
i want list of all procedures
for that i wrote query as
select * from sysobjects
but in output i don't find any name starting with sp_
Thanks in advance
Malathi Rao
hi, i see there are lots of system store procedures available but could you specify me the ones that are used in day to day work as a DBA??
View 6 Replies View Related