Hi, We have been working with SQL 2000 for about a year now pretty successfully except with dates. This just doesn't seem to be correct, we came for the VFP data world and it worked with dates easier however dates/times only recently became one so that was always a fun calculation. In the...more >>

Hi, This is a tough one, and I am looking for any small clue on where to look. I have a VB.NET app that communicates with databases on SQL Server 2000SP3 via ADO.NET. I am running the app with no problem on my development machine. On my production machine, which is part of a domain, I am g...more >>

All, I would like to use READPAST lock hint in my app, but not lucky. I can not make it work. I have it in a JOIN query. If I put READPAST in WHERE clause, I got an error in JOIN Clause. Any information is great appreciated Souris, ...more >>

Hi folks, I've been following the article at: http://www.sqldev.net/sqldmo/SQL-DMO.NETEventsCS.htm about using SQL-DMO events in C#. I want to subscribe to the following events: CommandSent Event ConnectionBroken Event QueryTimeout Event ServerMessage Event StatusMessage Event an...more >>

I have a group of fields that I want to check for len() = 0. If one of them in that group is blank len()=0 then I will set some output parameter to yes. Then the client will report to the user that they need to complete that section. My solution now is to set a each field to a variable and ...more >>

I have an incomming table that is similar to the following... locationNbr, SalesDate, SalesAmt, TranCode, SalesQty I have to update a table that is formated as such... locationNbr, historyType, Year, D001, D002, D003, D004... --> D365 I am running a join from the incoming table to the ta...more >>

Friends, I am trying to use a cursor inside a stored procedure to add columns to a temp table. Can anyone tell me how to fix the following code so the cursor will feed new column names to the alter table statement? The code fails when I try to use a variable as a column name in the alter...more >>

Hi, What is the execution precedence if there exist more than one update, delete or insert triggers I mean if there are more than one type of trigger such as more than one update trigger. Also, Can we specify a precedence for them? and last If we call an update statement in the same update t...more >>

I'm trying to optimize an ETL batch procedure which takes an extremely long time to run. I was using cursors for most part of the batch script on different Store Procedures. Now I'm trying to remove the cursors and replace them with alternative methods such as using temp tables. I'm really ...more >>

VB.Net / SQL 2K We are developing a VB.Net application and the question is regarding the Login screen We have a table which stores the access-level for each users. Here is our requirement. 1> Need to restrict users with readonly access when connected to the database NOT thru the applic...more >>

Hi NG, MSSQL2k+SP3a+MDAC2.8 If I use ListAvailableSQLServers (SQLDMO) I get after 2 or 3 seconds a Serverlist. But the serverlist is not up to date. Thats means a server which shut down 20 seconds before apperas in the serverlist. A server which started 20 seconds before dont show in t...more >>

I've not done a lot of triggers, so perhaps this is a dumb question, but here goes. BOL says "if update(x)" is for both insert and update triggers, but I don't understand what role it plays in insert triggers. My little experiments suggest that in an insert trigger, "if update(x)" fires if x...more >>

SQL Server 2000 Is there a way to reset an identity field of an empty field back to one without createing a temp table and renaming? TIA Tim Morrison ...more >>

Hi , I have a Net application written in C#. It is storing data in an MSDE data base. I notice that the sqlserver.exe memory consumption keeps increasing when we add , remove or get data from the database. There is one column in one of the tables , that is of type image. I store th...more >>

Does anyone know how to view the contents of a temp table when using the integrated SQL debugging of Query Analyzer (or Visual Studio.NET)? We've got our server configured to be able to step through the stored procedures, and can view local variables (declared with the @ symbol), but we do not...more >>

Hi all - I need to create a temp table containing all Items with activity for the last five years. These records will be coming from a few different tables. So I have the following . . . CREATE TABLE #tt_item (item nvarchar(30) PRIMARY KEY) INSERT INTO #tt_item SELECT dbo.item.item FR...more >>

Hello All! I would like to thank everyone for all the help, but.. (there is always a but) i have another question. I would like in my select a column displaying if the current line is a Company or a Person, something like that: SELECT *, "(Company or Person) As Type" FROM Client LEFT J...more >>

Hi, What i would like to do is to use script to do the maintenance plan instead of using the maintenance plan wizard. I create the following backup scipts, my question is how am i able to write the result to a log file just like what maintenance plan does? DECLARE @DBName NVARCHAR(100),...more >>

Hello, With sp_changedbowner I can set the db owner from the current DB. Is there a way to do that 'from' another DB; e.g. the current DB is master and I want to set the owner for MyDB. (SQL Server 7) Thanks in advance Jan -- ----------------------------------------------------------...more >>

if i have the following data: Name ----------------------------- SILFL-CFL-ELI24-220-GR SILFL-CFL-ELI24-220-WH SILFL-CFL-TOP-GLASS SILFL-CFL-TOP-LOUVER SILFL-CFL-TOP218-WH SILFL-CFL-TOP226-WH SILFL-FLU-EST18-220-WH SILFL-FLU-EST36-220-WH SILFL-FLU-FLA08-220-WH SILFL-FLU-FLA13-220-WH S...more >>

Question about this code: Is this the proper way to word this query or should the order of the statements be different? (i'm trying to make my code as standard as possible. select u.unvName, i.idxhstIdxID, i.idxhstDate, i.idxhstBuy, i.idxhstSell, i.idxhstPosDate, i.idxhstPosXO, i.id...more >>

We are getting ready to setup the gov's death file on a sql server. I would like thoughts on the table design to optimize space. This is the data spec's https://dmf.ntis.gov/recordlayout.pdf The master file doesn't have the first column. So here is where I was headed for the main table. ...more >>

Hi , I would like to modify the current trigger, but I could not find the script for it. How do I find a script for TriggerX? Thanks, Culam...more >>

Hi, SQL Server 2000 allows the following three types of database files: 1. Primary data files: extension MDF.=20 2. Secondary data files: extension NDF.=20 3. Log Files: extension LDF. But the Log files (.LDF files) are BINARY files and also, there is no = header defined for this files.=2...more >>

Hi In my asp.net app I collect a unknown number of employee ids (e.g. 1,2,4,6,8,21,43) as an int array (can be arrange to be a string array if that helps). I would then like to send these ids as an parameter to an local variable in a stored procedure and then use this variable in a ".. ...more >>

Hi, I have a table with data that every 2 rows have same data for most of cloumns (11), only 4 columns have different data. I would like to query this table so that such rows appear as single row in the result ( I don't need the 4 cloumns), so I used that 11 columns in my group by clause. A...more >>

I have the following table: ------------- CREATE TABLE [dbo].[import_operation] ( [id] INT IDENTITY (1, 1) NOT NULL PRIMARY KEY ,[unit_id] INT NOT NULL ,[filename] NVARCHAR(1024) NOT NULL ,[file_group_code] VARCHAR(2) NOT NULL ,[file_grou...more >>

Hi We have a table structure for storing away Hires in a SQL Server database. Related to this table is another table that stores events/logs that have occurred on the Hires table. I.e. Record Created, Modified, Price Changed, etc. We are trying to do a query that will return the first log for...more >>

The following is an issue using ado.net in an asp.net page using SQL Server.... I have a Statement that when executed from my asp.net dataadapter takes about 24,000 reads according to SQL Profiler. I think the read count is accurate as it grids a long time. The same statement when execu...more >>

How do I write a sql to select all last names with an apostophe? My result set should look like this - o'brien o'neil x'mas the following does NOT work select LastName from Clients where LastName like '%'%' I know in Sybase you can say '%\'%' - but it doesn't work with MS SQL - Is t...more >>

Hi, My table has a column [account type], some rows have null value, when I query it to eliminate some account type like accout != 'A', all the rows with null account won't showup either. I would like all the accounts other than 'A' show. How can I write it? Thanks...more >>

Hi group Some tables of my database have a column auto-increment, however I have a doubt about the maximum capacity of rows that you/they can have those tables. Does it exist some it limits of rows with fields increased in SQL-Server? They are int Greetings Mario ...more >>

I have some code for a duntion that looks like the following alter FUNCTION fnLevelTable_1 (@sourceID SMALLINT, @sectorID VARCHAR(4), @manufacturerID VARCHAR(4), @rangeID VARCHAR(4), @bodyID VARCHAR(4), @tra...more >>

I would like to put the sql statement listed below into a SQL Server Enterprise Management job. If the counterValue = 0 then Abort the job, send error message "CounterValue should NOT be ZERO" Please help me complete this task. Thanks, select CounterValue from counterdata where ...more >>

I need to reposition a column on a large table but everytime the EM will time out. Am I not waiting long enough and if so how long do I have to wait. Is there a better way to go about this ?...more >>

I have a field that has HTML tags in it. I need to remove these tags in a select statement to populate a dropdown. Here is the select and data: SELECT QuestionID, Question FROM FormCreator_Questions WHERE (QuestionOptionType = 'radio') OR (QuestionOptionT...more >>

I have to "break out" several fields in one table to create another, with a foreign key placed in the first table to represent the new table. How would I do that, other than manually entering in the foreign key id, not practical. For instance, table 1 has client information along with amou...more >>

Hello, I am just new to T-SQL programming on SQL server 2000. I would like to ask you if it is possible (like in VBA for example) to save the result of query (1 row, but multi columns) to some "array" or "object" variable in order to reference the concrete components (fields) of this variable...more >>

How does the Function Split look like when I need to pick up a word in a column, ie. second, third, or fouth word. Those words are separated mostly by spaces, but sometimes there are some ',', '/' etc in between -- Message posted via http://www.sqlmonster.com...more >>

Hi, I have a table with ID int, Name varchar, Age int how could i make an stored procedure to get the three old persons from database? --- Thanks Regards. Josema...more >>

I am new in using SQL. My problem is as follow: Table-A has two date columns. They are date1 and date2. Date1 Date2 1/1/2005 1/10/2005 1/3/2005 1/25/2005 etc...... Table-B has just one date column. Date 12/31/2004 1/1/2005 1/2/2005 ...more >>

Hi all, How can i pass a parameter value to UDF. select * from UDF(@Paramvalue).can i do like this. Thanks Krish...more >>

We have a requirement to change a user defined type from char(4) to vchar(4). I was wondering whether there's a easier(quickest) method to do this as opposed to: - Drop dependent storedprocs and views - rename type - add type with new def - reattach table columns - drop old type - recreate st...more >>

Hello, I am having a problem where I'm trying to call a store procedure from a function, is it possible ? J...more >>

Hi, I have a following table: Columns: PartName SerialNumber IN_OUT Values: Part1 1233445 IN Part1 1233446 OUT I want to query this table to obtain following result: Columns: PartName_IN SerialNumber_IN PartName_OUT SerialNumber_OUT Values: Part1 1233445 ...more >>

I'm attempting to write a query that pulls data from two tables in a really complex way, and I can't figure a way around it. Because of the architecture in use, it has to be done in a single query, without using a stored procedure. This is a simplified version of what I'm doing: Let's say I ha...more >>

Hi, Can you tell me if something like this is doable? Assume I have 2 tables as follows: Member(ID, Name, Gender) Children(ID, FatherID, MotherID) where ID, FatherID, MotherID are integers, Name is a character. Assuming that I have the member's ID to start with, would it be possible to ...more >>

I have a table as follows: SizeID | Description 1 Extra Small 2 Small 3 Medium 4 Large 5 Extra Large And then a Product_Size relationship table: ProductID | SizeID 1000 1 1001 3 1001 ...more >>