Hello, I have a table like this: Key; Text; ViewOrder Values can be like this: 1; Some text; 3 2; Some text; 2 3; Some text; 4 4; Some text; 1 5; Some text; 5 It is only posible to change the vieworder with one. If you want to change the position of records 3 then you can only change...more >>

Hi I have SQL Server 2000 with Service Pack 3 in the Production Server. We need to send mail using stored procedure. We dont have any IIS, SMTP and Mail client software(Like Outlook, Exchange Server) in our Machine (this is our requirement). That means we have to use remote SMTP serve...more >>

I have a Table ARTRAN where is getting populated when a AR debit memo is created. ( Assume Batch 0001) The fields that i am trying to work with are tran date and tran id which are created and captured. Then a credit memo is created which again has a tran date and a tran id. (Assume Batch...more >>

Hi, I have a stored pocedure that creates a report and output looks like this Program Name Number of clients ------------------------------------------------------------------------------------------ Overall 36 Education 23 Pre-employment Services 5 Employment 8 I need it to lo...more >>

I am writing my first trigger in the example listed below ...and I'm getting lost in something...so thx for any assistance -I am trying to update a field called RecordVersion any time there is an update to the table -I realize that the code below is "wrong" ...but I don't see how u can pin...more >>

I am trying to write a xml report generator using a data dictionary and i'm having trouble with modeling the table relations. Would it be best to call sp_pkeys & sp_fkeys for each table listed in the data_dictionary? This would then tell me if it is a 1-to-1 relationship or a 1-to-many. Is t...more >>

I need to select for each date in my history database select 10 stocks with the best criteria(for example highest volumes) and combine them in one recordset. Table contains at lkeast 3 fields: 1.Symbol varchar(8) 2.TradeDate int 3.Volume int I created function best10: create function bes...more >>

I am trying to build a query that will result a list of different rows based on column value, I thought about using the following strategy, but get I get a compile error. DECLARE @FEMALE INT, @MALE INT SET @FEMALE = 0 // even counter SET @MALE = 0 // odd counter SELECT TOP 10 * ...more >>

In chartofaccount form ,u use txtAcctCode, in glchg <-- why chg ?? I change into gldetail , u use acctcode, but in other tables, u use acctno too confusing, I change them all into acctcode ...more >>

I got some table design problem and hope someone can give me advice. I got an InvoiceTable with the primary key Invno, InvoiceChgtable will keep the charges , noofunit, unitpirce.. etc, its primary key is using 'smallint' with increment YES Now, I need to keep each version of the invoice data in...more >>

Hello there. I have a stored procedure that displays rows when executed and the sproc is in the following form ==================== CREATE PROC TestSproc AS SELECT f1, f2, f3 FROM TestTable ==================== I would like to know the way that works as follows SELECT * EXEC TestSproc ...more >>

Hello, Can someone help me with a SQL statement that returns either email from Table A or email2 from Table B based on the following logic: if the email is found in Table B then email2 is retuned/used from Table B otherwise email is retuned/used from table A. I.E Table A ...more >>

I'm creating a procedure to build a list of customers, policies, and balances (based on an 'as of' date), where the balances exceed a user-specified threshold... The calculation of balance is a UDF...mybalanceudf() My question is this: which of the following is faster/more efficient...using ...more >>

A big Thanks in advance - as I'm totally stuck on this one... To show you what I'm trying to do, I'll show you the sort of thing I'm looking for that works on a single table. My problem is that I don't know how to get the same sort of result when joining tables. I need to retrieve subsets of...more >>

Hi, I have an preorder table, create table preorder ( order_id numeric(17) NOT NULL PRIMARY KEY, purchase_date date_time NOT NULL, .....) data will be: 1, 1/23/2005 2, 12/30/2004 3, 12/21/2004 4, 2/12/2005 etc Now I need to count the order for a user in every month so that it can't ...more >>

Hi all, I am having a problem creating a query for the following scenario... I have a table of users (primary key is user_id). I have a table of orders ( with user_id as a field). what would the query look like if I wanted to get a list of all users and their latest order id? Thanks, ...more >>

Need help running a query to find size along with index size for all tables in a database Thanks ...more >>

This could be a stupid question... How do I store a column name in a variable and access it. I mean: declare @test varchar(50) set @test='D5' select @D5 from table What I need to do is to access the column D50 from the table 'table'. The above example does not work. The reason I need t...more >>

it doesnt work with that this error VBScript runtime (0x800A01F9) <!--#include file="conn.asp"--> <% Set cmd=server.CreateObject("ADODB.Command") cmd.CommandType=4 cmd.CommandText = st_myfriends cmd.ActiveConnection=baglantim cmd.Parameters.Append .CreateParameter("@userid", adNumeric,...more >>

Thanks in advance for your help. I have the following function in a MS Access query statement so that instead of seeing the table value, I see the interpretation of that value. I would like to use a pass-through query and "switch" is an Access function. Could you tell me what syntax would acc...more >>

Hi, Are there any info./links to talk about how to look at the graphical execution plan? I could not find any and I am not quite sure what all the graphics mean and how to improve the performance, too... Thanks Ed...more >>

Hi all, I had a customer using a program written in Delphi using DBISAM database. Now I am in need to read and store some of these data to SQL Server tables. I mean, I need to open / read those DBISAM database files. Is there a way to do this ? Thanks for your help. ...more >>

Hello, Can anyone tell me the SQL equivalent of the following Oracle stored procedure? CREATE OR REPLACE Procedure NAFTA_DELETEUSER(iuser_id IN nafta_user.user_id%TYPE) Basically this Oracle stored procedure declare a parameter iuser_id but it gets the length and datatype dynamically fr...more >>

Hi all, I have table1 with the following info: Name Hi Lo A1 10 2 A2 20 1 and the second table Table2 Name HiHi Hi Lo LoLo B1 2 1 0.5 0.1 B2 20 ...more >>

Just wondering if encrypting stored procedures would make production support more difficult. I have an application that currently tells me (via ADO.NET) offending lines of code (line number) in stored procedures that choke - something very useful to know when troubleshooting in a production serve...more >>

I'm trying to use 'IF EXISTS' to test a CASE statement. I want CASE to return a 'Y' if the t_users.userID exists within the SELECT statement or 'N' if userID isn't within the SELECT statement. I just need help with the 'CASE WHEN IF EXISTS' part. QA gives me an syntax error at 'IF EXISTS' ...more >>

And after the transcation is completed, sent a email alert to user.... Thanks ...more >>

Hi, all. Sorry if this is a level -100 question, but: Is there any way to generate a view with the rowset generated by a stored procedure? Something like this might be great: create view myView as exec myStoredProcedure Any ideas? Regards eval...more >>

We have a rule for developing database-driven applications that all interaction with the database must be done through stored procedures i.e. all selects, inserts, updates etc. I am looking for simple ways to enforce & support this design principle - and one would be if I could put the SQL ...more >>

Hi, Any Suggestions on identifying the File System (FAT / NTFS)of the current hard drive on which the SQL Server is Running? Rao...more >>

Hi All? I know that My SQL Server 2000 (Default Installtion) DB is Case insensitive. But How Do I Find that? Is there any Environment Variable? And I have One Doubt... Does the Search oc Character Field Differ if that is RUN on a Case Sensitive DB or on a Case IN Sensitive DB? If Yes what...more >>

Hello, does anyone know if there's a document about patterns and practices when it comes about programming stored procedures? I've tried to find on MSDN site, but I could not find anything. Thanks ...more >>

Databases were migrated via detach/attach from SQL Server 2000/Windows 2000 to SQL Server 2000/Windows 2000 and now scheduled backups and even manual backups say they complete successfully but the backup only runs for like 10 seconds and a backup file is not created. The file pops up in the di...more >>

Hi All, I have one table with Millions of records with a Varchar Column which is Indexed. Suppose I will Search on that Column using the Below SQL, which one will result faster output? a) select col1, col2, col2, col4, col5 from table1 where col2 like 'prab%' b) select col1, col2, col2...more >>

The following code works fine to select, but derails at line 3 (the JOIN) with a syntax error if run as a deleting statement. Can anybody tell me why? I know I'm going to feel dumb when I get the answer.... Select pjinvdet.* -- DELETE From pjinvdet JOIN pjpent ON pjinvdet.project = pj...more >>

Hello, I have vstudio.net 2003 which came with SQL Server Developer. The sleeve that the CD in appears to wrong, as the product key is not working. The text on the back of the sleeve reads: 'Windows Server Enterprise 2003 Media Assy' How do I get the license? Thanks...more >>

I have an application that uses SQLServer 2K at my site but allows for a "suitcase" model using MSDE on the client's laptops. I have a table that is keyed on three fields: STUDENTID, COURSECODE, PREPCODE with additional fields of MarkQ1, MarkQ2, MarkQ3 and MarkQ4. I would like to have a S...more >>

Repost and added with more info. I have a set of database which is replicated from production box. That means all objects are inherited from production box including the data. This is the problem situation. We have a client application that keep receiving data from server application and in...more >>

Hello, Are there any programming tricks to have a faster Sql Server response over a VPN using ADO in VB.NET? Regards, Alpha ...more >>

Hi, I am not sure how the Transaction Log works. I created a scheduled job to make a full back up of the database every night and a transaction log back at the noon everyday. After the backup, should I also shrink the Transaction Log? or I can set up the AutoShrink function? If I ch...more >>

Hi, Are there any difference(in performance) between these two type of join: SELECT Customers.CustomerID,orders.orderid FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID & select c.customerid,o.orderid from customers c,orders o where c.customerid=o.customerid ...more >>

Hi NG DevEnvironment: WinXPpro, SQL2000Dev(SP3)-Mixed mode ProductionEnvironment: Win2Kserv, SQL2000Ent(SP3)-Mixed mode I use the DTS wizard to export data from a table to a fixed lentgh file, and save the DTS. Created with connection User=sa. The DTS works great, and is saved to ...more >>

Hi everybody !!!! How can i return the results from the query in columns results??? Exemple: func | date_time 1 29/10/2004 08:00:00 1 29/10/2004 12:00:00=20 1 29/10/2004 13:00:00=20 1 29/10/2004 18:00:00 to func date_time1 | date_time2 ...more >>

I'm trying to write a form that will insert records into two tables using 2 separate stored procedures. I am trying to do this all within one transaction in Visual Studio 2003 and VB.Net. This is a very common header/detail arangement with header and detail linked by order number. The deta...more >>

Hi Friends, I setup SQL mail profile and I am able to send mail using xp_sendmail Now I have problem with using operators for sending mail. It is complainig about that mail agent is not started. I checked the SQL Mail and it doesn't show green arrow on it. How should I start SQL mail? I ...more >>

Hi all, I am using ADP as a front end to SQL Server2000. I am using ADO to create view1 with a view of 4 columns. Then I drop view1. Then I create view1 again with only 2 columns. If I exist out of the form and look at view1 at this time, it will have header for 4 columns (just like the ori...more >>

Hello, Is there a way to get a "random" sql server generated temp table name that has not been used before? This way the same stored proc could be called from different threads and not conflict? Regards, mekim...more >>

I'm confused about covering indexes. Inside Microsoft SQL Server 2000, Delaney 2001, page 827, says this: An index that contains all the referenced columns is called a 'covering index' and is one of the fastest ways to access data. ... For example, suppose that the Employees table has a c...more >>

Hi, I have to import a data file into my table. My table has 4 columns & the datafile has 3 columns. I am using the following format file with fixed length. 7.0 3 1 SQLCHAR 0 5 "" 1 order_number 2 SQLCHAR 0 7 "" 2 item_number 3 SQLCHAR 0 15 "" 3 type 4 SQLCHAR 0 0 "\r\n" 0 info_...more >>

I have a table with photo information. One column is Date_Taken. How can I select the last 50 rows based on the Date_Taken column? If I use SET ROWCOUNT 50 SELECT * FROM Photos, I get the first 50 rows in the table. -- Walt...more >>