April 29, 2016 at 7:32 am
Jeffery Williams (4/28/2016)
Not exactly.The execution comes later in another proc.
HOWEVER the printing of the variable is just BEFORE I set the variable value to the DB. What is printed to screen is absolutely correct. What is saved to the DB is truncated. I know this not from just a print but doing a LEN(PreparedStatement) wihtin a select query.
I have a work around that I am implementing as we speak. This so far seems to work but I don't particularly like it. It does add some visibility to each step of my process; and has no significant impact on performance. That said I still do not like it and I will continue to dig.
The work around, in case anyone else runs into this is as follows.
My statement is built through the process of running several procs. One builds the insert / select. The Select get s set with aliases for the FK's in the table.
Another proc then builds the JOIN statement and yet a third builds the WHERE clause.
I am sorry but I can not go into the business rules behind this or why all of this is dynamic.
Anyway the work around is each of these steps. Rather than taking the value of the column and ADDING to it is not saved in its own column.
The proc that does the execution; rather than loading the contents of the one column will not concatenate each of said columns. The JOIN and WHERE columns I use a coalesce with my alternate value as a space, for those tables that do not have one or both of those conditions in their respective statements.
I just made my changes and outside my process read each of these into a variable and it worked fine.
Again not what I WANT but it works and allows me to move forward so it will do for the moment.
Thank you all for your efforts and contribution. When I I figure out the root cause of this I will share with the community.
Ah, so the exact code being run is different than what we've seen posted here (the stored procedure code you posted had the PRINT after the UPDATE; that obviously isn't relevant to the problem, but it does mean the code is different). There's likely some other difference we've not seen that explains the behavior, but without seeing the exact code being run, it's very difficult to guess.
I'm glad you found a workaround, though! If you ever get to a point where you can post a script that allows us to reproduce, please do! I'm quite curious 🙂
Cheers!
April 30, 2016 at 5:58 pm
No I am not sure I following what you are suggesting here or I am missing the point.
So I build a dynamic query. In this case it is TWO joins that will be appending to an already existing INSERT / SELECT statement in the target column.
So I build the JOIN in @SQL1 and @SQL2 for example, which each are varchar(4000) - and really never exceed a few hundred characters.
AFTER They are built they are appended to the already existing string in said column. I have tried two ways of doing this step:
UPDATE TABLE
SET TargetField = (TargetField + @SQL1 + @SQL2)
AND I have tried combining in the proc.
SET @FinalSQL = TargetFieldValue
SET @FinalSQL = (@FinalSQL + @SQL1 + @SQL2)
UPDATE TABLE
SET TargetField = @FinalSQL
The PRINT Statement literally comes just above the UPDATE So what I get on screen would be exactly what should be written to the table. AND The print statement is absolutely correct. The insert is truncated however
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
April 30, 2016 at 9:26 pm
Jeffery Williams (4/30/2016)
No I am not sure I following what you are suggesting here or I am missing the point.So I build a dynamic query. In this case it is TWO joins that will be appending to an already existing INSERT / SELECT statement in the target column.
So I build the JOIN in @SQL1 and @SQL2 for example, which each are varchar(4000) - and really never exceed a few hundred characters.
AFTER They are built they are appended to the already existing string in said column. I have tried two ways of doing this step:
UPDATE TABLE
SET TargetField = (TargetField + @SQL1 + @SQL2)
AND I have tried combining in the proc.
SET @FinalSQL = TargetFieldValue
SET @FinalSQL = (@FinalSQL + @SQL1 + @SQL2)
UPDATE TABLE
SET TargetField = @FinalSQL
The PRINT Statement literally comes just above the UPDATE So what I get on screen would be exactly what should be written to the table. AND The print statement is absolutely correct. The insert is truncated however
Just an FYI, I write a lot of dynamic SQL where I work. At one point I was using sysname data type for some of the information being used. I found that caused truncation issues and I now use nvarchar(max) exclusively when building dynamic SQL. Just something to think about.
April 30, 2016 at 10:16 pm
REALLY??? WOW.. I have written a fair amount in the past however this is by far the largest dynamic project I have worked and in effect reverse engineers the relationship portions of SQL in order to migrate multiple databases into a single DB; all the same structure.
Obviously the GUID fields are a pass through however IDENTITY columns are renumbered and I have to keep track of the OLD and NEW values for proper mapping for the dependent tables.
Thanks for the Heads up. I am not using SYSNAME often here and I would have to look at this proc to see if I am using it at all. I know that I am in some procs so this is good to know.
That sounds strange because after you append the data it is just data without a datatype outside of the field it is being appended to.
I am using varchar rather than nvarchar as it is longer. I will have to lookup the length of the nvarchar type. Does it follow the same rule? Basically half the characters as a varchar? So a nvarchar(max) would be like 1.3 terabytes? (rhetorical question really, I will look it up 🙂 )
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
May 1, 2016 at 4:29 am
I know this doesn't answer your question, but why don't you just generate your statements as nvarchar(max)? You'll be giving the dynamic sql generated as a parameter into sp_executesql (bol) which expects it to be a nvarchar anyway. Plus, it eleminates the risk of accidently truncating a statement that goes past 8000 characters. Maybe you don't have these lengthy statements right now, but after some future update you can have them...
My guess is you won't be generating new dynamic code for each row being transfered, instead you'll most likely reuse the same statement until your meta data changes, so the loss in using nvarchar against using varchar for the storage of that statement won't be too big. Especially since you're going to need to convert it into nvarchar anyway.
May 1, 2016 at 6:42 pm
The length part of your recommendation here has not bearing; varchar is actually longer.
That said nvarchar is not a bad idea to avoid the conversion. The convert is very low impact but it all adds up.
I do read meta data into an object table (Parent / Child referreced) to indicate
Server
|
|> Each DB On Instant
|
|> each Schema For Each DB
|
|> Each Table for Each Schema
|
> column for each table (Including META)
This is read for each MERGE job because the source and destination of each of the above is dynamically selected by the user. The META data is refreshed by DB version and kept in my DB but the dynamic scripts are generated on the fly for each run
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh
Viewing 6 posts - 46 through 50 (of 50 total)
You must be logged in to reply to this topic. Login to reply