Hi, I have a DTS that generates a Excel file, all it is OK, but now we want to format the Excel file, let's say to create combo box, format cloumns headers and so on, we had recorded the macro excel, but how Do I call the macro form the the DTS? Please send me some example. Regards, Cesar A...more >>

Dear all, I need to delete duplicates in one table: Desc Product item 1 200 mg 33456 item 1 2 33456 item 1 200 mg box 2 33456 item 1 200 mg box 2 ind 1 33456 The way for to do that is the follo...more >>

Hi There, I am trying to make this query work, I can get it work but when I add the clause & "AND status <> 'Inactive' " _ the query does not work. Have a I missed a comma or something? Moe strSQLQuery = "SELECT model, listprice, manufacturer,location,listeddate, username, status " _ ...more >>

security concerns aside can someone help me with the following Trying to get a stored proc. to move data from one server to another. There is no defined suitable primary key so a column has been added to specify whether or not the data has already been moved over. The following was used by my ...more >>

Hello, Does anyone know of an efficient way to run accumulative totals for records per month? When the previous days amount is 0 or null then current date = last accumulated amount. I am currently CASEing 31 days in a trigger. I would be most grateful for any input! :D Thanks!! ...more >>

Without waiting for the next, greatest, SQL Server - is there any way to invoke [any] URLEncode from within a stored procedure? -- "Anyone who considers arithmetical methods of producing random digits is, of course, in a state of sin." John von Neumann (1951) ...more >>

I would like to compare the values of one record with another record for the previous year, for example to calculate growth. I can extract and calculate these values using the SQL below however I am unable to update the records as I am told the recordset is not updatable in MS Access. Create t...more >>

Currently i do this: SET @varchar_code = 'ADCFA' SET @cntr = LEN(@varchar_code) WHILE @cntr > 0 BEGIN INSERT INTO #tmpCodes (code) SELECT SUBSTRING(@varchar_code, 1, @cntr) SET @cntr = @cntr - 1 END to create a list like: ADCFA ADCF ADC AD A there must be ...more >>

i am wrtiting a procedure to check the server errorlog for messages by loading the log into a #table with xp_readerrorlog. to check that the load doesn't fill the database/disk i want to check the size of the log before loading? does anybody know how to do this? ...more >>

Hi, I have just started a new job and my first task is to test the viability of placing the database, currently 80 gig, and holding 3.5 million customers onto the intranet. The current system that they use to update search etc the details, is via a C++ front end and a typical search for example ...more >>

Hello, I have columns ToyDate, ToyTime, PreviousManufacturer, Toystatus. I need a count of all the PreviousManufacturer and a list of each where Toystatus equals 'yes' and also I need the Toydate and Toytime as well in the result set. Also a way to do the dame thing using the Distinct counts of...more >>

Hello gurus, We have a software package (ERP software) and a SQL Server backend. On occasion, some users will be blocked from doing things in their program and when i run SP_LOCK2 i see another user and the ERP program they are in listed usually many times. So that says to me that this program...more >>

Hello all, I have taken over the management of a web site developed using ASP 3.0 and SQL Server 2000... I have been tasked with gaining a firm handle on our database to understand its structure so that I can demonstrate in-depth knowledge of how it's put together. The end result my boss is ex...more >>

Hi friends I have a table (one column staffid) with data like following staffid ------ AA Ben Bev BW Carri CHRIS i want staffids that r in uppercase only. my query should return AA,BW,CHRIS staffids only -- cheers...more >>

Hi! I'm trying to create a stored procedure called spTest. It should be different depending on the existance of a column in a table. However, the following code gives me the error "Incorrect syntax near the keyword 'procedure'.". What am I doing wrong? if (col_length(N'MyTable', N'MyColu...more >>

I have a SQL Server 2000 databse that is expect to handle about 2.5 Million row deletes & 5.0 Million row insertions everyday into the same table. I am developing a scheduled maintainence plan and have already setup the database to use 'DBCC INDEXDEFRAG' every 6 hours. What I am unsure about...more >>

Can a comment be added to a table or a column in a table ? If so, where in the SQL server information schema can I retrieve table and column comments ? ...more >>

I've got a very simple SQL2K table with columns SSN, ProcedureID, and TotalDone. (will post DDL if requested). I want to sum TotalDone for each ProcedureID within an SSN, which is simple enough to do. However, if the ProcedureID is equal to 5, 6, or 7, then I want to sum those up together to ...more >>

Hi, Is there any alternative for TOP clause apart from Cursors. I mean suppose i want to list down the top three values from a resultset, how do i do without TOP Clause. Thanks ...more >>

I am trying to query Excel data like this: SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\SupportRotation.xls"; Extended properties=Excel 8.0')...[ Sheet1$ ] All I get is the folowing: erver: Msg 7314, Level 16, State 1, Line 1 OLE DB provider 'Microsoft.Jet...more >>

I'd like to combine these two into one statement: set @CurrentTimestamp = current_timestamp -- Update a bunch of rows update MyTable set Field1 = @CurrentTimestamp where *something* -- Get the total for the rows just updated select @Total = sum(Field2) fro...more >>

i have a outer inner join in the cursor defined. when I run the SP, i get this error [Microsoft][ODBC SQL Server Driver][DBNETLIB] ConnectionRead (WrapperRead()). Server: Msg 11, Level 16, State 1, Line 0 General network error. Check your network documentation. Connection Broken Is ther...more >>

In QueryAnalyzer - when viewing the object browser, one can right-click a stored procedure name; then from the pop-up menu, select "Edit" to open the stored procedure for editing. Whenever I do this, the stored procedure, plus some additional text - both before and after the stored procedure defi...more >>

quick question, what type of collation should i use to have my dbase sort my database by numbers first then letters. thanks...more >>

My company has been trying for some time now to find both a Data Miner and a Data Analyst. We are using both Dice and Monster plus using 4 consultants. We keep winding up with people that claim to be pros but cant pass a simple interview. Can someone recommend a sight/ stategy for this dil...more >>

Below, the stored procedure must log a record in the "SampleSource" table indicating that an import batch happened. Then get the @@identity of that record for use as the foreign key to SampleSourceArchive after bulk inserting into SampleSourceArchive (which contains all the imported data) My ...more >>

I've run into a major performance issue with a current SQL Statement and have been unable to resolve it yet. Basically, I have two tables, one called "urllist" and the other called "urltemp". urllist contains full URLs with domain and any paths they may have. urltemp contains only the domain. ...more >>

Hi all: I'm attempting to write a query against two tables which reside on different servers. Do I have the syntax basically correct here? I looked in BOl for it but didn't see anything in the SELECT examples. SELECT * FROM SQL04..db_ABC.tbl_ABC a JOIN SQL04..db_ABC.tbl_DEF b ON a.c...more >>

Hi group, SQL Server 2000 has a REPLACE function; what, if any, is the equivalent function in SQL Server 6.5? Thanks. Richard...more >>

Is there a difference between LEFT JOIN and LEFT OUTER JOIN?...more >>

Does anyone know how to declare use a variable in a SQL Server View? In trying to insert a variable name into the 'WHERE' statement of a query (VIEW), i.e. '@StartDate' and/or '@EndDate' which is to be used as a parameter range, I am getting an error stating you must first DECLARE the v...more >>

I am getting deadlocks, and I'm baffled. One of the programs that is deadlocking is a background job that runs about once a minute. This is the only one that I have the ability to change. The job runs very quickly, less than 100ms, so I thought that I could stop the deadlocking problem by getting...more >>

im trying to do a sum of a value if the date is withing a range my current code is: sum( if DB.dbo.table.date >= @date1 and DB.dbo.table.date <= @date2 DB.dbo.table.qty else 0 ) as wtdship i have tried this several ways. ...more >>

SQLServer 2000 Standard Edition, SP3a, Win2K SP4. I am getting behavior from a view where the results are stale (incorrect) unless there is a small delay before querying the view. I have included two scripts, the first creates objects and populates tables. The second demonstrates the issu...more >>

I was wondering if there is an easy way to run some sql commands against a set of databases defined in another database. We have a database that keeps track of the databases we have and there will be times when we want to run a certain command against all of the databses....more >>

can i set a trigger for an update of one specific feild only ? this is so that i can xp_sendmail if someone changes a date in a date feild. cheers...more >>

hi, I have a recordset like so, DT VEHICLEID NSEW 03/08/2004 08:30:16 169 IGNITION ON 03/08/2004 08:35:33 169 IDLE 03/08/2004 08:40:17 169 IDLE 03/08/2004 08:45:51 169 IDLE 03/08/2004 08:50:23 169 IDLE 03/08/2004 08:55:29 169 IDLE 03/08/2004 09:00:16 169 IGNITION OFF 0...more >>

I've got a table that has the columns below (and others) I'm supplied with a PROJ_ID and IF that proj_id has got a FAS_ID that IS NULL I have to find the first of PARENT_ID that has a FAS_ID that IS NOT NULL ID (int) PROJ_ID(int) FAS_ID(varchar) PARENT_ID(int) 3434 422 NULL 2730 3436 450...more >>

SQL SERVER 2000 SERVICE PACK 3A OR THE LATEST i have this stored proc which does Inserts\ Delete in a lot of table. An Insert trigger on one of these tables causes Inserts to fail. I wanted to intilitally disable this trigger while the stored procs are running but later found out that Sql server do...more >>

Can Anyone please provide the tuning tips for the following code. Thanking in anticipation ======================================================== CREATE PROCEDURE dbo.spFixHtml @uiDocumentId uniqueidentifier /*********************************************************************** ******...more >>

Im running a query to look up a clients licence number from partial data. The table i am querying has 150000 + records and there may be several hundred records retruned matching the search criteris. I know that there will only be 1 licence number for the search criteria, so how can i stop the que...more >>

Hello everyone, I'm having a problem with Joe Celko's nested set model for hierarchies combined with custom sorting. My ultimate goal is to generate a dhtml navigation menu based on a table in a sql server 2000 database. The main columns of this table are: CREATE TABLE [dbo].[Contents] ( ...more >>

Hi there, I've created a view using most of the columns from one table (tblADCStudents), half a dozen Case statments and one column from another table (tblCoursesADC.CourseTitle) using a join: FROM [dbo].[tblADCStudents] INNER JOIN [dbo].[tblCoursesADC] ON [dbo].[tblADCStudents].[CourseCode] =...more >>

I have a trigger which is based on a single column of a table. When that column value is updated, the trigger executes a stored procedure which has to copy data from the updated record to another database within SQL Server. The problem is that the trigger must pass the record's primary k...more >>

Hey, i have a string that comes to the SP as a text data type...i need to parse this.. but none of the string funtions support the text data type...any idea as to how i can parse it.. thnks and rgds, anu ...more >>

Hi to all sql gurus, I have got a table "Table2" having a column as "Instruction" with data type as varchar(512) NULL. In this column i am storing information/error messages. My requirement: Every time an error occurs i want to append that error message to already existing text in this c...more >>