SQL Reporting Services - Dynamic SQL Stored Procedures
May 7, 2004
Hi, all:
Kind of new to reporting services. I've been playing around with SQL Reporting Services and was wondering if anyone knows how to populate the fields from a dataset in the Report Designer from a stored procedure that uses dynamic SQL. I've had success with non-dynamic stored procedures and inline queries, but am unable to generate fields when the sp contains dynamic SQL. I've tried defining the fields manually, but when I execute the report I receive errors that the fields are undefined.
Any help would be greatly appreciated!
Thanks
View 2 Replies
ADVERTISEMENT
Jun 8, 2015
How to find out last created stored procedures in sql.
View 5 Replies
View Related
Aug 11, 2005
Hi all,
I want to use 2 stored procedures in one report, but I could not find what is the way of doing this. I searched this subject on the intrernet, and I found some solutions. However, they used stored procedures in one stored procedure and used in one report. This is not the thing which I want to do, I want only do using 2 stored procedures in one report at the same time.
Thanks, for now...
View 1 Replies
View Related
Mar 18, 2004
REF: http://msdn.microsoft.com/netframework/default.aspx?pull=/library/en-us/dndotnet/html/storedprocsnetdev2.asp
It seems dynamic SQL is just as efficient as stored procedures in terms SQL Server caching. Any comments?
View 3 Replies
View Related
Jun 9, 1999
I open a recordset using a string to call a stored procedure. In the stored
procedure I create a temporary table and use the exec function to fill the
table. I then select * the table and drop the temporary table. The problem
is the recordset will not even open. My script keeps getting a "The
operation requested by the application is not allowed if the object is
closed. " error when points to the line after rs.Open is called. This line
checks for rs.EOF. When I Response.Write the SQL statement and paste this
into an ISQL session I get the output I am looking for. The only difference
is above the records I get a "# row(s) affected" which maybe from the
Insert. Does anyone know what is wrong here?
David Stanek
View 1 Replies
View Related
Jun 12, 2006
Hello, I'm trying to create a Stored Procedure who receives the table name as a parameter, then uses a cursor to obtain every column name and then builds a string like SELECT col1, col2, ... from TABLE
In fact that would be the same as SELECT * FROM table; but I can't do this, because I'll be using this stored procedure to loop through many tables that has different quantity of columns with a DTS, and if a specify the *, then the DTS wouldn't let me do the select with tables with different quantity of fields.
Could you help me please, because my code isn't working:
CREATE PROCEDURE dbo.stp_Test
(
@tablename AS VARCHAR(50)
)
AS
DECLARE @columnname varchar(50)
DECLARE @strsql Nvarchar(500)
DECLARE @query varchar(4000)
SET NOCOUNT ON
DECLARE c1 CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = @tablename
OPEN c1
FETCH NEXT FROM c1 INTO @columnname
WHILE @@fetch_status = 0
BEGIN
IF (@strsql is null)
BEGIN
SET @strsql=@columnname
END
ELSE
BEGIN
SET @strsql = @strsql + ',' + @columnname
END
FETCH NEXT FROM c1 INTO @columnname
END
CLOSE c1
DEALLOCATE c1
SELECT @query = 'SELECT ' + @strsql + ' FROM ' + @tablename
EXEC @query
SET NOCOUNT OFF
GO
View 4 Replies
View Related
Aug 1, 2006
Okay, I have sort of a peculiar permissions question I am wondering if someone can help me with. I'm suspect there's a simple answer, but I'm unaware of it. Basically, here's the scenario...
I have a CLR stored procedure which does some dynamic SQL building based on values sent in via XML. It's a CLR stored procedure using XML because I want to build a parameterized statement (to guard against SQL Injection) based on a flexible number of parameters which are basically passed in the XML.
The dynamic SQL ends up reading from a table I'll call TableX and I actually discovered an (understandable) quirk with security.
Basically, the connection context is impersonating a low-privilaged Windows account ("UserX") coming from a .NET application. UserX has no permission to the table referenced in the dynamic SQL and because of the dyanmic nature of the query, the stored procedure apparently adopts the security context of UserX. Naturally, this throws a security exception saying UserX has no SELECT permission on TableX.
Now, I can give UserX read permission to the table in question to get things running, but one of the points of using stored procedures is to defer security to the procedure level vs. configuration for tables or columns.
So in striving toward my ideal of security at the procedure level, my question is what is the best way to allow minimum privilege in this case?
I thought about having the internals of the CLR stored procedure run under a different (low-privalaged) security context, but I am wondering if there's an alternate configuration that may use the same connection, and be as secure, but simpler.
View 8 Replies
View Related
Oct 7, 2006
Hi there,
I would like to know how to create Dynamic stored procedure which defines TableName as a Variable and return all fields from this Table.
And also how to Dynamicly create a sp_GetNameByID (for instance)
using vars only.
Thanks
It would be very helpfull to me if you could give links of Dynamic SQL tutorials from which i can learn.
View 1 Replies
View Related
Feb 13, 2008
Hi!
I have an integration code write in T-SQL. It´s a TRIGGER that when some data is INSERTED on a specific table, verify the first caracter of a nvarchar on the column named "idCli", and depending on their value, call one specific stored procedure that will execute some data modifications to fit on other table on a diferent database.
Each client of mine can have only one table that start the trigger on APP1, but can have many instances of SQL for different codes.
Until now, what we do is:
Find how many different databases (and their names) a specific client have to APP2 and write a specific stored procedure for each database, using the names (that are always different...). We use a template of course, but this don't change the fact the we must correct many times the database name on the different stored procedures.
This increse the time and chance of errors on installing the system.
The first way we think for solve this question is using dinamic sql, like this code:
Code Snippet
CREATE TRIGGER T01
ON [dbo].[table1]
FOR INSERT, UPDATE
AS
-- some code that put values in @v1 and @V2...
IF @v1 = 1
EXEC fct ('DB1..Tabela1', @V2)
ELSE
EXEC fct ('DB2..Tabela1', @V2)
GO
CREATE PROCEDURE fct (@table_name nvarchar(50), @valor int)
AS
EXEC ('INSERT INTO '+@table_name+' (valor) VALUES ('+@valor+')')
GO
This type of code has the advantege (we think) to permit us change only the TRIGGER, and use always the same number of procedures on install.
Is there any security problem to do this type of code?
Even if the @table_name and @valor are determined by the program?
In case of yes, how can I do something like this, or, if this is not possible, how can I "automate" the creation of the procedures with a variable number of choices (like 2 different tables for client A, 5 for client B, etc)?
Thanks in advance
View 8 Replies
View Related
Mar 23, 2006
I want to use parameters within a stored procedure to generate dynamic columns using SUSER_SNAME as the name for the column that I want to dynaically select (e.g. Select @SUSER_SNAME, First, Last, City FROM MyTable). I have been able to successfully use parameters in the WHERE clause within a stored procedure but haven't been able to find a way to use parameters for column names let alone to tie the parameter value back to SUSER_SNAME.
Any insight would be greatly appreciated!
View 4 Replies
View Related
Oct 17, 2007
Hello,
I was in a confusion that is Stored Procedures are really fast ? I have a .NET application where I am using Stored Procedures. But recently I cam through this link http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx which describes Stored Procedures are bad and it won't give any performance difference. What is the truth ? Will it give good performance that passing query from the application ?
Please make it clear
View 8 Replies
View Related
Mar 12, 2008
Im reviewing my stored procedures for a new application and got to thinking about protecting against sql injection. I think im pretty safe since im using stored procedures and none of them use any 'exec' commands within them, but im not sure.
I was reading this article, and again all the examples that list a stored procedure, have an 'exec' command somewhere that is the culprit. So, in my case lets say I was doing something like this:
Im generally using regularexpression validation controls on the client side of the application and limiting the max length of the input there as well.
Am I safe, or do I need further input checking within the procedure ?
Code Snippet
CREATE PROCEDURE [dbo].[get_Uploads]
@app varchar(50)
--Init variables
SET @error_number = 0
BEGIN TRY
SELECT [Logid],[Filename],[Label],[UploadDate],[App]
FROM UploadLog au
WHERE [App]=@app
END TRY
BEGIN CATCH
SET @error_number = -2
END CATCH
View 1 Replies
View Related
Apr 22, 2007
Hi
I've a stored procedure returns a table with dynamic SQL.
Columns of the returned table is dynamic.
How to make a report for this table?
Thanks.
View 2 Replies
View Related
Jun 18, 2007
Hi,
I have a dynamic SQL statement like
'Select * from Table name where month=@month' ..Exec(SQLStatement)
Dynamic SQL does not recognizes @month which is a reporting services multi select parameter...An suggestions???
View 6 Replies
View Related
Apr 1, 2008
Hi,
i want to display images on the Reporting services, Images resides on the same machine but we want to set the image path dynamically from dataset fields. please suggest me best possible approach. this is windows application and all the data with image path is in a single dataset.
thanks,
Nitin Sood
View 5 Replies
View Related
May 17, 2005
Hi all!
If I have some mdx I'm using in reporting services like this:
select
{ [TimeByMinute].[All TimeByMinute].[2005].[May].[1] : [TimeByMinute].[All TimeByMinute].[2005].[May].[6] } on columns,
{A_list_of_measures } on rows
from ACD_Calls
The column names are unique to the day of month- which means when I use a table to display this in reporting services, the field names change dynamically when the date parameters change which means the table stops working.
I'll post this in reporting services too but I thought maybe I could alias the column names in mdx shielding the reporting services table from changes in dates.
What do you think? Would a matrix be more flexible in this case?
Richard
View 7 Replies
View Related
Jun 29, 2007
I have a requirement to create dynamic reports for my client.
once i create these reports then the user will choose columns of there choice.
so the columns may belong to multiple tables.
Now the report should get generated with the layout etc. is it possible.
since our project is totally on the webserver(webbased.)
please if you can provide me with any links with dynamic report creation wizards.
and also we only use Stored procedures via database.
which is best is writing the entire queries right behind the layouit or calling the entire logic via Stored procedure. i am a bit confused. this is my first project working on reports itself.
Thank you all for the helpful information.
View 1 Replies
View Related
Apr 17, 2008
Hello,
I have a matrix with a title column. My matrix has a column that is expanded in 2 with data changes
as followings:
MY TITLE1 MY TITLE2
YEAR 2007 YEAR 2008 } matrix
matrix Column column } matrix
How can I change de title (MY TITLE2) differently according to the changing value of the dynamic data column ?
My title2 has to be located above the YEAR 2008 and has to be different from MY TITLE 1.
Thank you for your help.
Best Regards,
Hirondelle.
View 2 Replies
View Related
May 21, 2015
I have a SP like this:
ALTER PROCEDURE [dbo].[ReportGateWay]
(
@ISO bigint= 0,
@Gateway bigint= 0
)
AS
BEGIN
DECLARE @SQL nvarchar(max)
[Code] ....
I need to invoke this in SSRS. The problem is that when on creating dataset for this, I get an error which read:
You must have atleast one field for your dataset
What can be done in this case?
Here is the SQL Fiddle:Â [URL] ....
View 16 Replies
View Related
Dec 19, 2005
Hi all,
I am currently working on some reports where I need to display images dynamically.
there is one total field whose value ranges between 0 and 100 %. amd I need to display different images depending on the range of the value.
for example,
if the range is between
80% - 100% smily face.
60% - 80% normal face
40% - 60% sad face.
Can any one help in approaching this.
Initially I worked with only static embeded images.
It also helps me in solving another problem.
I need to change the company logo (header image) as per the company in the common report template provided by the provider dynamically.
Thanks in advance.
waiting for an early help as it is very urgent for me.
Regards,
Ramesh P
View 5 Replies
View Related
Apr 22, 2015
I am using sql 2012 SSRS.
My requirement  : I have Database which has got 100 tables.I need to create SSRS report which display top 10 records from the table within that database.
User will select The table name as parameter in the report. the number of column in each table will vary .
I am using the below statement in stored proc, and calling same from ssrs report
exec(
'select top 10 * from ['+@datbasename+'] .[app].'+@tablename+'')
View 6 Replies
View Related
Nov 10, 2000
Currently I have a backend Sql Server 7 Database. I am creating views of the data. The views are then made available using ODBC as read only data to Access 97, so that users can produce there own reports.
I have created a number of stored procedures which produce commonly used reports. How can I connect them to Access so that result data is available to them.
Is there any other way of connecting to backend stored procedures to frontend applications. I would prefer not to install Sqlserver Desktop edition.
View 2 Replies
View Related
Aug 5, 2015
I am attempting to create a subscription that will send a report whose name would have been formatted to the following: ReportName_MMDDYYYY.
I know this can be done by creating a Windows File Share subscription type, but in this case, I would need to use E-Mail.
In this scenario, the user will receive the email and included, the file formatted as described above.
Is there a way to get it done ?
View 3 Replies
View Related
Jun 15, 2007
Hi every one,
Could you please anyone tell me how to have static and dynamic columns in a matrix?
We can add static columns by right clicking detail text box and add column. But it will repeat for each column group. I need static columns in the dynamic columns level itself.
Thanks in advance!
View 3 Replies
View Related
Sep 9, 2015
I need to put a dynamic table (the amount of rows I control) into a static container (with a fixed size).
View 2 Replies
View Related
Aug 21, 2015
I am trying to use SSRS to allow users to select any one or several columns from a set of cascading parameters which will then do a "data dump" of the contents of the selected columns for "export".I know how to do cascading parameters, but I am having problems coming up with a way of using the parameters to dynamically build a matrix which has as its columns the list selected in the parameters. I need the user to choose WHICH columns will be displayed.  There could be 1, 2, 3, up to 50 columns.
View 2 Replies
View Related
Feb 14, 2008
Will the width property of a column be able to be set at run-time (i.e. ability to set the width of a column to an expression) in SQL Reporting Services 2008? One problem I have seen is the inability to set the width of columns in a report when you you have several columns whose order is determined based on parameters.
Â
Lets say I have 2 columns in a report and two fields from the database which will populate those columns: 1 field that is varchar(5) and one which is varchar(50). If I do not know which column each field will go to until runtime how am I supposed to adjust the column width? Letting the column width be set by an expression would solve everything as I could pass the width in to the report. As it stands now, I would have to set each column's width to accomodate a field of type varchar(50) in order to avoid wrapping of data.
Â
You could say just create 2 reports, one for each case, but what if I had 15 fields? 20?how to dynamically set the width of a column? Will the width of a column be able to be set at run-time in SQL Server Reporting Services 2008 via expression or some other means?
View 39 Replies
View Related
Nov 19, 2015
We run 2014 enterprise. I have 3 series being charted on a line (with dots) chart. I know what the series are but to ssrs they are dynamic. One of them really needs a secondary y axis because its a percentage. Is there a way to do this? From what I read if I was to somehow make them less dynamic, then ssrs could treat this one differently. Also, what if I want data point values to show only on this plot but not the other two?
View 4 Replies
View Related
Oct 8, 2015
I am trying to generate a report using  SSRS ( SQL server 2012 & Visual studio 2013).  when I try to execute stored procedures I am getting below error. Below are the details of enviroment.
1. SQl serverr 2012
2.Oracle 12c client
3.Oracle Server 12c
4.Visual studio 2013
[code]....
View 3 Replies
View Related
Sep 21, 2015
I have created an SSRS report containing a bar chart. The Y-axis contains some application names which are displayed based on a certain criteria. Hence sometimes there are only 2 values in y-axis while sometimes it may range upto 20 items. I have tried a lot but have been unsuccessful in getting a dynamic y-axis which changes with the number of items present.
View 2 Replies
View Related
Aug 18, 2015
I am working on SSRS column chart which has one series that is quantity
I have added a stripline based on Quantity value > 500
Now I want my column to have 2 colors.
for portion above quantity 500 = RED
column part below quantity 500 = GREEN
How that can be done
View 11 Replies
View Related
Oct 16, 2015
I have the following result set that I am putting into a SSRS 2012 Matrix:
RowNum RowLabel Val Title
1.00 Advance 10000.0000 TestTitle1
4.00 List Price 18.0000 TestTitle1
5.00 Units Shipped 20000 TestTitle1
6.00 Return Units -8125 TestTitle1
7.00 Net Sales Units 11875 TestTitle1
8.00 Return %Â 45.0%/10.0%Â TestTitle1
*Note: The data in Val for 'Return %' is a text field - informational only and necessary.
When setting it up such that the Columns are Title, and the Rows are RowLabel, I get the following:
TestTitle1
View 3 Replies
View Related
Sep 30, 2015
I have a user table with Label and value fields where i would like to control the display of the text boxes based on the values selected in my user table.Can we adjust the text box positions dynamically based on the user table values.
Ex: Table
Label1 Field1 Label2 Â Field2 Â Label3 Field3
ID    100              Dept  Sales
Report Design :
Label1: ID Â Â Field1:100
Label2: Â Â Â Â Â Field2:
Label3:Dept  Field3:Sales
Expected Result :
Label1: ID Â Â Field1:100
Label3:Dept  Field3:Sales
In my table i don't have values for Label 2 & Field 2 , can we adjust the spacing conditionally to be utilized by Label3 & Field3 in SSRS.
Note : Above mentioned data is just for an example and in my actual report i can have more than 3 columns and report is looking ugly with all the spacing if i don't have data in all the fields.
View 9 Replies
View Related
