I've been using SQL Server profiler quite frequently to check for redundant or badly performing queries. But is there an event (among the giant list) that allows you to log the total amount of time it takes to transfer the data from the data base to the application? This would be a very good indicator for queries that return way more d...
I am running the following command: EXEC @ReturnCode = master.dbo.xp_cmdshell @cmdline On the Results tab I get 2 lines Could not find a part of the path '\server\directory\filename'. NULL How do I capture the first line in an error message? I tried using a Try Catch block with "SELECT @ErrorMessage = ERROR_MESSAGE()" and it doesn...
I have the following tables: Entry EntryID - int EntryDate - datetime Hour EntryID - int InHour - datetime OutHour - datetime For each registry in the Entry table, there should be at least one (could be many) registries on the Hour table, like so: Entry EntryID: 8 EntryDate: 9/9/2010 12:31:25 Hour EntryID: 8 InHour: 9/9/2010 1...
So I got a varbinary(max) column in SQL Server 2005 and it's full of XML. A few records somewhere have truncated XML so they're invalid. This means if I run a SELECT CAST(myVarbinaryColumn as XML) ... it blows chunks. How can I filter out/skip invalid xml ? When I've done similar with a varchar that supposedly has dates I could u...
I'm using SQL Server 2000 and my ERP vendor has used a Text datatype field to hold Dates. The Dates look like: 6/21/2001 However, some of them are blank. I've tried using something like: select coalesce(nullif(fdelivery, ''), '1/1/1900') FROM sorels to remove the blanks to no avail. I've also tried: case when ltri...
Hi All, I have a scenario where I need to round and then remove the extra zeros from numbers. So if I have a number that I have rounded (12.456400000) I want the zeros to be removed. Is there a function I can use to remove those numbers? The round function appears to leave the zeros in place? As always greatly appreciate the input. ...
I have a script that needs to insert 50+ rows into a table, is there a way to loop though each row I want to insert, rather than coding this below statement 50 + times in TSQL? IFEXISTS ( SELECT 1 FROM table where column 1 = ) UPDATE table Column1 = value, Column2 = value, Column3 = value, Column4 = value WHERE c...
Trying to execute an update of a single field where the current db wants to pull values from last night's backup. Should be something close to: update myTable set status = (select status from .lastBackup.dbo.myTable as t where t.idField = idField) Help? ...
Database : SQL Server 2005 Problem : Copy values from one column to another column in the same table with a billion+ rows. test_table (int id, bigint bigid) Things tried 1: update query update test_table set bigid = id fills up the transaction log and rolls back due to lack of transaction log space. Tried 2 - a procedure on fol...
Hi All, Where can I find a list/explanation of Extended Properties for Microsoft.ACE.Oledb.12.0 Thanks ...
I have a table that contains multiple records per employee by effective data and effective sequence: EMPLID, EFFDT, EFFSEQ. I am trying to come up with a view in which I could specify an As Of Date other than the current date and get the most current record for each employee as of that date. The query that I usually use is: SELECT C.E...
I want to write a query in T-SQL to perform a search on two concatenated columns. The two columns are fname and lname. Here is what I have so far: SELECT fname, lname, ... FROM users JOIN othertable ON foo=bar WHERE fname+' '+lname LIKE '%query%' SQL server doesn't like that syntax, though. How do I structure t...
hi friends, http://pastebin.ca/1946913 When i write "IN(1,2,4,5,6,7,8,9,10)" inside of the procedure, i get correct result but when i add the id variable in the "IN", the results are incorrect. I made a function on mysql but its still not working, what can i do? ...
I'm currently doing some maintenance on an application and I've come across a big issue regarding the qualified name in tsql. I wondering if someone could clear my confusion up for me. From my understanding you want to use USE [DatabaseName] to declare which database you are using. I notice if u "rename" the databse it automatically u...
How to convert VARCHAR time like '18:15' to DateTime like 23/09/2010 18:15:00. There can be any date format I just want to have date and time. The reason is I have a column in db time of VARCHAR(5) type. I have input, time in string, from user. Which I want to compare like this WHERE MyTable.Time < @userProvidedTime ...
I have one database that has a bigger size than expected, how can I query it to retrieve the fields that take more space. let's say I have 2 only tables Table1.FieldOne Table1.FieldTwo Table2.FieldOne Table2.FieldTwo Let's say 99.9% of DB data is in Table2.FieldTwo, how do I query the db? Of course I have many many tables. The db i...
I have a column in an SQL Server 2005 DB which contains an XML stored as a string. Within that XML is the following element tree <DriverDetails> <DriverDetail> <ID>2334</ID> <PRN>1</PRN> </DriverDetail> <DriverDetail> <ID>2335</ID> <PRN>2</PRN> </DriverDetail> <DriverDetail> <ID>2336</ID> <PRN>3</PRN> ...
SQL Server 2008 Hello. New templates I create in Template explorer are not showing in the solution explorer Template explorer > Stored Procedure > New folder - Custom > usp_NewStoredProcedure > Save. I can then see new SP in template explorer. However when I try and use the new template in Solution Explorer Solution Explorer > ...
I googled. I asked in forums but I could not find any forum, newsgroup or discussion boards for "trainers" (teachers, coachers) and supporters (writers of courses, sysadmins of labs, et al) of professional courses for IT developers Might be the certified by major vendors trainers have the sites with forums but they are closed to non-...
My scenario: I can change the ordinal position of a column in a table.Is there a way to change the ordinal position of a column in a table without recreating the table? ...