Hi Anyone know how to perform a create table like it is done in oracle Create table xyz as (select * from A,B where a.id=b.id) thanks in adv....more >>

Hi, I can't seem to get this to work. Its a stored procedure using XML EXPLICIT. I'd really appreciate any help. Below is the stored procedure and below that is the xml syntax I'm trying to achieve. Thank you very much in advance, Tim (newbie). CREATE PROCEDURE dbo.FindUser (...more >>

I was stuck trying to get a stored proceedure (update) to work using VB.NET to call it. I didn't see anything wrong with my code. The update stored proceedure generated by the VB wizard looked massive, but I didn't really see anything wrong with it. I ordered a book on using SQL Server stored proce...more >>

I'm new to SQL and need some help. I have to compare data from two tables and insert the changed records. The one is an existing table and the second a view that I created from the second table (Temporary table). The problem is that it keeps adding the new data whether it differs or not from the ...more >>

Hi: In VB6 one could declare a SQLDMO.Application variable and through its ListAvailableSQLServers property obtain the list of accesible SQL servers. Does anybody in this forum know how to obtain the same information under VB.NET? Thanks in advance, Saeed ...more >>

Server: Msg 8623, Level 16, State 1, Line 2 Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information. I am getting this error on a normal SQL statement that works without the insert into of the /*DATE*/ stat...more >>

Hi all, I have written a very simple ASP.NET page on my website that displays the results of wedding fayres based on the Venue Name selected by the user. However, when the ASP page loads for the 1st time, this takes quite a long while to appear (approx 15-20secs). I understand that the ASP ...more >>

I have a two tables I'm working with. One contains a full URL with paths and other contains only the domain name with http://. Example: Table 1 Data: http://www.mysite.com/mypage.htm http://www.myothersite.com/myotherpage.htm Table 2: Data: http://www.mysite.com/ What I'm trying to do...more >>

Suppose I have a stored procedure that does the following (and only the following): 1. Insert a new row into TableA 2 Return SCOPE_IDENTITY() to calling application ... and there is FOR INSERT trigger on TableA that inserts a new row into TableB Is the new row inerted into TableB *prior to...more >>

What is the function to convert a string to first letter upper of each word and the rest lowercase. Can I store data in a table this way no matter how the user enters it? Thanks! Darren ...more >>

Say I have a table like so: create table tableA ( columnA datetime, columnB varchar(20), columnC int ) Say I have a query like so: select max(columnA) as MaxOfA, columnB, columnC from tableA group by columnB, columnC For every value in columnC there is more than one value in co...more >>

I have the following table structure and am curious if someone can help me perform an operation without using a cursor. Create Table Changes ( to_id int, to_ordnum numeric(4), to_trak2id int ) INSERT INTO Changes (to_id, to_ordnum, to_trak2id) VALUES ('1', '1111', '39') INSERT INTO Chang...more >>

How do I use replace in query analyzer? I want to replace text xyz with abc in a particular column, sql books only says do Replace('abcde', 'cde', xxx') but that doesn't tell me how to designate which column or even which table. I tried: select Replace(FieldName, 'xyz', 'abc') from tblName wh...more >>

Hi, I'm in the process of porting some SQL from Sybase ASA to MS SQL Server. The following SQL works on Sybase, but not on MS: select K1.kontonr, (select max(K2.kontonr) from c00001001.konto K2) SubKol from c00001001.konto K1 where SubKol <> '' So far, I have tried variations of th...more >>

Hi, I was wondering if there is some way to use DMO to roll back a failed method call. For example, in detaching and attaching files, what if your code is successful in detaching a file but the attach operation fails leaving everything in a mess. This could be taken care of in the error han...more >>

Hi, I'm in the process of porting from Sybase ASA to SQL Server. On Sybase ASA this will work: ...more >>

I just discovered that datediff doesnt work as I had expected. How do I find the difference beween 2 dates in terms of months, years, etc. datediff(yyyy, '12/1/2004','12/31/2004') returns 0 datediff(yyyy, '12/1/2004','1/1/2005') returns 1 I want something that finds the rounded differen...more >>

background: sql2k on nt5. has anyone tried to output scheudled jobs into a format like Outlook calendar before? pls advise how to. thank you. steve ...more >>

Hi, I have created the following procedure, which is returning the Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) error message. Your assistance is appreciated.... BEGIN SET nocount on TRUNCATE TABLE sys_load -- load the sys_load table initially ...more >>

I am trying to write a query to identify those teams that attended any of certain local events but did not attend our championship. Each team has a unique TeamID and there is a registrations table that contains the following fields: TeamID EventID DateRegistered Class The query needs to ...more >>

TableOne LineName ProductType ENDING_Inv_Date Qty Crosby Line 1 AAA type 01/01/2004 50 Crosby Line 1 AAA type 02/01/2004 70 Crosby Line 1 AAA type ...more >>

I need to send 2-3000 bytes at a time from sql server into a stored procedure but it seems to be getting cut of at 255 bytes. Here is my sql server 2000 code: EXEC xp_sample 'a11111111111111111111111111111111111111111111111111111111111111111111111111 1111111111111111111111111111111111111111...more >>

Is there a way to add a few characters to the front of an existing ntext field value? I have tried using something like I am using a stored procedure to update an existing record and need to append a little text. UPDATE MyTable SET MyTable.MyNTextField = 'MyText' + @NTextVariable Where My...more >>

******************** alt.php.sql,comp databases.ms-sqlserver microsoft.public.sqlserver.programming *********************************** Why doesn't this work: SELECT * FROM 'Events' WHERE dayofweek REGEXP 'monday' OR description REGEXP 'monday' When this does work: SELECT * FRO...more >>

Hi Everyone... My problem is that I have a menu table where all the parents and children are stored in the same table (SEE SQL BELOW). I need to determine the "top" parent (value of -1) and so thought to implement a UDF (SEE SQL BELOW). Unfortunately it doesn't work and I'm stumped!! (I hav...more >>

I'm trying to troubleshoot the content of a table that is manipulated in a stored procedure and surrounded by a transaction. The problem is that I need to get a copy of the table in the state that it is *during* the transaction. I've tried using RETURN right before I want to evaluate the curr...more >>

I have the following table structure Table: Person Columns: ID: int identity FirstName: varchar(50) LastName: varchar(50) BirthDate: datetime I would like to create a GetPerson stored procedure that takes any combination of column values as parameters and searches based on the...more >>

Hi I want to sum rows based on id's Here's the data I am working:- AcdCalls Logid Date 53 4245 2004-06-30 00:00:00 20 4245 2004-06-30 00:00:00 43 4233 2004-06-30 00:00:00 46 4313 2004-06-30 00:00:00 29 ...more >>

Hi, How can I import data from a DOS program into SQL Server 2000. The DOS program is using a file with .DAT extention. Thanks Dib ...more >>

We are experimenting with different backup/restore techniques. One technique is to perform full backup once a day, then perform transaction log backups once a minute. This generates 1440 t-log files per day. We are storing them on a networked drive. When I recover to the same SQL Server tha...more >>

I have this problem with a join because one of the tables involve is not directly related. I have the tables below [table CUSTOMER] ID BILCUST [table SORDERS] ID NUMBER [table PRODUCT] NUMBER SUPPLIER <----------------- Supplier and code fields have the same data (related...more >>

I've run into an interesting problem. This is probably more of a problem with VB than SQL but I've posted to those newsgroups (microsoft.public.vb.database, and microsoft.public.vb.database.ado) and haven't found the solution. For some reason when I try to insert a record into a SQL table tha...more >>

Please, I am new into this. I have a table called "test" inwhich I want to insert 2 field values. first field being a string I will like to pass on and second field to hold a count of values from another table.... see below: CREATE TABLE test ( description text, totals nu...more >>

Dear all I have been working on a solution to a problem and have only got this far with help from this newsgroup, I do however need some more help My problem is to provide average delivery times over a given period from information I already have. There is some duff entries in the data but u...more >>

Hi, could someone please help me with this We have three tables (well, these tables are made up, but the situation is still the same): stock, marketvalue and offer where we have one item in stock for each company (stock) market value history for each item (marketvalue) offer history for...more >>

Hi, I have a query in which I compare two columns. These columns could have NULL values. So basically I want a behavior such that NULL=NULL should be true, and NULL=somevalue should be false. SET ANSI_NULLS - allow me to do this but only when I am comparing a column with a NULL value. It se...more >>

Does anyone know where I can find odbc drivers so I can cannect to a btrieve database from sql 2000? Are there native sql 2000 drivers that support this? Thanks, Jim ...more >>

Hi All I am currently using this :- Where (Date_Logged between convert(datetime,'20040630')and convert(datetime,dateadd(millisecond,-3,dateadd(day,1,convert(datetime,'2004 0630'))))) To get all of the data that was logged yesterday, I am now having to run this each day and want to Schedule...more >>

I want to import a bunch of excel data which contains line feeds and all sorts into a unicode (NVARCHAR) field in MS SQL. Is there a way to transform the excel data so that it imports correctly? Either through replacing the existing characters with unicode or some other way someone else has ...more >>

Hi, How do I access fields in a subquery, which forms a table for a from statement? Example: select table1.a, table2.b from table1 inner join (select b from table3) table2 on table1.link1=table2.link2 I always get an The column prefix 'table2' does not match with a table name or...more >>

Question: Is something like the below possible using IN or something else? WHERE TO_ID IN (21-32, 44,56, 67-76) I know you could write WHERE TO_ID (BETWEEN 21 AND 32) AND (TO_ID BETWEEN 67 AND 76) AND (TO_ID IN(44,56)) I am just curious. I thought I read somewhere you can use a range w...more >>

I run an INSERT INTO table1 SELECT FROM table2 query where the table1 has triggers. The query says it completes, but no rows show up in table1. However, when I delete the triggers from the table it does insert the appropriate rows. Please advise to this problem. Thanks....more >>

I am creating a running total for each row which works great the if the rows do not have the same date. If the dates are the same I get totals for all rows for that date and before (no timestampe). I need to add to the where clause "and < key" to avoid summing the rest of the rows on that date. ...more >>

Hi: I have a simple question - I think! Basically I need to select records in a stored procedure file based on date and NOT time on a datetime column. SELECT * FROM mydbtable WHERE (mindate >= dbtabledate) AND (dbtabledate <= maxdate) However its not that simple since the time can caus...more >>

Once you created it, can you delete it? I rather have my clustered (the faster one between clustered and non clustered) based off of some other column in my table... Thanks Ralph Krausse www.consiliumsoft.com Use the START button? Then you need CSFastRunII... A new kind of application lau...more >>

I have mande a number of table changes such as changing field names, and have updated a large number of stored procedures to reflect the changes. I used sp_depends and the syscomments table to track down the changes I needed to make to the stored procedures but am worried I might have missed ...more >>

I'm primarily a vb .net developer, doubling (as many of us have to) as a sql 2000 dba. I'm having difficulty running a vb.net exe as a sql agent job. I figured out that sql 2000 can't really run a windows forms exe, as it thinks it needs user interaction. However, since it can run an activex...more >>

Hello, Is it possible to run a query which says: Where date BETWEEN 12:00:00AM Last Monday and 12:00:00 AM This Saturday? Havent a clue how to star. Thanks for any helpful tips. Gary. ...more >>

Hi all, I need some assistance with the sequel statement below. The rule and result are show below. Any help would greatly appreciate. Thank you in advance. DROP TABLE #Temp go CREATE TABLE #Temp ( PolicyId INT NULL, StatusId TINYINT NULL, UploadedDt DATETIME...more >>

Hi all, I'm a software developer. I've a new client who has a business analyst that is very, very busy. A few phone conversations has me spending hours trying to sort out table names, field names, and field values. I'd like to email the business analyst the table structure of certain ta...more >>

I'm using SS2000. In general is it almost always faster to use a query rather than a cursor? Here is a piece of code that uses a cursor and I want to know if the query at the end is equivalent to using the cursor. CREATE TABLE t_mailed_facts ( id int not null, promo_date datetime null, p...more >>

Dear Friends I want to have the name of the user in the results of the query ( procedure ) who fired the Procedure. Like i have a user ABC who is running the procedure XYZ The Procedure XYZ is : Select Adress from Employee Please suggest if the same is possible. I want to have the...more >>

Our business users want me to build them a "what if analysis" tool on SQL Server I am planning to use the AS2000 writeback to acheieve this but they also have a requirement to store some documents mainly projects related like word, Excel documents which is all non-numeric data in the same databa...more >>

Hi all, Using: MSSQL 2000 EE. (dev ed) / stored procs. I have problem that I hope someone could help me solve. Below I've entered the tables and required data for testing. I have a map that has "cities" located on the map (table cities) and a 2nd table with routes between the cities. Each...more >>

How can you specify multiple address's (net send) for notification when a Job fails? tia...more >>

Hello Experts, Can you suggest me some good "Pivot" technics? Thanks in advance. ...more >>

Hi, I need a list of records carrying UserID and ALL SectionIDs and SectionTitles he/she is associated with so I created following query: SELECT UsersSections.UserID, Sections.SectionID, Sections.SectionTitle FROM UsersSections RIGHT OUTER JOIN Sections ON UsersSections.SectionID = Sectio...more >>

Hi, I have two tables: ParentTable, ChildrenTable. I have a one to many relation between ParentTable and ChildrenTable. How can I get all the rows from ParentTable and the children count for each row? I need all the rows from ParentTable even if it has no children. ParentTable (parentid, name)...more >>

Hi, I have two tables: ParentTable, ChildrenTable. I have a one to many relation between ParentTable and ChildrenTable. How can I get all the rows from ParentTable and the children count for each row? I need all the rows from ParentTable even if it has no children. ParentTable (parentid, name)...more >>