January 20th, 2006 at 3:26 pm
In my last post I mentioned that SQL Server 2005 provides with you with the flexibility of having an XML document stored into one single column of “Xml†datatype. Just like a int, datetime or a nvarchar() datatype, you can define “Xml†datatype to store the whole Xml document or a fragment as long as your XML is valid.
But the catch here is if your XML file has a header something like “ “, and you are sending this document as a string to the database, you are in trouble. You are presented with an exception something like “XML parsing: line 1, character 38, unable to switch the encodingâ€.
So the solution is, while you are opening the FileStream, open with a second parameter “Encoding.Unicodeâ€. That’s it. So now your XML document is going to be stored in Unicode (UTF-16) format. However when you retrieve it back from database, remember to convert it to UTF-8, if you are expecting it.
It pays to use http://groups.google.com
Check out limitations with “XML datatype“ in SQL server 2005
Update: Or better still, you are better off using the following.
m_sqlCommand.Parameters["@XmlDocument"].Value = new SqlXml(new XmlTextReader(xmlDocumentToPush, XmlNodeType.Document, null));
0
Database
September 1st, 2004 at 1:17 pm
I got this info so just thought would put it here for my reference and for yours.
Of course, you can still obtain a list of all tables in the database
by executing the following: select * from sysobjects where type = ‘U’.
However, there are some major differences between the TABLES view and
sysobjects:
The TABLES view only displays tables on which the user has
permissions. It is a useful way of allowing users to see only what
they are supposed to know about.
The TABLES view is part of a standard. The sysobjects table is not
guaranteed to stay the same.
However, this does not mean that you will never use the sysobjects
table anymore. In fact, my previous select from sysobjects points out
a crucial difference. By using the “where type = ‘U’,” I eliminate all
system tables from the list. There is no way to differentiate between
system tables and user tables in the TABLES view. (However, in the
TABLES view, system tables such as sysobjects are listed with a
TABLE_TYPE of view. Perhaps at some point system tables will be under
a separate owner rather than dbo.)
But first, we should ask the question, “What’s wrong with pulling information from sysobjects and other system tables?” Well, the standard answer is Microsoft says not to do so. Microsoft reserves the right to change any and all system tables.
0
Database
January 18th, 2006 at 11:34 am
At work I got installed the new SQL server 2005 express edition, with VS.Net 2005. Through the Visual Studio’s server explorer I was unable to connect to my SQL express. After trying different things, I downloaded the SQL Server Management Studio Express CTP. But it had so many runtime bugs, that I couldn’t help stop using it.
I even tried setting up the TCP/IP and NamedPipes protocol to ‘enabled’ in the Configuration management tool of SQL server 2005. This is done to enable remote connections.
After doing a lot of search on the internet, finally found the solution to this problem.
When you connect through the Visual Studio 2005 server explorer, you need to specify “.\SQLExpress†as the server name as opposed to “yourservername†or “(local)†or “SQLExpressâ€. Simple solution but bad problem.
I am on SQL server 2005 express now, right within the VStudio and the tool is damn good and fast.
Note: On a different note, SQL server 2005 has special “Xml†datatype which you can use to store XML documents, and dynamically “XQuery†them and setup relational database type of functionality. I am going to explore it now.
0
Database
