Hi, does anybody know what the number in parameter "locks" mean? I believed it is a number of locks, but if I set it to 5000 (minimum), it would be then 5000*96/1024 = 468Kb, but SQL Server 2000 allocates at start 480Kb, as sysperfinfo tells me. How is it possible? Or does the number mean 5000...more >>

There seems to be something about INSERT...SELECT that I don't know (probably are many things). Running a certain SELECT statement in Query Analyzer returns 3 rows. Using the same statement as part of an INSERT...SELECT gives the message "0 rows affected" (or whatever it is in English). And, ...more >>

Isn't Microsoft going to UPDATE ENGLISH QUERY? ITS STILL THE OLD VERSION. How about Yukon? Does it comes with new EQ ? ...more >>

I have a query that looks like this: SELECT TOP 2 col1, col2 FROM table1 It returns: 1000377, 2004-02-05 1000376, 2004-01-22 I would like it to identify the first record as "1" and the second a "2" like this: 1000377, 2004-02-05, 1 1000376, 2004-01-22, 2 How can I do that? ...more >>

Dear bilingual people, I'm trying to use Access 2000 as a front end to SQL2000, using ODBC linked tables. I have written a query that works fine directly in SQL2000 ( Query Analyser ), but I cannot seem to get it into a format that Access will digest. The error is : Syntax error (missing ...more >>

Hi Frends, SQL 2000 I created a DTS to export to excel file. It is working fine but need some other modifications : -how to create excel file name everythime I run DTS like MYFILE_09012004172114.excel For that purose I created a tmp excel file and export to that file and then running a ...more >>

Hi when creating a view say Create view wmuser.myview It returns an error saying that wmuser cannot be accessed or do not have permission to do so. wmuser has db_owner rights to the database. The creator of the database is dbo, when executing the same statement this time Create view dbo...more >>

I need a SQL query that will select a field (varchar), but if the length of it is more than N characters, it truncates it to the N length and appends it with a "...". It seems like it should be straightforward. Mike S...more >>

update ( select top 1 * from userpics as pele) where userid=21 set special=1 where userid=21 i want to update the record which i apply a select query. but it doesnt work.. select top 1 * from userpics as pele where userid=21 i want to apply this query an update execution.. how it ...more >>

Hi all, I have a query that was very slow. I looked at its execution plan and it told me the statistics was missing for two fields. After creating the statistics the query runs very fast. So my question is how does statistics affect the query's performance? Do I need to write t-sql code to ch...more >>

Hi Can we use UDDTs to define parameters in a stored procedure? Is there some special syntax to make it happen? Thanks Tim Cowan ...more >>

Thanks in advance. Assumption: The schema is locked for any objects that exist to support the application. New objects are allowed if they do not modify the original objects. Instead of triggers in this environment wreak havoc with the application. I have had to build tables to accept th...more >>

I have two identical tables in 2 different databases. each of the 2 table should be an exact replica of each other. Is there a way to check row by row to make sure they are both identical? Or is the only way to check is a query with a huge where clause where a.vvv <> b.vvv or a.qqq <...more >>

CREATE PROCEDURE st_showthisphoto @userid numeric(18)=NULL,@photoid numeric(18)=NULL AS update userpics set special=0 where userid=@userid and special=1 update userpic set special=1 where userid=@userid and id=@photoid GO it runs only first query how can i run the second one in this pr...more >>

Is there another way to alter a text column, changing it to a varchar, without rebuilding all dependent objects? I assume this has to do with maintaining linkage since the table is backed up and the data pumped into a new modified table....more >>

A customer is having problems w/ deadlocks and noticed that it only happens when updating a record, not when inserting a new record. Is this b/c one has a higher priority than the other? TIA, aj...more >>

I am reposting this because I didn't receive any useful answer the first time. This will be the last time... Win2000 Server, SQL2000 SP3a -- With a query like this: Update b Set b.TextField = a.TextField From LINKEDSERVER.DatabaseName.dbo.RemoteTable a, LocalTable b Where ...more >>

Hi All, I am having a very strange thing happening. I have a job that runs everynight, that takes records from my branch office and updates a database in my home office. I noticed that my two tables went out of sync the other day. I checked the status of the nightly job and it said it...more >>

Hi all, I understand how to use a CASE statement within my queries such as: CASE FirstName WHEN 'John' Then 'Good' WHEN 'Bill' Then 'Bad' ELSE 'Who Cares' END AS [Personal Status] My qeustion is how can I, if I can, use more then one field as the criteria? In other words, ...more >>

Hello, I have a simple table: CREATE TABLE [dbo].[Table1] ( [id_entity] [numeric](18, 0) IDENTITY (1, 1) NOT NULL , [col1] [numeric](18, 0) NULL , [col2] [varchar] (50) NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD CONSTRAINT [PK_Table1] PRIMARY KEY CLUSTERED (...more >>

My application uses a third party DLL for some functions. Can this DLL reside in a location other than MSSQL\Binn. when I specify a path in the XP create script, the dll doesn't show up in enterprise mgr XP dll path --this works fine: exec sp_addextendedproc 'xp_cimevent', 'cimxp.dll' --...more >>

Ok i have to get data from one database into another but have to use a merge guide. For example married in one table equates to a number in another, this i know i can sort with an IF statement. So i thought use a select to get my data, then using the merge guide sort the data ready for insert...more >>

Let say I have the table like this: 2004-09-01 00:00:00.000 2004-09-02 00:00:00.000 2004-09-03 00:00:00.000 2004-09-06 00:00:00.000 2004-09-07 00:00:00.000 2004-09-08 00:00:00.000 2004-09-09 00:00:00.000 2004-09-10 00:00:00.000 How do I write a function to return a prior date even if th...more >>

Hi all SQL 2k I was wondering if there is a better way to write the below IF condition. I got a MemberID & a Topic ID (forums topic) and I want to see if the topic lastpost Datetime is newer than when the member last visited the forums. If it is then its a "new" topic for that member. ...more >>

hi, What's the pros and cons for the following two methods when you define charactor strings in a dynamic sql? 1. SELECT @EXPORT_VIEW_SQL = ... 'SELECT ' + char(39) + '000000' + char(39) ... 2. SELECT @EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ... they both work, I persona...more >>

Hi all, How do I write a function or a SP to return the previous business date. If the previous business is one of the national holidays, it then now should return the previous busniness date that is NOT a national holiday. Thanks, Tj...more >>

Hello, I am trying to restore a database from a .bak file. I am getting an error relating to Device error / device offline. I have admin privileges what can cause this? It fails on the FILELISTONLY SQL too. -- Regards Simon Jefferies mailto:simon[nospam]@cooltoolsonline.co.uk -- r...more >>

select top 2 * from (select top 5 * from users where userid>10 order by userid DESC) as pele why i must use here AS statament.. it refers inside query or outside query... when i dont use AS statament it fails. in normal query we dont use AS statement select * from users when we must ...more >>

Hello I mean how to open connection with sql port like data source=mycomp;initial catalog=mydata;user id=sa; ...more >>

Hello all, I have a strange solution to resolve. I am trying to figure out gaps in our coverage. I have a table with the following fields & value: CustID CovgID CvgStartDt CvgEndDt (PKey) (INT) (Datetime) (Datetime) ========================================== E.g.#1: THIS...more >>

Does anyone know what the LINENO Reserved Keyword in SQL Server was/is/could/never implemented used for?? BOL is no help, I have done a Web Search and am none the wiser Many thanks Paul...more >>

Hello Can I connect on sql server using sql ports in connection string? Thanks ...more >>

I'm using sql query analyze to run sql stored procedure. I know that there is an option to write out the result into a file (Query/Result in file..) is there a way I can add a command to my stored procedure to write the result automaticly into a file without doing it manually? Thank You ...more >>

Hi, We have a trigger which updates a company's global standard codes on all the databases on all the servers. We have 20 databases on 5 MS SQL Server 2000 and the OS for all the 5 machines are Windows 2000. Here in our company, We have a 'company global table' in one of these databas...more >>

Sorry for the lack of info. I figured the query I had given would provide enough to infer the table structure. I just put this solution together. I doesn't require a calendar table, but I'm wondering about the efficiency compared to your solution since both require a subquery select p AS Pr...more >>

Hi What will happen if I issue a BEGIN TRANSACTION at the begining of a stored procedure, but I never issue a ROLLBACK TRANSACTION or COMMIT TRANSACTION? Will the changes be automatically rolled back or commited? Thanks Craig ...more >>

In SQL Plus you have a command called describe which list the attributes for an Oralce table. Is there anything like that in transact-sql. Thanks, Edie ...more >>

i have a table Events (FormID, Sender, Useraction) and another table SenderReceiver(FormID, SenderNb, ReceiverNB,Action) and tables Users, Role, UserRole, form, RoleForm UserAction can be: new, update or view Action is same as userAction Sender and SenderNb are the role nb for the pe...more >>

I've been doing a lot of reading in these group and have found many articles suggesting I use derived tables or table variables instead of temp tables. These articles are also suggesting I use While loops instead of cursors. My question is Why? I've done many test that don't support these art...more >>

Using an SQL statement, I need to know whether a field in a table is: a) been autoincremented b) the increment amount (1,2,etc) c) the seed (what number to start from) To gather info on fields in a table I usually use: EXEC sp_columns @table_name = 'tablename' , but this doesnt seem to...more >>

Dear Sir: I have 2 Store Procedure : proc_A, proc_B. User choose which fields and condtion they want and trans to proc_A. Then proc_A will call proc_B. proc_B will assemble a query string and output @q_sql to proc_A. Generally I can create a temp table : #temp_result to get the d...more >>

Hi, I have SQL2000 installed on my laptop and I wish to import a directory full of .txt files into a table. I want to import the contents and the filename. Are there any links on the web or any pointers to a newbie please? Thanks, skc...more >>

Hi there, I Have two tables: TableA contains the columns LDUserName varchar (35) Primary key LDDate Status1 smallint (1) [this can be 1, 2 or 3] TableB also contains the column LDUserName I can create views etc for where TableA.LDUserName = TableB.LDUserName. What I'm stuck on is a way ...more >>

Hi all, I use Enterprise Manager's Generate Script feature to create scripts that I can run on my client's production server. Problem is; I found out that the scripts do not assign table's Primary Key. I have to open each table one by one, right click on the PK column and mark it as 'Primary ...more >>