I'am using SqlServer 2005..
I'am able to view created tables via:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;
How do I do the same with created procedures and functions? i.e how do I view all procedures and functions using SELECT query?
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
Using the report builder, I created a simple report based on a report model. This report runs ok in the report builder.
I proceeded to save the report to the report server, but when I want to view this report in Report Manager, I get the following error:
An error has occurred during report processing.
Query execution failed for data set 'dataSet'.
Incorrect syntax near '<'. The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'xmlns' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'xsi' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'xsi' has already been declared. Label names must be unique within a query batch or stored procedure. The label 'xsi' has already been declared. Label names must be unique within a query batch or stored procedure.
The place where I work is moving to MS SQL Server from Lotus Notes. Ihave done a lot of coding in Lotus Notes, and have, I suppose,intermediate skills in basic SQL -- queries, insert, updates, tabledesign, etc. I have a couple of questions, however. First, storedprocedures vs. functions. In my world, a function is a body of codethat returns a value; a procedure is a body of code that does thingsbut does not return a value (other than an error if it fails). At firstblush, MS SQL seems to follow this distinction. But further reading ofthe Books Online and paper books (like Advanced T-SQL for SQL Server2000) demonstrate that stored procedures also return values. So, whyuse one over the other? Apparently, I can debug SQL stored procedures(haven't tried yet) and it looks like I can't debug functions. On theother hand, I can use a function in an SQL statement but I can't use aprocedure (I think). So, if I want to take two columns and do somehand-waving over them to make a third column for each row in a row set,I need to use a function.I guess the above gives you a sense for what little I know. What arepeople's thougts on functions vs. stored procedures. What's a good bookthat might give me better insight into these sorts of issues withoutspending a lot of time on what a left outer join or a derived table is?Thanks.BD
View 7 Replies View RelatedHi allTrying to figure out what you use ms sql functions for. I understand stored procedures and how to create them. the question is what is the real purpose of a ms sql function considering everything i have read so far makes me think that there is no valid use for them. You can do almost everything that a function does but in a stored procedure. If somebody can give me a good examplle of a sql function i would appreciate it very much.thanks
View 7 Replies View RelatedI hope I didnt POST in the wrong group. If I did sorry. Anyhoo, on to my question. I have searched the forums and didnt quite find what I was looking for so here goes..
I have create a function that is supposed to return the "SCOPE_IDENTITY" from a stored procedure that updates the database. I'm kinda lost as to how to get the SCOPE_IDENTITY into the function.
I have the following line in the function:
Dim retcode As Integer = cmd.Parameters.Add("@retcode", SqlDbType.Int).Direction = ParameterDirection.ReturnValue
...
cmd.ExecuteReader(CommandBehavior.CloseConnection)
and the following in the stored procedure
@retcode int=NULL OUTPUT
....
SELECT @retcode =SCOPE_IDENTITY()
RETURN @retcode
If I run the stored procedure by itself I get
@retcode = 135
@RETURN_VALUE = 135
So i know the stored procedure works but how do I get that return value into a asp.net function?
what is the difference between a Stored Procedure and a Function?tia,mcnewsxp
View 1 Replies View Relatedhi,
what is the different between store procedure and function?
when it is right to use sp and when functions?
thanks in advanced
I have the following case.
I have a linked server and I want to execute SQL statements on that linked server which contains millions of records. so I decided to use SELECT * from OPENQUERY(RmtSrv,'SELECT * FROM RmtTbl WHERE Col = ' + @ColValue) but Unforetunatly the query is not succeeded because the OpenQuery doesn't accept parameters.
I used Scalar valued functions and built the query dynamically, but the Scalar valued functions doesn't execute dynamic SQL
ET @LinkedServerName = 'RmtSrv'
SET @RemoteTable= 'RmtTbl'
SET @Query = 'SELECT * FROM OPENQUERY({0}, ''SELECT Col1 FROM {1} WHERE Col2 = ''''{2}'''''') '
SET @Query = REPLACE(@Query, '{0}', @LinkedServerName)
SET @Query = REPLACE(@Query, '{1}', @RemoteTable)
SET @Query = REPLACE(@Query, '{2}', @Col2Value)
EXEC(@Query)
the above code is not executed because the functions doesn't execute dynamic queries.
I deleted the function above and wrote a Stored Procedure with the same code. but I can't query the stored procedure
SELECT GetColValue(Table1.Col1)
FROM Table1.
where the GetColValue is the name of the stored procedure.
Do you have any solution that I can use to perform Remote SQL statements considering the performancewise and the code shall be centralized in certain stored procedures and functions
the Remote server contains millions of records and I will use it from tens of applications and databases.
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.
Hi
im using sqlserver studio managment
i goto a specific database and goto stored procedures and add stored procedures from context menu and created successfully .
i try to find it between procedures but not found i try to create it again gives me error that already found so.
where i can get my created stored procedures from sqlserver studio managment?
thanks in advance.
Hi 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.
Hi all,
In one of my UDF I use the following functions:
.....
and len(@int_date) = 4
and isnumeric(substring(@int_date,5,6)) = 1
when I use the function I get
Only functions and extended stored procedures can be executed from within a function.
Yes, when I comment the two lines the function works fine.
Ehm.... why can't I use these functions in my function ?
Thanks: Peter
Hi All,
Novice question. Would someone explain tell me what a view is used for? Also I am confused about the difference between a function and a stored procedure. They both seem like functions to me.
surjeet writes "Sir My question is ;
Sir i am a software Engineer.I want to know about the Stored Procedures.Please Give me briefly details and examples of Stored Procedures.How to use Conditions and Literals ,How to use Procedures,Triggres,Functions in Crystal Reports and Suitable method of Data Base Design."
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 RelatedAre they unique to a user/session? Like if 2 users simultaneously run the stored procedure?
TIA!
Hello 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
Hi everyone.I'd like to know how stored procedures and table-valued functions compare when it comes to returning a resultant set of data. I know there is a link somewhere but I can't immediately find it.Thanks.
View 2 Replies View Related WE release our software once a week. In 1 months time we will have over 500 stored procedures in our DataBase.
What we do for releases is when a stored procedure is changed, we put the Drop and Create parts of that script in our SQL Update Script.
A problem comes up when Developer A changes My_StoredProc and then developer B changes the same stored procedure. Sometimes it works ok (the developer B will run the update script before changing his stored procedure. HOwever, it can happen where one Update script file has the same SP 5 times (5 drops 5 creates)... especially if over 300 SP's are getting updating in 1 release.
We will always catch these on our tests, however, it's the 2 hours to fix the Test DB after we run these tests...
What is the best way to manage these? We thought about putting our stored procedures into Team Foundation Server, but we don't know if that will work for us.
We have 8 developers in our team.
If anyone could help or give advice on this, it would be awesome.
Thanks.
What 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.
Hello,
Is there a way to copy some selected or all stored procedures and functions from one database to another?
Thanks in advance.
Best regards,
How to find out last created stored procedures in sql.
View 5 Replies View RelatedHello,
This is my foray into Stored procedures, so I'm hoping this is a fairly basic question.
I'm writing a stored procedure, in which I dynamically create an SQL statement.
At the end of this, the SQL statement reads like:
Code SnippetSELECT COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
Now this statement works a charm, and returns a single value. I want to assign this count to a variable, and use it further on in the stored procedure. This is where the problems start - I cant seem to do it.
If I hard code a statement, like
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger
that works fine (although it brings back a count of all the lines).
But if I modify the dynamically created SQL Statement from earlier on to:
Code SnippetSELECT @LineCount = COUNT(*) FROM StockLedger WHERE StockCode = 'STOCK1' AND IsOpen = 1 AND SizeCode = 'L' AND ColourCode = 'RED' AND LocationCode IS NULL AND RemainingQty > 0
it doesnt work - it complains: Must declare the scalar variable "@LineCount".
Just to clarify, when I say "dynamically created an SQL statement, I mean that by a bunch of conditional statements I populate a varchar variable with the statement, and then eventually run it exec(@SQLStatementString)
So, my question would be, how do I do this? How do I make a dynamically generated SQL statement return a value to a variable?
Hi 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 RelatedHi,Right, i have a fairly good understanding of SQL. However, i have a fairly basic understanding of SQL Server.I know Storedprocedures are really good and i'm starting to use them. I understand they are good for making inserting, updating very easy.However when you look at a SQL Server database you get various folder, this leaves me a little confused with what they are all used for? whats the difference between these?Thanks in advance!sorry for the basic question, i'll try to challange you next time
View 1 Replies View RelatedHi, l've created an function [GSM].[KPIAging], and test it in studio by substitule declare value, i.e.
DECLARE @sCellName VARCHAR(8)
DECLARE @dDate DATETIME
SET @sCellName = "CELL1M_1"
SET @dDate = CAST('06/Jun/2006' AS DATETIME)
EXEC GSM.KPIAging @sCellName, 'CSSR', @dDate
It work fine and return the desired result, but when l used this function in SQL,
SELECT DATEKEY, CELLREGIONKEY, CELL_NAME, CELL_ID, CSSR, GSM.KPIAging(Cell_Name, 'CSSR', @dDate)
FROM GSM.GSMCellDaily_vw
WHERE CSSR BETWEEN 0 AND 85
AND FULLDATE = @dDate
AND CM_SERV > 30
AND (TCH_TRAFFIC > 2 AND TCH_SEIZURES_ATTS > 30)
I got the following error, i.e.
Msg 557, Level 16, State 2, Line 19Only functions and extended stored procedures can be executed from within a function.
Does anyone have any idea on this, and what's the workaround for this?
Thanks you!
For those intersted here is our TOC and the book's link. You can preorder at this point. We are sticking to the Nov. timeframe, but we may get it done sooner.
Chapter 1 Introducing SQLCLR
Chapter 2 Building a Procedure
Chapter 3 SQLCLR Strucutre & Common Tasks
Chapter 4 Creating Objects
Chapter 5 Compare & Contrast
Chapter 6 Replacing TSQL Objects
Chapter 7 Using the Base Library
Chapter 8 Using Procedures in Apps
Chapter 9 Error Handling
Chapter 10 Administration
Chapter 11 Case Study
Here is the link:
http://www.wrox.com/WileyCDA/WroxTitle/productCd-0470054034.html
Enjoy,
Derek
Greetings,
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
Hi mister, I have this script sql but I get this error:
Mens. 557, Nivel 16, Estado 2, Línea 1
Only functions and extended stored procedures can be executed from within a function.
DROP FUNCTION ObtenerTablaPorNombre2
GO
CREATE FUNCTION ObtenerTablaPorNombre2 (@ParamNombreTabla VARCHAR(100))
RETURNS @T Table ( Descripcion VARCHAR(20) NOT NULL, CIF VARCHAR(8) NULL )
AS
BEGIN
DECLARE @cmd nvarchar(max)
DECLARE @params nvarchar(max)
DECLARE @NombreTabla VARCHAR(MAX)
DECLARE @Descripcion VARCHAR(MAX)
DECLARE @CIF VARCHAR(MAX)
SELECT @NombreTabla = [CD_NOMBRE_TABLA], @Descripcion = [DS_CAMPO_DESCRIPCION] , @CIF = [DS_CAMPO_CIF]
FROM [TABLA_MAESTRA] WHERE [CD_NOMBRE_TABLA] = @ParamNombreTabla
SET @cmd = 'SELECT ' + @Descripcion + ',' + @CIF + ' FROM ' + @NombreTabla
--EXEC (@cmd)
SET @cmd = 'SELECT @pDescripcion, @pCIF FROM @pNombreTabla'
SET @params = N'@pDescripcion varchar(100), @pCIF varchar(100), @pNombreTabla varchar(100) '
EXEC sp_executesql @cmd, @params, @pDescripcion = @Descripcion, @pCIF = @CIF, @pNombreTabla = @NombreTabla
RETURN
END
GO
SELECT * FROM [dbo].ObtenerTablaPorNombre2 ('tabla2')
-- Only functions and extended stored procedures can be executed from within a function
I am a bit confused by the difference between a stored procedure and a table-valued function. Can somebody please either give me a simple explanation, or point me at something I can read.
I thought I had it worked out, and had coded some action queries as stored procedures, and I wrote a table-valued function that was effectively an encapsulated SELECT so that SELECT * FROM Spouse(@ID) worked fine. Then I wanted to use a function SpousePair, that was similar to Spouse, to power a Gridview. I discovered that I couldn't. It seems that a SQLDataSource requires either a SELECT statement or a stored procedure. So I wrote a stored procedure SpousePair(@ID1, @ID2).
I find that whereas I tested Spouse with
SELECT * FROM SPOUSE(@ID)
I tested SpousePair with
EXEC SpousePair @ID1 @id2
Now I want to combine these: if I could I would write
SELECT * FROM SPOUSE(@ID) WHERE SPOUSEID NOT IN
(SELECT SPOUSEID FROM SpousePair(@ID1, @ID2))
However this is invalid because you can't put a stored procedure in a Select statement, and SELECT .... NOT IN (EXEC SpousePair @ID1 @ID2) is also invalid.
Is there any alternative to creating a table-valued function, SpousePairA, that is identical to SpousePair but coded as a function. I'm reluctant to do this because then I'll have two bits of quite complicated SQL logic to maintain.
I have this function called fn_GetTimedKey() when I run it in my SQL2005 environment it works fine.
When I run it in my SQL2008R2 environment I get the error:
Only functions and some extended stored procedures can be executed from within a function.
The SQL2008R2 server is new. What can I look for?
Here's the code for the function:
BEGIN
DECLARE @v_Key CHAR(12)
EXEC master..xp_usg_gettimedkey_VAL @v_Key OUTPUT
RETURN @v_Key
END
Hi Folks,
I am writing a program that transforms a generic MS SQL database to make it compatible with our application. In order to make the transformation, I have to access all the elements of the generic database programmatically.
I know that the Master database contains all this information. Is there a query that allows me to access the "non-system" tables, sps, views, and functions?
For Tables, I need to know the Name(s) of the tables, the column names, the column type, ALLOW Nulls, Primary Key, Identity Seed settings, and Triggers.
For SPs, I need to know the Name(s) and access the SP source code (assuming it's unencrypted).
For Views, I need to know the Name(s) and access the Views Source
For functions, I need to know the Name(s) and access the function source.
I can see various tables in the Master database from management studio, like sys.objects, sys.tables - which look like they have the info I need. However, when I run a query against master like:
select * from sys.objects .. I get an error:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.objects'.
Thank you in advance.