I have a stored proc that processes a large amount of data (about 5m rows in this example). The performance varies wildly. I've had the process running in as little as 15 minutes and seen it run for as long as 4 hours. For maintenance, and in order to verify that the logic and processing is correct, we have the SP broken up into secti...
In MS Access, I have a query where I want to use a column in the outer query as a condition in the inner query: SELECT P.FirstName, P.LastName, Count(A.attendance_date) AS CountOfattendance_date, First(A.attendance_date) AS FirstOfattendance_date, (SELECT COUNT (*) FROM(SELECT DISTINCT attendance_date ...
I'm trying to do some basic paging in MSSQL. The problem I'm having is that I'm sorting the paging on a row that (potentially) has similar values, and the ORDER BY clause is returning "random" results, which doesn't work well. So for example. If I have three rows, and I'm sorting them by a "rating", and all of the ratings are = '5' - t...
How can I view the SQL code of my query after it has been parameterized by Parameters.Append ...
I'm looking for a tool to enforce coding styles, validation check for T-SQL script. And it recommends best-practice when appliable. In .net code domain, the tool for this purpose is FxCop. Is there a counterpart for T-SQL? It will be best it is free or open source tool. Anyone can shed some light? ...
Why does the SQL Standard accept this? Which are the benefits? If have those tables: create table prova_a (a number, b number); alter table prova_a add primary key (a,b); create table prova_b (a number, b number); alter table prova_b add foreign key (a,b) references prova_a(a,b) ; insert into prova_a values (1,2); You can insert thi...
We have 2 tables called TableToUpdate and Dates. We need to update TableToUpdate's EndTimeKey column by looking from other table Dates. We run sql below to do this, but it takes to long to finish. Table TableToUpdate has 6M records. Table Dates has 5000 records. How can we optimize it ? Thanks for replies ! update TableToUpdate set E...
Hi All, I was wondering how I can pass either an ArrayList, List<int> or StringBuilder comma delimited list to a stored procedure such that I find a list of IDs using IN(): @myList varchar(50) SELECT * FROM tbl WHERE Id IN (@myList) In C# I am currently building the list as a string which is comma delimeted; however when using...
Hey, I need to create a stored procedure that upon exceution checks if any new rows have been added to a table within the past 12 hours. If not, an warning email must be sent to a recipient. I have the procedures for sending the email, but the problem is the query itself. I imagine I'd have to make an sql command that uses current date...
In my last job we worked on a very database heavy application and I developed some formatting standards so that we would all write SQL with a common layout. We also developed coding standards but these are more platform specific so I'll not go into them here. I'm interested to know what other people use for SQL formatting standards. Unl...
I have a class User object. I am trying to load this object from the Database using Hibernate. My SQL statement is: SELECT users.uid AS uid, users.deleted AS deleted, users.username AS username, users.passwd AS passwd, users.disabled AS disabled, users.lockout AS lockout, users.expires AS expires, data_first...
For example my table is here>> CREATE TABLE tProject( name nvarchar(1000), Goal ntext, Activities ntext, Result ntext, MonName nvarchar(1000), MonGoal ntext, MonActivities ntext, MonResult ntext, TotalFund nvarchar(100)) how to search from all ntext and nvarchar fields in onetime. help me please ...
I am trying to take tables from one Access Database File, add them to another Access Database file with the exact same structure but with different information. I need to overwrite any existing tables. I am almost done with my project this is last my brick wall. I am using a separate class file named DatabaseHandling.cs to work with the...
Hi people. I have a database with some stored procedures. My question is: Is there a problem to do this: GRANT EXECUTE ON [dbo].[StoredProcedureXPTO] TO [Public] i.e. grant permission of execution to public?? Or is thar a security issue? ...
I have two Microsoft SQL Server 2000 databases, and a stored procedure on one tries to read data from the other. This used to work fine, but since I became security-conscious and changed the login (SQL user) from "db owner" to "denydatareader" the call fails. I can get things working if I use the group "datareader", but since I do not w...
I'm not clear about the interaction between database schema changes and differential backups on sql 2005. Lets say I do a Full backup right right now. Then I perform some schema changes. Then I do a diff backup. What happens? Do I need to create another FULL backup? Are my schema changes and any data in those new schema bits included...
Hello all. I am just beginning to learn SQL and have stumbled at the first hurdle, I am unable to create a table. Below is the code example. The error I am receiving when running the statement, references line 7 stating there is an issue with a 'relational operator'. The purpose of line 7 is to check that the age of the person is greater...
Let's say you're running an UPDATE statement on a table, but the information you're putting into this base table is from some other auxiliary table. Normally, you would JOIN the data and not expect the rows in the UPDATE statement's FROM clause to multiply, maintaining that one new row maps to one old row in the base table. But I was wo...
I am attempting to select a distinct list where duplicates are created over several fields. For example, SELECT tablename.field1Date, tablename.field2Number, tablename.field3Text FROM tablename; Would select duplicating records over the date, number and text fields respectively. Now, when I select distinct records t...
I am looking at using Ruby on Rails for a storefront that has to make use of existing data, but I can create my own database schema if I need to. A lot of the fields in the existing data are char(1) emulating a boolean field (i.e. Y/N) for, I believe, cross-platform portability. Since the data is prone to change, I don't want to have t...