how to detect all sql server instances from studio?
There are multiple instances of SQL Server 2005 installed on a box. Is there a T-SQL query I can run from Studio that will detect these other instances and their names? ...
sql-server-2005
Dealing with duplicated data in SQL Server
I have a large SQL server table that looks something like this: ImageId int Page int FSPath varchar(256) ImageFrame int ... The table stores an entry for each page of a number of image files. This is done to enable the table to represent images where each page is represented by a different file, and multi-page image files that conta...
SQL Server 2005 - Trigger Loop?
I am using triggers for the first time. If I update a field in a table by an update trigger on the same table, with this spark a loop? Does sql server guard against this recursive behavior? Thanks ...
execute sp in a select statement
For an instance I a select statement and it is returning 1000 rows. I need to execute a particular stored procedure for every row the the select statement is returning. have you got any idea how can I do that? ...
TSQL: Update values by using With statement?
I have a table with a column I would like to update its values. Here is an example of TSQL codes: WITH Pieces(id, newdesc) AS ( SELECT itemid, REPLACE(REPLACE(description, 'DESC_A', 'DESC_B'), 'NEW_1', 'NEW_2') FROM myTable WHERE description like '%DESC_A%DESC_B%' ) -- SELECT * FROM Pieces UPDATE myTable SET description = newdesc //...
Dynamically taking a table name in aspx form using sql server
Hi! I'm trying to dynamically accept a table name depending on the conditions satisfied, also the column name is selected dynamically, and so is the comparison value, but I'm getting an error while running it. I'm writing this code in C# and my backend is SQL server 2005. Please help me. Here is the code: if( table=="studenttab"...
Problem restoring a 2005 SQL DB with 2008
I have a DB I created with SQL Server 2005 and created a backup (*.bak) now I want to restore it with 2008 but ouch, I get the following error: TITLE: Microsoft SQL Server Management Studio ------------------------------ Restore failed for Server 'SHIMMY-PC\SQLEXPRESS'. (Microsoft.SqlServer.SmoExtended) For help, click: http://go.mic...
how to copy sql table relation to remote database
I'm using sql management studio 2005. When i copy all tables to remote database using Import tool, relation dont copy. How to copy tables with relation. ...
How can I rollback an UPDATE query in SQL server 2005?
How can I rollback an UPDATE query in SQL server 2005? I need to do this in SQL, not through code. ...
Copy data from one database to another?
I am upgrading a clients app to a newer version, the databases are close but slightly different. I need to figure out a way to transform and copy the data from one table in one database to another table in the new database. Oh geez :( ...
Which is faster for multiple SQL calls - parameterized queries or stored procedures?
I'm using SQL Server 2005. I'm looking at opening a SQL connection, looping though a collection and running an update query (stored procedure or parameterized query) with the data from the collection item, then closing the connection. Which is going to give me better performance and why? ...
Date Format conversion in SQL
I want to convert date format from 01/09 to January 2009 , 09/03 to September 2003 etc. Is this possible in SQL? Please let me know if there is a API for the same. ...
database mirroring/replication, SQL Server 2005
Hi there, I have two database servers running SQL Server 2005 Enterprise that i want to make one of them as mirror database server. What i need is, create exact copy a database from primary server on mirror server, so when the primary server was down, we could switch database IP on application to use mirror server. I have examined "mi...
SQL server identity column values start at 0 instead of 1
Hi, I've got a strange situation with some tables in my database starting its IDs from 0, even though TABLE CREATE has IDENTITY(1,1). This is so for some tables, but not for others. It has worked until today. I've tried resetting identity column: DBCC CHECKIDENT (SyncSession, reseed, 0); But new records start with 0. I have tried doi...
Most recent record in a left join
Imagine I have the following 3 tables in SqlServer: Customer (CustomerID, FirstName, LastName) Address (AddressID, CustomerID, Line1, City, State) Product (ProductID, CustomerID, Description) A customer can have multiple delivery addresses and mulitple products. What I would like to do is to list the number of customers for each Stat...
Dynamic sql join in sql server
I have a relationship between two tables. The two tables PKs are int types. In one table (UserS), I need to supply the Username and get the corresponding ID (which is the PK). This is the standard ASP.NET user table when using forms authentication. However, in a related table, I want to supply the ID I find from the Users table to get ...
Crystal Reports Best Practices
I don't have much experience managing reports, but I work on an application that works like this: There is an interface for the users to select a report they want to view from a list of reports. This list is populated by a reports table in the database which holds info like the report name, the file name, and which parameters the report...
What is the best practice use of SQL Server T-SQL error handling?
We have a large application mainly written in SQL Server 7.0, where all database calls are to stored procedures. We are now running SQL Server 2005, which offers more T-SQL features. After just about every SELECT, INSERT, UPDATE, and DELETE, the @@ROWCOUNT and @@ERROR get captured into local variables and evaluated for problems. If the...
Trigger to capture schema changes in the Server
Is it possible to implement something like the following trigger CREATE TRIGGER [tr_AU_ddl_All_Server] ON DATABASE WITH EXECUTE AS self FOR DDL_DATABASE_LEVEL_EVENTS AS DECLARE @data XML , @rc INT SET @data = EVENTDATA() EXEC @rc = __AU.dbo.AU_DDLLog @data GO BUT on the whole server. My idea is to cap...
Coalesce and Pivot in TSQL
I am having trouble figuring out how to coalesce or pivot on a SQL recordset that looks like this: ID VALUE GROUP 3 John 18 4 Smith 18 5 Microsoft 18 3 Randy 21 4 Davis 21 5 IBM 21 etc and I want formatted like this NEWVALUE GROUP Smith, John (Microsft) 18 Davis, Randy (IBM) 21 thanks for any su...