Recursive Call In Stored Proc
Jul 20, 2005Hi all
I am implementing a stored procedure which needs to recursively call
itself until specific condition is reached, Could anyone give some
advice about that?
Thanks a lot
Robert Song
ADVERTISEMENT
Recursive Stored Proc Help
Jul 23, 2004I'm trying to write a recursive stored proc for the TreeView control that will return XML for the TreeView. Does anyone have any examples of code to do this or any resources that might help me write the stored proc?
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.
Need To Call Sp_replicationdboption From Another Stored Proc
Feb 10, 2006i am trying to call sp_replicationdboption from another stored procedure that i wrote myself but it is giving me the following error
"sp_replicationdboption cannot be executed within a transaction"
note that i'm not using begin or commit transaction in my stored procedure
is there a way to do this
Remote Stored Proc Call
Aug 29, 2007I'm calling this from another sql server....
I created a linked server... and want to restore database backups on the other box....
The restore script runs fine when ran locally but fails with the message below when calling it remotely
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
CREATE PROCEDURE usp_restore_database_backups AS
RESTORE DATABASE BesMgmt
FROM DISK = 'D:MSSQLBACKUPBesMgmtBesMgmt_backup_device.bak '
WITH
--DBO_ONLY,
REPLACE,
--STANDBY = 'D:MSSQLDataBesMgmtundo_BesMgmt.ldf',
MOVE 'BesMgmt_data' TO 'D:MSSQLDataBesMgmt.mdf',
MOVE 'BesMgmt_log' TO 'D:MSSQLDataBesMgmt.ldf'
WAITFOR DELAY '00:00:05'
EXEC sp_dboption 'BesMgmt', 'single user', true
GO
I have set it to read only dbo only .... single user.... still get the same message....
does anyone have any suggestions....
HELP!! HOW TO CALL A DTS PACKAGE FROM A STORED PROC
Jul 20, 2005I'm a rookie with MSSQL. I need to run a DTS package to export a result setto an MX Excel spread sheet. I need to call the DTS from a stored procedureand pass it three values, depending on the input parameters to the storedproc.DTS package is no problem. Pretty easy with the DTS wizard. My problem isthat I can't figure out how to instansiate the DTS package object from astored proc and pass the three values as parameters to the DTS package sothey can populate the parameters I created in it.I found an article related to it, but I'm too much of a rookie to grasp it.It showed how to do this from a stored procedure:EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("MyGVName").Value,'MyGVValue'IF @hr <> 0BEGINPRINT '*** GlobalVariable Assignment Failed'EXEC sp_displayoaerrorinfo @oPKG, @hrENDI have three values and tree global variables to populate. Do I need to dothe above 3 times?How do I instantiate the package object? I've read up some on sp_OACreate,but I don't get it, yet.How do I initiate the variable @oPKG?It contains the name of the sp_OACreate string, right? How do I address aDTS package in the sp_OACreate string?I would really appreciate just writing out the sp_OACreate string and how Ipass values for three existing global variables to a DTS package named"DTS_1".I'm under some real pressure to get this done.Thanks for any help I can get.Gunny
View 1 Replies View RelatedRetreive @@Identity From Stored Proc Call
May 3, 2007Hi all, I've been struggling with this one for a while, but am still doing something wrong: I have three tables which need to be updated. I have a stored proc which accomplishes the first writes the data in for the first 2 tables. The last table is a one to many, so needs a seperate stored proc which will be called multiple times depending on the number of items in the order. My only question I am trying to get to here is: How do I get the first stored proc to return me the primary key value from the 1st insert (NOT the second)? I've tried a few different methods: the current one shown below returns me "2", as in the number of inserts performed. Dim InsertCmd As New SqlCommand("WriteOrder", oSQLConn)
InsertCmd.CommandType = CommandType.StoredProcedure
InsertCmd.Parameters.AddWithValue("@CartTotal", Session("CartTotal"))
InsertCmd.Parameters.AddWithValue("@CARDFNAME", BillingInfo("CARDFNAME"))
InsertCmd.Parameters.AddWithValue("@CARDLNAME", BillingInfo("CARDLNAME"))
...
InsertCmd.Parameters.AddWithValue("@CONTACTEMAIL", BillingInfo("CONTACTEMAIL"))
InsertCmd.Parameters.AddWithValue("@COMPANYMATCH", 0)
InsertCmd.Parameters.AddWithValue("@RECNUM", 0)
Response.Write("---" & InsertCmd.ExecuteNonQuery().ToString() & "---")
---------------------STORED PROC---------------------
ALTER PROCEDURE [dbo].[WriteOrder]
@CartTotal float,
@CARDFNAME varchar(30),
...
@CONTACTEMAIL varchar(100),
@COMPANYMATCH bit,
@RecNum int = 0 OUTPUT
AS
INSERT INTO [dbo].[ORDER]
([OrderDate], [OrderTotal], [CARDFIRSTNAME], [CARDLASTNAME], [BILLINGADDR1], [BILLINGADDR2], [BILLINGADDR3], [BILLINGCITY], [BILLINGSTATE], [BILLINGPOSTALCODE], [BILLINGCOUNTRY], [BILLINGPHONE], [BILLINGEMAIL], [CCTYPE], [ACCOUNTNUMBER], [CARDEXPIREMONTH], [CARDEXPIREYEAR], [CVV2], [TransactionID], [TransDateStamp], [ProcessorAuthCode])
VALUES
(GetDate() ,@CartTotal , @CARDFNAME, @CARDLNAME, @BILLINGADDRESS1, @BILLINGADDRESS2, @BILLINGADDRESS3, @BILLINGCITY, @BILLINGSTATE, @BILLINGPOSTALCODE, @BILLINGCOUNTRY, @BILLINGPHONE, @BILLINGEMAIL, @CCTYPE, @ACCOUNTNUMBER, @EXPIRATIONMONTH, @EXPIRATIONYEAR, @CVV2,@TransactionID, @TransDateStamp,@ProcessorAuthCode)
SET @RecNum = @@IDENTITY
INSERT INTO [dbo].[CONTACT]
([ORDERID], [CONTACTFNAME], [CONTACTLNAME], [CONTACTADDRESS1], [CONTACTADDRESS2], [CONTACTADDRESS3],
[CONTACTCITY], [CONTACTSTATE], [CONTACTPOSTALCODE], [CONTACTCOUNTRY], [CONTACTPHONE], [CONTACTEMAIL], [COMPANYMATCH])
VALUES
(@@IDENTITY , @CONTACTFNAME, @CONTACTLNAME, @CONTACTADDRESS1, @CONTACTADDRESS2, @CONTACTADDRESS3, @CONTACTCITY, @CONTACTSTATE, @CONTACTPOSTALCODE, @CONTACTCOUNTRY, @CONTACTPHONE, @CONTACTEMAIL, @COMPANYMATCH)
RETURN @RecNum
GO
I've also tried returning parameters like this, with no luck: InsertCmd.Parameters(32).SqlDbType = SqlDbType.Int InsertCmd.Parameters(32).Direction = ParameterDirection.ReturnValue Response.Write("---" & InsertCmd.Parameters(32).Value() & "---")Any help is greatly appreciated!
Call A DTS Export Into Excel From A Stored Proc ??
Jul 8, 1999I'm new to 7.0 and to DTS, and I find it all very confusing and need some help. :)
1) I need to automatically import data from a text file on a daily basis. Can someone tell me in short, simple steps how to set this up?
2) I need to export certain data into a new Excel sheet. Can this be triggered from a stored procedure, by calling some function? If yes, is it possible to send parameters to this function?
thanks bunches.
-M
Call Webservice From Trigger/stored Proc
Sep 17, 2004Is it possible to call an external web service from a SQL Server trigger or stored procedure?
View 6 Replies View RelatedCall A Oracle Stored Proc In SQL SERVER
Jul 20, 2005We have set up Oracle database as a linked server in SQL Server.We are able to access Oracle tables fine.I am trying to call a Oracle stored procedure in SQL Server as follows:declare @p1 varchar(1000)set @p1 = 'HHH'exec GENRET..OPS$GENRET.BOB_TEST_PROC @p1This is the message:Server 'GENRET' is not configured for RPC.Please help.Thanks in advancev
View 1 Replies View RelatedCall To Stored Proc Returning Null Datatable
Jun 6, 2007I have a stored proc which should be returning a datatable. When I execute it manually it returns all requested results. However, when I call it via code (C#) it is returning a null table which leads me to believe the problem is in my code. I'm not getting any errors during runtime. Any help at all would be a BIG help!
private void PopulateControls() { DataTable table = CartAccess.getCart(); }
public static DataTable getCart() { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "sp_cartGetCart";
DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = cartID; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param);
DataTable table = (GenericDataAccess.ExecuteSelectCommand(comm)); return table; }
public static DataTable ExecuteSelectCommand(DbCommand command) { // The DataTable to be returned DataTable table; // Execute the command making sure the connection gets closed in the end try { // Open the data connection command.Connection.Open(); // Execute the command and save the results in a DataTable DbDataReader reader = command.ExecuteReader(); table = new DataTable(); table.Load(reader); // Close the reader reader.Close(); } catch (Exception ex) { Utilities.SendErrorLogEmail(ex); throw ex; } finally { // Close the connection command.Connection.Close(); } return table; }
Can You Call A Stored Proc That Returns A Table Variable Using ADO?
Jan 8, 2004I have a stored proc that inserts into a table variable (@ReturnTable) and then ends with "select * from @ReturnTable."
It executes as expected in Query Analyzer but when I call it from an ADO connection the recordset returned is closed. All the documentation that I have found suggests that table variables can be used this way. Am I doing somthing wrong?
Call Cfusion Web Page/webserice From Stored Proc
Jun 12, 2008I am trying to call a CF web page/web service from a SQL 2005 stored proc and getting proxy info cannot be created. I cannot use stored proc 2005 CLR assembly because it will help us in creating only .asmx proxy not CFC proxy , any help would be appreciated.
exec master..xp_cmdshell 'http://wifi.abctest.com/Test/lartnerCall.cfm
Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 1
The xp_cmdshell proxy account information cannot be retrieved or is invalid. Verify that the '##xp_cmdshell_proxy_account##' credential exists and contains valid information.
Transact SQL :: Stored Proc Call By Adding Right Login
May 14, 2015I have dw schema in the database, owned by user dw.The login name is dw. The login had db_owner right in the database. The default schema for the login on the database is dw.Now Once I assign 'sysadmin' serverrole to dw login, I started seeing stored proc not found error, if try to execute stored proc without mentioning dw.spname;Also I am seeing table not found error while quering tables under dw schema, after the change.
View 20 Replies View RelatedStored Proc Call, 'table Name' As String, And T-sql Statement
Jun 16, 2006I need to write a storedproc that receives the name of a table (as a string) and inside the stored proc uses select count(*) from <tablename>. The problem is the passed in tablename is a string so it can't be used in the select statement. Any ideas how I can do what I want?
TIA,
barkingdog
NH: Best Practices Approach - Call Stored Proc - Or Run It Via Linked Server?
Mar 27, 2007what pro's cons would there be to having a linked server run a local stored proc against another sql server or create that stored proc on that other sql server and call it from there in the c# code.
i would think that calling the stored proc would be more efficient that running a linked server - but please let me know your thoughts. I'm not sure i can have permission to add a stored proc on that server, so possibly the linked server is the only solution - but if i can put a stored proc on that server should i?
thanks.
Jeff
SQL Server 2012 :: Call Stored Proc Once Per Each Row Of A Table Without Using CURSOR
Jul 10, 2014I have a situation where I need to call a stored procedure once per each row of table (with some of the columns of each row was its parameters). I was wondering how I can do this without having to use cursors.
Here are my simulated procs...
Main Stored Procedure: This will be called once per each row of some table.
-- All this proc does is, prints out the list of parameters that are passed to it.
CREATE PROCEDURE dbo.MyMainStoredProc (
@IDINT,
@NameVARCHAR (200),
@SessionIDINT
)
AS
BEGIN
[Code] ....
Here is a sample call to the out proc...
EXEC dbo.MyOuterStoredProc @SessionID = 123
In my code above for "MyOuterStoredProc", I managed to avoid using cursors and was able to frame a string that contains myltiple EXEC statements. At the end of the proc, I am using sp_executesql to run this string (of multipl sp calls). However, it has a limitation in terms of string length for NVARCHAR. Besides, I am not very sure if this is an efficient way...just managed to hack something to make it work.
How To Call AS400 Stored Proc And Evaluate The Return Code?
May 30, 2007I am trying to use SSIS to update an AS400 DB2 database by calling a stored procedure on the AS400 using an OLE DB command object. I have a select statement running against the SQL Server 2005 that brings back 20 values, all of which are character strings, and the output of this select is piped into the OLE DB command object. The call from SSIS works just fine to pass parameters into the AS400 as long as the stored procedure being called does not have an output parameter defined in its signature. There is no way that I can find to tell the OLE DB command object that one of the parameters is an output (or even an input / output) parameter. As soon as one of the parameters is changed to an output type, I get an error like this:
Code Snippet
Error: 0xC0202009 at SendDataToAs400 1, OLE DB Command [2362]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x8000FFFF.
Error: 0xC0047022 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "OLE DB Command" (2362) failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
Error: 0xC0047021 at SendDataToAs400 1, DTS.Pipeline: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0202009. There may be error messages posted before this with more information on why the thread has exited.
Information: 0x40043008 at SendDataToAs400 1, DTS.Pipeline: Post Execute phase is beginning.
Information: 0x40043009 at SendDataToAs400 1, DTS.Pipeline: Cleanup phase is beginning.
Task failed: SendDataToAs400 1
Warning: 0x80019002 at RetrieveDataForSchoolInitiatedLoans: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
Warning: 0x80019002 at Load_ELEP: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (3) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Load_ELEP.dtsx" finished: Failure.
I really need to know if the call to the AS400 stored procedure succeeded or not, so I need a way to obtain and evaluate the output parameter. Is there a better way to accomplish what I am trying to do? Any help is appreciated.
FoxPro Triggers Call FoxPro Stored Proc Calls SQL Server Stored Procedure
Mar 10, 2005I didn't want to maintain similar/identical tables in a legacy FoxPro system and another system with SQL Server back end. Both systems are active, but some tables are shared.
Initially I was going to use a Linked Server to the FoxPro to pull the FP data when needed. This works. But, I've come up with what I believe is a better solution. Keep in mind that these tables are largely static - occassional changes, edits.
I will do a 1 time DTS from FP into SQL Server tables.
I then create INSERT and UPDATE triggers within FoxPro.
These triggers fire a stored procedure in FoxPro that establishes a connection to the SQL Server and fire the appropriate stored procedure on SQL Server to CREATE and/or UPDATE the corresponding table there.
In the end - the tables are local to both apps.
If the UPDATES or TRIGGERS fail I write to an error log - and in that rare case - I can manually fix. I could set it up to email me from within FoxPro as well if needed.
Here's the FoxPro and SQL Server code for reference for the Record Insert:
FOXPRO employee.dbf InsertTrigger:
employee_insert_trigger(VAL(Employee.ep_pk),Employ ee.fname,Employee.lname,Employee.email,Employee.us er_login,Employee.phone)
FOXPRO corresponding Stored Procedure:
FUNCTION EMPLOYEE_INSERT_TRIGGER
PARAMETERS wepk,wefname,welname,weemail,WEUSERID,WEPHONE
nhandle=SQLCONNECT('SS_PDITHP3','userid','password ')
IF nhandle<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nhandle<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
RETURN
ENDIF
nquery="exec ewo_sp_insertNewEmployee @WEPK ="+STR(wepk)+",@WEFNAME ='"+wefname+"',@WELNAME ='"+welname+"',@WEEMAIL ='"+weemail+"',@WEUSERID ='"+weuserid+"',@WEPHONE='"+wephone+"',@RETCODE =0"
nsucc=SQLEXEC(nhandle,nquery)
SQLDISCONNECT(nhandle)
IF nSucc<0
m.errclose=.f.
IF !USED("errorlog")
USE tisdata!errorlog IN SELECT(1)
m.errclose=.t.
ENDIF
SELECT errorlog
INSERT INTO errorlog (date, time, program,source,user) ;
values (DATE(), TIME(), 'EMPLOYEE_INSERT_TRIGGER','nSucc<0 PARAMS: '+STR(wepk)+wefname+welname+weemail+WEUSERID+WEPHO NE,GETENV("username"))
IF m.errclose
USE IN errorlog
ENDIF
ENDIF
RETURN
SQL SERVER Stored Procedure called from FOXPRO Stored Procedure
CREATE procedure ewo_sp_insertNewEmployee (
@WEPK int,
@WEFNAME char(20),
@WELNAME char(20),
@WEEMAIL char(50),
@WEUSERID char(15),
@WEPHONE char(25),
@RETCODE int OUTPUT
)
AS
insert into WO_EMP (
WE_PK,
WE_FNAME,
WE_LNAME,
WE_EMAIL,
WE_USERID,
WE_PHONE
)
VALUES (
@WEPK,
@WEFNAME,
@WELNAME,
@WEEMAIL,
@WEUSERID,
@WEPHONE
)
IF @@ERROR <> 0
BEGIN
SET @RETCODE=@@ERROR
END
ELSE
BEGIN
-- SUCCESS!!
SET @RETCODE=0
END
return @RETCODE
GO
How To Convert Recursive Function Into Recursive Stored Procedure
Jul 23, 2005I am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
View 4 Replies View RelatedRecursive Call Of ETL Through SQL Server Job Error
Apr 30, 2008I have created a package which has to move huge volume of data in chuncks from source to target with a few sorter transformations and many lookup transformation. Once a chunck is completed the Execute SQL Task in the package updates the etldates into a control table against the chunkid. I want this run in a loop, as on completion of a chunk another should start and follow the same process until there are no more chuncks in the control table.
First i execute the package manually, then i have written a trigger to call the SQL Server Job (etl) on update of the dates for the chunkids in the control table. The job when being called by the on update trigger is throwing up the following error. "The job failed. The Job was invoked by User fwt
obin. The last step to run was step 1 (Package1)".
Am i missing something basic here?
PS: So i tried putting the package into a for each loop container, performance suffers. A chunck which used to take 2 hours is taking more than 12 hours to complete. Then i removed sorter transformation and used db sorting in my source OLE db. Then thought of cacheing the lookups but did not know how and what to give so i gave up. While iam here, DB sorting/SSIS sorting? which is better?
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
Maximum Characters In A Proc/SQL Statement Call
Jan 8, 2002How many characters can be used in a query?
Say I have a query that inserts into a varchar(4000). I need to do ~100 of them. Can all this data be passed at once. Or would I need to break it up?
Thoughts?
Dano
How To Call Proc On Linked Oracle Server
Sep 12, 2006
Sorry if this is very stupid question, but i've spent too long searching for the answer:
I have a linked Oracle server set up for RPC in SQL server 9 db. How do I call it? I've tried this, but gives Unspecified error:
"
OLE DB provider "OraOLEDB.Oracle" for linked server "SANSORA1" returned message "Unspecified error".
"
From this:
declare @UserName char(20) -- Current Username
declare @Password varchar(20) -- Current Password
declare @PasswordNew varchar(20) -- New Password
declare @PasswordCfm varchar(20) -- Confirm New Password
set @UserName = '900878'
set @Password = '900878'
set @PasswordNew = '777'
set @PasswordCfm = '777'
declare @return int
exec SANSORA1..PCG.USR_CHANGEPASSWORD @UserName ,@Password ,@PasswordNew ,@PasswordCfm;
--Exec ( 'SANSORA1..PCG.USR_CHANGEPASSWORD (' + @UserName + ' ,' + @Password + ' ,' + @PasswordNew + ' ,' + @PasswordCfm + ')')
Stored 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.
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 RelatedTransact SQL :: Types Don't Match Between Anchor And Recursive Part In Column ParentID Of Recursive Query
Aug 25, 2015Msg 240, Level 16, State 1, Line 14
Types don't match between the anchor and the recursive part in column "ParentId" of recursive query "tmp". Below is query,
DECLARE @TBL TABLE (RowNum INT, DataId int, DataName NVARCHAR(50), RowOrder DECIMAL(18,2) NULL, ParentId INT NULL)
INSERT INTO @TBL VALUES
(1, 105508, 'A', 1.00, NULL),
(2, 105717, 'A1', NULL, NULL),
(3, 105718, 'A1', NULL, NULL),
(4, 105509, 'B', 2.00, NULL),
(5, 105510, 'C', 3.00, NULL),
(6, 105514, 'C1', NULL, NULL),
[code]....
Recursive Stored Procedures
Nov 19, 2003I've got an application where I need to traverse a "tree" of parent and child relationships. I've got code in my application that does this nicely, and I want to move it to the database in the form of stored procs for a little better performance. The initial way I created it, is that the stored proc creates a cursor for the parent nodes, then calls itself for each child node, and rips through it that way. The problem is is that I get a cursor already exists error. Am I doing something wrong, or is that a limitation of SQL server to only have one named cursor open at a time? Is there a better way to do this?
Thanks in advance...
Stored Recursive Procedure
Mar 11, 2007Hi,
I would like some help on a stored recursive procedure I have been working on for my assigment. I had the solution from my course master some time back, but accidentally deleted the file before finishing the course, and would like the solution to that problem.
The questions are as follows:
1) Create a recursive stored procedure that counts from 1 to 10
2) Create a recursive stored procedure that counts from 10 to 1
I believe there's more than one approach in solving these procedures, and any feeback is welcome.
cheers
Recursive Stored Procedure?
Oct 16, 2006How do I call my Stored Procedure recursively:CREATE PROCEDURE dbo.GetParentIONode(@IONodeID int,@FullNodeAddress char(100) OUTPUT)ASBEGINDECLARE @ParentIONodeID intIF EXISTS (SELECT ParentIONodeID FROM IONodes WHERE IONodeID = @IONodeID)BEGINSET @FullNodeAddress = CAST((SELECT ParentIONodeID FROM IONodes WHEREIONodeID = @IONodeID) AS VARCHAR) + ' / ' + @FullNodeAddress--CALL SP Again with @ParentIONodeID and @FullNodeAddress untilParentIONodeID = NULLSELECT @FullNodeAddressENDENDGO
View 4 Replies View Related'Recursive Stored Procedure In SQL SERVER
Aug 31, 2003Hi,
I have 2 SQL SERVER tables MSTHDRML (Header table) & MSTDTLML(details Table)
MSTHDRML
MLID int 4
MLITemID int 4
ConcatStringvarchar 20
EffectiveDateFromsmalldatetime
EffectiveDateTodatetime
MSTDTLML
MLID int40
ItemID int40
ConcatStringvarchar201
Qty money81
The MLID in the header table will be generated automatically.All the Parents will be stored in the HEADER and their childs in the DETAIL.When a child is added to a parent,the Parent's MLID will be stored in the MLID field in the DETAIL table with the newly added child.That child will come to the PARENT table when a child is added to that.The MLITEM id in the parent table can be repeated when that item undergoes a rivision.But the MLID for this will be a new one.An item in the Parent Table can have any number of childs and these childs can have any number of children(there is no limit for the level.)
Some Sample Data
MSTHDRML
--------------
MLIDMLITemID ConcatStringEffectiveDateFromEffectiveDateTo
11000 56V 01/06/200331/12/9999
21003 Red 01/08/200331/12/9999
31001 01/08/200331/12/9999
41007 01/08/200331/12/9999
51008 01/08/200331/12/9999
61002 01/08/200331/12/9999
71005 01/08/200331/12/9999
82000 01/08/200331/12/9999
MSTDTLML
--------------
MLIDItemIDConcatStringQty
11001Round 10
11002Square 20
21004Blue 19
11005Green 22
31007Flat 223
41008 100
51009 200
61010 11
71011 22
71010 45
71012 454
82001 5
Now if i select an item id '1000' (for example from the Header Table) with a concatstring (it could be without a concat string also).all its childs and their children should be printed in a report like the following
1000
|
--- 1001
| |
| --1007
| |_ 1008
----1002 |__1009
| |_1010
|
----1005
************************************************** ***************************
I NEED TO CREATE THE TREE USING BOTH THE HEADER(MSTHDRML) AND THE DETAIL TABLE(MSTDTLML)
************************************************** ***************************
How can this be done.Is it necessary to use a recursive function in a stored procedure to generate this ...... i have never used Recursive function in SQL SERVER Stored Procedures.Can anyone help me on this(with Code).if not stored procedure, then what else can be done for this.
Recursive Stored Procedure Problem
Feb 10, 2008thnx
View 3 Replies View Related