I've never worked with Database Functions, but my current project requires it. I need to put a common sql query into a function so we don't have to type it out in our code hundreds of times. I've got the function created, but I don't know how to use it. Here's the function code: USE [DB_NAME] GO SET ANSI_NULLS ON GO SET QUOTED_IDENT...
Hi I have a table listing people along with their date of birth (currently a nvarchar(25)) How can I convert that to a date, and then calculate their age in years? My data looks as follows ID Name DOB 1 John 1992-01-09 00:00:00 2 Sally 1959-05-20 00:00:00 I would like to see: ID Name AGE DOB 1 John 17 ...
I understand that the "Cannot sort a row of size 9754, which is greater than the allowable maximum of 8094." from SQL server is caused by a row that has over 9k characters in it, which is greater than the page size limit in SQL Server 7. But I'm not calling an order by on the data below, so why does the error say it cannot sort? SELECT...
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 need to install SQL server 2005 sp3. I need to know before if the installation need a reboot and if the installation will stop my SQL service. I got over 300 database on my server. thanks. ...
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 an application that allows my users to make temporary changes to an ongoing booking. I have recenlty read about the new XML datatype in SQL server, and the corresponding SqlXml type. I am thinking of using the new type to store the previous values of the object so that I can later revert to them. Something like: Booking Table:...
We're in the process of switching from Windows Server 2003 to Windows Server 2008, and we've encountered a frustrating problem: In the old environment, some developers used sql server 2005 and others used sql server 2008. No problems. Our connection strings all pointed to: localhost/sqlserver. Now, on the new Server 2008 environment, Sq...
Hi, So I need an SQL function that will concatenate a bunch of row values into one varchar. I have the functions written but right now I'm focused on what is the better choice for performance. The Scalar Function is CREATE FUNCTION fn_GetPatients_ByRecipient (@recipient int) RETURNS varchar(max) AS BEGIN DECLARE @patients varchar(m...
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...
We had the following query in sql server: SELECT b.columnB, b.displayed_name AS displayName, c.type_cd, c.type_desc, b.month_desc AS month FROM table1 a, table2 b, table3 c WHERE b.region_code *= a.columnA AND c.program_type_cd *= a.program_type_cd which, in oracle, got converted to: SELECT b.columnB, ...
To All, We are wanting to use Extended Properties as part of our database documentation. One of my co-workers mentioned that on a prior project they used Extended Properties and it impacted database performance. I have been searching the internet for any corroborating information in regard to this. I've not found anything. Has anyon...
my goal is if I have this: colmuns c1 | c2 | c3 | c4 | c5 | n.. row1 a | a | a | a | a | row2 b | b | b | b | b | rowN... I want to do a query that would return myCol aaaaa bbbbb nnnnn... I know I can do this select t2.id, ( select * from mytable t1 where t1.id= t2.id fo...
How do I get the execution plans for a Stored Procedure? ...
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...
Hi friends hope all are doing well. I am facing one problem while saving data into sql server database from xml file using datatable using vb.net i.e. "Conversion from type 'DBNull' to type 'String' is not valid". But not always getting error may be based on xml file. Below follows one of my xml files. I used to generate xml file randoml...
I want to write a query to see if a category field is within a certain range. The problem is the field can contain null, text or numeric text prefixed by '#' character. Does anybody know of SQL that will strip the non numerics and allow me to do the following check. category > 1 and category < 100 Here is a sample of what the field...
I have defined a class FieldProperty with some of properties corresponding to field defined in it.ArrayList object contains objects of class FieldProperty. now i want to store data in table .There is one record in table corresponding to each object of FieldProperty.if i call query for each record insertion.It will be very slow.Is there a...
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...