Hi, A] Regarding Identity column values: Create table A(id int identity(1,1) Primary key) For above table, 1. What will happen to the insert statement when the identity column value has reached the maximum value defined for integer ? My answer: Should generate an error message. 2. Wil...more >>

I several instances of SQL server on my Win 2k3 box - from all those failed installations - and now I'm trying to get rid of them. I tried to uninstall SQL server, and I got the error: = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Uninstall - - - - - - - ...more >>

Dear fellow expert, In the select statement, I want to get the data from the table in different server. Then I found that I could use this format to fulfil my need: : : Select D.FACTORY,D.CATEGORY,D.[TODAY SCRAP $$],M.[MTD SCRAP $$],Y.[YTD SCRAP $$] from pyserver.JMIDB..V_INM_ANALYSIS...more >>

Hello all, What would the difference be between SQL 7.0 and SQL2000 ? The reason for my question is that I have been offered a contract to create an app in VB6, using SQL2000 SP3 on a 2K3 server. The VB side is OK. I have written a number of apps for VB6/SQL7 so I know my way around that co...more >>

Win2K, SQL2K SP3a: With a query like this: Update b Set b.TextField = a.TextField From LINKEDSERVER.DatabaseName.dbo.RemoteTable a, LocalTable b Where a.Id = 5 I get b.TextField updated with an empty string, even though I know that a.TextField holds some text. The interesting thing is ...more >>

Hi all, I understand that when I am denied access from the table but given the right permission in a sproc accessing the table, I am still able to access the table. However I have a table which I am denied access but given the correct permission yet I am still not able to access. I have ch...more >>

I am looking for a query that will convert inches into feet: Here is what I have: SELECT Orderid, Bundlename, PanelID, Linenumber, Height, Length, Panelstatus, CompletedDT FROM panel WHERE completedDT BETWEEN '2004-08-25 06:00' AND '2004-08-25 17:00' and panelstatus='C' order by completeddt ...more >>

Hello, Would someone out there understand and can explain to me why clustered index for GUID primary key will result a faster select operation when we look up one row by a primary key like below comparing with non-clustered index for the primary key? I experimented this myself, so there is no...more >>

I am writing an application that calls numbers of stored procedure wrapped in an ADO.NET transaction. Some of the stored procedure implements T-SQL transactions. Is it safe to call Stored Procedure like this or there is potential of data corruption. Have anyone seen any document or article...more >>

Hi all SQL server 2k (dev ed) I have another problem that I would hope you can help me with. I have a Member table where each member has a score value (DDL & DATA AT BOTTOM) If I do the following select: select MemID, score from members order by score desc, MemberID ASC MemID ...more >>

Hello everyone, I am trying to find some benchmark that compare different database engines. I already know what are the pros/cons of Oracle vs SQL, but I have to come up with metrics to help us choose the best database for our needs. Thank you! Martin Paré ...more >>

Hello, I am trying to remove the public role on all the databases for security. However there are so many permission that I have to uncheck under Enterprise manager. Is there a easier and faster way to do so? Thanks in advance. ...more >>

how can i check for the existence of a transaction - similiar to checking for the existence of a cursor by using: IF CURSOR_STATUS ('global', 'myCursor') >= 1 BEGIN CLOSE myCursor DEALLOCATE myCursor END ...more >>

Hi, can anyone tell me how to keep trailing whitespace in an NVARCHAR field? I have an NVARCHAR column with a unique constraint, where the strings 'abc' and 'abc ' should be considered different. (Currently the unique constraint fires). I've had a look at ANSI_PADDING, but according to the ...more >>

Hi all, Right now I just type in the date to get the correct numbers for the month. how would I write it to loop through what months have past for the current year displaying each months numbers and, the current month-to-date. DECLARE @Start DATETIME DECLARE @End DATETIME SET @Start...more >>

I have the following query: SELECT sum(case when datetime > CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112)) AND step >= 1 then 1 else 0 end) as step1_td, sum(case when datetime > CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112)) AND step >= 5 then 1 else 0 end) as step5_td, sum(cas...more >>

Hi, I'm trying to use the full-text search. My Enterprise Manager shows as the Full-Text Search as running. WHen i try to index a field on a table that is VARCHAR(8000), it tells me that the max for the full text search is 900 bytes. Any ideas on how to index this field? thanks rafael ...more >>

Hi, I am trying to make a Resume Database. But there are some issues. I get resumes in word format. I want to store these resumes and be able to do a search through them. For eg: If i search for Mechanical, i'll get the resumes of all people who have mechanical in their resume i.e. Mechanical ...more >>

Hi, I use in my data base smalldate data type. When I use select statement the date is like 8/24/2004 12:32:56PM (m/dd/yyyy time). Is it possible to be: 24/08/2004 12:35:56PM (dd/mm/yyyy time) and if yes how? Thank you very much! Regards, Viktor --- Outgoing mail is certified Virus Fre...more >>

One of our offices is running a special, the fee for services is less than normal I don't have access to the code that adds the fee, so I figured a trigger would work. I'm creating a trigger to change a fee inserted into the database if the purchase originated from that specific office. ...more >>

I have a query, trying to divide two fields... SELECT (table1.field1/table2.field2)AS '%documentation' From... The actual values for the fields are: Field1 -1034 Field2 -1657 So the the answer %documentation should equal 0.62401931 But it doesn't- How do I fix this?? THANKS!!...more >>

In a program I am working on I have data which can be coming in faster than it can be processed. Rather than process it on the fly, the data is being added as rows in a table which is being read from a separate process (or processes) at a slightly slower rate, and processed there instead. Th...more >>

Hi, There is some problem the way in which sp_executesql works. What happens is when I manually replace @Next with NULL, the query works. But, when I execute this query it hangs. This is the query I use declare @CNSMPLSEQID varchar(8) declare @status char(1) declare @NEXT char(1) declare...more >>

every time i run the debugger on an SP, any SP, I get this message ODBC: Msg 0, Level 19, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]SqlDumpExceptionHandler: Process 59 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process. This ...more >>

SQL2K backend w/Access 97 front end. I'm using views in certain SQL statements where I am UPDATEing a local table with data from the view. But Access gives an error "Operation must use an updateable query" even though I'm not trying to update anything in the view. The reason for the error is the...more >>

I have a the following view in my database: CREATE VIEW vQtyLast365 AS SELECT tblSalesRamacDetail.itemID, Sum(tblSalesRamacDetail.qtyShipped) AS SumOfQtyShipped, Sum(tblSalesRamacDetail.qtyOrdered) AS SumOfQtyOrdered FROM tblSalesRamacDetail WHERE tblSalesRamacDetail.invoiceDate BETWEEN getD...more >>

Hi, Maybe you can help me with following problem: I am running a VC++ program using ODBC to connect to a MS SQL-Server2000 instance (running on a Win2k Server) which queries and havily inserts rows into several databases. But now I found many files named 'CTTxxx.tmp' in the systems temporar...more >>

Hi Is it possible to loop through the Files in a directory and delete certain ones based on there names. Would I use FileSystemObject for this maybe Ian ...more >>

Hello, I want to format my float number to a precison of .5 i.e: 30.1 => 30 30.5 => 30.5 30.6 => 31 How is this possible? Thanks! ...more >>

In a SQL Server 2K table, I have a field formatted as a SMALLDATETIME field, and the values are stored with Year, Month, Day, Hour and Minutes. In my query, I need to work with only the "DATE" part of this field. Basically, drop the hours and minutes, as if they were 00:00. I've tried usi...more >>

Should I explicitly drop temp tables that I have granted or should I let Sql Server handle that for me? My purpose is to release resources. Thanks . ...more >>

Do you know how to add a row counter to a view? Ie. I have a dynamic link to sales data and I have a descending rank on revenue, but I want to add a dynamic field called Rank that mirrors the order of the descending revenue. Ie.. Rank Total Revenue 1 10...more >>

Hi! I have developement and stage environment. They are both set up with same options. They both point to same link server. When I execute them, I get two different execution plans. Any Ideas? SELECT DE.CLIENT_ID, DE.DATA_ERROR_ID, U.HDC, U.DISPENSE_DATE, U.PATIENT_ID FROM dbo.DRX_DA...more >>

I need to read eventlog entries looking for login/logout events so that I could update a table that tracks the worked hours of the employees in my company. Has someone already done something similar? Is there any place to look for login/logout events other than server's eventlog ? Is there any wa...more >>

We are pulling in Real Time/Stock - Time and Sales quote and trade Data from an ascii file with TABS as delimiters into a table to hold the raw data... tbl_AllData the string structure we pull in is as follows DATE TIME PRICE VOLUME BID BIDSIZE ASK ASKSIZE if a string is a quote string...more >>

Hi Why would this error stop the execution of my Stored procedure. Server: Msg 8501, Level 16, State 3, Line 1 [Microsoft][ODBC SQL Server Driver][SQL Server]MSDTC on server 'Server1' is unavailable. I thought that only errors with level 20 to 25 did that. Ian ...more >>

I tried to install SQL server, and I've got the error: = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = = Fatal Error - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Execution cannot continue as the language dependent resource fi...more >>

im a newbie.. i want to learn using function in a stored procedure and query analyser.. any example or url can u offer me? ...more >>

I am trying to run a SQL Delete statement on a table and it causes SQL Server to hang (SQL Server seems to be processing the statement but it doesn't do anything. No records are deleted from the table and SQL Server continues to run the Delete query.). I am only having this problem on one ...more >>

Say I have a Table A with 100,000 rows and 4 columns, and a Table B of 10,000 rows and 15 columns. Table B is a subset of Table A, and has a one-to-one relationship. Also, if a key exists in Table B, it must also exist in Table A. I've read in places to be wary of one-to-one relationships, yet a...more >>

Hi, Is there any way to use macro substituion in sql server ? In a stored procedure I am having some variables like var1, var2, var3, var4,var5. I want to print the values of these variables in a for next kind of loop using a print statement and the current counter value of the loop. Tha...more >>

I have a DTS package which has an ActiveX Task. I want to add a line into the ActiveX task that will run the folowing as if it were run from the command prompt: "copy file1.txt+file2.txt /A file3.txt" How would I go about doing this in an ActiveX script? Thank You Nick ...more >>

I have a number of records in a table and I want to check the time difference on each record with the next record and loop until the end of the recordset, or possibly add and extra column containing the difference in seconds/minutes/etc The column is datetime and the data looks like this: 1...more >>

Hi guys, I need a stored procedure two SELECT rows from table based on multiple search criteries. The problem is that not all of them may be supplied to the stored procedure. The search should be done only with parameters that are given to the procedure. I guess that all parameters should have...more >>

I need to create a page that will allow a user to put in a start number, end number and a date and have this insert all the numbers from start to end with the date entered into a table. SQL 2000, ASP Can anyone please give me a sample of what the insert code would look like please?? Thanks...more >>

Please help me. I have to do a report on date wise. I want to pull data from 10 tables and group them by date . There is only one field common in all the tables. Each tables has about 30 columns. Thanks Rakki...more >>

Hi all backend: SQL Server 2k (dev ed) frontend: ASP IIS5 jScript. First let me apolgise for the length of this post, but I have included all the DLL & example inserts below, also I'm new to Stored Procs/db design. I'm trying to create a simple forum system that will be hard for members...more >>

Hi all, Newbie question. How does one change the name of a linked server? I know I can drop and recreate, but I would guess that with a linked server of VFP2, I should be able to use sp_rename: sp_rename 'VFP2', 'CustomerVFP', 'OBJECT' but have tried that and a number of variations, gen...more >>

I am receiving the following error: Server: Msg 7391, Level 16, State 1, Line 1 The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the specified transact...more >>

Forget about the silly $QL $erver language.... :) -- .~. Might, Courage, Vision. In Linux We Mutate. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.27 ^ ^ 6:58am up 7 days 6:40 load average: 1.04 1.01 1.00...more >>

My DB was suffering of many deadlocks,..I did the following actions to resolve the problem: - Got all SPacks and hot fixes for the server. - Optimize some procedures and querys. - Did a dialy SQL JOB that shrink the DB and with no log as follows: backup log ADCS with no_log ...more >>

Hi, Is it possible to pass in a variable in the sp_oaMethod as follows.. EXEC @hr = sp_OAMethod @QMAcc, 'MakeKeys(@accountid, 1)', @nAccKeyCount OUT The problem is with the 'MakeKeys(@accountid, 1)', part. @accountid is a variable that is set before the call to the sp_oaMethod but it obv...more >>

Hi all! I have a Windows service, where I want to use a timer.. But i does not seems work?? It does not catch the Timer1.tick event ??? But the code works on a form??? Can anyone help me, please?? "Onstart" - I set: Timer1.Interval = 5000 Timer1.Enabled = True ...more >>

Hi, How to decrypt stored procs? We are using MS SQL Server 2000. Can some one let me know please ... Many thanks in advance, Harish Mohanbabu ------------------------------------------- <b><i>Long way to go before I sleep ....</i></b>...more >>

I am writing a stored procedure in which I need the following logic. Problem is that I get an error message: "Incorrect syntax near the keyword 'IN'." Does anyone know what's the problem? Thanks in advance, -Itai --------------------------------------- SELECT * FROM myTable WH...more >>