Strange Result When Trying To Use The IsMissing Property
Mar 7, 2007
Hello once again !
This is a very strange result I had noticed while using the IsMissing property.
I had the following Expression.
=IIF(Fields!CertainField.IsMissing = True, "N/A", Sum(Fields!.CertainField.Value))
This should just put the text "N/A" into the textfield if the specific field is missing from the dataset in the report.
I had put this in the following TextFields:-
The group's detail and the table Footer.
The result that I had gotten show an empty textfield in the group's detail but showed "N/A" in the page footer. So I know that the expression I had used is correct since it showed up in the table footer. What baffles me is that it didn't give me a "#Error" in the textfield, but an empty string (which essentially is correct, since this particular field in the dataset is missing to begin with).
The question I am asking is, does this have anything to do with the scope or is it something else, and how do I resolve this problem ?
We're trying to launch a SSIS package throw a webservice. This WebService loads a configuration file with "myPackage.ImportConfigurationFile(pathConfig)" and assigns values to some variables with "myPackage.Variables.Item(Parameters.Keys.Item(i)).Value = Parameters.Item(i)". Seems to work.
I we execute the webmethod of the WebService, this works but only when we execute first time, the SSIS finished succesfully. But when we execute a second time the Webmethod, we've a lot of failure with messages:
The result of the expression "@[User::Email]" on property "ToLine" cannot be written to the property. The expression was evaluated, but cannot be set on the property.The result of the expression ""The Import of " + @[User::ExcelFile]+ " is successful"" on property "MessageSource" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Hi Folks!I have a strange thing happening; I have a field that Counts the number ofrecords and another field that shows the number of clients(Count(RecordID)=3 and NoOfClients=2) When I do a simple expression"Count(RecordID)/NoOfClients" the expected result should be 1.5. Insteadthe result I get is 1. Any ideas?Thanks!Rick
I have a SQL Server 200 database called sampleDB in my local server that uses windows authentication, and all objects are owned by 'dbo' user. I created a creation script using the Eneterprise Manager 2000. Then I ran the script against a database on my hosting service provider's server, that uses sql server login and not windows authentication, and I found some stored procedures were created with 'dbo' as owner and some with 'sun21170' as owner. 'sun21170' is the user for the database of my hosting service provider's server. Why should not all stored procedures be owned by dbo, as they were in original database on my local server?
Hello all, I have the following Stored Procedure that has been working perfectly for the last year: CODE ==================================================== DELETE FROM tblReportMainMembers INSERT INTO tblReportMainMembers (emplid, userid, membership, price, approvecode, purchasedate, wpecend)SELECT DISTINCT tblCart.emplid, tblCart.userid, tblCart.membership, tblCart.Price, tblcart.approvecode, tblCart.addedcart, tblCart.addedcart + 365FROM tblCart INNER JOIN tblMemberships ON tblCart.membership = tblMemberships.idWHERE (tblMemberships.type = 'MAIN') AND approvecode IS NOT NULL AND approvecode <> 'X44444444444' UPDATE tblReportMainMembersSET tblReportMainMembers.fname = tblRecords.fname, tblReportMainMembers.lname = tblRecords.lname, --tblReportMainMembers.userid = tblRecords.id, tblReportMainMembers.address = tblRecords.home_address, tblReportMainMembers.city = tblRecords.city, tblReportMainMembers.state = tblRecords.state, tblReportMainMembers.zip = tblRecords.zip, tblReportMainMembers.homephone = tblRecords.home_phone, tblReportMainMembers.officephone = tblRecords.office_phone, tblReportMainMembers.email = tblRecords.email, tblReportMainMembers.signup = tblRecords.signupFROM tblRecordsWHERE tblReportMainMembers.emplid = tblRecords.emplid UPDATE tblReportMainMembersSET tblReportMainMembers.membership = tblMemberships.membershipFROM tblMembershipsWHERE tblReportMainMembers.membership = tblMemberships.id UPDATE tblReportMainMembersSET tblReportMainMembers.emplid = Onecard.dbo.Accounts.CustomFROM Onecard.dbo.AccountsWHERE tblReportMainMembers.emplid = Onecard.dbo.Accounts.Account SELECT RTRIM(emplid) AS EMPLID, RTRIM(userid) AS USERID, RTRIM(fname) AS FNAME, RTRIM(lname) AS LNAME, RTRIM(membership) AS MEMBERSHIP, CAST(price AS varchar(12)) AS PRICE, RTRIM(approvecode) AS APPROVECODE, CONVERT(varchar(20), purchasedate, 101) AS PURCHASEDATE, CONVERT(varchar(20), wpecend, 101) AS WPECEND, RTRIM(address) AS ADDRESS, RTRIM(city) AS CITY, RTRIM(state) AS STATE, RTRIM(zip) AS ZIP, RTRIM(homephone) AS HOMEPHONE, RTRIM(officephone) AS OFFICEPHONE, RTRIM(email) AS EMAIL, signup AS SIGNUP FROM tblReportMainMembersWHERE fname IS NOT NULL AND lname IS NOT NULLORDER BY lname As you can tell from the procedure, i copy some records into a report table, do some modifications, and then send the results to the browser. But all of a sudden, i'm getting timeouts on all my users. But here is the strange part, when i take the above code and run it using Query Analyzer, it works. And then after that, my users are OK running the clients for about 1 week. And then it starts acting up again. Everytime i run the code in Query Analyzer, i have no more problems for about a week. Weird isn't it. Any ideas? Thanks in advance.Richard M.
This simple query is designed to show all members with the same first name as other members. The result I got shows duplicates of existing members an inconsistent number of times even though I specified not to show duplicates with WHERE m1.MemberID <> m2.MemberID
2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 2 Scharenguivil Rodney 1958-06-24 00:00:00.000 5 O'Grady Patrick 1975-09-23 00:00:00.000 7 Greenfield Lynne 1955-07-26 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000 8 Harvy Simon 1965-08-27 00:00:00.000
Any help in explaining where I have gone wrong here would be greatly appreciated.
Hi I have two database called test1 and test2 both are of same size(both device and log). I run the following statements to take the backup of both the databases using the tape device 'tapedevice1'. dump database test1 to 'tapedevice1' dump database test2 to 'tapedevice1' After this I run the load statement load database test2 from 'tapedevice1'. It just restore the database test2 with test1 data which is added first to the tape with a warning. Is there any restriction that in a single tape backup of more than one database is not possible. Your valuable suggestion appreciated Jiji
this is my problem in stored procedure 1) TABLE SilokE IS MY TABLE OF THE ALL EMPLOYEE 2) I need to see only the employee than in the table v_un 3) i see all the employee 4) problem in this line ( [new date] = @mydate2,[new_shift2] = ) i see 2 rows of result from fields [new date] + ,[new_shift] how to see only 1 field for each field
new date new_shift new date empid name --------------------------------------------------------------- 2007-12-01 99 2007-12-02 99 2568947 aaa 2007-12-01 99 2007-12-02 99 2845209 bbbb 2007-12-01 99 2007-12-02 99 4807756 ccc 2007-12-01 99 2007-12-02 99 9819590 ddd 2007-12-01 99 2007-12-02 99 10055648 eee 2007-12-01 99 2007-12-02 99 10815413 ffff 2007-12-01 99 2007-12-02 99 11070042 gggg 2007-12-01 99 2007-12-02 99 11162047 hhh ------------------------------------------------------------------------------- i need to see only one for each field
new date new_shift id name --------------------------------------------------------------- 2007-12-01 99 2568947 aaa 2007-12-01 99 2845209 bbbb 2007-12-01 99 4807756 ccc 2007-12-01 99 9819590 ddd 2007-12-02 99 10055648 eee 2007-12-02 99 10815413 ffff 2007-12-02 99 11070042 gggg 2007-12-02 99 11162047 hhh
Code Block DECLARE @yeara [varchar](4) DECLARE @month1 [varchar](2) DECLARE @day1 [varchar](2) DECLARE @day2 [varchar](2) DECLARE @day3 [varchar](2) DECLARE @mydate1 [datetime] DECLARE @mydate2 [datetime] set @yeara ='2007' SET @month1 ='12' Set @day1 ='1' Set @day2 ='2' set @mydate1 = CONVERT([datetime] ,@day1 + '/'+ @month1 + '/' + @yearA ,103) set @mydate2 = CONVERT([datetime] ,@day2 + '/'+ @month1 + '/' + @yearA ,103) SELECT SilokE CASE WHEN (empid IN (SELECT empid FROM SilokEWHERE (shift = 51 ))) THEN 1 WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) THEN 2 else 99 END, [new date] = @mydate2,[new_shift] = CASE WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 11 ))) THEN 1 WHEN (empid IN (SELECT empid FROM v_un WHERE (shift = 12 ))) THEN 2 else 99 END, SilokE.empid, SilokE.Fname FROM SilokE
It seens that a simple script:Update sysxlogins set name = 'AA001' + substring(name, 9, LEN(name)-8)where name like 'ILLINOIS%'Will replace the SQL2000 domain name correctly in sysxlogins:ILLINOISJonesP becomes AA001JonesPBut for some strange reason via ILLINOISJonesP can still logon viaQueryAnalyzer although he is no longer in the sysxlogins tableanymore? SQL has been stop/started, server even rebooted, yet BOTHthe new and old logins seem to both allow QA login, any thought howthe old one is getting thru SQL security?thanks in advance for any help...
I have a very strange problem using SQL Server 2005
I have several machines running an application, the problem is that on all machines except one of them the size of the result set that gets returned when I execute the following query is dfferent:
Select * from custoemr where EmployeeID = 3
on three out of the four machine the size of the result set is 1000, where on the other machine the size of the result set is 250, No errors are generated..
Can someome please teel me how to preceed in resolving this issue..
Error 3 Error loading MLS_AZ_PHX.dtsx: The result of the expression ""C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1
Directly using C:sql_working_directoryMLSAZPhoenixDocsArmls_Schema Updated 020107.xls as connectionString works
However - I'm trying to deploy the package - and trying to use expression: @[User::DIR_WORKING] + "\Docs\Armls_Schema Updated 020107.xls" which causes the same error to occur
(Same error with other Excel source also: Error 5 Error loading MLS_AZ_PHX.dtsx: The result of the expression "@[User::DIR_WORKING] + "\Docs\Armls_SchoolCodesJuly06.xls"" on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property. c:documents and settingsviewmastermy documentsvisual studio 2005projectsm l sMLS_AZ_PHX.dtsx 1 1 )
I am having problems exporting data into a flat file using specific code page. My application has a variable "User::CodePage" that stores code page value (936, 950, 1252, etc) based on the data source. This variable is assigned to the CodePage property of desitnation file connection using Property expression.
But, when I execute the package, the CodePage property of the Destination file connection defaults to the initial value that was set for "User:CodePage" variable in design mode. I checked the value within the variable during runtime and it changes correctly for each data source. But, the property of the destinatin file connection doesn't change and results in an error.
[Flat File Destination [473]] Error: Data conversion failed. The data conversion for column "Column01" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
[DTS.Pipeline] Error: The ProcessInput method on component "Flat File Destination" (473) failed with error code 0xC02020A0. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.
If I manually update the variable with correct code page and re-run the ETL, everything works fine. Just that it doesn't work during run-time mode.
I developed a simple custom control flow component which has several read/write properties and one readonly property (lets call it ROP) whichs Get method simple returns the value of a private variable (VAR as string). In the Execute method the VAR has a value assigened. When I put the value of ROP or VAR into MsgBox I can see the correct value. However when I execute the component I can not see the value of the ROP in the property window. I see the property but its value is empty string. For example when I put a breakpoint to postexecute or check the property before click OK in a MsgBox I would expect that the property value would be updated in SSIS as well. Is there a way how to display correct values of custom tasks properties in property window?
Untill recently I had a smooth running SSIS package,but suddenly it throws error syaing "OnError,,,,,,,The result of the expression
"@[User:trTextFileImpDirectory] +"SomeTextStringHere"+ @[User:trANTTextFileName] +(DT_STR,30,1252) @[User:taging_Date_Key]+ "SomeTextStringHere" " on property "ConnectionString" cannot be written to the property. The expression was evaluated, but cannot be set on the property."
I have child SSIS package running under a parent package (through execute package task)
I have few flat file connection managers in child package for text file import , in which I am building text file path dynamically at run time by assigning an expression in connection string property of connection manager. The Expression is as follows
Where @[User:trTextFileImpDirectory] is a variable which contains path of directory containg text files.Value in this variable is assigned at runtime from parent package's variable,which in turns fetch value from a configuration file on local server.
With my current configuration this path has been configured to some other server's directory over network ( I.e my package picks text files from some other servers folder over network)
While "Some string here"+ @[User:trANTTextFileName]" part of file name string.
(DT_STR,30,1252) @[User:taging_Date_Key] Contain the date of processing ,value in this variable is also picked up at run time from parent package variable.
1) So can someone give me some insight into possible reason of failures. 2) Is it possible that problem arises if directory (from which I m picking text files) is assigned password or is there exist some problem in accessing forlders over network ? 3) Or there can be some problem in package configuration at design time( I.e where I m assigning value in variable from parent package vriables)?
The header table row for G4 contains 3 textboxes containing the sums of the contents within G4. The header table row for G4 is visible while it's contents, including the G4 footer table row, is kept invisible until the report user drills down into the group.
When the report user drills down into G4 the footer table row becomes visible and the sums of the contents of the group are displayed for a second time.
At this point I want the sums in the header to be set to invisible when the sums in the footer are made visible by the drilldown.
When I try to reference the hidden property of textbox66 in the G4 footer in order to set the hidden property of header textbox57 in the G4 header I get to this point...
=IIF(reportitems!textbox66.
When it fails to give me an option of choosing the .Hidden property and instead only gives me a .Value.
If I complete the IIF statement manually so that it spells out .....
So my question is, how do I reference the hidden property of one or more textboxes in a group to use as condition checks to set the hidden property of another textbox in that same group?
Thank you for any help you can provide. We are only now beginning to implement reporting services and I have not yet had the chance to research this in greater detail for lack of time.
I 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
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error: [Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
---------------------------------------------------------------------- I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
HI, I ran a select * from customers where state ='va', this is the result...
(29 row(s) affected) The following file has been saved successfully: C:outputcustomers.rpt 10826 bytes
I choose Query select to a file then when I tried to open the customer.rpt from the c drive I got this error message. I am not sure why this happend invalid TLV record
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC. Tables I have are structured as follows:
SHOWS showID showTitle
SHOWACCESS showID remoteID
VIDEOS videoDate showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle, (SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID) FROM shows, showAccess WHERE shows.showID = showAccess.showID AND showAccess.remoteID=21 ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate. Can anyone shed some light on where I am going wrong?
I am trying to code a WHERE xxxx IN ('aaa','bbb','ccc') requirement but it the return values for the IN keyword changes according to another column, thus the need for a CASE function.
WHERE GROUP.GROUP_ID = 2 AND DEPT.DEPT_ID = 'D' AND WORK_TYPE_ID IN ( CASE DEPT_ID WHEN 'D' THEN 'A','B','C' <---- ERROR WHEN 'F' THEN 'C','D ELSE 'A','B','C','D' END )
I kept on getting errors, like
Msg 156, Level 15, State 1, Line 44Incorrect syntax near the keyword 'WHERE'. which leads me to assume that the CASE ... WHEN ... THEN statement does not allow mutiple values for result expression. Is there a way to get the SQL above to work or code the same logic in a different manner in just one simple SQL, and not a procedure or T-SQL script.
I€™m suffering a queer behaviour when I use BIDS. Concretely, when I open a dtsx from my project (it has 10 packages) many times Sequence Container and Data Flow tasks are invisible. I mean, its lines are not visible at all whereas its titles are. I mean, what you see is just a white box€¦
Then, I€™m gonna Data Flow layer and I have to do double-clik over the tasks and are visible but on Control Flow I don€™t see how to solve.
Curiously in our development and production server such behaviour doesn€™t happen (we are accessing by mean Terminal Server from our workstations)
How odd!. Everything is fine except this.
I want to remark you that such project has been copied from the server, this is, these packages are been built on the server
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. Code and results are below.
declare @start smalldatetime declare @end smalldatetime set @start = '2015-03-22 00:00' -- this should be a Sunday set @end = '2015-03-28 23:59' -- this should be the following Saturday
In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.
Hi, After many nights without sleep I'm not seeing this? Can anyone help why I'm getting a ConnectionString Property not set error? thanks! Dim Sconn As StringDim DBCon As New Data.SqlClient.SqlConnectionSconn = ConfigurationManager.AppSettings("LocalSqlServer")DBCon = New SqlClient.SqlConnection(Sconn)Dim cmdCommand As New Data.SqlClient.SqlCommand 'Dont forget to instantiate a connection object cmdCommand.Connection = DBConDBCon.Open()
I would like to create table by T-SQLand need to specify DescriptionBut I can't find any sample to add Description property by T-SQL Additionally, I also would like modify Description property by T-SQL. What can I do ????? Please help me ......
Hello friends, I am using sql server 2005. In some tables to create the column Autoincrement I had set the 'Idetity Specification' property to 'Yes'. I want to know that how can we do it through sql scripts i.e. by writing query. Please let me know Thanks & RegardsGirish Nehte
I'm trying to see the properties of a database and I'm getting the following error, even when trying to connect as sa.
Property Owner is not available for Database '[EPro]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights.
I am use Reporting services 2000 SP2 and discover that the LineHeight property rendered correctly only in renderers for HTML output. PDF renderer, TIFF renderer and client side printing client do not render changes in this property at all.
Is it bug in Reporting services 2000?
Can someone try to change LineHeight property in SQL Server 2005 Reporting services and verify rendering output?
within a data flow task I have two OLE DB sources that I am attempting to put into a merge join.
When I connect them to the merge join transformation I get the following error:
The IsSorted property must be set to true on both sources of this transformation.
I understand what this means but I cannot see an IsSorted option in the properties of the OLE DB sources so I can't set it. Can you tell me where it is? for the time being I'm having to put a sort transform in to get it to work.
