Dynamic Cursor Generation..
Hi Everybody,
I have a probs with dynamic generation.
I am writing the probs
======================================
create proc test
as
declare @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.
Regards
Arijit Chatterjee
View Complete Forum Thread with Replies
Related Forum Messages:
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 !
Dynamic SQL Generation For The UpdateCommand Is Not Supported
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information. I am getting the above error if i try to create a update command through a Command builder in .net cf on a SQLCE 3.0 database The Select query I used for creating the update command is 'SELECT * FROM Users' I populated a datatable with the above query through a DataAdapter, to check for the primary key and the primary key was available in the table. But still the CB could not able to create an Update Command for that query, The Primary Key column that I use is of type GUID Then when try to do the above job using a Typed DataSet with TableAdapters , then it throws the following error 'Update requires a valid UpdateCommand when passed DataRow Collection with modified rows' can any one please tell me a way to fix this , Thanks and Regards Varadan
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 !
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 !
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 !
Declare Dynamic Cursor From String
Hi,is it possible to create a cursor from a dynamic string?Like:DECLARE @cursor nvarchar(1000)SET @cursor = N'SELECT product.product_idFROM product WHERE fund_amt > 0'DECLARE ic_uv_cursor CURSOR FOR @cursorinstead of using this--SELECT product.product_id--FROM product WHERE fund_amt > 0 -- AND mpc_product.status= 'aktiv'Havn't found anything in the net...Thanks,Peppi
View Replies !
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 !
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 !
Make A Dynamic Cursor In A Stored Procedure
I need im my aplication to meke a "Cursor" in a execution of a stored procedure. For sample: In a table with a report definition I have the "Fileds, From, Group, Order " clausulas and I need make a cursor with a contents of this fileds. How can I do ??? My code: Declare @idRelat int, @cmd_FROM nvarchar(1024), @cmd_Det nvarchar(50) SELECT @idRelat = idRelat, @cmd_Det = cmd_DET FROM Relatórios WHERE Nome = @p_Relat Declare @Tot_Col smallint, @Tot_Lin smallint, @Campos smallint, @Aux_Select nvarchar(1024), @Aux_Group nvarchar(1024), @Aux_Order nvarchar(1024) Select @Tot_Col = 0 Select @Tot_Lin = 0 Select @Campos = 0 Select @Aux_Select = "SELECT " + @cmd_DET + "AS Soma" Select @Aux_Group = "GROUP BY " Select @Aux_Order = "ORDER BY " Declare @a_Local char(1), @a_Linha smallint, @a_Campo nvarchar(50) Declare cur_Aux insensitive cursor for SELECT Local, Linha, Campo From Relatórios_Margens WHERE (idRelat = @idRelat) ORDER BY Local, Linha Open cur_Aux Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo While @@FETCH_status = 0 begin If @a_Local = "C" Select @Tot_Col = @Tot_Col + 1 Else Select @Tot_Lin = @Tot_Lin + 1 Select @Campos = @Campos + 1 If @Aux_Group <> "GROUP BY " begin Select @Aux_Group = @Aux_Group + ", " If @Aux_Order <> "ORDER BY " begin Select @Aux_Order = @Aux_Order + ", " Select @Aux_Select = sSelect + ", " + @a_Campo + " AS Campo" + @Campos Select @Aux_Group = @Aux_Group + @a_Campo Select @Aux_Order = @Aux_Order + @a_Campo Fetch cur_Aux into @a_Local, @a_Linha, @a_Campo End Select @Aux_Select = @Aux_Select -- <<<< MONTA COMANDO SQL Select @Aux_Select = @Aux_Select + " " + @cmd_FROM + " " + @p_Filtro + " " + @Aux_Group + " " + @Aux_Order Declare @Cursor_Aux cursor Set @Cursor_Aux = cursor for @Aux_Select Open @Cursor_Aux Not working !!!!
View Replies !
Dynamic Query, Local Cursor Variable And Global Cursors
Hi all. I am stuck in a bit of a conundrum for quite a while now, and I hope someone here will help me figure this one out. So, first things first: let me explain what I need to do. I am designing a web application that will allow users to consult info available in a SQL2000 database. The user will enter the search criterea, and hopefully the web page will show matching results. The problem is the results shown aren't available per se in the DB, I need to process the data a bit. I decided to do so on the SQL Server side, though the use of cursors. So, when a user defines his search criteria, I run a stored procedure that begins by building a dynamic sql query and creating a cursor for it. I used a global cursor in order to do so. It looked something like this: SET @sqlQuery = ... (build the dinamic sql query) SET @cursorQuery = 'DECLARE myCursor CURSOR GLOBAL FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery OPEN myCursor FETCH NEXT FROM myCursor INTO ... CLOSE myCursor DEALLOCATE myCursor This works fine, if there's only one instance of the stored procedure running at a time. Should another user connect to the site and run a search while someone's at it, it'll fail due to the atempt to create a cursor with the same name. My first thought was to make the cursor name unique, which led me to: ... SET @cursorName = 'myCursor' + @uniqueUserID SET @cursorQuery = 'DECLARE '+ @cursorName + 'CURSOR FAST_FORWARD FOR ' + @sqlQuery EXEC @cursorQuery ... The problem with this is that I can't do a FETCH NEXT FROM @cursorName since @cursorName is a char variable holding the cursor name, and not a cursor variable. So to enforce this unique name method the only option I have is to keep creating dynamic sql queries and exucting them. And this makes the sp a bitch to develop and maintain, and I'm guessing it doesn't make it very performant. So I moved on to my second idea: local cursor variables. The problem with this is that if I create a local cursor variable by executing a dynamic query, I can't extract it from the EXEC (or sp_executesql) context, as it offers no output variable. I guess my concrete questions are: Is it possible to execute a dynamic sql query and extract a (cursor) variable from it?Is it possible to populate a local cursor variable with a global cursor, by providing the global cursor's name?Can I create a local cursor variable for a dynamic sql query? How? Anybody sees another way arround this?Thanks in advance, Carlos
View Replies !
Using A &"dynamic Top&" Statement With A Cursor
Help please,Have a situation when converting from Oracle SP's to SQL SP's. The oldoracle cursor was roughly as followsCURSOR cur_rsStock ISselect*from(select StockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWID)whereROWNUM <= numQuantity;The closest I can get in MS SQL is as follows :declare cur_rsStockCURSOR forselect top @numQuantityStockRowId, CategoryIdfromSTOCKDISPOSABLEwhereSTOCKDEFID=numDefIdORDER BYSTOCKROWIDBut, SQL doesn't allow variables next to top. I know I can assign the wholeselect statement to a string and use exec to exec the string to get arecordset but how can I point a cursor to receive its output?i.e.set @strSQl = select top ' + @numQuantity + ' StockRowId, CategoryId.......exec @strSQLbut how do I dodeclare cur_rsStockset cur_rsStock = ( exec @strSQL)Flapper
View Replies !
Could Not Complete Cursor Operation Because The Set Options Have Changed Since The Cursor Was Declared.
I'm trying to implement a sp_MSforeachsp howvever when I call sp_MSforeach_worker I get the following error can you please explain this problem to me so I can over come the issue. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 31 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Procedure sp_MSforeach_worker, Line 32 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Procedure sp_MSforeach_worker, Line 153 Cursor is not open. here is the stored procedure: Alter PROCEDURE [dbo].[sp_MSforeachsp] @command1 nvarchar(2000) , @replacechar nchar(1) = N'?' , @command2 nvarchar(2000) = null , @command3 nvarchar(2000) = null , @whereand nvarchar(2000) = null , @precommand nvarchar(2000) = null , @postcommand nvarchar(2000) = null AS /* This procedure belongs in the "master" database so it is acessible to all databases */ /* This proc returns one or more rows for each stored procedure */ /* @precommand and @postcommand may be used to force a single result set via a temp table. */ declare @retval int if (@precommand is not null) EXECUTE(@precommand) /* Create the select */ EXECUTE(N'declare hCForEachTable cursor global for SELECT QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE = ''PROCEDURE'' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(SPECIFIC_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME)), ''IsMSShipped'') = 0 ' + @whereand) select @retval = @@error if (@retval = 0) EXECUTE @retval = [dbo].sp_MSforeach_worker @command1, @replacechar, @command2, @command3, 0 if (@retval = 0 and @postcommand is not null) EXECUTE(@postcommand) RETURN @retval GO example useage: EXEC sp_MSforeachsp @command1="PRINT '?' GRANT EXECUTE ON ? TO [superuser]" GO
View Replies !
Csv Generation
hi I want to generate excel file which contain table name , column name,datatype ,size how we can do in sql server is there any way pleases tell the steps
View Replies !
DDL Generation
I have a quick question regarding SQL Server Enterprise Manager. I'm looking at setting up a job to automatically create DDL for a user database. This will be done along with our normal nightly backup routine. I'm very familiar with using EM to create SQL scripts, but is there anyway to schedule this task? I've considered DTS and some type of scheduled package, but can't seem to find anything similar. I'm thinking I may need a custom task. Could someone please shed some light on the subject? If not from within EM, how about any third party tools? FYI - I already own the Embarcadero suite and am trying it out wwith that. Thank You. Anthony Robinson
View Replies !
SMK Generation
Server: SQL 2005 SP2 on Win 2003 Ent. SP1 A 3rd part app is requiring that I create a credential, whick in turn requires an SMK be set. When I try to create the credential, I get an error message indicating a decryption error. When I run the alter command to regenerate the key (without force) it throws an error indicating the key cannot be decrypted. According to a KB article I found, this may indicate that a key has never been generated. My question is, I have a number of production databases in this instance, including SQL Reporting Services. Except for the SRS DB's, all other user db's are simple db's that don't use encryption. If I run the Force command to generate the key, am I going to break anything? I'm really concerned about report servioces. Thanks.
View Replies !
Sql Code Generation
I need a tool to generate sql code of database including all data like "insert into table values()". Same as sql file in IBuySpy portal. How can I generate a file like this? I tried with enterprise manager but it doesn't generate insert statements and default values of some fileds lost. Can someone help me?
View Replies !
XML Tables Generation
Hi all,I am following the procedure where I generate XMLs of tables and put itin a DataSet and read that dataset back into another database to bewritten into the tables.Although I am VERY close to completion a small problem that I am facingis that the XMLs being generated have as the table name "table" and notthe actual names.Is this a known issue or am I amiss? and, does anyone have a solutionfor this?Thank you.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
XML Generation Issues
Hi guys,Apologies Simon for not making it clearer last time. What i am doing isreading a table from the SQL Server 2000 database and using:SQLDataSet.WriteXml(strFileName, XmlWriteMode.WriteSchema);to write to the DataSet. But I realised that when I do that the XML isgenerated correctly, the only error being that<xs:element name="Table"> comes in as the tablename instead of theactual table name.Any suggestions will be appreciated. Thank you.*** Sent via Developersdex http://www.developersdex.com ***
View Replies !
SQL Report Generation
HelloFor my client, I need to generate reports from the information storedin the database. The client has fixed format forms (on paper e.g. USCustoms forms etc).Will I need to redesign the forms in the application and then show theinformation?Another approach is to scan the forms as image and print theinformation on top of that image, so when it is printed , theinformation will be displayed at the right places.Is there any other way? How is the reporting done if the forms arepre-defined and the information is stored in a databaseThanks for your input
View Replies !
Generation Of Subscription
Hello all, I have a data driven subscription with the information about the file name/file extension/ path etc coming in from a database. The problem is that the subscription status after running tells me that things are done and there is no error but the file is not being generated in the specified directory and for that reason the file is not generated at all anywhere on the hard disk. can anybody please help. The information in the database for the report is as follows FileName : Test1 FileExtn : True Path = \ram\C$Reports Render_Format = PDF Username = Administrator Password = password Writemode = Overwrite All the fields are varchar(50) in size.
View Replies !
Surrogate Key Generation
Hi, I'm trying to use the SK script from Donald Farmers book but the code isn't accepted Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Dim CurrentKey As Integer Public Overrides Sub PreExecute() CurrentKey = CInt(Me.Variables.FILCodesSK) End Sub Public Overrides Sub Input_ProcessInputRow(ByVal Row As Input0Buffer) CurrentKey += 1 Row.SurrogateKey = CurrentKey End Sub End Class There is a problem with the use of the overrides on the Input_ProcessInputRow sub should this be renamed? Cheers, Al
View Replies !
Auto Key Generation
Hi, Can any one tell me how can I create auto number (similar feature to MS Access) i.e. autmatic increament by 1 in MS SQL 2005 (without using any script)
View Replies !
RDL Generation Problem
Hi, I am working with SQL Server 2005 Reporting Service from few days, though I am not expert, for some reason I have to run on field without having sound knowledge of RDL, but need your help, gys. I am using SQL Server Business Intelligence Development Studio to design report. Here is the procedure of my work to populate a RDL report I used a stored procedure for the DataSet of my RDL Then I drag and drop necessary field to my report layout. Put required parameters to preview tab and then run report. This is quite simple, I didn't face any problem with this process, even though the process may not correct, but working perfect. My problem is when the stored procedure returns multiple result set. The data tab only shows the first result set though the SP returns multiple result set, I have run the SP in the Query Analyzer. I don't want change my stored procedure. And please give me some suggestions about the best procedure to develop RDL report in real life. Please reply me ASAP, it€™s very urgent. Thank youTareqe
View Replies !
Join Cursor With Table Outside Of Cursor
part 1 Declare @SQLCMD varchar(5000) DECLARE @DBNAME VARCHAR (5000) DECLARE DBCur CURSOR FOR SELECT U_OB_DB FROM [@OB_TB04_COMPDATA] OPEN DBCur FETCH NEXT FROM DBCur INTO @DBNAME WHILE @@FETCH_STATUS = 0 BEGIN SELECT @SQLCMD = 'SELECT T0.CARDCODE, T0.U_OB_TID AS TRANSID, T0.DOCNUM AS INV_NO, ' + + 'T0.DOCDATE AS INV_DATE, T0.DOCTOTAL AS INV_AMT, T0.U_OB_DONO AS DONO ' + + 'FROM ' + @DBNAME + '.dbo.OINV T0 WHERE T0.U_OB_TID IS NOT NULL' EXEC(@SQLCMD) PRINT @SQLCMD FETCH NEXT FROM DBCur INTO @DBNAME END CLOSE DBCur DEALLOCATE DBCur Part 2 SELECT T4.U_OB_PCOMP AS PARENTCOMP, T0.CARDCODE, T0.CARDNAME, ISNULL(T0.U_OB_TID,'') AS TRANSID, T0.DOCNUM AS SONO, T0.DOCDATE AS SODATE, SUM(T1.QUANTITY) AS SOQTY, T0.DOCTOTAL - T0.TOTALEXPNS AS SO_AMT, T3.DOCNUM AS DONO, T3.DOCDATE AS DO_DATE, SUM(T2.QUANTITY) AS DOQTY, T3.DOCTOTAL - T3.TOTALEXPNS AS DO_AMT INTO #MAIN FROM ORDR T0 JOIN RDR1 T1 ON T0.DOCENTRY = T1.DOCENTRY LEFT JOIN DLN1 T2 ON T1.DOCENTRY = T2.BASEENTRY AND T1.LINENUM = T2.BASELINE AND T2.BASETYPE = T0.OBJTYPE LEFT JOIN ODLN T3 ON T2.DOCENTRY = T3.DOCENTRY LEFT JOIN OCRD T4 ON T0.CARDCODE = T4.CARDCODE WHERE ISNULL(T0.U_OB_TID,0) <> 0 GROUP BY T4.U_OB_PCOMP, T0.CARDCODE,T0.CARDNAME, T0.U_OB_TID, T0.DOCNUM, T0.DOCDATE, T3.DOCNUM, T3.DOCDATE, T0.DOCTOTAL, T3.DOCTOTAL, T3.TOTALEXPNS, T0.TOTALEXPNS my question is, how to join the part 1 n part 2? is there posibility?
View Replies !
Generation Of Sql For An Alter Column Etc
Hi.I have a database I need to supply something (I'm assuming a t-sql script..maybe something else is better) to update customer tables with.The operations include mostly changing varchar lengths, though a couple ofcolumns were renamed.I'd like to maybe figure out how to get Enterprise Manager or Query Analyzerto generate the scripts.I can't just send alter table scripts because I'm involving all sorts ofconstraints that have to be disabled/or dropped, the alter made, then havethem enabled/ or re-created.Basically I'm hoping to get the tools to do the rather large amount of workfor me. I'm targetting sql server 2000.Can someone make a knowledgeable suggestion?RegardsJeff Kish
View Replies !
Sequence Number Generation
Does anyone know an efficient method for generating a sequence number in the following form? Starting with 2 columns 1 2 ---- A X A Y B X B Y B Z C X I want to then generate a third column as follows: 1 2 3 ------- A X 1 A Y 2 B X 1 B Y 2 B Z 3 C X 1 The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations. Any ideas? Thanks.
View Replies !
Autonumber Or Unidue Id Generation
Hi, I am using a relation(table) which has a artificial key. I want to use this key as the primary key hence is unique. What datatype is associated with this attribute in MS SQL 2000. How can I generate unique id everytime I add a new record to this table ? Thanks -Sudhakar
View Replies !
Snapshot Generation Slooooow
We have setup the following replication topology: SQL Server 2005 SP1 Merge Replication (with Dynamic/Paramterized filters) Subscribers all running SQL Express Pull subscription Bi-directional changes allowed Problem - When the subscriber initiates dynamic snapshot generation, the process is taking 4-8 times longer, than when I create the partition and generate the snapshot through enterprise manager. Any suggestions?
View Replies !
Automated Generation Of Snapshots
I have a database which contains more than a hundred publications and the clients need a snapshot for each one. Is there a way of automating this process of creating a snapshot? Like a job or something.
View Replies !
Artificial Data Generation
I'm looking for some tool to help generate artificial data. Excel functions with macros would do. Ideally I would be able to define the attributes, the kind of distribution (normal and uniform at least) and ranges. Perhaps some simple mapping functions for the variables (linear for continuous attributes, injective for discrete values is fine). And Markov chains if possible. If not that's ok. I would like to generate XML because my end format is actually data in JScript, but having the cases generated inside Excel is fine. Any other tools besides Excel is fine too. Suggestions would be greatly appreciated. thank you, Gustavo
View Replies !
Slow Report Generation
I have developed several reports with selectable parameters. When the report is first requested three stored procedures are triggered and return the parameters (+2 min), following parameters being returned the default report is returned (+3 min), this time is unacceptable. Is there anything I can do to speed up the report generation? Any help here is greatly appreciated.
View Replies !
Cancelling A Report Generation
Hello all, Say I have a report that is running for a long time and I want to cancel it. Is there a way to cancel the report generation once we submit the Report Generation Button? If I close my Browser, Am I closing my session in the server side? If server is still churning out the old report and I open another browser to request the same report, I might easily crash the server if the requests for the same report keep growing? How do I handle or how does SSRs handle this? Thanks in advance to you all experts.. Phewa Taal
View Replies !
Data Model Generation
Hello Experts, I have been presented with one more challenge in MSRS. I hope this will not end up being like my other threads with no reponses. This is interesting My client is requesting a datamodel where the name of the columns keep changing. So, for this I had to start off with generating a table on the fly and populating it with the data. This was a tricky process (as this was a stand alone table with 145 cols) but we got there. So now the everytime the client calls this stored procedure it will drop the existing table, create a new one with new columns and populate the table.. For simplicity,lets say below is the table with one row PartID LocID 2008P3 2008P4 123XX A 12 20 Now when I generate the datamodel and deploy it and use it in report builder everything works great...Basic stuff This is the tricky part Now lets say my client wants to re run the process of creating new data and populating the table. As he is running this process in the next period his new table will look like this PartID LocID 2008P4 2008P5 123XX A 20 25 Notice the Column name changed, this is the reason I create the table on the fly to accoumadate the current period numbers. So now when you go back to report builder and If i were to create a new report, first of all I will not see the new column as I have to recreate the whole datamodel. So my question is, is there any thing I can do for report builder to see the new columns. or Can i automate the whole regeneration of datamodel and deployment so that client can just recreate and deploy the datamodel on the fly? Please let me know if you guys can think of any workaournds on this... Appreciate your help in advance
View Replies !
Report Generation And Notification
Hi, We need to generate the reports in a file share location and notify the Users about the location. Since the subscriptions support either File share or Email notification, we have configured file share mode in our subscriptions. We tried writing a custom C# component to send emails to the Users but getting the status of reports generation is difficult. Is there a way to notify the users after generating the reports in a file share? Subash
View Replies !
Report Generation Image
Is there any way to change the image "Report is being generated" to something else or at least change the location of it. I have a report that is very long and our users have to scroll down to see that. They think the report is frozen when in all reality it is still generating. It is postioning in the center of the page and I want it to position at the top.
View Replies !
Disarming IDENTITY Generation
Ok, suppose I have created a new table with an Identity column, but I want to load the table from an existing table and I can€™t let SQL Ev reset the Identity values. In SQL Server I can turn off Identity generation using SET IDENTITY_INSERT biblio.dbo.authors OFF Is there an equivalent in SQL Ev? My next question is (if this does not work), can I use ALTER TABLE to disarm the Identity value and re-arm it after the load?
View Replies !
Sequence Number Generation
Hi, I have an error table that is to be updated by more than one package. There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table. When more than one package runs parallely, conflict occurs in generating the sequence number. How can this be handled?
View Replies !
|
TRACKER
