partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David
|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>
|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>
|||You could try save the files you want to attach on a safe place, create a database with same file
structure as you want to have for the one you want to attach, stop SQL server, copy the files you
want to attach over file files for the db you just created and start SQL server. Now, the db is
corrupt to some extent, the question is whether the database will be available with problems in it
or suspect when you start SQL server. MS Support is probably the best route in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
>> use northwind
>> BEGIN TRANSACTION namechange
>> UPDATE dbo.Employees
>> SET Title = 'team leader' where FirstName = 'Bishoy'
>> UPDATE dbo.Employees
>> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
>> SAVE TRANSACTION namesaved
>> UPDATE dbo.Employees
>> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
>> SELECT *
>> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>>
>> ROLLBACK TRANSACTION namesaved
>> COMMIT TRANSACTION namechange
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a database with same file
structure as you want to have for the one you want to attach, stop SQL server, copy the files you
want to attach over file files for the db you just created and start SQL server. Now, the db is
corrupt to some extent, the question is whether the database will be available with problems in it
or suspect when you start SQL server. MS Support is probably the best route in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/info_corrupt_suspect_db.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

partial transaction is not working

use northwind
BEGIN TRANSACTION namechange
UPDATE dbo.Employees
SET Title = 'team leader' where FirstName = 'Bishoy'
UPDATE dbo.Employees
SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
SAVE TRANSACTION namesaved
UPDATE dbo.Employees
SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
SELECT *
FROM dbo.Employees WHERE FirstName = 'Bishoy'
ROLLBACK TRANSACTION namesaved
COMMIT TRANSACTION namechange"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
Nope, works perfectly.
Add another
SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
At the end. The BirthDate change is rolled back, but the Title change is
commited.
David|||Also make sure you don't have an implicit transaction that is nesting your
changes without a commit.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||Yes , you are right , thank you.
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:eggqJCaeFHA.3848@.TK2MSFTNGP10.phx.gbl...
> "Bishoy George" <bishoycom@.gmail.com> wrote in message
> news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> Nope, works perfectly.
> Add another
> SELECT * FROM dbo.Employees WHERE FirstName = 'Bishoy'
> At the end. The BirthDate change is rolled back, but the Title change is
> commited.
> David
>|||You could try save the files you want to attach on a safe place, create a da
tabase with same file
structure as you want to have for the one you want to attach, stop SQL serve
r, copy the files you
want to attach over file files for the db you just created and start SQL ser
ver. Now, the db is
corrupt to some extent, the question is whether the database will be availab
le with problems in it
or suspect when you start SQL server. MS Support is probably the best route
in any case...
Some thoughts:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Bishoy George" <bishoycom@.gmail.com> wrote in message
news:%23RntioSeFHA.3700@.TK2MSFTNGP10.phx.gbl...
> use northwind
> BEGIN TRANSACTION namechange
> UPDATE dbo.Employees
> SET Title = 'team leader' where FirstName = 'Bishoy'
> UPDATE dbo.Employees
> SET TitleOfCourtesy = 'Dr' where FirstName = 'Bishoy'
> SAVE TRANSACTION namesaved
> UPDATE dbo.Employees
> SET BirthDate = '8/15/1978' WHERE FirstName = 'Bishoy'
> SELECT *
> FROM dbo.Employees WHERE FirstName = 'Bishoy'
>
> ROLLBACK TRANSACTION namesaved
> COMMIT TRANSACTION namechange
>
>

Parms

I was wondering how to default an input parm to nulls.
I created a proc which looked liked the following:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID = null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
This compiles, but it does not run correctly. If I change the
declaration of @.ClubID and removed the '=null' - then it compiles, but
it requires the @.ClubID to be populated (will not accept nulls).
How do I define an input parm which is not required to be populated
(accepts nulls and is defaulted to null)?Hi Jim.
The parameter is defined correctly - you should check for null by using IS
NULL rather then = null in the body of the proc:
ALTER proc dbo.Proc_QuerySkaters
@.ClubID uniqueidentifier =null,
@.msg nvarchar(220) out
as
declare @.cnt int
if @.ClubID is null
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
set @.cnt = @.@.ROWCOUNT
end
else
begin
select ClubID, FirstName, MiddleName, LastName
from dbo.Skater
where @.clubID = ClubID
set @.cnt = @.@.ROWCOUNT
end
if @.@.ERROR > 0
begin
set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
- Error code: ' + cast( @.@.ERROR as char(4) )
return -1
end
set @.msg=''
return 0
Regards,
Greg Linwood
SQL Server MVP
"Jim Heavey" <JimHeavey@.nospam.com> wrote in message
news:Xns9458D9954D842JimHeaveyhotmailcom@.207.46.248.16...
> I was wondering how to default an input parm to nulls.
> I created a proc which looked liked the following:
> ALTER proc dbo.Proc_QuerySkaters
> @.ClubID uniqueidentifier =null,
> @.msg nvarchar(220) out
> as
> declare @.cnt int
> if @.ClubID = null
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> set @.cnt = @.@.ROWCOUNT
> end
> else
> begin
> select ClubID, FirstName, MiddleName, LastName
> from dbo.Skater
> where @.clubID = ClubID
> set @.cnt = @.@.ROWCOUNT
> end
> if @.@.ERROR > 0
> begin
> set @.msg = 'Unexpected error occurred executing Proc_QuerySkaters
> - Error code: ' + cast( @.@.ERROR as char(4) )
> return -1
> end
> set @.msg=''
> return 0
> This compiles, but it does not run correctly. If I change the
> declaration of @.ClubID and removed the '=null' - then it compiles, but
> it requires the @.ClubID to be populated (will not accept nulls).
> How do I define an input parm which is not required to be populated
> (accepts nulls and is defaulted to null)?

parentheses in a check constraint

Can we use parentheses in a check constraint in MS-SQL-server DDL?

e.g. I'm having a problem with the following statement:

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]
CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)
OR
([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff] IS NOT
NULL));

The statement appears to run fine, but when I look at my table
definition afterwards, it appears that SQL-server ignored the
parentheses in my constraint; it shows the constraint expression as:
(([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL AND
[TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))

My intention is that if there's (non null) data in either of the columns
TimeOn or TimeOff is not null, all three of the columns TimeOn, TimeOff
and ShiftCode must have non null data.

OK, I realise I could enforce this by altering my table setup in other
ways. Right now I'm just trying to figure out if this I'm just up
against a difference between dialects of SQL in check constraints here.
Am I missing something obvious with parentheses?

BTW the DDL for the table I'm testing on:
CREATE TABLE [dbo].[MyTable](
[FNname] [nvarchar](50) NOT NULL,
[ShiftDate] [datetime] NOT NULL,
[ShiftCode] [nchar](2) NULL,
[TimeOn] [nchar](4) NULL,
[TimeOff] [nchar](4) NULL);Helen Wheels <helenwheelss@.yahoo.com.auwrote in
news:136mli9pi74bs63@.corp.supernews.com:

Quote:

Originally Posted by

Can we use parentheses in a check constraint in MS-SQL-server DDL?
>
e.g. I'm having a problem with the following statement:
>
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]
CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)
OR
([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff]
IS NOT
NULL));
>
The statement appears to run fine, but when I look at my table
definition afterwards, it appears that SQL-server ignored the
parentheses in my constraint; it shows the constraint expression as:
(([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL
AND [TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))
>
My intention is that if there's (non null) data in either of the
columns TimeOn or TimeOff is not null, all three of the columns
TimeOn, TimeOff and ShiftCode must have non null data.
>
OK, I realise I could enforce this by altering my table setup in other
ways. Right now I'm just trying to figure out if this I'm just up
against a difference between dialects of SQL in check constraints
here. Am I missing something obvious with parentheses?
>
BTW the DDL for the table I'm testing on:
CREATE TABLE [dbo].[MyTable](
[FNname] [nvarchar](50) NOT NULL,
[ShiftDate] [datetime] NOT NULL,
[ShiftCode] [nchar](2) NULL,
[TimeOn] [nchar](4) NULL,
[TimeOff] [nchar](4) NULL);
>

"When more than one logical operator is used in a statement, the AND
operators are evaluated first ..." (BOL) - your inner parentheses are
therefore unnecessary.|||Chris.Cheney wrote:

Quote:

Originally Posted by

Helen Wheels <helenwheelss@.yahoo.com.auwrote in
news:136mli9pi74bs63@.corp.supernews.com:
>
>

Quote:

Originally Posted by

>>Can we use parentheses in a check constraint in MS-SQL-server DDL?
>>
>>e.g. I'm having a problem with the following statement:
>>
>>ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]
>>CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)
> OR
> ([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff]
> IS NOT
>>NULL));
>>
>>The statement appears to run fine, but when I look at my table
>>definition afterwards, it appears that SQL-server ignored the
>>parentheses in my constraint; it shows the constraint expression as:
>>(([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL
>>AND [TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))
>>
>>My intention is that if there's (non null) data in either of the
>>columns TimeOn or TimeOff is not null, all three of the columns
>>TimeOn, TimeOff and ShiftCode must have non null data.
>>
>>OK, I realise I could enforce this by altering my table setup in other
>>ways. Right now I'm just trying to figure out if this I'm just up
>>against a difference between dialects of SQL in check constraints
>>here. Am I missing something obvious with parentheses?
>>
>>BTW the DDL for the table I'm testing on:
>>CREATE TABLE [dbo].[MyTable](
> [FNname] [nvarchar](50) NOT NULL,
> [ShiftDate] [datetime] NOT NULL,
> [ShiftCode] [nchar](2) NULL,
> [TimeOn] [nchar](4) NULL,
> [TimeOff] [nchar](4) NULL);
>>

>
>
"When more than one logical operator is used in a statement, the AND
operators are evaluated first ..." (BOL) - your inner parentheses are
therefore unnecessary.

So they are. The constraint is working as expected, it just doesn't look
quite the way I'm used to reading it. Thanks.|||Helen Wheels (helenwheelss@.yahoo.com.au) writes:

Quote:

Originally Posted by

Can we use parentheses in a check constraint in MS-SQL-server DDL?
>
e.g. I'm having a problem with the following statement:
>
ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [CK_MyTable_TimesDataOK]
CHECK (([TimeOn] IS NULL AND [TimeOff] IS NULL)
OR
([ShiftCode] IS NOT NULL AND [TimeOn] IS NOT NULL AND [TimeOff] IS
NOT NULL));
>
The statement appears to run fine, but when I look at my table
definition afterwards, it appears that SQL-server ignored the
parentheses in my constraint; it shows the constraint expression as:
(([TimeOn] IS NULL AND [TimeOff] IS NULL OR [ShiftCode] IS NOT NULL AND
[TimeOn] IS NOT NULL AND [TimeOff] IS NOT NULL))

SQL Server does not store the constraint text as provided, but performs
some normalisations on it. One such normalisation is apparently to
remove superfluous parantheses. Your original constraint text and what
SQL Server saved, are equivalent. AND binds tighter than OR, so these
two are the same:

x AND y OR a AND b
(x ANY y) OR (a AND b)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Parameters in Function in OLE DB Source

Hi there!

I need to use some parameters in an OLE DB Source like that:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= dbo.fkt_get_guelt_von (?)
and dbo.fkt_get_guelt_bis (posh_valid_to) >= dbo.fkt_get_guelt_bis (?)

The function returns a valid datetime. These parameters normaly works fine, but not with use as funtion-paramerters. I get feeble error messages (by trying to map the parameters) like that:

"Falsche Syntax in der N?he von ')'. (Microsoft SQL Native Client)" - what means "Wrong syntax near ')'" and says nothing. If i replace the parameters with the mapped value (i.E. '200601') it works fine.

What is the reason for that? It there any solution or a workaround?

Thanks, Torsten

The OLE DB source may be having problems detecting the parameter in your query. I have the impression that it just understands simple queries. The work around is to place the query in a variable, Set the 'EvaluateAsExpression' property to TRUE, and then use an expre ssion to make the query dynamic.

Then in the OLE DB source, choose the option that says your query is in a variable and select the variable from the dropdown list. This is a very common practice to pass the query into source components

Here you can find more posts on how to do that:

http://forums.microsoft.com/MSDN/Search/Search.aspx?words=query+variable+expression&localechoice=9&SiteID=1&searchscope=forumscope&ForumID=80

|||

Thanks!

I've found another way: I execute these functions in an "Execute SQL-Task" an put the results into a variable, what i use in the OLE DB Source. Works fine:

select * from mdm.mdm_pos_hierarchies
where dbo.fkt_get_guelt_von (posh_valid_fr) <= ?
and dbo.fkt_get_guelt_bis (posh_valid_to) >= ?