I checked jamietre link, and this is the complete answer: Create table #MyTempTable (output varchar(max)) INSERT INTO #MyTempTable EXEC xp_cmdshell ‘tasklist’ select * from #MyTempTable where output like ‘ie%’ order by output Thanks for all…
Once I determined that it is the ADO.NET connection at the root of the problem, this thread led me to the answer. Basically connections through Sql Server Management Studio (SSMS) by default have SET ARITHABORT ON. ADO.NET connections do not. Setting ARITHABORT OFF and executing the query directly through SSMS gives me the same slow … Read more
You can’t update fields from multiple tables on a single update query. There error you are getting is because this is not permitted: update a, b, c Since you can only update one table per update command.
You’re very close. declare @qry nvarchar(max); select @qry = (SELECT ‘DROP INDEX ‘ + quotename(ix.name) + ‘ ON ‘ + quotename(object_schema_name(object_id)) + ‘.’ + quotename(OBJECT_NAME(object_id)) + ‘; ‘ FROM sys.indexes ix WHERE ix.Name IS NOT null and ix.Name like ‘%prefix_%’ for xml path(”)); exec sp_executesql @qry
You’d need COLLATE in both places most likely. Select City COLLATE DATABASE_DEFAULT AS Place, State, Country FROM DEPT1 UNION ALL Select ” COLLATE DATABASE_DEFAULT AS Place, ‘Arizona’ As State, Country FROM DEPT2 Edit: You may need it on every string if you get it in one places Select City COLLATE DATABASE_DEFAULT AS Place, State COLLATE … Read more
CREATE TABLE Lookups.Gender ( GenderID INT NOT NULL AUTO_INCREMENT, GenderName VARCHAR(32) NOT NULL );
Are you sure it’s not a Table-Valued Function? The reason I ask: CREATE FUNCTION dbo.chk_mgr(@mgr VARCHAR(50)) RETURNS @mgr_table TABLE (mgr_name VARCHAR(50)) AS BEGIN INSERT @mgr_table (mgr_name) VALUES (‘pointy haired boss’) RETURN END GO SELECT dbo.chk_mgr(‘asdf’) GO Result: Msg 4121, Level 16, State 1, Line 1 Cannot find either column “dbo” or the user-defined function or … Read more
Yes: SELECT * FROM sys.tables WHERE is_replicated = 1 From MSDN for is_replicated field: 1 = Table is published using snapshot replication or transactional replication.
Well, in SQL Server 2012 you could use the new TRY_CAST(), but with SQL Server 2008, you should be able to use ISNUMERIC(), and then include handling for values that do not pass that test.
A couple of the other answers touched on the problem, but this might explain. There really isn’t an order implied in SQL (set theory). So to refer to the “fifth row” requires you to introduce the concept Select * From ( Select Row_Number() Over (Order By SomeField) As RowNum , * From TheTable ) t2 … Read more