This could be an easy one I have a table with 3 fields as follows: 1) Userid - int (This is the key identity field) 2) I94Date - varchar 3) I94DateD - smalldatetime I SEE TWO OPTIONS FOR WHAT I NEED: OPTION # 1 I need to update the I94Date for all the table, so that for record # 1 the ...more >>

I have 2 tables in 2 different databases, and I want to compare the data of 2 tables if they are identical. 2 table structures are identical. Here's my attempts: SELECT * FROM DB1..TB1 WHERE NOT EXISTS (SELECT * FROM DB2..TB2); If both tables have identical data, it will return no rows. A...more >>

What's the correct t sql syntax for merging 2 tables (same structure) into one, either a new table using something like 'select... into' or either of the existing tables? The tables have, respectively, 701 rows and 11 rows - the new or replacement table should have 712 rows. Thanks for any he...more >>

Will these two programs work together? ...more >>

I have written some code doing bulk copy operations from inside my application using SQLDMO. It works fine but I have the following questions : 1. When importing a data file created by the ExportData operation is it possible to specify a table that does not exist in the DB and which will be crea...more >>

Hi, I would like to ask if someone knows how to make a column in which I store passwords CASESENCETIVE? Thank you in advance! Viktor --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.729 / Virus Database: 484 - Release Da...more >>

Can anyone point me in the right direction for getting detailed information on how to use the xp_cmdshell stored procedure for copying files on a corporate network between two computers which are not SQL servers? I have read all of the discussion threads regarding the xp_cmdshell and have gle...more >>

I'm looking for utility similar to this one: http://www.databasejournal.com/features/mssql/article.php/1462341 .... but for SQL Server 2000. Basically, I'm having trouble importing a fixed-width text file because when trailing columns are blank, the rows are not padded. In other words, ...more >>

i have a bunch of data that has various numbers of preceding zeroes - can someone help me with a query that will trim off all leading zeroes. i know how to use the replace function, but my problem is that sometimes there are five leading zeroes, sometimes one, or none, etc. example of data an...more >>

I generate a report in a web browser calling a sql 2000 stored procedure. There are no joins or anything fancy within the stored procedure, but I've been asked to do something I'm not sure how to do. The stored procedure is generated from a table that has last year's and this year's data for...more >>

Hi all, I am a new user of SQL database. I would like to ask the comment for showing the structure of Tables in the SQL Analyzer. Thanks a lot, Ray ...more >>

We ran this query in Access to get a pivot table in Excel. Now the DB was tranferred to SQL-server and we cannot convert the query to SQL. Any help? Outstanding: Choose(Int(DateDiff("d",mktable.DispDate,Date())/30)+1,"<30 days","30-60 days","60-90 days","90-120 days") Thanks ...more >>

Hi, I want to make samething like this in TSQL (SQL server 2000) array colname('col1','col2','col3') SET SQLstring='CREATE TABLE (' foreach (colname as cname) { SET SQLstring=SQLstring+cname+' varchar(20),' } SET SQLstring=SQLstring+')' EXECUTE sp_executesql @SQL so is possibl...more >>

I have a migration procedure that fires off dozens of other stored procedure to create tables, relationships, etc. Can I also create triggers on those newly-created tables by using those same procedures? I tried syntax similar to below, but it of course didn't work. Thank you \\\ ALTER P...more >>

I would like to be able to have a different formulas in each row of a table. Right now I use a cursor and select each formula and execute the select and insert values into a table, does anyone know a better, quicker way of doing this?? Thanks in Advance. G....more >>

I have a query tuning problem. I am putting in all the data structures, queries, execution plan. Please help me in turning the query. All feedback is appreciated. -- Table 1 CREATE TABLE DIM_ENTITLEMENT ( ENTITLEMENT_ID numeric(18, 0) NOT NULL, ... several other columns CONSTRAINT DIM...more >>

In Sql Server 2000 I am tfs as a user and login. I have no server roles. I have no access to the vdw database (dbo is sa). If, using query analyser, I try to connect, using Sql Server Authentication - I cannot connect to vdw (can't issue "use vdw" or choose vdw from the drop down box) ...more >>

Hi All! If I have QUERY like this then which will be faster? SELECT A.COL1, A.COL2, B.COL2, B.COL3 FROM TAB1 A INNER JOIN TAB2 B ON A.COL1 = B.COL1 WHERE B.COL5 = 'VAL1' AND SELECT A.COL1, A.COL2, B.COL2, B.COL3 FROM TAB1 A, TAB2 B WHERE A.COL1 = B.COL1 AND B.COL5 = 'VAL1' Both ...more >>

The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified. So I insert it here: SELECT TOP 100 var1, var2 However, i want to return all the rows, I can't seem to do 100% because % is a wildcard. How do I get around this?!?!?! ...more >>

I need to extract data from SQL Server. I am using ISQL command line utility to output query results to a file --> isql /o. I would like the file to contain data only, no column header, and no feedback. What is the SQL Server syntax to suppress the column header / feedback data? The Oracle...more >>

Hi I would like to now wheather we can pass Arrays to SQL Procedure and will it handle .I am trying to execute from a different application Thanks ...more >>

What can I use instead of CASE? Thanks, -- David Lozzi Web Applications/Network Specialist Delphi Technology Solutions, Inc. dlozzi(remove-this)@delphi-ts.com ...more >>

ASP Developer/Company who has experience in Java Script and Microsoft SQL Server sought urgently for ongoing contract work. Initial template website 95% finished, but current developer can no longer afford to provide enough dedicated time, and would not be able to develop the other 35 clone ...more >>

Hello, all. We need to drop all user tables in a SQL Server 2K database all at once with a piece of code. However, many of the tables have foreign key relationships, and it gives errors until we drop the constraints. Any way to drop all constraints on all user tables, and then drop all user table...more >>

Within a stored procedure, I need to retrieve XML over HTTP from an external URL, then load some stuff into a recordset and return as such. Like this, sort-of: -- ******************************* create procudure queryremote (@param) declare @xmldoc varchar(2000) set @xmldoc = (GET 'HTT...more >>

I have written the following query to show me records where the last character for the data field is a number, if its not a number, then it shouldn't retrieve that record. I am getting errors since for some records the last value isn't a number, it could be any alpha like 'a','b', etc. Ho...more >>

With the query (SELECT MyNumbers FROM MyTable) I get a list looking like below. 2,75 16,28 0 2,86 0,506 26,18 0 I would like to format the output with a fixed number of digits as below 2,75 16,28 0,00 2,86 0,51 26,18 0,00 How is that possible? TIRislaa ...more >>

Hi, I am using VB 6.0 and SQL Server 2000 for developing a custom project. I would like to know if it is possible to use a transact SQL statement to export data stored in a recordset to an Excel file. Something like: ' Get Excel filename in FileIn Dump Recordset into FileIn ' Recordset co...more >>

Hi, all, what's different between select * from tableA, table B where TableA.a1 = tableB.b1 and select * from TableA join tableB on TableA.a1 = tableB.b1 Which one is better? Thanks Brian...more >>

Hello NG, i try to call a stored procedure with parameter frm anotherone lite that: .... and ObjSnr in ( exec sp_FEWO_hole_Kriterien @Kriterien ) .... But it dosn works...where is teh error Thanks Michael ...more >>

Hi, I need both column names and data. So is there a way to SELECT column names along with the stored record for each column? I know we can do that as "AS", but since somehow this table has more than 30 columns, I don't feel like doing them one by one. Cyont ...more >>

I have 3 fields REF, ID and OFFER REF is the id of a article ID is the ID of the client OFFER is a offer made by the client Now some of the clients wonder on which position they are in the list of offers. Suppose Art, Id, Offer 888, 100, ?55.00 888, 108, ?67.30 888, 145, ?52.70 888,...more >>

The company I work for is in need of a Senior level SQL coder/ report writer. Must know your way around derived tables, subqueries, etc. Must be able to perform aggregations from multiple tables. If you are still @ the temp table/ cursor level don't bother to apply. (Of course sometimes th...more >>

I need the end-user to be able to select which column of data to sort by and the suggested implementation is with a CASE statement inside the ORDER BY clause. The following fails with "Invalid column name 'NumPosts'" when I try to include it in a stored procedure DECLARE @SortCol char(40); ...more >>

Hi Please review my query below INSERT INTO employee (emp_full_name, emp_first_name, emp_middle_name, emp_last_name, emp_suffix_name, address_line1, address_line2, address_city, address_state, address_zip, work_addr_line1, work_addr_line2, wor...more >>

Hi, I want to retrieve a count of a level. All Region (1st level) Asia (2nd level) China (3rd level) Singapore Taiwan Europe Belgium France Spain Italy America Canada US Depending on where i stand i want the coun...more >>

Hi, I have a situation where I need to retrieve the TimeZone info using 'HKEY_LOCAL_MACHINE','SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones\ I should be able to read all the Time Zone info along with the offsets and Daylight saving details. Is this accomplishable in T-SQL us...more >>

Hi, I have written a software which spawns threads depending on the type of message this software receives. In each of the threads I connect to database and fetch records and then I use the fetched records to insert into another database. After writing to database the thread returns. The thi...more >>

Hi! Is there any kind of worry when using the keyword "distinct" in sql commands for querying DB Sqlserver 8?? Some people higher experienced than me in vb.net apps development told me not to use "distinct" but they mentioned no reason for that... I´m struggling to not use "distinct"... Is th...more >>

Hi, I am building datamart but I need your expert advise to update the datamart. I get text file & load it into Staging Table. From Staging Table I load it into Dimension table Cust_Staging_Table Cust_id Cust_Name Cust_Add Cust_City 111 Sam 100 St. San Jose 222 ...more >>

Has any one heard of the command :r? If so could you explain which version it's supported under and how to use it. I believe it allows you to include scripts. Thanks,...more >>