In a distributed partitioned view, you split a table into multiple tables of different linked server; and you use a UNION ALL view to join the tables into one logical entity. Most attempts to query the (distributed partitioned) view will result in all the rows in the remote tables to be bro...more >>

HI all, I'm new to sql and trying to learn it. I'd like to replace a field in one column but don't know how to get it work 10AA1234_TEST.ABC --> 10AA1234.TEST I'd like to replace the "_" with the "." and get rid off whatever after the "." the the original string. Please advice. Thanks. ...more >>

Hi, I have a table like with columns like this: foreignid1 foreignid2 somedate pkid othercols filled with data like this: 28 4185 2005-04-14 00:00:00.000 4385 ... 28 4185 2005-04-15 00:00:00.000 8518 ... 36 4439 2005-01-06 00:00:00.000 4421 ... 36 4439 2005-01-08 00:00:00.000 8480 ... ...more >>

Ok, I'm trying to run a query that will leave me with rows that are not distinct. i know how to get the distinct rows but i need to do the opposite. i've been searching for this for an hour. thanks in advance. -- Message posted via http://www.sqlmonster.com...more >>

Hi, I am trying to count the records that are currently stored like this: ss/id Col0 Col1 Col2 Col3 ------------------------------ 100 1 H H S 100 2 H S H 101 1 H H S 101 2 H H S 102 1 S S S 102 ...more >>

Hi, Is it possible to generate SQL Profiler on specific database(pubs) ? thanks ...more >>

I've a SQL view performing a number of very precise calculations ie. 495/(1.112 - (.00043499*((Col1 + Col2 + Col3))) + ( .00000055 * ((Col1 + Col2 + Col3)) * ((Col1 + Col2 + Col3))) - (.00028826 * Col4))) - 450)/100 Obviously this sometimes causes a precision issues because the float and ...more >>

Hi, Is any utility/easyest way to find out the effected tables for a particuler operation ? thanks, ...more >>

Hello there I've build code on query anlyser that create string with some sql to run. The sql that run on the sting is a simple select I would like to know after I run it if it return any records I run it with EXEC ('string') is there a way only to know if this string has returen any r...more >>

Vendor software set password field as binary in sql server 2000 I would like to write some pages to check the user name and password how can i check and match the field? ...more >>

hi i need to remove the ' character from a string in sql table, how can i call it in a query??? i'd like do something like: SELECT * FROM table WHERE name LIKE (% ' %) thanks Carlo ...more >>

Our databases have tables that use a Timestamp column for row-level concurrency checking. All of our Insert and Update stored procedures have an OUTPUT parameter that is supposed to return the value of @@DBTS (which is the last assigned Timestamp for the current database). This value is then ...more >>

Hello all, I'm starting to experiment with an auxiliary calendar table as described in: http://www.aspfaq.com/show.asp?id=2519 Basically I'm trying to determine the numbers of days in a range of dates, taking into account weekdays stored in the calendar table. So if I had this: CREATE...more >>

This query returns 155 rows: select distinct dp_source from clients This query returns 97 rows: select distinct dp_source from clients where dp_source IN(select distinct source from applicat) This query returns 0 rows: select distinct dp_source from clients where dp_source NOT IN(select ...more >>

Hi, I'm a noob when it comes to TSql and Sp's. I just want to add to columns together like so... SELECT MonHrs, TuesHrs, (MonHrs + TuesHrs) as TotHrs From Hours What do I need to do to add these two columns together to get the sum? Thanks, JJ...more >>

Hi, I have a stored procedure that takes quite a lot time to execute. (Around 20-30 minutes.) While running, it prints messages with PRINT(), but they only show after the procedure has completed. Is there any way to somehow get the messages (not neccessarily on screen: in a file or anything) in...more >>

Hi all, I appreciate that this xp is unsupported, but I was hoping that there might be someone here who's run into the same problem and might be able to offer some assistance. When I try to run the procedure (with the example code but changing the server and stuff) I get the following, t...more >>

Hello folks, I very much would like to log errors as they occur. The the following code is immediately after an INSERT and it "almost" works: SET @errnum = @@ERROR IF @errnum <> 0 BEGIN SELECT @errdescr = description FROM Master..sysmessages WHERE error = @err...more >>

I've created a stored procedure to duplicate an order with several child tables. It does the following steps: 1. Insert copy of Order 2. Set Ordernr equal to OrderID 3. Insert copy of OrderProduct 4. Insert copy of OrderKosten 5. Insert copy of OrderTraject 6. Insert copy ...more >>

Hi all, I have a table that looks like the table below. I would like to count each of the column where the dates fall within this calendar year. I would like to produce an end result that shows counts of each column that fits the criteria. Can someone please help me accomplish this? Thank ...more >>

I have a table with a column CategoryName as nvarchar(50). A new category name I am trying to add has 59 charaters in it. I get the typical data will be truncated message. I then increase the table and sp to nvarchar(75), and I am still getting that error. It only allows 50. Are nvarchars st...more >>

Hi, I'm trying to get the name of the server I'm running my SP, but can't have it work. I've found the SP called XP_GETNETNAME, but it does not look to be documented... so, I'm wondering if I should use it. Any idea on how to retrieve the server name into a variable ? thanks, Chris V. ...more >>

Hi All, After I rebuilt my SQL Server and restore the databases. I have a problem with my logins. To fixed this problem I have to go in each database using sql analizer and refresh the login using sp_change_users_login 'auto_fix', 'loginName' I have more than 20 logins and many databases. Ho...more >>

Hello, In my query statement I am joining five tables. The query works fine! However, I am not sure where i have the WHERE clause is the best place. SELECT tb_1.create_date tb_2.email_address, tb_3.file_name, tb_4.virus_name FROM tb_5 INNER JOIN tb_4 ON tb_4.id_viru...more >>

I have the following stored procedure, problem is that sometimes there is no value passed on to @expiration, when this happens I get an error. How can I avoid from getting this error ? ----------------------------- create procedure BlueDotSetDocumentononcurrentAP @catalogId int, @UserId...more >>

Hello I am looking for a way in SQL to find out how many occurances of the search string are in a field, so for example select paragraphtext, [NumberOfMurrays in field] from Paragraphs where paragraphtext like '%murray%' Order by [NumberOfMurrays in field] Does anyone know how to a...more >>

Hi Freinds, SQL 2000 I have a publisher which push to 3 subscribers. How can I drop only one subscriber from the list of subscribers and continue push for two other subscribers. Thanks in advance, Pat ...more >>

Hi Freinds, SQL 2000 How can I drop one article ( table ) from transactional relication that I have ? Thanks, Pat ...more >>

Hi, I have to execute stored procedure that has not records returned from DOS commend line. Any info for it? Regards, Frank...more >>

I am design a database containing BrokerOffices Brokers and BrokerAssistants. (among other things) I do three tables: BrokerOffices, Brokers and BrokerAssistants. All have a 1:M relationship Since an assistant is assitant for one or many brokers I do a table Assistancies containg this M:M re...more >>

Another Newbie question for you guys. Let's say I have two indexes setup in my table. 1. Indexes Col1 only 2. Indexes Col2 only Now I have a query: Select * from MyTalbe Where Col1 = 'ABC' and Col2 = 'XYZ' In this simple example are both indexes used? If no, then I could dro...more >>

Using SQL Server Enterprise Manager, I am trying to change the sa password. When I "right click" on sa and select "properties" I receive the error, Cannot load dll xpstar.dll, or one of the DLL's it references. Reasion: 126(The specified module could not be found.). Any help with this will be ...more >>

I've got a strange problem with SQL Server 2000 running on Windows Server 2003. Every few days (after re-booting) the server CPU utilization goes up to between 80% and 95% (but NOT to 100%) and certain connections from our ASP website OR from a VB6 program will not run: they time-out. Afte...more >>

I recently learned about parameter sniffing and I understand how to deal with it. What I don't understand is why it happens. Is it a bug? Why does it consistently result in less efficient execution plans? Is it only apparent on multiple processor db's? Are there any other configurations that a...more >>

I'm getting better!!!....BUT----- here is my query: select count(stkhstBuySell) as cnyBS, s.stkhstBuySell, s.stkhstDate from stkhst s where stkhstCsiSym IN (select unvmemCsiId from unvmem where unvmemUnvID = 47001) and stkhstBuySell IN ('B', 'S') group by s.stkhstDate, s.stkhstBuySell order...more >>

Hi all, The following below prints out fine. I need to print the line in the order of the greastest to the lowest. using the last variable ending in total? PRINT ' Total for Z'+ SPACE(8) + ' TG = ' + @Z_total PRINT ' Total for H'+ SPACE(8) + ' TG = ' + @H_total PRINT ' Total ...more >>

I am running the query below against a very large database to convert characters to date and I get the following message: Query: select convert(datetime, datefield,101) Error message: Server: Msg 8115, Level 16, State 2, Line 14 Arithmetic overflow error converting expression to data type ...more >>

Hi all, I have an UPDATE that makes changes on records as determined by a subquery. Oddly (to me at least), the records that get affected by the update are different than those that the subquery itself finds if run separately. Can anyone shed some light on this one????? SET ROWCOUNT 10 UP...more >>

I have some 100 commands in a batch to setup my Testdata. How can I see at the end of the batch if an error ocured and rollback? I know can store the error after each command, but thats not feasible here. It would be to much code and the testdata not readable anymore, which is an requirement. ...more >>

I've created new indexes where there were foreign key constraints that didn't have an associated index. When I exeucte the stored procedure and use the show plan option, I find the old access path is still being used. When I run the SQL statements in a normal query, the the new access ...more >>

Why the result of this script is 3? Is it not shoud be 3.3333???? ---------------------------------------------------- declare @var1 INT declare @var2 INT declare @var3 FLOAT set @var1 = 10 ...more >>

Query initial: INSERT INTO JOBS VALUES ( '1999-04-15 11:35:28.123000', 'Clerk', 'Associate', 'Description of a clerk position. Some more description.', 'C01' ); Query fixed: INSERT INTO JOBS VALUES ( CAST('1999-04-15 11:35:28.123000' AS timestamp ), 'Clerk', 'Associate', 'Description of a...more >>

Dear Expert, I would like to have your valuable suggestion on solving the large record search issue: The situation is that there are two tables, A and B. Table A has 60000 records and Table B has 40000 records. My goal is to loop through each record in Table B to find out the record that...more >>

My user has written a Select that can generate a deadlock everytime. Even on a test system with no other running queries. We've found a way around the problem but don't understand why the solution works. Any help understanding this will be greatly appreciated. Here are the 2 queries: Select...more >>

I have a vb6 app that is widely used in my organization that accesses an sql 2000 db using ado/oledb. I am randomly getting errors from only one computer (out of ~100). It appears that SQL Server is getting a different sql command than what my code is sending. My vb6 code looks similar to t...more >>

I am trying to get Sql mail to work. Here is what I have and what I have done. I am using Sql 2000 and just applied sp3a. Also use Outlook 2002 sp1. I am trying to use SQL mail; however, I am not able to see my profile name created under SQL mail. I am able to send/receive emails from out...more >>

Hi folks, I have a database with several tables of different structures, each having an identity column called ID. I want to setup some stored procedures to allow me to delete a record from any table and thought that rather than having one stored procedure per table I could perhaps have on...more >>

Dear All I have series of columns I would like to rename, so I am using the sp_rename command. EXEC sp_rename 'TABLENAME.[OLDCOL]', 'NEWCOL', 'COLUMN' However I am getting the error message:= Server: Msg 15248, Level 11, State 1, Procedure sp_rename, Line 163 Either the parameter @...more >>

Hi, we have next kind of data in our database and we need a query that returns "qty" from last 5 mondays. And from those rows we need to drop out the max and min "qty". Thanks for any help! With best regard, Jahoo date prod warehouse qty (monday)06.12.200...more >>

Hi All I need the Sum() total in the following query to be in 'hh:mm:ss' format... SELECT '2/10' As Shift,COUNT(Machines.[Name]) AS 'No Of Stops', Machines.[Name], SUM(DATEDIFF(SECOND, StartDateTime, EndDateTime)) AS 'Total' FROM Downtimes LEFT JOIN Machines ON Machines.[ID] = Downtime...more >>