I have tables named: Name, Date, Income , with the following columns and 2 inner joins from the Income table to each of the other tables via the lookup columns(they are more complicated, but this illustrates the basic layout). Name A B Date 2003_Q1 2003_Q2 2003_Q3 2003_Q4 2004_Q1 2004...more >>

My trigger does what I want it to do, but does it for every item in the my item_master table every time. How can I limmit it to only the record that is being updated or inserted. Thank you for your help!!!!! CREATE TRIGGER [short_desc] ON [dbo].[item_master] FOR INSERT, UPDATE as updat...more >>

i've account and subaccount table relation is account_urn Now i need a sql to retrieve based on this condition need to update the account fields by grouping subaccount but when we group just i want to add only first 2 rows of sub account of salary to account field How can i do this Tha...more >>

There are 2 tables in my sql server:phys_datei,firm.phys_datei has records of 196412, and firm has records of 294621.When sql server run query "select * from phys_datei a, firm b where a.id = b.id",the collection records is 589000.About 250000 records are empty.How this happen? if there is a...more >>

I want to create a table of greek characters: ID Character Symbol 1 alpha a 2 beta B etc but instead of a and B, I want the alpha and beta symbols stored. I tried to make the symbol column a nvarchar and typed "N'0381'" (for alpha) and "N'0382'" (for beta) but I don't get the sym...more >>

I have table using autoincreament integer seed(1,1) at SQL Server 2000. This field autoincreament is primary key. and my transaction record grow. Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). what if counter autoincreament have value over 2,147,483,...more >>

Hello, How can i automate this repetitive select/insert from Table two into TableOne. So i do not have to rewrite twelve times the same SQL statements. Basically this is what I have to do: INSERT INTO TableOne (Account,Category,Entity,RptCurrency, SIGNEDDATA ,Dept,TimeID) SELECT ...more >>

I have stored procedure and i would like to include transaction. If all updates are ok, no error, than commit transaction else rollback transaction. How can I do that? example: --BEGIN TRAN T1 SELECT @advIdStara=a.adv_Id,@dolzinaStara=a.adv_duration,@tsad_id=t.tsAd_id FROM cpoAdvertis...more >>

I need help for a query. there are 2 tables: --(table 1) Encounter EncounterID bigint (key) AdmitDate datetime DischargeDate Datetime PrincipleDiagnosis char(5) ...(more fields) --(table 2) SecondaryDiagnosis EncounterID ...more >>

Can anyone advise the cheapest place or source to get SQL Server 2000? The installation I had expired with the 120 day thingy. I'm using EM to manage SQL on our web hosts server. I want to experiment with catalogs and they are disabled for users on the web host's server. I'd like to have SQL loc...more >>

Given the following schema and data CREATE Table Calendar (CalendarDate DATETIME NOT NULL Constraint PK_Calendar Primary KEY, DateType Integer NOT NULL CONSTRAINT CK_DateType CHECK (DateType in (0,1))) 0 = Holiday 1 = working day INSERT Calendar (CalendarDate, DateType) ...more >>

Where is my previous post :-( declare @askey varchar(16) declare @htaskey varchar(16) set @askey = '1578/ a/1' print @askey set @htaskey = replace(' ','', @askey) print @htaskey I want to elimate the space in the string. In the above case I want to eliminate space before 'a/1'...more >>

Hello, I have an XSP which currently takes 2 delimited strings, which are parsed within the XSP and the values are put into their own respective arrays which are needed to perform some mathematical calculations. The number of values within the delimited strings can vary. I perform a large sele...more >>

declare @askey varchar(16) declare @htaskey varchar(16) set @askey = '1578/ a/1' set @htaskey = replace(' ','', @askey) print @htaskey I Want to eliminate the space before 'a/1'. But the above script is not working. Where is the wrong? With Regards Satya Rao ...more >>

An interesting problem. For simplicity sake, I have only one table here copied from Northwind Order Details. The table contains the details for the products ordered. For orders, the same product can be ordered mutliple times -- quantity has no meaning in this example. I am looking for ...more >>

I know that SQL Server can sent email, am i right?? If i am right, please let me know how can I do it, or give me some reference to me. Thx~ ...more >>

Hello to everybody and a Happy New Year I have a Query in Access97 where two of the fields are like that: InQuantity: Sum(IIf([In_Out]="1";[Quantity];0)) and OutQuantity: Sum(IIf([In_Out]="2";[Quantity];0)) That is : "Give me the sum of quantity when InOut is equal to 1" and "G...more >>

This is likely a very basic question, but I am stumped. I am trying to convert an integer value to a character string that has four characters with zero-padding to the left. Thanks...more >>

Hi I have a query and I wish to produce my own key(ID), anyone know how I can do this..?? -- Kind Regards Rikesh (SQL2K-SP3/W2K-SP4) ...more >>

create table #t (labelchar(10)) insert #t values ('labela') insert #t values ('labela') insert #t values ('labelb') insert #t values ('labela') insert #t values ('unknown') I need the count of labela, the count of labelb, and added to the count of labela and labelb is unknown divided by 2...more >>

Urgent help needed! We have a complicated stored procedure, which inserts and updates records within a cursor loop. Inserts and updates are done by calling (other) stored procedures. When this stored procedure is executed from QA, everything goes well. However, when executed from within the a...more >>

Hi I am fairly new to MSSQL environment. What I want to know is by using EM can I import my entire db which resides on a remote host server onto my PC. Appreciate your help. Pam ...more >>

Hi, I need to query a varchar column which contains alpha- numeric data. Could be 1111 or 2222 - 3, or 3p111, etc. Most of the data is numeric. I would like to query it like this in pseudocode: Select Max(Cast(f1 As int)) From tblA Where f1 is not varchar Is there something I coul...more >>

Hi I have a table that contains historical data for all our customers and products over the last few years. The relevant fields and typical data are: CustomerID, Value, Year 100 53 2001 100 10 2001 100 ...more >>

can anybody tell me some scenario's where i can use "READPAST" locking hints. TIA. ...more >>

I have occasionally seen Isqlw hang (when called from a batch file to execute a script file and produce an output file) after completely finishing the script execution and completely writing the output file to disk. Isqlw appears in the task list thereafter. The exact same runs do not ha...more >>

Can anyone recommend a report writing software product to use with MS SQL and a Macintosh. I use Sybase InfoMaker for my Windows customers, but I have nothing for those pesky Mac users. They are the bain of my existence. Any ideas? Thanks. ...more >>

I am new to MSSQL. My clients use the extended stored procedures xp_startmail, xp_sendmail and xp_endmail from my master db They are currently getting error messages saying that the Microsoft Outlook is not set as the default mail client Now the problem I have is, with the install of windows, Ou...more >>

Would somebody please explain if there is any reason to use Osql rather than Isqlw in batch files, other than the new features in Osql, such as return codes. I am converting an app that worked fine in SQL 7.0/Isqlw to SQL 2000. The app already prints completion messages in the output fil...more >>

Hi, I would like to display two records below as one record with the following condition: If joint_acct = 0 then then score1 and score2 belong to Primary Application. If joint_acct > 0 then score1 and score2 belong to Secondary-Application. joint_acct model1 score1 model2 s...more >>

If I am running a query on one small table and one very large tables with the following schema: Table_1 (PKey on Field_A, Field_B): SMALL ONE Field_A varchar(50), Field_B int, Field_C Table_2 (PKey on Field_D, Field_E): LARGE ONE Field_D int, Field_E int, Field_F varchar(5000) and ...more >>

I know, I know, it's me again, with this email stuff! I use the following trigger to (successfully) send email: --trigger to send email with more information CREATE trigger [emailsend] on root.ae_a_aemail_a for insert as declare @a varchar(1), @b varchar(40) set @a = (select actiontaken f...more >>

I am using a VB6 app to connect to a SQL Server 7 db through an ODBC connection with ADO. I have 70 computers connected to the db and performing well for 2 years. One computer just started frequently getting a timeout error on startup. SDBase.ConnectionString ="PROVIDER=MSDASQL;DSN=" & stODBCDSN...more >>

I have a datetime field using which I need to count results for the past 3 complete months and ignore the current month. The logic below gives me the past full 3 months and excludes the current month of data. However, the query takes longer to run and I am looking for something simpler if ...more >>

We need to insert Project Server data into RESERVED_BINARY_DATA field in MSP_PROJECTS table in SQL Server 2000 using stored procedure. In order to do this we need to convert hexadecimal string to binary. Does anyone has an example that we can use? ...more >>

help! I am new with SQL 2000 and I have a server that is NOCASE. I request lower case data and I get records that are both upper and lower case...Identical accept for the case. I need to have SQL code that is not case sensative but data that is case sensative. Can anyone HELP! Thanks...more >>

Hello all. We need to store student GPA's (i.e., "3.40"), but don't know the data type to use for the column in the table. We're thinking FLOAT(24) because that's SQL Server's synonym for the REAL data type. We don't need precision beyond that. How do we limit it to two decimal places (2.99 and ...more >>

I'm using temp table to get rowNumber and adv_Start from cpoTimeAdv: SELECT IDENTITY(int,1,1) AS adv_position,c.adv_start INTO #myTable from cpoTimeAdv c where c.ts_id=1 ORDER BY c.adv_start select * from #myTable ORDER BY adv_position drop table #myTable Problem is, that I dont get th...more >>

Hello, I have just deleted a table. Is there a way of rolling back the database in order to undo this ? Thanks, Andrew. ...more >>

In a stored proc, during delete, if a constraint error occurs (error = 547) is there a function i call to get the table name that caused the = error? =20 I would like to catch the error in the sp, then reformat it using = RAISEERROR with a much more user friendly error message. for example:...more >>

I would like to create TSQL statement that will meet the Table Name (XPAT_%) and Column_Names (MON%) critiera and will also give the Primary Key Name for this Table. Need to add the Primary Key to the TSQL statement listed below: select o.name TABLE_NAME, c.name COLUMN_NAME from sysob...more >>

Hi All I know you're going to ask for table definitions and insert statements, but I think I'm just confused on the syntax of how to get this query to work rather than anything else. My setup is as follows: TABLE 1 - STOCKCATEGORIES This table consists of the STOCKCATEGORYID and the NA...more >>

Hi, this procedure returns a Null regardless of the IsNull function. This only happens when there are NO RECODS matching the input-parameter. Suggestions to sole this will be appreciated CREATE PROCEDURE [MTS_GET_BILLET_DIAM] @BILLET_SEQ_NO int , @DiameterMM int OUTPUT AS SELECT @...more >>

I would like to know how to insert desired value into an identity column. For eg supp there are rows in an identity column till 13000, and instead of inserting rows from 13001(increment is 1) I wish to insert rows from 14000 onwards (with increment 1). TIA Roji. ...more >>

I have table Add with dateTime field and advId. Now I fould like to move the ad from position 5 into position 2 and ad from position 2 into position 5 (where mediaId=1). Just exchange 2 ads. What is the easiest way to do that? The fields ad_startDate and ad_endDate between this 2 ads must be cha...more >>

Hi everyone - I am trying to move the datafiles of a database because we running out of space on the C: drive. I tried to do this in the properties of the database but it gives me the following message: A database file's physical name cannot be changed once the database has been create...more >>

Need urgent help. I am new to MSSQL and stored procedures. I have granted execute permissions to my client on the following stored procedures in my master db: xp_startmail, xp_sendmail and xp_endmail. I used the following code to grant the permission: USE master GO EXECUTE sp_grantdbaccess '...more >>

I am trying to figure out how to tell what position a particular character is holding in a string. For Example if my string is 'laport, PA 12345-6789 I want to know what position the "," is holding. My result should be 7. I am aware a function in Access that will enable me to do this called...more >>

The below is a paste of a query within SQL2000 that works. I copy the query to VB6.0 and get the error further down. A copy of the table structure is below that. The Department and SectionNames tables are used to define the Department names and Section Names for people who want training...more >>

Hello this might be a piece of cake for you guys but I was wondering if you could help me to get this following result: Player SumPoints Wins Ties Losses C 3 1 1 0 B 3 0 3 0 A 2 0 2 0 trying to get SUM(Points), Count(...more >>

I have been getting the following error upon executing the stored procedures as a user. What is the command to set Outlook as the default mail client? Should it be done at the admin level? ERROR: " Server: Msg 18030, Level 16, State 1, Line 0 xp_startmail: Either there is no default mail clien...more >>

Is it possible to create a row level trigger on MSSQL 2000? I want to update a history table using a trigger: if a transaction row is added/modified/deleted, I want to update the corresponding history records. Best regards, Edgard L. Riba ...more >>

Hello, I'm trying to print out progress inside a loop in an admin stored procedure: set @row=@row+1 print 'Containers:' + @row @row is an int and 'Containers:' is obviously varchar How can I do this? Do I have to cast or convert @row, or is there a better way? Many thanks, Kevi...more >>

Hi All, I'm new to stored procedures and wanted to see if this is possible in a single procedure. I have a SQL 2000 database with a Videos table. The customer can rate a video from 1 to 10. Following is my table layout; Fields: RatingNumberReplies int TotalRatings int When the a is m...more >>

I have a table having some statistics created on this. how can I query sysindexes to get just the row having statistics created by me....more >>

Hi I need to audit the DB that I'm working on, and I need to extract details of the column properties, I was just wondering the differences between 'xusertype' and 'Length' are they not the same fields? If not which one do I use to specify the column length? Kind Regards Rikesh (SQ...more >>

I'm setting up linked servers, both SQL Server 2000. I'm trying to pass data from one server to another, but instead of inserting it to a remote table, I'd prefer to bundle a temp table somehow and pass it as a parameter of a stored procedure. Does anyone know how to do this? I'm thinking ab...more >>

Hi, I need advise about Trimming spaces and comma from a varchar string What query would you use to trim this string ', The Quick Brown Fox, Jumped Over, ' to the resulting string 'The Quick Brown Fox, Jumped Over' - Thomas ...more >>

Hello, Is there a way that i can run a select statement and populate an array with the results so i can do some other stuff with the results? Thanks, Matt...more >>

is it possbile top n qeury by ANSI standard? ...more >>

How can I create a job on sql server 2000 that exec. an sql statements and sends these values to a process of my choice. eg. Select Name, Adress from customer where id = 1 start process test.exe Name.value Adress.value ...more >>

Hi there I have created some test code to test a trigger on a view I have tested it on Personal and Developer Editions of SQL, but I'm not sure whether it makes a difference in terms of functionality available. When I get to the batch that creates the trigger, I get the following error: ...more >>

Hi, I have a stored procedure I want to run every night, is there a place in SQL 2000 where I can do that? Lasse ...more >>

Hello In this repro I try to check wheter exists or not a value in the table. I don't have PK on the table because I would like to check it by using UDF. As you can see I have one value=10 in my table. Next I want to insert let me say value=20 ,so how will behave CHECK constraint ? This va...more >>

Does any one know of a way to iterate in a stored procedure, the same way as you may use rs.movenext in ASP or VB. I have 1,000,000,000 iterations to carry out and the webpage times out, so I need an alternative solution....more >>

Hi I have been getting the following error upon executing the stored procedures at the individual user-level. How do I set Outlook as the default mail client? Should it be at the admin level? Please help " Server: Msg 18030, Level 16, State 1, Line xp_startmail: Either there is no default mail...more >>

Hi!! I have to create a multiple case-selection, eg: case code when a,b,c then when d,e,f then... .... where a, b, c are mutually exclusive.... I didn't want to create N-when clause for each value but in Sql I don't know HOW to do it... thanks!! milena ...more >>

Hi All, I am try to run a sql query on a remote server. The server name has hypens in it. This is my query: select distinct top 10 uniqueid from qcid-srp-sqltrn.MydB.dbo.uniqueid_bank a .... I get the following error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect synt...more >>

I am trying to select a record by a given day ie FROM tblRecords WHERE strDate = 12/12/03(or a variable @date) But SQL compares the date down to the millisecond and so doesn't find a match I have tried < or Between statements but this seems crude Thanks in advance Charlie ...more >>

I have just started work for a new company and have been given some stored procedures to investigate. I have come across some code which looks harmless but I cannot help thinking it is a bit odd and I just wondered if this is a common way of doing things. It doesn't seem the correct way of do...more >>

Hi All I have been searching all over to find some detailed explanation for the status column in the sysfiles table but with no avail. I am interested in one value in particular. The following value 0x80 which inidicates if the file has been written to since the last backup as stated in BOL. T...more >>

I have a table that i am trying to run a join query on a Third Party table. Looking at the two tables, the one i have created is using ; collation = Windows Collation, Latin_General whereas the third party table is using collation = SQL Collation, SQL_Latin1_General_CP1_CI_AS I am g...more >>

When I use the following Procedure for finding the Disk Space on SQL Server Box, It gives me the desired results, but when I call this SP from another SP to fetch the server names into the output I get the following error: server.databasse.dbo.sp_diskspace Server: Msg 8164, Level 16, State 1, Proc...more >>

If I change the sql statement below to "SELECT o.ContractId FROM..." instead of "SELECT * FROM..." it takes dramatically longer to execute the statement (from a few seconds, to a few minutes). One would think that selecting just o.ContractId would actually be faster... but it's not. Can anyon...more >>