sql

How to clean a columns with a dots at the beginning and end of a text using T SQL for SQL Server?

Description: I have a column(EmailsAdress) on a table(BusinessUsers) on my databases that stores email address. Problem: Some of the rows of data have a dot at the beginning of this column for example [email protected] (The dot i want to get rid of is the dot just before the charater j in jane) Some of the rows of data have a...

Most efficient way to count rows of a query

I'm using Hibernate to retrieve the number of rows for a specific query. Let's say I have a table called 'Person' with various columns. One of those columns is 'name'. If I wanted to get the number of people with the name of 'Andrew', which of these ways would be most efficient? Assuming there is a performance difference between some...

Put back deleted row from another db while preserving id field

Hi, I have SQL 2005 databases. I have deleted a row from one of them and want to get it back from another database that was a backup of the row. How do isnert it while preserving its id primary key identity field? Can you give TSQL to do this assume databases are called "tbrPdata" and "tbr0910" which is the backup? Malcolm ...

windows authentication to SQL with AD username and password in connection string

Hi I'm writing an ASP.Net MVC intranet application which is accessed via handhelds and a range of browsers. My users all have AD accounts and have different permission to our SQL database views/procs depending on who they area. The database also uses CURRENT_USER for horizontal partitioning. Because of the range of devices that nee...

real_escape_string vs. prepared statements

is there any reason to use one over the other in terms of speed and safety? Thanks! ...

How to return different strings from a Boolean type in MySQL?

If I have a column set up as Boolean in MySql, a query returns the value as either 0 or 1. Is it possible to do something like this SELECT `bool_value` AS "yes" OR "no" What I mean is, return two different strings based on whether it is true or false. ...

Execute multiple aggregates at once (SQL, LINQ).. or with better performance?

I have a table with records which include a datetime column "CreationDate". I need to get the following information for every of the last 90 days: How many records were there in total in existence How many records were added on that day I could do this through a loop of counting of course, but this would hit the database 90 times.....

Update/Increment multiple mySQL columns in one query

I have this query, which works... UPDATE `contacts` SET `calls_to`=`calls_to`+1 WHERE `contact_no` = '0412345678'; What I also want to do is add a value to the cost field. From my understanding, the way to do this would be... UPDATE `contacts` SET `calls_to` = `calls_to`+1, `cost_to` = `cost_to`+0.25 ...

" Not able to open my report on report manager"

I have built a report on SQL server business intelligence development studio ( microsoft SQL server 2005). After that when I try to deploy a solution in SSRS 2005, I get a dialog box with title: "Reporting Services Login". It has my server listed as the first row: "http://localhost/reportserver" and then it has two text boxes asking for ...

SQL audit recommendation

I am working on creating audit policy for SQL server tables for my application. I need to track all the insertion/updation/and deletion operations on the tables so that this can be used for the audit reports. I am planning to create audit table with the same structure for each table and define triggers on the main table to populate the a...

Trace what DB a stored Procedure is located from a script

is there a way to make a query which DB the Stored Procedure is located? I got this bunch of Databases doing a lot of Cross DB query from each other, now I got lost with 1 stored procedure and I just want to look from which DB it is located. I want to make a script that will return the DB name of where that stored procedure is located. ...

Iterate list of Objects in Ibatis

Hi. I have a list of object where I want to iterate and access a particular field in ibatis sql. Ex. public Class Student { String id; String name; } I will pass as parameter a List of Student object(List(Student)) and do iteration accessing the id for each object bean. How do I do this? ...

creating a trigger to update orderdata

I've created a trigger in ms sql server 2005. The trigger is supposed to update orderdata. Example: table id order 1 1 2 2 3 3 4 4 Update so that id 4 has order 2 would create. table id order 1 1 2 2 3 3 4 2 With my trigger, after this it would automatically create. table id order 1 1 2 3 3 4 4 2 My table obviously l...

Select DISTINCT items rows and associated columns based on a condition

Let's say I have a table like this in my DB Date ItemId Count 7/29/2010 3 1 7/30/2010 3 2 7/31/2010 2 3 7/29/2010 3 4 7/30/2010 1 5 7/31/2010 1 6 7/29/2010 2 7 7/30/2010 3 8 7/31/2010 1 2 For each item type, I want to select the count on the latest date found in the table only. Is there an ea...

Modeling database : many small tables or not ?

Hello, I have a database with some information which are repeated in some tables. I want to know if it's interesting to create a table with this information and in the other table, I put only the id. It's interesting because with this method I haven't got redundance. But I will have to do many joints between my tables in my request, ...

Monitoring Oracle DB remotely

I would like to know how to do this: Environment: UNIX I would like to write a script which can be scheduled to run on a remote machine. It would read the DB details (which is residing on another machine) from a config file which looks like this: user= pass= product1_SID= product2_SID= ... Each product has its own database files. So...

sql server 2008 timeing related

I want to find out what will be the time in India when clock tick to 1Am mid night in any other country.. How i will find out that through any means plz help me to find out this this is to fire birthbay mails at 1AM midnight of that resp country... so i did following DECLARE @indianTime DATETIME --= SYSDATETIMEOFFSET() DECLARE @timeZon...

How to join tables easily Microsoft SQL Server?

I have two separate tables used for categories. One is Categories(ID, Title, Description), and the other is SubCategories(ID, UpperID, Title, Description) I want to insert the records from categories to SubCategories with upperID=0. I've looked at SQL SELECT INTO but don't know how to use it for existing tables. ...

SqlDataReader getting rows when no data

Hi! This is incredibly urgent, I need to present this application in 3 and a half hours. My application checks against a data source to see if a value exists in the database and changes values depending on whether or not the value in question was found. The problem is that I've run the sql query with the value in question in SSMS and n...

QT and SQLITE problem during building

Hi, I'm developing a simple application to use sqlite, the problem is that the following code /* ... */ QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName("hellogoodbye"); if (!db.open()) { QMessageBox::critical(0, qApp->tr("Impossibile aprire il database"), qApp->tr("Non ...