I have a query that uses for xml explit to return XML result. select ... from ... order by [BatchType!1!TypeName], [FormType!2!TypeName], Tag, Parent for xml explicit, root('ClientImages') But the name of resultant column name is something as cryptic as Is there a way to change the column name? [ANSWER] I had a several nested ...
Hi I dont have any idea why am i getting this error :- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated. I was trying to run this query:- ALTER TABLE Test1 NOCHECK CONSTRAINT ALL ALTER TABLE Test2 ...
I want to display Alias name based on the value of some other column name in the query in SQL Server. For e.g. SELECT P.Amount AS (CASE P.Type WHEN 'Individual' THEN 'Salary' ELSE 'Profit' END) FROM Person P I know the above is not right, but something like this will help. ...
Hi, we have a huge table of companies (17 million entries) for which we want to find duplicates according to search critera (based on phone number). The query runs very slow (5 minutes+) Here is a simplified version of the query, but the problem is the same : SELECT C1.* FROM dbo.Company AS C1 WITH(NOLOCK) INNER JOIN dbo.Company AS C2 ...
hello everyone. Im current implementing the permission like this: Permission UserPermision User ------------------- ---------------------- ----------------- PNAME PID PID UID PVAL UID UNAME This concept work fine, but at somepoint, we will want to add a Permissio...
I have a database table containing fields RACE, ETHNICITY and ETH. I need to evaluate RACE and ETHNICITY fields and populate ETH using a series of cases: if Race = W and Ethncity = 1 then ETH = Caucasian etc. Can someone advise me on the best way to structure a stored procedure to accomplish this? ...
I have a table to log site visits and it has over 100,000 records. There don't seem to be any performance issues, but should large log type tables be regularly moving records to an archive table and clearing out the current table? ...
I have a table with 117000 or so records. I need to perform a search that checks 3 separate fields for a given string pattern. My where clause is as follows: field1 LIKE '%' + @DESC + '%' OR field2 LIKE '%' + @DESC + '%' OR field3 LIKE '%' + @DESC + '%' This seems to take about 24 seconds regardless of input... Is there a better way ...
I have a job that performs several validation checks on rows in several tables in a database. If the check fails it logs the failure in a table that. The information that is logged includes the table name, a uniqueidentifier value of the row that failed, which check it failed, and which job was being run at the time. Here's the simpli...
select A.regYear, A.regMonth, isnull(A.regCount,0), isnull(B.regCount,0), isnull(C.regCount,0), isnull(D.regCount,0), isnull(E.regCount,0), isnull(F.regCount,0), isnull(G.regCount,0),isnull(H.regCount,0),isnull(I.regCount,0),isnull(J.regCount,0), isnull(K.regCount,0),isnull(L.regCount,0),isnull(M.regCount,0),isnull(N.regCount,0), isnull(...
Hi there. I have 3 tables, tblBook(BookID, ISBN, Title, Summary) tblAuthor(AuthorID, FullName) tblBookAuthor(BookAuthorID, BookID, AuthorID) tblBookAuthor allows for a single book to have multiple authors and an author may have written any number of books. I am using full text search to search for ranking base on a word: SET @Word ...
I would like to know if I can get the average of a sum in one single SQL SERVER request, Have tried to do it with the following request but it doesn't work: SELECT t.client, AVG(SUM(t.asset)) AS Expr1 FROM TABLE t GROUP BY t.client ...
I have the following script: SELECT left(SHI.FSOKEY, 6) AS [SoNo] , substring(SHI.FSOKEY, 7, 3) AS [So Item] , right(SHI.FSOKEY, 3) AS [So Rels] , QAL.FCLOT AS [LotSerial] FROM shmast SHM INNER JOIN shitem SHI ON SHM.FSHIPNO = SHI.FSHIPNO INNER JOIN qalotc QAL ON SHM.FSHIPNO = Left(QAL.FCUSEINDOC, 6) ...
I don't have much t-sql experience, but I came up with this function to pad 0's to the left of an nvarchar parameter passed in. The number of 0's padded to the left is based on the original length of the parameter passed in. I will explain each variable and then display my function. I tested it out and it does work, but I am curious i...
I want to create a simple summary report in Reporting Services using age, account and age group as follows: SELECT AGE,COUNT(ACCOUNT)AS TOTALCASES, 'AGEGRP' =CASE WHEN AGE <=5 THEN 'AGE 0 TO 5' WHEN AGE >=6 THEN 'AGE 6 AND OLDER' END FROM MAIN GROUP BY 'AGEGRP' When I run this in SQL Ser...
I'm having trouble executing a stored proc... I've got C# code that tries to call the stored proc. It looks somewhat like this: DataTable myDataTable = new DataTable(); using (SqlConnection connection = new SqlConnection(myConnectionString)) { SqlCommand selectCommand = new SqlCommand("MyStoredProc", connection); selectCommand...
My company has recently put up a demo database on his remote servers to allow some of our partners to test our beta version of the software. We noticed, as expected, some bottlenecks in some parts of the program, in particular on the places in which many queries are done. For example, I have to load customer from database, with all...
Is there a way to find a statistics on table read and write count on SQL Server 2005/2008? I am specifically looking for DMVs/DMFs without using triggers or audits. The goal here is to find out a**ppropriate fill factor for indexes** - got an idea from this article (Fill Factor Defined). [UPDATE] Follow up question on ServerFault...
If I run the following in Management Studio (SQL Server 2008) : exec [USP_CNT_BookingDetail_ExtractAccountingPlanData] '4AFD6633-CB90-4165-913D-EE3EA74708DA', '7EF7CCB2-E09F-4408-AE2D-F857C063F2C1' I get the result back in less than a second I however I run it in VB.Net like this : Using aConnection = New System.Data.SqlClient.SqlCo...
Can somebody advise idea, how to match user input (few words) to appropriate tags in system (each of them also 1 or N words)? Here is sample to demonstrate problem: I have a Tags, assigned to objects. For example (tags are separated by COMA, but in real life I have relation to table) Object Tags Earth ...