Declare Dynamic Cursor From String

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 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 !

---

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 !

---

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 !

---

Pass In Null/blank Value In The Date Field Or Declare The Field As String And Convert
I need to pass in null/blank value in the date field or declare the field as string and convert date back to string.

I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable

mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))

option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.

With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End

Please help

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 !

---

How To Loop A Cursor And Accumulate A String Value ?
Hi
Why can't I loop a cursor and add values to a string  in Sql server ?



Code Block
ALTER FUNCTION [dbo].[fn_Get_Project_String]
()
RETURNS nvarchar(255)
AS
BEGIN
DECLARE @Prosjekt nvarchar(255), @Pro nvarchar(255)
 
DECLARE c1 CURSOR
FOR select nvarchar3
FROM dbo.AllUserDataCopy
 
OPEN c1
FETCH NEXT FROM c1 INTO @Pro
WHILE @@FETCH_STATUS = 0
 
BEGIN
select @Prosjekt = '<item>' + @Pro + '</item>' + ''
FETCH NEXT FROM c1 INTO @Pro
select @Prosjekt = @Prosjekt + @Pro
END
 
CLOSE c1
DEALLOCATE c1

RETURN @Prosjekt
END
 
 


 
Ivar

 
 

View Replies !

---

Using Stuff To Return A String Of Values From A Column Without A Cursor
I think it was Pat Phelan who posted a little trick here where he used the STUFF function to create a string fo values from a column without using a cursor.

I am starting a brand new project and I did my table design and I am awaiting a finalized requirements document to start coding and I thought I would spend a little time writing some code to autogenerate some generic one record at a time SELECT, INSERT,UPDATE and DELETE stored procedures. With the coming holiday things are getting quiet around here.

The code that is not working is listed below. It does not work. It returns Null. I suck.

DECLARE @column_names varchar(8000)

SET @column_names = ''

SELECT @column_names = STUFF(@column_names,LEN(@column_names),0,C.COLUMN_ NAME + ', ')
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE TABLE_NAME = 'MyTable'

SELECT @column_names

little help?

View Replies !

---

Inserting Parsed String Using Cursor,want To Validate Line Before Insert
Hi ,
We loading file using DTS into ''working" table with 1 column then open cursor and
parse each line into 50 columns and insert each line into
table CustomerOrders

We start to get Files with invalid lines.

Insert of line cause error
---
Server: Msg 8115, Level 16, State 8, Line 202
Arithmetic overflow error converting numeric to data type numeric.
---


Since code inside procedure ,failure returned by procedure .

I want to verify column in cursor against column in table CustomerOrders and if any column in cursor invalid insert row as text in log table and continue


Is possible to do it without testing each column (have 50 columns!!!) against every possible scenario

Thanks
Alex

View Replies !

---

‘default To Local Cursor’ Causes Errors In String-built Declares
Hi,

I would like to use the dboption ‘default to local cursor’ to true.

But when I set it to true for my database, cursors that are built in a ‘string’ and then executed return an error :

Select @SQL = 'Declare SICCursor cursor For Select SIC1 From ' + @StateTable + ' Where BusinessName = ' + '''' + @BusinessName + '''' + ' Order By PubDate desc'

Exec(@SQL)

Open SICCursor

Openning the cursor returns the error message

“A cursor with the name 'SICCursor' does not exist.”

What am I doing wrong (cursors declared ‘normally’ do not have a problem)?

Thanks,
Judith

View Replies !

---

Dynamic SQL String
I need to write a SQL String to retrieve a field value from a table. The problem is that I need to supply the table name as a parameter. If I was just updating a table, I could build a dynamic SQL String and use Exec()

This is what I would write if the name of the table was known:

Select @RecordNo = MAx([VehicleID]) from Alto

This is what my dynamic SQl string looks like:

Select @SqlStatement = 'Select Max([VehicleID]) from ' + @TableName

So how do I run this statement and get the value it would return? Is there an equivalent to exec() that returns a value?

View Replies !

---

Dynamic SQL String
Hello,

I'm trying to build a dynamic SQL string in the following
SP, but the ' and "" character gives me trouble regarding
the @strPosition. Position could be A,B,C etc. All kinds of
suggestions are very welcome!

Regards,

Abrazame


CREATE PROCEDURE spEstateList
@PositionID nvarchar(1)=NULL
AS


DECLARE @strPosition nvarchar(20),
@strSQL nvarchar(500)

/* Build WHERE-string */
--Position
IF @PositionID IS NULL
SELECT @strPosition = '1=1'
ELSE
SELECT @strPosition = 'tblEstate.Position=' +@PositionID


/* Build complete SQL-string */
SELECT @strSQL = 'SELECT *
FROM tblEstate
WHERE ' +@strPosition + '
ORDER BY EstateID'


/* Execute SQL string.*/
EXEC sp_executesql @strSQL

View Replies !

---

' In A Dynamic Select String?
I'm building a select string on the fly based on criteria selected by the user.  The user is given a data grid with Names and Check Boxes, where they can select multiple names and then either print those selections, or download them to excel.  Everything is working fine, except when the Name has an ' in it.  For example O'Kelly or St. John's.  I know I can take all the 's out of the database, but I'd rather keep the data authentic.  Is there a way to manipulate a select string built on the fly accounting for an embedded '?
For example, I build Select * from table where Name IN ('Smith', 'Jones', 'Jordan', 'Bird', 'O'Kelly').... the ' in O'Kelly ends my string and my sql statement blows up.
Any ideas?
Sample Code:<code>         For Each SelectedIndex In rsc.SelectedIndexes                Counter=Counter + 1                dgAssociates.SelectedIndex = SelectedIndex                If Counter = 1 then                    SelectString = "Select * from Associate_Table where AssociateID IN ('" & dgAssociates.SelectedItem.Cells(16).Text() & "'"                Else                    SelectString &= ",'" & dgAssociates.SelectedItem.Cells(16).Text() & "'"                End If            Next            If Counter > 0 then                SelectString &= ")"            End If</code>

View Replies !

---

Putting Dynamic SQL String Together
I want to fill a DBtemp table with data but want to check first if some data exists in
another history table (before filling DBtemp table).
The DBtemp table will then be further prcocessed.

EXEC ('INSERT INTO DBTemp (TableName, trxYearMonthStart, nmbrtrx)
@ProcessTable,
SELECT CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+ ''01'',
COUNT(*)
FROM ' + @ProcessTable + ' GROUP BY
CONVERT(varchar(6), ' + @SelectedColumn + ' , 112)+''01''
')

First problem, I want to write @ProcessTable (name of the table to be processed) into table DBtemp but having
syntax problems near @ProcessTable?
Second thing, how can I check if data (column TableName=Transactions and trxYearMonthStart=01/04/2002) exists in table history and therefore not load anymore this data into table DBTemp?

Thanks for any hints

mipo

View Replies !

---

Update Using Dynamic String
Hi guys,

Problem with trigger again. I need to update databases. I am using a string because the databse name is only determined at run time.

So when a value is updated on axdb.bmssa.empltable , a corresponding value is updated on @databaseName.dbo.PW_IMF

I have tried the following:

IF UPDATE(FirstName)
begin
exec ('UPDATE ' + @EmpDatabaseName + '.dbo.PW_Imf set ' + @EmpDatabaseName + '.dbo.PW_IMF.GivenNames = inserted.FirstName from ' + @EmpDatabaseName + '.dbo.PW_IMF, inserted where ' + @EmpDatabaseName + '.dbo.PW_IMF.EmpNo = ' + @EmpNo)
end

this does not work because I have used the invalid object 'inserted' and is obviously not understood @ runtime

so I tried the followingreplaced 'inserted' with full description of the second database)

IF UPDATE(FirstName)
begin
exec ('UPDATE ' + @EmpDatabaseName + '.dbo.PW_Imf set ' + @EmpDatabaseName + '.dbo.PW_IMF.GivenNames = [axdb].[bmssa].[Empltable].FirstName from ' + @EmpDatabaseName + '.dbo.PW_IMF, axdb.bmssa.empltable where ' + @EmpDatabaseName + '.dbo.PW_IMF.EmpNo = ' + @EmpNo)
end

This updates the correct record and field in the stipulated database but not with the correct value . The value which is used is the value from the last record in axdb.bmssa.empltable

Probably simple solution for experienced developers but has been a bit frustrating. Any help appreciated

Rowan

View Replies !

---

Dynamic Connection String
Our Reporting Services environment uses Oracle as the data source.  Based upon the user connecting to the database determines what rows they will see for various tables.  How can we dynamically pass the username/password to the connection string?  Background: Our users log into Active Directory and are assigned to a group.  The AD group name is used to access a control table in Oracle that contains the database username/password for that group€™s connection to Oracle.  All subsequent connections to Oracle will use the group€™s username/password from the control table.  We have an ASP.NET application that works like this and stores the connection information in the session state.   How can we do something similar with our connection in Reporting Services? Note: Our Oracle Database does not use Windows Integration.

View Replies !

---

Dynamic Connection String
We were able to use a dynamic connection string in the report designer, but once we deployed to the report server we are getting the following error:
Error during processing of the ConnectString expression of datasource €˜Dynam€™. Has anyone experienced this, and how did you fix it? 

View Replies !

---

Concatenating Dynamic String In SP
Hey guys, I 'm coding my very first stored procedure as accessed by a
.NET application. My input parameter is a dynamically built string. I need to concatenate to a sql query within the SP. I've tried using '+' as the concat. character but it doesn't work.

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER procedure [dbo].[tmptable_query] (@condition_cl varchar(100)) as select * from temp_table + @condition_cl

Help would be appreciated. Thank you.

View Replies !

---

How Can I Pass A String Parameter More Than 4000 Characters Into Execute() And Return Result For FETCH And Cursor?
Dear All
 
I have no idea to write a store procedure or only query to pass a string parameter more than 4000 characters into execute() and return result for FETCH and Cursor.
 
Here is my query sample for yours to understand.
 
 

SET NOCOUNT ON

DECLARE @ITEMCODE int, @ITEMNAME nvarchar(50), @message varchar(80), @qstring varchar(8000)

Set @qstring = 'select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union 
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union
select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm union

select itemcode from oitm'

PRINT '-------- ITEM Products Report --------'

DECLARE ITEM_cursor CURSOR FOR

execute (@qstring)

OPEN ITEM_cursor

FETCH NEXT FROM ITEM_cursor

INTO @ITEMCODE

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' '

SELECT @message = '----- Products From ITEM: ' +

@ITEMNAME

PRINT @message

-- Get the next ITEM.

FETCH NEXT FROM ITEM_cursor

INTO @ITEMcode

END

CLOSE ITEM_cursor

DEALLOCATE ITEM_cursor

 
Why i use @qstring? It is because the query will be changed by different critiera.
 
Regards
Edmund

View Replies !

---

Dynamic SQL String With Cookie Request
Hi,is it possible to build a SQL SELECT COMMAND with a Cookie Request in between a IF ELSE Loop?My idea is to change the  SQL SELECT COMMAND depending to a cookie.Is there any help or tutorial that somebody can suggest? caspar.netcologne, germany, EU 

View Replies !

---

Dynamic Connection String Problem
I need to be able to deploy my updated website to many customers on a monthly basis and dont want to be mucking around changing the connection strings each time. Some of my web servers have multiple copies of my site and DB so each website will need a different connection string.
The simplest method I could come up with is to use the Application Name field in IIS as it doesn't get overwritten by Visual Studio when I deploy the site.
I am trying to write some code to dynamically change the connection string in the web config but cannot find any way of reading the Application Name field in IIS to use in altering the connection string. I'm using the Global.asax file to change the connection string before the DB gets called.
I had tried embedding the DB in the website folder but it would overwrite the customers database.
 
 

View Replies !

---

Dynamic SQL - Single Quotes In String
I'm constructing a SQL string that needs single quotes in the WHERE clause. How do I encapsulate them in a string variable. I looked into ESCAPE and SET QUOTED_IDENTIFIER, but i don't really see any examples using string Concatenation. I'm trying to filter out the zls (0 length strings)

This doesn't work (all single quotes):

@sqlString = ' SELECT * FROM myTbl '
@sqlString = @sqlString + 'WHERE fld1 <>'' '

Thanks,
Carl

View Replies !

---

Dynamic Connection String And Subscriptions
 We were able to get the dynamic connection string working with Stored Procedures. Is there a work around to use this and still be able to use subscriptions? I understand that the subscriptions run as one user. We are using our dynamic connection string to connect to the database based on the active directory group that the person belongs to. If the report runs as the unattended user then it will use the connection string and connect to the database as the unattended user, not as the person that subscribed to the report. Has anyone been able to implement this?

View Replies !

---

Dynamic Connection String Error
We are trying to use a dynamic connection string that brings back the user name and password to connect to the database. We created a dll that passes back the connection string. This works beautifully in the designer, but it does not work when we deploy to the report server. This is the error that I retrieved from the logs. Has anyone had a similar problem or know of a work around?
 
 aspnet_wp!library!8!08/24/2007-10:44:12:: i INFO: Call to GetPermissions:/
aspnet_wp!library!8!08/24/2007-10:44:12:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!d!08/24/2007-10:44:16:: i INFO: Call to GetPermissions:/New Folder
aspnet_wp!library!d!08/24/2007-10:44:16:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!08/24/2007-10:44:17:: i INFO: Call to GetPermissions:/New Folder/Connection String Test
aspnet_wp!library!8!08/24/2007-10:44:17:: i INFO: Call to GetSystemPermissions
aspnet_wp!library!8!08/24/2007-10:44:18:: i INFO: Call to RenderFirst( '/New Folder/Connection String Test' )
aspnet_wp!processing!8!8/24/2007-10:44:18:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error during processing of the ConnectString expression of datasource €˜Dynam€™., ;
 Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error during processing of the ConnectString expression of datasource €˜Dynam€™.
aspnet_wp!processing!8!8/24/2007-10:44:18:: e ERROR: Data source 'Dynam': An error has occurred. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error during processing of the ConnectString expression of datasource €˜Dynam€™.
   at Microsoft.ReportingServices.ReportProcessing.DataSource.EvaluateConnectStringExpression(ProcessingContext processingContext)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.OpenConnection(DataSource dataSourceObj, ReportProcessingContext pc)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.Process()
aspnet_wp!processing!8!8/24/2007-10:44:18:: e ERROR: An exception has occurred in data source 'Dynam'. Details: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error during processing of the ConnectString expression of datasource €˜Dynam€™.
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.Process()
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSourceNode.ProcessConcurrent(Object threadSet)
aspnet_wp!processing!8!8/24/2007-10:44:18:: i INFO: Merge abort handler called for ID=-1. Aborting data sources ...
aspnet_wp!processing!8!8/24/2007-10:44:18:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing., ;
 Info: Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error during processing of the ConnectString expression of datasource €˜Dynam€™.
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.Process()
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSourceNode.ProcessConcurrent(Object threadSet)
   --- End of inner exception stack trace ---
aspnet_wp!webserver!8!08/24/2007-10:44:18:: e ERROR: Reporting Services error Microsoft.ReportingServices.Diagnostics.Utilities.RSException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ProcessingAbortedException: An error has occurred during report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: Error during processing of the ConnectString expression of datasource €˜Dynam€™.
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ReportRuntimeDataSourceNode.Process()
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RuntimeDataSourceNode.ProcessConcurrent(Object threadSet)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.AbortHelper.ThrowAbortException(Int32 reportUniqueName)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessingContext.CheckAndThrowIfAborted()
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.Merge.Process(ParameterInfoCollection parameters, Boolean mergeTran)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessReport(Report report, ProcessingContext pc, ProcessingContext context)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.ProcessReport(Report report, ProcessingContext pc, Boolean snapshotProcessing, Boolean processWithCachedData, GetReportChunk getChunkCallback, ErrorContext errorContext, DateTime executionTime, CreateReportChunk cacheDataCallback, ProcessingContext& context)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(IRenderingExtension renderer, DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc, CreateReportChunk cacheDataCallback, Boolean& dataCached)
   at Microsoft.ReportingServices.ReportProcessing.ReportProcessing.RenderReport(DateTime executionTimeStamp, GetReportChunk getCompiledDefinitionCallback, ProcessingContext pc, RenderingContext rc)
   at Microsoft.ReportingServices.Library.RSService.RenderAsLive(CatalogItemContext reportContext, ItemProperties properties, ParameterInfoCollection effectiveParameters, Guid reportId, ClientRequest session, String description, ReportSnapshot intermediateSnapshot, DataSourceInfoCollection thisReportDataSources, Boolean cachingRequested, Warning[]& warnings, ReportSnapshot& resultSnapshotData, DateTime& executionDateTime, RuntimeDataSourceInfoCollection& alldataSources, UserProfileState& usedUserProfile)
   at Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters)
   at Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext reportContext, ClientRequest session, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
   at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()
   at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()
   at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService rs, CatalogItemContext reportContext, ClientRequest session, JobType type, Warning[]& warnings, ParameterInfoCollection& effectiveParameters, String[]& secondaryStreamNames)
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderReport(HttpResponseStreamFactory streamFactory)
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.DoStreamedOperation(StreamedOperation operation)
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderItem(ItemType itemType)
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPageContent()
   at Microsoft.ReportingServices.WebServer.ReportServiceHttpHandler.RenderPage()

View Replies !

---

Dynamic Connection String For ReportDatasource
Hi,

 

Is there any way to Dynamically change(Based on User login) Connection String  for a report data source from code behind. In My application each user may have different data base. I am using a single shared data source for all the report. Please give me a solution.

 

Thanks

Sonu
 

View Replies !

---

Pass Dynamic Connection String To Rdl
Hi,

In c#  - how to pass uid,pwd,dbname and servername  as input parameters  from vs 2003 windows application (am calling the rdl file from reporting service 2005 web service) to sql server 2005 rdl files.

Thanks,

Shanthi

 

View Replies !

---

Dynamic Connection String For Oracle
i have successfully implemented a dynamic connection string based on a dropdown list of environments (dev, test, prod).  it works well during testing in the vs2005 ide; but once i deploy it to the rs server, it complains that the credentials are not stored in the rs server database and won't run the report.
 
as in most large organizations, the developers do not have control over the rs server, so i cannot manipulate rs config or web config files on the server side; so, how do i get past this obstacle?
 
thanks in advance
 

View Replies !

---

String / Variable Problem - Dynamic Table Name
HiI'm grateful for any light you can shed on this!!I've to admit, it's an unusual design but I've multiple contact tables namede.g. i2b_ash_contact or i2b_ted_contact.'i2b_' and '_contact' are static but the middle part is dynamic.Storing all contacts in one table with an identifier of e.g. 'ash' or 'ted'for each record is not possible.Returning the value from the dynamic Query is no problem but I don't knowhow to assign it to a variable.When I try doing this it either runs into problems with evaluating thevariables or doesn't retuen anything at all e.g. if I say at the end 'Print@AddressID'. The variable remains empty.How can I do something like:DECLARE@AddressID int,@ProgClient (varchar(10),@Table varchar(10)Note: @Prog is a string e.g. 'ash' or 'ted'SET @Table = 'i2b_ + @ProgClient + '_contactSET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)

View Replies !

---

Dynamic Connection String For Analysis Services
I have a package where I need a dynamic connection string for an Analysis Services connection manager.

I have implemented this successfully for a Text data source, and a SQL data source, but the same approach does not seem to be working for an AS connection.

I set some expressions for the AS connection manager (ServerName, InitialCatalog, even the entire ConnectionString itself), but they don't take.  I don't get any errors, but the task processes the cubes for the AS connection as it was established at run time.  The design time connection string changes don't appear to get evaluated.  This seems to be an issue only for AS connections.

Let me know if you have any ideas - thanks.

 

 

View Replies !

---