Today’s T-SQL Headache

Today I came into the office in hopes of re-creating the below report using SQL Server. First I thought it might be nice to upload a table into my local DB that had the list of departments at the location requested for creating the patient list.

COPD Report Criteria

First, I couldn’t connect to the local server. It was basically the same error as I got on my windows 10 computer at home, except this was my windows 7 computer at work. Thankfully, a facebook friend’s dad was able to help me figure out the issue. It turned out I needed to turn on TCP/IP and Named Pipes in the SQL Server Configuration Manager, and then restart the SQL Server Service. Yay!

But I still can’t seem to be able to get a query to work which uses tables in my local db and tables in the big server. I read that I needed to create a linked server, so I liked the big server to my local database in the “Server Objects” but I still haven’t found how to write a query in which fields from tables in both places don’t end up with invalid object names. So today – today was mostly frustration. The possibilities are exciting and I learned how to do some neat stuff, but I didn’t get to do the things I had hoped to do.

 

Advertisements

3 thoughts on “Today’s T-SQL Headache

  1. The format is ServerName.DatabaseName.Schema.TableName. Let’s say the linked server is identified by it’s IP Address 123.456.78.90. It has a database named MedicalDB, the schema is dbo, and the table you want is PatientData. You have a local table named PatientInfo and both tables have PatientID as the primary key. I’m aliasing the table names to simplify the code.

    SELECT PD.*
    FROM [123.456.78.90].MedicalDB.dbo.PatientData PD
    INNER JOIN PatientInfo PI ON PD.PatientID = PI.PatientID

    Liked by 1 person

  2. Okay, so from a Temp File on the big server, I had trouble joining with the local server table:

         SELECT PD.
         FROM [BIGSERVER]..##COPDPtList PD
         INNER JOIN [SQLEXPRESS2014].[COPD].[dbo].[zipcodes] Zip on PD.ZIP = ZIP.Zip;

    Database name ‘BIGSERVER’ ignored, referencing object in tempdb.
    Database name ‘BIGSERVER’ ignored, referencing object in tempdb.
    Msg 208, Level 16, State 0, Line 1
    Invalid object name ‘##COPDPtList’.


    I’m thinking these temp tables are going to be a bit problematic.

    I will try a query that doesn’t involve the tempdb of either server:

     SELECT PD.*
     FROM [BIGSERVER].CLARITY.dbo.PATIENT PD
     INNER JOIN [COPD].[dbo].[PatientLevelData] PLD on PD.Pat_MRN_ID = PLD.MRN;
    

    This worked!!! Thank you so much Joe!!!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s