Hi, I am trying to automate a SQL Trace via a stored procedure and a job. The job executes the stored procedure to start the trace and every 15 minutes, the job is supposed to stop the trace, clear it from memory, rename the trace file, and start a new trace so I can select the average duratio...more >>

I have two tables. One has approx 90,000 rows with a field .. let's call in BigInt (and it is defined as a bigint data type). I have a reference table, with approx 10,000,000 rows. In this reference table, I have starting_bigint and ending_bigint fields. I want to pull out all of the refe...more >>

I break somone's name in a table in fields as First, Last, Middle, Suffix. Is it reasonable to use a UDF to create the full name as a string if I need it, by passing it the First, Last, Middle, & Suffix fields? Is this really slow to do? It's just sometimes I want to do a GroupBy Query and ...more >>

Hi there, we tried to restore a sql 7 DB to sql2000, somehow some of the varchar filed become nvarchar, why is this? thanks, -sarah ...more >>

Can you group by a value returned by a UDF? Example: SELECT Col1, SUM(Col2), MyFunc(Col3, Col4) AS 'MyResult' GROUP BY Col1, MyResult I've had no luck. Can this be done? ...more >>

I have a long sql batch that does this: 1. Gets a list of all tables in user database that start with name CORE declare @tablename varchar(30), @command varchar(2000), @cnt integer declare GetCOREOids cursor for select sysobjects.name from sysobjects where ( OBJECTPROPERTY(s...more >>

I am creating a table that will store my companys annual punctuality scale. What is the best way to name the columns? Here is some sample rows with data. -------------------------------------- DPT_CODE_1 | 300 | 121 | 120 | 61 | 60 DPT_CODE_2 | 360 | 241 | 240 | 121 | 120 Thanks for any ad...more >>

In my MSSQL server 2000 sp4, in performanece monito, buffer memory, page life expectancy is equal to 0.00000 (average for 10 sec, auto update). I think sth is wrong configured, but what? Marek ...more >>

Hello All, On all of my SQL servers (2000 with SP3), when I go to task manager and look at memory usage, sqlservr.exe is always at 1.7gigs. If I reduce the maximum to let's say 1 gig it will go down to 1 gig. But if it is even at 2gigs or 3 gigs it will be showing 1.7 gigs. Why? Is is ok...more >>

This may be a really simple question, but I always have problems with Distinct queries. In this instance, I have a table of Delivery addresses, some will be exactly the same EXCEPT for the DeliveryID field, but eitherway I need the DeliveryID so that I can pass it onto the next form. So,...more >>

Hi, I am relatively new to SQL. I am using SQL 2000. I am trying to Update a field base in a criteria in a scond table. UPDATE Tbl1 SET Tbl1.Row2 = '1' WHERE Tbl1.Row1 = (SELECT Tbl1.Row1 FROM Tbl2, Tbl...more >>

I am trying to script the DROP(IF EXISTS) and CREATE for all of my tables, views, stored procs, and functions to individual SQL text files (one per object). This was trivially done in SQL 2000 with Enterprise Manager, but when I try in SQL 2005 through Management Studio my only "script mode" op...more >>

Hi All , I am hoping some can help or point me in the right direction. We have recenlty installed SQL 2005 and are using integrated services using ole db connections. We are connecting to an Informix database. My questions is there a way I can pass through multiple sql statements when I cre...more >>

Hi, I need some advise on how to automate record selection on a stored proc. Here is my situation. I have a stored proc that I used on Crystal reports with two parameters - Acctcode and Subacct. When a user enters ' *' on these parameter, it means to report on all accounts otherwise, repor...more >>

Are there tools for SQL2k database that take a database return all relations of tables. thanks. Stefano. ...more >>

I will doing an installation of MS SQL 2000 that will be used by multi language web sites. The languages are Japanese, German and of course English. Currently I'm planning to install MS SQL 2000 standard version but after reading some articles I'm considering the Enterprise version. Can som...more >>

Hi, Apologies if this sounds dumb but I'm very new to this. I'm just trying to create a simple login page ( http://aarkenuk.co.uk/barnesg/login.php) In my MySQL databases I have the correct database selected, but it's the users I'm struggling with. You get like a front end with my hosting s...more >>

I installed a script that is suppose to accept paypal, however on trying to test a payment, I get this error msg: Error Database access error and I get this error msg emailed to me: Unknown column 'State' in 'field list' Query: 'INSERT INTO `TransactionsMembership` (ID, Sum, State ) ...more >>

I have a base customer table of 2 million records. We're doing some testing and I added 33000 rows incorrectly. No biggie, we'll just delete them, right? Nope....9 hours later, process is still running using this query. delete from customer where custid in (select custid from #tmp1) k......more >>

Hi everyone, I have problem to join 2 tables together to show the selected results into a datagrid. table1 hosts all customer personal information. table2 hosts all the trasaction records for each customer table1 has fields such as "customerID", "CustomerName" and "TelNumber". (customerI...more >>

I am using a hosted MSSQL 2000 database that powers the backend of my website. Website visitors interact with it via ASP pages I have developed. I also have an internal FileMaker 7 database that periodically synchs with it via Filemaker's ODBC functionality. Several times now, I have come in o...more >>

I have some queries that involve subqueries to select the appropriate record from a bunch of candidates. For example the following, which selects the most recent transaction for a given customer: CREATE TABLE #Customer (CustID int, OtherInfo int) INSERT #Customer SELECT 1,7 INSERT #Customer ...more >>

Hello all, I have a table that contains 100+ rows. I'm trying to run a query that looks like this: SELECT ..., log10(...) as Rank FROM Table and the resultset contains only the first row of results. SELECT ..., log10(...) as Rank FROM Table ORDER BY Rank returns no results at all! If yo...more >>

I have a table (cars) with 3 fields: VIN, Class, sell_price 101, sports, 10000 102, sports, 11000 103, luxury, 9000 104, sports, 11000 105, sports, 11000 106, luxury, 5000 107, sports, 11000 108, sports, 11000 109, luxury, 9000 i need to write a query that WITHOUT USING A FUNCTION wil...more >>

I am currently trying to set up a linked server between two SQL-Server 2000 servers. I have a number of linked servers to other machines, which use local accounts and work fine. However, in this particular case, I am forced to use my domain account to log into the remote machine. My account has ...more >>

We recently copied(replicated) a data base for development. everything went well until yesterday we had rows start to disappeared in the source data base. I then found out that the rows disappearing from the source data base were being deleted from the database copy and this was being replica...more >>

We have a very long and complicated SQL script which we run to upgrade a version of our software from old to new. It works great in Query Analyzer, but when run through osql it takes errors on lines that are very long and (I think) stops reading after a certain amount of characters. I've searche...more >>

I have the two following tables: tblProject: ProjectID 5001 5002 6001 6002 7001 7002 tblProject_type: ProjectTypeID ProjectIDFrom ProjectIDTo A 5000 5999 A 7000 7999 I need to create a SELECT statement which shows all...more >>

Hello all! How to copy a table in MS SQL Server 2000 without chaning a structure? I mean, I have one table, which has autoincrement numeric field (ID). When I copy this table by exporting this table into the same database folder I loose the specification of the field ID. Now it is not autoincr...more >>

Currently I must manually complete the following commands to set a new value for the "open objects" and "open indexes". I need a way script this process in a batch file. COMMANDS RAN: This is nothing to script, these commands obtain the current values for open objects and open indexes. ...more >>

I have an existing SQL 7 server named HHARBR. HHARBR has a database named SPR with a table named "reportname" in it, the table has the name HHARBR embedded the table data. I migrated the HHARBR database SPR to a second server called HHARBR2. When I look into the table in HHARBR2 I still see...more >>

Hi All, Bit of a newbie question i'm afraid, so sorry if this is a really stupid question, I am not sure if I am trying to do this in the right place as I am sure SQL Server has a far better way of doing this What I am trying to do is join the contents of several columns and present the ...more >>

Hi, On my laptop I am running Virtual Server 2005 with 2 x Windows 2003 Servers. Both the VS can access the internet and shared files on the host laptop. On the host laptop I have a SQL Server 2000 running. I have written a Windows Service to detect Application Errors and fire duplicates of...more >>

Ok so I'm gonna attempt to explain my problem as easy as I can. I have a database of contacts (they are actually vendors). I am writing a procedure that will query the vendors and generate a tab delimited file that will be used an accounting program (MYOB). In the vendors database I am exporting...more >>

If I run this statement in Query Analyzer, it properly returns 1 for my testing table. But if I put the statement into a stored procedure, the stored procedure returns NULL. What am I doing wrong? I suspect it may be related to how I defined the parameters for the stored procedure. Perhaps my...more >>

Hi, I am trying to get a 0 or 1 depending on the sucess or failure of a t-sql statement, and here is what I have: Declare @boolDatabaseExists bit Declare @chvnNewDatabaseName nvarchar(260) SET @chvnNewDatabaseName = 'NewDatabase' SET @boolDatabaseExists = ( SELECT (1) FROM maste...more >>

I want to create an UPDATE statement that updates a value in a table in database A based on the results of an inner join between tables each residing in deifferent databases. What is the correct syntax for doing this? The following should give you an idea of what I'm trying to do: UPDAT...more >>

I am currently using the BACKUP DATABASE method to backup my database. Everything works fine, except for users. I created various users with sp_addlogin to access this database and they are located in the xlogins table. The problem is for example, if I backup, remove some users and do a restore,...more >>

Hello all, We were just getting started on a small project using SQL Server Express on our PC's and we already had SQL Server 2k Enterprise on our main Server. Problem is , we cannot do merge replications between the two. From what i have read this is not possible, but it blows my mind, ...more >>

hi, i had a small doubt , i have a table xxx with two columns (a int,b int ) and i have inserted 5 rows my query is to add the two colums using a stored procedure and the result has to be displayed in an separate column --this has to be done only stored procedures ---i know how to solve the pro...more >>

> This is a common problem with some solution > > /*********************************************************************************** * * Problem: * Determine the Duplicated Records in a table using single SELECT. * * We shall be using Northwind database, add some duplicate recor...more >>

How would i add order by to the syntax below: "Select * Into ETCLog_holding from etclog where box# BETWEEN " & Box1 & " and " & Box2 i have tried adding it after Box2 but it doesnt work. Any ideas? ...more >>

Hi, I need to lock a database (prevent users from connecting) in order to update it. I already know how to kick everyone out with their spid but i can't figure how to prevent them from reconnecting. Thanks ! ...more >>

Hi, I'd like to reverse engineer the schema definitions in the SQL Server on a daily basis and store them in a version control system. Could anyone please let know if there are any tools to reverse engineer the entire schema definitions in the SQL server. I'd like something like the perl sc...more >>

I have a problem... Data transformation rounds or truncate decimals! I have an ODBC source in witch is a table with float numbers (ODBC Driver publish it as float). If I build a query form access or from excel with Query Analizer, I see all decimal places, but when I try to insert data using ...more >>

Hello everybody. Unfortunately I am pretty new to sql-server 2000 I need some help with a Trigger I created. I created a trigger witch takes the id of the affected row and does a update on a other table with that ID. The trigger works fine with one affected row. But when there are more then ...more >>

Having a brain cramp here and don't know where to start. I have 2 tables: vehicles and vehicle_useage. What I would like to do is this: For each distinct vehicle in the vehicle table, I want to make entries for each day of the month taken from a given date. This routine will be scheduled...more >>

We have two applications (1 on two servers and 1 on another). Every so often, we 'lose' the connection to the SQL database which in effect takes these applications down. One app is a service (written in Delphi) , the other is a web page (ASP). >From what I can tell, both different methods use ...more >>

Hi, I have a DB where I would like to maintain a fixed size and control it by myslef. I do not have the options: "Auto-Grow" and "Auto-Shrink" enabled. >From time to time, without a notice, or any logging, the database files gets shrinker and this causes a database full error. As I wrote, ...more >>

I apologise if this is a stupid question, but I would appreciated any help on this subject. I want to create a view (VIEW_1 in example below) where I take numeric values from a field in one table (DEPTH_FROM in TABLE_1) and find the closest matching values from another field in another table (...more >>