I have a single server running SQL Server 2000 sp3a, and acting as a ASP.NET webserver (.net 1.1 sp1). W2k sp4. Dual Xeon processor, 2 gigs RAM. Everything works fine, but sometimes when I execute a particular stored procedure (on server console using command line, nothing .net), my webapps ...more >>

Hello, I am running a complicated stored procedure (that calls a few stored procedures) that returns lots of result sets. I have a few SELECT statements that I use to initialize loop counters. I use the loop counters to help me iterate through a local temp table. I have two questions in re...more >>

I have a problem, which I can explain but am struggling to translate into SQL syntax. myTable has 3 columns, with some example values ID NAME NUMBER_ITEMS 1 john 4 2 tim 3 3 john 2 4 tim 1 5 ...more >>

Hi, I want to check for records from the fAdrBook table that contains any of the "/<>\|*" characters in the CompanyID column. I know that I can say select * from fadrbook where companyid like '%/%' or companyid like .... but there should be a better efficient method? Kind Regards, Rex De ...more >>

A user ran a command that started updating every record in a table that had about 40 million rows because she didn't have the right where clause. Now when I try to kill her spid, I am getting this error message. SPID 60: transaction rollback in progress. Estimated rollback completion:...more >>

Hi: Simple question--is there a tool that convert SQL script written in SQL Server to the equivalent in another DBMS such as Oracle or DB2? Thanks!! blair ...more >>

SELECT iif(fromwhom<>10,"10",fromwhom) AS xuserid FROM crosstable WHERE fromwhom=1 i want to select fromwhom value as 10 if it is not equal to 10 and itself value of it if it is =10 Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '<'. ...more >>

i'm using sql2k. i dont' see that sp in the master database but many articles refer its usage thru sp_smtp_sendmail. thank you. ...more >>

Hi Everyone, Thanks for helping me with this... I really appreciate it. I have a table like this : col_1 col_2 col_3 col_4 col_5 A 1 4 A 2 A 3 B 5 6 B 7 B 8 I want to run a query and get result like this : col_1 col_2 col_3 col_4 col_5 A 1 2 3 4 B 5 6 7 8 An...more >>

Consider the scenario where the database is big and let's say more than 10 users at a time are accessing the database through some software. Let's say because of some hardware problem system crashed and SQL Server database is restored (which was set to Full as Recovery Model) to a point in...more >>

I'm trying to do a EXEC @fsof = sp_OAmethod @fso, 'openTextFile'/*, @fso_return out*/, @strfilepathname,2 (@fso is a createfile method that works fine) but only get this error code out. Can't find it anywhere. What is it? 0x80042725...more >>

I'm using VB6/ADO to connect to SQL Server. What I want to use VB to = execute a stored procedure. That stored procedure creates 2 temporary = tables which are basically summaries of information from many other = tables. Then I would like to read those temp tables and do something = nifty wit...more >>

How can i run a single script that will indexdefrag all indexes on all tables in a db ? ...more >>

We use binary checksums on columns to determine if data changed. We have a text column we need to check but binary checksum fails. Does anyone know if there is there an alternative method to determine if data changed for the text column? thx, Jan...more >>

Hi all, I've two tables depicted bellow. I would like to get All jobs (from tbl_jobs) where the difference in days between jobs.date and the current date is greater than tbl_myparam.days (tbl_myparam is a table that has exactily only one row) I'm trying "SELECT tbl_jobs * FROM tbl_jobs ...more >>

We have used triggers extensively in the past because we like how they protect and maintain the data right at the source, regardless of who or what is accessing it. Since we have not seen a big need so far for a multi-tiered arrangement, we have located most of our business logic in the trigger...more >>

Hi All ! Are there any limits for the sql server mdf files ? We have one mdf file, it is 19.4 gb but it does not grow up. All clients and applications are down right now.They can not work with that database. Server configuration is Windows 2000 server And Sql Server 2000 Enterprise Editi...more >>

Afternoon, I am doing an update statement, and need to update a field in Table 1, with a field found in table2. The problem is, there is SOMETIMES more than one record corresponding in the 2nd table. In this event, I need to select the 2nd record in this table. What would be the best way to...more >>

I am trying to get today's date in my SQL Statement with GETDATE() but it returns also the time. Is there a way to return just the Date part without the time? i.e. 09-08-2004 not 09-08-2004 10:00:00 Thank you Peace in Christ Marco Napoli http://www.ourlovingmother.org ...more >>

Hi We have a stored procedure which transfers data from a set of tables in DATABASE_A, to a set of tables in DATABASE_B (databases will ALWAYS be on the same server). Right now one of the database names is hardcoded in the stored procedure, is there a way of doing what we need without hardc...more >>

I have a view that shows the data that I need to send on a daily basis. I have a DTS package that runs and shoots a file to my desktop, but, I want to be able to shoot it via e-mail ? Any suggestions? Dennis ...more >>

i have two field.. fromwhom towhom 1 2 1 3 1 4 2 4 3 10 i have two parameter... parameter 1 will find the other friend of his code **************************** such us parameter1=1 ...more >>

Hi I have a series of tables linked by a 'Mapping' table. The Mapping table links objects on one table to objects on another. Each row has a GUID as a key. Aside from the key, each row has two more rows. One for GUID1 and one for GUID2. GUID1 and GUID2 represent the keys for entries in ...more >>

I have three tables in a 1:1:1 relationship We bulk insert gigantor data files into the first one, then run massive string chomping on it in VB.NET, updating the other two with the results. CREATE TABLE Table1 ( keyCol INT IDENTITY(1, 1) PRIMARY KEY CLUSTERED, Col1 NVARCHAR(255), ...more >>

Hi I am using ADO, VB6 and MS SQL Server 2000 Why is it that when I run this statement I get the wrong date format and date sSQL = "UPDATE tbl_MyTable SET tbl_BTP_Scenario.UpdateDate = " & Date() sSQL = sSQL & " WHERE tbl_BTP_Scenario.ScenarioID=" & glScenarioID adoCON.Execute sSQL ...more >>

I have the following two tables: Table Requests ID Team (varchar field) 240 2,3 241 1 242 1,2,3 Table Team int ID Desc 1 SC 2 PE 3 CO I want to cre...more >>

Hi, I'm importing records from a data file, and each record has a type, date run, and file name field.. I'm trying to return unique filenames, and the associated date run, recordtype, and count of how many there were for that group. What I want to do is have it return the total number of re...more >>

is there a generic stored proc which allows me generates INSERT sql statements for a table(s)? Thanks ...more >>

Hi, I have a database that is very large. I deleted many un-necessary records (about half the database) but the size didn't change. If I truncate the data, is that going to slow down queries? please advise. thanks rafael ...more >>

Folks, Can anyone explain why when I try to update the underlying tables of an indexed view I get an error Server: Msg 2601, Level 14, State 3, Line 75 Cannot insert duplicate key row in object 'mvFB' with unique index 'clust'. The statement has been terminated. FB_ISSUE is ...more >>

How to import Microsoft Excell table (or multible tables from a single file), using stored procedure? TIA ...more >>

Hello, I need to create a query that returns any record in tblB that is not in tblA. Can anyone advise how to do this correctly? tblA: column1, column2 ---------------- 1, a 2, b 3, c 4, d tblB: column1, column2 ---------------- 1, a 2, b 3, y 4, z Using the example tables ...more >>

Hi Is there anyway to return only one recordset. Because the statement uses 3 SELECT ... even though two are embedded in INSERT it creates three recordsets. Two blank and one that I want. How do I tell it to not make recordsets for the first 2 selects? Thanks Tim ...more >>

hello all, I am relatively new to the world of SQL programming, however, I am quite proficient with VB.net and C++, etc. I have an VB.NET application that requires access to a SQL database that has approximately 91,000+ records. It needs to show the entire list when the application shows u...more >>

I have a field called bdate it is the type of datetime. It has values as below.. 1911-11-23 00:00:00 I run the code below Select convert(smalldatetime, bdate, 101) from table.. My output is still 1911-11-23 00:00:00 What am I doing wrong?...more >>

Hi, Is there any sprocs in SQL Server which can give me information about the server which I can use for licensing of an application. Let me explain: I have a small rich client database application that currently doesn't have a license model. I now want to apply a licensing model to this appl...more >>

Hi, I have a table with a half a million records. there are about 6,000 duplicate records. How do I get rid of the duplicate records but keep the originals. this is a sample query to get all the records that are duplicates select mpn, count(*) from tbl_products where sourceID = 2 group ...more >>

It's weird but it seems that the number of questions posted recently about cursors, maybe in the last month or so, has just exploded. I wonder what is happening that is making so many people turn to cursors. Are we getting more software programmers writing SQL? Or conversley are we seeing f...more >>

This is probably very simple, but I can't find it. Is there a function = to determine min/max between two values without using the full SELECT = MAX/MIN? I.e. I would like to write a statement something like this: SELECT *=20 FROM dbo.MyTable t WHERE t.datetime_value =3D MAX(x,y) given t...more >>

Hi, I have 'Companies' table that has information about some companies. Each company can send a message to another company. I created second table that logs sent messages. This table has a filed to keep ID of sender company and another field that keeps the ID of receiver company. I want to creat...more >>

Hi The following is the SP I am using: CREATE PROCEDURE sp_lookup_web_page_prompt_login_link_list @page_prompt_link_id INT AS CREATE TABLE tmplogingroups (login_group_id INT PRIMARY KEY , lg_description VARCHAR(50), on_page BIT, ordinal_position INT) INSERT INTO tmplogingroups (...more >>

Hi, Why it's not possible to create trigger on temporary tables? What can I do instead? Thanks, Amin ...more >>

I've created a temp table in a stored procedure. Should I create a primary key on it to improve the preformance? Thanks ...more >>

First of all, I don't know to type Chinese! But, a user of my database wants a field where he could enter Chinese names in Chinese. I presently use a varchar (100) for the name. I know I should change this to nvarchar(100), but what else should I do? Or is this it? If I change the type to nVarcha...more >>

When using SQL server and doing some memory intensive operations, SQL = Server will consume enormous amounts of my PC's memory and paging file. = When completed, the system as a whole runs slowly because of all the = information SQL Server is keeping in memory/paging. If I stop and = restart t...more >>

When I define a filter in Profiler on the TextData column, e.g., TextData like '%Shippers%', it does not filter out blank TextData. How does one do that? Thanks Vern Rabe...more >>

Very sorry for the repost to new thread... having a lof of trouble this morning with newsreader (not leaving Outbox and not deleting from Outbox)... thought new thread may work. Sorry. Thanks for the replies, everyone. All the solutions sem to work fine, however, I can't seem to impliment...more >>

Hi All, Can someone please help me? I am trying to return the most recent record. Below is my query at this point: The date Column is not in the example because I don't know where to place it. Let's say the column is called "LastUpdated". By the way this is within a function. DECALRE @R...more >>

I have a cursor in a trigger. I want to fetch the data from the cursor without using FETCH NEXT FROM cursor INTO variable. Is that possible? How should I do that? The problem in the trigger below is in the @c_deleted.something which doesn't exist. Thanks, Reynold CREATE TRIGGER trg_tr...more >>

I=B4m trying to store an image data type in a database but I=20 always receive this error message: Text or image pointer and timestamp retrieval failed. I=B4ve created a table in Northwind database named test. id char(10) photo image textcopy /Ssqlserver /Ulogin /Ppass /Dnorthwind /Ttest /...more >>

Hi I have created a DTS package that extracts data from my table to a flat text file, This package is scheduled to run daily. The problem that i am faced with is that every time it writes to this file i need the file to be name with todays date (eg : E080904.txt - this will be todays file), ...more >>

I am trying to update a table via dynamic SQL using a date variable: CREATE PROCEDURE dbo.InitializeAuditSummary @DBName varchar(100), @SelDate datetime AS DECLARE @strSQL varchar(2048) SET @strSQL = 'UPDATE ' + @DBName + '.dbo.AuditSummary SET Inserts = 0, Updates = 0, Deletes = 0...more >>

Our SPs are each defined in their own files. Each has a drop (after checking for existence) and a create statement. Each has SET NOCOUNT on. Each has GO statements where required. When we create the database, we concatenate all of these files (there are something like 150 at present) into o...more >>

Hi I have a table named Navigation and it has a varchar column called urlname ...this holds URL'S. A server name has been changed so I need to change just the old server name to the new servername in the urlname column and leave the other strings as they are does anyone have an idea how ...more >>

Hi, I have a bunch of tables with indexes created on primary keys that are not clustered. When I open "manage indexes" window and try check clustered option I get errors saying Server: Msg 3723, Level 16, State 1 An explisit DROP INDEX is not allowed on index blah. It is being used for PRI...more >>

I'm wondering how to reference the non linked server from a subquery (if it will even work). So if I'm running this query from a sql server against an access linked database. INSERT INTO Divisions(DivisionID,DivisionName,LeagueID) SELECT DivisionID,DivisionName,LeagueID FROM LinksMSAccessD...more >>

Hello all, I have defined the following Rule; CREATE RULE Password_RULE AS dbo.IsMatch @value, '^(?![0-9]{6})[0-9a-zA-Z]{6,15}$' GO EXEC sp_addtype Password, 'VARCHAR(15)', 'NULL' GO EXEC sp_bindrule 'Password_RULE', 'Password' GO but I get the error message 'Incorrect syntax ...more >>