sql-server-2005

Refactoring "extreme" SQL queries

I have a business user who tried his hand at writing his own SQL query for a report of project statistics (e.g. number of tasks, milestones, etc.). The query starts off declaring a temp table of 80+ columns. There are then almost 70 UPDATE statements to the temp table over almost 500 lines of code that each contain their own little set o...

Reduce deadlock on PAGE level on update query on MS SQL

I have some funny deadlock caused by a stupid simple SQL UPDATE query, on a flat plain table, under default "READ COMMITED" transaction. UPDATE table SET column=@P1 WHERE PK=@P2; While PK varchar(11), has a clustered index on it. no trigger or table relation..etc on the table. I did some check and find that the deadlock happen ...

What is the best way to do a wildcard search in sql server 2005?

So I have a stored procedure that accepts a product code like 1234567890. I want to facilitate a wildcard search option for those products. (i.e. 123456*) and have it return all those products that match. What is the best way to do this? I have in the past used something like below: *SELECT @product_code = REPLACE(@product_code, '*', '...

"select * from table" vs "select colA,colB,etc from table" interesting behaviour in SqlServer2005

Apology for a lengthy post but I needed to post some code to illustrate the problem. Inspired by the question What is the reason not to use select * ? posted a few minutes ago, I decided to point out some observations of the select * behaviour that I noticed some time ago. So let's the code speak for itself: IF EXISTS (SELECT * FROM...

SQL CLR function and OleDb permissions

I have a .NET 3.5 C# library that uses OleDb to pull data from an Excel file and return it in a DataRowCollection which is used by a CLR Table-Valued Function in SQL Server 2005. I deployed the ASSEMBLY in SQL Server as sa and used PERMISSION_SET = EXTERNAL_ACCESS. The sa login has EXTERNAL ACCESS ASSEMBLY and the database has TRUSTWOR...

What's the best way to update my asp.net application / database after changing the schema?

Hi, One of the core fields present on several of my app's major database tables needs to be changed from varchar(5) to varchar(8). The list of stored procedures that depend on these tables is extremely long, not to mention the changes that would have to be made in my Data Access Layer (ASP.NET 2.0 DataSet with TableAdapters). Is there ...

Optimizing unions

HI, im having trouble trying to optimize the following query for sql server 2005. Does anyone know how could i improve it. Each one of the tables used there have about 40 million rows each. I've tried my best trying to optimize it but i manage to do the exact opposite. Thanks SELECT cos , SIN FROM ConSisHis200...

retrive unicode using asp from sqlserver 2005

I'm using the following code to retrieve a message from the database and then write it out to a html page: Dim strDSN, cnn, cmd strDSN = "Driver={SQL Server};" & "Server=(local)\sql2k5;" & ... set cnn = Server.CreateObject("ADODB.Connection") cnn.ConnectionString = strDSN cnn.CursorLocation = adUseClient cnn.Open set cmd = Server.Creat...

Windows Server 2008 as development machine - step by step

I have found many tutorials about using Windows Server 2003 as a development machine, and very little information about Windows Server 2008 for the same purpose. For a nicer experience, I have followed the steps from Convert your Windows Server 2008 to a Workstation. I am searching for the requirements and installation order for IIS 7 ...

Backup Database from ServerGrid.com

Hello I have a hosting account with servergrid.com. I want to backup my database, they say I have to use Sql Server Integration Service to backup the database and I would need a commercial version of Sql Server management studio. I have Sql Server 2005 Developer Edition. I have no idea how to do SSIS backup. I tried playing around with...

Execute DB2 iSeries Stored Procedure from a SQL 2005 Linked Server

I am trying to execute a stored procedured from a linked database in MS SQL 2005. The linked database is a db2 database on a iseries server. I am using the iSeries IBMDASQL service provider. I am able to query the stored procedure without problems using a simple vbscript and ado. When I try to execute the same stored procedure in que...

SQL group by day, show orders for each day

I have an SQL 2005 table, let's call it Orders, in the format: OrderID, OrderDate, OrderAmount 1, 25/11/2008, 10 2, 25/11/2008, 2 3, 30/1002008, 5 Then I need to produce a report table showing the ordered amount on each day in the last 7 days: Day, OrderCount, OrderAmount 25/11/2008, 2, 12 26/11/200...

Occasional Deadlock.

I'm having trouble with a web application that will deadlock occasionally There are 3 queries involved. 2 are trying to update a table UPDATE AttendanceRoll SET ErrorFlag = 0 WHERE ContractID = @ContractID AND DATEPART(month,AttendanceDate) = DATEPART(month,@Month_Beginning) AND DATEPART(year,AttendanceDate) = DATEPART(year,@Month_Begi...

How many columns are too many for a sql 2005 table?

I have a request to allow a dynamic table to have 1000 columns(randomly selected by my end users). This seems like a bad idea to me. It's a customizable table so it will have a mixture of varchar(200) and float columns(float best matches the applications c++ double type). This database is mostly an index for a legacy application and s...

SQL Statement Help - Select latest Order for each Customer.

Say I have 2 tables: Customers and Orders. A Customer can have many Orders. Now, I need to show any Customers with his latest Order. This means if a Customer has more than one Orders, show only the Order with the latest Entry Time. This is how far I managed on my own: SELECT a.*, b.Id FROM Customer a INNER JOIN Order b ON b.CustomerID...

What is the best way to use SQL Server Analysis Services data in a line of business application?

We'd like to see if we can get some improved performance for analysis and reporting by moving some of our key data into Analysis Services cubes. However, I haven't been able to find much in the way of good client front ends. Our users have Office 2003. The move to 2007 is probably at least a year out and the Analysis Services add-in fo...

Msg 64, Level 20, State 0, Line 0 SQL Server Error

I am running a sproc on an SQL Server 2005 server which is resulting in the following error: Msg 64, Level 20, State 0, Line 0 A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) Once the error occurs I loose m...

Any guides on learning how to interpret a SQL query's Execution Plan in SQL Server 2005?

Is there any good article, tutorial or similar references on interpreting the Execution Plan of a query in SQL Server 2005? ...

How do I check if a Sql server string is null or empty

I want to check for data but ignore it if it's null or empty. Currently the query is as follows... Select Coalesce(listing.OfferText, company.OfferText, '') As Offer_Text, from tbl_directorylisting listing Inner Join tbl_companymaster company On listing.company_id= company.company_id But I want ...

Create a DDL trigger in every database on a 2005 instance

I need to create a trigger in every database on my sql 2005 instance. I'm setting up some auditing ddl triggers. I create a cursor with all database names and try to execute a USE statement. This doesn't seem to change the database - the CREATE TRIGGER statement just fires in adventureworks repeatedly. The other option would be to pr...