What is equivalent to instrRev() function of VB-6 in Sql 2K ? For example: Retrieving last dot position from 2.2.5.4.3 which is : 8 Best Regards, Luqmn ...more >>

I have a grouping question... I have two tables - State and Region. They are related by the regionID I have a VB form that has a dropdown box. I would like to have the region and state listed in the box like so: Region1 -State ... -State Region2 - State ... - State Region3...more >>

Another issue arises under my favourite topic of discussion! What solutions do people have for creating a "natural" key that is both meaningful, and unique. Consider a situation where people create "things" that will be stored as unique entities, but where the user themselves have not predefin...more >>

Hi, I have a linked server admin-new, is there a way to create and tables in the linked servers? I tried: drop table [admin-new].master.dbo.tempIDs Got syntax error. Thanks in advance ...more >>

We discovered yesterday database corruption and with help from my previous posting we checked the database. A few tables do have problems and some other errors are reported. I created a backup of the database and restored it to a different database to test repair and a few hundred records are ...more >>

The below code gives me this error. However, the syntax checks out OK in Enterprise Manager. Can anyone enlighten me as to what this error means? thanks! ----------------------------------------------- Microsoft OLE DB Provider for ODBC Drivers error '80040e07' [Microsoft][ODBC SQL Server Driv...more >>

Hi all, I have a column which looks like this: Status ID ========= 100 100 100 200 100 300 100 I want the output to be: Status ID ========= IDLE IDLE IDLE RUNNING IDLE SYNCRONIZING IDLE Any ideas on how to write the Select statement? Thanks. -- Lucas Tam (REM...more >>

At work we have a stored procedure that takes selected data from a job record and turns it into an electronic invoice ( a text file), to be sent via an EDI mechanism. The file is just created and placed in a folder on the network. The extract has worked ok for 18 months but all of a sud...more >>

Hi, I need to update the data part of a datetime field without changing the time part of the field. Can anybody tell me an easy way to do this? I've tried converting to a string, extracting the time, then building a new string (with original time but modified date) which I pass back t...more >>

Each row for a table in SQL server can hold up to 8060 bytes data. A client give me a list of columns with all definitions, I need to create a table by CREATE TABLE statement. CREATE TABLE statement may fail with a message indicating I am over its 8060 byte row size limit. Assume all ...more >>

I have a select statement that can return many rows that have a single column with a duplicate value. I'd like to limit it to a maximum of two rows with the duplicate column. Example: Currently: TableA: Field1 Field2 ------- ------------ 1 value A 1 value B 1 ...more >>

I have a trigger on update, but i need to know if a specified column of the data type TEXT was changed or not. Since it's TEXT, i cannot use '=', so, is there any other way to do this? Thanks! -- Regards, Kristofer Gafvert http://www.ilopia.com - Useful help for Windows Server 2003 Rep...more >>

Hi, I want to write a batch file which essentially calls the osql utility = (osql -i xxx.sql....) to run a SQL script. How the script can be written to accept run-time parametrs from osql? = How the script is to be called with parameter value from osql ? The equivalent thing which I have do...more >>

I'm trying to use a format file for importing in a data file that has fewer columns than my table I'm importing to. I've gone through BOL, and it looked really simply to set one up to do this, but I keep encountering problems. Here are the steps I'm using... 1. Export file from Excel to ...more >>

I have created a stored procedure 'CopyCategory' with prefix 'Sysdba' But why can't the following sql sentence not find it: if exists (select * from sysobjects where id = object_id(N'[CopyCategory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [CopyCategory] GO It does n...more >>

Hi Is there any SQL Server encryption to hide the contents of fields That is I do have a password column and I want to hide/encrpt it from users to see it. Is there any way to do it --like turn encrption ON for a column ??? Is there any way SQL Server help us for this ? Thanks in advance ...more >>

Hi I am trying to run a stored procedure from an access project and have come across the 10000 record default limit. I have had a google about on the subject and know that if I set "MaxRecords" from 10000 to 0 it will update my entire record set, and fix my problem, however, I cannot for the l...more >>

dateadd(dd, number_field-1, datetime_field) doesn't work. See revised example: I have the following in a table: datetime_field..........number_field 2003-06-03 00:00:00.000...3 2003-06-03 00:00:00.000...4 2003-06-03 00:00:00.000...5 I want to select from this table and have the number fie...more >>

Hi, I need to retrieve an specific value from a column whose name it's stored in a variable (@cName) I want the result of the query to be store in the variable @NewValue This is my query (it's simplified from my original query, but it has to have this format): select @NewValue = @cName F...more >>

I have table1 that has a Foriegn Key to table2, using the sql dmo i am able to get the script from Table1 to create the Foreign key with Table2 no problem Set objTable = objDatabase.Tables("table1") strSQLScript = objTable.Script(SQLDMOScript_DRI_ForeignKeys) strSQLScript Now = "ALTER TA...more >>

Hi guys, I have two select statement, each will return one cell select1: Select2 a b If I use Union, I will get Select1 a b I want so that I can get the result Select1 Select2 a b in the same table. Any help is appreciated. Jun ...more >>

I am looking to upgrade a database from SQL 7.0 to SQL 2000. The application that uses the database is written in VB 6.0 using ADO. Will I have problems with SQL that used JOIN syntax "where emp.empid = dept.empid"? An associate read that these must be changed to ANSI syntax using INNER, O...more >>

Hi, How do I set a field in a table in a SQLServer database so that it increments automatically everytime I do an insert. For example I have a table with the following fields ID Primary Key int TYPE int VALUE ...more >>

I want to write a T-Sql script that will cycle through all of the tables in a database and write the number of records in each table. I have the cycling and writing fine, but when I try to execute sql with a variable as the table name is doesn't work. Can anyone tell me how to accomplish...more >>

Hi folks, I need your help on find articles or magazines that proven using SQL Server2000 is more cost effecitve and easier to maintenance than DB2 databases. My company thinking to convert all SQL Server to DB2 databases and we all oppose that decision. We are using VB.Net and ASP.ne...more >>

I would like to set a filter such that if Reads, Writes and Duration are all zero, the event shouldn't be logged. I can easily do this for any individual field, but I want to do it for all three fields combined. Any way to do this? Thanks. ...more >>

Have a table with 2 columns: ParentItem and ChildItem. The parent and child relationship may go many levels deep... ie ChildItem may be a ParentItem on another row. Would like to query and get resultset that lists in one column all of the ChildItem's throughout a relationship... ie breaks ...more >>

I have the following in a table: datetime_field..........number_field 2003-06-01 00:00:00.000...1 2003-06-01 00:00:00.000...2 2003-06-01 00:00:00.000...3 I want to select from this table and have the number field represent the day. For example, I want: datetime_field..........number_field.....more >>

What's the different between ad hoc and regular query or script? Thanks! CM ...more >>

I am trying to join three acess tables in a recordset and trying to acess their their contents. for ex . if we have a customer table having fields id,name and another table Payment having fields id,amount. now if rst is the recordset then rst!id,rst!name,rst!amount does not work then h...more >>

Hello All, I have a problem with the IsNumeric function. I am trying to convert the values in a varchar column to float only when they are numeric, so I do the test using IsNumeric. From the BOL: "ISNUMERIC returns 1 when the input expression evaluates to a valid integer, floating point nu...more >>

How to get the list of columns which compose the PK (not the clustered index) on a table? I want to get it from system tables. Thanks....more >>

Hi, This has confused me and I have not been able to figure out what is happening. I have a simple application that is developed in Visual Studio .NET and I used the wizard template to create a SDI App with ODBC support (no file support). The application allows a user to insert records to ...more >>

Suddenly in one database we have a lot of errors, it seams some things are corrupted. I tried to start maintanance / database repair, but this fails too. When selecting in Query Analyzer a range of records from a table I get the following message: Location: p:\sql\ntdbms\storeng\drs\includ...more >>

Hi to all, I have VB application calling a SP. It works when: --return this to application SELECT * FROM tblVariance WHERE VarianceID = @VarID --update table UPDATE tblVariance SET NetworkID=@NetworkID WHERE VarianceID = @VarID return 0 I would like to see update values: I pla...more >>

I feel a little stupid sending this, but I must be having a mental block or something. I am trying to do a Select Distinct on a column from a table, but keep getting back multiple results. Below is my query and sample results. QUERY: Select distinct(aip.policynumber) as PolicyNumber f...more >>

Hi Everyone! I have an issue that I cannot seem to find a solution for: I have one table that holds information about parts installations. The parts have a serial number which is alphanumeric. I have another table which has descriptions about the particular part, indexed by this same ...more >>

Hi all, I have seen a few messages about this in the archives, but none provide a comprehensive answer. Basically, I want to use CDONTS mail support from within SQL server to send HTML formatted email that could potentially be longer than 8000 characters. (I have done it successfully with s...more >>

This is probably a bit of an obscure one but I am trying to set up a call from a telephony scripting language to an extended procedure calling e.g. xp_hello(?) from the samples and one of two things happen : 1) I call it using the specified number of parameters (one being an output parame...more >>

Hi, I have a pretty simple problem for a SQL programmer regarding GROUPINGS: I have two tables tblShoppingCarts and tblProducts. The first table stores all shopping cart data. I would like to retrieve all this data (cart and all related product information) BUT need to GROUP BY tblShoppingC...more >>

I have a set of queries all within a single transaction. I would like to insert logging information into another table. However, I would like this to be done outside the transaction, as I don't want it to be rolled back if there is an error. Is there a query hint / option that I can use to tel...more >>

#1 on my wishlist for future mssql support: The ability to correlate on either a) table-based udfs, or b) parametrized views. I would even be happy with c) some sort of t-sql macro preprocessor or possibly even d) the ability to return multiple fields from a subquery, or e) aggre...more >>

Attempting to add a table from another server without copy/pasting it to my server. I have written the following: { SELECT * FROM DYNAMICS1.LiveFinancials.dbo.livCalendar livCalendar_1 } It seems to work but it won't save, instead showing the following error message: ...more >>

Hi I have to specify in a table certain values for: Minutes duration spent on phone-eventduration, the eventdate-which date, and event time- exact time I was wondering in your opinon which are the best data types to use for these values ...more >>

I would like to have a complete tutorial or documentation for SQLlanguage in SQLServer dialects Anyone knows about it?...more >>

Hi, I'm getting the following error when trying to fire a trigger to a different server: The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction. [OLE/DB provider returned message: New transaction cannot enlist in the s...more >>