QP Is Missing And It Is Required To Evaluate Default Expressions?
Sep 17, 2007
Hi everyone
I have a really odd problem that has manifested its self. I have a Windows Mobile 6 device that I debugging my application on, the application uses a local Sql Compact sdf file, if I just run the app, when it comes to executing some ado.net against the database I get the following error:-
QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)
If however I open the the database using query analyzer first on the device then everything is ok until I next soft reset the device
Has anyone else come across this?
Thanks in advance
Dan
View 8 Replies
ADVERTISEMENT
Oct 9, 2007
Hi,
I am getting an error when i am trying to insert some value to the sql ce database
"QP is missing and it is required to evaluate default expressions. Ensure that sqlceqp30.dll is in the same directory as the storage engine (sqlcese30.dll)"
Please help
Thanks
Nebu
View 1 Replies
View Related
Oct 26, 2007
All,
I have seen it happen frequently that I type in a perfectly valid SSIS expression (this is easy for me since I am an old hand at C++/C/C#) in a row in a Derived Column transformation, and it turns red. Or sometimes, I will have an invalid expression that I correct, but it stays red. Finally I have also seen it happen that I make some change in the data flow pipeline and suddenly a Derived Column transform develops an error. I then go into the Derived Column transform and find that the expression has turned red. So, I literally have to go into the expression in these cases, and make a trivial change to them to get the red error to go away. Alternatively, I can cut the derived column expression text, and then paste it back in and it works (this is most telling.)
So, it seems to me the Derived Column is somehow holding onto some meta data about the Derived Column that is getting out of date (rather than re-evaluating the correctness of the Expression.) One thing I usually can do to repro this at times is to remove a column (that the Derived Column depends upon) from the pipeline and then re-add it. When I go into the Derived Column it will be red, and then like I said I have to tweak the expression to force SSIS to re-evaluate the expression.
Anyway, I'm curious if anyone else has seen this?
Thanks.
View 10 Replies
View Related
Dec 7, 2006
Hello all,
I am new to SSIS, so I am hoping there is an easier way to do this...
I need to evaluate a date in a field and determine if it is between the beginning and end of whatever the current month is... In Access, this was written as something like:
IIF(datevalue >= CDate(Format(Now(),"mm/01/yy")) AND datevalue < CDate(Format(DateAdd("m",1,Now()), "mm/01/yy)), value1, value2)
Trying to recreate this in SSIS using expressions during a derived transformation has been extremely difficult. Here is what I came up with:
(DUE_DATE >= (DT_DATE)( (DT_WSTR,2)MONTH(GETDATE())+"/01/"+ (DT_WSTR,2)YEAR(GETDATE()))) && (DUE_DATE<(DT_DATE)( (DT_WSTR,2)MONTH( DATEADD("m",1,GETDATE()) )+"/01/"+(DT_WSTR,2)YEAR( DATEADD("m",1,GETDATE() )))) ? value1 : value2
Any help you all could give would be appreciated.
Thanks!
Josh
View 7 Replies
View Related
Jan 24, 2008
Trying to setup a derived column to use an expression stored in a package variable. But it seems that variables are always evaluated as text...I need it to evaluate as a Columname sometimes.
Example:
On an ETL of Products I want a new derived column that uses two other columns.
I can hardcode an expression of ProductID + " - " + ProductName and that results in dynamic output. But now I want to use that as an expression stored in a variable so I can change it when needed.
So I make that expression = @[User::Variable]
and stuff into @variable ( a string param) : ProductID + " - " + ProductName
My output is the literal "ProductID + " - " + ProductName", and not the actual ID's and Names
I've tried with/without brackets, quotes and braces but no change.
Any way I can get the pieces in that variable expression to evaluate as column names?
View 6 Replies
View Related
Jun 17, 2015
Is it possible to force a build to fail when a stored procedure in a project calls another stored procedure with one or more required parameters missing. E.g.:
Â
CREATE PROCEDURE [App].[ServiceUser_Save]
@userID int-- Param #0
,@serviceuserID int-- Param #1
,@version int-- Param #2 etc...
And then in a separate stored procedure we have the following
CREATE PROCEDURE [Admin].[CreateMiscellaneousData]
@customerIDint,
@serviceIDint,
@fullURL nvarchar(255),
@apiUserPwd nvarchar(255)
AS
BEGIN
...
EXEC@return_value = [App].[ServiceUser_Save]
@userID = 1,
@serviceuserID = 0, etc...
Note there is no value passed for the @Version parameter.
What I want is the build to fail because of the missing parameter on the call to ServiceUser_Save in the Create_MiscellaneousData stored procedure.
How can I achieve this?
View 4 Replies
View Related
Apr 25, 2007
I have a server with 3 SQL Server named instances on it - servername, servername/TST and servername/GOLDSTD. I have several databases on the default instance (servername) but users cannot access the databases unless they are given system admin privileges to their login. This occurs using both SQL Server Logins and Windows authentication logins. If I move the database to a different named instance (servername/TST), the problem no longer exists and users can connect with dbo privileges. Any suggestions or is this typical for default instances? Thanks in advance for any help
View 8 Replies
View Related
Feb 8, 2007
Ok, so I must have screwed something up.
I have several databases set up for transactional replication to another instance of SQL Server 2005 for fail over purposes. Today, I restored one of those replicated databases to my development machine and discovered two surprising problems:
1) The Default Values settings in the replicated tables are missing. They are there in the publishing tables, just as they were before I set up replication. However, they are not in the subscribing tables. Now, this is not such a big issue, since I tend to send all default values in insert queries as necessary.
2) The second problem is a more of an issue, since I use auto-numbered Identity columns in my tables (yes, I know that's just plain lazy...). Anyway, in the replicated tables, €œIs Identity€? is indeed set to yes, but despite that fact that there are thousands of records with incrementally unique IDs, SQL server is trying to insert a record starting with 1. This, of course, throws a PK constraint error.
Obviously, if I am use them for failover purposes, these replicated databases need to be identical in every way.
So, what did I do to cause this situation, and how to I fix it?
Thanks a bunch!
md
View 9 Replies
View Related
Dec 11, 2007
I want to have this query insert a bunch of XML but i get this error...
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 117
The name "ExpenseRptID" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Msg 128, Level 15, State 1, Procedure InsertTimeCard, Line 151
The name "DateWorked" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
What am i doing wrong...Can anyone help me out!! Thanks!!
p.s I know this query looks crazy...
Code Block
IF EXISTS (SELECT NAME FROM sysobjects WHERE NAME = 'InsertTimeCard' AND type = 'P' AND uid=(Select uid from sysusers where name=current_user))
BEGIN
DROP PROCEDURE InsertTimeCard
END
go
/*********************************************************************************************************
** PROC NAME : InsertTimeCardHoursWorked
**
** AUTHOR : Demetrius Powers
**
** TODO/ISSUES
** ------------------------------------------------------------------------------------
**
**
** MODIFICATIONS
** ------------------------------------------------------------------------------------
** Name Date Comment
** ------------------------------------------------------------------------------------
** Powers 12/11/2007 -Initial Creation
*********************************************************************************************************/
CREATE PROCEDURE InsertTimeCard
@DateCreated DateTime,
@EmployeeID int,
@DateEntered DateTime,
@SerializedXML text,
@Result int output
as
declare @NewTimeCardID int
select @NewTimeCardID = max(TimeCardID) from OPS_TimeCards
-- proc settings
SET NOCOUNT ON
-- local variables
DECLARE @intDoc int
DECLARE @bolOpen bit
SET @bolOpen = 0
--Prepare the XML document to be loaded
EXEC sp_xml_preparedocument @intDoc OUTPUT, @SerializedXML
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--The document was prepared so set the boolean indicator so we know to close it if an error occurs.
SET @bolOpen = 1
--Create temp variable to store values inthe XML document
DECLARE @tempXMLTimeCardExpense TABLE
(
TimeCardExpenseID int not null identity(1,1),
TimeCardID int,
ExpenseRptID int,
ExpenseDate datetime,
ProjectID int,
ExpenseDescription nvarchar(510),
ExpenseAmount money,
ExpenseCodeID int,
AttachedRct bit,
SubmittoExpRep bit
)
DECLARE @tempXMLTimeCardWorked TABLE
(
TimeCardDetailID int not null identity(1,1),
TimeCardID int,
DateWorked DateTime,
ProjectID int,
WorkDescription nvarchar(510),
BillableHours float,
BillingRate money,
WorkCodeID int,
Location nvarchar(50)
)
-- begin trans
BEGIN TRANSACTION
insert OPS_TimeCards(NewTimeCardID, DateCreated, EmployeeID, DateEntered, Paid)
values (@NewTimeCardID, @DateCreated, @EmployeeID, @DateEntered, 0)
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
--Now use @intDoc with XPATH style queries on the XML
INSERT @tempXMLTimeCardExpense (TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
SELECT @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
FROM OPENXML(@intDoc, '/ArrayOfTimeCardExpense/TimeCardExpense', 2)
WITH ( ExpenseRptID int 'ExpenseRptID',
ExpenseDate datetime 'ExpenseDate',
ProjectID int 'ProjectID',
ExpenseDescription nvarchar(510) 'ExpenseDescription',
ExpenseAmount money 'ExpenseAmount',
ExpenseCodeID int 'ExpenseCodeID',
AttachedRct bit 'AttachedRct',
SubmittoExpRep bit 'SubmittoExpRep')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardExpenses(TimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
Values(@NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep)
select @NewTimeCardID, ExpenseRptID, ExpenseDate, ProjectID, ExpenseDescription, ExpenseAmount, ExpenseCodeID, AttachedRct, SubmittoExpRep
from @tempXMLTimeCardExpense
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- For time worked...
INSERT @tempXMLTimeCardWorked(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
SELECT @NewTimeCardID, DateWorked, ProjectID, WorkDescription, BilliableHours, BillingRate, WorkCodeID, Location
FROM OPENXML(@intDoc, '/ArrayOfTimeCardWorked/TimeCardWorked', 2)
WITH ( DateWorked DateTime 'DateWorked',
ProjectID datetime 'ProjectID',
WorkDescription nvarchar(max) 'WorkDescription',
BilliableHours float 'BilliableHours',
BillingRate money 'BillingRate',
WorkCodeID int 'WorkCodeID',
Location nvarchar(50)'Location')
-- check for error
IF @@ERROR <> 0
GOTO ErrorHandler
-- remove XML doc from memory
EXEC sp_xml_removedocument @intDoc
SET @bolOpen = 0
INSERT OPS_TimeCardHours(TimeCardID, DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
Values(@NewTimeCardID,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location)
select @NewTimeCardID ,DateWorked, ProjectID, WorkDescription, BillableHours, BillingRate, WorkCodeID, Location
from @tempXMLTimeCardWorked
-- commit transaction, and exit
COMMIT TRANSACTION
set @Result = @NewTimeCardID
RETURN 0
-- Error Handler
ErrorHandler:
-- see if transaction is open
IF @@TRANCOUNT > 0
BEGIN
-- rollback tran
ROLLBACK TRANSACTION
END
-- set failure values
SET @Result = -1
RETURN -1
go
View 1 Replies
View Related
Nov 8, 2007
Here is the issue I'm encountering.
Steps to recreate:
Start Business Intelligence Stuido
Select New Project
Select Integration Services Project from Templates
Project opens with default package.dtsx
Open Toolbox but I am missing all but a few of my items
Only items available are MAINTENANCE PLAN TASKS and GENERAL(which is totally empty)
I've changed the view from List View to Show All but which will display all the groups but all the options in those groups are grayed out.
I'm running on Windows Vista Business edition with SQL server 2005(9.0.3054) with SSIS installed. If someone could help me figure out why I don't have any avaible options it would be greatly appreciated.
View 6 Replies
View Related
Jul 9, 2015
I am currently hardening our SQL 2012 (with AlwaysOn Availability Groups) environment. Both the SQL service and agent account are using service accounts (only domain user). SQL browser service is disabled. Permissions to all roles are handled by using domain groups.
Currently a lot of (default) NT Service accounts are listed (some with sysadmin privileges). Are there accounts that can be removed?
View 3 Replies
View Related
Jan 8, 2007
I am having trouble getting an
View 6 Replies
View Related
Apr 17, 2008
I havea flat file source.All of the coulmns in the source file are string of 5o length( default). There going to be couple of numeric columns like beginning balance, end balance etc. Now I have to evaluate those column to see if there is any column has more then two digit after decimal point. I have to do it through vb.net scripting in SSIS.
Can some one please give me some suggestion how can I achieve it?
Thanks.
View 3 Replies
View Related
Aug 9, 2006
Hi,
I have a table which has expression as a column and the data looks similar to the following .
ID (int) Expression (nvarchar data type)
1 8*(1/2)-6
2 278*(1/4)-2
3 81*(3/5) +4
I now have the expression as an nvarchar. (Ex: 8*(1/2)-6)
Now I need to evaluate this expression and output the result (which is -2 in above Ex) .Can someone give me idea on how do I evaluate the expression which is available as a string. I need to write a procedure for this.
I am unable to cast/convert nvarchar to float/int
Any sample code would be greatly appreciated.
This a very urgent requirement for me.Please get back
Thanks
Swapna
View 8 Replies
View Related
Jun 13, 2006
I would like to evaluate expressions from within my execute function in a custom task. I saw this post from about 8 months ago detailing how it would be possible:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=117564&SiteID=1
Is there an updated response (or more detailed example) to the question? Am I really risking a lot by using this undocumented feature?
Thanks!
View 3 Replies
View Related
Apr 30, 2001
We want to start a job remotely on another SQL7 server.
innocently we thought that should be no problem with sp_start_job.
We used the command listed below:
use msdb
go
EXEC sp_start_job @job_name='MyJobName', @server_name='blablabla'
The strange thing is, that sp_start_job ignores the @server_name at all.
Even if we enter complete nonsense for @server_name the job gets started as long it is present on the local sqlserver instance.
sp_start_job seems to search locally for the given job_name, only.
Consequently if we enter the remote server's name the job fails as the
jobname is not present on the local sqlserver.
Any ideas what's going wrong here.
Greetings from Mannheim, Germany
Ricardo
View 2 Replies
View Related
Oct 19, 2013
I am trying to evaluate dates within my sub queries for A,B,C,D. If there is a count > 0 in the 'A' category, I need to evaluate if the servicedates in B,C,D are within 30 days of 'A'. How would I code this within my current code?
select
customernumber,
SUM(CASE WHEN category = 'A' then 1 else 0 end) 'A',
SUM(CASE WHEN category = 'B' then 1 else 0 end) 'B',
SUM(CASE WHEN category = 'C' then 1 else 0 end) 'C',
SUM(CASE WHEN category = 'D' then 1 else 0 end) 'D'
[Code] ....
My BEFORE the case statement:
Customernumber A B C D SERVICEDATE
123456789 1 0 0 0 2012-02-07
123456789 0 0 1 0 2012-02-28
123456789 0 0 1 0 2012-03-06
123456789 0 1 0 0 2012-12-17
My output after case statement:
Customernumber A B C D
123456789 1 1 2 0
What I need my output to look like after case statement:
Customernumber A B C D Service w/in 3 mnths
123456789 1 1 2 0 2
View 1 Replies
View Related
Jun 18, 2007
In 'MyTable' I have the following columns...
TotalNumber (numeric)
Weighting (numreric)
Hours (numeric)
Minutes (numeric)
Formula (nvarchar)
'Formula' column stores a literal string of the formula that may include some, none or all of the previous columns or be NULL.
Here are some examples of the actual strings it stores...
Weighting*Hours
Weighting+(TotalNumber*Hours)
Weighting*Hours)+(TotalNumber*2)
etc etc
All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.
Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.
I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.
Here is the vb version i use in Ms Access...
getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)
If IsNull(strFormula) Or strFormula = "" Then
getFormula = dblHours
Exit Function
End If
'Create the expression string with literal values
strExpression = Replace(strFormula, "TotalNumber", _
CStr(dblTotalNumber), , , vbTextCompare) _
strExpression = Replace(strExpression, "Weighting", _
CStr(dblPF), , ,vbTextCompare)
strExpression = Replace(strExpression, "Hours", _
CStr(dblHours), , , vbTextCompare)
strExpression = Replace(strExpression, "Minutes", _
CStr(dblMinutes), , , vbTextCompare)
'Evaluate the math of the literal expression
getFormula = Eval(strExpression)
-----------
Many Thanks
View 1 Replies
View Related
Sep 8, 2006
I've been tasked with taking over the support for a client's SQL Serverdatabase. I'm not a DBA by profession, I'm a software developer whouses SQL Server as a database designer.The clients have reported that the server is running out of disk spaceand examination shows that the log files for several of the databasesare at 5Gb or more.After reading around the subject I suggested the following sequence ofoperations:-- Select the name of the database you want to shrinkUSE MyDB-- Dump unwanted transactionsdump tran MyDB with truncate_only-- Get the name of the logfileSELECT * from sysfiles-- Having examined the rows returned by this use the log file....-- Shrink the file to required size (in MB)DBCC SHRINKFILE('MyDB_log', 10)Is this a reasonable approach? Please bear in mind that I'm pretty newto this, and I have many other tasks to do besides manage the server.A previous DBA has set up good maintenance plans etc. so everything isbeing properly backed up (well, I think it is)If this IS a good approach, would it be reasonable to do this on, say,a monthly basis as a scheduled job? Obviously the stepSELECT * from sysfileswhich gives us the physical name of the log file would be removed andthe job would operate explicitly on each log file for each database inturn.Many thanks for reading.William Balmer.
View 2 Replies
View Related
Dec 12, 2007
Is there a way to evaluate an expression (like the derived column component) in a custom component? If so where should I look first? Is there an example?
An extremely simple sample is to put in an expression and evaluate one column and then add that to another column to create a new column. i.e. newcolumn = column1 + column2.
I realize that the derived column allows me to do this but I'm trying to figure out if it is possible to do this in a custom component without having to build my own expression evaluator.
Thanks!
-Thames
View 5 Replies
View Related
Apr 11, 2008
Hi all,
I'm wondering if anyone's accomplished this before - I've been unable to find a whiff of info on how to do this so far.
I'm creating a custom component that I'd like to give a "Derived Column" type of ability to. By that, I mean I'd like to populate a property of my component with an expression (including references to input columns, package variables and functions) and be able to evaluate it at runtime - per row processed by the component.
I would also appreciate any information as to how to provide the interface to allow the user to build such an expression as well - is there a UI function in SSIS I can call to pop up the "expression builder"?
Thanks!
View 6 Replies
View Related
Sep 4, 2007
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.
View 1 Replies
View Related
Oct 24, 2015
playing with the Power pivot , DAX. While analyzing the DAX ,I came across a function EVALUATE , but when I tried this function in excel Power Pivot workbook - =EVALUATE 'Date' where 'Date' is my one of the Power pivot table , I was writing this function within the Calculation area of the Power Pivot model. I get the below error when I hit enter after writing the function ."The expression is not valid or appears to be incomplete..An MDX expression was expected while a full statement was specified."But in many forums I find the syntax is correct.
View 4 Replies
View Related
May 12, 2006
I have a script component which loads a file which is in a custom format. The script component is inside a For Each Loop Container and it uses a flat file connection manager. The loop sets the connection string for the connection manager.
The problem I'm having is that the connection string needs to be set to something every time that I start the package but I don't know ahead of time what file there will be, so I get a System.IO.FileNotFoundException error on the script component. If I manually set the variable for the connection string and point it to a file that exists, then the package runs fine but at the end of the package the connection string is set to the last file loaded and this file will no longer exist the next time the package runs.
I hope this makes sense...
View 1 Replies
View Related
Sep 17, 2015
I have an ssrs (report builder) with 2 dataset. the first dataset is a summary if records which the report has a column name qty and i put also a total qty summary in the last rows. the second dataset is a raw data and have a column name qty, also i put a total qty summary in the last row. The requirements is to be able to evaluate or check the total qty under dataset1 from total qty of dataset2 if equal else if not equal i have to make the font as red so that the user will inform that the total qty has a discrepancy. the users will validate from raw data which are the one items that have a missing qty. How to work on this or is this appilcable in report builder.
View 4 Replies
View Related
Apr 10, 2008
Hi!
On a Reporting Services 2005 report, I have a table with a single group row and a single detail row (and about 5 columns). The details are set up to toggle visibility based on a textbox in the group header called "txt_Order_Number". The order line item details are set with their initial visibility set as hidden. So, when you click on the Plus sign next to the Order Number, the detail rows display below.
However, what I'd like to do is change the background color of the toggled group header row when it is toggled and the details are displayed. Here's what's going on . . .
The row with the Order header information has every textbox set in bold. That's good. It makes a subtle distinction between the order detail rows and the order header row, so when you're looking at the dollar amounts on each line item and trying to add them up in your head to see whether they correctly add up to the dollar amount in the order header row, you can at least more easily see the total amount. However, it makes the whole overall report look funny because all of the order header information is in bold. What I really want to do is change the background color of the order header row to light gray or some other subtle color and change the font to Bold when the details are toggled to visible in order to distinguish that row as the header row.
In order to do this, I think I somehow need to get to a certain element of the Reporting Services object model called "Textbox.ToggleState". If I can get to that piece of the puzzle in an expression or even in code within the report, that should tell me the state of the textbox's toggle switch and I should be able to use that information to conditionally set the color of the textbox.(or every textbox in the row probably.) Here's all I could find out about this on TechNet
http://technet.microsoft.com/en-us/library/microsoft.reportingservices.reportrendering.textbox.togglestate.aspx
I just can't figure out how to get to that piece of information in a Reporting Services expression or in the report's code block.
Any suggestions? I'd appreciate a response even if it's "You can't do that." Actually, the best response someone might be able to provide might be a link to a sample showing how to get to any of the textbox properties like that.
Thanks!
Karen
View 13 Replies
View Related
Apr 11, 2008
how to evaluate working day through script component in SSIS
I have a data source. I need to verify if the date belongs to working day, if it does belongs to holiday or weekend ,then I need to send that row to the out put table. I think the only way I can verify this working day issue through script component. Can any one give me some idea/thoughts?
You all have a wonderful weekend.
Thanks
View 17 Replies
View Related
Sep 13, 2006
I am working on a company sign out sheet for our Intranet. The application accesses a sql database. I tried using a datareader as well as a dataset to access a small piece of information regarding the group that each employee belongs to in the company. My last attempt was to assign the value to the text property of a text box and evaluate the text from the text box with the Select Case but that did not work either.The application is supposed to generate an email to our receptionist as well as to the group technical assistant responsible for each group according to the value that is passed into the Select Case statement. With the datareader as well as the dataset and even now with creating a text box and accessing the text property, I was able to assign the group value to a variable (I can see the value in the subject of the email), but when the variable is supposed to be evaluated by the select case statement it skips right through all of the cases to case else and uses only the receptionist's address in the email as if it doesn't even see the value of the variable. I have searched for a possible answer to the problem I am but so far have had no luck. Any ideas? I included the part of the code that I am having trouble with: '********************************************************************************************' New DataSet is created to determine the group the employee belongs to so that the appropriate' group technical assistant is emailed when employee signs out'******************************************************************************************** ' declare variablesDim sqlGroup As String ' string variable to store sql statement for datasetDim BLGroup As String ' string variable to store the bl group of the employeeDim emailAddress As String ' string variable to store resulting email address Dim groupDS As DataSet ' dataset variableDim groupAdapter As SqlDataAdapter ' sql data adapter variable' sql statement to access group value from the databasesqlGroup = "SELECT BLGroup FROM BLGroupsView WHERE (RTRIM(fname) + ' ' + LTRIM(lname)='" & employee & "')" ' repopen the connection (leftover from working with the datareader)Connection.Open()' create new SqlDataAdapter applying sql statement and connectiongroupAdapter = New SqlDataAdapter(sqlGroup, Connection)' create new dataset groupDS = New DataSet()' populate the datasetgroupAdapter.Fill(groupDS, "BLGroupsView") ' get the value stored in the BLGroup column (only one row is returned at a time) BLGroup = groupDS!BLGroup' Create GroupTextBox TextBox control.Dim GroupTextBox As New TextBox()' Set options for the UserTextBox TextBox control.GroupTextBox.ID = "GroupTextBox"GroupTextBox.Visible = "False"GroupTextBox.Text = BLGroup' just trying anything with this next line, even when i didn't set this the select case statement did not seem to see the value returned by the variableGroupTextBox.runat = "server" ' use a select case statement to evaluate the value of the group (didn't work when I passed in the BLGroup variable eitherSelect Case GroupTextBox.TextCase "bh"emailAddress = "receptionist@myaddress.com; bhassistant@myaddress.com"Case "env"emailAddress = "receptionist@myaddress.com; envassistant@myaddress.com"Case "sw"emailAddress = "receptionist@myaddress.com; swassistant@myaddress.com"Case "fac"emailAddress = "receptionist@myaddress.com; facassistant@myaddress.com"Case "www"emailAddress = "receptionist@myaddress.com; wwwassistant@myaddress.com"Case ElseemailAddress = "receptionist@myaddress.com"End Select '*************************************************************************************************' set new message objectDim myMessage As System.Web.Mail.MailMessage = New System.Web.Mail.MailMessage()'**********************************************************' set message properties'********************************************************** myMessage.From = "me@myaddress.com"myMessage.To = emailAddressmyMessage.Subject = employee & " Signing Out at " & TimeOut.Text & " " & BLGroupmyMessage.Body = employee & " Signing Out at " & TimeOut.Text & " for " & Destination.Text & " will potentially return at " & EstTimeIn.Text'***********************************************************' set smtp server namesmtpMail.SmtpServer = "mailserver1"' send email using smtpsmtpMail.Send(myMessage) It sends the message and returns the BLGroup value everytime but only sends to the receptionist. Thanks for your time!
View 1 Replies
View Related
Nov 25, 2005
have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.
For a very general example: In the .NET code, the following variables are recognized:
sex="M"
Paid=4350.00
Outstanding=28000.50
One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..
EVAL EXPR
BOOL Sex='F' and Paid/Outstanding < 27.50
BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F'
INT Paid*52.33
In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?
If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....
View 3 Replies
View Related
Apr 28, 2008
This is for SQL Server 2000. The purpose of the procedure is to return a subset of a filtered and sorted result set. The subset, filter criteria, and sort column and sort direction can be set dynamically. It uses the rowcount technique for paging.
This would be used to drive an ASP.NET gridview which supports filtering of the data, paging, and sorting. Please let me know what improvements I can make or if you have an idea for a better solution. (I didn't put this in a vBulletin code block because personally I find two sets of scroll bars annoying, but I can if people think it's better).
CREATE PROCEDURE dbo.Books_GetFilteredSortedSubset
(
-- paging
@startRowIndex INT = 1,
@maximumRows INT = 999999,
-- sorting
@sortColumn NVARCHAR(30) = 'title_id',
@sortDirection NVARCHAR(4) = 'ASC',
-- filtering
@title VARCHAR(100) = NULL,
@type VARCHAR(30) = NULL,
@price MONEY = NULL
)
AS
BEGIN
DECLARE @sql NVARCHAR(4000)
DECLARE @parameters NVARCHAR(4000)
DECLARE @tableSource NVARCHAR(4000)
DECLARE @orderByExpression NVARCHAR(4000)
DECLARE @searchCondition NVARCHAR(4000)
DECLARE @uniqueKey NVARCHAR(30)
-- set the unique key used to ensure the rows are sorted deterministically
SET @uniqueKey = 'title_id'
-- build the FROM table source used throughout this procedure
SET @tableSource = 'titles t
inner join publishers p on t.pub_id = p.pub_id'
-- build the WHERE search condition used to control filtering throughout this procedure
SET @searchCondition = '(1 = 1)'
IF @title IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (title LIKE ''%' + @title + '%'')'
IF @type IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (type LIKE ''' + @type + '%'')'
IF @price IS NOT NULL
SET @searchCondition = @searchCondition + ' AND (price = ' + CAST(@price AS NVARCHAR) + ')'
-- build the ORDER BY expression used to control the sorting throughout this procedure
SET @orderByExpression = @sortColumn + ' ' + @sortDirection
-- add uniqeKey to ORDER BY statement to ensure consistent ordering of results when @sortColumn is not unique
IF @sortColumn <> @uniqueKey
SET @orderByExpression = @orderByExpression + ', ' + @uniqueKey + ' ' + @sortDirection
-- Get the column value at the position specified by @startRowIndex when the results are sorted in the desired sort order
SET @sql = 'SET ROWCOUNT @rowcount; SELECT @start_row = ' + @sortColumn + ', @start_row_id = ' + @uniqueKey +
' FROM ' + @tableSource +
' WHERE ' + @searchCondition + ' ORDER BY ' + @orderByExpression
PRINT @sql
SET @parameters = '@rowcount INT, @start_row sql_variant OUTPUT, @start_row_id sql_variant OUTPUT'
DECLARE @start_row sql_variant
DECLARE @start_row_id sql_variant
EXEC sp_executesql @sql, @parameters, @rowcount = @startRowIndex, @start_row = @start_row OUTPUT, @start_row_id = @start_row_id OUTPUT
-- Get the filtered subset of results
-- add sql to filter the results based on criteria passed in as parameters
SET @sql = 'SET ROWCOUNT @rowcount; ' +
'SELECT
t.title_id,
t.title,
t.price,
t.type,
p.pub_name,
p.city,
p.state,
p.country
FROM ' + @tableSource +
' WHERE (' + @searchCondition + ') AND '
-- add sql to control the starting row
IF @sortDirection = 'ASC'
SET @sql = @sql + '( (' + @sortColumn + ' > @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' >= @start_row_id) )'
ELSE
SET @sql = @sql + '( (' + @sortColumn + ' < @start_row) OR (' +
@sortColumn + ' = @start_row AND ' + @uniqueKey + ' <= @start_row_id) )'
-- add sql to control the ordering of everything
SET @sql = @sql + ' ORDER BY ' + @orderByExpression
PRINT @sql
SET @parameters = '@rowcount INT, @start_row sql_variant, @start_row_id sql_variant'
EXEC sp_executesql @sql, @parameters, @rowcount = @maximumRows, @start_row = @start_row, @start_row_id = @start_row_id
-- Reset the rowcount for others
SET ROWCOUNT 0
END;
GO
View 14 Replies
View Related
Feb 25, 2015
I want to evaluate a math formula inside of a function, so I can't use dynamic SQL.
I found that the query on an XML-variable with a literal works well.
DECLARE @sParsedFormula varchar(200);
DECLARE @xFormula xml;
SET @xFormula = '';
SET @sParsedFormula = '1+2';
SELECT @xFormula.query('3+3') , @xFormula.query('sql:variable("@sParsedFormula")')Output (1st value is correctly evaluated, 2nd not):
"6", "1+2"
I can't directly pass @sParsedFormula to the query otherwise I get "The argument 1 of the XML data type method "query" must be a string literal". What is the correct XQuery-syntax to evaluate the SQL:variable?
View 2 Replies
View Related
May 22, 2015
I am having trouble limiting a sum function to only evaluate the data in the slicer. I need a sum of data calculated per employee and when there is a slicer of one employee my measure still evaluates all employee. For example:
WITH
MEMBER [Measures].[TotalEmpHours] AS
SUM([Employee].[Employee].members, [Measures].[Actual Ovt Hours])
select
{
[Measures].[TotalEmpHours],
[Code] ....
The above reports the overtime for one employee in [Measures].[Actual Ovt Hours], I expected [Measures].
[TotalEmpHours] to be the same as there is only one employee in the slicer select. The real measure is the minimum of two values for each employee.
This is a simplified example but I think it should work.Is there another construct to iterate the employee set? I have no control over the query as it is from a pivot grid. If the filter (employee) is leftmost in the row area, then it works because the employee ends up in the row part of the query.
View 7 Replies
View Related
Mar 2, 2012
I'm trying to put conditional formatting on a field, that behaves as follows:
The data in the field is varchar, and sample data is either:
NULL
3.0 :0
11.7 :1 (these are ratios of a sort)
I want to evaluate the first 3 characters of the string as numbers.
Example:
Mid(fieldvalue,1,3) = "3.0" or "11."
Any data that is greater than 1.99, I want to make the background dark red, anything else including nulls, zebra formatting. I have the following expression built so far and it appears to work, except when the value is null. If the value is null, it leaves the background color white.
This is the warning: [rsRuntimeErrorInExpression] The BackgroundColor expression for the text box "Asthma" contains an error: Input string was not in a correct format.
=iif(
isnothing(Fields!Asthma.Value)
,(IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))
,(iif(mid(Fields!Asthma.Value,1,3)>1.99
,"DarkRed"
,IIf(RowNumber(Nothing) Mod 2 = 0,"#b8cce4","#dbe5f1"))))
My logic is, if the field is null, zebra format, if mid of the value is > 1.99, dark red, everything else zebra formatting. As I said, this seems to work except for nulls.
View 2 Replies
View Related
