Parameters For Query

Oct 19, 2006

I'm not sure if any1 can answer is on this forum, but any help would be VERY appriceted....

I am creating a report using Visual Studio.Net..I want to write a query using parameters BUT in this case I dont no what the parameter will be...

ie the user can enter a customer account OR a customer group
and sort can be on product OR total sales...

Any ideas?!

Thanks!!! :)

View 9 Replies


ADVERTISEMENT

Query Duration Using Parameters Vrs No Parameters

Apr 27, 2006

Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.

I open a SQLProfiler and this is what I have :

exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111

I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)

I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.

Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?

Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP

I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.

Is there a way to handle "dynamic parameters" in a efficient way???

View 1 Replies View Related

SQL Query Parameters

Dec 19, 2006

I am trying to search a SQL database using a TextBox  and a DropDownList. The textbox being where the user enters there search query and the dropdownlist allowing them witch column to search.  If I hardcode in the table column to search everything works fine but as soon as I parameterize the column to search I no longer get any results.   While stepping through the code both parameters do get assigned the correct values to make the sql statement valid.  Any advice would be appreciated.
.... code that does not work .....
            string connection = ConfigurationManager.ConnectionStrings["serverlist"].ConnectionString;            string sqlquery = "SELECT server_id, hostname, os, description, owner FROM server_info WHERE @SearchItem=@SearchQuery";
            conn = new SqlConnection(connection);            comm = new SqlCommand(sqlquery, conn);
            String searchQuery = searchBox.Text;            String searchItem = DropDownList1.SelectedValue;
            comm.Parameters.Add("@SearchQuery", System.Data.SqlDbType.VarChar);            comm.Parameters["@SearchQuery"].Value = searchQuery;            comm.Parameters.Add("@SearchItem", System.Data.SqlDbType.VarChar);            comm.Parameters["@SearchItem"].Value = searchItem;
......

View 1 Replies View Related

Using Parameters In SQL Query

Jul 11, 2007

Hi all,
I am trying to write an sql query in my web application to select some records from the database using parameters. I use SqlClient for that. The query has a WHERE clause where its supposed to values from the various dropdownlists that I have in my application. I bind the SelectedValue from the dropdownlists to the appropriate parameters and the SQL query does not work if all the conditions are satified in the WHERE clause.
Can I have an SQL query where I can provide all the parameters in the WHERE clause (with AND conditions) and it takes only those values where there is some value from the dropdownlist and ignore the rest of the parameters.
This is what I am trying to do:  private DataSet GetSalesDataBas()
{
DataSet ds = new DataSet();
SqlConnection connection = new SqlConnection("...");
SqlCommand command = connection.CreateCommand();

SqlParameter prm_1 = new SqlParameter();
SqlParameter prm_2 = new SqlParameter();
SqlParameter prm_3 = new SqlParameter();
SqlParameter prm_4 = new SqlParameter();
SqlParameter prm_5 = new SqlParameter();
SqlParameter prm_6 = new SqlParameter();
SqlParameter prm_7 = new SqlParameter();

prm_1.ParameterName = "@pNum";
prm_2.ParameterName = "@sNum";
prm_3.ParameterName = "@paramLoc";
prm_4.ParameterName = "@paramDist";
prm_5.ParameterName = "@paramPNum";
prm_6.ParameterName = "@paramDesc";
prm_7.ParameterName = "@paramBuild";

prm_1.Value = TextBox2.Text.Trim();
prm_2.Value = TextBox3.Text.Trim();
prm_3.Value = DropDownList15.SelectedValue;
prm_4.Value = DropDownList12.SelectedValue;
prm_5.Value = DropDownList21.SelectedValue;
prm_6.Value = DropDownList13.SelectedValue;
prm_7.Value = DropDownList18.SelectedValue;

command.Parameters.Add(prm_1);
command.Parameters.Add(prm_2);
command.Parameters.Add(prm_3);
command.Parameters.Add(prm_4);
command.Parameters.Add(prm_5);
command.Parameters.Add(prm_6);
command.Parameters.Add(prm_7);

string strWhere = "";

if (DropDownList23.SelectedIndex == 0)//if no sort field selected
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location = @paramLoc" +
" AND m.[District] = @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")";
}
else
{
strWhere = @" WHERE ""UserName""='" + userName + "' AND ([Status] LIKE 'REF%A' OR [Status] LIKE 'A%')" +
" AND m.[Part Number] LIKE '%' + @pNum + '%'" +
" AND m.[Serial Number] LIKE '%' + @sNum + '%'" +
" AND (Location= @paramLoc" +
" AND m.[District]= @paramDist" +
" AND m.[Part Number]= @paramPNum" +
" AND m.[Description]= @paramDesc" +
" AND m.[Building]= @paramBuild" +
")" +
" ORDER BY " + DropDownList23.SelectedValue + " " + DropDownList24.SelectedValue;
}

string sqlSalesData = @"SELECT * FROM Main m" + strWhere;
command.CommandText = sqlSalesData;
SqlDataAdapter salesOrderAdapter = new SqlDataAdapter(command);
salesOrderAdapter.Fill(ds, "Main");
salesOrderAdapter.Dispose();
command.Dispose();
return ds;
}    

View 7 Replies View Related

DTS Query Parameters

Mar 14, 2002

I am creating a package within which I have a query step that needs to accept
a value from a variable step that has already been run, but I cant get it to work and the books online dont help!

The query is simple

insert into table1
select col1 = cola,
col2 = colb,
col3 = ?
from tableA

Even though the variable that I want use has been set, I cant get the query to recognise there are any parameters. If I click the parameter button in the query set-up, I get a message saying that col3 is an invalid column name and then a box saying that an error occured when parsing the statement fro parameters !

Can anybody help ?
Thanks
Tom

View 1 Replies View Related

MDX Query With Parameters

Jul 25, 2007

Hi there,

I'm developing one MDX query to place in my report, but I'm having a lot of troubles when using one of my report parameters.

I hope I can explain me good enough.

I have a report parameter that's used in the WHERE clause to choose the value of one of my dimensions, the problem is that I'm also trying to compare the value of this parameter to decide the value of another dimension, but the comparison isn't working at all.

Let me show one example:

The parameter name in question is: DimFolderWalletDesciption.




Code Snippet


IIF(
@DimFolderWalletDesciption = [Dim Folder].[Wallet Desciption].&[Investimento],
[Dim Indexation].[Indexation Group].[Rendimento Variável],
[Dim Indexation].[Indexation Group].[Indexation Group].ALLMEMBERS
)


This is suppost to do the following: If my parameter as the selected value "Investimento", then the "Indexation Group" selected should be "Rendimento Fixo" if not, there shouldn't be any "filter" in the Dimension "Indexation Group".

I've tryed a lot of combinations, but none works, what am I doing wrong?

Thanks in advance.
Regards.

View 1 Replies View Related

Update Query With Parameters

Apr 18, 2007

I have a web form that is populated from the DB values. User can update teh record by keying in new values in the form fields. In my .vb class file I have an update statement -
strSql = "UPDATE msi_Persons SET Firstname=@firstname where Id=@id"
 I have the parameters defined as
cmd.Parameters.Add(New SqlParameter("@firstname", _FirstName))
cmd.Parameters.Add(New SqlParameter("@id", _Id))
 _FirstName & _Id are the private variables whose values a set thru set and get methods.  I expect _FirstName to have the new value I am keying in the form.
Can any body help me with what's wrong here? This is not updating the database. When I do trace.write it shows me the Update statement as "UPDATE msi_Persons SET Firstname=@firstname where Id=@id" instead of @firstname and @id being replace by actual values.
Please help.
Thanks,Shaly

View 23 Replies View Related

How Can I Execute SQL Query From DOS With Parameters?

Feb 8, 2007

I believe all of us use SQL Server Management Studio to execute query. As I encountered serious problem with this method, due to typo error by accident, I am wondering if we can run the .sql file from dos and supply parameters with it.Possible?

View 4 Replies View Related

Query - Scanning Of Parameters

Mar 6, 2007

Hi all. I have a problem on my query. let me explain first the scenario.

I have a windows application project that views a report through report viewer(Report of all employees that have a religion of adventist, born again, catholic, baptist, etc....). In my windows application, I have checkboxes for a specific religion. So, if I checked baptist and catholic, report generated is all employee that has religion of baptist and catholic.

My concern is, How could i make a query of this?

sample tables.
table - employee
dcno name religion
1 jon baptist
2 rose catholic
3 joy baptist
4 mike adventist
5 soy born again
6 rich protestant

you can supply additional table if needed.

thanks
-Ron-

View 7 Replies View Related

LOOKUP: Specifying Parameters For The SQL Query

Jul 18, 2006

guys i'm trying to use a Lookup in a dataflow that looksup stuff in the results of a query.

Problem I have is that the query needs to take two parameters.. (Source
and BaseCurrency in the code below) and i can't figure out how to
supply the parameters..

Parameters can be supplied in other task types or transforms .. but can't see how to do it in the Lookup...



PJ



SELECT ForeignCurrency, RateFromFile AS YesterdaysRate

FROM inputrates IR

WHERE fileheaderid in (

SELECT top 1 MAX(ID)
FROM FileInputAttempts FIA
WHERE Source = '?'
AND FIA.BaseCurrency = '?'
AND status = 'SUCCESS'
Group by CAST(FLOOR(CAST(LoadDate AS float))AS datetime)
order by MAX(loaddate) DESC
)

View 10 Replies View Related

Getting ORA-01036 When Using Query Parameters

Dec 13, 2007

I'm adding parameters to an already working query for a standard report I'm building. The object I am connecting to is an Oracle object (I have SP2 installed in all the appropriate places). I'm changing two date comparisons from hard coded date values to two query parameters. Based upon what MS's Step by Step book on Reporting Services is telling me, I have my query parameters correctly pointing to my Parameters collection. Thing is, when I click the preview button, I get subject error message -- "illegal variable name/number". Here's my query:

SELECT S.COVER_DATE, S.AD_NBR, S.APP_NBR, S.PRODUCT, S.EDITION, S."SECTION", S.PROGRAM_RATE, S.NET_AMT, S.ACCOUNT_ID,
ACT.SALES_ID
FROM MDTI.SCHEDULE S INNER JOIN
MDTI.ACCTSALES ACT ON S.ACCOUNT_ID = ACT.ACCOUNT_ID AND S.PRODUCT = ACT.PRODUCT
WHERE (S.COVER_DATE BETWEEN "@StartCoverDate" AND "@EndCoverDate") AND



(ACT.EXPIRE_DATE > '30-NOV-07') AND
(ACT.EFFECTIVE_DATE < '01-DEC-07')

The query parm names start with "@" in the WHERE clause. Nothing to it, right?

Any ideas why I am getting this error? SBS is telling me that I must select the Oracle data provider instead of the generic ODBC data provider in the Data Link Properties of my data source. I can't get anyone to show me where the Data Link properties are.

I'm looking for a solution to this error.

Respectfully Submitted,

Dave Matthews
Atlanta, GA
aka FlooseMan Dave

View 5 Replies View Related

Query Parameters Vs.Filters

Mar 23, 2008

I am developing a summary report that will have multiple tables, charts and matrixes, all using the same set of data. However, one table may only show one month of data while another will show three months. If they all use the same Dataset with the same data parameters, is the data only pulled once? And then each component can use Filters to further refine the data? If this is true, this would seem to be the best option.

Or does each report component execute the query independantly?

Before I get too far down the road developing this report, I'd like to know the best way to do it from the beginning.

Thank you for your input.

Rob

View 3 Replies View Related

Using Two Parameters To Query The Same Column ID

Mar 17, 2008

I am trying to build a report that has two parameters that are entered by the user. They will be a simple string that is basically a six digit unique identifier number called DEALID. I have my report set so that I can enter one of these Deal ID's and it will pull in all of the fields I need. However, within the same report I want to have the user be able to enter a second Deal ID and have it pull in effectively the same values, yet associated with that new ID.
The problem seems to be that I am trying to have multiple parameters querying the same field in the database. Is this something that can be done? I have tried naming them all uniquely with a new alias and that does not seem to help.

Thank you

View 16 Replies View Related

Parameters In Sql Task Sub Query

Oct 9, 2006

DELETE T1
WHERE EXISTS
(SELECT *
FROM T2 A
WHERE A.C1= T1.C1
AND A.C3 >= ?)


Results in below error (OLEDB SQL TASK):

" failed with the following error: "Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

Any resolution?

View 9 Replies View Related

Query A Linked Server With Parameters

Apr 20, 2001

I need to query a linked server (which is Oracle) with some parameters. When I try to use a four part SQL statement, it does not work. But when I try to use OPEENQUERY statement, it works just fine. Problem comes when I need to send some parameters with the OPENQUERY'S 'query' part.

For example, the following statement works just fine:

SELECT *
FROM OPENQUERY(OracleLinked, "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '2000.01.01' AND '2000.01.31'")


But If I try to use:

DECLARE @DynamicSQL VARCHAR(1000),
@StartDate VARCHAR(10),
@EndDate VARCHAR(10)
SET @StartDate = '2000.01.01'
SET @EndDate = '2000.01.31'

SELECT @DynamicSQL = "SELECT ACCOUNTNUMBER, POSTINGDATE FROM ORA_SERVER.FINANCEENTRY WHERE DATEOFENTRY BETWEEN '" + @StartDate + "' AND '" + @EndDate + "'"
--SELECT @DynamicSQL

SELECT *
FROM OPENQUERY(OracleLinked, @DynamicSQL)

it does not work.

Well, I did some research and found out that OPENQUERY does not accept variables for its arguments. See the link below:(http://msdn.microsoft.com/library/psdk/sql/ts_oa-oz_5xix.htm)

Then is there any way I can accomplish what I want to on the Remote server?

Thanks in advance for your help.

View 1 Replies View Related

Search Query With Optional Parameters

Jun 12, 2006

I need to create a stored procedure that will search some tables.

The stored procedure will be passed some parameters that may or may not have a value.

I have googled the best way to do this.
I found this post as an example: Optional Search Parameters

and also found this example : Optional Parameters in T-sQL

I am trying to figure out the best way to do this.

In the past I would build a dynamic query like the following.


SQL Code:






Original
- SQL Code




CREATE PROCEDURE [dbo].[Search_Results]

@SUBCITY VarChar(100) = 'Any'

AS

------------------------------------------------------------------------------------------------------
Declare @SUBCITYString Varchar(200)
If @SUBCITY <> 'Any'
Begin
Set @SUBCITYString = ' AND (Table1.SUBCITY LIKE ''' + @SUBCITY + '%'') '
End
Else
Begin
Set @SUBCITYString = ''
End
-----------------------------------------------------------------------------------------------------

Declare @SQLString As Varchar(500)
Set @SQLString = 'SELECT*

FROMTable1

WHERE Table1.ID IS NOT NULL
' + @SUBCITYString + '

ORDER BY Column ASC'

Execute (@SQLString)

GO






CREATE PROCEDURE [dbo].[Search_Results]  @SUBCITY VarChar(100) = 'Any' AS ------------------------------------------------------------------------------------------------------DECLARE @SUBCITYString Varchar(200)IF @SUBCITY <> 'Any' BEGIN  SET @SUBCITYString = ' AND (Table1.SUBCITY  LIKE ''' + @SUBCITY + '%'') 'ENDELSE BEGIN SET @SUBCITYString = '' END----------------------------------------------------------------------------------------------------- DECLARE @SQLString AS Varchar(500)SET @SQLString = '  SELECT    *                            FROM  Table1               WHERE  Table1.ID IS NOT NULL                ' + @SUBCITYString +  '            ORDER BY Column ASC' Execute (@SQLString) GO


However this is really cumbersome to create and is not fun debugging!

Does one of these ways have an advantage over the other? Or is there another way to do this?

Thank you!

View 2 Replies View Related

Pass Parameters To A Query Using Subquery?

Apr 23, 2012

I don't know how to pass parameters required (dates) to a query1, if I'm using a subquery (query2) which is using the results of query1, but I'm not showing that field on that subquery (query2)

Example

table1
id - autonumeric
id_user - id from user
dates - date of register

table2
id - user id
name - user name

query1
SELECT Table1.id_user, Count(Table1.id_user) AS CuentaDeid_user
FROM Table1
WHERE (((Table1.datess) Between [begining] And [ending]))
GROUP BY Table1.id_user
ORDER BY Table1.id_user;

subquery (query2)
SELECT query1.id_user, query1.CuentaDeid_user, Table2.name
FROM query1 LEFT JOIN Table2 ON query1.id_user = Table2.id;

This is just an example, the think is that I want to know that if it's possible to pass the parameters requested in query1 from the SQL of the subquery (query2)?

View 3 Replies View Related

T-SQL (SS2K8) :: Appropriate Query For Multi Parameters?

Mar 13, 2014

I am using the following code in my query to fetch data for my ssrs report which have a parameter @auditCode, where multiple auditCodes can be inputted to generate the report.

Is there any other way I can achieve the same functionality avoiding the part charindex(LU.auditCode,@auditCode)<>0 , as it will return wrong results.

For instance, it will return, the results for the audit code ‘INPS45’ and ‘INPS450000’ when audit code ‘INPS45’ is inputted.

SELECT distinct Ac.activityCode,
Ac.ActivityName + isnull(Ac.description,'') AS ActivityName,
Ac.activityStartDate, Ac.activityEndDate,
LU.auditCode,
LU.AuditName,
St.studyCode AS StudyCode,
St.StudyName AS StudyName

[Code] ....

View 3 Replies View Related

Dynamic Query With Multi-value Parameters

Jun 1, 2008

Is anybody here knowing how to create a dynamic query based on a multi-value parameter?

e.g. there is a multi-value report parameter called names. For a static query, the where clause of a select statement likes the following

select * from students where name=@names

For the dynamic one, I tried something like the below, but it did not work.

="select * from students where name=(" & Join(Parameters!names ,',') & ")"

Any suggestion would be great appreciated.



Thanks,
KY

View 2 Replies View Related

Query With Parameters Not Returning Right Results

Mar 19, 2008

I havea 2 part issue with a query I'm trying to run for a report.

I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions?

Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense?

Here’s the query (thanks!):

SELECT incident.incid_id,
incident.incid_short_desc,
incident.incid_received_date_time,
incident.incid_closed_date_time,
security_users.description,
incident.incid_assigned_to,
incident.tagged_delete_flag,
activity_result_master.result_desc,
incident_priority_master.priority_desc,
activities.result_id

FROM activities AS activities INNER JOIN
incident AS incident ON
activities.incid_id = incident.incid_id INNER JOIN
activity_result_master AS activity_result_master ON
activities.result_id =
activity_result_master.result_id
INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOIN
incident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_id

WHERE incident.tagged_delete_flag = 'N' AND
activities.result_id = '6' AND
(incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND
incid_closed_date_time <=DATEADD (d,-3,GETDATE())) OR
CONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDate

ORDER BY incident.incid_assigned_to, incident.incid_id

View 2 Replies View Related

DB2OLEDB Query Parameters In OLE DB Sources

Aug 10, 2007

Hi

I'm using Microsoft DB2 OLE DB Driver to access a DB2 database, and I have a problem when I create an OLE DB Source using a parameterized query. Everytime I push the "Parameters" button, I get this error:

TITLE: Microsoft Visual Studio
------------------------------

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

------------------------------
ADDITIONAL INFORMATION:

El proveedor no puede derivar la información del parámetro, no se llamó a SetParameterInfo. (Microsoft DB2 OLE DB Provider)

------------------------------

I would really appreciatte any help.

Thanks.

View 5 Replies View Related

How To Write A SQL Update Query Using Parameters?

Mar 23, 2008

Hi, could someone explain to me with sample code how to write a sql query using parameters to update a record, as I am new to this.

thanks

View 4 Replies View Related

Help Designing Query For Any Number Of 5 Parameters

Nov 27, 2007

Hello everyone,

I'm having trouble designing a query for an ASP.NET project I'm working on. The main portion of the query is done and works, but I need to modify it to accept 5 parameters. Below is the main query:





Code Block

@LocID as nvarchar(10)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE dbo.ProfileNames.Type = 'Gate' AND dbo.ProfileNames.Location = @LocID
The changes I need to make to this are confusing me because I've had to do something like this before. There are 5 DropDownLists on the page the user can select as parameters for searching/filtering the information. The query above is what's being used now, and causes the page to dynamically generate a certain number of colums based on the number of returned columns.

The parameters that are available are: Gate, Location, Offload Zone, Onload Zone and Equipment. I wwant the user to be able to select all or any number of these as search options but I'm not really sure how to design the query to do this. A co-worker suggested doing something like this:





Code Block

@LocID as nvarchar(10),
@Gate as nvarchar(50),
@Location as nvarchar(50),
@OffLZ as nvarchar(50),
@OnLZ as nvarchar(50)

SELECT dbo.ProfileNames.Name, dbo.AircraftGateInput.*
FROM dbo.ProfileNames
LEFT JOIN dbo.AircraftGateInput
ON dbo.ProfileNames.Name = dbo.AircraftGateInput.Gate
WHERE (dbo.ProfileNames.Type = 'Gate') AND (@Gate IS NULL OR dbo.ProfileNames.Name = @Gate)

AND (dbo.ProfileNames.Location = @LocID)


AND (@Location IS NULL OR dbo.AircraftGateInput.Location = @Location)
AND (@OffLZ IS NULL OR dbo.AircraftGateInput.OffLZ = @OffLZ)
AND (@OnLZ IS NULL OR dbo.AircraftGateInput.OnLZ = @OnLZ)
At this point I'm not a 100% sure on how to test this query in the query designer in Management Studio 2005 (this is also a .NET 2.0 project). If anyone can help, I'd really appreciate it, thanks.

View 3 Replies View Related

Passing Parameters To AS400 Query

Dec 5, 2007

Hello,

I'm using

IBM DB2 UDB for iSeries IBMDA400 OLE DB Provider
to extract data from AS400 and copy them to SQLServer2005. It's my first experience with exptracting data from AS400.

I need to pass in SSIS a parameter to the extract query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > ?

I'm naming the parameter in the mapping as 1 but geetting an error
[as400 [1]] Error: The SQL command requires a parameter named ""00001"", which is not found in the parameter mapping.

I have tried 00001, '00001', ("00001" is not accepted from SSIS) but getting still this error.

A query like
SELECT dtses
FROM oslglf3.flp016
WHERE dtses > '1071205'
works OK.

Can anybody help me ?

thanks in advance

View 7 Replies View Related

Parameters For Lookup Transformation Query!

Aug 2, 2007

Hi Gurus,
I have a Dataflow Task which has an OLE DB Source calling a SP with parameters (?, ?). Then this OLE DB Source is conencted to a Lookup Transform which also calls a SP but on a different database. I am unable to figure out how to pass parameters in a Look up Transform.
In the 'Use Results of an SQL Query' pane of Lookup Transform:



Code SnippetEXEC GetMonthlyDataExtract 4, 2007

( I am passing month and year values) this works ok.


But when I chage to



Code SnippetEXEC GetMonthlyDataExtract ?, ?





It says EXEC not supported. Also I can not figure out how to configure parameters since 'Reference Table' Tab of the Lookup Transform does not have any option where we can attach variables to parameters.
Also I am interested to map parameters to variables not to input columns.
If mention if that is not possible or any other alternative.

Your help will be appreciated.

Thanks,
Paraclete

View 3 Replies View Related

Lookup Transforms Using A SQL Query With Parameters. Is It Possible?

May 9, 2008

When I retrieve data using an OLE DB Source I can create a SQL query and pass parameters to filter the data I get back. I'd like to do the same thing with the Lookup Transform but the parameters button isn't there. Am I missing something or do I have to use some special text format to insert my parameters into the query?


Thanks in advance.

View 5 Replies View Related

Analysis :: MDX Query Parameters In WHERE Clause?

Sep 25, 2015

I need to filter my select statement with 2 parameters, each of which defaults to the "All" member.  Some of the members may have spaces in the name.

So I need to handle something like these:

FROM MySalesCube
WHERE ( ... expression here ...)

The expression above  needs to resolve to a set using both parameters

 STRTOSET("[Dept].[Dept][" + @pDept + "] , [Salesperson].[SalesPerson].[" + @pSalesPerson + "]")

Running the query in SSMS, Im using something like this to test

WHERE ( [SalesPerson].[SalesPerson].&[17]  , [Department].[Department].[All] )

I just need to use the above in an expression with parameters, where BOTH SalesPerson and Department could use a specific member OR use the All member.

[update]

Here is what I have now:

WHERE ( STRTOSET(CSTR("([SalesPerson].[SalesPerson].[" + @pSalesPerson +"]  , [Department].[Department].[" + @pDept +  "])")  ) )

If I hard-code @pDept to something in a string, it works ok, otherwise, I simply get an error

View 2 Replies View Related

Dataset MDX Query For ALL Selected In Parameters

Apr 2, 2008



Hello All,

I have 2 MultiValued Parameters in my reports, our of which first is dependent on the other.

Eg. I have a SubProductGroup Parameter and depending upon the value selected in SubProductGroup, the other parameter which is Product gets filled in combo box.

Both these parameters are MultiValued and I am having "Select ALL" option in both the combo box.

Now, whenever you select any SubProductGroup values, the Dataset for Product gets executed to fill the Product combo box.

The MultiValued Parameters works using the "IN" Clause. All those products are retrieved which are "IN" the selected SubProductGroup.

Whenever user selects "Select All" option for SubProductGroup, there is no need for this "IN" clause, because we actually need all the records for Product.

Similarly, I have the Main Dataset which retrieves the Sales data for the Products selected from the Combo Box. So, the same question arises there also.

Right now I am using STRTOSET( ) and Join( ) function and this is working fine for me.

But when User Selects "Select All" in SubProductGroup, it takes a long time to fill the combo box for Product as well as when user selects "Select All" for Product, it takes a long time for the actual Dataset to retrieve records. But its working fine.

In order to improve the Performance, I am trying to eliminate this IN clause when "Select All" is selected.

How can I do this ?


Any Inputs ?

Thanks & Regards,
Kapadia Shalin P.

View 4 Replies View Related

Values In Define Query Parameters Box

Nov 21, 2007

How do I create value from the dropdown list of each parameter within the "Define Query Parameters" box, which appears when I run a query/dataset from the "Data" tab of the SSRS Report Designer ?

It usually list all the "Parameter Name"s I defined, and has another column for "Parameter Value", but it always shows only <Null> or <Blank> even though I have assigned the report parameters with data (from a Query, not hard coded).

The parameter values work fine when in "Preview" mode, but it would be of convenience to have them during design time.

Thanks in advance.

View 7 Replies View Related

MDX Query With Parameters In Management Studio

Feb 27, 2008



Hello,

I would like to know if it is possible to make a MDX query on a cube with parameters in Management Studio and if yes, how to define the parameters?

In fact, I have a quite big query which of course does not work and in Reporting Services, I get an error message which is not very clear :

Cannot set the command text for data set "Cube"
Error during processing of the CommandText expression of dataset "Cube"

So I would like to test my query in SSMS and correct directly.

Edit :

Forget the query but my question concerning the parameters definition in SSMS is still asked.

Thanks

View 5 Replies View Related

SqlDataSource And Passing Multiple Int Parameters In Query

Aug 11, 2007

Hi All,

View 2 Replies View Related

Problem With SqlDataSource Using Sub-query And Date As Parameters

Dec 13, 2007

I am creating a search page for master detail tables. The search criteria is mainly on the header table. However, there is also one criteria which is in detail table, let said product number.In my SqlDataSource, I setup the SQL like this.select fieldA, fieldB, ..., fieldZ from masterTable where (1 = 1)Then, the additional search criteria is appended to the SqlDataSource select command once the user click the search button. If user wants to search product number, the following will be appendedand exists (select 1 from detailTable where pid = masterTable.id and productNo = @productNo)The problem is when I provides both the sub-query criteria and 2 date fields criteria. The page will raise an timeout exception. I don't have any clue on this as I can copy the SQL and run it inside the SQL Server Management Studio. The result come up in a second.Any suggestion on tackling this problem? Thanks! 

View 4 Replies View Related

How Do You Write A Query To Return All Record With Parameters?

Jan 24, 2006

For example:

Select * From Customers Where CustomerName = "Tom" and CustomerLocation = @Location

I know MS Access you can use a "*" to retrieve all records when there is a parameter. What does SQL Sever query use to retrieve all record?

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved