Hi, I am very new with Sql Server 2000. I should convert Access 2000 database to Sql Server 2000. First problem is how to convert Access queries which include user defined function. I have a normal Access query: Table: Customer Fields: Number, Name, Address and so on. Then I have company ...more >>

Hi SQL Gurus, (Sorry if double post, got wrong timezone setting) I have a Trigger on a Table, but on some condition / variable at runtime (front end), some code in the Trigger must be Skipped. Since I cannot pass @parameter to a Trigger, can I create a #TempTable, then the Trigger must check ...more >>

How can I wrtie a query that joins 2 or 3 tables without using a SELECT statement? ...more >>

In the following statement: select * into Staff3 from Staff2 where 1=2 What does 1=2 mean? ...more >>

I have created an stored procedure in northwind db. create proc dbo.getemployeeaddress @employeeid INT, @employeeaddress nvarchar(120) OUTPUT as select @employeeaddress = address+'.'+city+'.'+region+'.'+postalcode+'.'+country from employees where employeeid = @employeeid go Now I want t...more >>

Hi, I am looking for an easy way to get all of the properties from a SQL 2000 database much like the documenter does in MS Access. Any ideas? ...more >>

Hi All, I am in need to do a server to server copy of data. Which is a better solution for this in terms of performance: dts or linked server and why? Thanks, Sumanta ...more >>

There is a function in VBA called "format". If I have a number x=12345, and if I always need 6 digits, I can do something like Format(x, "000000" the result would be 012345. Now I'd like to know if there is similar function in TSQL or how do I acomplish the same. thanks...more >>

database SQL 2000 Dynamic Parameter? I would like to pass a parameter to a procedure from dot net. the relevant part of the query is this: AND ROT_Creative.Contact_id not in (21,22,3) I would like to use a parameter like so AND ROT_Creative.Contact_id not in (@param) I would like the...more >>

How do I get the returning value of a SP in a variable? The code I tried but which doesn't work is: set @ActualDiscountDays = exec get_discounts @BeginDate, @SN_ID BTW The last line of the Get_Discounts SP is: select count(*) from #TempDiscount where TD_Date >= @BeginDate and TD_OutCount >=...more >>

hi guys, I want to query my table 'A' with have 20 fields. there a duplicate value in field 'SEQ_01'. i want to select all field and eliminate the duplicate in field 'SEQ_01' using distinct keyword. i already use this statement : select distinct seq_01, city, name, .. from tbl_a where seq_01...more >>

Hello All -Especially Celko and Doug Miller, Firstly Joe, I think your book SQL For Smarties is great. I have been reading, with much interest, your debates on the IDENTITY attribute of a field for the purpose of a Primary Key in a table. This is important aspect of a new project that I ...more >>

I was not clear in my prior post, so I've re-written it slightly, below. My data tables have been reorganized and now I have a new problem. My Where clause has to be restructured in a way that is unknown to me. I now have columns named: Period, Symbol, Revenue This is a flat table at th...more >>

Hi, Any suggestions on giving a String Variable as the File Name in Bulk Insert Command? What I mean is to give something like this -- Bulk Insert From xx (where xx may be 'C:\abcd\xyz.txt' instead of giving Bulk Insert From 'C:\abcd\xyz.txt'. I am getting an error when I try to do this...more >>

I'm creating a statement that must first retrieve a sum of downtime, set it equal to a variable and then use that variable in a 2nd statement. I just need help with syntax to set the sum of downtime to the below @sTotalDowntime variable. Below is my failed attempt: declare @sTotalDowntime d...more >>

Hi, One of our customers installed SQL Server 2000 on a Domain Controller Server. I cannot manage to connect to SQL using an SQL user (not even sa), by using a trusted connection I do manage to connect. I get an error message that a session could not be initiated for user sa. Any ideas? ...more >>

Hi, I have a database that saves Orders. There are many products that an order can have. With this in mind there a 3 main catagories of products. Currently, I have one product table for each catagory because each product has different options. What is the best way to map to each product tab...more >>

Take 'pubs' as an example, is it possible to query the following information using a select statement? au_id, number of books with 'price > 10', number of books with 'price <= 10', number of books with 'type=business' ----------------------------------------------------...more >>

I know how I can get the results of a procedure inserted in a table: insert into #TempAllForfaits exec get_forfaits @Date, @SNID But now I need the extra field @SNMut added to the insert. So I need the result of the get_forfaits procedure PLUS an extra column which contains the value @SNMut...more >>

I have two tables, which have 28 common fields(name, data type and value are exactly the same), and 6 fields unique to the first table(TableA), and 6 other fields unique to the second table(TableB). I created a view like this: CREATE VIEW model AS SELECT COALESCE(a.x1, b.x1), ...more >>

[Microsoft][ODBC SQL Server Driver]Unknown token received from SQL Server Data transfering between two databases in SQL2000. WHAT for the fu...k that means? I went crazy by now also!!!! ...more >>

I have the following query: SELECT Min(ID) as MinTransactionID, MIN(Batch) AS Batch, SUM(Qty) AS Qty, MIN(MachineID) AS MachineId, MIN(EngRev) AS EngRev, MIN(PartNum) AS PartNum, MIN(RecordTime) AS TimeProduced, 0 AS LabelRequired, 1 AS Summarize, MIN(LockID) AS Loc...more >>

Hi all, I have a very "newbie" question: I'm trying to make a quick sp that will read a specific set of table names from the dbo.sysobject ie: SELECT name FROM dbo.sysobjects WHERE name LIKE 'TMP_TOUPLOAD_%' and from there perform another select to look for nulls in a specific fie...more >>

I know this is possible, I just can't seem to get it to work. I want to take a table, query the rows from one column and invert it to create a temp table using those results as column names. Example: select typecode from mytable Typecode -------- ABC DEF GHI JKL Then using those...more >>

how do we check the existenc of a temporary table, e.g. create table #t (field1 int) insert into #t values (23) ----- i want to check the existence of table #t table, if it were a regular table e.g, thetable , i would have done as: if object_id('thetable') is not null drop t...more >>

We recently changed from a domain to a workgroup. I have about 20 jobs that were created under the domain. Now they wont run because they can't determine if the domain which was the owner has rights to the server. Is there a way to change the job owners. I went into the sys tables an...more >>

Why is it that Inserted & Deleted tables cannot access ntext & Text fields. This is a serious limitation in triggers. Makes a simple task like creating an audit trail, a huge headache. Is there anyway around this? ...more >>

Hi All, Is there a good article which guides in how to read query plans. What does Bookmark Lookup in a query plan means? Thanks, S ...more >>

Is there a way to have a table insert into another table only where the field names match? ...more >>

I am writing a VB server that will query a SQL Server 2000 database for various data. The VB server will run on a separate box from the database server. Before I make any SQL queries, I want to test that the database is online and prevent making any SQL calls if it is offline. I do not consi...more >>

This is the result I would like to have: RowID provid LastDate lastname firstname RecVal ------- ------- ----------- --------- ---------- ---- 201307 118498 2003-01-10 JOY NOEL 4 377532 118498 1973-12-11 NOEL JOY 5 RecVal says four fields are not ...more >>

how can a variable string be appended to where clause when I try as below I keep getting a syntax error example: ------------------ CREATE PROCEDURE [dbo].[xxx] @Type integer AS Declare @string ... if @Type=1 set @string = "this" if @Type=2 set @string = "that" select ...more >>

I have to group 2 years of data into 8 quarters (4 quarters per year). They want 8 columns of data returned with the data summed over the 2 year period. I can't figure out how to do the dates. Can anyone suggest a resource that would help understand how to accomplish this? Thanks....more >>

Hi, We currently encourter a strange problem. Everytime, when we use ASP to call a stored procedure, the sp insert some records to a table. With same parameters, the number of records that's been inserted is not the same. The sp never finished the numbers of row it should insert to and there...more >>

I have a sql table that has a column datatype smallint. In Access the same table same field we can format it as 0000, so that there are 4 digits, like 27 would be 0027. but SQL server table design view, I cannot find a format. How can I implement this in SQL server tables? Thanks ...more >>

I have an existing table that was, er, misdesigned. I need to retro-fit a bigint identity column (with auto-num). First, I added the column to the table as NULL-able. Now, of course, the column holds no data, so I can't just add the IDENTITY constraint. I need to number the existing rows and a...more >>

Hello, What=B4s the best book to learn SQL Server Stored Procedures? I have experience in DBMS. TIA, Bastian...more >>

Hi, I have the following statement. I would like to replace part of a sql statement with a variable -@strDeptHead (that is a string which is built) where it says REPLACE HERE. However, I don't know how to make a string part of a select statement. Any insight would be great. Thanks Dhruv...more >>

I have 4 tables sales, salesDetail, quote, quotedetail. I copied the 1 record from quote to sales and I got the @@identity. Not I have to copy multiple records from the quotedetail to salesDetail Here is the SP so far: CREATE Procedure addNonStock ( @tempQuoteID int ) As DECLAR...more >>

I have a need integrating SQL2000 & VSS. Is there a simple way or any other way to do it? There are some undocumented procedures in SQL2000 which are to help one to integrate SQL database into VSS. Does anyone have documentation about those procedures? Or, is there a tool which makes it easyer? T...more >>

I want to use sp_OA extended stored procedures but I have an SQL Server 2000 and I did not find them in the stored procedures of the master table. Where they are supossed to be? Is there any way to add them to muy installation if they are not installed?...more >>

Hi, I am using OSQL to ouput a SELECT into a text file. C:\>osql -Ssql1 -E -dnorthwind -s -w100 -s" " -Q"set nocount on select employeeid, lastname, firstname from employees" -h-1 -otest.rpt 1 Davolio Nancy 2 Fuller Andrew 3 Lev...more >>

So i should down load this free util to get it done and execute the command?...more >>

Hi All, Just wondering, does Windows Authentication considered "faster"? Thanks! ...more >>

I am trying to use the statement below in SQL 7 to pad the import of a number to be a specified length. The parsing produces an error on the LEFT command. Any ideas on problems with this code? select {fn CONCAT(CMN_NUM, LEFT('00000000000', 11-LEN(CUSTOMER_NUMBER)) + CUSTOMER_NUMBER)} A...more >>

Hello, I'm trying to create a trigger that will update column X in row Y when column Z in row Y is updated. So far, I've only managed to create a trigger that updates column X in every row of the table when I update column Z in row Y, which is a bad thing. Does this require the use of a c...more >>

What's the use of TempDB database...just to store the temporary tables...... ? ...more >>

I have view in SQL 2000 that joins an Item Description (varchar) field from another view. The ItemDesc field's longest record is 257 chars long. I want users to be able to search on this entire field, from the ItemMasterView view, but only want to return the first 60 characters of the Item D...more >>

i have two tables t1 and t2 with identical structure. Iam copying records using truncate table t1 insert into t1(col1,col2,col3) select col1,col2,col3 from t2 t2 has two million rows. iam tracking progress of records are inserted into t1 using select count(*) from t1 form another dbconn...more >>

I am new to SQLServer and SQL Server 2000, which I am using. Came from a Sybase environment. I am writing a stored procedure where a record is inserted into a table. The table has 80 fields in it. I want to insert a record and to put values in only 3 or 4 fields of the 80 field table. The t...more >>

I want to send an email to our users when a change is made to their request in our maintenance management system. I am writing a trigger on the appropriate table in my database (customer) to collect certain data on update and write it to a new table (emailer). From that new table I will write a t...more >>

I am trying to do run an SQL in VBA (through MS Access) and I wanted to know how to use an AND statement. I seem to be able to do it when I use an or statement but when I try and use an AND for the same exact thing I don't get any results. When I try to sue the query builder to do the...more >>

How can I return a qry as a string of date in format. 'Thu, 11 Dec 2003 17:00:00 +1100' This is date of qry + 3 hours +1100 is GMT offset for AU I have tried different ways but all seem to be inelegant, Hope some one can see an efficient way of doing it. Don ...more >>

Hi All, I would like to get a list of all tables in a db that contains a specific field. I assume this can be found using the system tables but I can't find the appropriate tables. TIA, Chris...more >>

A class can have many instructor. I have an index table and one record for each instructor listed under a class. I do an inner join between the 3 tables to get the results, and i use the group function. Trying to this in a stored proc or sql query, no asp code When i run a query to sum ...more >>

I have a table that cannot be truncated because of foreign key relationships. This table has an identity seed field for the Primary key. Each night, I need to run a dts package to re-populate this table with current data. I use the "delete from tablename" statement with no where clause to delet...more >>

The folloing query runs in query analyser, sends the mail and everything is fine. However if we run it as a agent job it completes everything apart from sending the mail using xp_sendmail. At the weekned we rolled this server out which has restored databases (except MASTER) of another SQL ser...more >>

it is possibleto pass as parameter an array for one store procedure ? thanks, Silvana...more >>

Hello All, I know basic SQL statements and stored procedures. Now I like to learn more advanced ANSI-SQL techniques. Can someone please recommend a good comprehensive book on such subject? Thank you very much. simon. ...more >>

Hi I have run this command will this cause any problems as I believe I should have specified a size..also is the tempdb that important..thanks for any answers USE tempdb GO DBCC SHRINKFILE (tempdev) GO ...more >>

Can anyone explain the line numbers shown in the error messages? They sure don't seem to match the code? BTW - I did fix the errors by changing dbl quotes to single quotes but I am still trying to figure out the QA line numbers. ---------------- errors --------------------- Server: Msg 1...more >>

Is it possible to put a trigger on the syscomments table to save changes to stored procedures. Using this mechanism to create a version control log table ? Obviously putting a trigger on a system table, isn't the best idea always, but this is a dedicated development server, not the production...more >>

Hi All, We have an odd thing while using the DELETED and INSERTED- "tables"in triggers. If my collegue executes and SP in the Query Analizer, a query in a trigger takes a lot less when I execute the same SP. We think the difference is in the the usages of the DELETED and INSERTED-tab...more >>

When I write: go select * into Staff3 from Staff2 where 1=2 go exec master..xp_cmdshell 'bcp master..staff2 out d:\cust.txt -c -t -S<server> -Usa -P"yahoo"'; I got the following error message: 1- The system cannot find the file specified. 2- NULL Where is the problem? what do [-c -t -...more >>

How to insert a column in the middle of a table programatically without using enterprise manager and also without losing the data. Thanks, David ...more >>

Hi I want to create a batch file to execute a bcp, but I am having a problem since there is a space in my database name (for example: my DB). So when I execute the bcp, it inteprets "my" as the first argument and "DB" as the second argument The command is: bcp my DB.dbo.table1 in files.txt -fbcp...more >>