Hi My user has put ' with editable place and then press enter so this same way put text information with ' string into database. When I try tu user command : UPDATE and try to change text with ' and use command WHERE = ('text'') then I just received error command from SQL2000 server - I know wh...more >>

Hello I was wondering if someone could help me out with something. With the following rows: agnt_num supv_num meet_num strt_dt strt_lvl trm_dt trm_lvl 1 1 1 10-10-2000 01 10-15-2000 02 1 1 2 10-09-2000 06 ...more >>

Hi all, I have a problem trying to generate the u/m list of customer. I am trying to generate a list of customer whoes last commence date is jan 04 to current. It is part of a billing system which the customer come in and pay for their season parking in carpark. They can pay for various p...more >>

Hello, How can I record certain type of querys ? I would like to record delete command sent to a specific database, and using a specific login account. This "query capture" should run in background because I don´t know the exact time someone will delete a record. I´m using SQL-Server 7.0...more >>

Dear Group I'd be grateful if you can send me on the right track in achieving this. I have three tables A,B,C outlined as follows: Table: A Field: RowID Field: EntityID Field: TypeIdentifier Table: B Field: RowID Field: Name Table: C Field: RowID Field: Name Let's assume I'v...more >>

Hi, Appreciated anyone who can help us with our problem. Our backup job for ROUTT database has been failing for the last 4 days. It was working fine for almost a year now. The only thing happened few days before the problem started is the addition of more memory to the server because we've b...more >>

I am new to SQL Server 2000, I am looking to set up some type of loging on out test database. I need to track what table are hit when a record is inserted via a front end piece of software (Struxure). Any help would be appreciated. Daniel Kubicek Senior Programmer Analyst - Engineering & P...more >>

Is there a keystroke such as "CTRL-N" that would allow me to have multiple .SQL files open at once in SQL Query Analyzer? I feel like I am "jumping through hoops" to edit multiple documents. I have to keep selecting "connect" from the menu bar to get a new document for editing. TIA- Bria...more >>

This seems like it'd be a really stupid question, but for the life of me I haven't been able to find an answer that works.... I have a database that's approximately 400MB when shrunk... and the transaction logs are at about 4.8GB when shrunk... I can't seem to get the transaction log any smal...more >>

Hi, I'm getting errors when trying to run a query over linked servers that works fine on a single server. The error text that comes up in QA is as follows; Server: Msg 8180, Level 16, State 1, Line 1 Statement(s) could not be prepared. Server: Msg 170, Level 15, State 1, Line 1 Line 1: In...more >>

Anyone encounter differences between SQL2K EE and SQL2K Dev Editon? FAILS ----- Machine_A [App Server] <-- JDBC --> Machine_A\SQL2K_Dev_Ed..db Error: "com.inet.tds.SQLException: Connection refused" When app server on Machine_A attempts to connect to database on Machine_A the conenction i...more >>

I have a text field (called TreeNode) that contains node identifiers for a dynamic tree on a web page. For example: "1.1" "1.2" "1.2.1" "1.2.2" --- --- '1.2.10" --- etc.. What I need to do is compare this field "value" to another value in a query. (I'm using ASP and VBScript to creat...more >>

Hi All, I'm about to begin studying for the MCDBA certification and I'm wondering what books people would recommend? The following gets a good review, so I'm planning on purchasing it: MCSA/MCSE/MCDBA Self-Paced Training Kit: Microsoft SQL Server 2000 System Administration, 70-228, Second...more >>

Hello, Is there a query or a method that can return the list of all the indexed words in a column that has been full-text indexed in sql server 2000? I need to get the words returned above in a Containstable[] query on another column to get a rank that can suggest a degree of similarity betwee...more >>

Hi All, I have a database that is serving a web site with reasonably high traffiic. We're getting errors at certain points where processes are being locked. In particular, one of our people has suggested that an update statement contained within a stored procedure that uses a where conditi...more >>

Can you give me the step-by-step procedure to setup some alerts in SQL 2000 to get pager or email notification once the : 1- SQL Server (service) is down 2- Trans log is full...more >>

Hi - I hope some one can help me with this. I am using sql server 2000 [Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build 2195: Service Pack 4)] I have a stored procedure th...more >>

I have a table in my sqlserver 2000 that has a field IDNO. i want this field to be my primary key. however i don't want this field to use the autoincrement feature. when i access this table from vb.net and try to add a record this field is autoincrementing. how can i disable the autoincrement of...more >>

Hello, I have been trying to solve this problem for a couple of weeks now with no progress. I created a package that works when it runs from my computer but when it runs as a scheduled job, it fails. I found references to this quite a few times and the responses have always pointed to permissi...more >>

1- What is a transaction? 2-How does Sever & Client communicate ? ...more >>

Hi; Before I start let me say I know this is a silly way to go about things, but it is one of those "my company made me do it" things. My company is in the process of ( finishing ) migrating from foxpro to sqlserver. They have a foxpro program that does a lot of various updates. I would...more >>

Hello group. I have an issue, which has bothered me for a while now: I'm wondering why the column statistics, which SQL Server wants me to create, if I turn off auto-created statistics, are so important to the optimizer? Example: from Northwind (with auto create stats off), I do the follo...more >>

I'm trying to install SQL Server 2000 on a newly-installed Win 2003 Server box. My disk is SQL Server SP 2. I got the error message stating that I couldn't run SQL Server less than SP 3, but I downloaded the patches from Microsoft's website figuring I'd install SP 2 off the disk and then install...more >>

Hi everybody! I need create trigger in firebird and mssql. The trigger must do this: If someone try to delete record in table "mast_trans" and if column "ID" is equal 145 delete cannot be executed otherwise perform delete. How can I do this? My books dont have any examples for this :( T...more >>

When a web application becomes overloaded with traffic, one can offload it by load balancing and clustering the front end web servers. What happens when the back-end MSSQL database becomes overloaded? Does MYSQL offer load balancing and clustering? ...more >>

Hello, what is the advantages running MS SQL 2k on window 2000 verse windown NT? thanks...more >>

Hello SQL and Crystal Reports friends, I am trying to make a report and need some help please. It is a helpdesk database. Jobs are logged, and then closed. Each of these events is timestamped in the database in the date fields “DateLogged” and “DateClosed” Jan Feb Mar Apr New ...more >>

Hi, I'm working with two databases on to physicaly separated servers: #1 is on a local machine en I can only connect to #2 via an ADSL connection. I need to syncrhonise the data. This works fine via the wizard from the Enterprise Manager. But I want to get this done via a script form the Que...more >>

Hi, I have a table-valued user defined function (UDF) my_fnc. The execution of statement "select * from my_fnc" takes much longer time than runnig the code inside my_fnc (with necessary changes). What can be the reason? How can I see an execution plan used for UDF? Thanks a lot Martin...more >>

Using SQL2000, is there another way of optimizing the original query below. TIA, Bob Update Transaction set field1 = (select (sum(tax)-sum(credit))/sum(credit) from tblOrder where tblOrder.id = Transaction.id), field2 = (select avg(price) from tblOrder where tblOrder.id = Transaction.id), ...more >>

Hi, I need to extract randomly 5 records from the table "Questions". Now I use SELECT TOP 5 FROM Questions ORDERBY NEWID() And it works. The problem is that I need an additional thing: if SQL extracts record with ID=4, then it should not extract record with ID=9, because they are similar....more >>

Hi all, I need sone help on generating a SQL statement i had 3 tables now Customer >> Name >> Acct No >> Address >> Phone Invoice >> Invoice no >> Customer no Invoice Details >> Invoice No >> Commence Date >> Expiry Date >> Amount Every month, the customer will come in to...more >>

Hello group! I use MS Visual C++ 6.0, ADO, MS SQL Server 2000. When I attempt to open my database I meet with a following problem: when I try to get a bookmark of the current record in a Recordset object a following run-time error occurs: Unhandled exception in testdb.exe(KERNEL32.DLL): 0...more >>

OK. For DDL, please refer to the classical Northwind ORDERS table, problem/challenge, find the longest duration (start_date and end_date), during which, no orders were placed. FYI, column names by the order of colid per syscolumns: OrderID CustomerID EmployeeID OrderDate RequiredDate Ship...more >>

When you create database diagrams in Enterprise Manager, the details for constructing those diagrams is saved into the dtproperties table. This table includes an image field which contains most of the relevant infomation, in a binary format. SQL Enterprise manager offers no way to script out ...more >>

Hello I´m using MS-SQL Server 2k with a custom application connecting to it. I need a quick and simple way to detect any change (insert, update, delete) to my database (not just to a single table). The purpose is to notify somehow different instances of the application about the data changes,...more >>

I have several ResultSet Querys/Statements within my page. An example of the code looks like this: ResultSet rs1 = stmt1.executeQuery("SELECT right(' ' + '$' + convert(varchar,SUM(ActivePrim),1), 15) AS 'ActivePrim', right(' ' + '$' + convert(varchar,SUM(KGAP)...more >>

How would you count the number of columns with a value not equal to 0 for each row in a table? Thanks! Joe ...more >>

I am trying to set up a "cmd.CommandType = adCmdStoredProc" but I receive the error "Type 'adCmdStoredProc' is not declared". What do I need to do to declare it? I am using MSDE with SQLDataAdapter. I am trying to exceute a stored procedure from within my VB .NET 2003 code. Thanks. JH...more >>

does anyone know if there is a way to generate an insert script based on the records in a table? I want to distribute the contents of a couple of tables to customers without having to use BCP or DTS. Greetings Sjaak...more >>

Hi All, Sorry if the subject line is too obscure -- I couldn't think of a way of describing this request. I have a table that contains approximately 1 million records. I want to be able to be able to select the top x records out of this table matching variable criteria. Pseudo table re...more >>

Hello I am running a SP from the SQL Server Agent, the job has one step that looks like this. exec q_spr_inlevextsystem This job fails with the following message Job 'AutoInlev' : Step 1, 'Run the SP q_spr_inlevextsystem' : Began Executing 2004-04-05 09:00:00 output ---------------...more >>

Hello, I have a table which contains some nullable columns. I want to write a single query, which can be prepared (ie. prepared statement), that can handle null or non-null values for the where clause. Is this possible in a standard-conforming manner? The simple for of the query is this: ...more >>

Hi there, I am trying to execute a DTS package from another DTS package and change some variables in the original DTS. I have used the info on the following page but it doesn't work: http://www.sqldts.com/default.aspx?215. Would you have any input? Thanks, Andreas *** Sent via Devel...more >>

HI I am using the following code in an SP, it seems like an ugly hack I have done to check if the BCP was working or not, I check the table it shold have filled instead of checking the error from BCP itself. Does anyone know how I can check the BCP errors directly? this is the code I am u...more >>

What is the SQL Server equivalent of DB2 common table expressions? For example, with gry(year,count) as( select floor(sem/10),count(distinct ssn) from grades group by floor(sem/10) ) select year,sum(count) Head_Count from gry group by year having year >= 1980; ...more >>

I come from Oracle DBA background and I know a little about SQL serverand SQL Server EM. In a Production SQL Server environnmet: 1- How do you create a baseline for performance tuning? 2- How do you refresh Dev box from a large Production DB? 3- what's the few main scripts you schedule or ...more >>

I am trying to use this code to send an email declare @rc int, @invjournal int set @invjournal = 2222 exec @rc = master.dbo.xp_smtp_sendmail @FROM = N'matt@hasta.se', @FROM_NAME = N'Matt Douhan', @TO = N'inventering@hasta.se', @replyto = N'Reply to NONE', @CC ...more >>

Hi Trying to send email from sql server seems a very hard task, can it even be done without using exchange? all examples I can find relies on exchange but I would rather send it to any SMTP server since we do not use exchange. rgds Matt...more >>

Hi. I am trying to automatically backup transaction log when error 9002 happened. So i have created appropriate job and alert to catch this error. I have two instances of sql server under Windows 2000. One of them is full SQL Server, another is msde. When transaction log is full in full S...more >>

Hi Every Body. Its Biswajit I want to know about use of Store Procedure in SQL.What is benifit of store procedure and we r using it....more >>

Hi, I have two tables. One of them has a text field, and the other has a numeric (integer) field that serves a similar purpose. I want to connect (UNION actually) the two tables, and store the text from the first table in the same field as the number from the other. For example: SELECT ...more >>

Hi, I have a table of text. I need to search for whole words within this text... For example, I need to be able to search for records that contain 'dog' but not return 'hotdog' or 'dogma' for example. I am doing this by throwing a space around both the records in the table and the search wo...more >>

The database has four tables (-> = fields): Account -> AccountID, ClientID, (others) Security -> SecurityID, AccountID, (others) SecurityTransactions -> SecurityID, Shares, (others) SecurityPrices -> SecurityID, PriceDate, Price, (others) Account and Security are connected b...more >>

Hi All, I have a table in SQL Server 2000 that contains several million member ids. Some of these member ids are duplicated in the table, and each record is tagged with a 1 or a 2 in [recsrc] to indicate where they came from. I want to remove all member ids records from the table that have ...more >>

Hello, We are the authors of an application written in PowerBuilder (type Client-Server, database MSSQLServer2000). After some time (1 hour) of client inactivity happens disconnect from server and aplication reports error 'Communication link failure ...' Please, would you help me, if exists s...more >>

How can I make a stored procedure which has a output parameter with text data type? My procedure is: CREATE PROCEDURE try @outPrm text Output AS select @outPrm =(select field1 from databaseName Where field2='12345') GO Here field1 is text data type. Thanks...more >>

How does the preformace of SQL Server depend on the number of databases? Theoretical maximum number of databases on one MS SQL Server is 32000. What is the maximum number of databases in real life? TPa ...more >>

Greetings, I am in the process of installing a SQL database at a customer location. I have determined that there are 3 ways to do this, and I wanted to know which is the best of the 3. 1 Install From Script. In this method I create the database and its objects in scripts that are run via ...more >>

Hello, I am having trouble installing Evaluation copy of SQL Server on my notebook, which has XP, P4 processor,1G Ram, and 14 G harddisk space. After I run the Setup.exe, I get the files files extracted in the SQLEVAL directory. As per the instruction, I need to run Autorun.exe file. For ...more >>

Hi, Anybody have an idea of copying data from tables of a database to another database. It should be a choice to select all tables, single table or several tables. For them knowing Oracle it is possible to do it with 'exp', where you can choose to script the database with or without data. S...more >>

When you import data using DTS into a table that has triggers - do the = triggers fire off if there are triggers for on insert or on after = insert? Thanks, --Micah...more >>

Hi, In my application, I need to copy data from an Excel file into a SQL table. The article related to this can be found at http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B306572 Using this,I am first extracting data from given excel file into a temporary DataTable. After maki...more >>

Hi I have a database with full recovery model. Every night at 11.00pm performs full backup, and dayly every 3 hours performs differential backup, and trasnactional log backup performs every 15 minutes during a day. I am interested what operations i can not recover from log or differential ba...more >>

I have a database with over 450 tables, that until a short while ago has been strictly 3NF. In cases where complicated permutations of data are required quickly for certain parts of the system, sometimes it is not possible to write a query fast enough, due to the complexity of deriving many nes...more >>

My employer currently pays me $1/hour when I carry a pager. I have to carry the pager every fourth week. The problem is that my employer insists that I be available when I'm carrying the pager. So, for $1/hr they expect me to remain within pager range, remain sober, and be available to come ...more >>

Any ideas how I can recover a job that has been deleted (?) from SQL 7.0 ? We had one which was used on a regular basis which has now ceased to exists. I've spoken to the people responsible for the server and was met by blank faces (as usual). They have no idea when it was deleted, by whom or wh...more >>

I need to get a print return of db_namelog01 for the script I have written below: select @cmd=@db_name+'to dump'+@db_name+'log'+right(stuff(convert(char(2),@log_number),1,0,'0'),2) What I get is db_name log01. The space in between is making my script all go wrong!.... Can anyone h...more >>

Hello I am trying to update a table where the trigger only allows one row at the time to be updated, my only way around this has been to use a cursor to perform row by row updating, this seems to have a massive performance impact on my applications, getting rid of the trigger is not an option...more >>

I am not a GURU of SQL, and i have to solve this problem, hoping that it can be solved trough the transaction blocks of SQL Server: i have an user who opens a record in order to modify it. i want that the other users can make a "select" of the same table, but this selection should avoid to ret...more >>

Hi, I have a table containing for ex. a column named MenberDate. The column has property as datetime. Values look like 13.04.2004 (dd.mm.yyyy) in the table. Select 'INSERT INTO Menber(Name, MenberDate) VALUES (' Name + ',' , cast(menberDate as varchar(12)) + ')' as List from Menber Whe...more >>

Hi All, Just wondering if you perform an update using a where condition that actually returns no rows to be updated, does @@Error contain an error message afterwords? We want to fire an update ona table against records that may or may not exists. As a matter of course we test for an @@Error...more >>

I have to run a query to give a column a value based on a time range. Can I use DECODE? select decode(trans_date, trans_date>='01-Jul-2002' and trans_date<='30-Jun-2003','Fiscal2002', .....) as fiscal, from. . . where. . . ...more >>

I have a table of product orders. It contains a row for "platform" and I need to return how many times each platform is listed in the DB Example data for platform could be: XBOX XBOX XBOX PLAYSTATION PLAYSTATION GAMECUBE PLAYSTATION I'd like the data to be returned as XBOX - 3 PLAY...more >>

Hi all, Just wondering if anyone can tell me if an order by clause on a select query would have any impact on the time it takes to retrieve results? Essentially I'm selecting Top 1 out of a table via various criteria and currently getting it back without an order by clause. The order by wou...more >>

Suddenly a stored procedure, very much like several others, is giving EXECUTE permission denied on object 'Add_Adjustment', database 'InStab', owner 'zhoskin'. server:Msg 229, Level 14, State 5, Procedure Add_Adjustment, Line 18. I'm zhoskin. I am the dbo and created the procedure, and when I ...more >>

Hi I've developed a simple web service that lets people easily set up their own websites, and keep them up-to-date themselves. I'm storing all the content, including images in the SQL 2000 database. So far it's working well enough. Is there likely to be a performance or reliability problem ...more >>

Hi All, This seems like a tricky question to me. I have a Stored Procedure that encapsulates a number of updates to various tables within a transaction. However, at a later part of the transaction I need to be able to select records changed by an update statement made earlier within the sa...more >>

Hello, We maintain a 175 million record database table for our customer. This is an extract of some data collected for them by a third party vendor, who sends us regular updates to that data (monthly). The original data for the table came in the form of a single, large text file, which we ...more >>

Hi, I have boolean values in a table for ex. Federation. And I want to select following Select 'Insert into' + member + 'test' as test1from federation Then I get error message Server: Msg 403, Level 16, State 1, Line 1 Invalid operator for data type. Operator equals add, type equals b...more >>