Hello I'm just trying to learn how to program functions in Sqlserver 2000. and created this function, but it returns no rows, just the column headers in Query Analyzer Why doesn't it return any rows?? I thought that the INSERT statement works, but I'm a newbie create function tfunc (@par...more >>

I have about 3 companies and they all are connected thru VPN. I have created 3 sql server registeration so I can now see all three sql servers in my enterprise manager. I want to copy few tables from company1 and company2 to company3 on a regular basis. I am looking for a SQL statement t...more >>

Hi! I have this SQL statement WHERE ProductAttributeSet.ProductAttributeSetID IN(#variables.AttributeSet#) Sometimes the variables.AttributeSet is an empty string and the server throws an error. Is there any way to avoid this? What is the alternative to IN? ...more >>

Hello, I have a table that is repeatedly checked for date range. Something like: SELECT * From Sales WHERE Sale_datestart<=getdate() AND Sale_dateend>=getdate() What Index should I create for maximum speed? Thanks! ...more >>

I'm trying to run a program from sql server 2000 SP3. exec master..xp_cmdshell 'post.bat' If post.bat contains: dir *.exe > dir.txt post.bat runs fine and dir.txt contains directory listing. However, if post.bat contains: notepad or notepad.exe or c:\winnt\notepad.exe Notepad doe...more >>

Is it possible to use a forced parameter in a view? If I created a query in Access, I would put in the Criteria a forced parameter thus [Enter Start Date]. If this is possible in SQL Server, how do I achieve it please? Regards Darren ...more >>

I am building a database using SQL Server 2000 as the backend (tables & views) but wish to use Access XP (2002) as the front end. My problem is that I do not want users to have full versions of MS Access installed. I know there is a runtime version but do not know how to go about this. I hav...more >>

Is there a way to select all of the columns in a table except for one column without listing all of the columns in the select clause? I know that the syntax listed in my subject line does not work, but perhaps there is another syntax or a clever use of joins which can accomplish this. Any help ...more >>

1. A composite key (2 or more columns) contains an index on the 1st column? 2. From the SQL2K book: "... A FOREIGN KEY constraint is a candidate for an index because: 1. Changes to PRIMARY KEY constraints are checked with FOREIGN KEY constraints in related tables. 2. Foreign key...more >>

hi, so i have a routine ready wich can be executed in a stored proc what i now need to be able is to receive the table name and where clause i seem to fail to use input variables in the stored proc to use in the select statement any examples lying around? something like create procedure Tes...more >>

I'm working with SQL 2000. The database server is located in the Pacific Time zone. Data will to be collected from other time zones (CT, MT, ET, etc.). Is there a stored procedure that can convert the time stamp on the data retrieved from the time zones into GMT? Thank you in advance. ...more >>

I have a table that contains a number of items. I am trying to keep track of when the last time each item was updated. Has anyone done this? Thanks in advance! Don...more >>

I've 2 tables called "Prova" and "Prova_tmp" "Prova" is the main table Using XMLBulkLoad component I'm importing thousand of records into "Prova_tmp", some of these records should be inserted into "Prova", some other sould update records in "Prova" and others should be deleted from "Prova" ...more >>

How can I find an old message posted on 7/24/03 about DTS and FTP? Can I build a DTS to transfer data from a FTP site to SQL server directly? And how? Thanks a lot! AW ...more >>

Hi, I need to make a decision wether to use CDO or MAPI in order to send emails from SQL Server. (I have an Access adp front end and a SQL Server 2000 backend) I will need to be able to customize the Subject and the body of the email by querying specific fields of a table. I don't know whi...more >>

I uploaded some data about 2 or 3 times and it keep appending it to the table. Now I want to keep only first duplicate and delete rest of. Suppose part number 123 has been added 3 times so I want to keep only 1 record. Thanks ...more >>

Hi, I have a table with an ID column set up (INT). Somehow (dunno how) I have = 2 records with the same ID. I can't update or delete either without = getting the error above. I've tried temporarily making the column a normal (i.e. not an ID or = auto-increment one) but I still get the error...more >>

hi I need use inserted and deleted tables in EXEC functions, in update trigger. It work without EXEC. But when forming string: SET @SqlStr = 'SELECT "' +@FN+ '", i.' +@FN+ ', d.' +@FN+ ' FROM inserted i INNER JOIN deleted d ON i.id = d.id' ....and put it string in EXEC: EXEC (@Sq...more >>

hi I need use inserted and deleted tables in EXEC functions, in update trigger. It work without EXEC. But when forming string: SET @SqlStr = 'SELECT "' +@FN+ '", i.' +@FN+ ', d.' +@FN+ ' FROM inserted i INNER JOIN deleted d ON i.id = d.id' ....and put it string in EXEC: EXEC (@Sq...more >>

Hi, I Have a question about how I'd go about writing a query: I need to find when the latest transaction for an item is. Transaction can be of following types: Purchase Order, Quote, or Invoice the problem is the detail for the 3 different transaction types lie in different tables. I can w...more >>

Given the Service name (e.g., "MSSQLSERVER"), is there a way of getting the name of the SQL Server instance (e.g., MYSERVER)? I need to establish a connection, but need the name of the SQL Server instance. Thanks! ...more >>

Hey: Is there a t-sql command within alter table or some such thing that allows me to rename a table column? thanks....more >>

I have a datatime record, it is UTC time.It need to be show as Eastern time. Is there a function that i can pass time zone and time to it, it returns the time format as in that time zone. Thanks ...more >>

hi, i'm looking for a way to get a RowNum value for each row returned i looked at the Identity(int,1,1) solution but this only works if you're copying the result set of a table into another is there a way to add a column to the select statement that simply increments with every row returned? t...more >>

Have three tables Table0 Pk0, data fields say DF0 Table1 Pk1, FK0ForTabl0 and T1data1 and other data fields say DF1 Table2 PK2, FKForTable1 and data fields say DF2 and also one field StartUsingOn (dateTimeType) I need to get a list of table0 data, table1 data and 1 record from table2 that ...more >>

Server Spec: Windows 2K Server SQL Server 2000 SP 3 RAID 5 One Production Database I run a job that executes a stored procedure. This stored procedure does alot of Inserts & Updates to many tables within the database. I have one master stored procedure that has many nested stored pr...more >>

Does any one know how to delete with SQL all Constraints on a Database. Thanks. ...more >>

Hello, I have two tables that I am needed to generate reports from, one has 10 million rows+ and the other has less than 200,000. These number of rows will be increasing fairly steadily over time. I have some queries that do counts and group bys along with some sums on these two tables and...more >>

I have a link server which points to a text data file. when i do a select * into to create a temporary table query analyser give following error. The total row size (9767) for table 'TX20' exceeds the maximum number of bytes per row (8060). Rows that exceed the maximum number of bytes will n...more >>

I would like to take a nightly backup from the Production database (STAR_JET) and restore it to a Developement database (STAR_JET_DEV). I would need to create a job that will restore the nightly backup of the Production to the Development databases. Also I will need to execute a script t...more >>

Hi, We are experiencing sporadic deadlocks in a database. They occur a couple of time a day, with a few dozen users. However, because of the sporadic nature we have unable to reproduce the deadlocks in the testing environment. I am looking for suggestions on how to monitor and troubleshoot t...more >>

(reposting with new subject name) I've populated my CONTACT table shown here: CREATE TABLE [CONTACT ] ( [MASTER_COMPANY_ID] [int] NULL , [DATE_ATTEMPTED] [datetime] NULL , [WEEKS_SINCE_LAST] [int] NULL , [WEEKS_SINCE_START] [int] NULL , [WEEKS_TO_END] [int] NULL ) ON [PRIMARY] GO ...more >>

I've populated my CONTACT table shown here: CREATE TABLE [CONTACT ] ( [MASTER_COMPANY_ID] [int] NULL , [DATE_ATTEMPTED] [datetime] NULL , [WEEKS_SINCE_LAST] [int] NULL , [WEEKS_SINCE_START] [int] NULL , [WEEKS_TO_END] [int] NULL ) ON [PRIMARY] GO with data shown (INSERTs at end of ...more >>

Why do words like Name and Description turn blue when typed into QA? I can't find them in the T-SQL keywords list and the help doesn't show them as being keywords. Also, using them as field names doesn't seem to cause problems. I'm asking because some of our databases use them as field n...more >>

Hi everybody, I'm trying to run the following statement: SELECT Len(RTrim([claimid])) AS ClaimLen, (If (([claimid] Like 'r%'), (Right(RTrim([claimid])),([claimlen]- 1)),[claimid])) AS ClaimID2, dbo.TempReportOnline.CLAIMID, ...more >>

In an ASP-built intranet app, which keeps track of trouble tickets for a tech support team, I've been asked to make it send emails automatically to managers if a problem is marked as serious, then it needs to poll the database every 15 minutes or so, and if the problem has not been updated in a ...more >>

I have following StoredProcedure but It gives me following error. Error 7405: Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these Options and then reissue your query. CREATE PROCEDURE [DBO].[Inse...more >>

Hi SQL gurus, I have to work with a badly designed database to do the DML Updates from Journal Transaction to Account master. Below is the simplified DDL and the Updates requirement . Create table account( AccountNo char(4) not null primary key, debit01 decimal(15,2),debit02 decimal(15,2),de...more >>

Using updlock as locking hint in a select statement what should be the locking hint of another select statement in order to return only records that are not locked by the first procedure. For the first store procedure i want to set an exclusive lock and not a shared one. I tried readpast ...more >>

I'm trying to save / extract a blob data value to an image field simply by using the standard INSERT and SELECT statements. I've created the following stored procedures to save and retrieve data (very simplified at present) CREATE PROC Save_File @PubID char(4), @FileObj image AS UPDATE...more >>

i have a sql query something like this. select ,(select count(*) from A inner join B on a.id=b.id) as temp1 ,(select count(*) from C left join D on a.id=MAIN.id left join E on e.id = MAIN.id) as temp2 ,lastname ,firstname ,mi from MAIN there is more to the query...more >>

I have a database I'm developing for a claim processing system. The primary key on the claim table is PolicyNumber LossDate SequenceNumber Some sample data would look like this PolicyNumber 1X LossDate 01/01/2003 SequenceNumber 1 PolicyNumber ...more >>

Using the attached select statement, i want distinct rows by "cust.nam" and "sa_lin.item_no", but i have to include the "sa_hdr.post_dat" because i need to refer to it in code. How do i tell the "DISTINCT" to only be distinct on "cust.anm" and sa_lin.item_no" and still get the "sa_hdr.post_dat" ...more >>

--table 1 data ticker measure prd_yr prd_mon val -------- ------- ------ ------- -------------------------------------------- --------- COBZ EPS 1999 9 0.12666653999999999 COBZ EPS 2000 9 0.1666665 COBZ EPS 2001 9 0.19 COBZ EPS 2002 ...more >>

Hi, why this statement doesn't work ? print cast ('12' AS smallmoney ) Thanks, Ali ...more >>

I have a table that is like the following: id text ---- ---------- 1 'Row----------1(linefeed)Row--------2(linefeed)Row--3 (linefeed)Row----4' Where (linefeed) is char(10) This is one row. I need to import this to a new table that will look like id text ---- ---------- 1 R...more >>

Hi, I have 2 tables in my database, the first is Foreign key the Second, I want to delete a row from father table (Second), but I can't cause it will return an error in my app... I create a trigger for DELETE to execute an operations with my first table, it's update my first table and set...more >>

Thanks in advance for any help that you provide. Without going into my specific project, I need to something like the following (using sports as an example so everyone will understand): 3 Tables: League, Team, and Player. A League has many teams, a team has many players and players have st...more >>

Instead of sending many parameters to a procedure, I'm sending only one, in XML format, and then I parse parameters out, like this: ALTER Procedure dbo.spTheProcedure ( @XmlParams NText ) As Declare @xmdDocument Int, @Parameter1 Int, @Parameter2 ...more >>

Is there anyway I can pass table name dynamically within my stored procedure. I am thinking of creating 12 tables for each months data within a year. Each year of data will be date stamped. Now I want to create a stored procedure which accesses the data from these tables. Is there anyway I can...more >>

Create table TableA (Mainid int, MainName varchar(10)) Create table TableB (Colid int, Pid int, Cid int, CurrentCount int ) Insert tableA values ( 1, 'A') Insert tableA values ( 2, 'B') Insert tableA values ( 3, 'C') Insert tableA values ( 4, 'D') Insert tableB values ( 1,1,2,2)...more >>

Hello All, I have a stored procedure with the select statement below. One of my inner joins is to a table created with a select statement. I am giving this inner table the same conditions as the outer table, but I am wondering if this is efficient or if I should just get the (Select Max Fro...more >>

Ok....here is my set up.... MS SQL Server 2000 I have created 2 tables in MS SQL Server 2000. table 1 and table 2 have a one-to-one relationship by primary key I have created a View in MS SQL Server 2000 - it selects all fields from table 1, and all fields from table 2, except tabl...more >>

Which System table table is used by the SQL Server 2K to store the Last IDENTITY Value generated for any particular Table say Table Name is ARMast and the IDENTITY Column is id_col OR Is there anyother mechanism available to reset this value say when all the records from the table are delet...more >>

Hi, SQL Server doesn't accept following query: SELECT a+b+c+d as exp1 FROM t WHERE exp1<100 I have to change it to following form to make it work: SELECT a+b+c+d as exp1 FROM t WHERE a+b+c+d <100 I have similar problem to sort or group by expressions. Is there any way to NOT TO rep...more >>

I'm writing a series of stored procedures to do ETL, and I need to trap the *EXACT* error messages & store them in a log. For instance, when MS SQL (2000) throws an exception on a duplicate key: Server: Msg 2601, Level 14, State 3, Line 104 Cannot insert duplicate key row in object 'Z_MY_TAB...more >>

Hello, I have created the following trigger: CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE AS DECLARE @foobar varchar(100) SELECT @foobar= foobar FROM inserted IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar') ...more >>

Hello, I am trying to lean how to create and utilize stored procedures. I created a simple little stored procedure and get it to execute. When I run the SQL on my own, it runs just as I expect, but when I run it as a proc, it always ruturns 0 rows when it should return rows. I am quite sure I a...more >>

Hello Everyone, I wish to insert name, dob, and ss number into a persons table in a customers database from an access table that I have set up as a linked server called Customers. My persons table does not allow blank ss numbers, but the Customers table ( from a linked server has blank ss number...more >>

Hi, I'm new in this newsgroup and I want to know How can I create a trigger. It must be called when I DELETE a row in my table. What is better? Trigger or Procedure to do it? thanks ------------------------------------------------- Marcus Vinícius M. Montezano Developer -----------...more >>

what is faster/better? using cascade deletes or coding the deletes of dependent tables in the stored procedures? currently, weve coded all the deletes in our stored procedures, but we have foreign keys defined for the tables. thanks stephane ...more >>

Create table TableA( [id] int, [fld1] int, [fld2] int, ) Create table TableB( [id] int, [fld3] int, ) Insert TableA([id], [fld1], [fld2]) Values(100, 'Joe', 200) Insert TableA([id], [fld1], [fld2]) Values(100, 'Steve', 400) Insert TableA([id], [fld1], [f...more >>

In T-SQL how can I determine the position of a particular character in a string? ie, if I have an email address like 'fred@fred.com' how do get the position of the '@' so I can then use substring to extract all the characters before it and end up with just 'fred' ? Thanks Brian ...more >>

If i have a statment that i do a simple inner join of 3 tables say tableA,TableB and tableC, is it a must to have a join condition as such assuming id is a key on these tables such as "tableA.id =tableB.id and tableB.id = tableC.id and tableA.id =tableC.id " to avoid any cartesian product. wh...more >>

Hello: I apologize for the lengthy message but I thought I should live as much information as I can to explain the situation. Your feedback will be very much appreciated. Hello: I am trying to implement a databse that calls for a hierarchical data structure: There is a possibility...more >>

Is there a way to have multiple null values in a row with a UNIQUE constraint or index ? I want the rest of the values to be unique but null values can be repeated....more >>

This statement works well to delete all rows that have the same item number, order and is less than the max release in SQL. DELETE FROM TRANSACTIONS WHERE JOB < (SELECT MAX(rel) FROM mbs023t t2 WHERE mbs023t.[order] = t2.[order] AND mbs023t.item = t2.item) What I need to do i...more >>

Hello, T-SQL make a mistake in compare charactor. ex1 IF NCHAR(0x30a2) = NCHAR(0x32f3) COLLATE Japanese_CI_AI PRINT NCHAR(0x30a2) + ' = ' + NCHAR(0x32f3) NCHAR(0x30a2) is A in Japanese. NCHAR(0x32f3) is YA with Circle in Japanese Collectoly A <> YA A with Circle is NCHA...more >>

Dear all, Doubt (in VB ) ---------------- i want to print the number in the following Format 1000.00 125.10 12.00 3.00 While i am writing the Format(a,"###0.00") the output looks like 1000.00 125.00 12.00 3.00 ...more >>

I need the help regarding how to print and retrive the BMP Picture in the Text File. I want to take the picture Print out in Dot Matrix Printer. Thats my aim. -- Posted via http://dbforums.com...more >>

Hi, the following query returns select shmsharect as shares, shmctryres from shrsharemaster where shmctryinc = '' shares shmctryres 0 SGL 0 MY 0 SGL 10000 MY 27000 SGL 0 ID 0 MY 5000 MY 0 ID 0 JP 3000 CN 1000 CN 12000 CN 0 IN I have changed the query to sele...more >>

Anyone know of a Visual Control or COM that has the "look and feel" of Enterprise Manager? Want to use it from a VB or C/C++ program. Thanks. Neil ...more >>