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 Complete Forum Thread with Replies
Related Forum Messages:
Sort Expression - Custom Code
Hi, I have a report that needs sorting on a column that is calculated. however, the calculation is so complex (iterative calculations) i've had to do that within my custom code. I am getting the results back well and good but i cannot perform a sort based on that. My problem is: how do i write a custom sort expression function that will direct the column to sort my table based on say the value(s) returned from that function. I want for example: Code Snippet Public Function mySortExpression(byVal myIDorWhatever as Something) as CustomSortableExpression ' Perform some calculation ' Return some value(s)/array or anything that the column can appreciate and sort the table accordingly with End Function then in say the Interactive Sort Sort Expression, i'll have the code: Code Snippet =code.MyClass.mySortExpression(myID) I hope i've made myself clear enough. Cheers
View Replies !
Returning Table Rows From Custom Code?
I wish to call a custom code function from a table control that would return rows of data to be displayed in the table. Is this possible? Specifically, I'd like to pass a large text string to the function, have the function break the string into smaller strings, and then have the smaller strings displayed in the table. The number of lines returned may vary, depending on the original string passed in.
View Replies !
Custom Code Error With VB Function
Hello, I am about out of hair from pulling it out. If someone could please show me what I'm doing wrong I would really appreciate it. I have this function for a SSRS 2005 report: Public Function funAdditions(pFields As Fields) As Double if pFields !FA00902_AMOUNT.Value > 0 and pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and NOT instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end else if pFields !FA00902_TRANSACCTTYPE.Value = 3 and pFields !FA00902_DEPRTODATE.Value > Parameters!BeginDate.Value and pFields !FA00902_DEPRTODATE.Value <= Parameters!CutOffDate.Value and instr(pFields !FA00902_SOURCDOC.Value, "FACHG")=1 then return pFields !FA00902_AMOUNT.Value end End Function ...and after much research cannot figure out the solution to this error: [rsCompilerErrorInCode] There is an error on line 1 of custom code: [BC30201] Expression expected. I'm new to SSRS so is there a syntax error I'm missing? Thanks in advance, Buster
View Replies !
Convert String From Textbox To Int - SQL Code
How do I convert for example, a value comming from C# app "TextboxGBDataDeleted" to int column GBdatadeleted? I have a RangeValidator on this textbox to accept only numbers. I don't want to write C# code. I woul prefer to do this is SQL. Thank you. My SQL update statement: INSERT INTO KPITbl (Lead, WRM, PTDB, PAR, PM, RequestingLOB, LOB, StartLocation, FinishLocation, Description, ProjectType, ServerName, ServerType, DCOorSTANDALONE, Responsible, Status, RAG, StartDates, EndDates, TreeOrDomainImpacted, NumOfSites, NumOfUsers, GBdatamoved, GBdatadeleted, NumOfSrvrsAdded, NumOfSrvrsDecommed, NumOfAppsDeployed, EUTEngineeringConsult, Comments, TimeSpend, Complexity, ECM, LastUpdated) VALUES (@Lead,@WRM,@PTDB,@PAR,@PM,@RequestingLOB,@LOB,@StartLocation,@FinishLocation,@Description,@ProjectType,@ServerName,@ServerType,@DCOorSTANDALONE,@Responsible,@Status,@RAG,@StartDates,@EndDates,@TreeOrDomainImpacted,@NumOfSites,@NumOfUsers,@GBdatamoved,@GBdatadeleted,@NumOfSrvrsAdded,@NumOfSrvrsDecommed,@NumOfAppsDeployed,@EUTEngineeringConsult,@Comments,@TimeSpend,@Complexity,@ECM,(getdate() ) ) ~~~~~~~~~~~~~~~~~~~~~~MY TABLE~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ CREATE TABLE [dbo].[KPITbl]( [TaskID] [int] IDENTITY(1,1) NOT NULL, [Lead] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [WRM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PTDB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PAR] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [PM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RequestingLOB] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [LOB] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StartLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FinishLocation] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Description] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ProjectType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ServerName] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ServerType] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Responsible] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Status] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [RAG] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StartDates] [smalldatetime] NULL, [EndDates] [smalldatetime] NULL, [TreeorDomainImpacted] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [NumOfSites] [int] NULL, [NumOfUsers] [int] NULL, [GBdatamoved] [int] NULL, [GBdatadeleted] [int] NULL, [NumOfSrvrsAdded] [int] NULL, [NumOfSrvrsDecommed] [int] NULL, [NumOfAppsDeployed] [int] NULL, [EUTEngineeringConsult] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Comments] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TimeSpend] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [Complexity] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [LastUpdated] [smalldatetime] NULL, [DCOorSTANDALONE] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [ECM] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [StatusCompletedDate] [smalldatetime] NULL, [StatusCancelledDate] [smalldatetime] NULL, CONSTRAINT [PK_Sheet1a] PRIMARY KEY CLUSTERED ( [TaskID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY]
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 !
Custom Code: Enterprise Library And Connection String In The Web Config
Hello, My report uses a dll in order to calculate a field and has been set up correctly to access it. The method in the dll connects to a database using the Microsoft Enterprise Library. I have placed all of the enterprise library dlls in to the Private Assemblies of Visual Studio but the report has the following error: The requested database ABC is not defined in configuration The reason for this error is becuase the connection string is held in the project's web config. The reports are in the same solution but are within their own report project. What do I need to do to set up the report to access this connection string? Many thanks.
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 !
Expression Issue With Custom Data Flow Component And Custom Property
Hi, I'm trying to enable Expression for a custom property in my custom data flow component. Here is the code I wrote to declare the custom property: public override void ProvideComponentProperties() { ComponentMetaData.RuntimeConnectionCollection.RemoveAll(); RemoveAllInputsOutputsAndCustomProperties(); IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New(); prop.Name = "MyProperty"; prop.Description = "My property description"; prop.Value = string.Empty; prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; ... } In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime Here is my expression (a file name based on a date contained in a user variable): "DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER" @[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time So the expression is evaluated as: "DB189912189912.VER". My package contains 2 data flow. At runtime, The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15) The second one contains my custom data flow component with my custom property that was set to an expression at design time When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER" Any idea ?
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 !
Custom Task - Custom Property Expression
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime. I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me? This is what I did in the custom task customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY; In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName] Any help on this will be very much appreciated. Thanks
View Replies !
User Defined Function: Convert String Value Of Table To Table Object
Does anyone know where to find or how to write a quick user defined fucntionthat will return a table object when passed the string name of the tableobject. The reason why I want dynamicallly set the table name in a storedprocudue WITHOUT using concatination and exec a SQL String.HenceIf @small_int_parameter_previous = 1 then@vchar_tablename = "sales_previous"else@vchar_tablename = "sales"Endselect * from udf_TableLookup(@vchar_tablename )So if I pass 1, that means I want all records from "sales_previous"otherwise give me all records from "sales" (Sales_Previous would last yearssales data for example).udf_TableLookup would I guess lookup in sysobjects for the table name andreturn the table object? I don't know how to do this.I want to do this to avoid having 2 stored procedures..one for current andone for previous year.Please respond to group so others may benfiit from you knowledge.ThanksErik
View Replies !
Returning A NULL Value In An SSIS Expression
Greetings SSIS friends, I have the following expression but it doesn't seem to evaluate findstring(eventstatus, "O", 1) > 0 ? "OFF" : NULL(DT_STR, 30, 1252) All I want to do is something like : All I want to do is return a NULL value if my condition is false. Your help would be appreciated.
View Replies !
Custom Code (Embedded Code) Question
Hi all, Could someone tell me if custom code function can capture the event caused by a user? For example, onclick event on the rendered report? Also, can custom code function alter the parameters of the report, or refresh the report? Thanks.
View Replies !
SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
View Replies !
Returning A Dataset From Custom Assembly?
Is it possible to have a Reporting Services dataset be returned from a custom assembly? I need to produce data for a graph that is not easily queried from the database and I am already familiar with using a custom assembly in an RS report. What I would like to do is have a method that fills a dataset and returns it to the report for processing. Is this possible? I am running SQL 2000 and RS 2000. Thanks.
View Replies !
Evaluate Expression In Custom Component
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example? An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2. I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator. Thanks! -Thames
View Replies !
Help Convert MS Access Function To MS SQL User Defined Function
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View Replies !
Custom Components - How To Evaluate A Property As An Expression?
Hi all, I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far. I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component. I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"? Thanks!
View Replies !
SSRS Reporting From Custom List Returning 1;# In SQL Report
Hi, I have a set up SSRS which is reporting from a custom list in sharepoint to SQL 2005. I have created a parameter on the SQL report to return data from a field in the sharepoint list, however when it retrieves the data i get 1;# in front of the returned data? Why would this happen? Any help would be much appreciated, thanks.
View Replies !
How To Protect From SQL Injection In ASP.NET And SQL 2005 For Custom Query Expression?
How to Protect From SQL Injection in ASP.NET and SQL 2005 for custom query expression?In my project, I allow user to custom query expression through UI, such asstring queryCondition=' sale>20 and sale <100'string queryCondition=' createDate>"10/10/2005"'string queryCondition='Fullname like "%Paul%" '...I construct SQL based the queryCondition string, such as string mysql='select * from mytable where '+queryConditionI know it's very dangerous because of SQL Injection, but it's very convenient for user to custom query expressionCould you tell me how to do? many thanks!
View Replies !
Building Expression Like Properties In Custom SSIS Tasks
Ive been using SSIS for a month or two and now find I need to create some custom tasks to perform some performance logging. in the the overloaded ProviderComponentProperties section I am trying to create a property which has the same look as the Expressions properties you find elsewhere (Little + on the left and a group of sub properties when expanded). Ive have played with creating a IDTSCustomPropertyCollection90 collection then adding my sub properties to it but I cant seem to then add my new collection to the ComponentMetaData.CustomPropertiesCollection. Im assuming the Expressions parameter is a collection added to the properties collection but I cant figure out how. Any help would be much appreciated.
View Replies !
How To Convert DT_WSTR To DT_DBDate In Expression
Hi all, Actually trying to add one month to current variables value so I can process per month data in for loop for that I have written the following statement in Assign Expression Part of For Loop dateadd("Month",1,((DT_DBDATE)(@[User::_year]+"/"+@[User::_month]+"/"+"01"))) here I am getting the error message that error converting from data type dt_wstr to to datatype dt_dbdate @[User::_year] contains year value like 2003 and @[User::_month] contains month value like 1 using these variables I want to add the month to the date created by using these both variables. so this is the first part to add the month to date and other part is to assign new value to variables by using datepart function any help and guidance related to this whether I am doing right or not if any other wayout to acheive this goal.
View Replies !
Custom Connection Manager; Returning Recognized OLEDB Source?
[One day I will come back here and answer questions, rather than only ask.] I have a custom connection manager that returns an OleDbConnection. The only thing "custom" about the connection manager is how it determines its connection string. The object returned in AcquireConnection is an OleDbConnection. Code:public override object AcquireConnection(object txn) { UpdateConnectionString(); OleDbConnection oleDbConnection = new OleDbConnection(this.connectionString); oleDbConnection.Open(); return oleDbConnection; } Some of the objects within SSIS only work with an OleDbConnection, but mine is not recognized as such. Is there any way to have my connection recognized as a valid connection? Thanks in advance. -bms
View Replies !
Custom Task: How To Access/modify The Expression Collection Of A Package
Hi, I created a Custom Task which it has a Property called ConfigFilePath. I'm overriding the Validate() method from Task. I want to throw an error if my property ConfigFilePath is empty and if the expression for this property is empty. So far, I can check if the property is empty but I don't see how I can access the Expressions Collection of my Custom Task. Any thoughts? I'd appreciate your help.
View Replies !
Assign Expression Value Using Code
I am trying to use a variable to set an attribute value of an SSIS task but I keep running into the 4000 character limit of the string variable. Not sure why the variable which is of .NET type String has this limit when it doesn't when you are in a .NET environment. Regardless, can anyone provide some sample code that I could use to do this in a script task? I am trying to set the QueryString property of the Data Mining Query Task. All help would be appreciated. Thank you in advance.
View Replies !
Enabling Expression Builder For Custom SSIS DataFlow Source Component
Hi, I have implemented a custom source component that can be used as the data source in the Data Flow task. I have also created a custom UI for this component by using the IDtsComponentUI . But my component does not have the capability of setting the custom properties via the DTS Variables using the Expression Builder. I have looked around for samples on how to do this, but I can only find samples of how to do this for custom Control Tasks, i.e. IDtsTaskUI. My question is, How can implement the Expression Builder in my custom Source component + custom Source UI. Or do you know of any samples which I can look at. Thank you, Jameel.
View Replies !
Java Code Returning Null Resultset
Hi, Here's a snippet of my java code : Code: boolean b = statementSQLServer.execute("select ROW_ID from S_INDUST where NAME='InWire'"); resultSetSQLServer = statementSQLServer.getResultSet(); System.out.println("b = " + b); System.out.println("Are there any rows ?? " + resultSetSQLServer.next()); This code is returning null resultset. But when I fire the same query directly into MS SQL, it is returning the corresponding rows. I dont know where i'm going wrong. But just to make things more clearer, here's my connectivity code : Code: Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connectionSQLServer = DriverManager.getConnection("jdbc:odbc:dm", "user", "pass"); statementSQLServer = connectionSQLServer.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE); Please help me out if possible....
View Replies !
Capitalize A Text String/String Function Related
Hi Folks: How can I capitalize a string like 'JOHN DOE' into 'John Doe' with one SQL statement. SQL does provide string function like LOWER(char_expr) which would change the whole text string to 'john doe' and I don't want that. So far, in order to do that, I have to use a front-end development language like 'Omnis' which has a string function 'cap()' to capitalize the whole string, then update the back-end SQL with the new string. Thank you in advance for your time and advice. David Nguyen
View Replies !
CLR SP Or Function Returning A Table
Hi guys, I have to work with some configuration data that is stored in rows as a comma separated values. Something like this: Key1 A,1,Z,0;B,2,Y,9;C,,8,X; Key2 Alpha,101;Beta,102; Each group of data is separated by a semicolon and each value by a comma. The quantity of values may vary from one key to the other. Over this values sometimes I need to run some selects, so I went with the idea to get it as a table using CLR. There I find the first problem: I didn't find a way to return a "variable" column with a CLR function, I had to create a SP. Ok, now I execute spGetConfigurationAsTable 'Key1' and I can obtain something like this: A 1 Z 0 B 2 Y 9 C 3 X 8 But I'm faced with a second problem: How can I run a query over this? I didn't find a way to run a query over an output of a SP. And I can't INSERT the result into a temporary table because I cannot CREATE the table static (remember the columns may differ from one configuration to the other). So, it seemed a pretty simple task and a neat solution, but I'm kinda stuck. Is there a way to run a query over the SP output? Or is there a way to have a variable table output from a CLR UDF? Here is the code of the CLR SP I use to obtain the data, and also the (non-working) CLR user defined function. THANKS! Code Snippet public partial class StoredProcedures { [Microsoft.SqlServer.Server.SqlProcedure] public static void spGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) return; StringBuilder SqlCreate = new StringBuilder("DECLARE @Output TABLE ("); for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { SqlCreate.AppendFormat("[{0}] varchar(50),", Convert.ToChar(65 + i)); } SqlCreate.Remove(SqlCreate.Length - 1, 1); SqlCreate.AppendLine(");"); StringBuilder SqlInsert = new StringBuilder(); foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { SqlInsert.Append("INSERT INTO @Output VALUES ("); // busca las diferentes "columns" ~ Charly foreach (string column in row.Split(SeparatorColumn)) { SqlInsert.AppendFormat("'{0}',", column); } SqlInsert.Remove(SqlInsert.Length - 1, 1); SqlInsert.AppendLine(");"); } } string SqlSelect = "SELECT * FROM @Output;"; cmd.CommandText = SqlCreate.ToString() + SqlInsert.ToString() + SqlSelect; SqlDataReader reader = cmd.ExecuteReader(); SqlContext.Pipe.Send(reader); reader.Close(); reader.Dispose(); } conn.Close(); conn.Dispose(); cmd.Dispose(); } }; Code Snippet public partial class UserDefinedFunctions { [Microsoft.SqlServer.Server.SqlFunction] public static IEnumerable fGetConfigurationAsTable(string Key) { SqlConnection conn = new SqlConnection("Context Connection=true"); string SqlCmd = string.Format("SELECT Value FROM Configuracion WHERE [Key] = '{0}' ", Key); SqlCommand cmd = new SqlCommand(SqlCmd, conn); conn.Open(); string Value = Convert.ToString(cmd.ExecuteScalar()); conn.Close(); conn.Dispose(); cmd.Dispose(); DataTable dt = new DataTable(); if (Value.Length > 0) { char SeparatorRow = ';'; char SeparatorColumn = ','; if (Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length > 35) { // throw exception } string ColumnName; for (int i = 0; i < Value.Split(SeparatorRow)[0].Split(SeparatorColumn).Length; i++) { ColumnName = string.Format("[{0}] varchar(50),", Convert.ToChar(65 + i)); dt.Columns.Add(ColumnName, Type.GetType("System.String")); } foreach (string row in Value.Split(SeparatorRow)) { if (row.Length > 0) { dt.Rows.Add(row.Split(SeparatorColumn)); } } } return dt.Rows; } };
View Replies !
SQL Function Returning Varchar(max)
I have a SQL function which returns a varchar(max). This gets truncated when the length is greater than 8000. Could you let me know how do I get the return value in a function without it being truncated.
View Replies !
Function Returning A Table
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go create FUNCTION [dbo].[search](@art varchar,@cd varchar,@tra varchar,@gen varchar,@cdate datetime,@label varchar) RETURNS @result TABLE(Artist varchar(100),CDTitle varchar(100),Track varchar(100),CDtype varchar(100),CDDate datetime, Label varchar(100)) AS BEGIN IF @art <>'/' INSERT INTO @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track WHERE artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and artist.artist=@art Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @cd <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtitle=@cd Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @tra <> '/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and track.track=@tra Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @gen <>'/' insert into @result SELECT dbo.CD.CDCoverURL AS ' ', dbo.CD.CDTitle AS 'CD Title',cd.cdtype as 'Section', convert(varchar,cd.cddate,106) as 'Release Date', dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cdtype=@gen Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @cdate<>'01/01/1900' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and cd.cddate=@cdate Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf if @label<>'/' insert into @result SELECT dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf FROM artist,cd,label,shelf,cdtrack,artisttrack,track where artist.artistid=artisttrack.artistid and cd.cdid=cdtrack.cdid and track.trackid=cdtrack.trackid and label.labelid=cd.labelid and shelf.shelfid=cd.shelfid and artisttrack.trackid=track.trackid and label.label=@label Group by dbo.CD.CDCoverURL, dbo.CD.CDTitle, dbo.CD.CDType, dbo.CD.CDDate, dbo.Label.Label, dbo.Shelf.Shelf return end --------------------------------------------------------------------- upon running executing this function with valid values i am not getting any results. anything is wrong? thank you,
View Replies !
Using Custom Code
I am trying to use custom code on a report which is: Public Function Percentage(decValueOne As Double, decValueTwo As Double) As Object If (decValueOne = 0 Or decValueTwo = 0) Then Percentage = 0 Else Percentage = (decValueOne - decValueTwo) End If End Function In my textbox I have: =Code.Percentage(SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_Sales"), SUM(Fields!PreviousYTDExpenseAmount.Value, "Template_OutputData_CGS")) When I run the report in design mode, I get the "#Error" on that report field. Any help with why this is happening would be greatly appreciated!
View Replies !
Custom Code....
Hey guys I have a problem I am trying to solve using custom code but I am open to other solutions. Situation: I have multiple reports that the user can see. Example (Move In Corp. report, Move In Region report,Move In Division report, Mid Year Corp. report, Mid Year Region report,Mid Year Division report etc.) I have a stored procedure that returns all the information needed based upon the UserID being passed from the report to the stored procedure. Goal: When the user logs in I want to be able to foward the user to a specific report based upon their userid (global in RS). I currently have three textboxes(Move In, Mid Year, Year End) with navigation enabled to jump to another report. The report to which the user is passed to is determined by an expression which is something like this for the Move In text box =IIF(Fields!orgtypeid.Value = 1,"Move In Corp","Move In Region"). The orgtypeid.value is returned by the stored procedure. I want to be able to forward the user a specific Move In report (Move In Corp, Move In Region, etc) without having the user select the textbox. Any suggestions are greatly appreciated.
View Replies !
Custom Code
Hello, this is probably a pretty easy one. I have made a report, and added custom code in the code tab on the report properties. But when I type code.whatever in a text box expression my function does not show. Does anyone know what I am doing wrong? Thanks
View Replies !
Edit Expression Dialogue Box - Code On Multiple Lines
Hi there Is it possible to write code in the edit expression dialogue boxes on multiple lines? At the moment I have some extremely long expressions on just the one line and it makes it hard to read and add to as you have to use the scroll bar to view all your code. Any ideas are much appreciated John
View Replies !
Returning String Length
I need to write a query that tells me which string values are empty orblank in a table.Is it possible to return the length of the string contained in characterfield?
View Replies !
Returning A String From A Sproc
I am having problems returning a string from a sproc. What I want to do is to be able to create an XML Doc and return it. I have tried the following: select @error = @@error, @row_count = @@rowcount set @outgoingXML = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>' return @outgoingXML where @outgoingXML is an output parameter. And I have tried: set @serilializedReturnCode = '<return_code><error_code>' + Convert(varchar, @error) + '</error_code><row_count>' + Convert(varchar, @row_count) + '</row_count></return_code>' return @serilializedReturnCode where @serilializedReturnCode is locally declared as varchar(250) This is the error I get: Syntax error converting the varchar value '<return_code><error_code>0</error_code><row_count>0</row_count></return_code>' to a column of data type int.
View Replies !
Evaluate A String Expression
Hi, I have a table which has expression as a column and the data looks similar to the following . ID (int) Expression (nvarchar data type) 1 8*(1/2)-6 2 278*(1/4)-2 3 81*(3/5) +4 I now have the expression as an nvarchar. (Ex: 8*(1/2)-6) Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string. I need to write a procedure for this. I am unable to cast/convert nvarchar to float/int Any sample code would be greatly appreciated. This a very urgent requirement for me.Please get back Thanks Swapna
View Replies !
Expression In Connection String
Hi, I use expressions in connection strings ..... ="Data Source=" & Parameters!resultServer.Value & ";Initial Catalog=" & Parameters!database.Value Problem is when i change the parameter server, before i am able to change the parameter database, the report errors out. How can i enforce the report server to wait till i am able to give it valid values for both server and database. Thanks.
View Replies !
Question About Returning A Smalldatetime From A Function
I've been working this for a while. Kind of new to SQL Serverfunctions and not seeing what I am doing wrong. I have this functionCREATE FUNCTION dbo.test (@Group varchar(50))RETURNS smalldatetime ASBEGINDeclare @retVal varchar(10)(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE(event_id = 13) AND (group_ =@Group))return convert(smalldatetime, @retVal, 1)ENDThe error I get isServer: Msg 296, Level 16, State 3, Procedure test, Line 6The conversion of char data type to smalldatetime data type resulted inan out-of-range smalldatetime value.1) I tried declaring @retVal as a smalldatetime and get the error "Mustdeclare the variable '@retVal'.'2) If I run that same query in query analyzer (manually inserting theparm) it returns 11/14/2006. That's what I want.If I change the function to this and run itCREATE FUNCTION dbo.test (@Group varchar(50))RETURNS varchar(50) ASBEGINDeclare @retVal varchar(50)(SELECT @retVal= MIN([date]) FROM dbo.t_master_schedules WHERE(event_id = 13) AND (group_ =@Group))return convert(smalldatetime, @retVal, 1)ENDIt now works but the return value is Nov 14 2006 12:00AMWhat am I doing wrong?TIA
View Replies !
Function Returning Error During Compilation.....
Hi , I am creating a function which is going to return a table. The Code ofr the function is as follows... =============================== Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70)) AS begin declare @t1_sys char(10),@t1_all varchar(11) declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS) if (substring(@cg1,1,2)='Q$') set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$') DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY insert into @temp_qcard values(@cg1) OPEN C1 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t1_all) declare @t2_sys char(10),@t2_all varchar(10) DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY begin OPEN C2 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t2_all) declare @t3_sys char(10),@t3_all varchar(10) DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY begin OPEN C3 FETCH NEXT FROM c3 INTO @t3_sys,@t3_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t3_all) FETCH NEXT FROM c3 INTO @t3_sys,@t3_all end end close c3 deallocate c3 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all end end close c2 DEALLOCATE c2 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all END CLOSE c1 DEALLOCATE c1 Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata RETURN END ========================== While compiling this I am getting the Below error .... ================== Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35 Mixing old and new syntax to specify cursor options is not allowed. ================= Can Anyone please help me how to resolve this issue... Thanks with Regards. -Mohit.
View Replies !
Function Returning Middle Values
I have this assignment where i have a table full of two digit exam scores and I have to write a function that eliminate x number of top values and x number of bottom values and return all the middle values. When the function is called, obviously a number is entered such as 3 and the top 3 and bottom 3 scores are not returned. i.e. SELECT * FROM GetMiddleValues (3); If anyone has any ideas on how to accomplish this, that would be great. Thanks
View Replies !
EncryptByKey Function Always Returning Null
When I use EncryptByKey function to encrypt text using AES 128 bit key, I get always null result. this is how I do the encryption: ALTER PROCEDURE [dbo].[ProcMyProc](@ClearText nvarchar(50)) AS BEGIN OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD= 'MY_Password_128'; Declare @Temp varbinary(8000); Set @Temp =EncryptByKey(Key_GUID('MyKey'),@ClearText); close symmetric key MyKey; select @Temp as temp; END The result I get for this procedure is null. Is there something wrong with this code?
View Replies !
Grouping And Custom Code
Hello everyone, I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup. TH GH1 (hidden) GH2 (hidden) Det (hidden) GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value) GF1 =Code.ShowAndResetSubTotal() TF =Code.GrandTotal I have the following in my Code window. Dim Public SubTotal as Decimal Dim Public GrandTotal as Decimal Function ShowAndResetSubTotal() as Decimal ShowAndResetSubTotal = SubTotal SubTotal = 0 End Function Function AddValue(newValue as decimal) as Decimal SubTotal += newValue GrandTotal += newValue AddValue = newValue End Function This gives me incorrect results and I can't figure out why. Here's how it shows on my report: Part Number Quantity Cost Regular Subtotal Method Using Custom Code Part 1 4,000 1.49 $5,947.20 Customer 1 $11,894.40 $0.00 Part 2 10 1.01 $10.07 Customer 2 $50.34 $5,947.20 Part 3 1 0.44 $0.44 Part 4 6,050 0.25 $1,530.41 Part 5 0 1.25 $0.00 Part 6 0 1.23 $0.00 Customer 3 $42,851.86 $10.07 Part 7 16,250 0.24 $3,922.59 Customer 4 $19,612.94 $1,530.85 Part 8 17,250 0.38 $6,544.82 Part 9 27,225 0.20 $5,380.20 Customer 5 $66,891.69 $3,922.59 Grand Total $141,301.23 $0.00 The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different. As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this? Jarret
View Replies !
|
TRACKER
