Hello, I have a float field called actual. I am trying to format it as I am selecting it and I am not successful. The select is select convert(money, actual, 1) from title. Supposedly, it should have yielded $xxx,xxx.xx, insted it have me xxxx.xxxx . Could somebody tell me what I am miss...more >>

SELECT * FROM tbl AS n1 WHERE score in ( SELECT TOP 3 score FROM tbl AS n2 WHERE n1.name = n2.name order by score desc) my query won't give me the result i like. teamA MARK1 20 MARK5 14 MARK3 10 MARK4 08 MARK2 14 teamB fred2 12 fre...more >>

Hi,guys! I have a table below: CREATE TABLE rsccategory ( categoryid NUMERIC(2) IDENTITY(1,1), categoryname VARCHAR(20) NOT NULL, PRIMARY KEY(categoryid) ) Then I do: INSERT rsccategory(categoryname) VALUES('url') IN...more >>

Hello folks, in a stored procedure I create two temporary tables (could as well use table varables) and then I need to know wether they are identical or not How could I perform such a task? Thank you Anton ...more >>

We are using SQL Server 2000 under Windows 2000 Advanced Server with Delphi clients that they accessing the database with ADO.We have 2 applications A,B We doing a big transaction from application A in order to do an update and SQL locks the records in order to update them.The same time we are tr...more >>

i'm using the FIGURE 1 to return a time duration difference between StartDateTime and EndDateTime. It returns a value equaling a percent of an hour. For some reason, FIGURE 2 will not work when using seconds, instead of milli-seconds. What number should I use in FIGURE 2 to divide the DATEDIFF...more >>

I've been told that '%string%' will not use an index (if that column is indexed). However when I run the SQL in SQL Analyzer it does show it uses an index. Can one of you gurus explain how and when indexes are used with LIKE? Thanks ...more >>

By using the MSSQL extended procedure xp_cmdshell to execute a DOS command Im not able to execute a visual basic .EXE developed program. The execution of commands like 'DIR *.*' or 'COPY ...' works fine but using the developed program it get stuck and it never ends. Do you have experien...more >>

I have a user-defined message as follows: Error=50020; Desc=SP_UpdateSOR: Could not record (insert) Version History (FORM_UPDATE_LOG) for TrackingId=%d. Cannot proceed. In the SP_UpdateSOR, I raise the above error (50020) and pass the TrackingId. At run-time, when I invoke the above SP fr...more >>

Hi, I have a Sproc. In this I'd like to get a single value from a particular record and assign it to a variable. I have tried @qty = select LI_ID FROM tblLineItems WHERE LI_ID = @LI_ID however, this doesn't work. After reading I have found that I should be able to do this using a curso...more >>

Oracle 9i to SQL 2K Can someone shed some light , how can I create SQL server db objects from the dmp file (export from Oracle). I need to create the schema and later load data in SQL SERVER.. Any links or related topics will be highly appreciated. Thanks Sh ...more >>

WARNING -- NEWBIE ALERT -- WARNING I'm trying to maintain an audit trail of changes made to one of my tables. (The tblUsers table is the actual data table and the hstUsers table is the "history" table -- where the audit trail is stored.) What I want to do is update the ModDate column in the tb...more >>

I have table names tblTest: Field1 Field2 ============== 1 A 1 B 1 C 2 D 2 E 2 F How can I write SQL Statement to get the result: Field1 Field2 ===...more >>

It has been suggested that we should separate our large varchars (like 500) out into a separate notes table and then join them to the "main" table. So the main table contains a foreign key to a note, which may or may not be set. The note table contains a primary key and the varchar. When ...more >>

I have a question regarding SQL error number 547. When attempting to execute the statement: alter table Badge add foreign key(BadgeLayoutID) references BadgeLayout(BadgeLayoutID) go I get the SQL error: Server: Msg 547, Level 16, State 1, Line 1 ALTER TABLE statement conflicted with ...more >>

I have a complex problem relating to the summing of costs on workorders at our pulp manufacturing plant. Workorders are used to track all maintenance costs at the plant and they have a parent child hierarchical structure. For major planned maintenance we set up a parent child hierarchy for all...more >>

I pulled thi from BOL... In Transact-SQL, use sp_executesql: DECLARE @MyIntParm INT SET @MyIntParm = 1 EXEC sp_executesql N'SELECT * FROM Northwind.dbo.Products WHERE CategoryID = @Parm', N'@Parm INT', @MyIntParm What are the leading N's for in the SELECT statement? TIA, Ste...more >>

I have a MS SQL Server Connection string, and I want to parse the database name, server name, username and password out of it - without having to manually parse the string. I figured there would be a function in the SQL server client components (or something) but I cant find it. Is there one, or ...more >>

Hi is there a way to do a radom select eg: i need to select from 1million rec one record at radom but not the first not the last select rand() * from table1 where catid = 12 thankx i appriciate it John ...more >>

Hi there, I'm using an index on a Datetime field. When I try to run this: SELECT FROM Products WHERE Product_date<getdate()-30 There is an Index Seek working and being used but the query takes huge amount of time, I have no idea why! Any help? ...more >>

is it possible to get a copy of a DB using T-SQL? I have a workstation that only has Database access to the server, and they don't have any commonly accessable network file shares. Is DTS or maybe replication a possibility here? The workstation will have MSDE installed. Any tips or adv...more >>

I got the following problem, i got the following stored procedure that i pass as X1 , X2, Y1, Y2 decimal numbers like 3.545 and any other kind of decimal number, the problem is that in the calculations it seems to be rounding the numbers!!!! even more , when i check in the last select the valu...more >>

how to get date of all mondays in a month thanks priya...more >>

Is there a way to convert mulitple LIKE clauses into one clause similar to the IN clause? Example: SELECT Name FROM table1 WHERE Name NOT LIKE 'Dave%' AND Name NOT LIKE 'Roger%' AND Name NOT LIKE 'Steve%' I want to rewrite the above query so that it is something like this: SELECT Nam...more >>

I have used ODBC to set up my database connection to SQL Server 8.0. I was able to use windows authentication, but I was not able to use SQL Server authentication - I get an error 18435 with SQL Server authentication. Second, when I run my program using IIS and ASP using SQL Server Datab...more >>

Hello All Below are 2 alter table statements that I am trying to execute. If I execute them individually, they work fine. But if I put them in a stored procedure in the sequence as below, I get an error. It seems to execute the second alter table before the first one and generates the error th...more >>

Hi, I am facing problem in creating partition views. My table is like this Create table Table1 (Id int Identity(1,1) , Name Varchar(50), CustomerId int Not Null Check (CustomerId = 1),, Primary Key Clustered (Id,CustomerId)) Create table Table2 (Id int Identity(1,1), Name Varchar(5...more >>

I am creating a table in query analyzer on an XP client. However, when I use enterprise manager to check the tables, the new table is not there! The scripts execute correctly, the correct database is being used and I refresh the database before checking if the table is there. The only way I ...more >>

sql2k sp3 Is there any beinfit to using @@error instead of xact_abort? I have had a proc that set several parameters to @@error and then ended like: if @param 1 = 0 and @param 2 = 0 commit else begin rollback end Then yesterday I read about xact_abort. So Im curious as to the be...more >>

Hi everyone, I'm running a select statement like so: select fID, Field1 from myTable where Field2=2107 or Field2 = 2108 I get back results like this: fID Field1 34 AC 34 WD 77 AC 79 AC 79 WD (Note that fID isn't ...more >>

I would like to write a searching function like below description (like as goolge.com): When user type the sentence: expert ASP programmer in the world then the search result will not exactly match that sentence, some result may just match "ASP", some just match "programmer", or just match ...more >>

Hi, I want to do data paging in SQL Server Stored Procedure. Something similer to ADO. Not the ADO.net. ADO.net doesn't support something like that. Actually i want to pass the page size, Starting from values to a stored procedure and gets the particular records set. I have done following piec...more >>

All, Man, this drives me nut. I read and read the reference, and then do many tries, but failed to add a constraint to an existing column. For example, I have table CUSTOMER that have column sale_man. I want to add a constrant to the column sale_man to make this column has a default valu...more >>

I'm trying to do a multiple query SQL statement that I'm trying to run in a transaction. The first query modifies a field and the second checks to see where that field is the new value, with some other criteria. EG: UPDATE T1 SET FIELD1 = 1 FROM TABLE1 T1 INNER JOIN TABLE2 T2 ON T1.PRI_KEY...more >>

Hi all - I have a list of serial numbers. I am trying to generate a query that will return a list of numbers where the serial number matches any other serial number in the last 4 digits. For example, in the following list, the items with arrows next to the serial number would qualify. ...more >>

I'm still have problems with the stored procedure parameter. From the suggestions, I used coalesce ... that will not work because my parameter could have several values. EX. create procedure simple_test @input_name as varchar(25) as select user_name fr...more >>

I didn't see the first post show up in an hour, so this is re-post, sorry How do I ref a table as a variable.. say declare @myTable as nvarchar(20) set @myTable = 'TestTable' and execute T-SQL commands select * from @myTable or CREATE TABLE @myTable (fields) or INSERT INTO @myTab...more >>

OK everyone i would like a good book on improving SQL Server Performance. Im not looking for general concepts, i already know most of them like Horizontal/Vertical Partitioning, Index Strategies, Hardware Stuff, ect. What i want is specific SQL Server stuff you can do to improve performance. A g...more >>

I am hoping someone can give me some direction on this. I have an ASP page that is being run from a 2003 Windows Server accessing a 2000 SQL DB. When I load the page, it connects with no problems and pulls results back for the DB to be added to a drop down list. Once the user pick an item from...more >>

Hi I have a table with a date format of Jun 9 2003 and would like convert it to 09-Jun-2003. convert(char,t1.activation_dt,105) give me the date in 09-06-2003 format. How can I get the date in 09-Jun-2003 format? I am using SQL 6.5. Thank you Aj ...more >>

I have stored procedure that creates a temporary table named ##NewTempTable. Is there a way to test for the existence of ##NewTempTable before I create it? I want my stored procedure to create ##NewTempTable only if it hasn't already been created. Please help! Thanks. Derek...more >>

I would like to be able to stop and start my SQLSVRAGENT and MSSQL services on a scheduled basis on my server. Does anyone have any ideas on how to do this? Thanks Brian...more >>

When adding a record in Access it was easy to rely on the Autonumber field to create a unique record, Record.AddNew was all you had to do. But SQL doesn't have Autonumber, so what's the best way to accomplish the same thing as Access's AutoNumber in SQL? ...more >>

what is incorrect in this select: select DateEntered from Data_Out n join MaxDate_Out m on n.ID= m.ID and (isnull(n.DateEntered, '1/1/1900') =isnull (m.maxdate, '1/1/1900')) and maxdate between (case When DatePart (dw , getdate ())=2 Then (getDate()-3 ) When DatePart (dw , getdate ())=4...more >>

I have a table for contest entrants that has an identiy field called app_id. The increment is set to one. I have a procedure written to insert rows into the table and return the identity of the insert field using @@idnetity. When i test the functionality all is well. When i put the pag...more >>

How can I access an Oracle PL/SQL Record data type from Sql Server stored procedure? If this can not be done through a stored procedure, how can it be done? Thanks, Shiran...more >>

I am passing parameters to a stored procedure. If a user leaves the parameter blank, I would like to set the database item equal to itself. Ex. create procedure simple_test @input_name as varchar(25) as select user_name from test_table where us...more >>

Hi, i'm just wondering how can i do the following query I GOT 2 TABLES ( Examples Table No Based on my DB ) Invoices InvoiceItems InvoiceID--1--- ItemID ClientName I Line Date I Descrip...more >>

Hello everyone, The scenario is this...My application is a web app. ASP page uses COM+ to execute Store procs. I have store procs that run at night and some that executes by users request through ASP. My store procs can be multi level. Ex. SP-1 can be called from COM+. When SP-1 is execut...more >>

I am learning to build an user defined function. I have the following codes: create table tmp (f1 char(1)) GO insert tmp values ('A') insert tmp values ('B') GO CREATE FUNCTION fn_try (@getletter CHAR(1)) RETURNS CHAR(2) AS BEGIN DECLARE @RET_VAL CHAR(2) IF @getletter IN ('A',...more >>

How do you check a table to see if it has an Identity field? I have a routine that loops through all my tables and I run DBCC CHECKIDENT against them. I get a message like this on the tables without an Identity field.; <snip> Server: Msg 7997, Level 16, State 1, Line 1 'EventsSecurity' doe...more >>

Hi does anyone know how I would select the last entered entry in a table I know the Top command and thought there might be something similar for the last entry..thanks for any help...more >>

Can i execute *.sql files from within a stored procedure or dts package? ...more >>

I use ado's connection to execute sqls, The execute's string includes two sqls, When one sql has error,the error can not be raised. How can I catch the errors? example: conn.execute "select * from table1;select * from table2" ...more >>

Please help, I tried to create a user-defined function but with no success. Even for the example from help: CREATE FUNCTION CubicVolume -- Input dimensions in centimeters (@CubeLength decimal(4,1), @CubeWidth decimal(4,1), @CubeHeight decimal(4,1) ) RETURNS decimal(12,3) -- Cubic ...more >>

Dear all, Is there a way of changing the error level in SQL? I want to insert records into a table with a primary key, and there will be some duplicates. Rather than the statement failing with an error when it encounters a duplicate key, I want to be able to change the error to a warning and ...more >>