Hi All, Can anyone tell me how to get the data type and other details of a column through a database query in sql server. We are using sql server 6.5. For e.g. I’m running a query like this “select a.name,b.name from sysobjects a,syscolumns b where b.name like 'Property_Type%' and a.type=...more >>

How do I remove everything on both sides of these characters "<>"? They're in different positions in the field and inside is the value I want, the email. Thanks....more >>

Hello, I was wondering how best to write a valid query that Assimilates the following query. SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id; Basically I would like to know how to create a query that returns a distinct value using an alias. an...more >>

How do I delete everything in a field that's to the left of the "<" character? I have thousands of records and the "<" character is in different positions. Thanks....more >>

Hello, I was wondering how best to write a valid query that Assimilates the following query. SELECT id,COUNT(*) AS cnt FROM table_name WHERE cnt > 0 GROUP BY id; Basically I would like to return a recordset where the distinct(unique) value is an alias. Is this possible? thanks ...more >>

Howdy, I have read in the VC++ help that you can debug your C++ and step into your stored procedure (having the VC++ debugger "handoff" the SQL Debugger), or that's what the docs seem to imply. Has anyone gotten this to work? Can you provide an example? Please let me know - thanks. NO...more >>

Hi all I'm trying rewrite this trigger, originally wroted for Interbase, to MS SQL Server, but I can't. Anyone can help me ? Interbase version =========== CREATE TRIGGER TG_INS_BOOK_00 FOR BOOK BEFORE INSERT POSITION 0 AS DECLARE VARIABLE SEQ_REG NUMERIC(15,0); BEGIN EXECUTE PROCEDU...more >>

I am using SQL Server 2000. Sometimes I can see SQL Server Agent is stopped. where to findout the reason for this ? Thanks Rudy Mark ...more >>

Hi all, I have set all my databases to backup on a nightly basis, including the master, msdb etc... I keep getting the following error message in the event log : Event Type: Error Event Source: VSS Event Category: None Event ID: 6004 Date: 15/10/2003 Time: 00:02:10 User: N/A Comp...more >>

hi, lets say i have a product table and a product name table (cause i handle product names in different languages and synonyms, so there is a restriction on the language) now i want to let the user search in this product name table (lets say 1.000.000 records) the name is varchar(50) and the o...more >>

Hello, I have worked out the following SP to get stats from a table that holds browser, IP, and other info on each access to the banner. I am trying to get the last 30 days accesses/Clicks, and by the hour for Today. I have over 100,000 Hits and it takes 30 Seconds to run. I am hopping t...more >>

I am wanting to get the space usage statistics on several tables within several databases on a single SQL Server. I could do the following: EXEC dbname.dbo.sp_spaceused Table1 EXEC dbname.dbo.sp_spaceused Table2 ... EXEC dbname.dbo.sp_spaceused TableEtc But all the results will be separa...more >>

I have a question can I insert a file in a image data type. Like "C:\test.jpg". How would, if possable, do that w/TSQL? BOL didn't make much sense, but maybe I am reading it wrong?...more >>

I have about 30 tables in SQL 7 DB. Each table has a primary key in the ID column. The user table contains several columns such as ID email address, firstname, lastname,etc. In the remaining tables, I am (programmatically) inserting data along with the ID of the record which is create...more >>

I have finished my MCSE schooling, and am wondering if there is an app out there that will allow you to study and practice on T-sql. Something that will allow me at home to parse for syntax without being on a server? Something I could practice at home with. Thank you all....more >>

This simple query: SELECT ShipCountry, CustomerID, COUNT(*) AS Count FROM Northwind..Orders GROUP BY CustomerID, ShipCountry ORDER BY ShipCountry, Count, CustomerID returns something like: ShipCountry CustomerID Count --------------- ---------- ----------- Argentina O...more >>

Hi- I am tring to programatically get todays date using Transact SQL and getdate() which returns a date with a time stamp on it, however I want a date with no timestamp or at least with a timestamp pf 12:00AM Any ideas how I can do this, Thanks very much, Jez ...more >>

Hi there. U have table structure shown here: category (Int - AutoIncrement) parent_category(Int) Title(string) 1 0 HOME PAGE 2 1 FIRST DEPTH 1 3 ...more >>

Is there a convenient way to write an SQL statement to grant exec on "all storedProcedures" to [a_windows_user]? We all know that it's ok to grant exec on sp_foo to [computerName\userName] but it would be terrible if there're hundreds of SPs to be granted the exec right. Just wonder whe...more >>

Is it possible to use variables in the Connections string for an OPENROWSET call. ie. Select * FROM OPENROWSET('SQLOLEDB',@TheServer + ';' + @TheUser.........) Or does the OLEDB Call Expect String Literals Cheers Dean ...more >>

I have a work table into which I insert data extracted from a mainframe. This row is then JOINed on the Xref column from another table. Each row stores one identifier, in this example case the social security number. CREATE TABLE dbo.Tbl_WorkOtherIdentifier ( Xref decimal(7, 0) NULL , -- ...more >>

Hi All DDL's are at the bottom of this post I need to find data inconsistincies in some of the reansactional tables that i have The field PartIdSeqNo for each unique combonation of PartId1 and PartId2 should start at one and increase for each transaction added to the table My issue ...more >>

Hi Can we lauch the Query Analyzer from the application? Thanks Frank...more >>

Hi, If the identity column grows to the maximum integer value, how to deal with? Thanks in advance Frank ...more >>

If I login to my SQL Server, I can make a query with sp_who2 to see which clients are logged in and which program the client uses. This works great but not with Win98. The programname is not available. I get only squares, quotation marks or other strange characters. Does somebody have a solut...more >>

How do I convert date parts back into a datetime data type? For example, if I have the following variables @Month int @Day int @Year int how would I create a datetime datatype? Thanks ...more >>

I need to add column into a table. My problem is that ALTER TABLE command adds column to the end of field list only. Is it possible to insert column into a table positioning it in the middle of the field list (using T-SQL)? Thanks in advance. Michael Gesser ...more >>

I have a simple query question. Currently I have a query that joins a UDF that returns a table to a sub query of a number of secondary tables. This query seems to run forever, but if I dump the results of the udf into a temp table and use joined to the sub query the results are as expected. Can a...more >>

Hello: I am intentionally posting this message here; it was posted under OLE DB news group but it does not seem to be as active as this fourm. Sorry. I have a table that has as the primary key a column that has BigInt data type and uses the Identifier clause. When I link this table in...more >>

We are running SQL 2000 on 18 branches and I want to set up email notification if backup or replication failed where can i found step by step instruction to set it up. Thank you. ...more >>

I have an application (ASP dot net app) that runs SQL stored procedures, when an option on the screen is clicked. It forms some of the variables, which I then use executereader to execute the stored procedure, and then bind the results to an object in the dotnet application. I have a separate...more >>

I have a function that takes a var and returns a table, consisting of 3 fields: AgreementNumber, SiteNumber and BuildID. The function is essentially a parsing function. It takes a string such as this and returns the data in a table format: 'A=2003006,S=002,B=9777|A=2003006,S=003,B=9865|' ...more >>

We have a sproc which is being called quite a bit and has been identified as being a bottleneck. The sproc isn't overly slow, but it is called a lot, so we are interested in tweaking it as much as possible. In doing so we've observed a very odd behaviour. Basically, while the code is running...more >>

Ever have one of those days? I've been staring at this function definition for the last hour or so and I don't see where my problem is. The goal is strip all the "chaff" from the data that's in our phone number fields in our database. I figured the easiest way to handle this would be with the use...more >>

The following sql works fine if there are payment records in the payment table, but if there are no records there, the AmountOwed = 0. How do I deak with the null? SELECT (SELECT SUM(SalesPrice) FROM tempSalesDetail WHERE (te...more >>

Trying to create a trigger that will fire when a new record is added to an Employees table, columns include: (EmployeeID as int primary key, EmployeeLastName as varchar, EmployeeFirstName as varchar) i have a second table called EmployeeChangeHistory with columns: (EmployeeChangeID as i...more >>

Hi Guys, Does anyone out there know the best pratice when using .Net for development. Does each API or a web click have to have its own database or what. I have a sitution where the developers in a group that is using .Net for application web development creating databases like crazy ...more >>

I have to upload an entry into a table, and then upload related data (including the primary key of the first entry as an FK) into another table. I would like to do this within a stored procedure, can anyone tell me how this can be done? Also, I need to upload a big batch of entries at once, is...more >>

Hi, I have an attribute with a datetime data type that I want to convert to varchar on certain instances. After converting it to varchar, I want to set it equal to a constant. How can I do this? Below is my sp... After the line that reads CONVERT(varchar (9), MaxOfqual_dt), I want to set ...more >>

Using bit field in SQL 2000 sometimes returns different values than are expected. True sometimes come out at -1 while False sometimes come out as 0 Is there any way that you can set SQL to always return either True and False? Any suggestions would be helpful Thanks David Taylor ...more >>

I have several insert statements like following. But the rows in tblRptQcDocIndex table are not in the order they are supposed to be, e.g. "A" - "Z". In other words, they are randomly in a order like "M" - "S", "I" - "L", "P" - "R". Any idea? INSERT tblRptQcDocIndex SELECT * FROM tblRpt...more >>

I have a table gId Name communityId 001 james 100 001 james 200 001 james 300 002 johnn 100 002 johnn 200 002 johnn 300 what would be the sql statement to produce: gId Name communities 001 james 100,200,300 002 johnn 100,200,300 -- Posted via http://dbforums.com...more >>

Hi, I work with sql server 2000. Wondering what's the best practice to shrink the size of tempdb. I don't want to interrupt productions db running on the server. JJ ...more >>

Hi! I have a problem with a query which is executing very slowly under certain conditions. When I use query like this: SELECT * FROM MyView WHERE Column1 LIKE '%' AND Column2 LIKE '%' AND Column3 LIKE '%' it executes ok - it returns about 5000 records always in less than 4 seconds...more >>

Hi, How do I map a user-defined type when I'm calling a stored procedure that uses one. For example, for a regular data type I would map it in C# using SqlDbType.Int. I'm stumped with this one as I can't find anything on it anywhere. Thanks ...more >>

My company wants to see when specific users logged in and out of a specifice database and track this activity. We have a workforce deployed over a large area that is supposed to be updating info in 'real time' as we spent a small fortune getting them all laptops and cell WAN cards. Can anyone p...more >>

Hi there, I'm new to PL/SQL and have been given the following procedure: create or replace procedure otime (hoursworked number) as overtimehours number; normalhours number; message varchar2(30); begin normalhours := 35; if hoursworked > normalhours then overtimehours := hoursworked - norma...more >>

is there a function equivelant to SUM() that can be used for returning the total value of character fields. Example Select FirstNameList = SUM( FirstName + ' ' ) from contacts where BusinessID = 123 group by BusinessID if there are three rows where BusinessID = 123 with the FirstNames o...more >>

Hi, Anyone see this error msg before? I used asp file to create olddb connection to sqlserver 2000. It was working before. But it suddendly does not work. The error message is ADODB.Recordset(0x800A0E7A) Provider can not be found. It may not be properly installed Help please!!...more >>

Can anybody tell me what is the diff between ansi sql and tsql, which one is better over another. ...more >>

I am inserting data into a temp table which come from a table based on 3 diff criteria. using union. e.g. inset into #mytemp select * from tab1 where < criteria1> union select * from tab1 where < criteria2> union select * from tab1 where < criteria3> Now I just want to check is t...more >>

Is it possible to do the following query? declare @Year varchar(5) set @Year='02/03' SELECT case when @Year='02/03' then AccountNumber between '50' and '59' when @Year='01/02' then AccountNumber between '40' and '49' else AccountNumber end FROM Master WHERE (Number = '71') ...more >>

Greetings one and all! Here's a nice simple one for you. I am running the following query: select s.member_id, sum (s.amount) local_amount, sum (s.currency_amount) currency_amount, s.currency from owner.table_name s where currency_amount between -5.00 and 5.00 and currency_a...more >>

I have 2 tables: Member and GroupBelong Member has 2 fields: memberID and MemberName GroupBelong has 2 fields memberID and groupID The members belong to several groups. They are joined by the memberID. I need to delete the members who do not belong to ANY group???? -- _______________...more >>

Hi Everyone, Is there an easy way that I could pass a filter (variable) unto a view via stored procedures? I would appreciate your suggestions. Benito...more >>

Please can someone help me. I have to port a helpdesk application to SQL Server from Access 97. Within the current application a domain user list is obtained by means of API calls and this is used to populate the Users table. What is the easiest way to get this information from the dom...more >>

I have a delete trigger in a table (table A) that updates a second table (table B). Table A is a log that tracks money being moved from one item in table B to another. Table B is basically a table containing our annual capital equipment budget items. Users have the ability to move money from ...more >>

I'm new to triggers. How do you write a trigger that will prevent the user from deleting a records where the value in field MYFIELD in table MYTABLE = 77 ? Thanks....more >>

hi, I have two Sql servers running sql 2000 enterprise edition I am restoring a pubs database from the backup(it is of pubs database on server A) on server B and i named it pubs_restored. On another box on Server A in pubs databse I am creating a publication of transactional replication type on pu...more >>

I ran a query INSERT INTO t1 (c1, c2, c3,) SELECT c1, c2, c3) FROM t2 I had previously created t1 table using sa login. I received error invalid object name on the t1. What am i doing wrong. Table t1 exist and was created by and owned by sa. Please help. ...more >>

Hello All Gurus, The problem that I have today is, Is there a way to extract the last modification date on a table ie... the date that the table was either altered or had records added to it. The create date is no problem as that is avail through task pad or properties Will *** Sent via De...more >>

Dear All, I am migrating data over to a new system written in a completely different lauguage, the database is not SQL but one completely different. In this DB we have a persons table, which can be summerised as follows; - 1 Smith Peter 2 Smith ...more >>

Hi, I am trying to figure out how to find the last date (that will be the date of sunday) in a weeknumber. This means that week 42 (2003) should return 20-10-2003. I've tried with something like this: dateadd(dd,(42 * 7), dateadd(dd,-(7-(7-(datepart (dw, '1/1/2002')-1))), '01-01-2002...more >>

I understand that I can use a debugger within SQL Server but I have not been successfull in finding out how to use this product. I am using the word "debugger" in the help, which provides two links, one for the Icons and one for "transact-sql" which tells me to go into enterprise manager and ri...more >>

I am trying what i thought was pretty basic query on a table that exists in our accounting system. All I am trying to do is retreive transactions posted from a certain time on a certain day. What is causing me problems is that the date and time values are held in seperate datetime columns. The...more >>

SQL Server 2000 Running in QA: declare @id int, @min numeric set @min=-10 select @id=id from confirmlog..surveymonitor where performed > dateadd(mi, @min, getdate()) SELECT Count(*) as hitCount from confirmlog..surveymonitor WHERE test<>1 AND id > @id This count(*) returns 0 If I ...more >>

Hello All, I have a few queries to ask. USE myDatabase2 SELECT * INTO table1 from myDatabase1.DBO.table1 I know that the above statement creates a table in myDatabase2 My Queries: 1) What happens if myDatabase2 have two File Groups? 2) Does the above statement create the ne...more >>

What is the syntax to convert an image field into something readable? I assume cast. The problem is that we store text information in such a field. The display works fine with our frontend app but when I try to get the content of this field with Query Analyzer I just get 0x7465737420746573...more >>

i have an error 9003 when i attach the mdf file is there anyone have a solution my email is firashaddadin@hotmail.com...more >>

I'd like to knom how i can display a message using raiserror in trigger. Is it possible to display the message of the trigger in a client application programmed in visual basic .net ? Most of the time the message is prefixed by the name of the server like this :[Microsoft][Sql Server][ODBC]...... My...more >>

Hi, I need help on modifying this SQL query. select count(*)as cnt, sum(a.shmsharect)as shares, b.cdename from shrsharemaster a, shrcodemaster b where b.cdecode = a.shmtype group by b.cdename The above query returns: 4 0 Individuals 4 27000 Nominees 1 0 Others ...more >>