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'

 

[bborissov]

USE DataRep
GO

CREATE TABLE dbo.PARTICIPANT_DEMOGRAPHIC

That is NOT enough. You should add fields and their types.

But if table didn't exists you could use INTO clause:

USE DataRep
GO

SELECT DISTINCT PD.* INTO dbo.PARTICIPANT_DEMOGRAPHIC
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' "

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

I still get the same error where it does not see the 'DataRep' database on my machine, do I need to add the path since so the outside server can connect? Thank you.

Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'DataRep'. No entry found with that name. Make sure that the name is entered correctly.
Msg 262, Level 14, State 1, Line 2
CREATE TABLE permission denied in database 'AH1PD01'.

 

[bborissov]

Did you said that there is NO DB named DataRep on you server?
If so, create it.
Also you must have linked server to your remote server, so you could query one or more tables from there.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

Yes there is a DB named DataRep on my machine, I tired to have it as a "Linked Server" but get denied via rights. I have access to the host DB and can create temp tables and run queries. I was looking for a way for me to run the query, which I can do, then have the results moved to the database on my local machine. Maybe I should be looking at this a differnt way. I tired "Export" which works but since there are over a 110 million rolls (I need only 145,422) a lot of time is just wasted doing the export then filtering. Also how can I export but not do a "Link Server"? Is it a limitation of MS SQL Server Management Studio for SQL Server 2008 R2? Or maybe I need to study up more on how to do a link server? Any suggestion are more than welcome and thank you for your patience and time.

Thank you.

 

[SQLBill]

First, who owns DataRep?

Second, can you open it in SSMS? If not, then you don't have access to it.

Lastly, go to Security>Logins and find your login. Right click on your login and go to Properties. Does your login have access to DataRep?

Before you do anything else, you need to make sure you have access to DataRep.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Stangleboy]

'DataRep' is my database on my local machine, I created and defined and I also have added 4 tables already from the host server (using a different method because the tables were small -- "Export"). The host server that has the data is 'AH1PD01', I have the read rights to.

First- Yes.
Second- Yes.
Third- Yes.

Thank you.

 

[bborissov]

if you have ALL of these that you can't get this error message instead if you are connected to different server;
So,
what is the result of this:

SELECT @@SERVERNAME

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

On my machine- "DCSS-LL-001941\SQLEXPRESS"
On the host server- "Burma

 

[bborissov]

O, another thought
HOW are you sure that this DB is presented on your SQL Server?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

I am not, I am new to SQL and decided to learn using new MS SQL Server Management Studio that came out two months ago, allowing database on my own box (not a full SQL system) up to 10GB per database. Plus SQL reporting, etc.

 

[bborissov]

Ok.
They try this:

DECLARE @myname varchar(200)
IF @@SERVERNAME = 'DCSS-LL-001941\SQLEXPRESS
   BEGIN
        USE DataRep
        SELECT DISTINCT PD.* 
               INTO dbo.PARTICIPANT_DEMOGRAPHIC
        FROM LinkedServerName.RemoteDB.dbo.CASE_CAS As CC
        INNER JOIN LinkedServerName.RemoteDB.dbo.CASE_PARTICIPANT As CP
           ON CC.C_CASE_ID = CP.CP_CASE_ID
        INNER JOIN LinkedServerName.RemoteDB.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

not tested!!!!

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

Similiar error, see below. Since the data and table that I want the data from is the host server with 'AH1PD01' I ran the query on the host server not my machine, isn't that correct? Thank you.

Msg 911, Level 16, State 1, Line 4
Could not locate entry in sysdatabases for database 'DataRep'. No entry found with that name. Make sure that the name is entered correctly.

 

[bborissov]

IF @@SERVERNAME = 'DCSS-LL-001941\SQLEXPRESS'
   BEGIN
       USE [master]
       IF NOT EXISTS (SELECT name 
                      FROM sys.databases
                      WHERE  name = N'DataRep')
          BEGIN
              print 'There is no DB named DataRep on you server '+@@SERVERNAME
          END
ELSE
   print 'You are not connected to the right server. Your Server name is '+@@SERVERNAME

BTW what is the collation of your SQL Server?Can it be Case-sensitive?

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

I had an usual error and I cannot see any discrepancy:

Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '@@SERVERNAME'.

 

[bborissov]

Sure you would
I forgot to close one of the BEGINs:

IF @@SERVERNAME = 'DCSS-LL-001941\SQLEXPRESS'
   BEGIN
       USE [master]
       IF NOT EXISTS (SELECT name
                      FROM sys.databases
                      WHERE  name = N'DataRep')
          BEGIN
              print 'There is no DB named DataRep on you server '+@@SERVERNAME
          END
   END -- This one :-)
ELSE
   print 'You are not connected to the right server. Your Server name is '+@@SERVERNAME

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Stangleboy]

I didn't even see that.

On my database runs great.

Now my prior code was running on the other database, since that is where the data I need to retrieve is located.

 

[Stangleboy]

Sorry should have shown the results.

Command(s) completed successfully.

 

[SQLBill]

When you name your database in the script, are you using the full name or just DataRep? If just DataRep, then it is looking for that on the server where you are running the command. Try:

[DCSS-LL-001941\SQLEXPRESS].DataRep

You will need the square brackets since the name has hyphens (-).

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[Stangleboy]

Here is what I just tried and the error I received:
USE [DCSS-LL-001941\SQLEXPRESS].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'

Error:
Msg 911, Level 16, State 1, Line 1
Could not locate entry in sysdatabases for database 'DCSS-LL-001941\SQLEXPRESS'. 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'.

Thank you in advanced.