« first day (4243 days earlier)      last day (51 days later) » 

2:38 AM
@PaulWhite so reductive
3 hours later…
5:55 AM
Seems like the Gravatar is updated. Probably caching
1 hour later…
8:26 AM
How lovely
Hope it isn’t anyone you know
Doing roll call now
1 hour later…
9:51 AM
@PaulWhite rolling luggage would be wise for that
Is anyone here good at writing stored procedures? I'm having trouble getting one to run outside the database its stored in
we're good at writing stored procedures, but we're great at writing invoices
Hahaha didn't realize stackexchange was such a massive business opportunity :)
we're just waiting for reputation to get converted into bitcoin
Hey that would be kinda cool
I have a single param SP which selects from the sys.database_files table but I cannot seem to EXEC it on any database but the one its stored in
I'm also not allowed to use another PARAM combined with USE in the SP
So then how do I do this?
10:09 AM
pay @PaulWhite $1000
he'll tell you
@ErikDarling Best I can do is NFT
@Foxyproxy can you post a link to your question? Sounds interesting
Yes a question would make it clearer. At the moment it sounds like you want sys.master_files instead.
10:34 AM
alright I'll post a question
never mind, I actually got it :) Can just use the USE statement in dynamic sql
10:57 AM
I wonder what the question would have been
Something along the lines of "how can I use a SP stored in database A on database B. I was making a selection from sys.database_files and it seemed to only want to grab that data from the table of database A"
Still a bit vague isn't it.
Like why wouldn't you query sys.master_files instead?
@Foxyproxy Copy the procedure into database B. Alternatively, put the whole script into dynamic SQL, then call it using DECLARE @proc nvarchar(1000) = @yourDb + '.sys.sp_executesql'; EXEC @proc @sql;
@PaulWhite I tried that too but that didn't seem to matter
@Charlieface I ended up using dynamic sql indeed
11:10 AM
@Foxyproxy I ask because people sometimes don't realise sys.database_files is scoped to a single database, but sys.master_files contains entries for every database, so you can filter on the database name provided directly. I might be mis-guessing what you were actually trying to do.
I realized that was the reason you asked and it seemed like a logical solution to my issue as well but I still had to resort to dynamic sql for some reason
Righto never mind
> Righto never mind
Aug 13 at 15:21, by Paul White
@ErikDarling are they talking very loudly and putting an O at the end of each word to be better understood, while demanding a proper Full English Breakfast and nine beers?
ha ha
11:38 AM
@Foxyproxy Just note what I did: there is no injection of the database name into the SQL. I just create a sp_executesql procedure name dynamically, then use EXEC @proc
@PaulWhite Am I missing something?
12:14 PM
What the HELL
12:35 PM
understandable, have a good day
1:25 PM
@HannahVernon Looks like they're at it again with loads of not-very-useful and almost identical questions dba.stackexchange.com/users/162483/variable
2:18 PM
3:04 PM
@Charlieface le sigh. Will deal with it. Thanks for the heads up.
3:26 PM
If I INSERT INTO dst SELECT FROM src CROSS APPY xmlcolumn.nodes('blah') and dst has an identity column, can one say for certain that the order the nodes are inserted in the dst table (i.e. the identity column) matches the order of the nodes from the original xml document?
3:50 PM
@CadeRoux Sounds like a question to me.
4:34 PM
@CadeRoux No. While it's probably true, there are no guarantees that identity columns necessarily behave like that. If you want the position of the node, then use this answer
A: Xquery Get Position()

user9361177It might be simpler and better performing to use Node Order Comparison Operators in order to count the preceding //comment nodes in the XML tree. I didn't test on huge XML documents, but it's definitely less I/O intensive, and was less CPU intensive on my contrived tests. declare @x xml = N'<po...

A: Get the position of xml element in SQL Server 2012

CharliefaceThere is a way within XQuery to return the position of the current node: you can count all nodes previous to it SELECT T.X.value('text()[1]', 'nvarchar(100)') as RowLabel, T.X.value('let $i := . return count(/Rows/Row[. << $i]) + 1', 'int') as RowNumber FROM @xml.nodes('/Rows/Row') as T(X)...

Remember also that parts of your query could go parallel, which could also mess up the default ordering
That's what I was thinking. I think a number needs to be extracted during the XML processing and let that go through all the way.
I posted it as a Q anyway
Q: Order of XML nodes from document preserved in insert?

Cade RouxIf I do: INSERT INTO dst SELECT blah FROM src CROSS APPLY xmlcolumn.nodes('blah') where dst has an identity column, can one say for certain that the order the nodes are inserted in the dst table (i.e. the identity column) matches the order of the nodes from the original xml document? I think the...


« first day (4243 days earlier)      last day (51 days later) »