We have a 200 gig prod. database, I need to refresh this db on the dev. server. Dev. box has a space limitation of 50 gig. My question is how to refresh big db's in dev. boxes. Any help is greatly appreciated. ...more >>

Hi Guys, We are planning to move our SQL 2000 server( all the databases) from Windows 2000 Server to Windows 2003 Server. I need to install SQL 2000 on new Windows 2003 server then load all the databases from Windows 2000 server. Is there any document which can guide mein this process?...more >>

Hi All I want to deploy a SQL server database on my client windows 2003 server ; I will have admin access on the server through Terminal services The client server will have SQL server 2000 installed ; and I will use a backup copy of my db and install it using Restor Database command in the ...more >>

Most datasets work fine, but one, connected to the same server through the same connection, gives: Exception (EOleException) occured at address $0052BBD6 Message: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied This works fine on my development machine, and...more >>

1. Is there any limitations on number of items used in the where criteria. Ex. select * from tblX where (col1 = '2' or col1 = '3' ). I am building the WHERE criteria dynamically. 2. Any limitations on number of items in the IN clause? Ex: select * from tblX where col1 IN ('1','2'). I am using...more >>

Hi, How do I get rid of the seconds in a date: 10/13/2004 8:27:00 PM should read as 10/13/2004 8:27 PM I currently use the smalldatetime format. Thanks in advance Christian ...more >>

Hello, How do I get the most common set of columns which are present across all the given tables? eg) TableA(a,b,c,i) TableB(a,b,d,f) TableC(a,g,b,h) If I give this 3 tables ( Table A,B,C), the query should return the common columns a, b. How do I achieve this? Tha...more >>

Hello all, I will appreciate it if somebody could let me know how I can use "IF" in a sql statement to check a flag in a row. There is a table which contains a flag field which could have different values (converted from hex to decimal). How can I run a select and check the flag values simult...more >>

Hello all, When I'm using SQL Server 2000 to create a backup file, I'm receiving = this error: "-2147217900 (80040e14)". What should I do and how can I prevent that from happing in the future? Thanks, Elad Rosman Senior Programmer DrMover Inc. (http://www.drmover.com)...more >>

Hello: I have a table like this: ID | Date | Flag | ----------------------------- 1 01/25/04 p 2 02/12/04 p 3 03/31/04 s 4 04/15/04 p 5 05/30/04 p 6 06/15/04 s 7 07/18/04 s 8 08/30/04 s 9 09/30/04 p 10 1...more >>

Ok, I have two queries: 1 and 2. They are both identical, except query 1 has the following WHERE clause: where Trade.TradeDateCreated > @Date and TradeStatus.Value is not null When I run both queries back-to-back w/ Execution Plan turned on, here are the results: Query 1: Query cost (re...more >>

Hello! I am creating a Stored Procedure that the user will run from Excel. I have granted exec permission for public role, and the users are in that role. The problem is that this SP runs perfect for me, though my NT account is set up as the dbo NT account. The users get error messages about ...more >>

Lately, I've been running into this construct more and more. Its seems that people want to combine the INSERT and UPDATE operations into a single stored procedure (and sometimes even DELETE). As a developer, this disturbs me. I know that SQL is not like programming with C++, C#, etc. But, this "...more >>

I have a simple select statement to retrieve a list. i would like to give the option to sort it by different fields. is there a way to use a variable in the order by clause or am i forced to repeat the select statement in a huge if-else tree for each possible sort field? thanks for any tips ...more >>

I have a table with a field named "lineItem". It's "Identity" property is set to "NO". Does anybody have a script that I can use to change the property to "YES" Not for Replication. thanks in advance. ...more >>

First let me state I have read the example at http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm and also Celko's solutions but neither of these help me for my particular situation so I am enjoying a potent migrane trying to solve it. Here is my problem; I have a standard Refl...more >>

I'm sure this "feature" is by design, but I have a script that does stuff like this: SET XACT_ABORT ON BEGIN TRANSACTION -- do stuff here EXECUTE sp_Rename... -- do stuff here COMMIT TRANSACTION I had spelled the name of a table incorrectly, so my batch rolled back...more >>

hi I have a very large database with several stored procedures. now, I need to find a stored procedure that does some calculation if that stored procedure has a string "MESSAGE"(just an example) in an IF statement somewhere down the procedure. currently i am opening every stored procedure,...more >>

I am having a problem tuning a query. The basic query is: DECLARE @Date DATETIME, @Date2 DATETIME .... SELECT Info1, Info2 FROM Table1 WHERE Start_Date BETWEEN @Date AND @Date2 The @Date variable gets filled in early in the proc. When I run this proc, it does not see the...more >>

Is it possible to run sp_rename throug SQLDMO? lsScript := Format('sp_rename [%s].[%s], [%s], COLUMN;', [_DMOTable.Name, AColumn, ANewName]); _SQLDB.ExecuteImmediate(lsScript, SQLDMOExec_Default); Is it correct this routine? ...more >>

Dear All, I am trying to write a query to extract information from my database. The query uses four tables. They are as follows: Client Policy Tracking Action We have a number of actions that can be associated with a single client. I.e. A form being issued or returned. The sceniro...more >>

Hi, I need help connecting to a remote SQL Server, and am not sure how it can be done. The SQL server I want to connect to is SQL Server 7, and is behind a firewall. How can I connect to it from my laptop? I have SQL Server 2000 on my laptop. Also, how can I connect to the machine that...more >>

Hi I've created a temporary table in which i want to insert values from the following stored proc: sp_change_users_login 'Report' I try and run INSERT INTO #tblTemp EXEC sp_change_users_login 'Report' but get the following error message Terminating this procedure. Cannot have an...more >>

Can someone please help with following query? insert into #delete_CRFT_PAYMENT_INVOICE select b.FEE_INVOICE_NUM,b.PAYMENT_ID from CRFT_PAYMENT_INVOICE b inner join CRFT_ADMIN_FEE_ALLOC_STAGE c ON b.FEE_INVOICE_NUM=c.FEE_INVOICE_NUM where b.PAYMENT_ID not in (select d...more >>

I have been reading a lot of posts regarding Joe Celko's neat nested sets solution for storing and querying hierarchies in SQL databases. It seems that one of the most frequently used examples is a Bill of Material (BOM) detailing components, sub-assemblies and final assemblies. This is conven...more >>

Hi All, Can I have a Optimized Stored Procedure which will return the Data in Page? I have a Table with around a Bilion or records. The Stored procedure should work like ADO Page technique. I have read some pages: http://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_2 ...more >>

Hi, Got SQL Server 2000 Sp3a and the following query on 3 tables: ____________ SELECT DISTINCT resResourceID, resDateIn, resDateOut, empEmployeeID, empLastName FROM Employee INNER JOIN Resource ON Employee.empEmployeeID = Resource.resEmployeeID INNER JOIN Department ON Resource.resDepartm...more >>

Dear Sirs, I had Arabic data in my table and the columns data type was varchar. I changed the data type to nvarchar, which affected my data it became unreadable. How may I change the format of the existing data to unicode? Regards, Yahya ...more >>

We have a VB program that uses SQL server as the backend database. Our customers have been running our DOS version of the program for 15 years and we have been upgrading them for the last year (it was a helluva good DOS program to have that kind of shelf-life). We converted their data from C...more >>

I'm trying to work out the best way to store tables of data in SQL. By tables i mean, the data is intended for a grid of data (a html table). The tricky thing is, the tables are going to vary in number of columns and datatypes (some string, some numeric). Currently i have setup db tables sim...more >>

Explaining by example....I have a table that contains customers and a table that contains orders linked to the customer table... I need to create a SELECT statement that returns all customers and IF orders exist within the given dates, the fields for the LAST order placed within those dates......more >>

Hi Guys, Some of the people here where I work write queries using date range criteria: '1 Oct 2004' instead of '20041001' How much does this impact query performance? What is the most correct way to use date criteria? Dates are stored in the DB as datetime. Thanks ...more >>

OK.. This may or may not be an easy one for you SQL gurus out there. Here goes.. Let's say I have a table that looks like this: Field1 Field2 A Mike B John C Joe D ...more >>

Hi, I am trying to write a stored procedure that brings back all data where todays date is one month or less than a contract date. Any help on this would be appreciated. Thanks Damon ...more >>

Just a quick question. Microsoft's website says something about "xcopy deployment". Does this mean that SQL server 2005 and - express were written in a dotnet language such as C#? -- Arthur Hoornweg (please remove the ".net" from my e-mail address. I had to take this measure to...more >>

How can I make the following SQL Script (Access) work for SQL Server? The problem is "First" is not a valid T-SQL function. Thanks in advance for any help. SELECT Left([tblReportType9].[GroupCode],Len([tblReportType9].[GroupCode])-1) AS GrpCode, First(tblGroups.GroupName) AS FirstOfG...more >>

Hi everyone, I am sure that this is a question that has been hashed out many times before, but endless googling has not provided a satisfactory answer. I know that in most cases SELECT * FROM A WHERE A.field1 IN (SELECT field1 FROM B) is functionally equivalent to SELECT A.* ...more >>

Hello all! I have the following table which contains over 4million rows Counter decimal 9 Machine varchar 60 LogEntry varchar 1000 Active varchar 50 SysInfo varchar 255 Idle varchar 50 IP varchar 15 KioskDate datetime KioskTime datetime ServerDate datetime ServerTime datetime ...more >>

Is there a stored procedure or script that can be used to identify all the databases a user has access to, y entering the user login? ...more >>

I am using SQL Server 2000. When I run a query I want to differentiate between the data "E" and "E ". How to differentiate the data which has blank space after it and which does not have blank space after it? Thanks for your answers. Smith ...more >>

Hi, I am trying to write a stored procedure which brings back all dates which are one month or less before a contract_date, any ideas on how I can do this? Would appreciate the help. Thanks Damon ...more >>

Both Service Pack SP3a for SQL has been installed on the windows 2000 server and Service Pack 2 for XP on the development machine the XP machine runs the development environment and the database resides on the windows 2000 box. debugging of stored procedures on the XP machine has now st...more >>

I am getting the following error: Server: Msg 107, Level 16, State 3, Line 10 The column prefix 'Agentlocatordata2' does not match with a table name or alias name used in the query. When running the following query Insert Into Agent ([Agent], [AgentLoc], [AppointmentDate], [BusinessN...more >>

I need to perform the same operation on a group of tables. I have the list of tables, stored in a table. What would be the best way to do this? The operation is a delete statement. Any recomendations? thanks ...more >>

Anyone know where I can get the APIs that will allow me to write an application in VB for importing/exporting? Any help would greately appreciated. Thank you....more >>

Is there any environment variable that tells me what database I'm in on SQL 2000? TIA, CJ ...more >>

I am trying to track my Linked Servers. If all of them are connected, the script runs flawlessly and records the time, databases associated with the server. If any one of the servers are down, I get a Level 16 error and the script stops. I have tried many different error handling, but none of ...more >>

We are seeking developers who have both .NET and Oracle experience to help us test a very cool new upcoming product. 2 hours of your time (on site) will get you $75-$100. Send an email to dbwindows@yahoo.com if you have the required experience, are located in the SF Bay area and are intereste...more >>

SQLMatcher is an advanced schema comparison and synchronization tool for Microsoft SQL Server. It is an essential tool that should be a part of every SQL Developer and DBA's toolkits. This beta is the final beta and the product is scheduled for release next month. To obtain the software yo...more >>

What if I want to give users Query Analyzer-style access to my data, but don't want them to delete it? I can do deny delete on dbo.mytable to public which is great... assuming they don't know how to do 'truncate table'. uh? is there any way I can prevent users from truncating a table b...more >>

How can I return a list of dates (rounded to the day) from @StartDate to today's date ? Thanks, Craig...more >>

Hi all, I've been reading up a bit on SQL Server 2005 DTS packages. The new version of SQL server seemed to have made major changes to DTS. My queston is... will old SQL Server 2000 DTS packages be compatible with SQL Server 2005? Will I need to rewrite all my packages when I upgrade to SQL...more >>

Hey, My first DTS. I made a DTS. With execution of a query (delete from file1 and delete from file2) After execution (on completion) I start a data import of a text-file into file1 and another data import of a text file into file2. Now, before I start the deletion (clearing the files) ...more >>