I want to write a T-SQL query which returns not only the maximum value, but the number of rows having the maximum value. There must be a better way than what I have come up with --wrong way select LibraryBranchId, max(daysCheckedOut), count(daysCheckedOut) from books group by LibraryBranchId LibraryBranchId Expr1 Expr2 -...
I have a query that returns data in the following form attribute value --------- ---------- petid 1000 name buttercup species cat age 10 owner Bob Dole Basically I want to go through every row and return the fields name and species in a single string, so here the result wou...
We have a 3rd-party app that wants to call a stored proc using SQL 2005-style "schema" syntax: Customer.InsertNewOrder This db is still on SQL 2000, though, so we're trying to make it "look" like a SQL 2005 schema, and still work right (with minimum necessary permissions). So we: Created a user called "Customer" Created the stored p...
I have inherited a database where there are clustered indexes and additional duplicate indexes for each of the clustered index. i.e IX_PrimaryKey is a clustered index on the column ID. IX_ID is a non clustered index on the column ID. I want to clean up these duplicate non clustered indexes and I wanted to check to see if anyone could t...
Dear Friends.. Below is my SQL Query which takes more than 10 minutes and still running.... select DISTINCT Auditdata.ID,ns.ProviderMaster_ID as CDRComment from Auditdata AuditData inner join AuditMaster am on am.ID=AuditData.AuditMaster_ID inner join HomeCircleMaster hcm on hcm.Ori_CircleMaster_ID=am.CircleMaster_ID ...
I use MS SQL Server 2000 SP4 and I have this part of script, that checks for existing database: IF EXISTS (SELECT * FROM sysdatabases WHERE name='MY_DBNAME') BEGIN PRINT 'Using the existing database MY_DBNAME.' USE MY_DBNAME END ELSE BEGIN PRINT 'Creating new database MY_DBNAME.' CREATE DATABASE MY_DBNAME END GO I keep...
I have 2 SQL Task, each calling different stored procedures. The query in each SP is enclosed between BEGIN and END trans. I added "OnSuccess" of task 1 then move to task 2. I also added 'Fail Package On Error" and enabled "Tansactions" at package level. Question - If task2 fails, shouldn't task 1 be rolled back? ...
Is there an easy way, using Java, to scan/search the local network for SQL Server database instances? ...
I've got a number of ASP.Net websites (.Net v3.5) running on a server with a SQL 2000 database backend. For several months, I've been receiving seemingly random InvalidOperationExceptions with the message "Internal connection fatal error". Sometimes there's a few days in between, while other times there are multiple errors per day. The ...
I'm using SQL 2000 for my application. My application is using N tables. My application has a wrapper for SQL server called Database server. It is running as a 24/7 windows service. If I have checked the integrity check option in the SQL maintenance plan, when this task is running one time after that one of my tables has been locked and...
I need to populate a column with the running count based on another column contents. The table is like this: count seq_num 1 123-456-789 1 123-456-780 1 123-456-990 2 123-456-789 2 123-456-990 So, as the seq_num column changes, the counter restes to '1' and as the co...
I have a trigger that supposed to update log out time[generate random log out time within an hour of log in time, seconds have to differ too], how do I achieve that functionality, please help here is my trigger so far: USE TestDB GO CREATE TRIGGER AfterInsertTG ON dbo.usage_reports_Dummy2 AFTER INSERT AS DECLARE @pk_id as int, @mem...
Hi i want to know how to enable remote server connection in sql server 2000. I have sql server 2000 installed in a system with windows server 2003 os. i want to create a dsn connection to the database through remote. I have created a dsn locally in the server it is working. but how to create dsn from the remote system and make it work. ...
I am building a view in SQL Server 2000 (and 2005) and I've noticed that the order of the join statements greatly affects the execution plan and speed of the query. select sr.WTSASessionRangeID, -- bunch of other columns from WTSAVW_UserSessionRange us inner join WTSA_SessionRange sr on sr.WTSASessionRangeID = us.WTSASessionRang...
Dear Friend, Below is my SQL Query which run against 30000000 rows and its takes 6 minutes to run indexes are defiened to the all columns used in the where clause as wll as inner join Please help me SELECT auditData.id,nstmp.ProviderMaster_ID as CDRComment,Auditdata.Calltypetag from Auditdata AuditData inner join NoSeriesMaster_t...
I have a continous time dataset and I want to break it up into 15 minute blocks using sql. I don't want to have to create a new table to be able to do this if I can help it. i.e. Time, Count 09:15, 1 09:30, 3 09:45, 0 10:00, 2 10:15, 3 ..... Does anyone have any idea of how I can do this. I presume that is use a select similar ...
I'm using MSSQL's full text indexing on a handful of tables in my CMS and am unfortunately stuck with SQL Server 2000. I'm querying the index using freetexttable joins and am having pretty good results, but we have some unique terms that are likely search queries that do not appear to be stemming. For example a query using the term "sm...
When I try to import Text File into SQL Server 2000 using DTS, it gives me the following error Not enough storage available to complete this operation Any suggestions? ...
I have a stored procedure for SQL Server 2000 that can only have a single instance being executed at any given moment. Is there any way to check and ensure that the procedure is not currently in execution? Ideally, I'd like the code to be self contained and efficient (fast). I also don't want to do something like creating a global temp...
How would I programmatically go about limiting the access of a database in SQL Server 2000 to the database owner for that database? Example... if I right-click "Properties" on the "Northwind" database in Enterprise Manager, the Owner is listed as sa. How would I limit access for this database to just the sa login? ...