| |
| |
| |
|
Page: 1 2 3 4 5 6
Comments:
<0> Drk: I sent him a link for order by syntax like 5 minutes ago.. and it says the same thing as the msdn page.
<1> lets just forget him, he doesnt wanna listen
<2> I've seen a lot of people like that recently..
<0> Yeah, I've gotta write the parser.. yeay.. I wish I could use mssql or any *sql.. but Access is imposed upon me right now
<0> I think perhaps the easiest way would be to write a function that just parses the fields into a temporary table then uses a joined query to translate the integers to names
<1> depends how you wanna do it
<1> alternatively you could delete * from options where userid = ?? then do a series of inserts from your client code into options table
<1> ready for joining
<0> I was thinking something like select parsefields(tab1.freelist) into tab2 from tab1; but I'm not sure if that will work if parsefields() returns something like 'blue','red','yellow'
<3> Dunno about access, but mssql you can use functions in the from clause
<4> evening
<3> so you could seelct * from blah join parse(blah blah) p on ...
<5> i didn't think access even supported functions
<3> That is entirely possible. I don't use access. :)
<0> Drk'Angel: I can use functions written in VBA in the db's modules
<4> tenfour : it does - and vba function can be included in a query - even user-written ones
<4> is really quite handy, but can make performance **** even more...
<0> Yuppie: in fact, that is what i'm doing
<5> that's pretty cool
<5> hehe performance in access
<5> u r funny
<0> Yuppie: this isn't a server, it's run to output data in a certain format for a particular analysis program to import
<4> [rb] : i think that you're presuming that (a) i scrolled up to read what was said earlier, and (b) i care...
<4> [rb] : did you actually have a question?
<6> I have a table in SQL server 2000 that I cannot select past row 12 in, it just never returns
<6> SELECT TOP 11 * returns fine, TOP 12 * hangs
<0> YuppieScm: if you don't care and don't want to read the question we're talking about? then why bother me about it?
<6> DBCC reports no problems
<1> hm twin, ive heard of this before
<6> DBCC CHECKTABLE that is
<4> twint70s : sounds like an index is broken
<4> twint70s : or that something has locked a row
<6> DBCC CHECKDB also shows no problems
<6> No there is nothing else running, no locks showing
<4> does it still do this after a reboot of the sql service? what about if you copy the table?
<0> yuppiescm: the question is about approaches to parsing data from a column, that is a list of integers "1 5 3 6 8" that each correspond to a string value and translating them to their strings, [using Access]
<6> I think the first thing I should do is try and take a backup of the database?
<4> and what happens if you just do 'select * from table'?
<6> Or is it safe to just restart SQL server
<6> It hangs
<1> try selecting the TOP 1
<6> That works
<6> TOP works up to 11
<1> oh nm, top 11 works fine
<6> COUNT(*) also hangs
<4> twint70s : sounds borked big-time... backup the db, the stop sql and backup the files, then restart sql
<1> when you select TOP 12, whats in the process list ? sp_locks and sp_who2 ?
<6> Spid is 53 for the SELECT TOP 12 client, is showing : 531677003KEY(ed00da11a7fd) SWAIT
<1> what service pack & edition of sql ?
<6> In WHO Status SLEEPING, Command SELECT
<1> trying to find the forum post of the guy who had same problem
<4> [rb] : why does a single column contain multiple items of data? sounds like a really poor design...
<6> CPUTime : 175672
<6> and disk io is also really high
<6> Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
<0> YuppieScm: yes it is. But that's out of my control.
<4> sp1? dude, they're up to sp4 now
<4> first thing ms will tell you to do is upgrade to sp4
<4> if you burn a call on this problem
<0> YuppieScm: that's how the data was created.. I just need to write a function/query combo that ouptput it for a certain analsys app
<1> have you tried updating your statistics ?
<1> and does the estimated execution plan for TOP 12 differ from the one for TOP 11 ?
<4> twint70s : drop all the indexes, and rebuild them
<4> sounds like you may have a crosslinked entry thats recursive
<1> try this.... select TOP 12 * from table with (nolock)
<0> YuppieScm: in fact, I'm planing to parse the space separated fields, and create a view or temporary table with each as a separate row, then use joined queries from the index table of those values. Sound right to you?
<4> well, ms-access doesn't do temporary tables
<1> also check sp_who2 to see if anyone has any transactions which havent been committed/rolledback if row 12 lies on a different page its possible a transaction is blocking tht page
<0> YuppieScm: right, but since this is run only occasionally (maybe once per dataset) I just mean another table
<0> yuppiescm: the temporariness of the table won't matter, I'll make it, populate it, query, and drop it.
<6> WITH (nolock) worked
<1> ok, so its being blocked by something
<1> see next bit i suggested
<6> I don't see anything showing up in locks
<1> hm
<1> if you run it without NOLOCK, so it blocks again
<1> then run sp_who2, and check the blkby column
<6> Ok, I stopped and restarted SQL Server and no love
<1> backup and restore to a diff db, see if it still borks
<1> btw, can you select * from table ? with no top ?
<0> Hrmm.. I might be able to finagle the Replace() function in access into what I need in the query...
<0> ack.. I really wish they'd document the vbDatabaseCompare constant with more than "performs a compare based on the data from your database".. oh yeah, how?
<7> auch mich bin ein Elephant
<7> and, yes, it's me, the old bastard chrisxp
<7> i lost my self respect years ago, in the mud
<8> using mssql, i have a query: insert into table1(val1,val2,val3) select f1,f2,f3 from table2
<8> can i call a stored proc in place of f2?
<8> i mean val2
<8> no f2
<8> heh
<1> youd need to call it prior to the insert
<1> and store the result in a var, then p*** the var instead of f2
<8> hmm
<1> exec @var = myProcWhichReturnsaRETURNValue
<1> or use a UDF, if you need a diff value per row
<8> but you can't call that in the middle of a query?
<9> anybody know of a way to pump xml data into SQL Server using DTS ?
<10> question if i wanted to backup my Db to a flash drive would that be good?
<10> good idea that is
<11> depends how important it is to you
<10> lol
<10> its my work for the last 2 months
<11> depends how important 2 months is to you
<11> i wouldnt use a flash drive
<11> at least not yet
<11> too many corruption issues with windows and flash drives
<8> is there a function to get a sub string of a string?
<8> not including left and right
<8> substring()
<8> excellent
<3> Glad we could help.
Return to
#sql
or
Go to some related
logs:
#politics
spoof3r
#bsd
#visualbasic
#politics
microwave smelting
#politics
#hardware
salaheddin province govern
#computers
|
|