October 29, 2009 at 5:51 am
i've got an import job that runs against a mysql db that i have set up as a linked server. There is a messge field in the import that is a varchar(max)
The import is written out as a stored procedure, I have a Job that fires off the stored procedure every 20 minutes.
when I get in, in the morning, i check the import table and every message has the same lenght of 512 characters. looking at the data itself, and the data on the mysql db I can see that the messages are truncated.
Everytime I run the import job, it truncates every message field to 512 characters. Even if I fire off the job manually.
However, When i take the stored procedure and run it from an SSMS query window, I get the full lenght of the fields every time.
I tried using my account as an execute as in the stored procedure and it failed to execute under that context. So I don't believe it's my account, vs the Agent account (the DB Service account and Agent are one in the same, I know I know they should be different service accoutns but that is the way the accounts are)
I feel like i'm missing something any help would be appriceated.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
October 29, 2009 at 6:03 am
However, When i take the stored procedure and run it from an SSMS query window, I get the full lenght of the fields every time.
Was the length of the imported data less than 512.
"Keep Trying"
October 29, 2009 at 6:04 am
it was actually around 1600 - 65500
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
October 29, 2009 at 6:23 am
The error I get when i try to impersonate or execute as in the stored procedure is that I do not have permissions to use that account on the remote server. So I created a sql account with the same username and password as the mysql db, granted it some crazy rights just for the heck of it.
and got the same error that my account got
Access to the remote server is denied because the current security context is not trusted.
So i don't know that it is a permission account error.
But there has got to be some setting that is executing the SSMS procedure one way, and the Job Procedure another
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
October 29, 2009 at 9:52 am
Okay so I figured this one out but have a new question, I should have done this sooner, but I ran profiler, and executed the job and watched. And then executed the stored procedure from SSMS and watched, and i saw the difference was SET TEXTSIZE 1024, was being run by the system before the package ran.
Now from what I understand that restricst the amount of data that can be returned by a query.
http://msdn.microsoft.com/en-us/library/aa259190(SQL.80).aspx
So I added a line to my procedure that said SET TEXTSIZE 2147483647 (which should top it out at the maximum which is what I needed). Now the job runs fine.
Now the question I cannot believe that SET TEXTSIZE 1024 would get set automatically for every job, that would make SSIS unusable for big jobs......does anyone know what the seting is that turns this on, off, or toggles it.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
October 29, 2009 at 10:13 am
This behavior appears to be a connection manager behavior when connecting to Non-SQL sources (have seen that it also happens with sybase - and the fix was to change the connection string). I have used SSIS to move BLOB data from one SQL server to a different SQL server and have not run into this issue with that scenario.
I am curious what connection managers you have tried?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
October 29, 2009 at 10:20 am
I added the MySQL ODBC drive 5.1, then I created a Linked server using the Microsoft OLE DB Provider for OdBC Drivers.
I didn't use BIDS to make this though, it is just a basic execute t-sql task, just did the right click on jobs, new job, one step t-sql task and had it execute the stored procedure.
There is a Provider String aspect to the linked server, I wonder if I would need to specify this there.
I'll have to make a copy of this in BIDS and see if i set that value in the connection string as well.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply