Executing Extended Stored Procs???
Dec 3, 1999
I've written a very simple extended stored proc--little more than what VC++
6.0's ext stored proc wizard generates--set up the xp in the master database,
and eventually want to execute it from a trigger defined on a table in another database...
I've tested the xp in Query Analyzer. If the DB is set to 'master', the xp
runs (exec xp_InputTrigger), no problem. If I change databases and try to run the xp, I get:
Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_InputTrigger'.
Keying "exec master.xp_InputTrigger", also fails: Could not find stored procedure 'master.xp_InputTrigger'.
I've checked permissions--all seem to be set right. Anyone have any insight
for me here? Many thanks,
mjo
View 1 Replies
ADVERTISEMENT
Aug 2, 2000
Is it possible to write extended stored procs in VB? I do not know C or C++ or Perl or whatever ....
How about calling a COM from a stroed procedure???
Thanks,
Judith
View 4 Replies
View Related
Aug 16, 2001
What happens if any of the extended stored procs below can't connect to the Exchange server? Is the failure logged? Does it back off and try again later?
xp_startmail
xp_sendmail
xp_findnextmsg
xp_readmail
xp_deletemail
sp_processmail
xp_stopmail
View 1 Replies
View Related
Jan 30, 2007
Hi experts,
I m working with MS SQL Server 2000 with ASP for my application. There're some Stored Procedures created for the new functions but seems I can't run these new SPs with my ASP pages. When I load that ASP page, it shows error message that can't find my SP. I've execute the SP alone in SQL Enterprise Manager and it works.
When I work with the SPs, I can connect with the DB with Enterprise Manager only without administrator right. As my SPs I suppose they should work with ASP but I just worry would it be the problem with my right granted in SQL server?
There are some other SPs running fine created by dbo but for my SPs not by dbo. Would there be any differences? Does it mean my SPs need to be granted by dbo instead of my current role? I m sure if I use the same ASP page then running another existing SPs, it works really smooth.
I also tried to make a SQL statement in my ASP page (e.g. an insert statement) but it seems nothing can be inserted. I got really screwed up!!!
Thanks in advance!!
Manfred
View 3 Replies
View Related
May 28, 2007
Hi. Does anyone know how to display the results if i execute "xp_fixeddrives, xp_availablemedia and xp_subdirs" commands with VC++ 6.0? I can't obtained the results using Recordset class. Can someone help me? Thank you.
|_N_T_|
View 3 Replies
View Related
Aug 10, 2007
Hello! I am new poster and I hope I am posting this question in the right forum.
I have an application (winform written in c#) that has tons of interaction (lots of triggers and stored procs) with sql server (SQL Express, SQL 2000 & SQL Server 2005).
I want to monitor when a stored procedure starts executing and then display something until the execution ends. I was thinking of displaying a modal form or if not a non-modal form and just play around with the Z-order. I thought of utilizing the Query Notification service but I believe it is only available in SQL Server 2005. My guess is that a CLR will be the best way to go.
Also, is it possible to put this in a dll and then just add the dll to the application?
I would appreciate it if I could get some input/advice on this matter (or some sample codes if someone has done something similar).
Thanks a lot!
View 2 Replies
View Related
Oct 13, 2006
What is the procedure to create a extended stored procedure using C#.
Actually we have created a dll file using c# and it was added to the bin folder of mssql server.
Also dll file is added to the sql server using "xp_extendedproc"
But while executing the extended proc we got this error
"ODBC: Msg 0, Level 16, State 1
Cannot load the DLL xp_TestSql.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)"
View 8 Replies
View Related
May 15, 2008
i have created the folowing function but keep geting an error.
Only functions and extended stored procedures can be executed from within a function.
Why am i getting this error!
Create Function myDateAdd
(@buildd nvarchar(4), @avdate as nvarchar(25))
Returns nvarchar(25)
as
Begin
declare @ret nvarchar(25)
declare @sqlval as nvarchar(3000)
set @sqlval = 'select ''@ret'' = max(realday) from (
select top '+ @buildd +' realday from v_caltable where realday >= '''+ @avdate +''' and prod = 1 )a'
execute sp_executesql @sqlval
return @ret
end
View 3 Replies
View Related
Jul 23, 2005
HiIf I register my own proc via execsp_addextendedproc 'xp_myproc', 'xp_mylib.dll'.How then I can look all my own procs and dll's?In which tables are this data saved?Thx.
View 1 Replies
View Related
Jan 15, 2008
Is there a way to namespace groups of stored procs to reduce confusion when using them?
For C# you can have ProjectName.ProjectSection.Classname when naming a class. I am just wondering if there is a way to do the same with SQL stored procs. I know Oracle has packages and the name of the package provides a namespace for all of the stored procs inside it.
View 1 Replies
View Related
Jan 12, 2004
Hi,
I would like to execute multiple stored procs by calling a single script say A. I want to A to execute on a nightly basis. The multiple stored procs are bulk inserts into separate tables. I want to be notified of the error if one among the many stored procs fail when I call A. But if one fails, I don't want A to fail. The remaining stored procs should execute. Does anyone have a script wriiten for A? I guess A is just not about -
EXEC SP1
EXEC SP2
EXEC SP3
EXEC SP4
EXEC SP5
Thanks
View 4 Replies
View Related
May 7, 2004
HI,
CREATE PROCEDURE PROC1
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (6,7,2,3,12,10)
ORDER BY B.INTCUSTOMERID
END
IF I AM USING THIS PROC AS I/P TO ANOTHER PROC THEN IT GIVES NO PROBLEM AS I CAN USE ?
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC PROC1
ST......1,
ST......2,
END.
BUT IF , I USE ANOTHER PROC SIMILAR TO THE FIRST ONE WHICH GIVES SLIGHTLY DIFFERENT RESULTS AND GIVES TWO SETS OF RESULTS,THEN WE HAVE A PROBLEM,HO TO SOLVE THIS :-
CREATE PROCEDURE MY_PROC
AS
BEGIN
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CUSTOMER A
INNER JOIN CUSTOMERPREFERENCE B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
WHERE B.INTPREFERENCEID IN (23,12,10)
ORDER BY B.INTCUSTOMERID
END
SELECT A.INTCUSTOMERID,MAX(case when A.intpreferenceid = 23 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 12 then '1'
else '0' end) +
MAX(case when A.intpreferenceid = 10 then '1'
else '0' end) AS PREFER
FROM CUSTOMER
GROUP BY A.INTCUSTOMERID
ORDER BY A.INTCUSTOMERID
END
WHICH NOW GIVES ME TWO SETS OF DATA , BOTH REQUIRED THEN HOW TO USE ONE SET OF RESULTS AS I/P TO ANOTHER PROC AND OTHER SET OF RESULTS AS I/P TO YET ANOTHER PROC .
CREATE PROCEDURE PROC2
AS
BEGIN
CREATE TABLE #SAATHI(INTCUSTOMERID INT,CHREMAIL NVARCHAR(60),INTPREFERENCEID INT,CHRPREFERENCEDESC NVARCHAR(50))
INSERT INTO #SAATHI
EXEC MY_PROC
ST......1,
ST......2,
END.
BUT, HERE I WANT TO USE FIRST DATASET ONLY , HOW TO USE IT ?
THANKS.
View 4 Replies
View Related
Jan 24, 2008
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
View 1 Replies
View Related
Jan 23, 2008
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies
View Related
Apr 29, 2008
How 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'
View 1 Replies
View Related
Feb 5, 2005
almost choked when i read the following recent post on The Daily WTF (http://thedailywtf.com/) the other day --
Logical Tiers? That Makes No Sense! (http://thedailywtf.com/ShowPost.aspx?PostID=28959)
i don't think i can do justice to how utterly stupid that stored procedure is
read the comments and have a laugh
one of the points made was that stored procedures are bad
this twigged something in my memory, so i dug around in my bookmarks, and sure enough, here's another decent discussion about stored procs --
Stored procedures are bad, m'kay? (http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx)
enjoy!
View 6 Replies
View Related
Jun 28, 2007
Hey guys.
I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.
Thanks
tibor
View 14 Replies
View Related
Apr 8, 2004
First off, this is a cross post which is also located here:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34073
Now, with the following stored procs and UDF, the Result is NULL and I cannot figure out why.
What I am doing is calling a stored procedure pass in an OUTPUT variable of type float. This stored proceudre then calls a different stored procedure passing the same OUTPUT variable of type float. The second stored procedure then calls a UDF passing in two variables to be multiplied ans this should set the OUTPUT variable to the result.
The UDF does return the correct result to the interior stored procedure, but the interior stored procedure does not pass the value back to the original stored procudure resultint in a Result value = NULL.
The code is below, just copy / past, execute and you will see exactly what I mean.
Any thoughts?
USE NORTHWIND
GO
CREATE FUNCTION RECTANGULAR_XSECTION
(@fWidth float, @fHeight float)
RETURNS float
AS
BEGIN
RETURN (@fWidth * @fHeight)
END
GO
CREATE PROCEDURE usp_shapes_GetRectangularXSection
@fResult float OUTPUT
AS
declare @fWidth float, @fHeight float
SELECT @fWidth = 108, @fHeight = 10
SELECT @fResult = [dbo].[RECTANGULAR_XSECTION](@fWidth, @fHeight)
SELECT @fResult AS CalledProcedureOkHere
GO
CREATE PROCEDURE usp_shapes_GetXSection
@fResult float OUTPUT
AS
EXECUTE usp_shapes_GetRectangularXSection @fResult
SELECT @fResult AS CallingProcedure_NULL?
GO
declare @fResult float
EXECUTE usp_shapes_GetXSection @fResult
SELECT @fResult as OutsideCallingProcedure_NULL?
GO
DROP FUNCTION RECTANGULAR_XSECTION
GO
DROP PROCEDURE usp_shapes_GetRectangularXSection
GO
DROP PROCEDURE usp_shapes_GetXSection
GO
Mike B
View 3 Replies
View Related
Nov 13, 2007
I am trying to improve my SQL and reduce the number of connections my website is making to the server.
Currently I have a stored procedure that gets all the games (and their details) for a single user which works fine which uses a WHERE userid = @userid which is an Int value.
I now need to create a new procedure which brings back the to all their "friends" (again by their userid). How is it best to do this? I originally tried to do a WHERE userID IN (@userid) but was unable to work out what variable type to use for @userid.
Is it best to do a single query in this way or is there a way to use the existing SP, loop through the collection of userids and join the result together into a single set to return? If an IN is the best route, what is the correct datatype for the variable?
View 5 Replies
View Related
Aug 8, 2006
can anyone help me out.... i need to compare a date that was retrieved from the database and the system date then it must be coded in the stored procs of the database.. help!!!!
View 10 Replies
View Related
Apr 12, 2005
Could someone who has done it before be kind enough to post a short example of how to make a call from an extended stored procedure to a .NET DLL? Or even direct me to an example, or tell me that this is possible / not possible, it would help.
Thanks,Brian
View 4 Replies
View Related
Oct 12, 1999
I'm trying to execute an exetended stored procedure from a trigger. The scenario is this: when an input occurs on a table, my trigger is fired, the extended stopred procedure is to be run, and VB app is to be notified that there is new input to process.
Problem is, I can't seem to get the XP to run. I have written a minimal DLL
with VC++ 6, has nothing but the required __GetXpVersion function and the
trigger function, as:
__declspec(dllexport) SRVRETCODE xp_InputAlarm(SRV_PROC *pSrvProc)
{ return XP_NOERROR; }
__declspec(dllexport) ULONG __GetXpVersion()
{ return ODS_VERSION; }
Pretty simple. I have defined the XP in the master database, set the properties point to the DLL. Believe everything is set up properly. However,
when an input occurs, a run-time error is generated on the XP. Attempting to execute it in Query Analyzer gives this error:
"Cannot find the function xp_InputAlarm in the library D:CustomMedInstaCert SourceInputAlarmInputAlaInputAlarm.dll. Reason: 127(The specified procedure could not be found.)."
Which I think means it can't find the proper entry point (xp_InputAlarm) in the DLL. Any help would be greatly appreciated.
mjo
View 2 Replies
View Related
Jan 6, 2005
Is there any way I can create an SQL script on any extended stored procedures? I need to see what they do. I looked in books online, but it didn't help. Thanks.
View 3 Replies
View Related
Jun 25, 2002
I am looking for examples written in VB. Thanks ALOT!!
View 1 Replies
View Related
Jun 13, 2001
I am working with Visual Basic and SQL Server 2000. I am looking to convert some of the VB into extended stored procedures, yet everything I've been reading has said that xp's can only be written in C or C++. Is this true??
Thanks.
Caroline Kaplonski
ckaplonski@buckconsultants.com
View 1 Replies
View Related
Oct 15, 2001
is it possible to view the source of an extended stored procedure.
I know that it is a dll program
but i want to know how works one of my extented stored proc
View 1 Replies
View Related
Jan 6, 2005
Howdy,
I'm trying to call XP_CMDSHELL from a stored procedure that's not in the Master db. How do I call XP_CMDSHELL? I've tried "Use Master" but I get an error telling me that I can't use "USE" inside of a stored procedure.
Thanks!
View 2 Replies
View Related
Sep 11, 2007
Hello,
i have created one C# DLL
xp_hello.dll
Its having below code in Class1.cs
namespace xp_hello
{
public class Class1
{
public string xp_hello()
{
string strReturn = string.Empty;
strReturn = "Chirag Patel";
return strReturn;
}
}
}
i compiled that DLL and kept it in C drive.
now i am trying to create Extended stored procedure using
sp_addextendedproc 'xp_hello', 'c:xp_hello.dll'
its showing successful creation.
Now i am trying to Execute it with
DECLARE @txt varchar(33)
EXEC xp_hello @txt OUTPUT
now its showing below message.
Msg 17751, Level 16, State 0, Procedure xp_hello, Line 1
Could not find the function xp_hello in the library xp_hello.dll. Reason: 127(The specified procedure could not be found.).
can anyone please help me out in this?
Thanks
View 4 Replies
View Related
Mar 8, 2004
All,
I'm relatively new to stored procs (not to SQL or SQL Server) and I am trying to get transactions to work within a stored proc. Here is the code:
(
--define the parameters that are needed
@userID char(32),
@userName varchar(50),
@status char(10),
@type char(10),
@password varchar(50),
@firstName varchar(100),
@lastName varchar(100),
@email varchar(200),
@domain varchar(50),
@pwdExpiry int,
@badLogin int,
@lastLogin datetime,
@full varchar(8000),
@read varchar(8000),
@noaccess varchar(8000)
)
AS
BEGIN TRAN tmp1
--First, insert the user into the system
INSERT INTO ptsUsers(UserID, UserName, Status, Type, Password, FirstName, LastName, Email, DomainName, PasswordExpiry, BadLoginAttempts, LastLoginDate)
values(@userID, @userName, @status, @type, @password, @firstName, @lastName, @email, @domain, @pwdExpiry, @badLogin, @lastLogin);
--Now, we need to add the function access edges for the user
declare @arrayValue char(32)
declare @rightID char(32)
declare @sepPos int
while patindex('%,%',@full)<>0
BEGIN
select @sepPos = patindex('%,%' , @full)
select @arrayValue = left(@full, @sepPos - 1)
-- replace the value with an empty string
select @full = stuff(@full, 1, @sepPos, '')
--create and parse the new id
declare @strID char(32),@tmpStr varchar(40)
set @tmpStr = newid();
set @strID = REPLACE(@tmpStr,'-','')
--get the access right id for full control
select @rightID = (Select AccessRightID from ptsAccessRights where Name = 'Write')
if(@rightID IS NOT NULL)
--insert the records that are full access
INSERT INTO ptsFunctionAccessEdges(FunctionAccessEdgeID, FunctionID, AccessRightID, UserID)
VALUES(@strID, @arrayValue, @rightID, @userID)
else
RAISERROR(50100,15,1)
END
COMMIT TRAN tmp1
IF @@TRANCOUNT > 0
RAISERROR(50101,15,1)
ROLLBACK TRAN tmp1
The transaction does not rollback when any errors occur at all. Any advice/help?
View 3 Replies
View Related
Apr 12, 2004
Hi all!
I am not an expert in Stored Procs. I would like to build one for a product list that would return a default value without using output parameters, if possible. Ultimately, I wouldn't be opposed to it.
It currently looks like this:
CREATE PROCEDURE ProductsByVendorNo
(
@VendorNum nvarchar(24)
)
AS
SELECT
P.ProductID AS ProductID,
P.ProductShortName AS ProductName,
P.ProductDesc AS ProductDesc,
U.UnitDesc AS Unit,
P.VendorProductNumber AS VendorNumber,
V.VendorName AS VendorName,
P.Price AS Price,
P.ImageThumb AS ImageThumb
FROM
tblProducts AS P
INNER JOIN tblUnitCodes AS U ON P.UnitCode=U.UnitCode
INNER JOIN tblVendors AS V ON P.VendorID=V.VendorID
WHERE
V.VendorsVendorNo = @VendorNum
AND P.Inactive = 0
ORDER BY
ProductName,
VendorNumber
I would like for it to return a default, constant value for the URL in the ImageThumb field, if this one is empty. I could not find good documentation of how to use IF statements for this case, i.e. to alter the return of just one field.
Suggestions?
Any input is highly appreciated.
Thanks in advance,
Mili Skikic
View 8 Replies
View Related
Mar 25, 2002
I want to "deny" create, update,and delete access on the dbo stored procs that are in the database, but do not want take away dbo owner access. is this possible?
can i create a role and deny access on a particular table in msdb? or a system table in the user table. Thus preventing the developers on the box access to update any of the dbo owned sp's and have them create their own user-owned stored procs?
this is sql7, sp3, development box.
thanks,
View 1 Replies
View Related
Apr 27, 2001
I am trying to set up a DTS package that selects data from one table on server A into another table on server B. I want to do a select statement that will do the following:
select store_name
from store (server A)
where date_created >= (select max(date_created) from store (server B)
I use EM 7.0 to manage all of my extracts, however, the data is moving from a Syabase (adaptive Server)onto another Syabase(adaptive server) machine.
Unfortunately, there is no functionality for a linked server connection.
I tried the following, (which doesn't error out), but is not displaying the source columns in the destination tab of the DTS package when setting up the transformations.
declare @maxdate datetime
exec serverB.dbo.sp_max_date_from_store @maxdate
select store_name
from store --(server A)
where date_created >= @store
Any help or suggestions would be greatly appreciated!
trevorb
View 1 Replies
View Related
Jun 19, 2000
I will be taking over a database that has almost no pk's or relations(this is not my choice, but a vendors)
Management is looking at stored procs to improve performance, but I am wondering
if the db is in this state will there really be a gain. I am pushing for normalization
first, but if anybody has any ideas or opinions I would appreciate
View 2 Replies
View Related
