Hi, I have my mdf and ldf files but dont have ndf file. How can I attach db without my indeks file ? Thanks Banu...more >>

We often build databases on behalf of our clients and we're still using SQL 7 as a base level as we can generate .BAK files which can be restored to SQL 7 and SQL 2000. I assume that if we upgraded to SQL 2005 on our production server that we will be *unable* to restore .BAK files generated...more >>

Using Visual Studio 2005, SQL Server 2005 It once worked but now in the code I get no value returned for the output parameter @Msg (I get an empty string). However in query analyzer I get the right value. Stored proc: ALTER PROCEDURE [dbo].[GetBad_SCS_AllocationUp] @inSCS_ID INT, @inSNet...more >>

The script below is a cursor. I get the following error when I run it: --{Server: Msg 170, Level 15, State 1, Line 14 Line 14: Incorrect syntax near '@table'.}-- The error is caused by this phrase: -- UPDATE STATISTICS @table -- How do I resolve this. ****************************************...more >>

Can someone tell me where my syntax is wrong in the following SQL statement. It's something to do with the use of the TOP keyword: SELECT distinct top 1 * from calldetails order by calldate desc I am trying to order the table by calldate and them select the most recent record. Many than...more >>

hi Can anyone help in converting A Date format to text and only display month and year? Thanks ...more >>

I have to do a delete from a table with about 25 million rows. I need to delete about 4 million rows, based on the date in another table Table Session SessionID SessionDate 1 1/25/2004 2 7/25/...more >>

It's probably a piece of cake for those in the know but it's proving hard work for me... Scenario: I developing a report for a system that managed a filter exchange business. Basically filters are sent to/from customers to our business. I'm trying to produce a management report which summar...more >>

/* NOTE: you can paste this all in QA i want to perform a 3-way full outer join on 3 tables (in reality it is against 3 views, but my sample DDL here is tables). i want the 3-way join to be Customer,Year,Month Sample DDL*/ CREATE TABLE #SalesOrderStatistics ( Customer int, Year int,...more >>

I have an App that is the front end to a SQL 2000 DB. The business = rules in the App no longer fullfills all the business requirments. At = while ago a consultant was brought in and created a trigger on one of = our tables that connects to a seperate database and gets some details = that are...more >>

Hi there, I have 4 tables: Summary_Table Header_Table Detail_Table_1 Detail_Table_2 Summary_TableID is defined on both Summary_Table and Header_Table. Summary_Table is like a parent table to Header_Table. Header_TableID is defined on Header_Table, Detail_Table_1 and Detail_Table_2....more >>

Hello, I have the following function and it won't let me pass the syntax. Help would be appreciated. CREATE FUNCTION dbo.udf_dispMandReq (@empnum varchar(11)) RETURNS table AS BEGIN declare @divnum int SELECT @divnum = DpsDiv_Num FROM vDPSMain WHERE CAST(DpsIndv_StateNum AS varc...more >>

Here is a fun one, and I am not sure its possible. I have 2 SQL Stored Procedures. The first one calls the second one. All the first one does is grab the next database name and pass that variable to the second stored procedures. Once the second one finishes it goes back to the first one goe...more >>

Hi, I have a table where I want a certain condition with 3 of its fields. I want only 1 of them not to be null. For example, if the fields are A,B,C these combinations are ok: A B C NULL 3432 NULL NULL NULL 554 333 NULL NULL Howere these are not: A B ...more >>

Pardon my persistant newbieness: I have something like this: SELECT SUM(CASE WHEN ogroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Open', SUM(CASE WHEN ogroup = 'Group_Name' AND cgroup = 'Group_Name' THEN 1 ELSE 0 END) AS 'Closed' FROM tablename WHERE customer = 'customer_name' and da...more >>

Hi All I want to know how a certain column called nameID is declared in several tables in a database called DBA. I have noticed that this column has been specified with different datatypes and lenght in the database. I want to know the datatypes, lengths and the tables. Pls help. Thank you ...more >>

The following SQL Sums are not matching up. Can anyone explain why? SUM(column_a - (CASE WHEN column_b =1 then column_a ELSE 0 END)) FROM my_table SUM(column_a) FROM my_table WHERE column_b <> 1 The first one should subtract column_a from itself, when column_b = 1. So it should add u...more >>

How can I concatenate 2 columns, both of type ntext, in a select statement? I get the following error. "Invalid operator for datatype. Operator equals add, type equals ntext" ...more >>

I would like to re-index two tables (t1 and t2) if the logical scan fragmentation is greater 80% on these tables. Please help me create a t-sql script to create this task. Thank You, ...more >>

I have an existing view, I didn't create. It runs, but I was running it through a 3rd party tool Toad to do some tuning and it gve me an error saying, "SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS., CONCAT_NULL_YIELDS_NULL, ANSI_WARNINGS, ANSI_PADDI...more >>

hi all what is an equivalent to VB's dateserial (if any)? thanx, erez...more >>

I am running a Stored Procedure that goes through all the databases and reindexs them. However when it reaches a database that is "read only" the program quits with an error. If there an easy way to determine if the database is Read only and skip it if it is? Using SQL 2000 / 2005 Thanks -Ma...more >>

Hey guys. I've a few sp's. This sp's are doing some inserts and updates in a table I need to setup a job to run every4 hours. And at at 12 am, it's supposed to insert in table1 at 4 am, it's supposed to insert in table2 and truncate table1. at 8 am, it's supposed to insert in table1 Plea...more >>

I want to do something like this. declare @x ntext set @x='' select @x=@x+text from syscomments where object_name(id)= 'report_assetComponent' select @x When I do this, it tells me that ntext can't be a local var. The problem is, if i change the dataType to varchar(8000) it works but it ...more >>

In Sql 2000 or in Sql 2005, what is the best way to INSERT or UPDATE a View? For example, I have three tables: zWoof, zSonOfWoof and zGrandsonOfWoof. Woof has Columns WoofID(key) and WoofName(nvarchar). SonOfWoof has cols SonOfWoofID, WoofID and SonOfWoofName. GrandsonOfWoof has Cols GrandsonO...more >>

Database: SQL Server 2000 Standard (works fine) Database: SQL Server 2005 Standard (fails) Provider: SQLOLEDB We can update recordset field (database: MS SQL 2000, client side cursor, adLockOptimistic), regardless of the recordset's source (query or stored procedure) The problem begins w...more >>

Hello! I am trying to design some tables to solve this problem. The main goal is to have a very good performance, but still have a good design. I am trying to build a search function with a ranking system. The search function can be used to search for cars/hotels or whatever. Each car/hote...more >>

I'm writing an ASP.NET application that uses a SQL Server 2000 database. The application searches in large tables with 500, 000+ Records and then displays the search results, the search results could be easily 20,000 or 30,000 results. Ofcourse i need to use paging to show like 10 or 20 result...more >>

Hey all, I'm having problems with a TSQL query, and I know someone out there can help. Note the following: -- CREATE TABLE #temp ( ID int, ParentID int, Text varchar(50) ) INSERT INTO #temp (ID, ParentID, Text) VALUES (1,null,'Top') INSERT INTO #temp (ID, ParentID, Text) VALUES (...more >>

Hi, I'm trying to run a SP with a select statement. Example below: Stored procedure: uspI_InsertUsername (@Username VARCHAR(50)) SQL Statement: EXEC uspR_InsertUsername SELECT Username FROM TempUser Will this work? -- Thanks, Jig Patel...more >>

Hi, I'm working on an application that uses SQL Server 7 as a back end. Nearly everything in the database references one table with the following columns. generation_id int (primary key) entity_id int effective_from datetime effective_to datetime active tinyint deleted tinyint other te...more >>

Hi; I want to remove trailing zeros from the results produced by stored procedure. SELECT rdts_SizeIN FROM CatalogSQL.dbo.RuptureDiskTypeSize WHERE [rdts_RuptureDiskTypeID]=@rdts_RuptureDiskTypeID and rdts_Active =@Active rdts_SizeIN is a decimal field in the table. Resulting data mayb...more >>

What is the difference in doing a Delete Table where x=y VS. Delete From Table where x=y ...more >>

I have a stored procedure which uses dynamic SQL using parameters from a Windows app. Two of these parameters are date fields, but the stored procedure keeps bombing on the section of my string that appends the date values indicating: "Syntax error converting datetime from character string." ...more >>

A couple of questions regading table size: 1. Why is the size of a table reported by Enterprise Manager in Table Info so different than the size that is returned when using sp_spaceused? 2. I've found the calculations but was wondering if there exists a system stored procedure that would r...more >>

Hi, I am using a database with massive update and insert action and therefore shrink logfiles every 15 minutes since the logs explode otherwise. Shrinking the logs results in a massive performance hit and usually takes about three minutes with huge disk queues. Is there any way to shrink th...more >>

Hi, I've been struggling trying to figure out this code to no avail. I need to query a date time field on an external database to give me data if the Closed_Time has changed within the last day. The Closed_Time field looks like this 9/1/2005 11:59:00 AM. The code I've been attempting to ...more >>

Hi I've been using Select * FROM tblSomething WHERE SomethingID IN (Select Distinct SomethingID From tblSomethingElse WHERE SomeConditionsAreTrue) in MSAccess quite successfully, but it doesn't seem to work in SQL Server 2k. Is the syntax different in SQL Server? Tried searching for th...more >>

I have a SQL server I want to update from a spreadsheet. There is data in the table already and I can't clear it but I need to do an UPDATE from the spreadsheet to the sql server? I'm guessing this may be a data driven task but I can't map fields from the spreadsheet to the update query against ...more >>

Hey, I have a .Net Windows Application which in some circumstances needs to make a connection to a remote SQL server instance over the internet. Where a local internet connection is present this works fine, just entering the IP Address in the connection string. However in some circumstan...more >>

How can you tell the recovery model of a database using SQL Syntax? Don ...more >>

Trivia perhaps, but this has bothered me for a long time. Over the years, I've noticed that some folks are adamant that table names should be singular; others are adamant they should be plural. Myself, I believe in the plural school. I simply don't understand the argument that a table should b...more >>

Hi ! I have problem with locking concept in my application (ADO.NET , C#). Application has several documents which looks like Order. My question is what and how should I lock those documents. 1.Lock document as a whole (Order + OrderLines) so another user can't work on this doc...more >>

Hi folks I am writing a humdinger of a stored procedure, which I can use to automatically create a second copy of a database, and ensure that the tables, etc, are all of the same specification. Here's what I've done so far: 1. Check to see if the second copy of the database exists. If...more >>