We are randomly experiencing this error when connecting to SQL W2K from web servers using ODBC. I see numerous links on the web but no real answers. [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionWrite (send()). ...more >>

I have a problem that occurs inconsistently. I have an ASP page that is updating a SQL Server table through a stored procedure and it seems that sometimes the update does not happen. There is NO error that is generated either. Trying to save the update again works on the 2nd or 3rd try someti...more >>

Hi, I am doing an inner join which results with duplicate columns and I am wondering if there is a way to remove the duplicate columns? I understand I can do it in the select statement however there is alot of columns i need in the resultset so looking for a better way. Any suggestions? T...more >>

I am creating a slightly-denormalized table by adding some user-friendly values (such as stock ticker symbols) for some user-unfriendly values (asset IDs). Anyway, without going into the complete DDL, is this the most efficient syntax to update the value in a field, with the corresponding v...more >>

Hi all, I've been spending most of the last week trying to find the cause of bad performance in my project. I have now been able to pin it down to the exact statement that causes the delay, but I still don't really understand WHY it executes so long and HOW to solve it. First the relevant t...more >>

I'm trying to determine what tables in a database are involved in a cascade update constraint. I've searched the net but found nothing. I know I can use sp_help to get this info. or sp_helpconstraint, but I don't know where these procedures are get their information. I would like to create a s...more >>

How to increment a value with datetime data type by 1 day? SELECT '2004/10/31' + 1 as [Inc Date] Expected Result : Inc Date ====== 2004/11/1 any idea? Thanks ...more >>

Hi, I have this result of a query Alum_ID Subject Result 357 Math 100 357 Math 80 357 Math 85 the diferent result is for the quantity of tests made it for the person, first , second and thir period. How can I do for make a query with this ...more >>

Hi, Does anyone know if a full list of SQL DMO Collections/ Objects/ Methods (with argument names and datatypes) and Properties (with datatypes) exists that I could download... ? or does anyone know how to generate such list, for example, from the Object Browser of the MS Development Environmen...more >>

Hello How I return 2.5 in this example? CREATE TABLE #A ( A int ) GO INSERT INTO #A SELECT 2 INSERT INTO #A SELECT 3 SELECT AVG(A) FROM #A GROUP BY A DROP TABLE #A ...more >>

I have seen several cautions like "don't depend on 'Display Dependencies' in Enterprise Manager; it's not reliable". I have seen evidence of this in SQL 2000 -- if you rename a view, the associated 'text' field in the syscomments table is not changed to reflect the rename. Which makes the...more >>

Hi, I am having some problems with xp_sendmail on SQL Server 2000. I am running Outlook 2003 with Exchange Server on Windows 2000, I have configured the Exchange account to run under the same user that is running SQLServer and SQLAgent. I can also send a sucessful test message from an operati...more >>

Please see my previous post (about 4 hours ago), just wondering if I should go to a different newsgroup for help. Thanks. ...more >>

Hello, I am uncertain when to build user-defined db roles to restrict or grant access to a db object or create a windows group to do the same. Any thoughts are best practices when implementing security in sol server with respect of when to use windows groups/user-defined db roles/ or both? ...more >>

Hi All, Should be a simple question! I have a Price column in a table which is of type Float with length 8. This column contains data like below: 827.66999999999996 645.58259999999996 827.66999999999996 1241.5049999999999 124.15049999999999 I would like to run an update statement and ...more >>

Is there a way to do an insert and have it pass back a value? I am inserting a new record into my table that has an identity field. The problem is I use the identity field to get my record, since I don't know that this field is when I insert, how do I get it back? For example: create t...more >>

Hi, I'm in the process of migrating a system from Postgres to MS-SQL. I'm wondering if there are any major SQL syntax differences between MS-SQL and the ANSI standard, or between MS-SQL and Postgres. Are there any websites that have this information detailed? Thanks! ...more >>

hi i need to write a script that converts a non-clustered index to a clustered index. anyone know how. the table is very huge and has about 5 million recs. create NONCLUSTERED INDEX [PK_Table1] ON [dbo].[Table1]([id]) WITH FILLFACTOR = 90 ON [PRIMARY] this is the index and its the primary k...more >>

I am reading data from a flat file and one of the columns in the file is MATERIAL and sometimes the values in MATERIAL column have a control character. I want to do a simple validation as follows if MATERIAL as a value containing only alphanumeric values like A-Z0-9 like the following values...more >>

Hi, I am getting error when I run the following statement declare @a nvarchar(255) select @a = tblname from logtbl where tblname like 'orders%' and logtbl.logdate > getdate() - .5 update @a set @a.log_id = l.log_id from @a , logtbl l where l.logdate > getdate() - .5 l.tblname...more >>

Hi, I have an sql in a stored procedure but I am getting an erro. @NumOfCust int --Parameter Declare @NumOfCustomer int SELECT @NumOfCustomer = @NumOfCust SELECT Top @NumOfCustomer t.SumOfSales, t.CustId, C.CustName, C.Contact, C.Addr1, C.Addr2, C.City --etc But I a...more >>

Hi, When I try to debug a stored proc in Query Analyzer, I get the following error message: The debugger interface is not installed. Please rerun setup, select 'add components to your existing installation' and make sure you select 'Development tools'\'Debugger Interface' Even when I reinsta...more >>

Hi all, I have been given direction to come up with a custom auto number system, but cant seem to wrap my mind around it. I would love some assistance. I need to be able to have a numbering scheme like the following: 00000001 00000002 00000003 ......... ......... If anyone could p...more >>

Hi All, I am trying to access the values of a inserted or deleted table in a trigger i.e. The follow code is ok... If Update(Field1) etc but not IF Field1 = value etc.. How do u access an inserted/deleted field name? Regards, Mekim (i posted the question into the wrong mi...more >>

Hi gurus, I need to know DataTypes for return of Stored procedure. I really have no idea how to do it. For example: some_function(execute my_sp @order) and result could be: column_name1|column_name2|column_name3 ------------+------------+------------ int |string |int it is...more >>

I want to be able to set a variable equal to the output of a system stored procedure. How can I do something like the following? declare @text as char(2000) set @text=exec sp_help_jobhistory...more >>

I'm doing a DBCC DBREINDEX & I get the error message [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 169: [Microsoft][ODBC SQL Server Driver][SQL Server]A column has been specified more than once in the order by list. Columns in the order by list must be unique. [Microsoft][ODBC SQL Server Dr...more >>

Two questions related to SQL searches (ideally everything will be in a stored procedure): I have an ASP.NET checkboxlist on a search page (this is a search of properties for sale). The checkboxlist is responsible for displaying the available amenities in a home (air conditioning, patio, garage...more >>

I'm used to working in Access and I can usually translate the sql syntax for sqlserver but I haven't figured this one out. I'm trying to delete orphan records. I can select them with the select statement but I can't delete them with an equivelent Access statement Works: SELECT Case_Attri...more >>

I posted a question yesterday but I think maybe i didn't explain it well enough so I am giving here a sample of something to work with for anyone that may be able to help me. At the bottom of this post is my original post if it's needed. One person did mention a website that does in a way tell ...more >>

I am a semi-experienced DBA, but have some questions regarding the storage of images inside of SQL Server (which I have never done before). I am researching to pros and cons of storing images inside of SQL Server verses a file server and DB combination (storing the file paths in SQL). The...more >>

I did "delete from mytable", but 2 of the 10 rows have child/foreign keys, so the command failed. How can I wrap that command to keep going and delete the other 8, ragardless of this kind of error? Thanks. ...more >>

Hi, I have this annoying transaction problem. It's annoying because it happens rarely. I have this database driven e-store. Database is SQL 2000. Basically people add items to their cart, then they go to checkout and complete their order. Every order is recorded at the last step by a sto...more >>

Hi folks ! I have a problem with a MSSQL-Database from by predecessor, which has a lot of orphaned entries. DB1 with 5000 entries DB2 with 45000 entries DB1:DB2 = 1:n link I want to delete all entries in DB2, which have no entry in DB1. There are several entries in DB2, but only on in...more >>

How the hell do you do this its driving me nutz. Select * from Table1 Where Col_A = (select * from Col_B in Another Table2) i.e I want to match items in Col_A with the same value in Col_B in Table2 help...more >>

Hi I have a long script I run on my default database, in the middle I change to the master - USE master EXEC sp_dropmessage 50001 GO How do I change back to my default database again without knowing the databse name. Our client rename their databases to suit their envonment so no databa...more >>

Thanks and advance. I have the next problem, i have a table like this id val1 val2 xx1 mat1 9 xx1 mat2 8 xx1 mat3 7 and i need a query to obtain the following result xx1 mat1 9 mat2 8 mat3 7 i need it for MS access is that possible???... any sugestions?? m...more >>

I am wondering if my stored procedure has been badly written? CREATE PROCEDURE spFullTextSearch ( @sSearchText varchar(255), @nNumRecsToFind int, @nJustReturnTheseRecs int ) AS SET ROWCOUNT @nNumRecsToFind select *, tempidentity = ItemId into #Store_BasicSearchableItems_TEMP fro...more >>

HOW CAN I CONVERT GET DATE FOR VARCHAR(5) HH:MM??? HELP PLEASE!!!!...more >>

I am working on this stored proc listed below. I am trying to make 3 sp into by passing in the @type var which is = to Billing, System, and Log. When I hardcode say '%Log%' in the place of the var it works when I pass it in it doesn't but no error. Does anyone see anything jumping out at them?...more >>

I have tables with the following layouts: Table1 Account# Month Actuals -------- ----- ------ A1 200401 $100 A2 200401 $250 A2 200401 $250 Table1 Account# TotalPlan -------- --------- A1 $1000 ...more >>

I want to be able to easily create a new table with the same structure as another table in the same database. Right now I would have to use the create table statement and type out all of the columns and datatypes to do this quickly. This works well for a table with a small number of fields and ...more >>

The following groupby query takes 3 seconds to execute: SELECT max([ObjectName]), max([CounterName]), [InstanceName], max(machinename), substring(CounterDateTime,12,5) as CounterDateTime, sum([CounterValue]) as CounterValue FROM [Exchange Performance Data].[dbo].[CounterData] as a in...more >>

Hello all! I'm not sure the best way to approach this problem. I have a simple math formula that I'm using to figure out odds. The numbers the are being used in the formula are dynamic. They are being updated by user input over a period of 2-3 minutes. So the odds are always being updated...more >>

Is there a way to make a SQL statement pause for a few milliseconds? I need to create a PK based on time. Sometimes i end up with times which are identical: 2004071343697 thanks, Justin ...more >>

I have the following 2 tables. I'm trying write a query and an update statement to calculate the elapsed hours between the Entered Date (entdte) and the Closed date (SVCClosed), taking out the 48 hours on the weekend if a DayofWeek in the calendar table is 1 or 7, and populate the AdjustedHou...more >>

Tried posting this in another SQL newsgroup and got no response, so trying in this forum. If I am in the wrong place, just let me know where would be a better fit for this type of problem. Thanks for all the help: OK, I am having problems understanding how to lay this sql out. Here is an exam...more >>

Does it make sense to create a stored procedure that does nothing but perform a simple select search? For example, a stored procedure has a parameter of @Value and the stored procedure basically performs: SELECT * FROM TABLE WHERE Field=@Value; Wouldn't it be faster to simply execute t...more >>

I am trying to save an embedded excel document with xp_readmail from SQL2K SP3 on a WIN2K server and Outlook 2000. The @attachment variable is always empty. Regular attachments work fine. There is only a problem if they are embedded. (This still works fine under our older SQL7 NT box) I s...more >>

Is there a way in SQL Server 2000 to have alphanumeric field that auto increments in the same way as an Identity feild. For example "US00001"? If there is what datatype should the feild be set to and how do I have it auto increment? Thanks in advance Russell...more >>

Hi Firends, I have remote database server (2000) behind Firewall. Ican get the connetion to the remote machine.When i execute some query from my VB 6 application. Query is not executed. I get Error -21474672559. At the SQl server machien in the Event Viewer I get the following mess...more >>