Declare Dynamic Cursor From String
Hi,
is it possible to create a cursor from a dynamic string?
Like:
DECLARE @cursor nvarchar(1000)
SET @cursor = N'SELECT product.product_id
FROM product WHERE fund_amt > 0'
DECLARE ic_uv_cursor CURSOR FOR @cursor
instead of using this
--SELECT product.product_id
--FROM product WHERE fund_amt > 0 -- AND mpc_product.status
= 'aktiv'
Havn't found anything in the net...
Thanks,
Peppi
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
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 !
|
TRACKER
