How To Declare Cursor In Stored Procedure?

DECLARE CURSOR
Is there any way to create a cursor, based on a dynamically created select_statement? Something like:
DECLARE someCRS CURSOR LOCAL FAST_FORWARD FOR @strSelect
where @strSelect is previously declared as let's say varchar.
I don't want to create a stored procedure for this.


Thanks!

View Replies !

---

Help With Declare And Cursor
 

I keep getting the message

Msg 156, Level 15, State 1, Line 3

Incorrect syntax near the keyword 'declare'.

Msg 156, Level 15, State 1, Line 4

Incorrect syntax near the keyword 'declare'.

 

What am I doing wrong?

 

 

declare @dbname varchar(8000),

declare @countyname varchar (200) ,

declare @sql varchar(8000)

 

declare county_name cursor for

select distinct county from Zipcodes

open county_name

fetch next from county_name

into @countyname

 

declare dbname_name cursor for

select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'

open dbname_name

fetch next from dbname_name

into @dbname

 

 

WHILE @@FETCH_STATUS = 0

BEGIN

set @sql =

'

select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'+@countyname+'

from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'

exec (@sql)

end

set @sql = ''

fetch next from dbname_name into @dbname

fetch next from county_name into @countyname

 

 

CLOSE county_name

DEALLOCATE county_name

CLOSE dbname_name

DEALLOCATE dbname_name

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 !

---

Declare Or Create Cursor
Hello guys,just wanted to ask a question some might percieve it as a stupid one but I don't know so I will ask anyway?

Is Declare Cursor same as Create Cursor and if not what is the major difference?

View Replies !

---

Declare Cursor With Dynamic SQL?
Hello..

Can you declare a cursor with dynamic SQL?

I have a situation where the SQL for my cursor MUST be assembled in a buffer, but I cannot get the cursor declaration to accept my buffer as the SQL statement.

these attempts did not work:

DECLARE crsCursor CURSOR FOR @vchrSQL
DECLARE crsCursor CURSOR FOR (@vchrSQL)

Does anybody know if you definitely can or definitely cannot use dynamic SQL with cursors?

View Replies !

---

Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi

View Replies !

---

Parameter In Declare Cursor Statement
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'

Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'

but I can't seem to come up with the right statement.

Any help greatly appreciated.

Thanks,
Judith

View Replies !

---

Declare Cursor For Execute Stored_procedure
Hello,

I am using SQL 2005 and i would like to create a cursor from executing a stored procedure (dbo.SP_getDate @Variable).
Something like this:

DECLARE Cursor1 CURSOR FOR EXECUTE dbo.SP_getDate @Variable

i get an error saying "incorrect syntax near the keyword 'EXECUTE'."
cannot get rid of the error. what am i doing wrong?
(i am trying to avoid using #tempTbl to store the results of the execute first and then doing a select on the #tempTbl)

Not sure if i am doing this right all together.
any help would be greatly appreciate.

View Replies !

---

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC &&amp; Sp?
Hi all,
 
In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--

USE shcDB

GO

CREATE PROC sp_insertNewRecord @procPersonID int,

                                                       @procFirstName nvarchar(20),

                                                       @procLastName nvarchar(20),

                                                       @procAddress nvarchar(50),

                                                       @procCity nvarchar(20),

                                                       @procState nvarchar(20),

                                                       @procZipCode nvarchar(20),

                                                       @procEmail nvarchar(50)

AS INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,

@procCity, @procState, @procZipCode, @procEmail)

GO

EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',

'Michigon', '67899', 'PeterWang@yahoo.com'

GO

=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure
 

EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'

 
Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

End Class

===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared  (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'  "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.

Please help and advise me how to correct these problems.
 
Thanks in advance,
Scott Chang

View Replies !

---

Declare Cursor Based On Dynamic Query
Hi,

I am declaring the cursor based on a query which is generated dynamically. but it is not working

 

Declare @tempSQL varchar(1000)

--- This query will be generated based on my other conditon and will be stored in a variable

set @tempsql = 'select * from orders'

declare cursor test for @tempsql

open test

 

This code is not working.

 

please suggest

 

Nitin

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 !

---

Order By Clause In DECLARE CURSOR Select Statement Won't Compile
The stored procedure, below, results in this error when I try to compile...


Msg 156, Level 15, State 1, Procedure InsertImportedReportData, Line 69
Incorrect syntax near the keyword 'ORDER'.

However the select statement itself runs perfectly well as a query, no errors.

The T-SQL manual says you can't use the keywords COMPUTE, COMPUTE BY, FOR BROWSE, and INTO in a cursor select statement, but nothing about plain old ORDER BYs.

What gives with this?

Thanks in advance
R.

The code:




Code Snippet

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF object_id('InsertImportedReportData ') IS NOT NULL
    DROP PROCEDURE InsertImportedReportData
GO
-- =============================================
-- Author:        -----
-- Create date:
-- Description:    inserts imported records, marking as duplicates if possible
-- =============================================
CREATE PROCEDURE InsertImportedReportData
    -- Add the parameters for the stored procedure here
    @importedReportID int,
    @authCode varchar(12)
AS
BEGIN
    DECLARE @errmsg VARCHAR(80);

--    SET NOCOUNT ON added to prevent extra result sets from
--     interfering with SELECT statements.
    SET NOCOUNT ON;

    --IF (@authCode <> 'TX-TEC')
    --BEGIN
     --   SET @errmsg = 'Unsupported reporting format:' + @authCode
      --  RAISERROR(@errmsg, 11, 1);
    --END

    DECLARE srcRecsCursor CURSOR LOCAL
    FOR    (SELECT
           ImportedRecordID
          ,ImportedReportID
          ,AuthorityCode
          ,[ID]
          ,[Field1] AS RecordType
          ,[Field2] AS FormType
          ,[Field3] AS ItemID
          ,[Field4] AS EntityCode
          ,[Field5] AS LastName
          ,[Field6] AS FirstMiddleNames
          ,[Field7] AS Title
          ,[Field8] AS Suffix
          ,[Field9] AS AddressLine1
          ,[Field10] AS AddressLine2
          ,[Field11] AS City
          ,[Field12] AS [State]
          ,[Field13] AS ZipFull
          ,[Field14] AS OutOfStatePAC
          ,[Field15] AS FecID
          ,[Field16] AS Date
          ,[Field17] AS Amount
          ,[Field18] AS [Description]
          ,[Field19] AS Employer
          ,[Field20] AS Occupation
          ,[Field21] AS AttorneyJob
          ,[Field22] AS SpouseEmployer
          ,[Field23] As ChildParentEmployer1
          ,[Field24] AS ChildParentEmployer2
          ,[Field25] AS InKindTravel
          ,[Field26] AS TravellerLastName
          ,[Field27] AS TravellerFirstMiddleNames
          ,[Field28] AS TravellerTitle
          ,[Field29] AS TravellerSuffix
          ,[Field30] AS TravelMode
          ,[Field31] As DptCity
          ,[Field32] AS DptDate
          ,[Field33] AS ArvCity
          ,[Field34] AS ArvDate
          ,[Field35] AS TravelPurpose
          ,[Field36] AS TravelRecordBackReference
      FROM ImportedNativeRecords
      WHERE ImportedReportID IS NOT NULL
      AND ReportType IN ('RCPT','PLDG')
     ORDER BY ImportedRecordID  -- this should work but gives syntax error!
    );

END

View Replies !

---

Stored Procedure - Must Declare The Scalar Variable &"@var1&".
Hi all, when i run this query i get the following error "Must declare the scalar variable "@var1". What am i doing wrong?


Code:


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[admin3c]
-- Add the parameters for the stored procedure here
@tableName varchar(100),
@authorityId varchar(50)

AS
BEGIN
SET NOCOUNT ON;

Declare @SQL VarChar(2000),
@var1 varchar(50),
@var2 int,
@total int

SELECT @SQL = '
select@var1 = name,
@var2 = sum(age),
@total = count(*)
from dbo.'+@tableName+'
where authority='+@authorityId+'
group by name'
Exec ( @SQL)

select @total - @var2

END

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 !

---

Stored Procedure Using A Declared Cursor
I need to write a stored procedure using T-SQL to declare a cursor for containing id(staff_no), names and specialism of all doctors that have specialism, The contents of the cursor then are to be displayed using a loop and print statement to give a formatted display of the output of each record within the cursor.

The doctors table has the following columns with specialism allowing NULL values

doctor
(
staff_no CHAR(3),
doctor_name CHAR(12),
position CHAR(15),
specialism CHAR(15),
PRIMARY KEY(staff_no)
)

Any help would be greatly appreciated.

View Replies !

---

Sql Stored Procedure - With In Cursor Get @@identity Value For Insert And Use That Again
I have stored procedure which contains follwing part of it. it says syntax when i worte line to get @@identity valuewhen delete that  line command succesful. but i need to get @@identity from the insert statement and assign it to a variable and use it after
any body pls tell me how to get this within a stored prosedure or what is the error of the following code bit.  (#tblSalesOrders is a temporary table which containsset of  records from orginal table )DECLARE @soNo1 INT
 DECLARE @CursorOrders CURSOR
SET @CursorOrders = CURSOR FAST_FORWARD FOR
select fldSoNo from #tblSalesOrders
declare @newSONO1 int OPEN @CursorOrders
FETCH NEXT FROM @CursorOrders INTO @soNo1
WHILE @@FETCH_STATUS = 0
BEGIN
----for each salesorder insert to salesorderline
insert into tblSalesOrders (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) select (fldWebAccountNo,fldDescription,fldSoDate,fldGenStatus) from #tblSalesOrders where fldSoNo =@soNo1;
 
 set @newSONO1=SCOPE_IDENTITY;
-------in this section again create another cursor for another set of records and insert to a table passing identity value return from the above insert --------------------------
SELECT @intErrorCode = @@ERRORIF (@intErrorCode <> 0) GOTO PROBLEM
FETCH NEXT FROM @CursorOrders INTO @soNo1
END CLOSE @CursorOrders
DEALLOCATE @CursorOrders

View Replies !

---

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 !

---

SQL 2000 Partitioned View Works Fine, But CURSOR With FOR UPDATE Fails To Declare
This one has me stumped.

I created an updateable partioned view of a very large table.  Now I get an error when I attempt to declare a CURSOR that SELECTs from the view, and a FOR UPDATE argument is in the declaration.

There error generated is:

Server: Msg 16957, Level 16, State 4, Line 3

FOR UPDATE cannot be specified on a READ ONLY cursor

 

Here is the cursor declaration:

 

declare some_cursor CURSOR

for

select    *

from       part_view

FOR UPDATE

 

Any ideas, guys?  Thanks in advance for knocking your head against this one.

PS: Since I tested the updateability of the view there are no issues with primary keys, uniqueness, or indexes missing.  Also, unfortunately, the dreaded cursor is requried, so set based alternatives are not an option - it's from within Peoplesoft.

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 !

---

Creating CSV Files Using BCP &&amp; Stored Procedures:BCP Executed From T-SQL Using Xp_cmdshell-How To Declare The Scalar Variable @@
Hi all,
 
I tried to create a CSV file using Bulk Copy Program (BCP) and Stored Procedures: BCP executed from T-SQL using xp_cmdshell.  I have the following sql code executed in my SQL Server Management Studio Express and error message:

 
--scBCPcLabResults.sql--
 
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
                c:cpChDbLabResults.txt -c -t, -T -S' + @@.SQLEXPRESS
exec master..xp_cmdshell @sql
 
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@".
 
=========================================================================================
--scBCPcLabResults.sql--
 
declare @sql varchar(8000)
select @sql = 'bcp ChDbLabResults out
                c:cpChDbLabResults.txt -c -t, -T -S' + @@SQLEXPRESS
exec master..xp_cmdshell @sql
 
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@@SQLEXPRESS".
 
===================================================================
 
I copied this set of code from a tutorial article that says "@@servername". My Sql Server is SQLEXPRESS, so I put @@.SQLEXPRESS or @@SQLEXPRESS in the code of scBCPcLabResults.sql.
I do not know why I got an error {Must declare the scalar variable "@@"} or {Must declare the scalar variable "@@SQLEXPRESS"}!!!???  Please help and advise me how to solve this problem.
 
Thanks in advance,
Scott Chang 
 

View Replies !

---

How To Declare A Procedure Parameter Type To Match A Referenced Table Colum Type
I like to define my procedure parameter type to match a referenced table colum type,
similar to PL/SQL "table.column%type" notation.
That way, when the table column is changes, I would not have to change my stored proc.
Any suggestion?
 

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 !

---

Must Declare The Scalar Variable &"@CategoryID&". Error In Stored Procdure.
Hi People, i'm having some trouble with a stored proceddure used for an updat from a Formview.
Error message i'm getting is Must declare the scalar variable "@CategoryID".
I can't seem to work out why this is occuring and not allowing my proc to run properly. If anyone could help me that would be great :-)
Here is the whole store procedure.
 ALTER PROCEDURE dbo.DeluxeGuideAdmin_Update
@ID int,@CategoryID varchar(10),
@RegionID int,@CompanyName nvarchar(25),
@Email nvarchar(25),@PDFFront nvarchar(50),
@PDFBack nvarchar(50),@ThumbFront nvarchar(50),
@ThumbBack nvarchar(50),@Notes nvarchar(max)
 
AS
DECLARE @SQL varchar(1000)
SET @SQL = 'UPDATE DeluxeGuide SET CategoryID = @CategoryID, RegionID = @RegionID, CompanyName = @CompanyName, Email = @Email, Notes = @Notes'IF (@PDFFront IS NOT NULL) OR (@PDFFront != '')
SET @SQL = @SQL + ', PDFFront = @PDFFront'IF (@PDFBack IS NOT NULL) OR (@PDFBack != '')
SET @SQL = @SQL + ', PDFBack = @PDFBack'IF (@ThumbFront IS NOT NULL) OR (@ThumbFront != '')
SET @SQL = @SQL + ', ThumbFront = @ThumbFront'IF (@ThumbBack IS NOT NULL) OR (@ThumbBack != '')
SET @SQL = @SQL + ', ThumbBack = @ThumbBack'
SET @SQL = @SQL + ' WHERE (ID = @ID)'
Print '@SQL = ' + @SQLEXEC(@SQL)
 
RETURN

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 !

---