Hi Guru's In Win98 I can get a trigger to call any executeable with xp_cmdshell but this is not true in Win2000. I only seem to be able to call OS based calls like 'net send' or 'echo..' or 'dir ..' My objective is to get data from a recently added record out of SQL2000 into an external pro...more >>

Is this a Bug? In SQL server 2000 service pack 3a, I am having problems with the convert function which is causing reports to be unreliable even after testing. The only difference between Part 1 and Part 2 is a primary key but part 2 gives a convert error. Furthermore, Adding about 20 ro...more >>

Dear hi I want to compare two tables structure.what to do? thanx....more >>

i've added a Linked server in SQL 2000, to some dBase dbf files using the Visual FoxPro ODBC driver in "Free Table" mode. In Enterprise manager i can see my dbf files i.e. Linked Server dbCottam Tables comsales c:\Cottam\CODEBASE\SERVER\32\data\cottam User customer ...more >>

I am attempting to create a View from MS Access 2000 SR-1 to an MSDE 2000 (SQL Server 2000 Desktop Engine) database . I accessed with exclusive rights but I get the following errorwhen trying to create a view: "You do not have exclusive access to the database at this time. Your design changes ...more >>

Hi I have a very big table with about 40 million records.Unfortunately, the user can query this table using any field (we have about 20 fields). If the user uses one of the indexed fields, the query is very fast. However, if the field is not indexed, it will take forever. What do you guys...more >>

I want to create a database using QA. As this is planned to be executed at various customers, I don't want to specify a fixed path. So I came up with this: CREATE DATABASE [tupApplication] ON (NAME = N'tupApplication_Data', FILENAME = N'tupApplication_Data.MDF' , SIZE = 1, FILEGROWTH = 10%) L...more >>

Hi, I am dbo and sysadmin on this table that i'm trying to deny SELECT permission. I tried the following code, but was still able to select the object. Any help is appreciated! DENY SELECT ON C_copy TO dbo select * from C_copy -Lawrence...more >>

Hi, I'm dbo & sysadmin on this table that I'm trying to take away SELECT permission. How do I do that? I tried the followin code, but was still able to select the records. DENY SELECT ON C_copy TO dbo select * from C_copy Any help is appreciated! -Lawrence...more >>

I'm still having problems trying to install sqldmo on a couple of client machines The files I'm installing are sqldmo.dll Resources\1033\sqldmo.rll sqlresld.dll sqlsvc.dll Resources\1033\sqlsvc.rll sqlunirl.dll sqlwid.dll sqlwoa.dll w95scm.dll When I attmempt to register it I get a...more >>

Hello, I am loosing hair and memory by the minute. Here is my problem. I am calling a SP from a COM project. Everything works but there is a memory leak, at least I think there is. I am using CreateParameter and Append to setup my SP then call execute to run it. I have this running in a endl...more >>

We are trying to run bulk insert from a mapped network drive on a unix machine. The file has the row terminator as '\n' and not '\r\n' like the the windows. When i run the bulk insert command as below, CREATE TABLE STAGING( [LINE] [NVARCHAR] (1024) ) BULK INSERT STAGING FROM '\\unix-machi...more >>

I have a second probelm with my view. see example -> table person 1 hans 2 peter table keywords 1 superstar 2 schweiz 3 england 4 2 zu 0 5 zkb 6 microsoft table per_key 1 1 1 2 1 3 1 4 2 5 2 6 2 1 VIEW = ...more >>

i need to create a union select statement with all tables that have 'Mail' in the name of the table. i will have an unknow number of tables (Mail, Mail1, Mail2, Mail3, .....) how would i creat the statement? ...more >>

Is there any way to catch an error in a user defined function? I am converting data from a string to a datetime and sometimes the data is not valid. Even though isdate return 1 (valid) it seems to happen that once ina while it isn't. I don't mind losing those values but the problem is that the...more >>

This seems to be the most active newsgroup on usenet about iso-11197 (thanks to Joe :P), so I thought I'd ask this here. Im trying to follow the general standards of ISO-11197 while designing some new structure into a database. I am having trouble determining how ISO-11197 recommends a certain ...more >>

Hi, I have a table that contains all the contact information for people in the database. I didn't want to make 'name' the primary key because of the obvious repetitions, so I'm thinking phone number. But then I would want to cascade on updates tables that refer to this attribute. It has been m...more >>

(opened new thread) I am having problems getting a trigger to fire correctly when being fired from our 3rd part management software. In my trigger I need variables filled from tables that weren't updated. simplied version: declare @draftsman varchar(30) select @draftsman rtrim(d2_stuf...more >>

Hi, I am trying to add a column to a huge table that contains 138 million = records. First, I backuped the transation log and then tried to add a = column to my huge table using EM. The process failed because the = transaction log was full. Could someone tell me how I can add this = column ...more >>

Hi, I have a database that has many different RAID 10 arrays for OLTP performance with various filegroups laid out appropriately between the arrays. I want to backup/restore (1 full/day + 15 mins incrementals) out to a RAID 5 system where the target database is in standby mode (i.e. read-only...more >>

I have a column in a table that has dates stored as numeric data. The format of the digits is yymmdd. I need some sql syntax to retrieve this data retaining the leading zeros. EX: 105 in the db as characters 000105 (Jan 5, 2000) ...more >>

Im trying to remove values in a temp table based on values that it shares with another temp table..Im getting a "Incorrect syntax near 'a'. message on this statement delete from @test6 a inner join @test6purger b on a.[cost center] = b.[cost center] and a.[payor type] = b.[payor type] ...more >>

Hi I suspect there is an easy trick to this but I can't seem to find it. I have a table of data and want to return a view of it as follows: Table Fld1 Fld2 A a A b A c B a B c B d C f View Fld1 Fld2 A a,b,c B a,c,d C f Thanks Ian ...more >>

Probably an easy question but I don't have much experience with scripting. There are three tables in a single database, and I want to write SQL to insert an inner join of the first two tables into the 3rd table. Can someone please give me or point me to a sample script of that? Thanks!...more >>

I have a table that I need to show in a web-based report. Because of the data in the table there is no unique way to identify a row. I currently have 500000 records, but know it will grow to around 4 million in the future. It is used as kind of a FILO queue; new records are added and old ones...more >>

Hi all, I placed the SET XACT_ABORT ON statement at the beginning of a sproc, but appearently it did not skip the t-sql errors happended within the sproc and rollback. I'm hoping to find a method to skip all error messages and just rollback. Is there a way to do that? Thanks, -Lawrence ...more >>

hi, I want to query a table but I want the resultset in a random order that's decided by the SQL server. How would I write the ORDER BY clause. thanks rafael ...more >>

I have some junk data in a column. It is supposed to be a a phone number column, but someone before me had made it a 25 char field. I noticed I have a few entries in the field like this -233-3457 When it should be just this: 2333457 Is there a way to trim the 5th character ('-')? ...more >>

This is my second question of the day and it isn't even luchtime yet... This is my Query; SELECT s.fld_str_Name, SUM(p.fld_int_ManifestBoxCount) AS Manifest, SUM(ISNULL(p.fld_int_DeliveredCount, 0)) AS Delivered, p.fld_dat_PickUpDate, (fld_int_DeliveredCount-fld_int_ManifestBoxCount)*10...more >>

Hi @ll I have in a table "adress" 2 fields "adress.fax1" and "adress.fax2". Now my job is to create a view witch have only "view.fax" field. If "adress.fax1" ist set then "view.fax" has to be "adress.fax1". if "adress.fax1" not set but "adress.fax2" then "view.fax" has to be "adress.fax2". ...more >>

Hi, My problem in MS SQL 7.0: User 'John' has access to the database 'Articles' and has all permissions (select; update; insert; delete) to the defined tables within this database. Now in 1 table 'Article' I have a list of articles. The ID (identity increment) of a record occurs in another ...more >>

I created a derived table out of a union statement and then selected data from the derived table..however I want the select statements in the union query to "union" based on a certain citeria..I put the citeria in comments..heres the code I have so far: select sum(revenue)as revenue, (cas...more >>

Hi, I have a datetime column in my table and I am trying to write a stored procedure to get the last 5 records based that datetime column (the newest values). I am having a brain fart this morning because I cannot get it to work. Can somone help me? Thanks ...more >>

Hi again, I'm going through the relationships in the database and deciding what rules I should have for cascading. No user except the DBA will have the ability to delete data, so I'm hesitant to cascade on delete. On updates I feel like you should always cascade and am unclear why it is an opti...more >>

I have a trigger set up that fires when a column in a table is updated. If I create an update statement and execute it through Query Analayzer the trigger fires correctly. If our 3rd party software package updates the field it doesn't produce the results needed. The trigger is fired ...more >>

Hi All I need to calculate average delivery times over a given period. Each time is held in a row in the HH:MM format and represents 1 day per row Any suggestions appreciated Regards Gavin ...more >>

Hi there, I cannot find a good explanation of how to execute a DTS package from a remote computer. I want to write a script or application so that when a new dataset arrives anyone can just put it in the right directory and then run the script/app to load the data. How have others done th...more >>

Hello all, I am not sure how to handle this issue but here's a shot at explaining the problem: Our customers have multiple changes to their insurance over the course of a year or multiple years as long as they are employees. Thus, there is a InsuranceStartDate and an InsuranceEndDate...more >>

i have 3 different types of "Customs Forms". These are Pro-Forma Invoices, that need to be presented to United States customs, when a shipping brings goods into the US. Here is (simplified!) DDL for a theoretical unified table: CREATE TABLE Customs ( CustomsGUID uniqueidentifier NOT NULL ,...more >>

just curious..... searching for an underscore(wildchar) in a string.... anyway to do this? ie. FILENAME 1234_1_89023.ext i've worked around this by replacing the '_' char. just wanted to see what others thoughts are. warren...more >>

I'm calling a Stored Procedure from within a Stored Procedure. The SP that is being called returns an ID. How can I utilize this ID from the Calling SP? In this Example, the SP spLocal_HomeGetNextID has as its second parameter a return value (@NextID). When I run this SP, though...more >>

Table 1 =3D Warehouse Master =3D Whs_Key & Whse_Name Table 2 =3D User's access to Whse's =3D User Key & Whse Key Table 1 Data : W100 Abc Warehouse W101 Def Warehouse W102 Ghi Warehouse W103 Jkl Warehouse ...more >>

Hi I have to create a resultset by joining a bunch of tables. These need to be accessed by a few stored procedures within a transaction. What is more efficient for doing this, a view or a temporary table ? Thanks, Venkatesh...more >>

Hello! I'm modeling a big database in the SQL Server (more than 1.000 tables), and we are in trouble with the defining os primary keys. For example, we have a table called 'People', that have a primary key 'PeopleID'. In another table, called 'Address', we have a 'AddressID' column that is ...more >>

Hi Group, I have read all I could find (in BOL and elsewhere) about table hints and I get the basic idea. When I run complicated SELECTs that may take a few minutes, I specify NOLOCK so that INSERTs and UPDATEs will not be blocked. I am filtering for data of the past, which will not change dur...more >>

This SQL statement SELECT MAX(DISTINCT ProductVersion) AS New, ProductType, ProductDesc, CAST(ProductLogo AS varbinary) FROM eProducts GROUP BY ProductType, ProductDesc, ProductLogo gives me this error Server: Msg 306, Level 16, State 2, Line 1 The text, ntext, and imag...more >>

How do I get the current proc name in a proc? I have tried variations of the following without success: SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_NAME = OBJECT_NAME(@@PROCID) Thanks! ...more >>

Hi I need to delete duplicate enties from a sequel table Example as follows: DATA IN TABLE customer PAYMENT DATETIME HA02314 99 20040404 23:01:59 HA01234 99 20040404 23:01:59 HA01234 69 20040404 16:02:49 HA01234 69 20040404 16:02:49 HA01234 69 20040404 16:02:49 HA01236 8...more >>

We use a third-party software product for real-time data replication between an AS400 box and a SQL box, and sometimes the data can become squirrelly, and require a bulk-copy to normalize the data. I'm looking for an easy way to compare the fields between the two tables. I've got both boxe...more >>

I have an application which has SQL server 2000 as the backend. I am trying to track users who do certain modification to a table via a trigger. The hostid and hostname give me only the server id and name and the username is a common profile name. Hence I need the workstation id of the user who logs...more >>

Hello All, I need to change the base types of my UDT's.I know that sp_help stores all the required information abt UDT's needed to re-create them. The question is, how do I pull out this information, so that I can re-create my UDT's with the new data types. Any help appreciated....more >>

Dear Sir, How can I migrate all SQL server Stored Procedures to Oracle adn Oracle to SQL Server? I want to code in VB 6.0 or VB.Net Please tell me Can I move all stored procedures using DTS if yes then How can ? Thanks very much, usman *** Sent via Devdex http://www.devdex.com *** Don...more >>

Hi Everyone, I have noticed that when dealing with large volumes of data using 'not exists' is very slow. Does anyone know of any good alternatives? The stored proc I am working on is below: SELECT File_ID, TM_ID, CE_ID, TP_ID, CurrentValue, CurrentFormula, WS_ID, RL_ID FROM ...more >>

I read how to get every other row using a select but I forgot how. I think it was done using GROUP BY and using modulo in the HAVING part of the clause. Can someone post a succinct example? Thanks....more >>

Hi there... I have come up with a script which returns around 3 million records. The script runs like for ever( approx. 3 hrs). What are the steps that I can take so to make my script does a optimal performance. The script does invlonve lots of inner joins, Temp tables, Select into and up...more >>

I see BOL says that text fields are not included in the inserted/deleted tables in AFTER triggers. I would like to audit changes made to text fields without losing the AFTER triggers I already have. What would be the most efficient way of doing this? Thanks for any help Bernie...more >>

This expression is ok select cast(year(startdato) as varchar) from npr_episode where isdate(startdato) = 1 group by cast(year(startdato) as varchar) order by cast(year(startdato) as varchar) but this is not ok select cast(year(startdato) as varchar) + 'date' from npr_episode where...more >>

Hi, I have table in which data is stored as follows ColName ColValue ------------------------ Col1 ColVal11 Col2 ColVal21 Col1 ColVal12 I need output like this Col1 Col2 ---------------------- ColVal11 ColVal21 ColVal12 --...more >>

Is there any way of using EXECUTE to run a sql statement in a function. The statement is just a query and has no follow on effects. E.G. CREATE FUNCTION GetMaxID(@aTableName) RETURNS int AS......more >>

Hi, I'm trying to read from a basic excel file (1000 or so rows from column A) but am having problems. The code I am using is: Declare @Return Int SET NOCOUNT ON Exec @Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL', 'Microsoft.Jet.OleDB.4.0' , 'e:\jsbackup\RACodes.x...more >>

I am trying to read information from a basic excel file (1000 or so rows in column A). I am using the following to set up the linked server. Declare @Return Int SET NOCOUNT ON Exec @Return= [master]..[sp_addlinkedServer] 'READ_XLS', 'EXCEL', 'Microsoft.Jet.OleDB.4.0' , 'e:\jsbac...more >>

I'm having a strange problem with SQL server 2000 and full-text search on a table that contains data in greek. Although everything is ok when I run my queries on Query analyzer when I use my classic ASP application I get the 'A clause of the query contained only ignored words' error. I used respo...more >>

Hello Experts, I got a Table filled with the Hierarchy of Tables. As you can see table 'Works' references with tables 'Contractors' and 'Work Sites', 'Contractors' with 'Members' and 'Members' with 'MembersContacts'. What I want is to get the full Info (With a single query if posible) ...more >>

Hi, I'm using EXEC sp_executesql to execute some dynamic sql. My problem is due to the limitation of 4000 bytes, I had to execute a seperate SP for another column. How can combine them into one SP call? For example, I have: EXEC sp_executesql @sql, @paramList, @intCode AND EXEC sp_1 @a...more >>