I have a rather expensive query that returns a page of results: SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY j.PostDate DESC) as Row, FROM JobListing j, Location l, City c, JobListing_Skill_XREF js, @SkillTable st WHERE DistanceBetween(@lat,@long, c.Lat,c.Long) <= @miles AND js.Sk...
I would like to take the data output from the query below and join all of the email addresses together separated by a semicolon grouped by the employee name. SELECT DISTINCT p.email , e.name FROM PERSON p INNER JOIN EMPLOYEE e ON p.agentofrecord_id = e.employee_id WHERE dbo.GetPersonMember(p.person_id) =...
This is similar to a previous question MSSQL: Change type of a column with numbers from varchar to int, except in this case not all of the original data can be successfully converted from varchar to int. In these cases, I want the output to just be NULL. For example, the incoming set of values in a varchar column will look like: {'123...
How do I increase the size of the transaction log? Is is also possible to temporarily increase the transaction log? Let's say I have the following scenario. I have a Delete operation that's too big for the current transaction log. I wan't to: Increase the transaction log (can I detect the current size?, can I tell how large I need th...
I want to create an indexed view that integrates data from several tables, however SQL Server complains with the error message: Cannot create index on view "MyView". The view contains a self join on "dbo.Companies". The view definition is something like this (simplified version): SELECT T.Field1 , T.Field2 , P.CompanyNam...
I work at a college and our student management systems academic year start date is determined by the Monday on or before the 1st of August. I need to match this in my query, is there a way to easily get the date of the Monday on or before this date. ...
In my TSQL table I added a constraint with the following sQL statement alter table disabledqualities add constraint uc_uIdQualCode unique (userId, qualitycode) I did it by hand because I just can't work out how the GUI is supposed to work. You add a constraint and then you get a window where you should "define" your constraint. It's b...
I created a constraint on my tsql table like this: alter table disabledqualities add constraint uc_uIdQualCode unique (userId, qualitycode) In MSSMStudio the constraint shows up under indexes rather then under constraints. Why? --EDIT-- I understand that it creates an index to enforce the constraint, but then why is there a node cal...
I have an MSSQL 2000 table that has a lot of duplicate entries. Each row has an EffectiveChange data column. I want to get the most up to date row by getting the row with the max(EffectiveChange) for each key value. This is some sample data: NPANXX TZONE EFFCHANGE RATE 555555 1 01/01/09 1 555555 1 05/01/09 6 214555 2 0...
A client has reported repeated instances of Very strange behaviour when executing a stored procedure. They have code which runs off a cached transposition of a volatile dataset. A stored proc was written to reprocess the dataset on demand if: 1. The dataset had changed since the last reprocessing 2. The datset has been unchanged for 5 ...
In a SQL Server 2008 database, I have a table with a column of type varbinary. Currently, I am using LINQ to SQL to access the database. I already know that I can delay the loading of the column. However, I wish to consume less memory by not loading all of the bytes from that value. Ideally, I would like to have a Stream to those bytes. ...
For SSRS 2005 report how do i read inconsistent nvarchar data values from database as consistent numeric decimal data for plotting a line chart? the nvarchar column has lab readings to be plotted on a graph as nvarchar(15) datatype. sample column with inconsistent data as shown sample columnvalues: 00123 102 (NULL) 333 456 N/R No Resu...
For SSRS 2005 report how do i read inconsistent nvarchar data values from database as consistent numeric decimal data for plotting a line chart? the nvarchar column has lab readings to be plotted on a graph as nvarchar(15) datatype. sample column with inconsistent data as shown columnvalues 00123 102 (NULL) 333 456 N/R No Result 567 ...
I have a need for a "run once" query (so performance isn't that critical), using tables similar to these (simplified versions of the actual): CREATE TABLE #Items ( ItemPK int not null primary key ,ItemDescription varchar(25) not null ) INSERT INTO #Items VALUES (1,'rock') INSERT INTO #Items VALU...
Hi, I need a bit of help constructing a query that will let me filter the following data. Table: MyTree Id ParentId Visible ===================== 1 null 0 2 1 1 3 2 1 4 3 1 5 null 1 6 5 1 I expect the following result from the query: Id ParentId Visible ===================== ...
Hello everybody. I'm working on a trigger which needs to re-insert its data on another table. The destination table has a primary key INT NOT NULL WITHOUT Identity, so I have 2 choices: Calculate the maximum and insert from here. Take max value from a sequences table. I use to always create a table variable with identity and inse...
Hi, One of our user did an insert statement in development server. The insert worked fine. However, when the same insert statement is executed in production server, he encountered the error below: Error:.Net SqlClient Data Provider Error Message: The conversion of char data type to a datetime data type resulted in an out-of-range datet...
here is my Storprocedure CREATE PROCEDURE [B] @BoardID varchar(5000) AS declare @sB varchar(8000) set @sB= ' ' Select name,id,address from BoardDetail WHere IsActive=1 and @sB=@BoardID GO here i send string parameter....My @BoardID contain string condition like: name=a and id=5 and address =adfas i want to supply just string ...
I am working on a console application to insert data to a MS SQL Server 2005 database. I have a list of objects to be inserted. Here I use Employee class as example: List<Employee> employees; What I can do is to insert one object at time like this: foreach (Employee item in employees) { string sql = @"INSERT INTO Mytable (id, name,...
Is there a straightforward way of finding the index of the last occurrence of a string using SQL? I am using SQL Server 2000 right now. I basically need the functionality that the .NET "System.String.LastIndexOf" method provides. A little googling revealed this - http://www.sqlservercentral.com/scripts/T-SQL+Aids/31116/ - but that doe...