How can i konw how many time pass since las execution of all my sp. thnks may thanks ...more >>

I have a set of text files which are ftp'd to my server daily. I have to update these to multiple remote SQL databases. I am linked to these databases via a VPN and use the IP address to set up the link to my local server. Each of these stored procedures build a work file from these text file...more >>

I'm having trouble understaning a code snippet from Ken Henderson's book The Guru's Guide to Transact-SQL. The exmple computes a statistical median (see below). I've used GROUP BY and HAVING lots of times. I usually use a simple HAVING clause like HAVING COUNT(*) = 1 or something like that....more >>

Hello, I have a sproc declared like this. CREATE PROCEDURE [dbo].[GetMyResult] @Param1 varchar(50)=null AS... SELECT * FROM TABLE1 WHERE Col1 IN (@Param1) As you can see above, I need to select result set filtered based on values passed in. First, is it how I should do? Secon...more >>

I have the following View in SQL 2000: SELECT DISTINCT [Property ID], SUM(Amount) AS SumOfAmount FROM dbo.[Payments more than 60 days old] GROUP BY [Property ID] and get the following error: [Microsoft] [ODBC SQL Server Driver] [SQL Server] Location: recbase.cpp 1375 Expression: ...more >>

st_getfriendoffers @offerid='26',@userid='' CREATE PROCEDURE st_getfriendoffers @userid numeric(18), @offerid numeric(18)=0 AS if @offerid ='' select * from crosstable where towhom=@userid and block=0 and active=0 else select * from crosstable where id=@offerid GO Error convertin...more >>

Can someone please help me? I am working on a dual 2.4 GHz Compaq Server with 2 GB of RAM. This server has Hyperthreading enabled. SQL Server 2000 SP3 I have been doing some performance testing of one of our applications. My issue is related to Transactions. Lets say that I have tw...more >>

Hi, I am trying to write a search query which should query 3 tables based on the keyword provided by the user.I am using ASP as a front end. The search should be displayed according to best match (the best match should be displayed first) The tables & fields of each table are : main : mai...more >>

I would like to be able to check if SQLDMO is installed in a machine and if no to install it without installing SQL Server. Is this possible? If yes what do I need to install and register? Is it possible to get the machine name knowing the name of an instance of SQL Server? Thanks Yannis ...more >>

I have been switching my C#.Net application so it uses stored procedures. Mostly this is not a problem but I have a couple windows that allow the user to search based on different values in the database. To do this I give them a screen with a list of check boxes and entry fields and then buil...more >>

Can't seem to get help on the replication group so I wanted to ask here. Is it possible to use Transactional replication between 2 sites without using a VPN? How about SSL? This seems like the only other option. Would I need to get the servers to talk to each other first by connecting in Enter...more >>

Hi,.. Is there any way i can see an history of the SQL statements (DDL and DML) which have been executed against a database? I have a sproc which runs fine when i run it from Query Analyzer, but when i try to run it from my application, the procedure is throwing a syntax error from somewhere i...more >>

Hi, I have sql server agent jobs that are failing becuse the "SQL server job log" is full. apparently this is causing notifications to stop being sent. My question is how do I delete all sql server job logs. I also find this error strange because there is plenty of disk space, which make...more >>

I have 2 tables. One of the fields in the second table needs to be linked to the first table, using the 'like' operator Table1 ------- Name, Description Table2 ------- Description, Price The description in table 1, must be 'like' that in table2. Is there a way to do this? ...more >>

Hi, I have an SQL Server 2000 database with several tables, views and SPs that need to be copied to new databases on semi-regular occasions (copying the structure and data of one "master" database to another) The problem I have is, for reasons unknown to me, the copy always fails at around...more >>

I'm trying to find duplicates between a temp table and a live table. If there are, it creates an error and emails an attachment that reports which record(s) were duplicated. I have a data transformation step that uses this T-SQL: SELECT * FROM temp_lifting LEFT JOIN lifting ON temp_lifting....more >>

select a.*,b.* into New_MPWORK from MPWork as a, TestData as b Behold the above query! Simplistic, no? When run using the two small tables above, the query runs for well over an hour and consumes gigabytes of server space... Far, far more than both tables combined. What gives? What am I doi...more >>

When I run the query below I get the following output: PostFICO_Score 627.0000000000000000000 I want to output to look like this: PostFICO_Score 627 Please help.. Query: SELECT ((CONVERT(numeric(18, 0), PostFICO_v)) / (CONVERT(numeric (18, 0), PostFICO_n)) )as PostFico_Score ...more >>

does anyone have an optmized method of searching a database or database table? I've already done an index on my table with all possible columns and have no difficulties doing one word searches using: CREATE PROCEDURE [dbo].[spSearchDatabase] @search VARCHAR(50) AS SELECT jobid FROM ...more >>

Ihave created a (non-temp) table with a single column. I would like to annotate this column so the DBA after me knows exactly what "ColGrtRte" is used for. Are there any INFORMATION_SCHEMA columns I can hijack, or a reasonable equivalent? I don't want to make an entirely new table if I can ...more >>

I am trying to figure out what to use for a primary key in a products table and need some advice. At the moment there isn't any single column that distinctly identifies a product/Item in the system. What I would like to find is a way to identify a unique product in the system with a single c...more >>

Hello£¬everyone, My program will collect a testing machine's data ,save the data and deal with the data everyday. I want to use vb.net to create database, add and delete tables or modify the records in the database. Is it possible to create a SQL Server database using vb.net? I know I can ...more >>

Greetings, I have a Trigger that runs and updates a date field. It's accurate 98% of the time. Is it possible that something in SQL Server would prevent a trigger from being accurate 100% of the time? Maybe a system issue? Here's the syntax: ----------------------------- Create TRIGGER trg...more >>

Hi, is there a way that I can learn the table design (DDL) of the output table of a stored procedure? The procedure only returns one recordset. psd...more >>

The problem occurred with this control. ADO Data Control 6.0 (OLEDB) MSADODC.ocx 6.00.8171 L,P,E This is one of the fileVB6.0 ships with. I am able to work with the control in design mode, (connection string setup and database table selection works) but at run time the control is d...more >>

Hello there I have a table that i would like to have a huge history of the table: when i add new record the entire records that was before should be saved and the same for update and delete for this i'm tried to use the trigger FOR INSERT, UPDATE, DELETE and working with the table But...more >>

In the following examples, why the first case statement works and why the second doesn't, I wonder. Could anyone shed some light on this? Thanks, Ganesh -- this is an attempt to: -- Remove leading zeroes if @in is all numeric -- return same value, if not all numeric dec...more >>

Here is the table: CREATE TABLE [EmployeeOBenefitApproved] ( [emp_obenefit_id] [int] IDENTITY (1, 1) NOT NULL , [empplan_id] [int] NOT NULL , [obenefit_id] [int] NOT NULL , [cost_per_month] [float] NOT NULL CONSTRAINT [DF_EmployeeOBenefitApproved_cost_per_month] DEFAULT (0), [coverage_st...more >>

Hi, I know that I can get client name (SUSER_SNAME), client host (HOST_NAME), but can I get Client IP address? Thanks in advance Radovan ...more >>

Hello experts! sql2000,vb,ado2.8 I want to notify the user about recordset changed while he is browsing the recordset. How can I do this? i know, one way is to create a second recordset, requery regularly, and watch the differences..., but i assume, there are automatism in sql2000 to do th...more >>

does anyone know a simpler way of writing a select statement where we filter using a different column depending on what the parameter passed into the stored procedure. For Example: CREATE PROCEDURE [dbo].[spSelectJobInfo] @condition INT AS IF @condition=1 SELECT * FROM JOBS WHE...more >>

I have an "inner join" select statement that is part of a deadlock situation. It seems to me as the tables in the select statement are locked one by one as the select statement is processed. And between these locks, another process is inserting/updating the tables in reverse order, cusing the de...more >>

Howdy, First of all thanks in advance, I'm runnig SQL 2000 from my desktop I've created a table that I use for reporting purposes sysfact_temp. This table is created via a series of SQL statements. Once created, there are around 15000 rows and 20 fields. The second ].[dbo].[CXMSShip_Fact Vi...more >>

Hi Guys, I've got a bit of an issue with a database / Server. Its a server which needs to be up 24/7 and the SAN for the Databse is about 2.7TB. Basically lots of transactions going through the database every day. How would I go about indexing/tuning the database, while people need 24/7 con...more >>

I have a table that has a column defined as 'image' with the text in row option set to ON where the value is in the data row. The table is growing to fast, and I want to change the text in row option to OFF to allow a pointer in the data row instead of the value. What is the process fo...more >>

Hi, I have a table in which I store an attribute for a given record over a period of time. It has an effective date and ending date. When the attribute change, I insert a new record on the table and enter the range of dates to which this is effective. I have like 1,000,000 records on the or...more >>

I have to perform a full outer join on two massive tables using query analyzer. Since the server consistantly bombs out each time I try, I decided to create one index on each table, as supposedly this will speed up the process, and decrease the overhead processing. However, how do I write the co...more >>

Hi, i try to select users from a specific table and for each user i want to do something, so: [code] select UserName from UserTable where fragen_flag=1; -- for each user do -- select something... [/code] howto realize that? i tried while structure, but it seams not to be ...more >>

Hello, is there a way to sort sp_who2 results by DatabaseName? Any suggestions? Thanks, ...more >>

Hello, I am trying to write a dynamic where clause without DynSQL. I found a great example showing how to do this using CASE to switch on a value (and a COALESCE too). The examples ive found are for single values. Follows one of them (@LastName is a param to the sproc this is in)... select...more >>

I have been tasked with the responsibility of managing "Release Compatibility" of our products with Microsoft products. Basically, it is my job to make sure that we keep a detailed list of which of our products work with which of Microsofts products. This includes not only different versio...more >>

I'm new to this area. Now my VB6 application uses ADODB DSN-less connection with a single password and user name for any user (users do not know these values. It's hardcoded stuff). Then I'm requesting a list of user names and passwords from SQL Server table. User is asked to enter her name and ...more >>

I've been tasked with creating a method for performing database transfers from production to development . . . it seems many changes happen on the live server, for whatever reason, and we need to periodically back-up and restore Production onto Development. Problem is, the production database ...more >>

I am working with several developers who feel that it is necessary to use a transaction around a select statement in order to prevent 'dirty reads'. I believe this is handled with the isolation level, but that a transaction around a select is unnessecary. Anyone have any thoughts or proo...more >>

Hello, I have a VIEW with two autonumber fields. The autonumbering works correctly. However, I needed to add a trigger to the view; therefore, I added the option WITH VIEW_METADATA. This forced the trigger to look at the VIEW's metadata instead of the underlying tables metadata. The problem...more >>

Hi, I am quite new to Sql Server and am using 2000. I have written a fairly simple collection of stored procedures and views that I would like to deploy on a client's Sql Server. In other words, the client has the necessary data already, and my code provides some functionality. The cli...more >>

Hello Experts, Is there any stored procedure or something, that can change the ordinal position of a column in a table programmaticaly (T-SQL maybe)? Thanks in advance...more >>

hi, there are 275 tables in the database. I want to list out all the tables who are having identity col as primary key. please suggest me a script for this -- Satya Rao ...more >>

Hi all; Hope someone has the rith answer for this; I have one windows app written in vb.net and i'm using SQLDMO to check if my Sql Server is running. I don't have any troubles when connected to my local DB (MSDE) for checking the state of Sql Server, but when i try do it on my remote Sql S...more >>

Hi, Can any of you help me with examples in using the table datatype in SQL Server 2000. I am trying to take the output of a stored procedure, into a variable of type table. I tried Books Online but it does not seem to help me :-( Thanks in Advance, Nataraj...more >>

How can i run window programs through xp_cmdshell eg calc program. which requires user intervention. if i use execute master..xp_cmdshell 'calc' this is hanging ... Thanks and Regards Mahesh...more >>