I am trying to write a cursor to update certain rows in a particular order as follows: (I need the cursor version, not SQL, as the update logic depends on the order of rows and some other conditions. I removed the Order-By clause from the statement to simplify it; it gives the same error messag...more >>

Hi there, Getting ready for deployment and I would like to be able to run an install script that will not just generate the schema, but fill in some of the tables with default data. Is there a tool out there that will scan user tables in an SQL database and generate the script required in ord...more >>

Hello all, Background: I've posted a couple questions recently regarding using BCP to extract many tables to text files. I've written an extract application that bcp's about 100 tables from a database to text files. I've run into a couple limitations using BCP, but I finally landed on (what I...more >>

Hi! Another silly question: If a stored procedure returns multiple result sets, how do I choose the one I want to insert into a table? For example sp_spaceused returns two result sets if object name is ommited. I only need the first result set. How do I do that? Tnx! Darko ...more >>

Hi, I can't seem to get this where clause to function as I wish.. must be something simple but I can't see it! have tried ac.coursename like CASE @course WHEN null THEN '%' ELSE '%' + @course + '%' ac.coursename like CASE @course WHEN '' THEN '%' ELSE '%' + @course + '%' ac.coursename like CA...more >>

Norton Anti-Virus Affecting SQL Across Network-- I help maintain a small software package developed using Microsoft Visual Basic that utilizes Microsoft SQL Server. I'm having major speed issues when it comes to making queries across the network. I have tracked the problem down to Norton Ant...more >>

Greetings, I have Full database backup job scheduled at 02:00 every day, Differential backup every 2 hours from 00:00:00 to 23:59:59 and Log backup every 15 min from 00:00:00 to 23:59:59. Is there a way to control order of execution, for example: Full backup starts before Differential backup a...more >>

I am assigned to setup a small (less than 15 PC) network, accessing a database server(SQL2000). I am wondering if I can use XP Home (which we already have)for the clients and setting them all (including the server -WS2003-) as workgroup? Or do I need a domain to setup the connection to the datab...more >>

I have a table called Orders with three fields, OrderID, Action,Status. Action and Status are code values with FK lookup to dbo.Action and dbo.Status respectively. Is it possible for me to return not just the code values but the filed names? For example: SELECT OrderID,Action,Status FROM Ord...more >>

Hello, The PRINT command works fine on Query Analyzer. However, when I used it with other Database Client, eg: Aqua Data Studio, nothing got printed out. Is there a way to make it work? Thanks in advance....more >>

Hi group, I have a 175MM record table, with a record length of 200 bytes (about 20 columns). Sometimes when I run a very simple DTS to import our monthly text file of new records (about 10 million records) it really flies (takes less than an hour or 45 minutes). However, sometimes it tak...more >>

Hi, this is easy with OLAP tools, but I need to do it just with MS-SQL server: fatTable year type val 97 a 1 97 b 2 97 c 3 98 a 4 98 b 5 98 c 6 .... year type_a type_b type_c 97 1 2 3 98 4 5 6 99 ... The problem is number of different types - not just 3 like a,b,c but more tha...more >>

I need to modify a query through which I've done mostly using the Query builder in Enterprise Manager so it can be run periodically without manual editing. The query builder creates the following text: select [ATHSTATDATA].[NSTATEVENTID] as "Page or Search",[ATHSTATDATA].[NDATA] as "Hits if Se...more >>

Hey all - We are running SQL 2000 with ALL available service packs, etc. applied. We just built a brand new database server, which has dual 2Ghz XEONs, 2GB memory, and the following disk configuration: RAID 1 array (2 disks) Operating System Windows Server 2003 RAID 1 array (2 disks)...more >>

Hi, Used the wizard to create a maintenance plan for a db. But it fails most of the time. I checked the log-files. At 1.00 AM, it runs the "Optimizations" job for 6-9 seconds. It succeeds always. At 1.05 AM, for 1 second, it runs "integrity check", but most of the time it fails, and says...more >>

I have a table with 3M rows that contains a varchar(2000) field with various keywords. Here is the table structure: PKColumn ImageID FullTextColumn There is an association table: ImageID ContractID Now, I want to do a query where the ContractID = x and Contains some word in the Fu...more >>

Is there a good approach to modelling many heterogeneous entity types with that have some attributes in common? Say I have entities "employees" which share some attibutes (e.g. firstname, lastname, dateofbirth) but some subsets of employees (e.g. physicians, janitors, nurses, ambulance drivers...more >>

In my database there is a text field type that is used to enter street address. This address could be a few lines long, each line with a carriage return at the end. Is there a way to search for these carriage returns and break out what is in each line seperately? Thanks. Mike...more >>

Hello, I am porting a stored procedure from Oracle. It uses a variable that remembers its previous values from each invocation. (It uses a PRAGMA REFERENCES clause for those who are familiar with Oracle.) In other words, the variable in a particular stored procedure acts as a global variab...more >>

Hello and thank you for your time. I have never worked with sql server and currently have a project updating the database. I have a CSV file as follows: id keywords (this field contains commas within) date I have a sql server table as follows with only the id populated. id keywords dat...more >>

Hi all, I have a Windows 2003 server, which is also a terminal server for application, with sql 2000 installed. My company has developed an application that uses SQL 2000 as its database. The application is a client/server one. In each client computer there's a link to the application on the se...more >>

Im getting way too many rows retured..what its trying to do is insert a 0 for revenue for months 7 - 12 (aka July through December) for each of these cost centers for each payor type..Im getting a lot of repeats and the concatenation field date always comes back as January 2003 instead of the mo...more >>

Hi, I need to provide a facility to do routine database administration (backups, etc.) without allowing the logged in user to modify the data in any of the SQL server tables. Is there any way to accomplish this (such as maybe password protecting the tables or otherwise)? I am fairly new to S...more >>

Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_final a right outer join Actual_account_Tbl b on a.account_desc = b.account_desc where (a.source_type...more >>

Hi All, I am using command line bcp utility of SQL Server to import data from a text file to database. I have some german words in the text file and after import the German characters are lost. see eg below. Input : Kühner, Klaus -> Text file value OutPut: Khner, Klaus -> Table data, ...more >>

Hi, I need your help to resolve this problem. I have written a right outer join query between 2 indipendent tables as follows. select b.Account_desc, b.Account, a.CSPL_CSPL from Actual_data_final a right outer join Actual_account_Tbl b on a.account_desc = b.account_desc where (a.source_typ...more >>

Dear Expert, I have 2 tables as the following, Table : Product Column : ID, Code, Name Table : Trx Column : Trx_ID, Trx_Date, Product_ID How can perform the following query in MSSQL or is there any other way to perform the same query ??? For you info I can do this in Sybase and it...more >>

Hi! Another problem: How can I fetch only the last row in my table? Thanks, Darko ...more >>

Hi! How can I save a result set from a stored procedure into a table? Can you show me some examples? Thank you! Darko ...more >>

Hi, I have two tables TblEmployee and TblEmployeeUpdates. If I create a trigger to copy deleted records from TblEmployee into TblEmployeeUpdates I would use: CREATE TRIGGER Employee_Updates ON TblEmployee AFTER DELETE AS INSERT INTO TblEmployeeUpdates SELECT * FROM DELETE This works ...more >>

For some reason the compiler is telling me that I must declarethe variable @costcenter_tmp on lines 74 and 98...but if i put a select statement in ther (for testing) before the loop I get data back from the temp table.. why is this happening to this temp table and no others?..heres my code..i...more >>

Dear Group Sorry for posting this here. I'm desperate for a solution to this problem and thought some of you might have come across it with .NET and SQL Server. Let's assume I've the following code: Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handl...more >>

I'm trying to drop all indexes and primary keys so that i can rebuild them (from a script created from same database on another server). when i go to the 'generate sql scripts', it has the ability to drop or generate all tables. it also has the ability to generate all keys only. but i cant f...more >>

I am trying to copy data from one table to another, and in the process add a sequence number to copied rows. I have looked at the IDENTITY function when creating tables, and essentially this is the behavior I am trying to duplicate. However, my question is if I can do this with the SELECT INT...more >>

Hi i have a table with primary key defined on col1 and col2. now i want to have col3 also included in primary key. when i alter the table it gives me error for duplicate rows. there is an option for 'with nocheck' but it only works with check or foreign key constraint. is there any option i...more >>

Is there any short way to make the server give out IDs of all client processes connected to a specified DB? I guess this information must be stored somewhere in system databases......more >>

I want to update top 30% of a table. Is there a way without creating a separate table? create table temp_tableA (jobno varchar (20), formno varchar (20), otherinfo varchar(20)) truncate table temp_tableA insert into temp_tableA values ('1', '1', 'aaa') insert into temp_tableA values ('1', '...more >>

Hi! How to create an alert that responses in case of long querya. I haven't noticed any counters that deals with these kind of situations? Thx for your help! ...more >>

How can i calculate the last day of the previous month? Help me,please ...more >>

Hi My database contains , lets say, 100 tables. Out of these 100 tables, 60 tables contain both columns; Column1 and Column2. I want to: Update (All tables with BOTH of these columns) Set Column1 = Column2 Is there a better way of doing it? OR do I have to do it manually for each ta...more >>

A couple of months back a question was posted regarding ordered factorizations of numbers (see http://tinyurl.com/2n6s8), a combinatorial problem. Here's a relational solution that uses a branch-and-bound style approach. For a natural number N, let's call its ordered factorizations F where "...more >>

What I'd like to do is use the result of one query as the input for another. eg: INSERT INTO foo VALUES ( (SELECT id FROM people WHERE name = "bar"), 10, 'foobar' ) WHERE id = 1; Problem is, MSSQL wants a scalar value. Is there a way around this? Can someone tell...more >>

ie if i have x 1 2 3 4 5 ------------ 1| a b c d e 2| f g h i j 3| k l m n o 4| p q r s t 5| u v w x y and i want to change it to x 1 2 3 4 5 ------------ 1| a b c d e 2| a b c d e 3| a b c d e 4| a b c d e 5| a b c d e any ideas on how to do that?...more >>

We recently translated the backend db from Access(97) to SQL Server. We are still using Access frontends. I have an update query in the Access front end that uses a lookup table to populate fields. The common fields between the table and the lookup table are the primary key (LocID) and date &...more >>

I'm very familiar with Oracle at a conceptual level and need to understand MSSQL as well. Can anyone point me to a document/web page that describes the different files used in MSSQL and a discussion of how they relate? (ex. with Oracle, there is tempdb, redo, archive, db files, rollback, et...more >>

Hi NG, i've got a mystic problem i can't solve, perhaps one of you has a good idea... i will explain with some code, so fo better understanding my probroblem: i'll use asp, but for testing the syntax i use the MS Query Aanalyzer. following Stored Procedure is ok and does it's job fine with "...more >>

Hi I am probably going to regret asking this because I'm sure you are going to tell me my design is bad 8-) ah well we all have to learn.... anyway I often use Nulls as a marker to see if certain tasks have been completed. A typical example would be a column say invoice_value when n...more >>

In another database that I've used, I found the FOR SELECT syntax handy. It allows a stored procedure to perform actions for each record returned by the select statement. I can't find the documentation on this in SQL Server. Am I missing something here? I've just started with this product, s...more >>

hi, we are having this weird problem that can be reproduced easily on 2 out of 2 servers where we've tried: - we run a SQL Server 2000 stored procedure that processes 2.5 million rows (big procedure with many cursors) - when it hits an area of procedure (not always the exact same place, but ...more >>

Hello all, I tryed to simplify the problem as much as possible. I'll start with the DDL: ---------------------------------- CREATE TABLE #MyTable( NoID INT, Type CHAR, DateTransaction DATETIME ) INSERT INTO #MyTable (NoID, Type, DateTransaction) SELECT 1 AS NoID,...more >>

Just when I though I knew what I was doing. I ran into a roadblock. I have two tables, organizations & users I am building a form online for orgs to input their own information. How do I insert into both tables when the org table needs to know what the user_id is, but the user_id hasn't been...more >>

I've got a job that sends out the results of a stored procedure in an email via xp_sendmail. The job code is as follows: DECLARE @rlist varchar(1000) Declare @Q varchar(100) Select @Q = 'EXEC impact_exec..TLD_Reconciliation' Declare @Sub nvarchar(60) Select @Sub = 'TLD Reconciliation - Co...more >>

I've got a table with 36+ million rows. I've been asked to modify the table and add in an identity column. The code I used caused SQL to lock up and it maxed out the log files. :) The code I used is: Begin Transaction Alter Table ODS_DAILY_SALES_POS ADD ODS_DAILY_SALES_POS_ID BigInt NOT...more >>

What is the common mechanism (if any) to make the server notify a client when some condition is met (say, upon completion of a sql-transaction)? The only way to implement this feature I am aware of now is to set up a timer and query the server every once in a while but surely this technique isn'...more >>

Hi Can anyone point me to a document somewhere that shows a mapping of SQL Server 2000 datatypes to C datatypes? I am writing some extended stored procedures which need to be able to process pretty much any data type, so I want to make sure I am taking them from SRVPROC and storing them in the ...more >>

Hello to everyone. Hi there i have a question i am building a website in .net that will use a database back-end. The .net Pages will be hosted by a payed provider. The MSSQL-Server Database will be hosted behind my own adsl connection. The thing i hope some of you could help me with. Is what ...more >>

Hi, I wanted to get the domain name from the email in a query.. So if the email is aj70000@hotmail.com, I just to display hotmail.com Thanks in advance for the help. AJ...more >>

Hi all. I've been tasked with "speeding up" a mid-sized production system. It is riddled with nulls... "IsNull" all over the procs, etc. Is it worth it to get rid of the nulls and not allow them in the columns anymore? If so, how to go about removing the nulls with a script? Thanks s...more >>

I've moved the database tables from the .mdb file to Sql Svr and now I have an *intermittent* problem. When I select a record from a combo box, it will intermittently pull up the wrong record. The code that does the search is below. I have a column named 'ProsId' that is a unique number (ident...more >>

I thought I'd give the SQL Server 2000's log shipping wizard a try (in the past I've always done it with Perl scripts), so I attemted to set it up on one of my servers. But in the maintenance plan wizard, I don't get the checkbox at the bottom of the window to "Ship the transactions logs to othe...more >>

Hi, Ik created an application with visuals basic.NET. This has a connection string to one database, let's say 'A'. In this database a stored procedure is called which should execute a string (which is passed by the) VB tool. This string is a CREATE VIEW statement en this should be executed i...more >>

I'm sonewhat new to MS SQL Server and I'm wondering about which of the following two queries would be faster: DECLARE @ResidencesBuilt int DECLARE @BarracksBuilt int DECLARE @AirBaysBuilt int DECLARE @NuclearPlantsBuilt int DECLARE @FusionPlantsBuilt int DECLARE @StarMinesBuilt int DECLARE...more >>

Hi, I have tables named like ag97a027, ag98a027, ... where 97 and 98 is from year 1997, 1998 and a027 is just specification of product type. I need carry out repeated operataions over these tables. To generate their names is easy, for example use [P5-01] declare @Yint int, @Y char(2), @tabNa...more >>

what is the equivalent data type of autonumber of access in sql server?...more >>

I'm new to SQL and can't figure out how to update my table (StoreItemStatus) that contains the current status for items in each store (STORE_KEY, ITEM_KEY, STATUS,...). I get updated status info in a table (I'll call it NewInfo) that has similar fields. NewInfo may contain multiple records for ...more >>

I want convert a mysql database in SQL Server 2000. SQL Server forecastes the possibility to view graphically the relationship between all tables containing in my database with relative structure by graphic. About mysql i don't know as actes. Thanks and i'm sorry for my probable encorrected engl...more >>

Hi I am writing SQL scripts to define my tables and also fill some of them with data that are essentially look up tables. One of the tables contains hundreds of stock items which need to be filled with information. Is it better to write my INSERT INTO.... statements to fill this large table...more >>

record_id Status Due_date 549 In Progress 2004-06-02 00:00:00.000 549 Not Started 2004-06-07 00:00:00.000 549 Not Started 2004-06-08 00:00:00.000 549 Waiting 2004-05-31 00:00:00.000 549 Waiting 2004-06-04 00:00:00.000 550 Completed 2004-05-05 00:00:00.000 551 Completed 2...more >>

I have a stored procedure where I want to select all fields matching the query into another table. In addition, I want to add a common groupID to each of the records that are being inserted into the second table. I can get the results that I want by using a temporary table but need to know i...more >>

Hi I'm having a problem with deadlocks in a table in SQL server when trying to update it through Biztalk 2004. There is no problem when I use the same Biztalk solution to update a similar dummy table, but when I try updating the original table in the production database, some transactions are...more >>