I want to store amount data in a field. I am getting confused whether to use Deciaml data type or Currency data type. I am not finding any points in using currency when compared to decimal data type. Any comments. Gary ...more >>

Hi, I'm new to SQL Server and have encountered an interesting problem and was wondering whether there were any solutions. Basically I retreive records from the SQL Server and populate a form which the user filled in earlier however, although there is data stored in the fields, it returns nu...more >>

Hello I have remote access to a sql 2k database and i was wondering if there some way i can retreive the systeminfo of that server? or do i need sertain permissions for doing that if even possible? /Lasse ...more >>

Hello How do i change the following query: SELECT (SELECT COUNT(*) FROM (SELECT DISTINCT SUM(Points) FROM Reports AS P3 GROUP BY TourNo,Team HAVING SUM(P1.Points)<=SUM(P3.Points)) AS P2(P)) AS Rank, Team,TourNo, SUM(Points) AS Points FROM Reports AS P1 GROUP BY TourNo,Te...more >>

trying to find all records with date less than 06/01/2003 when I try tbl.fld < 06/01/2003 results are not being filtered - all records are being returned I tried datediff but it only returns intervals between 2 dates, I need to see negative or positive difference. how can I get the "age"...more >>

Hi There, Are there significant performance difference between left join and right join? If yes, which one is more efficient? Thanks...more >>

Hi, I want to use a date for the column name for the next 80 months. Every month one drops off and one is add to the other end. Can I do this and if so how. Thanks MarkS...more >>

Hello, Here's a mystery for you to chew on. We had a system running on MSSQL 7.0 on Windows 2000 Server. Now it is running on MSSQL 2000 on Windows 2003 Server. The 7.0 machine was upgraded to 2000, then the databases were all updated using sp_updatestats. Next, the databases were deta...more >>

Hi all, I know this topic has been covered several times, but all of the solutions I've read will not work in my circumstance. All of the solutions either recommend using a cursor or declaring a variable and concatenating each row. While these solutions will work in most cases, they will not ...more >>

Hi when using alter table to add columms, it add columns to the end of the table , is it possible to add column into cretain position? via a script. thanks michael...more >>

Hi, after using the alter table command to add some columns and then 'design' on a table thru enterprise manager, the column that were added do not appear, however if you go properties on the table the columns are there. This dosen't seem to be fixed by doing a refresh, the work around is...more >>

Hi I'm grateful for any light you can shed on this!! I've to admit, it's an unusual design but I've multiple contact tables named e.g. i2b_ash_contact or i2b_ted_contact. 'i2b_' and '_contact' are static but the middle part is dynamic. Storing all contacts in one table with an identifier ...more >>

Hi SQL/OLAP Gurus, I am new comer to OLAP with Analysis Service. What are the considerations to decide to use OLAP (e.g: with Analisys Service) or to use just 'traditional' reporting ? (what I mean by 'traditional reporting' is reports on the operational/OLTP database, not the OLAP database...more >>

CREATE TABLE [dbo].[tblFlattenTest1] ( [TheRowKey] [char] (10) NULL , [TheColumnName] [char] (10) NULL , [TheColumnValue] [decimal](18, 0) NULL ) ON [PRIMARY] GO insert into tblFlattenTest1 values ('Car' ,'Speed',120) insert into tblFlattenTest1 values ('Car','Year',2003) insert into ...more >>

I am working with msde2k, but figure this problem could relate equally to access and couldnt' find a dbdesign group for sql, which is why i'm crossposting to these 2 groups. I am working on an inventory type application db design. We are tracking only total quantities of parts and not individ...more >>

I am working on a game ranking system similar in design to the tables below. tblMember --------------- member_id_pk (primary key) first_name (varchar) tblPoints -------------- point_id_pk (primary key) member_id_fk (foreign key to tblMember.member_id_pk) points (INT) I need to return...more >>

how can I convert a string to a date to get datediff to work .... @vReportdate Varchar(25) datediff(d,tblA.fld1 ,' + @vReportdate + ' ) > 1 ' ...more >>

i want to know is there any tools automatically generating a insert sql statement based on the data from a select table. thanks! ...more >>

If a column has been specified as Decimal Precision 12 Scale 6... how would you format the data in a view as Decimal Precision 9 Scale 2 using the following formats... ....with leading / trailing zeros including the decimal point ....with leading / trailing zeros excluding the decimal point (de...more >>

Hi guys, SQL server creates statistics on tables. I have seen that these -WA statistics are created for almost every field on every table. My Question is on production boxes is it this a good idea. or we should create the statistics only on potential fields after analysis. I w...more >>

Is there any tool like visual source safe which can be used for monitoring the changes on SQL server and generating scripts for that change? Is there maybe possibility for using VSS? ...more >>

table has a string field that corresponds to an ID , such as a license-number business-process requires that field characters must be digits (only). for various complex reasons, "gaps" may occur in the numeric sequence, e.g. 1001000 1001001 1001002 1001003 1001005 1001006 1001007 ...more >>

Hi Can someone please tell me why the following query won't use the index - it has something to do with the fAdd function which as far as I can tell is deterministic and precise but I get the following warning message when the indexed view is created Warning: The optimizer cannot use th...more >>

Hi, I have a stored procedure which keeps throwing me out of visual basic when I try and run it, it works perfectly in Windows NT but throws me out when running it in Windows XP, any ideas why this is happening would be greatly appreciated. Here is my stored procedure: CREATE PROCEDURE d...more >>

Hopefully someone has some experience with SQL Server 2000 Developer Edition. I installed the above, including server components which should include Full Text capabilities. However, Full Text does not show up in Enterprise Manager like it does on my web host's server. Can anyone give me a clu...more >>

How can I get what's the default value of a column as set in the schema ? ...more >>

I want to write a stored procedure that inserts new rows into a table by selecting other rows in that same table. The table has an IDENTITY column which is the primary key. INSERT INTO tbl_A ( ... ) SELECT ... FROM tbl_A WHERE ... This works fine. But I have another table that uses the IDE...more >>

I want to see last 10 commands executed by a user. is there some command like dbcc inputbuffer. TIA ...more >>

In which scenario's we should use OPTION (KEEPFIXED PLAN) in queries. ...more >>

Hello all. Can user defined data types be used in stored procedures, for example to define input parameters? Thanks. ...more >>

Hi, I am trying to calculate the difference between two date in my query using the DATEDIFF function. But the probkem is that the DATEDIFF function rounds the result. So for example SELECT DATEDIFF(YEAR,'19991021','20010626') returns 2 instead of 1. I also need to calculate the difference i...more >>

SS 2000 SP3 I'm executing a DTS Package. It has two connections, one to SS and one to an Excel spreadsheet. The package has a series of tasks that move data from an SS table to Excel. Occasionally a task fails with the error message "The connection is currently being used by a task. The...more >>

Hi, I have a query that uses a CASE statement with a load of ELSE-s to work out a result from source data, I then name this result with an As clause. For eaxample, the result might be blue, red or green and my derived column name is 'Color'. My question is, I want to put WHERE Color = 'blue...more >>

In a large user setup , Is there any program available to find out who is deadlocking who ? Thanks ...more >>

Hey all, I can't update any stored procedure in my database, even as an admin. The user is connecting as the db_owner and for that object he has EXEC privileges. I'm new to SQL Server but I thought he would need update as well but that column isn't selectable. I get: Error 208: In...more >>

Is it possible to choose which column to ORDER BY depending on whether one column is NULL or not? For example I have a table with two columns, Column1 may be NULL and if it is I would like those records to be at the top of my sorted output. If Column1 is not NULL, then I would like the remainde...more >>

Hi, I get error using openquery statement in SQL by using MDX that was generated by my olap client tool (ProClarity). The thing is that only specific calculated members makes this error. In general it works fine. The problem is that I need data of the problematic calculated members. SQL 200...more >>

Hi i deleted all the rows of a table with an identity column and now i have to insert new rows, i need to reset the identity start value ... i did it in the past... but i forgot how... can anyone help me?? thanks Carlo ...more >>

Hi there, Is it possible to be able to pass a table name to a stored procedure and have it return data from that table. If not is there anyway I could achieve the same effect? much appreciated, John...more >>

I was helped earlier with a Query , but Im still not getting the correct results; Table 1 LedgerRef verchar(6) RecievedDate smalldatetime LedgerRef RecievedDate 111111 01/01/2004 111111 12/12/2003 111111 23/03/2003 111111 ...more >>

Hi al I have also another problem I like to have a CASE in my WHERE Clause. I have read that this should be possible but I cannot get it work... I tried the following DECLARE @Test varchar(50 SET @Test = 'J SELECT convert(varchar(50), getdate() WHERE CASE WHEN @Test = 'A' THEN Left(convert(...more >>

Hi ! I am trying to use the Performance counters exposed by SQL Server "Yukon" Beta 1 in a .NET windows application written in VB.NET. I am receiving the following error : "Counter is not a single instance, an instance name needs to be specified." when i checked through Contr...more >>

Hi All, I am trying to pull records which are in table1 and not in table2 SELECT * FROM TABLE1 t1, Table2 t2 where t1.OrgName <> t2.OrgName Its not working....returns everything from t1.... how to just get records which are only in T1 and not in T2 TIA ...more >>

If i set a column as Primary Key do i gain anything by or lose anything by also creating an index for that column? Thanks ...more >>

Hi all, I have SQL 2000 Server and Window XP and work in hebrew I want do get XML string from aquery in SQL 2000. For instance select ID,Name From tableName FOR XML AUTO I got all question mark even when the fields is not in hebrew How can I Do it TIA gals@mekorot.co.il ...more >>

SERVER = WINDOWS 2000 SP4 (MDAC 2.8) SQL_SERVER = 2000 SP3 CLIENT = WINDOWS 2000 SP4 (MDAC 2.8) TASK: We have a table of usernames, a table of criteria for those users and a table of number balances broken into years, periods and codes. Using Access Data Projects, we need to all...more >>

I have a trigger that executes a stored procedure performing an insert, which may potentially throw a constraint exception. I can't capture this with normal error checking (as exceptions are higher than errors), however I don't want to reject the original action that triggered the stored proced...more >>

Hi, I want use "table variables" in stored procedure, CREATE PROCEDURE sp_TBL_CNTRL @TABLE_NAME VARCHAR(128), AS DECLARE @COUNT INT .... DECLARE @TBL_MEM TABLE (FLD_MEM INT NOT NULL) SET @SQL = 'INSERT INTO ' + @TBL_MEM (FLD_MEM) + ' SELECT COUNT(*) FROM ' + @TABLE_NAME + ' WHERE UID I...more >>

Hi, For a number of reasons, when inserting in tables with identity columns I have to provide the identity value explicity. First I find the next identity value and then use it to insert a record in the table thus making sure that there's no conflicts. Now my problem is this - some of my inser...more >>

Hi, We have jobs on some of our sql servers that will send query/store procedures' running results as attachment through outlook email to people. most of the times, the job will run just fine. But sometimes lately, in some of our server, these type of jobs fail due to this error: '...more >>

Hello Is there a way to do a query using between 'a' to 'z' instead of numbers? For example, select lastname from northwind..employees where lastname between 'c' to 'g' Thanks a bunch!!! ...more >>

Hi I have a SQL Update which runs fine when I run it on it's own (takes around 15 minutes) but when I run the SQL in a batch with other statements the process goes on forever (it's been running 14 hours so far and still going). Looking at the session it's waiting on Parallell Query, packet s...more >>

Dear all, I want to update each row in temp table one by one, is there any thing I can do to make the rows stored in temp table are in sequence while doing update? Something like: Declare @a INT, @b INT SET @a = 5 SET @b = 0 Update temptable set @b = column1, column1 = column...more >>

I have the following data in a table Date JobCode Value 1/11 job1 3 1/11 job2 4 1/11 job3 6 1/11 job4 7 1/11 job5 11 1/11 job6 89 1/12 job1 0 1/12 job2 1 1/12 job3 2 1/12 job4 3 1/12 job5 4 1/12 job6 4 1/13 job1 7 1/13 job2 8 1/13 job3 9 1/13 job4 10 1/13 job5 11 1/13 job6 12 ...more >>

Hi, I have a select query like: select * from table1 left outer join table 2 on (join condition) The problem is if Table2 has more than one row satisfy the join condition, then Table1's row repeats. Is there any trick that i can have query just pick the first row of Table2 in the case ...more >>

Hello How do i format SELECT 5/8 AS 0.625 or any division with two integer values? /Lasse ...more >>

I am working with a trigger that sends data between servers using a Linked Server. I have found problems with slowness and what appears to be cursor like behavior when processing more than one record at a time. When I process 1 record or 6 records in a test of the trigger without the Link...more >>

We have a job that runs every so often. We had to go into sql server and setup the proxy account because we use xp_cmdshell in 1 of the stored procs. We got a different machine and it has windows server 2003 and it is a domain controller with active directory installed and I can't seem to get t...more >>

Hello, How can I bring back a result set such as the following: From this query: select lastname from northwind..employees where lastname a to d I am trying to display the lastname only inside a range like a to d or h to z.... Thanks for your help ...more >>

Is it possible to have a LIKE statement inside an IN? Something to this affect: IN(Like '111%', Like '112%, Like '113%') I am passing a string to my stored procedure that looks like this: '111,112,113' I have to strip it apart and add the '%' and then do my select. Thanks! *** Sent...more >>

Iam getting the following error message: Msg 170, Level 15, State 1, Server PASQL1, Line 1 Line 1: Incorrect syntax near 'Function' when I tried to create the following function: use dwexternal go CREATE FUNCTION dbo.fn_RectangleArea (@Width int, @Height int ) RETURNS int AS BEGIN ...more >>

Is this possible? What driver should I use, or is there a metadata file that needs to be created and used? Thanks! -- William Morris Product Development, Seritas LLC Kansas City, Missouri ...more >>

Hi al I am trying something what is maybe not possible... but who knows.. I would like to build aomething like a dynamic SQL Statement without using EXEC The target would look like this SELECT something FROM sometabl WHERE MyString = '123 IF something = BEGI AND somefield IN (... EN ...more >>

I have a situation where I need to change databases within a stored procedure. The setup is as follows: - database1 - database2 - reportingdb We have lots of stored procedures in reportingdb that were "hardcoded" to reference database1. database2 is an exact copy of database1. In th...more >>

What is the diff. between. select unicode('A') go select ascii('A') TIA...more >>

Can someone tell me what the Shared Memory option is used for when one runs the SQL client network utility. Start->Rin Cliconfg There's an option for shared memory protocol. I would like a good technical description of this option. When to use it and when one doesn't need to use it. Many ...more >>

Hi all I have a server called CDC-DRUG. It is a linked server. I am trying to query against from another server. I am getting a syntax error though. This is my query. SELECT * FROM CDC-DRUG.Nation.dbo.Companie The error says incorrect syntax on the hyphen "-". Can anyone offer up assistanc...more >>

I am trying to create a stored procedure that takes two input parameters: a table name and an in list. The table is going to be a tempory table (#myTableName). My create procdure is shown at the bottom of this message. When I check the syntax, I get the error "Must declare the variable ...more >>

Hello, I have an SP that groups things by categories. How do I use count to get a running total of each group. I want to have a field the does something like the following count category 1 ety 2 ety 3 ety 1 etn 2 etn 1 dny 2 dny ...more >>

I found that if I define a BIT field with a default and then update a row without specifying the bit fields to be updated, they are changed to their default values. This is disturbing. I changed them to TinyInt and they remained the same value. Is this a bug or they way it works? Anybody know? ...more >>

Can anyone tell me how I can paste Chinese characters into Query Analyser? I need to execute insert and update statements from the QA window, I usually recieve the Chinese text via email and paste it directly into the column in EM, however, if EM displays <Long Text> then I can't paste it in an...more >>

Hi I am developing a time reporting application where employees will submit there monthly timesheet. part of my requirement is to have a time_sheet table that will store hours for a particular employee. We need to store date worked and hours worked for that date. My question is how do I de...more >>

If the transactiondate is between Nov 11 and Dec 10 Then Then From Date = Nov 11 ANd ThruDate = Dec10 How do to in 1 case? ...more >>

I am in SQL 2000 and I need to check to se if a file is there or not. A thrid party product generates a TXT file that I have to input into a table. I would like to check to see if a file is in a folder. \\ComputerName\TxtDump\20040114.txt Thanks, Lynn....more >>

Hi, 2 questions. Using Win2K, SQL2K. 1) Is it possible to programmatically "push" or "pull" an update of the AD DB to my SQL servers? The reason I ask is that, 2) I create Global groups in AD using VBS. Those groups are immediately visible in AD, and I can issue a query from SQL against t...more >>

I have been asked if its possible to change a field in one of tha tables and format the data. The field is a varchar(6) and i want to change it to a varchar(8). i would also like to format the data in the field from 123456 to 12-34-56 for all records. Thanks in advance for you help...more >>

Hi everybody, I posted this originally on 9 jan and have had no reply since, so I'll try again. Hopefully this is the right ng for this. If not, please point me at the right one. We have a db wtih about 140 tables. It is an ERP solution for a paper merchant. We have discovered that SQL Ser...more >>

I have two tables, referenced by a LedgerRef field, Table1 contains hearer records and Table2 contains a breakdown of the transactions, Table 1 LedgerRef RecievedDate etc. qweasdew 01/01/2004 wwwddffr 12/12/2003 Table 2 LedgerRef TransRe...more >>

SQL Web Assistant: Source code page is not supported on the system. Ensure @charset and @codepage language files are installed on your system how can I solve it thx...more >>

Is there anyway to capture and manage errors in a procedure so it can = proceed? TIA! --=20 Greg C...more >>