Only way I know how is to run SP_who2 and see what connection is actually blocking the query. Then killing the connection based on the spid.

You need to split the Unit values apart. Sort on the Numeric part first, then the Alpha part.

SELECT Building,

unit,

CONVERT(INT,RIGHT (unit, (len(unit)...

mj obi-wan kenobi (9/5/2008)

---

Thanks a lot for the info.

This seems to make sense to an extent. But in my case this works fine on one SQL server running 2005...

try this...

DECLARE @XML XML

SET @XML = '<root><Users><Account AccountID="10"/><Account AccountID="20"/></Users></root>'

SELECT u.a.value('@AccountID','int') AccountID

FROM @XML.nodes('//Users/Account') as u(a)

AccountID

-----------

10

20

SET @XML = '<root><Users><Accounts><AccountID>10</AccountID><AccountID>20</AccountID></Accounts></Users></root>'

SELECT u.a.value('.','int') AccountID

FROM @XML.nodes('//Users/Accounts/AccountID') as u(a)

AccountID

-----------

10

20

This is complicated by having to make it SQL 2K compliant. If you were only using 2K5 you could use [n]varchar(MAX) and be set.

The only other option IMHO is...

Two different scopes. If you utilize the ADO transaction everything called with that scope will be in the transaction. But if you handle the transaction within the SP you can...

This is a case of scope. The INFORMATION_SCHEMA VIEWS and the 2005 system views (sys.objects, sys.tables, etc) require you to be in the database you are looking at.

This is...

Sounds like SQL Server was setup with Binary Sort as the default collation. I would also bet that all your column names, table names, etc are case sensitive as well....

Try adding the keyword "DISTRIBUTED" to your begin transaction statement.

IE:

BEGIN DISTRIBUTED TRANSACTION

...

This will change the way the transaction locks data. I'm pretty sure I had that working with SQL...

Been there, done that. Have fun! 🙂

Create Table dbo.Region

(

RegionID int identity(1,1) NOT NULL,

RegionName varchar(255) NOT NULL,

...

Is all this data coming from a single sheet?

If so do the following...

Recreate the flat table in SQL but add an Identity Column to it (Your PK for...

The Scope_Identity function call returns the last ID inserted into the identity field. Thus, if you need to add child records you use that value for their inserts.

From now on, when doing data manipulation on a production server, I would recommend placing the action queries in a transaction with a select statement checking the data. Only if...

I think you are going to need to use CROSS APPLY against your variable table. Without some sample XML and knowing what you have in your variable table it will...

You would be better off to create a Stored Procedure that has a parameter for the ID of the record you want to copy, and uses it as for your...