Can A Variable Prompt Be Changed Based On A Language Selection?
Nov 16, 2007there doesnt seem to be an object id associated with variable prompt phrases. Does this mean they cannot be localized based on language choice?
View 1 Replies
ADVERTISEMENT
Using Prompt Parameter Into SSRS Report Based On Sql Query
Dec 12, 2007Hi All,
I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries.
When I added the prompt into the query for use as a filter value, it says that there is an error.
It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.
Your help is much appreciated.
Thanks & Regards,
Mohd Fadzli
Rollback Based On # Of Rows Changed
Jul 10, 2001I am trying to create a stored proc that will update exactly one row. Simple. For insurance purposes, I want to create some logic that will rollback the entire transaction if more than one row is updated.
I know that I could force the primary key into the WHERE clause, but I was looking for some logic that will allow me to bypass that.
Thanks,
Tom
Drill Down Based On Parent Selection
Mar 28, 2007I have a report grouped by username, provider_name and it drills down on the username, and then on the provider name to display the detail data.
My report looks like this:
-Username1
+Provider1
+Provider2
-Username2
+Provider1
+Provider2
I would like to only display provider1 when I drill down on Username1 and only provider2 when I drill down on Username2.
-Username1
+Provider1
-Username2
+Provider2
Thanks!
Import Files Based On Whether They Have Already Been Imported Or If They Have Changed
Feb 9, 2008Hi there,
Today I tried out Integration Services and after a couple of hours of confusion I am impressed by the power of this product. I was wondering if I might get some help on the 'best practice' for the following requirements:
I have a foreach container that scans a directory and calls a data flow task for each file in the directory. The files import to the database fine, but I want to modify the procedure so it only imports files that have not yet been imported. There are a couple of scenarios:
- a file is created in the directory
- a file is modified in the directory
In both cases I want to insert (or reinsert) the file. How can I modify my package to accomodate this behaviour? Storing the filename is an option, but I am not sure how to also bring in the file creation/modified dates.
Is this the right approach?
Any other ideas?
Cheers,
Alex
Change SqlCommand Based On Combo Box Selection
Dec 14, 2007I have a textbox, combo box, and a button on a form. I would like to perform a different query depending on the combo box selection. I thought I could do something such as: if (cboSearch.Text == "Selection1")
{
scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection1";
}
else if (cboSearch.Text == "Selection2")
{
scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection2";
}
else
{
scCmd = "SELECT * FROM tblTable WHERE txtSearch = @Selection3";
} However, this obviously does not operate as I would need it to. What is the proper method for conditional SqlCommand statements like this?
Filtering Parameters Based On A Selection Of Another Parameter
Dec 28, 2007
Hi,
Im trying to create a drop down parameter whereby if i select a certain field, a different dropdown will be filtered off only the relevant selections, is this possible.
SQL 2012 :: How To Execute One Query From SP Based On User Selection
Dec 22, 2014I have a SSRS report with 6 columns each column containing count of total# of applicants meeting certain criteria.
User want to click on each column and see the basic information and also want to get the ability to export the data into excel.
I know that I can create 6 drillthrough reports with basic information of applicants and link it to the count from each column respectively but I was wondering if it is possible to write a Stored procedure with all 6 select queries and execute only 1 select query based on the column that user clicks on main report ?
How To Populate Parameter2 Based On Parameter1 Selection On Report
Jan 8, 2007I have two parameters on report both will be populated in dropdownlist boxes.
How can i trigger parameter2 based on parameter1 selection.
but when i fill parameter2 i also need to pass the selected value of parameter1's data.
Please i have done this on aspx form using vb.net but don't know here how to do that. haave difficulty in making a search on google, confused to form right words to search.
Thank you all very much, fot the information.
Hide/Show Table Based On Parameter Selection!!!???
Apr 28, 2008I'd like to hide/show a table based on selected parameters. In the current setup I have a matrix and two tables underneath that need to be displayed from time to time. First of I use a multi-value parameter called "Lieferart". Depending on the parameter I have set the visibility of the two tables with the following expression:
=iif(Parameters!lieferart.Value(0) like "Nagel%", False, True)
This expression doesn't work though, any ideas??? I am also unsure about what I have to do If multiple values from the parameter list are selected as Parameters!lieferart.value(0) doesn't necessarily need to have a label like "Nagel" included...
Set Up Permission Based On The Selection Made From The Drop-down (Parameter) List
Oct 4, 2007I created a report with a school parameter for all our schools in SQL report. The school parameter gets it's value from a query. The report is generated based on the selection made by the user. If user choose school "A", report is generated for school "A" and if user choose school "B" report is generated for school "B".
I am using a centralized user id and password for all users. A report URL (report link) is sent to user to access the report.
Currently, from the school parameter (school drop-down list) users from any school may/can choose report for other schools other than theirs schoos. The content of the report is sensitive and we do not want anyone to see anyone else report.
I want to create a user based security, so based on the user id for school "A", he/she will only be able to see school "A" report but nothing else.
I was trying to accomplish this by creating linked report but it does not look realistic to create a folder for each school on the report server (which means about 180 or more folder for all our schools ). Does anyone else have better suggestions what can/should I do?
String Variable With Embedded Quotes Getting Changed To Slash/quote
Jul 9, 2007I have a Foreach loop that dynamically builds a query that gets submitted to a Progress 9.1d database. The "SELECT" portion of the query is static - the WHERE clause is built dynamically each pass through the for each loop. The vast majority of the columns have a dash ("-") in their names, so the query passed to progress has to have double quotes surrounding the column names. Not a big deal you would think. If I am retrieving 3 columns, my string would look like "SELECT ""manager-name"", ''"employee-name"", ""date-of-hire"" FROM PUB.EmployeeTable" - with the columns requiring quoted identifiers escaped with double quotes. My script task adds some additional info to the WHERE clause based on other conditions. Once my script task has built the query string, it assigns that string value to my Dts.Variables("SqlString") variable.
I can put a MsgBox() call in the Script task to display my query once it has been built (displaying Dts.Variables("SqlString").Value) - and it looks fine. Using the above example, it would simply be: SELECT "manager-name", "employee-name", "date-of-hire" FROM PUB.EmployeeTable.
So - My foreach loop is set up so that the query command is pulled from my Dts.Variables("SqlString") variable. It fails anytime my query has a column that requires quotes around it. I was confused, since I could take the exact query string displayed by my test MsgBox() and validate it's syntax against our Progress database (and even run it) and it worked fine.
So - I then inserted a breakpoint to break on PreExecute for the DataReader task (next task after Scrip task) to view the value of the Dts.Variables("SqlString") variable just before it gets assigned to the DataReader's Sql Command property. That's when I noticed that the value of the Dts.Variables("SqlString") in the Watch window actually showed that the "double" double quotes I had entered in the SELECT clause (to escape to one double quote) were being replaced with " (slash quote). SO - in the Watch window, my Dts.Variables("SqlString").Value reads as follows:
SELECT "manager-name", "employee-name","date-of-hire" FROM PUB.EmployeeTable.
Obviously, that query is not going to fly via SQL-92/ODBC connection to our Progress database. I've tried everything I can think of - even trying to replace "" with "" in the Foreach expression editor where the Sql Command of the data reader gets assigned. I'm at a total loss. Why would SSIS plug in what looks like C# character escaping syntax when all the expressions are supposed to be VB.NET? I've already successfully run several packages that query our Progress databases that do NOT use the double quotes in the SELECT clause and they work fine. Does anyone have any ideas??
Random Selection From Table Variable In Subquery As A Column In Select Statement
Nov 7, 2007Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
Two Parameters Question? Second Parameter Should Get Data Based On First Parameter Selection.
Jan 8, 2007I have two parameters both are related to each other.
second parameter should get filled based on the selection of the first one which is project.
the first paramater is project, once the project is selected it should bring the all the contracts related to that project.
Please is it possible...
Thank you very much for all the helpful info.
Selection Of Table Based On Value Selected For The Particular Attribute In Master Table
May 29, 2008
Now i have master Table for a device Utility. There is a attribute called "Device Type " in the table. Every Device Type has specific Device Attributes associate with it . Now attribute of Diffrent Device type are stored in Different Tables. Now when i select a particular value of Device Type ( lets say Type 1 or TYPE 2 ... ) then the table with has the attribute associated with particuter device type only has to be selected .
So how can I do this ???
How to form a realtion between the tables,... ????
One Form To Update Different SQL Tables Based Upon Form Selection
Apr 16, 2008Hello,
My company Intranet has a form that agents can use to post their comments about the company to upper management, but our customer service department would like to modify the form so that the agent has to pick from a comment type.
The dropdown options on the form will be as follows:
ComplimentsComplaintsGeneral CommentsSuggestions
Each dropdown option has a designated table in a SQL DB.Using postback on the same page, I need to change which fields of the form are visible based upon which dropdown selection the user chooses, and I need the fields to then be inserted into the table that corresponds with the dropdown selection item.
For example: If the Compliments dropdown selection is picked, I need a text box to show for the user's location, the name of the customer, account number, and the message box. Once the submit button is clicked, the characters in these boxes need to be inserted into the Compliments table using its data adapter.
However, if the user selects Suggestions, the name of the customer and the account number should not be visible, since these fields do not exist and when the submit button is pressed, the Suggestions table should be updated.
If you need more information, I will provide whatever is needed.
As always, thanks for everyone's assistance.
Chris
In 2000 Is It Possible To List Second Parameter Based On The First Parameter Selection
Jan 11, 2007Hi
In 2000 is it possible to list second parameter based on selection
in the first parameter list
Cheers
How Do I Set A Variable Value Based On A SQL Query
Apr 25, 2007I have a SQL query that finds a value which I need to pass into an SSIS variable so I can use it later to set the filename for my data flow task. So far, I have been unsuccessful in determining how this is done.
google searches, etc seem to be fruitless for me, there are so many hits but none really seem to address what I want to do because there are so many things you can do with variables. Likewise the books online and the boks I have on SSIS also only give a very high level of info. I need some examples, if anyone could help I would appreciate it.
Here is the basic gist of my query:
exec SP_GET_FILE_NAME @file_name OUTPUT
I need to take the resulting @file_name and store it in an ssis variable. How?
Selecting Data Based On A Variable
Jun 7, 2007Hi All
I want to select a certainnumber of rows
select custno, amt, balance from customer where custno='customerno'
when showcust='r' then select rows where amt<balance
when showcust='c' then amt>balance etc
if showcust='' then show everything
Any insight will be greatly apprecaited.
Thanks
Lookup Task Based On A Variable
Jul 17, 2006This doesn't seem possible but I'll ask anyway...
Can I build a lookup task where the lookup query is based on a variable, rather than hardcoding the SQL staement?
Assign A Variable Based Upon Query Results...how To Do It?
Feb 23, 2007I have the following code which is incomplete. Where it says: txtVendorID = I need it to equal the results of the field VendorID from my query...here is my code. What do I need to add there?
Dim cmdSelect As SqlCommandDim intRecordIDintRecordID = Request.QueryString("RecordID")strConn = ConfigurationManager.AppSettings("conn")conn = New SqlConnection(strConn)cmdSelect = New SqlCommand("spMfgRepListAddaspxByRecordID", conn)cmdSelect.CommandType = CommandType.StoredProcedurecmdSelect.Parameters.AddWithValue("@RecordID", intRecordID)conn.Open()cmdSelect.ExecuteReader()txtVendorID.Text = conn.Close()
SQLServer Declare New Variable Based On Another Variables Value
Oct 26, 2005Hi, I'm a complete newbie to SQLServer and t-sql generally. What I want to do is create a new variable in a stored procedure based upon the value of another variable.
eg in the loop below I want to create 10 new variables, called @var0,@var1,@var2 ...@var9
declare @varname nvarchar(10)
declare @i integer
select @i=0
while @i<10
begin
set @varname = cast(('@var'+cast(@i as char)) as nvarchar(10))
set @i=@i+1
end
Does anyone know of a way to do this?
Changing Global Variable Based On Day Of The Week
May 11, 2007I'm new to SSIS so please be gentle...
I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?
Thanks in advance!
SQL Select Command With Where Clause Variable Based On ASP.NET TextBox
Apr 19, 2007All,
Please can someone advise how to use SQL select statement with where clasue which is based on a textBox.text value.
ex. below example I set the textbox.text value to a C# variable called TextBoxValue1 but I receive error this is not a valid
This is all done in Page_Load event backend code.
string strCommandtext = "Select Type.TypeName, Type.TypeDesc FROM Type Where Type.TypeName = TextBoxValue1";
Script Task - Set Variable Value Based On Success Or Failure
Feb 14, 2008
Please excuse my ignorance as I'm a complete noob when it comes to vb.net.
I have 2 script tasks, each connected to an upstream task via Success and Failure constraints. Each script assigns a value to a variable, depending on whether the task succeeds or fails.
My code thus far is:
Code Snippet
Public Sub Main()
Dts.Variables("strEmailBody").Value = _
"Business Model Reporintg Control Complete - Status = Success"
Dts.TaskResult = Dts.Results.Success
End Sub
What i want to do is use a single script task depending on the success or failure of the package, setting the variable value accordingly.
If there are no errors Then
"Success"
Else
"Failure"
I've tried
Code SnippetIF CBool(Dts.Results.Success) Then...
But whislt it compiled, didn't evaluate correctly during runtime.
Can anyone suggest where I'm going wrong? Again I'm totally new to .net and I'm surprised I've gotten this far!
Thanks in advance.
Leigh
How Can I Stop Or Continue Package Execution Based On Variable Value
Aug 1, 2006Hi,
I need to make my package check a variable value at the begining of the execution and depending on the value of that variable it decides either to continue or stop the package execution. How can i do that?
thanks,
Aref
Filtering Out 1 Time Dimension Based On The Selection In Another Time Dimension
May 19, 2008Hi!
Need some help building a query that does the following :
I have 2 Time Dimensions ; Time (Transdate) and ClosedDate (ClosedDate)
In my report/query, if [Time].CurrentMember = [Time].[YMD].[YMD].[2006].[200610].[20061031] I want to FILTER out all ClosedDate < [ClosedDate].[YMD].[YMD].[2006].[200610].[20061031]
Both Time Dimensions are Year -> Month -> Day and have the same Members.
I have every option available, using calculated Members and/or Measures to do this.
The report I'm creating is Aging of Receivables : Balance / 30 days / 60 days / etc.. But for the Aging, I need to filter like explained above.
Appreciate all help!
Regards,
Stian Bakke
SQL Server 2008 :: Stored Procedure Returning Different Datasets Based On Input Variable
Sep 15, 2015I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT)
AS
BEGIN
IF @rptType = 1
BEGIN
SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
So what I'm looking for is basically 2-fold.
SQL Server 2008 :: Split Varchar Variable To Multiple Rows And Columns Based On Two Delimiter
Aug 5, 2015declare @var varchar(8000)
set @var='Name1~50~20~50@Name2~25.5~50~63@Name3~30~80~43@Name4~60~80~23'
---------------------
Create table #tmp(id int identity(1,1),Name varchar(20),Value1 float,Value2 float,Value3 float)
Insert into #tmp (Name,Value1,Value2,Value3)
Values ('Name1',50,20,50 ), ('Name2',25.5,50,63 ), ('Name3',30,80,43 ), ('Name4',60,80,23)
select * from #tmp
I want to convert to @var to same like #tmp table ..
"@" - delimiter goes to rows
"~" - delimiter goes to columns
Set Variable Based On Result Of Procedure OR Update Columns Fromsproc Result
Jul 20, 2005I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
View 4 Replies View RelatedPrompt For Value
Jun 21, 2004What is the sql code to prompt for a value?
Can I do this in query analyzer or do I have to send the value from some other application?
Prompt In SQL Server 7
Mar 6, 2002HI,
I have a need where SQL Server should prompt me for a value when I run a Select statement.
Eg:
If I need the name of an employee by id number, SQL server should prompt me to enter a value for the ID when I run the statement like:
SELECT name FROM employee WHERE id=[Enter ID]
In oracle, it is possible by using "&" operator but I am not sure how it can be done in SQL Server. Is there any other way to get the prompts if not by the above method.
Thank you for your help in advance.
Sravan.
What's Sql Prompt Tool
Feb 26, 2008can anyone tell me what's sql prompt tool and how can it help in sql script? it finds error or what?
is it useful? its like visual studio?
thanks. pls reply.