OUTPUT clause in INSERT/UPDATE/DELETE statements

just playing with the new (for sql server 2005 and for me) OUTPUT clause.
I had some hopes for it but turns out i cant write something like

insert targettable (col1,col2)
output Inserted.col1, sourcetable.somecol
select somecol1,somecol2 from sourcetable

that is - i cant output values from the Inserted and also values from the source table :(
a bit disappointing in a way, of course, because i wanted to have backwards mapping between the source and inserted tables, but now I just decided to use one of the columns in the inserted table as this mapping ids placeholder

voila...

EDIT:
according to msdn article on T-SQL Delete
it is possible to OUTPUT exactly what I need...

DELETE Production.ProductProductPhoto
OUTPUT DELETED.ProductID,
p.Name,
p.ProductModelID,
DELETED.ProductPhotoID
INTO @MyTableVar
FROM Production.ProductProductPhoto AS ph
JOIN Production.Product as p
ON ph.ProductID = p.ProductID
WHERE p.ProductModelID BETWEEN 120 and 130;

strange...now I'll have to achieve the same results somehow...

Server: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' returned an unexpected data length for the fixed-length column

I got the error described here http://support.microsoft.com/kb/920930
when running a test select * from a view on a linked server





Server: Msg 7347, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' returned an unexpected data length for the fixed-length column '[LinkedServerName].[DBName].[OwnerName].[TableOrViewName].ColumnName'. The expected data length is n, while the returned data length is m.





when searching on google web/groups the compains were for sql server 2000 and below so not much help until i decided run the same query on the linked server itself and it went through without a problem.
turned out the column in question (the one that reportedly returned larger data than expected) is empty.
so i just rewrote the query to return only a subset of data i needed and all worked nicely

so...fun...

/LM/W3SVC/1/ROOT/AppName is unavailable

So I created this neat c# web service and went to deploy it to our clustered environment.
Before that I ran the web setup on a dev server and it installed flawlessly so what a big surprise it was that when i tried to install it in production I got this lovely message:



(it says: 'the specified path 'lm/w3svc/1/root/yourappname' is unavailable. the internet information server might not be running or the path exists and is redirected to another machine. please check the status of this virtual directory in the internet services manager')

don't you love it when all is fine while you develop but when you try to deploy something maddeningly breaks...

Few hours of google search didn't bring any solutions, so i had to figure it out myself, being the genius IIS admin, that i am (not).
first i thought this might be due to our production servers being in a cluster, (mis?)lead by the '...is redirected to another machine' part of the message, but even taking one or the other of the servers off the cluster (temporarily) didn't help.
after a few similar guess/error tries i simply created a folder in inetpub where my web service would be dwelling, created a virtual folder pointing to this empty folder, run the setup again and.....it didnt break this time(!) - simply copied the contents of the web service and off we went

so i guess Okam's razor works...even for deploying c# web service solutions in IIS

lovely ;-)