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.
I am trying to figure out a way to use a columns default value when using a stored procedure to insert a new row into a table. I know you are thinking "that is what the default value is for", but bare with me on this.
Take the following table and subsequent stored procedure. In the table below, I have four columns, one of which is NOT NULL and has a default value set for that column.
CREATE PROCEDURE TestTable_Insert @FirstName nvarchar(50), @LastName nvarchar(50), @SSN nvarchar(15), @geek bit = NULL AS BEGIN INSERT INTO TestTable (FirstName, LastName, SSN, IsGeek) VALUEs (@FirstName, @LastName, @SSN, @geek) END GO
and executed it as follows (without passing the @geek parameter value)
The error I got back (and somewhat expected) is the following:
Cannot insert the value NULL into column 'IsGeek', table 'ScottTest.dbo.TestTable'; column does not allow nulls. INSERT fails.
What I would like to happen is for the table to use the columns default value and not the NULL value if I don't pass a parameter for @geek. OR, it would be really cool to be able to do something like this:
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???
I have a problem here where I am trying to use SQL Parameters to update a column in the database, but the problem is that I need to concatenate the same column to the SQL parameter. The code I have is below, but it throws a Format Exception...
UPDATE tbl_NSP_Inspection SET Description = Description + @InspectionDescription
...It is because I am trying to Append teh data in the description column to the SQL Parameter ( @InspectionDescription). How do I actually do this using SQL Parameters?
I want to send a reports to two person. Reports are going to be delivered automatically. I hope to use snapshot option. In my report there is one column which can be seen by only one person. Can I use parameters to hide one column from one person? If its possible, can you explain how to do that please. If its not possible, what are the other option excet creating two different reports. Also If I use parameters , can reports be executed automatically? Thanks
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
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; ......
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();
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; }
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 !
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.
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?
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
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?
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
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 )
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 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.
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.
Hi,Suppose I have a table containing monthly sales figures from my shopbranches:Branch Month Sales-----------------------London Jan 5000London Feb 4500London Mar 5200Cardiff Jan 2900Cardiff Feb 4100Cardiff Mar 3500The question I am trying to ask is this: in which month did each branchachieve its highest sales? So I want a result set something like this:Branch Month----------------London MarCardiff FebI can do a "SELECT Branch, MAX(Sales) FROM MonthlySales GROUP BY Branch" totell me what the highest monthly sales figure was, but I just can't figureout how to write a query to tell me which month corresponded to MAX(Sales).Ideas anyone?Cheers,....Andy
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?
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?
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)?
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
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?
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
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.
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.
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.
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.
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?
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.
