Hello All, Please bear with me if this doesn't make sense, this is all new to me and never really worked with SQL before other than an oracle module at uni (well forgotten by now). I have these three table for example : Table 1 Table 2 Table 3 ---...more >>

I have two tables, A and B. Table A consist of article headers, while table B consist of article Numbers. Each article number`s first 2-5 digits is the header number. After the header number there is 1 to 5 additional digits giving the uniqe article number. I want to be able to do a lookup tow...more >>

Hello all, I am in a big dispute with my boss of indexed views. He told me that they can only be created in SQL 2000 Enterprise Edition. I think any version can create them. We went out and tried to find the answer on Microsoft, but we found strange things. We found places where it vali...more >>

Hi all gurus What is more beneficical Temporary tables or Functions. -- Thank and Regards Amish M Shah ...more >>

Hi When I try to execute distributed stored procedure then I am getting this error message. What may be the reason? 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 c...more >>

I was wondering if there are any performance differences between the "old" style JOIN syntax where the JOIN condition was buried in the WHERE clause and the "new" style where the condition is specified in the ON clause. In my opinion, the new style is easier to read and keeps the WHERE clause f...more >>

I am developing a complex set of reports with an ASP front end and a SQL Server 2000 backend. I don't want the user creating temp tables (I'm not a web expert but that doesn't sound logical) and want to know if there is a way to execute this sequence that works: DELETE FROM seTemp INSERT I...more >>

Is there a way to check for rowguid column in t-sql? I am writing some build scripts and need to alter an existing column... I didn't find anything in BOL or looking around the web... this GUID stuff wasn't in 6.0 and 6.5 when I used to write this scripts a lot more! :) thanks -ed...more >>

I have a table that I transferred to my SQL server. It lost the primary key information, and I would like to make the field a primary key. When I attempt to do it in SQL Enterprise Manager, it gives me an error saying: 'menu_topbar' table - Unable to create index 'PK_menu_topbar'. ODBC er...more >>

SQL people, I have a requirement to select all rows from one table, together with matching rows from a whole lot of inner joined other tables. I did want to put the first table first in the query, but can't seem to get this working: Create Table A ( Aid int, A varchar(10) ) Crea...more >>

How can I select all character to the left of a certain character. I need to select all characters to the left of a "/". LEFT will not work because the amount of characters will be different in each case. Thanks! Darren ...more >>

I am looking for SQL script that will display the SQL Server jobs the beginning and ending times that run on my SQL Server 2000 database within the last two days. Thanks, John ...more >>

I have a stored procedure that on execution updates various tables in a database. This procedure takes some time to run. When called from client applications(asp.net) the procedure causes the application to time out. What I want to know is if there is a way that stored procedure can be called...more >>

Has anyone tried to install Beta2 yet? If so, did anyone get the error "2337..... could not close file" when installing either DTS or Workstation tools? Everything else installed successfully, but useless with the wkst tools of course. Regards Immy ...more >>

Pleas help, Thanks, Ram Kumar ------------PROBLEM---------------------------------------- I have a temporary table #t1 with 15 columns that gets the data from a table for the given criteria and stores. There are 2 UPDATE statements on #t1 First insert will INSERT the data to 6 columns to ...more >>

Hi Is there an equivalent for TRUNCATE TABLE in Oracle and DB2? /Stefan ...more >>

Hi I had created an SP that looked like the following: CREATE PROCEDURE sp_lookupWebInquiriesEmail @EmailAddress VARCHAR(75) AS DECLARE @exist INT SET @exist = (SELECT InqID FROM dbo.tblWebInquiries WHERE EmailAddress = @EmailAddress) IF @exist IS NULL SET @exist = 0 ELSE UPDATE ...more >>

I have a stored procedure that on execution updates various tables in a database. This procedure takes some time to run. When called from client applications(asp.net) the procedure causes the application to time out. What I want to know is if there is a way that stored procedure can be called...more >>

I have a excel file that was pulled out of a database that is across the state. When I imported it into SQL Server, the SSN field became a float datatype. The problem is that usually SSN's are like, 123-45-6789. This value however is like this 12345678900.0. I need to get this in the databas...more >>

I am trying to copy data from a denormalized table into a normalized set of tables and can't seem to figure out how to get the ManufacturerID from the Manufacturers table copied into the Frames table for a given frame record. There are four tables that I'm using WS1.dbo.Parts, WS2.dbo.Compa...more >>

Strange situation: In a stored procedure, I'm trying to run an Update statement while passing the table name in as an input parameter. The only way I found to do this was to create a string containing the SQL I want to run, then execute it: CREATE PROCEDURE dbo.sp_Percents(@tname varchar(25), ...more >>

I have a strange problem... This looks like a bug in SQL, but I don't believe that SQL can be broken. System: SQL 2000 Developer edition, SP3, on Windows 2000 Pro. I think the gist of this can be conveyed without the DDL, because the tables have lots of fields (and one of the source "tables"...more >>

Hi.... I see that we can get same data if we use subquery or joining table(ex: inner/right/left join). my Question is: When to use subquery and when to use "join" ? and the plus minus? Performance? Best Practice? Thanks.... ...more >>

I have never really needed indexes, although now that one of our clients added 35,000 person records along with a ton of new orders, I feel I will need to add indexes soon. A respected developer told me if the database performance is slow, "just add another index". Well, how do I speed up an "...more >>

Hi, CREATE PROCEDURE dbo.proc_SP1 @FirstName VARCHAR (20) = NULL, @lastName VARCHAR (20) = NULL, @iage int = 0 as select fname,lname,age from table1 where faname = @FirstName, lname = @lastName, age>@iage Question: If the @firstname is not passed, @l...more >>

I have the following line of code in my stored proc and it works fine. CREATE TABLE @tblTemp(Hours tinyint NULL, Instance varchar(6) Null) I was told that this is a physical db temp table and I should use a memory temp table. I was given this, DECLARE @tblTemp TABLE ( [ID] [int] ID...more >>

Dear all, Does anyone knows how to write extended SPs in SQL Server 2000? Can I get sample projects for the purpose? Thanks in advance Vijayakumar ...more >>

I need to pull off the characters of a string which follow the LAST instance of a space character in the string. For example, in the string 300 East Mill Way Boulevard I need to capture 'Boulevard'. I'll also need to strip it off in the next query. (If it isn't obvious, I'm breaking down ...more >>

Hi All, I have a table that sets up employee securities. there are check boxes on a form that when checked the value in the column is -1. I need to create a trigger that will not allow anyone to check certain boxes. (Or update the column from 0 to -1) Is this possible? Thanks, Joe...more >>

the following select: select t1.status_id, t2.description, t4.description from tContract as t1 inner join tlkContractStatus as t2 on t1.status_id = t2.status_id left join tContractPendingReasons as t3 on t1.contract_id = t3.contract_id left join tlkContractStatusReason as t4 on t3.status_reas...more >>

I work with an application that have a primary key, this is a key of the business and this is a varchar datatype. Several other tables have this primary key in yours fields. My question is if it's more fast and efficient to make a key numeric aditional in principal table with the other varch...more >>

I'm on a shared SQL server and my host frowns on DTS packages for security reasons. I want to schedule a stored procedure to run on a schedule. They recommend I call the SP from an ASP page. Then I can schedule the script from my control panel. What's the best way to do this? How could I get ...more >>

I have 2 questions. 1) When I DECLARE a varibale DECLARE @Numusers INT SELECT @Numusers = (SELECT COUNT(*) ...) Some code .... At this point I would like to desroy the variable and then later recreate it with another DECLARE line? 2) Is there some syntax that I can use to supress...more >>

when i run my sql script i get this Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'BENESP_AddContractCountNewMonth'. The stored procedure will still be created. no mater how i organize the script i still get a message... is there an...more >>

When I script my database from VB.net it make space line after RETURN in all stored proc. I hate that..... Is there any solution for this problem, or is there any better prog to script a whole database? //Kenneth ...more >>

I have table order with ORDER ID. I have table products with Orderid and productID. Example: order: orderID ---------- 1 .... products: orderid productID -------------------------- 1 2 1 3 ..... I have always 2 produc...more >>

I need to update rows in a table that were added without a uniqueidentifier. In sql books online it says you can do an implicit conversion between char and uniqueidentifier (ive been making sure the char is hex:a- f and 0-9). I cannot get these rows with null uniqueidentifiers updated. T...more >>

I need to retreive [Reserved] field from system procedure [sp_spaceused]. I need to retreive it in my stored procedure and assign it to a variable. Is there a way to do that? ...more >>

For SQL views, is there any way to find out the base table.column that each view column represents? (Of course there may be view columns that don't correspond directly to any table.column, but I need to find out this info for ones that do.) Background: We have our own metadata about tables and...more >>

Is there a way to return multiple values from stored procedure using the RETURN command ...more >>

The easiest way to script defragment all of my indexes in a single table. The indexes are not static were we adding and deleting indexes from the table. DBCC INDEXDEFRAG (0,table_name,?) Thank You, Janet ...more >>

I need to write a stored procedure which changes user password based on validity of his old password. My table with users contains this columns: UserID - int (unique) Username - nvarchar(20) Password - nvarchar(20) I can easy change the password by using: CREATE PROCEDURE dbo.ChangePasswo...more >>

I m atrying to import data from one table to another in differente servers and I got this error. Could not allocate space for object microfilmacion in database Dbsiab because ther PRIMARY FILE GROUP IS FULL ...more >>

Hi All, I need some help with the following requirement: (pseudo code below) Create Trigger on Date_Field Allow only these two (or more) users the ability to UPDATE this field. Any help on this would be appreciated :) Thanks, John. ...more >>

I have a DTS package which must append 30-40 Million rows of data from one table to another.. All I have at the moment is a DTS package with one 'Execute SQL Task' with an INSERT command... The problem is that the package fails because the Transaction Log runs out of space.. Is there ...more >>

All, How can we make a loop to work with every inserted row separately in trigger? We can get the number of rows using @@ROWCOUNT. What's next? WHILE, FOR? What's a correct syntax to retrieve a required ROW in this loop? D. ...more >>

I have an application that needs to search on insurance company claim numbers. Sometimes the number is entered with hyphens or spaces, for example 415-34 76543 and the special chars are not always in the same locations. I want to be able to search on 4153476543 and find a match. Any ideas? T...more >>

I have a reporting server and I would like to set up some tables in the memory every time SQL Server restarts. Can some one point me to the right direction how to set it up. I know DBCC PINTABLE will do the job but I dont know how would I set it up so that when SQL Server is restarted eith...more >>

I have transaction log backups scheduled at hourly interval and every time a transaction lod is backed up, it adds an event to the SQL Error Log. Since I have this schedule set up on 10 different databases, my SQL Error log looks nothing but like this: "Transaction Log is Backed Up". Is th...more >>

How come I can't do this: SELECT iitm_item AS c1 FROM icitem WHERE c1='poass8x1' It returns an error: Invalid column name 'c1'. I actually what C1 to be the return value of a function, and the use the where clause to specify a range of return values for the function. But, the function t...more >>

Hello All, Is there any way to add column to the table in a particular position(after certain column) thru ALTER TABLE commnad or other SQL command I would appreciate the reply Thanks...more >>

Is there an Equivalent function in MS SQL to the "DateSerial" function in VBA. i.e. if the day and month and year values are variables, How can I construct the full date in SQL. Thanks ...more >>

Dear Experts, I am running a SP on SQL 2000 (IBM Xeon dual processor server) . It is taking 2-3 minutes to execute when i am running it from query analyzer . But when i call this sp from VB application its taking 8-9 minutes . I am running same Sp on SQL 7 on same configuration machi...more >>

Hi I have a view that includes two tabels. I have a new field in the view that should get the value 0 if a field in one of the table is NULL if not NULL the value should be 1. Anyone that can help me solve this?? /Anett...more >>

I need to create an Extended Stored Procedure, but know little or no C++. Does anyone know of any good guides and tutorials that will give me a step by step account of how to use Visual Studio 6 to create an Extended Stored Procedure. I'm not too concerned about coding the functionality as ...more >>

For the SQL below: select 1/2 , col 1/col 2 where co1 = 12 col 2= 5 it should return 0.5, 2 respectively. However, my return is 0. How can I change to 1 or 2 decimal places in the SQL? ...more >>

How can I resolve a deadlock from Table with the identitycol. I have SQL Server 2000 Enterprise Edition with SP3A. The system I have is numerous processor computers. Computer1 executes select MAX(IDENTCOL) from A then Computer2 executes delete A from where MAX(IDENTCOL) both Computer...more >>

Does anyone know if query optimizer (SQL Server 2000) care about cost of locking? The query analyzer shows the same execution plan (and cost) no matter what locking hint I use. I assume that there should be some difference between BEGIN TRAN SELECT * FROM Table WITH (NOLOCK) COMMIT and BEG...more >>

I have the following table CREATE TABLE ShipRate { ShipMethodNameID AS VARCHAR(20), OrderLimit AS SMALLMONEY, Freight AS SMALLMONEY } I am trying to design a query that would return the appropriate freight if I pass in an order amount. i.e. If the order was $50, then I want...more >>

For date entry into the following table: CREATE TABLE [dbo].[tblFutureClassesSub] ( [LDUserName] [varchar] (35) AS NOT NULL , [CourseName] [varchar] (75) AS NOT NULL , [BigClassCode] [varchar] (25) AS NOT NULL , [ClassDate] [smalldatetime] NOT NULL , [Attended] [varchar] (35) AS NULL , [Num4C...more >>