I can use queries to retreive many informations from INFORMATION_SCHEMA SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = 'tablename' But I want to know how to find where that table exists as it is not part of sysobjects Madhivanan ...more >>

With the stored procedure below if i do DECLARE @ProductName nvarchar(40),@ProductID int EXEC updateProduct @ProductName = ProductName, @ProductID = ProductID why do i get error:- Error converting data type nvarchar to int. -------------------------------------- ALTER PROCEDURE upd...more >>

I want to move records from table1 to table2 if the records in table1 does not exist in table3 I have following code: START TRANSACTION INSERT INTO TABLE2 SELECT * FROM TABLE1 WHERE NOT EXISTS(SELECT * FROM TABLE1, TABLE3 WHERE TABLE1.ID = TABLE3.ID) DELETE FROM TABLE1 WHERE NOT E...more >>

hey all, is there a way to hide all those system store procedures in Query Analyzer in the Object Browser? thanks, rodchar...more >>

Any ideas how to resequence the numbers in a column after inserting or updating a record. The column is not the Unique ID and is an integer column it is used to allow users to select the order in which things are displayed for them the column is displayOrder and the select statement would Order B...more >>

Hello, I have a table T1: Date | Value --------------------- Jan A Feb A Feb B Mar A Mar B Apr A May A I need a query to get dataset like this for ANY VALUE with maximum daterange: Date | Value -------------------- Jan A Feb ...more >>

Hi, Is there a way to lock entire database? What I am supposed to do is kind of replication: - a number tables exist in database, these tables should be updated - so, temporary tables are created and filled with data - these temporary tables are renamed to final name (using sp_rename) This...more >>

in an sql statement that is concatenating asp, i want to query first names to a customer database with LIKE: "SELECT fname + ' ' + fname AS Name, cust_id " _ & "FROM tblcusttag " _ & "WHERE fname LIKE '%" & Left(strSearch,1) ...having trouble usingLeft function, as when the user does ...more >>

Hi, I need to transfer a database from MSDE to SQL Server. I can use T-SQL to detach a database, copy the .mdf and .ldf files to another server and re-attach them there, but I do not want to use the Import and Export Data (via Enterprise Manager) if possible. I need this to be as easy as po...more >>

Is there any builtin SQL function (or UDF) that allow me to do this? I have a query that returns several records of varchar values (usually no more than 4 or 5, so eficiency is not critical here) I want to return them in a single record separating their values by carriage returns. With an...more >>

I have the following query that returns extra header info in the results when there is more than 1 day with no records in the query. I'm trying to figure out how to run this and not get the additional header information. Here is the query: SET NOCOUNT ON DECLARE @datestart int DECLARE @...more >>

Hi all, I need to get data from a remote SQL Server and, using some basic logic, either update or insert rows within an existing table on the destination server. I originally thought that doing all of this via DTS would be best, but maybe a better idea would be to break this into a couple of s...more >>

I am getting "Failed to create the share OMWWIZC" when trying to copy a database. How do I solve this? I checked the MS KB article, but can't interpret it, something about local admin rights. -- Chuck Foster Programmer Analyst Eclipsys Corporation - St. Vincent Health System ...more >>

Hi, We can create more than one=20 relationship between two tables. Could you tell me a situation where we = need two relationships between two tables? Thanks...more >>

I create a variable table using this statement in my UDF ,I'm like to put an index on of its field (Internal_ID),How can I do it? DECLARE @TBEnrollmentsForActiveClients TABLE(ENROLLMENT_ID NUMERIC(9),COURSE_CODE VARCHAR(3), COURSE_YEAR NUMERIC(5),COURSE_SESSION NUMERIC(5), ...more >>

I am using MS SQL Server 2000. I am using a java component which generates SELECT statements based on different critieria. The SELECT statements generated are using the old SQL syntax. For example it will generate : SELECT e.employee_id, er.location_code FROM employee e, employee_record er W...more >>

I am trying to use the case expression to change a number from positive to negative or vice versa. If QTY = 50 I want to return 50 when TRANS_TYPE = 'A' and -50 when TRANS_TYPE = 'B' If QTY = -10 I want to return -10 when TRANS_TYPE = 'A' and 10 when TRANS_TYPE = 'B' Psuedo code here: ...more >>

On SQL Server 2000 we are connecting to many AS/400 tables using Linked Servers. We are developing a process which runs solely on SQL using Stored Procedures. We are finding inconsistent results in the return of a query utilizing a linked table. The query: Insert into #tmpTableData(Control...more >>

Hi, I have been asked to check the value of @@Error after some of my Select statements but I think that I don't need to. Could you please tell me in what conditions we have none zero value in @@Error after a SELECT statement? If SELECT doesn't return any record, it doesn't return any error ...more >>

We store multiple revisions of some data in a single table. When we = want the current revision we use a subquery with MAX(revision) in our = VIEW. Our user key and the revision are indexed: "user key" ascending "revision" ascending Would changing "revision" to "descending" in the i...more >>

I hate to post twice but I never received a response from 2 days ago. I am capturing system information about computers. Currently I have one table named SystemInfo that gets data inserted by DTS everyday. Before running DTS job, I would truncate table and insert all new data. Since there ar...more >>

I am looking for a script that will list windows userids and sql userids with database object permissions. Please help me with this task. Thanks,...more >>

Platform - SQL Svr 2000 SP3a I have a couple of questions regarding stored procs 1. Is there a way to query on keyword text withiin stored proceduers? for example, if I wanted to find all stored proc's that contain 'AND where col_name <> 3' in the statement(s), can I do that? Does...more >>

I am way over my head here so I hope someone can help. I am doing an e-commerce site. I have items that have three different size options. They are waist inseam, size and width, and size. Each of the above option has it's own table. Example if the use chooses a shoe it is most likely th...more >>

I'm having a UDF which is quite fast till the last step which makes executed for 50 seconds on a single execution.In the last step I have used a right join which makes a very slow ,IS there somebody can give some hints to enhance the last join.Thanks alot CREATE FUNCTION dbo.GetC4MissingEnr...more >>

I have two databases on the same server and would like to access data from both. In oracle there is a 'dblink' function that allows you to do this. What is the procedure in sqlserver ? Don ...more >>

Can anyone provide any links or information about the differences in rounding between access 97 and sql 2000. ...more >>

Hi I have the following table: tblMeasurement StationID (char(7)) DateTime (smalldatetime) ValidMeasurement (bit) measurement (integer) now the original Access table had the first three as PKs. When transfering it from Access I lost the PK info and I also want to modify the data type ...more >>

Where can I find good information about how to write triggers? I'm using SQL Server 2000. Now, the task at hand: How can I write a trigger that reacts on both insert and update to keep some fields in two similar (but not the same) tables in sync. (I need to both insert and update the 2nd ta...more >>

I have a SP called SP_GetTable. In a seperate SP how do perform the following? CREATE procedure SP_GetValuefromSP as DECLARE @MyTable (@Field1 Integer, @Field2 Integer) Insert into @MyTable [Results from SP_GetTable] return 0 go Thanks, Dave....more >>

I have upsized an access database and am trying to find all the maximum lengths of the columns, so I can restructure the DB. I have the query that will give me the max length of the column, but since I have a bunch of columns, I would like to write a SP to do it for all the columns. Here i...more >>

Hello. A quick layout of what I am trying to do. I have 3 fields in a table. There names are.. fname, lname and fullname. What I would like to know is, how do I combine the first 2 fields to show the results as the full name in the fullname field? Is this possible. If so, can someone pleas...more >>

in QA I have select sum(F_Basic_premium) TotalBasicPremium from fat_bse_po_risk_detail(nolock) where f_latest_sum_insured = 'Y' -- 1. 22421075998346.898 -- 1. 22421075998346.594 select sum(F_Basic_premium) TotalBasicPremium from fat_bse_po_risk_detail(nolock) where f_late...more >>

Given a user name and a stored procedure name, can someone provide me with the TSQL code to set a boolean indicating whether that user has EXEC permission to that proc? The specific proc I'm interested in is xp_loginconfig. Thanks in advance for your help, Hal Heinrich VP Technology Ara...more >>

Please help with this sample sql drop table marc create table marc(m1 varchar (10), m2 varchar(10), m3 int, m4 varchar(12), m5 varchar(2), m6 varchar(6)) insert into marc values ( 'EIR CE0', 'EIR CE0', 5, 'Clare', '05', 'Clare') insert into marc values ( 'EIR CN0', 'EIR CN0', 4, 'Cavan',...more >>

The following stored procedure works perfectly, but it's my first attempt at writing one that generates and executes a dynamic SQL call to the DB and I'm a little annoyed that I had to declare variables multiple times. The way it is now, if I remove some declarations ("remit_vendor_out", for ...more >>

If i want to select antim_id from following table using query Q1...which has all the missile_id values returned by another query Q2 .. For eg. if Q2 returns 100,300,400 my Q1 should return 3000...How to do it.? Will using ALL will help? DEFUSE_CAPABILITY +----------+------------+ | ant...more >>

Are there any alternatives to 15 if...else....statements in SQL Server Programming. If so do they scale better and/or provide performance gains? Thx Marc...more >>

I've got a column that is as type int(length=4). This column is getting changed to smallint. Obviously, some values in the column will not fit into smallint type. I've been told that as part of moving data over, I should ignore top two bytes and carry forward the bottom two bytes - so that it wi...more >>

Hi all, I too have a similar problem that I can't figure out. I have a SP I have created (below) that checks spacing on all the databases on my server. I have set a cursor to loop through all the servers listed in sysservers so that I can do multiple servers at one time, but am hitting a problem...more >>

Hi Everyone, I've existing data for a ordering system. Currently there are "Order" & = "Distribute" tables. "Order" is keeping transaction of what product are brought and how much = quantity.=20 "Distribute" is a table that records how the product are issue to or = return by each personnel. ...more >>

Hi. Not sure if this is the correct newsgroup (could not find another suitable group), and if not please redirect me to the proper one.... We're running multiple instances of MSSQL 2000 on a server. This is done to easily incorporate client DBs into our testing operations. The problem is th...more >>

I had connect another SQL server VIA VPN , the server name is IP e,g 123.123.123.123 I can read the data sucessfully, However, I want to insert some data from my local server. sp_addlinked server '123.123.123.13' sp_linkedserver (I can see it) then select * from [123.123.123.123].database.d...more >>

Hi all In Table1 I have prmary key constraint and it is refernced by some column as a foreign key in Table2 Now I want to modify my constraint by TSQL in primary key table , How to do it? For ex in primary key table ALTER TABLE [dbo].[table1] add CONSTRAINT [PK_table1] PRIMARY KE...more >>

Hello, I want to search for a charachter '%', but this doesn't work: SELECT * FROM table WHERE patindex('%%%') > 0 What is the solution? Thanks for any help in advance! Andreas ...more >>

I have an enterprise Manager of SQL and a file called file.sql. How can I run this file to create a database with tables and stored procedures? regards reidarT ...more >>

Hi, Is this possible? .... I have some data like so: ID Type num capacity 1 12 4 6 2 13 4 999 3 13 7 999 4 13 8 999 I want to get the rows where the choice of capacities for a particular ...more >>

Hi All, Some of the stored procedures are called by the framework Ex (load,update,delete) when we click the buttons on the grid which is out of our scope. Please give me an idea of writing testcases on stored procedures .. Regards, Jyothsna. . ...more >>

Hi Everyone, I've a 'Sale' table that keep the accumulated sale quantity by day. But I need to generate a query to split these accumulated qty into = individual record. Anyone has any idea how can this be achieved? Sale_date Product Quantity ---------- ------- -------- 2005-01-01 ...more >>