MS SQL Server :: Declare Cursor With Dynamic SQL?

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 !

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 !

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 !

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 !

Dynamic Cursor Versus Forward Only Cursor Gives Poor Performance
Hello,I have a test database with table A containing 10,000 rows and a tableB containing 100,000 rows. Rows in B are "children" of rows in A -each row in A has 10 related rows in B (ie. B has a foreign key to A).Using ODBC I am executing the following loop 10,000 times, expressedbelow in pseudo-code:"select * from A order by a_pk option (fast 1)""fetch from A result set""select * from B where where fk_to_a = 'xxx' order by b_pk option(fast 1)""fetch from B result set" repeated 10 timesIn the above psueod-code 'xxx' is the primary key of the current Arow. NOTE: it is not a mistake that we are repeatedly doing the Aquery and retrieving only the first row.When the queries use fast-forward-only cursors this takes about 2.5minutes. When the queries use dynamic cursors this takes about 1 hour.Does anyone know why the dynamic cursor is killing performance?Because of the SQL Server ODBC driver it is not possible to havenested/multiple fast-forward-only cursors, hence I need to exploreother alternatives.I can only assume that a different query plan is getting constructedfor the dynamic cursor case versus the fast forward only cursor, but Ihave no way of finding out what that query plan is.All help appreciated.Kevin

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 !

Declare Variables In A SP Dynamic?
Hello there,

i have been asked about a thing that, i think, is not possible. But maybe i am wrong.

question:
Is it possible to have a Stored Procedure in that the declaration of the variables is dynamic?
This means, can i get the variable name and the type of it from a database to create
a dynamic stored procedure that changes itself by firing a trigger.

Thanks for all oppinions and answers.
everWantedLINUX

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 !

Dynamic Cursor/ Dynamic SQL Statement
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.

Using the examples given in Books Online returns compilation errors. See below.

Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?

James

-- SQL ---------------

EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;

EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;

EXEC SQL
PREPARE select_statement FROM :szCommand;

EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;

--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.

View Replies !

Dynamic SQL In Cursor
I need to pass a list of values into a cursor as such...

DECLARE
@group_SQL varchar(255)

SET @group_SQL = 'SELECT group_id FROM groups where group_id in (' + @group_id + ')'

DECLARE groupContact_import_cursor CURSOR
FOR EXEC(@group_SQL)
OPEN groupContact_import_cursor
FETCH NEXT FROM groupContact_import_cursor INTO @group_id
WHILE (@@FETCH_STATUS = 0)
BEGIN
insert into groupContacts (group_id, contact_id) values (@group_id, @new_cid)
FETCH NEXT FROM groupContact_import_cursor INTO @group_id
END
CLOSE groupContact_import_cursor
DEALLOCATE groupContact_import_cursor

But MS SQL doesn't seem to like the FOR EXEC(@group_SQL). Can someone shed some light?

TIA

View Replies !

Dynamic Cursor
I am trying to use a dynamic cursor in a stored procedure:
The code looks like this :

/************************************************** ***
set @sFormula = 'Monthlyformula'
set @sStartDate = '02/01/2004'
set @sEndDate = '02/01/2004'

exec('DECLARE APPGRIDROWS_METRICS CURSOR FOR select populateid From appgridrows where histdisplaygrid = 3 And '+ @sFormula +' Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between '+ @sStartDate +' and '+ @sEndDate +')' )
/************************************************** ***

And this is what it is interpreting

select populateid From appgridrows where histdisplaygrid = 3 And Monthlyformula Is Null and exists (SELECT 1 From PAYROLL_DATA_PERIOD as h Where h.id=1 and h.populateid=appgridrows.populateid and h.StartDate between 02/01/2004 and 02/01/2004)

My problem is Is there anyway that I can put the quotes before those dates('02/01/2004') so that my cursor has some records returned

Thanks in advance

SK

View Replies !

Dynamic Cursor
Hello !

I´m having a big problem with a dynamic cursor.
There is my problem:

I have two cursors. One I use to select a master table. The other I use to select a detail table. I want to fetch all rows of the master and for each row select the details.

How can I pass to the detail cursor the master key ?

Thanks and I´m sorry for my poor English ! :)

View Replies !

Dynamic Cursor
Hi All€¦

I need to bind a DataGrid to server dynamic cursor.

Please help!

View Replies !

How Get The Dynamic Sql In To Cursor
Dear folks,

In My Query i am using where in condition .It return multiple record .I want store it in to cursor and perform the operation.

Declare @sql varchar(5000);
set @sql='select * from Role where Role_id in('+ @role_ids +')';
Exec @sql;

I want take this record set in to cursor .How to do it.
please help me.

View Replies !

Dynamic Cursor Generation..
Hi Everybody,I have a probs with dynamic generation.I am writing the probs======================================create proc testasdeclare @query varchar(500)set @query = 'select * from table'----------------------------------------------declare mycur Cursor for Select * from table |open mycur |----------------------------------------------but instate of above block how can I dynamically generate this query?---------------------------------------declare mycur Cursor for exec (@query) |---------------------------------------Or tell me the way.RegardsArijit Chatterjee

View Replies !

Dynamic Select For CURSOR
Hi all

I am trying to do dynamic Select for Cursor. The dynamic would be like this:
IF CONDITION1 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID
IF CONDITION2 IS TRUE:
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE CustomerTenderID = @CustomerTenderID AND
CustomerSiteID = @CustomerSiteID

etc etc

Here's the cursor

DECLARE RateList CURSOR FOR
SELECT CustomerTenderID, CustomerSiteID, ContractPeriod, SupplierID
FROM dbo.tnd_TenderSiteRateConsumptionView
WHERE (BASED ON CONDITION)
ORDER BY CustomerTenderID,
CustomerSiteID,
SupplierID,
ContractPeriod

OPEN RateList
FETCH NEXT FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID
WHILE @@FETCH_STATUS = 0
BEGIN
SET @rowNum = @rowNum + 1

-- DO SOME FUNKY STUFF

FETCH NEXT
FROM RateList
INTO@CustomerTenderID, @ReturnedCustomerSiteID, @ReturnedContractPeriod, @ReturnedSupplierID

View Replies !

Dynamic Cursor - Sorting In Declaration
Hello everybody!I have a small table "ABC" like this:id_position | value---------------------------1 | 112 | 223 | 33I try to use a dynamic cursor as below.When the statement "order by id_position" in declare part of the cursor_abcis omitted - cursor work as it should.But when the statement "order by id_position" is used, cursor behave asstatic one.What's the matter, does anybody know?Code:declare @id_position as int, @value as intDECLARE cursor_abc CURSORFORselect id_position, value from abcorder by id_positionset nocount onopen cursor_abcFETCH NEXT FROM cursor_abcINTO @id_position, @valueWHILE @@FETCH_STATUS = 0BEGINprint @id_positionprint @valueprint '----------------------------'update abc set value=666 --next reading should give value=666FETCH NEXT FROM cursor_abcINTO @id_position, @valueENDCLOSE cursor_abcDEALLOCATE cursor_abcGORegardsLucas

View Replies !

Dynamic Execution Of Cursor Fetch
I'm trying to write code which will fetch records from a dynamically created cursor. Therefore the cursor name is not known at design time.

Unfortunately applying an enclosing the fetch command in quotes and then calling the execute method with this string will not work.

e.g.

declare @A varchar(10),
@B varchar(10),
@sFetch varchar(255)

--Open the cursor
Execute ('Open ' + crAnyCursor)

--create fetch string
Select @sFetch = 'Fetch Next From' + crAnyCursor + 'into @A, @B'

execute (@sFetch)

The following error is returned...
Msg 137, Level 15, State 1
Must declare variable '@A'.

Is there another means of working with dynamically executed cursors?

Thanks
JohnG

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 !

Executing SP Having A Dynamic Cursor Fails In Calling SP
Hi,
In a stored procedure (SP1) I call another stored procedure (SP2), passing along parameters. In SP2 I dynamically build cursor c1. I can execute SP2 without any problems but when I start SP1 I get the following message:

Msg 16916, Level 16, State 1, Procedure SP2, Line 114
A cursor with the name 'C1' does not exist.

Yes, the cursor is of type GLOBAL. I am sure I miss something here ...
Any help is highly appreciated !

Thanks: Peter

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 !

Dynamic Query, Local Cursor Variable And Global Cursors
Hi all.

I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out.

So, first things first: let me explain what I need to do. I am
designing a web application that will allow users to consult info
available in a SQL2000 database. The user will enter the search
criterea, and hopefully the web page will show matching results.

The problem is the results shown aren't available per se in the DB, I
need to process the data a bit. I decided to do so on the SQL Server
side, though the use of cursors. So, when a user defines his search
criteria, I run a stored procedure that begins by building a dynamic
sql query and creating a cursor for it. I used a global cursor in order
to do so. It looked something like this:

SET @sqlQuery = ... (build the dinamic sql query)

SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

OPEN myCursor

FETCH NEXT FROM myCursor INTO ...

CLOSE myCursor

DEALLOCATE myCursor

This works fine, if there's only one instance of the
stored procedure running at a time. Should another user connect to the
site and run a search while someone's at it, it'll fail due to the
atempt to create a cursor with the same name.

My first thought was to make the cursor name unique, which led me to:

...

SET @cursorName = 'myCursor' + @uniqueUserID

SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery

EXEC @cursorQuery

...

The problem with this is that I can't do a FETCH NEXT FROM @cursorName since
@cursorName is a char variable holding the cursor name, and not a
cursor variable. So to enforce this unique name method the only option
I have is to keep creating dynamic sql queries and exucting them. And
this makes the sp a bitch to develop and maintain, and I'm guessing it
doesn't make it very performant.

So I moved on to my second idea: local cursor variables. The problem with
this is that if I create a local cursor variable by executing a dynamic
query, I can't extract it from the EXEC (or sp_executesql) context, as
it offers no output variable.

I guess my concrete questions are:

Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How?

Anybody sees another way arround this?Thanks in advance,

Carlos

View Replies !

Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper

View Replies !

Variable Type For Fetcing The Cursor Record In Tsql Cursor
what is the equivalent of the %rowtype in oracle for the tsql

View Replies !

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 !

Join Cursor With Table Outside Of Cursor
part 1

Declare @SQLCMD varchar(5000)
DECLARE @DBNAME VARCHAR (5000)

DECLARE DBCur CURSOR FOR
SELECT U_OB_DB FROM [@OB_TB04_COMPDATA]

OPEN DBCur
FETCH NEXT FROM DBCur INTO @DBNAME

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' +
+ 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' +
+ 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL'
EXEC(@SQLCMD)
PRINT @SQLCMD
FETCH NEXT FROM DBCur INTO @DBNAME

END

CLOSE DBCur
DEALLOCATE DBCur

Part 2

SELECT
T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE,
SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE,
SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT
INTO #MAIN
FROM
ORDR T0
JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY
LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE
LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY
LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE
WHERE ISNULL(T0.U_OB_TID,0) <> 0
GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS

my question is,
how to join the part 1 n part 2?
is there posibility?

View Replies !

Declare @var????
I am trying to get a grasp on the Sql Stored procedures it seems i dont really understnad what DECLARE @Date DateTime means??? I mean i think it means that i am just declaring a varible name Date that will hold a DateTime Value??? is that correct or is it more to it????

CREATE PROCEDURE dbo.Tracking_GetStatus
AS
DECLARE @Date DateTime
DECLARE @Begining DateTime
DECLARE @Ending DateTime

SET @Date = GETDATE()
SET @Begining = DATEADD(ss,(DATEPART(ss,@Date)*-1),
     DATEADD(mi,(DATEPART(mi,@Date)*-1),
     DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date)))
SET @Ending   = DATEADD(ss,-1,
     DATEADD(dd,1,DATEADD(ss,(DATEPART(ss,@Date)*-1),
     DATEADD(mi,(DATEPART(mi,@Date)*-1),
     DATEADD(hh,(DATEPART(hh,@Date)*-1),@Date)))))

SELECT
  Vehicl,
  UpdateTi
  XCoord,
  YCoord,
  Status
FROM Track
WHERE UpdateTime >= @Begining
  AND UpdateTime <= @Ending
RETURN

GO

View Replies !

DECLARE In SQL CE
Can I use DECLARE in SQL 2005 Compact, and if not, how do I do INSERTs into tables which have columns with Primary Key constraints?

Matt

View Replies !

My DropDownList Won't Declare?
Need a little help! I am trying to insert ListItems values from a DropDownList into a database table. However in the code behind I am continuosly met with the error Name 'ddltest' is not declared. As you can see from the code below ddltest is an object with the ID ddltest. What am I doing wrong?
Protected Sub ddltest_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)Dim MyVar As String = ddltest.SelectedItem.Value

If MyVar = "" Then
ErrorMessage.Text = "Please select a test"
Else
'Insert selection into databaseDim oConnection As New SqlConnection
Dim oCommand As SqlCommand
Dim sConnString As String
Dim sSQL As String
sConnString = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|xxxxx.mdf;Integrated Security=True;User Instance=false"oConnection = New SqlConnection(sConnString)
sSQL = "INSERT INTO testDB(Myxxxx) Values (@Myxxxx)"
oConnection.Open()oCommand = New SqlCommand(sSQL, oConnection)oCommand.Parameters.Add(New SqlParameter("@Myxxxx", MyVar))
oCommand.ExecuteNonQuery()
oConnection.Close()
ErrorMessage.Text = "You selected " & MyVar & " and it has been added to the database."
End If
End Sub

<asp:TemplateField HeaderText="Test">
<EditItemTemplate>

<asp:DropDownList ID="ddltest" runat="server" AutoPostBack="True" OnSelectedIndexChanged="ddltest_SelectedIndexChanged" >
<asp:ListItem Selected="True" Value="" ><-- Please Select a test --></asp:ListItem><asp:ListItem Value="1">1</asp:ListItem>
<asp:ListItem Value="2">2</asp:ListItem>
<asp:ListItem Value="3">3</asp:ListItem>
<asp:ListItem Value="4">4</asp:ListItem>
<asp:ListItem Value="5" >5</asp:ListItem>
</asp:DropDownList>

</EditItemTemplate>
</asp:TemplateField>

View Replies !

Must Declare Scalar @.....
Hi everyone,
I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised in the body of the script.
I post the relevent code below:
SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int))    sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar))    sqlSetCandidateEmail.Parameters("@LoginName").Value = userName    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar))    sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email    sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime))    sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered    sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection)
    C4LConnection.Open()    CandidateID = sqlSetCandidateEmail.ExecuteScalar()
Catch Exp As SqlException    lblResults.Visible = True    lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try
All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition
So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script.
Any help would be appreciated.

View Replies !

How Do I Declare A Recordset ?
Hi allHow do i declare a recordset and fetch records from an sql server?TIAGuy

View Replies !

'declare' Syntax In A UDF
Hi, I'm trying to create a function that returns a table, however I wantto use a local variable in there and enterprise manager ain't liking it!The error I get is number 156 'incorrect syntax near the keyword'declare'.. hopefully this is just a simple thing where I've put it inthe wrong place.The code follows:CREATE FUNCTION AFGroupedTotals (@campaign nvarchar(30),@datefromsmalldatetime, @dateto smalldatetime, @prospect nvarchar(30), @typenvarchar(20))RETURNS TABLE ASRETURNdeclare @set nvarchar(150)select "Total Pledged" as info, sum(total) as totFROM AFresponseTotals (@campaign, @datefrom, @dateto,@prospect)Cheers for any help,Chris

View Replies !

Declare @var Text
Hi,

I want to store more than 8000 characters. Ideally it might have been fine if decalre @var text had worked for me.

Can anyone tell is it possible....

thanks,
sajai.

View Replies !

How To Declare Integer Var In VB&&>NET
PLeaset le tme knwo

View Replies !

Declare Variable For All In SP
In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.

The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.

The MemberId field is a varchar(20) in the table.

Create procedure sp_GetClaims_BY_MemberID
@Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @memberid

EXEC sp_GetClaims_BY_MemberID '99999999999'

The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?

I am currently working in SQL 2000.

View Replies !

Declare Variable
Declare @DBName varchar(25)
select @DBName = 'Production'
Select @DBName = @DBName + '.dbo.'+'sysfiles'
select @DBName
select * from @DBName

When I executes above lines of code in query analyser it give me an error like :

Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@DBName'.

give me solution as soon as possible

Thanks
Aric

View Replies !

Why Declare Does Not Work
declare @fdas as varchar(10)
set @fdas = 'master'

use @fdas

i try to execute this one it does not work
said that Msg 102, Level 15, State 1, Line 4
Incorrect syntax near '@fdas'. how to fix that one? by not replacing the @fdas

but i got master database

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

what is the difference actually between

use master than the first querry?

arifliminto86

View Replies !

Declare Table
Dear All,
actually, in our application, we are using so many temporary local variables like @table_name....

and based on these local variables, we are making joins with the regular adtabase tables. is it better thing instead of temp tables?

what exactly i want to know is,
@table_name,#temp_table,##temp_table.....
in these three processes, which one is the best one...

thanks in advance

Vinod
Even you learn 1%, Learn it with 100% confidence.

View Replies !

Declare Table
When I run this script.It gives me an error that I need to "Delcare @pc2" Why? Please help.

Declare @pc2 Table

([prop_char_typ_code] [varchar](5) NOT NULL,
[tax_year] [varchar](4) NOT NULL,
[property_id] [int] NOT NULL,
[id] [int] IDENTITY(1,1) NOT NULL,
[value] [varchar](100) NULL,
[prop_segment_id] [int] NULL)

Insert into @pc2

([prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id])

Select

[prop_char_typ_code],
[tax_year],
[property_id],
[id],
[value],
[prop_segment_id]

from property_char

Go

SELECT 'ACRES', '2008', property_char.property_id, ROUND(property_char.value,0)
FROM

property_char INNER JOIN
property ON property_char.property_id = property.id INNER JOIN
property_char AS @pc2 ON property.id = @pc2.property_id INNER JOIN
prop_valuation ON property.id = prop_valuation.property_id INNER JOIN
val_component ON property.id = val_component.property_id

WHERE property_char.property_id < 81695 AND
property_char.property_id = property.id AND
property_char.prop_char_typ_code = 'SIZE' AND
property_char.tax_year = '2008' AND
@pc2.prop_char_typ_code = 'USECD' AND
(@pc2.value not in ('85','86','87','88','95') AND --( <=== Review list of Usecodes))
@pc2.tax_year = '2008' AND
@pc2.property_id = property.id AND
property.pact_code = 'REAL' AND
(property.eff_to_date is null OR property.eff_to_date >= getdate())AND
prop_valuation.property_id = property.id AND
prop_valuation.tax_year = '2008' AND
prop_valuation.local_assed_ind = 'Y' AND
val_component.value_type = 'MKLND' AND
val_component.property_id = property.id AND
val_component.tax_year = '2008' AND
val_component.modified_value > 0)AND
NOT EXISTS (Select 'z' from parcel_exclude where property.parcel_number = parcel_exclude.parcel_number AND special_assessment = 'CD')

View Replies !

Declare A Concatenated Value.
OMG i'm so stupid, i edited my original post instead of replying!!

I was wondering if there was away to write a stored procedure where I concatenate several columns to create a Phrase and use that Phrase as a new value to do a second search in another table.

Example.

Table 1.

Column1 (Modem) Column2 (Highspeed) Column3 (Black)

Concatenated to be MODEM HIGHSPEED BLACK.

The 2nd table has the concatenated value as it's own column and 2nd column for the number in Inventory.

Column1 (MODEM HIGHSPEED BLACK) Column2 (44)

View Replies !

Must Declare The Scalar Variable
Hi with the code below I am getting the error
Error inserting record. Must declare the scalar variable "@contractWHERE"
I removed @contract and it then gave me the error
Error inserting record. Must declare the scalar variable "@zipWHERE"
I was wondering if some can point me in the right direction for fixxing this
protected void cmdUpDate_Click(Object sender, EventArgs e)
{
//Define ADO.NET Objects.
string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city,state=@state, ";
updateSQL += "zip=@zip, contract=@contract";
updateSQL += "WHERE au_id@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
//Add the parameters.
cmd.Parameters.AddWithValue("@au_id", txtID.Text);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked));
cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value);
//Try to open the database and execute the update
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
lblStatus.Text = updated.ToString() + " records inserted.";
}
catch (Exception err)
{
lblStatus.Text = "Error inserting record. ";
lblStatus.Text += err.Message;
}
finally
{
con.Close();
}
}
}

Many Thanks in advance

View Replies !

Must Declare The Scalar Variable
I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.

View Replies !

How To Declare Global Sqlcommands...
Hello guys! Is it possible to declare global sql commands and call it in a rowcommand_function?
Here's what I did...
Dim p_s_syounin2 As New SqlCommand
Dim cnn As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("StrConn").ConnectionString)

Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)

If (Session("syozokubu_id") = 20) And (Session("syozokuka_id") = 21) And ((Session("kaikyuu_id") = 23)) Then

p_s_syounin2.CommandText= ("UPDATE TE_shounin_zangyou SET p_s_syounin2=syain_hnm FROM TR_syainID WHERE syozokubu_id=20 AND syozokuka_id=21 AND kaikyuu_id=23")
'''' connection string is not placed here acc. to my research
End If

End Sub

Protected Sub my_gridview_RowCommand(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewCommandEventArgs) Handles my_gridview.RowCommand

If e.CommandName = "Approve" Then

cnn.Open()
p_s_syounin2.ExecuteNonQuery()

cnn.Close()

End If

End Sub

I get an error message that says " ExecuteNonQuery: Connection property has not been initialized. "

Please help me.

Thanks guys.

Audrey

View Replies !