- Home
- Microsoft
- Development
- MS SQL Server
- DTS
- Data Pump task, error when exec sp
Advertisement
- 1. chapmandew 73,360
- 2. acperkins 41,384
- 3. nmcdermaid 29,225
- 4. brejk 25,200
- 5. cs97jjm3 20,048
- 6. PedroCGD 19,230
- 7. angelIII 17,110
- 8. dready 12,500
- 9. bhess1 9,700
- 10. Zberteoc 9,350
- 11. JimFive 8,375
- 12. mark_wills 8,000
- 13. dbbishop 7,300
- 14. ptjcb 7,120
- 15. paulop1975 7,100
- 1. acperkins 309,633
- 2. angelIII 157,526
- 3. arbert 149,684
- 4. nmcdermaid 141,942
- 5. chapmandew 73,360
- 6. EugeneZ 57,168
- 7. matthews... 42,304
- 8. aneeshat... 39,799
- 9. ptjcb 38,638
- 10. rboyd56 34,850
- 11. bhess1 29,280
- 12. Sirees 27,902
- 13. twoboats 27,515
- 14. jltoops 27,275
- 15. cs97jjm3 26,578
| 04.25.2008 at 11:38AM PDT, ID: 23354494 |
|
[x]
Attachment Details
|
||
|
[x]
The Solution Rating System
|
||
|
With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.
Your Input Matters If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support. Thank you! |
||
Data Pump task, error when exec sp
Zones:
MS SQL DTS, MS SQL Server
SQL2K DTS package, I'm just trying to execute a stored proc, and export that output to a tab-delimeted text file.
UDL as the data source, no problems there.
Data Pump Task connecting source to a text file.
Source SQL Query: exec my_stored_proc_name 'a date' This SP has SET NOCOUNT ON and SET ANSI_WARNINGS OFF
Preview button click: Returns records, just like when ran in QA, no problems here.
Destination tab, Define Columns button click, **** I get the error message in the attached image. ****
#GLACCOUNT is one of the temp tables used in the SP, and there is a DROP TABLE #GLACCOUNT at the end
Thanks in advance.
Jim
UDL as the data source, no problems there.
Data Pump Task connecting source to a text file.
Source SQL Query: exec my_stored_proc_name 'a date' This SP has SET NOCOUNT ON and SET ANSI_WARNINGS OFF
Preview button click: Returns records, just like when ran in QA, no problems here.
Destination tab, Define Columns button click, **** I get the error message in the attached image. ****
#GLACCOUNT is one of the temp tables used in the SP, and there is a DROP TABLE #GLACCOUNT at the end
Thanks in advance.
Jim
Attachments:
Question Stats
Zone:
Microsoft
Question Asked By:
jimhorn
Solution Provided By:
chapmandew
Participating Experts:
1
Solution Grade:
A
Views:
8
Translate:
Loading Advertisement...
Loading Advertisement...
| Microsoft |
- Internet Protocols
- Applications
- Development
- OS
- Hardware
- Windows Security
| Apple |
- Operating Systems
- Hardware
- Programming
- Networking
- Software
| Internet |
- Search Engines
- File Sharing
- WebTrends / Stats
- Spy / Ad Blockers
- Web Browsers
- New Net Users
- Web Development
- Chat / IM
- Anti Spam
- Web Servers
- Anti-Virus
- Email Clients
| Gamers |
- Tips
- Online / MMORPG
- Puzzle
- Emulators
- Action / Adventure
- Role Playing
- Consoles
- Game Programming
- Strategy
- Sports
- Misc
- Computer Games
| Digital Living |
- Hardware
- New Net Users
- New Users
- Software
- Digital Music
- Gaming World
- Home Security
- Apple
- Networking Hardware
| Virus & Spyware |
- Vulnerabilities
- IDS
- Encryption
- Anti-Virus
- Operating Systems Security
- Software Firewalls
- WebApplications
- Cell Phones
- Operating Systems
- Internet
- Hardware Firewalls
| Hardware |
- Handhelds / PDAs
- Displays / Monitors
- Components
- Networking Hardware
- Peripherals
- Laptops/Notebooks
- Storage
- Servers
- Desktops
- New Users
- Misc
- Apple
| Software |
- System Utilities
- Industry Specific
- Network Management
- Photos / Graphics
- Page Layout
- VMWare
- Misc
- Web Development
- OS
- CYGWIN
- Voice Recognition
- Message Queue
- Quality Assurance
- Security
- Firewalls
- MultiMedia Applications
- Development
- Database
- Office / Productivity
- Business Management
- OS/2 Apps
- Server Software
- Internet / Email
| ITPro |
- OS
- Storage
- Encryption
- Operating Systems Security
- Apple Hardware
- Laptops & Notebooks
- Servers
- Networking Hardware
- Peripherals
- Devices
- Displays / Monitors
- WebTrends / Stats
- Search Engines
- Firewalls
- WebApplications
- IDS
- Vulnerabilities
- Email Clients
- File Sharing
- Spy / Ad Blockers
- Web Browsers
- Web Servers
- Networking
- Anti-Virus
- Chat / IM
- Anti Spam
| Developer |
- Web Servers
- Web Browsers
- Game Programming
- Dev Tools
- Industry Specific
- Office / Productivity
- Database
- CYGWIN
- Web Development
- Search Engines
- File Sharing
- WebTrends / Stats
- Programming
- Content Management
- Application Servers
- Protocols
| Storage |
- Removable Backup Media
- Storage Technology
- Servers
- Grid
- Remote Access
- Backup / Restore
- Misc
- Hard Drives
| OS |
- Miscellaneous
- Security
- Development
- Linux
- VMWare
- MainFrame OS
- Unix
- Apple
- OS / 2
- AS / 400
- BeOS
- Microsoft
- VMS / OpenVMS
| Database |
- Oracle
- Miscellaneous
- MySQL
- Software
- Sybase
- Contact Management
- PostgreSQL
- Data Manipulation
- Clarion
- InterSystems Cache
- Siebel
- MUMPS
- OLAP
- SQLBase
- SAS
- GIS & GPS
- 4GL
- Berkeley DB
- DB2
- Informix
- Interbase / Firebird
- FoxPro
- Reporting
- LDAP
- Filemaker Pro
- MS SQL Server
- dBase
- MS Access
| Security |
- Misc
- Web Browsers
- Software Firewalls
- Operating Systems Security
- File Sharing
- Spy / Ad Blockers
- Vulnerabilities
- WebApplications
- IDS
- Anti-Virus
- Encryption
- Anti Spam
- Email Clients
- VPN
- Chat / IM
| Programming |
- Editors IDEs
- Installation
- Handhelds / PDAs
- Multimedia Programming
- System / Kernel
- Algorithms
- Game
- Signal Processing
- Project Management
- Open Source
- Database
- Misc
- Languages
- Processor Platforms
- Theory
| Web Development |
- Scripting
- Blogs
- Web Servers
- Software
- Search Engines
- Web Graphics
- Images
- Internet Marketing
- Images and Photos
- Components
- Document Imaging
- Web Languages/Standards
- Illustration
- WebApplications
- Fonts
- WebTrends / Stats
- Authoring
- Digital Camera Software
- Miscellaneous
| Networking |
- Protocols
- Apple Networking
- Network Management
- Message Queue
- Application Servers
- Content Management
- File Servers
- Email Servers
- Misc
- Java Editors & IDEs
- Wireless
- Networking Hardware
- Backup / Restore
- System Utilities
- ISPs & Hosting
- Web Servers
- Storage Technology
- Removable Backup Media
- Servers
- Broadband
- Grid
- OS / 2
- Novell Netware
- Unix Networking
- Windows Networking
- Security
- Telecommunications
- Operating Systems
- Linux Networking
| Other |
- Community Advisor
- Lounge
- Community Support
- New Net Users
- Philosophy / Religion
- Math / Science
- Miscellaneous
- URLs
- Expert Lounge
- Politics
- Puzzles / Riddles
| Community Support |
- Suggestions
- New to EE
- New Topics
- Community Advisor
- CleanUp
- Announcements
- General
- Feedback
- Input
- EE Bugs
| 04.25.2008 at 11:42AM PDT, ID: 21442199 |
remove the code from the stored procedure that removes the temp table
better yet, don't use a temp table and use a local user table instead...that will definetly fix your problem.
better yet, don't use a temp table and use a local user table instead...that will definetly fix your problem.
| 04.25.2008 at 11:47AM PDT, ID: 21442248 |
Would this be acceptable before the temp table is created?
IF OBJECT_ID('tempdb..#GLACCOUNT) IS NOT NULL
drop table #GLACCOUNT
At the moment I have to request that the SP be moved onto the target server.database, as due to server space needs our DBA's can't give me a DEV sandbox, so I'm trying to cut down on the number of iterations here.
IF OBJECT_ID('tempdb..#GLACCOUNT) IS NOT NULL
drop table #GLACCOUNT
At the moment I have to request that the SP be moved onto the target server.database, as due to server space needs our DBA's can't give me a DEV sandbox, so I'm trying to cut down on the number of iterations here.
| 04.25.2008 at 11:49AM PDT, ID: 21442267 |
The problem is that I am afraid that the context changes, so that your transformation is unable to see your local temp table. Try to make it global instead: ##GLACCOUNT
| 04.28.2008 at 02:06PM PDT, ID: 21457622 |
Can't do global ##TEMP_TABLES either. The company I work for has been burned on them in the past, and is strongly shying away from them.
I would think the easiest approach would be to have the SP do an insert into an actual table, albeit just for this purpose, then then in the DTS package it's a SP call, then a vanilla data pump 'SELECT * FROM ThatTable'
I would think the easiest approach would be to have the SP do an insert into an actual table, albeit just for this purpose, then then in the DTS package it's a SP call, then a vanilla data pump 'SELECT * FROM ThatTable'
| 04.28.2008 at 02:42PM PDT, ID: 21457900 |
That is the best way to do it...by far. I only suggested the global temp table to get around your temp table problems....local user table is for sure the way to go here though.
Accepted Solution
| 04.28.2008 at 02:43PM PDT, ID: 21457910 |
Thanks.
Another developer created the SP, but I've been assigned the project to take it and do a text export, so I have some leeway in recommending changes. I think I'll try that angle.
Another developer created the SP, but I've been assigned the project to take it and do a text export, so I have some leeway in recommending changes. I think I'll try that angle.
20080206-EE-VQP-25 / EE_QW_2_20070628
