I've created a parameter in my report that gives a list of incident #'s which are in the int format. I've always used a Union Select 'All' to allow the user to pull up one item or all items. In this case, I can't get it to work because of the formatting difference.
Here is the code for the dataset called Incident that I'm using for the parameter:
Code Block
Select Incident
from Table
Union Select ' All'
order by 1
This is in the where clause of the main dataset:
Code Block( Incident = @Incident and @Incident<> ' All'
or Incident <> @Incident and @Incident = ' All')
I have this working on a bunch of reports but this is the first time I've tried to use this theory on an integer field. Any ideas?
I have a report that shows all the jobs for a given resource. i.e. there are three jobs waiting to be run through the CNC Machine resource. Each job has more than one resource so I use a filter to get information for just that resource. i.e. just 3 lines for the CNC Machine. However on the report I need to put the resource_id that the job just came from and resource_id for where the job is going next. I still just want 1 line per job.
I have a stored proceedure that is adding a record to a database table. When the record is added using an insert statement, the ID field is autogenerated. I have a second insert statement that inserts into a second table, however, I want/need? to use that ID field in order to link this additional information to the proper record in the initial table. Is there an easy way to do a select or just pull the ID? I was thinking I could do a select before the final insert using 2 or 3 required fields of which used in a select altogether would be unique, but before I did that, I wanted to see if I was missing a better way. I posted the code below.... set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go
ALTER PROCEDURE [dbo].[Add_Employee_Data] ( @LastName as varchar(100),@FirstName as varchar(100), @MiddleName as varchar(100),@Position as varchar(100), @Department as varchar(100), @DirectDial as varchar(100), @Ext as varchar(100), @Fax as varchar(100), @HomePhone as varchar(100), @CellPhone as varchar(100), @Partner as varchar(100), @TimeKeeper as varchar(100), @Notary as varchar(100), @Practice as varchar(100), @SummerAddress as varchar(250), @SummerPhone as varchar(100), @LegalNonLegal as varchar(100), @Bar as varchar(100), @OtherEmail as varchar(100), @HomeAddressComplete as varchar(100), @HomeAddress as varchar(100), @HomeCity as varchar(100), @HomeState as varchar(100), @HomeZip as varchar(100), @School as varchar(100), @Degree as varchar(100), @Status as varchar(100), @Floor as varchar(100)) AS DECLARE @Code as varchar(100)
INSERT Into tblMain2(LastName,FirstName,MI,Position,Dept,Extension,DirectDial,[FAX DID],HomePhone,CellularPhone,SpousePartner, TimeKeeper,Notary,PrGroup,SummerAddress,SummerPhone,LegalNonLegal,Bar,OtherEmail,HomeAddressComplete,HomeAddress,HomeCity, HomeState,HomeZip,Floor,Active) Values(@LastName,@FirstName,@MiddleName,@Position,@Department,@Ext,@DirectDial,@Fax,@HomePhone,@CellPhone,@Partner,@TimeKeeper, @Notary,@Practice,@SummerAddress,@SummerPhone,@LegalNonLegal,@Bar,@OtherEmail,@HomeAddressComplete,@HomeAddress, @HomeCity,@HomeState,@HomeZip,@Floor,@Status) <<<<< Put select statement here to pull in @Code where LastName = @LastName and Extension =@Ext ?? INSERT Into Education(Code,CollegeSchool,DegreeCert) Values(@Code,@School,@Degree)
We have a custom web C#/SQL2K8R2 workflow application that I need to pull Oracle data into a varchar(max) field as an XML DOM document. I have no problem pulling the Oracle data using OLEDB, but I'm not sure how to create the XML DOM doc. Once I get it into the DOM doc, I then need to assign metadata about the XML DOM doc and insert it all into a staging table:
CREATE TABLE [stg].[EtlImports]( [EtlImportId] [int] IDENTITY(1,1) NOT NULL, [EtlSource] [varchar](50) NOT NULL, [EtlType] [varchar](50) NULL, [EtlDefn] [varchar](max) NULL, --Either a SQL statement or path to file on disk [EtlData] [varchar](max) NULL, --BLOB field to hold the XML data or FILESTREAM path to file on disk [EtlDateLanded] [datetime] NOT NULL, [EtlDateProcessed] [datetime] NULL, [EtlStatus] [varchar](50) NULL, [Comments] [varchar](4000) NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
I will have a separate SSIS package to pull the [XML/File] field and process the data into the workflow tables. Is there a wasy I can use the ADO Record-set Destination task to accomplish this, or do I have to create a custom C# script to create the XML DOM Doc?
Hi, Im trying to pull data from 2 fields in the same table into a SqlDataSource that feeds into a GridView, and display them as 1 field in GridView? I have a database table that has entries of users and their friends. so this tblFriendUser has a column called UserName and another column called FriendUserName. I am trying to get a list of friends for that particular user. Note that if User1 initiated the friend request, he will be listed as UserName and his friend as FriendUserName, but if his friend initiated the friend request, it will be vice versa: him being the FriendUserName and his friend the UserName. So I want the following 2 queries run and merged into one query in order to return 2 columns only: UserFriendID & UserName, is that possible? Is my design bad? Any suggestions/advice would help! Thanks a lot!
SELECT UserFriendID, UserName FROM tblUserFriends WHERE (UserName = @UserName);
SELECT UserFriendID, FriendUserName AS UserName FROM tblUserFriends WHERE (FriendUserName= @UserName);
We have 1000s of SSRS reports hosted on the SQL 2008 R2. All reports are supposed to have all parameters "hidden". Random reports have been reported to expose the parameter when developers accidently deploy to test servers. Is there a way to identify the reports and the parameter property (Hidden, Visible, Internal) from the Report server Database?
I checked executionlog2 but can not find the parameter property.
I'm developing a Windows Mobile application, which is using RDA Pull for retrieving data from SQL Server 2005 database to PDA. Please, see the example:
Code Snippet
using (SqlCeEngine engine = new SqlCeEngine(connStr))
the sqlcesa30.dll cannot connect to SQL Server database.
In the sqlcesa30.log then I found following line:
Code Snippet
2007/04/17 10:43:31 Thread=1EE30 RSCB=16 Command=PULL Hr=80040E4D Login failed for user 'test'. 18456
The user 'test' is member of db_owner, db_datareader and public roles for the Demo database and in SQL Server Management Studio I'm able to login to the Demo database with using the 'test' users credentials and I'm able to run the select command on 'mytable'.
So, what's wrong? Why the sqlcesa30.dll process cannot login to the Demo database, and from another application with using the SAME connection string it works?
I'm trying to figure out how to pass a field name into a procedure so the procedure can selectively find out the value of different fields. The DB table I'm interested in has multiple bool fields in it and the field name is the parameter I want to pass into the procedure. With the example code I list below, there is only one row that exists in the table so the end result should be dependent upon the value in the field. The calling procedure is "attempting" to pass in the name of the field, and the called procedure should use the paramter passed in as the field to return in the table. If the field returned in the called procedure is true, the called procedure returns "0" to the calling procedure, otherwise it returns 1. The syntax I have doesn't seem to work in that the called procedure always returns true from the field and the calling procedure always gets 0 back from the called procedure.
In the calling procedure, I'm doing a select just to find out if a 0 or 1 was returned, but this will not be the final version of the code, its just for test purposes.
Any help on what I'm doing wrong greatly appriciated.
- Bruce
ALTER PROCEDURE [dbo].[caller]
AS
BEGIN
SET NOCOUNT ON;
DECLARE @include int,
@Committee bit
execute @include = dbo.callee @Committee
IF @include = 0
BEGIN
select * from ProvCompensation
END
ELSE
BEGIN
select * from ProvCommittee
END
END
ALTER PROCEDURE [dbo].[callee]
(@fName bit)
AS
BEGIN
DECLARE @aBool bit
SET NOCOUNT ON;
select @aBool = @fName from ProvisionsPlanSum where PlanId = -99
say I have a table with field1,field2,field3,....and have a SP with a parameter @myfieldis there a way I can doSelect @myfield from Table1thanks for your help
I have report based on a cube and it is working. What it does is display a matrix based on six parameters selected. The matrix displays with the following results (there is some more, but this is the gist):
Job Level Headcount
Partners 102
Managers 85
Associates 98
By clicking on a number it properly navigates to the next report (also based on a cube) and properly passes all of the parameters. All of these paramters are dimensions.
But the rows (Partners, Managers, Associates) are not a parameter in this first report. So when I click a number and it navigates to the next report, it is not passing the JobLevel (Partners, Managers, Associates) I clicked on.
When I add the JobLevel to pass to the parameters in the second report, I can only add it as a Fields!JobLevel.Value, I can't pass it as a Dimension or a Set - which is what I assume the second report needs.
I tried StrToSet in the Navigation expression, but it doesn't recognize StrToSet in that expression.
How can I pass a Field value to populate a parameter in a second report that uses MDX for its parameters?
If we make a change to a parameter and tab to the next parameter (or use the mouse to get to another parameter field), the cursor is not in the next parameter field. This behavior is seen in Reports Manager reports. Is there a fix for this problem?
Hi, I'm using the SQL Datasource control. I want to use a value of a row in a session control parameter. I have something like this now: <asp:SessionParameter Name="ArticleType" SessionField="Article_type_" & number_in_gridview /> number_in_gridview must be filled with a value in the gridview of the current row. Does anybody how to do this ? Regards, Vortex
Another table with an uniqueidentifier which references the user iD
I want to set the value of the iD in one of the entries in the second table to null.
I tried this: string assigned is null, i am passing this as a method parameter ............... string queryString = "UPDATE [table2] SET AssignedUserId=@assigned WHERE ProblemId = @id"; System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); ................... System.Data.IDataParameter dbParam_au_pr = new System.Data.SqlClient.SqlParameter(); dbParam_au_pr.ParameterName = "@assigned"; if (assigned == null) { dbParam_au_pr.Value = null; } else { dbParam_au_pr.Value = assigned; } dbParam_au_pr.DbType = System.Data.DbType.String ; dbCommand.Parameters.Add(dbParam_au_pr);
I also tried using "" instead of null, or not using that "if" statement at all. I get an error that says:
Parameterized Query '(@id nvarchar(1),@assigned nvarchar(4000))UPDATE [tracker_Probl' expects parameter @assigned, which was not supplied. Please help with this as soon as possible.Thanks
But it does not work, and when I just want to use the format property of the textbox content such as "dd/MM/yyyy", it displays "dd/MM/yyyy", not the real formatted data, this property works for "=Today()" but not for parameters and fields. What is the diffrence?
Have you an idea to format a Parameter/Field of Date type such as dd/MM/yyyy?
I have two tables: eg. a person-table (no nulls allowed), with an idand so on, and a person_course table (an intermediate table in amany-to many relationship between person table and courses tables),with two fields person_id and course_id.But I want to make ONE multipurpose stored procedure, which has ONLYoptional parameters on all fields in the person table AND the fieldcourse_id in the person_course table.I have no problems making optional parameters on the person table (eg.P.ID=ISNULL(@PersonID, P.ID ) ) BUT the problem is, when I try to addan optional parameter on the field course_id it dosn't produce theright results. Some times the course_id is null (because some personshavn't joined a class yet).Is there a way around it?Ole S. Pedersen
I am currently writing a report that will use as one of the filters the date (in this case it is the date of service of a physician office visit).
however the DB developers included the date and the time of the visit in one field so my resulting data set contains 4/13/2007 4:30pm for example, so using an @date parameter as a filter on the data field doesn't work because when you run the report and enter the date in the parameter it doesn't return anything.
I am an SQL Report writer amateur so I am sure there is an easy fix, any and all help will be appreciated.
I have a report where I'm showing a list of users to select. When previewing the report, the width of the field is automatically set to the largest label in the dropdown list. But when I change the parameter to have "Multi-Values", then the width is set to a fixed value and a horizontal scrollbar appears. Does anyone know how to have a "Multi-Values" parameter, but keeping the lasgest width of the labels in the dropdown list ?
SELECT ArticleID, ArticleTitle, ArticleDate, Published, PublishDate, PublishEndDateFROM UserArticlesWHERE (ArticleDate >= PublishDate) AND (ArticleDate <= PublishEndDate) When I use the above on a GridView I get nothing. Using SQL manager it works great. I don't know how to pass the value of the ArticleDate field as a default parameter or I think that's what I don't know how to do. I am trying to create a app that I can set the dates an article will appear on a page and then go away depending on the date. Thanks for any help!
I have two reports 'Product Details' and 'Sales by Product'
When i click on 'Product ID' field in 'Product Details' report, It has to drill through to 'Sales by Product' which has a parameter 'Product ID' in it.
I have a gridview connected to a sqldatasource, and it works pretty good. It gives me the subsets of the information that I need. But, I really want to let them choose all the companies and/or any status. What's the best way to get all the values in the gridview...besides removing the filters :) I thought the company would be easy, I'd just set the selected value to blank "", and then it'd get them all....but that's not working. And, for the boolean, I have no idea to get the value without having a separate query. (tabs_done=@tabsdone) and (company like '%' + @company + '%')1 <asp:DropDownList ID="drpdwnProcessingStatus" runat="server"> 2 <asp:ListItem Value="0">Open</asp:ListItem> 3 <asp:ListItem Value="1">Completed</asp:ListItem> 4 </asp:DropDownList> 5 6 7 <asp:DropDownList ID="drpdwnCompany" runat="server"> 8 <asp:ListItem Value="">All</asp:ListItem> 9 <asp:ListItem Value="cur">Cur District</asp:ListItem> 10 <asp:ListItem Value="jho">Jho District</asp:ListItem> 11 <asp:ListItem Value="sea">Sea District</asp:ListItem> 12 <asp:ListItem Value="san">Net District</asp:ListItem> 13 <asp:ListItem Value="sr">Research District</asp:ListItem> 14 </asp:DropDownList> 15 16 17 <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:HRFormsConnectionString %>" 18 SelectCommand="SELECT DISTINCT [id], [lastname], [company] FROM [hr_term] hr where (tabs_done=@tabsdone) and (company like '%' + @company + '%')"> 19 <SelectParameters> 20 <asp:ControlParameter ControlID="drpdwnProcessingStatus" DefaultValue="0" Name="tabsdone" PropertyName="SelectedValue" /> 21 <asp:ControlParameter ControlID="drpdwnCompany" DefaultValue="" Name="company" PropertyName="SelectedValue" /> 22 </SelectParameters> 23 </asp:SqlDataSource> 24
Hello, My problem is with Validating Date parameter filed, when I user enters wrong (assume user is entering date instead of selecting from date picker). Where following are scenarios I would like to validate.When user enters non-date. When user enters 30/feb/yyyy.When user enters 29/feb/yyyy. (for non-leap year).When user enters 31/mm/yyyy. (for months does not includes day 31)
Could you explain in a little more detail how to accomplish this?
I need to set a field value based on two date time parameter's.What is the correct syntax to allow me to pass the value into the field in my SSRS expression ?
I have a report which displays a customers invoice, in both the companys local currency, and the customers local currency.
The report language is "English (United Kingdom)" The fields showing customers currency language setting is set to something else, i.e. "France (French)" to display the Euro currency.
The application handles 34 currencies, the query returns the language string, ("France (French)"), to allow the report to bind its language setting to the querys output.
However, it doesn't work, a normal textbox will display the correct country name string, but Reporting Services cannot bind the language setting to a query result. So I also tried setting it as a report parameter, but no joy either (all currencys revert to USD).
I'm using =First(Fields!curFormat.Value, "myDataSet") to bind the 'language' setting, the result of this expression returns "France (French)", which is a valid option for this language setting, as it's in the drop down list.
Rather than create 34 seperate reports for each currency, are there any suggestions on how to bind a fields language setting to a query result?
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate] /* Input Parameters */ @TableName Varchar(20), @Dist Varchar(20) AS Declare @SQLQuery AS NVarchar(4000) Declare @ParamDefinition AS NVarchar(2000)
I hope to update a DateTime column value with a Time input parameter. Poor attempt below but it looks like the @ApptTime param is coming in as 10:45:00.0000000 and I might have an existing @SendOnDate as: 2015-10-05 07:00:00.000...I hope to end up with 2015-10-05 10:45:00.000
ALTER PROCEDURE [dbo].[SendEditUPDATE] @QuePoolID int=null ,@ApptTime time(7) ,@SendOnDate datetime
