Hello! How bad is it to use SET QUOTED_IDENTIFIER OFF before an INSERT and then switch it ON straight afterwards. So I can insert strings like O'Neil etc. It's my only design option at the moment. Everything works fine and as far as I can see, it's only set to OFF for the current connection/se...more >>

Hi I'm grateful for any light you can shed on this!! I've to admit, it's an unusual design but I've multiple contact tables named e.g. i2b_ash_contact or i2b_ted_contact. 'i2b_' and '_contact' are static but the middle part is dynamic. Storing all contacts in one table with an identifier ...more >>

I hope you can help me. I posted this in the microsoft sql server newsgroup a few days ago and got no response so I thought I'd try here. If I can provide any clarification I'll be glad to do so. I'm trying to calculate a column based on the value of the previous record. I'm not very experie...more >>

Hi! I would be grateful for any advise regarding what I'm doing wrong.. My brain is stuck. Probably some stupid simple mistake I don't see. Thanks very much for your efforts! Martin I have this code: DECLARE @ContactID varchar(10), @AddressID int, @cmdSQL varchar(500) Set @ContactID...more >>

Hi, I am evaluating Microsoft Reporting Services against Business Objects as a reporting tool against SQL Server. I am primarily a BOBJ developer and am finding RS not easy to use. Can anyone provide any feedback to the overall comparison between these 2 products as I am finding RS frustratin...more >>

I am trying to get some information to compare and contrast the Truncate Table function and the Drop Table function. I know that using Truncate Table is faster and saves the structure of the table while the Drop Table will delete the table totally (data and structure). My question is whether u...more >>

Does anyone has a script which gives all databases names and there allocated, used sizes in SQLserver2k. I want to schedule this to create a daily report. Thanks, Nasir ...more >>

Hello, I know that I've seen this question asked on here before, but I can't find an answer that gives me the performance that I need. I have a table that stores events for users: CREATE TABLE Lead_Action_History ( lead_action_seq INT IDENTITY NOT NULL, lead_action_date DATETIME...more >>

Hi All there! I am quite new in MS SQL administration so let me explain how it work on Your instances of SQL Servers. We have several DTS packages on our server, all of them managed on some station which have seriously hardvare problem. So we wolud like to catch two problems at one time and d...more >>

Hi! I would be grateful for any advise regarding what I'm doing wrong.. My brain is stuck. Probably some stupid simple mistake I don't see. Thanks very much for your efforts! Martin I have this code: DECLARE @ContactID varchar(10), @AddressID int, @cmdSQL varchar(500) Set @Contac...more >>

I am a sql novice and would appreciate any help with the following problem. In a table I have property addresses stored in 6 fields. Field6 always hold the Post Code. However, fields 4 and 5 are sometime NULL. Using the desktop integration package we have which interfaces with MS Word when ...more >>

<usualDisclaimer>Please forgive me if this is in the wrong group, and if so, what is the right group.</usualDisclaimer> Let me start off by first saying im a newb. Ok, with that out of the way I am trying really hard and boy have I learned a lot in the last little while but I have a question ...more >>

I have a SQL database with a number of tables (assume one of them is t1 and one of the columns in T1 is t1col) For a reason I don't understand when I sign on to query analyizer to the database the following sql state returns an error - something like "invalid object t1" select all * from t1...more >>

All, I seek your knowldege. I am looking for a monitoring tool that has the abilility to page and email me should something go wrong with SQL server. I know of two such tools: 1.) BMC Patrol, and 2.) Quest's Foglight. I was hoping that some of you have had some experience with other such tool...more >>

I see a lot of posts saying that to check if a job is currently executing one needs to look at run_status for the job in the sysjobhistory table in msdb catalog. I also see the possible values of that column in the help files. What gets me is that even when I know my job is executing (accordin...more >>

Hello, Is the multiple instances capability of SQL2K mature enough for a Production system? We're looking at upgrading our hardware but the proposed solution calls for consolidating two currently separate SQL Server's onto one machine with two CPU's. Of the current two servers, one is fo...more >>

Is there anyway to extract part of a string in a stored procedure using a parameter as the starting point? For example, my string might read: x234y01zx567y07zx541y04z My Parameter is an nvarchar and the value is: "x567y" What I want to extract is the two charachters after the parameter, in this...more >>

Hi, i have imported an access database into sqlserver 2000. In access, i have some table fields which were using automatic number as datatype. But i see that there is not a datatype in sqlserver like automatic no. I have to enter id numbers to my tables for each records automatically. Would any...more >>

Hello: I run one process that calls the following the store procedure and works fine. create PROCEDURE sp_GetHostSequenceNum AS BEGIN SELECT int_parameter_dbf + 1 FROM system_parameter_dbt WHERE parameter_name_dbf = 'seqNum' UPDATE system_parameter_dbt SET int_parameter_dbf = int...more >>

Can you change your license setup if it was initially done by seat and you want to change it to by processor? The server is already up and running in a production environment. If so how?...more >>

Hi, I am new to Win2k and SQL server and need to back up everything each night. I used the supplied NT backup utility to schedule a backup but this missed some files that were open or in use. I am more used to Unix/Informix where I could backup everything whether it was in use or not. Ho...more >>

I have upsized an Access database into SQL Server and manged to get the data in place ok. The wizard created an Access Project which I have started to modify. However, I am also trying to get data from an AS/400. Specifically for a currency/exchange rate file. I only want ot copy the records s...more >>

I'm trying to backup a SQL 7.0 database to a network server (NT) from Enterprise Manager and am getting the following error: Title bar: Microsoft SQL DMO (ODBC SQL State: 42000) Message: Backup or restore operation terminating abnormally. The SQL Log has the following message: BackupDiskFil...more >>

I'm trying to rebuild from a meltdown. I'm using disk backup files. I could have sworn that I had SQL Server 2000 SP3 installed on the old machine, but maybe not. With SP3 installed, I get a message along the lines that the restore can't be done because the backup was created using server vers...more >>

Hi; I went to the microsoft site to try to find a guide to the error messages that the jdbc drivers give ( for sqlserver 2000 ). I had no luck. Does anyone know if there is such a guide? I got this mysterious error message below in my logs ( nothing visible in user land ). Anyone kn...more >>

Hi, Have a database that contains various tables. I need to run a query on two tables. Table A contains a column called Titles Table B contains a column called Uni_Titles In Table B's column it contains multiple titles as shown below separated by semi-colons in each row. Table B...more >>

I'm having two general problems trying to do a JOIN. I have a table with three fields {Code, Date, Amount}. Code+Date is a unique key. I'm trying to get a rowset with 1) one row for each unique Code+Date pair, 2) and with each row containing, {Code, Amount for Date-A and Amount for ...more >>

Hi there... Is it possible to write an UPDATE or INSERT query, where the new value comes from an array? For example: UPDATE table_a SET column_x = [@array1] WHERE column_y = [@array2]; It's a query (for argument's sake called query1) in an Access database, which I'm accessing as a stored...more >>

Hello, due to some testing my SQL Server crashed. I have to reinstall it and ... For now I only have .mdf file and nothing else. My question is: is there any possibility to restore this .mdf file for SQL 2000? I have some useful data in this file and there will be nice to use it. Best Regards...more >>

I have MS SQL Server 7 and over the years have built quite a few Stored Procedures. It would be extremely convenient if I could print out or view ALL the text of ALL the stored proceudres at once. Is there a way i can do this? Is tehre a way I can make a quick print out?...more >>

Hi, I am running a financial database on SQL 7.0. For several reasons I cannot upgrade to another version shortly. My problem is that I need to create database users that have limited access to a number of tables. Example: tableX: code, value, type a, 100, x a, 200, y b, 300, x b, 400,...more >>

Here is my issue. I have a list of merchants (generated daily) and I need to assign them eually to a set of analysts. Both the merchant list and analyst list can change daily. I want to assign each merchant with an analyst daily and need help to write out a stored procedure for this (do not want...more >>

Hi, I have a stored proc StoredProc1 = { INSERT INTO Table1 SELECT * FROM View1 Return @@ERROR } StoredProc1 is used in another sp StoredProcMain = { (some code before)... EXEC @iResult = StoredProc1 If @iResult <> 0 BEGIN ROLLBACK TRANSACTION Return @iResult END .... (continu...more >>

How do I open more than one existing *.sql file at a time without closing the first? When I want to open more than one saved query file, the first one closes. Also, when building a new *.sql file, when I want to open a saved one for reference, QA assumes I want to close the new one, and prompt...more >>

I have observed that a temporary loss of a domain controller can cause problems creating new ado connections between a client machine running ado and a separate sql server machine that are members of the domain. I understand why this happens when creating connections with windows authentication...more >>

Hi I want to trace all the selects/deletes/modifys whatever on a database in an application that are performed in a seperate application. I need to look into this, any ideas? - Can triggers do this kind of thing - Can you somehow access the profiler via OLE or similiar to do this? - Anyt...more >>

I just took over admin control of a website at http://www.lyricster.com and I am having a hell of a time trying to pin down the problem of double spacing when I add new lyrics to the database. The thing is, most all of the db items (lyrics) on the website are single spaced, and yet when I try ...more >>

Hey everyone, I read in a SQL Server book that you can now create a table variable. DECLARE @TMP TABLE (list of fields) Then you can you can use the statement INSERT INTO @TMP SELECT (whatever). I've tried it and it works. The book also says that you should be able to pass these v...more >>

I have a single .asp page that opens a connection and then sequentially opens and closes 14 recordsets from stored procedures to obtain various product information before closing the connection. Is it common practice to do something like this? Or is opening 14 recordsets going to become a rea...more >>

Have gone through BOL and Google, but can't find the answer... please help with a simple Q. I'm trying to create a simple cascade delete trigger in SQL Server 7 where deleting "parent" records in table X delete corresponding child records in table Y. Table X ========= X_ID SOME_VAL Table...more >>

I have a query (query1) that operates on the outcome of another query (query2). However, query1 only operates on the first row of the results from query2. How can I make the query1 work on all rows returned from query2? ...more >>

Hello, How can I see which login make more traffic to MSSQL ? Not by IP and external firewall , but with binding to database login ...more >>

Greetings, In the customers table in Northwind db, one can update PK (customerid) and all other fields in the same table. My question is how can you do this in the udpate stat. That is, if one wants to write update query to update all fields including PK, how it can be set? Using PK in the ...more >>

Hi there... I am having a whole bunch of trouble trying to design a certain query. I have two tables (see a representation of them here: http://www.plankmeister.org.uk/tables.html ) one called menu_data and one called page_data. menu_data contains a list of defined menus (menu_group) which ...more >>

Hello there, I have and small excel file, which when I try to import into SQl Server will give an error "Data for source column 4 is too large for the specified buffer size" I have four columns in the excel file, one of the column contains a large chunk of data so I created a table in SQL S...more >>

Greetings, I have to admit that I'm still a beginner in the database field, but I'm actively studying, and this is why I will be utterly grateful for the proper, accurate, and wise guidance to the right direction or specific paths of database studies. I simply want to make my first major da...more >>

have a urgent requirement. Please somebody help me. I have a table departinfo with following records begin_time end_time Name Pieces 10:00 10:15 PopCorn 3 10:15 10:30 Biscuits 5 10:30 10:45 PopCorn ...more >>

I have already install my Microsoft SQL server 2000 as not case sensitive, how to I change it to be case sensitive?...more >>

Is there any easy way to create a change script as illustrated below for = all tables within a database? Right now I would have to create a seperate script for each table. I would like to be able to update the customers database while = preserving their exisiting data. -----------------...more >>

Is it possible to call a user-defined function without prefixing it with 'dbo.' within a SELECT clause somehow? Just curious; it's not a big issue but just a stylistic one for me. Thanks! Joel Thornton ~ <groups@joelpt.eml.cc>...more >>

I have two tables that are related by keys. For instance, Table employee { last_name char(40) not null, first_name char(40) not null, department_name char(40) not null, age int not null, ... } Employee table has a primary key (combination of last_name and first_name). ...more >>

Hello everybody I work at a company in Iceland and we have developed a 3-tier solution written in ASP - Visual Basic - MSSQL2000, 4 companies are using the solution almost constantly and accessing it through a browser. The connection has never gone down (yet) so that it has affected our clien...more >>

Hi, I am running SQL Server 2000 SP3 on Windows Server 2003 and since recently have a strange problem executing shape queries from COM+ components using ADO. Until 4 days ago, they worked, then from one moment to the next (I must have changed something, but I have no clue what other than re...more >>

I'm trying to track this error from the driver down: '42000' [-1] '[Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'deadrow' already exists. ** AND ** [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated.' [msg='SQL call failed.']....more >>

Hi, Our DBA group is debating production release methodology. We always perfect our deployment package (typically a script) against the database in a Staging environment, before executing the package against Production. One side argues that the safest approach is to create a script containin...more >>

I am installing my application thru Installshield Professional 5. How to know whether MS SQL Server is installed or not on the machine on which i am installing my application. What is the registry key thru which we can find this....more >>

I have a column that has 75 values, 50 are unique/25 are duplicates. I need to be able to bring back a list of the duplicates, listing all rows even if more than two have the same value. I need to be able to do this using t-sql. Thanks, Tim...more >>

I need to insert a last and first name field taken from a full name field on the same table. If I have 25 rows of customers in this table, what would be the best way to do this? I can split the full name into 2 fields with the syntax below, but I need help with the syntax for inserting these to ...more >>

Hi, I would like to parse out each value that is seperated by a comma in a field and use that value to join to another table. What would be the easiest way to do so without having to write a function or routine ? EX. Table AAA COL1 COL2 1 11, 124, 156 2 ...more >>

Hi All just been given a project converting clipper app with Dbase files into Windows using "CA Visual Objects" Now my question: My objective is to keep the selling price of the app as low as possible but I need SQL database. MS SQL 2000 is 1st price I know but what els is available at mini...more >>

Hello, I am running Microsoft SQL Server 2000 on a Windows 2000 Sever. I have been working with SQL Server, Building ASp WebSites for many years now. I am by no means an expert - nor have I had ANY formal training. So ebar with me if my questions seem elementary... I have some questions reg...more >>

I get the following error when trying to create an Access Data Project: Login failed for user 'myname'. Reason: Not associated with a trusted SQL Server connection. I'm using a specific username and password. The sQL Server (actually, MSDE) is installed on \\server1, and I'm working on m...more >>

I'm testing this query and it does not append any new rows from the source tables unless the destination table is empty. I want to design the query just to add new records from the destination tables and leave existing ones in tact. Help appreciated. Query: INSERT INTO tMASmembers ( Division...more >>

How can i change the default value of a column? I already have a column named DateOfRental but I want to alter it so that it has default value getdate() Thanks David -- http://www.nintendo-europe.com/NOE/en/GB/register/index.jsp?m=l&a=Prodigious ...more >>

I know the answer to this is so obvious, I've done it before myself, but I can't for the life of me get a check to work I have a column called SaleOrRent which is char(1). all i want is to make sure the only value that the column will accept is an S or an R does this check have to be in the ...more >>

I'm trying to use a couple of variables in a stored procedure. Things work fine when I hard code the data into the variables and also work fine when I use the variable in the WHERE clause and hard code data for the other variable. So, I think I have a syntax problem when trying to use "FrontPa...more >>

I am using a clever product called SQL Expert Pro to optimize problematic SQL statements. It works by generating syntactically identical variations of the original SQL, finding all unique execution plans, and batch testing them. Anyway, it generally turns out that a very odd change dramaticall...more >>

Hi all, I am writing a database using SQL as the back end and Access as the front end. My project is to create a rota for when people have to work. The problem I have is setting up the tables so I can query them correctly. I currently have a staff table which holds the staff_id and staff_n...more >>

Hi everybody ! I´m maintaining a large intranet (approx 10000 concurrent users) running on one IIS box and one DB box with sqlserver 2000. Currently there is 2,5 GB Ram, 1 1400 mhz cpu and 2 scsi disks installed on the db box. Sqlserver is set to use max 1,4 GB RAM, and the sqlserver does n...more >>

Hi All I know you're going to ask for table definitions and insert statements, but I think I'm just confused on the syntax of how to get this query to work rather than anything else. My setup is as follows: TABLE 1 - STOCKCATEGORIES This table consists of the STOCKCATEGORYID and the NA...more >>

Hi all! I need to insert into destination table from source table depending on the mapping provided in another. Example, Source table has columns 'A', 'B'...'Z' Mapping table has 2 fields: 'Source' field with values A,B,C,D,E 'Dest' field with values P,Q,R,S,T Destination table has the same...more >>

Hi all, I am new to the ADODB.Stream I am using following code lRecordset.Open "Select * from <some table-name>" 'this query return more than 1000 records dim lstream as new ADODB.stream 'assigning the recordset data to the stream lrecordset.save lstream lStream.Position = 0 Dim lRecord...more >>

Hi, i got a .mdf file with a database but i dont have the transaction-protocol. Is it possible to recover this database? Thanks Philipp ...more >>

I'm working with some long standing VB/SQL Server applications and for the second time we've suffered from having the parameters to a stored procedure call get silently truncated now that the data field has got much larger than when the code was developed all those years ago. This is always very...more >>

Hi, I'm developing an application to crypt SQL databases and i need to read SP and Views source code from VB6, if you know how to do this please answer me. Greetings....more >>

Hello, I am new to MS SQL database and have recently downloaded MSDE (Coming in from an Oracle background). I have installed it and have a couple of questions. 1.) Is there a product something like SQLPLUS (hopefully shipped with MSDE) where you can log on to the database and view tables/data...more >>

Hi all, I have a query that looks like so: SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID AS Person_Name FROM F0911 WHERE (GLAID = '00181913') However by stipulating that GLAID = GLAID I cannot get the person_name as not all the GLALID fields are filled in. from my ...more >>

Anyone know if this has been resolved? Not hugely important, more amusing. Thought I was patched up. sp_lock Reports information about locks. Syntax sp_lock [[@spid1 =] 'spid1'] [,[@spid2 =] 'spid2'] Arguments [@spid1 =] 'spid1' Is the Microsoft® SQL Server™ process ID number from m...more >>

Hello guys, Wonder if any of you could help me out here. I have just created an new empty database and imported data from another database into it. This was done with the import wizard from MMC. First thing that I noticed was the size difference, the old database was well over 1GB, but the...more >>

SQL Server 2000 Is there any way to take sample data in my database and create an INSERT = INTO script? I have a commercial application that I would like to include sample = data, and instead of restoring a backup like I am doing now, I would = like to first run a script that creates the da...more >>