Hi, We have a database in SQL Server 2000. I made a copy of this database(mdf,ndf,ldf) and attached it to SQL Server 2005 on the same machine. When I try one particular SP in both of them, SQL Server 2000 performs almost 2.5 times faster than 2005. The execution plan in 2005 contains HASH p...more >>

I probably didnt make sense the first time around so I'll try again. I'm selecting one field from a tbl but with a result set of more than one row and I want to bring back all the rows in a single string. -- obelix "Whether you think you can or you think you cant you are right" .... Anon ...more >>

I'm reading one value in a select but I want to return it as a string in a single row.Please help -- obelix "Whether you think you can or you think you cant you are right" .... Anon Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/20061...more >>

According to the results of ShellExecute, it seems that I can have a successful OSQL run that wasn't really successful. For example, if I perform a "Restore database" using OSQL and the restore could not get an exclusive lock on the db, ShellExecute returns a successful result but the message fr...more >>

Based on this query..... how can I total just the starred SYS_OP's produced column Compute by will sum them all. I want daily totals of just the starred operations but still list them all. DECLARE @sDate SmallDateTime DECLARE @sEnd SmallDateTime DECLARE @sPart Char(15) SET @sDate = '2006...more >>

Is this an issue or should I just leave it? I use Management Studio in 2005 to create a view. If I type in my SQL, it changes the SQL and the syntax is a heck of a lot longer and I don't know if it screws up the efficiency of the view too. Here is an example: Where (AAA = 0) And (BBB = 1 ...more >>

If I execute the folowing statment against the table that I have in my database... SELECT Customers.Name, Customers.Active FROM Customers ....the result will be: Name (varchar) Active (bit) Customer1 1 Customer2 0 Customer3 ...more >>

hello everyone, i started using the coalesce function to return empty strings instead of the keyword null if a column contains a null value however, this function breaks if the column data type is smallint (or any number type i think) example data: STKNU|ENGHP AAAAA|NULL BBBBB|300 i...more >>

I've gotten a request from a user to create a report I'm not sure I know how to create. There are two tables, one a product table, the other a customer table. The request is to give a listing per customer of products they have NOT bought. i.e. to identify possible sales opportunities in a...more >>

Hi, I used to have a giant SQL Server 2000 poster that was a diagram of all the system tables, but in my most recent move it has disappeared. While I still have my 2005 system views diagram poster (from the Rock the launch event), I would like to get one of the 2000 tables to post on my wa...more >>

Hi, I am using this query to list all my trigger that are used but is there a way to tell if they are enabled or not? SELECT name, crdate, refdate, stats_schema_ver, type, schema_ver, parent_obj, base_schema_ver, status, info FROM sysobjects WHERE (type = 'TR') ORDER BY...more >>

I'm new to SQL and I can't seem to figure out how to do this. I have multiple SET and CASE statements that I want to process. They work if I run them one at a time but it doesn't work if I run the entire sequence. I'm not sure if it's because I refer to previously created fields later in the s...more >>

I’m using XQUERY to slog through an XML doc, and dump the value of a CDATA element into an NVARCHAR(MAX) field. The problem I’m running into is that when an ampersand goes into that field, what I get back out in a query (using FOR XML) is “&”. using: CONVERT( nvarchar(MAX), T.c...more >>

im running a vb.net 2003 application and it is using a data adapter to sql2005. The data adapter has an Inser command which is as follows SELECT Licence, LicenceIndex, IsMaster, Title, ForeName, SurName, Position, DayTelephone, DayExt, EmergancyNumber, MobileNumber, Facismile, ...more >>

Hi All, I'm writing a stored procedure to insert data into a table that has a text field (SQL 2000). I'd like to compare the input data prior to making the insert so that I don't get duplicates of the text data in the table. How can I do this? The server won't let me do a select ... where...more >>

I've spent a couple days going over online book on SSIS. It looks too complicated. Is this just me or others feel the same way? is there any good book or tutorial out there? Thanks Ken ...more >>

How do I do nothing? I have a statement that only formats correctly in one format and I have an if else. Right now I do a PRINT '' but that seems hoaky...any suggestions? DECLARE @flg int SELECT @flg = id FROM jobStatusFlags WHERE quoteNumber='{0}' AND unit = {1} IF @flg > 0 PRINT '...more >>

Here is the situation: I have a table with a latitude and a longitude column, this table has like 5000 rows. So in my web app, I get a latitude and longitude coordinate, and a range from the user. I need to compute the distance between the 2 points(user lat/long and every lat/long in the table...more >>

I have a table with a list of names (example: Aaron, Ben, Chris, David, etc.) and a second table in a different database with a list of names and id's (example: Aaron, 1) some of the names from the first table are not in the second table because they do not have an id number yet. My problem i...more >>

Is there a way to go through the user defined tables and re-organized the colum names of each table? I want all guid fields to be at the top and then sort the rest of the columns of each table. ...more >>

I need to create a stored procedure similar to the following: create procedure dbo.pSync ( @in_db char (20), @in_server char (50) ) AS INSERT INTO Table1 (tab_col1, tab_col2) SELECT tab_col1, tab_col2 FROM @in_server.@in_db.dbo.ARCustomer WHERE ccst_salesman=@in_salesman But, it see...more >>

Can I directly apply SP4 to a fresh installation of SS2K off the CD, or do I have to download and install all the previous service packs first? Paul ...more >>

Hi All: I am getting an error with this line of code: Case dbo.hbs_pohd.variance_dollars_ When Sum(dbo.hbs_pohd.invoice_dollars_- dbo.hbs_pohd.total_budget_ - dbo.hbs_pohd.variance_dollars_)> 0 Then Sum(dbo.hbs_pohd.invoice_dollars_ - dbo.hbs_pohd.total_budget_ - dbo.hbs_pohd.variance_dol...more >>

hello everyone, i need help with a query i designed. i'm not sure why the description = column shows up as null for stknu 1300. SELECT I1.STKNU AS [Unique ID], I1.MODMK AS Make, I1.MODEL AS Model, = I1.MODYR AS Year , (CASE I2.AMTPR WHEN .00 THEN 'Call For Price' ELSE CAST(I2.AMTPR AS = V...more >>

Up until last night, everything with our production SQL Server was running just fine, but a few jobs failed and now I have a problem I can't figure out.... Actually, there were three things that went South last night.... don't know if any of them relate to my problem, but I'll just state th...more >>

There is process that dynamically buids a list to include in the where clause so select * from blah where ID in ('afa','asd','afafsds') but at times we would like to just put in a wild card into the in clause. So is this possible? Select * from blah where id in (%) kind of like select *...more >>

Is there any problem(s) with doing log backups on the system tables on SS 2000? Is this a "best practices" thing?...more >>

need an sql statement that will get generate a single record for each duplicate data and also for unique records that might be in either table A or table B left outer join? ...more >>

How do I export the results of the query into a text file using Visual Basic. i have looked at several groups for the result, with no luck My current code:- Option Explicit Private CnSql As New ADODB.Connection Private RsSql As ADODB.Recordset Public Function CnSql.Open "PROVIDER=MSD...more >>

Hi, I need to compare 2 dates to see if they are equal but without the time stamp. where process_date = currentdate (both without time: hh:mm:ss) Surprisingly this search has led me to investigate julian dates, casting, converting, datediff, dateadd and and everything in between. How hard...more >>

I'm new to DTS. How do i run a DTS Package saved in SQL Server? ...more >>

Hi, I want to copy a row into the same table. I tried INSERT INTO PRODUCTS SELECT * FROM PRODUCTS WHERE ProductId=2 ProductId is an identity column in Products table. But the above statement doesn't work. How can I make a copy of a row in the same table and I want to do this in on...more >>

This query list all the user tables in the database along with their sizes: I am trying to put COMPUTE clause so i can get the total database size. i am getting an error. anybody knows where should I put COMPUTE claus -----------------------------------------------------------------------------...more >>

This is a continuation of sorts of the thread: UDF that take a UDT as a parameter (CLR) Thanks again Kent I have my interval type (UDT) and have created a number of UDFs that use it. I can see the new type in SS2k5 and am able to create tables that use the type in a column. The UDT is...more >>

I need to take an int representing minutes and convert it to hours, with one decimal place, and put it in a varchar(5). So the code below works, but I'd be interested if there was a more concise way to do it. declare @minutes int select @minutes = 121 select cast((cast((cast(@minutes as de...more >>

1. Can someone help me with the code for reorganizing the data and indexes? 2. And also the code for checking the database integrity and attempt to repair minor errors? Thank you! Svein Erik...more >>

There're two tables, Result, Account Result ====== Account_ID Quantity Reason 1111 123 2222 32 3333 442 4444 567 Account ======= Account_ID Account_Name 2222 xxxx 3333 yyyy I want to write a single update statement t...more >>

Hi all, I got some bug reports on search queries in my enterprise application based on Sql Server 2000. After a session of bug hunting I found a strange behaviour using isnull and null varchar. Steps to recreate the problem: 1) Create a table named "Table1" if exists (select * from dbo....more >>

Hi, I'm using a temporary table in my T-SQL code and I would like to include a unique identifier, i.e. automatically increase for each row in the table. Is there a simple way to achieve this? Adam ...more >>

I'm having a problem with a select query. I want to return a GrandTotal value grouped by name, but the query analyzer throws an error: Column 'ct.Total' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. If I add th...more >>