Error 7405 accessing SQL Server database by database link

I'm persistently getting the above error when executing a
stored procedure that uses a dabase link to another SQL
Server database.

I can run the procedure in MS SQL Server management studio
fine.

I'm setting ANSI_NULLS and ANSI_WARNINGS ON before creating
the procedure.


Procedure code:

SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE
[dbo].[SSP_WAR_RPT_MANUAL_RECLAIM]	@adt_sbi_from datetime,
						@adt_sbi_to   datetime,
						@as_vendor    varchar(15) output,
						@as_campaigns Char(1)
AS

BEGIN
	SET ANSI_NULLS ON
	SET ANSI_WARNINGS ON

	DECLARE @ls_vendor_name		Char(40)	-- Vendor Name
    select @as_vendor = VENDOR_CODE
      from SPIL.SPI.dbo.PART_MASTER
     where PART_NUMBER = '00000A00085'
END
0
Tony
8/26/2009 1:08:23 PM
sybase.powerbuilder.database 9855 articles. 1 followers. Follow

2 Replies
1413 Views

Similar Articles

[PageSpeed] 23

<Tony Clulow> wrote in message news:4a9533c7.7a3f.1681692777@sybase.com...
> I'm persistently getting the above error when executing a
> stored procedure that uses a dabase link to another SQL
> Server database.

It would help to include the complete error message, so that we are not 
forced to look up the error number.  Not everyone has these memorized.

>
> I can run the procedure in MS SQL Server management studio
> fine.
>
> I'm setting ANSI_NULLS and ANSI_WARNINGS ON before creating
> the procedure.
>
>
> Procedure code:
>
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
> GO
>
> SET QUOTED_IDENTIFIER ON
> GO
>
> ALTER PROCEDURE
> [dbo].[SSP_WAR_RPT_MANUAL_RECLAIM] @adt_sbi_from datetime,
> @adt_sbi_to   datetime,
> @as_vendor    varchar(15) output,
> @as_campaigns Char(1)
> AS
>
> BEGIN
> SET ANSI_NULLS ON
> SET ANSI_WARNINGS ON
>
> DECLARE @ls_vendor_name Char(40) -- Vendor Name
>    select @as_vendor = VENDOR_CODE
>      from SPIL.SPI.dbo.PART_MASTER
>     where PART_NUMBER = '00000A00085'
> END

The basic problem is that the connection settings in use at the time of 
execution are not correct.  Do not assume or rely on any defaults you might 
set at the server or database level - MS has seen fit to use different 
default settings within different client-level APIs.

To use a linked server, you need to make sure that your application 
establishes the correct settings for its connection.  Complicating this 
scenario is the use of a stored procedure.  If you search the newsgroups for 
"sql server heterogeneous queries" you will find previous discussions 
regarding this error.   Give the sql server documentation a look in the 
"create procedure" topic since it discusses the use of the ansi_nulls and 
quoted_identifiers settings in a stored procedure.

One way to enforce the correct connection settings is to use an execute 
immediate statement with the appropriate set statements.


0
Scott
8/26/2009 2:05:03 PM
Setting ansi_nulls and ansi_warnings on in powerbuilder (as
well as in the stored procedure) got round the error,
thanks.

And appologies for not expanding the error code...

> <Tony Clulow> wrote in message
> > news:4a9533c7.7a3f.1681692777@sybase.com... I'm
> > persistently getting the above error when executing a
> > stored procedure that uses a dabase link to another SQL
> Server database.
>
> It would help to include the complete error message, so
> that we are not  forced to look up the error number.  Not
> everyone has these memorized.
>
> >
> > I can run the procedure in MS SQL Server management
> > studio fine.
> >
> > I'm setting ANSI_NULLS and ANSI_WARNINGS ON before
> > creating the procedure.
> >
> >
> > Procedure code:
> >
> > SET ANSI_NULLS ON
> > SET ANSI_WARNINGS ON
> > GO
> >
> > SET QUOTED_IDENTIFIER ON
> > GO
> >
> > ALTER PROCEDURE
> > [dbo].[SSP_WAR_RPT_MANUAL_RECLAIM] @adt_sbi_from
> > datetime, @adt_sbi_to   datetime,
> > @as_vendor    varchar(15) output,
> > @as_campaigns Char(1)
> > AS
> >
> > BEGIN
> > SET ANSI_NULLS ON
> > SET ANSI_WARNINGS ON
> >
> > DECLARE @ls_vendor_name Char(40) -- Vendor Name
> >    select @as_vendor = VENDOR_CODE
> >      from SPIL.SPI.dbo.PART_MASTER
> >     where PART_NUMBER = '00000A00085'
> > END
>
> The basic problem is that the connection settings in use
> at the time of  execution are not correct.  Do not assume
> or rely on any defaults you might  set at the server or
> database level - MS has seen fit to use different  default
> settings within different client-level APIs.
>
> To use a linked server, you need to make sure that your
> application  establishes the correct settings for its
> connection.  Complicating this  scenario is the use of a
> stored procedure.  If you search the newsgroups for  "sql
> server heterogeneous queries" you will find previous
> discussions  regarding this error.   Give the sql server
> documentation a look in the  "create procedure" topic
> since it discusses the use of the ansi_nulls and
> quoted_identifiers settings in a stored procedure.
>
> One way to enforce the correct connection settings is to
> use an execute  immediate statement with the appropriate
> set statements.
>
>
0
Tony
8/27/2009 1:09:19 PM
Reply:

Similar Artilces:

New errors access MS SQL Server Database on (CREATE DATABASE and Batch SQL Statement)
------=_NextPart_000_002B_01C2F2CB.0C201FF0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable I'm running ActiveState perl 5.8.0 on a WIN2K box with MS SQL Server = 2000. Recently I updated DBD-ODBC to v1.05 and DBI to v1.35 Now I get the following errors executing some sql commands CREATE SQLSTMT:[CREATE DATABASE POMS_SER ON (NAME =3D 'POMS_SER_DAT', = FILENAME =3D 'd:\Database\POMS\POMS_SER_Data.MDF', SIZE =3D 10MB) LOG ON = ( NAME =3D 'POMS_SER_LOG', FILENAME =3D = 'd:\Database\POMS\POMS_S...

Error accessing database in App_Data that has linked tables from a database on another server
I have a database that lives on a Windows Server 2003 server (Server A). On another Windows 2003 server, I have my ASP.NET intranet application. In the App_Data folder of my ASP.NET application, I have a .mdb file that is basically a shell to hold links to the tables in the database file located on Server A. The reason why the master tables live in a database on Server A is because we access the data in those tables from projects outside ASP.NET, so I put the database on the server that made the most logical sense for universal access. Because I also want to access the data in those tables f...

Access Database and SQL Server database
Hi, I have an access database server and a sql server DB. The  Structure of both DB is same . The data is updated or inserted in access DB. I want to update SQL Server DB on a any event say button click. All updated data or new data in access DB should be reflected on SQL Server DB . Can anyone guide on the approach to be attempted. Thanks   Mark this as an answer if you find my it helpful.Thanks,Anubhuti ONe approach would be to use the System.Data.OleDb library and store connection strings for each database in your web.config file.  Each time you need ...

updating Sql database from linked Access database
I got thrown into a new project that is going to require me to update an SQL server database tables from an Access table on the backend of an Oracle database on another server. At the end of each day the Access dabase will be updated from the Oracle database. What I need to do, is when the Access database is updated I need to have the table in the SQL database automaticaly updated. When a new record is added to the Access table I need the new record added to the SQL table. When a record is deleted in the Access table I need to keep that record in the SQL table and set a field to a value (...

Converting Access database to the Sql Server database
how can I convert or transfer my Access database to SQl Server database ? Thanks in advance.HighOnCodingWanna get high! Try "the Upsizing wizard". Look in Access help...

COnverting SQL SERVER 2000 Database into Access database
Hi, How can I import SQL Database 2000 into Access database. I have created a SQl script file of my database. thanks, bye,HighOnCodingWanna get high! the SQL Script isn't that much use in this case - use DTS to export the DB from SQL to AccessRTFM - straight talk for web developers. Unmoderated, uncensored, occasionally unreadableJason Brown - MVP, IIS whats the DTS can u explain me please ??HighOnCodingWanna get high! Data Transformation Services - the primary way of miporting and exporting data to and from SQL Server - I assume you've installed the SQL Server Client...

moving an sql server 2005 database to a sql server 2000 database
I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?.....   this article explains it. http://searchsqlserver.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid87_gci1149585_tax301536,00.html hth,mcm pizzamaker74: I am trying to move a database which I wrote in SQL Server 2005 to a SQL Server 2000 database. I'm not sure the best way to do this....... Can anyone enlighten me?..... If both are in the same network or box just register the 2005 with the 2000 cr...

SQL Server Database File vs SQL Server Database -VWD Express
VWD Express 2008, SQL Server Express 2005. I have been using SQL Server Express with ASP and VB for some years - I just create a DB in Management Studio Express, have SQL Server set to 'SQL Server and Windows authentication', and use a connection string with 'sa' and a password (I never have managed to get it working with 'integrated security', but that is probably irrelevant to this post). So I have a list of DB's attached to SQL Server that I can see, attach, detach etc in MSE. I am getting started with .NET. Working through a VWD Express book tutori...

Is there a way to downsize a SQL Server database to an Access Jet database?
I won't bore you with the reasons why at this stage, but I have a need to downsize a SQL Server database to a Microsoft Access Jet database for use as a back end to an existing Access 2003 application. Is there any way to do this?  My SQL Server database sits on a webserver - a shared SQL Server 2005 facility provided by my Windows hosting ISP. I only have SQL Server Express and SQL Server Management Studio Express on my own PC (plus Visual Web Developer 2008 Express). David Hi David, As far as I know, there is no such tool to migrate from SQL Server to Access.I think we c...

Link FoxPro Database table to SQL Server Database table
I have LAN Server in witch Visual Foxpro Database reside.I have another remote server in witch SQL Server 2005 installed.I want to link Foxpro table to SQL Server Database Table.Is it possible? Can please someone help? Thanks MS...

Migrating sql server 2005 express database to sql server 2005 database
Hi, I have an application developed using VWD and sqlserver express database. The express database is turning out to be small in size and we need to migrate to larger sqlserver 2005 database. What are the steps for this migration, please list in detail. Regards, Sandyhttp://www.thequinn.infohttp://www.sksdataservices.comhttp://www.infobasket.info Hi Sandy, To move a database from SQL Express to SQL 2005, you can use the following steps. 1. If your database file is attached to the server instance, you will need to detach it first using SQL management studio. If it is under App_Data folde...

how to access other database in this database ?
Hi,Everyone,I used to use oracle 8i,but now for some reasons,I must change to ASE11.5,at the beginning,I don't know how to access tables among databases,in oracle, if I grant "select" to some user,this user will access other user's table in username.tablename format,I want to know how to implement this functions,please help me! thanks! Very similar in sybase ASE. grant SELECT on <user>.<tbl> to <newuser> for new user: select * from <user>.<tbl> Patrick wrote: > Hi,Everyone,I used to use oracle 8i,but now for some rea...

Can microsoft SQL Server 2005 database upgrade to microsoft SQL Server 2008 database?
I have a microsoft SQL Server 2005 database, I don't know how to upgrade the 2005 database to 2008 database, I try to use SQL Server Management Studio Express 2008 to open the 2005 database, it's OK, does it mean that the 2005 database have been upgraded to 2008 database? BTW, is SQL Server 2005 database compatible with SQL Server 2008 database completely?SuperCool Multiple ZIP - A utility to unzip multiple files and work with multiple zip filesSuperCool Random Number Generator Yes. It is compatible. Anyhow go through this url for more informationhttp://msdn.microsoft.com/en-us/li...

-934: ERROR! Unable to start specified database: Server must be upgraded to start database C:\FUELTAX\DATABASE\PCFTAX.DB
Our program which uses runtime engine for version 9 is getting the following error -934: ERROR! Unable to start specified database: Server must be upgraded to start database. Does anyone know what might have happened. It was working fine for months and all of the sudden we are getting this error now. Thanks, Igor Vodo. This error is a result of the database server being at a lower version than the database file format. Something has changed in your environment. Do you have multiple installs of SQL Anywhere on your machine (for example ASA 9.0.1 and 9.0.2)? If s...

Web resources about - Error 7405 accessing SQL Server database by database link - sybase.powerbuilder.database

Highest Percentage Of Opera Mini Users Accessing Facebook? Macau
If you were asked to guess which country had the highest percentage of users of Opera mobile Web browser Opera Mini users accessing Facebook ...

International Users Accessing Facebook Places Through US VPN Accounts
By using a virtual private network (VPN) hosted in the United States, Facebook users from around the world are accessing Facebook Places. The ...

What are some alternatives to Yodlee for accessing bank information?
Clay Loveless , Founder, Jexy. Co-founder, Mashery. Founder, Jexy. Co-founder, Mashery.

Cloud Console - Accessing files in cloud storage for iPad on the iTunes App Store
Get Cloud Console - Accessing files in cloud storage on the App Store. See screenshots and ratings, and read customer reviews.

Meryl Streep On Accessing The Characters Within - YouTube
Meryl Streep talks about the importance of an actors work representing their ability. CONNECT WITH AFI: http://facebook.com/AmericanFilmInstitute ...

Accessing the Future Fund a sensible proposal, says super expert Daryl Dixon
As portfolios go, the Future Fund's asset allocation is concentrated in higher-risk investment, including equities and alternative assets.

Accessing my Super
I'm 57 - can I access my super?

Australia, US accessing Indonesian telephone data, leaked documents show
Newly disclosed documents from former US intelligence contractor Edward Snowden have revealed that Australian intelligence efforts against Indonesia ...

Euthanasia advocate Philip Nitschke detained at Melbourne Airport, accuses Customs officials of accessing ...
Euthanasia advocate Philip Nitschke has accused Customs officials of accessing hundreds of patients' sensitive records on his laptop after detaining ...

Frances Abbott scholarship: Sydney woman pleads guilty to accessing Whitehouse Institute records on PM's ...
A Sydney woman who leaked the student records of Tony Abbott's daughter pleads guilty. A Sydney woman who leaked the student records of Prime ...

Resources last updated: 1/28/2016 8:57:58 PM