Stored Procedure Using A Declared Cursor

Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker
I get the following error can you please explain this problem to me so I can over come the issue.
 

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32

Could not complete cursor operation because the set options have changed since the cursor was declared.

Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153

Cursor is not open.
 
here is the stored procedure:
 

Alter PROCEDURE [dbo].[sp_MSforeachsp]

@command1 nvarchar(2000)

, @replacechar nchar(1) = N'?'

, @command2 nvarchar(2000) = null

, @command3 nvarchar(2000) = null

, @whereand nvarchar(2000) = null

, @precommand nvarchar(2000) = null

, @postcommand nvarchar(2000) = null

AS

/* This procedure belongs in the "master" database so it is acessible to all databases */

/* This proc returns one or more rows for each stored procedure */

/* @precommand and @postcommand may be used to force a single result set via a temp table. */

declare @retval int

if (@precommand is not null) EXECUTE(@precommand)

/* Create the select */

EXECUTE(N'declare hCForEachTable cursor global for

SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = ''PROCEDURE''

AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 '

+ @whereand)

select @retval = @@error

if (@retval = 0)

EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0

if (@retval = 0 and @postcommand is not null)

EXECUTE(@postcommand)

RETURN @retval

 

GO

 
example useage:
 

EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]"

GO

View Replies !

---

Stored Procedure Using A Declared Variable In Insert Query (inline Or Using EXEC)
Hello,

I have a stored procedure where I run an insert statement.  I want to knwo if it is possible to do it using a variable for the table name (either in-line or with an EXEC statement without building a string first and executing that string.  See examples of what I am talking about in both cases below:

I want to be able to do this (with or without the EXEC) :
------------------------------------------------------------------------------------

DECLARE @NewTableNameOut  as varchar(100)


Set @NewTableNameOut  = 'TableToInsertInto'


EXEC(
                Insert Into @NewTableNameOut
                Select * From tableToSelectFrom
            )

------------------------------------------------------------------------------------

I can not do the above because it says I need to declare/set the @NewTableNameOut variable (assuming it is only looking at this for the specific insert statement and not at the variable I set earlier in the stored procedure.


I can do it like this by creating a string with the variable built into the string and then executing the string but I want to know if I can do it like I have listed above.

------------------------------------------------------------------------------------

DECLARE @NewTableNameOut  as varchar(100)


Set @NewTableNameOut  = 'TableToInsertInto'


EXEC(
                'Insert Into ' + @NewTableNameOut + ' ' +
                'Select * From tableToSelectFrom'
            )

------------------------------------------------------------------------------------



It is not an issue for my simple example above but I have some rather large queries that I am building and I want to run as described above without having to build it into a string. 

Is this possible at all?

If you need more info please let me know.

View Replies !

---

Set Value For Variable In A Declared Cursor
Hi,

I have a problem on setting the value for the variable in a declared cursor. Below is my example, I have declared the cursor c1 once at the top in a stored procedure and open it many times in a loop by setting the variable @str_var to different values. It seems the variable cannot be set after the cursor declared. Please advise how can I solve this issue.

------------------------------------------------------------------------
DECLARE @str_var VARCHAR(10)
DECLARE @field_val VARCHAR(10)

DECLARE c1 CURSOR LOCAL FOR
SELECT field1 FROM tableA WHERE field1 = @str_var


WHILE (Sometime TRUE)
BEGIN

....

SET @str_var = 'set to some values, eg. ABC123, XYZ123'

OPEN c1

FETCH c1 INTO @field_val

WHILE (@@fetch_status != -1)
BEGIN

PRINT @field_val
...

FETCH c1 INTO @field_val
END

CLOSE c1

END

DEALLOCATE c1

----------------------------------------------------------------------

Thanks a lots,
Vincent

View Replies !

---

Cursor Declared With Variable In Where Clause
When I execute next query on sqlserver 6.5 nested in stored procedure I can see that 'open testCursor' selected rows using new value of @var. When I execute query on sqlserver 7.0 I can see that 'open testCursor' selected rows using value of @var before 'declare ... cursor'. Is there any way to force sqlserver 7.0 to proccess cursor like it did it before.

select @var = oldValue

declare testCursor cursor
for select someColumns
from someTable
where someColumn = @var

select @var = newValue

open testCursor

fetch next from testCursor into @someColumns

Thank's in advance.

Mirko.

View Replies !

---

Error: Table Schema Changed After The Cursor Is Declared
Hi,

I have a package which loads data from one sql server table to another. I am loading 15million records in that. Earlier I tested that package with smaller data (less than a million) and it worked fine. So, I put it in production to load that 15 million records. But strangely after loading over 1.5million records, the job aborted with error at destination. The log says 'the table schema has changed after the cursor is declared'. But there is no change made in both destination as well as source.

In my package I am using a 'OLEDB Source' to read data from a SQL Server table, using 'Script component' making some changes and loading data to a sql server table using 'OLE DB Destination'. Both source and destination are in same server, but under different schema.

Do you have any idea about the problem?

Thanks.

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 !

---

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 !

---

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 !

---

Unique Cursor Name In Recursive Stored Procedure
How can I solve this probleM?

procedure XX
DECLARE cc CURSOR FOR SELECT....
OPEN cc
...
WHILE @@FETCH_STATUS = 0
BEGIN

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 !

---

Is It Possible To Use Twice Declared. Variable Names- KILL And After Declared. Variable
is it possible to use twice declared. Variable names-
declared. Variable  and after KILL
and use the same declared. Variable 
like

DECLARE

@StartDate datetime
 
KILL @StartDate datetime (remove from memory)
use after with the same name

i have 2  big stored PROCEDURE
i need to put one after one
and psss only 1 Variable name to the second stored PROCEDURE
like this i don't get this error
 

The variable name '@Start_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.

Msg 134, Level 15, State 1, Line 146

The variable name '@End_Date' has already been declared. Variable names must be unique within a query batch or stored procedure.
i use like
KILL @endDate  ??
KILL @StartDate   ??

 
TNX

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 !

---

Connect To Oracle Stored Procedure From SQL Server Stored Procedure...and Vice Versa.
I have a requirement to execute an Oracle procedure from within an SQL Server procedure and vice versa.

How do I do that?  Articles, code samples, etc???

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 !

---

Grab IDENTITY From Called Stored Procedure For Use In Second Stored Procedure In ASP.NET Page
I have a sub that passes values from my form to my stored procedure.  The stored procedure passes back an @@IDENTITY but I'm not sure how to grab that in my asp page and then pass that to my next called procedure from my aspx page.  Here's where I'm stuck:    Public Sub InsertOrder()        Conn.Open()        cmd = New SqlCommand("Add_NewOrder", Conn)        cmd.CommandType = CommandType.StoredProcedure        ' pass customer info to stored proc        cmd.Parameters.Add("@FirstName", txtFName.Text)        cmd.Parameters.Add("@LastName", txtLName.Text)        cmd.Parameters.Add("@AddressLine1", txtStreet.Text)        cmd.Parameters.Add("@CityID", dropdown_city.SelectedValue)        cmd.Parameters.Add("@Zip", intZip.Text)        cmd.Parameters.Add("@EmailPrefix", txtEmailPre.Text)        cmd.Parameters.Add("@EmailSuffix", txtEmailSuf.Text)        cmd.Parameters.Add("@PhoneAreaCode", txtPhoneArea.Text)        cmd.Parameters.Add("@PhonePrefix", txtPhonePre.Text)        cmd.Parameters.Add("@PhoneSuffix", txtPhoneSuf.Text)        ' pass order info to stored proc        cmd.Parameters.Add("@NumberOfPeopleID", dropdown_people.SelectedValue)        cmd.Parameters.Add("@BeanOptionID", dropdown_beans.SelectedValue)        cmd.Parameters.Add("@TortillaOptionID", dropdown_tortilla.SelectedValue)        'Session.Add("FirstName", txtFName.Text)        cmd.ExecuteNonQuery()        cmd = New SqlCommand("Add_EntreeItems", Conn)        cmd.CommandType = CommandType.StoredProcedure        cmd.Parameters.Add("@CateringOrderID", get identity from previous stored proc)   <-------------------------        Dim li As ListItem        Dim p As SqlParameter = cmd.Parameters.Add("@EntreeID", Data.SqlDbType.VarChar)        For Each li In chbxl_entrees.Items            If li.Selected Then                p.Value = li.Value                cmd.ExecuteNonQuery()            End If        Next        Conn.Close()I want to somehow grab the @CateringOrderID that was created as an end product of my first called stored procedure (Add_NewOrder)  and pass that to my second stored procedure (Add_EntreeItems)

View Replies !

---

How Assign Value To Cursor Using Sp_executesql Procedure
What is error here when i declare cursor ?

declare curQueryVehicleHave cursor for
exec sp_executesql @strQueryVehicleHave



@strQueryVehicleHave this string contain a query

View Replies !

---

Can I Fill A Cursor From A Strored Procedure?
Basically, I have a complex stored procedure that combines two tables and fills a cursor.

I would like to fill another cursor in another stored procedure from the results of this first stored proc, rather than have to type it all in again.

The reason being that I am doing a one time import of some data from two tables into one new table based on some complex linking/querying.

Can I fill a cursor from the output of another stored procedure rather than an inline SELECT statement?

Does the sp I am using have to have cursor as an out parameter?

View Replies !

---

How To Call A Procedure While Declaring A Cursor
HI,
WHILE DECLARING A CURSOR TO SELECT RECORDS FROM A TABLE WE NORMALLY WRITE :-

DECLARE CUR_NAME CURSOR
FOR SELECT * FROM CLEANCUSTOMER

BUT SAY, IF I HAVE WRITTEN A SIMPLE PROCEDURE CALLED AS MY_PROC :-

CREATE PROCEDURE MY_PROC
AS
SELECT A.INTCUSTOMERID,A.CHREMAIL,B.INTPREFERENCEID,C.CHR PREFERENCEDESC
FROM CLEANCUSTOMER A
INNER JOIN TRCUSTOMERPREFERENCE03JULY B
ON A.INTCUSTOMERID = B.INTCUSTOMERID
INNER JOIN TMPREFERENCE C
ON B.INTPREFERENCEID = C.INTPREFERENCEID
ORDER BY B.INTPREFERENCEID

WHICH IS RUNNING FINE AND GIVING ME THE REQUIRED DATA WHILE EXECUTING THE PROCEDURE :-

EXEC MY_PROC

BUT IF I WANT TO CALL THIS PROCEDURE MY_PROC WHILE DECLARING A CURSOR :-

I AM USING :-

DECLARE CHK_CUR CURSOR
FOR SELECT * FROM MY_PROC

WHICH IS GIVING AN ERROR "Invalid object name 'MY_PROC'."


AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR EXEC MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near the keyword 'EXEC'".


AND IF I USE :-

DECLARE CHK_CUR CURSOR
FOR CALL MY_PROC

WHICH IS GIVING AN ERROR "Incorrect syntax near 'CALL'. "

IS THERE ANY WAY BY WHICH I CAN FETCH RECORDS FROM THE STORED PROCEDURE?
HOW DO I DECLARE THE PROCEDURE WHILE WRITING THE CURSOR
PLS HELP.

I NEED THIS URGENTLY, I HAVE TO USE THE CURSOR TO FETCH THE RECORDS FROM THE SP,THAT'S HOW THEY WANT IT.I CAN'T HELP IT AND I DON'T KNOW HOW

THANKS

View Replies !

---

Cursor - Query In Procedure To Get Maxdate
i m new in sql...and i have this procedure..which have cursor inside..

1. i want to get all distinct date into #tempt table.
2. In the loop for each distinct date fetch all the date into another #temp
3. get max date from that #temp and use that date to get the data from original table

but i m getting 0 rows else all 8000 rows..which is wrong..can anyone help me plz...


create procedure procdate1
(@name varchar(50))
as
begin
SET NOCOUNT ON

DECLARE @MaxDate datetime
DECLARE @Date datetime


select id, title, dated,
CONVERT(CHAR(10), dated,101) as date,
CONVERT(CHAR(8), dated,114) as time from general

where name = @name AND dated = @MaxDate


DECLARE CUR1 CURSOR FOR

SELECT @DATE FROM #tempt

OPEN CUR1

FETCH NEXT FROM CUR1 INTO @Date

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT dated INTO #Date1 FROM general WHERE CONVERT(CHAR(10),dated,101) = @Date
SELECT @MaxDate = MAX(dated) FROM #date1


--DROP TABLE #Date1

FETCH NEXT FROM CUR1 INTO @Date
continue
CLOSE CUR1
DEALLOCATE CUR1
end
-- DROP TABLE #tempt
end

View Replies !

---

Putting Data From A Store Procedure Into A Cursor
The tile says it all... How to put into a Cursor the result of a procedure that selects certain rows.

View Replies !

---

Procedure/Cursor Question About Returning Results
I'm working on a procedure that needs to cycle through the records of some raw data and combine the the current record with the datetime field of the prior record.  I have been able to write a script to do that with cursors and variables but my problem is it returns each record separately.  How do I go about getting the procedure to return all the records as one set of data?

To see what I mean, the following script for the Pubs DB returns each pass through the data as a seperate query.  Since I can't do a select *, what approach should I take?  If you want the actual script, I would be happy to provide it.

DECLARE authors_cursor CURSOR
   FOR SELECT * FROM authors
OPEN authors_cursor
FETCH NEXT FROM authors_cursor

WHILE @@FETCH_STATUS = 0
begin
FETCH NEXT FROM authors_cursor
end

Close Authors_cursor
deallocate authors_cursor

 Thanks in advance

Tony Murunion

View Replies !

---

Get A Cursor From A Stored Proc.
Hello.
I'm having a perfectly(!) normal stored procedure that returns a Resultset with one row (containing an ID I want).
Not I need that ID in another stored procedure and I can't get it out from the stored procedure.

exec @blabla = MyProc -- works well if I use return
exec MyProc @blabla -- works using OUTPUT keyword

But neither of these examples works with a CURSOR as the @blabla.
Do I need to specificly pass a cursor as a return value, wich would give me bellyache, or can I do something like this:

DECLARE @MyCursor CURSOR
SET @MyCursor = CURSOR FOR exec MyProc

Thanks for any help!
Daniel Ronnqvist, Stockholm

View Replies !

---

Help With Stored Proc + Cursor
Help..Help
I have a very li'l experience with Stored procs and I wrote a moderately SSP....Okay here is the deal:

Tables Accessed:
1.Contract (Fields in it are: Contract ID, ContractPrefix,CustomerID)
2.Rate Schedule(Fields: Rateschedulecode)
3.Daily Detail(Fields: ContractPrefix, Contract ID, AccountingDate, FlowDate,AllocatedNetQty,DailyDetailStatus,NominatedFlowDirection)
The Stored Proc I wrote is some'in like this:


DECLARE
@errno int,
@errmsg varchar(255),
@ForeverDate smalldatetime

SET @ForeverDate = '6/6/2079'

Select
cntr.RateScheduleCode,cntr.ContractPrefix,cntr.ContractID,cntr.EffectiveDate, cntr.DeactivateDate, DlyDtl.FlowDate from Contract cntr

INNER Join RateSchedule RtSch
on cntr.BusinessunitID= RtSch.BusinessunitID
and cntr.rateschedulecode=RtSch.rateschedulecode
and @ActiveDate BETWEEN RtSch.BeginDate and COALESCE(DATEADD(day, -1, RtSch.EndDate), @ForeverDate)

INNER Join Dailydetail DlyDtl
on cntr.BusinessunitID= DlyDtl.BusinessunitID
and cntr.CustomerID=DlyDtl.CustomerID
and cntr.ContractPrefix =DlyDtl.ContractPrefix
and cntr.ContractID=DlyDtl.ContractID

where cntr.BusinessUnitId=@BusinessUnitId
and cntr.CustomerID=@CustomerID
and @ActiveDate BETWEEN cntr.EffectiveDate and COALESCE(DATEADD(day, -1, cntr.DeactivateDate), @ForeverDate)
and RtSch.ContractPrefix in ('SA','TA','BA','PA')
and (Month(DlyDtl.accountingdate) =Month(@ActiveDate) and Year(DlyDtl.accountingdate) =Year(@ActiveDate))
and (DATEPART(m,DlyDtl.flowdate)<DATEPART(m,@ActiveDate) and DATEPART(y,DlyDtl.flowdate)<DATEPART(y,@ActiveDate))

order by DlyDtl.flowdate

IF @@ERROR <> 0
BEGIN
SET @errno = 58400
SET @errmsg = 'Select/Retrieval Problem in Stored Procedure: rpt_CSI005_GetContractandAdjustmentsbyContract_ssp'
raiserror @errno @errmsg
RETURN @errno
END
ELSE
RETURN 0
END
GO


My requirement is:

1> Add the AllocatedNetQty from a DailyDetail record with DlyDtl.DailyDetailStatus="Reverse" to the AllocatedNetQty of a DailyDetail record with DlyDtl.DailyDetailStatus="Restate" and the result will be stored as Total Adjustments and for the first time this will be initialized to 0
2>If DlyDtl.NominatedFlowDirection="Receipt" then subtract the above calculated qty from the Total Adjustments
3>Else if DlyDtl.NominatedFlowDirection="Delivery" then Add the above calculated qty to the Total Adjustments

Now this far I know that I need to write a cursor to the above stored proc to fulfil the requirement but that is what i am craving for....a li'l help from ya guys
Thx a bunch
BJ

View Replies !

---