I've inherited a rather large application that really could use some cleanup. There is data access code littered throughout the application. In code behinds, some in business logic classes, some inline in in classic asp pages. What I'd like to do is refactor this code, removing all the data access code into a few DAL classes. All the...
I have an object I am using to store document meta data into a table. The body text of the document can be very large, sometimes > 2GB so I will be storing it into a nvarchar(max) field in SQL 2008. I'll use SQL 2008 later to index that field. I won't be using filestreams because they are very restrictive to the database and prevents ...
How do conditional statements (like IF ... ELSE) affect the query execution plan in SQL Server (2005 and above)? Can conditional statements cause poor execution plans, and are there any form of conditionals you need to be wary of when considering performance? ** Edited to add ** : I'm specifically referring to the cached query execu...
I have hit a classic problem of needing to do a string replace on a text field in an sql 2000 database. This could either be an update over a whole column or a single field I'm not fussy. I have found a few examples of how to use updatetext to achieve it but they tend to be in stored procedures, does anyone know of a similar thing that ...
I have a stored procedure on SQL Server 2005 doing a Serializable Transaction. Inside this transaction, it selects a table with rowlock. At the end of the procedure, after rollback/commit, it sets the transaction isolation level to Read Commited. This procedure is running, different processes have concurrent access controlled by these c...
I've got mssql 2005 running on my personal computer with a database I'd like to run some python scripts on. I'm looking for a way to do some really simple access on the data. I'd like to run some select statements, process the data and maybe have python save a text file with the results. Unfortunately, even though I know a bit about pyt...
I started using a Sql Compact version of my DB and during the development it occured that the DB needs to be accessible over the network. Cause I wasn't able to find a way to share the SDF file over net without replication I need to import it into SQL Server 2008 (Express). Any Ideas? ...
Hello, I'm using ASP.NET Preview 5 (will upgrade soon to Beta) with LINQ2SQL on my recently launched tiny webapp. I was so proud of my work until Silverlight.net featured it on the first page and it started receiving a more than humble number of visitors. For some reason, users are sometimes getting "Specified cast invalid" thrown by ...
How is the guest account in SQL Server (2000, 2005, 2008) supposed to be used? What is it good for? I've tried enabling the account but I still can't get certain users to be able to refresh Excel 2007 PivotTables attached to views which I have given SELECT rights to GUEST. What am I missing? ...
Sql server complaining about this IF NOT EXISTS statement, saying that there is 'incorrect syntax near the keyword 'OR'. My query: IF NOT EXISTS ( (SELECT * FROM Users where userID = 1) OR (SELECT * FROM sales WHERE saleID = 1) ) BEGIN // blah blah blah END ...
Hi, If I simply wrap my query with: BEGIN TRANSACTION COMMIT TRANSACTION If anything fails inside of that, will it automatically rollback? From looking at other code, they seem to check for an error, if there is an error then they do a GOTO statement which then calls ROLLBACK TRANSACTION But that seems like allot of work, to hav...
It's a trivial task to find out if an object is referenced by something else or not. What I'd like to do is identify whether or not it's actually being used. My solution originally involved a combination of a table that held a list of objects in the database and an hourly job. The job did two things. First, it looked for new objects t...
I've got a strange problem with SQL Server 2000, and I just can't think of a reason for this to happen. There are two tables, both having a combined primary key with a clustered index on it, both keys have the same structure: (VARCHAR(11), INT, DATETIME) /* can't change this, so don't suggest I should */ So, joining them like this ...
I'm trying to completely automate my builds and part of it is applying SQL scripts to the SQL Server. I've created a batch file that calls SQL Server utility (OSQL.EXE) to apply scripts and it works if I call it directly on the SQL server. But I cannot use it on my build machine because it does not have SQL Server installed. Is there ...
I'm running Windows XP SP3 and I do have the latest .NET libraries installed, as well as Visual Studio 2008. I'm also running (and would like to continue to run) Apache HTTP Server as well, if that matters. Perhaps someone can provide a list of software that I need and then I can look up configuration or ask here if I can't find what I ...
SpousesTable SpouseID SpousePreviousAddressesTable PreviousAddressID, SpouseID, FromDate, AddressTypeID What I have now is updating the most recent for the whole table and assigning the most recent regardless of SpouseID the AddressTypeID = 1 I want to assign the most recent SpousePreviousAddress.AddressTypeID = 1 for each unique Spou...
I want a query that returns a list of all the (user) stored procedures in a database by name, with the number of lines of code for each one. i.e. sp_name lines_of_code -------- ------------- DoStuff1 120 DoStuff2 50 DoStuff3 30 Any ideas how to do this? ...
If you have a lot of Stored Procedures and you change the name of a column of a table, is there a way to check which Stored Procedures won't work any longer? Update: I've read some of the answers and it's clear to me that there's is no easy way to do this. Would it be easier to move away from Stored Procedures? ...
I have looked at the SQL Server 2008 feature comparison matrix and it lists the express/web and workgroup editions as having the SSIS runtime. Does this mean it is possible to develop SSIS packages using the developer edition, and then deploy and run them on a server running one of the lowly SQL Server editions such as SQL Server 2008 Ex...
I have a few ASP.Net database front-end websites where MS Access is the back-end. I am trying to use ASP.Net dynamic data website. Should I change this database to SQL Server Express or something else to make it easier or should this work from with MS Access. ...