Stored Procedures

Question

Post reply

Stored Procedures

Flavio

Right there with Babe

Points: 793
More actions
June 10, 2003 at 2:30 am

#82123

I have this problem
I execute a stored procedure by Query Analyzer.
The execution of stored procedure failed with message:
Server: Msg 7411, Level 16, State 1, Line 1
Server <SERVER NAME> is not configured for DATA ACCESS.
What means??
Help me please.
Thanks
Flavio

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Andy Warren SSC Guru Points: 119902 More actions · Answer 1

Are you referencing a linked server? It has a property you have to enable to allow data access.

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Flavio Right there with Babe Points: 793 More actions · Answer 2

Thanks AW, but....

I don't understand like resolving the problem!

I execute the stored procedure in local SQL Server.

In the code of the stored procedure, I use (for example), to reference a table:

INSERT INTO TABLE_NAME_1

select ....FIELDS_NAME, .... from [SERVER_NAME].[DB_NAME].[dbo].[TABLE_NAME_2]

SERVER_NAME is the name of my local SQL Server.

Executing the procedure the errore message is the same

Can You help me??

Thanks

Flavio

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Hello Flavio,

quote:
select ....FIELDS_NAME, .... from [SERVER_NAME].[DB_NAME].[dbo].[TABLE_NAME_2]

does SELECT * FROM [dbname].dbo.table_name_2 also not work?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Flavio Right there with Babe Points: 793 More actions · Answer 4

Hello Cheers and thanks for your attention

Now

If I execute by Query Analyzer the statement:

SELECT * FROM [dbname].dbo.table_name_2

work, It's OK!

instead

SELECT * FROM [serverName].[dbname].dbo.table_name_2

not work!

Bye bye

Flavio

Flavio Right there with Babe Points: 793 More actions · Answer 5

Flavio

Right there with Babe

Points: 793

June 10, 2003 at 7:05 am

#460555

Ops!!

Frank, not Cheers!!!

Excuse me, pardon!

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Hello Flavio,

de niente, buoa fortuna!

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

mimorr SSCarpal Tunnel Points: 4850 More actions · Answer 7

sp_serveroption @server = 'SERVERNAME'

,@optname = 'DATA ACCESS'

,@optvalue = 'TRUE'

Execute this on the machine you want to remotely access.

Michelle

Flavio Right there with Babe Points: 793 More actions · Answer 8

OK Frank, but

Do you know, because

SELECT * FROM [dbname].dbo.table_name_2

work

instead

SELECT * FROM [serverName].[dbname].dbo.table_name_2

not work ??

Bye

Bye bye

Flavio

Frank Kalis SSC Guru Points: 111183 More actions · Answer 9

Hello Flavio,

quote:
SELECT * FROM [dbname].dbo.table_name_2
work
SELECT * FROM [serverName].[dbname].dbo.table_name_2
not work ??

see BOL, syntax conventions, Transact-SQL for details. [serverName] is only used when you try to access a linked or remote server, which isn't the case for you.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Flavio Right there with Babe Points: 793 More actions · Answer 10

Thanks Mimorr (Michelle)

I executed your code by Query Analyzer

and then, I tried to execute one SELECT statement like example.

The problem is resolved!!

Now, I think I'll execute my stored procedure, and I'm going to see in the Books on line

the procedure sp_serveroption.

Thanks Michelle

I can offer you a pizza? 🙂

Thanks at all !!

Bye bye

Flavio