Copy Selected Records from Another Server/Database Error 4

[Stangleboy]

I am sorry to post a second time but I think the code at the end is close, the error I get is it cannot find my database on my machine:
(Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'DCSS'. No entry found with that name. Make sure that the name is entered correctly.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'INSERT'.)-->

--Original--
I am trying to copy select records from a table on another server to my local computer using MS SQL Server Management Studio for SQL Server 2008 R2. The table does not exist on my machine and I would like to create and then copy the table structure (indexes, etc.) and table data. I only have read rights to the host server and cannot make views, but I can make temporary tables if this helps. I have been searching the forum/web and found pieces here and there but I cannot seem to incorporate them together to get my end results. One issue is that there are over 11millions records and 42 fields that are in the table so when I try to use the "Export/Import" function the process dies on my computer (and I have a quad core 64-bit system). Any help will be greatly appreciated.

My goals are:
1. Have it run as one script.
2. Create the new table in the database.
3. Copy table structure and data for select records (see code below).

This host database name is "AH1PD01" and I have the server IP if needed for the code (Ithink this is my main issue is writing the code to use the full server name and database name correctly). My database name on my local machine is "DataRep". Thank you again in advance.

"USE DataRep
GO

CREATE TABLE dbo.PARTICIPANT_DEMOGRAPHIC

INSERT INTO dbo.PARTICIPANT_DEMOGRAPHIC

SELECT DISTINCT
PD.*
FROM
CASE_CAS As CC INNER JOIN
CASE_PARTICIPANT As CP ON CC.C_CASE_ID = CP.CP_CASE_ID INNER JOIN
PARTICIPANT_DEMOGRAPHIC As PD ON CP.CP_PRTCP_ID = PD.P_PRTCP_ID
WHERE
CC.C_MNG_CNTY_FIPS_CD = '071'
AND CC.C_PND_CLS_STAT_CD <> 'ACT'
AND CC.C_CRNT_STAT_CD = 'OPN'

 

[gmmastros]

What do you get when you run this?

Select @@ServerName

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

Burma

 

[gmmastros]

When you open SQL Server Management Studio (SSMS) you are effectively logging in to a server. You can have multiple connections open (to multiple servers). But... when you are in a query window, you are absolutely ONLY connected to a single server.

[tt]
USE [DCSS-LL-001941\SQLEXPRESS].DataRep
[/tt]

The use command only accepts a database name. You cannot put the name of the server in front of the database name and expect it to do anything (other than cause an error). If you want to use the DataRep database, you first need to connect to the server that the database is attached to, and then you can use it.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

Yes, but the data I need to retrieve is not in that database (DataRep) it is in Burma (AH1PD01). Can I run the query from the database I want the data to populate (DataRep) and have the query portion point to the host(Burma)?

 

[gmmastros]

What do you get when you run this?

sp_linkedservers

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

On my box (DataRep)- error:
Msg 102, Level 15, State 1, Procedure sp_addlinkedserver, Line 70
Incorrect syntax near '%'.

On the host server (Burma- AH1PD01)- error:
Msg 208, Level 16, State 6, Procedure sp_addlinkedserver, Line 53
Invalid object name 'sys.sp_addlinkedserver'.

 

[gmmastros]

Connect to your computer and run this:

SELECT Top 1 *
FROM   [Burma].[AH1PD01].dbo.CASE_PARTICIPANT

What happens?

Just to be clear.... this assumes that the data you are trying to get is on a server named Burma, a database named AH1PD01, and a table named CASE_PARTICIPANT. If this is not correct, please adjust accordingly.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

By the way.... this is not the final query. This is just a quick test to see if we can get the data we need.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

OLE DB provider "SQLNCLI10" for linked server "Burma" returned message "Invalid authorization specification".
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI10" for linked server "Burma" reported an error. Authentication failed.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "SQLNCLI10" for linked server "Burma".

 

[gmmastros]

Based on the error message, I would guess that the linked server exists, but the login specified is missing or invalid. I can't really check right now, but I suggest you google for "SQL server add linked server login". Whatever credentials/login you use for the other server should be used to configure your linked server login.

When I am at the office tomorrow, I may be able to help more (unless someone helps you bfore then).

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

I think you will want to add a linked server login so that you can connect remotely to the other server. Something like...

exec sp_addlinkedsrvlogin 'Burma','true',null,'[!]username[/!]','[!]password[/!]'

You should run this code on your local SQL Server. Uusername and password should be the login credentials you use for the remote server (the one with the data).

After doing this, can you re-run the previous query I suggested. Again... run this on your local sql server.

SELECT Top 1 *
FROM   [Burma].[AH1PD01].dbo.CASE_PARTICIPANT

Please post any error messages and/or non-error messages.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Stangleboy]

It looks like it connects using the link server Stored procedure, I get:

Command(s) completed successfully.

Then I ran this code from my machine with the 'DataRep' code from a different query tab:

DECLARE @myname varchar(200)
IF @@SERVERNAME = 'BURMA'
BEGIN
USE DataRep
SELECT DISTINCT PD.*
INTO dbo.PARTICIPANT_DEMOGRAPHIC1
FROM BURMA.AH1PD01.dbo.CASE_CAS As CC
INNER JOIN BURMA.AH1PD01.dbo.CASE_PARTICIPANT As CP
ON CC.C_CASE_ID = CP.CP_CASE_ID
INNER JOIN BURMA.AH1PD01.dbo.PARTICIPANT_DEMOGRAPHIC As PD
ON CP.CP_PRTCP_ID = PD.P_PRTCP_ID
WHERE CC.C_MNG_CNTY_FIPS_CD = '071'
AND CC.C_PND_CLS_STAT_CD <> 'ACT'
AND CC.C_CRNT_STAT_CD = 'OPN'
END
ELSE
print 'You are not connected to the right server. Your Server name is '+@@SERVERNAME

And it gets farther then every before but then this error:

OLE DB provider "SQLNCLI10" for linked server "BURMA" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "BURMA" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 126, Level 16, State 1, Line 0
VIA Provider: The specified module could not be found.

Thank you for your patience and help. This closer then I have been for 1 week.

 

[gmmastros]

After a little googling, it appears that you may have a network issue, but it's hard to tell for sure.

I did a google search on this:

[google]OLE DB provider "SQLNCLI10" for linked server "BURMA" returned message "Login timeout expired"[/google]

Take a look at some of the replies and see if anything applies.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom