sql-server

How can I get a percentage of Linq-to-SQL submitchanges?

Hi, I wonder if anyone else has asked a similar question. Basically, I have a huge tree I'm building up in RAM using Linq objects, and then I dump it all in one go using DataContext.SubmitChanges(). It works, but I can't find how to give the user a sort of visual indication of how far has the query progressed so far. If I could ultimat...

Refreshing a SQL database connection

How do you refresh a database connection within a VB application to SQL Server 2005 so that whenever data is changed in SQL, such changes are picked up by the application? For now the application only picks up the changes after it has been restarted, and that is not what I want. ...

Kill a blocked process in a database

What is the best solution if we see a lot of blocked processes in a database? ...

How to Append String at the end of a Given a Column in SQL Server?

I have a project I am working on. based off a backup SQl Server database from a production server. They have over 16,000 user email addresses, and I want to corrupt them so the system (which has automatic emailers) will not send any emails to valid addresses. But I still want the users, and I want them in a way that I can reverse wha...

Minimum permissions for a SQL server "heartbeat" check?

We are developing an application to do a "hearbeat" test of all our SQL servers (2000, 2005 and 2008), and we're trying to figure out the minimum permissions necessary on the SQL server to do that. (Platform involved is TBD, but should use a standard connection string). We are considering using a "SELECT @@VERSION" query, which should b...

Primary key Ascending vs Descending

In Sql Server, I have a table with an Identity primary key. Often I want the latest few new records, so I grab the Top n sorted by descending the primary key. Should I define the Primary Key index as Descending, or does it make no difference? i.e. if they are in Ascending order, then can sql just as efficiently work backwards? ...

Insert values into multiple SQL tables

What is a good method to insert table data that replies on many tables in C# and SQL server? So for example say I have the following tables: Products: PK=PID, Product_Name Build: PK:BID, Build_Number, FK:PID Files: PK:FID, File_Name, FK:PID FileDetails: PK:FDID, FK:FID, FK:BID, File_Size, File_Version So all the Primary Keys are auto...

Do you lose precision when converting from real to money datatype in sql server?

I have a table with datafields in real datatype. Do you lose precision when converting to money or decimal datatype? ...

Updateable view in mssql with multiple tables and computed values

Huge database in mssql2005 with big codebase depending on the structure of this database. I have about 10 similar tables they all contain either the file name or the full path to the file. The full path is always dependent on the item id so it doesn't make sense to store it in the database. Getting useful data out of these tables goes a...

Why doesn't SQL Server fail when the result of an UPDATE is ambiguous?

I have two tables, a destination for the update: create table dest (value int) insert into dest values (0) and a source: create table source (value int) insert into source values (4) insert into source values (1) If I run this query: UPDATE dest SET value = (select value from source WHERE 1=1) SQL Server fails with: Subquery re...

Upload file to SQL database using classic ASP

I am trying to upload files (.doc/.pdf) to a SQL database (2005) but I am really struggling to find any step by step guides. This is what happens on my ASP form: User selects a document Document is currently upload to a temp file and sent to a email address However it also needs to be stored within a database field. I have set the ...

Are SQL Server 2000+ transactions connection dependent?

Case 1: I start a connection to the DB I BEGIN a TRANSACTION I close the connection What happens to the transaction? Case 2: I start a connection to the DB I BEGIN a TRANSACTION I start a concurrent connection to the same DB With the second connection I modify the contents of a table With the first connection I ROLLBACK the TR...

Tracking/Querying Status Changes using SQL

I was given a database design which stores information about an organization and any changes that have happened or will happen to the organizations. Since pretty much anything can change about an organization, there is one table that only contains the unique OrganizationID's in a table called "Organizations". The changes to that organiza...

SQL Server Triggers - grouping by transactions

At work we have just started building an auditing framework for our database (i.e. to log what data has changed when it is created or updated). We'd quite like to implement this using triggers as data sometimes gets imported into the system from other places, not just via the front-end. This seems to be a fairly common solution. Howeve...

Missing Join Predicate Warning

I'm running a SQL Server 2005 trace using profiler. I'm seeing some missing join predicate warnings, but I can't determine where its occurring with the data they are giving me. All I have is the transactionID. Any way to find out what object is causing this? ...

optimize for unknown for SQL Server 2005?

I was listening to the SO podcast and they mentioned Optimize For Unknown for SQL server 2008, they also mentioned that there was something similar for SQL Server 2005. Anyone know what this is? ...

Upgrade database from SQL Server 2005 to 2008 — and rebuild full-text indexes?

I upgraded from Sql Server 2005 to Sql Server 2008. I backed up the data in SQL Server 2005, and then I restored in SQL Server 2008. Next, I found the catalog under "Storage->Full Text Catalogs", and I right-clicked on it, and am trying to rebuild the Full-Text Catalog. The database is only 600mb. However, it keeps running for hours, and...

Most Efficient Method to Detect Column Change in MS SQL Server

Our system runs on SQL Server 2000, and we are in the process of preparing for an upgrade to SQL Server 2008. We have a lot of trigger code where we need to detect a change in a given column and then operate on that column if it has changed. Obviously SQL Server provides the UPDATE() and COLUMNS_UPDATED() functions, but these functions...

Management Studio default file save location

Open a new query window. Write some SQL. Save the script, the Save File As dialog box opens - but always to the same default location in the Profiles directory. Is there any way to set my default file location? ...Like I used to do with apps from the 1980s? Under Tools|Options a default location can be specified for query results. I...

T-SQL query with funny behavior

Let's say you have the following two tables: X Table X_ID Y_ID_F X_Value 1 1 Q 2 1 G 3 1 T 4 2 W 5 2 K ... Y Table Y_ID Y_Value 1 A 2 B ... You want to query only those properties whose Y parent's value is A and update them so you write a query as follows (I realize there is a bette...