What's the difference between SQL 2005 Express (which gets installed with VS 2005 by default) and SQL 2005 Developer editions? Also, I miss the old SQL Enterprise manager and query analyser so I've installed SQL Server Management Studio Express. What does the Studio Express bit relate to? I...more >>

Hi, I have selected a field name and declared it as varchar, since it is varchar in table and performed some numeric operation with numbers, even after i cast the sql in below code, it throws an exception as "Error converting data type varchar to numeric." code: CREATE PROCEDURE ...more >>

Hi, When I run the following sql statement, I got error message: Cannot specify outer join operators in a query containing joined tables. View or function 'v_Address' contains outer join operators. SELECT Name_a.FullName, v_Address.Line1, v_Address.CityName , v_Address.ProvinceName,...more >>

Hi, I use my gmail account to send email from outlook express. But when I try this account from DatabaseMail, it fails with the following error: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail using Account 15 (2007-05-01T20:46:41). Exception ...more >>

Hi all, Using SQL 2005, I have a table of office locations (simplified DDL below) CREATE TABLE [dbo].[Offices]( [OfficeID] [int] IDENTITY(1,1) NOT NULL, [OfficeName] [varchar](50) NOT NULL, CONSTRAINT [PK_Offices] PRIMARY KEY CLUSTERED ( [OfficeID] ASC )WITH (PAD_INDEX = OFF, STATIST...more >>

In a situation where I need to build a report that executes an existing stored procedure which concludes with the following code "print 'Process is complete'" Since I do not want to alter the existing code, i want to build a new proc which calls this proc and then does a select on some tabl...more >>

> I have a table in SQL Server 2000 that contains filenames in a text field > and files of various types (.doc,.xls, .jpg) in a image datatype field. I > would like to write a program within a dts package that would read each and > write the contents of the image field to a directory, saving it...more >>

Hi, I have a string of 15 characters with format 99-AA-99999-999. User can enter any length of data at number's position. It could be i- 9-AA-999-9 or ii- 99-AA-9-9 or iii- 9-AA-99-9 or anything but in the limit of 99-AA-99999-999. Now depending upon user input I want to put leading 0...more >>

Hi, I am using a update trigger to send a mail if the record of a field has been updated. This works fine. However the problem I have encountered is if the record is not there then it is created which also generated the FOR UPDATE trigger. Which I do not want to do. I just want to create this...more >>

Dear friends: Writing a UDF. Purpose, the views which I've already written (7 of them) are too slow (> 2 minutes). My experience is that, if you dump the raw data into a temporary table it can be processed in memory many times faster than the views would do it. Msg 1220, Level 17, Stat...more >>

I have SP which returns some value in hierarchical like manner: SELECT SPACE(dbo.f_FinancialAccountTable.Depth * 3) + dbo.f_AccountTable.[Name] AS DisplayedName FROM .... It returns DisplayedName preceded with as many spaces as Depth field contains multiplied by 3. I'd like to replace Spac...more >>

I would like to query a table to return results that have a date column > than a certain date. However, I can't retrieve results while comparing like this: Where StatusDate > '1/1/2007' Because there are nulls --I swear i've done this before but I can't think of how ... ...more >>

I have a table, definition below USE [ptest] GO /****** Object: Table [dbo].[mirror] Script Date: 05/01/2007 18:02:56 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[mirror]( [r1] [int] NULL, [r2] [int] NULL ) ON [PRIMARY] with the following data...more >>

I created a Table-Valued Function to return a record to use within the recursive processing of a CTE. It is giving me a compile error of Msg 102, Level 15, State 1, Procedure upGetSubordinates, Line 91 Incorrect syntax near '.'. Msg 102, Level 15, State 1, Procedure upGetSubordinates, Line ...more >>

(cross-posted from comp.databases.ms-sqlserver) I'm using a 3rd-party app's back end which stores SQL statements in a table, so I have no choice but to use dynamic SQL to call them (unless someone else knows a workaround...) Problem is, I can't get the statement to run properly, and I can't...more >>

I know it's recommended to not use Numeric and, instead, use decimal.....however, I need to store mileage, which, needs a decimal type number, but I don't really want to specifiy a precision - I don't want it to pad with leading zeros. How can this be accomplished? ...more >>

I'm just butting up against the issue of Authorization and Authentication in a Windows client-server environment. We are (re)creating a windows based client-server app in .net which connects to SQL Server 2005 and Application Roles seem to be compelling. We can manage all our application functio...more >>

In SQL Server 2000 I receive the error message "Invalid use of getdate() within a function" when trying to CREATE a function (please see function snippet below). Any corrective advice or work-around would be appreciated! TIA...Chris. BEGIN DECLARE @weeks_hours DECIMAL(15,2) BEGIN ...more >>

Here is code I am trying to use to pull data from Excel: SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=K:\sfcs_scanning\rates.xls', 'SELECT * FROM [Employeelastnamefirstdeptdesc$]') I am getting an error back: Msg 7399, Level 16, State 1, Line 1 The OLE DB pr...more >>

Some of our developers are getting VS.NET installed on their machines here at work. My team, the SQL team, want it as well. We are migrating to SQL 2005 at the same time. I need to make a case for why my team should also get VS.NET. We want the full enterprise/prof version. Any ideas? ...more >>

I am developing some reports and have noticed the following behaviour. When I run the following query from Query Analyzer(SQL 2005), it takes approximately 3 mins. But when I run using ADO connection it will take ~15 mins. Do you have any suggestions that might explain this type of behavi...more >>

I'm tried to trace a deadlock situation. I notice that sometimes I will only see Lock: Deadlock Chain event but no Lock:Deadlock event and no Exception event even though I'm capturing those events. What does it mean? ...more >>

One of the configuration options that you see when you do an sp_configure is "query wait". The default value is -1. We want to set it so that query wait is unlimited. We are guessing that setting the value to 0 would do this, but we can't find any documentation to confirm that. Does anybod...more >>

Hi, Do I need any special permissions on 2005 when executing sp_executesql? The user I am executing the sproc as is the dbo. of the database but fails. As soon as I make the user member of sysadmin the sproc executes? Thanks,...more >>

Hi, I have a few questions about converting *= and =* to ansi outer joins. 1. It seems that if there is =* which suppose to be converted to 'right outer join'. But it doesn't return the correct result. I had to change it to 'left outer join' and switch the table columns, then it will work....more >>

Hi Guys, I have a question regarding the job. I want to setup a job running every 5 minutes to execute a store procedure that inserts data into a table. I wonder if the new job start to run when last job run over 5 minutes? That means if there are two jobs are running in the same time, or the ...more >>

Dear all, I have an .net application which store data on a local SQL express 2005 database table "History". Then I have a pocess wich reads stored history data from that table and then set the Read column of that table to "1". Then I have a Windows service which is suppose to flush rows f...more >>

Greetings: 1. I know that I have used this feature before, but sitting at my SQL box at home, I cannot remember where the setting is. I have been all over Query Analyzer and Enterprise Manager, and the Server Properties dialog. I want to set the "default database" for client connections, so ...more >>

I have thie very simple table: ID processtime cost category 1 100 50 0 --- labor 1 200 60 1 --- sewing 1 300 70 2 --- boxing What I want is this: ID labortime laborcost se...more >>

Hi Guys, I am trying to update some values in one table based on the results of a query on another table and it isn't really going well. I want to update some rows in the customer table with new values if the details in the address table meet certain criteria. The following select stateme...more >>

If I create a view and execute it, the cells in the results pane aren't updatable. If I create an function or stored procedure to return the view and execute them by typing "Select * from Func()" and "Exec Proc", respectively, the cells returned are also not updatable. However, if I open t...more >>

I'm getting the following error in the web client: Transaction (Process ID 164) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. Is there a way to tell in which stored procedure this error was thrown or from which...more >>

I get Error 170: Line 12: Incorrect syntax near '-'. Any ideas? Thanks in advance. The good news is, it looks like I got past 169 other errors. :-) CREATE function dbo.myfunc ( @var1 varchar(255), @var2 double precision, @var3 double precision, @var4 double precision, ...more >>

Hi, I am about to start on a project that involves building a financial forecasting application. The database will be SQL 2005. The basics of the system involve forecasting 4 revenue sources for each month of the year. I am trying to figure out the way to design this. Should the forecas...more >>

I have a pdf copy of 'The Waite Group Visual Basic 6 Database How To' and reading through it seems an excellent book. However when I came to do the examples I found that I really need the CD. I have been on Amazon, and they have some very cheap deals - but they won't ship to UK (The Co's sel...more >>

I am using SQL Server 2005. I created a Maintenance Plan to backup the Transaction Log every 4 hours with "Verify backup integrity" checked. I thought after the backup the transaction log will be truncated, but when I check the size of my DeskMZ_log.ldf it is 102 Meg, while the size of DeskMZ...more >>

I have got an application running which connects 2 databases and performs an action that copies data from one database to the other database. When using SQL Server 2000 MSDE, it takes about 5 seconds. When using SQL Server 2005 Express Edition it takes about 30 seconds. For testing 2005, I us...more >>