Date Literals In Expressions?

Sep 12, 2007



How do I specify a date literal in an expresison? It's not covered in Books Online. None of the following worked:

mydate == '1899-12-30'
mydate == "1899-12-30"
mydate == #1899-12-30#

This did work:

mydate == (DT_DATE) 0

but it's not self-explanatory and it would be utterly stupid if that's the only way to specify a date literal. Are we once again victims of the "rushed-out-the-door" syndrome?

View 10 Replies


ADVERTISEMENT

Not Allowed Characters In SQL Literals

Feb 23, 2005

Hello,

I need to know what kind of characters are NOT allowed in SQL literals and the way(s) to go around it.

In example:

Code:

' must be replaced by ''



I would greatly appreciate all the help you guys can provide me with.

Thank you for taking the time.

View 2 Replies View Related

Date Conversion And Expressions

Aug 31, 2006

I have a table with about 20,000 records that have a date field, stored as a datetime in the database like '8/28/2006 8:42:14 AM'. The dates range from March 2004 to current. What I would like to do is retrieve the dates in that format (month year) and put them in a dropdown. I have this so far:SELECT DISTINCT DATEPART(month, dte_date) AS Expr1, DATEPART(yyyy, dte_date) AS Expr2 FROM myTable ORDER BY DATEPART(yyyy, dte_date), DATEPART(month, dte_date)And the query returns the information that I want, but I can only bind one field to the dropdown. I was thinking that if I return the results a single expression (concantenate?) then I could bind that to the dropdown. I'm not sure as how to go about this. Also, the month returned is numeric and I would rather have the name of the month returned (like "July" instead of "7"). Thanks in advance to anyone who helps me.

View 4 Replies View Related

Reporting Services :: SSRS Date Expressions

Sep 21, 2015

SSRS expressions for the following queries.

Last year last Week (15/9/2014)
Last Week ( 14/9/2015)
Before Last Week (7/9/2015)

View 2 Replies View Related

Is There Any Easy Way To Evaluate Complex Date Logic In Expressions?

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

Transact SQL :: GROUP Dynamically Generated Date And String Expressions

May 17, 2015

I have below SQL. When I run it I get the 'Each GROUP BY expression must contain at least one column that is not an outer reference' error. The date and string expressions are generated dynamically and need to be grouped upon if possible. What am I missing?

INSERT INTO tblStaffPayrollHistory (StaffID, FromDate, ToDate, PayrollNo, EventID)
SELECT DISTINCT tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID
FROM tblStaffBookings INNER JOIN tblEvents ON tblStaffBookings.EventID = tblEvents.ID
WHERE ...
GROUP BY tblStaffBookings.StaffID, CONVERT(DATETIME, '2015-05-17', 102), CONVERT(DATETIME, '2015-05-17', 102), 'tree', tblEvents.ID

View 2 Replies View Related

Valid Expressions Are Constants, Constant Expressions, And (in Some Contexts) Variables. Column Names Are Not Permitted.

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

Expressions

Jun 7, 2006



When creating expressions we have access to a list of functions. It is my understanding that while these functions seem to have the same names and parameters as SQL functions, they are not so. They are implemented in the package libraries themselves. It is also my understanding that this function library cannot be extended to add new ones.

Am I correct? If so... why not?

Leaving alone the fact that they follow the same screwy names as SQL (instead of .NET on which SSIS is built on) and what seems to be a limited library (i.e. You have YEAR(), MONTH(), DAY() functions but no HOUR(), MINUTE(), or SEC() functions -- instead you have to use DATEPART())

I mean honestly... a common expression for most people is using date and times for folder and filenames... So instead of a simple .NET type expression of DateTime.ToString("yyyyMMdd") or Format(DateTime.Now, "yyyyMMdd_hhmmss") I end up with the very complex:

(DT_STR, 4, 1252) YEAR( GETDATE() ) + RIGHT("0" + (DT_STR, 2, 1252) MONTH( GETDATE() ), 2) + RIGHT("0" + (DT_STR, 2, 1252) DAY( GETDATE() ), 2) + "_" + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("hour", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("minute", GetDate()), 2) + RIGHT("0" + (DT_STR, 2, 1252) DATEPART("second", GetDate()), 2)

Personally I find myself using Script Tasks and Variables to "build" my expressions and just use Expressions to set the property to the variable. (Which I think may defeat the full purpose of expressions.)

Any thoughts?

View 6 Replies View Related

Expressions In RS

May 25, 2007

How do I add comments to expressions I create in Reporting Services?

View 3 Replies View Related

Conditions, Expressions

Aug 7, 2007

I have a table
CREATE TABLE [dbo].[CmnLanguage]( [Id] [char](2) NOT NULL CONSTRAINT PkCmnLanguage_Id PRIMARY KEY, [EnglishName] [varchar](26) NOT NULL, [NativeName] [nvarchar](26) NOT NULL, [DirectionType] [smallint] NOT NULL, [IsVisible] [bit] NOT NULL, [CreatedDateTime] [datetime] NOT NULL DEFAULT GETDATE(), [ModifiedDateTime] [datetime] NULL)
We will use these 3 queries
select * from CmnLanguage where IsVisible = 0select * from CmnLanguage where IsVisible = 1select * from CmnLanguage
I want to make a method which handles these queries.
But at the back end on Stored Procedures
We have to write 3 queries
Which I don't want to do.
I want to minimize the queries and conditions
and want to just write one for these 3
Can any one do it?

View 2 Replies View Related

Subquery Expressions Help

Oct 31, 2007

Hello all,
I have come across a road block once again and it is probably something fairly easy to do. I have 3 queries that give counts with different criteria. They are:
 SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_scores = 'N') GROUP BY building ORDER BY building SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) GROUP BY building ORDER BY building  SELECT building, COUNT(asmt_number) AS Cnt FROM dbo.mr_gb_asmt WHERE (school_year = 2008) AND (due_date <= CONVERT (DATETIME, '2007-10-26 00:00:00', 102)) AND (publish_asmt = 'N') GROUP BY building ORDER BY building
 My question is how can I combine these three queries into one so that I only get one record set?
 Thanks for all the help!

View 3 Replies View Related

MS SQL:- Regular Expressions

Jun 24, 2008

Hi, the Microsoft SQL Server version is 2000


Basically I want use a basic regular expression but can't seem to get the syntax right.

i want to compare ID_short = ID_Long

ID_short is a truncated version of ID_Long, and I want to search on the beginning only (hence I can't use the 'LIKE' comparative on it's own).

What is the syntax to use Reg Expressions (or if anyone knows a non RegExp way of searching the beginning please let me know).



Thanks

View 5 Replies View Related

Can I Set Expressions Programmatically?

Apr 16, 2007

I'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.



For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:



"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"



What would the code look like?

View 4 Replies View Related

Color Expressions

Jun 5, 2007

Hey everyone,



I know that you can make an expression that will make it one color if a certain condition is met and a different one if it is not but is there anyway to make it so that if a number is less than another it's one color, if it's greater it's a different color and if they're equal it's a third color? Thanks for the help.



-Keith

View 1 Replies View Related

Multiple Expressions.

Apr 16, 2007

Good Morning all.

Is it possible to put multiple expressions in one cell.



Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.



=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))



Desired output will look sim to this in one cell







Y = 5

N = 3

NA = 0



Thanks,

Rick

View 4 Replies View Related

Expressions And Other Code?

Jun 16, 2007

Is there any way to write C# code in SQL Server Reporting Services?

View 1 Replies View Related

Dataflow And Expressions

May 23, 2006

Greetings!

I am attempting to implement the following case statement BEFORE getting the data in to my destination table but I don't know how to create an expression for it.



In the mapping section of my OLE DB destination component I can only do mapping but I can't actually manipulate the data before it gets to the destination table.

What do I have to do to implement :



case
when SOPD.PRICE_TOP_NUMBER is NULL then -8
else SOPD.PRICE_TOP_NUMBER
end AS price_top_number,



before it goes to the destination column?!



Thanks for your help in advance.

View 11 Replies View Related

Problem In Expressions

Jun 30, 2007

Hello,





When i select jan month i should display december and previous year using expressions. plz advice me.

View 1 Replies View Related

Boolean Expressions

Nov 12, 2007

I have a unusual problem, well it is unusual becaue i cannot understand it.

I am retrieving the following data, Revenue, Sales, Commission, and calculating Commission Rate in the report itself.

I got warnings that a textbox that was attempting to calculate the Commission Rate was trying to divide by zero.

When I analysed the data, I realised that indeed there were instances when Revenue was 0.00 and therefore when calculating the commission rate, commission/revenue it returned an error.

I then thought I'd do the following in the expression field

=iif((Fields!RevenueGbp.Value = 0.00), "Zero", (Fields!Commission.ValueGbp/Fields!RevenueGbp.Value))

However this does not work. However if i tried the following Boolean query

=iif((Fields!RevenueGbp.Value = 0.00), "Zero", Fields!CommissionGbp.Value)

it does work. In fact if I use any other identifier or use two field thats add, subtract or multiplied with each other it will work. The problem only arises is when I decide to divide two fields together.

Strangely if the condition is satisfied - [Fields!RevenueGbp.Value = 0.00] - it will still try to work out the division even though it should just return Zero!

Anyone help?

View 4 Replies View Related

Slash In Expressions

Jan 14, 2008



Hi,

I have the following expression:

@[User::varLogFolder] +"\"+ "CollectTrnsRxaFecPagLog.txt"

but when I evaluated it I get:

\testteamserverMigrationLog\CollectTrnsRxaFecPagLog.txt

As you can see I´m trying to create a folder but it keeps the double slash, how can I fix it?

thanks.

well the real expression is:

@[User::varLogFolder] +"\"+ @[System:ackageName] +"\"+replace(replace(@[User::varFilesCollect],"\","" ),".","")+"\"+"CollectTrnsRxaFecPagLog.txt"

and the error is because:

[CollectTrnsRxaFecPagLog [4712]] Information: The processing of file "\testteamserverMigracionLog\PCKG_BH_COLLECT estteamservermigraciondatamcli010001rxaCollectTrnsRxaFecPagLog.txt" has started.

the yellow characters are the conflict!!

View 3 Replies View Related

Report Expressions

Sep 19, 2007

Hello there,just thinking something.I have a matrix which have values retrieve from a database.I had make the value goes like this


=Sum(Fields!Allocated.Value) & " [" & Sum(Fields!Allocated.Value) & "]"

But now,i am curious if ever theres a way where i can make the value look like this in the matrix cell


Actual Value -> 0.5 [1.3] Is this possible??(It is in the same cell)


Thanks Guys!!

View 2 Replies View Related

Using Expressions In FOR LOOP

Apr 11, 2007

I need to create a While Loop with the FOR LOOP task.



According to my SSIS book, "to set up a While Loop in the For Loop, simply create an expression that tests a variable that can be modified by some workflow either inside or outside the For Loop"



What I want to do is, put an FTP task (whose job it is to receive a file) inside the For Loop.



I want the control flow to go like:



FOR LOOP --> FTP task --> file received? --> NO --> set variable 0 --> FOR LOOP --> check for variable --> is variable 1? --> NO --> FOR LOOP --> FTP task



and so on, until the variable is set to 1, indicating the successful transfer of the file



According to my book, this can be done simply with the use of expressions.



My question is, how do I write the expressions, both for the FTP task (which will set the variable to 0 or 1), and then the expression for the For Loop, which will evaluate the variable in its expression?



So, seems like I need to write 2 expressions, but not sure how.



Any help appreciated.



Thanks





View 35 Replies View Related

Multiple Expressions.

Apr 16, 2007

Good Morning all.

Is it possible to put multiple expressions in one cell.



Here is an example of the expressions I'm using. I'm currently having to put them horizontally in a seperate cell.



=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "Y", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "N", 1, Nothing))

=Count(IIf (Trim(Fields!NOB_Pickup_L_D_T.Value) = "NA", 1, Nothing))



Desired output will look sim to this in one cell







Y = 5

N = 3

NA = 0



Thanks,

Rick

View 1 Replies View Related

Numeric Expressions And Aliases

Mar 8, 2006

I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.

In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:

SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue

which is how the formula reads in the Access view.

However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:

(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue

Which gives me an incorrect value.

Does anyone know why this is happening, or am I just unaware of the right way of doing it?

Thank you,

Derrick

View 3 Replies View Related

? On Check Constraint Expressions

Sep 15, 2004

I am creating a check constraint on a field (GRID_NBR) for values between 1 & 99. I am a little confused on creating the expression for it (Books online is vague).

Can I use the following expression: GRID_NBR BETWEEN 1 AND 99

Or do I have to use: GRID_NBR > 0 AND GRID_NBR < 100


Thanks!

View 1 Replies View Related

SSIS And Regular Expressions

Feb 9, 2007

Hi,

I have a crappy old database that has an email field with a lot of bad data, and I need to use SSIS to extract a list of names & email addresses.

I have an OLE DB Source set up to pull the relevant columns out of the database. Is there a way that I could setup a Regular Expression based filter to disregard any rows where my email Regex does not match the value of the email column?

Thanks,
John

View 4 Replies View Related

Expressions Based On Subtotals

Apr 23, 2007

How do I create calculated fields based on subtotals?

View 6 Replies View Related

How To Use Regular Expressions On SSIS?

Sep 27, 2007

Hi everyone,


Is it possible? I haven't idea how to implement as if it were done from conventional .Net code
I'd like to check every field for a plan file

First field NNNNNNN
Second field XXXNNNNNNN
,,

Thanks for any input or idea,

View 11 Replies View Related

Setting Expressions In DtsConfig

Jun 12, 2007

Hi, i'm new in this forum and in SSIS, so I apologize if mine is a stupid question and also for my english.

I have this problem: i have to develop an ETL package and i have to set the transformation between input and output columns using a configuration file, i.e. my client want to change the transformation simply modifying a config file( for example from UPPER to LOWER ).

I tried using dtsconfig and setting EvaluateAsExpression with value 1( true ) and Expression UPPER[Column 2] but i get an error saying that Column 2 is not a variable.

There is a way to do it?

I don't know if i explained the problem well.

Thanks in advance

View 6 Replies View Related

IN Function Equivalent For Expressions?

Dec 3, 2007


Trying to write the below in an expression box, I still have not figured out an equivalent to €œIN€? in SQL syntax, so far I€™ve had to use €œOR€? to get it to work. Anyone know the right way? I€™m trying to avoid writing this in the stored proc

Example:

Iif (Fields!INVESTOR_NBR.Value in (0, 265, 465), "DIRECT", "PARTICIPATIONS")

View 1 Replies View Related

Need Expressions/Syntax Source

Apr 24, 2008

Does anybody know a reliable source for common Expressions and Syntax rules used in RS2000 or RS2005?
This is my third day chipping away at RS and it's been challenging so far.
thx,
John

View 1 Replies View Related

Regular Expressions In SQL 2005 Using C#

Sep 24, 2006

Hi all,

i wrote a little function that is basically supposed to give me a match of a regular expression in C#. i tested it out in VS05 and it seems to work fine. It hits the fan when i try it on SQL Query analyzer once deployed. Anyone have any idea why?

here is the C# class that I deploy to a db using VS05.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.Text.RegularExpressions;



public partial class RegExTest

{

[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true, IsPrecise = true)]

public static string RegExMatch(string pattern, string matchString)

{

Regex r1 = new Regex(pattern);

if (r1.Match(matchString.TrimEnd(null)).Success)

{

Regex testReg = new Regex(pattern);

Match regMatch = testReg.Match(matchString);



string runnumber = regMatch.ToString().TrimEnd('_');

runnumber = runnumber.TrimStart('_');

return runnumber;

}

else

{

return null;

}



}

};

Basically i have a teststring that looks like abc_xxx_nnndef. xxx and nn are both numbers, and im using the pattern _\d{3}_ to test it out in SQL (i need the numbers between '_' and '_'). But it never even proceeds past the if statement in SQL i think. here is the basic SQL that i use to test the above function:

use northwind

go

declare @teststring varchar(50),

@pattern varchar(10),

@out varchar(50)

set @teststring = 'dw_012_34.ext'

set @pattern = '_\d{3}_'

set @out = dbo.RegExMatch(@pattern, @teststring )

select @teststring

select @pattern

select @out



Anyone have any suggestions as to why it works in VS 05 when I use it in a C# program, but fails on the SQL version?

View 8 Replies View Related

Expressions And Datareader Source

Mar 12, 2007

Greetings my SSIS friends,

Apologies for asking a similar question again but I am still non the wiser with this problem!

Let me explain to you my situation and the method I've adopted to try and solve it.

I have some source data residing in a SQL Server 6.5 database. The source data consists of a single table. For this example I will assume that my table contains only 2 columns, an ID column called result_ID and a Result_Name.

The idea is to retrieve new data each time the package is run. We will know this because the result_IDs in the source table will be greater than the maximum result_ID in my destination table . The way the package should work is like this :

1) Retrieve maximum result_ID from destination table

2) retrieve data from source table where result_ID > maximum result_ID from destination table.



My package consists of a

1) SQL Query Task which retrieves the maximum result_ID and places it in a user variable (type Int32).

2) A Data flow task with a Datareader source adapter which uses an expression to retrieve the data. My expression looks like this : "select * from result where result_id > " + (dt_str, 10, 1252) @[User::max_result_id]

When I run my package the first time all the rows are retrieved (as my destination table is empty to begin with). BUT when I run it the second time the same thing happens again!! All rows are retrieved.

I placed a breakpoint at the point where the variable gets populated with the maximum result_ID and true enough, the variable gets populated with the correct result_ID BUT then that variable gets reset to 0 in my expression!



This problem is driving me crazy! Has anybody out there experienced this kind of problem before?! What are the ways to solve it?!

Thanks for your help in advance.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved