Need Info About Dynamic Reporting (Read Problem Statement, Inside)
Hi All!
I have a specific requirement.
I have to generate a report on the fly.
The display fields, parameters and sort conditions would be user specified at run time in a ASP.NET web form. There will be a superset of the display, filter and sort fields out of which the user cans select one or more.
From the web form, i am taking these three parts as three strings and sending them as parameters to a Stored Procedure. The Stored Procedure will read each string, and identify what are the individual fields and generates the result accordingly.
So here my requirement is that Reporting Services must read the Stored Procedure, create a dataset and even create the User Interface all at run-time as we do not know what fields are displayed at design time. The Headers for each field come from the Stored Procedure. I have to show the report based on what are the fields in the Stored procedure at that instance of time.
I hope i have explained very clearly.
I would be grateful for your contributions.
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
How To Read Text Inside HTML Files
Hi, I just want to know how to search text/record inside the Html files. I had one column in my database named €˜Path€™ I saved the html file names in this column and the physical files in a folder. Full Text search is enabled on this column. Whenever I try to search it returns nothing. Just need one small example to know how it works, what functions used to search inside the files and how. Thanks Navi
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
How To Read Data From Remote Server Inside A Transaction?
Hello, everyone: I have a local transaction, BEGIN TRAN INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE COMMIT View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query: Server: Msg 8501, Level 16, State 1, Line 12 MSDTC on server 'PROD' is unavailable. Server: Msg 7391, Level 16, State 1, Line 12 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c]. It looks like remote server is not available inside the local transaction. How to handle that? Thanks ZYT
View Replies !
Dynamic SQL && Parameter Inside A SP Not Working
When I run the query below on my DB, it gives the following error: Server: Msg 8114, Level 16, State 5, Procedure partstlist ...Error converting data type varchar to float. Only after I replace the " + @d + " section with a float numnber such as 39020.5 does it work fineIt's reported that my temporary table named #tbl doesn't exist within the scope of the environment that the EXEC(UTE) command operates.Someone said that I couldn't combine dynamic SQL and temporary tables or table variables...and that I would have to use a global temp table "##tbl" or a permanent table. So here I have appearantly a "DYNAMiC SQL & TEMPORARY TABLE" problem. That's sure. But I don't have much knowledge and experience with SQL tables and have difficulty forming a global temp table "##tbl" or a permanent table.and I have to employ the datetime variable dynamically @d inside the Dynamic SQL string. So what kind of a code should I use here to properly combine Dynamic SQL string with my dynamic datetime variable? I'd be grateful if you could post your code suggestions so that I can try them on my DB .. alter PROCEDURE partslist@no INT,@dt DATETiMEASDECLARE @s VARCHAR(5000)DECLARE @d floatSET @d = CONVERT (float, @dt2)create TABLE #tbl(pageindex int IDENTITY(1,1) PRIMARY KEY , prt_name varchar(50), prt_country varchar(50) , prt_date smalldatetime, partID int , prt_cat varchar(50),prt_product_type nvarchar(10), producer_company nvarchar(50), producer_city nvarchar(50), prt_released DATETIME, dvalue float, datevalue varchar(26) )BEGiNSET ANSI_WARNINGS OFFSET ROWCOUNT @noSELECT @s = 'INSERT INTO #tbl SELECT prt_name, Countries.Val AS ''prt_country'' , prt_date, partID , partsCategories.Val AS ''prt_cat'' , partsProductTypes.Val AS ''prt_product_type'' , producers.producer_company AS ''producer_company'' , producers.producer_city AS ''producer_city'' , prt_released , CONVERT (float, prt_released) , CONVERT (varchar(26) , prt_released, 109) FROM producer_parts JOIN partsProductTypes ON (producer_parts.prt_product_type = partsProductTypes.ID) JOIN partsCategories ON (producer_parts.prt_cat = partsCategories.ID)JOIN Countries ON (producer_parts.prt_country = Countries.ID) JOIN producers ON (producer_parts.producerID = producers.producerID) WHERE prt_visible = 1 AND CONVERT (float, prt_released) > ' + @d + ' AND CONVERT (float, prt_released) < 39025.5ORDER BY prt_released DESC SELECT * FROM #tbl'EXEC (@s)SET ROWCOUNT 0ENDGO partslist 10 , '10.10.2006 10:10:10' -- This is just to test the SP. This code may not be syntatically correct, it's a re-edited version of my code which works fine in my DB.
View Replies !
Dynamic Sql Inside Function And Return Value
Is it possible to write dynamic sql on scalar function and assign the value to return value? I like some thing like below but it is not working... Thanks ______________________________________________________________________ set @sql = 'select @CallLegKey= min(calllegkey) as CallLegKey from rt_'+@platform+'_CallLegs where datediff(day,convert(datetime, CallEndTime) ,'''+cast(@today as varchar(20))+''') = '+cast(@cutoff as varchar(5)) exec @sql return @CallLegKey
View Replies !
Using SQL To Feed Title And Meta Info To Dynamic Webpages.
I have been told that it is possible to place META tag information in a database so that each page can have unique Meta Tag data when using Master Pages. I already know how to use override the default using the .cs page, but I am building a site that will have dynamic pages created on the fly. Is it possible to put the Meta Tag information in the database as well and then have it added to the page as it is created? And how would the search engines look at this? For instance I am building a site for a non-profit organization I am associated with, Their site will have several dynamic ASPX pages that are build off a database, but which we would like the search engines to index using page specific Meta and other data Tags. Can and How do I do this.Thanks Ed
View Replies !
Need Info On Tools For MSSQL Reporting Etc.
Hi All, Am looking for inputs on tools for MSSQL. Some of my requirements are 1) Database Audits or to audit database settings and Logins 2) Performance Monitoring 3) Query Tuning 4) Data Compare 5) Load Testing - to see how an sql instance/server reacts to higher load/volume. I am looking for reviews/web-links from any first-hand experience wherever possible. TIA, Warm Regards, Ranjit.
View Replies !
Using A While Inside A Select Statement
Hi All, Can we use the while loop inside a select statement? Meaning, something like this: Code Block SELECT DATE, WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23 (SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) ) FROM SC_DATEDIMENSION_TABLE What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right. Hope the question is clear. Thanks a lot. Mannu.
View Replies !
Select Statement Inside UDf
iam trying to rerieve a certain value from one table and i want to use that vaue inside a UDF iam usinf a table valued function as i have to retireve no of values Can i do something like this to retrieve the value SET @Value=Select Value from Table WHERE xyz='some no.' as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
If Statement Inside Sql Query??
Hello, I have an if statement for one of my columns in my query. I want to write the if statement as a part of my select statement. How would I do that. Do to a couple of rules I am not allowed to write a stored procedure for this. I could use the "decode function" but I have something like this: if column1 = '1' or column2 = '2' then select "Yes" etc.. I tried doing select decode (column1 or column2, , , ) but it doesn't work. Any ideas?
View Replies !
Installing More Than One Reporting Services Inside
Hi, I have two question regarding the Reporting Services security and configuration: 1. Is the configuring 3-4 separate Reporting Services installations common scenario where each Reporting Services is dedicated to specific group of applications? Two SSRS are behind firewall and two on the web farm. How this configuration is good and how it looks to you? 2. When having 3-4 SSRS, is it possible to centralise access to these SSRS (maybe trough sharepoint or something)? Let's say that a user can login to all of those 3-4 SSRS using the same application and the same login which is not Windows Domain Account? Thanks Alex
View Replies !
SQL 2005 Reporting Services Security Info
We have 2005 Reporting Services fully functional - Dev, QA and prod (farm). I have at least 5 distinct business group trees in my structure, who have inherited or modified security based upon NT Users and AD groups. Now, we want to replace a major group (<domain_name>Domain Users) with a controlled group. I cannot find anyway to report or map the folders that a specific NT User and AD group has rights. Cannot it be done?
View Replies !
How To Read SQL Statement In T-SQL Statement Task?
hi all, after convert from DTS to SSIS, how can i open the SQL statement? because i only saw a line in the properties of the task. i open the DTSX in vs2005, but i can open to view the SQL statement. in sql 2000 just double click on the SQL Task, then will popup a dialog form to show the SQL task. please help. thanks a lot
View Replies !
Counter Inside Select Statement?
Hi, can you add a counter inside a select statement to get a unique id line of the rows? In my forum i have a page that displays a users past posts in the forum, these are first sorted according to their topicID and then they are sorted after creation date. What i want is in the select statement is to create a counter to get a new numeric order. This is the normal way: SELECT id, post, comment, created FROM forum_posts WHERE (topicID = @topicID) ... some more where/order by statements This is what i want: DECLARE @tempCounter bigintSET @tempCounter = 0SELECT @tempCounter, id, post, comment, created FROM forum_posts WHERE (topicID = @topicID)... some more where/order by statements and at the end.. (SELECT @tempCounter = @tempCounter + 1) Anyone know if this can be done?
View Replies !
Calling Variable Inside T-SQL Statement
Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =) I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName> SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: XXXXXXXXXXXXX -- Create date: 10/4/2007 -- Description: This proc is designed to pull all CSA -- part sales from XXXXXX business system and upload them -- into the local XXXXXXXX Database for commission reporting -- =============================================CREATE proc usp_CSAPartsSalesUpdate@date1 int, @date2 int As INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount ) SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(<CompanyName>, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmtFROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''' + @date1 + ''' And (PMINVHST.HInvDate)<=''' + @date2 + ''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))') In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September. This is the error message I get when I try to create the proc: Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17 Incorrect syntax near '+'. ~~~ Thanks All~~~
View Replies !
Automatic Rollback Inside Using Statement?
If I start a transaction using the following approach ... using (SqlTransaction trans = destConn.BeginTransaction()) { ...do some transfers using SqlBulkCopy }...will an automatic rollback occur in case of unhandled errors inside the scope of the using statement?
View Replies !
If Statement Inside A Stored Procedure!!!!
hi all, i'm wondering if i can use one stored procedure in too cases, this is the senario: i have stored procedure for admin and another one for user, they have the same select everything is the same except that in admin SP i have where @admin = admin and for user i have where @user = user if there a way to use if and else to make this happen this is what i did so far: CREATE PROCEDURE [test] @admin INT, @user INT, @indexType INT as if @indexType = 1 begin SELECT * FROM table WHERE something IN (SELECT * FROM anothertable where admin = @admin) end else begin SELECT * FROM table WHERE user = @user end GO any suggestion will be very helpful thanks
View Replies !
'Case' Statement Inside 'Where' Clause
Hi I've been trying to put a simple case statement into my 'where' clause but having no luck, is there another way to do the following? DECLARE @searchCriteria Int SET @searchCriteria = 2 SELECT column1, column2 FROM TABLE WHERE CASE @searchCriteria WHEN 1 THEN (column3 = 1000100) WHEN 2 THEN (column3 = 1000101) END CASE ...cheers
View Replies !
Declare Inside Select Statement?
I have a need to execute a cursor inside a select statment, but I'm having problems figuring this out. The reason this need to be inside a select statement is that I am inserting the cursor logic into a query expression in PeopleSoft Query. So! Here's the statement that works: ====================== DECLARE @fixeddate datetime DECLARE @CVG_ELECT char(1) DECLARE @Effdt datetime DECLARE EFFDTS CURSOR FOR SELECT Z.EFFDT, COVERAGE_ELECT FROM PS_LIFE_ADD_BEN Z WHERE Z.EMPLID = '1000' AND Z.EFFDT <= GETDATE() AND Z.PLAN_TYPE = '20' ORDER BY Z.EFFDT DESC OPEN EFFDTS FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT WHILE @@FETCH_STATUS = 0 BEGIN if @CVG_ELECT <> 'E' break ELSE SET @fixeddate = @Effdt FETCH NEXT FROM EFFDTS INTO @Effdt, @CVG_ELECT END CLOSE EFFDTS DEALLOCATE EFFDTS PRINT @fixeddate ====================== If I execute this in SQL Query Analyzer it gives me the data I am looking for. However, if I try to paste this into a select statement, it goes boom (actually, it says "Incorrect syntax near the keyword 'DECLARE'.", but you get the idea). Is it possible to encapsulate this inside a select statement?
View Replies !
Select Case Inside Sql Statement ?
Code: function findingcinemaid(nameofthecinema) findcinemaid = "select cinemasid from cinemas" &_ " where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'" set cinemaidfound = objconndb.execute (findcinemaid) end function select case foreachcinema case 0 cinemaname = "ONE UTAMA" findingcinemaid(cinemaname) case 1 cinemaname = "MINES" findingcinemaid(cinemaname) case 2 cinemaname = "SEREMBAN 2" findingcinemaid(cinemaname) case 3 cinemaname = "KINTA CITY" findingcinemaid(cinemaname) case 4 cinemaname = "BUKIT RAJA" findingcinemaid(cinemaname) case 5 cinemaname = "TEBRAU CITY" findingcinemaid(cinemaname) case 6 cinemaname = "SUNWAY PYRAMID" findingcinemaid(cinemaname) case 7 cinemaname = "SURIA KLCC" findingcinemaid(cinemaname) end select any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it
View Replies !
Trying To Select Info With If Exists Statement With No Avail
Im trying to select rows on the following criteria My app has a user that can have x jobs, each job has related entries which are marked has unread or read. I need to return any jobs that contain any unread entries also I need to return other jobs that have no entries marked as read. I am able to return the following: Comment UnReadMy Job 164 Comment UnReadFor Cam 166 Comment ReadThe Job 157 Comment ReadThird 159 Comment ReadMy Job 164 On Site Visit ReadMy newest job for log test 167 but as you can see jobid 164 appears in both groups and I need it to be one or the other I tried using the if exists statement and was only able to return one group either unread or read, not both. I've tried everything but I'm new and I figure that theres got to be a more elegant way. Heres my sql: CREATE PROCEDURE spGetJobsByUnreadAndReadByUserID @UserID INT AS BEGIN IF EXISTS(SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact, Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who FROM Master_Jobs INNER JOIN Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND Job_Assignments.UserID = Users.UserID WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND BackUp_Read = 'UnRead') BEGIN SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact, Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who FROM Master_Jobs INNER JOIN Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND Job_Assignments.UserID = Users.UserID WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND BackUp_Read = 'UnRead' ORDER BY BackUp_Read ASC END ELSE BEGIN SELECT DISTINCT Master_Jobs.JobID, Profiles.ProfileDescriptor, Backup_UserNotes.BackUp_Read, Master_Jobs.Job_Title, Master_Jobs.Contact, Master_Jobs.Due_Date, Master_Jobs.Due_Time, Master_Jobs.Next_Action, Master_Jobs.By_Who FROM Master_Jobs INNER JOIN Note ON Master_Jobs.JobID = Note.FK_JobID INNER JOIN Backup_UserNotes ON Note.NoteID = Backup_UserNotes.BackUp_NoteID INNER JOIN User_Notes ON Note.NoteID = User_Notes.FK_UN_NoteID INNER JOIN Job_Assignments ON Master_Jobs.JobID = Job_Assignments.FK_Master_JobID INNER JOIN Profiles ON Master_Jobs.FK_ProfileID = Profiles.ProfileID INNER JOIN Users ON Backup_UserNotes.BackUp_UserID = Users.UserID AND User_Notes.FK_UN_UserID = Users.UserID AND Job_Assignments.UserID = Users.UserID WHERE Users.UserID = @UserID AND-- Note.FK_UserID = User_Notes.FK_UN_UserID AND BackUp_Read = 'Read' ORDER BY BackUp_Read ASC END END GO Thanks again
View Replies !
Database Logon Info To Reporting Services At Runtime
In our asp.net application I want to Incorporate SQL Server Reporting Services. In that I should be able to switch database info at runtime. how to pass Database logon info to reporting services at runtime in VB.NET. To understand it better we have this function for crystal where we can pass the database connection info at runtime, please find the attached text file. We would like to achieve some thing like that in reporting services. code used in crystal: Private Function SetReportConnection(ByRef aReport As CrystalDecisions.CrystalReports.Engine.ReportDocument, ByVal server$, ByVal database$, ByVal ID$, ByVal password$) As CrystalDecisions.Shared.TableLogOnInfo Dim logonInfo As New CrysShared.TableLogOnInfo Dim connInfo As New CrysShared.ConnectionInfo Dim table As Crystal.Table Dim SubRep As New Crystal.ReportDocument Dim sr As Object Try SetReportConnection = Nothing For Each table In aReport.Database.Tables ' Get the TableLogOnInfo object. logonInfo = table.LogOnInfo logonInfo.ConnectionInfo.ServerName = server logonInfo.ConnectionInfo.DatabaseName = database logonInfo.ConnectionInfo.UserID = ID logonInfo.ConnectionInfo.Password = password table.ApplyLogOnInfo(logonInfo) Next table For Each sr In aReport.ReportDefinition.ReportObjects If TypeOf sr Is Crystal.SubreportObject Then SubRep = sr For Each table In SubRep.Database.Tables logonInfo = table.LogOnInfo logonInfo.ConnectionInfo.ServerName = server logonInfo.ConnectionInfo.DatabaseName = database logonInfo.ConnectionInfo.UserID = ID logonInfo.ConnectionInfo.Password = password table.ApplyLogOnInfo(logonInfo) Next SubRep = Nothing End If Next Catch ex As Exception End Try End Function
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 !
Unable To Execute Job Inside Sproc From Reporting Services
We have a request to build a report based on user input from an excel spreadsheet. We have a SSIS package that imports the data from Excel. This is run by a sql server agent job. Our stored procedure executes this job and runs this whole process just fine but when we execute the stored procedure from reporting services we get errors. Has anyone done this type of thing before and do you have any working solutions for how to get this reporting methodology to function? Thanks!
View Replies !
Installing More Than One Reporting Services Inside Organisation And Security?
Hi,I have two questions regarding the Reporting Services security and configuration: 1. Is the configuring 3-4 separate Reporting Services installations common scenario where each Reporting Services is dedicated to specific group of applications? Two SSRS are behind firewall and two on the web farm. How this configuration is good and how it looks to you? 2. When having 3-4 SSRS, is it possible to centralise access to these SSRS (maybe trough sharepoint or something)? Let's say that a user can login to all of those 3-4 SSRS using the same application and the same login which is not Windows Domain Account? Thanks Alex
View Replies !
Hello , How To Handle Error Inside A Reporting Data Processing Extension..
I need help about this , What i've done so far is a data processing extension that gets its data from a WS , I use it as a datasource for a report , That report will be displayed withing an ASPX page containing a reportviewer control that displays that report , The problem is catching errors that occurs withing the data processing extension .. , When i debugged the extension i saw that after throwing an exception the code ends up inside the "cancel()" event inside the IDBcommand implementation... In all examples for a data processing extension i could find online they always have a comment saying the cancel is not implemented and throw a NewUnsupportedExtension().. What happens is i get some uninformative , non-userfriendly error from the reporting services , something like "an error occurred during the report processing(rssomehing)" How should i go about handling those exception that happened inside the data processing extension? I've tried catching the error inside the event of the reportviewercontrol - reporterror but it seems that event don't fire when those errors are generated.. Any help on how to approach this would be highly welcome
View Replies !
How To Setup User Security For Reporting Services Used Inside ASP.NET Web Applications?
I'm working on a project that requires integration of SQL Server Reporting Services with ASP.NET 3.0 Web Applications. I'm working on Visual Studio 2005, with SQL Server 2005 on an XP development workstation. SQL Server, Reporting Services, and IIS are all running on my local machine. I'm trying to prototype two approaches, one using the Report Viewer control, and the second using the Reporting Services Web Service. I have the two examples setup in projects in Visual Studio. The sample reports and data sources work fine in Visual Studio. I can access the report using the Reporting Services URL like this: http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fBTT_BDS_DEV%2fCustomers; Report works fine. My problem is, that when I try and access the report using the Report Viewer inside an ASP.NET page or from the Web Serivce hooked up inside an ASP.NET Page I get a security errors. I have chosen Windows Security for the Datasource, and ASP.NET pages. In the case of the Web Service, I'm passing in my local domain user name as the credentials. I'm prototyping this on my local workstation, but I need to design this to be used on our corporate Intranet using Windows Security. My questions are: 1. How do I need to setup users on my local development workstation to get this to work. 2. How should I plan for user security for enterprise deployment, i.e. using Reporting Services inside a large ASP.NET Web Application? 3. Can anybody give me some links to some good developer type working examples of doing this. I've looked but have not found the answers to the "how do I setup users" part of the question specifcally related to ASP.NET apps? Below is the code example of the Web Services example app I'm working with which came out of a book I have on Reporting Services. This example compiles and seems like it would work but doesn't. Also following are a few of the error messages I get when experimenting with the example apps: Errors: 1. The permissions granted to user 'LocalMachineNameASPNET' are insufficient for performing this operation. (rsAccessDenied) 2. System.Web.Services.Protocols.SoapException was unhandled by user code Message="System.Web.Services.Protocols.SoapException: The permissions granted to user 'LocalMachineName\ASPNET' are insufficient for performing this operation. ---> Microsoft.ReportingServices.Diagnostics.Utilities.AccessDeniedException: The permissions granted to user 'WCRBUSCNC2830B\ASPNET' are insufficient for performing this operation. at Microsoft.ReportingServices.Library.RSService._GetReportParameterDefinitionFromCatalog(CatalogItemContext reportContext, String historyID, Boolean forRendering, Guid& reportID, Int32& executionOption, String& savedParametersXml, ReportSnapshot& compiledDefinition, ReportSnapshot& snapshotData, Guid& linkID, DateTime& historyOrSnapshotDate, Byte[]& secDesc) at Microsoft.ReportingServices.Library.GetDataForExecutionAction._GetDataForExecution(CatalogItemContext reportContext, ClientRequest session, String historyID, DataSourcePromptCollection& prompts, ExecutionSettingEnum& execSetting, DateTime& snapshotExecutionDate, ReportSnapshot& snapshotData, Int32& pageCount, Boolean& hasDocMap, PageSettings& reportPageSettings) at Microsoft.ReportingServices.Library.GetDataForExecutionAction.ExecuteStep(CatalogItemContext reportContext, ClientRequest session, DataSourcePromptCollection& prompts, ExecutionSettingEnum& execSetting, DateTime& executionDateTime, ReportSnapshot& snapshotData, Int32& pageCount, Boolean& hasDocMap, PageSettings& reportPageSettings) at Microsoft.ReportingServices.Library.CreateNewSessionAction.Save() at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo) --- End of inner exception stack trace --- at Microsoft.ReportingServices.WebServer.ReportExecution2005Impl.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo) at Microsoft.ReportingServices.WebServer.ReportExecutionService.LoadReport(String Report, String HistoryID, ExecutionInfo& executionInfo)" Source="System.Web.Services" Actor="http://localhost/ReportServer/ReportExecution2005.asmx" Lang="" Node="http://localhost/ReportServer/ReportExecution2005.asmx" Role="" StackTrace: at System.Web.Services.Protocols.SoapHttpClientProtocol.ReadResponse(SoapClientMessage message, WebResponse response, Stream responseStream, Boolean asyncCall) at System.Web.Services.Protocols.SoapHttpClientProtocol.Invoke(String methodName, Object[] parameters) at WebReportTester.localhost.ReportExecutionService.LoadReport(String Report, String HistoryID) in C:Documents and SettingsconbcxMy DocumentsVisual Studio 2005ProjectsBTT_BDS_DEVWebReportTesterWeb ReferenceslocalhostReference.cs:line 242 at WebReportTester._Default.btnExecuteReport_Click(Object sender, EventArgs e) in C:Documents and SettingsconbcxMy DocumentsVisual Studio 2005ProjectsBTT_BDS_DEVWebReportTesterDefault.aspx.cs:line 82 at System.Web.UI.WebControls.Button.OnClick(EventArgs e) at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) Web Service Code Example: protected void btnExecuteReport_Click(object sender, EventArgs e) { byte[] report; //Create an instance of the Reporting Services Web Reference localhost.ReportExecutionService rsv = new localhost.ReportExecutionService(); //Create the credentials that will be used when accessing Reporting Services //This must be a logon that has rights to the Customers Report rsv.Credentials = System.Net.CredentialCache.DefaultCredentials; rsv.PreAuthenticate = true; //The Reporting Services virtual path to the report. string reportPath = @"/ReportFolder/Customers"; //The rendering format for the report string reportFormat = "HTML4.0"; //The devInfo string tells the report viewer how to display with the report StringBuilder deviceInfoBuilder = new StringBuilder(); deviceInfoBuilder.Append(@"<DeviceInfo>"); deviceInfoBuilder.Append(@"<Toolbar>"); deviceInfoBuilder.Append(@"False"); deviceInfoBuilder.Append(@"</Toolbar>"); deviceInfoBuilder.Append(@"<Parameters>"); deviceInfoBuilder.Append(@"False"); deviceInfoBuilder.Append(@"</Parameters>"); deviceInfoBuilder.Append(@"<DocMap>"); deviceInfoBuilder.Append(@"True"); deviceInfoBuilder.Append(@"</DocMap>"); deviceInfoBuilder.Append(@"<Zoom>"); deviceInfoBuilder.Append(@"100"); deviceInfoBuilder.Append(@"</Zoom>"); deviceInfoBuilder.Append(@"</DeviceInfo>"); string deviceInfo = deviceInfoBuilder.ToString(); //Create an array of the values for the report parameters localhost.ParameterValue[] parameters = new localhost.ParameterValue[1]; localhost.ParameterValue parameterValue = new localhost.ParameterValue(); parameterValue.Name = "@WTRKCustomerCode"; parameterValue.Value = "B34186"; parameters[0] = parameterValue; //Create variables for the remainder of the parameters string historyId = string.Empty; string credentials = string.Empty; string showHideToggle = string.Empty; string extension = string.Empty; string mimeType = string.Empty; string encoding = string.Empty; localhost.Warning[] warnings; localhost.ParameterValue[] reportHistoryParameters; string[] streamIds; localhost.ExecutionInfo execInfo = new WebReportTester.localhost.ExecutionInfo(); localhost.ExecutionHeader execHeader = new WebReportTester.localhost.ExecutionHeader(); rsv.ExecutionHeaderValue = execHeader; execInfo = rsv.LoadReport(reportPath, null); rsv.SetExecutionParameters(parameters, "en-us"); try { //Execute the Report report = rsv.Render(reportFormat, deviceInfo, out extension, out mimeType, out encoding, out warnings, out streamIds); //Flush the pending response Response.Clear(); //Set the HTTP Headers for a PDF response. HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "text/html"; //Filename is the default filename displayed //if the user does a save as. HttpContext.Current.Response.AppendHeader("Content-Disposition", "Customers.htm"); //Send the byte array containing the report as a binary response. HttpContext.Current.Response.BinaryWrite(report); HttpContext.Current.Response.End(); } catch (Exception ex) { if(ex.Message != "Thread was being aborted.") { HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "text/html"; StringBuilder errorMessageBuilder = new StringBuilder(); errorMessageBuilder.Append(@"<HTML>"); errorMessageBuilder.Append(@"<BODY>"); errorMessageBuilder.Append(@"<H1>"); errorMessageBuilder.Append(@"Error"); errorMessageBuilder.Append(@"</H1>"); errorMessageBuilder.Append(@"<BR>"); errorMessageBuilder.Append(@"<BR>"); errorMessageBuilder.Append(ex.Message); errorMessageBuilder.Append(@"</BODY>"); errorMessageBuilder.Append(@"</HTML>"); string errorMessage = errorMessageBuilder.ToString(); HttpContext.Current.Response.Write(@errorMessage); HttpContext.Current.Response.End(); } } } Any direction here related to best practices on setting up users for code use with ASP.NET applications would be greatly appreciated... Thanks in advance...
View Replies !
Help: Table Name Displaying Twice In SQL Statement Inside SQL Server Enterprise Manager
For some reason whenever I look at the SQL statement of a particular table, the table name displays twice.For example,SELECT * FROM State StateEven when I execute this statement, it still returns the correct results. It does this for all tables in this particular database. I also check another database and thoses display the table names in the SQL statements correctly. Does anyone know why the table name would display twice in a table inside of a particular database?
View Replies !
Writing Reports/Extracting Data/Header Info/Reporting Services (2 Questions Really)
I have a program where I have to do a weekly data upload of approx 1,500 records. I've written the sql, but need to know how I can add static header text to the SQL statement.the text I need in the header is Reportname(Static string) + #rows (Rowcount of the sql, int) + department (String static) I'm planning on writing it and saving it as a package, then schedule it to run every Friday at 5:00. But I have been reading a little on Reporting Services and wonder if people think it' might be the way to go? Is it a whole new area of security issues? Or is it worth installing and learning? I will have more and more reports I need to write that will increase in complexity, and want to know if I want to keep writing querys by hand, or use reporting services. Thanks in advance! Dan
View Replies !
SQL Server 2005 Reporting Svcs - How To Use Dataset 1 Info To Drive Nested Table Select
I've only been doing sql 2005 for a couple of months with minimal training so there's a lot I don't know. What I'm trying to do is load a nested table (industry & customer totals) based on a value from the table it's nested in. This is the relationship of the data. I have at the highest group, an industry code, then a customer, then the part and then the fiscal year. What I'm trying to accomplish is to get a group total (footer) for the (1) industry code and (2) the customer code. This footer would contain the fiscal years (ascending) and their monthly totals. I would like to take the industry code from table one and pass it to the select statement in the dataset that feeds the nested table. I've read this is not possible to load a dataset field into a parm but I've seen where some people know how to work around this. If you reply, please explain in simple terms. Thanks! industry Customer Year OCT NOV DEC 001 - Signposts M12345 Part 1 2006 5 6 2 2007 0 3 1 Part 2 2006 4 3 0 2007 1 0 7 Customer M12345 totals 2006 9 9 2 2007 1 3 8 M45678 Part 3 2007 8 4 7 2008 3 4 8 Part 4 2006 3 8 7 2007 5 6 6 Customer M45678 totals 2006 3 8 7 2007 13 10 13 2008 3 4 8 Industry 001 totals 2006 12 17 9 2007 14 13 21 2008 3 4 8
View Replies !
&"Dynamic&" Sorting Inside A Procedure
Hello,I need to have a stored procedure, which performs sorting. Something likethis:CREATE PROCEDURE procname@sortby varchar(30)ASBEGINSELECT some, columnsFROM some_tableORDER BY @sortbyEND(of course, i know this won't work, but it gives the idea of what i mean)Is there a possibility to write a procedure which behaves like that? It isimportant for me not to have multiple procedures just for different sortingcriteria...Thanks,Mike
View Replies !
No Response To Code And Query Analyser Cannot Read Statement
hi have a problem ... I tried to test my statement in QA as it returns no dataset ,it gives me empty columns in return,I though it will return all the columns and some records of (lmeyer) as the current user ,who logged in on windows auth,but it return only the columns,is that means my query analyser cannot read the login1 ,column which has the username as (lmeyer) select * from tstudents where (login1='@param1') if not what could me wrong in my code ,because I get no response to the sql server Public Function login(ByVal login1 as string) as dataset Dim myconnection as new sqlconnection("server=G103-TT03;database=CampusLANDB;Trusted_Connection=yes") dim mycommand as new sqlcommand ("Select * from tStudents Where login1 = @param1",myconnection) mycommand.parameters.add(new SQLClient.SqlParameter("@param1",login1)) dim DS as new dataset() try myconnection.open dim adpt as new sqldataadapter adpt.selectcommand = mycommand adpt.fill(DS,"tStudents") catch ex as exception throw new exception(ex.message) return nothing finally myconnection.close end try try mydatagrid.datasource=DS.Tables("tStudents") mydatagrid.databind() catch ex as exception errorlabel.text=ex.message end try return DS End Function thankx in advance pamela mkosana
View Replies !
Dynamic SQL Statement
I am trying to write a dynamic sql insert statement in c#, but given this is my first run at creating sql statements dynamically I am having issues. My reason for creating the sql statement dynamically is because I do not want to insert any items that are null. Will I have to have a separate string for each section of the statement? For example:sql1 = "Insert Into Table [test]";sql += "(Column1, Column2, Column3," ;sql2 = "Values" ;sql2 += "(field1, field2, field3"; and then a third section if I were to use parameters. My thinking here is how do you append to a string in numerous locations? I will populate the sql strings first with those columns and fields that I know will not be null, and then use if statements to add to each sql string if the field in question != null. in other words, little help.
View Replies !
Dynamic Sql Statement
I would like to know if it is possible to pass a table name to the from section of a sql select statement? Something like: Declare @paramTable as nvarchar(10) Set @paramTable = TableName Select firstname, surname from @paramTable Is this possible?
View Replies !
Dynamic Sql Statement
I need to create a where clause dynamically reading the values from a temp table. Example: select * from #tmp_keyword k_id keyword 1 like "%DBA%" 2 like "%MSSQL%" 3 like
View Replies !
Dynamic Sql Statement
I need to create a where clause dynamically reading the values from a temp table. Example: select * from #tmp_keyword k_id keyword 1 like "%DBA%" 2 like "%sql server%" 3 like "%7%" I would like to generate a where clause like the one below: declare @st_sql (500) select @st_sql = 'where keyword like "%DBA%" and keyword like "%sql server%" and keyword like "%7%"' Can anyone help me the way to do it? number of rows in temp table varies anywhere from 1 to 15. (I know what I am trying to do is not a good sql coding practice.) Thanks
View Replies !
Dynamic SQL Statement Help
Hi, I try to get the dynamic insert statement script. See the below statement I'm getting syntax error. How can change this right way script?. select * into pubs.dbo.employee_temp from pubs.dbo.employee where emP_id<>emP_id Declare @cmd varchar(8000) set @cmd =N'insert into employee_temp(emp_id,fname,minit,lname)'+char(13)+ 'values '+'('+select ''''+emp_id+''''+','+''''+fname+''''+','+''''+mini t+''''+','+''''+lname+''''+')' from pubs.dbo.employee EXECUTE sp_executesql @cmd
View Replies !
|
TRACKER
