Hello, Looking at another message, I was able to come up with the following query to apply a rank to ciites based on the company for example: Company1 Denver 1 Company1 Durango 2 Company1 Gunnison 3 Company2 Chicago 1 Table Structure ---------------- Business varchar...more >>

Hi, I pass a string into a SP, that is actually a bunch of int values seperated by the ',' char. (1,4,2,54,2,1) I want to pull out each int, and insert it into a table. Just started to get into SP code, and have no idea how to do this. Any help would be great... Thanks ...more >>

Hi guys, first, sorry if this is the wrong form for this, but I hate cross posting and I had to choose someplace to post. Anyway, here's my project: - Database server running SQL Server 2000 - Database server stores files of size generally > 3MB - Client program accesses SQL Server 2000 an...more >>

I need to consolidate 2 seperate databases into one. The databases are identitical and the majority of the tables have an identity column as a primary key, which columns are then referenced in other tables. Since all the tables from both databases have basically the same key value (unless...more >>

I made an UNION query of a way that didn't please me, and it would like your suggestion. PROBLEM: I need the sum of a field amount of 2 different tables contained by the same badge, I explain: I have the tables Items of Sale and Items of Production. The two have a field Item that is the b...more >>

Hi All, Is there a way in MS SQL server to know that which all tables have been modified (insertion, deletion or updation) in the recent past? For e.g., I might want to have a list of table names which have been modified since last week. Cheers, Sanka...more >>

Hello, I am using function SUBSTRING for a data type text,but when it exceeds 8000 bytes, appears the message: "Invalid lenght parameter passes to the substring function" Somebody can help me ? Thanks. ...more >>

Hi All Anyone know how I can retrieve the local system time from a SQL server and then insert that value into another SQL server's table? Thanks J...more >>

Hi Is it possible to generate full create table scripts that include default values etc in addition to the default? I find default values very useful, and often have to pass sql scripts to others for deployment. -Brian ...more >>

Hi I have the following tables and stored procedure. I need to pass a value to the stored procedure and have it use the value in a query. After running that query it will return an ID which is then used in an insert statement. At present the values in @MovieId int, @UserID int are left empty...more >>

Aaron, Thanks for following me! About 'some actions' I want to copy an order and its properties ( tables: ORDER, ORDER_PROPERTY mapping orders and properties) ( table ORDER_PROPERTY: OP_ORD_ID, OP_PRP_ID and OP_VALUE) The SP that copies the source-order > new order works OK and returns...more >>

In the local area network,using the machine name can connect to the SQL Server, but using the Ip Address can't, why? I've configured both the server and clients to support the Pipe and Tcp/Ip protocol and the server side can reply the clients side by pinging,vice versa. Any suggestions? T...more >>

How do I avoid scans when searching by date criteria (e.g., is null, max/min, etc.). I am thinking of using another column which is searchable (e.g., 0 for Null, an integer/long for datetime). Has anyone ever done this or is there a better way of accomplishing this search? CREATE TABLE [dbo]...more >>

Hi All, I have to come up with a report but am having a problem learning the basics regarding Group By. It's a fairly complex task I have been assigned so I will try and explain lightly. Company | Total Completed | Percent Completed On Time | etc... A ...more >>

Hi, i need help on inserting the row number in the same group. for example, in the SQL Server database originally, Row Number Color ================== 1 red 2 red 3 red 4 ...more >>

I have a table called T1 and primary key A, columns B and C. I would like to determine if there exist duplicate values for column B and C. A B C 1 2 3 2 3 4 3 2 3 There exist duplicate values for columns B and C. (2,3) Primary keys 1 and 3 have duplicate values. ...more >>

Is it possible to disable logging when performing an Update query with Query Analyzer? If not can it be done via bcp? The reason is I don't have enough disk space for a large update. -Craig ...more >>

Does SQL server programming support similar Dlookup() as like the one in access? in access: DLookup("[query name]![field name]", "[query name]", "") Thanx Ling...more >>

Background: Classic ASP app, takes in info about trouble tickets for a tech support team, uses SQL Server 2000. One table called Ticket has exactly one row per ticket. Table called History has zero, one, or many rows per ticket. One of the fields in History is "Status" (status examples include c...more >>

Hi, I want to set a particular port number when connecting to the sql server. Can be it be possible to set it in the connection string? Because, i do not want to use default port number to connect sql server? thanks... ...more >>

*apologies for multipost Hey we have an SQL7 box (soon to be upgraded to SQL2k) we have in-house client software that connects to a db hosted on the box via ADO + SQLOLEDB. Our client software always specifies an "Application Name=" parameter in the OLEDB connection string. The applicatio...more >>

I have a monolithic stored procedure that I have inherited and I want to break it into smaller logical pieces. I can pass some values from a parent procedure to a child procedure. My problem is that I am not sure how I can go about using values returned/derived from the child procedure wi...more >>

Hi All, This is something that I always knew I'd have to do in SQL queries, but never had the opportunity to learn: I have a query that returns a single column result set that contains the table names I need to use in the 'next' query... How do I do this without resorting to some kind of 'l...more >>

I need to take a data from a field that is actually several fields of data delimited by a CHAR(13) and out put them into seperate fields. Example (I put a "~" in place of the CHAR(13)): Field1: 'abc~def~ghi' I will need to change to: Field2: 'abc' Field3: 'def' Field4: 'ghi' The g...more >>

How can one initialise the identity value in a table. After running tests on a table, I now want to clean the table and start entering data afresh. How I can I accomplish this?...more >>

I have two tables First one is Clients FullName : nvarchar 50 ClientId : primary key Second Table is Policys ClientId Policy_Id : primary key Company : nvarChar 50 ToDate : DateTim...more >>

Hi, I have a table A with only one column of datatype varchar. This column can have numeric or alpha values. For example: 'A', 'B', '90', '60' I want to write a query that will return the datatype of the data. Is it possible to do this? Any help is greatly appreciated. Thanks, Ratna. ...more >>

I have two tables First one is Clients FullName : nvarchar 50 ClientId : primary key Second Table is Policys ClientId Policy_Id : primary key Company : nvarChar 50 ToDate : DateTim...more >>

Hi all, I want to select a column's data only if it has a value. If it does not than I wish to select from another column regardless; but I want this to be in a single column. My example: SELECT IF (col1 IS Null) col2 AS [ReportColumn] FROM TABLE1 Thanks for the help. ...more >>

I need to do an SQL query that reports the names of SQL agent jobs. Not sure how to do this, any ideas? Thanks Griff ...more >>

Hello all, Here is what I am trying to do... I have several items in a row which represent ID's and I need to query information from another table based upon that ID. For example: TABLE1: CharacteristicID1, CharacteristicID2, CharacteristicID3, etc.... TABLE2: Characteristic...more >>

I have a table with the following fields: ID (Index) FirstName LastName Email EntryDate Field1 Field2 Field3.... I like to delete all rows with duplicate email address except keeping a newest entry of that email. Therefore, the table should not have any duplicate email address by deleti...more >>

Is MS creating a new report tool to compete with Crystal Reports? Is a copy available for review? Thanks...more >>

Hi, I'm using stream object to get the call and get the return of a SP SQL 2000. I would like to know the size of a adVarWChar (actually a text type in SQL) ..Parameters.Append cmdProfileSoft5.CreateParameter("@sClientXML", adWVarChar, adParamInput, ??????, vsClientXML) Thanks, ...more >>

Hi, Here's the sp: @intUser int, @intJob int, @intPrice money INSERT INTO ClientPrices( userid,jobid,price ) values ( @intUser,@intJob,@intPrice ) when I execute this: addCLientPrices 1,1,22.50 the price in the table reads: 2250 SQL seems to be removing the decimal pla...more >>

Hello I am attempting to use the isqlw utility, but so far I have been unable to get the command to work properly. It appears to execute without error, but the output file is never created. I've also tried using isql and osql, but I can't get those two commands to execute without error messages....more >>

sql2k i need to create some t-sql scripts that run on a nightly basis to create "derived fields" in a table ... for example, a field created by stripping all non-alphanumeric characters from another field. purpose of the field is to improve peformance for a search that ignores punctuatio...more >>

Hi there. I am running into collate errors in a simple join between two tables on two separate databases and I can't figure out why: My join is based on an equal to clause between two varchar columns. If I script the tables to window, both columns seem to have the same COLLATE setting ...more >>

I have the following query. Where I do a max num. My problem is: I need to group by id1 only, but at the same time display [id]. When I am add the [id] to select list it asked me to add into group by clause, but the result is incorrect. How do I display [id] without having in group by clau...more >>

Hi, the thing is that I have data from betrive database and fields that create my PK are of data type char, but there comes to an error where the data should be unique, but due to 3 zeros in HEX after the characthers it is not unique. How can I make SQL to see where there is HEX data and wh...more >>

Dear all Your urgent help in sql select statement will be greatly appreciated I am developing a dot net insurance quotation application that uses a data-centric web services.Iwant to do a query that brings up automatically policies that are due for renewal two weeks in advance.The following is a...more >>

Hello, I need a query that would return the median from a table grouped for each region. I have a sales amount coulumn in the table and a region column. I was able to find a query in "SQL for smarties" to calculate the median value, by - Select the max sales amount from the TOP 50 percent of ...more >>

Hi, I have a C# program where I call several stored procs. However there stored proc are very long (total of about 15-20 minutes). My Problem is that when a execute the program, it kind of stalls, and its status is NOT RESPONDING. But it seems to execute the Store Proc OK. But we don't...more >>

Has anyone ever heard of any issues where SQL Server intermittently does not complete multiple complex queries for a 2 tiered database app with about 10-20 concurrent users?...more >>

On ERP database goes through a gateway. When it locks up using SQL query Analyser the commamd sp_who2 will list all the id numbers being blocked by. It can take some time to analyxe all the id numbers to find the source where the blockinbg originates. Is their a SQL script to help make this easier...more >>

I'm a newbie to SQL programming for SQL Server 2000. My experience has been limited to creating Access databases using Visual Basic 6.0 and DAO. I recently learned how to create a .mdf database using the Enterprise Manager. I've also learned how to connect to this database using VB 6 code as f...more >>

Hi all, I have a consumer services project coming up, and we will our our online product warranty registration online soon and I have a few important questions: 1) The SQL Server will not be hosted at our ISP, it will be hosted in-house here, unlike our Web Server, any problems o...more >>

Hi there, I've created the following simple UNION query: SELECT SecurityNumber, Book FROM Trades UNION SELECT SecurityNumber, Book from Positions; I'd really like to show is all the fields from the Positions table, but these don't exist in the Trades table, so they can't be included in the...more >>

BOL says that 'OPENQUERY does not accept variables for its arguments'. So, I tried using something like: Declare @qry varchar(1000) Set @qry = 'select * from OPENQUERY (<linked_server_name>, ''<query>'')' exec (@qry) Would this work? Thanks....more >>

Hi I'm trying to debug an active-x script that is run as an sql agent job. I want to see the values of some variables as teh job runs and I am trying to update the event log with some messages. Does anyone have any code to show how to do this - or better still how to see output as a...more >>

I'm trying to write a UDF that will strip out any non-numeric characters. I have the functionality working; however, it is painfully slow when I run this UDF on a column belonging to a table with many rows. Is there anyway I can optimize this? (Excuse the wordwrap) CREATE FUNCTION dbo.Tr...more >>

We want to create a stored procedure in the Master database that anyone with a valid login on our server can execute. If we grant rights to the Public role in the Master database for this stored procedure will everyone be able to execute it, or will we still have to make everyone a user of the ...more >>

Hi all, I posted this on last Friday and OJ was helping me with the queries but the queries took almost 2 minutes to run. I have almost 8 millions rows and joins with 5 different tables. I just create a quick Test table below and the SQL statement run find and return the CORRECT r...more >>

Hi, I have a stored procedure that I lifted from BOL and slightly modified it. I would now like to get it into a dataview so I can link the results to other tables and/or filter the results. Is there a way to do this? Thanks. PS: here's the SP if it helps out....it came from BOL: hie...more >>

Hello I have a user that needs to update values in one table periocally and i dont want to give this person Enterprise manager or query analyzer. Is there a way to create a view or sp for this user to update values in this table periodically I have a table called Account Columns (AccountID va...more >>

Hello, I am working on a system to track violations from an inspection. I have the following two tables for violations and follow-up violations (a subset of violations). : CREATE TABLE [dbo].[tblInspVio] ( [Inspec_ID] [int] NOT NULL , [Vio_ItemNum] [smallint] NOT NULL , [VioType_ID] [...more >>

I need to change the property LastBackup of a transaction log. Anybody who now how to do this? Thanks Geir ...more >>

I have a table that contains invoices and I need to select a number of invoices to wich the sum of the value is less or equal to a certain value. Please help. Thanks ... ...more >>

Hi, I would like to know if @@ROWCOUNT is tied with the current connection from where it is called? In other words, if I use @@ROWCOUNT in a stored proc, will it return the numbers of rows from the last operation for the current connection only, or is it taking all the connections into...more >>

hello, i can't get this to work. i only want to see 2 from each carrier. thanks! AP declare @shipments table ( carrier char(3), trackno varchar(10) ) insert into @shipments values ('UPS', '1ZOI230932') insert into @shipments values ('UPS', '1Z023093M3') insert into @shipments values...more >>

In a UDF with a parameter @param, you can do following: if exists (select * from table where col = @param) set found = 1 How do you make it work for case like: if exists (select * from table where col like '% @param % ') set found = 1 I know this is kind of dynamic SQL. Is it possi...more >>

Hi all, My challenge is to get all possible combination pairs of values from my table and their counts. I can get it done with a temporary table with a cursor and then get the count, but I am looking for a single query solution. ddl follows... ------------------table create table mytabl...more >>

Hi I am having a problem with converting a varchar column that has dates in this format 07/06/1998 would anyone know why. thanks for any advice Shaun this is my query: select convert(smalldatetime,datecreate,103) from iuk_invoice where custname like 'net%' the error:Server: M...more >>

I have a diagram with relationships upon it. I want to pull a list of all tables that are in that diagram and the details about the relationships from the systables. I found the diagram in dtproperties. Any way to get the rest? L...more >>

I am using a web server package called PerfectTracker by AvenSoft (http://www.avensoft.com). The SQL statement I am trying to execute is the following: INSERT INTO ST_ATTACHED ( FILE_ID , ROW_VER , TRACKING_ID , FILENAME , DESCRIPTION , FILE_SIZE , FILE_DATE , FILE_DATA ) VALUES ( local.f...more >>

I have a joined Table Query Select * from ClientAdminDetails as A JOIN ClientRegisteredUsers as B on A.ClientReferance = B.ClientReferance Where a.ClientReferance = '21266747514' Order By A.ClientReferance I the Operator wishes to make any changes to the data, is there a simple wa...more >>

We can get extended property(like description of column or table) through fn_listextendedproperty. Is there anyway do this by SQL-DMO. Michael Peng ...more >>

Hi All. I have a stored procedure that after it has been ran a few times need's to be re-compiled to get it working again. Is there some code that I could write into the Stored Procedure to do this automaticaly. Thanks Phil...more >>

Hello, I have moved several Access databases up to SQL but I have a problem in several of my queries. Access allows a great deal of freedom in writing functions in queries ie the IIF function and others. Is there a way to convert these functions or do I have to write something else. Some ...more >>

Using SQL2k.. I thought this was simple. I need to send emails out to unique email addresses from a table that may contain duplicate email addresses. I really don't care which row is returned as long as I only get one row per email address. In the example below I want either Bob Smith from ...more >>

Hi All, I have a source table wherein some columns are "TEXT" data type. I want to create a logging of data being updated or deleted from this table. I have a mirror table containing the same set of columns as what I have in my source table except for TIMESTMP, USER_RESPONSIBLE, ACTION_TY...more >>

Hi, I am very new to SQL server programming Currently, I have a access application where calculations are done in VBA. Now I linked the tables to those in SQL server so I have to use stored procedures For eg, if one of my stored procedures return a value, I should I write the code in VBA to refer ...more >>

Hi Please help if you have any idea on how I can get date from timestamp column.I am using SQL , VC++ . (I tried using CAST but to see 1900..as year in all the rows.But when I tested to cross check the values using DTS ,exporting to flat file..It shows me correct date.) ...more >>