Calling Stored Proc In ActiveX Script
Dec 13, 2005Hi!
How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.
Thanks.
ADVERTISEMENT
Calling Stored Procedure From An ActiveX Task
Jun 6, 2006I'm attempting to call a storedprocedure from within an ActiveX task ina DTS and am getting a "Command Text was not set for the commandobject" error. I have no problem if I replace the stored procedure callwith the actual SQL.Is it possible to directly call a SP via an ActiveX task? If yes, couldsomebody help me with the syntax please?This is what I have currently haveSet Conn = CreateObject("ADODB.Connection")SQL = "exec (MySP)"set RLoop=conn.Execute(SQL)do while (NOT RLoop.EOF)msgbox RLoop(1)RLoop.MoveNextLoopTIAKarthik
View 1 Replies View RelatedStored Proc - Calling A Remote Stored Proc
Aug 24, 2006I am having trouble executing a stored procedure on a remote server. On my
local server, I have a linked server setup as follows:
Server1.abcd.myserver.comSQLServer2005,1563
This works fine on my local server:
Select * From [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.dbo.TableName
This does not work (Attempting to execute a remote stored proc named 'Data_Add':
Exec [Server1.abcd.myserver.comSQLServer2005,1563].DatabaseName.Data_Add 1,'Hello Moto'
When I attempt to run the above, I get the following error:
Could not locate entry in sysdatabases for database 'Server1.abcd.myserver.comSQLServer2005,1563'.
No entry found with that name. Make sure that the name is entered correctly.
Could anyone shed some light on what I need to do to get this to work?
Thanks - Amos.
Calling CLR Stored Proc
Apr 15, 2008For a new web project I am working on I wrote a couple of Stored Procs in C#. I have deployed them to the database, and can run them fine using SQL Mgmt Studio. The problem is that when I try and run them from my application, I get a 'The Stored Procedure 'ProcName' does not exist' error message. I orignally thought it might be a permissions issue, so I changed my connection string to connect as 'sa' and it still got the same error. I am using the Microsoft.Practices.EnterpriseLibrary to connect to the database. Any ideas on what I need to do to get this to work?
View 1 Replies View RelatedCalling DLL From A Stored Proc
Jan 8, 2003I need to call a DLL from with one of my stored procedures. How do I achieve this?
The DLL is currently on App server (Not on the SQL Server box). Do I have to register this DLL on my SQL server, create an extended stored proc and call it from my stored proc?
Please throw some light on this topic.
Thanks,
Varma
What Is Calling A Stored Proc?
Apr 24, 2007How can I find what is calling a stored proc in a database or on the server?
Currently a job runs daily that calls a couple of DTS packages. Somewhere in this process a stored proc is being called that pumps out several emails. BUT nowhere in the DTS or job can I find any reference to the stored proc.
Calling At Stored Proc (DHL)
Sep 17, 2007I would like to know how to call a Stored Proc with in code. Have an SQL code but need data from a table that needs to be updated by a Stored Proc. Would need to call proc in the middle of code to get the correct data. Just not sure of what the statement looks like to call the proc. Thank you for your help. David (DHL)
View 9 Replies View RelatedCalling Stored Proc. Multiple Times...
Feb 1, 2008I have basic SQL query that returns a one column result set. For each row returned in this result set, I need to pass the value in the column to a stored procedure and get back a result set.
I have 2 solutions, neither of which are very elegant. I'm hoping someone can point me in a better direction.
Solution 1:
Use a cursor. The cons here, are the SP returns a result set on each pass which generates multiple result sets overall. If there is a way to combine these result sets, I think this solution might work.
Solution 2:
Use a temp table or table variable.
The cons here are, if the schema of the result set returned from the stored procedure changes, the table variable will have to change to accommodate it. This is a dependency I'd rather not create.
Any help is very much appreciated.
Calling A Stored Procedure From Within A Stored Proc
Dec 18, 2007Hi Peeps
I have a SP that returns xml
I have writen another stored proc in which I want to do something like this:Select FieldOne, FieldTwo, ( exec sp_that_returns_xml ( @a, @b) ), FieldThree from TableName
But it seems that I cant call the proc from within a select.
I have also tried
declare @v xml
set @v = exec sp_that_returns_xml ( @a, @b)
But this again doesn't work
I have tried changing the statements syntax i.e. brackets and no brackets etc...,
The only way Ive got it to work is to create a temp table, insert the result from the xml proc into it and then set @v as a select from the temp table -
Which to be frank is god awful way to do it.
Any and all help appreciated.
Kal
How To Specifiy An Output Parameter When Calling Stored Proc In C#
Oct 2, 2007How do I specify a parameter as an output parameter --> OUTPUT paramI am referring to how to do this on line 10 below
1 int GetTheReturnValue=0;2//Code not shown//
9 mySqlCommand.Parameters.Add("@returnParameter", SqlDbType.Int, 10).Value = 0; // How to specify output param?10 GetTheReturnValue=mySqlCommand.ExecuteNonQuery();
Calling Stored Proc With Default Params From .NET Not Working.
Mar 20, 2006All,
I have the following :
ALTER PROCEDURE [dbo].[sp_FindNameJon]
@NameName varchar(50)='',
@NameAddress varchar(50)='',
@NameCity varchar(50)='',
@NameState varchar(2)='',
@NameZip varchar(15)='',
@NamePhone varchar(25)='',
@NameTypeId int=0,
@BureauId int,
@Page int=1,
@Count int=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @SqlString nvarchar(3000),@SelectClause nvarchar(1000), @FromClause nvarchar(1000),@WhereClause nvarchar(1000)
DECLARE @ParentSqlString nvarchar(4000)
DECLARE @Start int, @End int
INSERT into aaJonTemp values (@Page, 'here2', @NameCity);
And inside of aaJonTemp, I have the following :
NULL
here2
NULL
NULL
here2
NULL
How is this possible? If @Page or @NameCity is NULL, how come it doesn't default to a value in the stored proc?
Thx
jonpfl
Problem In Calling A Stored Proc With OUTPUT Parms
Sep 26, 2000I have a problem in calling a procedure.
It has two input parameters and seven(7) output parms.
When I run it this way:
exec usp_List_Relationship_Users @relationship_id = 1851 ,
@status = 'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT
I get this error:
Server: Msg 119, Level 15, State 1, Line 10
Must pass parameter number 3 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
But when I run it this way :
exec usp_List_Relationship_Users 1851 ,
'Channel Member',
@cust_idOUTPUT,
@user_login_id OUTPUT,
@username OUTPUT,
@password OUTPUT,
@status_id OUTPUT,
@sdesc OUTPUT,
@administrator OUTPUT
I get the expected results. Why should I omit the name of the name of the input parameters? I don't know why I am getting this error if don't run by ommitting the name of the input params.
Any help regarding this matter is greatlt appreciated.
You can assume that all the variables are declared prior to executing the stored procedure.
Regards
Sushruth Nanduri.
Calling A Stored Proc From Within Another Stored Proc
Feb 20, 2003I have seen this done by viewing code done by a SQL expert and would like to learn this myself. Does anyone have any examples that might help.
I guess I should state my question to the forum !
Is there a way to call a stored proc from within another stored proc?
Thanks In Advance.
Tony
Calling Stored Proc B From Stored Proc A
Jan 20, 2004Hi all
I have about 5 stored procedures that, among other things, execute exactly the same SELECT statement
Instead of copying the SELECT statement 5 times, I'd like each stored proc to call a single stored proc that executes the SELECT statement and returns the resultset to the calling stored proc
The SELECT statement in question retrieves a single row from a table containing 10 columns.
Is there a way for a stored proc to call another stored proc and gain access to the resultset of the called stored proc?
I know about stored proc return values and about output parameters, but I think I am looking for something different.
Thanks
Calling T SQL Stored Proc From CLR Stored Proc
Aug 30, 2007I would like to know if the following is possible/permissible:
myCLRstoredproc (or some C# stored proc)
{
//call some T SQL stored procedure spSQL and get the result set here to work with
INSERT INTO #tmpCLR EXECUTE spSQL
}
spSQL
(
INSERT INTO #tmpABC EXECUTE spSQL2
)
spSQL2
(
// some other t-sql stored proc
)
Can we do that? I know that doing this in SQL server would throw (nested EXECUTE not allowed). I dont want to go re-writing the spSQL in C# again, I just want to get whatever spSQL returns and then work with the result set to do row-level computations, thereby avoiding to use cursors in spSQL.
Strange Results When Calling A Sql Stored Proc From Within An Access2000 Project
Nov 18, 2006Hi,I have written a stored procedure which includes a DATEPART command, i.e.DATEPART(weekday, <date>)The result when ran from SQL Query Analyser is as expected . i.e. Sundayreturns 1, Monday 2, etcWhen the same proc is called from within the Access 2000 project Sundayis returned as 7, Saturday as 6 instead of 1 and 7 respectively.Basically the same stored proc returns different data depending on fromwhere it has been called.This is causing some issues obviously as the resulting tables andreports are showing incorrect data when presented in Access 2000.Has anyone else experienced this before or have any idea what may cause it.Regards,PB
View 1 Replies View RelatedBest Practice For A Long Running Queries / Asynchronously Calling A Stored Proc?
May 23, 2008All -
I am using SQL Server 2005 and I have an endpoint that exposes some stored procedures as web-methods in the endpoint.
One particular stored procedure I have exposed takes a long time to execute: about 10 - 15 minutes. While, it is OK, that this stored procedure takes this long, it is not desirable for the HTTP Request that executed this proc to not wait for that long.
What I want to be able to do is to call the stored procedure and have the call return immidetaly but the stored proc continues what its doing. I will call another stored proc at a later time to retrive the result of the first stored proc. The first proc will store its results in a temp table. I am thinking of using SQL Server Service Broker to achieve this.
Is there a better a way to achieve this? And how does SQL Server process the Service Broker requests, i.e., I dont want the query to be executed when the server is busy. Are there any hints that I need to give to Service Broker to be able to do this?
Thanks.
ActiveX Script In A SSIS Package - Calling An FSO To Create/manipulate Files
Jul 3, 2007I have a SQL2000 DTS package that executes vbscript to loop through a recordset which:
- runs a stored procedure and populated tables
- builds a recordset from the populated tables to write records to an Excel file
- writes status to text files with either the error or success notices
I use FSO to set up the success and error files, but the scheduled job in SQL2005 which calls the SSIS package returns the following error:
"Retrieving the file name for a component failed with error code 0x0015F74C"
I can successullly run this (vbscript) in both the SSIS package via the BI Development Studio and in MS Access (exactly the same code in both) - but not as a SSIS package called in a scheduled job in SQL2005.
I am at an impasse with this ... any and ALL assistance would be GREATLY appreciated.
TIA,
Bob
Can You Trace Into A Stored Proc? Also Does RAISERROR Terminate The Stored Proc Execution.
Feb 13, 2008I am working with a large application and am trying to track down a bug. I believe an error that occurs in the stored procedure isbubbling back up to the application and is causing the application not to run. Don't ask why, but we do not have some of the sourcecode that was used to build the application, so I am not able to trace into the code.
So basically I want to examine the stored procedure. If I run the stored procedure through Query Analyzer, I get the following error message:
Msg 2758, Level 16, State 1, Procedure GetPortalSettings, Line 74RAISERROR could not locate entry for error 60002 in sysmessages.
(1 row(s) affected)
(1 row(s) affected)
I don't know if the error message is sufficient enough to cause the application from not running? Does anyone know? If the RAISERROR occursmdiway through the stored procedure, does the stored procedure terminate execution?
Also, Is there a way to trace into a stored procedure through Query Analyzer?
-------------------------------------------As a side note, below is a small portion of my stored proc where the error is being raised:
SELECT @PortalPermissionValue = isnull(max(PermissionValue),0)FROM Permission, PermissionType, #GroupsWHERE Permission.ResourceId = @PortalIdAND Permission.PartyId = #Groups.PartyIdAND Permission.PermissionTypeId = PermissionType.PermissionTypeId
IF @PortalPermissionValue = 0BEGIN RAISERROR (60002, 16, 1) return -3END
Calling A Proc From A Proc
Jan 10, 2007I'm having problems calling my second proc (MyProcedure2) from within anexisting proc. MyProcedure2 does not seeem to fire this way. My code isbelow. Help appreciated.Thanks,TrevorALTER procedure dbo.MyProcedure1(@newsletterid int)ASSET NOCOUNT ON-- Return Subscribersdeclare @howmany intset @howmany=isnull((select count(subscriberid) from mySubscribers wherenewsletterid=@newsletterid),0)update Mynewsletters set status=3,howmany=@howmany wherenewsletterid=@newsletteridselect @howmanyexec MyProcedure2*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedSQL 2012 :: Calling Proc For Each Row In A Table Without Using Cursor
Apr 14, 2015I have a table that has the following data
ID
---
101
102
105
108
124
189
I need to call a stored proc for each of the IDs above. Our existing code which has a cursor to loop through the table and call the proc for each value is proving to be a performance nightmare. Is there an alternate method that I can use to avoid cursor and make it more efficient?
Permission Problem Calling The Sp_start_job Proc
Feb 12, 2008Code Snippet
ALTER procedure [dbo].[sp_MyProc]
AS
EXEC msdb.dbo.sp_start_job @job_name = 'MyJob'
I'm trying to write a procedure that calls a job. If I execute it (calling it from adp) as a user that is not db_owner (I guess), I get the error: The Execute permission was denied on the object sp_start_job, database 'msdb', schema 'dbo'.
How can I resolve this problem?
Data Driven Sub Calling Proc With Multiple Parameters
Aug 31, 2007I have a couple of questions that I hope someone might be able to provide some direction on.
1. Why can't you call a proc when the proc uses temp tables? I wrote a simple proc (select * from table) and the data subscription works perfect. I then take that same query and put it into a temp table and the data set can't be validated by the subscription, Why?
2. Why can't you pass a simple execute statement in a data subscription? (execute dbo.RptTakeTheLeadFLSDinnerCompetition @FiscalQuarter ='2007-Q3', @StoreRegionID = '9231', @StoreType = 'Full-Line Store') works perfect in SQL but once you try and validate in the data subscription is fails.
3. Another question, why if I dumb down the proc and the number of parameters does it work? Are there limitations on the number of parameters you can pass into a proc from a data driven subscription?
Basically, I want to declare a few variables dynamically and pass them into a execute statement that calls the proc. Which in turn runs the report.
Any ideas on where I seem to be going wrong?
Stored Proc Question : Why If Exisits...Drop...Create Proc?
Jun 15, 2006Hi All,Quick question, I have always heard it best practice to check for exist, ifso, drop, then create the proc. I just wanted to know why that's a bestpractice. I am trying to put that theory in place at my work, but they areasking for a good reason to do this before actually implementing. All Icould think of was that so when you're creating a proc you won't get anerror if the procedure already exists, but doesn't it also have to do withCompilation and perhaps Execution. Does anyone have a good argument fordoing stored procs this way? All feedback is appreciated.TIA,~CK
View 3 Replies View RelatedASP Cannot Run Stored Proc Until The Web User Has Run The Proc In Query Analyzer
Feb 23, 2007I have an ASP that has been working fine for several months, but itsuddenly broke. I wonder if windows update has installed some securitypatch that is causing it.The problem is that I am calling a stored procedure via an ASP(classic, not .NET) , but nothing happens. The procedure doesn't work,and I don't get any error messages.I've tried dropping and re-creating the user and permissions, to noavail. If it was a permissions problem, there would be an errormessage. I trace the calls in Profiler, and it has no complaints. Thedatabase is getting the stored proc call.I finally got it to work again, but this is not a viable solution forour production environment:1. response.write the SQL call to the stored procedure from the ASPand copy the text to the clipboard.2. log in to QueryAnalyzer using the same user as used by the ASP.3. paste and run the SQL call to the stored proc in query analyzer.After I have done this, it not only works in Query Analyzer, but thenthe ASP works too. It continues to work, even after I reboot themachine. This is truly bizzare and has us stumped. My hunch is thatwindows update installed something that has created this issue, but Ihave not been able to track it down.
View 1 Replies View RelatedStored Procedure Problem In ActiveX Script
Jan 10, 2006Hi..
First off, apologies if you've read this on any other forums.. I just can't seem to get an answer to the error I encounter..
I have a Stored Procedure being called from an ActiveX script in a DTS Job that runs daily. First the DTS JOb checks the Date value of a remote file, if it is more recent than a file stored locally, it dowloads the file [a semi-colon delimited text file]. Then, from within a While/Wend loop, I am iterating thru each row in the semi-colon delimited text file, formatting the array values of each line, assigning them to Params, executing the SP and setting the command object to Nothing.
The problem is, that after 3151 rows inserted correctly into the Table, the ActiveX script fails and with the following error reported:
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
Is this a "maximum number of connections" issue? Can it be resolved? I can see that the oCmd [and hence the connection] is trashed and then initialised for each of the 5007 records, but this doesn't seem like a inordinate amount of work really?
I have included an abridged version of my code below.. It is all dim'd elsewhere in the ActiveX so implicit coding notwithstanding..
-- CODE START --
While Not oTxtStream.AtEndOfStream
strLine = Trim(oTxtStream.ReadLine)
i = 0
myArr = Split(strLine, ";")
strParam = fncCharCheck(myArr(i))
Set oCMD = CreateObject("ADODB.Command")
Set oCMD.ActiveConnection = oConn
With oCMD
.CommandType = adCmdStoredProc
.CommandText = "sdnInsert"
.Parameters.Append oCMD.CreateParameter("@Param",adVarChar,adParamInput,50,strParam)
.Execute
End With
Set oCMD = Nothing
Wend
-- END CODE --
What I'm after really is a reason for the connection to error on me.. The 5007 line [and up to 650 chars per line] file is too big to be dealt with by the SP directly, right? So I have to use VB to Loop through each line? Is 5007 successive connections alot? Surely this kinda thing is done elsewhere, I just wonder how..
Any help would be greatly appreciated..
KingRoon
Executing A Stored Procedure In ActiveX Script
Dec 13, 2005Hi!
How do you call a store procedure in an ActiveX script? I have a stored procedure that I want to execute everytime my Data Driven Query tasks inserts a new record.
Thanks.
$3.99/yr .COM!
http://www.greatdomains4less.com
Stored Proc Calls Another Stored Proc
Jan 13, 2006Hi all,
I have a stored procedure "uspX" that calls another stored procedure "uspY" and I need to retrieve the return value from uspY and use it within uspX. Does anyone know the syntax for this?
Thanks for your help!
Cat
Stored Procedure Calling Another Stored Procedure_
Jul 20, 2005Hi all,I have a stored procedure that return a resultsete.g. stored proc: get_employee_detailsselect emp_id, emp_name, emp_salary, emp_positionfrom empoloyeeI would like to write another stored procedure that executes the abovestored procedure - returning the same number of records but it willonly show 2 columnse.g. new stored proc: get_employee_pay -- executesget_employee_detailsI only need to know emp_id, emp_salary.How can this be done in sql stored procedure?Thanks,June Moore.
View 2 Replies View RelatedCalling 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
Execute Stored Procedure Y Asynchronously From Stored Proc X Using SQL Server 2000
Oct 14, 2007I am calling a stored procedure (say X) and from that stored procedure (i mean X) i want to call another stored procedure (say Y)asynchoronoulsy. Once stored procedure X is completed then i want to return execution to main program. In background, Stored procedure Y will contiue his work. Please let me know how to do that using SQL Server 2000 and ASP.NET 2.
View 3 Replies View RelatedCalling 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?
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.