System Stored Procedures/behavior Not Understood

May 11, 2004

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?

View 2 Replies


ADVERTISEMENT

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



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

View 16 Replies View Related

How To Save Stored Procedure To NON System Stored Procedures - Or My Database

May 13, 2008

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!

View 5 Replies View Related

Stored Procedure Being Saved In System Stored Procedures

Apr 7, 2006

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!

View 24 Replies View Related

SQL Server 7.0 System Stored Procedures

May 1, 2001

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.

Any help would be greatly appreciated. TIA.

View 2 Replies View Related

Model Db Got No System Stored Procedures

Nov 7, 2001

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

View 1 Replies View Related

Modify System Stored Procedures

Oct 31, 2002

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 Related

Missing System Stored Procedures

Aug 28, 2002

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.

View 1 Replies View Related

Missing System Stored Procedures

Feb 25, 2005

When I create a new database, the system stored procedures are missing;

dt_addtosourcecontrol_u
dt_checkinobject_u
etc.

I'm logged in as sa.

View 5 Replies View Related

Stored Procedures Getting Created As System

Nov 8, 2007

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.

View 4 Replies View Related

System Stored Procedures Do Not Execute

Oct 23, 2006

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

View 2 Replies View Related

Undocumented System Stored Procedures

May 15, 2007

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.

View 3 Replies View Related

About Default Created System Stored Procedures

May 12, 2008

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 Related

Recreating/reloading System Stored Procedures

Jul 21, 2006

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 Related

Export/import A Non System Stored Procedures From One DB To Another

Aug 23, 2006

Hello, I want to export/import a non system stored procedures from one DB to another. How Should I proceed ?

Thanks in advance.

View 1 Replies View Related

How To Implement The Replication With The System Stored Procedures Or TransactSQL?

Jun 8, 2004

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.

View 9 Replies View Related

Replication System Stored Procedures Parameter Defaults ?

Oct 27, 2005

Hi there

View 5 Replies View Related

What System Stored Procedures Have Changed Between SQL 2000 And SQL 2005?

Aug 2, 2006

What system stored procedures have changed between SQL 2000 and SQL 2005?

View 4 Replies View Related

Merge Agent System Stored Procedures And Sp_Recompile

Jul 13, 2006

Hello,

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

View 3 Replies View Related

SQL Security :: How To Disable User Access To The Critical And System Stored Procedures

Sep 22, 2015

disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?

View 5 Replies View Related

Is It Possible To Change Permissions Of SQL Server System Stored Procedures And DBCC Commands?

May 8, 2008

Hi 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

View 1 Replies View Related

Erratic File System Task Behavior

Jan 3, 2008

Hello all-
I am currently trying to deploy a production package to our servers and am seeing some very strange behavior. I am attempting to use a File System Task inside a Foreach Loop Container that renames (i.e. moves) a file from a processing location, appends a date, and places it in a different directory. The source file is coming from a connection manager whose file name is generated dynamically. The destination file is coming from a variable that is set in the foreach as a preceding task. I experience two different behaviors for the File System task depending on where/how I run the package...

Inside the VS solution on the server & from the SSIS MSDB store on the server
The package behaves as intended. The file is correctly processed then renamed/moved to the correct location.

From the SSIS MSDB store on my local machine
The file is correctly processed, however strange behavior occurs in the renaming task. The informational message in the execution window says that it deleted the newly renamed file (the same message displayed in the VS solution results). Upon checking the file system, the file is neither in the source directory nor in the destination.

Does anyone have some insight as to why these might be behaving differently? My only guess is that it's some sort of file system permission (even though I have domain credentials for a local admin on the server), but I can't imagine why it would allow me to delete the file and not rename it... Though finding the source of the problem would be nice, my biggest question is whether or not the package will behave correctly if I setup a schedule for it.

View 6 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

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 Related

Stored Procedure Behavior

Jun 4, 1999

I have a stored procedure that does a lookup on a particular field. Sometimes it runs almost instantly and other times it drags. Running the proc through ISQL it will come back with less than 10 logical reads and other times it comes back with over 800,000 logical reads. This only happens on our production box(of course). Anybody seen anything like this? Thanks
-Bob-

View 1 Replies View Related

Odd Stored Procedure Behavior

Feb 8, 2007

We have a stored procedure that we've tried with two slightlydifferent designs. It needs to take a 30 day date range and return aresult set.Design 1 takes one date as a parameter. The other date is calculatedin a local variable to be 30 days before the one that was passed in.Both data types are datetime and are in the where clause.Design 2 takes two dates as parameters with the 30 days beingcalculated outside the stored procedure, both in the where clause.There's some joins, but the main table has maybe 20 million rows.This is sql server 2000.Design 1 takes maybe 30 mintues to run. Design 2 runs 15 timesfaster.The plan says that Design 1 is doing a table scan on the 20 millionrow table. For Design 2, the plan says it's doing a bookmark lookupon the date in question.Why?brian

View 1 Replies View Related

Strange Stored Proc Behavior

Jun 23, 2000

I have a stored proc with the following two lines of code:

Select @SumCredits = (Select Sum(CreditAmount) From AccountBalanceList WITH (NOLOCK) Where (AccountRowId = 2000) And (DocDate >= '1/1/1900' And DocDate <= '12/31/2099'))

Select @SumDebits = (Select Sum(DebitAmount) From AccountBalanceList WITH (NOLOCK) Where (AccountRowId = 2000) And (DocDate >= '1/1/1900' And DocDate <= '12/31/2099'))

If I execute this stored proc via Query Analyzer, it will take about 11 seconds. If I execute the above two SQL statements indiviudally within Query Analyzer, each takes less than a second (the entire stored proc should take about a second). This hasn't always been happening. Just recently this behavior started occuring - after we imported a large amount of data into our database. However, I don't know if the two events are related.

Has anyone ever noticed this type of thing?

View 1 Replies View Related

Stored Procedure Inconsistence Behavior.

Mar 29, 2007

I have SQL Server 2005 9.0.3050 running on a machine with 3.4GHz and 3.00GB of memory. I have a C# stored procedure that is marked as SAFE. In the SP I am using two DataTables. Table 1 contains just the schema of the table that will be updated. Table 2 contains the data to be manipulated. The result set can contain 10,000 to 150,000+ rows of data. I can run the SP and get 50,000 to 80,000 records returned and stored in Table 2 and it works fine no errors. The problem I keep running into and can€™t seem to figure out is when I have 150,000 records returned and stored in Table2 and start to loop through the DataRows and manipulate the data. I will get the following error message.
Executed as user: NT AUTHORITYSYSTEM. .NET Framework execution was aborted by escalation policy because of out of memory. System.Threading.ThreadAbortException: Thread was being aborted. System.Threading.ThreadAbortException: at System.Data.Common.DecimalStorage.SetCapacity(Int32 capacity) at System.Data.RecordManager.set_RecordCapacity(Int32 value) at System.Data.RecordManager.GrowRecordCapacity() at System.Data.RecordManager.NewRecordBase() at System.Data.DataTable.NewRecord(Int32 sourceRecord) at System.Data.Data. The step failed.
I€™ll stop SQL Server then restart it and sometimes that will do the trick and other times it won€™t. I have exception handling everywhere and when I debug it I can€™t recreated that error. Any help would be greatly appreciated.

Thanks
Mac

View 1 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



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



View 1 Replies View Related

System Procedures

May 2, 2007

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

View 20 Replies View Related

Imp System Procedures

Mar 16, 2008

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

Undocumented System Procedures

May 21, 2015

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?

View 10 Replies View Related

SQL 2005 System UDF Function And Procedures

Jul 15, 2005

In SQL 2000 for Procedures

View 5 Replies View Related

Peculiar Behavior In Stored Procedure (outputs Are Returning Proper Vals For Uniqueidentifiers And Ints, Not Nvarchars)

Apr 27, 2005

Rather than the real code, here's a sample we came up with.
 
Here's the C# Code:
public class sptest : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Label Label1;
private DataSet dtsData;

private void Page_Load(object sender, System.EventArgs e)
{
// Put user code to initialize the page here
string strSP = "sp_testOutput";
SqlParameter[] Params = new SqlParameter[2];
Params[0] = new SqlParameter("@Input", "Pudding");
Params[1] = new SqlParameter("@Error_Text", "");
Params[1].Direction = ParameterDirection.Output;
try
{
this.dtsData = SqlHelper.ExecuteDataset(ConfigurationSettings.AppSettings["SIM_DSN"], CommandType.StoredProcedure, strSP, Params);
Label1.Text = Params[0].Value.ToString() + "--Returned Val is" + Params[1].Value.ToString();
}
//catch (System.Data.SqlClient.SqlException ex)
catch (Exception ex)
{
Label1.Text = ex.ToString();

}
}
 
Here is the stored procedure:
 
CREATE PROCEDURE [user1122500].[sp_testOutput](@Input nvarchar(76),@Error_Text nvarchar(10) OUTPUT)AS
SET @Error_Text = 'Test'GO
When I run this, it prints up the input variable, but not the output variable.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved