Hello, I am designing a database that communicates with a PLC. I have a product-log with a primary key of type intgere which is a Identity inctr 1. The PLC kan only read integeres up to 65k. In other words i need to reset the Identity before exeeding 65k. This will not cause duplicat...more >>

I'm using MS Sql Server MSDE Table1 shotID NextShotID CaromShotID 1 2 3 2 NULL NULL 3 NULL NULL I want to select the row where NextShotID is null and shotID does not exist in the CaromShotID column. Since ShotID1 contains a non...more >>

hi all how can check if the field 'mycol' if exist in table or not programing ? Tankes ...more >>

Hello, I have two tables. One table (asset_master) stores the asset and the month_id in which the asset is acquired. The other table, Month_week stores the week_ids for the month_id. The simplified table structure is as follows: CREATE TABLE [dbo].[ASSET_MASTER] ( [PROJECT_ID] [int] NULL ,...more >>

Do one of the following T-SQL statements cause a Performance hit if used within a query: 1. IN, OR, NOT 2. Sub-Queries Thanks ...more >>

According to BOL, columns in an ORDER BY clause do not have to be in the SELECT column list unless the SELECT includes DISTINCT, or the UNION operator. Is this a SQL Server thing, or SQL standard behavior? That is, if I were to write absolutely pure SQL-92, must columns in the ORDER BY clause ...more >>

Hi, I am facing problem in creating partition views. My table is like this Create table Table1 (Id int Identity(1,1) Primary Key, Name Varchar(50), CustomerId int Not Null Check (CustomerId = 1)) Create table Table2 (Id int Identity(1,1) Primary Key, Name Varchar(50), CustomerId in...more >>

what is the following missing T-SQL code, please if exit ................... drop database Tesing Go Create database Testing...more >>

I have the following code that checks out syntax-wise. However, is this code going to update every row in [OrderDetailsArchived] or will it only update the rows that were just inserted? I need the later. If the code is wrong, what do I need to do to make it update only on inserted rows? thanks! ...more >>

How do I execute a file that contains SQL Server script from inside another SQL script in Query Analyzer? ...more >>

We have a "SET IDENTITY_INSERT " statement in a stored procedure and the stored procedure has been granted execution privileges to a user 'xyz'. This user 'xyz' is Neither a dbo Nor part of db_ddladmin. role. When the stored procedure is being executed I get a permission denied msg. From BOL ...more >>

What Select can I use to display all the existing tables in my DB? Also, if I want to display only the system tables or the user tables, how would the Select look like? Thanks. ...more >>

Both FIGURE 1 and FIGURE 2 yield the same result to find the time duration between STARTDATETIME and ENDDATETIME fields. I created the FIGURE 2 formula because it will chop the result off after 6 digits on fractions that would otherwise be in scientific notation. FIGURE 1 was causing problems whe...more >>

class order NAME Degree books test score count classA 1 fname master 5 bio 60 1 classA 1 fname master 5 classA 1 fname master 5 phy 70 4 classA 2 fname BS 4 bio classA 2 fname BS 4 phy 60 3 classA...more >>

Hi I need to insert the result of a SELECT FOR XML into a table or variable, but SQL Server does not allow the INSERT INTO TableName with a SELECT FOR XML. Does anyone have another idea on how to do it? Thanks, Fernando ...more >>

I have been asked to produce a report that has taxed my SQL knowledge to its limits. This report is for a service work order system. There are two tables of interest. One table called tblFSO contains all work order information including the date that the work order was entered into the system...more >>

sql2k sp3 I posted a question regarding this scenario a couple of hours ago. However now I think I have a better understanding of why its not working. Ive got a job that calls a proc that I want to have exec another job upon failure. The rest of the proc works, except that the 2nd job d...more >>

Hello - I have a table with 100,000 rows. The id field is currently an integer primary key identity columns. I wish to generate unique, unused, nonsequential id's, but I don't want to have the length of a guid field. What are some options? TIA NS ...more >>

I have the following query which works fine in access 2000. How can I convert it so that it work under sql 2000? UPDATE DESKS LEFT JOIN VISITS ON DESKS.DESK=VISITS.Desk SET DESKS.MEMCODE = visits.memcode WHERE (((VISITS.BEGDATE)<=#5/19/2003#) AND ((VISITS.ENDDATE)>=#5/12/2003#)); Thanks ...more >>

What is the idiom for creating a new row, returning the identity, and keeping the row open in a recordset? I have a Sessions table with an integer identity column('id') I'm using as the SessionId field in VB. The 'id' column becomes the foreign key in the documents table. Each user should ...more >>

Hi all: I have a table named ClosedDate, which contains all dates closed for a store CREATE TABLE [dbo].[ClosedDate] ( [StoreID] [int] NOT NULL , [ClosedDate] [datetime] NOT NULL ) ON [PRIMARY] GO Insert into ClosedDate values (1, '2003-8-24') Insert into ClosedDate values (1, '20...more >>

I'm adding a column (data2), inserting data from a different column (data) in the same table and then dropping the old column (data2) and renaming the new column (data). We are having issues with trailing spaces and want to clean it up without dropping the table. How would I go about upda...more >>

Hi I need information how restore store procedure that has been deleted. Thanks in advance Marcin ...more >>

Hi Pro's This is followup of what Aaron and Anith suggested. I did create a table called PIDailyDates which has each day record from 2000-01-01 00:00:00 thru 2010-12-31 00:00:00. I have another table tagdatadaily: tagkey,datetime,value with records as 12,'2003-06-01 00:00:00',23.40 12...more >>

The db where I work is quite large - however there are only a few tables inside the db that are critical and need to be backed up. The other tables are quite large, but the data in them is readily recreated and backing them up is unnecesary. If I wish to only back up a few tables I need ...more >>

sql2k sp3 Ive got a job that calls a proc that I want to have exec another job upon failure. The rest of the proc works, except that the 2nd job doesnt start. Nothing in the log. if @dropsub = 0 and @dropart = 0 and @alter = 0 and @addart = 0 and @addsub = 0 --and @@trancount > 1 com...more >>

I am showing 3098 exclusive locks on tempdb on a SQL Server 2000 database. Is there an upper limit of locks you can have on a database?...more >>

I believe there is a bug in the relative monthly scheduling of a job. If I set a schedule that will occur after the scheduled start date, the job schedule becomes disabled and cannot be re-enabled until a start date is set that is after the next scheduled run date. Example: Current date Aug ...more >>

Is there a good way to set global variables? If I wanted to store something, say my site name, and be able to reference it in different places, mostly views for reports, can someone give me some ideas of how I could store it? A hardcoded UDF that was dropped and recreated if my variable ever c...more >>

I would like to see a schema on the existing database. How would I do this?...more >>

Dear all, How can I format the money in SQL Server 2000? For example, 1234567.0 becomes 1,234,567.0 Thanks. Terence ...more >>

MVPs, Can you please tell me which one performs better assuming me that both can satisfy the needs. Please point me to any available documentation. Specifics about how Functions are cached compared to Stored procedure cache also useful Rick ...more >>

Hello, My scenario: 1. Two Databases ( A and B) into the same SQL Server instance 2. A Table into Database A (tableA1) 3. A View into Database B referring tableA1 in Database A 4. Database Users have read rights on the View What I need: The Database Users have access only into the Datab...more >>

Hello All I have table with the following fields. ---- SSN, FirstName, LastName, DepCode DATA DESCRIPTION The data is as follows - the primary member is always DepCode '00'. The spouse is always DepCode '01' Any subsequent dependent gets the DepCode Incremented by 1 - so if a member has...more >>

I'm trying to create new records using ADO's Addnew method, updating and reading the IDENTITY field (See code below). Pretty standard stuff, but I seem to be having the same problem as others on this same newsgroup. The code has worked on a live system for the past 4 months. But for some rea...more >>

How do I make an SP available to all DBs in a server? Just like Sp_who or other one, the Idea is I have written my own utility and dont want to create it in every database. - I dont like puuting it in the model because most of my databases have already been created. - I did put it in master...more >>

I am trying to rewrite an existing query to something that can be handled in one or two statements without the IF..ELSE.. blocks. It looks like it can be done, but I am giving myself a headache and wonder if there is a query wizard who can see what I cannot: -- Update script marks account's v...more >>

What is SQL Server 2000 threshold of when an index will be ignored based on size of the table?...more >>

The IS_MEMBER function is driving me nuts. Can someone explain exactly what the heck it does when you pass it an NT group name, and what formats actually work? Here's my quandry. IS_MEMBER('MYDOMAIN\MyGroup') - works like a champ IS_MEMBER('LOCAL\Administrators') = fails to find group...more >>

Hello: Can guide me on how I can rewrite the following statement to use the format SELECT * FROM TABLE_A A JOIN TABLE_B B ON (A.ID B.ID) Thanks in advance. Venkat SELECT C.Cust_Acct_No_C AS 'PCP-NUM', CASE B.Name_Ty_C WHEN 'Domestic' Then 'D' WHEN 'Intl' Then 'I...more >>

Hi, I'm having troubles with the SQL Server ODBC driver 2000.81.9001.40 dated on July 22 2003. It is incompatible with our custom application developed with Centura SQLWindows/32 Version 1.5.1-PFT2. When running some queries, the error message reported by the driver is Error No: 20035 Erro...more >>

Hi all, i try to add calculated field and got this error Server: Msg 8142, Level 16, State 3, Line 1 Subqueries are not supported in COMPUTED COLUMN constraints, table 'T_TCS_STUDY'. is the only way to add a calc field is by using a function? 10x. ...more >>

I need to change a PK from non-clustered to clustered. I can do it via the gui no problem. But am having a hard time creating the SQL code to do it via scripts. I can't drop the PK as its a foriegn key for another table. Please advise Here's the schema.. CREATE TABLE [dbo].[code] ( [name]...more >>

Hi, Here is my problem: I am sending an XML as nvarchar and some other variable to a stored procedure. I have to update an element in the XML with the identity value of the current row. The only way I see at the moment is to insert the record and get the identity value. Then do an update ...more >>

SELECT DATEADD(d, Dates.Digit, '20030101') FROM ( SELECT 1 * Unit.Digit + 10 * Ten.Digit + 100 * Hundred.Digit FROM ( SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELE...more >>

I have used the Generate Script tool in Enterprise Manager to generate a script for my tables + stored procedures. I want to execute this script in my application so as to create these tables + stored procs in an empty database. If I execute the following sample script to create two stor...more >>

I have the following problem in SqlServer 2000 any help would be appriciated thanks i have 3 columns max_male_attendance (type int, null not allowed) male_registered (type int, null not allowed) percentage_of_male_registered last column hold a formula which is (maleregistered*100)/m...more >>

I have the following stored procedure which shows the recordset of all the duplicate or more records in a table called "Market". Is it possible to write some code to delete all the duplicates or more leaving just the one single record for each entry which I would eventually like to make ...more >>

I am trying to create a stored procedure that uses two cursors to GRANT EXECUTE access to users that have access to the database for User created stored procedures. The error I received after the second cursor completes its WHILE (@@FETCH_STATUS = 0) statement is: Server: Msg 16905, Le...more >>

SELECT TOP N .... return rows on Northwind test database SELECT TOP N .... return error "Incorect syntax near 'N'" on my data base Can anybody help me ?...more >>

Hi all, How can i detemine the column is a identity column if I use ADO 2.7 as client tool ? SQL: Create table testing( field1 numeric(5, 0) identity(1,1), field2 varchar(40) ) Code: Set conn = CreateObject("ADODB.Connection") conn.Open 'connectionstring' Set rs = conn.Execute(...more >>

Hi there, I have the following table: Product_url varchar(900) Product_url_reffer varchar(900) Product_url_text varchar(4000) The table lists all links (+ their description) from one product_url to another product_url_reffer. Product_url and Product_url_reffer are queried very f...more >>

Hi! I use fn_get_sql() to figure out what sql command caused a table lock. Sometimes the output of this function looks like this: select c.*, sp.sales_person, sales_person_name = sp.name from SUPPLIER c (NOLOCK) left join CUSTOMER cu (nolock) on c.customer = cu.customer left join SALES...more >>

Hello I have a slight problem, so i did a simple test that didnt work......... CREATE TABLE #Test ( a int, b int ) INSERT INTO #Test(a,b)VALUES(14,7) INSERT INTO #Test(a,b)VALUES(8,0) INSERT INTO #Test(a,b)VALUES(1,2) SELECT * FROM #Test ORDER BY a/b FROM #Test ORDER BY a/b ...more >>

I currently have a stored procedure like this: CREATE PROCEDURE dbo.GetCategories @begin smalldatetime, @end smalldatetime, @USID smallint AS SELECT CA_ID FROM Categories where (t.TS_Begin between @begin and @end) and (t.TS_USID = @USID)) GO @USID is the user ID. Now I was wondering ...more >>