I'm wondering what's the fastest solution (if one is faster than the orther) between : if exists (select * from MyTable Where ...) and if exists (select Null from MyTable Where ...) Thx a lot for your answers. JN. ...more >>

Hi All, I have to count the total placement of a particular employee to each branches in one year. The expected resultset is like this : Employee BranchCode Total_Placement : -------------------------------------- 1001 100 2 1001 200 1 .... etc..etc From the dat...more >>

Please help comparing the following two scenarios: 1/ Create View V1 As Select * from T1 Union Select * from T2 2/ Insert Into Into T3 Select * From from T1 Union Select * from T2 Will there be any difference in query response between V1 & T3 ? A few queries that I have tried showed a margin...more >>

hi i have a table create table payment ( payment_installment_no integer, due_date datetime ) insert into payment (due_date) values (getdate()) insert into payment (due_date) values (getdate()) insert into payment (due_date) values (dateadd(day,1,getdate())) insert into payment (due_date...more >>

I have a procedure running locally on my server A. This procedure does an insert into a table after getting a return value from a select done on another server say B . Server B is set up as linked server on server A. th error i get shows as The operation could not be performed because the OL...more >>

I've been trying to diagonose a strange problem with an application that I inherited. Every few days, SQL Server will take between 3 to 4 minutes to perform a really simple update. The application that runs against the database is a real-time monitoring program that communicates to a numbe...more >>

Win2k, SQL Server 2000 SP3a. I've encountered a strange problem, when attempting to insert data into a table, with an index (clustered or non-clustered) The code performs an INSERT, using the results of a SELECT statement, which works perfectly (finishes in 20 seconds) when the target tab...more >>

Hi I have two tables with almost similiar data. Create table temp1 ( name char (40) ) go insert into temp1 values (Joe Smith) insert into temp1 values (Jane Smith) insert into temp1 values (Joe Doe) insert into temp1 values (Jane Doe) insert into temp1 values (Bob Hart) se...more >>

> My problem is I have a folder named SQLSCRIPTS which contains 6 stored > procedures and one .txt file. This .txt file contains the sequence of > execution of my stored procedures. What I want is to create a procedure > using SQL which will open the folder, read the sequence of execution and ...more >>

In a lot of cases for the sake of performance I use UPDATE, INSERT, and DELETE triggers for maintaining a denormalized column in another table that stores summary information (such as an inventory transaction table and a total on-hand balance column in an item master table). I was thinking, si...more >>

Hello, I need some help on getting the name of a table, or even creating an id of some kind, that relates to where a record was pulled from. I’ve included a *simplified* example so you can see what I’m talking about… say I have 3 tables, and a query that returns 2 rows, each row made up ...more >>

Is is possible to use a variable for the table name in a stored procedure? I tried this... CREATE PROCEDURE spTest @TableName AS SELECT * FROM @TableName GO It tells me I need to declare @TableName. Is there a problem with my syntax or is this not possible. -- Chuck Foste...more >>

Ok, here is a (hopefully) more informative way of relaying my question. Using this DDL...I have a question below. CREATE TABLE Apps( app_id smallint IDENTITY(1,1) PRIMARY KEY, app_name varchar(50) NOT NULL ) go INSERT Apps values('Application One') CREATE TABLE...more >>

I have a script that creats a number of stored procedures. It is called using osql. When the script is called, like: c:\source> osql -i create_sprocs.sql Is there a way to not have the #> lines shown whenever a GO statement is in the scripts, like STEP --------------------------- ...more >>

Why shoudl the query plan derived by an SP and one on Query Analyser would differ widely? I have a system that has been running stably for a while now when one of the queries started behaving badly. The SP version of the query comes out with an estimated cost of 0.311 whereas if I take the sam...more >>

I would like to set the value of one field based on the value of another column. Basically if the user inserts or updates a record with columnA it will determine and set the value for columnB. How do I do this in a update and insert trigger?? The user may insert or update one or multipl...more >>

I'd like to get the following output, eventually via DTS to a text file deposited in a folder for pickup by another application. <ImportSession> <Batches> <Batch Name="tes01" BatchClassName="Test_v1a"> <Documents> <Document FormTypeName="TAO_App"> <IndexField Name="Accountno" Value...more >>

how can i check if a user is active? ...more >>

New at this and would like to get the code in VB or . Net which would allow connection, reading and writing to a SQL database previously created. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hello all Guru's I am in the process of switching servers due to a new very large hard drive that will carry data only. The problem is, that using several different methods I have now trasferred 18 out of 19 databases to the new drive. All went well except in a few databases all the jobs did n...more >>

A really simple question,,,, I need to calculate the median of a measure. I'm having a tough time to figure out which dimension to use. If If I have 4 dimensions, Date, region, site, and process code and I want to get the median for the measure number of days, what do I use to define the set? ...more >>

My problem is I have a folder named SQLSCRIPTS which contains 6 stored procedures and one .txt file. This .txt file contains the sequence of execution of my stored procedures. What I want is to create a procedure using SQL which will open the folder, read the sequence of execution and start exec...more >>

Is there a way to disable the message "Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object..."? I cannot rearrange my stored procedure as they are self-referencing: create proc A as begin exec B ("TRUE") ETC... ETC... ETC... end ...more >>

table1 contains an id field and comment fields. I need to pass the Id value to an update query every time someone change/add comments. Can anyone show me how to do this? Thanks, Culam CREATE TRIGGER trig_tables ON server1.dbname.dbo.table1 FOR UPDATE AS IF UPDATE(comment1) ...more >>

I have a case where I do not have a business specified primary key for certain tables. Also data may be moved from database to database. That requirement rules out the use of Autoincrement fields. Anyway I do not usually use Autoincrement fields since I like to set values at the client. So I am ...more >>

I have select like this: SELECT isnull(table1.structure,'-')as structure1 FROM table1 and I get an error message: Msg 8115, Level 16, State 6, Line 74 Arithmetic overflow error converting varchar to data type numeric. Structure column is decimal type. Why is this not allowed and what...more >>

Hi, SQL 2000 I disabled the transaction replication from publisher server and everything cleaned up from publisher, but I still can see agents under subscription on subscriber server. How can I reomove the agent from subscriber server ? Thanks. Pat ...more >>

Originally I thought this was going to be a DISTINCT issue, but I don't think that is the case. I have 3 tables.. Apps ID AppName 1 ApplicationOne Tests ID TestCase 1 Run Overnight TestAttributes ID AppID TestID Type OS ProjectID 1 1 ...more >>

hi, I try to do insert a stored procedure's result into a temp table, then I try to put it inside the function, but it doesn't work. The reason I want to do this is because we have 200 stored procedures which need to reuse the same code, so by putting these logics into a function, the code would...more >>

Greetings, I'd greatly appreciate if someone could give some answers or pointers to the following topic: How SQL 2005 can be extended with, let's say, new indexing functionality? What I mean is something along the lines of "data blades" or "cartridges" in other RDBMSes. A typical use case: you...more >>

I have a lookup table like: ID Desc -- ----- 1 AAa 2 B 3 C I know there are some queries to to see if an ID exist in this table or not ,but I just wondered which one is the quickest one. Thanks ...more >>

Hi All, I have the following question. CREATE TABLE [dbo].[Table1] ( [ClaimNumber] [varchar] (10) NOT NULL , [SupplierID] [varchar] (10) NOt NULL , [VMATLCST] [money] NULL , [VLABCST] [money] NULL ) Insert Table1 values('C1234', 'SID1', 45.67, 59.77) Insert Table1 values('C123...more >>

Hello, I need to get a list of all my SPs where the text contains Join SOI In a VB app I can loop through all the SPs using SQLDMO and say If Instr(1, sp.Script, "Join SOI") > 0 then ... In Tsql I got as far as Select t1.Name, t2.Text From SysObjects t1 Join SysComments t2 W...more >>

Hi I would like to execute a program from an SQL Server Proceedure. But I need to Execute it with a 3 minute time delay, plus if there is another instance of this delayed execute running then I need to not add another. Any help would be much appreciated Thanks B ...more >>

This recordset is not updateable Server = SQL Server 2000 SP3a Client = Access 2000 (data project) I've run out of ideas and ability, any help would save me having to re-design a chunk. I have a sproc which creates sql statements to create views to be used as "Criteria" depending on pa...more >>

Hi Guys, My am using Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4) The applicatin ( written in java ) has a functionality where it creates views an...more >>

hi. I am trying to populate a table using this query: insert into ADTSVR01.WM.dbo.sales_orders (SO,SoldTo,user_name,ShipDate) (select distinct sn.so, c.cmp_name, sn.customer, p.DateField1 from ADTSVR01.WM.dbo.LogSN sn inner join prproject p on sn.so = p.projectnr inner join cicmpy c ...more >>

I do this to backup the database on our production server BACKUP DATABASE [DP2] TO DISK = N'D:\mssql\data\MSSQL\BACKUP\DailyDbBackup' WITH INIT , NOUNLOAD , NAME = N'DP2 Backup Job', NOSKIP , STATS = 10, NOFORMAT DECLARE @i INT select @i = position from msdb..backupset where database_n...more >>

Ok so here is the issue. I am thinking I somehow have to clear the old data after executing the trigger. So I add a user Joe Brown with his info on a users table, a trigger fires and dumps the duplicate data into a users-dup table (for other justifiable purposes). Update does the same bas...more >>

What is the syntax to call a stored procedure (With parameters) using the OpenConnection command? Documentation for doing this seems to be non-existent. Thanks Scott ...more >>

I hav a table with a column wich has a default constraint. I want to remove this constraint and to add an other one whitout to delete the column. It is posible? Thanks for help! ...more >>

We have a view called "vwtblBranchData" which is this: "SELECT * FROM Branch_Master.dbo.tblBranchData" We have a query that uses the view that has worked for over a year and now has stopped working. The query is: select * from vwtblBranchData with (readuncommitted) where (closed=0) and bran...more >>

hi! I have a stored proc which aggregates some values and returns a recordset with one record ....looking something like this: val1 val2 val3 ----------- ----------- ----------- 24723 26457 342579 how can I use this record for further processing e.g. write it into a...more >>

HI Here is a piece of code which i execute while running a stored procedure, when i trace the procedure i get error 208 , the inner proc which i execute is there in the database but still i get error 208 when i execute the outer procedure, here is the code for outer proc which executes the...more >>

Hello, I know there is a function that returns the name of a database, can anyone tell me what it is, darn BOL will not show it for me J...more >>

Hi, I have the following 2 tables: tbl_CustomerEmail1 ( CustomerID1 int, EmailAddress varchar(200), Unsubscribe bit ) tbl_CustomerEmail2 ( CustomerID2 int, EmailAddress varchar(200), Unsubscribe bit ) On each table I have a trigger to subscribe/unsubscribe a customer in the o...more >>

The error message for err. 547 is: "%ls statement conflicted with %ls %ls constraint '%.*ls'. The conflict occurred in database '%.*ls', table '%.*ls'%ls%.*ls%ls." This error is raised when a foreign key error is occured, and when a check constraint error occured, too. What kind of values c...more >>

My apologies for cross-posting, but I'm not sure which newsgroup this belongs in. I am attempting to execute a DTS package from Visual Basic, and I'm encountering a problem. In a nutshell, how does a low-level user (from a security standpoint) execute a DTS package? ' here's the relevant part...more >>

hi out there, is there a way to create triggers on system tables (e.g. sysusers)? I receive an error message that tells me I have insufficient rights. ...more >>

My problem may be difficult to explain. But I have a problem with reporting balances at a month end because software doesnt roll the balances over. If the code hasnt had any activity in current month then no inserts. Hence I need to create a SP to carry over prvious months balance. i.e. tb_ba...more >>

Error:Too Many Arguments Specified Posted: 03-03-2005 05:03 AM Hi, I'm pretty new to ASP.NET so I'm having some problems I'm trying to insert a users registration info into the DB using a Stored Proc but for some reason it won't work When i run the Sproc in query analyser it seems t...more >>

Error:Too Many Arguments Specified Posted: 03-03-2005 05:03 AM Hi, I'm pretty new to ASP.NET so I'm having some problems I'm trying to insert a users registration info into the DB using a Stored Proc but for some reason it won't work When i run the Sproc in query analyser it seems t...more >>

Hi, I'm pretty new to ASP.NET so I'm having some problems I'm trying to insert a users registration info into the DB using a Stored Proc but for some reason it won't work When i run the Sproc in query analyser it seems to be ok with it but when i run the asp pages it lets me enter th...more >>

Hi all I have a single connection msde. is it possible to backup the open DB? Or must be the backuped db closed?...more >>

can any one advice me on how to insert the results of a SP into a temp table *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

I want to make a copy of a production database on the same server, so I can test things on the copy before I deploy them. So, after I run the copying SQL, i will have "Northwind" and "Northwind_COPY". Does anyone have any SQL script for this ? Thanks, Craig...more >>

Hi, I apologize if this is the wrong forum for this kind of thing. I have inherited the following stored procedure (of some 1000 lines) and would like to rationalize, tune, optimize or just completely re-architect it. Some thoughts or guidance would be appreciated. What I'm...more >>

i had to truncate a table in several instances of same database. for switching databases USE is not allowed in Stored procedure. is there any other way of looping in db1,db2,db3,db3 all are instances of same database. i want to execute a truncate statement. instead of manully doing it can any...more >>