Greetings my SQL friends.
Why do I get an error message saying that the following expression won't evaluate?
"Select * from Price_grp where price_grp_id >= " + (dt_str, 10, 1252) @[User::MIN_PRICE_GRP_ID] +
" and " + (dt_str, 10, 1252) @[User::MIN_PRICE_GRP_ID] + " > 0"
Thanks for your help in advance.
I have been looking all over for some info about other people having this problem, but haven't found anything.
I have a package that needs to download a dated file from an ftp site. I am using a couple script objects to set variables, and one of them is the filename based on the date. I use an expression to get the date:
@[User::varFileName] = (DT_WSTR,4) Year( GetDate()) + (DT_WSTR,2) Month( GetDate()) + Substring((DT_WSTR, 29) GETDATE(), 9, 2)
Everything works really well when I am debugging it locally. However once it is on the server or even once I come back to it in a day or two, I am still seeing the old date. I thought it might be because my variable needed to be set to evaluateexpression = true, however once I did this it hung me and prevented me from debugging and I had to end bus dev studio. Not sure if its because it is being evaluated in two places (as a global and then in a script) but when I took it out of my script it hung again. Its strange in order to get it to work when I am debugging it locally I have to go to each process and evaluate the expressions in there, then it seems to work. thanks!
Hi all€”I'm new to expressions, and am trying to write one where I can evaluate if a field is null, to handle it by inserting a default value if a null value is found. Given this table definition:
CREATE TABLE [dbo].[Meter Readings_stage](
[Meter System ID] [varchar](8) NOT NULL,
[Reading Date/Time] [datetime] NOT NULL,
[Meter Status] [varchar](50) NULL,
[Totalizer] [float] NULL,
[Secondary Totalizer] [real] NULL CONSTRAINT [DF_Meter Readings_Secondary Totalizer2] DEFAULT (NULL),
[Demand] [real] NULL CONSTRAINT [DF_Meter Readings_Demand2] DEFAULT (NULL),
[Demand Date/Time] [datetime] NULL CONSTRAINT [DF_Meter Readings_Demand Date/Time2] DEFAULT (NULL),
[Reading Type] [varchar](30) NULL CONSTRAINT [DF_Meter Readings_Reading Type2] DEFAULT (NULL),
[Comments] [varchar](200) NULL CONSTRAINT [DF_Meter Readings_Comments2] DEFAULT ('None'),
[Checked?] [bit] NULL CONSTRAINT [DF_Meter Readings_Checked ?2] DEFAULT ((1)),
[Estimate Required] [bit] NULL CONSTRAINT [DF_Meter Readings_Estimate Required2] DEFAULT ((0)),
[Hold] [bit] NULL CONSTRAINT [DF_Meter Readings_Hold2] DEFAULT ((0)),
[Processed?] [bit] NOT NULL CONSTRAINT [DF_Meter Readings_Processed2] DEFAULT ((0))
I am importing data from a CSV file where any of the fields can be null. The format is as follows:
[Meter System ID],[Reading Date],[Reading Time],[Totalizer],[Demand],[Demand Date],[Demand Time], [Reading Type]
I need to handle it something like the following in a derived column transformation, where:
[Meter System ID]: string [DT_STR]
[Reading Date]: database date [DT_DBDATE]
[Reading Time]: database time [DT_DBTIME]
[Totalizer]: float [DT_R4]
[Demand]: float [DT_R4]
[Demand Date]: database date [DT_DBDATE]
[Demand Time]: database time [DT_DBTIME]
[Reading Type]: string [DT_STR]
This is what I have built so far:
NULL((DT_DBDATE)("Reading Date")) ? (DT_DBDATE)(GETDATE()) : (DT_DBDATE)("Reading Date")
I am getting an error on the cast conversion, however. Where is the error in the evaluation?
Thanks,
Jon
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
)
$exception {"Arithmetic overflow error converting expression to data type smalldatetime.
The statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}
occurs
here is my code
protected void EmailSubmitBtn_Click(object sender, EventArgs e)
{
SqlDataSource NewsletterSqlDataSource = new SqlDataSource();
NewsletterSqlDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["NewsletterConnectionString"].ToString();
//Text version
NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.Text;
NewsletterSqlDataSource.InsertCommand = "INSERT INTO NewsLetter (EmailAddress, IPAddress, DateTimeStamp) VALUES (@EmailAddress, @IPAddress, @DateTimeStamp)";
//storeprocedure version
//NewsletterSqlDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
//NewsletterSqlDataSource.InsertCommand = "EmailInsert";
NewsletterSqlDataSource.InsertParameters.Add("EmailAddress", EmailTb.Text);
NewsletterSqlDataSource.InsertParameters.Add("IPAddress", Request.UserHostAddress.ToString());
NewsletterSqlDataSource.InsertParameters.Add("DateTimeStamp", DateTime.Now.ToString());
int rowsAffected = 0;
try
{
rowsAffected = NewsletterSqlDataSource.Insert();
}
catch (Exception ex)
{
Server.Transfer("NewsletterProblem.aspx");
}
finally
{
NewsletterSqlDataSource = null;
}
if (rowsAffected != 1)
{
Server.Transfer("NewsletterProblem.aspx");
}
else
{
Server.Transfer("NewsletterSuccess.aspx");
}
Hi, I'm creating a dynamic SQL statement in MS SQL Server that is similiar to this:
EXEC('IF @' + @current_column + ' (SELECT ' + + @current_column etc...
I'm basically looping through a large list of parameters that correspond to column names. However, since SQL Server treats EXEC() as its own scope when it gets to what "@' + @current_column" evaluates to it says the parameter must be declared.
Is there a way to convert "@' + @current_column " into the actual value of the parameter?
We recently moved from v6.5 to v7.0. Now I have the databases and logs set to "autogrow". How can I monitor the disk space to ensure I do not run out of room (or is that preset as to how large it can grow ?). Can't find anything in the books online. Do I do this through the NT admin tool or through the SQL*Server Enterprise Manager and more importantly - how ???
Thanks so much for any help...
Nancy
Hi,I am evaluating Lumigent's Entegra for doing security and businessaudit of some of the critical database(s) in the company I work for. Iwould like to know what has been your experience in using this productfor doing similar audits in your company, if you have also done suchaudits.Thanks,Sanjeev
View 2 Replies View RelatedOur Business partners request me to read the field names from a SQL table dynamically so that my SSIS package will not get impacted if Web Service hosts make a change.
Is there a way to evaluate a string at runtime that contains a field name ?
Thanks,
Gulden
Hello all,
I currently have a pain in the butt with a subquery that needs to be evaluated, and if the result gives me a null value, I want to re-evaluate the condition and fill the column with the proper information.
I'll try to explain it as best as I can:
The query retrieves information from 2 tables basically, but i do need some inner joins of diferent tables in order to follow the relations..
I got 2 conditions that need to be acomplished, the 'typeof' (intId_Tipo) has to be diferent from 6 and a keycode equals to 3.
For the "general query" i do filter this just fine, but in the subquery is where I get stucked:
Since the evaluation that I do in oder to find the initial point is just by substracting 1, there are some cases where the type called intId_Tipo is actually 6 and the subquery returns me a null value.
In this particular case I want to substract 2..if not I just want to substract 1.
I added a SQL CASE in oder to evaluate the null value, but it is not evaluating properly, why? I'm not sure, I need your help and recomendation guys
Here's the query that I currently have: (the comments were made in order to take the next screenshot)
Code Snippet
SELECT --h.dt_Lectura,
--h.int_Velocidad, rp.int_VelMaxima,
(SELECT intpunto FROM tblRecorrido_Puntos WHERE intPunto = case when (intPunto) is null then rp.intPunto - 2 else rp.intPunto -1 END AND rp.intId_Ruta_Ramal =intId_Ruta_Ramal AND intId_Tipo =rp.intId_Tipo) as PuntoInicial,
rp.intPunto as PuntoFinal
--rp.strDescripcion_Punto as DescripcionPuntoFinal,
--1 as Contador
--a.idRuta_Guid, a.idRamal_Guid,a.idUnidad_Guid, a.idOperador_Guid
FROM tblHistorico h
INNER JOIN tblAsignaciones a
ON a.id=h.intIdAsignaciones
INNER JOIN tblRutaRamal_Generado rrg
ON rrg.id_Ruta = a.intRuta_Asignada
INNER JOIN tblRecorrido_Puntos rp
ON (rp.intId_Ruta_Ramal =rrg.intId_Ruta_Ramal AND rp.intPunto=h.int_PtoDest)
WHERE (
h.int_ClaveTDE =3 and rp.intId_Tipo <> 6
)
ORDER BY rp.intPunto ASC
Here's the screenshot of the query result: http://img218.imageshack.us/my.php?image=consultahd0.jpg
Any help is appreciated!
Jaime,
I want to do some error checking on the parameters found in a SQLDataSource before I run the insert. The problem is these are ControlParameters and I want to do this dynamically so I can't just call the Control.Text property and grab its value. So how can I get access to what the ControlParameter evaluates to?
Secondly, is there a way to access what the update parameters evaluate to in order to check them before they're inserted - if so how do I get access to these?
Here's an example of one of the data sources i'm using:<asp:SqlDataSource ID="sqlContact" runat="server" ConnectionString="<%$ ConnectionStrings:strConn %>"
SelectCommand="SELECT [ContactID], [FirstName], [LastName], , [Address], [Phone], [Grade], [Contacted], [ListServe] FROM [Contact]"
UpdateCommand="UPDATE Contact SET FirstName = @FirstName, LastName = @LastName, Email = @Email, Address = @Address, Phone = @Phone, Grade = @Grade WHERE ContactID = @ContactID"
DeleteCommand="DELETE FROM [Contact] WHERE ContactID = @ContactID"
InsertCommand="INSERT INTO [Contact] ([FirstName],[LastName],,[Address],[Phone],[Grade],[Contacted],[ListServe]) VALUES (@FirstName,@LastName,@Email,@Address,@Phone,@Grade,0,0)">
<UpdateParameters>
<asp:Parameter Name="FirstName" Type="String" />
<asp:Parameter Name="LastName" Type="String" />
<asp:Parameter Name="Email" Type="String" />
<asp:Parameter Name="Address" Type="String" />
<asp:Parameter Name="Phone" Type="String" />
<asp:Parameter Name="Grade" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:ControlParameter Name="FirstName" Type="String" ControlID="txtContactFirst" PropertyName="Text" />
<asp:ControlParameter Name="LastName" Type="String" ControlID="txtContactLast" PropertyName="Text" />
<asp:ControlParameter Name="Email" Type="String" ControlID="txtContactEmail" PropertyName="Text" />
<asp:ControlParameter Name="Address" Type="String" ControlID="txtContactAddress" PropertyName="Text" />
<asp:ControlParameter Name="Phone" Type="String" ControlID="txtContactPhone" PropertyName="Text" />
<asp:ControlParameter Name="Grade" Type="String" ControlID="ddlContactGrade" PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
An Event is fired when I click add on a button which looks similar to this:
//Add btnAddContact_Clickprotected void btnAddContact_Click(object sender, EventArgs e)
{
InsertRow(sqlContact);
}The InsertRow() function is then what i'm using to evaluate the values... So how can get the values those controlparameters actually are in order to evaluate them before I actually insert. Or is there a better way to do it?
Hi
I'm updating an old Access application to SQL Server and am currently trying to decipher one of the reports on the old application. It appears to be evaluating a derived column from one query (qryStudentSuspGroup.Suspension) in the Select statement of another. I have tried to put the query that creates the derived column in as a nested query into the other query but can't get it to work. This is all a bit beyond my rudimentary SQL skills! Any help would be greatly appreciated!
The original Access SQL appears below:
SELECT [Enter the academic year (4 digits)] AS [input], ResearchStudent.Department, ResearchStudent.DateAwarded,
ResearchStudent.StudentNumber, Person.Forenames AS fore, Person.Surname AS Sur, ResearchStudent.Mode,
ResearchStudent.RegistrationDate, StudentExamination.Decision,
IIf(([Suspension]) Is Null Or [Suspension]=0,([DateAwarded]-[RegistrationDate])/365,(([DateAwarded]-[RegistrationDate])-([Suspension]))/365) AS CompDate,
ResearchStudent.EnrollmentCategory, qryStudentSuspGroup.Suspension
FROM ((ResearchStudent LEFT JOIN Person ON ResearchStudent.ResearchStudentID = Person.PersonID)
LEFT JOIN qryStudentSuspGroup ON ResearchStudent.ResearchStudentID = qryStudentSuspGroup.ResearchStudentID)
LEFT JOIN StudentExamination ON ResearchStudent.ResearchStudentID = StudentExamination.ResearchStudentID
WHERE (((Year([DateAwarded]))>=[Enter the academic year (4 digits)]
And (Year([DateAwarded]))<=([Enter the academic year (4 digits)]+1))
AND ((IIf(Year([DateAwarded])=[Enter the academic year (4 digits)],Month([DateAwarded])>8,Month([DateAwarded])<9))<>False))
ORDER BY ResearchStudent.Department, ResearchStudent.Mode, ([DateAwarded]-[RegistrationDate])/365
I have two int fields in my database, CEOAnnualBonus and CEOBonus, and I want to return the value of whichever one has the larger value as CEOBonusCombined. I thought using COALESCE would do the trick like below but there are many cases where either CEOAnnualBonus or CEOBonus have a zero value instead of NULL and it doesn't work.
SELECT
COALESCE(CEOAnnualBonus, CEOBonus)
AS CEOBonusCombined
FROM tbenchmarktemp
WHERE Ticker='F'
Thanks for any help
In reference to this thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=50392
madhivanan asks for sample table data to avoid using a cursor. I could really use this, so here is the table
CREATE TABLE #T (
FORMULA VARCHAR(15),
ANSWER decimal(15,8)
)
INSERT INTO #T SELECT '1', NULL
INSERT INTO #T SELECT '1.0 + 2', NULL
INSERT INTO #T SELECT '1/2', NULL
INSERT INTO #T SELECT '1/2+2', NULL
INSERT INTO #T SELECT '1/(2+2)',NULL
INSERT INTO #T SELECT '3*7.5', NULL
INSERT INTO #T SELECT '3/2*.6-2', NULL
INSERT INTO #T SELECT '(1-1)', NULL
SELECT * FROM #T
-- RUN THE MATH FUNCTIONS, UPDATE THE TABLE'S ANSWER COLUMN WITH THE ANSWER
SELECT * FROM #T
DROP TABLE #T
I would expect this output:
FORMULA ANSWER
====================
1 NULL
1.0 + 2 NULL
1/2 NULL
1/2+2 NULL
1/(2+2) NULL
3*7.5 NULL
3/2*.6-2 NULL
FORMULA ANSWER
====================
1 1.00000000
1.0 + 2 3.00000000
1/2 0.50000000
1/2+2 2.50000000
1/(2+2) 0.25000000
3*7.5 22.50000000
3/2*.6-2 -1.10000000
(1-1) 0.00000000
Any help appreciated, thanks,
I have a table with some examples below. I need to identify records where:
1. the person has 3 or more consecutive months ordered.
2. I then need to exclude the first and last month and capture only those Months in between.
PERSON ID MONTH ORDERED
JD12345 4
JD12345 7
JD12345 8 Note: JD12345 should be excluded entirely
[code]....
What will be the best way to write this code? I have the following code:
BEGIN
declare
status_sent as nvarchar(30)
SET NOCOUNT ON;
case status_sent =( select * from TableSignal
[Code] ...
then if Signal Sent.... do the following query:
Select * from package.....
if signal not sent don't do anything
How to evaluate the first query with the 2 options and based on that options write the next query.
I would appreciate assistance as to how I should correct the following CASE expression::
BilledCarrier = CASE LEN(cur_billed_carrier) WHEN >0 THEN cur_billed_carrier ELSE 'Not Billed' END
I want to return the cur_billed_carrier when its length is >0 but I can't find the correct syntax. My attempt returns an error at the '>' operator. Thank you.
I'm getting this invalid expression error (#error) when I try to return the following in a text box. Is there syntax error?
=Parameters!CampaignSiteName.Label
Hi all,
I've recently started using reporting services after about a 2 year break and have been refreshing my knowledge. I've got a very simple report linked into MS CRM, the report is designed to display cases that are overdue, displaying green colums for cases less that 2 hours old, orange for 2 - 4 hours and red for over 4 hours.
In the backgroundColor field for the details colums I have this code-
Code Snippet
=iif(Fields!Hours_Neglected.Value < 2, "Green", iif(Fields!Hours_Neglected.Value > 4, "Red", "Goldenrod"))
this works fine in the preview window in VS2005 but when I try to deploy I get this error:-
"Warning 1 [rsRuntimeErrorInExpression] The BackgroundColor expression for the textbox €˜ticketnumber€™ contains an error: Input string was not in a correct format. G:AppsVisual Studio 2005ProjectsCRM ReportsCRM ReportsNo Responce.rdl 0 0"
Can anyone shed any light on what this problem is as I'm struggling to find anything online......
Thanks in advance
I am getting an error from the case part of the select statement below which reads 'Incorrect Column Expression' then it quotes the case statement. All I am trying to do is convert and return the weight value to kilos if it was entered in pounds.
SELECT Salesinv.Unique, Salesinv.SalesNo, Salesinv.PurchNo, Salesinv.SalesInvNo, Salesinv.InvValue,
(case when Salesinv.WUnits = 'Llb' then round(Salesinv.NettWeight/2.2046,0) else Salesinv.NettWeight end)
FROM Salesinv Salesinv
WHERE (Salesinv.Unique>=38397.3092 And Salesinv.Unique<=38537.39885)
Any help would be greatly appreciated, hopefully thanks in advance.
insert into----
select ID_NO,cast(row_number() over(partition by ID_NO order by ID_NO)as varchar(2))
from
test_222
I am trying to insert into test222 table .The id_no column is varchar field
error:
Arithmetic overflow error converting expression to data type varchar.
The statement has been terminated.
Hello friends'
I am new to integration services,first time I'm using XML Task Control in SSIS,
I am stuck with problem saying....
"[XML Task] Error: An error occurred with the following error message: "Expression must evaluate to a node-set.".
I am trying to perform XPath operation type and accumulating values in a variable.
I have no clule about error if u can help to come around the problem.
Hi,
I'm trying to set the DestinationDatabaseFiles property programmatically through an expression. I keep getting the error:
SSIS package "Package1.dtsx" starting.
SSIS breakpoint 'Break when the container receives the OnPreExecute event' at executable 'Transfer Database Task' has been hit
Error: 0xC002929C at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the filename.
Error: 0xC002929D at Transfer Database Task, Transfer Database Task: The transfer file information "","","" is missing the folder part.
Error: 0xC0024107 at Transfer Database Task: There were errors during task validation.
Warning: 0x80019002 at Package1: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
SSIS package "Package1.dtsx" finished: Failure.
I'm setting the property to a variable, copyDestFiles, which I've tried numerous ways to set, the latest being in a script.
Dts.Variables("User::copyDestFiles").Value = _
"""" + stageDataName + """,""D:Program FilesMicrosoft SQL ServerData"","""";""" + _
stageLogName + """ ,""D:Program FilesMicrosoft SQL ServerData"","""""
When I view the variable at runtime it looks like this: "Prod2.mdf","D:\Program Files\Microsoft SQL Server\Data","";"Prod_log2.ldf" ,"D:\Program Files\Microsoft SQL Server\Data",""
How does one set this property using an expression?
Thanks, Nathan Vollmer
An error has occurred during report processing.
Cannot set the command text for data set 'ScoreboardOLAP'.
Error during processing of the CommandText expression of dataset €˜ScoreboardOLAP€™
Can anyone help at all here? I've seen about 10 articles on this one but none appear to be relevant. I have a complex report comprising many sub-reports which runs successfully in a development environment. When deployed to an environment which comprises a separate report sever and report server DB I get the above error even when I try and browse to any of the sub-reports.
The sub-report is using an OLEDB connection to an SSAS DB and its command text is set as:-
=Code.GetQueryString(parameters)
Where GetQueryString is a function in the code section of the report which returns some MDX based on the supplied parameters. I obviously know the function works because it works in development mode.
I have tried to determine what is going on from the logs but the only messages I get are those above. I've set the data sources on the server up to use valid Windows Credentials stored on the server so I don't believe the issue is one of authentication
Any thoughts or tips in helping to diagnose the cause of the problem would be greatly appreciated.
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()))
I can export to all other formats from the report viewer with no issues, but when I try to render to Excel, I get an exception:
[Exception: Object reference not set to an instance of an object.]
[Exception: An error occurred during rendering of the report.]
[Exception: An error occurred during rendering of the report.]
Microsoft.Reporting.WebForms.ServerReport.ServerUrlRequest(Boolean isAbortable, String url, Stream outputStream, String& mimeType, String& fileNameExtension) +553
Microsoft.Reporting.WebForms.ServerReport.InternalRender(Boolean isAbortable, String format, String deviceInfo, NameValueCollection urlAccessParameters, Stream reportStream, String& mimeType, String& fileNameExtension) +941
Microsoft.Reporting.WebForms.ServerReport.Render(String format, String deviceInfo, NameValueCollection urlAccessParameters, String& mimeType, String& fileNameExtension) +97
Microsoft.Reporting.WebForms.ServerReportControlSource.RenderReport(String format, String deviceInfo, NameValueCollection additionalParams, String& mimeType, String& fileExtension) +126
Microsoft.Reporting.WebForms.ExportOperation.PerformOperation(NameValueCollection urlQuery, HttpResponse response) +153
Microsoft.Reporting.WebForms.HttpHandler.ProcessRequest(HttpContext context) +202
System.Web.CallHandlerExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +154
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
I've pinned it down to a footer cell that uses an expression to determine the TextAlign property. If I do something as simple as ="Right", I get the error. Of course, if I just use Right, there is no problem. So are expressions not supported in footer cell properties or is there possibly some fix for this?
Thanks.
I have been trying to get the following Selects to work using a case expression in the order by section.
I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.
If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.
Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.
Thanks
J
declare @date
set @date = '2001'
select internalID from section_data_v3
union
select internalID from section_data_v4
order by
case when (@date = '2001') then internalID end desc
Hi
We have set up an SSIS package which goes to an FTP site and downloads files.
Everything is fine... EXCEPT (lol) when there are no files to download. This then fails the task.
However, I want the package to continue to run.
Is there away of assigning the error message given to an expression and then using the expression in the precedence contraint?
thanking you in advance
David
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)
Hi, Please could someone assist - the above error occurs. This is my code: Protected Sub btnReport_Click(ByVal sender As Object, ByVal e As System.EventArgs) ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ddlCompany.SelectedValue) Dim myConnection As New OleDbConnection(connString) Dim Str As String = "SELECT clientid,company FROM Client WHERE company =" & ddlCompany.SelectedItem.Text Dim cmd As New OleDbCommand(Str, myConnection) Dim ds As New DataSet Dim da As New OleDbDataAdapter(cmd) da.Fill(ds) Label7.Text = ds.Tables(0).Rows(0).Item("clientid") ' Response.Redirect("CrystalreportTEST_Print.aspx?C_id=" & ds.Tables(0).Rows(0).Item("clientid")) End Sub Thank you in advance
View 1 Replies View RelatedI'm getting the error listed above. To create this I used the wizard in VS2005 for the datagrid. The delete works but not the updating. I can't seem to find what's wrong. In the update parameters I've removed eventID and added it, neither works. Delete does work. Here's the code:
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataSourceID="ds1" DataKeyNames="eventID"><asp:CommandField ButtonType="Button" ShowEditButton="True" ShowDeleteButton="True" /><asp:BoundField DataField="eventID" HeaderText="ID" SortExpression="eventID" ReadOnly="True" /><asp:BoundField DataField="trainer" HeaderText="trainer" SortExpression="trainer" /><asp:BoundField DataField="employeeNumber" HeaderText="Emp#" SortExpression="employeeNumber" /><asp:BoundField DataField="area" HeaderText="Area" SortExpression="area" /><asp:TemplateField HeaderText="Training Date" SortExpression="trainingDate"><EditItemTemplate><asp:TextBox ID="TextBox1" runat="server" Text='<%# Eval("trainingDate", "{0:M/dd/yy}") %>'></asp:TextBox></EditItemTemplate><ItemTemplate><asp:Label ID="Label2" runat="server" Text='<%# Bind("trainingDate", "{0:M/dd/yy}") %>'></asp:Label></ItemTemplate></asp:TemplateField><asp:CheckBoxField DataField="additionalTrainerExpected" HeaderText="Addl Trainer Expected" SortExpression="additionalTrainerExpected" ><asp:TemplateField HeaderText="Ext Trainer" SortExpression="extendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox1" runat="server" Checked='<%# Bind("extendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle Font-Bold="False" HorizontalAlign="Center" /></asp:TemplateField><asp:TemplateField HeaderText="NonExt Trainer" SortExpression="nonextendedTrainer"><EditItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' /></EditItemTemplate><ItemTemplate><asp:CheckBox ID="CheckBox2" runat="server" Checked='<%# Bind("nonextendedTrainer") %>' Enabled="false" /></ItemTemplate><HeaderStyle Font-Names="Verdana" Font-Size="Small" /><ItemStyle HorizontalAlign="Center" /></asp:TemplateField><asp:SqlDataSource ID="ds1" runat="server" ConnectionString="<%$ ConnectionStrings:TrainingClassTrackingConnectionString %>"ProviderName="<%$ ConnectionStrings:TrainingClassTrackingConnectionString.ProviderName %>"
UpdateCommand="UPDATE [trainingLog] SET [trainer] = ?, [employeeNumber] = ?, [additionalTrainerExpected] = ?, [extendedTrainer] = ?, [nonextendedTrainer] = ?, [area] = ?, [trainingDate] = ? FROM [trainingLog] WHERE [eventID] = ?">
<UpdateParameters><asp:Parameter Name="eventID" Type="Int32" /><asp:Parameter Name="trainer" Type="String" /><asp:Parameter Name="employeeNumber" Type="String" /><asp:Parameter Name="additionalTrainerExpected" Type="Boolean" /><asp:Parameter Name="extendedTrainer" Type="Boolean" /><asp:Parameter Name="nonextendedTrainer" Type="Boolean" /><asp:Parameter Name="area" Type="String" /><asp:Parameter Name="trainingDate" Type="DateTime" /></UpdateParameters>
<DeleteParameters><asp:Parameter Name="eventID" Type="Int32" /></DeleteParameters>
Hi,
When i try to save my stored procedure.. i am getting the above error and this is my sproc
1 INSERT INTO Statement..ClientSources
2 (
3 ClientId,
4 ClientSourceId,
5 SourceName
6 )
7 Select Distinct
8 @ClientId,
9 SOURCE_NUM,
10 (Select CASE s.SOURCE_NUMWhen 1 Then SRC1NAME
11 WHEN 2 Then SRC2NAME
12 WHEN 3 THEN SRC3NAME
13 WHEN 4 THEN SRC4NAME
14 WHEN 5 THEN SRC5NAME
15 WHEN 6 THEN SRC6NAME
16 WHEN 7 THEN SRC7NAME
17 WHEN 8 THEN SRC8NAME
18 WHEN 9 THEN SRC9NAME
19 WHEN 10 THEN SRC10NAME
20 WHEN 11 THEN SRC11NAME
21 WHEN 12 THEN SRC12NAME
22 WHEN 13 THEN SRC13NAME
23 WHEN 14 THEN SRC14NAME
24 WHEN 15 THEN SRC15NAME
25 END
26 FROM
27 PlanDBF p
28 Where
29 p.PLAN_NUM = s.PLAN_NUM
30 ) as SourceName
31 FROM
32 SourceDBF s
33 Where
34 SOURCE_NUM NOT IN (
35 SELECT DISTINCT
36 ClientSourceId
37 --SourceName
38 FROM
39 Statement..ClientSources
40 Where
41 ClientId = @ClientId
42 )
I am getting the error in Line number 35 .. the inserts works fine... and if use * instead of the field name or use more than 1 field name i get this error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Any help will be appreciated.
Regards
Karen
l'm running this procedure and l get this error. All l'm trying to do is to get the size of the database and its objects and what the size should be so that its sized right. Is there a better way of doing this ?
CREATE PROCEDURE sp_totalsize as
SELECT o.name 'Table', SUM(c.length) 'Record size',
MAX (i.rows) '#of rows',
CONVERT (decimal (10, 4), SUM (c.length * i.rows)/(1024.00 * 1024.00)) 'Approx. size (MB)'
FROM sysobjects o, syscolumns c, sysindexes i
WHERE o.id = c.id
AND o.id = i.id
AND (i.indid = 1 or i.indid = 0)
AND o.type = 'U'
GROUP BY o.name
COMPUTE SUM (CONVERT (decimal (10,4), SUM (c.length * i.rows)/(1024.00 * 1024.00)))
GO
(17 row(s) affected)
Server: Msg 8115, Level 16, State 2, Procedure sp_totalsize, Line 3
Arithmetic overflow error converting expression to data type int.