I've read around the subject of temporary tables and scope and all the answers i've seen don't seem to talk about one of my concerns. I understand that a local temporary table's scope is only valid withing the lifetime of a stored procedure or child stored procedures. However what is the situation with regard to concurency. i.e. if i ha...
I am part of a software development company looking for a good way to update my SQL Server tables when I put out a new version of the software. I know the answer is to probably use scripts in one form or another. I am considering writing my own .NET program that runs the scripts to make it a bit easier and more user-friendly. I was wond...
hi there i had to uninstall sql server 2005, coz it was configured to be used only in windows authentication mode, and install it again in mixed mode. so i had to take a backup of my only database there, and restore it again on installing the sql server back, though the backup was taken successfully but when am trying to restore it, it i...
I will be starting a simple datastore-and-search project soon. Basically, one of those "put my huge Excel spreadsheet into a database, build a web GUI for it, and make it searchable" type-things. One thing that's been bugging me is the actual search logic that will be used when the user enters some criteria. I am imagining a search inte...
I was wondering what is the easiest way to see total number of database queries from my ASP.Net (.NET 2.0) application. My application heavily use sql 2005 database because all data are dynamic and everything goes through one connection string in web.config. Connection pooling is enabled there. So, I am wondering how many select stat...
I store events in SQLServer 2005 where the time the event occured is important and must be stored in the datebase. What is the fastest way to write the date range check in the where clause to ensure everything on that day is selected? Currently when @DateStart and @DateEnd are passed in I set @DateStart to midnight and set @DateEnd to t...
Table UserData (UserID, Sales, Credits) I need to return the SUM of sales, sum of credits and the # of rows returned for a given date range. Is it possible in 1 query? ...
Hi, Sorry stuck on this query, since I have the data in the order by clause it tells me I have to put in an aggregate or group by clause? (even if I don't need that aggregate value?). Table UserData ( userID, sales, credits, dateCreated) My query has to return the last 10 results: SELECT TOP 10 COUNT(*) as totalDays, SUM(sales), SUM...
I have been passed a piece of work that I can either do in my application or perhaps in SQL: I have to get a date out of a string that may look like this: 1234567-DSP-01/01-VER-01/01 or like this: 1234567-VER-01/01-DSP-01/01 but may look like this: 00 12345 DISCH 01/01-VER-01/01 XXX X XXXXX Yay. if it is a "DSP" then I want that ...
I'm using migratordotnet to manage my database migrations. I'm running them on application setup like this, but I would also like to check on application startup that the migrations are up to date, and provide the option to migrate to latest. How do I tell if there are available migrations that need to be applied? I see that I can get...
I need the number of line items per Order. In this example, order 10 has 0 and order 11 has 2 lines. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderDetail') AND type in (N'U')) DROP TABLE OrderDetail IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderHeader') AND type in (N'U')) D...
I have a report that needs to display accounting data from different locations or all locations. Table_sales: PartId Rev LocId 1 $10 1 2 $2 1 3 $5 2 Lets say the query is something basic like this to get all revenue: SELECT SUM(rev) FROM Table_sales If I want to be able to limit the rep...
I have a couple of tables that are joined by GUIDs in SQL Server. Now, I've found a few custom fields to add support for GUIDs in django, but I tend to shy away from using code in blog posts if at all possible. I'm not going to do anything with the GUID other than join on it and maybe assign a GUID on new entries (although this is opti...
An earlier question was answered about getting the OrderID and the number of associated line items. Now I would like to get the rest of the fields in the OrderHeader row. IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.OrderDetail') AND type in (N'U')) DROP TABLE OrderDetail IF EXISTS (SELECT * FROM sys.objects ...
Single-threaded version description: Program gathers a list of questions. For each question, get model answers, and run each one through a scoring module. Scoring module makes a number of (read-only) database queries. Serial processing, single database connection. I decided to multi-thread the above described program by splittin...
Is there any way to add a SQL Server Database Diagram to source control? I can't seem to find a way to script it out of the database. If so, is there a way to get that diagram into a Visual Studio Database Project for easy deployment? ...
I have a database that I just converted the back end to SQL Server using SSMA. I left the front end in MS Access. I only converted the tables and not the queries. It already had some data in it and that moved over just fine. All was going well until just recently. On opening the database and loading the main form Event Interest it s...
Hi, I am developing a web application on .NET Framework 3.5. The database server is SQL Server 2008. In connectionStrings of web.config, the database name is set as “mydatabase”. Mini pool size is set to 50. When I use sp_who to check database connections, I found that for most of sleeping connection, the dbname is master, not “mydatab...
Hi All, In my application I am sending password to database,lets say my Password is PassworD123. Now this is giving me proper value ,but when i am using password123..its also giving me the proper value.So how to chaeck for case sensitive data in SQL server. Any demo code will help. Thanks. ...
Hello, I have a table in SQL Server 2005 which has approx 4 billion rows in it. I need to delete approximately 2 billion of this rows, but if I try and do it in a single transaction, the transaction log fills up and it fails. I don't have any extra space to make the transaction log bigger, so I assume the best way forward is to batch up...