Well, I just found out there that I can't declare a varialbe as type text. That kinda took me by surprise. What is the preferred/best case on how to handle this? I've got several fields in various tables of type 'text'. Do I simply declare a variable as varchar and put a super huge length on i...more >>

I have a table, TABLE1 with 2 fields: ID Name -------- --------------- 1 Mary Wong 2 (Name in Chinese Character) 3 John Lee 4 (Name in Chinese Character) I want to write a SQL statement to replace the names in Chinese to blank: ID Name --...more >>

I want to this query to ignore the condition AND g.Book = h.account iff the parameters @Trader and @Desk are both NULL. Can I code this 'inline' using a nullif or coalese statement? I'd prefer not to write an if .. else statement with a lot of duplicated code. Cheers, David SELE...more >>

hi all i want to format a money field in a storedprocedure means.. i want output like $ 222,222,222 of a money field how can i do this in sql server 2000 thanks Noor ...more >>

Is it possible to do a dynamic select. I will be using it in a select statement in a union query Below is what i want to do but it does not work Example: Set @strTbl ='Table50' Select * from StateTable1 where state = 'TX' Union Select * from @strTbl where state = 'TX' T...more >>

Table Code Scale Reaso 20031101 APS Sic 20031101 DPS Sic 20031101 VPS Sic 20021101 DPS Annua 20021101 CPS ...more >>

I have a form in an Access front-end that is fed by a SQL View. Update security on the View is done on the SQL side. The Question is how can I restrict (blank or hide) fields on the form based on the users security access? How do I read if the user has access to a specific field? Any hel...more >>

Dear All, In order to get a do some version control, I would like to get the [last modified date] of the Store Procedure, Table Definition I find that the [sysobjects] Table ONLY contains a Create Date. Do SQL Server remember the [ALTER] and [RECOMPILE] time of these objects? How can i ...more >>

We have a swipe card entry system that writes to a SQL database. When a card is swiped, depending on the action code that is entered we want to create an absence record in a table in another database - but only if this is the first swipe for ths student for the day. I think the best way to do thi...more >>

Hello, What's the difference between primary key and unique index? At this point, they are the same to me, but I could be wrong. Thank you in advance for your comments. ...more >>

Hi, This is the first time I use SOAP. Dont really know the exact picture yet. Hope you can shed some lights on me. May I know where should I start troubleshooting if I get the followin error message when running SOAP on Windows 2003, SQL Server 2000 ? Error Code: 0 Error Source= Micro...more >>

I want to use UDF or something for aggregation function. for example I want to concatenate varchar charactors seperated by comma. Is it possible? ...more >>

I am executing an insert on a table with an incrementing identity, and need to get the value of that identity field as I insert new records. Here's what I'm doing right now: begin transaction insert into tbl (col_one,col_two) values ('value1','value2') select max(pk_col) as pkvalue from...more >>

I am in the process of converting a large number of convuluted flat files from a legacy system into a more organized and managable database. I have put together a DTS package to automate this, but have come across one conversion that I can't quite figure out how to do. I have one task that tak...more >>

Is possible to write a query to order its output based on the length of string? something like this? select wrd from tabl1 order by wrd.length asc and the result would look like this dfsfsdfsd dfdffdfdfdff sdffffffffffffff saggggggggg Thanks ...more >>

I have a table in which I have a date field and an identity field. I want to create values in an indexed bigint field consisting of the date in the form yyyyMMdd with the value of the identity column tacked on the end so that I can quickly identify the last record entered on any given date. I want...more >>

Hi All, I'm fairly new to the world of SQL and have been doing pretty well on most projects except when it comes to generating reports. Here is what I am trying to achieve Table: Main (primary key)custID lots of data rows Table: Events (primary key)eventID custID -- One-To-Many relat...more >>

I have an interesting SQL programming issue I've been struggling with most of the day. What I want to achieve can be done with cursors but ideally I want a single SQL to do it because performance is going to be a problem. I'm writing routines to combine some company records together, one of th...more >>

I've just started reading "Microsoft ADO.NET" by David Sheppa. One of the lines in the overview caught my eye: "Because many database administrators allow users to modify the contents of a database only through stored procedures, many developers cannot submit updates through the ADO Recordset ...more >>

I have the following storedprocedure in my server .. is check ok but i do no know if i can add a variable that will use the result of an operation to calculate another variable is that possible ? I want to calculate CON=Flowrate/12*2 but it tells me thar flow rate is not declare any Ideas ...more >>

If you create a temp table such as #MyTemp and later in the same SP want to drop that temp table how do you drop the table?? I would need to check if the #MyTemp table exists first and then drop the table. I am having difficulty with the syntax. My SP loops thru a few records in a cursor an...more >>

hi all, Iam trying to scratch my head over this for some time now, what iam trying to do should be pretty standard, but somewhoe iam not able to do it :-) iam trying to do a query for all words on a column ,a the rule is that order of words does not matter and space is the delimeter so ...more >>

The goal of this is pretty simple, for each date (DT), subtract the next prior date's return value (RV). The SQL at the bottom of this post works, although I haven't run it against 1000's of rows to test performance. I am simply wondering if a better SQL statement can be written. Mine isn't to...more >>

I made a trigger for insert operation but it won't work the way I expect. when I use "Insert tblOrderNew select * from tblOrderOld" it won't work. I think the trigger I made is for statement, not for rows. I did it with additional update statement for now, I wonder how can I achieve the ...more >>

I have a table with two fields, CompanyName and Comments. The table's key is CompanyName. I would like to know how many characters there are in the comments fields for each company. I have devised a query below that gives me part of the answer. The result shows me the length of the comments f...more >>

I have SQL server 2000 Enterprise with 4GB of ram on Windows 2k3 Standard. However, SQL server server is only seeing UPTO 2GB of ram. How can I configure SQL or the server to see over 2GB? I read about some switch to palce on the boot.ini file but was not sure the proper way. Please help....more >>

when I say select @@version : server says Microsoft SQL Server 2000 - 8.00.534 (Intel X86) Nov 19 2001 13:23:50 Copyright (c) 1988-2000 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3) What is the option to activate performance monitor and wha...more >>

Dear All, How to compare the fields in the 2 different tables from 2 different DATABASES. Yours Sridhar K ...more >>

I've got two tables, tblPart, and tblMessages. I've included a script for these tables at the bottom of this posting. The first table, tblPart, is made up of Part Numbers, with a previously-assigned, unique PartID (set up as the key for the table). The second table, tblMessages, is a list of m...more >>

How can I check my recovery model. or which recovery model I am running on. TIA ...more >>

which one is better select distinct name from tt OR select name from tt group by name TIA ...more >>

I am upgrading my sql 7.0 server to 2000. Ent. Mgr was able to create a script to export the alerts from 7.0, but when I run the script on the 2000 box I get the following error: The specified @message_id ('70060') does not exist. How can I get these alerts to import ?? ...more >>

I would like to write a UDF (User Defined Function) that encrypts a given value (in sql) and returns a string of the encrypted value. Any thoughts? ...more >>

Here is what I am trying to do: create table ttmp (seq1 int, seq2 int, amt money) go insert into ttmp (seq1,amt) values(1,0) insert into ttmp (seq1,amt) values(2,0) go create table ttmp2 (seq1 int, seq2 int, amt money) go insert into ttmp2 (seq1,seq2,amt) values(1,1,10) insert into tt...more >>

Hello I am getting an 8120 - '<column name> not valid in SELECT List because it is not in GROUP List I have another query that runs nicely despite the fact it is so close, it would be funny Excerpts of 1st query SELECT MASped.MANumber, Procedures.ProviderCode, CASE WHEN [ProviderCode...more >>

In by database application I use several drop down lists e.g. for action types, project types etc. etc. I have recently decided that instead of creating separate tables for each set of options e.g. (tblActionTypes, tblProjectTypes etc.) that it may be better to have an options and option types...more >>

I'm new to SQL and I'm stump. How do you display time only with the following format (hh:mm:ss AM/PM) on a datetime column? I tried convert(char(26), getdate(), 108) but it's not the right format that I need. There's not much articles out there for time formating. Thank you very much....more >>

Hi all how do you stress your SP guys ? are there some third party tools that you use ? have a nice day ...more >>

my col has datatype datetime ... in my select statement i wish to return this col as a formatted string, such as "11/30/2004" ... what is the best way to do this ? ...more >>

Hi, I want to write a trigger to check wether the insert row exists in the current table. If the inserted row exits in the table, the inserted row should be deleted, otherwise the row should inserted. I don't want to compare each colum seperatly. Is there a simple command to do this? So...more >>

what is DTS Lookup. Can anybody pls give a quick example. Any comments is appreciated. ...more >>

H I need to import xml data into sql server databases. The xml data comes from six different types of files, located into a directory on the harddisk. I want this directory scanned automaticaly to have the import procedure starting automaticaly How can I do ? ...more >>

HI My Application is built by SQL Server through internet. I have question about the concurruent problem. I have two tables in my database, Sale_information and Invoice. Their strucutrue is like that... Sale_information invoice_id, product_id, sale_price Invoice invoice_id, invoic...more >>

Hi, I want to pass name, path etc. as parameters to a stored Proc that should create a database. Why does the following code not work ('error near @DataName')? CREATE PROCEDURE [dbo].[spCreateArchiveDB] @ArchiveName varchar @DataName varchar, @LogName varchar , @DataNamePath varchar, @LogN...more >>

"UPDATE" is a logged transaction regardless of the recovery model, true or false?...more >>

H I need to import xml data into sql server databases. The xml data comes from six different types of files, located into a directory on the harddisk. I want this directory scanned automaticaly to have the import procedure starting automaticaly How can I do ? thx...more >>

Hi, I've a VB program connected to a SQL 200 Personal edition with SP3. It's just run for a month. Recently the program has ended with "Timeour Expired" error message. Then I incresed the waiting time to 60 seconds and the result is same. I've checked the PC is not busy all and have...more >>

Hi I have problems with a querry, when I try to insert data with the character '-', for example orientation="south-west" it returns the error: data truncate, how can i resolve this problem thank ...more >>

which one is better? when we do a check for existance e.g. where exists (select 1 from mytable where <criteria>) or where exists (select 'a' from mytable where <criteria>) TIA...more >>

why ? create table tab1 (clpdate datetime constraint c1 default '1jan 2003', ) does not give error. TIA ...more >>

Hi, In an effort to break up some of my lengthy stored procedures into more manageable units I'd like to know how to call one stored proc from another. I know about using exec to actually call the stored procedure. I guess I'm more interested in how to pass values from one to another and the ...more >>

I have a select statement in a cursor that orders by the start date, (in Ascending Order) for each row/record returned During the processing within the cursor, the last row returned by the select statement is used later in other cursors, before finally being updated to a work table, (see sample cu...more >>

Hi All! I need help with query: I have table CREATE TABLE Table1 (P1 varchar, P2 int ...) and data for example INSERT INTO TABLE1 VALUES (11, 11), (12, 11), (13,11) (11, 22), (12, 22),(13,22) I know P2 = 11 and 22 and I need find out if view1.P1 for...more >>

I get the following error "domain error occurred" when I execute the query SELECT STDEV(QCValue)FROM MyTBL. The QCValue column has been defined as type float...more >>

I hace two tables, one containg header records and one containing transaction records, in table 1 one of the fields is calles transactioncount . I am trying to Sum(transaction count) and check that it equals the number of transactions in Table the tables are referenced by a LedgerRe Ie BacsHdr...more >>

I don't know if there is a SQL statment that will perform this operation or if I need to cull the data myself but here it goes. I am trying to generate a list of all products that a client has not purchased. I have 2 tables. Products table =============== id - identity name etc. Pu...more >>