Hi, Actually i have one emp table with Columns [id, name, salary, DOB, mgrid]. I want to display employee data in tree structure, in relation with their supervisors [Managers] (like explaned below) from emp table. This tree can go to any range. Can any body help to write the t-sql q...more >>

This is more a design question then anything else but I have yet to come = up with a good solution so I am posting here. Currently, I am rearchitecting the database schema for the security for = both our Intranet and Internet. I am fairly comfortable with the = features that will be supported...more >>

Hi, I have a problem that's been driving me nuts for months. We have some stored procedures that link to an Oracle database to import some data, then process that data in SQL Server. A couple of them will drop out of the cache and recompile (or try to). The procedure then abends with the...more >>

In the MSSQL2000 Transact-SQL Reference the syntax for a search condition (used in a WHERE clause) is defined as: < search_condition > ::= { [ NOT ] < predicate > | ( < search_condition > ) } [ { AND | OR } [ NOT ] { < predicate > | ( < search_condition > ) } ] } [ ,......more >>

I have a server SQL accessed for about 50 users and ace times, when beginning the application I receive the following mistake: [DBNMPNTW] SPECIFIED SQL SERVER NOT FOUND. The interesting is that the problem is occasional, in other words, machines that in the previous day they usually worked, sudd...more >>

Hi All, I Have 2 Large TABLES and A View On which retrives records from Both the Tables Like: CREATE VIEW REAPPEALS_RECORDS AS SELECT A.Col1, A.Col2, B.Col1, B.Col2 FROM Tabl1 A INNER JOIN Tabl2 B ON A.ID = B.ID WHERE B.Col5 = 'FLCC' AND B.Col6 < GETDATE() AND B.Col7 = 1 AND (...more >>

Hi I have tables that have spase bar signs in their names. But SELECT doesn't want to get it. And it doesn't want to work with Table name included in ' sign. Is any method to get data from such table by SELECT statement. Thank you....more >>

Greetings, I would like to know what is the requirements in setting the Identity_Insert On to a table. I already make the Identity Column in a table. IDENTITY(1,1) SET IDENTIY_INSERT dbo.Table ON and for some reason it is still off and produces error like: Cannot insert explicit v...more >>

I am a bit confused as whether it is really necessary to declare indexes. I know with indexes Data retrival is fast, but it also makes data updation slow. Thus it serves only half my purpose. I was reading the MSSQL Server Black Book by Patrick Dalton. It says depending on the situation one must ...more >>

Hi, Does anyone have any ideas about comparing demographic information = contained in two tables? I've got two tables full of names and = addresses, and I'm trying to determine if any of the data matches. = These tables are really populated from two different sources, and aside = from what a h...more >>

I have the need to change an existing datetime stamp field, into two fields. one with just the date, and the other with the time. Is there an easy way to do this...more >>

Hi I was wondering if there is a way to get the schema of any selected columns from a SP? I have used sp_columns with a table and view and it works great. What I am trying to accomplish is to get the character length for any varchar fields and set the MAXLENGTH property of the text box. ...more >>

Hi all, I'm not sure if this is the most appropriate group to post SQL questions to--please point me in the right direction if it's not. My question is this: Is it possible to query for specific bits in an integer field? What I mean is, I have a few values--DWORDs--at my disposal, and ne...more >>

Ive always thought it wasnt kosher to post jobs on these boards. However, I have no clue where I got that idea. Is the general feeling that its OK or not?...more >>

Hi, I'm developing an extended stored procedure using the native C++. The mentioned xp is supposed to create a dataset and archive the related data. You know, to develop an xp, you've to export a function in your DLL file, a function that receive a pointer of SRV_PROC as its formal parameter. ...more >>

Hi, I'm wondering what code assigns a user to a database using 'Windows Integrated' authentication. I have an ASP.NET application that says access is denied to user Network Service (IIS6.0). Thank you in advance, Tim. ...more >>

Im trying to append a bunch of text columns values in a table to one text variable. Ive created some test data for this: ---------------- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TextDataTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TextD...more >>

How can I get the updates in my stored procedure to run consecutively? It's doing updates to records I don't want updated because the first update hasn't been completed yet??? I"m not sure if this is what it's doing, but it's the only explanation I have. Thanks. ...more >>

I have below a very inefficient SP. The bottom half where it's doing = DATEDIFF stuff is what's really hosing performance.anybody have a better = approach? Right now it takes 2 minutes to return sometimes only 1 row! =20 SELECT DISTINCT Tran_state_id, tran_state_actv_co...more >>

What is this: (scroll down for answer) myStoredProc GETDATE() Answer: Passing the time. Get it? Your passing the time into the stored procedure...Sorry. I'm more like wasting the time. DELETE WorkWHERE time=GETDATE(); ...more >>

Hi, I developed a stored procedure in SQL2000 to be used a source for a report. I have three tables: Months, which has month_number, month_name and mnth (Jan, Feb, Mar...three-letter abbr. for each month); Labor, which has month_number, hours, empid, billcode; and Employee, which has empid...more >>

Is there a way to grant permissions to all tables in a database to a user you are just adding with one command. Something like: Grant select on all? I assume not. I know I could just add them to Public or another group, I was just wondering if there was a way to not create another group. ...more >>

Is there anything I can put in a trigger to signal an event in my program (C# as it happens)? To put it another way, how can I make a bit of my C# code to run when a row is inserted or updated? Thanks. ...more >>

Hello, I pick up a recordset with a select statement and iterate through the recordset using a While loop. I do stuff in the While loop. Here is how I iterate through this recordset, but is there a better more reliable way without using a cursor? here is tbl1 rownum fldName 1 ...more >>

Hi All MS SQL Server 2k Dev ed IIS 5, ASP classic. I'm still learning SQL and was wondering what will happen in the following scenario. I have a HTML page with a link on it that goes to "displayaccounts.asp" Now the above ASP page calls a Stored procedure before displaying some infor...more >>

Hi all, I've got an application that's a pig because it's calculating values constantly... so I want to speed it up by periodically calculating the information and storing it in a table. I'll probably use a view WITH CUBE and then save the results of that, and then query this new table. Wh...more >>

is it possible to do an update statement using an inner join? have seen some syntax on the internet that looks like UPDATE tblTest2 INNER JOIN tblTest1 ON tblTest2.PersonID = tblTest1.PersonID SET tblTest2.LastName = [tblTest1].[LastName] WHERE (((tblTest2.LastName)<>[tblTest1].[LastName...more >>

I need to create a SQL Server Stored Proc that will handle a variable number of Or conditions. This is currently being done with a MS Access Query as follows Do Until rst.EOF myw = myw = "(rst!Field1 <> 0) OR (rst!Field1 <> 1) " Loop mysql = "UPDATE Table SET Field2 = 1 WHERE " & myw ...more >>

Greetings, I'd like to write a SQL query that joins 2 tables (BOOKS and VERSIONS) to produce the following report: ID TITLE VERSION DATE VERSION DATE -- ----- ------- -------- ------- -------- 1 ABC 1 09/09/99 2 09/09/01 2 DEF 1 01/01/01 ...more >>

We need to update names of objects, like indexes, primary keys, foreign keys, and defaults to follow naming standards. We'd like to used 'sp_rename' instead of drop/create since it's fast. It will be done in batch on the weekend when users are not allowed on the system. Any concerns? And any way...more >>

Hello. I will use the "Select Max(fieldName) From ...", and add 1 to it. I want to ensure that some other user's job grabs this number. Lock the table? All suggestions are welcome, except for ones that discuss using the "IDENTITY" field property. Thanks in advance, Mike ...more >>

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(Stock.Authors,"'",''),',',''),'.','' ),' ',''),"the",'') The above statement works, but I'm a bit confused on when to use double or single quotes. If I want to replace a single quote, I have problems with ''', but "'" works. Is it OK to switch back and...more >>

HI, Can I have the identity insert start from 0001 & increment it by 1. Example 0001 0002 ' ' ' Currently my identity column 'Cust_wk' starts from 1. Example 1 2 ' ' I am having a problem that I am building script for data mart but I don't get any results Using example ...more >>

I have been doing t-sql queries, updates, inserts, etc. for some time but not often into stored procedures. Most of the stored procedures I see use cursors to cycle through the recordset, and this seems to be a very slow process. Is there a reason why I can't simply enter my queries into a sto...more >>

Hi, What's T-SQL's Equivalent of VBScript's ChrW function? Thanks, BL...more >>

Hi: I'm still very new to SQL and don't yet have the experience to resolve a problem I have. I simply wish to implant an auto-line numbering feature for the details part of master/detail invoicing schema. For every new invoice line, I want the database to increment the line number, start...more >>

I'm new to SQL programming. Are there column name wildcards in SQL? If I have a table with columns labelled N1, N2, N3,...,N50 (plus other fields) is there an easy way to Select, or Sum etc without typing out each of the column names? SAS for example allows you define and do operations on arrays....more >>

Hi Actually i am Creating a dynamic query and executing using EXEC. After creating dynamic query , i am printing the query. If i run this query in query analyzer i am getting the results within 4 seconds. Same thing if i run the dynamic query using EXEC, It is almost taking 6 mins. Can any...more >>

Hi, I develop an application with ASP.NET/C#/MS SQL. Well, I have a form for searching and the SQL STATEMENT IS: SELECT U.REALNAME,E.ESTATEID,E.ADDATE,ET.TYPEEST,TC.TypeConstr,O.PRICE,O.SQMETERS,O ..FLOORS,O.ROOMS,E.ESTPHONE,E.ESTFURN,E.ESTHEATING,E.ESTDISTR,E.ESTCITY,D.DES CR FROM BLEK.USE...more >>

i am using access DB for this in a table.. i have the following columns in table scores winner, loser, points_win, points_loss now let me see if i can explain this... i want to display all the "username" from table (players) and the totals for the points_win and points_loss from scores they...more >>

Hi, I exported our national phonebook into a SQL Server database. Since my callerid-software produces a concatenated areacode-subscribernumber (impossible to split into areacode and subscribernumber) I need to lookup the entire callerid. Is there a nifty way of storing a concatenated string f...more >>

Hi I'm adding extra fields to a table, is there anyway in which I can specify where the 'New' Field is positioned? -- Kind Regards Rikesh (SQL2K-SP3/W2K-SP4) ...more >>

Is there any way to restrict applications connecting to a specific SQL Server 2000 database ? What I want to achieve is to restrict all insert, update and delete access to a database exept when my application runs. I cannot afford to do this though any user restrictions, since I am using Dom...more >>

I have a result set of toy manufacturers Toy Manufacturer ToyCount _______________________________ Hasbro Inc 2 Hasbro 4 Hasbro Company 20 Hasbrow 23 Milton B ...more >>

What is the easiest way to drop all the user defined indexes on a database....more >>

G'day all, Each time I try to stop a trace on SQL Profiler, the profiler hangs (not responding), and I have no choice but to end its process through task manager. I'm running with the latest SP - SP3a. Any ideas?...more >>

I am getting the error "Sql server does not exist or access denied" when I connect an asp application and sql server through an oledb connection. The connection string is as follows: Set cnnStoredProc = Server.CreateObject ("ADODB.Connection") cnnStoredProc.Open "Provider=SQLOLEDB;Initi...more >>

I'm a newbie here, so if this is an obvious problem with my code, please be kind! :) Firstly, I am using SQL Server 2000 on a Windows 2000 Advanced Server... For some reason the following code works perfectly when run through Query Analyzer, yet when I fire it off from a Trigger, the CURS...more >>

Hello, I have a number of orderitems that belong to an order. I associate a linenumber for each orderitem at the time of order. I have also given users the ability to move items positions around via an app. The problem occurs because the app sometimes does not mark the items with the appr...more >>

Hi all, Just learning about triggers and have a quick question. An insert statement holds an exclusive lock whilst the new row is written. What happens to the lock when an AFTER INSERT trigger is involved ?? Does the trigger hold the exclusive lock until it has terminated and the result ...more >>

Hello Experts, Can I use The @@NESTLEVEL to find the stack of a trigger? I test it and i seems to work fine. Thanks in advance...more >>

I read somewhere that sql server 2000 doesnt support the first function when grouping data. How then can I select the first row? Dan...more >>

Code if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop ...more >>

Hi, Can someone help me with this. I am trying to build scripts to develop my data mart. I am stuck at a point Using example here -->Customer_Dim_Table Cust_wk Cust_Name Cust_Add (etc) 1 2 3 ' ' ' 100 101 102 -->Sales_Stage_Table Cust_info_id Sales_Units Quantit...more >>