I have field Field1 varchar(50) which may have both numeric or text values. If it is a number, i want to use its value else 0. Something like: SET x = 2.0 * IS_NOT_NUMBER(Field1, 0.00) --- WHERE IS_NOT_NUMBER() will work similar to ISNULL() ...more >>

Hi Friends, I am it's happening to most of you that while a query is running, let's say for 4-5 min, you don't have anything else to do just waiting till it is finished. most of the times it's becoming very boarding and frustrating to wait, specially if the data size is big enough to cause th...more >>

Hello, I'm an Access/SQL novice and I have an sql problem: I have the following table Price: FuelID PriceDate Price LPG 1/05/2007 0,2 LPG 13/05/2007 0,21 SPS 2/05/2007 1,1 SPS 15/05/2007 1,08 And I have to make the following query: FuelID PriceDate_from PriceDa...more >>

I have an equipment table. The table contains both positions and assets. Each asset should have a position attached to it. I can easily query the table for assets not attached to a position, but I cannot figure of the syntax to query a position that does not have assets attached. A position c...more >>

Hello, I loaded Date column data, which is in YYMMDD format, from flat file to a SQL database table as DT_WSTR. Now I want to convert this column to datetime format YYYY-MM-DD. How can I do that? Thanks, GB ...more >>

I have a SQL2005 database with an Access database with linked tables as an interface. I have a form which is bound to a database table with editable fields. When a field is backspaced out completely, a NULL is stored in the SQL database for that record - as expected. However, I cannot use NU...more >>

hey all, i just deleted a record out of my sql 2005 database and was wondering if i can get i back somehow. thanks, rodchar...more >>

I'm looking for a way to write the following query. It doesn't work as is because of the where clause, but it shows what I am trying to do: SELECT c.last_name, (SELECT Count(*) FROM Planner WHERE client_id = c.client_id) AS planner_count FROM Clients c WHERE planner_count > 1 AND c....more >>

Hi, Does anyone know of a script I can use to remove the identity of a field instead of manually going in and removing it? Thanks, Kevin. ...more >>

Hello, I have to split from a table with an ntext field to entries in a new table Example: Table1 has 2 field:id-field,ntextfield example content id=1 ntextfield (in this field are about 20 lines ) 1;44;54 2;61;21 3;45;38 ....... 20;235;55 The values from this ntextfiel have to be c...more >>

I turned on Audit Events for Event type 40 SQL:StmtStarting. I had my program issue a command through ADO to SQL Server 2005: INSERT TERMCODE (CODE, TITLE, TC_KEY) VALUES('DC', 'Discharge', 34) I expected to now find that I could query fn_trace_getinfo(0) and see the SQL that I had submitte...more >>

If I have TableA and TableB I can have a one to many in these ways; A joining table that has TableA_ID, TableB_ID Or I can add TableA_ID to the TableB table. Could someone give me a brief run-down on the pros/cons and why you would use one solution over another. Mainly performance, ma...more >>

I am trying to use the opendatasource function to transfer data from one SQL Server to Another SQL Server. I use windows authentication and get the following error message. Server: Msg 7416, Level 16, State 2, Line 2 Access to the remote server is denied because no login-mapping exists. Here ...more >>

I just ran into a "problem", and while I understand that what I'm doing may be silly, I think SQL Server 2005 is giving me a semi-bogus reason for the error encountered. I have the following statement: UPDATE sometable SET somecolumn="somenewvalue" WHERE (somecolumn="someoldvalue") In...more >>

An application inserts some data using something like: insert into mytable ( dateCol ) values ( '1/20/2007 3:02:14 PM' ) the datetime string was created using DateTime.Now.ToString() Next, the user of the machine goes to the control panel/region settings and changes the short date for...more >>

I'm trying to run an view which updates the database on sql server 2000. How can i execute this view using a job scheduler. I wish to create a T-SQL job which runs once a day and run the view. I do not want to show the script in the commans area therefore created a view hoping to execute this ...more >>

hello everyone, here is another one i'd like to do without a cursor. i only have one parameter which is the date. i can only have one transaction per date. table1 date transaction grade 2007/01/01 AA123 80 2007/01/15 AA123 ...more >>

It's been 5 years that we’re using sql server 2000. And for us there's no need to upgrade to 2005 and I don't understand why people are upgrading? What's the big deal with sql server 2005 that 2000 canno't do? Just a questions! -- Un utilisateur curieux...more >>

I want to just keep track of the number of rows modified duing inserts... so I change the nocount status... but if I do it like below will I get what I want? just the inserted count? or will changing it multiple times mess it up SET NOCOUNT ON SELECT ..... SET NOCOUNT OFF INSERT ....... ...more >>

I'm trying to use a table type variable in my stored procedure: DECLARE @Temp3 table ( RecordID int IDENTITY (1, 1) Primary key NOT NULL , FAccountID int, [Name] varchar(100), FParentAccountID int, Sublevel int, FAccountNumber varchar(100), FAccountType varchar(100), TotalBalance money ) ...more >>

I've got a table that contains rates by day and another table that contains dated transactions. I need to create a query that will return the transactions with a calculated column that shows the transaction amount with the appropriate rate applied. As an example, let's say my tables are so...more >>

Hi all, I need to sort by certain criteria according to the business rules. I show the desire results below and the queries that I am working on but need a little help from sequel guru. Any help would greatly appreciate. DROP TABLE TestTable GO CREATE TABLE TestTable ( [District...more >>

I ran this simple script below, and got unexpected respult below. You see I was expecting the @@trancount in the 2nd print statment to have a value of 2, but it didin't. Please help. SET ANSI_DEFAULTS ON GO PRINT @@TRANCOUNT GO BEGIN TRANSACTION PRINT @@TRANCOUNT GO G...more >>

Greetings, I'm seeing a problem with printing very long strings using the PRINT command on a VARCHAR(MAX) variable. After a certain amount of characters the string is truncated....it looks like the limit is at around 8,000 characters. Does anyone know of a solution or a workaround for this...more >>

I was wondering if anyone has links to website or material information on DTS packages in sql server 2000 - 2005. I'm hoping for some guidance notes on this Thanks Nash ...more >>

I am trying to return a recordset with a list of users details, and also the last time they logged in. There can be many login records, and I just want the last one. I have tried using the code below, but when I add the bit that is commented out, it will only return 1 record. Can anybody help...more >>

What is the best way to store "long string data" ? What is the largest number (X) you can declare for a varchar(X) ? If I wanted to store an xml file in a SQL column, what column type would be best ? Also, I remember using the Text column type in 2000, but I believe you had to "process"...more >>

I am responsible for a multi-user application written in Access 2005 as frontend and using SQL Server 2005 as database. To insert new records in a specific table we use stored procedures which calculate an ID by "max(ID) + 1" before inserting the new record with this ID. We now had one case w...more >>

Using SS2000 SP4. I'm trying to update using two tables. The large table has 3.5M rows. When I run the query it says it updated all 3.5M rows but it should only update a few hundred. What am I doing wrong? CREATE TABLE [tbldropdate_dan] ( [branch] [nvarchar] (4) COLLATE SQL_Latin1_General_C...more >>

We are quickly running out of space on our server so we want to archive databases that are not in use. How can I find out if a database has been accessed or not. When I say access - I mean - querying, inserting, deleting and updating. Thank you. ...more >>

I am doing a number of inserts (in he range of millions) and deletions to some tables in my database. These operations are taking too much time and also the log becomes too big. I am using Recovery Mode Full. I am not able to quite grasp what the Recovery Mode is all about. Any practical example...more >>

Hi all I have problem with the trigger below...i can't copy an image into a local variable...how do i do it to get the trigger right? found something about textptr() but don't now how to use it. The problem column is [Document]. thanks for your help J. Ahrens Trigger: ALTER TRIGGER ...more >>