Which one would be preferred? Will the choice affect index performance? -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 11:50pm up 2 days 3:29 load average: 0.99 0.97 0.98...more >>

hello, How to insert the dso code(contains cube partition) into my stroed procedure.Is it possible.Actuall i want to call the procedure that will create the cube partions.where should i write the dso code?in vb or c#?That code can i execute in my stored procedure??pls clarify me......more >>

Is there a command to wait for a few milliseconds if not microseconds? WAIT DELAY doesn't support such a small time if I read the BOL correctly? -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.22-xfs ^ ^ 8:40pm up 2 days...more >>

can I do this while the connetion is alive? begin transaction execute sp_update_table_001 execute sp_update_table_002 commit/rollback If the connection dies before the commit, will SQL server rollback the transaction automatically? -- .~. Might, Courage, Vision. In Linux We T...more >>

I want to take the based table which I have defined and make a query/stored procedure which shows year to date values as well as the current. (Basically the origninal table two more fields for YTD). Any ideas how? --Base table create table budgets ( UserID varchar(4), Yea int, Mth...more >>

Hi, I need to join last record of a 0/1-to-many related table match. So far I can do it with this: Select <blah> from Table T1 left join (select top 1 * from OtherTable Order by date desc) JoinedTable T2 Where T1.blah = T2.blah Is there some equivalent for this (join top 1 or so)? Re...more >>

Hi, I have a dynamic SP using sp_executesql and am having some trouble. I want to assign the result of the SP (which is a count), to a local variable to use in another SP. For example, I have ALTER PROCEDURE sp_DynamicSP ( @userID int, @dateRange int, @beginningDate datetime, @endin...more >>

What is wrong with my CASE statement in the ORDER BY clause? This works fine... CASE @Column WHEN 'manuf_asc' THEN #TempNR.Manuf END ASC But I want to add another column, and it does not work. Syntax error... CASE @Column WHEN 'manuf_asc' THEN #TempNR.Manuf, #TempNR.Label END ASC thanks! --...more >>

Is it possible to ALTER COLUMN to convert a column into an IDENTIY column, from a script? (I know you can do this from Enterprise Manager - that doesn't help) So far, eveything I've seen on this says "NO" Can anyone confirm this for certain? If it IS possible... how? I can locate at d...more >>

Hi, My TRIGGERTABLE include update,insert,delete trigger.. Those triggers changes values in TOTAL table.DIFFERNTTABLE is only copy of TRIGGERTABLE (include no trigger,i create this table for test) When i insert ,update or delete one row in TRIGGERTABLE, trigger is working so TOTAL table is chan...more >>

Is there any API, either in SQL Server 2000 or a third-party product, which will allow one to display the same kind of display as in {table}->Open->Query? I notice that the interface for that command is very similar to the interface to the query designer in VS.NET, so I hope to find that they us...more >>

hello i have sql statement select * from OPENQUERY(linkserver, 'select issue-number,cover-date from issue where number = 92219 and cover-date = (select cover-date from issue where number = 92219 and cover-date = 08/25/2003)-1 my problem is not working the sql. this sql suppose to bring prior...more >>

Hallo All I am completely new, forgive me if this sounds one kind. I am working on a project to keep tracK of IT Assets. I have a master table containing all information that are general to assets of all kinds like prices, date of purchese, lifecircle etc. I have as many child-tables as the nu...more >>

Alright - how about this: I uploaded an image of the current data model at: http://www.jasonmackenzie.net/data%20model.jpg Basically there is a hierarchy in place that goes like this: Area > Line > Category > Reason > Station > Device What I'm trying to come up with is a good way to m...more >>

Hello. I need to SUM the TOP 140 rows. I need to SUM the BOTTOM 10 rows. I need a condition if (SUM of TOP 140 rows>=A and SUM of BOTTOM 10<=B) then__ Select TOP 140 Amt From TempAmtFiles I get the row set - but need to SUM(Amt) Any ideas??? Thanks. Lynn....more >>

I have a stored procedure that should return 1. Get rows within a date range and company name 2. Add the values for each day, and return totals. For example I have a table: NAME FORDATE AMOUNT COMPANY SINGLES 12/1/01 30 CDA DOUBLES 12/1/01 20 CDA SINGLES ...more >>

Hi all im sure i have used sub queries in my checkconstraints (e.g. to make sure sum of all transactions for a customer did not exceed his credit limit) I no cannot do this as i now get an error saying no sub queries allowed in check constraints Im sure i uesd to do this i even found ...more >>

Hi Quick question. Below is what I am trying to run, and the error message I am getting. Any help is appreciated! Thanks in advance select * from dev-pfwebsql..CategoryLinksReportCorrelatio Error Messag Server: Msg 170, Level 15, State 1, Line Line 1: Incorrect syntax near '-'....more >>

is that normal ? DECLARE @n NUMERIC(5,2) SET @n='999.999' SELECT @n result in Query Analyzer is: ------- 1000.00 (1 row(s) affected) without quotes gives it expected result: Server: Msg 8115, Level 16, State 8, Line 2 Arithmetic overflow error converting num...more >>

Getting no help in the ms access NG's so I'm giving it a shot here (not any results in ODBC NG either): I'm finding zero good, clear, uncomplicated info on how to pass RAISERROR info from an MS SQL 2k stored procedure or trigger to an MS Access 2k (not ADP) front end. Can anyone help me out h...more >>

Hi I am getting an error on this quer select * from OPENQUERY(PROGLINK, 'select * from issue where number = 9224 and cover-date is >'& date Server: Msg 170, Level 15, State 1, Line Line 2: Incorrect syntax near '&' ...more >>

Hi! Is it true, that the performance on parameterized queries is excatly the same as stored procedures? Answers and references would be greatly appreciated! :) - Klaus ...more >>

Hi, I have a table with a autonumber primary key column. After each INSERT statement, What would be the most efficient way to know what was the last generated autonumber for that autonumber column? Obviously I can Use SELECT Max(ID) ... to find that, but i am looking for a better way. Th...more >>

Hi all, I need to creat a view in sql server. It need to group records. my question is how do I group records like following example: Field1 field2 field3 A 001 N A 002 A I need the record with max(field2) and eliminate first one. Thanks ...more >>

I currently have a file as follows: code part cost 01 AB 1.25 01 AB 1.25 01 CD 3.44 01 CD 3.44 01 EF 7.25 01 EF 7.25 02 AB 1.30 02 AB 1.30 What I want to end up as an...more >>

Hi, I have been, up to now, storing images in standard directories, and entering the image reference information in an imageref column in the SQL table. Then using a bit of VBscript to have the image displayed: <img src="<% (rsRecordset.Field.Item("imageref").Value%>" border="0" alt="some tex...more >>

I need to create global Function. for example, getdate() is global function , which can be run on any dbs Let's say I want to create getdate2() as global function, is it even possible? ...more >>

i have a table which keeps the customers info like below: cus time cus1 8:15am cus2 10:15am when the time is met( like right now is 8:15am), it will go do something(possibly copy some files into some directory), is any trigger can help combined with other c# code? Thanks. ...more >>

Hello, I have a db with two tables Users User ID int User Name text User Password text Bugs Creator ID int Assigned To ID int Description text And I want to create a view that ...more >>

Does anyone know if there is a way of determining what action fired a trigger ? i.e. the stored procedure that executed a insert/update. I have some invalid records appearing in my database under a certain user name(even though the user has not used the system), after days of analysing store...more >>

I would like to create a Job scheduled to run every night. This job would execute a stored procedure and dump the results into a tab delimited text file in a specified directory for archiving. How can I do this? Can I do it with in EM? -- moondaddy@nospam.com ...more >>

I'm using MS SQL Server 2000. Suppose I have a query like this: (1) UPDATE ToBeChanged (2) SET Column1 = 'Something' (3) WHERE EXISTS (4) (SELECT * (5) FROM Table2 (6) INNER JOIN Table3 (7) ON ...more >>

Hi I have a table with the foll data col1 col 2002 2002 2002 1 2003 2003 9 2003 I want to have a select statement such that if I say get the previous value from 2 in col2 where col1 = 2003. It should bring back 99. if I say get the previous value from 99 in col2 where col1 = 2...more >>

Hello! I have a query like this: select id,name from t1 union all select id,name from t2 union all select id,name from t3 I guess you see what I want ... Now ... I cannot use this query for indexed view ... is there another way to get same result but without UNION ? Thanks for you inp...more >>

How do i create a procedure in master db which can be recognized in every other dbs. For instance, sp_help is a system procedure that exists in master db. but you can run this procedure in any other dbs as well. How can I create such a global procedure? ...more >>

If I catch a sql error in VB 6 like this objDB.ExecuteWithResults (storedproc If Err <> 0 The tbxStatus = "DB Err" + Err.Descriptio objDMO.RollbackTransactio Els How can I get the line number where the error occured?...more >>

Here is tricky one: How do I delete like this: TableA: A B 1 1 1 1 1 1 2 1 2 1 2 1 3 2 3 2 4 2 4 2 I need to leave records with max A value for each subset with B value. Result would be: (I left only records with maximum value in A for each val...more >>

I have a procedure that will take few mins. to execute. What is the best method to return ongoing progress related information to client app. from the procedure being called as well as sub-procedures? ...more >>

Hi all If I have a table below PrimaryKey Name TestCode Result UnitNumber 1 aaaa %UN 12345NN 2 aaaa TOOL 1 3 bbbb %UN 11AAAAA 4 bbbb %UN DDD2323 5 bbbb TOOL 1 6 bbbb %UN QWQW...more >>

There is a text datatype on Column ReportText. I am trying to do a cursor on a temp table. I am not sure why I am getting this error. It ran correctly when I select individual lines, but I got the error below when running as a SP. Any help is appreciated! DECLARE curCorrelation CURSOR FO SE...more >>

Hi guys, I am am just wondering whether we can use a dynamic sql with a cursor to loop thru all the recodes in the dynamic sql query ? any ides how to do this ? -Thanks -Aruna ...more >>

I am trying to use an 'ORDER BY' statement based on an input parameter eg SELECT * FROM tblMyTable ORDER BY @MyParam where @MyParam could be date or name or age dependent upon an input. SQL doesn't let me use a variable in the order by statement. Any Ideas ? Thanks in advance ...more >>

What i need is to do is basically create a list of numeric ids based on characters I am looking for a query that might be somthing like below Select someFunction(title), name from employee The employee table might be: Title Name --------------- manager bob assistant joe secretary j...more >>

I am writting a report that shows for any given date how many orders each customer had on that day, that week to date, and that month to date Using the Northwind database to demonstrate, I have created the following script to return these results. It works, but seems like it is a VERY inefficent w...more >>

I'm transferring data from Access db to SQL Server. All Text data type fields were converted to nvarchar data type in SQL Server by default. I cannot find in BOL the explanation for what's the difference between varchar and nvarchar except that nvarchar is Unicode Character data type. I'm in USA...more >>

What is the preferred way to create indexes; as part of the table definition or with the CREATE INDEX statement? Why might I want to use one vs. the other? Most of the books I've read only how to do each, but don't explain any benefits in one way of the other....more >>

One of the highly publicized new features of the next version of SQL Server is the integration of the .NET Common Language Runtime (CLR). Join members of the SQL Server development team and discuss the merits of this feature, when it makes sense, what it will be used for, and if .NET programming ...more >>

I'm looking for the T-SQL syntax to convert seconds to hh:mm:ss format. So 17890 would become 04:58:10 I've written one method just doing the math, but it's not pretty, I was hoping for a more elegant solution. thx Jeff ...more >>

Good day to you. I am working with some large databases with several tables hosting millions or tens of millions of records. The actual data is being stored on a SAN, while the database host is a 4 CPU SMP machine. In this case the SAN is often a bottleneck while the CPUs are idled. A situa...more >>

Do the @subject and @message parameters for xp_sendmail in SQL2K support nvarchar data? I have a unicode string stored in nvarchars that do not print our correctly in the resulting email. How would I troubleshoot this? ...more >>

I am trying to figure out how to script a database restore. I found some examples of using SQL-DMO, but wanted to know how to get a listing per database of the available restore history like is displayed when using Enterprise Manager to restoer a database. Does anyone know how to get that ...more >>

Hi all, Well I am baffled. Can anyone tell me why these two virtually same queries return different counts? Query 1 returns a count = 0 while query 2 returns a count = 1. I have listed some very simple code you can try in order to see what I am talking about. create table ##junk ( La...more >>

Can someone please tell me where the Reporting Services are located, either for download or on a MSDN disk. Thanks ...more >>

I have a fairly large dataset that has to be uploaded in to the SQL server table. I use data adapters command update, and that seems to work pretty well ( all records make in to the table and nothing gets left out). However, this mechanism is pretty slow when it come to 80,000 rows(the largest i ...more >>

I'm working in a data warehouse environment and we dump all of our data into a source table and all fields are of type varchar. Then we have procedures that do error checking on all of the data and then convert it into the correct data type. I am pulling in data such as 4.514451558489E-2....more >>

Hello Experts, I post this in this newsgroup too, hoping I got faster response here. Thank you. When I Open Enterprise Manager and click on my server, following msg appears: SQL Server Enterprise Manager ---------------------------------------------------- A Connection could not be es...more >>

Thanks to a response to an earlier post I have my basic query working but I am still having a problem getting JUST the one highest score for a given UnitName and ClassName when there are two scores on the same date. The truncated DDL for the tables is at the end of this post. The query I am ...more >>

We imported with Set QUOTED_IDENTIFIER ON... lots of extra characters in description. Is there an easy way to reverse this without reimporting GIAC...more >>

I am looking for T-SQL resource web sites which feature various tips and tricks in T-SQL. Can you please point me to sites for these resources Thanks Simon...more >>

Hi, When I use a rand() function within a SQL server function like, create function test() returns varchar as begin declare @x varchar(15) set @x = cast(rand() as varchar) return @x end I get the error , Msg 443, "Invalid use of rand() within a function" How to overcome thi...more >>

Hi I have an application built on top of SQL server 2000. I would like to be able to create a copy of a table (with all its data), under a new table name. Is it possible do script this either in a stored proc or a transact-sql script Any idea thanks for your time Marie-Eve...more >>

Hi all, I am interfacing SQL Server with a client's COM+ object using the SP_OAMethod. The method in the COM+ component accepts 4 Parameters and sets a value to one of the parameters passed in. I create/execute the sp_OAMethod fine using the following code: -- ** Create instance of Ole Objec...more >>

Hello All I have one file arriving from a legacy system which as different types of records I want to extrapolate and load these records into different tables. The situation is as follows: Flag col1, col2, col3, col4, col5, col6, col7, col8 =================================================...more >>

Hello Experts, I post this in this newsgroup too, hoping I got faster response here. Thank you. When I Open Enterprise Manager and click on my server, following msg appears: SQL Server Enterprise Manager ---------------------------------------------------- A Connection could not be es...more >>

I am converting a .mdb to sql and need to perform a modulus 3 calculation in SQL. if was very east in .mdb the command was just [Integer Name] Mod(3). Is there an equivalent?...more >>

Dir Sirs, i am using vb5 and rdo connection to read from an sql 7 database using user sa but i can't read from system tables as sysobjects Set EnvCommon = rdoEngine(0) MyServer_Login = "sa" MyServer = "myserver" Set Con = EnvCommon.OpenConnection("", rdDriverNoPrompt, ...more >>

What is the best way to store my tables in VSS so that I can recreate my database structure whenever I need to. I have started checking in each table individually (including constraints). The problem with this approach is that I can't recreate my tables one by one because of the FK constraints (erro...more >>

I can't seem to find any resource that describes recommended procedures for create a one-to-one-or-many relationship. How is this accomplished? I want to ensure a record is created in a second table as soon as it's inserted into the parent table - as time goes on, more records will be inserte...more >>

I have a database of many different process parameters, and I need to show the latest data available in a view. I have many different pieces of equipment, all identical, but the parameters will have been measured at different times The table looks something like this equipment_no date ...more >>