| |
| |
| |
|
Page: 1 2 3 4
Comments:
<voidzilla> rollback transaction
<voidzilla> do a select before the rollback so u see the effects of the update
<Shawnmb> I'll just run it normally, backups are no problem ^^
<Shawnmb> Hmmm
<Shawnmb> Straight out
<Shawnmb> Msg 468, Level 16, State 9, Line 1
<Shawnmb> Cannot resolve the collation conflict between "Korean_Wansung_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
<voidzilla> lol
<voidzilla> um i forget how to fix that
<voidzilla> books online ;)
<Shawnmb> http://msdn2.microsoft.com/en-us/library/ms179886.aspx
<voidzilla> COLLATE
<Shawnmb> http://msdn2.microsoft.com/en-us/library/ms179886.aspx
<Shawnmb> Ooops
<Shawnmb> http://msdn2.microsoft.com/en-us/library/ms179886.aspx
<Shawnmb> Ok my copy paste isn't working anymore
<Shawnmb> >.>
<Shawnmb> How do I write that into our query up there?
<Shawnmb> ^^
<voidzilla> hmm
<voidzilla> gonna take a guess here:
<voidzilla> UPDATE kworld.dbo.user_data SET account_id = ((SELECT uid FROM Lin2DB.dbo.user_account WHERE account = kworld.dbo.user_data.account_name) collate latin1_general_cs_as)
<voidzilla> i dunno
<voidzilla> o rrather
<voidzilla> UPDATE kworld.dbo.user_data SET account_id = ((SELECT uid FROM Lin2DB.dbo.user_account WHERE account = kworld.dbo.user_data.account_name) collate SQL_Latin1_General_CP1_CI_AS)
<voidzilla> did it work?
<Shawnmb> Nope >_<
<Shawnmb> Tried changing latin to Korean_Wansung_CI_AS as well
<voidzilla> new error?
<voidzilla> ah
<voidzilla> was about to suggest
<voidzilla> whats it saying now?
<Shawnmb> Msg 468, Level 16, State 9, Line 1
<Shawnmb> Cannot resolve the collation conflict between "Korean_Wansung_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation
<Shawnmb> Thats with korean on collate
<voidzilla> still?
<voidzilla> and with th elatin?
<voidzilla> *latin
<Shawnmb> With latin:
<Shawnmb> Msg 468, Level 16, State 9, Line 1
<Shawnmb> Cannot resolve the collation conflict between "Korean_Wansung_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
<Shawnmb> Yeah the same error
<voidzilla> hmm
<Shawnmb> Both databases are listed as latin, checking table properties
<voidzilla> one of the oclumns isnt tho
<Shawnmb> How do I check column collations ?>
<Shawnmb> Both tables are listed as latin too
<voidzilla> interesting question
<Shawnmb> user_account coallitions are both <database_default>
<voidzilla> ****
<Shawnmb> I see
<voidzilla> try script the tables
<Shawnmb> account_name is korean
<voidzilla> lol
<Shawnmb> in kworld.
<Shawnmb> That makes sense :P
<voidzilla> u can get around it and make a new table perhaps
<voidzilla> export the data into it
<voidzilla> or even alter the table maybe
<voidzilla> try alter first
<Shawnmb> Maybe we're collating it wrong ?
<voidzilla> see what happens
<Shawnmb> Yeah but then I'd loose my korean data in that column, wouldn't I ?
<voidzilla> i would change it al to latin to be honest
<voidzilla> um
<voidzilla> possible - but u joining latin to korean anyways and u gonna lose stuff
<Shawnmb> Hmmm
<voidzilla> why have it like that in the first place - to much room for error
<voidzilla> noob dbo
<Shawnmb> Yeah I didn't design the DB structure
<voidzilla> *nods*
<Shawnmb> The original apps are korean, that's probably why
<voidzilla> i dunno how u would collate
<Shawnmb> I guess I could change it, run the query and then change it back
<Shawnmb> :P
<voidzilla> i've never done cross language stuff like that
<voidzilla> i thought working with lobs was bad :/
<Shawnmb> Well
<Shawnmb> Look at that example
<Shawnmb> Lets see
<Shawnmb> SELECT * FROM TestTab WHERE GreekCol = LatinCol COLLATE greek_ci_as;
<Shawnmb> English: Select all from testtab where greekcol equals latincol (translated to greek)
<Shawnmb> so maybe we would have to do:
<voidzilla> try again:
<voidzilla> UPDATE kworld.dbo.user_data SET account_id = (SELECT uid FROM Lin2DB.dbo.user_account WHERE account = kworld.dbo.user_data.account_name collate SQL_Latin1_General_CP1_CI_AS)
<voidzilla> try that
<voidzilla> changed the brackets
<Shawnmb> It looks really really busy now
<Shawnmb> XD
<voidzilla> awesome
<voidzilla> beeeeeg tables?
<Shawnmb> Probably 100k to 500k rows in these
<voidzilla> ooh
<voidzilla> ok
<voidzilla> lol
<voidzilla> 100k=500k?
<Shawnmb> Somethings not right >.>
<voidzilla> *100k-500k?
<Shawnmb> Usually 2005 gets things done in 15 seconds
<Shawnmb> lol
<Shawnmb> 100,000 - 500,000
<TheGamble> perhaps account_name is not indexed
<voidzilla> ye ye
<voidzilla> big range
<voidzilla> i suspect colate does funky stuff
<Shawnmb> Popup error.
<voidzilla> ooh?
<TheGamble> heh
<voidzilla> popup error?
<Shawnmb> Timeout expired.
<voidzilla> lol
<Shawnmb> Then shortly after.
<Shawnmb> Msg 515, Level 16, State 2, Line 1
<Shawnmb> Cannot insert the value NULL into column 'account_id', table 'kWorld.dbo.user_data'; column does not allow nulls. UPDATE fails.
<Shawnmb> The statement has been terminated.
<voidzilla> icrease timeout
<voidzilla> lol
<voidzilla> silly
<voidzilla> UPDATE kworld.dbo.user_data SET account_id = isnull((SELECT uid FROM Lin2DB.dbo.user_account WHERE account = kworld.dbo.user_data.account_name collate SQL_Latin1_General_CP1_CI_AS), '[empty]')
<voidzilla> or something
<Shawnmb> Well
<Shawnmb> Thing is
<Shawnmb> That's impossible
<Shawnmb> >.>
<voidzilla> ---> collate
<Shawnmb> All these account names are defined, if one was missing I really don't know how that would have happened
<voidzilla> do isnull ^^
<Shawnmb> What's that going to do ?
<voidzilla> might be stuff getting lost in the translation
<voidzilla> checks if the value is null and replaces it with whtever u want
<Shawnmb> Msg 245, Level 16, State 1, Line 1
<Shawnmb> Conversion failed when converting the varchar value '[empty]' to data type int.
<voidzilla> i said '[empty]' for example
<voidzilla> lol
<Shawnmb> :P
<voidzilla> make it -1 then
<voidzilla> or 0
<Shawnmb> -1 are deleted accounts >.> I'll make it something obvious like -50
<voidzilla> sure
<Shawnmb> Its running
<TheGamble> [16:32] <Shawnmb> All these account names are defined, if one was missing I really don't know how that would have happened <--- cl***ic comment
<voidzilla> lol
<Shawnmb> Well no
<Shawnmb> If they don't have an account defined in Lin2DB there's absolutely no way they could have gotten in and created the data in user_data....
<voidzilla> did u write the frontend?
<Shawnmb> Lin2DB is the login database
<Shawnmb> Basically
<Shawnmb> kworld is the users data etc.
<TheGamble> so accounts just *never* get deleted after the fact, or renamed, or any other such issue ? Considering there is no cross-database referential integrity constraint (unless you make it yourself via trigger(s)), i find this suspect
<Shawnmb> You can't rename or delete accounts, no.
<Shawnmb> This DB would look totally different if I were the designer of it :P
Return to
#sql
or
Go to some related
logs:
#windows
porn form duba
how to shoot up heroin
#red
drivers for Intel82845g/gl
#flash
#beginner
loads drivers + hangs
#unixhelp
descrambler orasat 5.0
|
|
