About 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.
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?
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?
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?
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"?
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?
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.
I 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.
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?
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!)
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
I 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!
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?
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!!
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.
IS there a way inside code for vb.net to programmatically change sorting in a stored procedure???Reason is I need a two way sort and did not want to have to write a SP for each way ....If so Do you have an example.....
Everytime after I restore any database on the sql server 6.5, I have to recompile all the stored procedures in that database.Is there any method to overcome this problem?
Trying to run a SQL Server 6.5 stored procedure via ADO 2.0 in Visual Basic 6. The stored procedure moves roughly 10000 records from one table to another within the same database. The procedure works just fine when run from a SQL editor such as I/SQL, but only transfers a few hundred records when it is executed from a VB application using ADO 2.0. The guilty code can be seen below. Any thoughts?
Public cnSQL As New ADODB.Connection Public qry As New ADODB.Command
With cnSQL .ConnectionString = ConnectStringSQL .ConnectionTimeout = 20 .Open End With
Set qry.ActiveConnection = cnSQL qry.CommandType = adCmdStoredProc qry.CommandTimeout = 120
I have a stored procedure on SQL/Server 2000 that inserts into a #temptable. When I execute it from Query Analyzer it works fine. When I execute it from an ASP using ADO it gives me an Open Error message. When I comment out the #temptable the ASP works fine. Can someone shed some light on this? Do I have to do an ADO opent on the #temptable?
I come from a MySQL background, and have recently began migrating to SQL Server.
I can't, for the life of me, find an example of a stored procedure that does the following in SQL Server: - Multi-lined - Contains both input and output parameters - Sets one of the output parameters within the stored procedure
Can anyone provide some insight? I.e. Why does the following not work?
IF NOT EXISTS (SELECT * FROM syscomments WHERE id = object_id('usp_test')) BEGIN CREATE PROCEDURE "usp_test" (@num1 INT, @num2 INT, @result INT OUTPUT) AS BEGIN DECLARE newValue INT; SET @newValue = num1 + num2; SET @result = @newValue; END; END;
I get these errors (I'm using the Management Studio Express)
Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'PROCEDURE'. Msg 155, Level 15, State 2, Line 7 'INT' is not a recognized CURSOR option. Msg 137, Level 15, State 1, Line 8 Must declare the scalar variable "@newValue". Msg 137, Level 15, State 2, Line 9 Must declare the scalar variable "@newValue".
I am trying to determine which are undocumented system procedures used to satisfy our company's security audit. Well, the nature of undocumented system procedures is it's undocumented. Hence, it's hard to find.
So, is there a way I can get a list of SQL Server documented system procedures?
hi, if exists (select * from sysobjects where id = object_id('dbo.MRDD_FINAL') and sysstat & 0xf = 3) drop table dbo.MRDD_FINAL
This code was generated when I used the create a script to build a table from an existing table. is there a way to check if a a table contains data or not, The whole idea is to check if table A contains data, I need to truncate the table,otherwise I do nothing... regards
