Hello, I have a sproc which I use to search through my member table in which visitors can page through the results. My sproc returns only the members I need for the current page, along with the total number of members that match the query using @@ROWCOUNT. In my sproc I use a temp table to...more >>

If you are using my lock-monitoring procedure aba_lockinfo, there is now a new version available at http://www.sommarskog.se/sqlutil/aba_lockinfo.html. I recommend that you replace your existing version with this one. Functionally, there are only minor difference, but the older version did no...more >>

Hi all, I am trying to retrieve rows from a table containing 500,000 rows. Using pure SQL, or a stored procedure, how can I "page load" this data, to retrieve+-30 rows or so at a time, and on demand of a button click. Using ADO.net. In other words, what is the method I would use to send the foll...more >>

Hi all I need an efficient SQL Query I have 3 tables. 1. Students StudentID StudentName 2. Course CourseID CourseName 3. StudetMArks StudentID CourseName Marks Stuent and courses are many to many. I need to find out the studentnames who got the highest marks in...more >>

Any ideas on a better way to write this so that i don't get the following error: Exception Details: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'SELECT a_Users_Portfolios_Symbol.Portfolio_Symbol FROM a_Users_Portfolios INNER JOIN a_Users_Portfolios_Symbol ON a_Us...more >>

Hi all Is there any web site having some SQL Query problems and answers, so that I want to learn how to write the SQL statement efficiently. Thanks in advance GS. ...more >>

Is it possible to have a script to parse or strip out elements in a text file before using the import function in sql all in one tasking? Thanks, Geno ...more >>

Does anyone have any issues with xp_cmdshell with SQL Server 2000 on Windows 2000 Server? The vb program runs but does not end properly. In other words, the task manager shows the vb program hanging there. The same program that shells out of SQL Server 7 on Windows NT has no problem. It'...more >>

Hi, How do I compare two dates without matching the time. Example: Mydate TableDate in table (DateTime) 2/24/2004 2004-02-18 18:32:41.877 My below where statement does not work Where MyDate = TableDate Thanks in Advance, Culam...more >>

What would be more efficient, implementing complex code to manipulate data on a stored procedure or let a COM server do the manipulation and calculations through C++ code. We have an application that do all processing of data on SP and then give results back to a COM server. My concern is: is t...more >>

Greetings, I have a situation where I have two tables that both have triggers to update an additional two history tables. For example, TableA TableA_History (TableA has a trigger which modifies TableA_History) TableB TableB_History (TableB has a trigger which modifies TableB_History)...more >>

I have a table that includes a primary key, a timestamp, a username, and an IP address. I use it to log access to a user area of a website, adding a new entry every time someone logs in. This is not a problem. Now I want to be able to tell people who are returning after logging in before whe...more >>

Is there a simple way to visually identify when the frontend app is linked to the development DB as opposed to the production DB? I have MS Access as the FE and would like some kind of visual clue as a reminder of which DB I'm linked to. Pretty newbie question, I know Thanks so much! SMK...more >>

I have a huge table in a server, I want to bcp all the data to a DAT file (only data), then zip it up and move to other sql server. but I don't know how to do bcp. also, how do i do bcp out data from a DAT file to Table? ...more >>

Hi How do I represent unicode string literals when SET QUOTED_IDENTIFIER is OFF, and I am using double quotes to represent string literals I tried N"somerandomstring" but it doesn't work. Seems that the 'N' prefix only works on single-quoted string literals Any ideas Andry...more >>

Dear Anyone, I am a member of Microsoft's ISV program. I have a server running Server 2000 and a laptop as my workstation. The server is running SQL Server 2000. I want to install a desktop version of SQL Server on my laptop to do demos of my projects. Do I install from SQL Server D...more >>

Hi Here is the problem. I need to go down each row of Table1 and compare the prev row's data with current row's data for each SUB, if the current row's data is different than the previous row's data for this SUB, then store this SUB current row's data in the new table, called NewTable. For Examp...more >>

Hello all, I saw some sites have periodic counters for each articles. For example, "this article has been read 134 times in the recent 7 days, 1049 times this month." I just wonder how this is implemented in SQL Server? Any ideas? ...more >>

Hi! I want to select all records with a time between 16 and 20 every day during January. Like this: select * from report r where r.acc_date >= '2004-01-01' and r.acc_date < '2004-02-01' and DATEPART(hh,r.acc_date) >= 16 and DATEPART(hh,r.acc_date) < 20 Suppose I want to select all reco...more >>

I've got an Win2K Server running SQL Server 2000, and an Win2K3 Server running SQL Server 2000 SP3 (have to install SP3 on Win2K3 as far as i know). Anyway, my application select some data from a table using LEFT OUTER JOIN to another table. Something like this: Select a.1, a.2, b.1, b.2 from...more >>

Hi, I'm trying to write an SP to perform a dynamic search but am having a bit of trouble. In our app, you are able to select multiple selections from a list box such as City. I was thinking of using a WHILE loop to keeping adding on to the WHERE clause but that doesn't seem to work. Here's my ...more >>

Hi Is there anyway I can restrict the number of connections to a SQL Server database ? How can we do that.? Also what is the best way to find out number of current connections to the database using a query ? Thanks ...more >>

Hi All, I am writting an real estate website for a company. I need some advices on how should I store fields like city, and state for addresses. Originally, I was thinking to store the city and state as numeric fields (in the main Houses table) and having extra one table for citie...more >>

I have an sp that inserts a record from one table to another based on a parameter that works fine. How can I use a field on an MSAccess form to be the parameter? I can get the sp to run from the form but only to prompt me with the parameter required I want this to be the current record and to do...more >>

Hi, Thanks in advance to anyone who looks at thia. My scenario is simple. I have a new client who I will transfer from one WSP to another WSP. My question is how best to transfer their SQL database? I have Enterprise access to the new host, but not to the old host. The old host is freaky...more >>

Hi, Can you tell me how to get rid off all the empty space in my osql output file (text file)? It always start with a blank space and at the end of each line, there is another 500 blank spaces. I looked at BOL and could not find anything about blank space corrections. The result I get from...more >>

SQL 2000 sp3 (8.00.760), XPPro exec xp_sendmail=20 @recipients =3D 'kevin3nf@mydomain.com', @Message =3D 'test' When executed, I get: Server: Msg 17985, Level 16, State 1, Line 0 xp_sendmail: Procedure expects parameter @user, which was not supplied According to BOL, there is no @user...more >>

I store a large amount of hierarchal BOM data using a nested set table similar to the one below. It's used for a job shop, so each BOM tree has a top level item which correlates to a particular job. This 'job' exists in a table elsewhere with a corresponding identity JID. Most queries accessin...more >>

Hi everybody, I have two tables to get data from: 1. Users 1. UsersDealer I want a listings of all the users and if the user is in users dealer, a listing of the dealer as well. How can this be done because an inner join will only list all the users that is in the users dealer but i ...more >>

SQL Server 2000 I want to work out the 25th working day for a table of calendar days. Below is code I have adapted from: http://www.devx.com/gethelpon/10MinuteSolution/16528 to create a table of calendar days, and a two columns indicating if a day is a weekend or holiday. ===============...more >>

I have a table of 10 million rows as follows: CustomerNumber, Address, StoreNumber, Spending If I use COUNT (DISTINCT Customernumber) I get 3.2 million rows. If I use SELECT DISTINCT CustomerNumber, Address, I get 3.3 million rows This is because addresses changed, original data no well maintain...more >>

Hi I asked this yesterday but didn't get any response. Please help me. May be I didn't word it right and I apologize for that. What I am trying to do couldn't be the first time. I will try to ask in parts: I have a table with minutely data i.e. readings for every minute. Create Table TagM...more >>

I have a .NET webform with a multiple select dropdownlist control. Any idea how to create a stored procedure that could utilize the one/many parameters returned from the dropdown list? THE T-SQL Select WHERE search condition clause would have to read something like this: WHERE fieldA = ddParam...more >>

Hi, I tried to run the following to insert a TAB and RETURN to my select and none of them seem to work. select 'hello!' + CHAR(9) + 'my name is' + CHAR(13) + suser_sname() My result is a one line without any tabs or returns. I ran this code from BOL and it works... Can you tell me what i...more >>

sorry if this is a double post, it seemed like my first one got marked as invalid or something... Might be out to lunch, but I can't figure why this is being ordered the way it is: SELECT '1' as t UNION ALL SELECT 'A' as t union all SELECT '['as t ORDER BY t I'd expect it to be 1, A,...more >>

Hi all! i have a table in which i am saving serial nos i.e 1 1.1 1.2 1.3 .. .. 1.10 I have created a field with decimal datatype as serialnos decimal(18,2) ( with precision 2) I have put primary key on it , problem is when i put data into table i.e 1.1 it is being saved as 1.10...more >>

Hi, I use C# to fill a data field of "float" data type in SQL 2000 server. When I use Double.NaN value, there seems to be a problem in "Query Analyzer". I can no longer select the row, an error message: "Numeric value out of range". However, in Enterprise Manager, I can still see the row with ...more >>

Hi all, I've seen these type of problems posted before, but never understood the answers. I have a SP that accepts 3 variables. When the SP is executed, it takes over 5 mins to run. When I execute the exact same SQL from a query window and apply some GO's in it, it executes in 30 seconds. ...more >>

Hi, I am thinking of creating an identity column to use it as primary key in my table. But the problem is that once I delete some records, the SQL Server would not reuse the IDs of the deleted records. So there will be holes in the sequence. I know many people use Identity columns to generat...more >>

Hello, Is there a way to use a while loop to update a table with information from table being looped through for instance while (select score from source) < 250 begin insert different_table values (source.column(1), source.column(2)) end I need to INSERT values from the loop ...more >>

can alter table be used to change a column type?...more >>

Hi, How do I select people who ONLY have apples from the following table? So the the SELECT statement should return 234. UserID -- Fruit 123 -- Apple 123 -- Banana 234 -- Apple 345 -- Kiwi 345 - Banana 345 - Strawberry 456 - Apple 456 - Kiwi ...more >>

Hi, Can you help me write a sql query to show the current user and the current database name? For example, select 'the current user: "' + @currentuser + '" is connected to the "' + @databasename + '" database.' result: the current user "sa" is connected to the "northwind" database. ...more >>

Hi, I want to get a list of all values in particular column (each value once only in resulting set). I can use: select distinct my_column from my_table or select my_column from my_table group by my_column Is there any difference in efficiency of both methods? Regards, Miroo ...more >>

Maybe I'm out to lunch, but this doesn't seem right: SELECT '1' as t UNION ALL SELECT 'A' as t UNION ALL SELECT '['as t ORDER BY t returns [, 1, A instead of 1, A, [ which is what I'd expect. Why does [ get pushed to the top? it's ascii value is 91, greater than both that...more >>

I use the update method on numerous tables, but I get this error "Query cannot be updated because the from clause is not a single simple table". The is nothing specail about this table and in fact it's preety simple. use master create table tblUserLog (Record_No int identity primary key,...more >>

I'm having some trouble with an Insert statement. Here are my table definifions: table: test_id rowId -> INT - NOT NULL name -> CHAR(10) - NOT NULL date -> DATETIME - NOT NULL uid -> UNIQUEIDENTIFIER - NOT NULL table: test_id_source name -> CHAR(10) I populated the test_id_...more >>

Please refer below stored procedure! If two clients call this SP at the same time, then ProcessByPC field didn't get update by the first client before the second do the select PKID. This will bring in an issue that 2 client select the same record!! How to prevent this happen? Will SQ...more >>

I wanted to get an overview of my tables, used to do a diagram and print it out. But I really got tired with finding the proper positions and scaling with that. I got close to wonder what the person who implemented the print function for diagrams did think while doing this. So I was wonderi...more >>

How can I improve this code please? It sometimes produces this error: "Object reference not set to an instance of an object" When I do this: strSQL = "UPDATE TBL_Items SET" & _ " item_itemnumber = " & PrepareStr(Label6.Text) & _ " ,item_itemcurrentbidp...more >>

hi all, i store items in a table (#itemid(int, autonumber), itemname, itemprice) how can i get 5 random items in this table please help me! thanks ...more >>

When I am opening the application ,I am getting this error "runtime error380 invalid proprty value" what can be wrong? How will be I able to fix it?Does this has to do with quoted identifier ? Thanks...more >>

Hello, I have birth date in a table and need to compute someone's age. "DateDiff" with GetDate() along with the "yy" option doesn't help because it doesn't take into account the day of the year. I can't use "dy" because I just get a raw number of days back and can't divide that by "365" becaus...more >>

Hi, I want to display a date in the following format "yyyy-mm- dd". I don't want any of the trailling time information (ie. h/m/s). TIA, Eric...more >>

Hello All I have a cost center hierarchy which is like the following: ROOT | |-NODEA | | | |-NODEB | |-NODEC | |-NODED | |-NODEF | |-NODEG | |-NODEH |-NODEI...more >>

Hey all I have a number of stored procedures in SQL and am using an ASP to pass in parameters and display the results of the stored procedures in a html table. I need to perform actions like, SUM fields and use ORDER BY commands as well as only displaying certain fields off a results set etc. Is ...more >>

H I currently have a complex .sql script that produces several result sets detailing the age and status of work in our system I can Set up Query analyser to sent the results to a CSV file that I can then email to my managers I want to automate this process I initially thought That I could set ...more >>

H with exceuting a stored procedure for the first time 2 records are inserted to Master..syscacheobjects but if I execute a stored procedure with ExecProc metod of ADO StoredProcedure in Delphi 5 it inserts 4 records in syscacheobjects.What's the reason thanks...more >>

Hello, I have this select from users where userid = 3 select number from usernumber where userid = 5 how can I dynamically merge n columns to one table like this? state1 state2 state3 car1 3 38 57 car2 32 3 1 car3 3 8 ...more >>

Hello, I have a stored procedure like this Alter Procedure dbo.Test ( @id int ) AS select * from users where userid in @id How can I do it like this exec test '1,2,3,4' so the procedure would do this: Select * from users where userid in (1,2,3,4) Thanks for any help...more >>

Hi, I get always the error "Invalid Class string launchimg a DTS" when i try to launch a DTS in SQL server from a VB .Net program. tha DTS is made with: - a file text (source) - a connction to a DB in the SQL Server The code I use is Dim package As DTS.Package2Class ...more >>