Hello, I am having problem with a query, the query returns more than 6000 rows and it takes a LONG time since the sql server is not local, so I would like to use paging, but I would need to query for example, the first 100 rows, and then from 101 to 200, 201 t o300 and so on.... is there a...more >>

I have 2 Stored Procedures, "Parent" and "Child". The Parent SP calls the Child SP, but also the Child may be called directly. If the Child returns an error (which occurs whenever it is passed a value of 2), I want all updates to be rolled out. I have a couple of working version of these ...more >>

Is there a way to do something like an enum for a column? I have a column that should contain one of a fixed set of values. The set of values won't change and there is only one column in one table that uses this set of values. So I was thinking that there wasn't much point in creating a new ...more >>

Hi friends ! We are in the process of annual evaluation of our database administrators and developers for SQL Server. Their jobs cannot be evaluated as of accountants and our sales Team because their projects are always of on going nature. What is the best way for their evaluation ? Plea...more >>

I have two tables, say Accounts and Customers that share a foreign key, say CustomerId. I need a trigger so that when the Account column of the Accounts table is updated, the corresponding Customer column of the Customers table is updated as well. The logic would look something like thi...more >>

Hi, I'm working on a database having default constraint names created by SQL Server 2000. Is it possible to find out which columns the check works on or even to see the check code? TIA, Axel Dahmen ...more >>

UPDATE tmp_coa set cDebit = (select SUM(cDebit) FROM tmp_glinfo WHERE acctcode = tmp_coa.acctcode GROUP BY acctcode) I try the above statment, It run sucessfully, however, for some non-exist acctcode in tmp_glinfo, I will store NULL value in tmp_coa table, I want to store zero instead, Can ...more >>

Does anyone know how to run reports (.rdl files) in VB.NET? I am trying to run multiple reports in batch and have only the last one display. ...more >>

I need to find all the tables in a database that are empty (have nothing in them). I know how to do it individually but can I do a mass search for tables without data. Thanks, ...more >>

I'm trying to get multiple columns back from a query, one of which I'd like to be distinct. Here's what I am trying to get: drop table foo create table foo ( id int, name varchar(20), size int ) insert foo values (1, 'A', 1 ) insert foo values (2, 'B', 1 ) insert foo values (3, '...more >>

I have two SQLserver 2000 systems (Test1 and Test2), and each system has a TestDB database on it. Each TestDB database has a Ticket table, and each Ticket table has a TicketValidated column and a ValidationString column. The Test2 TestDB is used for generating reports and the Test1 TestDB is ...more >>

Is there only one instance of @@FETCH_STATUS in T-SQL procs or triggers? If I have two nested loops and I am using @@FETCH_STATUS to see when I am at the end of the rowset.....will the internal loop screw things up for the external loop? Do I need to save @@FETCH_STATUS off to another variable ...more >>

Hello! I am trying to put together a select query using a subselect statement to return a value from a user defined function that returns a table: SELECT N.*, (SELECT NodeValue FROM dbo.fnQuotePath(N.NodeID) WHERE NodeTypeID = 1) AS Thingy FROM ...more >>

I need to search a field named PartNumber using a LIKE '%xxx%' to find any matching part. However, I need it to ignore a space or dash (-). For example, if I enter LIKE '%123 45%' or LIKE '%123-45%' or LIKE '%12345%' I would get the same records. Is this possible? David ...more >>

select DATEDIFF(second, min (time_stamp), max(time_stamp)) AS responsetime FROM table1. Now, on this table1, time_stamp column is defined in datetime format. Case1 When the min(time_stamp) is 2/17/2005 8:34:55AM, and the max(time_stamp) is 2/17/2005 8:35:12AM ... the responsetime is show...more >>

I have a backup file: tom.bak that is backed up from another Sql Server. I want to add it to my other Sql Server. Do I need to do a restore? Do I need to Create the database first? What about Master - is there something I have to do with that? I am in the process of converting my Eval...more >>

I am very new to SQL Server and not sure what approach is best in designing the schema for the following situation. I have to put about 10 million records in the database. These records have exactly the same structure, 10 attributes, one of which, "Eligibility", has only 2 allowed val...more >>

Hello, I need to select one row from a group by query which contains a max value: create table t(datefld datetime, metric char(1), val int) insert into t(datefld, metric, val) values ('1/17/2005', 'A', 3) insert into t(datefld, metric, val) values ('1/17/2005', 'B', 2) insert into t(dat...more >>

Hi NG, I noticed something strange today. I did an EXECUTE sp_help MyTable and found that columns of type VARCHAR were exactly twice as long as they had been when they were Access columns. I used the Upsize wizard to convert the tables to SQL MSDE and then at a later date migrated the MSD...more >>

I'm successfully using the 'MSScriptControl.ScriptControl' from within a sql stored procedure. [ I use this object to evaluate dynamically generated mathematical expressions (stored in db tables): A=5 B=3 C = 3 * (A+ B) -- so far so good ] To enhance security and prevent that potential mem...more >>

We are currently writing software that connects to Sql server 2000. We use a connection string that provides the name of the server machine that sql-server resides on. What do we do when there is more than one sql-server on that machine. For example, there are instances A,B, and C on the machi...more >>

If i am running multiple osql, how can I append to a common output file such as osql -S ServerA -i q1.txt -o out.txt osql -S ServerB -i q1.txt -o out.txt Havent tried it but wanted to know of the second osql command would overwrite the first out.txt file and if it does, how can I append the...more >>

I have a table that holds passwords, and the column is a varchar data type. Is there a way to mask the field so if someone does a select * from it they cant read the passwords? TIA, Joe ...more >>

Hi everyone. So, after much coercion, I finally have my first extended stored procedure compiled and (apparently) installed on the server. It's just the xp_hello example off the MS website. I have followed these instructions exactly: http://msdn.microsoft.com/library/default.asp?url=/lib...more >>

Hi Is there an easy way to add table from one database into another database on the same sql server 2000? e.g DB1 ......Table1, Table2.... add Table1, Table2... into DB2 Thank you. Shailesh ...more >>

select showed_up = case client_regist when (isnull(s.client_regist,'')='') and (s.clin_appt_status like '%cancel%') then 'cancel' ELSE 'Unknown'END,count(p.lognum) as 'MonthlyNoshow' from t_client p inner join t_schedule s on p.lognum = s.log_no where s.Create_dt between '11/01/04' a...more >>

Hi fellows, I would need to know the contains the "password" field but data appears in hexa. Is there anyone here be able to show me how can I obtain the value? select password,* from master.dbo.sysxlogins where name = 'sa' order by xdate1 desc Thanks a lot, ...more >>

How do i find out whether the query is picking the data and query plan from the cache....more >>

Hi all. I do have a "simple" problem on my hand. I have one table containing PriceGroups. It could be 1 or many PriceGroups pr. produkt. When I list the produkts data in one record I want the PriceGroup description to be columns as well with the corresponding price. A kind of dynamic Pivot...more >>

How would i join these 2 queries: QUERY 1: SELECT DATEPART(yyyy, PERIODS.PERIOD_DATE) AS dteYear, DATEPART(mm, PERIODS.PERIOD_DATE) AS dteMonth, SUM(FCST.FCST_QTY) AS FCST FROM FCST INNER JOIN PERIODS ON FCST.FCST_PERIODID = PERIODS.PERIOD_ID GROUP BY DATE...more >>

1. If ADO .Net supports disconnected architecture why do we have to close a connection after we open it? Isn't ADO .Net supposed to do that automatically since it supports the disconntected architecture? 2. Why is it that i don't have to open a connection (by writing e.g. Conn.Open) afte...more >>

hi I using C# to write a ASP.NET page and I need to compare a string variable to a Char field in the SQL table in SQL Server 2000 how can I write a SQL statement to do that?\ I try to use "SELECT Name from Employee Where pass="+password it shows me an "Invalid column name error" How ca...more >>

Sorry if that title is confusing. Say I have a view on a table. And then say I build a view using that view. Shouldn't I, theoretically, always be able to decompose a view built on another view into a single view built on the underlying table(s)? I know that SQL Server has to do this ev...more >>

Is there a tool in SQL Server that allows a visual development of SQL queries, similiar to that used in MS Access? ...more >>

Hopefully someone can help. Using vbscript and ADO, I can call a very simple stored procedure that returns a recordset w/o any problems. However, when I call a stored procedure that stuffs records into another table using a SQL string built and the Exec command and then select the records fr...more >>

I've seen this linked server syntax many times in the group. but it didn't work for me, maybe someone can explain it better for me? sp_addlinkedserver 'ADSI', 'Active Directory Service Interfaces', 'ADSDSOObject', 'adsdatasource' i know that: * " ADSI" is the server argument, which will...more >>

Hi all, I've seen some articles on the net recently about this forth coming service pack, I just wondered if anyone knew whether it would also contain all of the previous updates from SP3 aswell (ie, cumulative) - the reason I ask is that we have a clustered SQL server here running services...more >>

Hi all of us, I'm trying to retrieve a table which has been deleted without bearing in mind... Is possible to restore .LDF file (the most recent backup) and to have back that table Any help will be welcomed. Thanks a lot, ...more >>

Hi, Below is my stored procedure, for some reason it is not bringing back the max contract start date although I have sopecified this. Any ideas would be greatly appreciated. Will also include some data to show what I am trying to do. SELECT TOP 100 PERCENT dbo.tbl_referral_name.rn_id...more >>

Here is a puzzle. Example Create Table #Temp1(lngDataID int, chrString1 varchar(80)) INSERT #TEMP1 SELECT 1,'ABCDDCBAABCDDCBABCDDCCDDCBAABCDDCBAABCDDCBABCDDCCDDCBABCDDCBAABCDDCBABCDDCCDDCBA' UNION SELECT 2,'ABCDDCBAAACDDCBABCDDDCDDCBAAB DDCBABBCDDCBABCDACCDDCB...more >>

Hello everyone, I need to: 1. Get data from a table row 2. Lock a row for delete 3. Delete it The problem is that the code that I use locks the whole table, not just one row. Does anyone know how to limit the lock to one row for one table? Thank you, Helen P...more >>

I'm trying to do it , but i see only '??' symbols instead my data in french. When i retrieve it in .NET application i have '??' symbols in datagrid too. Please help me, thanks...more >>

I am working with SQL Server 7 on win2k server. There was a vb6 ado ODBC application written accessing this SQL Server 7 db. Unfortunately, through a series of workstation upgrades, the source code was lost. Recently, the performance of table inserts degraded significantly. I am trying f...more >>

I have data in a database thus: DiscountGroup Rep Month1 Month2 Month3 Month4 Month5 Month6 Month7 Month8 Month9 Month10 Month11 Month12 Yea All REP01 120260.7 120260.7 126199.5 126199.5 120260.7 133623 126199.5 133623 133623 126199.5 133623 84627.9 NUL All REP03 124212.123 124212.123 130346.055...more >>

I am refactoring stored procedures that use dynamic sql. The reason the store procedures use dynamic sql is because the data that is need comes from another MS SQL database that resides on the same server instance. The following is a code example from a stored procedure: declare @theDatab...more >>

In MS SQL 2000, I would like to obtain the timezone offset from UTC for a given date. For today's date, I can do DATEDIFF(ss,GETDATE(),GETUTCDATE()). However, the offset for a future date may not be the same as today because some countries go in Daylight Saving mode. Can you suggest a way to obt...more >>

I am trying to search for a word such as "e-business" using the Indexing Service Query object (CissoQuery). Now what I would like to do is to be able to search for e-bus and return results of variations of this term, e.g. e-business, e-busi. So effectively, I would like to a do a wildcard se...more >>

imagine a DB with this table tblCalendar constructed like this (http://www.aspfaq.com/show.asp?id=2519) and a column like this dateTimepK smalldateTime imagine a SP with two params @MinDate smallDateTime @MaxDate smallDateTime imagine a webform with a calendar tool that passes tw...more >>

What is the command in SQL Server that allows you to output query results to a text file. I know that you can save the results to text file from query analyzer but I need a command to enter, As want to write each different query into different text file. thanks...more >>

I ran some delete statements in Query Analyzer and I found out it was the wrong database. Can you rollback the delete statements and recover the data from the transaction logs????...more >>

Hi. If I have two tables connected with foreign key, is there any index build on "slave" table on column which contains foreign key for better searching? And if not, is it better to create index on this column?...more >>

Hi there I'm trying to insert data into a table from a Excel file. After going through some past posts I've come up with this: INSERT INTO tblLisImport2 Select Reference, PlannedEnd, CompletionStatus, Outcome, ProvSpecLData1, ProvSpecLData2 from OpenRowSet('Microsoft.Jet.OLEDB.4.0','Excel ...more >>

Hi, I have an appointments table where I would like to return the count of all appointments for each day. this is what i have... but how can I return the DAY date for each count? CREATE PROCEDURE spGetAllAppointments_Day ( @UserID int ) as select COUNT(*) as AppCount from T...more >>

Hi This may be a simple one. But anyway.... I would like to catch the result of a select statement executet as synamic sql in a variable. A simple example of what I would like to do would look like this: declare @result int, @sql_string varchar(300) select @sql_string = 'select cou...more >>

I have sql 2000. Last night I had a backup problem where the Log would not restore becasue it was the wrong one. This left my DB greyed out. In my great wisdom I did a not so wise thing and detached the DB and tried to re-attach. No luck. Next thing I know I cannot connect to the server becasue ...more >>

Im probably doing something stupid, but its a strange request, so it requires a strange solution,so here its the problem: I have a table holding monthly data, that has a row for each change of status of a certain request. that request can be identified through the composite key: Type, Sit, Co...more >>

SQL server 2000: I have three tables - I'll call them A, B and C for simplicity. The join I wanted to achieve can be illustrated by the following psuedo code (note the parentheses): - select * from A left-outer-joined with (B inner-joined with C) I originally wrote it like this:- ...more >>

Why does the following not return records when the maximum counts_dt is null? set ansi_nulls off SELECT * FROM COUNTS WHERE counts.counts_dt = isnull((SELECT distinct MAX(c2.counts_dt) FROM counts c2 WHERE c2.property = counts.property),null) Is there another way of achieving this when ...more >>

SyetwemHi There. I'd like to alter a DEFAULT I have set up on a database table. Unfortunately, I don't know the name of the default as it was created when the table was created without specifying a name. Q: How do I find the name of a column DEFAULT (from INFORMATION_SCHEMA or System tab...more >>

Evening, I am trying to sort this example data. 17 Dec 2004 19:30:30:000 17 Dec 2004 19:30:30:000 17 Dec 2004 19:30:30:000 7 Jan 2005 19:30:30:000 7 Jan 2005 19:30:30:000 7 Jan 2005 19:30:30:000 7 Jan 2005 19:30:30:000 16 Dec 2004 19:30:30:000 16 Dec 2004 19:30:30:000 16 Dec 2004 19:3...more >>

I currently have SQL 2000 Standard on a box with a Single 2.4GHz Xeon HT processor. Would my performance be better served by adding the 2nd CPU or upgrading the current CPU to a 3.2GHz? Any thoughts are welcome, Scott ...more >>

Hi, I have created a stored procedure where I want to dynamically pass starting month, starting year, ending month and ending year values. My result should be one row for each month for the time frame selected. eg. I am using this right now: WHERE (DATEPART([Year], ODate) BETWEEN 2003 A...more >>