User Stored Procedures
Jul 10, 2007Dear all,
How to find out how many user stored procedures are there in a single Database in sql server.
Thanks,
msrs
ADVERTISEMENT
User Stored Procedures
Nov 30, 2006is there a sql query i can type in to list all the user stored procedures for the tables within my database?
or do i have to find them manually and how?
Using User Functions In Stored Procedures
Feb 1, 2005Hi There,
I've written an inline table-valued function in SQL such as the following:
ALTER FUNCTION dbo.GetCityByID( @CityID int)
RETURNS TABLE
AS
RETURN(
SELECT
Name,
Url
FROM Cities
WHERE (CityID = @CityID) )
suppose that Cities table includes three fields (CityID, Name, Url).
By the way I wrote a store procedure as follow:
ALTER PROCEDURE MyProcedure ( @MyID int)
AS
SELECT
CountryID,
OriginCityID,
DestCityID
FROM
MyTable
WHERE (MyID = @MyID)
The OriginCityID and DestCityID are related to CityID in Cities table. I wanna get the name
and url of each city by its ID through this stored procedue by making relation to Cities table.
so I call GetCityByID function in my stored procedure like this:
ALTER PROCEDURE MyProcedure ( @MyID int)
AS
SELECT
CountryID,
dbo.GetCityByID(OriginCityID),
dbo.GetCityByID(DestCityID)
FROM
MyTable
WHERE (MyID = @MyID)
this procedure dosn't work an returns error.
What's your solution for getting information from Cities table for OriginCityID and DestCityID?
Thank you in advance.
SQL 2012 :: User Is Not Able To See Stored Procedures
Jun 12, 2015user have db_datareader role in in a database.But user is not able to see the stored procedures.
View 5 Replies View RelatedUser Who Can Connect/run Stored Procedures Only?
Nov 6, 2007I'm new to 2005 (new to sql server in general). Up until now it's only been writing easy stored procedures. Read that it's a good idea to have a user who can only run stroed procedures (so aren't connceting with 'sa' privileges and such). How would I go about doing this?
The server I am working on has about 15 different databases on it. My database (named "myDBForTest") is the only one I want my end user to be able to connect on. I'm writing a .NET application that will have several users using it at same time. They will all be connecting with same information (limited to only running stored procedures and connecting/disconnecting). I basically want them to be able to run any stroed procedures that are part of my database. If I go back in 4 months and add 100 stored procedures, the next time the log on they should be able to use those new ones also.
Is there some kind of command I run to add a user (maybe with username of sprocUser?) who can do this? I'm interfacing with database using SQL Server Management Studio Express (from MS).
Thanks for any help you can give.
Lost User Stored Procedures
Jun 2, 2007Hi,
I presume the two problems I have are actually one. (hopefully)
I copied my database from my client machine, to a small business server when I deployed the application.
When I ran the program, where it called up a stored procedure, I got an error message
The EXECUTE permission was denied on the object 'Empty Temp Tables' database datbasename, schema 'dbo'
The procedures are there and visible in the Visual Studio IDE and they were there in the Sql Data directory, from where I copied the database files. I checked this from Management Studio on the client machine.
I decided to check my stored procedures in Management Studio on the server, and it seems the whole folder is missing.
Help would be appreciated
User Defined Functions To Stored Procedures
Jun 1, 2004Hello all:
Running into a brain problem here. I remeber reading an article a while back (2002?) on either Visual Studio Magazine or MSDN Magazine where there was a way to generate Stored Procedures from User Defined Functions. I need this information in order to do my job as it is also a way to cut down on time for this project I am trying to finish. Does anyone have the code or remeber what I am talking about. I just finished Kathleen Dollards article again on using XSLT to generate code but would really like to use the User Defined Functions.
I searched for the article on line but came up dry. Searched through all my magazines but could not find the article. Any help would be greatly appreciated. Bit of topic I guess but still relevant to the board.
Thanks
SQL 2012 :: How To Hide Stored Procedures From User
Sep 25, 2015How can I allow a user to run a stored procedure but deny them the ability to see it in SSMS?
I don't mean 'view def' permissions, I mean the actual proc.
I read about encryption which mask the contents but they can still see the proc, any other ways I can accomplish this?
How Use Stored Procedures In Multi-user Environment?
Apr 19, 2007Hi!
In my database I have all business logic in stored procedures. For example there are procedures: ReadBike and UpdateBike. Bike is business object stored in 4 tables.
On my system work 100 employees and we have one problem with this. The stroy is;
1. User A reads data about Bike1
2. User B reads data about Bike1
3. User A updates data about Bike1 (user B have old data)
4. User B updates data about Bike1
So user B don't know about chnages made by user A. How to solve that ptroblem?
It's probably solved in ADO, but I want use business login in procedures.
Regards,
Walter
Stored Procedures And User Defined Functions
Feb 4, 2006What are the pros and cons of each?
One advantage that I can see withh UDFs is that they are a bit a Views with parameters. You can perform joins on UDF columns (which you cannot do with a Stored Proc). You can do the same with Views but UDFs have the advantage that you restrict the number of rows with a parameterised WHERE (or HAVING) clause.
Diff Between User Defined Functions And Stored Procedures
Jun 19, 2008Hi to all, Can any body tell me what is the difference between Stored procedures and User Defined Functions ? In my assumption Function return a value or table, but SP doesn't return value instead of that SP use select statement or assign value to output statement. This is right?
View 3 Replies View RelatedUser Defined Data Types And Stored Procedures
Mar 13, 2001I have defined a user defined data type. When I try to create a stored procedure specifying the column and user define data tpye I receive message
Server: Msg 2715, Level 16, State 3, Procedure spStoredproc, Line 0
Column or parameter #1: Cannot find data type udtcol1.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #2: Cannot find data type udtcol2.
Server: Msg 2715, Level 16, State 1, Procedure spStoredproc, Line 0
Column or parameter #3: Cannot find data type udtcol3
Can you have user defined data types in stored procedures.
Store Procedure creation text
CREATE PROCEDURE spStoredproc
@col1 udtcol1,
@col2 udtcol2,
@col3 udtcol3
AS
INSERT INTO tblTempEmployee
(col1 , col2 , Col3)
VALUES (@col1 , @col2, @col3)
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS OFF
GO
Dave
Can A User Be Granted Read Only Privileges For Stored Procedures
Apr 26, 2004Can a user be granted the ability to create stored procedures with read only capabilities? I wouldn't mind be able to UPDATE but simply need to read only.
ddave
SQL 2012 :: Login / User Cannot Execute Or See Stored Procedures
May 6, 2014We are using a third party tool that does not store passwords in an encrypted format therefore we created a user with minimal rights. Isn't there a way to grant "execute any stored procedure" to a user/Login? Do we really have to grant execute on each procedure to the user? And then do the same for each new store procedure? The only other option we have found to be able to "see" and execute the stored procedures is by granting "db_owner". I would think that would negate the user being minimal rights.
View 5 Replies View RelatedHow Can I Execute Stored Procedures With Correct User Rights?
Sep 3, 2007Hi,
I have a problem with sp execution.:
objects of [dbo]
Tables of [nuran]
Grants of [nuran]
[dbo].tabloA
[nuran].tmptabloA
Select,insert, update on [dbo].tabloA
[dbo].tmptabloA
Deny for [dbo].tmptabloA
[dbo].sp_yordam
Grant for executing [dbo].sp_yordam
(1)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
BEGIN TRANSACTION @Tran1
¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [tmptabloA]
COMMIT TRANSACTION @Tran1
When user [nuran] execute the procedure sp_yordam by a VB program, the procedure use [dbo].tmptabloA not [nuran].[tmptaboA]. If there are data in the [dbo].tmptabloA, the procedure insert data to [dbo].tabloA from [dbo].tmptabloA. But when I checked user name in the procedure during execution, the user was [nuran].
If I write the procedure like that:
(2)
create PROCEDURE [dbo].[SP_tmpSil]
AS
declare @tablo1 as varchar(50),
DECLARE @sil as nvarchar(max)
select @tablo1='[tmptabloA]'
SELECT @sil = ' DELETE FROM ' + @tablo1 + ';'
EXEC (@sil)
END
And it executed by user [nuran],then it used the correct table [nuran].tmptabloA
Is there any way to use users table in an stored procedure without using the user name :
(3)
create PROCEDURE [dbo].[SP_yordam]
AS
BEGIN
BEGIN TRANSACTION @Tran1
¦¦¦¦¦. ¦¦¦¦¦¦ ¦¦¦¦.
INSERT INTO [tabloA]
(, ,)
SELECT ,,
FROM [nuran].[tmptabloA] WHERE ......
¦¦¦ ¦¦¦ ¦¦¦.
DELETE FROM [nuran].[tmptabloA]
COMMIT TRANSACTION @Tran1
I don't want to use (2) and (3) code methods, I prefer to use (1) script. Is there any compilation method, or any aditional way for using script (1) with correct user rights?
Thanks a lot
Nuran
Do You Replicate Views, Stored Procedures, And User Functions?
Jul 22, 2007Greetings,
We have recently begun using transactional replication to keep the data in our SQL Servers synchronized in a geographically dispersed environment. We replicate our tables but we have never replicated views, stored procedures, or user functions in our production systems. We are thinking of doing so but wonder if the overhead of running the replication agents doesn't outweigh the benefits of having replication assist with the occassional change to these design elements.
Is anyone on this forum replicating views, sprocs, and user functions? What has your experience been?
Thanks for any ideas that you share.
BCB
List Of Stored Procedures With Permission For Executing For User
Jul 26, 2007I have user XY in SQL 05. I would like to find all stored procedures, where user XY has permission for executing. Is there any way to find it than look in every stored procedure?
Thanks for tips
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 RelatedCannot Debug Stored Procedures Because The SQL Server Database Is Not Setup Correctly Or User Does Not Have Permission To Execute Master.sp_sdidebug.
Aug 7, 2007Hello All,I tried to set the access permissions for debugging stored procedure by reading the articlehttp://msdn2.microsoft.com/en-us/library/w1bhybwz(VS.80).aspxandhttp://technet.microsoft.com/en-us/library/ms164014.aspxI have tried to add the role to sysaminas follows1)SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = 'sp_sdidebug'(to find the sp)Error:--The stored procedure not found2)sp_addsrvrolemember 'Developmentswati.jain', 'sysadmin' though this is executed successfuly . Error is still persisting
Cannot debug stored procedures because the SQL Server database is not setup correctly or user does not have permission to execute master.sp_sdidebug.
User-defined Stored Procedures InsertCustomerin Northwind Database That Is Cached In Database Explorer:No New Values Inserted?
Jan 14, 2008Hi all,
I put "Northwind" Database in the Database Explorer of my VB 2005 Express and I have created the following stored procedure in the Database Exploror:
--User-defined stored procedure 'InsertCustomer'--
ALTER PROCEDURE dbo.InsertCustomer
(
@CustomerID nchar(5),
@CompanyName nvarchar(40),
@ContactName nvarchar(30),
@ContactTitle nvarchar(30),
@Address nvarchar(60),
@City nvarchar(15),
@Region nvarchar(15),
@PostalCode nvarchar(10),
@Country nvarchar(15),
@Phone nvarchar(24),
@Fax nvarchar(24)
)
AS
INSERT INTO Customers
(
CustomerID,
CompanyName,
ContactName,
ContactTitle,
Address,
City,
Region,
PostalCode,
Country,
Phone,
Fax
)
VALUES
(
@CustomerID,
@CompanyName,
@ContactName,
@ContactTitle,
@Address,
@City,
@Region,
@PostalCode,
@Country,
@Phone,
@Fax
)
=================================================
In my VB 2005 Express, I created a project "KimmelCallNWspWithAdoNet" that had the following code:
--Form_Kimmel.vb--
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form_Kimmel
Public Sub InsertCustomer()
Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _
"Initial Catalog=northwind;Data Source=NAB-WK-EN12345"
Dim connection As SqlConnection = New SqlConnection(connectionString)
connection.Open()
Try
Dim command As SqlCommand = New SqlCommand("InsertCustomer", connection)
command.CommandType = CommandType.StoredProcedure
command.Parameters.Add("@CustomerID", "PAULK")
command.Parameters.Add("@CompanyName", "Pauly's Bar")
command.Parameters.Add("@ContactName", "Paul Kimmel")
command.Parameters.Add("@ContactTitle", "The Fat Man")
command.Parameters.Add("@Address", "31025 La Jolla")
command.Parameters.Add("@City", "Inglewoog")
command.Parameters.Add("@Region", "CA")
command.Parameters.Add("@Counrty", "USA")
command.Parameters.Add("@PostalCode", "90425")
command.Parameters.Add("@Phone", "(415) 555-1234")
command.Parameters.Add("@Fax", "(415 555-1235")
Console.WriteLine("Row inserted: " + _
command.ExecuteNonQuery().ToString)
Catch ex As Exception
Console.WriteLine(ex.Message)
Throw
Finally
connection.Close()
End Try
End Sub
End Class
==============================================
I executed the Form_Kimmel.vb and I got no errors. But I did not get the new values insterted in the table "Custermers" of Northwind database. Please help and tell me what I did wrong and how to correct this problem.
Thanks in advance,
Scott Chang
Oracle Stored Procedures VERSUS SQL Server Stored Procedures
Jul 23, 2005I 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 RelatedStored Procedures 2005 Vs Stored Procedures 2000
Sep 30, 2006Hi,
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
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
User 'Unknown User' Could Not Execute Stored Procedure - Debugging Stored Procedure Using Visual Studio .net
Sep 13, 2007Hi all,
I am trying to debug stored procedure using visual studio. I right click on connection and checked 'Allow SQL/CLR debugging' .. the store procedure is not local and is on sql server.
Whenever I tried to right click stored procedure and select step into store procedure> i get following error
"User 'Unknown user' could not execute stored procedure 'master.dbo.sp_enable_sql_debug' on SQL server XXXXX. Click Help for more information"
I am not sure what needs to be done on sql server side
We tried to search for sp_enable_sql_debug but I could not find this stored procedure under master.
Some web page I came accross says that "I must have an administratorial rights to debug" but I am not sure what does that mean?
Please advise..
Thank You
How To Search And List All Stored Procs In My Database. I Can Do This For Tables, But Need To Figure Out How To Do It For Stored Procedures
Apr 29, 2008How do I search for and print all stored procedure names in a particular database? I can use the following query to search and print out all table names in a database. I just need to figure out how to modify the code below to search for stored procedure names. Can anyone help me out?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
Using A Stored Procedure To Query Other Stored Procedures And Then Return The Results
Jun 13, 2007Seems like I'm stealing all the threads here, : But I need to learn :) I have a StoredProcedure that needs to return values that other StoredProcedures return.Rather than have my DataAccess layer access the DB multiple times, I would like to call One stored Procedure, and have that stored procedure call the others to get the information I need. I think this way would be more efficient than accessing the DB multiple times. One of my SP is:SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, S.Name AS 'StatusName', S.ItemDetailStatusID, S.InProgress as 'StatusInProgress', S.Color AS 'StatusColor',T.[Name] AS 'TypeName', T.Prefix, T.Name AS 'ItemDetailTypeName', T.ItemDetailTypeID FROM [Item].ItemDetails I INNER JOIN Item.ItemDetailStatus S ON I.ItemDetailStatusID = S.ItemDetailStatusID INNER JOIN [Item].ItemDetailTypes T ON I.ItemDetailTypeID = T.ItemDetailTypeID However, I already have StoredProcedures that return the exact same data from the ItemDetailStatus table and ItemDetailTypes table.Would it be better to do it above, and have more code to change when a new column/field is added, or more checks, or do something like:(This is not propper SQL) SELECT I.ItemDetailID, I.ItemDetailStatusID, I.ItemDetailTypeID, I.Archived, I.Expired, I.ExpireDate, I.Deleted, EXEC [Item].ItemDetailStatusInfo I.ItemDetailStatusID, EXEC [Item].ItemDetailTypeInfo I.ItemDetailTypeID FROM [Item].ItemDetails IOr something like that... Any thoughts?
View 3 Replies View RelatedHow To Save Stored Procedure To NON System Stored Procedures - Or My Database
May 13, 2008Greetings:
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!
Stored Procedure Being Saved In System Stored Procedures
Apr 7, 2006We 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 Can I Call One Or More Stored Procedures Into Perticular One Stored Proc ?
Apr 23, 2008Hello friends......How are you ? I want to ask you all that how can I do the following ?
I want to now that how many ways are there to do this ?
How can I call one or more stored procedures into perticular one Stored Proc ? in MS SQL Server 2000/05.
SSIS And Stored Procedures Results Stored In #Tables
Mar 26, 2008Hello
I'm start to work with SSIS.
We have a lot (many hundreds) of old (SQL Server2000) procedures on SQL 2005.
Most of the Stored Procedures ends with the following commands:
SET @SQLSTRING = 'SELECT * INTO ' + @OutputTableName + ' FROM #RESULTTABLE'
EXEC @RETVAL = sp_executeSQL @SQLSTRING
How can I use SSIS to move the complete #RESULTTABLE to Excel or to a Flat File? (e.g. as a *.csv -File)
I found a way but I think i'ts only a workaround:
1. Write the #Resulttable to DB (changed Prozedure)
2. create data flow task (ole DB Source - Data Conversion - Excel Destination)
Does anyone know a better way to transfer the #RESULTTABLE to Excel or Flat file?
Thanks for an early Answer
Chaepp
MS SQL Stored Procedures Inside Another Stored Procedure
Jun 16, 2007Hi,
Do you know how to write stored procedures inside another stored procedure in MS SQL.
Create procedure spMyProc inputData varchar(50)
AS
----- some logical
procedure spMyProc inputInsideData varchar(10)
AS
--- some logical
--- go
-------
Calling Stored Procedures From Another Stored Procedure
May 8, 2008I am writing a set of store procedures (around 30), most of them require the same basic logic to get an ID, I was thinking to add this logic into an stored procedure.
The question is: Would calling an stored procedure from within an stored procedure affect performance? I mean, would it need to create a separate db connection? am I better off copying and pasting the logic into all the store procedures (in terms of performance)?
Thanks in advance
John
Calling A Stored Procedure Inside Another Stored Procedure (or Nested Stored Procedures)
Nov 1, 2007Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly. For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie
exec dbo.DeriveStatusID 'Created'
returns an int value as 1
(performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')
but I also have a second stored procedure that needs to make reference to this procedure first, in order to resolve an id - ie:
exec dbo.AddProduct_Insert 'widget1'
which currently performs:SET @statusID = (SELECT statusID FROM statusList WHERE statusName='Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
I want to simply the insert to perform (in one sproc):
SET @statusID = EXEC deriveStatusID ('Created')INSERT INTO Products (productname, statusID) VALUES (''widget1', @statusID)
This works fine if I call this stored procedure in code first, then pass it to the second stored procedure, but NOT if it is reference in the second stored procedure directly (I end up with an empty value for @statusID in this example).
My actual "Insert" stored procedures are far more complicated, but I am working towards lightening the business logic in my application ( it shouldn't have to pre-vet the data prior to executing a valid insert).
Hopefully this makes some sense - it doesn't seem right to me that this is impossible, and am fairly sure I'm just missing some simple syntax - can anyone assist?