sql-server

Aggregating two selects with a group by in SQL is really slow.

I am currently working with a query in in MSSQL that looks like: SELECT ... FROM (SELECT ... )T1 JOIN (SELECT ... )T2 GROUP BY ... The inner selects are relatively fast, but the outer select aggregates the inner selects and takes an incredibly long time to execute, often timing out. Removing the group by makes it run somewhat...

how to execute a stored procedure continuously?

I have a stored procedure that creates a database (ex: sp_createDB). In the similar fashion I have to create a 100's of databases using that one. So that I have to put it in a loop. But I don't know how to do it sqlserver. How can I do this in sqlserver. Help me in this regard. Thanks ...

what is the CLSID and PROG ID of Microsoft.sqlserver.Management.Smo

Hi Can any Body tell me wher can i get CLSID and ProgID of Smo library?? As for DMO it is givenn in a Header file by Microsoft.But i am not finding any Header file related to smo. What is the CLSID and ProgID of Smo ?? ANy Hep in this regard greately Appreciated. Thanks in advance. ...

How to create a stored procedure to create a database

I need the code to create the database. ...

Database Relationship

I have two database one with relationship(no data) and another with out Relationship (with data) and i want to insert data from one database to another database i am not able import data i got the error for forgien key. Is there any way for this ? ...

Difference between Condition in Where and Condition in Join

Hi All Can anyone please explain to me why the following two queries yield different results? SELECT o.* FROM Customer c LEFT JOIN [Order] o ON o.CustomerID = c.CustomerID AND o.OrderType = 'Cash' WHERE c.Country = 'USA' SELECT o.* FROM Customer c LEFT JOIN [Order] o ON o.CustomerID = c.CustomerID WHERE ...

SQL Server Group By Query using multiple values

I am new to Microsoft SQL Server and have been frustrated by a GROUP BY query that won't do what I want it to. The table is the following: make model distancefrom distanceto driverid toyota yaris 358.2 368.2 401 toyota yaris 368.2 378.7 103 toyota yaris 378.7 382.2 103 toyota yaris 382.2 392.2 103 to...

Unit Testing Database Driven .NET Applications

What is the best way to unit test heavily database dependant .NET middleware? E.g. a process which reads data from multiple databases, manipulates it, and then combines and writes it to other databases? Should the databases be filled with static data which is somehow reset on each unit test run? Should the whole SQL Server access be som...

How do I find out what tables have data in a file in SQL Server?

I want to drop a now supposedly redundant file in SQL Server (2005), but when I try to drop it I am told that the file is not empty. Does anyone know of a way to find out what data is still in this file so I can make whatever changes I need to allow me to drop it? ...

Identifying indentity column?

I have found out how to determine what columns are are primary key column of a given table by using this query: SELECT CONSTRAINT_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME='tablename_here' AND CONSTRAINT_NAME LIKE 'PK_%' I can find out what the identity seed and increment is by using this ...

create index using openquery

How do I create an index on a table that exist in a remote SQL Server database using the openquery syntax? ...

Do ADO.Net DataTables have indexes?

Hello everyone, I am using VSTS 2008 + C# + .Net 3.5 + SQL Server 2008 + ADO.Net. If I load a table from a database by using a DataTable of ADO.Net, and in the database table, I defined a couple of indexes on the table. My question is, whether on the ADO.Net DataTable, there is related index (the same as the indexes I created on physica...

How can I create a SQL unique constraint based on 2 columns ?

I have a Table like this one: |UserId | ContactID | ContactName --------------------------------------- | 12456 | Ax759 | Joe Smith | 12456 | Ax760 | Mary Smith | 12458 | Ax739 | Carl Lewis | 12460 | Ax759 | Chuck Norris | 12460 | Bx759 | Bruce Lee I need to add a constraint to this table s...

SQL Management Studio Express opening SQL scripts in Notepad

When I go to File > Open > File and select a .sql script, or even when I drag a .sql file into the SQL Management Studio Express window, it opens the script in Notepad which is totally useless when I want to run the script. Since this is on an external server (Windows 2003 Server), I end up having to disconnect from RDP, disable the loc...

Comparing database structures: how to create an SQL patch?

I have two SQL Server (2000) databases. Both are used for the same project, but different versions. Basically, the old database is from our TEST environment. The new database is from the DEVELOPMENT environment. We also have an ACCEPTANCE, PRODUCTION and MAINTENANCE environment and they all contain the same project. (It's our development...

How to group this query so it behaves correctly?

I have a table called Stock and another called Listed, within the Stock Table is a Status Code that indicates when something is at the front of the queue of items of stock - I want to be able to find the most recently added item and set this to be the "front of queue" status. For example to get all the items listed and then order them by...

VB6+SQL-Server: How can I execute a Query with named parameters using ADODB.Command?

I've been trying to execute a parametrized query with ADODB.Command. I know I can use "?" for parameters, but my queries are rather large and I really don't want to track the exact order of the parameters. I tried something like the following: objCmd.CommandType = adCmdText objCmd.CommandText = "SELECT ... WHERE field1=@p_Field1 ...." ...

Bitwise operations in T-SQL

I have a bitmasked int field in my database. Usually I manage it through c# code, but now I need to flip a bit in the mask using T-SQL How do I accomplish the following: The bit i want to flip: 1 << 8 (256) The mask value before i flip: 143 The mask value after i flip: 399 I'm not exactly a bit-flipping wizard but this c...

Mono C# SQL Update "Concurrency violation"

Every time I try and update a row via SqlDataAdapter.Update() in Mono, I get: Unhandled Exception: System.Data.DBConcurrencyException: Concurrency violation: the UpdateCommand affected 0 records. at System.Data.Common.DbDataAdapter.Update (System.Data.DataRow[] dataRows, Sy stem.Data.Common.DataTableMapping tableMapping) [0x00000] Th...

Web Database or SOAP?

We’ve got a back office CRM application that exposes some of the data in a public ASP.NET site. Currently the ASP.NET site sits on top of a separate cut down version of the back office database (we call this the web database). Daily synchronisation routines keep the databases up-to-date (hosted in the back office). The problem is that th...