can’t find the database if you are standing in it

I must have spend about 2 hours on this one.  I had a stored procedure that i was restructuring.  I was changing from one database to another, but both on the same server.  I got it to work in Data_1, but not Data_2.  I THOUGHT it was the same code, but on Data_2  i kept getting an error that the sys.servers could not find Data_2.  I could rebuild the process from the beginning, but as the logic got more complex, i would lost Data_2.  Since i could make it work, it was not the problem, except that i wanted to find the cause.  I tried to compare the two stored procedures in notepad++ with ‘move to other view’ on the second file tab, but i was not finding the problems.  And with usp, you need to both EXECUTE the usp to save it, and then exec it to run it.  Eventually, it would stop working and i was never confident of when it failed.

OK, so the answer is in the error.  I can reference [myTable] or [dbo].[myTable] or [databaseName].[dbo].[myTable] and they all should work.  What i can not do is this:


for a database on the same server, however i can do this:


Can you see where i am going?  The logic was complex enough that i had resorted to some limited copy\paste at times, and i had accidentally moved code like this


which looks like a four-part name to the parser, which complained it could not find [databaseName] — as a SERVER.

I hope it doesn’t take me two hours to remember this next time.

replace and convert

There are a couple of points in the stuff below.  One is the value of having sensible names.  Perfectly nice people put spaces into file names or urls.  Or mix numbers and letters (like putting a ‘z’ in from of something to indicate not to use that number.)  Just because they can.  There then is need to deal with such-like.  Or make EVERYTHING varchar(max) And a quick but dirty(?) way to create a numbers table.  This one i can even remember.

From Simon Sabin – he wants to parse data into into urls, but the data contains spaces and other naughty bits – which means URL encoding (space becomes %20, et al).  What he does is select the data (which are session titles) and  replace spaces with an underscore character:  “big cat” -> ‘big_cat’.

select cast(cast((
select case when substring(title,n,1) like '[a-z0-9/-]' 
then substring(title,n,1) else '_' end
 from num
 where n <= len(title)
 for xml path('')) as xml) as varchar(max))

So get the substring of one character, check to see if it is appropriate (is this all lowercase?) , if so select it, if not replace with ‘_’.  This assumes a numbers table/tally table of your choice.

From  Derek Dieter,  he has some strings that might contain non-numeric things (123A456) and he was to get rid of the ‘A’ and convert 123456 into an integer.  Similar to the above, he does this

 WHEN SUBSTRING(field,Num,1) LIKE '[0-9]' 
 THEN SUBSTRING(field,Num,1) 
 FROM Numbers
 WHERE Num <= LEN(field)
 FOR XML PATH('')  ) AS INT  )
FROM inputTable

It depends on how long the field is if the maxrecursion is needed – the default is 100 and this applies since we are using a CTE.

The rest of the code is generating the numbers table.  He does that like this

DECLARE @MaxNumber INT = 5000
;WITH Numbers AS
    SELECT 1 AS Num 
    SELECT Num+1 
    FROM Numbers 
    WHERE Num <= @MaxNumber

Select 1, union all and do it again with select Num + 1.  Repeat until cooked.  Actually, repeat as many of 32767 numbers.  As above for the default recursion of 100, this will not work with @maxNumber = 500.  It will work with 100, or you can set the OPTION(MAXRECURSION 5000) .  Or leave it at 32767 and don’t worry until it gives you an error

The statement terminated. The maximum recursion 32767 has been exhausted before statement completion.

identity column

I am instructed to add an identity column to rows of data in a table.  A simple 1, 2, 3 integer would be fine, and of course, when information is inserted, you don’t specify the identity and it will bee automatically added to the table.  Something like

create table myTable (
 id int identity(1,1)  not null,
 , myData varchar(10)
Insert into myTable (myData) "abc"

this results in 1, “abc” in the table

But,  you say “i want to create the table from a select into, NOT an insert?  Now what?

I got this from  on

 ,FirstName = pc.FirstName
 ,LastName = pc.LastName
INTO #person_contact  FROM Person.Contact pc

You are getting the first and last name into the (in this case) temp table and adding an identify column on the fly.  Notice that subsequent inserts of first and last name will automatically add the id entry, which is of type int, started with 1 and increments by 1 on each row and for each insert. You can also use row_number() over…  like this

    ,FirstName  = pc.FirstName
    ,LastName   = pc.LastName
INTO #person_contact2 FROM Person.Contact pc

66535, but who’s counting?

If you want to look at the description of a view (and that is the field name where in the selection code is stored) you can select the view, open DESIGN and see the code that selects the columns, with all the alias and the joins and froms, oh my.  Maybe

We have some views where that just doesn’t work the way you want.  You can see it, but when I try to select all\copy\paste into a text editor, and all the stuff doesn’t come over.  OK, so i put the cursor in from of the first word, and use the down arrow to select the text, REBAR style.  At some point, the “copy” select goes off and you can’t select the text.  A couple of times, i have been able to copy half and then select the rest, but there are times when that doesn’t work either.

So – how about this:

EXEC sp_helptext 'dbo.myView';

Yup, that works, but in my case, i get 1049 rows of text of varying lenghts.  REALLY ugly.

How about this:

USE [Ntier_MIPU]
SELECT definition,*
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(‘PM.vwGenPatInfo’);

you get the object_id, the definition field, which contains selecta view myView as select … but in my case, the definition is truncated.  There is a character limit for how long the field can be in grid-view.  I had no better (in fact, worse) luck with text view and exactly the same data if i output it to file.

In  Management Studio – Tools, Options, Query Results, SQL Server, Results To Grid, Maximum Characters Retrieved – Non XML Data, there is a setting for the Maximum Characters Retrieved, which can not be set to more than 64K.  You can of course select more data to a table, but the display is limited to the aforementioned 66535 characters.

And for XML RAW was similarly ugly and not terribly readable.  Maybe i could have opened it in MS Internet Explorer or such like with a style sheet that would not show the elements and attributes but that was WAY too much work, given the ease of my workaround.

If you want to build, or duplicate, a big table — i mean a table with a complex schema — you can right-click on the table in SSMS, Script Table as, create to … whatever (file, agent, query window).  Yes, query window.  So find the view, script to create in the query window, trim off the create view front stuff and the closing quote, and you have it.

column names

We have data that comes in from, possibly, multiple data sources.  This data is processed and chopped into our derived tables, from which we are going to work.  The process to combine and manage the data is done by several different user stored procedures  – which are maintained by multiple people.  In order to automate what needs to be done with new data refreshes, there is a single table that holds information about what tables are officially maintained.  On refresh, there is another usp that runs against this table, and uses the stored names to run the necessary usp to create each table.

Thing can change and versions of the usps or even the structure of the table can be modified and not announced.  Of course, this problem is eased by making the central program only necessary to coordinate the sequential running of each of the usps that actually create the tables, but still there can be confusion.

Our approach is to also make each usp (and the author) responsible for undating a central table of metadata with each of the names of the table, the names of the column, and the client database from which it was derived.  This CAN be done manually.  Sure.  If we are not certain where the column comes from, we can get it from the usp code, and put it into the table.  We can also update the metadataTable each time the usp runs to make a new derived table.  Code like this will work to first, clean up the table to remove old entries, then to insert the new values:

 delete from [dbo].[metadataTables] where tableName='table1' 
insert into [dbo].[metadataTables] (tableName, fieldName, 
systemName) values
 ( 'table1','EMR_Provider_id','S1')
,( 'table1','suffix','S1')
,( 'table1','first_name','S1')
,( 'table1','middle_name','S1')
,( 'table1','last_name','S1')
,( 'table1','pm_provider_id','S2')

Of course, this can be made better in cases (well, all cases, but most usefully) when most of the columns come from one source.  Then we can put them all in, and change the ones not correct:

delete from metadataTables where tableName = 'table1'

insert into metadataTables
select 'table1', column_name, 'S1' 
from information_schema.columns 
where table_name = 'table1' order by ordinal_position

update PDS_metadataTables set systemname = 'S2' 
where tableName = 'table1' and fieldName in ('pm_provider_id')

Delete old entries, get all the column names for table1, put them into metadataTables as from SystemName S1, then from your own knowledge, update just ‘pm_provider_id’ to the correct systemName.  Repeat Step three until everything is lined up.

Working with XML

XML data is a universal standard for storing human readable, self-documenting data.  It is useful for configuration files, data interchange files and non-relational data in database servers.  Non-relational in that a XML document might have different sized clusters of data entries, but within a cluster, each entity (think row here) doesn’t have to have the same attributes or values.  The document could be about a business, with part of it referring to the physical plant, a second part to the employees, only one of whom we know their favorite color.  The XML structure can support that.

XML data is text and can be stored in a varchar or nvarchar field.  This might be sufficient if all we want to do is retrieve the data, but MS SQL has a XML Datatype that allows additional functions – there is a built-in XML editor to show the structure of each XML item, and there are other methods that allow querying, updating and inserting XML data.

One approach to working with XML data is to ‘flatten’ it into normal rows and columns. this is straight-forward when you are dealing with non-nested data: groups of users, each with one first name, one second name, one collection of single elements. It becomes more difficult with you have a skills element with zero to many skill elements nested in that element. This is putting you into the situation of skill column one with an entry, and for the second user, two columns for skill one and for skill two.


By using cross-apply, we can get around this. A great article on by Simon Ince documents this well

First the setup:

Save this XML file as as c:\users\rbeck\desktop\new.xml

<?xml version="1.0" ?>

Create a table and import the data


notice the XML data type.

Import the data and display it

INSERT INTO XmlSourceTable(XmlData)
 BULK 'C:\users\rbeck\desktop\new.xml', SINGLE_BLOB)
AS ImportSource
SELECT * FROM XmlSourceTable

RecordId XmlData
———– ——-
1 <Root><Person><Name>Simon</Name><Age>20</Age… (snip)
(1 row(s) affected)

One row for each file/xml document. If we import this twice, we get the same XML data, and RecordIDs of 1 and 2. The rest of the output below is just duplicated. If the second import was different data (but the same schema) that would just look like more (and not duplicated) data.

Query the data

 pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,
 pref.value('(Age/text())[1]', 'int') as PersonAge,
 pref.query('Skills') as PersonSkills
 XmlData.nodes('/Root/Person') AS People(pref)

PersonName       PersonAge         PersonSkills
———- ——— ———————-
Simon                          20                <Skills><Skill>Cooking</Skill><Skill>Clean… (snip)
Peter                           21                <Skills><Skill>Ironing</Ski… (snip)

Notice that Simon has more skills than Peter. In this case, try this:

 pref.value('(Name/text())[1]', 'varchar(50)') as PersonName,
 sref.value('(text())[1]', 'varchar(50)') as PersonSkill
 XmlData.nodes('//Person') AS People(pref) 
 pref.nodes('Skills/Skill') AS Skill(sref)

PersonName PersonAge PersonSkill
Simon            20                Cooking
Simon            20                Cleaning
Peter             21                 Ironing

The magic is done by CROSS APPLY and by the XMLData.nodes function.

From BOL: “The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query.” I am going to simplify that to “the nodes table-valued function is applied to each pref.value and the one pref.query – the output is concatentated left to right.

When we use xxx.query, we get back XML, even if it is not enclosed in an element. If we use xxx.value, we get back the value – we need to specify the type of data (int, varchar()) and the explict path:

“Although there is only one PersonName or PersonAge attribute in the XML instance, the static typing rules require you to explicitly specify that the path expression returns a singleton. Therefore, the additional [1] is specified at the end of the path expression” (emphasis from rcb)

If we apply the value to the skills element and a path of [1] and a varchar datatype, we get back “CookingCleaning” for Simon

pref.value('Skills[1]', 'varchar(50)') as PersonSkills

The nodes method returns an xml fragment that matches the path supplied. It puts the result into a table with the supplied name and into the column in the parenthesis:

XmlData.nodes('/Root/Person') AS People(pref)

return xml that is found below Root in the or all Person elements

another example of using cross-apply is from The Cheater’s Guide to Mastering XQuery on SQL Server – Part 1.  Unfortunately, there is no Part 2.

His data looks like this

<?xml version="1.0" encoding="UTF-8"?>
<gesmes:Envelope xmlns:gesmes="" xmlns="">
 <gesmes:subject>Reference rates</gesmes:subject>
 <gesmes:name>European Central Bank</gesmes:name>
 <Cube time="2011-01-14">
 <Cube currency="USD" rate="1.3349" />
 <Cube currency="JPY" rate="110.71" />
 <Cube currency="GBP" rate="0.8422" />
 <Cube time="2011-01-13">
 <Cube currency="USD" rate="1.3199" />
 <Cube currency="JPY" rate="109.5" />
 <Cube currency="GBP" rate="0.83575" />
 <Cube time="2011-01-12">
 <Cube currency="USD" rate="1.2973" />
 <Cube currency="JPY" rate="108.17" />
 <Cube currency="GBP" rate="0.83155" />

Notice the namespace – there is a problem coming up.

His first select statement looks like this

T.rows.value('local-name(.)','nvarchar(100)') as [TagName]
 xmldata.nodes('//*') T(rows)

This gets  the top level tags from the references table we created and populated with the data, select the value of the XML cross apply the nodes method to the root of the document.

Notice also the irregular tag name cube, which is used at several levels. The cross apply nodes method can be started at some other place than the root (//). For example, we can push it down to the second level of cube with

CROSS APPLY xmldata.nodes('/gesmes:Envelope/Cube//*') T(rows)

However, in his example he gets an error – by starting the search at cube\cube, we lose the namespace reference which is in the envelope element. He gets around that with the WITH XMLNAMESPACE prefix. (You can also specify the namespace in the query or nodes methods of XQuery).

'' as gesmes,

He also has an example of cross apply … cross apply that looks like a pivot table – it puts each cube entry cube values (the different times, currencies and rates) as columns.

id TagName Time      Currency Rate     xmlfragment
A neat grid of all currency rates. by date.
1  Cube    2011-01-14 USD     1.3349  <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-14 JPY     110.71  <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-14 GBP     0.8422  <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-13 USD     1.3199  <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-13 JPY     109.5   <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-13 GBP     0.83575 <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-12 USD     1.2973  <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-12 JPY     108.17  <p1:Cube xmlns:p1=”h…
1  Cube    2011-01-12 GBP    0.83155  <p1:Cube xmlns:p1=”h…

Here is the XQUERY standard reference from Microsoft

He also has a link to an IBM developerworks article talking about XML namespaces ().


Another approach is to use OPENXML – which is a rowset provider that makes XML stored in SSMS xml datatypes viewable as if it were relational table or a relational view of the xml data.

sp_xml_preparedocument puts the XML into memory and returns a handle to the location that OPENXML will then use. sp_xml_removedocument will then trash the inmemory entity. has an example

 <Student id="1">
 <Course id="1">Fundamentals of Databases</Course>
 <Course id="10">Fundamentals of Networking</Course>
 <Course id="15">Fundamentals of Security</Course>
 <Student id="2">
 <Course id="12">Fundamentals of Data Warehousing</Course>
 <Course id="15">Fundamentals of Security</Course>
declare @pointer int
EXEC sp_XML_preparedocument @pointer OUTPUT, @XML;
FROM OPENXML(@pointer,'/Students/Student',2)
(StudentID int '@id', 
StudentFirstName varchar(50) 'FName', 
StudentLastName varchar(50) 'LName',
StudentAge int 'Age',
EnrolledCourse1 varchar(50) '(Courses/Course)[1]',
EnrolledCourseN varchar(50) '(Courses/Course)[N]'
EXEC sp_xml_removedocument @docpointer;

In case you are wondering, if student 100 does not have N courses, a null is returned for the missing courses. In the example, @id is an attribute of the Student element. There is also a course_id attribute, but this code does not get it.

I have modified the code to support the course id attribute. First put the item(s) into the select part of the document.

CourseID1, <-- added
CourseID2, <-- added
CourseID3 <-- added

Notice that the with FROM OPENXML(@pointer,’/Students/Student’,2), you have provided the starting point to query the student element. Courses are nested inside of student, so you need to provide the augmented path to the course element in order to query the course title, which is the entire contents of the course element. Since we can have 1 to N entries (in our example 3), we need the exact path to the expected element [1],[2] or [3].

EnrolledCourse1 varchar(50) '(Courses/Course)[1]'

When we go after the attribute for the first course, we query for @id but still need the path

EnrolledCourse1 varchar(50) '(Courses/Course)[1]',
CourseID1 int '(Courses/Course[1])/@id',

and variously for all three course elements. The second student has only two courses so the ID and the Name are reutnred as null

nodes method

You can also use the nodes method rather than OPENXML. You don’t need anything like EXEC sp_xml_preparedocument and EXEC sp_xml_removedocument since the engine extracts the data from the XML directly with XQuery

FROM @XML.nodes('/Students/Student')
as StudentTable(Student)

We get the structure as a rowset, starting with the students/student path, store it in StudentTable as the column student. From the rowset, we select the individual values (in the very first example we also selected XML with query) using code like this (i edited the original paper to include the course_id attribute

Student.value('@id','int') as StudentID,
Student.value('(FName/text())[1]','varchar(50)') as StudentFirstName,
Student.value('(LName/text())[1]','varchar(50)') as StudentLastName,
Student.value('(Age/text())[1]','int') as StudentAge,
Student.value('(Courses/Course/text())[1]','varchar(50)') as EnrolledCourse1,
Student.value('(Courses/Course/@id)[1]','int') as EnrolledCourse1ID, <-- added
Student.value('(Courses/Course/text())[2]','varchar(50)') as EnrolledCourse2,
Student.value('(Courses/Course/@id)[2]','int') as EnrolledCourse1ID, <-- added
Student.value('(Courses/Course/text())[3]','varchar(50)') as EnrolledCourse3
FROM @XML.nodes('/Students/Student')
as StudentTable(Student)

This blog has a stored procedure to examine the schema of an XML table

AD HOC XML File Queries

An interesting idea is suggested by Seth Delconte in SimpleTalk.  Store the XML in the file system (c:\myXML.xml).  Use OPENROWSET to read the xml into a CTE with an XML datatyle.  Or perhaps, create a function to return an XML variable that can be up to 2GB in content.

Now that we have XML data or at least an XML fragment, work with XQuery FLOR, XQuery nodes or XQuery value methods.


Still another approach is to leave the XML along and put a primary and perhaps secondard index on the XML column.  This makes XQUERY value, nodes or other methods run twice or more as fast.  Information on “Getting Started With XML Indexes” available and more on Selective XML indexes here.


if we want to get XML format  from SSMS, we can use the for XML syntax as explained in a SimpleTalk article. Options are “for XML raw”, “for XML auto”, “for XML explicit” or “for XML Path”.

Just as a side note, from XML is an excellent way to document the format of a table if the rows and columns are running off the screen – you can return the header as XML and the first line of results also as XML and use this to describe the table.

For XML raw returns each row as an element, each element as an value expressed as an attribute of the column name. Using the simpletalk example

select * from stores for xml raw

<row StoreID=”292″>
<BankName>United Security</BankName>
<Products Type=”Bikes”>
<Products Type=”Clothes”>

SELECT * FROM Stores for XML raw ('root')

just names the root element, in this case the case of column (survey_untyped) as ‘root’. You probably want to go the other way – make root into something more useful. If you can comparing versions, you can ‘for XML raw (‘v1′)’ et al.

And this is returning the attributes as attributes. You cAn force them into elements, for possible ease of querying, by specifying elements to the XML ROW method

 SELECT * FROM Stores for XML raw ('root'), ELEMENTS;

Further, you can wrap the entire output inside an XML element, which makes it valid XML:

SELECT * FROM Stores for XML raw ('survey'), ROOT('VERSION1')

One use for this is to take row column data, which you can query by column name or alias and convert it into valid XML.


For XML path returns differently formatted XML – you get to indicate what the path is and name the root.  If you finished the select with

FOR XML PATH('customer'), ROOT('customers')

you would get a root of customers and a top element of customer.  The individual elements would be named as you select them with aliases or with the field name.  Or you can put in nothing at all.  There is a clever way to use XML Path to string together common separated values in a query to get a field with several values.

SELECT CAT.Name AS [Category],
 STUFF(( SELECT ',' + SUB.Name AS [text()]
 FROM Subcategory SUB
 where SUB.ID = CAT.ID
 ), 1, 1, '' )
 AS [Sub Categories]
FROM Category CAT

this will give you cat1               subcata, subcatb, subcatc
cat2               sub2a, sub2b, sub2c

so that you no longer have to tell people that ‘it’s too difficult’ if this is the format they want.


A nice brief tutorial on XML is from Joel Lipman

He postulates an xml datatype column “Event_XML’ in a table “XML_Events” .  The root is Staff

Either of these queries will return a column that will contain ‘male’ for each staff entry. – not the XML data

 CAST(CAST(Event_XML AS XML).query('data(/STAFF/GENDER)') 
 AS VARCHAR(10)) AS Gender 
   CAST(Event_XML AS XML).value('data(/STAFF/GENDER)[1]',
   'VARCHAR(10)') AS Gender 

this will return the attribute financial_year for the current_employee element in staff

   AS INT(4)) AS FinancialYear 

if you look at the query, understand this is a normal t-sql query that also has support for XML via query or value (or node) with XPAth. So it is not surprising we can use a where clause

    CAST(Event_XML AS XML)
AS Gender

Note that this will return all the columns, including an XML datatype which you can access with the XML editor

If we want to return the XML, use nodes as below

-- to get all below nodes from root node - STAFF 
 SELECT Event_XML.query('/STAFF') 
 -- to get all element nodes named 'EMPLOYEE_NUMBER' 
 -- to get employee node with ID = 1 
 SELECT Event_XML.query('//EMPLOYEE_NUMBER[@ID = 1]') 
 -- to get employee node at position = 1 from root node 
 SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER[position()=1]') 
 -- to get employee node at last position from root node 
 SELECT Event_XML.query('/STAFF/EMPLOYEE_NUMBER[last()]') 
 -- to return unknown nodes from root node - STAFF 
 SELECT Event_XML.query('/STAFF/*') 
 -- to return unknown nodes from XML column - Event_XML 
 SELECT Event_XML.query('//* ') 

if we want to look for strings in the elements

-- exist() returns 1 or 0 if a node name exists 
-- containing "NAME" or not respectively 
 SELECT Event_XML.exist('(/*/*[1][contains(local-name(.),"NAME")])')
-- query() returns XML branch if the node name 
-- contains string "NAME" -- returns <SURNAME>LIPMAN</SURNAME> 
 SELECT Event_XML.query('(/*/*[1][contains(local-name(.),"NAME")])')
-- query() returns XML branch if the node value contains
-- string "MAN" -- returns <SURNAME>LIPMAN</SURNAME> 
 SELECT Event_XML.query('(/*/*[1][contains(.,"MAN")])') 
-- value() returns value of node if the node name 
-- contains string "NAME" -- returns LIPMAN 
 -- value() returns value of node if the node value 
-- contains string "MAN" -- returns LIPMAN 

how old is that usp in Windows?

We have a bunch of usp (user stored procedures), some of which are perfectly fine, and others are no longer used.  If y0u want to see when it was created, when it was last modified, and how long SINCE it was last modified, here is some code.

, create_date
, modify_date
, datediff(d, modify_date, getdate()) as [since_last_modification]
FROM sys.objects
WHERE type = 'P'
order by [since_last_modification] desc