Primary key on Linked server Issue

When I perform a simple query over a linked server that is looking for a
specific value using the primary key, SQL Server performs a constant scan in
stead of a remote query and returns 0 rows. Running the same query locally g
ives the expected result. When I force a data type conversion from integer t
o varchar in the criteria it gives the expected result.
Example problem query:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID = 1
0 records are returned, but a record with a MyID = 1 does exist.
The following works as expected:
Select MyID
From Server2.MyDB.dbo.MyTable
Where MyID Like 1
Select MyID
From Server2.MyDB.dbo.MyTable
Where Cast(MyID As varchar) = 1
If i do a select on any other column, then the execution plan shows a remote
query and when I run it the expected rows are returned.
This is only happening on one server, and the only difference I can see is t
hat the database is set up for merge replication.
Any ideas as to why this is happening and how to permanently prevent it?
Thanks
Chris LongstaffAs a follow up to this message and to save anyone else the hassle of finding
the solution ... the issue is merge replication.
Basically the check constraints placed on the table for replication are used
when querying the linked server ... in my case i had a constraint of values
between 810 and 1200 ... when you query the linked server it checks for co
nstraints first and as I was querying for id =400 then it will never find it
as according to the check constraint that value cannot exist within the tab
le...
There are two possible solutions ... force a conversion to bypass the check
constraint ... or use OLEDB for ODBC as the driver as this will not attem
pt to use the check constraint.
Hope this helps anyone else who comes across this!sql

Previous YTD

Hi all

I am trying to perform Pervious Year To Date Total (which is like YTD but for the previous year. ie. ytd would work for this year's jan -> whatever month. But I need a total from Last year from jan -> whatever year to do comparision), However I've ran into the following issues.

Since Year is a variable and it can change depending on which year you select. I tried to Return the first member of the month which in theory should be January of a Year. However when I performed the below query I got Dec which was the overall period through out the entire Month this include 2002 dec -> 2005 May period so the head returned Dec.

SELECT { head(Descendants(
[Period].CurrentMember,
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )
So then I tried to return the Head of the Set of the current year using the below query. But no matter what I do I can not get it working.

SELECT { head(Descendants(
{[Period].CurrentMember, [Period].[Month]},
[Month]),1)} ON COLUMNS , { [BrandModel].[All BrandModel] } ON ROWS FROM [Running Report] WHERE ( [Measures].[Unit] )

How would you guys usually do a previous year to date comparision ? since year is a variable

Thanks
Tom

This isn't clear. Can you be more explicit?|||hi vector

I think you've answered my question on the other thread. I will test this when I get into office first thing in the morning.

thanks alot !!

Prevent writing to the Transaction Log?

I know this isn't exactly best practice, but is there anyway to perform DELETEs and INSERTs without writing to the transaction log?

Mike

No. The only type of operations that can be minimally logged (in SIMPLE and BULK_LOGGED recovery mode) are Creation and drop of indexes Bulk loading of data Modification of BLOB data SELECT INTO -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://www.solidqualitylearning.com/ Blog: http://solidqualitylearning.com/blogs/tibor/ wrote in message news:99b53f54-94e2-44c5-998e-3a66b6bb2966@.discussions.microsoft.com... >I know this isn't exactly best practice, but is there anyway to perform
> DELETEs and INSERTs without writing to the transaction log? >
> Mike >
>