I have the following issue, I have this query that select the latest 5 records created for an employee: SELECT TOP 5 p.value, p.record_date AS FECHA FROM employee_loan_movements p WHERE p.employee_code = '1' AND p.record_date <= '2009-11-11' AND p.movement_type = 1 AND p.value > 0 ORDER BY p.record...
I am working on optimizing one of the SQL Job. Here I have few places where we have used <> operator. THe same query can be replaced using NOT EXISTS operator. I am just wondering which is better way of doing it. Sample Query If(@Email <> (select Email from Members WHERE MemberId = @MemberId)) --Do Something. --Same thing can be wr...
I want to join few attributes in select statement as one for example select id, (name + ' ' + surname + ' ' + age) as info from users this doesn't work, how to do it? I'm using postgreSQL. ...
I have this database which contains a varchar. I want to know which records holds numeric values. I tried REGEXP_COUNT and other but I'm running on 9i and I think this is for 10g > How can I achieve this? I tried: select to_number( my_column ) from my_table But it doesn't work, because well not all of them are numeric. EDIT Ba...
How do I do this query in linq? All the tables already are list of objects. This query give points to entities named "Empresas" (Companies) that fills the "Palavras" (Words) criterias. select x.empresaid, sum(x.pontos) from ( select a.empresaid, sum(1) as Pontos from empresa a inner join Palavras b on a.nome like '...
Hello, I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this "Order Rank Integer". ...
I have a table with two columns n integer and s varchar. n is the primary key. s is mostly unique but not always. For example n s 1 New York 2 Moscow 3 Paris 4 London 5 Moscow 6 Berlin 7 Moscow I want to create another table with the same structure, the same number of rows except that s will be made unique by adding nu...
I look for some exaple how use the stora procedurs in MVC with LINKQ. ...
I have a query as below: SELECT * FROM Members (NOLOCK) WHERE Phone= dbo.FormatPhone(@Phone) Now here I understand that formatting has to be applied on the variable on column. But should I apply it on variable to assign to some other local variable then use it (as below). Set @SomeVar = dbo.FormatPhone(@Phone) SELECT * FROM Me...
I have sales people and bean counters who are trying to sell customizations to clients, which is fine. But when a complex change request comes in that I send back a large estimate for, they get confused. Often they come back at me with "Why can't you just add another column?" which by another, they mean a dozen or so custom columns PER...
I am dealing with Sql Server and Oracle through Qt, when using QSqlDatabase::transaction() on a database connection. When another user/connection has a transaction open on the same database does the transaction() call block until the other transaction is finished or fail ? ...
Hi SET NOCOUNT ON stops the message that shows the count of the number of rows affected by a Transact-SQL statement or stored procedure from being returned as part of the result set. a) How can you read these messages using C# and ADO.NET ( I assume C# code reading these messages is the same regardless of whether T-SQL ...
I have a table on a SQL Server 2000 database, which I want copied verbatim to a 2008 server. I tried to do it manually with INSERT/UPDATE triggers, but this technique runs in a distributed transaction, which does not work because I apparently have to enable MSDTC and the firewall; I don't want to do any of that. Are there any other way...
I have a table which contains sales order data (order number, product number, sales price, etc.). However, the table is littered with corrections and various other invalid data. One of the main issues is that corrections were entered by adding a new row with a negative total equal to the amount of a previous order. The sales people were...
Suppose we have a denormalized table with about 80 columns, and grows at the rate of ~10 million rows (about 5GB) per month. We currently have 3 1/2 years of data (~400M rows, ~200GB). We create a clustered index to best suit retrieving data from the table on the following columns that serve as our primary key... [FileDate] ASC, ...
I have a long running job. The records to be processed are in a table with aroun 100K records. Now during whole job whenever this table is queried it queries against those 100K records. After processing status of every record is updated against same table. I want to know, if it would be better if I add another table where I can update...
My sqlconnection is SqlConnection(@"Data Source = John\Administrator; Initial Catalog = TicketingSystem; Integrated Security = true;"); I try to connect to the server but i cant this error pops up A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not ac...
I have a job with around 100K records to process. I have got many suggestions to split this job in chunks and then process it. What are the benefits of process smaller chunks of data compared to 100K records? What is the standard way of doing it? e.g. Picking 10K records in a temp table and process at a time? ...
I have a proc that print checks if there is any new checks to be print. If there is nothing to issue new checks it wont print any. Now i want to modify this proc like even if i don't have any new checks to be print, it should pick up at least one check to be print.( even if it is already printed). Can you tell me how to do that. Here is...
Hi, I've got a sql stored proc that is working fine in SSMS. When I try and execute through code and assign the return to a dataset I am getting zero rows back. I've used the immediate window to ensure that I am sending the correct params to the stored proc and that is all good. What else would cause me to get zero rows assigned to th...