Hi I have a huge table with over 100million records and on regular basis ineed to delete nearly a million records and insert a million records. Currently I delete indexes before going through the process and recreate the indexes which takes a very very long time. IS there a way to disable index...more >>

I have a MS SQL Server 2000 database that a customer of ours has sent to us for testing purposes. Is there any way to tell what service pack this database is at? A stored procedure of some kind that I can run on it? Thank you, Frank ...more >>

I am trying to bulk insert a text file. The file has fixed-length fields with no field terminators. BOL says that field terminators are only needed when the data does *not* contain fixed-length fields, which implies they are optional -- so I made a format file without any (two consecutive ta...more >>

Hello, We use Informix and MySQL on linux/unix to drive our web application. SQL*Server is used only for backend enterprise applications within the firewall. I am trying to get the management to use SQL*Server outside the firewall. They tell me there are security issues with Microsoft products...more >>

Can anyone point me to a good resource for learning cursors in MSSQL? Thanks Dave ...more >>

Hi all, I have the the problem with logging to MSDE using SQL-DMO interface. On the W2K with LoginSecure=TRUE everything is OK (connect is made without any parameters) But when LoginSecure=FALSE (on Win 98) connect( 'instance_name', 'sa', 'sa_password' ) then it returns error message :...more >>

From the table i want everything highlighted with a * I wanted an SQl expression to look at values in Column 1 (ID), look at the corresponding values in the second column (F1) and select the row with the highest value, and then if there was more than one row for that ID with the same value in ...more >>

I need to replace a portion of a url in a column as a result of changing servers. Is there a SELECT/REPLACE/UPDATE combination query that can do this. The table has close to a thousand entries and would be nice if a query can be set to do this. Tried the REPLACE example in the BOOKS ONLINE bu...more >>

.... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart: DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart: DTSStep_DTSDataPumpTask_2 DTSRun OnError: DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) ...more >>

here's my stored procedure: CREATE PROCEDURE proc @id varchar(50),@pswd varchar(20),@no_go int OUTPUT AS SET NOCOUNT ON SELECT user_id FROM profile WHERE user_id=\@id AND pswd=\@pswd IF \@\@ROWCOUNT = 0 BEGIN SET \@no_go = 1 END ELSE BEGIN SELECT date,date...more >>

I need audit triggers that change columns value in the same record that fire trigger. I need how to do.. Thanks.....more >>

Greetings, I have a SQL server 2000 running on an english win2000 workstation. In a database I have a table where one varchar column is set to polish collation. Regional settings for the system is polish. Data entered in a client application looks fine until they are posted. When reading the...more >>

please help i was trying to delete a specific record on a table but it still shows the same record that should be deleted. i dont know how....more >>

I have a stored procedure that calls some UDF User Defined Functions, the purpose of which is to create row strings out of numerous column strings for matching uniqueIDs. The problem is I need to join that SP with some other tables. The SP I have reads something like: mySPName @myUserID ...more >>

Hi there, I just tried to apply a set of primary keys to a table and after a very long time (like an hour or more) i got this error message .... Unable to create index 'PK_master_data'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a dup...more >>

Hi Everyone, All the very best for 2004!! i need urgent help with this problem, the users are about to skin me alive!! we have an access front end with linked to sql server 2k tables. when a user tries to insert a record into one of the tables it "randomly" returns a generic ODBC error...more >>

Whenever something is inserted to a given table, I want to run some shell commands using xp_cmdshell. Would it be a bad idea to put this xp_cmdshell in the INSERT trigger of this table? I understand that when using xp_cmdshell, the sql thread in question waits until xp_cmdshell finishes what ...more >>

Here's my stored procedure: CREATE PROCEDURE proc @id varchar(50),@pswd varchar(20),@no_go int OUTPUT AS SET NOCOUNT ON SELECT user_id FROM table WHERE user_id=@id AND pswd=@pswd IF @@ROWCOUNT = 0 BEGIN SET @no_go = 1 END ELSE BEGIN SELECT date,date_mod FR...more >>

In VBA I'd use Format(myDateField,"Short Date") to display 1/31/2004 instead of 1/31/2004 10:30:25 AM How can I do this in a stored procedure? lq...more >>

I have a database that is 70GB big. One of the tables has over 350 million rows of data. I need to delete about 1/3 of the data in that one table. I was going to use a simple delete command to delete the unnessacay data. Something like Delete from tablename where columname > '100' Howev...more >>

Still using SQL7. I am wondering how come there is not an Information_Schema view that lists indexes? Information_Schema is supposed to be the safest way to obtain information on metadata, but it appears that the only way to get a list of indexes is with a system stored proc....more >>

here's my code: my $sth = $dbhSQL->prepare('{call proc(?,?,?)}'); $sth->bind_param(1,"asd"); $sth->bind_param(2,"klm"); $sth->bind_param_inout(3,\$no_go, 1000); $sth->execute; print "no go = $no_go\n"; while( my @row=$sth->fetchrow_array){ print "@row\n"; } $sth->finish; Here's m...more >>

In VBA, I constructed the following to update all records in tblmyTable with each records in tblmyTableTEMP having the same UniqueID: UPDATE tblMyTable RIGHT JOIN tblMyTableTEMP ON tblMyTable.UniqueID = tblMyTableTEMP.UniqueID SET tblMyTable.myField = tblMyTableTEMP.myField, tblMyTable....more >>

Hi All, I am new this week to sql*server / query analyzer, coming from years of an oracle / TOAD background. Does anyone have any tools they prefer for writing and executing sql code other than query analyzer? Thanks! Mike...more >>

I have created a DTS that imports a text file to by data table. I get errors when ever I run this since there are fields in the table that are numeric. I understand that I need to create an activeX script to import those fields. DOes anyone have any guidance?...more >>

I have a table which is returning inconsistent results when I query it! In query analyzer: If I do "SELECT * FROM TABLE_NAME" I get no rows returned. If I do "SELECT COL1, COL2 FROM TABLE_NAME" I get 4 rows returned. In Enterprise manager: If I do "return all rows" I get 4 rows retur...more >>

Howdy! Does any of you good folk know how to change window size of the DTS Import/Export Wizard window? Darn thing is very small and inconvinient to work with, no apparent way to change it's size, may be there is a registry tweak or something. Thanks in advance, I.B. ...more >>

Does anyone know if there is a way to determine if the SQL Server installation being run is MSDE or SQL Server Standard/Professional/Enterprise? Thanks! *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

hi, i need some help with a query, also to find out if this is even possible with sql. currently i have a table with the following data: CustomerNumber CustomerBranch 123 NULL 123 1 123 2 221 NULL 221 5 555 1 555 9 125 NULL now...more >>

Hello I would like to convert my Access Backe End to SQL Server, but the first thing I notice when connecting to an ONC Table is that the name is preceded by dbo_ therefore it will not be recognised by the Access Front End. Does this mean that all the references to table in queries and in co...more >>

I have my own function, which I can use: declare @dt as datetime select @dt='20040121 12:22:33' select * from index_gold_iif(@dt) When I try do it from linked serwer: declare @dt as datetime select @dt='20040121 12:22:33'select * from lewiatan.e_.dbo.index_gold_iif(@dt) I have massag...more >>

I have a trigger on a table. I am trying to dynamically log the changed fields on the table to another table, so I am iterating through the bits in COLUMNS_UPDATED() to find what's changed, and getting the column name programatically. This is all working fine. If I do a regular insert comman...more >>

Dear Newsgroup, I am using sql server 2000 over win 2000 server with service pack 4. I have been given a back up of a database (I have tried both from T-SQL and Enterprise Manager) T-SQL RESTORE FILELISTONLY FROM DISK = 'c:\A.bck' RESTORE DATABASE B FROM DISK = 'c:\A.bck' ...more >>

Hi, Maybe this is an easy task, but I'm having a really hard time figuring out how to do this. I'm a complete newbie to SQL Server. I have a database dump file from MySQL that's in .sql format. I'm trying to figure out how to import that into SQL Server 2000 so that I'll be able to manipu...more >>

Hi there, When importing a text file using SQL Server, how do I specify the path to a file on the same remote server that SQL Server is running on? I tried //c:/filename but that doesn't seem to work. Also, once the import is working, how do I write a DTS package to first delete all rows in...more >>

I am working with an evaluation copy of SQL Server 2000 for the first time; my DB experience lies with MS Access. I have a simple table in SQL Server (tblCompany) that has a field called "Ticker." When new company stock tickers (i.e., MSFT for Microsoft) are entered into the field, I'd like th...more >>

What query do I use to list the primary key for each user table, i.e. TABLE | PRIMARY_KEY | Regards, Alan *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Scenario: Table 1 (a id, b name) Table 2 (a FKid, d value) A standard join on a gives me something like: a1 b1 d1 a1 b1 d2 What I want is: a1 b1 d1,d2 I can easily do this with a function or cursor, but is is somewhat slow, and I need to do this a lot and I don't reall...more >>

Hi i have 2 Table first one : Customer with 4 Fields : cst_no,cst_name,total_Debit,tot_credit second one : Transaction with 5 Fields : Trns_no,Trns_Date,cst_no,debit,credit MY QUESTION: HOW TO CREATE TRIGGER FOR UPDATE TOT_DEBIT AND TOT_CREDIT FILEDS IN CUSTOMER TABLE FROM Transactio...more >>

Hello! I'm seeking advice on a rather complex type of query I need to build in an Access ADP (SQL-Server 7). There are four tables: tblPeople ID(PK) PRENAME --------------- 1 Thomas 2 Frank 3 Chris tblInventoryClasses ID(PK) INVENTORYCLASS ---------------------- 1 Car 2 Phone t...more >>

My company stores thousands of images in a SQL 200 database (image datatype). We have a need to manipulate these images as they are presented according to different situations. For example - different logos need to be inserted at the bottom of the image according to the requesting party. Any ...more >>

Dear Group I wonder whether you can give me a syntax example for a SQL Statement. Lets assume I've a table containing three columns ContactID (Primary Key), Firstname and Lastname. I would like to write a stored procedure which returns me the first ten records and increments an outside va...more >>

SQL Server 2000, MSDE I have a table containing simple financial transactions. The 3 primary = fields are: TranSysID INT(PK) TranDate DATETIME TranAmount DECIMAL TranAmount contains both positive (Credit) and negative (Debit) values. I need to perform a query that ret...more >>

What I want to generate is a list of all users and if they belong to a site or not. Below is what I have for tables, sample data and the results. I am a newb with writing my own SQL and could use a few pointers and help. For example if I have the following data: Table: [Users] UserID Use...more >>

Hi, Does anyone out there know how to do this? I've been banging head for awhile now trying to answer this seemingly simple question. tia, Mike...more >>

TENGO UN SUPER PROBLEMA, HICE UN DELETE A UNA TABLA EN SQL SERVER 2000 QUE NO DEBI HABER HECHO, COMO PUEDO DESHACER ESE DELETE....more >>

The previous post regarding dependencies reminded me of another bugger I come up with from time to time. SQL Server allows us to rename objects via a system stored procedure (I usually use an ADP front-end that presumably calls the same procedure), but after the rename, I find singnificant stran...more >>

Is it possible to rebuild dependancy information for objects in a database? I can hardly ever DTS a database across to another server because something will fail with the message "invalid object name <name_of_a_view>". This happend to me just now on a database with just 12 tables and 4 view...more >>

I have a stored procedure using Convert where the exact same Convert string works in the SELECT portion of the procedure but fails in the WHERE portion. The entire SP is listed below. Specifically, I have a problem with this portion in the WHERE clause: DATEADD(Day,tblMyEventTableName.Reminde...more >>

I notice when I construct views using Enterprise Manager that "dbo." is automatically inserted before every table name so my table names appear as dbo.tblMyName. However, I have created a hundred or so views using MSDE/Access2K where I just refer to the tables as tblMyName. Am I going to run in...more >>

I've been testing a variety of database schema migration tools. Our company purchased Embarcadero Change Manager a while ago but we've been less than satisfied with the results. We are looking for a tool that can compare a source/developer database with a target/client database and then make ...more >>

To start off i have a database table that consists of "profiles", another that consists of "users" and finally one that consists of "exclusions", these are defined in the DDL below. I am trying to select all the profiles minus any exclusions that are set up for that user but i keep getting dupl...more >>

I created a database on my laptop, and because I was hoping to keep the data, I tried detaching it and copying the files to my server and attaching the database to the server. I quickly noticed that some of my SPs didn't have rights that I was expecting them to have. One SP executes a line of...more >>

Dear Group I'm having a very weird problem. Any hints are greatly appreciated. I'm returning two values from a MS SQL Server 2000 stored procedure to my ASP.NET Webapplication and store them in sessions. Like This: prm4 = cmd1.CreateParameter With prm4 ..ParameterName = "@Sec_ProgUser_G...more >>

using osql to apply SPs in mutiple threads Hello, I got a weird problem when I was using osql to apply scripts for msde database in multiple threads mode. Sometime 2 sps were missing during the whole apply process, sometime not, and seems like only those two SPs met the problem. No error was...more >>

Hello, I am having trouble with a production db server that likes to gobble up memory. It seems to be a slow burn (maxing out over about an 18 hour time frame, before pegging both procs on the server and bringing everything to a standstill). After viewing the trace logs, it appears that all...more >>

I've developed years ago a small business application (about ten tables, basic relationships, 10 forms, 3 reports a few queries) with Borland Paradox 7, most of it developed with wizards and little ObjectPAL coding. Old'n'crappy. Seriuosly thinking rewriting it from scratch using MS SQL server...more >>

Hi , I am looking for meta-information about the return recordset of a stored-procedure. The procedure returns a resultset that contains columns of more tables joined together. In all tables, I use, there is a Record-Creation-Timestamp-Attribute. When joining two or more tables these attribut...more >>

This seems simple, but it keeps tricking me. Could not get the proper result. Thanks in advance! TABLE1 App_ID Business_Unit_ID PROFILE_ID 1 001 AA1 3 001 AA1 TABLE2 - SUPERSET App_ID Business_unit_ID 1 001 1 002 1 003 1 007 2 002 2 003 3 0...more >>

Hi, I'm trying to grab records with a priority over those marked as yes (-1) in a certain field. Trying "select id, min(bit) from tab group by id" does not work, as the min operator doesn't work on bits. Is there an alternative to my query? Many thanks, Chris ...more >>

Hi everybody, I was looking for an equivalent ORACLE INSTR Function in MSSQL but I don´t found it and I don´t know if it exist so I must to write it and this is the code. Maybe it will be helful to you: /*************************************************************************** Descript...more >>

Gents, I have come into a system that uses a secondary table to generate (for want of a better word) Identities. eg create table myidents ( name sysname not null, ident int not null) create procedure getnextident @table sysname, @ident int output as begin if not exists (select top ...more >>