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"
@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;
@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
@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?
@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
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?
@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
It 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...
There 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
If 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...
There 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)...
