SQL 2005 System UDF Function And Procedures
Jul 15, 2005In SQL 2000 for Procedures
View 5 Replies
ADVERTISEMENT
What System Stored Procedures Have Changed Between SQL 2000 And SQL 2005?
Aug 2, 2006What system stored procedures have changed between SQL 2000 and SQL 2005?
View 4 Replies View RelatedAll 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
System Procedures
May 2, 2007Hi 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
Imp System Procedures
Mar 16, 2008hi, 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 RelatedUndocumented System Procedures
May 21, 2015I 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?
Sql Server 2005 Operating System Compatibility Warning Message In System Configuration Check
Apr 3, 2007Ok guys, I am trying to install Sql server 2005 on Vista but I am still stuck with this warning message in the System Configuration Check during Sql server 2K5 installation :
SQL Server Edition Operating System Compatibility (Warning)
Messages
* SQL Server Edition Operating System Compatibility
* Some components of this edition of SQL Server are not supported on this operating system. For details, see 'Hardware and Software Requirements for Installing SQL Server 2005' in Microsoft SQL Server Books Online.
Now, I know I need to install SP2 but how the hell I am going to install SP2 if Sql server 2005 doesn't install any of the components including Sql server Database services, Analysus services, Reporting integration services( only the workstation component is available for installation)?
Any work around for this issue?
P.S.: I didn't install VS.NET 2005 yet, can this solve the problem?
Thanks.
SQL Server 7.0 System Stored Procedures
May 1, 2001About 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.
Model Db Got No System Stored Procedures
Nov 7, 2001So 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
Modify System Stored Procedures
Oct 31, 2002I 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 RelatedMissing System Stored Procedures
Aug 28, 2002I 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 RelatedMissing System Stored Procedures
Feb 25, 2005When I create a new database, the system stored procedures are missing;
dt_addtosourcecontrol_u
dt_checkinobject_u
etc.
I'm logged in as sa.
Stored Procedures Getting Created As System
Nov 8, 2007I 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.
System Stored Procedures Do Not Execute
Oct 23, 2006I 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
Undocumented System Stored Procedures
May 15, 2007I 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.
About Default Created System Stored Procedures
May 12, 2008i 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 RelatedSystem Stored Procedures/behavior Not Understood
May 11, 2004I 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?
Recreating/reloading System Stored Procedures
Jul 21, 2006A 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 RelatedExport/import A Non System Stored Procedures From One DB To Another
Aug 23, 2006Hello, 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 TransactSQL?
Jun 8, 2004how 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.
Replication System Stored Procedures Parameter Defaults ?
Oct 27, 2005Hi there
View 5 Replies View RelatedMerge Agent System Stored Procedures And Sp_Recompile
Jul 13, 2006Hello,
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
How To Create Custom System Function
Feb 13, 2008Greetings,
I need to create a function that is available across all databases. This function is for exchange rate conversions and will be used extensively. I'd prefer not having to call it by it's full four-part name and just make it available everywhere on the server.
Is there a way to create such a function? Where is it stored?
Rob
System Function To Get Current T-SQL Object Name?
Feb 1, 2008We're building a simple logging facility and would like to systematically determine the name of the current T-SQL object(i.e., the procedure name or function name) to provide this as a logging parameter. For example, in procedure FooBar, I'd like to be able to call a system function that will return 'FooBar', the name of the current object. Does such a feature exist?
Thanks - Dana
Non-deterministic System Function Suser_sname
Dec 8, 2007Hi,
I am using Sql Server 2005 as the database management and Access 2003 as the front-end. In the database, I intend to give different views of tables to different users. That's why I used suser_sname system function, which returns the windows login id and authenticates users to see different records in the same view. What I want to do in Access is, allowing some specific users to be able to do update, insert and delete operations through a form based on this view (which only depends on 1 table). However, Access tells me that "the recordset is not updateable". In order to be able to change records, I tried to create new index for the view in Sql Server, which failed giving "Sql Server, Error number:1949" and telling me that it fails since suser_sname yields non-deterministic results. The strange thing is that when I open VB Editor in Access and write a simple update code within this form, it updates both the view and the table in the database. My question is: How can I do update, delete and insert operations on the form directly? Is there a way to do the authentication without using a nondeterministic function in Sql Server or using the front-end Access 2003? Maybe a function similar to the current_user function in Access can do that, I don't know.
It's been a long question but I desperately need the answer. Any thanks will be appreciated.
Should I Use Function Calls In My Procedures
Feb 4, 2008hi all.
when i worked with oracle before, it was very common to call a function from inside a s.procedure.
now, someone talled me that in sqlserver this is not a best performance approach, and i should try to avoid this.
is this true?
Why User Defined Procedure Shows Under System Procedures In Object Explorer
Jan 17, 2008I have created a store procedure in sql server 2005.
At first, it shows up as a normal user defined store procedure in Object explorer.
Recently, I found I could not found the procedure in normal place. But the execution is fine.
Finally I found it stays under system procedure folder.
How this could happen?
SQL Security :: How To Disable User Access To The Critical And System Stored Procedures
Sep 22, 2015disable the following system stored procedure (xp_grantlogin and xp_revokelogin)?
View 5 Replies View RelatedIs It Possible To Change Permissions Of SQL Server System Stored Procedures And DBCC Commands?
May 8, 2008Hi 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
Access Linked Server System Function
Mar 16, 2007Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies View RelatedCLR Function Error 6522 Using System.IO Namespace
Mar 4, 2008I created a clr proc that gets the most recent file within a directory based on the creation time property, see code below. I have attempted to replicate this within a clr scalar valued function in order to assign the resulting value to a variable within SQL server. I am getting the error message:
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "clr_fn_recentfile":
System.InvalidOperationException: Data access is not allowed in this context. Either the context is a function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read, is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.
System.InvalidOperationException:
at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_CurrentContext()
at Microsoft.SqlServer.Server.SqlContext.get_Pipe()
at clr_fn_recentfile.clr_fn_recentfile.clr_fn_recentfile(SqlString Filepath)
After trying to troubleshoot this I am aware that this error is rather generic and have not been able to find any specific documenation regardint the use of file system objects with clr functions. I am at a loss. Any help would be appreciated!
STORED PROC CODE WORKS
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_recentfile
_
Public Shared Sub clr_recentfile(ByVal Filepath As SqlString)
Dim strFile As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim maxDate As Date
Dim fil As String
Dim qry As New SqlCommand()
Try
If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString)
Path.GetFileName(strFile)
fil = Path.GetFileName(strFile).ToString
Dim fi As New FileInfo(strFile)
If maxDate = Nothing Then
maxDate = fi.CreationTime
fil = fi.FullName.ToString
Else
If maxDate < fi.CreationTime Then
maxDate = fi.CreationTime
End If
End If
Next
Else
sp.Send("Directory does not exist")
Return
End If
If fil <> Nothing Then
qry.CommandText = " SELECT '" & fil & "'"
'Execute the query and pass the result set back to SQL
sp.ExecuteAndSend(qry)
sp.Send(qry.CommandText.ToString)
End If
Catch ex As Exception
sp.Send(ex.Message.ToString)
End Try
End Sub
End Class
FUNCTION CODE DOES NOT WORK WITH ABOVE ERROR
Code Snippet
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.IO
Partial Public Class clr_fn_recentfile
<Microsoft.SqlServer.Server.SqlFunction()> _
Public Shared Function clr_fn_recentfile(ByVal Filepath As SqlString) As SqlString
Dim strFile As String
Dim sp As SqlPipe = SqlContext.Pipe()
Dim maxDate As Date
Dim fil As String
Dim qry As New SqlCommand()
Try
If Directory.Exists(Filepath.ToString) Then
For Each strFile In Directory.GetFiles(Filepath.ToString)
Path.GetFileName(strFile)
fil = Path.GetFileName(strFile).ToString
Dim fi As New FileInfo(strFile)
If maxDate = Nothing Then
maxDate = fi.CreationTime
fil = fi.FullName.ToString
Else
If maxDate < fi.CreationTime Then
maxDate = fi.CreationTime
End If
End If
Next
Else
sp.Send("Directory does not exist")
Exit Function
End If
If fil <> Nothing Then
Return fil
End If
Catch ex As Exception
sp.Send(ex.Message.ToString)
End Try
End Function
End Class
ClR Function Errror Cant Get To Lcoal File System
Apr 4, 2007My users complain that they cant run a CLR function. I am told that it cant get access to the local file system. I do not how to code these so from SSIS is there any way to let the users gain access to this. If this is a permission issue what is the lease privilege that I can configure for this to work?
Thanks
AdminAnup
Diff. B/w Stored Procedures And Function??
Nov 28, 2006Diff. b/w Stored Procedures and Function??
When any of them is appropriate to use?