sql

Sql Excution Plan Shows Different result for same inputs

declare @name varchar(156) set @name ='sara' --Query 1: SELECT [PNAME] FROM [tbltest] where [PNAME] like '%'+@name+'%' --Query 2: SELECT [PNAME] FROM [tbltest] where [PNAME] like '%sara%' suppose that there is a NoneClustered Index on [PNAME] column of [tbltest]. when running Queries, Excution plan show index Seek For Query 1 an...

Ordering a WITH RECURSIVE query in Postgres

Hi, I'm executing a recursive query in Postgres to retrieve a list of emails and their threaded children as follows: WITH RECURSIVE cte (id, title, path, parent_id, depth) AS ( SELECT id, title, array[id] AS path, parent_id, 1 AS depth FROM emails WHERE parent_id IS NULL UNION A...

help me generate sample data using sql script

I would like to write the SQL (MS SQL 2005/08) script to insert random large number of data in the database to test the system again heavy data. Now consider that i have sample of data like below for some columns stats [fail, pending, success, done, successful, partial_done] date [range preferably two end dates] Now can i write SQL...

Slow ORDER BY in large table.

Hi SQL experts, I have problem with ORDER BY clause. When I remove ORDER BY in following query, query is finished in 0.004 seconds. If I keep it, query is running very slow = 56 seconds. It is because MySQL is first grabbing all 500 000 records, then sorting and finally returing only first 18 records. How can I solve this big table ord...

Rewrite SQL query using Linq to entity.

Hi guys I need to rewrite the sql query below using Linq to entity. Unfortunately I don't have a lot of experience of using Linq. Please help me With TempTableName AS (SELECT [ColumnName], [ColumnName2], [ColumnName3], [ColumnName4], ROW_NUMBER() OVER (order by ColumnName desc) as RowNumber from TableNam...

how to join two db tables and cross reference another

I'm trying to figure out an efficient way to join tables and cross reference with another table, with the reference looking to check that each record found in the join does not have a field which exists in the other table. This is what I have so far - I somehow need to be able to make sure that each record's reg_no is not present in th...

SUBSTRING related problem

I have string type abc_01, abcd_01 or 02 now I want the substring up to _ ie abc_,abcd_ etc. I am using db2 as400. Please suggest the processing through RIGHT or LEFT function. ...

convert sqlite database to SQLAnywhere ?

is their any tools to convert SQLLite database to SQLAnywhere format ?,the purpose to use SQLLite on BlackBerry devices with software version less than 5 ...

SSRS 2008 - Reporting Services Webpage is Blank except for headers

I'm getting blank page when I open the Report Manager: I've got a header - "...Reporting... HOME", and then just empty space. I'm on SQL 2008 and Windows Server 2008 R2 Standard. Any ideas how to fix? I've confirmed that I am logged in as a local administrator. I tried adding the URL to trusted sites but that didn't seem to help eith...

DataReader.GetString() via columnname

Dictionary Fields = new Dictionary(); for (int i = 0; i < reader.FieldCount; i++) { Fields.Add(reader.GetName(i), i); } this._MyField1 = reader.GetString(Fields["field1"]); this._Myfield2 = reader.GetInt16(Fields["field2"]); doing this makes me want to cry but i can't seem to figure out how to use the type specfic retrieval meth...

Cannot set entity properties in NHibernate event listener.

Hi all I'm overriding NHibernate's PreInsertEventListener in order to set the entity's DateCreated property to DateTime.Now. Here is my code: public bool OnPreInsert(PreInsertEvent e) { DomainObject domainObject = (DomainObject) e.Entity; if (domainObject.CreatedById == 0) throw new Exception("The " + domainObject.GetType().Name +...

Unable to set a URL as Default value in MySQL

Hi all, I am using phpMYAdmin to set a URL as a Default Value to a Field of a Table in MySQL database, but of no luck. The URL is like this one: http://www.kaboodle.com/hi/img/c/0/0/b2/9/AAAADMEBXEUAAAAAALKWPw.jpg Here is the Table field structure: {varchar(255), utf8_general_ci, Not Null} ...

Getting the most recent records between 2 tables

Hello, This is a continuation of this question. I worked all weekend on this problem without solving it. I'm totally stumped. Essentially, I inherited an interesting table structure. This table structure looks like the following: GroupTable * ID * Name * Description GroupItem * ID * GroupID * Name * CreatedBy * CreationDate OpenG...

Inline Table-Valued UDF vs. A View - Which to use?

I recently created a couple inline table-valued UDF's and then referenced them in a couple views using Cross Apply in one case and Outer Apply in another case. After I got it working and tested, I thought it was a pretty cool use of UDFs and Cross/Outer Apply's. But then it occurred to me that I probably could have done the same thing us...

How does the .net SqlConnection object check that a connections string is syntactically valid?

System.Data.SqlClient.SqlConnection foo = new System.Data.SqlClient.SqlConnection(); foo.ConnectionString = "Data Source=myServer;Integrated Security=SSPI;"; // exception thrown on this line If I declare a new SqlConnection object in my .net code and then set the ConnectionString field to a syntactically invalid connect...

CSV Import with Validation

I have a need to import a number of CSV files into corresponding tables in SQL. I am trying to write a stored procedure that will import any of these CSV files, using a number of parameters to set things like file name, destination name etc. Simple so far. The problem comes because of the structure of this DB. Each data table has a numb...

Comparing two rows for the same table multiple times

I have a SQLServer table that contains snapshot data. I need to compare the latest row against the previous and determine if certain columns have changed. I need to do this several times comparing different combinations of columns, up to 40 at a time. Every time a column value differs within one of the combinations I need to create an xm...

Does where 1=2 get called for every row?

I have some sprocs that need a temp table. In order not to hardcode the column types (which are varchar with some length) so I don't have to change the declarations when the reference table schema changes (i.e. fields become longer) I do this (instead of a create table call): select orderId into #sometmptbl from orders where 1=2 Howev...

Problem modelling address generalization

Hello, I’m a bit concerned about how to model this rather simple scenario the best way in UML and ERM. When designed it has to be implemented in C# (Entity Framework 4) and SQL Server 2008. The case is that I have a person, who has a postal address. Now this address can be two types, either a postal box, or a house identifier (house nu...

Is there a way to get the first active bit through SQL (MySQL)?

I have a column named a, and a column named b. I want to fill with an UPDATE ... SET b = ... query the b column, so that it contains the first bit that a has set to 1. Okay, you probably didn't understand, it's much easier to understand with an example: a = 2508 = 0x9CC = 0100111001100 b = 4 = 0x4 = 0000000000100 a = 2080 = 0x8...