sql-server-2005

What is query execution doing to this query in SQL Server 2005?

Today I found this query in our code that pulls up a list of errors from our database: SELECT * FROM ( SELECT Substring(title, 9, Patindex('%)%', title) - 9) AS SERVICE, * FROM core.LOG WHERE logtypeid = 1 AND title LIKE 'Error: (%' AND lastmodified > '2010-06-21T00:00:00' ...

How to find all foreign keys?

I'd like to find all referencing tables in my db that have a foreign key that points to a specific referenced table. Is there a query that I can run to do this? Not sure if the question is confusing. Let me know if it is and I can try to explain it in more detail. ...

TSQL to know if database mirroring is setup for both PRimary and secondary server

Hi, I have Database mirroring setup between two SQl 2005 servers (no witness). Using Tsql, I want to find if there is a user database on both primary and mirror server. I can use the following on mirror: select * from sys.databases where state <>1 and database_id>4 What should i use for Primary server? Any help? Regards Manjot ...

SQL going nuts! does not accept parameters, ?

well heres the code public void select_table_names() {//start /* display all tables*/ string commandString = null; SqlConnection conn = null; SqlCommand command = null; SqlDataReader reader = null; ArrayList list = new ArrayList(); try { // ...

Any tool to convert mysql store procedure to ms sql server store procedure?

Hello all, I have to migrate database from mysql to ms sql server 2005.I imported table successfully, now i need to convert store procedure written in mysql to ms sql server 2005.i need to convert a ton of sp so manual conversion is a tedious task, is any tool or simple way to convert sp of mysql to sql server 2005. ...

SQL Server 2005, Management Studio errors, possibly .NET Framework

Hi, I'm facing the following problem. When I'm trying to open SSAS 2005 properties I have this error, the same in 2008: TITLE: Microsoft SQL Server Management Studio ------------------------------ Cannot show requested dialog. ------------------------------ ADDITIONAL INFORMATION: Cannot show requested dialog. (SqlMgmt) ---------------...

What does SQL Server Execution Times represent?

Hi, I have a query that I'm running on two equivalent databases, but hosted on separate MS SQL 2005 servers. I want to measure the time of a query on both servers, and thus tried the following: SET STATISTICS TIME ON GO SELECT TOP 10000 * FROM table GO SET STATISTICS TIME OFF; GO And got the following result: SQL Server parse and...

How to do a partial copy of a remote SQL Server Express database to a local SQL Server Express database?

I'm in a situation where I need to create a partial copy of a database from a remote SQL Server (2005/2008 Express Edition) and store it in a local SQL Server (2005/2008 Express Edition) database. The local copy will have the same schema definition, but contain only parts of the data from the remote database. The local database has to ...

Continued:- can not use SqlAdapter ot DataSet or anything - just SqlCommand

You may refer to this post of mine http://stackoverflow.com/questions/3092121/sql-going-nuts-does-not-accept-parameters Simply put I don't have a choice I have to take input of which table the user wants and what the user wants from that table. Please suggest a secure way to achieve this, within the limitations of the code Thank you....

Can't update column values, it is associated with a clustered index?

I am having some problems when trying to update column values, this column has a clustered index associated to it. This is the update statement. UPDATE dbo.VentureXRef SET RefValue = REPLICATE('0',7 - LEN(RefValue)) + RefValue WHERE LEN(RefValue) < 7 This is the error I get Cannot insert duplicate key row in object 'dbo....

SQL Server - need a SQL Query to identify/highlight specific changes in an audit table

Say I have some data stored in an audit table, where triggers on the main data table write all invoice record updates to this audit table. The audit table contains this data: InvoiceID CustomerID ItemSold AmountSold SalesPerson ModifyDate 1001 96 Widget 800 Robert 2001-1-1 1006 85 ...

SQL Server 2005 replication - indicate data is replicated

Hi I am using SQL Server 2005 replication to replicate data from one table on a server to the a table on a remote server. This is happening in real time. i.e whenever the data is changed at the publisher, it is then replicated to the subscribers. From the publisher's server I need to know what data has been replicated to the subscrib...

What's the point to enclose select statements in a transaction?

Hi guys, What's the point to enclose select statements in a transaction? I think select statements are just "GET" data from the database, they don't have chance to rollback something, because you just can't change the data. So, does that to say we never need put select statements in a transaction? Am I right? Thanks. ...

Which of these queries is better to get the most recent history

I have a MASTER object table and an ACTION_HISTORY table that chronicles when a user has interacted with the object. I need to know when the last action was for each object. I am stuck between two approaches. Here are some simplified test tables CREATE TABLE MasterTable (ID INT IDENTITY(1,1) NOT NULL, someData varchar(20) NOT NULL CO...

TSQL Reverse FOR XML Encoding

I am using FOR XML in a query to join multiple rows together, but the text contains quotes, "<", ">", etc. I need the actual character instead of the encoded value like """ etc. Any suggestions? ...

An object or column name is missing or empty.

I am getting the following error An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. for the query show below: CREATE PROC [dbo].[Sp_T...

Trigger is not working on LInked Server. Both servers are SQL Server 2005

I have link a server with another server. I can access records from linked server and I can also insert record using INSERT statement like INSERT INTO [LINKED-SERVER-IP].MyDb.dbo.Customer (CustomerId, CustomerName) SELECT CustomerId, CustomerName FROM MyCustomers WHERE CustomerId = 5 Above query work ok and insert record but when I us...

With AS error in SQL

i am getting the error as shown below : Msg 156, Level 15, State 1, Procedure Sp_Table1, Line 22 Incorrect syntax near the keyword 'AS'. while executing this SQL: WITH myCTE AS (Select mci.* from view_name AS si JOIN merch_catalog_ipt_view_name AS mci ON mci.view_id = si.view_id AND mci.resolved_view_name_id = si.view_name_id A...

The multi-part identifier "si.shi" could not be bound.

i am getting the error as shown below : The multi-part identifier "si.shi" could not be bound. while executing this SQL: ;WITH myCTE AS (Select mci.* from view_name AS si JOIN merch_ctlg_ipt_view_name AS mci ON mci.view_id = si.view_id AND mci.resolved_view_name_id = si.view_name_id AND mci.ctg_ipt_event_id = @ctg_ipt_event_id ...

Delete large portion of huge tables

Hello everyone, I have a very large table (more than 300 millions records) that will need to be cleaned up. Roughly 80% of it will need to be deleted. The database software is MS SQL 2005. There are several indexes and statistics on the table but not external relationships. The best solution I came up with, so far, is to put the databa...