No &&"Left&&" Function In Expression Builder?
Anyone know the reaon the Left function was left out of the list of string functions in the Expression Builder?
Danno
View Complete Forum Thread with Replies
Related Forum Messages:
Expression Builder
Hi All, I want to build following code into expression builder . I don't know how to write that things into expression builder. so can you help me out from this problems. 1.Daily stock Inventory by stores " & FormatDateTime( date() , vbshortdate ) 2. " Weekly stores Queue Productivity for " & date()-8 3.Customer Data Entry Monthly Report for stores " & Month( now()-27) & "/" & Year(now()-27) 4."Customer of stores " & FormatDateTime( now() , vbshortdate ) & " - stock" 5.customers opens account " & FormatDateTime( now() -8, vbshortdate ) & " (STOCK1) Thanks Aric
View Replies !
SSIS-Expression Builder
am trying to do the following in SSIS. I have Execute Process Task, in which I have the values are set for Executable, Arguments and working Directory manually. I keep changing this if the Connection Manager points to Dev or when it points to QA or to Prod. Instead I want to do like this. If my Connection Manager points to Dev, the Executale , Arguments and Working Directory should automatically change. Else if it points to QA, the Executale , Arguments and Working Directory should automatically change and similarly for Prod. Is this possible in Expression Builder in Execute Process Task. If so, how?. Thank you
View Replies !
Conditions In Expression Builder
Nobody answered my previous question. So, I am putting it this way. How to write an IF condition in Expression builder. I want to do something like this. IF (variable1='ABC' then value = '.......' else variable1 ='DEF' then value = '.......' else variable1='GHI' then value = '.......' END Thank you.
View Replies !
Expression Builder Question
Hi all of you, When you're making expressions with your own variables and system variables and so on. How could I see the contents for each variable? When you have an expression like this: "Cargas de Hacienda. " + SUBSTRING( @[System:ackageName], 7,20) + " " You can perfectly click above "Evaluated value" and see the resutl but I mean, individually over each variable on the variables listview is not possible. Thanks for any input or advice, Enric
View Replies !
Bug In Report Builder When Using Expression
I ran in to a strange problem in Report Builder. I drop a few fields from my Report Model on a simple talbe report. Some of them are straight forwards attributes and some of them are expressions. Now in the table layout of a report I have say a attribute named as "Account Number". This attribute is actually a field from a table. I change the column header to a two line column header with €œAccount€? on one line and €œNumber€? on second line. No problem, piece of cake. Now tried the same thing on an expression type attribute, say "Principal Balance". When I split the column header on two line I get this following error. ============================================================== Semantic query compilation failed: e MeasureNotFound One of the SubtotalMeasures.MeasureName properties of the SemanticQuery refers to the Measure Expression 'Principal Balance', which does not exist. (SemanticQuery ''). ---------------------------- An error has occurred during report processing. ============================================================== If you read the error you will realize that the query is trying to find an expression named "Principal" (newline char) "Balance" and it fails. This expression is nothing but a sum aggregation on a field in the underlying table. If I put the "Principal Balance" back on one line report runs like a charm. Also note that exact same report in Designer no issues at all. FYI: I am in SQL Server 2005 with SP2. (tried it both on Standard and Enterprise versions.)
View Replies !
Expression Builder Elipse Button Does Not Appear
Hello, I am attempting to create an expression for a package variable in a SQL Server Integration Services project, but the elipse button in the expression property for the variable does not appear (Visual Studio 2005). I've tried two different screen resolutions, no luck. Why would the elipse not be there? Is there a keystroke I can use to open the exrpression builder? Thank you for your help! cdun2
View Replies !
String Parsing And Expression Builder....
I can't figure this one out. I don't have enough knowledge of the string functions I guess. I need to pull a value out of a variable I setup in a for each loop. The value is the filename/path of each source file being processed. Let's say the variable that has the source file path is called VAR1. One sort of off topic thing I've noticed is when watch the variable in bebug mode and I look at the value of VAR1 it has double back slashes. Here's an example of the value of VAR1: "\\L3KRZR6.na.xerox.net\C$\Documents and Settings\ca051731\Desktop\Project4\DPT_20070926.ver" How come the back slashes have been doubled? And do I need to account for that when I start parsing the string value? Anyway, I need to grab part of the filename from VAR1 and I need the value populated at the start of the for each loop container - ideally when I capture VAR1 in the for each container. I'll be using the string in drop table, create table and create index statements before the actual Data Flow task within the overall package In the above example I need to grab the characters before the underscore and after the last \. So I'd need the string "DPT" captured in this example. The actual string could be 1 to 3 characters long, even though this example has it as 3 long. Underscores could exist anywhere in the actual UNC path once this package is moved to our actual system environments so I can't key off of the underscore. Because I can't count on the string being a fixed lenght I can't just use a positional string function and grab specific text starting/ending at specific points. Is there a way to use the various string functions in the expression builder to grab the text between the right most underscore and the right most back slashes or something like that? Ideally I'd like to setup a new expression based packed scope variable called VAR2 and build it using string functions applied to VAR1.
View Replies !
Quick SSIS Expression Builder Question
This works: len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) This doesn't: "dataware/"+ len ((DT_WSTR,2)DATEPART("mm", getdate()))==1 ? (DT_WSTR,1)0 + (DT_WSTR,1)DATEPART("mm", getdate()) : (DT_WSTR,4)DATEPART("mm", getdate()) I tried a couple of different things at this point, where am I missing the cast? Before len? Cast to what?
View Replies !
Using Expression Builder To Access A Dialy File Minus One Day
Hello, I am using the following expression to access a daily file. "D:\importdata\peregrinedata\ACD_DATA_" + (DT_WSTR, 4) YEAR( GETDATE() ) + (DT_WSTR, 2)MONTH( GETDATE() ) + (DT_WSTR, 2) DAY( GETDATE() ) + ".txt" output: D:importdataperegrinedataACD_DATA_2008123.txt I need to have this file by current day -1. I've tried dateadd and can't figure out how to get it to work. Thanks
View Replies !
Both Foreground And Background Color Appear To Be White In Expression Builder...
I have a co-worker who applied build 3161 to SQL Server 2005. The original problem was that the print preview was showing up as black.This is a documented issue with Microsoft here: http://support.microsoft.com/kb/935436 Now, when she goes into expression builder, the foreground AND background color for what you type in there appears to be white... so everything shows up as invisible. You can highlight the text you type and it shows up but otherwise it is obviously invisible. Anyone know how to fix this?
View Replies !
Cannot Access Expression Builder Via Variable Properties Window (no Ellipsis)
Has anyone encountered this before? My colleague opens up an SSIS package with variables, many of which use expressions built with the Expression Builder. She cannot bring up the Expression Builder by clicking the ellipsis (...) in the "Expression" Property of the Property Window, regardless of whether "EvaluateAsExpression" is set to True or False. I can open up this same package and use the Expression Builder just fine. Any ideas? Apologies if this has been answered already, I did many searches to try to find the solution before posting here. Thanks much! Brian Pulliam
View Replies !
Left Function
i have a query select message from ticket but i want to only get the first 100 characters select left(message,100) from ticket is giving me an error - please advise?
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 !
Left() And Right() Function In MS SQL Vs MS ACCESS
I normally use MS ACCESS vs MS SQL,, which has a left() and right()function. I need to use MS SQL for this project but I am not familiarwith it. I have read a few books, but can not figure out how to dothis. Please help.If I need to compare the first 4 letters of a field, with the firstfour letters of another field, how can I do this?Select field1, field2 FROM table1 Where left(field1,4)=left(field2,4)(MS SQL does not have left() and right() functions)Please help.In addition, I have a CSV file with data like 10.20, which I importinrto a numberic field. Unforunately the value gets changed to 10.It's seems to get rounded. How can I fix this.The import SQL I use is....BULK INSERT dbo.tableFROM 'c:MYDATA.CSVWITH(FIRSTROW = 1,FIELDTERMINATOR = ',',ROWTERMINATOR = '')Thank you in advance!!!
View Replies !
LEFT And String Function
select left('Hello World /Ok',charindex('/','Hello World /Ok')-1)Hello WorldThat works fine.However I got an error message:select left('Hello World Ok',charindex('/','Hello World Ok')-1)Instead of:'Hello World Ok'I get:Server: Msg 536, Level 16, State 3, Line 1Invalid length parameter passed to the substring function.Microsoft Doc incorrectly says:"LEFT ( character_expression , integer_expression )integer_expressionIs a positive whole number. If integer_expression is negative, a nullstring is returned."Is there an easier solutoin using left or any other string functioninstead of using a case statement?Also, charindex('/','Hello World Ok') should return NULL instead 0 sothat we can use isnull function.Thanks.
View Replies !
Can't Get Left() Function To Work With A Variable.
I have not been able to find the answer as to why the LEFT() function doesn't see the variable as being a variable. I originally thought it did not accept a variable as the first parameter, however the definition says it can be a variable. Anyone knows why this isn't working? This is how I have the code: SELECT LEFT(@tpatdata, CHARINDEX('^', Alert1) -1) the variable @tpatdata is the column name (tablename.Alert1), iif I rewrite it like this: SELECT LEFT(tablename.Alert1, CHARINDEX('^', Alert1) -1) it works.
View Replies !
Performance Issue Using Left Or Substring Function
Hi, I've tried the following query in SQL SERVER 2005, SQL Express and MACCESS. select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905'))) The query is using two table. The first one Table1 is a table with user info. The second table Table2 has the info concerning a survey. The Table1 containt approx. 6000 row and Table2 containt only 210 rows The table structure from the different environment(MACCESS, SQL SERVER 2005, Sql Server Express 2005) are the same. The Table1 containt the field "PostalCode" and "Telephone". When I execute this query on MACCESS and in SQL Server 2005 the result are approximately the same(Less than half second). But there a performance issue in Sql Express 2005. The query take an execution time between 7 and 9 secondes. When I add a condition using a field from tblResponsePQ2Part1 ex: QA=1 like in the following query : select * from Table1 where drid in (SELECT DrID FROM Table2 WHERE (QA = 1 substring(PostalCode,1,3) IN ('B0E','B1P','B2H','B2Y','B3A','B3M','B4A','B4H','E1A','E1C','E1N','G0A', …)) and (substring(Telephone,1,3) IN ('204','250','306','403','416','418','450','506','514','519','604','613','705','780','807','819','902','905'))) the query take an execution time of ~15 secondes!!!! This issue only happen in Sql Server Express, on the others cases(mean MSAccess, Sql Server) the execution time is less than half second. It’s weird because, Sql Express 2005 is supposed to be more performant than MACCESS, and have the same performance than Sql Server Professional Edition. Please Help Me!!!! Anyone have an idea why? Mathieu Desbiens
View Replies !
LEFT Function On Text-field (SQL Server Datatype)
Hello! I am trying to run a query on an SQL-server database: SELECT (Left(Comments, 20)) As shortComment FROM myTable The Field Comments is a Text field (datatype is text), and I can't get the LEFT function to work with this datatype... How can I bypass this problem? Can I convert the commentfield to varchar in the QUERY? noccy
View Replies !
Report Builder Conditional IF Function Problem
Hi everyone, I have created a view from the exisiting table to use in the report builder model and in the table whenever there is a null(when users does not enter a date for this particular field in the application) for datetime field either of these 2 ("1/1/1753" and "12/31/9999") dates are stored . Since I am creating my views based on these tables these dates will be in my views as well. Checking for this at the application level and cleaning up is not a option for me at this time. So what I am trying to do is to check for these values and replace it with Null or blank in the model designer expression property. So that when user creates a report using this field they will not see these sql standard dates. I tried using conditional IF in Model designer . EX: IF(Next MCR Review Date = 1/1/1753,EMPTY,Next MCR Review Date) IF(Next MCR Review Date = 1/1/1753,NULL,Next MCR Review Date) Both of these gives me errors. Can anyone tell me what I am doing wrong and also are there any other ways to get to what I want. I have already spent a lot of time digging for documentation but no luck any help is appreciated. Thanks a Lot Ashwini
View Replies !
Report Builder: RowCount Or Ranking Function
I have a end users that is in need of a some way to rank results in a Report Builder report. I have noticed that neither the RowCount or Ranking function is listed in the avaliable functions or expressions. Does anyone have an idea or solution to perform some type of ranking. Example: Name Count Rank Brad 10 1 Bill 9 2 John 8 3 Seems to be a pretty simple function that was overlooked in Report Builder by MS.
View Replies !
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 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 !
SQL &"left&" Function
I am using the following to display a short version of my description field: <%=LEFT(rs("DESCRIPTION"),150)%> The only problem is that it cuts at exactly 150 chars...so it will display a portion of a word. Can any one help me change this so it will cut the record off at the last "space" before the 150 chars is up? So it doesn't chop the last word.
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 !
Left Join Vs Left Outer Join Syntax Generates Different Execution Plans
Anyone know why using SELECT * FROM a LEFT OUTER JOIN b ON a.id = b.id instead of SELECT * FROM a LEFT JOIN b ON a.id = b.id generates a different execution plan? My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN". Any enlightenment is very appreciated. Thanks
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 !
Select Where Left In Left
hello for MS SQL 2000, i cannot get it i am having names like AB_12 I want to get all rows with left part similar , AB im that case SELECT id, name FROM Users WHERE LEFT(name, CHARINDEX('_', name) - 1) AS name IN ( SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users GROUP BY LEFT(name, CHARINDEX('_', name) - 1) HAVING (COUNT(*) > 1) ) does not work is there any way to use a variable ? declare @nm nvarchar set @nm = SELECT LEFT(name, CHARINDEX('_', name) - 1) AS ns FROM Users thank you for helping
View Replies !
Multiple Left Joins (2 Left Joins 1 Big Headache)
Hi All, Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:- Microsoft OLE DB Provider for ODBC Drivers error '80040e14' [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'. My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
View Replies !
Report Builder: How To Provide Parameter In The Report Generated Report Builder
Hi All, I have used Report Builder in one of the project for client. Is it possible to provide parameter in the report that is generated by Report Builder? It's quite the same with the parameter in usual Report/RDL (by Visual Studio). And is it possible to pass the UserID that is running the report to the Data Source View or directly to the query? Currently, I need to get the UserID of the user that runs the report to do some filtering of the data (in SQL Query). I can do this easily in the RDL file developed by Visual Studio, which I can use global parameter User!UserID and pass to query. I find difficulty to use SQL Execution Context (select USER_NAME(),SUSER_NAME()), because report needs to be run as certain account, so that the report can be subscribed by user (e.g. email subscription). How to pass this UserID to the query/data without end-user intervention in Report Builder? Thanks.
View Replies !
Report Builder: How Can I Append Two Table Fields To Report In Report Builder
Hi, When i select datasource in Report Builder, i am able to see all the available DataSources. Eg: I have selected one datasource from the list and which has 3 tables(table1, table2, table3) associated to that datasource. when i drag and drop table1 fields to report, i am not able to see the other 2 tables(table2 & table3) Is there any property or relationship do i need to maintain? Thanks, SR.
View Replies !
Retrieving Result Set From Dynamically Called Stored Procedure Or Function In A Function
Is there any way I can retrieve the result set of a Stored Procedurein a function.ALTER FUNCTION dbo.fn_GroupDeviceLink(@groupID numeric)RETURNS @groupDeviceLink TABLE (GroupID numeric, DeviceID numeric)ASBEGINDeclare @command nvarchar(255)SELECT @command = Condition// @command is an SQL string or stored procedue nameFROM DeviceGroupWHERE GroupID = @groupIDINSERT @groupDeviceLinkEXEC @commandRETURNENDIs there any way i can do anything like this. @command is a variableholding the name of a stored produre. I need to run that storedprocure and return the values in such a way that they can be used in aSELECT StatementMy goal is SELECT * FROM Device INNER JOINdbo.fn_GroupDeviceLink(@groupID) ON ....this fn_GroupDeviceLink should run the proper stored procedure andreturn the values. What i also want to do is play with that result setof the specific stored procedure before i return it. Is this possible?If not, what is the work arround?ThanksMark
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 !
In-Line Table-Valued Function: How To Get The Result Out From The Function?
Hi all, I executed the following sql script successfuuly: shcInLineTableFN.sql: USE pubs GO CREATE FUNCTION dbo.AuthorsForState(@cState char(2)) RETURNS TABLE AS RETURN (SELECT * FROM Authors WHERE state = @cState) GO And the "dbo.AuthorsForState" is in the Table-valued Functions, Programmabilty, pubs Database. I tried to get the result out of the "dbo.AuthorsForState" by executing the following sql script: shcInlineTableFNresult.sql: USE pubs GO SELECT * FROM shcInLineTableFN GO I got the following error message: Msg 208, Level 16, State 1, Line 1 Invalid object name 'shcInLineTableFN'. Please help and advise me how to fix the syntax "SELECT * FROM shcInLineTableFN" and get the right table shown in the output. Thanks in advance, Scott Chang
View Replies !
Getting Left _
how can I can update a table :: I must get all the left part of a columns part1_part2I dont know how many characterssomething like :UPDATE Users SET Users.Info = left(Users.name, _ ??? or split('_'))in that case Users.name = part1i must update only the one with a '_' and do nothing for the othersthank you
View Replies !
Pad Left
Hi, I am trying to pad a field from the left with zeros that is defined in a table as "[char] (9)". The field can have from 1 to 9 chars populated. In this instance the value in the field is "133000", which I am trying to pad so that it will be "000133000". I have made several failed attempts to do this, the latest of which was this: select right(replicate('0',9)+ convert(varchar(9),a.GS06GRP_CTL_NBR),9) fromTable1 a Thanks, Jeff
View Replies !
|
TRACKER
