DTS import text from Oracle truncating at 100 characters
-
I need to import an Oracle memo field into a SQL Server text field, and it works just fine... as long as I don't use a where clause. Unfortunately, the full table is several million records long, and we only need a specific couple of hundred of them.
Importing the full table takes hours and hours and usually eventually fails. The filtered view takes less than a minute, and doesn't fail...but it truncates the memo field. Right now, I'm running the filtered view dts package to collect the records and put them in the SQL Server table, then going to an Access mdb with both the Oracle tables linked in and the SQL Server tables linked in, doing a pass through that is the exact same sql as the dts package has, running a make-table query on that, setting the primary key, then updating the SQL Server table from Access. Which annoys me to have to do.
Any suggestions?
Thanks,
-
I have just tried something similar to see if what I suggesting will work...
I would do this using Linked Servers... Link the Oracle databases to SQL server. Do the filtered view to create a table then use a join on this to the Oracle table you want the data from to get it. Works quickly and well. Might even be able to get rid of the temp table.
exec sp_addlinkedserver 'oracleinstancename', 'Oracle', 'MSDAORA', 'oracleinstancename'
exec sp_addlinkedsrvlgin 'oracleinstancename', false, 'sa', 'oracleuser', 'oraclepassword'
select count(*) from oracleinstancename..ORACLEUSER.TABLE;
Sample select statement.
When you have done this you can use this link to get to the data you need and put it all in one DTS package.
Linked servers are good fun.. and work quickly when they are setup properly.
When you have done the link, go to the properties of the linked server and tick all the boxes... Data Access, RPC, RPC out.... etc this speeds things up a lot.
Enjoy.
Michael Girdler
-
Thanks for your help. I have long suspected that a linked server would solve many of the problems we're having.
I've had a request in for a linked server for a few weeks, but they are procrastinating, so I have been continuing to work on this problem using the non-linked method, which had this bug. (The company contracted out all the dba services and the contract dbas get mad if I infringe on their perogatives, regardless of either my need or my knowledge)
I'll just have to wait on them to fix it and continue the manual updates until then.
Thanks again.
-
Now we have a new wrinkle... the upgraded Oracle client was installed on the machine with the sql server, and ever since, our jobs running DTS packages fail. If we run the dts package from the dts window, it runs. However, every scheduled job with dts fails when it runs as scheduled or run via right-clicking the job and selecting "Start Job". Somewhere in the database I found it reporting EXCEPTION_ACCESS_VIOLATION errors when running the jobs but I can't remember where I found it now.
The dba group has tried to set up the linked server, but they also get the EXCEPTION_ACCESS_VIOLATION when they try to link it. We've had the machine rebooted, but the problem persists.
Any ideas? It is causing us big problems.
Thanks,
Metra
-
Hi Metra,
I did a Google (as I do every problem...) and got this:
FIX: You receive an EXCEPTION_ACCESS_VIOLATION error message when you try to save a DTS package in SQL Server 2000: You receive an EXCEPTION_ACCESS_VIOLATION error message when you try to save a DTS package in SQL Server 2000
http://support.microsoft.com/?kbid=830773
Both of these links give fixes for the error message you gave....
Can you tnsping the oracle database from the sql server?
The other thing you have to look at is the account that the scheduled job is running as. Does it have rights on the Oracle server? Oracle 8 didn't use the logon information but 9 + does. We had that problem with a dts package... I assume that you are using the scheduler within sql? If you are then let me know and I will let you know how to get around the issue....
Michael
-
Hi Metra,
I did a Google (as I do every problem...) and got this:
FIX: You receive an EXCEPTION_ACCESS_VIOLATION error message when you try to save a DTS package in SQL Server 2000: You receive an EXCEPTION_ACCESS_VIOLATION error message when you try to save a DTS package in SQL Server 2000
http://support.microsoft.com/?kbid=830773
Both of these links give fixes for the error message you gave....
Can you tnsping the oracle database from the sql server?
The other thing you have to look at is the account that the scheduled job is running as. Does it have rights on the Oracle server? Oracle 8 didn't use the logon information but 9 + does. We had that problem with a dts package... I assume that you are using the scheduler within sql? If you are then let me know and I will let you know how to get around the issue....
Michael
-
Yes, I can ping the oracle server. The dts package runs fine (including connecting to Oracle) as long is it isn't run from a job. I don't get any errors saving DTS packages, I only get errors on scheduled jobs.
The job has username and password in its connection properties, the same as any other user of of the Oracle database has, but I think you're on the right track -- this problem only occured after the upgrade to 9.2... before that we only had the truncation issue which I expect to be cleared up when we can use the oracle server as a linked server.
Yes, I'm using the scheduler in SQL. I would appreciate any help you can give.
Thanks a bunch,
Metra
-
We had issues running a DTS package linked to Oracle. DTS packages run using the account that SQL server runs under (usualy the default local system account). The 'contractors' that we had brought in to do the job that the DTS package was for wanted to change the account that SQL used to get around this problem... that would have worked except I vetoed it... didn't want SQL running on a domain account. I had a look at the job that they had created and noticed that it said command line. As soon as I saw this I copied the DTSRun /......... (VERY LONG STRING) into a scheduled task, created an account with suitable priviliges and run it... worked a treat...
To do this: Open the scheduled task from within SQL. - management / SQL server agent / jobs - open the job - open steps - double click on the step (notice it says type - operating system) this gives you the command: DTSRun /~Z0x......... etc. If you create a scheduled task, copy this string into it, set up and account for it (use your own account to see if it works) then you will find that all is well. Apparently Oracle 9? uses logon information on the server somehow to authenticate and this isn't happening. You can also past the command into the command line to test it.
Enjoy - and don't let them play with the account that sql uses to run......
Michael
PS - I hope that this solves the problem for you..
-
I'm sorry, I don't quite get your meaning...
Quote: As soon as I saw this I copied the DTSRun /......... (VERY LONG STRING) into a scheduled task, created an account with suitable priviliges and run it... worked a treat...
Question: Where do we set up the account for the job? Oracle? We can't do that... That's somebody else's baby...
Set up the account in SQL Server as a SQL login? That I can do. Do I do it using the job's long gobbledegook identifier as the login or the Oracle login?
Thanks a whole bunch. This sounds very promising. (Meanwhile I'll try setting up the Oracle login in SQL and using it as the "runner" of the package)
Metra
-
Sorry, as with most things they are either so easy they dont get explained properly or too difficult to explain properly.
When you are logged on using your logon account the DTS package works. Go to Schedule tasks on the SQL server that you want to run the task and create a scheduled task and get it to run with your authentication details (I assume that you are using NT....) that is the account to try it with. So in the scheduled task you have:
Run: DTSRun /~......
Run As:<Your logon details>
If you use your logon details (NT) you can test this quickly. The authentication problem is with the Oracle server which needs a suitable NT account to let this happen.
Michael
-
Unfortunately, I'm using VPN to get to the sql server and am using sql server security. I also have an NTDomain\Username I can use, and my other users are using NT authentication. BUT the Oracle server is using Oracle logins, and to make matters more interesting, they have a generic read-only user name for reporting. I have to use that read-only user name to pull the data from Oracle.
I created a SQL Server login to match the Oracle login, giving it public permissions, and it still fails, even though I added the Run As: username to the end of the job string. Can I pass the password there too?
Are we having fun yet?
Thanks for all the help. I really appreciate it.
-
I have a feeling that I have confused you. When I say that you will be scheduling a task. I mean that you use Windows scheduler on the server to schedule the task not SQL server job scheduler..... when you create the scheduled job on the server (not in SQL). That is where you use your logon details to run the DTSRun etc..... You do not even have to have enterprise manager open at all once you have the DTSRun string for the scheduled task. Use the account you use to VPN into the server as the account for windows scheduler. You know that that account has the rights to run the Oracle. When you have created the Windows Schedule Job you can test it by running it straight away.
I am attaching an image of the scheduled task I run..
I hope that this explains it for you.
We are always having fun, it is just sometimes we don't realise....
Michael
-
Unfortunately, the image didn't come over.
Using my account to run the job should work, even though my account has to use oracle logins to get into the oracle database?
To create the at scheduled item, I'd use the following from the dos prompt, where read_report is the read-only user login?:
at 5:03 /every:monday,tuesday,wednesday,thursday,friday,saturday,sunday "DTSRun /~Z0x647CE2FCA95656BD237357E8C4149B9757390663A32E01B4EC856B1798A656684E5FB28F3C94228D4CAC97BCBE47A6BE90460B11CC56D50330603A15AFA9C4164A735F9F00EC6116DA23BE4952213AEBC1ED17319C5C2FD79678D15F887A493B57F3F31030DE11C8BAC75BBCEC209DD6C3987113A3ADC5FE48F759BD717BD7D4907DF9855C7EC946701D18E9B97D947395BDBF2BC2A25E59C9E4606923E296486443DB4D61DE0A31D7B96093B63708EE4D39915ED8AD5C064E4EDFACB9495F577A5A47BF9691D7C34013E8 Run As: read_report"
Is that what you mean?
(I was about to write a bat file to do something similar to this, so I'm glad I'm on the right track.. I think?)
Thank you for being patient with me.
-
Hi, I use the gui for creating the scheduled job. The account you are using... read_report... is that a domain account? You can't use a sql account or a local account for this. It has to be a login account with rights on the sql server and the Oracle server.
You can write a batch file for this but the DTSRun .... can just go into the scheduled job as is. I tried to get an 'at' listing for the scheduled task I run but tells me I have none. Must work differently from the gui....
I hope that this all works for you.
Michael
-
No, it isn't a domain account. It is an Oracle account only.
Copying the job to AT scheduler doesn't work... it still runs under the job's security. (AT Scheduler is a Windows application, not a SQL Server application)
I've been working on a .bat file using DTSrun, since nothing else seems to be working. The .bat file is working at this moment. Once I verify that it works for each package, I will schedule that in AT scheduler. Which I suspect will work just fine.
Thanks,
Metra
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply