Assume a database (e.g. Oracle) field is declared as a) NUMBER (length=10) b) NUMBER (length=26) c) VARCHAR2 (length=1) d) VARCHAR2 (length=50) e) DATE How do I retrieve the contents from these resultset fields and put it into a (numeric) java resp Date field? For other field types it ...more >>

Hi folks I have copied a .mdf file from machine1 (without detaching it) Can I attach it to a SQL Server which is running on a different machine (machine2)? If not, is there anyother way to restore the database with the .mdf file (without using the backup)? Thanks in advance. *** Sent via ...more >>

I want to write the sql query for displaying sequence number in the result... E.g. SeqNo Col1 Col2 ==== === === 1 aa aa2 2 bb bb2 3 cc cc2 The firlst column SeqNo is not the physical colum...more >>

Hi, Can I ask you a simple SQL query question? This datetime data type stores records like "1/25/2005 11:37:25 PM". Is there a SQL query to format it like "1/25/2005"? Please let me know. Onnuri ...more >>

Hi all, I have upgarded db from sql 7.0 to sql 2000 and I notice that load of records have gone missing in sysprotects table ( something like from 5000 records down to 60 records only). As a results, permission setting does not appear in objects permission management in Enterprise Manage...more >>

I need help writing a SQL query that will return the Part_ID, Part_Num, and Part_Rev from a Parts table where I get returned all three values but only the highest Part_Rev for each distinct Part_Num For example, given Part table with the values below: Part_ID Part_Num Part_Rev 1 ...more >>

I want to use a variable inside a query the following way: Declare @Idx as Integer Set @Idx=0 SELECT @Idx = @Idx + Col1, @Idx AS SumSoFar FROM Tbl1 The Error I get for this query is: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operati...more >>

Hi Group; I am trying to update a field in a table datatype for a SP with code that works fine in Query Analyzer by itself, but not in the full procedure. I need to extract the first few numeric characters ( the number of characters varies by record) from a varchar field before a hyphen '-...more >>

I have a table storing these data COL1 COL2 COL3 ================== CN 2003-06-15 CAR CN 2003-06-16 BUS CN 2003-06-20 BIKE US 2003-07-20 FISH US 2003-07-22 BEER I would like to write a query that extracts the latest COL2...more >>

Hi i have column with this values : aa-bb-cc-dd ss-aa dd-aa-ee How can i find last '-' char index i need retrieve this result : dd aa ee Thank you ...more >>

Hi Group I want to divide one column by another column and stored that value into third column. Example : Column A, Column B, Column C I want to divide Column B by Column C and stored that value within Column A Basically Column A is % Column B < Column C ( always ) When I use divi...more >>

why does this Select * from cv_Volunteer_Section where CONTAINS(listingdescription, ' "a/p" OR "a/r" OR "bookkeeper" OR "accounting clerk" ') return this error? Server: Msg 7619, Level 16, State 1, Line 2 The query contained only ignored words. Is there away to prevent the error by editi...more >>

I had posted an earlier question about the CONTAINS predicate that uses the full text search. The entire process of comparing a column with a keyword variable using the contains predicate was not only taking too long (cursors) but also breaking the stored procedure when noise words were enc...more >>

Hello World, I'm quite new to MS SQL Server programming and I am wondering when should I connect and when should I disconnect. At the moment, our application connects to the server at start up, and disconnects when it's shut down. The users normally let it run several hours, maybe even d...more >>

From within a stored procedure, I want to call another stored procedure with the results of a SELECT statement. The SELECT could return multiple rows, so I want to call the other stored procedure once for each row of the result set. For example, ALTER PROCEDURE StoredProc1 (@param1 int...more >>

Hi, I am not sure if I have understood what is happening with the new SQL server 2005. Will we be using .Net classes instead of stored procedures? Will I be able to use an array or list type of parameter for my queries with SQL server 2005? If yes, how would I do this? Thanks, M...more >>

Can someone explain to me the main differences between these and why you would want to use one over another? Benefits? Downsides? Thanks....more >>

Hi all, IS it possible to create Instead Of Update, Instead Of Insert and Instead Of Delete triggers on a view that uses UNION keyword? Thanks. ...more >>

Hi, How to convert and transfer ANSI string data from FoxPro table to MS SQL Unicode table. Data in FoxPro are in 1251 codepage (Russian Windows). Now result is like "ÊÎËÜÖÀ ÊÎÌÏÐÅÑÑÎÐÀ 100ìì ".The ODBC driver does not performs the conversion from ANSI to Unicode. Help. ...more >>

What would be the best function to do an implied if statement in SQL? ex. if column A = 2 then Column B/Column C otherwise Column B Thanks for your help again!!...more >>

I have a stored proc which performs the following delete and insert statements. If any insert statement fails I want the transaction to fail and rollback. Is this the correct approach? Many thanks Rippo BEGIN TRAN delete from dbo.tblLocalised insert into dbo.tblLocalised (lDataTyp...more >>

I am importing data from one database to another using DTS. During the importation process, I get the following error message: INSERT statement conflicted with COLUMN FOREIGH KEY constraint. “FK_foot_Gen”. The conflict occurred in database ‘Sales’, table ‘Gen’, column ‘ID’ ...more >>

Hi there. I have many needs. One of these happens to be a requirement to set up a scheduled task which calls a simple stored procedure and then sent the resulting record set to a file - preferably in .csv format. Anybody any ideas how to go about this? My stored proc is very basic (selec...more >>

I'm using ADO.Net to return a set of records from a SQL Server, which I then process to create a text stream ultimately used as the InnerHTML property of a browser control in a C# project. Please don't ask why I'm doing it that way. I know it's stupid, but customer requirements dictate that it...more >>

What is correct syntax to alter a few columns in one query like this: ALTER TABLE dbo.Inventory ALTER COLUMN Fld1 varchar(50) NULL, Fld2 int NULL I get "Incorrect syntax near ',' " error message on this query. Thanks. ...more >>

Hi all - I am trying to rebuild a website abandoned by the builder and an SQLServer (I think 2k) db is used under it at the host. I can get at the db by using Access/ODBC with a URL but the migration facility seems only to let me get at the tables. There are about half a dozen SPs referred t...more >>

I was surprised by the above statement as I had assumed that SQL Server actually performed better if indexes were used on those colums in tables that are a part of a join, where, or aggregate? I also assumed that Sql Server would use these indexes which actually translated to better perform...more >>

I almost exploded today: select len(' ') returns 0!!!!!!!!!!!!!!!!!! The equivilant Oracle Function (length) returns 1. Could someone please tell me how to get the number of characters in a varchar field, INCLUDING leading spaces. Thanks! Forch...more >>

Is there a way to place a where clause into the select line? I have a query that returns a dataset, within this query I want to count the number of certain values that exist. My example code: SELECT dbo.LeadClient.ClientID, dbo.LeadStatus.LeadStatus, dbo.Accomplish.Accomplish DATEDI...more >>

Hi - I'm new to SPs, and wondered if anyone could help with this SP. It takes a username, email and password - and should register (add) the details to the customer table. But first, I would like it to check if the email address already exists in the DB - by performing the first SELECT statem...more >>

I am wondering which problems to expect when running both 2000 and 2005 on the same machine? Could they communicate to one another? If I can use extended stored procedures written for 2000non 2005?...more >>

I have the following statement : CONVERT(INT,( CONVERT (VARCHAR(4), SUBSTRING (terminatingcountrycode, PATINDEX('%[^0]%', TerminatingCountryCode), LEN(TerminatingCountryCode)))+ CONVERT (VARCHAR(4), SUBSTRING(TerminatingIDDDCityCode, PATINDEX('%[^0]%', TerminatingIDDDCityCode), LEN(Terminati...more >>

I would like to loop through or query all the tables in my database and determine their last modification date. Is there an easy way? Thanks, Dave ...more >>

does anybody know of query that will return the table def's for a given Table ie FieldName, FieldType, Length Allow nulls...more >>

Here is my newbie question o' the day: So far my SQL queries have been only a few lines. I write some VB code to send the SQL to the database and retrieve the data in an ADO Recordset. But now I have a query that is very long, and I wonder if there is some alternative way to send that que...more >>

Is there any way to convert a text field to a number field and drop any data that has an alpha character in it? I am able to override the message in Access but I am not sure if you can in SQL. For ex. "1234AB" Don't want this data "123456" Want this data Thanks, Lisa...more >>

I believe there is a bug with the syntax checker of SQL Enterprise Server 2000 (Version 8.0 with service pack 3a) regarding the check of variables declaration. It only checks if a declare statement for variable with the same name appear more than once without checking the logic of the trigger....more >>

My question is: I am having problems with a recompiling stored procedure and am trying to pinpoint where it is recompiling. I know that it is not recommended to use temp tables in control-of-flow statements but............if you do and the creating of the temp table does not fit the IF...more >>

I have a table that has a field with sample time that is set up as char(24). I would like to write a SELECT statement converts char(24) to smalldatetime format. Table A Counterdatetime char(24) Please help me resolve this issue. Thank You, ...more >>

Hi! I have this query running well, except that when executing some times, the result change and doesn't start with the urgence24 column order as requested!!! It's really strange. Does somenone can help me or tell me where to find? Does the SELECT INTO with temporary table can make a ...more >>

Hi all, I have the following SQL script: SELECT field FROM table UNION SELECT CAST ('' as ntext) as field FROM table 'field' is of ntext type. SQL Server returns the following error: The text, ntext, or image data type cannot be selected as DISTINCT Can I overcome this pro...more >>

I would like to insert many millions of records into a database. The task led me to ADO, which Microsoft flaunts heavily. I've just looked at ADO superficially. But I can't see many differences between ADO and ODBC. All of the ADO examples use SQL syntax. Does ADO allow for massive reco...more >>