I have a table that has character ranges in it, such as FIELD1 FIELD2 1234 1235 1235 1841 1842 1849 Those 2 fields are fixed length char fields. I need to do queries such as SELECT * FROM TABLE WHERE @test BETWEEN FIELD1 and FIELD2 How sh...more >>

Hey im working on a worldwide system and i am trying to work out the best approach to how i am going to Segment the world / Countries / States / Provinces - Suburbs | Citys??? Any ideas cause i neet for people to be able to select on any level i they may just select a Country or maybe the full...more >>

I am trying to execute a query string in an execute command and are getting the following error: Server: Msg 203, Level 16, State 2, Line 55 The name 'INSERT INTO tblDHMinerals (Dataset, Hole_ID, Depth_From, Depth_To, Code_System, Min_Code,Load_Date, Loaded_By,Modified_Date,Modified_By...more >>

oj gave me this and I know it's gonna solve my problems (cuz he's always right!) if I can just know how to put this in correct syntax: 1. Create a staging table: select top 0 * [Mink].[dbo].[TBL_Catalog_stage] from [Mink].[dbo].[TBL_Catalog] 2. Bulk insert into stage: sqlstr =...more >>

Dear All, I have spent the last 6 hours and I can't figure this out but I know have to admit defeat and seek advice from higher mortals. I am trying to post details from an ASP web form to a SQL Server database. The data will be posted into the database through a stored procedure. The stored p...more >>

I would like to create a job in which one of the steps is to call another job. Is this possible? If so, what is the syntax? J.R. Largo SQL Tools The Finest Collection of SQL Tools Available http://www.largosqltools.com ...more >>

I have the follwing proc but it isn't doing what I want it to:) It updates entries for a link directory once they have been approved. I want it to update the totalLinks column on the link category and any parent categories. The Link Table contains a column ParentCatID which is its parents catID. ...more >>

Hi, I have two databases -- Main database is on SQL Server 2000. I have another database that's on MDSE 2000. I want to take the tables, stored procedures and relationships from MSDE database and dump all of them into the Main database on SQL Server 2000. What is the best way to do this? ...more >>

What is the SQL server equivalent of Instr.. i.e. True if StringX in StringY ...more >>

In Short, I have Inventory table with two columns Quantity and Precision. When I do select CONVERT(NUMERIC(10,2), Quantity), Precision from Inventory This works fine. My problem is, I want numeric presision based on Precision column something like convert(Numeric(10,PRECISION),Quantity) rathe...more >>

I have to write a couple queries in which I build a file with product information as the column names. In my source table, I have rows with a product code on it. I join that to another table with the business area, report group, and family class on it. For example: widget1 has a product ...more >>

Hi, I have a question. Let take this table: Table1 (i int, update datetime) with this in it: 1 01.01.2000 1 02.02.2000 2 03.03.2000 2 04.04.2000 5 05.05.2000 6 06.06.2000 6 07.07.2000 and I'd like a query to return 1 01.01.2000 2 03.03.2000 5 05...more >>

I write a program for SQL Server 2000 backup and restore, the backup is ok but the restore can't work because share deny, my SQL sytanx: restore database mydb from disk='c:\myback.dat' this program is a part of my ASP Web application, the ASP connection is: connstr="driver={SQL Ser...more >>

Hi all, I plan on adding about 10000 tables in a database using SQLDMO. I did the following steps for a 10000 times. 1) Create the table using Database.ExecuteImmediate 2) Refresh the Tables Collection Object 3) Get the Table object, use ImportData to bulk copy the data to the database 4)...more >>

I have a very large SELECT query (involves over 20 inner and/or outer joins with a couple of subselects thrown in). I have executed this query in Query Analyzer directly and then I have wrapped it in a stored procedure and executed it. Direct execution of the query returns a resultset in...more >>

Hi, I'm using SQL Server 2000. When I return all rows from a table it doesn't give me a count of the number of records, like in Access for instance. Is there a way of quickly finding out how many records there are in a table? thanks. Raul. ...more >>

I need to know the number of distinct col1, col2 pairs in a table. The following does not work: select count(distinct col1, col2) from table This one does not work either: select count(*) from (select distinct col1, col2 from table) I do not want to use a view. Does anyone know a solution ...more >>

I just ran into a peculiar behaviour on MS SQL Server 2000 (Std.) 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 3) The CASE statement truncates a returned s...more >>

I am using dynamic queries and am having problems passing the values from the inserted table ( in a trigget ) to my exec(@sql) statement. I am currently trying to make a temp table that is accessable from within a execute statement. eg. SELECT * INTO #tempInserted FROM inserted Ho...more >>

This stops when it finds a duplicate record. What 'easy' thing can I do to get it to just skip the duplicate row and continue with the rest of the file? sqlstr = "BULK INSERT [Mink].[dbo].[TBL_Catalog]" + _ " FROM '\\Server05\c\Catalog1.txt' WITH (CHECK_CONSTRAINTS, FIELDTERMI...more >>

is there a way to stop the first value from repeating until it changes. EMLOYER EMPLOYEE NAME Test1 Jane Doe Test1 Joe Smith Test2 Larry King Test2 Mary Kin What I want it to look like is this EMPLOYER EMPLOYEE NAME Test1 Jane Doe Joe Smith Test2 ...more >>

Hi all, I was curious if anyone has an example that would compare the same PC hierarchy one month against another to see which children had new/different parents. I.e. EmployeeA in January rolls up to EmployeeB. In February EmployeeB is fired and EmployeeA now rolls up to EmployeeD. I ...more >>

Hi all, I was wondering if there is a way to pass a set of parameters into a SP where the number of parameters is not always fixed? For example, if I am updating a user, I could either pass in all of the parameters (username, password, address, phone) or just update 1 or 2 (address). Is ther...more >>

I need to match firstname, lastname in two different tables so that i can check to see if an account has expired (tblcustomer has the 'expires' date): exsql = "select tblcustomer.Firstname,tblcustomer.LastName,tblUsers.Firstname,tblUsers.L astName,tblcustomer.expires from tblCustomer INNER J...more >>

I have 100s of tables that have the same prefix and I want to move them to a new filegroup. Is there a method to use a variable in an ALTER TABLE statement to build the table names on the fly? Jason...more >>

i could swear i used to do this kind of thing in M$Access all the time: SELECT NameLast + ', ' + NameFirst AS NameFull FROM employees WHERE NameFull = 'Smith, Joe' but SQL Server keeps telling me 'NameFull is an invalid column name'. is there some bit of syntax i am missing, or is this jus...more >>

How do I find the list of SQL Servers available on my network. I tried sp_helpserver but it does not seem to show all the SQL Servers. Thanks in advance. ...more >>

I'm having trouble running this query on my server joining table tables on a linked server. SET XACT_ABORT ON GO BEGIN DISTRIBUTED TRANSACTION UPDATE Bond Set ItemNum = C.ItemNum FROM Bond,CDIMS.CDIMSDemo.dbo.Charge C WHERE BondRecId = (SELECT B.BondRecId FROM CDIMS.CDIMSDemo.dbo.Bond B...more >>

By default SQLDMO does not copy tables description. Does this work: object.Script( 4, 4194304) is this the right syntax for OR logical operator to copy tables with extended properties description?? What is the correct way to use the OR operator...more >>

I have a table that has a column which is a primary key and has a identity defined on it( increment = 1)... When a row is inserted into the table the column is automatically assgined a value, how can i found out the value of the row that has just been inserted? I would like to do the insert an...more >>

SQL 2K I created my cube, process data and played with it. My question is , how to make this accessible to my users?? will they have to get into Analysis Manager ?? Is there any automated job to refresh (process) my cube on a daily basis. ?? Thanks Sh ...more >>

Hi, All, I have a column called Mins and type is float. I run the query and get the summary of Mins. But problem is the results like 401118.70000000001 or 298919.98999999999. How can I only get 401118.70 or 298920.00? If I can use convert(varchar(15),sum(mins)), will lose something? or I ...more >>

Hi, I am working on a stored procedure and it requires cursor to be used in the procedure. For some reasons, I can't close the cursor even using both CLOSE and DEALLOCATE at the end of the procedure. Anyone has any idea what might be the cause. The procedure needs to update the data in a ...more >>

Hello Everyone! I am trying to programmatically derive the columns from syscolumns that constitute any existing index (as defined as existence in the sysindexes system table). Documenation states that the sysindexes.keys column is the "list of the column IDs of the columns that make up ...more >>

What is the difference between FOR and AFTER in the CREATE TRIGGER SYNTAX? CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS [ ...more >>

Hi Everyone-- Please help. Do you have any idea how to optimize (AKA speed up) this query? select distinct line_type, line_item, uom_cd from order_mgmt.dbo.job_sheet_other where line_type != '' and line_item != '' and uom_cd != '' order by line_type, line_item, uom_cd asc ...more >>

Say I have 3 tables A, B, C. Table A has primary key identity column A_primary_key. Table B has primary key identity column B_primary_key. C table has two columns: a foreign key column primary_key_from_A_or_B, and a bool column is_A. How can I write a constraint that in table C: 1. if is_A ...more >>

Hello, I am having problem writing a recursive stored procedure for a Content Management System and am hoping that someone can give me a hand with it. I have posted (below) a script to recreate a table and data to test it with, and I've included my sample stored procedure that isn't working as...more >>

Table structures are: Table1 Key,Name,Description,Units..... 1,'A','X1Y1Z1',.... 2,'B','X2Y2Z2',.... 3,'C','X3Y3Z3',.... 4,'D',X4Y4Z4',... etc... Table2 Key,Datetime,Value 1,'2003-08-01 00:00:00',23.23 1,'2003-08-02 00:00:00',-3.0 1,'2003-08-03 00:00:00;,-4.34 ...... 2,'2003-08-0...more >>

Hello, I need to create a sql statement that shows the value of a varchar field that has 3 string and 6 number XX-999999, and shows a special character when lose the consecutive, for example: P10-00001 P10-00002 P10-00003 **P10-00005 P10-00006 P10-00007 **P10-00009 **P10-00011 P1...more >>

Hi, I need to write a proc that will contain several select statements. I need each select statement to output its results to a pre-named text file. Any suggestions? Thanks, Ron ...more >>

Can you have more than one clustered index on a table? If so how can I set it up?...more >>

Does anyone know if and when a .NET SQLDMO dll will be available?...more >>

Just hit a snag with XML parsing. Here it goes, in order to be able to pass special characters (&, <, >, " ) in a XML document which I pass as NTEXT variable in a stored procedure, I need to code them. This part is not so bad, I can do a replace with the appropriate code before creating ...more >>

Hi, I am trying to transfer some data from an old database to a new one however the table structure in the new database isn't exactly the same. I was wondering how I do this! I would also like to keep the same identity in the identity column of the old Database and transfer it into ...more >>