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 Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
How Can I Assign Data Returned From A Stored Procedure Into The Return Table Of A Table Valued Function
Here is the scenario, I have 2 stored procedures, SP1 and SP2 SP1 has the following code: declare @tmp as varchar(300) set @tmp = 'SELECT * FROM OPENROWSET ( ''SQLOLEDB'', ''SERVER=.;Trusted_Connection=yes'', ''SET FMTONLY OFF EXEC ' + db_name() + '..StoredProcedure'' )' EXEC (@tmp) SP2 has the following code: SELECT * FROM SP1 (which won't work because SP1 is a stored procedure. A view, a table valued function, or a temporary table must be used for this) Views - can't use a view because they don't allow dynamic sql and the db_name() in the OPENROWSET function must be used. Temp Tables - can't use these because it would cause a large hit on system performance due to the frequency SP2 and others like it will be used. Functions - My last resort is to use a table valued function as shown: FUNCTION MyFunction ( ) RETURNS @retTable ( @Field1 int, @Field2 varchar(50) ) AS BEGIN -- the problem here is that I need to call SP1 and assign it's resulting data into the -- @retTable variable -- this statement is incorrect, but it's meaning is my goal INSERT @retTableSELECT *FROM SP1 RETURN END
View Replies !
View Related
How To Export Data From Stored Procedure Source?
Hi, I have a SSIS package which exports data from a view in SQL database into Excel file. It was created by Export Wizard in SQL 2005 Server. Now I would like to modify this package and change data source from view to stored procedure. What component from toolbox should I use? it should be placed in Control Flow or Data Flow? And how connetc it with my Excel Destination? thanks, Przemo
View Replies !
View Related
Calling A Stored Procedure On A Data Source (with Parameters)
This seems to be much more difficult than it was in DTS (or perhaps I just need to adjust to the new way of doing things). Eventually I found that I needed to use "SQL command from variable" and using two other variables as input parameters. The expresion for the command is "usp_ValveStatusForDay '" + @[User:ate] + "','" + @[User::Report] + "'" which typically evaluates as usp_ValveStatusForDay '18 Oct 07','Report_Name' This previews correctly and the resulting columns are available for mapping to a destination. So far so good. By the way, is this the best way to call a stored procedure with parameters? I have pasted the stored procedure at the end of this posting because I have come accross a puzzling problem. The query as shown below works correctlly but if I un-comment the delete statement, the preview still works and the columns are still avilable for mapping but I get the following errors when the package is executed. Error: 0xC02092B4 at Data Flow Task, OLE DB Source [1]: A rowset based on the SQL command was not returned by the OLE DB provider. Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "OLE DB Source" (1) failed the pre-execute phase and returned error code 0xC02092B4. I realise that I could execute the delete query in a separate SSIS package step but I am curious as to why there is a problem with the way I tried to do it. At one stage the stored procedure used a temp table and later I also experimented with a table variable. In both cases I got similar errors at execution time. In the case of the temp table there was another problem in that, while the preview worked, there were no columns available for mapping. Using a table variable seemed to overcome this problem but I still got the run time error. eventually I found a way to avoid using either a temp table or a table variable and the package then worked correctly, copying the data into the desitnation table. It seems to me that if there is any complexity at all to the stored procedure, these errors seem to occur. Can anyone enlighten me as to what the "rules of engagement" are in this regard? Is one solution to use a wrapper stored procedure that simply calls the more complex one? ALTER procedure [dbo].[usp_ValveStatusForDay] ( @dateTime DateTime, @reportName VarChar(100) ) AS BEGIN DECLARE @day VarChar(10) DECLARE @month VarChar(10) DECLARE @year VarChar(10) DECLARE @start VarChar(25) DECLARE @end VarChar(25) SET @day = Convert(Varchar(10),DatePart(day, @dateTime)) SET @month = Convert(VarChar(10), DatePart(month, @dateTime)) SET @year = Convert(VarChar(10), DatePart(year, @dateTime)) IF @month = '1' SET @month = 'Jan' IF @month = '2' SET @month = 'Feb' IF @month = '3' SET @month = 'Mar' IF @month = '4' SET @month = 'Apr' IF @month = '5' SET @month = 'May' IF @month = '6' SET @month = 'Jun' IF @month = '7' SET @month = 'Jul' IF @month = '8' SET @month = 'Aug' IF @month = '9' SET @month = 'Sep' IF @month = '10' SET @month = 'Oct' IF @month = '11' SET @month = 'Nov' IF @month = '12' SET @month = 'Dec' SET @start = @day + ' ' + @month + ' ' + @year + ' 00:00:00' SET @end = @day + ' ' + @month + ' ' + @year + ' 23:59:59' --delete from ValveStatus where SampleDateTime between dbo.ToBigInt(@start) and dbo.ToBigInt(@end) exec dbo.usp_ValveStats_ReportName @reportName, @start, @end, '1h' END
View Replies !
View Related
Visual Source Safe Data Cannot Be Accessed Through SQL CLR Stored Procedure
The goal is to address visual source safe database on the network. We have the srcsafe.ini in the network as \ipaddrsrcsafe.ini. Now I create a new VSSDatabase object and call its OpenDb. Well for simple consle app or winform it is ok. But I was running it under Sql server Stored Procedure. It failed for I cannot access the source safe path throgh the COM object. I know it is because of Windows identity. So I add the following code before I want to open the database, changing the to the WindowsIdentity: WindowsIdentity impersonId = SqlContext.WindowsIdentity; WindowsImpersonationContext orgCtx = null; try { orgCtx = impersonId.Impersonate(); VSS_Database = new MVSI.VSSDatabase(); // VSS_Database.ImpersonateCaller = true; VSS_Database.Open(Path, UserName, PassWord); } catch (Exception err) { orgCtx.Undo(); throw err; } finally { orgCtx.Undo(); } Without the commented line "// VSS_Database.ImpersonateCaller = true", this does not work at all. It just behave like no changes to the windows identity. However if I add this code, well, OpenDb will result in a No-response query. The Sql server is running the query with no responses. Have you ever met that before? I am really frustrated. Thanks
View Replies !
View Related
ODBC Connection With Stored Procedure On ISeries As Data Source
I have created a stored procedure on the iSeries that creates a cursor and opens it. I am trying to write my report to use the stored procedure. I cannot get the data source to work. How do I create my data source so that it uses the stored procedure? My SP has three parameters I am trying to pass from the report. The parms are created in the layout. Thank you
View Replies !
View Related
Please Help To Assign Multiple Results Into Individual Variables, Stored Procedure
I have a MSSQL2000 table called partspec.dbo.locationIn this table are 2 smallint columns: Tester (numbered 1-40) and Line (numbered with various integers)I am trying to create a stored procedure to read the tester number like so:Select Tester from partspec.dbo.location where Line = 2which will return up to 5 tester number results, lets say 11, 12, 24, 29 ,34My question is how do I store these tester numbers into 5 variables so that I may use them later in the sp ? So it would go something like this:CREATE PROCEDURE Table_Line (@Tester1 integer,@Tester2 integer,@Tester3 integer,@Tester4 integer,@Tester5 integer)ASSELECT Tester FROM partspec.dbo.location where Line = 2Now this is where I'm confused on how to get 1 value into 1 variable and so on for all 5 values returned. This is what I would like to happen: @Tester1 = 11@Tester2 = 12@Tester3 = 24@Tester4 = 29@Tester5 = 34GOThank you for any and all assistance.
View Replies !
View Related
How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?
Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo
View Replies !
View Related
Urgent. Output Columns Are Not Appearing When I Use OLEDB Data Source With An Oracle Stored Procedure In Dataflow Task
I am using execute sql task to run a stored procedure in oracle database which returns a resultset. This works. Now I need to send the ouput to a destination table in a sql database. Should I use for each loop to pick the resultset and insert it into the destination one by one (which I dont think is a great idea) or is there a better way to accomplish this task (in data flow task) ? When I use dataflow task instead of execute sql task, the main issue is I am not able to see the output columns when I execute an oracle stored procedure, but when I see the preview I can see the resultset . But I can see the output columns for a sql server stored procedure.
View Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 Replies !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
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 !
View Related
Getting Data From A Storeed Procedure In A Stored Procedure
What I am looking to do is use a complicated stored procedure to getdata for me while in another stored procedure.Its like a view, but a view you can't pass parameters to.In essence I would like a sproc that would be like thisCreate Procedure NewSprocASSelect * from MAIN_SPROC 'a','b',.....WHERE .........Or Delcare Table @TEMP@Temp = MAIN_SPROC 'a','b',.....Any ideas how I could return rows of data from a sproc into anothersproc and then run a WHERE clause on that data?ThanksChris Auer
View Replies !
View Related
Stored Procedure Source Control
Hi,I am trying to put SQL Server Stored Procedures into Sourcesafe as perthe Microsoft Knowledge Base article 818368, but have run into aproblem.The web server is SQL Server 2000 running on Windows 2003 Server. Theclient dev environment is Visual Studio 2003 Enterprise DeveloperEdition.I have carried out the following steps successfully:-1. Installed Sourcesafe client tools on the Server (sourcesafe is onanother server)2. Run the MSSQLServer service under a domain account that has Readand Write access to the Sourcesafe database.3. Added the above user to Sourcesafe using the Administrator tool.4. Installed the VS6 Stored Procedure Version Control components onthe Server5. Enabled Version Control for Stored Procedures on the clientHowever when I right-click on the Stored Proc node in Server explorerI do not get any of the Sourcesafe menu options. They are not greyedout, they are simply not there!Any help would be appreciated.Alternatively if anyone has recommendations for other strategies orother tools to use for this purpose than Sourcesafe then this wouldalso be welcomeKarl
View Replies !
View Related
Stored Procedure As OLEDB Source
Hi, at first... Yes, I have seen this post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=366077&SiteID=1 and yes, my Stored Procedure contains a "Set nocount on"... :-) My Stored Procedure contains variable SQL-Code to check a table (given by parameter) against some masterdata tables and I would like to write the result of the check to a flatfile to be able to send it via mail. but evoking the Stored procedure brings up no meta data for the Flatfile Destination - the preview works... any other hints? except for doing the ckeck completely in SSIS? :-P cheers Markus
View Replies !
View Related
Excel Source And Stored Procedure
Hello, I'm a SSIS beginner. I need to create users in my database from an Excel file source. I have an Excel source with user name and email. I would like to use SSIS to go though the Excel file and execute a store procedure (create_user @name @email @password OUTPUT) for each row and then create an output file with the new created password (a random password is created for each user and the stored procedure has an output @password) I tried to "plug" an "Excel source" to an "Execute SQL task" which execute my store procedure. But as a SSIS beginner I don't really understand how to pass the parameters (name and email in the Excel file) to my stored procedure. There is the concept of variable a for each loop thing... but i dont really know how to start. I would be greatfull if someone could tell me a solution to my problem. Cheers Fabrice
View Replies !
View Related
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 !
View Related
Using Stored Procedure As OLE DB Source With Parameters From Package Variables
Hi Guys, (I have searched this forum extensively, but still can't find the solution to this problem) Here it is: I have step in my ETL process that gets facts from another database. Here is how I set it up: 1) I have to package variables called User::startDate and User::endDate of data type datetime 2) Two separate Execute SQL Tasks populate those variables with appropriate dates (this works fine) 3) Then I have a Data Flow Task with OLE DB source that uses a call to a sproc of the form "exec ETL_GetMyData @startDate = ?, @endDate = ?" with parameters mapped accordingly (0 -> User::startDate, 1 -> User::endDate) When I run this I get an error 0xC0207014: "The SQL command requires a parameter named "@startDate", which is not found in the parameter mapping." It is true that the sproc in fact requires @startDate and @endDate parameters, so next thing I tried to do is call the sproc the following way: "exec ETL_GetMyData @startDate = ?, @endDate = ?" To no avail. It gives me the same error. Incidentally, when I hard code both dates like "exec ETL_GetMyData '2006-04-01', '2006-04-02'" everything works well. Also, I want to mention that in the first two cases, I get an error right in the editor. When I try to parse the statement it gives me "Invalid parameter number" message. This has been such a pain in my neck. I've waisted the whole day trying to monkey with the various parts of package/statements to get this to work and it still doesn't. I dont' want to say anything about Integration Services design right now, but you probably know what I'm thinking...
View Replies !
View Related
DataReader Source Freezes In Validation Of MySQL Stored Procedure
I have a package that hangs in the designer after I change the sql statement in a DataReader Source from a 'select' to a 'call stored procedure'. The stored procedure takes 2 date parameters. I use an expression to build the 'call stored proc' statement and the 2 date strings. The data reader source uses an ADO.Net connection manager. The ADO.Net connection manager uses the provider for MySQL (Connector/.Net 5.1) which I installed from MySQL.com (http://dev.mysql.com/downloads/connector/net/5.1.html). Before creating the stored procedure I had been using an expression to build a 'select' statement with two date variables as follows: select ... where ads.last_seen >= "" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "" and ads.first_seen <= "" + (DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] )+ "" group by sm.service_provider_id,lm.location_id,lm.web_sublocation_id;" The sql for the data reader source is set via the sql command property of the data flow component. After testing the sql, I created a stored proc from this sql and then changed the expression (using the sql command property of the the data flow component) to build the 'call stored proc' statement, like this. "call usp_SEL_Rollup ("" + (DT_STR,10,1252) Year(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Month(@[User:: StartDate] ) + "-" + (DT_STR,10,1252) Day(@[User:: StartDate] ) + "","" +(DT_STR,10,1252) Year(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Month(@[User::EndDate] ) + "-" + (DT_STR,10,1252) Day(@[User::EndDate] ) +"");" then when I tried to switch to the data flow tab, the editor froze, with the status bar saying "validating datareader source". The data flow tab says "Loading...". I don't know how to troubleshoot this. Each time I have tried I have had to kill the application. Any ideas/suggestions? Thanks, Al
View Replies !
View Related
How Do You Assign A Data Source To A Page And Which Type Sqldatasource, Objectdatasource Or ?
Hi Using ASP.NET 2.0, Sql Server 2005. I have a simple page (NOT a formview) with some entries textbox's , checkbox and dropdownlistbox's I want to link a datasource to the 'Item Page' and bind the datasource's values to the page The select statement is Select a.IssueID, a.ProjectID, a.VersionID, a.toincludeversionid, a.Version, a.toincludeversion, a.TypeofEntryID, a.PriorityID, a.WorkFlowID, a.Title, a.Area, a.Details, a.Question, a.Answer, a.HowToRepro, a.DevelopersNotes, a.TestersNotes, b.ProjectID, b.ProjectName, OldVersion.Version, ToIncludeVersion.Version, d.DESCRIPTION, e.DESCRIPTION, x.TaskID as TaskID, x.DESCRIPTION as TaskDescription, z.Taskdone, CONVERT (char(9),z.TaskAssignedDate, 3) AS Workflowdate, z.StaffID as StaffID, w.username, y.latest_workflowid from issue as a Inner join ProjS b on b.ProjectId=a.ProjectID Left Outer join Version OldVersion on a.VersionID=OldVersion.VersionID Left Outer join Version ToIncludeVersion on a.VersionID= ToIncludeVersion.VersionID Inner join TypeOfEntry d on d.TypeOfEntryID=a.TypeofEntryID Inner join Priority e on e.PriorityID=a.PriorityID inner join workflow z on z.issueid=a.issueid Inner join (select issueid,max(workflowid) as latest_workflowid from workflow group by issueid) y on y.latest_workflowid=z.workflowid Inner join task x on x.taskid=z.taskid Inner join staffls w on w.StaffID=z.StaffID Where a.IssueID= @IssueID I hope I have made query clear, if not I don't mind explaining more. Thanks in advance
View Replies !
View Related
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 !
View Related
Fail To Execute Store Procedure In OLD DB Source In Data Flow
Hi. I am trying to extract the data returned from a store procedure to a flat file. However, it fail to execute this package in the OLE DB Source. I select the SQL Command in the Data Access Mode, then use: USE [SecurityMaster] EXEC [dbo].[smf_ListEquity] It runs ok in the Preview, but not in the Run. Then the system returns during executing the package: Error: 0xC02092B4 at Load TickerList, OLE DB Source [510]: A rowset based on the SQL command was not returned by the OLE DB provider. Error: 0xC004701A at Load TickerList, DTS.Pipeline: component "OLE DB Source" (510) failed the pre-execute phase and returned error code 0xC02092B4. Please give me some helps. Thanks.
View Replies !
View Related
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 !
View Related
How To Dynamically Assign Database Name In Query Or Store Procedure?
Hello, I am not sure if this possible, but I have store procedures that access to multiple databases, therefore I currently have to hardcode my database name in the queries. The problem start when I move my store procedures into the production, and the database name in production is different. I have to go through all my store procedures and rename the DBname. I am just wonder if there is way that I could define my database name as a global variable and then use that variable as my DB name instead of hardcode them? something like Declare @MyDatabaseName varchar(30) set @MyDatabaseName = "MyDB" SELECT * from MyDatabaseName.dbo.MyTable Any suggestion? Please. Thanks in advance
View Replies !
View Related
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 !
View Related
|
TRACKER
