If I run a stored procedure in Query Analyser that returns 430,000 rows it takes 5 seconds. The query has completely finished in this time and I can immediately scroll up and down the grid viewing my data. How is this so fast compared to doing this in VB 6? In VB 6 I open up the stored proce...more >>

I have a row that I want to duplicate and change one field in the process Example DATE Shift Sample C1 C2 C3 C 4/1/04 1 A 0 0 0 I need a statement to copy the above to a new row with the sample being 'B' so I will end up with DATE Shift Sample C1 C2 C3 ...more >>

Is there any standard way to retrieve (in a stored procedure) the number of rows affected by the last statement in the current scope? I've been doing concurrency error checking by retrieving a timestamp when I SELECT, and using that value in the where clause when I DELETE or UPDATE. If the d...more >>

Hello Experts! How can I specify a text qualifier on Bulk Insert? When I Bulk Insert texts like "NY", the quotes are imported as well. Can I define this on my format file? In DTS, this will be the Text Qualifier drop down. Thank you very much for your assistance. Take Care, Michae...more >>

Hi, Sorry for the lengthy title. I have a 2 tables. Table 1: Product ====== id_product product_nm product_desc price Table2: Orders ===== id_order id_product price dt_order I want to add 3 fields to the products table. the first is a order_minimum field. This will give me the...more >>

I have a number of XML configuration files that are kept in text columns inside a table. I would like to use these XML files from within stored procedures, but I have some that are greater than 8000 characters, ruling out the use of a varchar. How can I programmatically operate on the contents, ...more >>

hi, i have 2 tables. Products table (fields) id_product, name manufacturer price second table: Orders id_order id_product date I want to query all the products in the products table and see a count of how many of these products i've sold. so it would be all records from the product...more >>

Hello, I have a question on how to map a Order and Order Details relationship. In the Orders table I have an OrderID key and some other customer info (Name, Ship Date...). The Order Details uses OrderID as the key because there is one to many relationship with Order. The problem I am runnin...more >>

So I have this column in the database: externalaccesscount int Its nullable. Here are some steps: 1) I do an insert into the table and I dont set this column. So this column is NULL 2) I have this bit of logic running in my application that runs next: daltable bndTable = new dalTab...more >>

Hi! I need to write a SQL statement to give me the last date of each month. please see below: create table #t( Mydate datetime) insert #t values ('12/31/03') insert #t values ('12/16/03') insert #t values ('11/30/03') insert #t values ('10/12/03') insert #t values ('9/30/03') The qu...more >>

I am using ODBC to connect to an SQL Server database. When I execute a certain set of queries, the memory usage of the SQL Server process increases each time. The memory usage will increase until it starts causing other memory to be swapped out. I have read some knowledge base information ...more >>

can you execute a stored procedure inside a cursor that retrieves the recordset for the cursor to loop through, or must there be a select statement to do this??? tia jt ...more >>

i have hard time to write a simple function to do following, when user input is mm/yy or mm/yyyy or mm/dd/yy or mm/dd/yyyy or yyyymm , pass into function and return the data with datetime format. if user didn't specify the day, then day should be first day of month (in datetime format). i...more >>

If ethier one of the two parameter field are null I dont get the correct data back, When there is a null value in either field. Help Sp Below proc usp_TimeSlipSeacrh @OtherCaseName varchar(100) = null, @Casenum varchar(100) = null /******************************************************...more >>

Hello My Sqlserver2000 runs perfectly and my windows app is running against it without problems. But when I go to EnterPriseManager I cant connect to the server, it times out every time i try to register it. Any suggestions ? \Anders ...more >>

Hello I have a sql update statement thats quite large with several "select nests". It generally takes an hour to run when I need to effect a lot of records. In VB6 using ADO, I issue the statement and sometimes the update doesn't actually preform the write (I think when I get over a certain ...more >>

Hi, I want to automate the following process. Query out of the database into an XML file which will be saved on a web server. the web server will display the data on the web. The data doesn't change that often so it's silly to make trips to the DB that are unnecessary. please advise. rafae...more >>

Another newbie question. I wish to use a regular expression (a-zA-Z0-9\-) as a search criteria. Do you have a SELECT and or UPDATE example? ...more >>

I am new to this, I have one table and I wish to remove records in it based upon NOT having matches in another table. The SELECT, for example would be like: SELECT parts.*, deliveries.partnum FROM parts LEFT JOIN deliveries ON parts.partnum = deliveries.partnum WHERE (deliveries.partnum ...more >>

I've strugged to get an update query to work. I'd appreciate some guidance. I've trying to determine the number of consumables purchased within 120 days before a product. Let's say the product is a printer and the consumable is an ink cartridge. Two tables: Table1 = PrinterSales Colu...more >>

Hi All I am creating jobs on the fly.But depending on the outcome of the Job i need to either delete the job,recreate and rerun it again. I cannot find a direct way to do so. If there is a system Proc that returns the outcome of the job , success or failure. If any one can help with the s...more >>

I have a proc called ParentProc that recompiles way to many times each hour and is affecting overall MSSQL 2000 performance. I have narrowed the cause of the recompile down to this statemen INSERT INTO #myTable (col001,col002,col003,col004 EXEC databaseA.dbo.myPro @var1 @var I thought about...more >>

Hi When using the command: SET SHOWPLAN_TEXT ON, the execution plan is returned as part of a column called Stmt Text. The data in the column is too large, and I cant read it. When copying to excel its still too large to read How can I get round this problem?? Thank JD...more >>

Hi, I'm using SQL server 2000 productversion 8.00.760, service pack 3, Standard Edition with an jscript/asp application level and am having some problems with table locking. In a nutshell, what I need to do is lock a row or rows (or page) in a select statement such that when it is locked,...more >>

Can I use an IF ELSE statement in an UPDATE? I've tried without success and haven't located any similar examples on the web. Here's what I'm trying to do in English. In Table1, if FieldA = FieldB set FieldC = FieldA Else if FieldA = 'ValueA' set Field C = 'Upgrade' ...more >>

for more experienced SQL programmers, this may seem dumb, so bear with me i need an auditing solution that will be able to track changes so that the database can be returned to a previous state. what are my best options with respect to performance?...more >>

I'm rewriting SQL statements used in Access in order to use them with SQL Server. This SQL works with Access, but says 'incorrect syntax near RIGHT keyword': UPDATE [Partial] RIGHT JOIN BillPayment ON [Partial].PartialID = BillPayment.PartialID SET [Partial].MfrPaidAmount = [Partial].MfrPaidA...more >>

Here is our problem. We are building a sp to get a list of rows that are not related to the parent table. This is like a list of checkboxes showing what is currently associated and what is not associated. The problem is that on top of this we hold history in the relationship and fact table. Hist...more >>

Hi, What is the best way to backup a 1 TB database without any downtime? I use Veritas for the backup software. Thanks again for your help. ...more >>

Hi, I have 2 stored procedure like that SP1 (simplified) create proc proc1 @param1 int a declare @arg1 in select @arg1= max (XXX1) from YYYY1 where YYYY1.param1=@param select Z1.* from Z1 where arg1=@arg SP2 (simplified): create proc proc2 @param2 int a declare @arg2 in select @...more >>

I have two stored procedures, one which inserts a record and another that gets the next record number available. I'll be calling this from an external application that uses a very UNpowerful language, so I'd like to port as much work off to the database as possible. So, is there a way to call th...more >>

Does anyone know what Microsoft's documented date through which they will support SQL Server 2000?\ Thanks, CT...more >>

Does my memory serve me correctly - in older versions of SQL Server, were we limited to 1 trigger per table?...more >>

I need help creating a query to return the following: I only want to return the ids that have both local and remote. Using the data below the only ids to be returned are 1 and 3. ID Location ---- ----------- 1 remote 1 remote 1 ...more >>

Hi, I have a SQL statement which extracts the quarter from a date i.e. in number form e.g. 1,2,3, or 4). I am doing this using datepart(q,<date>). What I want to do is extract not only extract the Quarter # but get the results like this: Quarter 1, Quarter 2, Quarter 3, Quarter 4. I...more >>

I am trying to log some Performance Monitor Counters for the SQLServer performance object. I can view these counters in "live" mode. If I log them and view them after, the SQL Server counters are not available. The other performance counters exist (% processor time). Is there something that I ...more >>

With earlier versions of MDAC I used to be able to specify "(local)" for a servername, but this doesn't work on Mdac 2.8. I'm looking to have the same connection string on multiple servers, so I would like to identify the local machine somehow. Anybody know how?...more >>

Hi gurus, this may be a little dump question. why this wouldn't run, is there another way besides dynamic sql?: declare @Tbl as nvarchar(50) declare @Fields as nvarchar(100) Set @Fields = 'id, name' Set @Tbl = 'sysobjects' SELECT TBLS.TABLE_NAME, CLMNS.COLUMN_NAME, CLMNS.ORDINA...more >>

Hi there, I am trying to run a query with expression but its not displaying any output for those column with expression. but its running in MS Access database not in sql database. Could i have any information please. sql script is: ALTER FUNCTION dbo.BonusQueryShare (@Invoice_Date_From_...more >>

Not sure if I should post in ASP or SQL newsgroup, since this issue touches both... I'm creating a web site to serve exams to users. Just a typical exam with some set of questions,choices, and answers. The issue: How best to separate the HTML formatted question from the text of the questi...more >>

We are running a system using a middle-tier running in COM+ (MTS) against SQL Server 2000 databases. We have two databases for active information and historical information and also a third database for audit entries. All three databases and the middle tier are running on the same clustered se...more >>

im running SQL 200 I have two tables to deal with operator passwords. table 1 is the current password and table 2 is a list of used passwords im trying to run a sp / sql that when the user changes his password i 1. uses Sp_password to change his SQL Server Logon passwor 2. updates table 1 wi...more >>

Hi Gurus, In the below query, I'm trying to view some specific columns for a specific table. I think it can be written with better T-SQL, any suggestions? SELECT CLMNS.TABLE_NAME, CLMNS.COLUMN_NAME, CLMNS.ORDINAL_POSITION FROM INFORMATION_SCHEMA.COLUMNS CLMNS INNER JOIN INFORMATION_SCH...more >>

Hi Tried assigning a local variable to a text data type for select statement. It kept returning NULL value. Any idea what went wrong Thanks !...more >>

Dear All, I am working on the following trigger since 3 hours and recieving the message Line 6: Incorrect syntax near ','. can any one tell me where is the wrong logic behind the trigger Regards, CREATE TRIGGER [UPDATEPERIODS] ON [dbo].[Pol02Loss1] AFTER INSERT, UPDATE AS UPDATE [P...more >>

Hello, Using Win 2003 and SQL 2000 sp 3a I am trying to use xp_cmdshell to copy a backup from Server A to Server B. Both Server A and Server B have the same Admin domain user and password. The sql server agent userid on Server A has full rights to the directory on Server B. I have logg...more >>

I should call an RPC from a server to another. I sholud use a linked server or i should prefer OpenRowSet method? I sholud call the procedure many times in a day Thanks...more >>

I have a database on a remote server that i can access and execute stored procedures on via adoDB & the IBM.uniOLEDB provider dim cn,rs1,rs2 set cn = createobject("adodb.connection" ) cn.open "Provider=IBM.UniOLEDB;Data source=UvTest;User ID=usr;Password=pwd" set rs1 = cn.execute...more >>