No one answered in another group, please help. Using access 2000 /msde I know how to compact the access project file (.adp), there's a menu choice for that. How do I compact the tables and stored procedures on the server? ( The .MDF file) I only have the DOS promp OSQL utility to work with. ...more >>

I'm formatting my mirrored drives and installing a third drive for raid 5 to add more space. I have backup exec. Can anyone tell me the best way to backup SQL so I can be sure it will restore properly? I was thinking of using imaging software and creating an image to a USB 2.0 hard drive. The ...more >>

SQL SERVER 2000 System let's you alter the system tables and add indexes. However, it won't let you drop the index afterward. Anybody know how to drop an index on a system table? Thanks, Kevin ...more >>

Hello, I need some help with implenting the following: I recently migrated from access to sql server and i now i want to use maintainable permissions on my tables, views, etc. The access database will serve as a front-end. I've created for testing purposes an testaccount with only a publi...more >>

Hi, I have a corporate database with about 60 different tables that spans manufacturing, accounting, marketing, etc. It is possible, but unwieldy, to establish a relationship for each table in the entire database through critical fields like customer_id or product_id. But should I do tha...more >>

Hi , long time I didn't ask any questions And now I am back. I have a huge query that looks like that select emp.name, a.field1, b.field1,c.field1,d.field1,e,field1,f.field1,g.field1 from emp left join (select sum(field1),empid from table group by empid ) as a on emp.uniqueid = a.empi...more >>

Hi All! I would like to have a composite PK on 3 columns, one of them is null CREATE TABLE TableA ( ColA int NOT NULL , ColB int NOT NULL , ColC char (3) NULL , ...... ) GO ALTER TABLE TableA ADD CONSTRAINT TableA_PK PRIMARY KEY CLUSTERED ( ColA, ColB, ColC ...more >>

Hi, How can I find out when a particular table structure (design) was last modified? I want to find out whether the creation and modification date of the table are same, to find out if anyone has ran a DDL statement against the table! regards, Surajit Laha...more >>

Hello everyone, I'm not a database guru so I'm sorry if this is a dumb question but here it goes... I have this sql query that I'm trying to run against a table on a Sql 2k server: SELECT * FROM ChangeTable WHERE (CompanyName >= 'Ad%') AND (Company...more >>

Is there something like exception handling in T-SQL? For example, how to catch an error of convertion at this sample: CREATE PROCEDURE SP @param VARCHAR(50) AS BEGIN DELCARE @var INT -- try { SET @var = CONVERT( int, @param) -- } catch (error#245) { -- handle a...more >>

Hello to all, I have a small question. I call the SP outer the DB. The procedure deletes some record in table T1. The table T1 has a trigger after delete. This is very importand for me, that the SP will be finished ASAP, that's why, I do not want, and I do not need to wait for a trigger. ...more >>

How to use db-lib to update/insert database records without using SQL language. I want to change the value of the data individually without plugging in the new values in the SQL language then execute it. The perfect situation for me is loop through the retrieved records then edit the values indi...more >>

Is there a quick way to number a query? ie I want to number the records returned by a recordset consecutively. This seems like it should be simple but I haven't figured out how to do it yet. Any help is appreciated! TIA Carter ...more >>

Hi I have a table called Orders with columns OrderId, ItemReceiptDate and itemnumber. Sample Data is given below: OrderID | ItemReceiptDate | ItemNumber ------------------------------------------- 1 | '2004-01-01' | 3 1 | '2004-01-28' | 4 1 ...more >>

Hi I want a simple select query on a column-name (smalldatetime) with values dislayed in desc order with null values FIRST. i.e. Select orderdate from orders order by ( null values first and then orderdate in desc order) could any one please help Thanks...more >>

I am having a problem executing long running queries from an ASP application which connects to SQL Server 2000. Basically, I have batches of queries that are run using ADO in a loop written in VBScript. This works pretty well until the execution time of a single query starts to exceed some thresh...more >>

I have a small doubt in Heap Is heap consists of extents as a whole or heap ma contain a part of extent1 and a part of extent2 plz me and my friend is fighting for this plz reply me soon...more >>

I am trying to do a summary SQL query. I have 3 fields. If one filed is null and the other is not null, I want to count how many records there are. I also want to count the opposite way then count both fields if they are both not null. Can I do this within the same query? Help appreciated. T...more >>

Using the "Pull New Subscription" wizrd in the enterprise manager, I can create a subscription without a problem. Howwever using TSQL I cannot do the same thing: Using this sequence: sp_addSubscription (on publisher) sp_addPullSubscription (on subscriber) sp_addPullSubscription_Agent (on ...more >>

Which is more efficient: Select * from table1 where id in (select id from table2) or Select * from table1 where exists(select * from table2 where table2.id=table1.id)...more >>

How I can to open a encrypted stored procedure in Hexadecimal mode with Query Analyzer? Thanks...more >>

Is it possible to approach a MS SQL Server using TCP/IP? I have looked everywhere but have not found any documentation about the protocol to connect to MS SQL Server. I wish to connect to MS SQL Server directly through TCP/IP, without using MS libraries (since i am not doing this on a windows ...more >>

I would like to find a utility that can scan through an existing Sql Server 2000 database and create scripts to re-insert/re-populate the data into another DB with the same table structures. Like a back-up utility. Can anyone recommend one (or tell me which ones to avoid)? I would like on...more >>

Hi, I need deep knowledge about DTS tasks. Can anyone send me an e-book or related document about DTS packages. (Links on the Internet site are generally advertisement or not working links). Thanks, Veysel Can Demir...more >>

I created a stored procedure like this: CREATE PROCEDURE SP AS BEGIN CREATE TABLE #T( C INT ) INSERT INTO #T(C) VALUES (1) SELECT * FROM #T END When I call it this way: EXEC SP, it works ok. But when I do it like this: SELECT * FROM OPENQUERY( MYSERVER, 'EXEC SP') I receive ...more >>

Hi, I have the following SQL statement which is pulling a few details from a database. As you can see, there is only the one table from which i am creating a temporary copy. The reason I do this is because in the table i only have the 'standIn' listed by integer and i want to return the 'sta...more >>

I have an archival process on a large database that runs once a month. At the beginning of the process the triggers and indexes on the tables whose data is moved are dropped, the data is moved and then the triggers and indexes are recreated at the end. This produces a massive improvement in pe...more >>

Hi there, I have created a hash table. After using it, somehow the primary key constraint of this hash table still exist in database. Which cause error. When I delete this constraint with Alter table Drop con.... It gives no table exist error. Can anybody give any idea. Thanks in Ad...more >>

Hi there. We have an application that can run on a non-unicode or a unicode sql server database. Currently the application is running in a unicode database, as a non-unicode database is less than half the size, I would prefer to have a non-unicode database for demo purposes to be on my lap...more >>

Hi suppose that I have a set named 'my_set' wich contain 'aaa','bbb','ccc' and I want to alter with an sql command the structure to get 'ddd' in addition. How can I do that?...more >>

Hi, I have a simple 'user' table and an ASP form that connects to it. I want users of all languages to be able to type in their registration info, in their own language, store it that way, then have another page that displays it. Are there any specific settings I need in SQL Server to handl...more >>

We are using Merge replication with clients from remote offices (SQL 2000, sp3). Recently, I have had a problem with users who are replicating, and they shut down their laptops. The connection never dies, and I end up with major blocking issues related to the "orphaned" spid. The tables that ...more >>

Hello, I try to shrink DB, in "Shrink action" I mark "Truncate free space from the end of file". After press "OK" button I see such a message: "Error 8985: Could not locate file "file_name.dat" in system. DBCC execution completed. If DBCC printed error messages, contact your system administ...more >>

I have several *.sql files with schema/data changes to be applied to our current database. Is there a way to create a TSQL script that could be run from the SQL Query Analyzer that would sequentially call the *.sql files? i.e. call schemaVersionCheck.sql call addFieldToLoanTable.sql ca...more >>

Hi there, I'm a little stuck and would like some help I need to create an update trigger which will run an update query on another table. However, What I need to do is update the other table with the changed record value from the table which has the trigger. Can someone please show me ...more >>

Please, explain me, why INSERT / UPDATE / DELETE with global tables and EXEC (any modifications of the global tables) are prohibited in the user-defined function? On my point of view UDF is the same as SP but it has quite better syntax. It is very comfortable for me to do SELECT from UDF. UDF ...more >>

I am trying to store the turn out of a stored procedure in a temporary table to make filters. I do it of the following way: SELECT a.* FROM OPENROWSET('SQLOLEDB', 'servidor';'user';'pwd' , 'EXEC VERT_PERIODOS_CCV ''07/06/2003'',''31/12/2003'',''49'','''',''1'','''','''',''''') AS a but it ...more >>

I am a little stomped and wandering if someone might have an idea how to go about doing this. following on from this guide http://www.4guysfromrolla.com/webtech/031004-1.shtml on matching a comma-delimited string, I would like to expand on this and match two comma-delimited string in a sproc....more >>

I have a client who wants to build a database with a user extensible schema. The client wants to model an entity with about 100 numeric attributes. As time goes on, more numeric attributes will be added. The client wants an admin function that will allow him to add these attributes when neede...more >>

Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril...more >>

I have MS SQL Server 2000 sp? on Windows 2000. I have various jobs set up to run with the SQL Server Agent. I have a job that imports data from an Access database located on the same server. Then later, a job runs that updates some rows. The import job fails with an error saying the source dat...more >>

I am pretty new at this so please bare with me. I have a page working perfectly but I just want to ad a column that will show the time it was entered into the database. I think all I need is the syntax for writing the server time to the TimeStamp field in the record. I have hidden field in the...more >>

We have a linked server, which worked fine, but we have just changed the collation sequence, in SQL-Server, from SQL_Latin1_General_CP1_CI_AS to Latin1_General_CI_AS. Now trying to use the Linked server, we get the following message: "Error 7399: OLE DB Provider 'MSDAORA' reported an error. Th...more >>

I am having trouble linking four count statements. Each statment is a count for a different table. The statements can be seen below; select count (*) from xwrsct01 select count (*) from xwrmvt01 select count (*) from xtrdlt01 select count (*) from xtrvrt01 I need the statement to ret...more >>

What is the best way to import a structured .prt export file from an accounting package into SQl Server?? Tabs, Fixed col length???...more >>

Hello, I was wondering is the following is correct with the network packet size. I've installed sql server 2000 and made some changes in the configuration. Now i've read that sql server's default network packet size is 4096 bytes, but when i look up the advanced tab, in the optionsmenu of th...more >>

Hi, I have two tables such that in each table I need to make sure that column x in table A and column y in table B have a unique values meaning that a user cannot insert a value to column A if its already exist in column B and vice versa. How can I enforce it? Please remember that this two ...more >>

am using FOR UPDATE triggers to audit a table that has 67 fields. My problem is that this slows down the system significantly. I have narrowed down the problem to the size (Lines of code) that need to be compiled after the trigger has been fired. There is about 67 IF Update(fieldName) inside the...more >>

Dear Group, I am tring to use a command that calls the server to fill an adapter, it never seems to get to the adapter, command and the server either times out or does not respond. The timeout is set at 10 hours. I am using Visual Studio to acces MS SQL - Server. I think I have all the r...more >>

I've encountered some strange behavior in a recursive procedure I'm writing for a bill of materials. First let me ask directly if what I think is happening is even possible: It seems like the procedure is not following the recursion in serial order, but in parallel. In other words, after one...more >>

I've been trying this one for 2-3 hours and can't figure it out. I'de appreciate any help or pointers in the right direction. Thanks. Query I need the query to return me all the lottery names and results that have the latest date in the database for that particular game and for the state [AZ...more >>

I had a problem today where I could not see column names and all tables had a _1 after them when viewing a Sql Server view in Enterprise Manager e.g. TableName Company when added to the view would be named Company_1 and the only columns available were 1 which was *(All Columns) After loo...more >>

I got this problem where I need to look up values in two columns from another table. I can get OUTER LEFT JOIN working when looking up one column from a table but when looking up at two columns from a table I can't get the SQL syntax correct. The scenario is A table has definitions for...more >>

Can anyone help me with this strange problem please? I have a stored procedure, with a parameter defined as a uniqueidentifier. The procedure does a select with a number of joins, and filters within the Where clause using this parameter. (@orderHeader_id uniqueidentifier) SELECT * FROM...more >>

We have an application that accesses a particular table, but it is never updated. Without stepping through the complex application code, is there a method of logging when a particular table is accessed?...more >>

I have a Database which is Merge Replicated to 5 sites I need to add 1 new field What is the best way of doing this ? I have previously removed the publications, subscribers, added the field and pushed the whole database out Please advise Regards Paul...more >>

Hi Group... In one of my tables in a SQL-2000 db, do I have a calculated column calling a user defined function. That's not a problem, but now I need to update another column in the same table form this udf. It seems not to be possible in SQL-2000, so how is such thing normally done ? ...more >>

Hello, I need to have a stored procedure, which performs sorting. Something like this: CREATE PROCEDURE procname @sortby varchar(30) AS BEGIN SELECT some, columns FROM some_table ORDER BY @sortby END (of course, i know this won't work, but it gives the idea of what i m...more >>