Post reply

DTS and Temporary tables

  • June 23, 2006 at 9:33 pm

    #366486

    I'm using DTS to pull data from a Sybase SQL Server to a Microsoft SQL Server.  Because the Sybase server is owned by a vendor external to our company, I have limited permissions on the server.  I cannot create tables, procedures, functions...

    Currently I have many dts packages that pull snapshots of the tables I need.  I process the data on my server.  This consists of about 20 queries and involves 10 different tables.  And since I can't store data on the sybase server I have local tables I keep data in to also process the data against.  At the end of my procedure I link everything up to select my results.

    I was experimenting and found I could create temporary tables on the sybase server.  I modified my queries to work on the sybase server and removed the special processing specific to my server.  I excecuted the queries in a DTS Execute Query task and it finished with success.  This works but it doesn't get me the data.  Temporary tables do not work with the Transform Data task.  I keep getting the "Invalid pointer error".

    So with a blindfold on and my hands tied behind my back, is there any way I can get the data to my server?

    MS SQL Y2K

    Report Analyst, SCS

  • June 26, 2006 at 1:59 am

    #645779

    Hi

    Is it possible for you to speak to the vendor to allow you to make use of physical tables as temporary tables?

    Do you need to run some types of queries on the data before you import it into MS SQL? If not you can pull the data into MS SQL using DTS with the Sybase driver.

     

    Mike

     

  • June 26, 2006 at 6:55 am

    #645799

    I can work with them on the issue.  It took a month just to get a question answered about some indexes.

    I'm currently importing the data to the MS SQL with the Sybase driver.  This consists of taking snapshots of all of the data tables that I run reporting against.  This is millions of rows, gigabytes of data and a big headache.  Thankfully there are some date fields I can query against so I don't have to copy all of the entire tables everyday but rather update/insert rows based on the date field.  I've tried one mega query but it never finished and was very complicated to follow.

    Ideally I would like to get down to pull back just the data I needed and run it through some value added routines on my server.

    What about another approach?  Step 1 of my process identifies primary id of the records I need and contains foreign keys to a majority of the other tables.  Rather than dumping the data from step 1 to a temp table I could return it from the physical tables.  Is there a way to query a local table against the sybase tables through dts?  I have tried setting up a linked server to the sybase server but results were undesireable (tried to query a single record....let it run over the weekend and it never finished). 

     

     

  • June 26, 2006 at 11:09 pm

    #645969

    Ahhh ... I feel your pain. We're also dealing with extracting gigabytes worth of data from an external vendors system.

    At least you have date fields to limit your data transfer. We have no way of knowing what changed when so we're stuck with copying the entire table everyday

     

    --------------------
    Colt 45 - the original point and click interface

  • July 14, 2006 at 1:21 pm

    #649361

    I wish I had an answer for you. I am about to feel your pain with a project about to come online.

    I, too, get the "invalid pointer" error in a DTS package I am working on. I have registered the external MS SQL server in EM and I can run their stored proc and return results in Query Analyzer. In DTS when creating the package, it gets enough information from the sproc to be able to create a new table on my end, but when I run it, I get nothing but invalid pointer. Does anyone know what kind of pointer is invalid, where this is coming from?

  • July 14, 2006 at 2:33 pm

    #649375

    I was able to resolve my issue by using the tempdb.  It's nice to find out after almost 2 years that I have access to the tempdb. 

    I now have permanent tables in the tempdb.  My understanding from the DBA is that these will remain until the server is rebooted.  In my queries I check if the tables exist, if not they are created otherwise they are truncated. 

    I think the bottom line is that for the data pump results need to come from a physical table.  I've read where some people where able to fool it by creating a fake query that returns all of the correct columns and then do a disconnected edit and change the query to the stored procedure.  I haven't tried it myself so I can't provided any feedback on it.

    I would check to see if you have access to the tempdb.  From there you can create a table in the tempdb, insert the results from the stored procedure into that table, and then data pump your results back from that table. 

    I found this about storing your results from a stored procedure into a table:

    http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21312594.html

Viewing 6 posts - 1 through 6 (of 6 total)

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