How efficient is ti to use join views in a database? I'm developing an e-commerce system and using join views to join the product, product category and product review tables and wondering if this would have any adverse effect on performance. Thanks in advance ...more >>

Hi all. I am sure there are lots of information about how to start the prosess of migrating an application from VB6 to .NET. I just couldn't find it. It is the common questions. How do we start? Can we do one bit at at time Should we do some changes in VB6 first to prepare to .NET +++++ ...more >>

I need some advice on how to go about diagnosing why a stored procedure execution plan is different than running the underlying query alone. I am not using any parameters and it should not be a recompilation issue because whenever I take this query and run it alone it ALWAYS produces a better/f...more >>

Hello, In my select I have a few columns that start with "sum". I needed to order these columns as per my post earlier. I have a table with 4 columns a-d and an id column. I need to order these records by locating lowest column value from each record and order by. results should be 3 ...more >>

Hi All, I am having quite a bit of trouble getting a certain stored procedure to work properly. In my web page I have a list of checkboxes that post to the url. When I retrieve the value for the "beds" checkbox list from the url it looks something like "1,3,5,7". Now I want to pass this int...more >>

Does anybody know where i can find "Microsoft Embedded SQL for COBOL Programmer's Reference" or "Microsoft Embedded SQL for COBOL Programmer's Toolkit" ? I have Microsoft COBOL 5.0 and MicroFocus COBOL 3.4 and would like to access SQL databases from COBOL. I've tried eBay, Amazon, Google et...more >>

Hello! In my query I'm suppose to have a lot of new items, and I was wondering how I can receive the following: Format(i, "0000") would return 0001 if i = 1 and 0010 if i = 10. Is this possible in t_sql? -- - Lars ...more >>

Hello I have the following: Teams: Team A B C D E RoundPoints: Team RoundPoints RndNo A 4 1 B 3 1 C 10 1 D 5 1 A 6 2 B ...more >>

Hi all, sql server 2000. I took over a database which has the system default user: 'dbo' login as one of the consultants' name who left the company already. How can I change the login back to normal which should be under 'sa'? I tried to delete it and recreate 'dbo' for this databa...more >>

Hi, I am planning to move an server from one domain to another. The server has sql 2000 enterprise edition on it. The sql server ha about 250 database on and a great many windows loginns. I can copy the databases from server to server fine, although I am unsure of how to re align the users ...more >>

Hi all, as you can see below, we are facing the deadlock situation between 02 indexes within the same table 1941581955:5 and 1941581955:1 KEY: 8:1941581955:1 (8000a1e37379) CleanCnt:1 Mode: X Flags: 0x0 Grant List 0:: Owner:0x49e73c20 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:142 E...more >>

Hello=20 How do test my web application for concurrent users (how much concurent = user connect at a time and how traffice will be affected and what will = the response time?) and how it is done, how long it will takes, does it = interfere with current site, etc.?=20 Reagarding the number ...more >>

Hallo folks! If I insert this into QA: select DATEPART(wk, getdate()) ------------------------------------ I obtain: ------------------------------------ 4 ------------------------------------ How can I do to obtain 20040119 and 20040125 that are 1) first day of week n...more >>

IF I execute this statements in my StoredProcedure: set identity_insert pregledNarocanje ON DBCC CHECKIDENT (pregledNarocanje, RESEED, 1) set identity_insert pregledNarocanje OFF I get an message: Checking identity information: current identity value '265', current column value '1'. DBC...more >>

I have a sql table where I'm dumping stats (table counts) on a daily basis. I would like to have a report created every morning that list which tables had updates and how many records where added-to deleted-from. The table these fields: Table_Name Kount(Records) Date_Logged o...more >>

I am looking for a SQL statement that will populate a Schedules table from a specified starting date with entries for all the sessions that comprise a class. Each class covers a series of units in a specified sequence: CREATE TABLE Curricula ( class_id CHAR(4) NOT NULL ,session SMALLINT N...more >>

Hello, funny error happening with this code... Basically I'm getting this error when I try to run code to create a stored procedure... 'There is already an object named '#fred' in the database.' if @containerId=0 begin select ident,name into #fred from container where lvl=@lvl end ...more >>

I have a field that stores data as milliseconds, I need to convert this to hours. Suggestions? Thanks. Alex ...more >>

Hello all, I am running on basic financial reports and need some help with a query. Here's the raw data set. GroupID Amount 1 2745322.94 2 2019725.83 3 612953.45 I need to substract GroupID # 1 from GroupID # 2, the result of which I need to substract from GroupID # 3. How w...more >>

A new update to the SQL Server 2000 Books Online is now available from the Microsoft Download Center. This version of the Books Online will also be in the January update of the MSDN Library. To download the update, go to www.microsoft.com and select Downloads in the left pane. On the Download ...more >>

Hi, I want to know how SQL 2000 stores varchar. When I'm using a column with varchar(5000) layout, are there 5000 bytes stored for each row or is just text stored? So when I insert 10 rows: INSERT TestTable (varcharfield ) VALUES ('') does it use a few hundred bytes or at least 50000? T...more >>

I have a stored procedure that calls a batch file. This bacth file MAPS a netowrk drive using NET USE and then calls another application to update some data in a legacy system. My problem is that this batch file seems to fail whenever the stored procedure is called from a PC on the network, I ...more >>

Power designer have generated such code: sp_addtype T_MY_TYPE, 'varchar(50)', 'not null' go create default D_MY_TYPE as '' go sp_bindefault D_MY_TYPE, T_MY_TYPE go alter table MY_TABLE add MY_COLUMN T_MY_TYPE not null go It should work but it doesn't. Could you tell me why? W...more >>

Hello everyone, I have a table with 4 columns a-d and an id column. I need to order these records by locating lowest column value from each record and order by. results should be 3 1 0 1 0 4 2 0 0 3 5 0 2 7 0 1 4 0 0 0 2 0 6 0 0 thanks again, Andrew --*******...more >>

I was wondering if there is a way to query an ODBC data source from within a stored procedure? Something like create procedure proc_name @parameter, @parameter, etc.. connect to ODBC driver syntax....This is the database I want to query. SQL statement I'm not having trouble with the SQL po...more >>

SQLJ appears to be a java standard that works with multiple databases via JDBC. Does SQLJ work with SQL Server, and if so, how can I get a copy?...more >>

Hi, I am having this error with date field when trying to import paradox 5.x table into SQL Server 2000. I have the same error with text file. Anyone having the same experience as I have? any help will be greatly apppreciated. This is the error message: "Error at Destination for Row num...more >>

Hello everyone, A few days ago Steve helped me with this problem. (Thanks again Steve) This method is great but I now need to have multiple room types for each property and nothing that I have tried achieves this. 1. propID 1 has two room types. (count(stopstart) = @finish - @start + 1) che...more >>

If I have column ID in table, which is PK and int type and with identity: YES identity seed: 1 identity increment: 1 it starts with 0 when I insert first record. When I delete all records and insert new ones, I would like that ID starts with 0 again. I do that ever...more >>

I am working on an ASP.NET project where I have inherited a ton of code. There are a large number of connections that are not closing within SQL (the mode is set to "sleeping"). All of the connections are blocking up the pool. I need to either expand the connection pool or use a tool that can loc...more >>

I want to update a column called upd_dt in a table whenever a row is updated. I don't want to use trigger for this. Is any workaround there. Thnaks a lot. ...more >>

Who should be responsible for stress testing. Thanks a lot....more >>

We've just upgraded a number of databases to SQL Server and I'm having a strange problem with some of my ASP pages. When I retrieve a recordset from a SQL Server table, frequently text or ntext fields appear blank. e.g. Set co = Server.CreateObject("ADODB.Connection") co.Open(myDSN) Set ...more >>

Hi Has anybody know could i add a timestamp when i select a table?...more >>

Hi, Is there any way to change the values that a CHECKSUM field uses without dropping the field and recreating it? I couldn't seem to get the following to work: ALTER TABLE MyTable ALTER COLUMN csCol AS CHECKSUM(OldField, OldField2, NewField) Thanks Rich ...more >>

I have an SQL Stored Procedure that I wish to have fire off a DTS package. My issue is that it will be possible for several instances of this Procedure to be fired off at the same time. What I would like to do is ensure that only one instance of the DTS package is running at the same tim...more >>

Ok, basically, this is what I have. I have a 'Queue' that patients are checked into at a workstation and then retreived from the queue in order of check-in. They are checked in from 2-3 workstations, but retrieved from 5-6 workstations. Below is the query that is 'selecting' the patient and u...more >>

Greetings, It's a VB-SQL Server 2K application. In the VB front-end, users insert calculation results to a "money" column that gets lots of values with 3 or 4 decimals, like $25.555 or $30.4448. I do not want to touch the VB front-end. Is there any setting in SQL Server I can use to make th...more >>

I need to create an XML file that represents our company tree. The data and relations are all contained in a single 'employee' database. Because it is impossible to predict the number of branches in the tree I can't use DTS to drop a dataset into a file and using an xml file with the "FOR XML A...more >>

Hi! I'd like to select a value from a subquery if there is a value, otherwise select it from a different subquery. I cannot seem to figure out the synatx of that. Here's roughly the query: select (if ((SELECT TOP 1 ConstraintDateTime FROM Task WHERE TeamworkReportNrOfDaysUntil...more >>

Hi together, i have a value: 121500. I want to show this value as a time-value on a .ASP-Site(.aspx) in the format: 12:15:00. I have use: left,right and convert, but it doesn't go. Have you a idea? Thanks ...more >>

Hi, I need some advice on how to output the following query into a xls or txt file. select * from northwind..employees order by lastname I don't want to use query analyzer and CTRL+SHIFT+F I will be writing the code in a job so I would like to directly output the result into a file. I...more >>

when I say sp_who2 active why it shows the sleeping processes. 73 sleeping sa 74 sleeping sa 75 RUNNABLE sa 76 sleeping sa 77 RUNNABLE sa 79 sleeping ...more >>

Here is the view stored in osah2k1 database and I want to modify if to select a column from another database. CREATE VIEW dbo.Viewfromanotherd A SELECT osah2k1..tblCaseDetail.[Case ID], osah2k1..tblClaimant.[Agency Ref #], osah2k1..tblClaimant.[First Name], osah2k1..tblClaimant.[...more >>

Hi All, Does anybody out there have a script that can be used to generate the table definition in the same format as one would get using the Query Analyzer's Script Object as 'Create'. I.e What TSQL statment can I use to generate a CREATE TABLE statement. TIA, KOY When I am right ...more >>

Hello All, I have a query like this: select col1, count(*) as cnt from tableA GROUP BY col1 The output is like this: col1 cnt 1 20 2 15 3 10 4 5 Can we write a query which also shows a percentage column for each col1 value i.e(cnt/50*100) so that the output is: New Out...more >>

I'm in the process of cleaning up some of the PostCode data in my database. Some of the PostCodes for some reason have a double space in the middle & I want to replace this with a single space. Now the PostCode field on the table is a varchar(10). But when I use the REPLACE function in a SELE...more >>

I received an error on line 188 of my stored procedure. I would like to display the lines numbers of my stored procedures. How do you display the line numbers of a SQL Server stored procedure? Thank You, Fred ...more >>

Can anyone point me in the right direction for code examples and best practices for executing SQL Server DTS Packages from a webpage...?...more >>

hi I am trying to use a Table variable to return a result for effeiceny in a custom paging solution for a DataGrid control that i am using I keep getting an error during compilation; Server: Msg 156, Level 15, State 1, Procedure upsel_datacollection_get_paging_results, Line 20 Incorrect syntax n...more >>

I need to create a recordset for a VB6 application, but am not sure of the correct query. The query is over two tables referenced by a ClienTRef field. Table1. contains comapny data and Table2 contains usage records. I am trying to get a recorset containing all the clients that are currently at 'L...more >>

I am trying to check that the number of records in BacsTrnYear match the total count of BHYear_transcount. Although i have altered some of the data because of the sensitivity, non of the alterations will effect the expected outcome If its possible is there a query that will show me there are an...more >>

SQL Server 2000 SP3 on Win2k Server SP Using code: EXEC master..xp_sendmail @recipients=my_address_goes_here, @message='' @query='SELECT foo FROM bar', @subject='Subject' @attachments='qry.csv' @attach_results='TRUE' @width=500,@separator=', Without @attachments and @attach_results the...more >>

Advice on dynamic view please Server = SQL Server SP3 (Win 2000 SP4) Client = Access 2000 SP3 (Win 2000 SP4) Q. Can 30 concurrent users update a view at the same time and 'see' their choice of data? Q. Is data entry even going to work with the view being 'altered' every 2 seconds. We ...more >>

Hai all, Can any one please help on the following issue? I am using SQL SErver 2000 as Development machine and i use "IDENT_CURRENT" to get the last identity values inserted. my client uses SQL 7.0 and this (IDENT_CURRENT) is not available in that. Can i know the equivalent of this in SQL 7...more >>

Hi whenever I use a condition "emp_name is null " the indexes on this columns does not seem to apply Is there any way that I can force the use of index for null values as well Thank you in advance...more >>