Need to query table in server using date from table in another server

Question

Post reply

Need to query table in server using date from table in another server

Ray Stille

SSC Journeyman

Points: 93
More actions
July 12, 2006 at 9:40 am

#76551

Hi guys
Need to build a DTS that queries a series of tables in server A, transforming the data to a table in server B...but using a date in a table in server C. I get an error "object db3.dbo.table3 does not exist".
I tried reading about Global Variables or Lookups, but could not make it work. Can you suggest something? Simplified query looks kike this...
SELECT field1,field2 FROM DB1.dbo.Table1
WHERE datecreated > (SELECT top 1 datecreated FROM DB3.dbo.Table3
ORDER BY datecreated DESC)

Viewing 9 posts - 1 through 8 (of 8 total)

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

Phil Parkin SSC Guru Points: 246993 More actions · Answer 1

You need to qualify your names further when accessing other servers.

Try using servername.DB1.dbo.Table1

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ray Stille SSC Journeyman Points: 93 More actions · Answer 2

Phil, you are everywhere!

Thanks for the advice. I'll try it. thanks

patrick tighe Newbie Points: 1 More actions · Answer 3

You may have to, or it would be tidier to set up a linked server under an alias that you can use a prefix for accessing it's dbo's

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 4

Why not do this:

Create a Global Variable called "MaxDate" or some similar name.

Create a connection to your table in server C.

Using a SQL Task, "SELECT MAX(DateCreated) FROM DB3.dbo.Table3" and assign the output to your MaxDate global variable.

When you build your SQL Statement to get the data from server A, use something like:

"Select * from DB1.dbo.Table1 WHERE DateCreated > ?" (without the quotes)

Click Parameters. Assign your MaxDate Global Variable to your parameter.

This should work without too much trouble.

If you try this through SSIS, that's a completely different beast altogether. I ended up writing an expression to build a query and put in the items I needed to change for some of my more troublesome queries. DTS definitely handled some things with greater ease. Not as powerful overall, but at times blissfully easy. 🙂

-Pete

Ray Stille SSC Journeyman Points: 93 More actions · Answer 5

Pete, your suggestion is more in the direction of what I was looking for (but my thanks to everyone who tried to help me).

1. I am embarrased to say I do not know how to create a Global Variable..

Ray

Erik Kutzler SSCarpal Tunnel Points: 4287 More actions · Answer 6

Right-click on an empty space of a DTS package and select Properties (or click the Package/Properties menu). Go to the Global Variable tab, enter a variable name, and select it's data type.

Peter Schott SSCrazy Eights Points: 9675 More actions · Answer 7

I think you can also create one directly on the Parameters section, but in general you're probably better off with the global variables section.

Also, watch out for the various properties, if you've selected an item, you can't get general package properties - just that item's properties. Click somewhere in the background and you should be able to manage the global variables.

Ray Stille SSC Journeyman Points: 93 More actions · Answer 8

Thank you and thanks to Erik for your advice on Global!!

That did it.

Good weekend to you all.