Receiving And Sending A Cursor With(in) A Stored Procedure
Can someone post some code that shows a Stored Procedure receiving a cursor that it can process - lets say a group of order detail records are received that must be saved along with the single Order header record.
And, in another example, a SP returns a result set to the calling program. - For example, a particular sale receipt is pulled up on the screen and the order detail is needed.
Thanks for help on this,
Peter
View Complete Forum Thread with Replies
Related Forum Messages:
Receiving Queue Does Not Fire Stored Procedure
I am doing my first SSB application where two different servers send messages to each other. The logic was all previously tested on a single server between two databases and it worked OK. The problem I am having is that when the message is received at the target server (I see this in profiler), the stored procedure associated with the queue does not fire. I see an acknowledgment fire back to the initiator, but it is like the target server does nothing with the initial message. Any ideas on how I can further troubleshoot this? FWIW, I used the setup tool provided by RemusResanu to set up the routes and service bindings. Thanks for any help! John
View Replies !
Receiving Queue Stops Firing Stored Procedure
I've set up a Service Broker and it was working fine. All of a sudden each time I switch on my computer it's not working anymore. I have to drop the services, queues, contract and messages and create them again. Then it works again until I shutdown my computer. When I start it again it's not working. Any suggestion on how to fix this? Thank you
View Replies !
Receiving Error 156 - Incorrect Syntax When Compiling Stored Procedure
The following query works fine in query analyzer, but when I add it to my stored procedure I receive an error 156. How do I work around this? select distinct(dateposted) from billingprocedures bp1, billingprocedureordercomponentvalues bpocv, ordercomponentvalues ocv where bp1.billingid = @billingid and bp1.procedureid = bpocv.billingprocedureid and bpocv.ordercomponentvalueid = ocv.ordercomponentvalueid Thanks, Bryan
View Replies !
Sending Mail Through Stored Procedure
Hi, I have created a stored procedure for sending mail. While executing that i am getting the following error message: SQL Server blocked access to procedure 'sys.sp_OACreate' of component 'Ole Automation Procedures' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ole Automation Procedures' by using sp_configure. For more information about enabling 'Ole Automation Procedures', see "Surface Area Configuration" in SQL Server Books Online. Can anybody explain me how to resolve it? Expecting the solution at the earliest
View Replies !
Sending A List To A Stored Procedure
Hello How can I send a list(s) of data into a stored procedure for processing? For instance, I have a table: GroupContacts(groupname, userid, contactid). At times I will be inserting X amount of records into it, X depends on how many contacts need to be added to a group by a user. How can I send a list of (groupname, userid, contactid)'s into a stored procedure and then use some kind of for-loop to iterate through the list and insert the records? Thanks jenn
View Replies !
Sending Mail From Stored Procedure?
Dear All, I need to create a functionality in SQL server 2000 that would send out data from a table to users within our company. Is there some built in funcitonality wherby i can mail out the selected data from the table from a stored procedure? Then i could schedule the stored procedure to be run every morning at 7 am. Never done this, can some one let me know if that would work and give me some pointers on what commands to use in the stored procedure? Thanks in advance!
View Replies !
Sending Data To A Stored Procedure With ASP Error
Hi all..I'm trying to send multiple INT values to a Stored Procedure that willbe handled in an IN statement.ASP Code:strSQL = "SP_Get_Selections '29, 32'where 29 and 32 are 2 integer valuesNow, in my stored procedure... I would like to look these values up inan IN statement likeCREATE Procedure SY_GET_SELECTIONS@authorid varchar(20)SELECT * FROM Authors WHERE AuthorID IN (@authorid)But when I use this, I get [Microsoft][ODBC SQL Server Driver][SQLServer]Syntax error converting the varchar value '29, 32' to a columnof data type int.Any help would be great.Thanks
View Replies !
Sending Null DateTime Value To Stored Procedure From VB
In a VB.NET script, I am adding the dbnull value to a parameter that will populate a smalldatetime column: cmd.Parameters.Add("@unitHdApprove", System.DBNull.Value.ToString) The stored procedure then defines the input as smalldatetime: @unitHdApprove smalldatetime, However, the result is that the record is inserted with 1/1/1900 as the date value, instead of <NULL>. I'm guessing that this occurs because the conversion of a '' to date will return 1/1/1900, and VB requries the parameter value to be a string (at least with this syntax), so System.DBNull.Value.ToString really equals ''. I've rewritten the proc to accept the date as a string instead, and then for each date, cast it to a smalldatetime or set it to null as is appropriate. But this is a really bulky way to do things with more than a few dates! Is there any way to change what is in my VB code so that the procedure will insert the actual null value? Thanks, Sarah
View Replies !
Sending Messages To .NET Code From Stored Procedure
I have some long running stored procedures that I invoke from ADO.NET (using Typed Datasets). Inside the stored procedure, I like to keep track of the execution by using the PRINT command. Is there a way of extracting and displaying this PRINT information in .NET during the stored procedure execution?
View Replies !
Problem Sending Date Parameters To Stored Procedure
Can anyone help, I have a problem with calling my stored procedure in vb and passing in its parameters. the system is supposed to then use the store the procedures recordset and use this to pass into a crystal report. I am getting the following message: Procedure 'TestReport' expects parameters '@StartDate', which was not supplied. This happens on the objCom.Execute line. This is my code: Dim CRXApplication As New CRAXDRT.Application Dim CRXReport As CRAXDRT.Report Dim CRXDatabase As CRAXDRT.Database Set CRXReport = CRXApplication.OpenReport(APPConst.DatabasePath & stReport & ".rpt") Dim objCom As adodb.Command Set objCom = New adodb.Command Dim prm_one As adodb.Parameter Dim prm_two As adodb.Parameter Dim prm_three As adodb.Parameter Dim fdate, tdate As Date Call CRXReport.Database.Tables(1).SetLogOnInfo("dell330", "Rota", "RotaAdmin", "dadcahadfu") Set CRXDatabase = CRXReport.Database fdate = cboFrom.Value tdate = cboTo.Value With objCom .CommandText = "TestReport" .CommandType = adCmdStoredProc .ActiveConnection = cn.ConnectionString Set prm_one = .CreateParameter("@StartDate", adDBTimeStamp, adParamInput, 8, fdate) .Parameters.Append prm_one Set prm_two = .CreateParameter("@EndDate", adDBTimeStamp, adParamInput, 8, tdate) .Parameters.Append prm_two Set prm_three = .CreateParameter("@Team", adInteger, adParamInput, 4, 2) .Parameters.Append prm_three .Parameters.Refresh End With Set SQLRecs = objCom.Execute CRXReport.DiscardSavedData CRXDatabase.SetDataSource SQLRecs, 3, 1 This is my Stored Procedure: CREATE PROCEDURE [dbo].[TestReport] @StartDate datetime, @EndDate datetime, @Team int AS SELECT dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode AS Expr1, COUNT(dbo._StaffUnavailable.ID) AS CountOfID, dbo._StaffUnavailable.[Date], dbo._Team.Description AS Team, dbo._Staff.Team_ID FROM dbo._Staff INNER JOIN dbo._StaffUnavailable ON dbo._Staff.Staff_ID = dbo._StaffUnavailable.Staff_ID INNER JOIN dbo._Team ON dbo._Staff.Team_ID = dbo._Team.ID GROUP BY dbo._Staff.Staff_ID, dbo._Staff.UniqueName, 2 * DATEDIFF(day, @StartDate, dbo._StaffUnavailable.[Date]) + dbo._StaffUnavailable.TimeCode, dbo._StaffUnavailable.[Date], dbo._Team.Description, dbo._Staff.Team_ID Having Team_ID = @Team AND ([Date] >= @StartDate AND [Date] <= @EndDate) GO Thanks in advance Ian.
View Replies !
How Can I Assign A Stored Procedure As Cursor's Data Source In AStored Procedure?
How can I create a Cursor into a Stored Procedure, with another Stored Procedure as data source? Something like this: CREATE PROCEDURE TestHardDisk AS BEGIN DECLARE CURSOR HardDisk_Cursor FOR Exec xp_FixedDrives -- The cursor needs a SELECT Statement and no accepts an Stored Procedure as Data Source OPEN CURSOR HardDisk_Cursor FETCH NEXT FROM HardDisk_Cursor INTO @Drive, @Space WHILE @@FETCH_STATUS = 0 BEGIN ... END END
View Replies !
Cursor With Stored Procedure
I have a stored procedure that basically recieves the where clause of a select statement and executes the new sql statement... ie: CREATE PROCEDURE [dbo].[bsa_GetImportIDs] (@FilterText varchar(1000)) AS DECLARE @MySQL varchar(1000) SET @MySQL = "SELECT Import_ID FROM tblImport WHERE " + @FilterText EXEC (@MySQL) GO Now, in another stored procedure, I need to use the stored procedure above in a cursor so that I can execute an insert statement for each occurance of the Import_ID that appears in that dataset... ie: CREATE PROCEDURE [dbo].[bsa_PutLargeCase] AS DECLARE @CaseID uniqueidentifier SET @CaseID = NewID() Declare @ImportID uniqueidentifier Declare curClient Cursor FAST_FORWARD for SELECT Import_ID FROM dbo.bsa_GetImportIDs (@FilterText) <---- this does not work!!! Open curClient FETCH NEXT FROM curClient INTO @ImportID WHILE @@FETCH_STATUS = 0 BEGIN EXEC dbo.bsa_PutCaseDetail @CaseID, @ImportID FETCH NEXT FROM curClient INTO @ImportID END CLOSE curClient DEALLOCATE curClient GO How can I utilize my first stored procedure in the cursor of the second? ... or Are there any other approaches that may be a better solution to what I am trying to accomplish? Thanks in advance for any input.
View Replies !
Stored Procedure Into A Cursor
Hi guys!!I am trying to fill a cursor with the results of a StoredProcedured, but SQL give me an syntax error message , does any one cangive me some helpI am using SQL Server, this is the first lines of my codeDECLARE FRates_Cursor CURSOR FORexec GET_FJRs_Eng 'all'OPEN FRates_Cursorif I run just the exec GET_FJRs_Eng 'all' line it give me the dataresults I am trying to put into the cursor, what that means is thestored is working fineThanks in advance
View Replies !
Help In Stored Procedure With Cursor
I am trying to create the procedure from two tables to create the flat file from the two tables and as they are processed to the file then want to change the status from 'U' to 'P'. I am trying to declare the cursor from the union of two tables so that I can store the data into one table. And then put the data into the flat file and then marked the processed data as 'P' from 'U' please help in doing that. thanks, Harish CREATE PROCEDURE SP_P8 AS declare c123 cursor for select 'CA'+'|'+COMPANY+'|'+COMPANYNAME+'|'+CUSTOMERNUMBE R+'|'+COMPANYPHONE +'|'+COMPANYFAX+'|'+FEDTAXID+'|'+DUNSNUMBER+'|'+S1 099+'|'+DUNSSUFFIX+'|'+convert(char,ADDRESSSEQ) +'|'+COUNTRY+'|'+ADDRESSTYPE+'|'+ADDRESS1+'|'+ADDR ESS2+'|'+ADDRESS3+'|'+ADDRESS4 +'|'+CITY+'|'+STATE+'|'+ZIP+'|'+FAXNUMBER hello1,status from v1 UNION select 'CT'+'|'+COMPANY+'|'+CONTACTNAME+'|'+CONTACTTYPE+' |'+CONVERT(CHAR(10),CONTACTADDRESS) +'|'+PHONE+'|'+FAX+'|'+TITLE hello,status from v3 declare @status char(1) declare @wholerecord text open c123 fetch next from c123 into @wholerecord,@status while @@fetch_status=0 begin update v1 set status='P' where current of c123 update v3 set status='P' where current of c123 fetch next from c123 into @wholerecord,@status end close c123 deallocate c123 select * into temp23 from v2 where status='P' and declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XCUST'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..temp23 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Ppassword' exec master..xp_cmdshell @cmdstring drop table temp23
View Replies !
Help In Stored Procedure With Cursor
Please help in creating the stored procedure for the following code. Thanks in Advance. ============================= Create procedure extractdata @companynumber varchar(3), @firstdateofmonth int, @lastdateofmonth int as truncate table xt truncate table xtt insert into xt(account_code,balance_date,net_change,current_ba lance,oldlob) select account_code, balance_date,net_change,current_balance,@companynu mber from ccc@companynumber..glbal where balance_date<=@firstdateofmonth declare @v1 varchar(32) declare @v2 int declare @v3 float declare c1 cursor for select account_code, max(balance_date) from xt where oldlob=@companynumber group by account_code open c1 fetch next from c1 into @v1,@v2 WHILE @@FETCH_STATUS = 0 begin update xt set status='max' where account_code=@v1 and balance_date=@v2 and oldlob=@companynumber fetch next from c1 into @v1,@v2 end close c1 deallocate c1 insert into xtt(account_code,balance_date,net_change,current_b alance,oldlob) select account_code,balance_date,net_change,current_balan ce,oldlob from xt where status='max' and oldlob=@companynumber order by account_code insert into test1(account_code,current_balance,net_change,bala nce_date,oldlob) select account_code,round(current_balance,2),round(net_ch ange,2),balance_date,@companynumber from xtt where oldlob=@companynumber and account_code in (select account_code from xxxmay where oldlob=@companynumber) update test1 set net_change=0 where balance_date<@lastdateofmonth and oldlob=@companynumber select sum(current_balance),sum(net_change) from test1 where oldlob=@companynumber
View Replies !
Out A Cursor From A Stored Procedure
hello! any of you have an idea how i can declare an output parameter for my cursor which is inside a stored procedure. i would lik to see the output using the exec command but i don't know how to get the out from my cursor. please help! honey
View Replies !
Using Cursor As OUT Parameter In Stored Procedure
Hi guys, I have a serious problem. I need to use my cursor as an out parameter, but the problem is, HOW CAN I CLOSE THE CURSOR??????If I dont close the cursor, my server is getting really slow because of the open cursors, cause I have more than 100 stored procedures, which have a cursor as an out-parameter.Here's one of my stored procedures : create or replace PACKAGE pkgResIS TYPE resType IS REF CURSOR RETURN res%ROWTYPE;END pkgRes; create or replace procedure res_sel_val(p_id in number,cs out pkgRes.resType)asBEGIN open cs for select * from res where res_id = p_id; --close cs;EXCEPTION when others then raise_application_error(-20970, 'record kan niet geselecteerd worden');END res_sel_val; How can I close my cursor? If I write the "close cursor" (which is in red at the code above), it returns an empty cursor, which is not my intention.Please help me with thisThanks in advance Morph 'n Nike
View Replies !
Creating Cursor From Stored Procedure
Hi guys!i want to create one cursor in the t-sql. the problem is i want to usestored procedure instead of select command in cursor.can anyone tell me how can i use stored procedure's o/p to createcursor?i'm using sql 2000 and .net 2.0thanks,Lucky
View Replies !
Error From Stored Procedure With A Cursor
I receive the following intermittent error when executing a stored procedure: Msg 16942, Sev 16: Could not generate asynchronous keyset. The cursor has been deallocated. [SQLSTATE 42000] Msg 3624, Sev 16: Location: lckclass.cpp:111 Expression: m_status == ACTIVE || m_status == ORPHANED SPID: 17 Process ID: 203 [SQLSTATE 01000] The process uses a cursor to update a SQL7 table from another. This is not a consistent failure. Sometimes the procedure runs fine to completion. Has anyone else had the same problem??? We had a suggestion that it might be a tempdb problem??? Any ideas?
View Replies !
Dynamic Cursor In Stored Procedure
when i try to compile the following sp, i get an error Line 11:Incorrect syntax near;. Can someone please tell me what i am doing wrong. thanks a lot. CREATE PROCEDURE test_dump (@p_query nvarchar(4000)) AS declare @cmdtxt as varchar(4000), @SQLString NVARCHAR(4000), @SQLString1 NVARCHAR(4000), @pid varchar(22), @lname varchar(60) begin EXEC SQL BEGIN DECLARE SECTION; char prep[] = @p_query; EXEC SQL END DECLARE SECTION; EXEC SQL PREPARE prep_stat FROM :prep; EXEC SQL DECLARE contact_crsr CURSOR FOR prep_stat; OPEN contact_crsr FETCH NEXT FROM contact_crsr INTO @pid, @lname -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN SET @SQLString1 = 'HELLO ' + @pid + ' ' + @lname select @cmdtxt = "echo " + @SQLString1 + " >> c:empmyfile.txt" exec master..xp_cmdshell @cmdtxt FETCH NEXT FROM contact_crsr INTO @pid, @lname END CLOSE contact_crsr DEALLOCATE contact_crsr end
View Replies !
How To Declare Cursor In Stored Procedure?
I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure. CREATE PROCEDURE STP_EMPSAL @empno int, @Employee_Cursor CURSOR VARYING OUTPUT FOR SELECT empno FROM AdventureworksDW.dbo.emp AS OPEN Employee_Cursor; FETCH NEXT FROM Employee_Cursor into @empno; WHILE @@FETCH_STATUS = 0 BEGIN BEGIN TRAN UPDATE emp set sal= sal+ 2000 where empno = @empno and comm is null mgr='Scott'; FETCH NEXT FROM Employee_Cursor into @empno; COMMIT; END; CLOSE Employee_Cursor; DEALLOCATE Employee_Cursor;
View Replies !
Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor. The doctors table has the following columns with specialism allowing NULL values doctor ( staff_no CHAR(3), doctor_name CHAR(12), position CHAR(15), specialism CHAR(15), PRIMARY KEY(staff_no) ) Any help would be greatly appreciated.
View Replies !
Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit. (#tblSalesOrders is a temporary table which containsset of records from orginal table )DECLARE @soNo1 INT DECLARE @CursorOrders CURSOR SET @CursorOrders = CURSOR FAST_FORWARD FOR select fldSoNo from #tblSalesOrders declare @newSONO1 int OPEN @CursorOrders FETCH NEXT FROM @CursorOrders INTO @soNo1 WHILE @@FETCH_STATUS = 0 BEGIN ----for each salesorder insert to salesorderline insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1; set @newSONO1=SCOPE_IDENTITY; -------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert -------------------------- SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM FETCH NEXT FROM @CursorOrders INTO @soNo1 END CLOSE @CursorOrders DEALLOCATE @CursorOrders
View Replies !
Unique Cursor Name In Recursive Stored Procedure
How can I solve this probleM? I thing that I need to create cursor with unique cursor name procedure XX param DECLARE cc CURSOR FOR SELECT....param --ERROR bcause it's already exists OPEN cc ... WHILE @@FETCH_STATUS = 0 BEGIN XX param FETCH NEXT ... END CLOSE cc ....
View Replies !
Stored Procedure Cursor Problem URGENT
Hi, I have created the following stored procedure to get the text from one table and compare with them with another table and the one's that match will assign the corresponding ID. But the problem is that it only assigns the last id in the table from the main table which new_cur2 holds. So the problem is that its not updating with the correct ID its just updating with the last ID the cursor holds. Does any one know what it could be.....I think it may just be a little coding error....thanks CREATE PROCEDURE [MYSP] AS Declare @pdesc nvarchar(30) Declare @ssc int Declare @myid int Declare @name nvarchar(30) Declare new_cur CURSOR DYNAMIC FOR SELECT ProductDescription, SubSubCatID FROM C2000HPB FOR UPDATE Open new_cur FETCH FROM new_cur INTO @pdesc, @ssc While @@FETCH_STATUS = 0 BEGIN Declare new_cur2 CURSOR DYNAMIC FOR SELECT SubSubCatID, SubSubCategory FROM SSC FOR READ ONLY Open new_cur2 FETCH FROM new_cur2 INTO @myid, @name While @@FETCH_STATUS = 0 BEGIN IF PATINDEX ('@name%',@pdesc) = 0 Set @ssc = @myid UPDATE C2000HPB SET SubSubCatID = @ssc FETCH NEXT FROM new_cur2 INTO @myid, @name END Close new_cur2 DEALLOCATE new_Cur2 FETCH NEXT FROM new_cur INTO @pdesc,@ssc END Close new_cur DEALLOCATE new_Cur
View Replies !
Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure. For sample: In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds. How can I do ??? My code: Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50) SELECT @idRelat = idRelat, @cmd_Det = cmd_DET FROM Relatórios WHERE Nome = @p_Relat Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint, @Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024) Select @Tot_Col = 0 Select @Tot_Lin = 0 Select @Campos = 0 Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma" Select @Aux_Group = "GROUP BY " Select @Aux_Order = "ORDER BY " Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50) Declare cur_Aux insensitive cursor for SELECT Local, Linha, Campo From Relatórios_Margens WHERE (idRelat = @idRelat) ORDER BY Local, Linha Open cur_Aux Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo While @@FETCH_status = 0 begin If @a_Local = "C" Select @Tot_Col = @Tot_Col + 1 Else Select @Tot_Lin = @Tot_Lin + 1 Select @Campos = @Campos + 1 If @Aux_Group <> "GROUP BY " begin Select @Aux_Group = @Aux_Group + ", " If @Aux_Order <> "ORDER BY " begin Select @Aux_Order = @Aux_Order + ", " Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos Select @Aux_Group = @Aux_Group + @a_Campo Select @Aux_Order = @Aux_Order + @a_Campo Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo End Select @Aux_Select = @Aux_Select -- <<<< MONTA COMANDO SQL Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order Declare @Cursor_Aux cursor Set @Cursor_Aux = cursor for @Aux_Select Open @Cursor_Aux Not working !!!!
View Replies !
How To Call A Stored Procedure In T-SQL And Pass It To A Cursor
Hi, I have a kind of problem. In SQL Server I have a stored procedure ressembling this: Code Block ALTER PROCEDURE procedure1 ( @param int ) SELECT * FROM table WHERE param = @param Now I want to call this procedure and pass it to a cursor. We all know you can do this: Code Block DELCARE cursor1 CURSOR for SELECT * FROM table WHERE param = @param .. , but I want something like this: Code Block DECLARE cursor1 CURSOR for EXEC procedure1 @param Is it possible? I could solve it in another, but then I have to connect 2x to the database, which is less performant. I have also tried something like this: Code Block ALTER PROCEDURE procedure1 ( @param int ) SELECT @test = id FROM table WHERE param = @param RETURN @test ALTER PROCEDURE procedure2 ( @param int ) DECLARE @var varchar(100) EXEC @var = procedure1 @param But then it returns always 0. So is there a way to pass a procedure's select to a cursor? Thanks in advance Stevevil0
View Replies !
Problem When Invoking Stored Procedure With Cursor From Inside .net
Dear all,i'm facing a problem with my storedprocedure which happened when i ran my web application and reach to the point where my class invoke this storedprocedure,my SP contains a cursor that built his sql according to certain condition, so i put the "SET @cur Cursor For....." inside the if block (definitely i've declared it under AS keyword directly) and this SP is working well inside sql server(I've tested it), BUT when my ASP.net code invoke this SP it gives me the following error : "The Variable @cur does not currently have a cursor allocated to it" repeated as much as there are IF clauses in my SP,Please Help.Regards,
View Replies !
Cursor Works In Query Analyzer But Not In Stored Procedure
Hi i have a script works in sql query analyzer; declare @id decimal declare mycur CURSOR SCROLL for select myRowID from myTable order by myRowID open mycur; Fetch ABSOLUTE 30 from mycur into @id close mycur; deallocate mycur; select @id this script turns me a value. i create a stored procedure from above script and its syntax is ok; CREATE PROCEDURE SELECT_MyRow AS declare @cur cursor declare @RowID decimal set @cur = CURSOR SCROLL for select myRowID from myTable order by myRowID open @cur Fetch ABSOLUTE 30 from @cur into @RowID close @cur deallocate @cur select @RowID GO my c# code using stored procedure is below; Code Snippet try { OleDbCommand cmd = new OleDbCommand("SELECT_MyRow", myconnection); cmd.CommandType = CommandType.StoredProcedure; myconnection.Open(); OleDbDataReader reader = cmd.ExecuteReader(); MessageBox.Show(reader.GetName(0));//here fails while (reader.Read()) { MessageBox.Show(reader.GetDecimal(0).ToString()); } reader.Close(); myconnection.Close(); } catch(Exception ex) { MessageBox.Show(ex.Message); } The code above fails because reader reads no values, error message is "No data exists for the row/column" but i know exists. Can anyone help me, what is the difference between stored procedure and script ?
View Replies !
Stored Procedure With CURSOR OUTPUT Parameter, Using JDBC And A Callable Statement
My server is MS Sql Server 2005. I'm using com.microsoft.sqlserver.jdbc.SQLServerDriver as the driver class. I've established a connection to the database. I'm trying to invoke a stored procedure using JDBC and a callable statement. The stored procedure has a parameter @CurOut CURSOR VARYING OUTPUT. How do I setup the callable statement so the output parameter is accepted by the driver? I'm not really trying to pass a cursor up to the database Server but I'm wanting a cursor back from the stored procedure that is other than the result set or other value the stored procedure returns. First problem: What java.sql.Types (or SQL Server specific) value do I specify for the out parameter I'm registering on the CallableStatement? Second problem: What do I set the value of the parameter to? The code looks like: CallableStatement cstmt = myConnection.prepareCall(sQuery); cstmt.registerOutParameter(1, Types.OTHER); // What is the right type? cstmt.setNull(1, Types.OTHER); // What is the right type? if (cstmt.execute()) { ResultSet rs = cstmt.getResultSet(); } Execution results in a NullPointerException from the driver. What am I doing wrong? Thanks for your assistance. Jon Weaver
View Replies !
PRINT Debug Messages And CURSOR In Stored Procedure Confuses DTS; &"Invalid Pointer&"
I have the following stored procedure that is called from the source ofa transformation in a DTS package. The first parameter turns on PRINTdebug messages. The second, when equals 1, turns on the openning,fetching, and closing of a cursor. Things are fine if only one of thetwo parameters was set to 1.When run with both parameters turned on, "dba_test_dts 1, 1", DTSsource (tab) preview fails because it thinks no row was returned. "Norowset was returned from the SQL statement". Understanbly then thetransformation step would also fail with the "Invalid Pointer" error.As you'd see, I have SET NOCOUNT ON in the code. Has anyoneexperienced this? Is this a known bug? This occurs in SQL Server 2000running on Windows Server 2003.-----------------------------------------------------------------------------------------------------------------CREATE PROC dba_test_dts ( @debug BIT = NULL, @cur BIT = NULL )AS-- Always have these 2 options set or unset so DTS would not error out-- with the Invalid Pointers message.SET NOCOUNT ONSET ANSI_WARNINGS OFFDECLARE @FMT_FILE_NAME VARCHAR(256)DECLARE @OUTPUT_FILE_NAME VARCHAR(256)DECLARE @emp_id INTDECLARE @lastname VARCHAR(70)IF ( @debug = 1 )BEGINPRINT '=== BEGIN ==='PRINT 'Stored Procedure dts_calling_stored_proc'PRINT 'Begin timestamp: ' + CONVERT(VARCHAR(32), CURRENT_TIMESTAMP,109 )PRINT 'Server : ' + @@SERVERNAMEPRINT 'Host Name/ID : ' + HOST_NAME() + '/' + CONVERT(VARCHAR(32),HOST_ID())PRINT 'Database : ' + DB_NAME()PRINT 'User/NT User : ''' + USER_NAME() + '''/''' + SYSTEM_USER +''''PRINT 'DEBUG FLAG : ' + CONVERT( CHAR(1), @DEBUG )PRINT '=== BEGIN ==='PRINT SPACE(1)ENDIF ( EXISTS ( SELECT 1 FROM sysobjects WHEREid=object_id(N'Employees_temp') ) )DROP TABLE Employees_tempCREATE TABLE Employees_temp(emp_id INTEGER, lastname VARCHAR(70))INSERT INTO Employees_temp([emp_id],[lastname])SELECT EmployeeID, lastnameFROM EmployeesIF ( @cur = 1 )BEGINDECLARE curEmp CURSOR FORSELECT emp_id, lastnameFROM Employees_tempOPEN curEmpFETCH NEXT FROM curEmpINTO@emp_id, @lastnameWHILE ( @@FETCH_STATUS = 0 )BEGINFETCH NEXT FROM curEmpINTO@emp_id, @lastnameENDCLOSE curEmpDEALLOCATE curEmpENDSELECT emp_id, lastnameFROM Employees_tempGO
View Replies !
Help Cursor Based Stored Procedure Is Getting Slower And Slower!
I am begginner at best so I hope someone that is better can help.I have a stored procedure that updates a view that I wrote using 2cursors.(Kind of a Inner Loop) I wrote it this way Because I couldn'tdo it using reqular transact SQL.The problem is that this procedure is taking longer and longer to run.Up to 5 hours now! It is anaylizing about 30,000 records. I thinkpartly because we add new records every month.The procedure works like this.The first Cursor stores a unique account and duedate combination fromthe view.It then finds all the accts in the view that have that account duedatecombo and loads them into Cursor 2 this groups them together for datamanipulation. The accounts have to be grouped this way because aaccount can have different due dates and multiple records within eachaccount due date combo and they need to be looked at this way aslittle singular groups.Here is my procedure I hope someone can shead some light on this. Myboss is giving me heck about it. (I think he thinks Girls cant code!)I got this far I hope someone can help me optimize it further.CREATE PROCEDURE dbo.sp_PromiseStatusASBEGINSET NOCOUNT ON/* Global variables */DECLARE @tot_pay moneyDECLARE @rec_upd VARCHAR(1)DECLARE @todays_date varchar(12)DECLARE @mActivityDate2_temp datetimeDECLARE @tot_paydate datetime/* variables for cursor ACT_CUR1*/DECLARE @mAcct_Num1 BIGINTDECLARE @mDueDate1 datetime/* variables for ACT_CUR2 */DECLARE @mAcct_Num2 BIGINTDECLARE @mActivity_Date2 datetimeDECLARE @mPromise_Amt_1 moneyDECLARE @mPromise_Status varchar(3)DECLARE @mCurrent_Due_Amt moneyDECLARE @mDPD intDECLARE @mPromise_Date datetimeSELECT @todays_date =''+CAST(DATEPART(mm,getdate()) AS varchar(2))+'/'+CAST(DATEPART(dd,getdate()) AS varchar(2))+'/'+CAST(DATEPART(yyyy,getdate()) AS varchar(4))+''DECLARE ACT_CUR1 CURSOR FORSELECT DISTINCTA.ACCT_NUM,A.DUE_DATEFROM VWAPPLICABLEPROMISEACTIVITYRECORDS AOPEN ACT_CUR1FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1WHILE (@@FETCH_STATUS = 0)BEGINSELECT @rec_upd = 'N 'DECLARE ACT_CUR2 CURSOR FORSELECTB.ACCT_NUM,B.ACTIVITY_DATE,B.PROMISE_AMT_1,B.PROMISE_STATUS,B.CURRENT_DUE_AMT,B.DAYS_DELINQUENT_NUM,B.PROMISE_DATE_1FROM VWAPPLICABLEPROMISEACTIVITYRECORDS B (UPDLOCK)WHERE B.ACCT_NUM = @mAcct_Num1ANDB.DUE_DATE = @mDueDate1ORDER BY B.ACCT_NUM,B.DUE_DATE,B.ACTIVITY_DATE,CASEB.Time_ObtainedWHEN 0 THEN 0ELSE 1END Desc, B.Time_ObtainedOPEN ACT_CUR2FETCH NEXT FROM ACT_CUR2INTO @mAcct_Num2 ,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateWHILE (@@FETCH_STATUS = 0)BEGIN----CHECK------------------------------------------------------------------------DECLARE @PrintVariable2 VARCHAR (8000)--SELECT @PrintVariable2 = CAST(@MACCT_NUM2 AS VARCHAR)+''+CAST(@MACTIVITY_DATE2 AS VARCHAR)+' '+CAST(@MPROMISE_AMT_1 ASVARCHAR)+' '+CAST(@MPROMISE_STATUS AS VARCHAR)+''+CAST(@mCurrent_Due_Amt AS VARCHAR)+' '+CAST(@mDPD AS VARCHAR)+''+CAST(@mPromise_Date AS VARCHAR)--PRINT @PrintVariable2----ENDCHECK------------------------------------------------------------IF @mDPD >= 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE < @mActivity_Date2 + 15----CHECK------------------------------------------------------------------------DECLARE @PrintVariable3 VARCHAR (8000)--SELECT @PrintVariable3 ='Greater=30 DOLLARS COLLECTED'--PRINT @PrintVariable3----ENDCHECK------------------------------------------------------------ENDELSE IF @mDPD < 30BEGINSELECT @tot_pay = SUM(CONVERT(FLOAT, C.PAY_AMT))FROM vwAplicablePayments CWHERE C.ACCT_NUM = @mAcct_Num2ANDC.ACTIVITY_DATE >= @mActivity_Date2ANDC.ACTIVITY_DATE BETWEEN @mActivity_Date2 AND@mPromise_Date + 5----CHECK----------------------------------------------------------------------DECLARE @PrintVariable4 VARCHAR (8000)--SELECT @PrintVariable4 ='Less 30 DOLLARS COLLECTED'--PRINT @PrintVariable4----END CHECK------------------------------------------------------------END----------------------------------------MY REVISEDLOGIC-------------------------------------------------------IF @rec_upd = 'N'BEGINIF @mDPD >= 30BEGINSELECT @mActivityDate2_temp = @mActivity_Date2 + 15--DECLARE @PrintVariable5 VARCHAR (8000)--SELECT @PrintVariable5 =' GREATER= 30 USING ACTVITY_DATE+15'--PRINT @PrintVariable5ENDELSE IF @mDPD < 30BEGINSELECT @mActivityDate2_temp = @mPromise_Date + 5--DECLARE @PrintVariable6 VARCHAR (8000)--SELECT @PrintVariable6 =' LESS 30 USING PROMISE_DATE+5'--PRINT @PrintVariable6ENDIF @tot_pay >= 0.9 * @mCurrent_Due_Amt--used to be promise amtBEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET PROMISE_STATUS = 'PK',TOTAL_DOLLARS_COLL = @tot_payWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PK.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDSELECT @rec_upd = 'Y 'ENDIF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay IS NULL)AND( @mActivityDate2_temp > @todays_date )--need to put 1dayof month here for snapshot9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'OP'WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto OP which is the original Activity Date.--The record will hold this date until it goes into PK,PB,orIP.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @mActivity_Date2WHERE CURRENT OF ACT_CUR2ENDENDELSE IF ((@tot_pay < 0.9 * @mCurrent_Due_Amt) OR @tot_pay ISNULL)AND( @mActivityDate2_temp <= @todays_date )--need to put 1dayof month here for snapshot 9/01/2004BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'PB',TOTAL_DOLLARS_COLL = case when @tot_pay is nullthen 0 else @tot_pay endWHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto PB.IF @mPromise_Status IN ('PTP','OP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDENDELSE IF @rec_upd = 'Y'BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSETPROMISE_STATUS = 'IP',TOTAL_DOLLARS_COLL = 0WHERE CURRENT OF ACT_CUR2--This statement updates the time that the status was placedinto IP.IF @mPromise_Status NOT IN ('IP')BEGINUPDATE VWAPPLICABLEPROMISEACTIVITYRECORDSSET Status_Date = @todays_dateWHERE CURRENT OF ACT_CUR2ENDENDFETCH NEXT FROM ACT_CUR2 INTO @mAcct_Num2,@mActivity_Date2,@mPromise_Amt_1,@mPromise_Status ,@mCurrent_Due_Amt,@mDPD,@mPromise_DateENDCLOSE ACT_CUR2DEALLOCATE ACT_CUR2FETCH NEXT FROM ACT_CUR1 INTO @mAcct_Num1 , @mDueDate1ENDCLOSE ACT_CUR1DEALLOCATE ACT_CUR1SET NOCOUNT OFFENDGO
View Replies !
One Distributor Is Re-sending Transactions Another Stops Sending Altogether.
I'm running SQL Server 2000 SP2 transactional replication.Periodically replication fails due to primary key errors. Oninvestigation I find that the offending transaction is attempting toinsert a duplicate of a recently replicated row (identical dataincluding the rowtimestamp, which is automatically generated in thepublishing database). My conclusion is that the distributor issending the same transaction twice. There is usually a large block oftransactions that are duplicates. Replication has been removed andreinstalled with no change. Any suggestions on what might be causingthis problem?Another distributor/publisher replicating to the same subscriber ishaving the opposite problem. When replication is first set up,everything works fine. Then, a few hours or days later, I notice thatno new transactions have been replicated for some time. Thedistribution agent appears to be running normally with no errors andno transactions are queued in the distribution database. Thesubscription and publication are still active but new data stored tothe published articles that should be replicated are ignored. Againany suggestions on how to proceed would be greatly appreciated.We have many other systems replicating with no problems, some to thesame subbscriber and built from the same image. I plan to re-installSQL Server on both of these systems when operations allow, but I wouldreally like to find the root cause of this problem.ThanksWalt Smith
View Replies !
Calling A Stored Procedure From ADO.NET 2.0-VB 2005 Express: Working With SELECT Statements In The Stored Procedure-4 Errors?
Hi all, I have 2 sets of sql code in my SQL Server Management Stidio Express (SSMSE): (1) /////--spTopSixAnalytes.sql--/// USE ssmsExpressDB GO CREATE Procedure [dbo].[spTopSixAnalytes] AS SET ROWCOUNT 6 SELECT Labtests.Result AS TopSixAnalytes, LabTests.Unit, LabTests.AnalyteName FROM LabTests ORDER BY LabTests.Result DESC GO (2) /////--spTopSixAnalytesEXEC.sql--////////////// USE ssmsExpressDB GO EXEC spTopSixAnalytes GO I executed them and got the following results in SSMSE: TopSixAnalytes Unit AnalyteName 1 222.10 ug/Kg Acetone 2 220.30 ug/Kg Acetone 3 211.90 ug/Kg Acetone 4 140.30 ug/L Acetone 5 120.70 ug/L Acetone 6 90.70 ug/L Acetone ///////////////////////////////////////////////////////////////////////////////////////////// Now, I try to use this Stored Procedure in my ADO.NET-VB 2005 Express programming: //////////////////--spTopSixAnalytes.vb--/////////// Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim sqlConnection As SqlConnection = New SqlConnection("Data Source = .SQLEXPRESS; Integrated Security = SSPI; Initial Catalog = ssmsExpressDB;") Dim sqlDataAdapter As SqlDataAdapter = New SqlDataAdaptor("[spTopSixAnalytes]", sqlConnection) sqlDataAdapter.SelectCommand.Command.Type = CommandType.StoredProcedure 'Pass the name of the DataSet through the overloaded contructor 'of the DataSet class. Dim dataSet As DataSet ("ssmsExpressDB") sqlConnection.Open() sqlDataAdapter.Fill(DataSet) sqlConnection.Close() End Sub End Class /////////////////////////////////////////////////////////////////////////////////////////// I executed the above code and I got the following 4 errors: Error #1: Type 'SqlConnection' is not defined (in Form1.vb) Error #2: Type 'SqlDataAdapter' is not defined (in Form1.vb) Error #3: Array bounds cannot appear in type specifiers (in Form1.vb) Error #4: 'DataSet' is not a type and cannot be used as an expression (in Form1) Please help and advise. Thanks in advance, Scott Chang More Information for you to know: I have the "ssmsExpressDB" database in the Database Expolorer of VB 2005 Express. But I do not know how to get the SqlConnection and the SqlDataAdapter into the Form1. I do not know how to get the Fill Method implemented properly. I try to learn "Working with SELECT Statement in a Stored Procedure" for printing the 6 rows that are selected - they are not parameterized.
View Replies !
Cursor And Procedure
Hello: I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to segment with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it? Your help is highly appreciated. s
View Replies !
Cursor In Procedure
Hi I am trying to write one cursor which is been made from join of two tables. and then i want to update one table by using where current of cursor. Can i do that..? or should i create the view by joining the two tables and then try to update it. or should i create the temp table and put the records in that and then try to update that. Please guide me in this. Thanks, Harish ==== CREATE PROCEDURE SP_P4 AS declare c123 cursor for select COMPANY.COMPANY, COMPANY.COMPANYNAME, COMPANY.CUSTOMERNUMBER, COMPANY.FEDTAXID, COMPANY.DUNSNUMBER, COMPANY.S1099, COMPANY.DUNSSUFFIX, COMPANYADDRESS.ADDRESSSEQ, COMPANYADDRESS.COUNTRY, COMPANYADDRESS.ADDRESSTYPE, COMPANYADDRESS.ADDRESS1, COMPANYADDRESS.ADDRESS2, COMPANYADDRESS.ADDRESS3, COMPANYADDRESS.ADDRESS4, COMPANYADDRESS.CITY, COMPANYADDRESS.STATE, COMPANYADDRESS.ZIP from COMPANY,COMPANYADDRESS where company.company=COMPANYADDRESS.company order by company.company declare @company char declare @COMPANYNAME char declare @CUSTOMERNUMBER char declare @FEDTAXID char declare @DUNSNUMBER char declare @S1099 char declare @DUNSSUFFIX char declare @ADDRESSSEQ int declare @COUNTRY char declare @ADDRESSTYPE char declare @ADDRESS1 char declare @ADDRESS2 char declare @ADDRESS3 char declare @ADDRESS4 char declare @CITY char declare @STATE char declare @ZIP char open c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP while @@fetch_status=0 begin update companyaddress set ADDRESSSEQ=@ADDRESSSEQ where current of c123 update companyaddress set COUNTRY=@COUNTRY where current of c123 update companyaddress set ADDRESSTYPE=@ADDRESSTYPE where current of c123 update companyaddress set ADDRESS1=@ADDRESS1 where current of c123 update companyaddress set ADDRESS2=@ADDRESS2 where current of c123 update companyaddress set ADDRESS3=@ADDRESS3 where current of c123 update companyaddress set ADDRESS4=@ADDRESS4 where current of c123 update companyaddress set CITY=@CITY where current of c123 update companyaddress set STATE=@STATE where current of c123 update companyaddress set ZIP=@ZIP where current of c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP end close c123 deallocate c123 declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XAPIALTMKT'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..expt1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa' exec master..xp_cmdshell @cmdstring drop table expt1
View Replies !
Cursor In Procedure
Hi I AM UPDATING VIEW V1 IN THE PROGRAM. AND I WANT TO PULL THE UPDATED DATA IN THER FLAT FILE WHICH I HAVE MARKED 'P' IN THE CURSOR. PLEAS ETELL ME HOW TO GO FOR THAT. ================================ CREATE PROCEDURE SP_P5 AS declare c123 cursor for select * from v1 declare @company char(12) declare @COMPANYNAME char(60) declare @CUSTOMERNUMBER char(12) declare @FEDTAXID char(12) declare @DUNSNUMBER char(9) declare @S1099 char(3) declare @DUNSSUFFIX char(4) declare @ADDRESSSEQ int declare @COUNTRY char(30) declare @ADDRESSTYPE char(12) declare @ADDRESS1 char(40) declare @ADDRESS2 char(40) declare @ADDRESS3 char(40) declare @ADDRESS4 char(40) declare @CITY char(30) declare @STATE char(2) declare @ZIP char(11) declare @status char(1) declare @dateofaction datetime open c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP,@status,@dateofaction while @@fetch_status=0 begin update v1 set ADDRESSSEQ=@ADDRESSSEQ,COUNTRY=@COUNTRY,ADDRESSTYP E=@ADDRESSTYPE, ADDRESS1=@ADDRESS1,ADDRESS2=@ADDRESS2,ADDRESS3=@AD DRESS3,ADDRESS4=@ADDRESS4,CITY=@CITY, STATE=@STATE,ZIP=@ZIP,status='P' where current of c123 fetch next from c123 into @company,@COMPANYNAME,@CUSTOMERNUMBER,@FEDTAXID,@D UNSNUMBER,@S1099, @DUNSSUFFIX,@ADDRESSSEQ,@COUNTRY,@ADDRESSTYPE,@ADD RESS1,@ADDRESS2,@ADDRESS3,@ADDRESS4,@CITY, @STATE,@ZIP,@status,@dateofaction end close c123 deallocate c123 declare @v datetime declare @filename varchar(32) select @v=getdate() select @filename = 'XCUST'+CONVERT(varchar, @v, 112)+ SUBSTRING(CONVERT(varchar, @v, 108), 1, 2) + SUBSTRING(CONVERT(varchar, @v, 108), 4, 2)+ SUBSTRING(CONVERT(varchar, @v, 108), 7, 2) + '.TXT' declare @cmdstring varchar(255) Select @cmdstring = 'bcp conversion..V1 out d:est' + @filename + ' -c -t"|" -r -S -Usa -Psa' exec master..xp_cmdshell @cmdstring
View Replies !
How Can I Store A Stored Procedure Name For A Report In A Table And Link It To A Dataset As A Stored Procedure?
Hi! I have about 100 SSRS 2005 reports, each of which links to a stored procedure. Each stored procedure may have two, three or even four parameters, so they vary a bit. I now also have a table called ReportInfo that stores the displayable report names, the rdl file names and some additional information that displays on the header of each report. I'd like to be able to store the name of the stored procedure in that table as well and just tell the dataset to execute that stored procedure, but it isn't working the way I expected. There are two datasets with each report. The first dataset points to the ReportInfo table, where all the standard information about the report is located, including now the name of the stored procecdure to which the second dataset is supposed to link. I'm not able to point the name of the stored procedure to a field in another dataset. I can't say, for example =First(Fields!StoredProcedure.Value, "ds_ReportInfo") That gives an error I then tried setting the dataset type to Text, creating a ReportParameter called StoredProcedure (which was filled in from the first query) and then tried: Exec (@StoredProcedure) In a way, that kind of worked. I got an error message back telling me the stored procedure needed a startdate and endingdate, which are the two parameters for this parrticular stored procedure. I just don't want to have to code that into the text query. Anyway, it wasn't my intention to have to use a text-based data query. It's as much of a hassle to use the drop-down to pick stored procedure names as it is to create a long text string with two or three parameters. I just want to dynamically control the name of the stored procedure and have it act exactly as it does when I select a stored procedure from a drop down. That is, I want to be able to tell Reporting Services where to find the name of the Stored Procedure for the dataset and then see all the fields it would return and be prompted for the two, three or four parameters exactly the same way I am when I select a stored procedure from a dropdown. The reason I ask this is that we've changed the naming convention for the stored procedures for reports, and now I'm having to go back into every report and reselect the new stored procedure name. I'd really much rather have the names in a database (in case they decide to change them again) and then just have the report pull the stored procedure name from the table. But I'm not finding an easy way to do that. I wouldn't mind putting a little piece of code in each report to do this if necessary, but what I don't want to do is have every report be different. That's the problem with using the EXECUTE statement in a text-based query. Each query has to be different based on the number and content of the parameters, and I don't want that. I just want to tell Reporting Services where to find the name of the stored procedure for the dataset and then have it treated like any other stored procedure. Any suggestions? Is anyone else trying to do this? Thanks Karen
View Replies !
Why Need To Open CURSOR In The Procedure?
Hi,everyone. Why need to open CURSOR in the part in which a procedure is created? The following codes are listed in the Microsoft SQL Server 2005 BOOKS ONLINE. (1) USE AdventureWorks; GO IF OBJECT_ID ( 'dbo.currency_cursor', 'P' ) IS NOT NULL DROP PROCEDURE dbo.currency_cursor; GO CREATE PROCEDURE dbo.currency_cursor @currency_cursor CURSOR VARYING OUTPUT AS SET @currency_cursor = CURSOR FORWARD_ONLY STATIC FOR SELECT CurrencyCode, Name FROM Sales.Currency; OPEN @currency_cursor; GO (2) USE AdventureWorks; GO DECLARE @MyCursor CURSOR; EXEC dbo.currency_cursor @currency_cursor = @MyCursor OUTPUT; WHILE (@@FETCH_STATUS = 0) BEGIN; FETCH NEXT FROM @MyCursor; END; CLOSE @MyCursor; DEALLOCATE @MyCursor; GO Please help me. Thank you in advance!
View Replies !
Cursor And Procedure Questions
Hello:I want find that the ntext column data string have more than 2000 characters. I need to truncate those string to the segments with 200 character, then put those segments along with their table_name and column_name to another table. Maybe need to use cursor? If so, how to use it? Your help is highly appreciated. S
View Replies !
Procedure/static Cursor
can someone tell me how to use static cursor to read the rows applying it in procedure. i have input and output tables i will have to use input table to read data and then in procedure update/insert into output table ) insert values from input and dditioanl calulate charges. ok, there is my problem: An Internet service provider has three different subscription packages for its customers: Package A: For $15 per month with 50 hours of access provided. Additional hours are $2.00 per hour over 50 hours. Assume usage is recorded in one-hour increments, i.e., a 25-minute session is recorded as one hour. Package B: For $20 per month with 100 hours of access provided. Additional hours are $1.50 per hour over 100 hours. Package C: For $25 per month with 150 hours access is provided. Additional hours are $1.00 per hour over 150 hours Assume a 30-day billing cycle. 1) Create a table to hold customer input billing data. 2) Populate input table with follwing records: CustomerID Pkg Hours ---------- --- ------ 1000 A 49 1010 A 50 1020 a 90 1030 a 130 1090 B 40 1100 B 99 1110 b 100 1120 b 145 1140 C 45 1150 c 85 1160 c 149 1170 c 150 1180 c 200 3) Create a table to hold customer data used to generate the statement to be sent to the customer. It should include CustomerID, Package, HoursUsed, and Charges. Write an SQL script that reads customer billing data, calculates a customer’s monthly charges, and populates the customer statement table. Use Cursor to process records and Stored Procedures for ProcessBill and calcCharges. CREATE TABLE custinput( cust_id int NULL, pkg char(1) NULL, hrs smallint NULL ) CREATE TABLE custoutput( cust_id int NULL, pkg char(1) NULL, hrsused smallint NULL, charges money null ) insert into custinput values (1000,'A',49); insert into custinput values (1010,'A',50); insert into custinput values (1020,'a',90); insert into custinput values (1030,'a',130); insert into custinput values (1090,'B',40); insert into custinput values (1100,'B',99); insert into custinput values (1110,'b',100); insert into custinput values (1120,'b',145); insert into custinput values (1140,'C',45); insert into custinput values (1150,'c',85); insert into custinput values (1160,'c',149); insert into custinput values (1170,'c',150); insert into custinput values (1180,'c',200); then there is conditions: if upper (@pkg)= 'A' begin if @hrs<= 50 set @charges =15 else set @charges =15 + (@hrs-50)*2 end; else if upper(@pkg)= 'B' begin if @hrs <= 100 set @charges = 20 else set @charges = 20 + (@hrs - 100)*1.5 end; else if @hrs <=150 set @charges = 25 else set @charges =25+(@hrs-150) insert into custoutput values(@cust_id,@pkg,@hrs,@charges)
View Replies !
Calling A Stored Procedure Inside Another Stored Procedure (or &"nested Stored Procedures&")
Hi 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?
View Replies !
|
TRACKER
