I have 2 databases with the same table names and want to compare rowcounts between them. How can I do so ? I guess the output should look like DB1Name TableName Rowcount DB2Name TableName Rowcount Status DB1 T1 100 DB2 T...more >>

The following query retrieves the tenth costliest product from a table: ---------------------------------------------- SELECT MAX(Price) FROM Products P1 WHERE 9=(SELECT COUNT(*) FROM Products P2 WHERE P1.Price>P2.Price) ---------------------------------------------- Can anyone explain me t...more >>

Suppose I have a table like this -------------- ID Attribute 1 A 2 B 2 C 3 D 3 E 3 F -------------- How to write a sql query to convert the above table into -------------- ID NewAttribute 1 A 2 B, C 3 D, E, F -------------- Thanks a lot. Roy ...more >>

I've tried to run this 'alter database mydb set read_write' in my DTS (Execute SQL Task ) but it seem has a trouble the process can't stop ... but if i run it in query analyzer it can run and finish immediately How come this can be happen ? ...more >>

I have a view with no table present such as Create table T1 (Col1 int) go Create view V1 as select * from T1 go drop table T1 go select * from V1 I want to be able to run a query against a database that would give me all the views that are inconsistent as above I tried dbcc ch...more >>

Hi, Anyone know how to determine the datetime of the last backup for a database, in code? Thanks, Roger ...more >>

I have two tables, Orders & Order_Detail. I was running the following query: select o.num, o.amount, d.code, d.rate, d.order_date from Orders o inner join Order_Detail d on o.id = d.order_id where d.order_date < 'Jan 1, 2005' The query was much faster if I don't have the "where" part. S...more >>

Hi, In SQL Server 2005, I'm trying to change my endpoint from non-secure to secure. It works great un-secured but when I try to recreate the endpoint by changing the statement From: CREATE ENDPOINT Henry_Endpoint STATE = STARTED AS HTTP ( PATH = '/sql/Henry', AUTHENTICATION = (...more >>

Hi, Does the Log Shipping Setup wizard provide any options to do the database backup and restore after the initialization? what if the table structures are changed and stored procedures are added? If not, does that mean I have to start over the setup process again? THanks Ed...more >>

Hi guys, what is the problem? how can I fix it? DBCC INDEXDEFRAG (0, table1, 2) Server: Msg 3628, Level 16, State 1, Line 1 A floating point exception occurred in the user process. Current transaction is canceled. ...more >>

I'm fairly inexperienced when it comes to database design and programming but since our company is too cheap to hire anyone more experienced, I was hoping someone here could help me out. Our company assembles computers and we want to develop an order processing system, so here is the set up...more >>

I have a table called BatchIteration (DDL below). There is a IsProcessing bit field in this table. There will be multiple computers running multiple threads processing batches. What I need is a query that sets the IsProcessing bit to true of the "TOP 1" row and at the same time selects the row. ...more >>

PLS HELP !!!! I cannot create multiple triggers (AFTER) of command type INSERT. When I create a new one the previous one gets deleted. Prodcut: SQL Server Enterprise Edition. Product Version:8.00.194 (TRM). The funny thing is that this happens on clients production environment while I am a...more >>

I'm executing a delete in a table and the result is the error 431: Could not bind foreign key constraint. Too many tables involved in the query. There are no triggers associate with the table. What's this could be? Thanks. Renata...more >>

any one know how the get the ip address of a source connection to my sql server using a query/stored procedure/ex stored procedure etc. essentially to ip address of the "host" process thanks ...more >>

I have an existing trigger and need to add another exception for the trigger. I started out creating another trigger but run into issues with two triggers on the same table. So I dropped my exisiting trigger and want to combine the two into one. Below is the two triggers but how do I combin...more >>

Recently I've been doing a good amount TSQL programming, and I think it is a phenomenal data query language. However, in my view, it may be a little too much so, because with all of its control constructs and additions to SQL92, it almost gives the appearance of and certainly gives the opport...more >>

I have two tables. One (table1) is look up table that has 48 records for time interval. They are: interval 0:00 - 0:30 0:30 - 1:00 1:00 - 1:30 : : 23:00 - 23:30 23:30 - 24:00 The other table (table2) has real data. It looks like that: Interval user ...more >>

I have a MS SQL database process that is run from ASP.NET. I would like to be able to backup the SQL database using either a full database or transaction log backup immediately before the process is done. Is this possible with ASP.NET and what kind of permissions or role would the SQL connecti...more >>

This is probably a really simple one: Given a table with a DATETIME column, how can I do an = or <> comparison with just the date part? ie. SELECT * FROM MyTable WHERE SOMEFUNC(MyTable.theDateTime) <> SOMEFUNC(@_In_theDataTime) , where SOMEFUNC will just return the date part (ignorin...more >>

how can i use 2 tables from 2 different databases in a view? thanks ...more >>

If I put it inside Transaction, then it shouldn't happen again right? BEGIN TRANS ....... "Hari Prasad" <hari_prasad_k@hotmail.com> wrote in message news:uO7ZwuXjFHA.3256@TK2MSFTNGP12.phx.gbl... > Hi, > > Looks like your database restore is interrupted in-between. > > Thanks > Hari...more >>

Hello all! I want to create a time stamp based on the system date and system time. The format of my timestamp should be YYYYMMDDhhmmss. YYYY year MM month DD day hh hours mm minutes ss seconds Does anyone have a clue on how can I do this? Thanks in advance, Hugo Madureira...more >>

I'm trying, without success, to get a function to return values calculated in a stored procedure. The reason I'm trying for the sp instead of just using a function is that sp promises to decrease my processing time. I know that, ordinarily, I can't call an sp from a UDF unless it's an exte...more >>

given the following table: create table t1 ( id int, description varchar(50) ) and the following stored procedure: create procedure owner.proc1 ( @input1 int, @input2 string, @output1 int output, @output2 string output) as -- unrelated stuff select @output1 = count...more >>

Hi Guys, Iam trying to set a version number to a database table. The main reason is if i add a new column later on i will be able to make sure that table schema need to be updated. I have tried updating the version column in the sysobjects for that table. but couldn't do that. Is it a go...more >>

How can I defragment all the indexes in my databases using dbcc indexdefrag? ...more >>

Hi, Our company has changed the workweek as Saturday to friday . Previously it was Sunday to saturday. I am trying to determine if a given date falls within the week ending date and is using the following formula, but for saturdays, the date is always coming in the previous weekending date....more >>

I have been trying to figure this out for some time and I am sure there must be an easier way than using a cursor. Given the following table (simplified example): ID CUSTID DTE 1 1 7/1/05 2 1 9/1/05 3 2 6/1/05 4 2 10/25/06 5 3 5/2/05 6 3 5/2/06 7 4 5/5/05 Return the max dte value record...more >>

When I attempt to step into a stored procedure from Visual Studio.Net the procedure runs without stopping on any of the breakpoints. I also receive the message "An mdm.exe that supports SQL attach wasn't found on ''. SQL attach aborted." I can debug locally using Visual Studio. This error o...more >>

I'm hoping someone can help with a problem I've bene having ever since I started using SQL Server. Every time I get into a stored procedure, the 'GO's are stripped off and extra commands are placed at the end. I also tried to insert a SET NOCOUNT message, but that was also stripped away when...more >>

Hi, I have a couple of questions about Log Shipping. If i am not mistaking, the log shipping is basically backup the database and log file and copy the files to another server e.g. Standby Server. My first question is, what is the different between Log Shipping and Transactional Repli...more >>

Hi, I have a proc which calculates a large dataset and stores all output into a single table with a calculation id field to identify it. The output is anywhere from 300k rows to 3m rows per calculation. My problem is, the backup log keeps filling up and gives me an error : "The log file ...more >>

I am getting time-out errors in my web application on pages which involve inserts into the dB. The application has not changed a bit so it is not a problem with the ASP code, Please help me trouble shoot and fix this problem. Pages that do a 'select' work without any problem, the problem is o...more >>

I am trying to run a query to one of two delete duplicates records. The process I normally use is use 1) SELECT DISTINCT from the table into a second table 2) Delete all duplicate values in original table 3) Copy the disctinct values from the second table back into the original table Un...more >>

Hi, I'm trying to export a text column into a text file using the bcp command but I keep getting the error message in the subject line. So I checked the database properties and didn't find anything. My Code: bcp "Select Replace(Str(UNITNUM, 12, 0), ' ', '0') FROM PICUadmissions WHERE Admit_D...more >>

Hello Microsoft Developers of newsgroups. I have a suggestion that must propably have thought already. Place a listbox (newsgroups will listed) to move a question (or a comment) from a newsgroup to another, many times questions are asked to irrelevant newsgroups "by accident" Regards....more >>

Wondering if anyone has tried to parse email from an MS Exchange 2000 server user account using WEBDAV SQL Queries? I found a good article at www.codeproject.com/vb/net/AccRemoteExchange.asp , but I am having problems figuring out how to construct the Query to get the individual email body ...more >>

Hello Experts, My problem is that I can't call QA and Profiler through Enterprise Manager from the tools menu. I made some MSDE installations and UnInstallations for debug reasons and then this problem occurred. Step 1) Installing an named instance "MYINSTANCE" Step 2) UnInstalling "MY...more >>

Hi All, I am working on Web Application which deals with history data for reports and keeping track of changes. Current Solution : 1) For each Entity I am having a column TID (Tracking ID) which keep on increasing for an instance of Entity. (so history and present data in the same t...more >>

Hi All, Finding out Nth Maximum or Nth Minimum from Table's Column For Finding Nth Maximun (1) select qty from tmpJSales T1 where @n = (select count(distinct qty) from tmpJSales where qty >= T1.qty ) Purpose: To find out the nth highest number in a column. Eg: Second highest salary f...more >>

regards,...more >>

Could anyone please give me a quick head start on the debugger in SQL Query Analyzer. I right click on my 'sp' and select debug. This shows my code in the right pane. However I seem unable to step though. The only buttons highlighted are 'Go' and the 2 breakpoint buttons. Only when I clic...more >>

Dear all, A couple of days ago I posted a request but nobody answered it. I need a task which reports me all the NT users and groups in every server/database and of course, their permissions. The following excerpt is a part of the work: declare @sql varchar(300) declare @USER as...more >>

Problem regarding getting an XML script from a stored procedure that returns XML string format of a select query on a temporary table created by the stored procedure itself and values also inserted within the stored procedure. ...more >>

select '','''','''''','''''''' returns ' '' ''' I dont understand how the single quotes function here Madhivanan ...more >>

Hi all I've got the following table which I'll call tZipStuff with th following data (reduced greatly since the actual table has about 1. million rows) OrigZipStart, OrigZipEnd, DestZipStart, DestZipEnd, SomeType, SomeVa 52001, 52999, 30001, 31999, A, ...more >>

...more >>

Hi all When I updated database I got the error message that size of row can't greater than 8060. Howerver, I think there must be a solution because 8060 is too small. Help me! Thanks ...more >>

I have a table that is ~ 25 fields wide and ~ 35,000 rows deep. I need to update it in a particular way and have no real idea the best way to go about this. Ignoring the majority of the fields, let us imagine that there are 10 fields: id objectID objectCode ref1 re...more >>

I've 3 problems. First, I've data like this: Date1 Date2 -------------------------- 1/1/2005 1/7/2005 {m/d/yyyy} 1/8/2005 1/14/2005 1/15/2005 1/21/2005 If I input date 1/9/2005, so data at record 2 is showed. Because date 1/9/2005 in between 1/8/200...more >>

I ask to you. How to delete data at table A that exist at table B. Usually I write like this: DELETE FROM TA where NOID in (SELECT NOID FROM TB). But it can works, if at table A (TA) has 1 field to be primary key. If table A (TA) has 4 fields to be primary key. How its syntax so good? ...more >>

Hi, we have a big stored proc wich take 15 second to run on the analizer but the same query as a strored proc take 90 sec. Can any one let me understand the difference ?? Thanks to all Paolol...more >>

how i can import a text file without commas to an sql table, knowing the subject of every clomn in the text file (col 1 to col 8- name, col 9 to col 15 - address e.c.)? ...more >>

I have a table with data create table a ( a01 char(4), a02 char(4), a03 int ); insert into a values('a','b',1); insert into a values('a','c',1); insert into a values('x','c',1); insert into a values('c','g',1); insert into a values('b','h',1); insert into a va...more >>

I have to get a count of records using dynamic sql. I have the following and I'm getting errors (Syntax error converting the varchar value 'SELECT ' to a column of data type int.): [Code] DECLARE @sCalldate varchar(10) , @sAgentId varchar(10), @sProject varchar(10) DECLARE @SQL varchar(80...more >>

I'm struggling with what i think should be a very simple query I have a table called vp which has several fields, the ones i am interested in ar vp_id, Version, Lastupdate_date and ln_no vp_id version lastupdate_date ln_no 1 a 01/01/05 ln001 2 ...more >>

Hi all, Is it possible to read a log file to see what changes have been done on a particular table at a particular time? And if yes, how? Thanks, Ivan ...more >>

Hi, I defined a proxy account that executes the xp_cmdshell. It is a local user. Everything works fine for few hours, my web users are logging into the application while this proxy account checks for identification by an xp_cmdshell. After few hours the application generates an error messag...more >>

I have a procedure that I'm trying to produce for a client. They want to see total order counts each day from the 1st to the end of the month. The procedure I have now will produce order counts for days that do exist. It is using the order open date to pull orders from the orders table from ...more >>

I know how to capture the error number using @@ERROR, but I don't know how to capture the actual text. It is easy to do so via an application that uses a database driver (BDE, ADO .NET etc...), but I just want to capture the error text in SQL script and log it to a table. Please Note: 'sele...more >>