Hi group - I've got a situation that Google and BOL can't seem to solve. I have some applications running at a client site that insert records in to a legacy table, this table's data is being migrated in to the main application's structure, so I defined a trigger on the table to copy the data in...more >>

What do you think about this? It seems to me that isolation levels only affect reads (SELECT statements) in the current transaction because isolation levels only affect how S locks are used. If the transaction contains only INSERT, UPDATE or DELETE, isolation level doesn't matter since they...more >>

Extract from the Microsoft article[1] on fourth section of DBCC MEMORYSTATUS output: <QUOTE> Typically, the majority of memory allocations from MemToLeave are from non-SQL Server memory consumers that are running in-process such as COM objects, extended stored procedures, and linked server...more >>

This isn't a pressing problem that I need to resolve to move forward on a project or anything, but what's the idea behind a primary key? I think of a "key" as a combination of columns that identify a row. And there might be a few keys in a table. Why single one of them out? Is it just to re...more >>

Either its a SQL bug, or an "undocumented", or "I-miss-the-instructions" stuff. Attempt to below give some NULL to WRITETEXT error! SELECT @pImg = TEXTPTR([img]) FROM [tbl] WHERE [id] = @id WRITETEXT [tbl].[img] @pImg @img Unless I re-update the [img] to NULL, then only the above work ...more >>

I have a problem which i am not sure how to solve: I have an article table: ARTICLE AR_ID, AR_CODE, AR_DESCRIPTION e.t.c. e.g. 1, 1001, 'Cadillac Fleetwood' I have an article price table ARTICLEPRICE AP_ID, AR_CODE, AR_TYPE, AR_PRICE 1, 1001, 'P', 5000 1, 1001, 'S', 7500 1, 1001, ...more >>

Simple question... I'm familiar with using SELECT * INTO <tablename1> FROM <tablename2> where <tablename1> doesn't exist and I want to create it as a copy of <tablename2>. Basically I want to know is there a way of doing this, but also copying the indexes from <tablename2> on to <tabl...more >>

Hi, I am writing a desktop .net app (vb.net) and part of that app is to display a dropdown list of all sql servers on the network. I have managed to accomplish that using the sql dmo library, however I am aware that the sql dmo library is being depreciated by microsoft, so my question is ...more >>

Hi All, Please help me to do the below script with out CURSOR. SET NOCOUNT ON BEGIN DECLARE @DONORID INT, @PLEDGEDT DATETIME, @PAIDDATE DATETIME, @LASTACTDATE DATETIME DECLARE cur INSENSITIVE CURSOR FOR SELECT DONORID, MAX(PLEDGEDATE) AS PLEDGEDATE, MAX(DATEPAID) AS DATEPAID...more >>

Hi, i have a stored procedure like this: PROCEDURE BeraterFunktion @beraterid int AS select dbo.BeraterTaetigkeit.Funktion from dbo.BeraterTaetigkeit,dbo.Berater,dbo.ZOT_Berater_Taetigkeit where dbo.ZOT_Berater_Taetigkeit.ID_Berater_Taetigkeit = dbo.BeraterTaetigke...more >>

I back up the log every 15 minutes and append the log onto one file. When restoring is there a way to restore all the logs in one statement. I tried: restore log lbrspecdata from disk = 'E:\backups\transactionlogs_lbrspecdata' (There are about 15 logs in this file and I would like to apply ...more >>

Hi, I have a table that looks like this: event_id hfacsdefinitionid -------------- ----------------- 20001204X00003 17 20001204X00003 15 20001204X00003 17 20001204X00007 15 20001204X00009 15 20001204X00010 15 20001204X00010 15 20001204X00011 17 20001204X00012 15 20001204X0001...more >>

Why can applications using SQL Server be able to handle more users than Access ? Why is SQL Server more scalable than Access ? Thank you. ...more >>

Consider this code : == create table blah (SomeNum int, SomeString varchar(20)) insert blah values (1, 'one') insert blah values (2, 'two') insert blah values (3, 'three') select * from blah where SomeNum = 2 --- XXX select * from blah where SomeNum = '2' --- YYY == Now, both XX...more >>

You can use COLLATE to force case-sensitive comparisons in WHERE clauses, GROUP BY, etc. create table t(x varchar(20)) insert into t select 'John' union all select 'JOHN' union all select 'john' select distinct x from t x -------------------- John (1 row(s) affected) select distinct ...more >>

Don't ask why i need this, (well i'll tell you why: html <select> element limitations) here is my DDL: create table actions ( ownerID nvarchar originatorID nvarchar actioneeID nvarchar ... other fields.... ) i need to filter a view on one of 4 selections in html <select> like so:...more >>

hola quisiera desde una aplicacion poder importar datos hacia una base de datos en mi servidor sql server... alguna forma de hacerlo que me puedan recomendar? Saludos ...more >>

I need to run a stored procedure, or make it simpler, to run a query to access the databases from two machines location at different locations. On client's machine A, say in city A, have installed SQL Server, another machine in head office, say in city B, there is another SQL Server in that ma...more >>

I'm trying to rename an existing column using SQL-DMO. Every time i get the same message from the server: - Either the parameter @objname is ambiguous or the claimed @objtype (column) is wrong. Here is the code i worte: fTable.BeginAlter; fCol.Name := sNewName; fTable.DoAlter; Can anyo...more >>

How do you go about returning a set of records from a table that includes an automatically updated row counter? For example, the table contains: Name ------ Apples Pairs Oranges Do something like this: Select * From Fruit Where [Name]='Apples' Or [Name]='Oranges' And it returns: ...more >>

I have two databases with in same sql server. I want to know whether a join of two tables with in same database is faster or than join of tables across two databases Thanks!!! ...more >>

I can't seem to find the answer I'm looking for, hoping someone can point me in the right direction. I'm writing an update script & am running into a few problems. I've broken my script into chunks: dropping necessary constraints, creating new tables, altering tables, creating new functions, a...more >>

hi, I have a sqlserver 2000 db. I want to alter two columns from one of the tables to not allow nulls. but these 2 columns (one if char, and the other is smalldatetime), one have clustered index on it, and the other one is part of a combined index of two columns. is the only way fo...more >>

I am having a problem with a query, I am not sure if i would use a join or a subquery to complete this problem. I have two queries, and i need to divide one by the other, but i cant seem to get any type of join to work with them. Here is the situation. I have a projectDB table that has a lis...more >>

I am testing restoring my transaction logs in case the time every comes where I need to perform that action. I take a full backup at 8pm at night. I then take transactional backups at 7:00 in the morning for every 15 minutes appended to each other. The full back up is in one file and the trans...more >>

Does anyone know such a tool for table design? ...more >>

Iam doing bulk insert while other users are accessing the database. Does sp_dboption turns off logging for current session or for the database all sessions. How can i continue logging for other users, while iam doing bulk insert.? TIA ...more >>

I have 3 tables indexed for a full text catalog. I'm using ASP. Depending on the table, there's 3-4 fields indexed for each table in one catalog. I want to have one search box that could query any one of the above fields. Do I query the catalog itself? How? Do I have to query each individual t...more >>

In in process, what it;s more efficient create a temporary table into memory or create a temporary physical table were I put all my results to be manipulated?? This temporary table is gonna be update and read in a cursor. Thanks ...more >>

Is it more efficient to do a check to see if anything exists first ... IF EXISTS (SELECT * FROM ....) BEGIN DELETE FROM ... END -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

background sql2k. nt5 running cognos and report net. I have a warehouse database used by the report net. the database is about 3 GB. but the tempdb kept filling up the drive (grew beyond 10 GB) when the developers were developing/running reports. and the tempdb couldn't be shrinked down ...more >>

Hi there, I'm having a problem with some code in a stored procedure that seems to be executing twice. If I call it from Query Analyzer it works as expected, but if I call it from my ASP.NET code it seems to fire twice somehow. A truncated version of the SP looks something like this: CREAT...more >>

In the table design of a SQL 2k table, I see the Formula property for a column. When I do a help on it, all it says is: Shows the formula for a computed column What does that mean? I want to have a calculated field in the table where it would display "Yes" if another field is equal to ...more >>

I've seen similar postings for this in this group, but i wanted to post a more detailed post. I already have a solution for my problem, but my question is "how can i make it better and is there a way to make it better?" I have 3 Tables i combine in a view: Dept Table id dept 1 ...more >>

Hello, I stumble onto an article which explains how you can pass data to a T-sql statement in the form of an xml string but I got lost on how the xml string is invoked into the Select statement. Here is the meat of the article: >> SQL Server 2000 offers you the ability to work with XML...more >>

I need to trace what password is the user trying to use when he's trying to login to the Sql Server. Using Tracing of Sql Profiler all i see is "Login Failed for user Support" but it doesn't tell me the password that was used for this attempt. Can i Find out the password? ...more >>

I have a query that works using a correlated subquery. However, the tables involved are large, and my query is too slow to be practical. I would like to rewrite it without the correlated subquery. The query must return columns from both the [Item] and [Reservation] tables. An Item may hav...more >>

Is there any way to determine if an exchange server is active before running sql mail? We don't always no when maintenance is performed on our exchange servers. The sql server jobs run, but if the exchange server is down it does us no good. We show they ran (job completed), but it do...more >>

Hi, I'm continueing learning stored procedures here and was wondering about goto conditions... If I want to do something similar to a subroutine how would i do it so that when i say go here and do something it will return to where it was called after it was done processing? kinda like this ...more >>

Hello, I want to insert a value into a temp table Select 'x' Into #tmp but I get error message that says "No column was specified for column 1 of #tmp". How do I specify a column for this example? 'x' is a varchar (or nvarchar). I have tried this to no avail: select 'x' varchar(...more >>

I have a view of my main table. But this view shows data with a delay. I want to show data changes in real time. How do I accomplish this? Any help appreciated. Fred...more >>

How to set ExecIsTriggerDisabled OBJECTPROPERTY? Thanks in advance. ...more >>

I have this sql statement: SELECT appID, questionID FROM tblAppToQuestions WHERE questionID = 100 And the results are: appID | questionID ------------------- 6 | 100 25 | 100 47 | 100 63 | 100 But if I run the other sql statement: SELECT appID, questionID FROM tbl...more >>

Is there an easy command to find the first of the month of a giving date? no need to code it if there is already one... say i have a date 6/12/2004, obviously the 1st of the month is 6/1/2004.. not to hard to code but just wondered if there was a predefined function, thanks! ...more >>

I'm trying this on a varchar field: SELECT LEFT(details, CHARINDEX(' ',details,50)) as tdetails but if the 'details' field has no space in it then nothing is returned! How do I make sure I return stuff in this case? Ben ...more >>

Hi All, I have one stored proc that is consistent in all my databases. I need to run this proc once a day in all my databases. Is there a way I can do this easily?...more >>

What is the life of a temporary table created inside a trigger with something like SELECT ... INTO #TempTable FROM deleted Does the table get droped when the trigger finishes? Thanks. -- Michael Tissington http://www.oaklodge.com http://www.tabtag.com ...more >>

is there a simple way to use the getDate() function to return the current date in the following format: June 24, 2004 4:33.30 AM November 12, 2004 11:09:04 PM or at least something very similiar - - i know i could do something like this, but it seems too complex for something that shou...more >>

When you have a process in a transaction block, and an error occured does the transaction automaticlly roll back or do you have to specify an error handler to tell it to roll back the transaction before a commit trans is executed? thanks ...more >>

sorry for the long post... I am able to parse a delimited string that is less than 8000 bytes. However there are times when my string could be greater than 8000. In that instance I have problems. My problem is illustrated below. The last date parsed is '8/24/2010', but the last date in the ...more >>

Using SQL2000. I've got 2 tables, one for procedures a resident has done and one for procedures they're supposed to do and how many times they're supposed to do it. I need to create a 3rd table that contains the procedures they have not done yet or have not done enough of. Could I use a stor...more >>

We are have the same version of an application running against multiple databases. One client's database (SQL 2k/SP3; high volume - above 70 million rows on one table) is experiencing problems. We have been able to track down 2 of the problems to an OPEN Cursor that is issued from the ap...more >>

Hi I want to be able to send each line that exists in a table as an email,but each line that exists has to be sent as a separate email .But I have had trouble doing this I have an example query below that doesn't work maybe someone alot better than me can help me thanks for any help Mi...more >>

I am having trouble getting my view to display its results. While there are close to a million records, I have thrown in criteria to return far fewer results, but it still seems to time out. Here is the UDF i have created, the view just displays all the results from the UDF. Can anyone tell me why t...more >>

Hi, I am having a strange error when running a large stored procedure in a SQL 2000 database concurrently from several different visual basic 6.0 programs. The error message I get is "incorrect syntax near '20040605'." ('20040605' is one of the parameters I pass into my stored proc) The...more >>

Hello, Can someone please take a look of my issue locating at th following addr.: http://communities.microsoft.com/NewsGroups/previewFrame.a sp? ICP=SQL&sLCID=US&sgroupURL=microsoft.public.sqlserver.serv er&sMessageID=%253C20dee01c459e4%2524d4ebdf60% 2524a501280a@phx.gbl%253E Thanks in ...more >>

Hello Workgroup Is it possible to progammically alter the Fill Factor for all the indexes on a database in SQL Server 7.0? I have a Commercial Database that has all of the Fill Factors set to 0 which is impacting on performance. Although I can set the Fill Factor manually, whenever we ...more >>

Hi, executing the following query : UPDATE project SET status = 'N' WHERE (status IS null) gets the following error: Server: Msg 512, Level 16, State 1, Procedure dpa_av_project_update, Line 25 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=...more >>

I have large "groupings" of data which are normally referenced as a whole. Is it better to store this material externally to the DB and reference the "file path" in the DB rather than try to store this stuff in the DB itself? Thanks ...more >>

hi trying to insert all rows from one table into another table (different databases), where a column in the first table has value 'u'. The following query should work, but returns an error: INSERT INTO [server].[db1].[dbo].[table1] (col1, col2, col3) ( SELECT col1, col2, col3 FROM [se...more >>

Hallo, I have a problem with dirty reads. The following is a test code to reproduce the problem: 1. insert a row into the table 2. delete this row 3. read this row The point is that these steps are sequential executed but step 3 is executed from a different connection. And just alw...more >>