Hi all, I'm doing .NET 3.5 programming in VB for a class. I have a .mdb database with 3 related tables, and a table adapter with some queries on it that look like this: SELECT PropertyID, Street, Unit, City, Zip, Type, Bedrooms, Bathrooms, Area, MonthlyRent FROM tblProperties Then in a form i have a DataGridView. What i w...
Consider that I have a transaction: BEGIN TRANSACTION DECLARE MONEY @amount SELECT Amount AS @amount FROM Deposits WHERE UserId = 123 UPDATE Deposits SET Amount = @amount + 100.0 WHERE UserId = 123 COMMIT And it gets executed on 2 threads, in the order: thread 1 - select thread 2 - select thread 1 - update thread 2 - update ...
I've written a paged search stored procedure using SQL Server 2005. It takes a number of parameters and the search criteria is moderately complex. Due to the front-end architecture I need to be able to return the number of results that would come back without actually returning the results. The front end would then call the stored proce...
What is the best SQL IDE you have used? ...
In an interview for a SQL DBA position a while back, I mentioned I prefer the SQL 2005 Management Studio app vs. the old 2000 versions of Enterprise Manager / Query Analyzer. The DBA who was interviewing me said that they had experienced some sort of database corruption in their 2000 databases when making changes from the 2005 MS, so the...
I've got a (SQL Server 2005) database where I'd like to create views on-the-fly. In my code, I'm building a CREATE VIEW statement, but the only way I can get it to work is by building the entire query string and running it bare. I'd like to use parameters, but this: SqlCommand cmd = new SqlCommand("CREATE VIEW @name AS SELECT @body");...
If I have a query to return all matching entries in a DB that have "news" in the searchable column (i.e. SELECT * FROM table WHERE column LIKE %news%), and one particular row has an entry starting with "In recent World news, Somalia was invaded by ...", can I return a specific "chunk" of an SQL entry? Kind of like a teaser, if you will. ...
I have a query in which I am pulling the runtime of an executable. The database contains its start time and its end time. I would like to get the total time for the run. So far I have: SELECT startTime, endTime, cast(datediff(hh,starttime,endtime) as varchar) +':' +cast(datediff(mi,starttime,endtime)-60*datediff(hh,starttime,endtime) as...
I need to take production data with real customer info (names, address, phone numbers, etc) and move it into a dev environment, but I'd like to remove any semblance of real customer info. Some of the answers to this question can help me generating NEW test data, but then how do I replace those columns in my production data, but keep the...
I have a partitioned table in SQL Enterprise 2005. I need to query the PARTITION FUNCTION to find it's partition ranges. What SQL query will give me those values? ...
I have a list of stores, departments within the stores, and sales for each department, like so (created using max(sales) in a subquery, but that's not terribly important here I don't think): toronto baskets 500 vancouver baskets 350 halifax baskets 100 toronto noodles 275 vancouver noodles 390 halifax noodles 120 halifax ...
I know you cannot use a alias column in the where clause for T-SQL; however, has Microsoft provided some kind of workaround for this? Related Questions: Unknown Column In Where Clause Can you use an alias in the WHERE clause in mysql? “Invalid column name” error on SQL statement from OpenQuery results ...
In class, we are all 'studying' databases, and everyone is using Access. Bored with this, i am trying to do what the rest of the class is doing, but with raw SQL commands with mySQL instead of using Access. I have managed to create databases and tables, but now how do i make a relationship between two tables? If i have my two tables li...
I want to port a project from sybase to oracle. I need to port the tables scripts (around 30) and some data in meta data tables(100 rows 2/3 tables). What will the best tool for this work? ...
There are two databases in SQL Server 2005: One called "A" and another one called "A_2". "A" is a variable name to be entered by the user, the "_2" prefix for the second database is always known. (So databases could be "MyDB" and "MyDB_2", etc) How to access the other database from within a stored procedure without knowing the actual nam...
I'm trying to do the classic Insert/Update scenario where I need to update existing rows in a database or insert them if they are not there. I've found a previous question on the subject, but it deals with stored procedures, which I'm not using. I'd like to just use plain SQL SELECT, INSERT and UPDATE statements, unless there's somethi...
I'm currently working with Db2 Enterprise Server V 8.2 with FixPak 10 And I want to retrieve list of all the open active connections with an instance. In Oracle there is a utility program called "Top Session" which does the similar task. Is there any equivalent in DB2? Thanks in advance, Kamal ...
In C# Is there a way of getting a finer granularity with SQL exceptions? I'm aware that an aweful lot can go wrong but I want to deal with certain cases differently and parsing the Error message doesn't seem to be very elegant. Also Are the error messages created by the framework or are they db specific? For example If i have a primar...
I have a query on my database as such: SELECT * FROM expenses WHERE user_id = ? AND dated_on = ? I have added an index to the table on both the user_id and dated_on columns. When I inspect the indexes using SHOW INDEXES FROM expenses, there are two lines -- one with a seq_in_index value of 1, the other with a seq_in_index value of 2....
I'm looking for a dummy SQL statement that will work from a C# SQL connection to check for connectivity. Basically I need to send a request to the database, I don't care what it returns I just want it to be successful if the database is still there and throw an exception if the database isn't. The scenario I'm testing for is a loss of ...