Re: Trouble with linked servers

Have you enabled on SS2005 remote and local connection checkbox in Surface
Area Configuration?




"gurbao" <audunj@xxxxxxxxx> wrote in message
news:1169638384.522446.226600@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,

I have SQL Server 2005 SP1 installed on win2003 SP1 with two instances
(default and \instance2).
The server is set up with mixed mode authentication.
SA is setup with the same password on both. The domain user
"sqlservice" is running both instances (and are a local admin).
Linked server setup between the instances with

I have a proc that is calling sp_makewebtask to generate a html-files.
Calling this proc to run a local query runs fine, but passing a query
issueing a remote query (using the linked server) returns an error:
Msg 18456, Level 11, State 1, Line 0
[Microsoft][SQL Native Client][SQL Server]Login failed for user 'sa'.
Msg 16805, Level 11, State 1, Procedure sp_makewebtask, Line 131
SQL Web Assistant: Could not execute the SQL statement.

Line 131 in sp_makewebtask is
SET @procname = 'xp_makewebtask'
and have hardly anything to do with the error.

A few lines above I find
EXECUTE @retval = sys.xp_makewebtask @outputfile, @query,
@username, @procname, @dbname,
@fixedfont, @bold, @italic, @colheaders, @lastupdated,
@HTMLheader,
@templatefile, @webpagetitle, @resultstitle, @URL, @reftext,
@table_urls, @url_query, @whentype, @targetdate, @targettime,
@dayflags, @numunits, @unittype, @rowcnt, @maketask, @tabborder,
@singlerow, @blobfmt, @nrowsperpage, @datachg, @charset, @codepage

which I guess is failing. But why?

Running the query directly in SSMS works fine.

I newly migrated this from a sql server 2000 environment where this
worked flawlessly. As far as I can see, the two environments are setup
identically when it comes to users running the services and logins on
the server.

I have struggeled with this for a while and would like to know if
anyone has a tip on what could be wrong.

Regards,



.

Relevant Pages

  • Re: freetexttable query never completes nor times out?
    ... SQL Server MVP ... This is a dev server so I hadn't even been ... When I get properties for the FT catalog, ... I just ran a similar query on ...
    (microsoft.public.sqlserver.fulltext)
  • SQL 2005 Express Connection Problem
    ... The remote site is not a company ... this remote site and try to log in I get this error: ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Single-Threading / Performance issues
    ... SQL Server MVP ... > Early this year my database was migrated from a single CPU, ... (when a long select query is running, ... server guy also sez it's not his problem. ...
    (microsoft.public.sqlserver.server)
  • Re: Advice Needed: Asynchronous Replication/Mirroring to central o
    ... Looking for a SQL Server replication book? ... geographically-dispersed remote locations. ...
    (microsoft.public.sqlserver.replication)
  • Re: SQL 2005 Express Connection Problem
    ... sql2005 support auto attaching a database in the connect string. ... The remote site is not a company ... An error has occurred while establishing a connection to the server. ... When connecting to SQL Server 2005, this failure may be caused by the ...
    (microsoft.public.dotnet.framework.aspnet)