hi all, I have a problem in creating a view with schemabinding for data across multiple databases from multiple instance of SQL Server 2000. But when i create a a view with schemabinding in the same database it works fine.. create view dbo.VWUSERINFOSM WITH SCHEMABINDING as...more >>

I try to identify the value of increment property of a column. INFORMATION_SCHEMA does not suppply it. Somebody know how to get it ? Thanks ...more >>

Hi All Please tell me difference between function & stored procedure. Also we can use views instead of procedure to get result of select. Then where to use procedure and where view Please help Thanks...more >>

Hi I have developed an application (Client Server) using power builder and MS SQL SERVER 2000. I can store/retrieve data (Korean + English) from database using power builder application. It is working great from front-end application. But I when I check from SQL QUERY ANALYZER it show English dat...more >>

I have developed an application (Client Server) using power builder and MS SQL SERVER 2000. I can store/retrieve data (Korean + English) from database using power builder application. It is working great from front-end application. But I when I check from SQL QUERY ANALYZER it show English data co...more >>

I have two identical tables (TableA has data, TableB is empty) Using SQL, I want to select a range of records from TableA and insert them into TableB. Can someone help. Thanks Ed ...more >>

How can I update a record with an UPDATE SQL statement when the table doesn't have any primary key or other unique columns? For instance: NORTHWIND.OrderDetails There must be a way, because the MS SQL Enterprise Manager lets me edit records from NORTHWIND.OrderDetails in its data grid (tab...more >>

Hi I'm trying to BULK INSERT some data from a simple text file, residing on a mapped drive (F:\) Well, I know, it's better to use UNC-Paths, but it was "programmed" in that manner some years ago. (Some dozens of stored procs which I don't want to change) This problem persists since we upgr...more >>

I need to achieve a situation where I save every record I edit .i.e the pre - edit information I have a number of stored procs - UPDATES/INSERTS which are manipulating data across 32 tables. I need to log every every column , (data prior to edit of record). Is there a standard way of doing this...more >>

-- I have this basic structure: -- create table userxfolder ( userid int not null, folderid int not null ) alter table userxfolder add constraint pk_userxfolder primary key ( userid, folderid ) create table folder ( folderid int not null ) alter table folder add constraint pk_folde...more >>

Hi all We have a table "Stock" which has columns like "StockID", "QtyInStock", "QtyOnOrder" and "CurrentSellingPrice". We make a lot of price tweaks, and it is a nuiscance having to change prices at the exact time which we want them to. I am considering using a different table structure to ...more >>

I'm New to the SQL Game, and could use some help Excluding some records. I have the following Tables. Table:[applications] (Purpose: Holds Application Info App_ID int (Primary Key)(Identity) App_Name nvarchar(50) Example Data [APP_ID] [App_Name] 12 .NET Server Farm 13 SQ...more >>

I need to be able to select and drop a database. I can drop the db, provided I haven't connected to it. Once I open the connection, the server says it can't drop the db 'cause it's busy, until my program closes. Closing/freeing the connection has no effect. How can I get SQL Server to re...more >>

Hi, Is it possible to define own constants in MSSQL 2000 to improve readability? Instead of writing my stored procedure like: CREATE PROCEDURE dbo.GetThrillers .... SELECT * FROM Books WHERE BookType=1 .... GO I want to write something like TRILLER = 2 (defined not only in one store...more >>

Hi, I am looking into creating triggers to populate a separate table whenever a record is deleted, inserted or updated on a different table. Is this possible and can any one point me in the right direction. A good book would be helpful. Thanks, Ellie ...more >>

I see many posts here where people link to good articles and stuff. I assume that some of the people in here (MVP's especially!) have acquired some great bookmarks over time that would be useful to other readers. Would any of you share your internet bookmarks? ...more >>

I know this is a SQL101 question, but darned if my fried brain can figure it out right now. I want to insert rows into a table which only has 2 fields. IT comes from an ASP form element which allows users to select, if they choose, more than one selection. If they only select one, it works fin...more >>

Hi, I have a query which returns this error message when run on SQL 7. The fields in the Query are indeed longer than 8194, when all are added up, so I created a view to reduce the big fields to length 1000 each and based the Query only on this view. I have still this error message coming u...more >>

Hello: I was wondering if anyone might be willing to provide some insight into the best datatype to use for primary keys in SQL Server 2000. From what I have read, in situations where uniqueness is required across servers, GUID's work well. In cases where universal uniqueness is not requi...more >>

how can I know which version of sqlserver I am running? I rememeber soething like select @ver or something like that.. ...more >>

Hi all When I execute PRINT ISNUMERIC('+' o PRINT ISNUMERIC('-' The result is 1!!! o_ And, if I try to cast it to a numeric value the operation fail declare @str_val nvarchar(10), @int_val intset @str_val = N'-'SET @int_val = CASE WHEN ISNUMERIC(@str_val) = 1 THEN CONVERT(int, CONVERT(float...more >>

I cannot get my extended stored procedure to debug. I am using c++ in VS .NET running SQL Server 2000. I can get my project to break in dllMain, but after that, my sqlserver console window reports Using 'xp.dll' version 'UNKNOWN' to execute extended stored procedure 'xp_proc' I'm not sure what...more >>

Hi I wanted to know that how Transaction work DDL statements Regards Mr.Bug ...more >>

Hi, What I'm trying to accomplish with this proc, is to add a row to a table for each minute starting midnight tomorrow, working my way back to this morning. the @startTime = CAST(FLOOR(CAST(GETDATE()AS FLOAT))AS DATETIME)+1 ------ this is the procedure should pass the correct startTime to...more >>

I'm looking for some advice on the design of the following table. This table stores entries for the percentage complete for a given order per department. Two questions: 1) Is it possible to enforce SUM(PercentComplete) <= 100 using a constraint rather than the trigger? 2) Any better sugge...more >>

I need to assign a default value to a table column. The default value needs to be the value from another column (on the same record) from the same table. In this specific case, the source column is an identity. Is this possible? Thanks Bruce ...more >>

Hi, I want to read data from DB in pages: 1-100 rows, after 101-200, etc. Does the cursor is the best choice? Thanks, Ruslan ...more >>

SELECT 60/ (24 * 60 * 60) returns 0, while in oracle I'm getting the correct value... Should I be using the convert or cast function? -- Alex Ivascu ...more >>

I got this question in an interview: Query for retrieving the names of all employees whose salary is 2nd (or 3rd or 4th...) greatest in the whole company. Test Data: ======= EmpID | Salary ------------------- Emp1 | 1000 Emp2 | 1000 Emp3 | 2000 Emp4 | 2000 Emp5 | 200...more >>

I have the following table: Table1 ------- AccID int AccName varchar Added bit My task is to select all the records from the Table1 except the records that have the same AccID,Name but different Added flag. i.e. Table1 AccID Name Added 1 Mike 0 2 S...more >>

Hi, i'm looking for a method that discovers the relations beetween tables in a SQL database, can anyone help me? Thanks!...more >>

this procedure is working perfectly, but im having a hard time understanding it - im wondering if someone could express this in a clearer way that would be easier to understand (or maybe it is as clear as it can be - i don't know) thanks much jt drop table tTrans create table tTrans(trans...more >>

I have an extranet site and SQL Server running outside my company's firewall, and an intranet site and SQL Server inside the firewall. I have a new application where, whenever a record is inserted in the "customers" table on the extranet server, I need the same record inserted in the "customers" tab...more >>

Hi there, I have a table with unique ids like the one below, I would like to query the db based on this from an asp page. How can I convert the value to a number or something to that effect, so I can use it in a where clause? Sean - Thanks in advance {CB5E6FB0-2353-41F5-891D-5735C1CDA523}...more >>

Hi, The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time. (3197) We are getting this error while inserting/updating SQL Server database using the Jet engine. Jet 3.6, SQL Server 2000. Any ideas? Thanks, ...more >>

Hi, I'd like to set ON settings quoted_identifier & arithabort for my maintenance plan for optimization. I need it because I have indexed views which require them. How can I do that? I'm using SS2000 Enterprise edition SP3 on Win2000 Advanced Server SP4 Thanks in advance Nikola Milic ...more >>

In Enterprise Manager, when you click on stored procedures for a particular database, you have an option to sort by create date (by clicking on the column header). However, as far as I can tell, this just does a character sort and not a date sort (the dates run from 1/1 through 9/30 with Octob...more >>

Hi I have a probleme with the declaration of a stored procedure where i want a parameter of "table" dataType My code --------------------------------------------------- CREATE PROCEDURE searchOp @tbl table id int not null type tinyint not null lib varchar(50) not null code int not...more >>

Is this A GOOD WAY to capture 'ALL' possible types of errors in sub-procedure (it does work when I test it and it also returns what I want). EXEC @RC = DBO.MyProcedure SET @ErrorSave = ISNULL(@RC,0) + ISNULL(@@ERROR, 0) IF @ErrorSave <> 0 BEGIN SET @MyMsg = ...... ...more >>

I have written the following sp but it takes too much time to execute. Sometimes I get timeout when the record goes over a half million. I am sure there might be better way of doing this without cursor. I appreciate your help. Thanks Bidhan here is the complete code /* This Cre...more >>

I have a little teaser that I was presented with yesterday and I wanted to see if any of the brains out here had a different approach than I took. Primarily because I'm not happy with the performance of my approach. I have 2 strings that I need to compare. The first string is called Analyze, ...more >>

Hi, I have a "Closed" system (SCADA)i.e not changable, that is storing to SQL Server 2000. The date format from the SCADA system is dd.mm.yyyy but the SQL server only wants yyyy.mm.dd. How do I change the date format in SQL Server 2000 to recognise the format from the SCADA system. Tnx Totto...more >>

Hello people I'm I right in thinking the xp_cmdshell is executed with the same user as starts the MSSQLServer ? ...more >>

I am trying to write a very advanced SQL Query and I am stuck half way = through it. If you have any suggestions I would greatly appreciate it. = Here is what I am trying to do. I have a table with people's names in it (Ex.. Lname,Fname,Mname). What = I am trying to do is return all records that...more >>

Greetings Workgroup Is there a way of reviewing the activity of users over a certain number of days? I am looking specifically for the number of transactions carried out by users. TIA Tony C....more >>

Hi, I have a problem with this script. It is meant to show the contents of a backup file: DECLARE @TELLER AS INTEGER SELECT @TELLER = 0 WHILE @@ERROR = 0 BEGIN SET @TELLER = @TELLER + 1 RESTORE HEADERONLY FROM DISK='d:\backup\2_reprint_mom_tuesday.trn' WITH FILE=@TELLER END GO ...more >>

Hi, If I pass a string, how to count a character in this string. like: string 'bookscompany' has total 3 'o' in it. Thanks. ...more >>

I have an Access front end to SQL 2000 backend and when I use the following code it returns every record in the ADO recordset, but should return none since there are none that match the WHERE clause. Can anyone help? p.s. all of the records on the Tasks table have a NULL value in the FollowupDa...more >>

I found the code snippet listed below with excellent reviews, but my counterpart had a few concerns. Can anyone address these concerns.... That looks kind of nasty. How would you index the underlying tables for performance? Also, Sql Server can't cache the execution plan on something like...more >>

Hi There My VC++ 6.0 based application is using DB Library calls for communication with SQL Server 2000 After executing query using DB Library API, I am getting following error when I do data fetch Error No. 10015 : Data-conversion resulted in overflo In my query, I am using CONVERT function o...more >>

Hello, I'm experiencing behaviour from SQL Server, using a cursor controlled update, I don't really understand. This is the situation: I'm using a cursor to set the status of the records in a table. The status of the active record depends on the value of the previous record. This is my T...more >>

Any body knows how much data can be stored/Mansged by SQL Server DB/ RDBMS ?...more >>

Can anybody tell me how to remove duplicate rows in the table (sql server )...more >>

1. How to save multiline text into one field name (data type = ?, etc) 2. How to save *.bmp,*.jpg, etc into one field nama (data type=?, ect) thank' Sus...more >>