September 30, 2009 at 9:47 am
I have a SP that uses an openquery that goes against an oracle table to populate my SQL 2005 table. I needed to add more columns to the SQL table, but when I tweak the SP to add the new columns to update to and the openquery fields, I hit the 8000 char limit. I've read that openquery can't use variables (and the workaround for that), but just can't seem to put everything together. I'm not sure where to go with this. I have about 20 additional fields to update with this SP. I guess I just need some pointers. My code is in the attached .doc.
September 30, 2009 at 10:35 am
Not a really solid solution but you could alias your tables with a lot shorter name. For example, PERS_SVCMBR_TBL a
then your select would lose around 14-15 characters per line.
Not the most elegant but it might get you under that 8k threshold without tons of extra work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2009 at 11:02 am
Any reason to not use more than one OpenQuery and just join them together?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 30, 2009 at 11:24 am
I think I'll give slang's suggestion a try. It seems like it will work. I had thought about doing the multiple openqueries, but I'd still have to juggle multiple joins due to how some of the tables are structured. And that way seems like alot of extra work.
I appreciate the info...thanks.
September 30, 2009 at 12:11 pm
Just keep in mind that data compression (which is essentially what you're looking at there) only goes so far, while multiple queries can scale infinitely to as many tables and columns as you want.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 6, 2009 at 9:41 am
david.l.mulholland (9/30/2009)
I think I'll give slang's suggestion a try. It seems like it will work. I had thought about doing the multiple openqueries, but I'd still have to juggle multiple joins due to how some of the tables are structured. And that way seems like alot of extra work.I appreciate the info...thanks.
could you create views to handle the table joins, and then reference those instead?
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
October 9, 2009 at 10:20 am
Couldn't SSIS handle this with little to no trouble? With a little work you can even join the tables together in it...
October 17, 2009 at 8:55 pm
david.l.mulholland (9/30/2009)
I have a SP that uses an openquery that goes against an oracle table to populate my SQL 2005 table. I needed to add more columns to the SQL table, but when I tweak the SP to add the new columns to update to and the openquery fields, I hit the 8000 char limit. I've read that openquery can't use variables (and the workaround for that), but just can't seem to put everything together. I'm not sure where to go with this. I have about 20 additional fields to update with this SP. I guess I just need some pointers. My code is in the attached .doc.
The leading spaces in the "dynamic" portion of the OpenQuery are killing you. Slam everything to the left.
The other thing I don't understand is why are you using OpenQuery for this? Why not just use a stored procedure?
--Jeff Moden
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply