Error: Could not create an acceptable cursor.

I'm trying to run a stored proc on a SQL 2005 SP1 box to return info to a SQL 2000 SP4 box, as a linked server. Both boxes have the latest service packs, and run Windows 2003 Server, again with the latest service packs.

The error I get is:

OLE DB provider "SQLNCLI" for linked server "192.168.0.126" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".
Msg 16955, Level 16, State 2, Line 1
Could not create an acceptable cursor.

The full script I am running is:


CREATE procedure [dbo].[proc_AuditServer]
as

/*
** Auditing Script for SQL Servers.
**
** D Maxwell, June 2007
**
** This script takes configuration and job status information
** and writes it to a designated logging server. I'll describe
** each section in detail, below. We write to the local box first,
** Then upload everything to the logging server.
**
** This is the SQL 2005 version.
*/

/*
** We want to know exactly what server this is, so
** we get the server name, instance name, as well as
** SQL Version, Edition, and Service Pack level.
*/


truncate table admin.dbo.sql_servers

insert into admin.dbo.sql_servers
select convert(varchar(15), serverproperty('ServerName')),
convert(varchar(25), serverproperty('InstanceName')),
convert(char(9), serverproperty('ProductVersion')),
convert(varchar(4), serverproperty('ProductLevel')),
convert(varchar(20), serverproperty('Edition')),
getdate()

/*
** Now, having that, we get the list of databases,
** as well as thier creation dates and file names.
*/

truncate table admin.dbo.databases

insert into admin.dbo.databases
select
convert(varchar(15), serverproperty('ServerName')),
dbid,
name,
crdate,
filename
from master..sysdatabases
where dbid > 4
order by dbid

/*
** We need to know how the server is configured, so we
** can compare it to a list of preferred configuration
** values, as well as the defaults. I cut this out of
** sp_configure.
*/

truncate table admin.dbo.server_config

insert into admin.dbo.server_config
select
convert(varchar(15), serverproperty('ServerName')),
name,
config_value = c.value,
run_value = master.dbo.syscurconfigs.value
from master.dbo.spt_values, master.dbo.sysconfigures c, master.dbo.syscurconfigs
where type = 'C'
and number = c.config
and number = master.dbo.syscurconfigs.config

and
((c.status & 2 <> 0 )
OR
(c.status & 2 = 0)
)
order by lower(name)

/*
** The next configuration item we want to get is the
** list of jobs that run on the server. We're looking
** specifically for backup and other maintenance jobs.
** (Which will hopefully be named appropriately...)
** We use Neil Boyle's job report script for this.
** My comments and changes prefaced by a 'DM:'
*/

truncate table admin.dbo.jobs

insert into admin.dbo.jobs
select
convert(varchar(15), serverproperty('ServerName')), --DM: Needed since we'll have lots of servers reporting
j.job_id, -- DM: More unique than a name.
convert(varchar(22), j.name) as job_name,
case freq_type -- Daily, weekly, Monthly
when 1 then 'Once'
when 4 then 'Daily'
when 8 then 'Wk ' -- For weekly, add in the days of the week
+ case freq_interval & 2 when 2 then 'M' else '' end -- Monday
+ case freq_interval & 4 when 4 then 'Tu' else '' end -- Tuesday
+ case freq_interval & 8 when 8 then 'W' else '' end -- etc
+ case freq_interval & 16 when 16 then 'Th' else '' end
+ case freq_interval & 32 when 32 then 'F' else '' end
+ case freq_interval & 64 when 64 then 'Sa' else '' end
+ case freq_interval & 1 when 1 then 'Su' else '' end
when 16 then 'Mthly on day ' + convert(varchar(2), freq_interval) -- Monthly on a particular day
when 32 then 'Mthly ' -- The most complicated one, "every third Friday of the month" for example
+ case freq_relative_interval
when 1 then 'Every First '
when 2 then 'Every Second '
when 4 then 'Every Third '
when 8 then 'Every Fourth '
when 16 then 'Every Last '
end
+ case freq_interval
when 1 then 'Sunday'
when 2 then 'Monday'
when 3 then 'Tuesday'
when 4 then 'Wednesday'
when 5 then 'Thursday'
when 6 then 'Friday'
when 7 then 'Saturday'
when 8 then 'Day'
when 9 then 'Week day'
when 10 then 'Weekend day'
end
when 64 then 'Startup' -- When SQL Server starts
when 128 then 'Idle' -- Whenever SQL Server gets bored
else 'Err' -- This should never happen
end as schedule

, case freq_subday_type -- FOr when a job funs every few seconds, minutes or hours
when 1 then 'Runs once at:'
when 2 then 'every ' + convert(varchar(3), freq_subday_interval) + ' seconds'
when 4 then 'every ' + convert(varchar(3), freq_subday_interval) + ' minutes'
when 8 then 'every ' + convert(varchar(3), freq_subday_interval) + ' hours'
end as frequency

-- All the subsrings are because the times are stored as an integer with no leading zeroes
-- i.e. 0 means midnight, 13000 means half past one in the morning (01:30:00)

, substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_start_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6),active_start_time), 6) ,5 ,2) as start_at

,case freq_subday_type
when 1 then NULL -- Ignore the end time if not a recurring job
else substring (right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6), 1, 2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,3 ,2)
+ ':'
+ substring (
right (stuff (' ', 1, 1, '000000') + convert(varchar(6), active_end_time), 6) ,5 ,2) end as end_at
from msdb.dbo.sysjobs j, msdb.dbo.sysJobSchedules s, msdb.dbo.sysschedules c
where j.job_id = s.job_id and s.schedule_id = c.schedule_id
order by j.name, start_at

/*
** Now that we know what jobs we have, let's find out
** how they did recently.
*/

truncate table job_status

insert into job_status
select convert(varchar(15), serverproperty('ServerName')),
job_id, run_status, run_date,
run_time, run_duration
from msdb..sysjobhistory
where step_name = '(job outcome)' -- The last 90 days' worth.
and run_date > (select replace(convert(varchar(10), (getdate() - 90), 120), '-', ''))
order by run_date desc


/*
** If this server is already known to the audit server,
** we need to remove the existing data from the audit
** tables.
*/


declare @.known bit
set @.known =
(select count(*)
from [192.168.0.126].AUDITDB.dbo.sql_servers
where server_name =
(select convert(varchar(15), serverproperty('servername'))))

/*
** Now we remove the existing information from the audit tables,
** if need be.
*/

if @.known = 1
begin

delete from [192.168.0.126].AUDITDB.dbo.sql_servers
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.databases
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.server_config
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.jobs
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

delete from [192.168.0.126].AUDITDB.dbo.job_status
where server_name = (select convert(varchar(15), serverproperty('ServerName')))

end



/*
** Finally, we upload the new info from here to the audit server.
*/

insert into [192.168.0.126].AUDITDB.dbo.sql_servers
select * from admin.dbo.sql_servers

insert into [192.168.0.126].AUDITDB.dbo.server_config
select * from admin.dbo.server_config

insert into [192.168.0.126].AUDITDB.dbo.databases
select * from admin.dbo.databases

insert into [192.168.0.126].AUDITDB.dbo.jobs
select * from admin.dbo.jobs

insert into [192.168.0.126].AUDITDB.dbo.job_status
select * from admin.dbo.job_status



This works fine for other boxes of the same service pack levels. I've already read KB302477, which doesn't appear to apply, since I'm already several revisions beyond that. I'm unable to duplicate this in test.

Any ideas as to what I should look at next? Thanks.

-D.Check DB_option to see whether local and global cursors are default, SP1 for SQL2005 is older one and latest is SP2.

Also see this http://support.microsoft.com/kb/302477 that talks about hotfix.|||OK, I went back and read that article again. It says this happens "If a cursor is created on a system stored procedure that returns schema information from a remote server..." I'm sorry, but I'm still learning to program in SQL and I don't see where I'm doing that. Is it in the DELETE FROM statement? And that article talks about SQL 2000 SP2. I'm running SQL 2000 SP4 on that box. Would I still need the hotfix?

I tried this with a server running SQL2005 SP2 (Version 9.00.3159), and got the same error.

I also tried setting the cursor option on the databases involved on both servers, to both global and local, in all 4 configurations. Still getting the same error.

Any other ideas?

Error: Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "

Hi,

I′m trying to make several distributed queries to a linked server, and for some of them I get this error message. I can′t find anytihing about it anywhere. Can anyone tell me what could be the reason of this error? The way I'm executing the query in my stored procedure is:

exec sp_executesql @.sqlinsert

@.sqlinsert being a nvarchar sentence that contains the distibuted query.

Thank you!

Mehdi

Guess the provider needs a return value. I don′t know if it needs a return status like return 1 or return 0, or he needs something to handle (just a dummy select or something else. Try setting the return value for the stored procedure.

HTH, Jens Suessmeyer.

error with sp_tables_ex

Hi all,

I am using a sp for show the external tables in a linked server ABC which via Ole DB for Visual Foxpro.
sp_tables_ex ABC
It returns error,
Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41
The OLE DB provider "SQL Server" for linked server "(null)" reported an error. The provider reported an unexpected catastrophic failure.
Msg 7311, Level 16, State 2, Procedure sp_tables_ex, Line 41
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used.

When I used the same command to other linked server(with ole DB VFP also but with different path). And I tried 'select * from openquery(ABC, 'select * from product')'. Both work fine.

Kindly advise. Thanks in advance.

Hi Stephanie,

Are you by any chance running into the problem described in this KB article:

FIX: OLE DB Provider Improperly Enumerates DBSCHEMA_TABLES
http://support.microsoft.com/kb/314888

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Error with linked Servers

I am having an extremely difficult time getting a linked server to
work. I am attempting to link to using an ODBC driver to a proprietary
database. I can create the ODBC connection and see the tables from
with Access using this connection. I can set up the linked server but
as soon as I attempt to view the tables it either gives me an error
7399 or just sits there. Any help would be appreciative and if there
are any other questions I need to answer just let me know.
Thanks,
Matt
Can you elaborate on how are you connecting via Access and how are you using
to create the linked server?
"Matt" wrote:

> I am having an extremely difficult time getting a linked server to
> work. I am attempting to link to using an ODBC driver to a proprietary
> database. I can create the ODBC connection and see the tables from
> with Access using this connection. I can set up the linked server but
> as soon as I attempt to view the tables it either gives me an error
> 7399 or just sits there. Any help would be appreciative and if there
> are any other questions I need to answer just let me know.
> Thanks,
> Matt
>

Error with Linked Servers

From time to time the linked servers we have set up cannot be accessed. When
I click on the tables node under the linked server within enterprise manager
the session hangs and no error message is displayed. When querying the data
via an openquery no error is thrown, the query simply runs until manually
aborted.
I've created a linked server OK but immediately cannot open it. The only way
around this problem we have is a reboot of the server. Does anyone know how
to resolve this problem and also what would cause this.
Thanks
MikeMikeD
Do you have ping to linked server?
"MikeD" <MikeD@.discussions.microsoft.com> wrote in message
news:ACBEC816-1EE1-46E8-9535-8E52BB7C502A@.microsoft.com...
> From time to time the linked servers we have set up cannot be accessed.
> When
> I click on the tables node under the linked server within enterprise
> manager
> the session hangs and no error message is displayed. When querying the
> data
> via an openquery no error is thrown, the query simply runs until manually
> aborted.
> I've created a linked server OK but immediately cannot open it. The only
> way
> around this problem we have is a reboot of the server. Does anyone know
> how
> to resolve this problem and also what would cause this.
> Thanks
> Mike|||I can ping the server that sql is on if that's what you mean. I can see and
create linked servers but cannot query them - see below.
"Uri Dimant" wrote:

> MikeD
> Do you have ping to linked server?
> "MikeD" <MikeD@.discussions.microsoft.com> wrote in message
> news:ACBEC816-1EE1-46E8-9535-8E52BB7C502A@.microsoft.com...
>
>|||MikeD
Do you run the query in this way?
SELECT <> FROM Server.dbname.dbo.table
"MikeD" <MikeD@.discussions.microsoft.com> wrote in message
news:2C11B49F-D1BF-48C2-9ECC-2470666F9691@.microsoft.com...[vbcol=seagreen]
>I can ping the server that sql is on if that's what you mean. I can see and
> create linked servers but cannot query them - see below.
> "Uri Dimant" wrote:
>|||No - it's an openquery so it's :
SELECT * FROM OPENQUERY[linked_server_name], 'Select * from table_name'
But the problem isn't so much in the query it's the fact that when you
select the linked server the system hangs with no error message and the only
way we have of resolving the issue is a server reboot
"Uri Dimant" wrote:

> MikeD
> Do you run the query in this way?
> SELECT <> FROM Server.dbname.dbo.table
>
>
> "MikeD" <MikeD@.discussions.microsoft.com> wrote in message
> news:2C11B49F-D1BF-48C2-9ECC-2470666F9691@.microsoft.com...
>
>|||Well, is it large set data you want to return?
exec sp_serveroption 'servername','data access','true'
"MikeD" <MikeD@.discussions.microsoft.com> wrote in message
news:6EA13818-E61B-49E7-B6E0-35F581ADAF0F@.microsoft.com...[vbcol=seagreen]
> No - it's an openquery so it's :
> SELECT * FROM OPENQUERY[linked_server_name], 'Select * from table_name
'
> But the problem isn't so much in the query it's the fact that when you
> select the linked server the system hangs with no error message and the
> only
> way we have of resolving the issue is a server reboot
> "Uri Dimant" wrote:
>|||It's nothing to do with the query or the size of data. the problem is that
the linked server cannot be accessed until a reboot is done.
"Uri Dimant" wrote:

> Well, is it large set data you want to return?
> exec sp_serveroption 'servername','data access','true'
>
> "MikeD" <MikeD@.discussions.microsoft.com> wrote in message
> news:6EA13818-E61B-49E7-B6E0-35F581ADAF0F@.microsoft.com...
>
>

Error with linked Servers

I am having an extremely difficult time getting a linked server to
work. I am attempting to link to using an ODBC driver to a proprietary
database. I can create the ODBC connection and see the tables from
with Access using this connection. I can set up the linked server but
as soon as I attempt to view the tables it either gives me an error
7399 or just sits there. Any help would be appreciative and if there
are any other questions I need to answer just let me know.
Thanks,
MattCan you elaborate on how are you connecting via Access and how are you using
to create the linked server?
"Matt" wrote:

> I am having an extremely difficult time getting a linked server to
> work. I am attempting to link to using an ODBC driver to a proprietary
> database. I can create the ODBC connection and see the tables from
> with Access using this connection. I can set up the linked server but
> as soon as I attempt to view the tables it either gives me an error
> 7399 or just sits there. Any help would be appreciative and if there
> are any other questions I need to answer just let me know.
> Thanks,
> Matt
>

Error with linked Servers

I am having an extremely difficult time getting a linked server to
work. I am attempting to link to using an ODBC driver to a proprietary
database. I can create the ODBC connection and see the tables from
with Access using this connection. I can set up the linked server but
as soon as I attempt to view the tables it either gives me an error
7399 or just sits there. Any help would be appreciative and if there
are any other questions I need to answer just let me know.
Thanks,
MattCan you elaborate on how are you connecting via Access and how are you using
to create the linked server?
"Matt" wrote:
> I am having an extremely difficult time getting a linked server to
> work. I am attempting to link to using an ODBC driver to a proprietary
> database. I can create the ODBC connection and see the tables from
> with Access using this connection. I can set up the linked server but
> as soon as I attempt to view the tables it either gives me an error
> 7399 or just sits there. Any help would be appreciative and if there
> are any other questions I need to answer just let me know.
> Thanks,
> Matt
>

Error with linked server after failover

Hi all,

The following query that uses a linked server is giving me the error message below after I initiate a failover (ALTER DATABASE Northwind SET PARTNER FAILOVER).I have SQL Server 2005 SP2.I think that without the service pack there is another error too.

The query is run from a database other than northwind of course.

select * from DualLink.northwind.dbo.Test1

Please note that:

without a failover itworks perfectly

it always work if I try to run it a second time - only the first time it fails.

it fails the first time for each of the open connections. A new connection that was open after the failover will work fine.

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

The linked server is defined as

EXEC master.

dbo.sp_addlinkedserver

@.server = N'DualLink',

@.srvproduct=N'',

@.provider=N'SQLOLEDB',

@.catalog=N'northwind',

@.provstr=N'Server=(local);FailoverPartner=MyIPAddr;'

EXEC master.dbo.sp_addlinkedsrvlogin

@.rmtsrvname = 'DualLink',

@.useself = false,

@.locallogin = 'sa',

@.rmtuser = 'sa',

@.rmtpassword = 'MyPwd'

Thanks,

Avi

I am having the same problem except I don't have a linked server.

I am running SQL Server 2005 sp1 and Windows 2003 using clustering.

In addition to your symptoms I have noticed the following:

1. The error occurs when I make a query through Server Manager and when using a java app that queries a database using an ASP page. However, if I bring up our website as the first request after failover it works fine.

2. If I failover from Server1 to Server2 and don't make any requests while it is on Server2 and then failover back to Server1 I do not get the error. If I make any requests while it is on Server2 then the first request I make on Server1 after failing over will cause the error.

If you have solved the problem I would like to know how. If I find a solution I will post it here.

Blake

Error with linked server

I have a "linked server" configured in my SQL Server 2000 (SP4) server, which used to work correctly. However, I had to reinstall SQL Server (I backed up and restored the master/model/etc databases, so all my settings stayed the same). Since then, I've been getting this error when I try to use the linked server:
Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.].

The linked server is a FoxPro database, which does not use catalog or schema names. So, my select syntax looks like this:
SELECT * FROM Server...Table

SQL Server is aparently expecting something like this:
SELECT * FROM Server.Catalog.Schema.Table

Does anyone know how I can fix it so that it allows the "empty dot" method to work like it used to?'

Thanks!
JoshTry creating a new linkedserver to FP and see if it works. Also, try to update to the latest Mdac to get the latest providers.|||Yeah, I already tried to create a new linked server, and it didn't work. I checked my MDAC version, and it's the newest one (2.8 SP1).|||what foxpro driver are you using? a long time ago in a galaxy far away I had a world of trouble with fox pro linked servers until I upgraded from the 7 driver to the 8 driver.

I would drop the linked server.

install the current VFP driver. maybe reinstall MDAC.

recreate said linked server.

test the connection with OPENQUERY (but do not use it in production, pushes the processing to foxpro).

test using your empty dot method.

so glad I do not have to use fox pro anymore.|||It looks like I'm running version 6... I'll try upgrading it, thanks.

Oh yeah, I've already tried the OPENQUERY method, and it works. I could just change all of my code to use that, but that would be a lot of work.|||OPENQUERY is a pig. It will process the query in foxpro instead of sql server.|||what version of VFP are using. Be careful. Try this out in a test envrionment first because you may not be able to go back.|||Ok, so I wouldn't want to use it, even if it was easy to use. :)|||I actually don't know what version it is. We have a third-party application written in FoxPro that we pull data from to display on an intranet site. My FoxPro experience is limited to using that app, and linking SQL Server to it.

So, thanks for the warning. I'll have to do more research...|||Ok, I got it. It turns out that there is a checkbox in the linked server's Provider Options window called "Level zero only". If that is not checked, the empty-dot notation doesn't work. I checked it, and it works now.

I'm not sure why it broke, unless that option is a recent addition to the provider options - maybe someone installed some update around the same time we reinstalled SQL Server...

Anyway, thanks for the help!
Josh

error with linked server

Hi
when i try to run a query using linked servers, i get the following
error.

Server: Msg 125, Level 15, State 1, Line 1
Case expressions may only be nested to level 10.

I do have more than 10 case statements, it works fine when it is less
than 10. can anyone tell me if there is a way to have more than 10
case statements. thanks alot.

Jay

my query
Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
Then 'N'
When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
Then 'N'
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x
Where KEEP = 'Y'
Group By CATEGORY, YR"Jay" <webforum2000@.yahoo.com> wrote in message
news:9594a55e.0404230831.50bcabe0@.posting.google.c om...
> Hi
> when i try to run a query using linked servers, i get the following
> error.
> Server: Msg 125, Level 15, State 1, Line 1
> Case expressions may only be nested to level 10.
> I do have more than 10 case statements, it works fine when it is less
> than 10. can anyone tell me if there is a way to have more than 10
> case statements. thanks alot.
> Jay
> my query
> Select category, val, Sum(QTY) As QTY , yr
> From
> (
> Select val, QTY2 As QTY,
> KEEP = Case
> When code = '004' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '005' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '003' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '017' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '007' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '008' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '009' And ((YR > 2003) Or (YR = 2003 And MON > 11))
> Then 'N'
> When CODE = '010' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '038' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '032' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '030' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> When CODE = '018' And ((YR > 2003) Or (YR = 2003 And MON > 12))
> Then 'N'
> Else 'Y' End
> From
> amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
> Where CATEGORY In ('1') And CODE In ('001','003','004','005')
> And b.YR Between 2003 And 2004 And b.MON <= 1
> ) x
> Where KEEP = 'Y'
> Group By CATEGORY, YR

Since most of your conditions are the same, have you tried something like
this?

Select category, val, Sum(QTY) As QTY , yr
From
(
Select val, QTY2 As QTY,
KEEP = Case
When code in ('004', '005, '003', '017', /* etc. */)
And ((YR > 2003) Or (YR = 2003 And MON > 12))
Else 'Y' End
From
amf a Join linkedserver.source.dbo.table2 b On a.COM = b.COM
Where CATEGORY In ('1') And CODE In ('001','003','004','005')
And b.YR Between 2003 And 2004 And b.MON <= 1
) x

Simon|||
hi
thanks for the suggestion. I have a problem, this is one of the query
where it is all the same, in a few others it varies a lot. i want to
know if the limitaion exists in sql using linked servers ( since it
works fine if i dont use linked servers and have them in the same
server). i want to get aroud this, so that i dont have to change all my
existing queries, and would hamper my using linked server. thanks.. any
suggestion?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Error whilst concatenating field to a string

Hi,

I want to say on a report

"We are XX% Through the month" on a report. I added the data to the data set that I want to show and linked this to a table. I tried saying:

"We are" + Field!PercentageHoursWorked.Value + "% Through The Month" but I am getting the error:

"Input string was not in the correct format" The output that gives me this number is numeric.

Any ideas?

You have to use vb.net - code!

vb concatenates Strings using &

|||Yes, I know. I got there in the end by using convert in my SP