Using Cint Function In Expression Editor
Hi, I am a new to Reporting Services...
I am using RS2000
I am trying to apply cint function in the expression editor for the textbox
eg. i type in =cint(Fields!firstfield.Value/Fields!secondfield.Value).
This is returning error
The value expression for the textbox €˜HoldDuration€™ contains an error: Arithmetic operation resulted in an overflow.
Could you please let me know what I did wrong in this and how to correctly apply VB or any custom functions in the expression editor where the expession output would be a decimal value and you are interested in getting an integer value by rounding the fractional part.
View Complete Forum Thread with Replies
Related Forum Messages:
Expression Editor - Avg Problem
This is my data set PersonId Type (varchar 20) 153 NewlyEnrolled 58 8 248 Enrolled 857 23 I am trying to show an avg for all the numbers in the €˜Type€™ field =avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, CDbl(Fields!Type.value))) I get the error €œInput string was not in a correct format.€? If I use: =avg(iif(Fields!Type.Value = "NewlyEnrolled" ORELSE Fields!Type.Value = "Enrolled", nothing, 1)) It works and it returns €˜1€™ so the problem is CDbl(Fields!Type.value). i'm tring to convert it to a number so it can be used in the avg function. any idea what I'm doing wrong?
View Replies !
Cannot Open Property Expression Editor
Hi, I want to change expression of a variable but dont see ellipses icon in expression. The variable property EvaluateAsExpression is assigned TRUE. I have reinstalled SSIS...i am sure there was a icon to open Property Expression Editor for a variable...dont see it anymore. -Amar
View Replies !
Connection Manager Property Expression Editor
Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated. Thanks.
View Replies !
Connection Manager Property Expression Editor
Is it possible to use a property, say name, of an object ( say the connection object) in the "Property Expression" of that object? I would like to modify the Connection String property of a flat file connection manager to append date to it. To do this I need to be able to use the Name property of the connection manager in the Property Expression editor. How ever I get an error that it does not recognize name, it almost seems to suggest I can only use variables. I find it hard to believe since it seems like common requirement to be able to use properties of an object (connection manager) in modifying other properties of the object. Any help would be greatly appreciated. Thanks.
View Replies !
Excel Connection Manager Expression Editor - Variable Problem
Hi, I have a data flow task within a foreach file loop. My problem occurs when I tried to make up an expression for the connection string of the Excel connection manager. Somehow I can see only system variables and none of the variables which I set up within the scope of the foreach file loop. Can anyone provide me any insight into this mystery? As far as I can see, all the variables I created in the foreach file loop are still showing on the variable windows (without the Show All Variable button clicked). Thank you very much and hope to hear from someone soon! Regards, Hsiao
View Replies !
PAD Function In Data Tranformation Editor
I have a value with a float datatype in Excel that is actually a date. For example, the value displays 1272006 for January 27, 2006. I am trying to convert this value to a date. I can do this in several stages using T-SQL by converting the datatype to integer, using replicate to make it eight characters and than using substrings to make it look like 01-27-2006 which SQL Server will recognize as a date. My problem is that there is nothing similar to a PAD (replicate) function using the Data Tranformation Editor in Integration Services. There is a replicate function but it merely functions to repeat a value. It doesn't appear to perform the same PAD function as the T-SQL replicate does. Here is my formula in T-SQL that works okay. right(replicate('0', 8) + convert(varchar, cast([Date] as varchar)), 8) I could do this by creating a staging table but I want to handle the entire transformation process within Integration Services. Can anyone offer me some advice? Thank you. David
View Replies !
Is Possible To Call A VB.NET Function Within Derived Transformation Editor
Hi, In a nut shell I want to be able to instruction some Data Analysts on how to modify SSIS packages using the simpliest solutions possible. This is because there are many different data sources and some of these data sources have a huge number of fields, and yes you guessed it these data sources are subject to change on a regular basis. A very common task they will need to do is to modify an SSIS package to do a to transform of a source date string format of "YYYYMMDD" into a date data type field within a table. Similar threads have advised the use of the Data Flow Transformations->Derived Column for this sort of thing. So within the Expression Text box I have inserted the following SSIS compatible SQL to convert the above string into a british format date data type; - Code Snippet (SUBSTRING(DOB_SRC,8,2) + "/" + SUBSTRING(DOB_SRC,5,2) + "/" + SUBSTRING(DOB_SRC,1,4)) But really what I want to be able to do is to instruct the Data Analysts to do is something like; - ConvertTextToDate(DOB_SRC) Where I previously defined that behaviour of ConvertTextToDate as a public VB.NET function. Can someone please help. I'm pretty certain I'm not the only one with this type of requirement. Thanks in advance, Kieran.
View Replies !
Expression Editor On Custom Properties On Custom Data Flow Component
Hi, I've created a Custom Data Flow Component and added some Custom Properties. I want the user to set the contents using an expression. I did some research and come up with the folowing: Code Snippet IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New(); SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; SourceTableProperty.Name = "SourceTable"; But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name. I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field. Any help would be greatly appreciated! Thank you
View Replies !
Function That Works In Sql Server Management Studio Does Not Work In Derived Column Transformation Editor
Hi I'm a relative SQL Server newbee and have developed a function that converts mm/dd/yyyy to yyy/mm/dd for use as in a DT_DBDATE format for insert into a column with smalldatatime. I receive the following erros when using the function in the Derived Column Transformation Editor. First, the function, then the error when using it as the expression Derived Column Transformation Editor. Can anyone explain how I can do this transformation work in this context or suggest a way either do the transformation easier or avoid it altogerher? Thanks for the look see... ****************************** ALTER FUNCTION [dbo].[convdate] ( @indate nvarchar(10) ) RETURNS nvarchar(10) AS BEGIN -- Declare the return variable here DECLARE @outdate nvarchar(10) set @outdate = substring(@indate,patindex('%[1,2][0-9][0-9][0-9]%',@indate),4)+'/'+ substring(@indate,patindex('%[-,1][0-9][/]%',@indate),2)+'/'+ substring(@indate,patindex('%[2,3][0,1,8,9][/]%',@indate),2) RETURN @outdate END ******************************** And the error... expression "lipper.dbo.convdate(eomdate)" failed. The token "." at line number "1", character number "11" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified. Error at Data Flow Task [Derived Column [111]]: Cannot parse the expression "lipper.dbo.convdate(eomdate)". The expression was not valid, or there is an out-of-memory error. Error at Data Flow Task [Derived Column [111]]: The expression "lipper.dbo.convdate(eomdate)" on "input column "eomdate" (165)" is not valid. Error at Data Flow Task [Derived Column [111]]: Failed to set property "Expression" on "input column "eomdate" (165)". (Microsoft Visual Studio) =================================== Exception from HRESULT: 0xC0204006 (Microsoft.SqlServer.DTSPipelineWrap) ------------------------------ Program Location: at Microsoft.SqlServer.Dts.Pipeline.Wrapper.CManagedComponentWrapperClass.SetInputColumnProperty(Int32 lInputID, Int32 lInputColumnID, String PropertyName, Object vValue) at Microsoft.DataTransformationServices.Design.DtsDerivedColumnComponentUI.SaveColumns(ColumnInfo[] colNames, String[] inputColumnNames, String[] expressions, String[] dataTypes, String[] lengths, String[] precisions, String[] scales, String[] codePages) at Microsoft.DataTransformationServices.Design.DtsDerivedColumnFrameForm.SaveAll()
View Replies !
Function With Expression To Return Values
I have created a function to return values, which works fine, but I can't do calculations in it. CREATE FUNCTION [dbo].[tf_Asset_Portfolio](@deal_id int, @as_of_date datetime) RETURNS TABLE AS RETURN ( SELECT DISTINCT dbo.Assets.issue_id, SUM(DISTINCT dbo.Assets.par_amount) AS par_amount, SUM(DISTINCT dbo.Assets.par_amount) AS market_value FROM dbo.Issue INNER JOIN dbo.Assets ON dbo.Issue.issue_id = dbo.Assets.issue_id INNER JOIN dbo.Issuer_Rating_History ON dbo.Issue.issuer_id = dbo.Issuer_Rating_History.issuer_id WHERE (dbo.Issuer_Rating_History.as_of_date <= @as_of_date) GROUP BY ALL dbo.Assets.issue_id, dbo.Assets.deal_id, dbo.Issue.default_date HAVING (dbo.Assets.deal_id = @deal_id) ) I need to do calculations on market value based on the default date. If default date isn't specified then it should be 100% of par amount. If default date is less than one year ago - 65% of the par_amount. If default date is one or more years ago - 0. I have no idea about how to do this and everything I try wont work. I created another function to do the calculations and this seems to work, but it only does one record instead of all of them. CREATE FUNCTION dbo.tf_Asset_Portfolio2 (@deal_id int, @as_of_date datetime) RETURNS @Market TABLE (issue_id int, par_amount money, market_value money) AS BEGIN DECLARE @ReturnDate datetime DECLARE @DD datetime DECLARE @PA money DECLARE @MV money DECLARE @ID int DECLARE @DateD int SELECT TOP 1 @ReturnDate = LAST_BATCH FROM master..sysprocesses WHERE SPId = @@SPID SELECT @ID = issue_id FROM Assets WHERE Assets.deal_id = @deal_id SELECT @PA = SUM(DISTINCT par_amount) FROM Assets WHERE Assets.issue_id = @ID AND Assets.deal_id = @deal_id SELECT @DD = default_date FROM Issue WHERE Issue.issue_id = @ID SET @DateD = DateDiff("yyyy", @DD, @ReturnDate) If @DD = Null BEGIN SET @MV = @PA END Else If @DD > @ReturnDate BEGIN SET @MV = @PA END Else If @DateD < 1 BEGIN SET @MV = @PA * .65 END Else If @DateD >= 1 BEGIN SET @MV = 0 END insert into @Market (issue_id, par_amount, market_value) values (@ID,@PA,@MV) RETURN END I need to combine the functionality of being able to return mutliple records that isn't in the 2nd function and being able to calculate the market value which isn't in the first one. Please help. Thank you in advance.
View Replies !
SUM Of Report Field With Expression Which Has COUNT Aggregate Function
Hi everyone, I have created a report which has 3 groups. The report output as shown below. I am having trouble getting the SUM of Total Credtis for each Org. Can't seem to get the total 42 and 16 (highlighted), but can get total unists 11 and 13. I get expression contains aggregate function. This is because Units assessed is the Count of IDs (details hidden from the report). Report has three groups Org , Assessor and Unit. Can someone please help me with this? Appreciate help. Thank you, Ski Org 1(Group1) Unit Credits Units Assessed(# of Trainees) TotalCredits Assessor 1 Unit 1 2 4 (Count of Ids) 8 (2*4) Assessor 2 Unit 2 1 2 2 (1*2) Assessor 3 Unit 3 5 2 10 (5*2) Unit 4 2 1 2 Assessor 4 Unit 5 10 2 20 -------------------------------------------------------------------------------------------------------- 11 42 ----------------------------------------------------------------------------------------------------------- Org 2 Assessor 3 Unit 1 2 3 6 Assessor 4 Unit 6 1 10 10 -------------------------------------------------------------------------------------------------------- 13 16 --------------------------------------------------------------------------------------------------------
View Replies !
How To Find Terms In A File Name: Regular Expression OR A String Function
hi, i am using a forEach look to import each file within a folder, but i also need to calculate the dataset related ot these files. the file are named as: ff_inbound_20071008_1.csv ff_inbound_20071008_2.csv where for file ff_inbound_20071008_1.csv: ff => flat file inbound => dataset of this csv 20071008 => date 1=> file count having in mind that they are store in the variable as a full path: z:myFlatFilesexportsproj01ff_inbound_20071008_2.csv i need to extract the dataset and the date for each file. how can i extract these terms from the file name? many thanks, nicolas
View Replies !
How To Convert A Returning String From Custom Code To A Function In Expression?
I have a custom code function that return string like: "SUM(Fields(Parameters!dept.Value + ""_1HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_2HeadCount"").Value) + SUM(Fields(Parameters!dept.Value + ""_3HeadCount"").Value) " How do I convert this string to a function inside the expression (in this case "SUM", "Fields", "Parameters!", etc.)? The expression of my TextBox show the string as a string instead of converting them to proper function and display the corrent value. The reason I need to build the string is because the Fields number is dynamic and I need a loop to increase the number in the field name. Any suggest is highly appreciated! Thanks, Tabbey
View Replies !
Can I Apply A Database Function Or Assembly Call In An Expression For Filter Data?
I need to translate a user€™s regional setting into one of our own language codes before I send it through as a filter to the model query. If our language codes were the same, the filter would look like this in the report filter - Language Code = GetUserCulture() Which translates to this in the database query (for us english) - table.language_code = 'EN-us' And of course I need it to look like this - table.language_code = 'ENG' I would like the logic to be globally available to all report writers (ie not forcing each report writer to have an iif or case stataement). I was thinking custom assemblies or maybe a database function, but at this level of the filter, I cannot seem to figure out how to embed a database function call to apply to the filter criteria like this Language Code = dbo.ConvertFcnIWrote(GetUserCulture()) Or how I would access the custom assembly in the filter expression. Do you have a recommended implementation for this situation? Thanks, Toni Fielder
View Replies !
Expression: !&&"No Exceptions Should Be Raised By This Code&&" When Selecting From System Function
One of my users is receiving the error below when selecting from this function: select * from sys.dm_db_index_operational_stats (null,null,null,null) I have already granted him "grant view server state" rights and this did not change anything. However, as a test I temporarily granted him sysadmin rights and he did not receive the error. I am curious as to what missing rights are causing the error. The server is sql version is 2005 standard edition 64 bit SP2: Microsoft SQL Server 2005 - 9.00.3042.00 (X64) All the posts about this error that I have seen indicate that this error is resolved after applying SP2. Anyone have any other suggestions? Error Location: qxcntxt.cpp:956 Expression: !"No exceptions should be raised by this code" SPID: 58 Process ID: 2000 Msg 21, Level 20, State 1, Line 3 Warning: Fatal error 1203 occurred at Jun 15 2007 10:32AM. Note the error and time, and contact your system administrator. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. The following is from the SQL Log: Process 58 unlocking unowned resource: DATABASE: 35 Error: 1203, Severity: 20, State: 1. Process ID 58 attempted to unlock a resource it does not own: DATABASE: 35 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator. Process 58 unlocking unowned resource: DATABASE: 35 Using 'dbghelp.dll' version '4.0.5' **Dump thread - spid = 58, PSS = 0x00000000A11EDBE0, EC = 0x00000000A11EDBF0 ***Stack Dump being sent to D:Datase-k-MSSQLServer-OCSQLOGSQLDump0027.txt * ******************************************************************************* * * BEGIN STACK DUMP: * 06/15/07 10:32:59 spid 58 * * Location: qxcntxt.cpp:956 * Expression: !"No exceptions should be raised by this code" * SPID: 58 * Process ID: 2000 * * Input Buffer 276 bytes - * execute as login = 'INTNPU22537' select suser_name() selec * t * from sys.dm_db_index_operational_stats (null,null,null,null) * * * MODULE BASE END SIZE * sqlservr 0000000001000000 0000000003501FFF 02502000 * ntdll 0000000077EC0000 0000000077FF7FFF 00138000 * kernel32 0000000077D40000 0000000077EB2FFF 00173000 * MSVCR80 0000000078130000 00000000781FEFFF 000cf000 * msvcrt 000007FF7FC00000 000007FF7FC85FFF 00086000 * MSVCP80 000000007C420000 000000007C530FFF 00111000 * ADVAPI32 000007FF7FEE0000 000007FF7FFE4FFF 00105000 * RPCRT4 000007FF7FD30000 000007FF7FED8FFF 001a9000 * USER32 0000000078C30000 0000000078D3BFFF 0010c000 * GDI32 000007FF7FC90000 000007FF7FD28FFF 00099000 * CRYPT32 000007FF7D180000 000007FF7D2DEFFF 0015f000 * MSASN1 000007FF7D150000 000007FF7D179FFF 0002a000 * Secur32 000007FF7E7F0000 000007FF7E811FFF 00022000 * MSWSOCK 000007FF76FE0000 000007FF7705BFFF 0007c000 * WS2_32 000007FF77150000 000007FF7717FFFF 00030000 * WS2HELP 000007FF77140000 000007FF7714BFFF 0000c000 * USERENV 000007FF7C4A0000 000007FF7C5A8FFF 00109000 * opends60 00000000333E0000 00000000333E7FFF 00008000 * NETAPI32 000007FF77370000 000007FF77407FFF 00098000 * SHELL32 000007FF7F190000 000007FF7FB97FFF 00a08000 * SHLWAPI 000007FF7EF60000 000007FF7EFFAFFF 0009b000 * comctl32 0000000000AF0000 0000000000C76FFF 00187000 * psapi 000007FF7E1B0000 000007FF7E1BFFFF 00010000 * instapi 0000000048060000 000000004806CFFF 0000d000 * sqlevn70 000000004F610000 000000004F7B8FFF 001a9000 * SQLOS 00000000344D0000 00000000344D5FFF 00006000 * rsaenh 000000000FFB0000 000000000FFEDFFF 0003e000 * AUTHZ 000007FF7E2C0000 000007FF7E2ECFFF 0002d000 * MSCOREE 000006427EE60000 000006427EED3FFF 00074000 * ole32 000007FF7ECE0000 000007FF7EF51FFF 00272000 * msv1_0 000007FF7E330000 000007FF7E373FFF 00044000 * iphlpapi 000007FF57250000 000007FF57280FFF 00031000 * kerberos 000007FF77410000 000007FF774C2FFF 000b3000 * cryptdll 000007FF7DAB0000 000007FF7DABEFFF 0000f000 * schannel 000007FF7DB70000 000007FF7DBB0FFF 00041000 * COMRES 000007FF7E920000 000007FF7E9E5FFF 000c6000 * XOLEHLP 000007FF5C560000 000007FF5C566FFF 00007000 * MSDTCPRX 000007FF67140000 000007FF67210FFF 000d1000 * msvcp60 0000000007420000 0000000007509FFF 000ea000 * MTXCLU 000007FF7B540000 000007FF7B569FFF 0002a000 * VERSION 000007FF7FBF0000 000007FF7FBFAFFF 0000b000 * WSOCK32 000007FF770F0000 000007FF770F9FFF 0000a000 * OLEAUT32 000007FF7E9F0000 000007FF7EB03FFF 00114000 * CLUSAPI 000007FF7B1C0000 000007FF7B1E3FFF 00024000 * RESUTILS 000007FF7B310000 000007FF7B32BFFF 0001c000 * DNSAPI 0000000007520000 000000000756DFFF 0004e000 * winrnr 000007FF7E820000 000007FF7E82AFFF 0000b000 * WLDAP32 000007FF7E780000 000007FF7E7E5FFF 00066000 * rasadhlp 0000000007610000 0000000007616FFF 00007000 * security 000007FF77530000 000007FF77534FFF 00005000 * msfte 0000000049980000 0000000049D2DFFF 003ae000 * dbghelp 0000000009630000 000000000978CFFF 0015d000 * WINTRUST 000007FF7E210000 000007FF7E25DFFF 0004e000 * imagehlp 000007FF7E2A0000 000007FF7E2B2FFF 00013000 * dssenh 000000000FF70000 000000000FFABFFF 0003c000 * hnetcfg 000007FF6D1F0000 000007FF6D280FFF 00091000 * wshtcpip 000007FF76FA0000 000007FF76FAAFFF 0000b000 * NTMARTA 000007FF7E2F0000 000007FF7E32BFFF 0003c000 * SAMLIB 000007FF76F80000 000007FF76F95FFF 00016000 * ntdsapi 000007FF7DAD0000 000007FF7DAF3FFF 00024000 * xpsp2res 000007FF5C210000 000007FF5C4D6FFF 002c7000 * CLBCatQ 0000000003510000 00000000035ECFFF 000dd000 * sqlncli 00000000337A0000 0000000033A5FFFF 002c0000 * COMCTL32 0000000003610000 00000000036FDFFF 000ee000 * comdlg32 000007FF7D360000 000007FF7D3D6FFF 00077000 * SQLNCLIR 0000000035000000 0000000035032FFF 00033000 * msftepxy 000000000AA10000 000000000AA30FFF 00021000 * xpsqlbot 000000004A7C0000 000000004A7C7FFF 00008000 * xpstar90 0000000053C30000 0000000053CB5FFF 00086000 * SQLSCM90 0000000053AD0000 0000000053ADBFFF 0000c000 * ODBC32 000007FF63EB0000 000007FF63F16FFF 00067000 * BatchParser90 00000000520C0000 00000000520ECFFF 0002d000 * ATL80 000000007C630000 000000007C64DFFF 0001e000 * odbcint 000007FF63DC0000 000007FF63DD7FFF 00018000 * xpstar90 000000000B440000 000000000B465FFF 00026000 * xplog70 0000000034730000 000000003473FFFF 00010000 * xplog70 000000000B480000 000000000B482FFF 00003000 * odsole70 0000000033380000 0000000033397FFF 00018000 * SQLDMO 000000001C0B0000 000000001C6E2FFF 00633000 * odbcbcp 000007FF63E90000 000007FF63E97FFF 00008000 * SQLDMO 000000001C000000 000000001C08DFFF 0008e000 * SXS 000007FF7CA80000 000007FF7CC6CFFF 001ed000 * SQLSRV32 000007FF5FD60000 000007FF5FE05FFF 000a6000 * sqlsrv32 00000000093C0000 00000000093D6FFF 00017000 * odbccp32 000007FF63E20000 000007FF63E44FFF 00025000 * sqlevn70 000000000C5D0000 000000000C7D4FFF 00205000 * sqlevn70 000000000C7E0000 000000000C9E2FFF 00203000 * sqlevn70 000000000C9F0000 000000000CB98FFF 001a9000 * sqlevn70 000000000CBA0000 000000000CD8BFFF 001ec000 * sqlevn70 000000000CD90000 000000000CF87FFF 001f8000 * sqlevn70 000000000CF90000 000000000D173FFF 001e4000 * sqlevn70 000000000D180000 000000000D33FFFF 001c0000 * sqlevn70 000000000D340000 000000000D51FFFF 001e0000 * sqlevn70 000000000D520000 000000000D6F9FFF 001da000 * sqlevn70 000000000D700000 000000000D8A8FFF 001a9000 * sqlevn70 000000000D8B0000 000000000DA58FFF 001a9000 * sqlevn70 000000000DA60000 000000000DC08FFF 001a9000 * * P1Home: 0000000000000001: * P2Home: 000000000FAD3E00: 0030004600420044 000000000000000A 0000000000000000 0000000000000000 0000000500000030 0000000000000000 * P3Home: 0000000000000000: * P4Home: 0000000077EF40D2: 00000320249C8B48 0000031824B48B48 0000031024BC8B48 0000030824A48B4C 0000030024AC8B4C 000002F824B48B4C * P5Home: 0000000000000000: * P6Home: 0000000000000000: * ContextFlags: 000000000010000F: * MxCsr: 0000000000001FA0: * SegCs: 0000000000000033: * SegDs: 000000000000002B: * SegEs: 000000000000002B: * SegFs: 0000000000000053: * SegGs: 000000000000002B: * SegSs: 000000000000002B: * EFlags: 0000000000000206: * Rax: 000000000FAD3E88: 0000000077D67D4D 0000000009307780 0000000000000000 000000000FAD4140 00000000A11EDBE0 00000000000042AC * Rcx: 000000000FAD39B0: 0000000000000001 000000000FAD3E00 0000000000000000 0000000077EF40D2 0000000000000000 0000000000000000 * Rdx: 0000000000000000: * Rbx: 0000000000000000: * Rsp: 000000000FAD3E90: 0000000009307780 0000000000000000 000000000FAD4140 00000000A11EDBE0 00000000000042AC 0000000000000000 * Rbp: 000000000FAD5620: FFFFFFFF000003BC 000000000FAD9324 000003E500000001 0000000000000001 0000000000000007 000000000FAD935C * Rsi: 000000000FAD4140: 0000000009307780 0000000000000000 0000000000000000 0000000000000000 0000000000000001 0000000000000000 * Rdi: 00000000A11EDBE0: 00000000A11ECB00 0000000000000000 00000000A11EDBE0 0000000000000000 0000000000000000 00000000A11EDC08 * R8: 0000000000000000: * R9: 0000000000000000: * R10: 0000000000000000: * R11: 000000000FAD4680: 0000000000000001 000000000FAD3E00 0000000000000000 0000000077EF40D2 0000000000000000 0000000000000000 * R12: 000000000000003F: * R13: 00000000A11EDBF0: 00000000A11EDBE0 0000000000000000 0000000000000000 00000000A11EDC08 00000000A11EDC08 00000000A11EDC18 * R14: 000000000FAD5530: 00610063006F004C 006E006F00690074 007100200009003A 0074006E00630078 0063002E00740078 0039003A00700070 * R15: 0000000000000001: * Rip: 0000000077D67D4D: C3000000C8C48148 15FF984B8D49D233 8699E990FFFD971F 9090909090900000 834853C08B909090 0C0D8A158B4830EC * ******************************************************************************* * ------------------------------------------------------------------------------- * Short Stack Dump 0000000077D67D4D Module(kernel32+0000000000027D4D) 0000000001D345DE Module(sqlservr+0000000000D345DE) 0000000001D3F3A9 Module(sqlservr+0000000000D3F3A9) 000000000247E28C Module(sqlservr+000000000147E28C) 00000000015D069F Module(sqlservr+00000000005D069F) 00000000781CF4B0 Module(MSVCR80+000000000009F4B0) 000000007816AA10 Module(MSVCR80+000000000003AA10) 0000000077EF3722 Module(ntdll+0000000000033722) 00000000010DEBF1 Module(sqlservr+00000000000DEBF1) 00000000010DE6EF Module(sqlservr+00000000000DE6EF) 00000000010DF551 Module(sqlservr+00000000000DF551) 00000000010E2B62 Module(sqlservr+00000000000E2B62) 00000000010E4115 Module(sqlservr+00000000000E4115) 00000000010E4695 Module(sqlservr+00000000000E4695) 00000000010E375D Module(sqlservr+00000000000E375D) 00000000781CF4B0 Module(MSVCR80+000000000009F4B0) 000000007816AA10 Module(MSVCR80+000000000003AA10) 0000000077EF3722 Module(ntdll+0000000000033722) 000000000102F94A Module(sqlservr+000000000002F94A) 000000000102F375 Module(sqlservr+000000000002F375) 0000000001030872 Module(sqlservr+0000000000030872) 0000000001270CE7 Module(sqlservr+0000000000270CE7) 000000000100685E Module(sqlservr+000000000000685E) 0000000001006F11 Module(sqlservr+0000000000006F11) 0000000001006A01 Module(sqlservr+0000000000006A01) 00000000010EF7A7 Module(sqlservr+00000000000EF7A7) 00000000010F051A Module(sqlservr+00000000000F051A) 00000000010F03F1 Module(sqlservr+00000000000F03F1) 00000000010F0289 Module(sqlservr+00000000000F0289) 00000000781337A7 Module(MSVCR80+00000000000037A7) 0000000078133864 Module(MSVCR80+0000000000003864) 0000000077D6B69A Module(kernel32+000000000002B69A) Stack Signature for the dump is 0x00000000A0A891FD External dump process return code 0x20000001. External dump process returned no errors. Error: 17066, Severity: 16, State: 1. SQL Server Assertion: File: <qxcntxt.cpp>, line=956 Failed Assertion = '!"No exceptions should be raised by this code"'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
View Replies !
CASE Function Result With Result Expression Values (for IN Keyword)
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.
View Replies !
Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.
Can any1 tell me why i am getting an error SELECT DISTINCT --p.voucher, --p.amount, p.siteID, b.siteID, SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance, SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque, SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers FROM BillingTotal b, Payment p --WHERE -- s.sitename=@cmb1 --AND p.siteid = s.siteid -- p.voucher = 0 -- p.voucher = 1 GROUP BY p.siteID,b.siteID Msg 130, Level 15, State 1, Line 1 Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
View Replies !
URGENT - My Error Or Bug? The Result Of The Expression Cannot Be Written To The Property. The Expression Was Evaluated, But
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 "C:\sql_working_directory\MLS\AZ\Phoenix\Docs\Armls_Schema Updated 020107.xls" 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 )
View Replies !
Sql Editor
Hi! I like a new sql editor instead of the query analyzer. The QA dont show the outlining, bracket injuctions and intellilist. Help! Wich editor able this functions?
View Replies !
MDX Editor
We started using analysis services, I managed to connect the analysis service database to the EM as a linked server, however I cant get the QA to connect to it. Can QA handle multi dimensional result sets? Can It connect to the analysis service at all? What is the best way to write and debug MDX? thanks for your help. Lee-Ron
View Replies !
Good Editor For MDX
Something helpful for those who write a lot of MDX. You could download it for free at: http://www.sysobjects.com/products/mdxedit/mdxedit.htm However, it is limited to work only with analysis services. Greg
View Replies !
What's A Good SQL Editor?
Hi everyone I'm wondering if there's a better SQL Editor than MS Query Analyzer on the market? I like a lot of the functionality provided by QA but want extra stuff like you get in VB6: intelli-sense (sytanx prompting), auto-complete (CTRL+Space provides list of sp's and tables, etc.) plus any other time saving features. I've tried a few products but nothing quite hits the mark. Is there a program you use and recommend I trial? Cheers - Andy
View Replies !
SQL Editor/ Reindent
I have a large stored procedure that I wish to reindent it. Are there any editors or any utilities where it would allow me to reindent the source code? TIA
View Replies !
Programming Editor
Just curious, What do all of you use to create and edit stored procedures? I am looking for a better way to code than to use the default window supplied with Enterprise Manager. Do most of you use the Query Analyzer? Are there third party products you like using better? Thanks for the info.. Eugene
View Replies !
OLE DB Source Editor
hi, I am using SSIS to extract data from sql server and import into MDB file. In the process, under data flow task, I have used OLE DB Source Editor as source. Here i have choosen SQL Command as mode of data population. In the box below i have typed the following statements. "Exec Site_Address" I have used many temperory tables in this procedure. When i run this procedure in the query analyzer window i get the desired data which has to be imported to an MDB. After typing the above statements and when i click the button preview i can see the data. But when i click the Columns.... i dont see anything there. I am unable to see any columns there. This is getting to my nerves because, when i use OLE DB as Destination i am unable to map the columns and i get an error. I dont know how to solve this problem. cannot we map columns in temp tables .... or wat is it ?? Please help me to find a solution. I will also paste the procedure code that i have used. Create procedure Site_Address as begin create table #Data_For_Site_Address_Table ( unitid varchar(20), city varchar(50), cust_num varchar(40), zip varchar(20), CountryID varchar(20), CreatedBy varchar(20) ) -- tblcrdsiteaddress insert into #Data_For_Site_Address_Table select distinct * from (select (select top 1 fsu.ser_num from fs_unit fsu where ca.cust_seq <> 0 and fsu.cust_num = ca.cust_num order by ca.city desc) as UnitID,ca.city,ca.cust_num,ca.zip, CASE WHEN ca.country like 'Luxembourg' THEN 'LU' WHEN ca.country like 'Deutschland' THEN 'DE' WHEN ca.country like 'Austria' THEN 'AT' WHEN ca.country like 'Czech Republic' THEN 'CZ' WHEN ca.country like 'Denmark' THEN 'DK' WHEN ca.country like 'CHINA' THEN 'CN' WHEN ca.country like 'CROATIA' THEN 'HR' WHEN ca.country like 'Egypt' THEN 'EG' WHEN ca.country like 'Germany' THEN 'DE' WHEN ca.country like 'Hungary' THEN 'HU' WHEN ca.country like 'Jordan' THEN 'JO' WHEN ca.country like 'Korea, Republic Of' THEN 'KR' WHEN ca.country like 'Poland' THEN 'PL' WHEN ca.country like 'Switzerland' THEN 'CH' WHEN ca.country like 'United Kingdom' THEN 'GB' ELSE '- N/A -' END AS CountryID, CA.CreatedBy from custaddr ca ) al where unitid is not null Select TT.Unitid as Short_Site_Name, TT.City as Site_Name,'N.A' as Street_Po_Box,TT.Zip as Postal_Code_City, Null as State_Region, TT.CountryID as CountryID,Null as Zone, Null as Note, TT.CreatedBy as UserID, GetDate() as Date, 'A' as [Action] From #Data_For_Site_Address_Table TT END Thanks. Rgds, Meher Krishna.V
View Replies !
Custom Editor
Hi, If I implement a custom editor by inheriting from UITypeEditor, Can I invoke it at RUN TIME from a standard control other than PropertyGrid, say a comboBox or a button ? If it is possible, could you please let me know how to do it or could you please direct me to some URL which shows how to do it. Do I have to create a custom control that inherits from control for doing this ? Thank you. regards. S
View Replies !
Any Editor For Rdlc?
I would like to use SQL client-side reporting in ASP.NET. However, our customer would like to edit the report sometimes, is there any existing graphical editor to edit .rdlc file? Thanks.
View Replies !
T-SQL Editor For SQL CE 3.5 In VS 2008
Hi. Is there a T-SQL Editor for SQL CE 3.5 in VS 2008 other than the Query Designer? I'm used to writing and running sql queries using the MSSMSE 2005 query editor and was looking for something similar in the VS 2008 as MSSMSE 2005 can't open sql ce 3.5 db files. I've found the T-SQL Editor (Data->T-SQL Editor) but unfortunately it only seems to work with regular SQL Server databases and not sql compact sdf files. Am I missing something or is Query Designer the only way to go (I really hate how it messes with my queries and doesn't let me execute only parts of the queries I write :/ )?
View Replies !
Sql Script Editor
Hi, I am looking for a sql script editor that enables printing in color like sql query analyzer does it on screen but unfortunatly not when printing. any suggestion is welcome thx rv
View Replies !
XML Editor In SQL 2005
Does anyone know if it is possible to use the XML Editor in SQL 2005 to write xml files from scratch? If possible, how is the editor being started? As of my current knowledge, I am only able to start the XML Editor by clicking on the content of a XML data type column within the sql query result pane. Thanks Mike
View Replies !
MDF File Editor Required.
I have just installed MS SQL Server 2005 and SQL Server Management Studio Express on my server but when I try to open a MDF file for manual editing I am told no editor exists. Is the software I have installed able to open a MDF file?
View Replies !
Which Is A Good SQL Server Editor
Hi,I am looking for a good SQL Server Editor which can be used forwritting stored procedures, doing queries etc. It should have featureslike autocomplete and so on. Any recommendations.Thanks !
View Replies !
Editor With Visual SCC / TFS Support ?
Company has switched to MS Team Foundation Server from VSS. Need aprogrammers text editor that interfaces with TFS or SCC providers tovisually provide checkin/out status on project files. So far, in all ofthe editors I have used, some support SCC interfaces, but do not showthe file status.The current alternative is to load Visual Studio (or the TFS clientwhich is simply a subset of VS), simply to manage checkins. Usingcommand line calls is also an options, but visualizing the file statuswould be more helpful.Suggestions?
View Replies !
Web Based SQL Table Editor
Is anyone aware of a tool that will allow users to update the values of a sql table via a web page? What I am looking for is a tool that when given the name of a table will generate a web front end to allow editing of the table. Thanks, Todd
View Replies !
Question About Cube Editor
Hi, How can we check which two fields among dimensions and/or amonst dimension and fact table(s) are joined if there is a link (line) between them. For example in the Diagram Editor or even the Query Designer, we can get properties of a linking line and find out which two fields on both sides are linked (this is useful if there are many links between 2 sides and if one needs to be sure if thecorrect fields are linked). Also, I just want to confirm that in the cube editor, it makes no difference which direction I drag the 1st field from (i.e. which dimension/fact) and where I drag it to. Is this correct? Thanks.
View Replies !
ForEach Loop Editor
Help, Trying to through a process together and using the "ForEach Loop" task. When configuring the task and using the ForEach Loop Editor I do not have the "ForEach File Enumerator" in the drop down. Anyone know whats going on or what I need to do to make this appear. I only see 5 selections: ADO, ADO.Net Schema Rowset, Variable, NodeList, and SMO Enumerators. I am also using the SQL 2005 eval with SP1. Thanks, John
View Replies !
How Do I Use If Inside Sql Task Editor
hi, lets say i have an input param calld x. i want to use it inside sql task: i try to put if(? = 0) begin do somthing end but i get an error. the variable is configured correctly in the mapping input as i have succeeded to do the same with insert: insert into y values(?) why it does not work with if statment? how can i use it? thx, Tomer
View Replies !
Property Expressions Editor
Hi, I am trying to load some text files to a SQLServer database. My text files are going to have a name like orders_200709.txt. I am trying to use variables and expressions to evaluate the datepart, YYYYMM. The problem I am facing is with the 'Property Expressions Editor'. When I click on the ellipsis ..., I get the editor, but I am not able to add a new expression; not able to view properites etc. Everything is like in a Disable mode. Does anyone know the cause of this? Thanks
View Replies !
How Do You Call RMD On A Component That Has No Editor?
I have a custom component that has no editor (i.e. NoEditor=TRUE in DtsPipelineComponent attribute). This component derives all of its metadata based on the input (hence no editor is needed). What this means is that if the input changes in any way (e.g. the datatype of a column changes) my component will fail validation. That's not a problem, the problem is my Validate() method returns DTSValidationStatus.VS_NEEDSNEWMETADATA but ReinitializeMetaData() is not being called. I get the familiar warning from the component: Warning 1 Validation warning. Data Flow Task: Normaliser: The component has inconsistent metadata. TestHarness.dtsx 0 0 and when I double-click on the component I get the familiar dialog box: TITLE: Editing Component ------------------------------ The component is not in a valid state. Do you want the component to fix itself automatically? ------------------------------ BUTTONS: &Yes &No Cancel ------------------------------ but when I click on 'Yes', nothing happens. RMD never gets called. The workaround is to detach and reattach the input (which results in a call to ReinitializeMetadata() ) or set NoEditor=FALSE in DtsPipelineComponent attribute but I'd rather not have to do that because strictly speaking the user has no need to edit the component through advanced properties. Is there another way around this problem? Is there a way to call RMD on a component that has no editor? Thanks Jamie [Microsoft follow-up]
View Replies !
SQL Server Support In OXygen XML Editor
oXygen XML Editor 8.0 (http://www.oxygenxml.com) added support for SQL Server 2005 database (available in Enterprise edition). It provides a new Database perspective with a Database Explorer view that can be used to browse the database structure in a tree format and a Table Explorer view that can be used to edit the table data. You can run SQL interrogations over the database with support for SQL parameter markers that can be configured into a scenario. If you are using the XML data storage and retrieval features from SQL Server 2005 you will be able to edit the table content of an XML type directly in the oXygen XML editor. There is also support for SQL/XML and XQuery interrogations as well for XSD schema view/registration/unregistration. The configuration for SQL Server support is simple, you need to configure a SQL Server datasource and a connection based on it: http://www.oxygenxml.com/doc/ug-standalone/working-with-databases.html#configure-sql-server-datasource http://www.oxygenxml.com/doc/ug-standalone/working-with-databases.html#configure-sqlserver-connection oXygen is available on almost any platform both as a standalone application and as an Eclipse plugin. To find the full list of new additions and more details please visit: http://www.oxygenxml.com/index.html#new-version Best Regards, Stefan --------------------------------------------------------------------- Stefan Vasile <oXygen/> XML Editor, Schema Editor and XSLT Editor/Debugger http://www.oxygenxml.com
View Replies !
Please Help - Error Message In Cube Editor :(
Hi, Amongst others, I have the following dimensions: 1. Distributors 2. Customers 3. Country Both the Distributors and Customers dimensions have fields called RegionId, StateId, and CityId (I'll refer to these as Geography fields later in this post). As you would probably have figured out these are to be linked with the Country Dimension which has all these fields (RegionId, StateId, and CityId) but with more details for each e.g. Name etc. The problem which I am facing is when I SAVE the cube: After I have linked the Geography fields of Customers and Distributors with the Geography fields of the Country Dimension and when I try to SAVE the cube, I get a message saying "There is a loop in the schema. Please remove the extra joins". If I delete the Geography links between either the Distributors Dimension and the Country Dimension OR between the Customers Dimension and the Country Dimension OR BOTH, I am then able to save the cube without any problems :confused: Please help. Please let me know if I need to add more information for you to be able to help me solve the problem. Thanks
View Replies !
|
TRACKER
