Simple Expression Fails. Must Be A Bug?
Sep 20, 2007
Check out this simple query
SELECT 0.172 * (365 / (90 + 91)) * 1.26 AS test1
, (0.172 * 365) / (90 + 91) * 1.26 AS test2
, 0.172 / ((90 + 91) / 365) * 1.26 AS test3
test1 and test2 should give the same result, but they don't?! test1 gives wrong result; test 2 gives correct result. Furthermore, test3 gives a Divide by Zero???
This makes no sense.
(365 / (90 + 91)) is a constant I use many times in my code; only way to procede is to replace it by a number, or have i misunderstood something?
I am running the query on SQL Server 8.0.2040
Regards,
Martin
View 5 Replies
ADVERTISEMENT
Jul 10, 2006
What is the equivalent SSIS expression for this C expression to format an unsigned integer by padding it with leading zeros?
sprintf(publ_doc_id, "%010u", id)
View 1 Replies
View Related
Apr 22, 2008
So, here is my expression that I use in a textbox on the site header. In the report it just returns error... any ides?
="Wareneingangsübersicht" + " " + today() + " / " +
iif(datepart("WeekdayName",dateadd("d",1,today()))= "Samstag", DateAdd("d",3,today()), DateAdd("d",1,today()))
View 5 Replies
View Related
Jul 26, 2006
I'm trying to set the value of the SelectedDatabases collection property of an Update Statistics Task using an expression for the task, basing it on a user variable. Ultimately, I'll set that variable's value from a configuration file at run time and thereby specify the database whose statistics I want to update.
When I set this up in the designer, I get an error at the time I try to save the package:
TITLE: Microsoft Visual Studio
------------------------------
Nonfatal errors occurred while saving the package:
Error at Update Statistics Task: The result of the expression "@[User::SelectedDatabase]" on property "SelectedDatabases" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
Error at Update Statistics Task: The result of the expression "@[User::SelectedDatabase]" on property "SelectedDatabases" cannot be written to the property. The expression was evaluated, but cannot be set on the property.
------------------------------
BUTTONS:
OK
------------------------------
I used the expression editor's expression builder to select the user variable, so I assume the syntax is correct. However, I'm suspecting that there's some additional syntax required to store that value in the collection's array. I cannot find any references to any special formats and am wondering if anyone know's how to do this correctly. On the other hand, maybe it is telling me setting this property from an expression is not even allowed. If it's not allowed, why is it in the list of properties to set? In any case, none of this is clear.
Any ideas or suggestions are appreciated. Thanks,
Joe
View 8 Replies
View Related
Nov 18, 2005
This is the error I get with a simple Data Reader Source (SELECT * FROM A) to SQL Server Destination copy between identical tables from a linked to a local server:
View 6 Replies
View Related
Feb 8, 2007
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 4 Replies
View Related
Oct 28, 2015
I have created 1 report with 2 datasets. This report is attached to the 1st dataset.For example,1st one is "Smallappliances", 2nd is "Largeappliances".
I created a tablix and, the 1st column extracts Total sales per Sales person between 2 dates from 1st dataset (Small appliances). I used running values expression and it works fine.
Now, I would like to add another column that extracts Total sales per sales person between 2 dates from 2nd dataset (Large appliances). I am aware that I need to use Lookup expression and it is giving me the single sales value rather than the total sales values. So, I wanted to use RunningValue expression within lookup table to get total sales for large appliances.
This is the lookup expression that I added for the 2nd column.
=Lookup(Fields!salesperson.Value,Fields!sales_person.Value,RunningValue(Fields!sales_amount.Value,
sum, " sales_person"),
"Largeappliances").
I get this error when I preview the report.An error occurred during local report processing.The definition of the report is invalid.An unexpected error occurred in report processing.
(processing): (SortExpression ++ m_context.ExpressionType)
View 7 Replies
View Related
Jun 11, 2007
I did a small package with only one ODBC connection (Merant 3.70 32-Bit Progess). This package runs well in Visual Studio and fails when runs by SQL Server Agent.
Configuration:
SQL Server Agent on a 32Bit server.
The ODBC connection configuration in available on System DSN on this server.
The user of Server Agent have full access (Admin).
Connect Manager Provider: ".Net ProvidersOdbc Data Provider"
SQL Server version: 9.0.3042
Error Message:
Executed as user: TEKCON cadmin. ...ion 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 16:50:33 Error: 2007-06-11 16:50:33.62 Code: 0xC0047062 Source: Data Flow Task DataReader Source [1] Description: System.Data.Odbc.OdbcException: ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. ERROR [HYC00] [MERANT][ODBC PROGRESS driver]Optional feature not implemented. ERROR [HY000] [MERANT][ODBC PROGRESS driver]msgOpen: unable to open message file: PROMSGS ERROR [IM006] [MERANT][ODBC PROGRESS driver]Driver's SQLSetConnectAttr failed. at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode) at System.Data.Odbc.OdbcConnectionHandle..ctor(OdbcConnection connection, OdbcCon... The package execution fa... The step failed.
I created a .bat file with this instruction and It's run well:
dtexec /f "C:Program FilesMicrosoft SQL Server90DTSPackagesIntegration Services Project estcom.dtsx"
pause
Why it's not running with SQL Server Agent???
View 7 Replies
View Related
May 26, 2004
Hey,
I have MS SQL database.
I have procedure:
code:--------------------------------------------------------------------------------
CREATE PROCEDURE dbo.Reg_DropTable
@ModuleId varchar(10)
AS
declare @TableName varchar, @kiek numeric
set @TableName = 'reg_'+@ModuleId
begin
DROP TABLE @TableName <- HERE I GOT ERROR
end
GO
--------------------------------------------------------------------------------
I got error when using variable with tables names.
How to do this?
Ps. Number is send to this function and it must drop table with name Reg_[That number]
View 1 Replies
View Related
May 8, 2008
We are trying to install the Standard Edition of SQL Server 2005. We already have SQL Express installed. Standard won't install and Express won't uninstall.
We have tried every suggestion we can find and some we made up. When we try to install Standard, we get to the bit where you choose what you want to install and the only thing available is workstation components.
There are so many cases of this on the Internat that I can't believe MS have not solved the issue. And I am stunned that they want to charge us more money to talk to us about how to fix something we just paid them an arm and a leg for!
What do I need to tell you to get some idea of where to go next with this?
View 11 Replies
View Related
Feb 21, 2007
I am receiving funny results from a query. To simplify, I have 2 tables (todayyesterday). Each tbl has the same 8 columns. My query joins the two tables then looks where either of two columns has changed. What is happening is that when checking one of the columns it seems as though sql is flipping the column, causing it to be returned in error.
result set
colA colB colC colD colE colF colG colG (from yesterday)
1 1 a b c d e m
1 1 a b c d m e
So what's happening is that the record above is actually the same record and should not be returned. There is a daily pmt column that changes but I am not using that in the query. Aside from that the two records are identicle.
Any help is appreciated.
View 7 Replies
View Related
Aug 19, 2006
Hi,
I have the following situation (with a site that already works and i cannot modify the database architecture and following CrossRef tables -- you will see what i mean by CrossRef tables below)
I have:
Master table Hotel
table AddressCrossRef (with: RefID = Hotel.ID, RefType = 'Hotel', AddrID)
joins
table Address (key = AddrID)
table MediaCrossRef (with RefID = Hotel.ID, RefType= 'Hotel', MediaID)
joins
table Media (with MediaID,mediaType = 'thumbnail')
foreach hotel, there definitely is a crossRef entry in AddressCrossRef and Address tables respectively (since every hotel has an address)
however not all hotels have thumbnail image
hence i have hotel inner join AddressXReff inner join Address ..... however i must have
left outer join mediaXref left outer join media
the problem is that if there is no entry in Media or mediaXref, I don't get any results
i tried to get over it by using
where (media.mediaTyple like 'thumbnail' or media.mediaType is null)
but then i started getting multiple results for each hotel because media's of type movie or full_image or etc... all got returned
any clue?
thanks
View 5 Replies
View Related
May 27, 2008
well I have this code. I connect to database and submit data.
now i dont know which expression to use to validate if data is submited redirect to next level else show some error.
protected void Button1_Click1(object sender, EventArgs e) { SqlConnection con = new SqlConnection(@"somecode");
SqlCommand comm = new SqlCommand(); comm.Connection = con; comm.CommandText = "insertuser"; comm.CommandType = CommandType.StoredProcedure;some code... comm.Connection.Open(); comm.ExecuteNonQuery(); con.Close(); }
i dont wanna validate the fields as i can do it using field validator i wanna check if data is inserted in database.
View 3 Replies
View Related
May 28, 2008
Hi
When I try and create this procedure, I get the error below
CREATE PROCEDURE GetUserSearchTypesCount
-- OUTPUT parameter to hold the count.
@UserID int,
@ReturnVal int OUTPUT
AS
SET @ReturnVal = (SELECT TOP 100 percent searchType, COUNT(searchtype) AS SearchCount
FROM ttracksearchresults
WHERE userid=@UserID
GROUP BY searchtype
ORDER BY searchtype)
GO
Error: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
View 3 Replies
View Related
Feb 7, 2007
I need a statement like:UPDATE tblQuestions SET QuestionNumber = QuestionNumber +1 WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
(Required output is clear from statement itself)
I tried with
UPDATE tblQuestions SET QuestionNumber = EVAL(QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
and
UPDATE tblQuestions SET QuestionNumber = (QuestionNumber +1) WHERE QuestionNumber >=10 AND TestID = 1 AND SectionNumber = 1
I've just started working with SQL. Please suggest correct approach.
View 3 Replies
View Related
Feb 1, 2008
I have the following code in the expression builder for a ExecuteSQL Task in the Control Flow.
"INSERT dbo.StgFileList SELECT ' " + @[User::MyFile] + " ' "
When I evaluate it, it renders as INSERT Staging.StgXMLFileList SELECT ' '
What am I doing wrong in the expression builder?
View 5 Replies
View Related
Feb 20, 2008
Is it possible to nest expressions within a script in a similar fashion to how they're nested within the expression editor?
I have a requirement to strip trailing digits from a string field, so that only two decimal places are left.
e.g.
1.12345 becomes 1.12
12345.6789 becomes 12345.67
I would do this in the expression editor but I need to use Script component because there are a multitude of conditions that need to be evaluated first which I will do inside a Select Case statement.
I'm wrestling with code that looks like
Newratefield = ratefield.Substring(1, Findstring(ratefield, ".", 1) + 2)
but VB doesn't like the 'Findstring(ratefield, ".", 1) + 2)' part.
Any suggestions on what I can do here?
View 3 Replies
View Related
Jan 9, 2007
Hi,
In RunningValue expression, can we able to put the DataSet name.
Thanks
View 17 Replies
View Related
Jul 27, 2007
Hi,
In ssis I would like to map a column from table a to a column in table b
Currently this is how I am doing it using sql.
I believe I have to use a derived column but not sure how to implement if statement inside it.
Thanks
case
when len([Column 5]) = 0 then ''
when lower([Column 5]) = 'unknown' then ''
when isdate([Column 5]) = 1 then substring(ltrim([Column 5]), 1, 4) + substring(ltrim([Column 5]), 6, 2) + substring(ltrim([Column 5]), 9, 2)
else
null
end,
View 4 Replies
View Related
Dec 20, 2006
The Value expression for the textbox €˜Name€™ uses an aggregate expression without a scope. A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set....what does this mean...this an error that I have when i try and build my report i get this error for some reason...how do i fix this???
View 4 Replies
View Related
Sep 28, 2007
Hi,
I need to replace a field that might contain a certain character with another should the value occur.
To explain
If i get a hash # in the field, then I need to replace the whole field with a 0 since its a numeric field
I'm just a bit of a noob still with expressions
Regards
View 3 Replies
View Related
Aug 17, 2007
Hi All,
SUM(IIF(Fields!NO.Value >= 1, Fields!tsHours.Value,0))
i write this expression inside the group footer row, it give a #error when generating report.
please help
Cheers
Nick
View 5 Replies
View Related
Aug 26, 2007
Hi,
Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like?
Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%.
Cheers,
koekie
View 3 Replies
View Related
Jun 30, 2005
Hello,In VB/VB.NET, I can use an expression as such to evaluate true/false:Dim blnValue As Boolean = (SomeObject.Property = "Some value")Can I do this in T-SQL?declare @var bitset @var = ?Thanks.
View 2 Replies
View Related
Jan 12, 2006
Hello, I need to create a table where a column's data should have a hypen inserted after the second character. The table will accept a large input of rows, so I think it would be better to use a SQL expression or formula on the column to provide the hyphen on insert rather than doing an update statement later. There is no way of knowing how many characters will follow the hyphen.
So for example... 888888 would become 88-8888
or 5555555555555555 would become 55-55555555555555
Any help would be great!
View 4 Replies
View Related
Jan 10, 2007
Hi Guys
I have a table in which one of the column name is "NAME".
I have to write query in which column is not null and if there is something then check for 'trust' or 'trustee' in the string. and if found don't pull that record. skip it
who do i do these. Any help please.
View 2 Replies
View Related
Mar 2, 2006
how can i CONCAT 2 columns & () ?
SELECT CONCAT(A,B) AS C From myTAble
but I want to get A (B)
dog (red)
thank you
View 2 Replies
View Related
Mar 25, 2008
HiI'm looking to return rows from a table where a column is LIKE a keyword. This is my current code:SELECT DISTINCT business_name FROM Table WHERE business_name LIKE @keyword + '%' ORDER BY business_name ASCThe problem with this is that if a business is called "The White Ship Inn", and the keyword to match is "White Ship" it will not match. If i put another wild card in front of @keyword then this finds too many matches - i need it to match against columns which either start with the keyword, or have a whitespace in front of them. Is there some kind of T-Sql i can use or will i have to go down the regex route (using this (http://www.codeproject.com/KB/mcpp/xpregex.aspx)) ?cheers
View 2 Replies
View Related
May 6, 2004
Does SqlServer support Regular expression?
I want to know if it's avilable or not...
And if it's not available, I want to know any other efficient way to validate string.. manipulating Regular expression style validation.
Thank you all...
View 6 Replies
View Related
Feb 2, 2014
The full error is this: Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
My Code:
SELECT Customers.CustomerID, Customers.EmailAddress, Customers.LastName, Customers.FirstName,
(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers
ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.BillingAddressID),
(SELECT Addresses.Line1, Addresses.Line2, Addresses.City, Addresses.State, Addresses.ZipCode FROM Addresses JOIN Customers
ON Addresses.CustomerID = Customers.CustomerID WHERE Addresses.AddressID = Customers.ShippingAddressID)
FROM Customers JOIN Addresses
ON Customers.CustomerID = Addresses.CustomerID;
I am trying to test a query I am going to use to create a view with all of a customers addresses (billing and shipping).
View 5 Replies
View Related
Sep 12, 2005
I have an expression based upon 2 fields in a view that I want to be able to divide and then multiply by a number. Whenever I do the division portion of the calculation all I get are 0 & 1's. I was hoping to use the percentage in other calculations.
Is there something similar to FORMAT(X/Y,0.00)?
(vwLbrPosSum.PosMin/vwMinByEmp.TotMin) x (a)vwLbrPosSum.PosMin or (b) 2400 or (c)vwMinbyEmp.TotMin - 2400
If the person worked more than 40 hrs I could allocate the OTMins to the different positions in labor reporting.
Thanks in advance for your assistance.
View 2 Replies
View Related
Dec 4, 2007
Hi i'd like to create a expression to find if the first character is 0 and if it is then don't display it, but if the first charactar is not 0 then its ok, am trying to do this within a drived column in SSIS
View 14 Replies
View Related
