Parameterized Reports
Jan 7, 2008My report needs to show Top (1-100) violators.... how do i put in this parameter?
ADVERTISEMENT
Parameterized Queries Running Slower Than Non-parameterized Queries
Jul 20, 2005HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
View 1 Replies View RelatedParameterized AND IN (@Var
Nov 12, 2004I am trying to create a list of values for an IN clause. How does one do it with parameterized queries?
If I use var = "val','val2','val3"
then the ' are replaced with '' - so how?
Comparison Of SQL Server Reporting Services To Access Reports, Crystal Reports, Cognos Or Other Options
Nov 5, 2007Hello SQL Server Experts, Data Analysts, and Report Writers et al:
re: Reporting Options with SQL Server
I wanted to propose an offshoot to the pryor thread:
Would anyone take a stab at comparing Access Reports, Crystal Reports,
Cognos or other options to all the Reporting Services and its components offered as part ofSQL Server, especially as to extracting data from SQL Server into a report format?
I guess this is a far as capabilites, ease of use, limitations, and especially formatting
or presentation of the end report product?
Thank you to all, and I hope this is a beneficial discussion to others.
Hal1490
Hal9000
Reports Usage Frequency For All The Existing Reports On SSRS 2000
Mar 18, 2008I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?
I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.
Any help is appreciated
Reports Usage Frequency For All The Existing Reports On SSRS 2000
Mar 18, 2008I have an already published application running several MS SQL Server 2000 Reporting Services report.
I need a way to find from either the reporting services log or the application server (IIS) logs or windows log
to know the frequency of each report being used.
Based on this info, the business needs to know which reports are being used and to what extend?
How can I acheive this?
I have already got the IIS logs and it did not give the required info.
I have looked into the Reporting Services logs but it does not provide the info either.
Any help is appreciated
Parameterized Query
Jan 29, 2008When I try to add a parameter called findby to the order by part of a query like this:
dim q1 as string="select store+' '+customer+' '+left(customer,len(customer))" q1=q1+"+replicate('.',30-len(customer))+' '+cdate as a" q1=q1+" from tblcustomers" q1=q1+" where store='65' and customer like @lookfor" 'eventually want @findby where this says customer q1=q1+" order by @findby"
with command1.parameters: .Add(New SQLParameter("@lookfor", textbox1.text+"%")) .Add(New SQLParameter("@findby", dropdownlist2.text)) 'dropdownlist2.text="customer" which is the name of a column end with
I get this server error:
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Are parameters not good for names of things in a query but ok for values of what those names represent? If not, what am I doing wrong?
Thank you very much.
Parameterized Or Array With This SQL?
Jun 2, 2008I have two CheckBoxList controls. One CheckBoxList is a group of area codes as they apply to our customers, and the second CheckBoxList is a group of categories of those customers. The code below works fine with either CheckBoxList as a standalone (this code applies to the Area Codes selection), but what I need is the VB code to combine the choices a user makes in both CheckBoxLists. Is this where parameterized SQL comes into play? Or can I/should I use an array statement to combine both CheckBoxList choices? Sometimes a user will select nothing in one CBL and a few choices in the other, or vice versa, or a handful of choices in both CBLs, so that they might want only customers in, say two area codes and then only the selected categories of those area codes. Need help on this one, thanks...Protected Sub btn_CustomerSearchCombine_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_CustomerSearchCombine.Click Dim ACSelections As Boolean = False Dim ACItem As ListItem For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSelections = True End If Next If ACSelections = True Then Dim ACSqlString As String ACSqlString = "SELECT Customers.CustomerID, Customers.CustomerName, Customers.CategoryID, Customers.EstHours, Customers.Locality, Categories.Category FROM Customers INNER JOIN Categories ON Customers.CategoryID = Categories.CategoryID WHERE " For Each ACItem In cbl_CustomerSearchAREA.Items If ACItem.Selected Then ACSqlString &= "Customers.AreaCodeID = '" & ACItem.Value & "' OR " End If Next ACSqlString = Left(ACSqlString, Len(ACSqlString) - 4) ACSqlString &= "ORDER By Customers.CustomerName" sql_CustomerSearchGrid.SelectCommand = ACSqlString End IfEnd Sub
View 7 Replies View RelatedParameterized Query?
Feb 5, 2004Can someone please help me with this parameterized query? Its is not working.
builder.Append("select datepart(dd, datetime) as 'Day', datepart(hh,datetime) as 'Hour', count(*) as 'Count' ");
builder.Append("from @table_name with (nolock) ");
builder.Append("where datetime > @date_time ");
builder.Append("group by datepart(dd, datetime), datepart(hh, datetime) ");
builder.Append("order by datepart(dd, datetime), datepart(hh, datetime");
dateTime = DateTime.Now.ToLongTimeString() + " " + DateTime.Now.ToLongTimeString();
cmd.CommandType = CommandType.Text;
cmd.CommandText = builder.ToString();
cmd.Parameters.Add("@table_name", tableName);
cmd.Parameters.Add("@date_time", dateTime);
Parameterized Where Clause
Jul 5, 2005Hello,I have an add stored procedure in Yukon (would work in 2000 too), where I select the ID from the table to make sure that it doesn't already have the data. So it looks like:create procedure ....set transaction isolation level serializablebegin transactiondeclare @ID intselect @ID = rowid from tblBusinessInformation where Name = @Name and Rules = @Rulesif ( @ID is NULL ) begin insert into tblBusinessInformation (..) values (@Name, @Rules) endcommit transactionThe problem is the values could be:Name RulesNULL 'Test''Test' NULL'Test' 'Test'When one of the values was NULL, it would never select the ID, unless I changed it to "where Name is @Name", and then it worked, because where Name is NULL, which is correct in SQL; so how do I allow for both; I can use the CLR, but would like to avoid rewriting the proc if possible, and I thought that was to work...Thanks.
View 3 Replies View RelatedParameterized Viewc
Oct 4, 2006can someone plz explain to me wats a parameterized view or may be a link somewhere on the web or a few examples of parameterized view. Thanks
View 4 Replies View RelatedParameterized SP In WHERE Clause Of Another SP
Jun 6, 2006I've got this SP:CREATE PROCEDUREEWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear char(11)ASSELECTContactIDFROMdbo.EWF_tblCustom_CompanyProfileWHERESchoolYear = @prmSchoolYearI'd like to be able to reference that in the where clause of anotherSP. Is that possible?I'd like to end up with something like this:CREATE PROCEDUREMyNewProc@prmSchoolYear2 char(11)ASSELECTContactID, SomeOtherFieldsFROMtblContactWHEREContactID IN (exec EWF_spCustom_AddProfiles_CompanyYear@prmSchoolYear2)How would I make that happen?If this isn't possible, what else might I try?Thanks much for any pointers.JeremyPS: I accidentally crossposted this in another group(http://tinyurl.com/gksq4) thinking it was this one. Sorry for that.
View 1 Replies View RelatedLink To Parameterized URl
Mar 19, 2008If I bring up a report in the report server (http://reportserver/Reports/Pages/.... and I set the parameters to see the information I want to see and say to myself, hey, my boss would really like to see this, how would I go about sending him the link to the report with the parameters already set. Ideally when I view the report, the parameters would show in the URL automatically as other reporting products do, however, SSRS does not provide this. I do not even see a function in the report designer to display the link on the report itself so I can cut and paste from it. Anyone know how to do this without having to custom craft a URL or set a one time subscription?
Thanks,
Parameterized Queries With RDA
Sep 25, 2006Hello..
Is there a way to use parameterized queries with RDA method? I write a program for WinCE5.0 and when I submit a query I use hardcoded date format and this causes problems in different systems.There's a solution for this?
Thanks in advance.
Parameterized Queries
Feb 15, 2007Just getting started using SSce and having a few problems
What I want to do is something like this...
Dim Code As Integer
Dim Description As String = txtDescription.Text.Trim
Dim conn As SqlCeConnection = ConnectToLocalDatabase()
Dim ssql As New System.Text.StringBuilder
ssql.AppendLine("INSERT INTO T_Titles (Description)")
ssql.AppendLine("VALUES(@Description)")
ssql.AppendLine("SELECT @Code = @@IDENTITY")
Dim cmd As New SqlCeCommand(ssql.ToString, conn)
Dim sqlCode As New SqlCeParameter("@Code", 0)
sqlCode.Direction = ParameterDirection.InputOutput
cmd.Parameters.Add(sqlCode)
cmd.Parameters.Add(New SqlCeParameter("@Description", Description))
cmd.ExecuteNonQuery()
Code = CInt(sqlCode.Value)
**********************************************************************
The above code doesnt work. Firstly I am not sure if I can execute the two statements in one go. Secondly, I am not sure if output parameters are supported.
I have been working with SQL Server since 6.5 but have always used sprocs and am feeling a little lost here without them. Any help getting started would be greatly appreciated.
Thanks
Parameterized Query In T-SQL
Mar 11, 2008Hi,
I am new to Parameterize query ...
and i want to use above for inserting XML data and to retrive the same as it contains some special character so by using string it is changed.
plz give example or link
thanks
Remove All Reports ( Not Data Sources) From Reports Server
Jan 23, 2007Hi does anyone know how to do the above with out going through reportserver url?
Preferably by using a cmd tool ? such rs.exe
or through the backend in the reportserver DB?
Thanks
Dave
Help Please!! (Parameterized Query Problem)
Feb 4, 2007can anyone show me where i've done wrong in my coding? because i can't seems to find the error. I've looked through forums and google but just can't understand what they are on about as i'm kind of a beginner. Please help me...thanks in advance...(i dont have any stored pocedure, just using a connectionstring called connectionstringnews)HERES THE ERRORParameterized Query '(@newsid nvarchar(4000),@author nvarchar(5),@date
datetime,@arti' expects parameter @newsid, which was not supplied.
Description:
An unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the error and
where it originated in the code. Exception Details:
System.Data.SqlClient.SqlException: Parameterized Query '(@newsid
nvarchar(4000),@author nvarchar(5),@date datetime,@arti' expects parameter
@newsid, which was not supplied.Source Error:
Line 37: txtMessage.Text)Line 38: con.Open()Line 39: cmd.ExecuteNonQuery()Line 40: con.Close()Line 41: 1 Imports System.Web.Configuration
2 Imports System.Data.SqlClient
3 Partial Class News_Articles_Default
4 Inherits System.Web.UI.Page
5
6 Protected Sub btnPost_Click( _
7 ByVal sender As Object, _
8 ByVal e As System.EventArgs) _
9 Handles btnPost.Click
10
11 Dim cs As String
12 cs = WebConfigurationManager _
13 .ConnectionStrings("ConnectionStringNews") _
14 .ConnectionString
15 Dim insertNews As String
16 insertNews = "INSERT news " _
17 + "(newsid, author, date, articles) " _
18 + "VALUES(@newsid, @author, @date, @articles);"
19
20 Dim con As SqlConnection
21 con = New SqlConnection(cs)
22 Dim cmd As SqlCommand
23 cmd = New SqlCommand(insertNews, con)
24
25 Dim newsid As String
26 newsid = Request.QueryString("news")
27
28 cmd.CommandText = insertNews
29 cmd.Parameters.Clear()
30 cmd.Parameters.AddWithValue("newsid", _
31 newsid)
32 cmd.Parameters.AddWithValue("author", _
33 txtAuthor.Text)
34 cmd.Parameters.AddWithValue("date", _
35 DateTime.Now)
36 cmd.Parameters.AddWithValue("articles", _
37 txtMessage.Text)
38 con.Open()
39 cmd.ExecuteNonQuery()
40 con.Close()
41
42 End Sub
43
44
45 End Class
46
If/then Parameterized Queries Using Tableadapter
Jun 29, 2007Hey fellas. Here's my situation. I have two textboxes where the user enters a "start" date and an "end" date. I want to search a table to find records who's "expired" column date is between those two dates provided by the user. The tricky part is, if the user just puts a start date in but no end date, I want it to search from whatever start date the user entered to the future and beyond. Essentially, I think I'm looking for a SQL statement along the lines of:
SELECT Request.RequestID, Request.URL, ActionProvider.Name, Request.CurrentStageID, Request.Decision, Request.SubmissionDate,
Request.ExpirationDate
FROM Request INNER JOIN
RequestSpecificActionProvider ON Request.RequestID = RequestSpecificActionProvider.RequestID INNER JOIN
ActionProvider ON RequestSpecificActionProvider.ActionProviderID = ActionProvider.ActionProviderID INNER JOIN
RoleActionProvider ON ActionProvider.ActionProviderID = RoleActionProvider.ActionProviderID INNER JOIN
Role ON RoleActionProvider.RoleID = Role.RoleID
WHERE
CASE WHEN @BeginDate is not null AND @BeginDate <> ''
THEN Request.ExpirationDate > @BeginDate
END
AND
CASE WHEN @EndDate is not null AND @EndDate <> ''
THEN Request.ExpirationDate > @EndDate
END
AND (Role.Description = 'Requestor')
I realize my code isn't correct and there's still a floating "AND" out there I would have to put some logic around. Anyway, how do I do this? Do I need to build three separate queries in my tableadapter (one for if both dates are provided, one for if start date is provided, one for if end date is provided) and build the logic in my application code or can I tackle it with SQL? If I can tackle it with SQL, where have I gone astray? I'm currently getting the error: "Error in WHERE clause near '>'. Unable to parse query text."
Thanks for the help everyone!
Debugging Parameterized Queries
Aug 22, 2007How would I debug such a query.
I have a sqlCommand to which I add several parameters for an insert statement.
if the statement fails, for some reason, I would like to copy the final sql with all values inserted as text and use this in e.g. TOAD to see where the error is coming from. Is this possible?
How To Use Parameterized Queries With IN Clause
Feb 5, 2008Hi,
I need to use parameters with the IN clause in a SQL statement like:
select * from tableX where field IN (1,2,3,4)
I don't know how to do that.
I'm using SQLServer and OleDB.
Thanks for your help.
Parameterized Query Question
Feb 6, 2008Hi,
Below are two methods o passing a parameterized query, are these the same, or is one open to sql injection attacks more than the other?Option 1 - through code behindDim testDataSource As New SqlDataSource()testDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionStringName").ToString()testDataSource.UpdateCommandType = SqlDataSourceCommandType.TexttestDataSource.InsertCommand = "INSERT INTO test(id) VALUES (@id1)"testDataSource.InsertParameters.Add("@id1", TextBox1.Text)
Option 2: through sqldatasource on page and control parameters<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringName %>"InsertCommand="INSERT INTO test(id) VALUES (@id1)" <InsertParameters><asp:ControlParameter ControlID="TextBox1" Name="id1" Type="Int32" /></InsertParameters></asp:SqlDataSource>
Feedback would be great, thanks!
Parameterized Query Question
Dec 8, 2003I am trying to use the following SQL query to return a set of values:SELECT id, submit_date, company_name, request_type, status
FROM tblRequestForms
WHERE request_type IN (@RequestType) AND status IN (@Status)
ORDER BY id ASCI have tried passing an array of string values to both @RequestType and @Status, but It does not work. Is there any way to pass multiple values like this using parameters?
Thanks,
Aaron
Parameterized Stored Proc ?
Feb 22, 2002I want to create a stored proc that might or might not recieve a value like below.
create proc cmine @vdate datetime = null
as
select * from table where mydate > @vdate
However if the value is null,( no value was supplied) I would not want to use the where clause. Should (or can you) use an if statement to check and see if no value was given to change the where clause?
Thanks
Using DTS Parameterized Query 'IN' Where Clause
Apr 30, 2002I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface.
I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
EXEC @hr = sp_OASetProperty @oPKG, 'GlobalVariables("outIDlist").Value', @outIDlist
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ...
FROM ...
WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure.
But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);).
The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
Alan
Parameterized SSIS Packeges
Aug 24, 2006How i can create parameterized sql query .This is my basic idea to implement.
select * from dimemployee
where name = ?
or
exec proc sp_para_employee ?
how can i pass expocit parameter (that means when i run the package that time it should ask me or when i pass the parameter should execute ,satisfy atleast one xondition ).rf any one have script please provide me because i tried all angles using books on line and other resorces.
Parameterized Filters On Publication???
Oct 20, 2006Does anyone know if you can use any other parameters in the row filters for merge replication besides the functions SUSER_NAME() and HOST_NAME()?
I would like to create a publication for a couple thousand mobile databases to replicate with one SQL Database but filter what data they get based on some parameters. Do I have to hard code WHERE statements into static filters and create a publication for every user (seems a little ridiculous)?
Is there a proper way to do this using the SUSER_NAME and give each user a different connection name that will filter data properly?
Thanks,
Patrick Kafka
Parameterized Configuratiion In SSIS
Mar 7, 2007Hello,
Does anyone know if its possible to have multiple package configuations in a SSIS package. That you can control via a parameter in some way?
For example, one configuration for each country.
Thankful for any help!
//Patrick
Q: Parameterized EXECUTE( @lSqlStr )?
Feb 21, 2008All,
I execute dynamic SQL with a return parameter like this:
SET @lSqlStr = ' SELECT @oRsltCount = COUNT(*) FROM foo';
EXECUTE sp_executesql @lSqlStr, N'@oRsltCount int out' @oRsltCount out;
The sp_executesql @stmt parameter is type nVarChar. Occationally, the size of @lSqlStr may be greater than 4000 bytes, which exceed the maximum allowable size of type nVarchar.
So, I may execute dynamic SQL like this:
EXECUTE ( @lSqlStr );
where the type of the argument is VarChar(MAX), and size of @lSqlStr may be up to 2^31 -- but cannot parameterize (or bind, if you will) the query result.
How can I execute dynamic query with string size greater than 4k bytes AND bind the query result?
-Kevin
Using Dateadd In A Parameterized Query
Apr 15, 2008I'm creating a data flow task to export a set of records that were created within a specific time frame. The date offsets I'm using are read into user variables that are of type Int32. I have an OLEDB source connected to a SQL Server 2005 database using the following query to get the records I want:
select * from claim where date_created > dateadd(day,?,getdate)
I've mapped Parameter0 to my offset variable, which has a value of -7. When I hit OK to close out of the OLE DB Source editor, I get a message saying "Argument data type datetime is invalid for argument 2 of dateadd function." I can't figure out why it keeps talling me this even though the variable I'm passing in is an integer, not a datetime. I've done a lot of searching and found some instances of other people having this problem, but so far no answers. I could just go ahead and try to create an equivalent query using datediff or something, but I'd like to know what's going on here. Is this a bug in Integration Services itself, or is there another explanation?
List Parameterized Dates
Apr 4, 2007Hello,
For my report I want to have the days listed as a dropdown with simple dates during the month (3/27/2007), but there may be many records for that date as it is using the time as well. Unfortunately I don't even know where to begin
Ultimately the user will be given a dropdown listbox showing the simple dates only for the current month and on selection user will view only that days report. How do I basically treat each day as a group in the parameter?
Thanks!
Can A Connection Manager Be Parameterized?
Sep 21, 2006
Can you create one connection manager that accepts the Server name and database name at run time and constantly reuses the server and datbase name at runtime when executing one SSIS solution with Multiple packages.
Creating A Table With A Parameterized Name
Apr 26, 2008Hi,
I'm a Transact-SQL newbie, and I would like to create a procedure that creates a table with a parameterized name. I wrote the following, which I thought should do the job:
CREATE PROCEDURE procedure_AddFund
@FundName varchar(10)
AS
BEGIN
CREATE TABLE @FundName (Date smalldatetime, Price money)
END
GO
But I am getting the following error in Management Studio:
Msg 102, Level 15, State 1, Procedure procedure_AddFund, Line 5
Incorrect syntax near '@FundName'.
Presumably, I need to convert the @FundName to some appropriate string type. My question is what is the string type, and how do I do the conversion.
Thanks!
Adam Cataldo