Setting Select Parameter Based On Config File
Jun 17, 2008Hi everybody,
Is there a way to set SelectParameter for SQLDataSource in ASPX file using System.Configuration.ConfigurationManager.AppSettings["SiteID"]) ?
Thanks a lot in advance.
ADVERTISEMENT
Web.config File Setting
Mar 1, 2008how to solve error SQL server does not allow remote connection
View 2 Replies View RelatedSetting Parameter Value Based On Matrix Column
Apr 10, 2007Hello all.
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
cmk
Reporting Services :: Parsing SSRS Config File And Dynamically Changing File Path Of Config File In Code
Sep 2, 2015Currently have a single hard coded file path to the SSRS config file which parses the file and provides the reporting services web service url. My question is how would i run this same query against 100s of servers that may or may not share the same file path as the one hard coded ?
Is there a way to query the registry to find the location of the config file of any server ? which could be on D, E, F, H, etc.
I know I can string together the address followed by "reports" and named instance if needed, but some instances may not have used the default virtual directory name (Reports).
Am I going about this the hard way ? Is there a location where the web service url exists in a table ? I could not locate anything in the Reporting service database. Basically need to inventory all of my reporting services url's.
Setting A Parameter Value To Select All.
Nov 27, 2007I searched this forum however I could not find an answer to my question. I'm sure it has been asked before but...
In my parameter if the user does not select anything, null, then it returns all the records.
Second question if he selects "All" then it returns all records.
If this is 1st grade stuff please forgive me. I'm pretty new, learning as the needs arise.
Thanks
CardGunner
Setting The Default To &&<Select All&&> In Multivalue Parameter List
Aug 21, 2007How can I set the multivalue parameter list's default option to <Select All> ?
View 2 Replies View RelatedParameter: C, I, P, Or ALL...how To Select Based On Parameter?
Jul 27, 2006Hi everyone,
I have this semi-complex query that is selecting items from numerous tables residing on 2 different databases. So far the query works perfectly. Here is the problem: The user is given the option of selecting items based on whether a course is Completed (C) Incomplete (I) Passed (P) Failed (F) or (ALL). I am not really sure how to do the select all, the others I can do depending on the value...
Any thoughts??
Query:
sql Code:
Original
- sql Code
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser]
(
@status varchar(25),
@course varchar(100),
@datesmalldatetime
)
AS
SELECT
A1.uLastName,
A1.uFirstName,
A2.mName,
A3.tStatus,
A3.tScore,
A3.tStartDate,
A3.tCompleteDate,
A4.cName
FROM VSALCP.dbo.[User] as A1
INNER JOIN FSDLMS.dbo.Student as A5
ON A1.uID = A5.stID
INNER JOIN FSDLMS.dbo.Transcript as A3
ON A3.t_FK_stID = A5.stID
INNER JOIN VSALCP.dbo.Member as A2
ON A2.mID = A1.u_FK_mID
INNER JOIN FSDLMS.dbo.Course as A4
ON A4.cID = A3.t_FK_cID
Where @status = A3.tStatus ...
ALTER PROCEDURE [dbo].[Sel_CourseActivityPerUser] ( @status varchar(25), @course varchar(100), @date smalldatetime )AS SELECT A1.uLastName, A1.uFirstName, A2.mName, A3.tStatus, A3.tScore, A3.tStartDate, A3.tCompleteDate, A4.cName FROM VSALCP.dbo.[User] AS A1 INNER JOIN FSDLMS.dbo.Student AS A5 ON A1.uID = A5.stID INNER JOIN FSDLMS.dbo.Transcript AS A3 ON A3.t_FK_stID = A5.stID INNER JOIN VSALCP.dbo.Member AS A2 ON A2.mID = A1.u_FK_mID INNER JOIN FSDLMS.dbo.Course AS A4 ON A4.cID = A3.t_FK_cID WHERE @status = A3.tStatus ...
"If status = ALL select * status"
Thanks for taking a look!
Filtering Data Based On Select Parameter Values
Jun 6, 2006Hi,
I am using SQL 2005. I have a SELECT query in a stored proc with 3 parameters:
@subaccount,@numDaysCutoff,@numDaysPcts. The proc needs to be modified to return data when subaccount values are any of these:
FRRIJ
FRRIC
FRMM
ROBECO
FRJV
MAIL
FRUKV
FRICE
Currently I use a WHERE condition and am able to get data correctly. However, for a NULL value I should get everything including those not in the above list. Should I use CASE statement instead? How?
@subaccount VARCHAR(8) = NULL
, @numDaysCutoff INT = 1
, @numDaysPcts INT = 1
SELECT Subaccount = ISNULL(h.subaccount, lo.subaccount)
, SecurityID = ISNULL(h.security_id, lo.security_id)
, SecurityName = s.name
, QtyHeldAndPending = ISNULL(h.quantity, 0) +
(CASE WHEN lo.type = 1 THEN lo.resulting_quantity * (-1)
WHEN lo.type = 2 THEN lo.resulting_quantity
ELSE 0 END )
, L.AverageDailyVolume
, XDaysVol = L.AverageDailyVolume * @numDaysPcts
, CutoffVol = L.AverageDailyVolume * @numDaysCutoff
, DaysVolHeld = h.quantity / NULLIF(L.AverageDailyVolume, 0)
, HeldPctNDaysVol = h.quantity / NULLIF((L.AverageDailyVolume * @numDaysPcts), 0) * 100
, TargetedHoldingsUSD = tm.ApprovedPortfolioTarget * iv.value_usd
, CutoffVolUSD = L.AverageDailyVolume * @numDaysCutoff * s.price_usd
, TargetedPctNDaysVol = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * @numDaysPcts * s.price_usd), 0) * 100
, DaysVolTargeted = (tm.ApprovedPortfolioTarget * iv.value_usd) /
NULLIF((L.AverageDailyVolume * s.price_usd), 0)
, NDaysCutoff = @numDaysCutoff
, NDaysPcts = @numDaysPcts
FROM subaccount_positions_table h --vGlobalHoldings h
JOIN iv_subaccount_table iv ON iv.subaccount = h.subaccount
FULL OUTER JOIN LiveOrders lo ON lo.subaccount = h.subaccount AND lo.security_id = h.security_id
FULL OUTER JOIN TM_DerivedSecurityTargetDetail tm ON tm.Subaccount = h.subaccount AND tm.SecurityID = h.security_id
LEFT JOIN dbo.security_table s ON s.security_id = COALESCE(h.security_id, lo.security_id)
LEFT JOIN dbo.SecurityLiquidity L ON L.SecurityID = h.security_id AND SourceID = 99
WHERE (h.subaccount = ISNULL(@subaccount, h.subaccount)
OR lo.subaccount = ISNULL(@subaccount, h.subaccount) )
AND status = 1
AND ( h.quantity > (L.AverageDailyVolume * @numDaysCutoff) -- qtyHeld > XDaysVol
OR -- Targeted Vol exceeds cutoff
ISNULL((tm.ApprovedPortfolioTarget * iv.value_usd), 0) >
ISNULL((L.AverageDailyVolume * @numDaysCutoff * s.price_usd), 0) -- Target > XDaysVol
)
ORDER BY ISNULL(h.subaccount, lo.subaccount), ISNULL(h.security_id, lo.security_id)
Thanks in advance!!!
sqlnovice123
File Location Based On Parameter ?
Jul 31, 2006Currently
File Connection Manager Editor with
Usage Type : Create File
and
File: eg C:DBALogSSISPackage1.log
The desired Goal:
Rather than hardcode the file directory for a log file
eg C:DBALogSSISPackage1.log
can the definition be parameterized.
For example \MachineNameDBALogSSISPackage1.log
where MachineName is the location where the package is phyiscally
located.
How To Limit The Select Query Result Based On Start And End Parameter
May 23, 2001I am a newbie to SQL Server.
I have a problem, in filtering the records returned by a query.
I have a table which contains 1 million records, it has a user defined primary key which is of character type.
The problem is i need to filter the output of a select query on the table based on two parameters i send to that query.
The first parameter will be the starting row number and the second one is the ending row number.
I need a procedure to do this.
For Eg:
MyProc_GetRowsFromBigTable(startRowNo,endRowNo) should get me only the rows in the specified range.
Thanks in advance,
Raghavan.S
Any 'Timeout' Setting In ConnectionString Of Web.config
Jan 24, 2006Hi:
I have some query that takes quite a long time to process in the sql server and every time the page seems to time out.
I wondor is there any Timeout setting that I can defined in the database ConnectionString in web.config file so that I can extend the "wait" time?
Many thanks!
Web.config Connection String Being Used Based Off Enviroment Or Domain?
Feb 20, 2007I used 3 seperate web.config files - one for Development seerver, one for Certification server, and one for Production server. The only difference between these three is the within the <connectionStrings> tags.
For Example:
<connectionStrings><add name="strConn" connectionString="server=xxx;uid=xxxx;pwd=xxxxx;database=yyy;"/></connectionStrings>
Within Development the connectionString points to the Development SQL server, Cert to cert SQL, and Prod to Prod SQL. I do not have permissions to copy new Web.Config files into Cert or Prod, so when I have a change within my web.config I have to have another group manually add these files.
Is there a way to identify the domain I am curernt within and use a specific set of connections string within the web.config based off this information. If so, can anyone post an example of how to do this...
OLAP Datasource: Second Parameter List To Be Filtered Based On The First Parameter
Apr 17, 2008Does anyone know if this is possible right out of the box in SSRS 2005 against an OLAP data source?
I have several parameters. My second parameter is to be filtered based on the first parameter (kinda like cascading), but how do I do this against an OLAP data source? Lets say I have param1 and param2 in a dataset. I want Param2 to show the locations only based on what I select in Param1.
Same but a little different: I have Parameter1 and then my second parameter (Param2) is a boolean (True/False). I want to show Parameter 3/Paramater 4 based on selection of Param2 (So, if true, show Param3, if false, show Param 4) and remember we are doing this in a sequence.
Can you do this thru SSRS? Any help would be great.
Thanks for your time in advance.
Kent
Is It Possible To Dynamically Populate A Parameter List With Values Based On Another Parameter Value?
Aug 11, 2005Is it possible to fill a parameter list with values based on another parameter value?
Here's what I have so far (which hasn't worked)...
I'd like to generate a report listing information for a student. The report viewer would first select a school from the first drop-down menu, and then the second drop-down menu would populate with the list of students at that school.
I have a dataset that calls a sp which returns a list of schools (SchoolID and SchoolName fields from the database table).
I have another dataset that calls a sp (with SchoolID as the parameter) which returns a list of students for that school.
Both datasets return the appropriate data when tested individually, but when I set up the Report Parameters and build the report, these errors come up...
The value expression for the query parameter '@SchoolID' refers to a non-existing report parameter 'SchoolID'.
The report parameter 'Student' has a DefaultValue or a ValidValue that depends on the report parameter "SchoolID". Forward dependencies are not valid.
...Is it possible for the reoprt to generate a list of available parameter values based on the value selected for another parameter?
Any help you can give me would be great!! Thank you
Reporting Services :: Hide / Unhide A Parameter Based On Another Parameter?
Oct 18, 2015I am using reporting services 2012, Can we make visibility of report parameter dynamic, ie can we make parameter visible or hide on certain condition or its visibility depends on other parameters Is this feature available in any other updated version of ssrs?
View 2 Replies View RelatedTwo 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.
SSRS Toggle Parameter Between Multi-Value And Single Value Based On Another Parameter
Feb 4, 2008
I would like to be able to adjust the multi-value property of a parameter based on the value of another parameter in my report. The controlling paramter would be binary with two options for Single or Multiple selection. I would like my parameter to default to multi-value, which I can do on the screen selection. I have tried to add an IIF statement to the XML code, with no success. Any ideas would be helpful.
Thanks
Reporting Services :: Displaying Report Parameter Based On Another Parameter
Aug 10, 2015I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
Config File Path Not Being Updated In The Dtsx File While Deployment
Sep 21, 2006Hello All,
I experienced a weird error while deploying my SSIS package. After running the manifest file, i noticed that one of the configuration file's path was not updated in the dtsx file. My solution has 8 packages and almost every package has 2 configuration files. Except 1 file every other config file's path is being updated. Has anybody experieced such a problem?
Thank you in advance for your help
Sumesh
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 To Convert UTC Based Datetime Values To Local Setting?
Sep 11, 2007Hello:
I've recently discovered the MS CRM 3.0 stamps any datetime field in SQL as UTC (aka GMT). Even though the end user may select 12:00 noon on the CRM gui interface, the datetime is stamped in SQL as 16:00 (I'm in EST).
So, is there an easy way to read the 16:00 back to local EST, taking into account daylightsavings (DST)? Apparently in VB or C# this conversion is a trivial matter of using ToLocalTime(), but I don't believe SQL 2005 has such a convenient function.
I'm currently building a SQL view that I wish to use to support multiple reports, and it would be ideal for the view to translate the UTC datetime fields (even if via a user-defined function) to EST rather than coding every report or custom app to translate the UTC.
Any suggestions or links to code will be greatly appreciated!
Thanks,
Pete
Web.config File Example
May 24, 2007Does anyone have an example of a web.config file that works with Dynamic Data controls? I asked because I can't seem to arrange my "config sections" in any working order. I use 1 connection string in the web.config to establish a conversation with the database. Thanks in Advance.
View 1 Replies View RelatedWeb.config File
Feb 21, 2008Hi the data connectionstring will be generated automatically in web.config file after dragging a data table from database into webpage.for example: <configuration> <add name="SQLProfileConnString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|MSPetShop4Profile.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/> </> But what if I have to write the connectionString part myself, I mean where I can get the syntax of it? Thanks a lot
View 1 Replies View RelatedConfig File Is Ignored
Mar 25, 2008I have a simple SSIS package which includes one Analysis Services Processing Task. I have created a connection manager object (cmo) and assigned it to the AS task. Within the cmo I selected a specific user name and
password to log on to the server with. Tested this [domain][user account] and it works. Now here is the problem. When I run the package either in debug mode or from a command line, the packages ignores the connection manager and
attemps to use the account I am logged into the server with.
Using a config file to hold the credentials does not work either. When DTEXEC is used, the package attempts to use the credientials used to log in to the server.
Does anyone have any ideas why this is happening?
XML Config File Gets Ignored
Dec 12, 2007Hi All!
I've been struggling with a problem I have. When I use DTEXECUI to run a package and manually add the config file, it gets ignored and there is no warning as such.
Here's the set up:
1) create SSIS package that uses a DB connection
2) enable config, add XML config, export the connection string, disable config
3) edit config file, change connection string
4) open package from Windows Explorer (double click)
5) add config file created in step 3
All of that works fine, but there are times when it doesn't. I think it depends on the editor used in step 3. If I use textpad and "pretty format" the XML (from the huge one-line built from within Visual Studio), then sometimes the changes aren't picked up. I can tell this because the "default" server in development is not accessable in production. When we "reformatted" the XML back to one line, the correct server was connected to.
I have no idea what the problem with the formatted XML was. I thought it was CRLF vs LF, but I have at least one config that formatted that way and it works fine. My current thought is character encoding when using TextPad.
My biggest problem is that DTEXECUI doesn't tell you that the config file isn't going to be used. It just ignores the "bad" config format and goes on its merry way using the information in the package itself. That really scares me.
Has anyone else seen this? What is the problem being introduced into the config file?
Thanks,
Frank
SQL 2012 :: Query Based On Column Name / Setting Up Database Table
Oct 30, 2015I have a set of data spread across a number of tables regarding stock market data. An example of this follows:
Market Capitalization...
Date CompA CompB
01/01/11 100 5
02/01/11 102 4
Share Price....
Date CompA CompB
01/01/11 100 100
02/01/11 101 99
Event Data...
Date Company
01/01/11 CompA
02/01/11 CompB
Pretty simply, I need a way to retrieve the market capitalisation and share price data based on the event data. So for instance I say 'oh, there is an event on the 01/01/11 involving company A, the market capitalisation on this day was 100, then for the next event it was 4 for company B.
I can also transpose the data so that the company name is in the rows and the dates in the columns for the market cap and share price tables, but this leads to the issue that when I try and get the data, I don't know how to query the correct company for that date.
For instance:
SELECT Event.Date, Event.Company
FROM Event
how do I now say.....
SELECT MarketCapitalisation.Column
WHERE Column = Event.Company
AND MarketCapitalisation.Date = Event.Date.
I have played around with a few basic joins, but I am having issue with the principle of that second to last line of SQL (so only getting the correct column).
I still have a copy of the data in excel so can flip things around as needed, but that would only mean that I would have the issue of WHERE Column = Event.Date instead of Event.Company.
Setting Page Break In Matrix Report Based On RowNumer
Mar 17, 2008Hi,
Do some one has idea how we can create pagebreak in matrix based on RowNumber.Say I have 40 rows in matrix(I don't have group on which I can create a group by setting PAGE BREAK AT THE END)
=ROWNUMBER(NOTHING) MOD 10 = 0 ,TRUE,FALSE
I don't have any distinct value on which I can create Group like
= IIF(Parameters!PageBreak.Value,Fields!CategoryID.Value,1) and setting Page Break At The End
-Thanks,
Digs
Need Help - ConnectionStrings In Web.config File
Jan 3, 2007I think I've read to many post and now I'm confused on what the exact syntax should be and where in the web config file for connecting to my remote database on the web. I have a SQL Server 2005 database on the web. It was imported by the host from the members ID and passwords database created in Express 2005 from the ASP.Net configuration
Here's the portion of the config file:
<configuration>
<appSettings/>
<connectionStrings/>
<system.web>
Here is the sample connection string my host provided:
DRIVER={SQL Server};SERVER=sqlserver5.loosefoot.com;UID=myadmin;PWD=Your Password;database=eldersmembers
Would someone show me exactly what and where the syntax for the connection string goes?
Thanks in advance
Password In Config File
Nov 6, 2006Hi
Config files doesn't seem to work for me :( i want to make a common config file for all of my packages but it is giving me error
Validation error. The AcquireConnection method call to the connection manager failed with error code 0xC0202009
I have placed pasword in my config file too. Following is config file which i m using, server name n password are masked.
<?xml version="1.0"?>
<DTSConfiguration>
<DTSConfigurationHeading>
<DTSConfigurationFileInfo GeneratedBy="SSBKD955688" GeneratedFromPackageName="DTS_ACPM_MarkIT_Indices_Composites_TRAC_X_Asia_Index" GeneratedFromPackageID="{CB3660A1-5E6C-4D08-A7F5-4D6CE83578E2}" GeneratedDate="2006/10/19 14:05:20"/>
</DTSConfigurationHeading>
<Configuration ConfiguredType="Property" Path="Package.Connections[ssmgn838.ACPM.acpmuser].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>server=########;user id=sa;password=******;database=ACPM;
</ConfiguredValue>
</Configuration>
<Configuration ConfiguredType="Property" Path="Package.Connections[Text File (Source)].Properties[ConnectionString]" ValueType="String">
<ConfiguredValue>C:acpmfiletouploadCopy of ACPM_MarkIT_Indices_Constituents_Composites_1_ITRAXX_ASIA.tab
</ConfiguredValue>
</Configuration>
</DTSConfiguration>
How To Use Config File In SQL-CLR Function
Mar 25, 2008Hi to all
i have sql-clr function written in C#.
i am performing data access opertaion using ado.net with in this function.
what i want ,to store connnection string inside config file.(appesettings)i got one article about this which is bellow
http://www.sqljunkies.com/Article/6CA864E9-E107-408E-B30A-4BA15B0CD11C.scuk#[4]
i follow all steps given in article.
but still getting error when executing function
A .NET Framework error occurred during execution of user-defined routine or aggregate "Amex":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at UserDefinedFunctions.CreateExpense()
can anybody help me....
thanks in advance,
Chetan S. Raut
Config File Issue
Jun 11, 2007I am using package configurations to store connectionstring for my database.
While moving the package from development to production, I used the same configuration files (without changing the connection string to point the database to production one). I ran the package in production and it updated the development database.
Then I changed the config files in production to point it to the production database.
However, the packages are still pointing to dev. database.
It seems that after running the package one in Production, it is not reading the updated config file for subsequent runs.
How to I make sure that the packages now read the latest config files.
SSIS - XML Config File
May 17, 2006
Hi All,
I am looking for help trying to configure an XML dtsconfig file, I am using different connection managers for my Source and Destination Databases, I am also using an XML dtsconfig file to set these parameters dynamically, however I dont want to use userid/password to connect to my source/destination databases and instead use trusted connection or windows authentication, I remember we had an option in earlier DTS where we could set it up as 0 or 1and use windows authentication, I cannot find this in the new SSIS package properties.
Appreciate your help in advance.
Config File Question
Aug 9, 2007In my SSIS packages I'm adding a config file that is storing only the connection strings to the database. Though most of my packages are reading or writing out flat files as well as connecting to a db. Do I need to put both db connection string and flat file connection strings in the config file? I'm currently adding only the db connection due to that will change depending which environment its ran in, unlike the flat file location will always remain the same.
I just want to be sure I don't need both for my package to run successfully.