Hi when I do a select against a table with a datetime column, and I want to get a list of record with today date i would have done the following select * from table where datetimefield = getdate() The problem is that the getdate() function returns a time value as well as a date value. How...more >>

I have to generate 100,000 PINs in one call to a stored procedure or a function, the PINs table might already contain many lacks of PINs PINs length must be 13 and PIN must be unique in the table tell me some way that dont take too much time to generate time to generate about 100,000 PINs thanx...more >>

Hi All, I have one doubt. If I have given my Stored Procedure name begin with the name "SP_" then when I Try to execute the Stored Procedure then Will the SQL Server will serach for that Stored Procedure first in MASTER database and then the Current Database? If That is the case. Then Why S...more >>

I'd like to display date in the following wa Jan 04, Feb 0 what can I use with my select statement to display in the above format. I couldn't find any style with the convert function thanks...more >>

Dear All Can some one help me with the following problem I have a table with two fields Int_Key and Int_Parent. Int_key is unique and primary key.Records are linked with each other by storing their parents Int_key in the Int_Parent field. A record may have any sub levels of records ( This is wil...more >>

I am working on a telecomm project. i have to generate PINs to be printed on cards.PIN specification is as follow Length:1 must be unique (there may be one lack or more pins already in PINs table has any one some function or stored procedure to do this thanx ...more >>

Hi All, I am using Windows 2000 and one of my clients have bespoke software that access's there database over an extranet, all this works fine BUT.......... I want to write a web page that takens the NT account details of the end user and passes that to the SQL server to access details. T...more >>

Hi, I have the following SP Create proc sampleSP @account int as select * from tbl_table where status = 1 and account = @account This works fine as is, but what I want to do is if the SP is passed a special value in the @account value, say 999, then I want to actually ignore the ...more >>

If I add a unique column to a table and then later need to drop the column, it fails unless the unique constraint is dropped first. Is there a way to programatically determine the unique constraint name to plug into the process in the following scenario? First add the column which automatica...more >>

Need help... Generating billing statements and have about 12,000 records. When my report is generated, each customer's records are given an invoice number for purposes of itemization. Problem is the format of the bill report only supports about 20 records per customer. Some of mine have a hundre...more >>

From SQL. Just the local drives. =20 Any ideas? TIA! Greg C...more >>

Hi, I need to stored opening and closing times in my database so I would like to ask the best datatype to use for this as I only want to store a time and not a datetime. I can only seem to be able to store a datetime. I suppose I could use a varchar but then any value could be inserted into a...more >>

Here is my code for a trigger -- CREATE TRIGGER [dbo].[trg_insert_export] ON [dbo].[export] FOR INSERT AS BEGIN INSERT [Ship].[dbo].[shipment] SET e.doc_type='8', e.doc_no=u.[reference1], e.deliver_date=cast(u.[pickupdate],datetime), e.deliver_method=u.[servicetype], e.tra...more >>

I've created a Stored Proc in the northwinds db. I can see it in the left pane, I can edit it, I've given public exec perms. Yet I can't call the proc. Query Analyser returns an error saying there is no such object. Any ideas or help is appreciated. R ...more >>

I'm getting this error every time I try to run a stored procedure I've created (a web page hit counter). I get it whether I run it manually from QA or from my ASP page via ADO: Server: Msg 128, Level 15, State 1, Line 1 The name 'asp' is not permitted in this context. Only constants, expressi...more >>

Hi all I`ve test a query over SQL 2000 Standard (English ) using Sql Query Analyzer and the results is the next message Server: Msg 242, Level 16, State 3, Line The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value But when I`ve tried to test...more >>

Hello: I want some tree in a table(like a tree of keywords) like this: id int (id of keyword) name varchar (keyword) parent int (parent of keyword) I want to make a query to get all keyword from a parent? Best regards. Owen. ...more >>

I need to construct a query with a where clause as follows( only the number of comparisons is not known up front) WHERE fruit LIKE '%apple%' or fruit LIKE '%orange%' or fruit LIKE '%kiwi%'.... what I really want to do is use a combination of the LIKE and the IN clauses, but can't figure out...more >>

I have install student version in my Home'PC, now it is expired. I want to remove it. from the contral panel i use add/remove program to do it. but it fails, even I remove all SQL server components. I still find the Enterprise manager.. everything about sql in my PC, and the sql server can't st...more >>

I need to associate an entity (in the real-word sense - let's say "actor") with a resource. This is an na:nr relationship, where na will be largish (few millions), and nr will be smallish (less than 100). I don't want to have an always populated na X nr table with "free" slots, because it will be...more >>

I have a table of organizations that contains duplicates I am trying to write a script to identify and consolidate duplicates. How can I detect duplicates? The fields I want in my result set are as follows: SELECT ProviderID, Organization1, Address1, City, State, Zipcode, DateModified FROM...more >>

First, I'd like it known that I have searched in BOL before coming here. Background: SQL Server 2000/ASP3/VBScript intranet application used to help Tech Support keep track of tickets. For each Ticket, one row in TKT_Ticket, and zero-many rows in TKT_History. Question: I'd like to know the s...more >>

Does anyone have any advice to make this table perform better I have a table that has the following fields ---------------------------------------- SessionID [Varchar(32) XML [Text(16) TTL [Varchar(16) ---------------------------------------- Here's what happens A visitor comes to the site...more >>

I have a custom-written application (VB6) in which I'm storing some stuff in an SQLserver 7 database. One of the fields is a password (the use of which has nothing to do with SQL or the sever). I'm storing it in an "nvarchar" field which, of course, leaves it wide open for viewing by anyone who ...more >>

How would I write a query between 2 tables, each having same set of unique ID's, to compare ID's existing in one but not the other and vice versa. I am currently using the following logic but the results do not appear to be accurate: select . . . from #tmp2 t2 where not exists (selec...more >>

Hi How can I pick only certain number of records for each group? For example, an employee has made 20 sales and what I would like to do is group by employee and return only top 5 sales and if possible sum the rest of the sales. So for each employee I could have at most 6 sales data with the last o...more >>

I am trying to create a stored procedure which depends on two columns not existing in table bill. e.g. SELECT e.exam_id, e.exam_date, e.nature_code, e.diagnosis FROM exam e INNER JOIN bill b ON e.exam_id = b.exam_id // From here downwards my conditional statement will read something like:...more >>

Hey there - I'm in process of identifying and cleaning bad data in an existing SQL2k database, and I'm wondering if anyone knows of an easy way to validate (not necessarily verify) whether a phone number field is correctly laid out. All I really need to know is how many dashes/hyphens are in ...more >>

I need to seperate the data in one row and split by the the commas. Does anyone know how I can compose the query? Paola...more >>

I was wondering if it was possible to make the default value of a field equal a different fields value? Here is what I mean, fields names: user_first_name : populated from an ASP page form / insert record user_ last _name : populated from an ASP page form / insert record user_full_name ...more >>

Hi all, is there a magic line that can convert and display sql query results from cube or rollup into html table using asp? or one need to hardcode reading line by line? thanks for info ...more >>

Hello, I am accessing all data on an SQL Server 7 through linked tables in an Access97 database. How can I get query results (in a recordset) from SQL7 using a normal SQL query string in Access97? I'm new to all this SQL Server stuff (as if you couldn't tell), uh, how the ^%$#* do you cre...more >>

Hi all, I am in the stage of structuring a database table for sales order. I came across some post on the newsgroups suggest the following. sku tbl itemid sku size color price 1 123 2 1 19.95 1 124 3 1 29.95 size tbl uid...more >>

I am try to using IIF, but it seems it cannot. my code IIF(MONTH(@auditDate) < 10, '0' + MONTH(@auditDate), MONTH(@auditDate)) it prompt there have syxtan error in '<' ...more >>

Hi! Imagine there is a table: CREATE TABLE mytable( aa varchar(5)) Why such statement works?: select mt.aa, 'Not existing field' as MyField from mytable mt order by mt.MyField We can even put any alias we want: select mt.aa, 'Not existing field' as MyField from mytable mt ...more >>

HI I want to query the getdate() function and return just the mmddyyyy part how do i do tha ex : it should be 05172004 for todays date , please do not forget the 0 Thank Rodger...more >>

H I want to query and return the date portion from the getdate() command, ex : 05182004 for todays date, please do not forget the 0 Thank Rodger...more >>

We have 2 indentical tables from 2 indentical databases, anyone know of a way to check to make sure that the data in the tables are the same after a period of time? The data needs to be the same on a row for row basis using a unique field for the tables. Thanks!...more >>

How can I delete data from all tables in my database? Do I really have to write delete statement for each table? Thank you, Simon ...more >>

I need a way to change a datatype from nvarchar to varchar on all tables within a database. Any help appreciated. Thanks ...more >>

Hello All, Myself and some other architects are debating a performance / scalability issue regarding high- availability & load sites and we thought we'd ask the experts: Is it more performant to: 1. Store user data, activity, state, profiles in the Session space and capture this to...more >>

hi. i've got a problem with a select. i'm writing to sqlserver.programming = newsgroup 'cause i couldn't find an explicit for only SQL problems. i have 3 tables: tblX {ID, Name} tbl1 {ID, Factor} tbl2 {ID, FactVal} The IDs on all 3 Tables are unique and the same for same record...more >>

Hi, is it possible to set a formula for a field in an Alter Table statement ? In a Create Table Statement I can set a formula for a field. thx Maddin ...more >>

I am trying to create a VIEW that combines 2 other views and sort it by one of the fields. The new view creates fine, but when I run it, the records do not come out in the correct order. Can someone help? Thanks. The view statement is below: ALTER VIEW dbo.vw_PeoplePick AS SELECT TOP 100...more >>

How do I alter a Column from NULL to NOT NULL ALTER TABLE tablename ALTER COLUMN columnname NOT NULL Issues an error. Thanks JEK ...more >>

I have posted this before without any luck... I have a LARGE SP, witch uses #tmp tables... When doing the first insert in the #tmp tables, som times it takes 10 Sec extra... this is critical, thou the SP is used ALL TIME... Any idea to what this problem can be?? Med venlig hilsen Klaus...more >>

Is there any way of makeing an audit trail while in Transact SQL? Meaning if I make a large amount of changes with an update command is there a way to capture these changes in a text file or something to review later? So I can make sure of everything that was changed? Thanks Joe...more >>

I have an SQL 2000 table with many millions of records. I would like to process this table sequentially sorted on 2 of the fields in a VB program utilizing ADO connectivity. While I have an index over these fields, it is not the primary key. I don't see how to tell VB to use this index as the pro...more >>

i have not tried this yet but will be building a new machine soon. i need to administer 2000 databases on the netowkr so i need the client tools of 2000. i also want to play with 2005 so i want to install the yukon database locally and sql workbench. so i will only have yukon db installed, not...more >>

What is the different between stored procedure, function and trigger? thanks in advance~ ...more >>

Hi there! Scenario: The table below represents a dart board round-robin tournament matchup where in this case 6 players play one time against each other Column P1 = Participant1 Column P2 = Participant2 ID P1 P2 1 1 2 2 1 3 3 1 4 4 1 ...more >>

Hi, I have a SQL statement with a lot of ORs in it SELECT * FROM table WHERE id = 1 OR id = 10 OR id = 17 .... The number of ORs vary from 1 TO 15. I could just create 15 ORs, but I don't think this is a good and efficient thing to do. I want to create a STORED PROCEDURE that will be e...more >>

I need to manage some data for a connection of users. It is possible (in programming) create a variable global of type @@ (similar to the SQL) ?? Thank's in advance....more >>

Using ADO in a VB application calling SQL Server, I have this problem: SELECT * from table1, table2 returns all columns, but columns with the same name are not prefaced with the table name. (table1.field1, table2.field1 etc...). This is not the same behavior when connecting to an Acces...more >>

Hi Guys Can anybody tell me if you are able to declare table objects according to the total rows you have in a table? I have a table in my DB that needs a temporary table built for each row. Only the table can differ in size, so the tables need to be created at runtime. If you can help that wo...more >>

How can i delete dulicate rows.ie if there exist two rows of the same data i want to delete a single row of the two...more >>

Here's a hypothetical situation. I have a table (t1) listing all of my unique clients, fields are id and name. I have a second table (t2) listing all of their phone numbers, fields are id, name, and number. Of course t2 will possibly have more than one entry for a given name. If I ulti...more >>

H I'm trying to filter a table on where one of the column values matches all values in sub table... this is wot I'm doing : SELECT * FROM tblTasks ts WHERE MeasureID = ALL (SELECT MeasureI FROM tblKeyTask WHERE typ...more >>

Hi, I'm having a little bit of a hard time getting the grasp of the UPDATE() function. I make use of the function in a FOR UPDATE trigger. Only one condition can be met any time this trigger is run. Code: ============================== IF UPDATE(BFOwnerID) OR UPDATE(ToBeDeletedDate) ...more >>

Hi We have a while loop for fetching the resultsets of a query. Within that while we want to fetch another resultset based on the values retrieved from the previous query. So I have created another connection within the while loop to fetch the resultset of the query inside. After that the while loo...more >>

I need some much appreciated advice Our application has 2 databases and we need to perform a couple of cross database queries. The database names are arbitary I tried a similar post from February where the solution was declare @x varchar(25 set @x='northwind.dbo.customers exec ('select * f...more >>

I have two tables that use Identity columns as artificial keys. If the child table holds the parent ID for a many to one relation, how can a bulk insert be achieved if the identity of the each parent item is only determined after the insert. This seems to force me to insert all child items at ...more >>

Simple question: Can you crop data? I know that you can accidently crop data by not having the correct fields lengths set but that's not vey good. i.e. table 1 Name Buzz_Lightyear crop first 5 character Name Lightyear Thanks Again John...more >>

Hi. Is there any tool in SQL Server to export table structures into Excel? I have a database with 99 tables. I want to get the details of each table : Column name, data type, length, NULLABLE, Description Is there any way to export this information into Excell? Or is there any SQL query t...more >>

hi all does anyone know any quick reference guides for MS SQL querieing? thanks ...more >>

Using isql, is there any way to have one TSQL script invoke another? I guess I can do something like !! isql -E -i script.sql which would be OK if using a trusted connection, but not so good if you have to pass -U and -P arguments along instead. I'm looking for something like Oracle'...more >>