This may be the wrong newsgroup to be asking about this but I'll ask anyway.. I have 200 possible conditions expressed as comparisons eg. A <= 100 And A > 69. They are stored in an SQL database. I also have a datatable with one row containing 10 values (A,B,C,D,E,F,G,H,I,J). How can I test th...more >>

i have a table as follows create table t1 (a int not null, b varchar(5) null,c varchar(5) null, d varchar(5) null) insert into t1 values(1,'b',null,null) insert into t1 values(1,null,'c',null) insert into t1 values(1,null,null,'d') select * from t1 the result will be .. a ...more >>

Hi all, I insert 183 rows to a specific table in SQL Server (say, T035). The method I used was I created a remote view rv_location with (CREATE SQL VIEW rv_location REMOTE CONNECTION "Conn" AS SELECT T035.* FROM dbo.T035 T035), and do the insertion to the remote view. After the insertion, I...more >>

Running SQL2K with all service packs... I have a table (tblACH) containing currently about 900,000 rows. The table has 18 columns, 2 of them are indexed. Each day about 5,000 rows are added and one of the columns has a status of 'PENDING'. Every night the PENDING rows are batched into a ...more >>

Any one else tried the beta yet? Have to say that I'm most impressed with it as a whole and am looking forward to ridding myself of having to use Crystal. Jay --- Yo! outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.554 /...more >>

Hey everyone, I have to call sp_password from within my stored proc. I am using the following code: EXEC @iReturnCode = sp_password null, @cNewPassword, @cLogonID IF @iReturnCode <> 0 Print 'Error executing sp_password for: ' + @cLogonID The OLBs say that sp_password...more >>

I have seen people using this proc. Can anybody tell me what is this used for. exec xp_fixeddrives 2 Thanks in advance. ...more >>

should we do error handling after every DML statement in the procedure. ...more >>

I am using a stored proc with exec (@sString) where I am dynamically building the query to execute. I want to return the results as xml using the for xml auto clause, when I conc my string with 'for xml auto' the procedure complains, any ideas? ...more >>

when we insert a row inside a tran in a table having identiy column, I noticed that in case the tran is rolled back, it does not start the identity value from the same point. It there any way to avoid this. ...more >>

I have a procedure, say procedure A which calls 2 more procedures B and C , I have started a tran in Proc A. do I need to start more tran for procedures B and C. Currently it is like this create proc a as begin begin tran a do some update exec B exec C commit tran end crea...more >>

whenever we ask our DBA that system is very slow, he says that that he will refresh the services and it should be okay. Does anybody has any idea what does that mean? TIA ...more >>

I just have a habit of picking the toughy tasks to conquer. and just before christmas break too! ugh I have two tables to collect user account information, and subsequent login information. For the sake of this posting, the relevent fields are: members: memberID int(4) [unique primary ...more >>

somebody posted a post something like this: am writing a stored procedure that processes records in a cache table and then resets the cache table. I would like to use the truncate table statement to delete the rows in the cache table instead of the delete statement. I am curious to know...more >>

Hi We have an access desktop app with front-end/back-end situation where all tables are in the back end and everything else, forms/queries, are in the front end. We would like to make a web app to use the same access database but are worried about access being able to handle web app users. Is ...more >>

Hi there. Is there a shortcut to get all the properties for a particular database in one queary, using DATABASEPROPERTYEX? Otherwise you have to ask on each and every one. I tought maybe a query that took the properties from a input list, and placed it in a variable, that in turn was a apart of...more >>

I'm going insane so any help would be greatly apreciated. I have one table with columns Item(PK), Seq(PK), Type(PK), Value. Here is some sample data from the table. Item(PK), Seq(PK), Type(PK), Value A 1 Comment This is a co A 2 Comment mment A 1 ...more >>

I am trying to use the statement below in SQL 7 DTS to pad the import of a number to be a specified length. The parsing produces an error on the LEFT command. Any ideas on problems with this code? The code runs fine in query analyzer Any ideas? select {fn CONCAT(CMN_NUM, LEFT('0000000000...more >>

I have a table to hold customers, and another table to hold a record of conversations with customers, as follows: Customers: ID [int] [PK] Name [varchar] (40) Conversations: ID [int] [PK] CustomerID [int] [FK Customers.ID] CallDate [smalldatetime] Conversation [varchar] (100) An exam...more >>

Hi I'm sure this is easy for you guys, but I haven't been able to find the what how to do it, because I'm not very familiar with dynamic sql. I want to insert some data into a temporary table whose name I also create dynamically. I have this: declare @csql varchar(4000) declare @cT...more >>

When I try to save a view I created I get a message "View definition includes no output columns or includes no items in the FROM clause." However, I am selecting columns, albeit from another view. How can I eliminate this error?...more >>

OK, I'm sorry for my confusing and ambiguous question that I posted this morning. I got answers from two nice persons. But none of them was the answer that I wanted. I know the Query Analyzer would display status if I run command in the Query Analyzer. What I want to know is: If I run ...more >>

I have a date field in my database and I want to calculate the difference, in years, between this date and the current date. This difference has to me exact however. Example: I have a date in my database that is 12/31/2003. This difference should stay at 0 yrs until 12/31/2004 when it ...more >>

Hi, Here is the scenario: One instance of SQL server 2000, Two databases (db1 and db2), ASP.NET, third party app plus a whole bunch of stored procs. The third part app insert one row into the database via a aspx page. The stored procedure that inserts the message into db1 also inserts i...more >>

Hi there, I need a simple (?) query but I can't figure it out. This is a simplified explanation: there is a table tblData having 2 columns (F1 and F2), containing the following records: F1, F2 1, 1 1, 2 1, 3 2, 1 3, 1 3, 2 4, 2 .... Now, my resultset should contain the *first* occ...more >>

Hi All, I am sorting data with some condition example ---------------------------------------------------------------------------- ------------- Use Northwind go Select * from Products Order by Case When CategoryID > 5 then UnitPrice else UnitsInStock end -------...more >>

Hi All, I am sorting data with some condition example ---------------------------------------------------------------------------- ------------- Use Northwind go Select * from Products Order by Case When CategoryID > 5 then UnitPrice else UnitsInStock end -------...more >>

I've been having trouble connecting to a 2nd instance of a SQL Server 2k with some VB.NET code from my WinXP Prof workstation. "System.Data.SqlClient.SqlException: Specified SQL server not found: my server" My application connects just fine from a Win2k machine with a like Client Network Ut...more >>

I dropped ten nonclustered indexes on a 9 million row table and it topk one minute. It took eighteeen minutes to drop the primary key constraint on the same table. Why so long? Am in an OLAP environment. Some SQL Server Performance web sites say drop the nonclustered indexes first. ...more >>

Hi all, I am passing the following: oCmd = "SELECT * FROM jourpf WHERE jounam = '" & oRS.fields("jtrnam") & "'" The problem is that JTRNAM can contain a quote (in this case it contains: O'CONNER) How can I code to allow the inclusion of the quote? Thanks in advance. Happy Holidays...more >>

Hi All, I need serial number with the select statement One way is : Select (Select Count(*) from jobs J2 Where J2.Job_id >= J1.Job_id ) as sno , * from jobs J1 order by job_id desc no job_id job_desc ...more >>

This is my VB code. It combines two tables through a loop to repopulate a table that is purged periodically. It builds about 30,000 records and takes about 10 minutes or so to run. I'd like to have an equivelent SQL stored procedure I could call so I can reduce my processing time. Thanks for...more >>

Hi, The environment I have is SQL sever 2000 and IIS 5.0. I have about 100 ASP files in a web application, I like to find a way to trace which ASP is executing what SQL statements against the SQL sever. I've tried SQL profiler, it gave me the SQL statement executed in TextData column,...more >>

If I create a stored procedure that returns multiple tables, can I specify the names of the tables in the resultset? I have created an SP that returns results from two tables: ALTER PROCEDURE GetLedgerComponents AS SELECT * FROM Ledgers WHERE LedgerStatus = 1 SELECT * FROM LedgerAc...more >>

Hi I have this SP: CREATE PROCEDURE sp_DataInSlidev2 AS create table #TempFinal ( ID integer ) declare @csql varchar(4000) set @csql='select FK_ID, Name, PropValue from prescustprop, #TempFinal where #TempFinal.ID = PresCustProp.FK_ID ORDER BY FK_ID' set @csql = 'DECLARE crResAux...more >>

I have been trying to get SQLagent Mail working for some time now and have yet to succeeded. So- I want to start from the beginning. Does it matter how sqlagent starts up? Can it start up under local or system or does it have to be one or the other? I had it working a while back and it...more >>

Hi everyone, I have a table that looks like this: TITLE DOCUMENT TYPE abc paper Engineering def project Engineering ghi procedure Manual dyi kit Manual tty document Proposal I want to get a report that will count up these documents, such output is like(corre...more >>

I've got a table set up as such: CREATE TABLE [dbo].[tblMESSAGES] ( [OtherTable_ID] [numeric](18, 0) NOT NULL , [MessageNumber] [int] NOT NULL , [Message] [char] (2048) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) For this table, both OtherTable_ID and MessageNumber are set up as the...more >>

I am writing a stored procedure that processes records in a cache table and then resets the cache table. I would like to use the truncate table statement to delete the rows in the cache table instead of the delete statement. Reason being, the truncate table statement will reset my identity...more >>

I know I've done this before, but can't seem to find the option now. I'd like to add a table into my database that exists in another database. Not copy the table over, I want to just have a reference to that table in my database. ...more >>

I have a stored procedure that uses xp_sendmail to send an e-mail containing query results. The resulting e-mail used to display each query row on one line. However, the past couple of e-mails display each row on two lines instead of one. I have not changed this stored procedure at all. ...more >>

I have an application that has a table that contains multiple small image files. This table now needs to have it's images archived, but I wish to have the rest of the record remain. I have set the image field data to 'null', and then proceeded to shrink the database. No additional free space was ...more >>

Hello, I am trying to put a CASE statement in my WHERE clause depending on the value of @mailed. When @mailed = 1 then I want to make FolUp_Date IS NULL but when @mailed <> 1 then I don't want this condition. I have tried the following but it doesn't work: WHERE Fup.FolUp_ID = 1 AND Fup.F...more >>

I'm trying to make a View based on a View, and am getting the error message when I try to save, "View definition includes no output columns or includes no items in the FROM clause." How can I correct this....more >>

Hi, I need to know the status of certain process. For example, when I restore database from backup file by using the Enterprise Manager, I can see the progress as the form of progress bar. And if I restore database from backup file by using the Query Analyzer, I can see the progress in the ...more >>

I want to call a procedure and pass an argument that will be used in SELECT * from table where col1 in (@arg1) Anyone have any idea how to do this? There can be multiple entries for the argument. i.e. 'A1','B1','C4' ... For example: ------------- Create procedure test_proc @arg1 V...more >>

In my DTS package, my source server's SELECT statement needs to include tables from the destination server. I have been trying to use OPENROWSET to accomplish this. This is what the query looks like so far: SELECT * FROM SourceServer ss INNER JOIN OPENROWSET ('SQLOLEDB', 'Serv...more >>

I have a file containing the following fields: Level Number Line Number Parent Line Number Product Code This file allows me to structure data as follows: Level 0 Line 1 Parent Line 0 PROD1 Level 1 Line 2 Parent Line 1 COMP1 Level 1 Line 3 Parent Line 1 COMP2 Level 2 L...more >>

Iam importing data into a table from another sql server using openquery. Since this is a temporary data i don't want to generate log for the inserted records. I am in multiuser environment while this takes place. how can i accomplish this.? thanks...more >>

Hello, What should I use if I don't want to use NOT IN my query? I also don't want to use NOT EXISTS. i.e. Order table and product table. I want to see all the product that is not in order table or other way any product that doesn't have single sale. Thanks! ...more >>

while bcp out i can able to pass max of 1024 lenght for a select query. i have a select query which is more than 2000 length .. how to pass this to querout ...more >>

Hello, I have written an Insert trigger on a table. The trigger works fine when I insert one row. But when I perform an insert like this: INSERT INTO tableA(col1, col2) SELECT col1, col2 FROM tableB WHERE id in(1,2,3,4) Then the trigger fires only for the first row. It does not fi...more >>

I use MsAccess linking Sql Server tables via ODBC. With the following code ============== Dim MiBd As Database Dim MiWs As Workspace Dim MiQr As QueryDef Set MiWs = DBEngine(0) Set MiBd = MiWs(0) Set MiQr = MiBd.QueryDefs("jp Contador") MiWs.BeginTrans MiQr.Execute dbSeeChanges MiQr...more >>

Hi All, Is it possible to generate an alert before the Transaction Log gets full. For example: My hard disk space is 40 GB My data file is 2 GB (Unrestriced Growth) My Transaction Log file is 1 GB (Unrestriced Growth) Is it possible to create an alert which fires when the Transact...more >>