Truncating a Table from MSSQL Server

I was having a problem truncating a table in MSSQLServer 2008.

Kept getting an error similar to this one:
Cannot find the object because it does not exist or you do not have permissions , error in SQL Server

The following code worked for me:

Exec [PutDatabaseNameHereAndRemoveBrackets].sys.sp_executesql N'TRUNCATE TABLE dbo.[PutTableNameHereAndRemoveBrackets]'

Connecting to an upgraded SQLServer

I had a database box with SQLServer2000 on it somewhere.  let's say the IP Address was www.xxx.yyy.zzz

I upgraded to sqlserver 2008.  Maybe I didn't upgrade, maybe I added a new instance who knows.  The old sqlserver2000 wasn't running and the new sqlserver2008 was.  On a different port.  My code connection strings became www.xxx.yyy.zzz/MSSQLSERVER2008

I was trying to connect to this new database for weeks using the SQL Management studio.  No dice.  Read all sorts of articles.  Made sure Remote Connections were enabled and that the SQL Browser service was running.

In order to connect to this database I had to enter the servername as:  www.xxx.yyy.zzz,PORT

so something like 333.444.555.666,4567

Worked like a charm.  Sucked when it didn't work and I had to log into Remote Desktop to do anything.

Can't log into SQL Server

Had to reboot one of my servers today and after it loaded, I couldn't access or log into my local SqlServer at all.

I had changed my windows login password a few months ago but I guess I never rebooted back then.  After some digging I was able to find this solution:

I actually managed to solve this problem...i think my SQL Server was set to mixed mode which is why i was having problems with my new win2k password--

here's how i did it (for others who are facing teh same problem)
in win2k-
-go to the control panel
-click on administrative tools
-click on services
-right click on MSSQLServer and choose properties
-click on logon tab
-enter and confirm new password
-restart SQL Server

its actually in the help section (for SQL Server 2k) under the heading "Changing Passwords and User Accounts" and there also is info there for win 4.0 NT.

Importing XML into SQL Server 2008 using asp.net

Last week I needed to Import an XML file into a sql server 2008 database. Something I thought would be done often and should be fairly easy. Not being something I was familiar with or not in any of the books I had here at home I did some fruitless searches, and then posted on 2 forums that I thought would be able to help point me in the right direction. Neither of those sites generated even a single response.

http://www.daniweb.com/forums/thread232967.html

http://forums.asp.net/p/1485040/3475429.aspx

So with no responses after 3 days and my question now past page 3 with over 100 views I figured no one could help and I was just going to have to spend a day figuring it out.

Here was my post:

 

Hi,

I need a little help importing an XML file into sqlserver 2008.  I followed this example

http://www.sql-server-performance.com/articles/dba/Loading_XML_data_into_SQL_Server_2008_p1.aspx

(make sure you goto the rest of the pages in the article!) , generated the schema etc., but I can't get the import to do what I need. I was able to add the DataConversion on my own but I can't seem to figure out how to get both ID fields in the middle table.
I made a simplified version.  For this example I have 3 tables.  Books, BookAuthors, Authors.

Books Table (bookID, bookTitle, bookNumPages)
BookAuthors Table (bookID, authorID)
Authors (authorID, authorName)

After the Import the tables should look like this
Books
79, Some Good Book, 300
112, Another Book, 350
BookAuthors
79, 200
79, 205
79, 215
112, 200
Authors
200, First Author
205, Second Author
215, Third Author

The Problem I'm having is getting the BookID and AuthorID into the BookAuthors Table.  I'm just wondering if there is an easy way to set this up to Import or if I'm going to have to code it line by line.

<?xml version="1.0" encoding="UTF-8"?>
<result>
    <books>
        <book>
            <bookID>79</bookID>
            <bookTitle>Some Good Book</bookTitle>
            <bookNumPages>300</bookNumPages>
            <authors>
                <author>
                    <authorID>200</authorID>
                </author>
                <author>
                    <authorID>205</authorID>
                </author>
                <author>
                    <authorID>215</authorID>
                </author>
            </authors>
        </book>
        <book>
            <bookID>112</bookID>
            <bookTitle>Another Book</bookTitle>
            <bookNumPages>350</bookNumPages>
            <authors>
                <author>
                    <authorID>200</authorID>
                </author>
            </authors>
        </book>
    </books>
    <authors>
        <author>
            <authorID>200</authorID>
            <authorName>First Author</authorName>
        </author>
        <author>
            <authorID>205</authorID>
            <authorName>Second Author</authorName>
        </author>
        <author>
            <authorID>215</authorID>
            <authorName>Third Author</authorName>
        </author>
    </authors>
</result>

After some digging SQLXMLBULK load seemed like a good way to go.

The 1st problem (of course) is that for some reason this was not included in sql server 2008.  You need to download and install Microsoft SQLXML 4.0 SP1 from this location it’s WAAAAY down near the bottom of the page http://www.microsoft.com/downloads/details.aspx?FamilyId=228DE03F-3B5A-428A-923F-58A033D316E1&displaylang=en

The next thing to do was to code the .xsd file or schema using sql:relation sql:is-constant and setting up the Parentkey ChildKey relationships with sql:relationship

Since this was a test environment I was able to make the column names the same, CASE is Sensitive.  If you are not in control of both fields you’re going to have to map each element in the xml feed to it’s column in the sql table like this

        <xsd:element name="FName"  
                     sql:field="FirstName" 
                     type="xsd:string" /> 

 

Here is my finished schema:

 

<?xml version="1.0" encoding="utf-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
    <xsd:annotation>
        <xsd:appinfo>
            <sql:relationship name="BooksBookAuthors"
                parent="Books"
                parent-key="bookID"
                child="BookAuthors"
                child-key="bookID" />
        </xsd:appinfo>
    </xsd:annotation>
    
    <xsd:element name="result" sql:is-constant="1" >
        <xsd:complexType>
            <xsd:sequence>
                <xsd:element name="books" sql:is-constant="1" >
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element maxOccurs="unbounded" name="book" sql:relation="Books">
                                <xsd:complexType>
                                    <xsd:sequence>
                                        <xsd:element name="bookID" type="xsd:integer" />
                                        <xsd:element name="bookTitle" type="xsd:string" />
                                        <xsd:element name="bookNumPages" type="xsd:integer" />
                                        <xsd:element name="authors" sql:is-constant="1" >
                                            <xsd:complexType>
                                                <xsd:sequence>
                                                    <xsd:element maxOccurs="unbounded" name="author"  
                                                                    sql:relation="BookAuthors"
                                                                    sql:relationship="BooksBookAuthors">
                                                        <xsd:complexType>
                                                            <xsd:sequence>
                                                                <xsd:element name="authorID" type="xsd:integer" />
                                                            </xsd:sequence>
                                                        </xsd:complexType>
                                                    </xsd:element>
                                                </xsd:sequence>
                                            </xsd:complexType>
                                        </xsd:element>
                                    </xsd:sequence>
                                </xsd:complexType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
                <xsd:element name="authors" sql:is-constant="1" >
                    <xsd:complexType>
                        <xsd:sequence>
                            <xsd:element maxOccurs="unbounded" name="author" sql:relation="Authors">
                                <xsd:complexType>
                                    <xsd:sequence>
                                        <xsd:element name="authorID" type="xsd:integer" />
                                        <xsd:element name="authorName" type="xsd:string" />
                                    </xsd:sequence>
                                </xsd:complexType>
                            </xsd:element>
                        </xsd:sequence>
                    </xsd:complexType>
                </xsd:element>
            </xsd:sequence>
        </xsd:complexType>
    </xsd:element>
</xsd:schema>

Remember that XMLBulkload thing? Create a reference to it, when done it will add Interop.SQLXMLBULKLOADLib.dll to your Bin Folder

 

After that I had to write the code to do the import:

            Dim objbl As Object
            objbl = CreateObject("SQLXMLBulkLoad.SQLXMLBulkLoad")
            'objbl.connectionstring = "provider=SQLOLEDB;data source=SQLSERVERINSTANCENAME;database=TESTDATABASE;integrated security=SSPI"
            objbl.connectionstring = "provider=SQLOLEDB;data source=SQLSERVERINSTANCENAME;database=TESTDATABASE;user id=sa;password=SUPERSECRETPASSWORD"
            objbl.ErrorLogFile = "c:\error.log"
            objbl.Execute("c:\temp\BookExample.xsd", "c:\temp\BookExample.xml")
            objbl = Nothing

I left the line commented out incase you want to use the Windows Integrated Security, it’s easy to switch back and forth. For me I was using the SQLServer authentication and it was near impossible to find the connection string formatted  correctly as this is not how it appears in my web.config file

 

That should be everything.  Hope this helps someone.