Solved

How to use select with linked server issue

Posted on 2011-03-12
4
778 Views
Last Modified: 2012-05-11
I need to use a select statement to query a linked server. The following does not work, probably because of the embedded spaces in the database name.
Doesn't work:
select * from [EOSK\DMZ].[BIG JOB COSTS].[dbo].[DNUM]

Open in new window

Does work:
select * from [EOSK\DMZ].[BIG_JOB_COSTS].[dbo].[DNUM]

Open in new window



Why? The item with spaces in the table name is not under my control. Anything I can do.

Bob
0
Comment
Question by:bob_mechler
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
agarwalrahul earned 250 total points
ID: 35120169
0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 35121456
>Doesn't work:
error message?

actually, the [ ] should handle the issue with the spaces.
what kind of linked server is it? I presume sql server ...
how have you configured the linked server exactly (options ...) ?
0
 

Author Comment

by:bob_mechler
ID: 35122851
Linked server type
SQL Server 2008 64 bit full version. I am not administrator on this database but I am dbo on one database..
I'm using SQL 2008 32 bit Express to do the linking.
Tried the sql server radio button and used oledb.
Once the test connection reported success I then tried the select statement in Express in sql 2008 management studio using the four part connection shown above.

sql express = a
sql 64 bit remote = b

The server b database is replicated from the production database. Server b is in a DMZ zone.
Server b was working fine as the source of data for the website we installed last week.

The other day, I started getting 'Fatal error 9001' from the website as it tried to login to server b. Nothing changed as far as my settings were concerned.

I deduced that since I was running an asp.net 1.1 web site with meta data support in IIS 7 that if I could just copied the 8 tables I needed periodically into emptied tables of the exact same structure on server a that then I could point my web site to server a which is on the same computer as the web site.

I could export one file at a time but when sql express is on either side, import/export I would get a message that said 'run immediately' but would not allow the job to be saved so I could make a job out of it.

I'm looking today at using OPENROWSET since it is infrequent but don't have any assurance that it will work.

Bob
0
 

Author Closing Comment

by:bob_mechler
ID: 35142776
The DBA also received the 9001 error so he believed me. We changed the database name to have underscores instead of spaces. Don't know what he did but it is now working fine.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql query 34 35
how to check the index used by a MS SQL queries or SP 7 39
DATETIMEOFFSET feature 1 33
sql query help 4 45
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now