Anyone know where I can find example scripts that will compact, recover, re-index etc... I am using vb.net, I would need to initialize it from there or from my app that is... Thanks, Anthony Nystrom ...more >>

I have a problem with fox pro tables that are connect to access databases. Two different locations of the same program has data files that are named the same. I am trying to link to the two different files with access 2000 with a link to the tables. When I try to build a table using the two li...more >>

I have a table of Customers and another table that contains orders from the last year. I want to delete all those customers who have NOT placed any order in the last year (i.e. that cust number is not in the orders table). A given Customer number may be in the orders table more than once. Is t...more >>

Hello all, I have a little challenge that I've been struggling with. The scenario is below. Note that the source table has over 2 million rows. The first solution we tried copied the table to a temporary table and then used a cursor to iterate over the rows and delete or modify from the tem...more >>

I have the following stored proc which is giving me an Error converting data type nvarchar to numeric error. Can somebody please help me trace the problem. ----------------- Stored Proc ----------------- CREATE PROCEDURE spAuthoriseLink ( @LinkID Numeric, @URL nvarchar(255), @Link...more >>

I have a need to cast a float to a decimal using different scale values: In my Select, I have CAST( th.price AS DECIMAL( 30,CAST(p.qty_decimal as int) ) ) as price I have tried many things but I alway get "Incorrect syntax near 'CAST' ". qty_decimal contains values from 0 to 8 Is it...more >>

I've a member table, It stores address fields also. I need to select the oldest person from each house. I pulled the data bases on group by and not able to get the primary key, how an I get that key when using group by. I tried alias also, but output is not correct. ...more >>

Hello Everyone, I am fetching through a table, tb_1, to get all the group names to which a user belong. I need to save all the group names i get back into ONE VARIABLE and separate the group names with either a space or a comma. Using SQL Sever QA 2000,I am running the code shown be...more >>

Sorry if this is a basic question but I have spent the day looking and can not determine how to do this. I want to run a query from Sql server as a job. That updates a table based on fields in a second table. That part I have done without problem. My problem is that I also want to insert ...more >>

I have an ASP.NET app that has been working for a while now but just started reporting errors when attempting to execute a stored procedure on SQL Server. There have been no major changes since the app worked - 2 columns were added to the table I'm attempting to modify with the stored pro...more >>

is it possible to use dts to load a different .txt file into different tables of the same database? thanks in advance, trint...more >>

Hi, I have question about multiple language handling. I want to store US-English and Japanese in my database and create value column and lang column to store the value and lang value. Since Sql Server instance only allows one collation, which collation I should use, SQL_Latin1_General_CP1...more >>

All- Does anybody know the maximum size for INPUT or OUTPUT varchar stored procedure parameters, or where I can find it? I looked all over the Help and couldn't find an answer. thanks! ...more >>

Hey all, I came across a SQL: select * from customer where Name <> N'Tom'. What does "N" mean in this case? Thanks David...more >>

I have a problem in that I need to shuffle a lot of data from the client to a table in my database. Previously, I was copying the data in a text file, then using BULK INSERT to accomplish this. However, I can no longer use this method on our new server, as the DBA doesn't want to add all th...more >>

Hi, I need help to update dates in a table below. Here is a fragment of the table: emp_benefit_id benefit_id coverage_end_date -------------- ----------- --------------------------- 168 101 2003-12-31 00:00:00.000 175 101 2003-12-31 00:00:00.000 202 ...more >>

I'm a new user of SQL Server and Analysis Manager. I'm wondering where it is that you can write MDX or SQL. Maybe I could get some help in arranging data, because I feel like I'm hacking my way through. It's probably because I a newbie at this. I have this table in an Access Database. I...more >>

Hi, For those of you that are familiar with MS Access... when you have a query and you use a group by, Access has a function called "Last". What this does is give you the last record of a group of data. For example.... ID Date_Column 1 ...more >>

Hi Freinds, How can I backup and restore DTS Packages on my server? Thanks, Pat ...more >>

I have a large table (1 million rows) which I horizontally partition into 4 sets of contiguous rows (250K rows each) by PartitionID. I want to do a complex calculation with a function call to update one column in this table. I've created a stored proc to do this update. It accepts a P...more >>

Following an update query I wish to be able to reference both the @@error variable and the @@rowcount variable, e.g. update test set test.col1 = 'x' if @@error <> 0 print 'error ' + convert(varchar,@@error) else print convert(varchar,@@rowcount) + ' rows were...more >>

I am curious...EXISTS function returns true if there is a least one record in the selection. Does the SELECT statment gather all records before the EXISTS function evaluates whether there are records in the result set? In other words... Is this statement any more effiecient IF EXISTS ...more >>

I have an orders table with an orderid, custid, and orderstats field. The orderstatus field can be 'c'. 'i', 'x', 't' or 'e'. Customers can have many orders with different status. How can I get all of the customers who have only orders with 'e' status? The following gives me all customers...more >>

Does anybody know a way to fix this? I've tried all the statistics-related techniques. DBCC SHOW_STATISTICS shows the correct number of rows, but sp_spaceused shows 0. Any ideas (other than editing the system tables)? Laurie ...more >>

What is a good general purpose reference book on T-SQL syntax and usage? Tom L. ...more >>

I have a table - SELECT COUNT(*) returns the correct number of rows, but sp_statistics gives Cardinality=0, sp_spaceused gives Rows=0. How can I fix this? Laurie ...more >>

Hi, Run the following statement declare @d datetime select @d = '2003-07-25 17:19:46.542' select @d It produces!! 2003-07-25 17:19:46.543 Does anybody know why there is a one millisecond difference when I select it to be 542 and it comes as 543? Thanks, Panos, London....more >>

Does anyone know how to update an id field(pk) with relationships without removing the relationship? In other words force an update to field with relationship. Any assistance would be greatly appreciated. Thanks, Ravi...more >>

Hi All, I want to find out the time and which user(e.g. sa) has modified the database (Table, view, SPROC, etc) schema in SQL server 2000. as well as any insert/update/delete on tables. In short like IIS LOGS is there any way to find out Who and When and What did to SQL server. Can any...more >>

Q1. The table structure is ID, Plant, Inc, TagIdentifier, Desc. Id is the primary Key. I want to update the Tagidentifier column with new values based of inc field value in this same table. I wrote: update Tablename set tagidentifier = (select newtagname = case When inc = 0 the...more >>

The following SQL works, but the SQLbelow that does not. Is there a way to do the second SQL??? SELECT DISTINCT InventoryRecord.codesID, MachineCodes.CATNUM, MachineCodes.DESCRIPTION FROM InventoryRecord INNER JOIN MachineCodes ON InventoryRecord.codesID = Mac...more >>

Hello, Is there a better way to remove time part from datetime then CONVERT( DATETIME, FLOOR( CONVERT( FLOAT, MY_DATE))) ? Better means faster, shorter, etc... Regards Miroo...more >>

HI, I think I might be having a brain fade can anyone help? I need to update the classification_id column in table1 with new id's from table 2. table 1 classification_id 2 3 4 5 table 2 classification_id | new_id 2 1 3 4 4 ...more >>

hi, i'm a new sql server user so plz pardon my ignorance if wat i'm about to ask makes u laugh or frown at the simplicity.. ok here goes, i'm using mssql8. I've created a 2 temp tables in a stored proc - the first table returns Debit order Rejects and the second retuns Debit orders Receiv...more >>

I would like to have a stored procedure where I pass in parameters of fields or even tablenames and fields as parameters for the select statement.... Select Param1, Param2,Param3 from Param4? Is this possible? I tried this just as above, but it didn't work. Curious if anyone knows ho...more >>

hi, again, can anyone help me with the below error msg when users update records from the access application: Server: Msg 306, Level 16, State 1, Line 1 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. the main column that fa...more >>

Hi, I have a variable decimal(9,4) and i want to convert it to decimal(9,2) but i don't want to round the number, i want to truncate it, like this: declare @a numeric(9,4) set @a = 100.1399 print convert(decimal(9,2), @a) This results in "100.14" but i need to get "100.13" I know i c...more >>

HI, Is it possible to programatically set a DB to Read Only? I have a job which archives data daily at 3:00am and stores it in an Archive DB. I'd like this DB to be Read Only so that no historical data can be accidentally deleted but of course I'd like it to be reverted to non Read On...more >>

Is it possible to create a trigger that fires when a new field is added to a table in the database? Any Ideas would be great. Thanks...more >>

I want to create a new database in a stored proc which includes table + other procs. Creating the database + tables is no problem, but does anyone know how I can create the procs? Say I'm running the creation proc from master and the target is database TEST1 My sample code so far: DE...more >>

Dear Friends With the advice form the friends of Newsgroup. i am tring to setup thh link server from my remote office. I have compteted the following steps. On Remote Server: 1. New fixed IP for the server running SQL server. 2. Open port 1433 on Firewall On My server where i am m...more >>

Hi, I have a question for you, I'm new to SQL programming so I'm not sure if this is possible. I want to setup a trigger that will fire if a new field is added to a table in the database. Any advice would be greatly received. Thanks ...more >>

I am looking to buy a Sql Server 2000 book, and was wondering if anyone had a favorite Sql Server 2000 book they would recommend. I'm looking for a book that details things such as backing up, security/ log in, T-SQL, and stored procedures, etc. Thanks for your insight, D. ...more >>

After moving an ASP3 site from one hosting provider to another, whenever I execute a sproc that selects a uniqueidentifier (in XML format) ..... the curly brackets around the uniqueidentifier are missing. This didn't happen on the previous site (same code). For example: <%=objLoopNode.geta...more >>