Daily upload from A2007

  • Yup it's the print command... unless you hit a real weird "bug" bu using order by newid() in the concatenation. Otherwise it's the print issue.

  • JimS-Indy (4/6/2011)


    Just tried that. Exceeds nvarchar(max) limit....

    Probly need to execute one at a time.

    On second thought. It simply didn't print the whole command. Maybe it's a limitation of the print command....?

    PRINT has a limitation...but it could also be SSMS display limitations. Go to Tools > Options > Query Results > Results To Text and check th Max # of characters you are seup to receive in the output window.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Perfect, the execute statements work.

    Now, I need to execute the "drop... " statements, then the "truncate" statements, then upload the data from Access, then execute the "Add constraint" table alterations in order. That means preserving the Add constraint alterations until needed...

    OK, don't panic. I can get through this.... Are we having fun yet?

    Jim

  • What's the problem? You already told us exactly and clearly what needs to be done.

    Generate the drop commands and run them

    Truncate all tables

    Reimport (already working as I understand)

    Reload constraints... you can buid this one at the same time you do the drop... just exec later.

  • I for one am having fun 😉 ...sounds like you are almost there JimS...keep us posted!

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • @ninja -- I'm an old guy. Be nice to me.

    My issue is with scope. I lose the SS variables when the query is executed. What's the strategy for saving them?

    Another issue. I'm using the SSMS ExIm Wizard to create a package to import all the tables -- 103

    of 'em. I don't see where I can set a "keep identity" setting... and how do I use that in the SSIS package I'm creating to envelope it?

    Jim

  • I can't help you with SSIS because I've never used it... and never had to.

    You can all you need from SS side in a job in a single script. Maybe that can flush your limitations. :w00t:

  • JimS-Indy (4/6/2011)


    @Ninja -- I'm an old guy. Be nice to me.

    My issue is with scope. I lose the SS variables when the query is executed. What's the strategy for saving them?

    Another issue. I'm using the SSMS ExIm Wizard to create a package to import all the tables -- 103

    of 'em. I don't see where I can set a "keep identity" setting... and how do I use that in the SSIS package I'm creating to envelope it?

    The SSMS Import Export Wizard is a boiled down runtime of SSIS. I would recommend you use a full copy of SSIS.

    You can see "Keep Identity" as a properties of the OLE DB Destination component in your Data Flow.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • @ninja -- It'll take alot to flush my frustrations. I am enjoying learning, and I appreciate your help. Thank you.

    @3 -- I tried a dataflow task using full SSIS and it made me identify one table at a time to copy, so I ran the wizard and saved the output to a "package". Did I miss something?

    Jim

  • Nope...I think you;re good...misunderstood what you were doing. You can open the package you saved out of the I/E Wizard in Visual Studio and continue to build it out using the full version of SSIS.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Wow, guys, this is exactly what I needed...an actual objective that required me to learn some new stuff. Unfortunately, my client is still on SS2005, so I was stuck using VS2005, but no matter, I seem to have prevailed -- to a point.

    My package validates, and using a stored procedure I discovered (EXEC sp_MSForEachTable 'truncate TABLE ? '), I cleared all the tables. My foreign keys come and go as required. All that's left is the runtime error I get accessing the Access2007 back end. It's in production, so I suspect the data connector is somehown looking for exclusive access (not currently available....) I'm rerunning it now to see if I can capture the error better. So far, it just shows an undefined return and stops.

    Error desc "Cannot acquire connection from connection manager". Cx020020009

    Maybe an authentication issue. When I tell SS Management Studio to run a package, what user is he? How do I add that user to my folder's authorized group?

    Jim

Viewing 11 posts - 16 through 25 (of 25 total)

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