sql

Constructing the FROM in SQL

I'm looking to pull a specific line from a number of table that have a field name criteria1. The problem I'm having is that when I combine the Owner and Table Name and try to call "select criteria1 from @t where linenum = 1" SQL is expecting @t to be a table. I need to know how to construct the full table name and then pass it to this qu...

SQL Server Query for Rank (RowNumber) and Groupings

I have a table that has some columns: User, Category, Value And I want to make a query that will give me a ranking, of all the users by the value, but reset for the category. Example: user1 CategoryA 10 user2 CategoryA 11 user3 CategoryA 9 user4 CategoryB 3 user1 CategoryB 11 the query would return: Rank User Category ...

What Access Rights am I Missing on my Stored Procedure

I'm trying to run a stored procedure from my website that disables a trigger. Here is the code for the trigger: CREATE PROCEDURE [dbo].[DisableMyTrigger] AS BEGIN alter table dbo.TableName DISABLE TRIGGER TriggerName END I've also set the permissions on the stored procedure with: Grant Exec on dbo.DisableMyTrigger To DBAccountNam...

SQL query to get most recent row for each instance of a given key

I'm trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I'd like one row for each user containing the most recent ip and the associated timestamp. So if a table looks like this: username | ip | time_stamp --------------|----------|--...

Upper Limit for Number of Rows In Open Source Databases?

I have a project in which I'm doing data mining a large database. I currently store all of the data in text files, I'm trying to understand the costs and benefits of storing the data relational database instead. The points look like this: CREATE TABLE data ( source1 CHAR(5), source2 CHAR(5), idx11 INT, idx12 INT, ...

Replace character in SQL results

This is from a Oracle SQL query. It has these weird skinny rectangle shapes in the database in places where apostrophes should be. (I wish we would could paste screen shots in here) It looks like this when I copy and paste the results. spouse�s is there a way to write a SQL SELECT statement that searches for this character in the fie...

When to separate columns into new table

I have company, customer, supplier etc tables which all have address information related columns. I am trying to figure out if I should create a new table 'addresses' and separate all address columns to that. Having address columns on all tables is easy to use and query but I am not sure if it is the right way of doing it from a good d...

DTS Packages Empty

When trying to edit a package the server doesnt display package contents. When trying to create a new package the Server Explorer shuts down. No error msgs. At the same time I can connect via client tools from a pc and the packages looks fine. Has anyone seen this issue? Rebooting doesnt fix it and the machine has plenty space on all dri...

Treatment of error values in the SQL standard

I have a question about the SQL standard which I'm hoping a SQL language lawyer can help with. Certain expressions just don't work. 62 / 0, for example. The SQL standard specifies quite a few ways in which expressions can go wrong in similar ways. Lots of languages deal with these expressions using special exceptional flow control, or b...

Most efficient LINQ to SQL query for objects and children?

In straight SQL, I'd probably wrap some logic over a simple join, but how would I do this efficiently in LINQ to SQL? Imagine I have two tables: Parent ID Child ParentID ID Name Ideally I'd like a graph of Parent objects with a "Childs" property that is actually a Dictionary<int, string>. Suggestions? ...

MySQL Help

I need to run a query, and Im not sure if it's possible. I could do 2 queries but one is better right :| I have two tables. First is user, this consists of username and userid. The second is search which consists of userid and query. When I select the search table I want user ID to be replaced by username by taking the data from the use...

How do I refactor repetitive SQL update queries?

I have 3 tables. 1. Users 4 Cols UserID - User - RealName - Flags 2. UsersGroups 2 Cols UserID - GroupID 3. Groups 3 Cols GroupID - Group - Flags and I want to set the flags on the User table for User = 'Administrator' and apply the same to the Group table. I have the following SQL that works, but I also have several flags that I ...

Is it possible to use both order by and where in a single query

Hi friends, I have created a table. In one field, I have a priority of that record (between 1-9). I didn't set priority for all the records, so for some records, it will remain null. When displaying these records in my HTML page, I just check those priorities -- if the priority exists, then I will display as it is, if it's null, then...

Query to get only the duplicate data

Hi I have a table with data ID Name 1 John 2 Robert 3 John 4 Sam 5 Jack 6 Sam Now i want ony the the duplicate names ony through query ie....

checkbox data binding

How to add a check box in a .aspx page. I had used a database. and i read data from the database successfully. And now the readed data is in data table "dt". And i added this statement if (dt.Rows[0]["IsActive"].ToString() == "True") if the condition is true, Then the check box will be checked? but i didn't get. How it will get? ...

mysql query question.

Hello I have a table with articles and the articles have a column category. The categories are filled like 1,4,6,8 How can i check if there is a product which has in it for example category 5 I tried something like select * from article where category in(5); But that doesn't work. If I use like than i will have a problem with for e...

Performance consideration: Spread rows in multiple tables vs concentrate all rows in one table.

Performance consideration: Spread rows in multiple tables vs concentrate all rows in one table. Hi. I need to log information about about every step that goes on in the application in an SQL DB. There are certain tables, I want the log should be related to: Product - should log when a product has been created changed etc. Order - same ...

MYSQL: How can I create a MySQL table so, that a field's default value is the output of a function.

QUESTION How can I create a MySQL table so, that a field's default value is the output of a function. Can it be done without an insert trigger? BACKGROUND: Some entities and the groups they belong to need to be observed on a daily basis. To do that I created the following MySQL table: CREATE TABLE entity ( entity_id VARCHAR(1...

Smarter way to format sql/php code ?

Hi , i have an large SQL Statement, is there any "smarter" way to format this stuff ?? $serverFields = 'SERVER_ID, SERVERNAME, SERVERLOCATION_ID, SERVERLOCATIONDETAIL_ID, SERVEROS_ID, SERVEROSVERSION_ID, IP_BACKEND, IP_FRONTEND, IP_BACKUP, SERVERREMOTETOOL_IDS, LOGIN, DESCRIPTIO...

SQL, returning logic of value being in another query to field

This is my query as it is now. I want to add another column to the result checking if the ItemID is equal to any value in another table column (BuildComponents.ComponentID). Eg. writing 'Y' if its there and 'N' if its not. I do not want this to affect what rows are returned. I am using MS SQL 2005. The logic would be something like this:...