I am with a problem on a query that I want to do in SQL I have a Table that generates Log´s for me and in the field dt_datalog esto storing together in this format " 29/8/2003 10:04:18 " with the hour. Only that I wanted to do a query ignoring the hour only the date. So that I can make a query am...more >>

I need to calculate 30day from given date, excluding weekends and holidays. I have holiday table holiday year stardate enddate x-mas 03 12/24/03 12/25/03 i need to write a function to calculate the weekdays. how do i do this. Thanks. ...more >>

I need to get 30 day from date which excludes weekends and holidays. I have holiday table holiday year statdate enddate xmas 02 -- Posted via http://dbforums.com...more >>

hi all any one tell me select query for database tables like catelog in oracle if any query is present in sqlserver 2000 please tell me thanks in advanced regards khurram ...more >>

Hello All, I have been experiencing some problems in getting updated one of the = tables...... below is the SP code: CREATE PROCEDURE sp_Order_Detail = =20 ( =20 @Order_Codes varchar(18), ...more >>

Hi All, First, thank you for everyone about this forum. It is very usefull to me when I am learning SQL programming. Could someone explain me in plain english what is relational division. Any reference to a web-site or a published book also appreciated. Best Regards Kari Laine ...more >>

Hi Guys I develop an accounting package that uses SQL/MSDE 2000 as the RDBMS. Its really hard for me to get new clients to look at my product when they have to download a minimum of 25MB (ie, MSDE installer) just to have a look at it. Now that I've got a fixed IP ADSL connection, I wanted ...more >>

SQL 7.0 Trying to load data from Excel (Office 2000) to a table. In 'Transform Data' Task, when clicking Tab 'Transformations' - ERROR Error source : Microsoft JET Database Engine Error Description : System resource exceeded Pls Help Thanks In Advance Shamim ...more >>

I have a question regarding the query optimizer and highly non-unique WHERE clauses. If anyone has any insight into this particular problem, I would really appreciate the help. I have a table with a primary key, a [status] column (which is also indexed), and a number of other columns, some of...more >>

I am importing data from Excel into SQL server using Select * Into ... From Rowset. I am getting extraneous blank lines at the end of the data. Since I don't know the size in advance is there any way to tell it to stop at the first empty line? -- Jonathan Blitz AnyKey Limited Israel "...more >>

Hi, I'm a network admin now managing database server and learning SQL DBA. We have MS SQL 2000 servers running. Everyday lots of data we loading to the SQL databases. How to monitor and calculate incoming data and the hardware usuage. Please let me know if you know any tools or SQL scri...more >>

I have the following tables: contacts: first | last | id John | Smith | 1 Jane | Doe | 2 Bill | Jones | 3 locations: location | id AL | 1 CA | 2 WA | 3 locationsToContacts locID | contactID | id 1 | 2 | 1 1 | 3 | 2 2 | 1 | 3 I would like the result set as follows ...more >>

hi there in certain situations i lock a row in my database using the updlock-statement. now if a user in my application tries to update the locked row, he is blocked until the record is not in locked state anymore. this is correct. but is it possible to make a statement to see if a record is...more >>

when @Criteria1 and @Criteria2 are both non-blnk and different from each other, the following T-SQL fails to continue construction after completing the 1st half of this line (approximately) SET @sql = @sql + ' (Field1 LIKE ''%' + @Criteria2 + '%'') OR (Field2 LIKE ''%' + @Criteria2 + '%'') '...more >>

Hi! Would someone help me - a novice - telling me whats wrong with this? I'm running this .bat file: REM Restore the database isql -Usa -pwd='somepw' -irestoreDb.sql ...which should execute this .sql: use master go RESTORE DATABASE ComputeBooks FROM DIS...more >>

Hi, Are there any examples as to how to save and display TIFF type files in a MS-SQL 200 table ? Thanks C.Fleury ...more >>

I am using INNER JOIN, however, I feel that is too slow to retreive records y?? and any website have the query performance for my reference?? thx~ ...more >>

hi there... i am trying to program an address-database in c#. now i would like to lock some records while working with them to avoid deleting or modifying the same record through an other user in the network. in oracle there exists an select for update statement : select * from tbladresse for...more >>

I run a script to delete the dt_sprocs when they are created but how do I keep them from being created. I have removed them from master and model. They still occasionally get created and I don't know where they are coming from. TIA, Kiril...more >>

Hi All, Is it possible to run VBScript within SQLServer7 as an object? I have some VBScript code that sends emails to our MS Exchange server. I have the code on an ASP page but the page occasionaly times out when sending emails to employees. Instead I want to place the code on SQLServer an...more >>

Is there a way to generate the DDL for a SQL Server 2000 database using Visio? We have 2002 Professional, and I can find Reverese Engineering, but have not found the capability to create DDL. Suggestions? Or is another version required? I used to be able to do this with a version of Visio ...more >>

Is there any way to define a manifest constant in TRANSACT-SQL? I have a number of "enumerator" type tables that define sets of valid values for a given datatype. But instead of using the constant value directly in my stored procs, I would like to use a symbolic value instead, so as to be abl...more >>

when using query analyzer to execute a stored procedure with heavy conditionally constructed content (based on parameter values, etc) ..... is there a way to cause it to show the actual sql that was executed (or attempted to be executed) ... ? ...more >>

Are there any issues with Outer Joins in SQL Server 2000? This is what I have. I have table 1 that has fields such as the following: SiteID Optnum Description I have table 2 that has fields such as the following: SiteID Optnum Counts Let's say fo...more >>

I am importing a date column from FoxPro to SQL. It seems that SQL does not allow empty date and fail the import process in DTS. I solve this problem by changing the empty date to a psuedo date. Is there any other way you can suggest? Thanks in advance for your answer....more >>

Hi all, I have 2 tables: Employee, Sales Employee columns: ID, Name (ID is primary key) Sales columns: Date, EID, saleamount (EID is a foreign references Employee.ID if I want to get a report like Date Peter John Frank ... -------------------------------------- ...more >>

I have a question regarding the "CONCAT_NULL_YIELDS_NULL" option. I've written a view, one of the columns returns a concatenated string which includes a user's last name, first name, and middle initial. I'm getting null values for those user's without middle names. I've tried using: ...more >>

Hi! I have upgraded all sql 7.0 dbs to sql 2000. Bc it was a sql 7.0 standard edition, it did not really upgrade. it created an instance of sql 2000. now, there are two sql server insanaces on the server: zzzz999400 (sql 7.0) zzzz999400\zzzz999400 (sql 2k) what i need is to delete sql ...more >>

Hello All & Greetings. I need to store values and display them in fractions for Width x Height x Depth (w x h x d) for eg. 5 1/2" x 3 3/4" x 7 2/5" and so on. I would like to store them as 3 fields but am open to suggestions since there may be search by only width or height etc. I am also t...more >>

Anyone have a recent update on the status of the upcoming SQL Reporting Services beta? I saw the product at TechEd and can't wait to get going with it. Is there any indication that the beta is about to start? ...more >>

I was reading through the WMI documentation for SQL Server and the description for the MSSQL_ConfigValue state: The MSSQL_ConfigValue class represents the SQL ServerT configuration values. Some SQL ServerT configuration options do not take effect until the SQL ServerT service has been stopped ...more >>

Problem is below DDL. CREATE TABLE [tblJobParametersPersonnel] ( [DetailID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [JobParameterID] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Personnel] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , ...more >>

I would like to develop a stored procedure that I can use to update multiple similar databases depending upon a parameter passed in. For example: We have databases Data_01, Data_40, Data_ 44, Data_46 All of which contain identical table names with data for different companies. I have a nee...more >>

I have two tables related in a many-to-one manner. The tables are as follows contacts.contactID,contacts.firstName, contacts.lastName locations.locationID, locations.locationName With a third relation table set up as follows contactsToLocations.id, contactsToLocations.frn_locationID, ...more >>

Hi, I want to add a field in my query that could be use as an index ? How can I do this with a Select statement ? I've tried with the count(*) but it return the number of row in my query. Example of what I want : rank Value1 Value2 1 Wilfred Calvo 2 Julie ...more >>

All, We are in the midst of building a large multi database server (version 2000). I would be grateful for some advise on database models and limiting transaction logs. There will be approximately 20 databases, all around 10Gb in size. Each database will be totally refreshed from source...more >>

We occassionlay change the stucure of one of our tables. I have been giving the task of creating a routine to rename the old table, recreate the table using the current structure and copying the data from the old table to the new table. Would also like to be able to script the indexes from the...more >>

Is there any way to define a manifest constant in TRANSACT-SQL? I have a number of "enumerator" type tables that define sets of valid values for a given datatype. But instead of using the constant value directly in my stored procs, I would like to use a symbolic value instead, so as to be abl...more >>

I am getting a deadlock error message that is perplexing me. Our scenario is like this; separate applications using ADO and ADO.NET with EnterpriseServices are coexisting in this project. The database and stored procedures are what we have in common. We are doing some testing now to see how we...more >>

I have two tables (t1 and tx aliases below) and 1) know that t1 has ~300K records more than tx 2) suspect that all the missing records in tx are contained in t1 3) am not sure if there are any records in tx that are NOT in t1 Executing the following gives me zero results. Anyone know how I...more >>

I have SQL 2K with SP3A installed Create a new stored procedure in EM The Title_Circulation table does not exist CREATE PROCEDURE sp_CheckSyntax AS UPDATE Title_Circulation SET Sname = 'BS' WHERE Organization_Num = 8855 GO Check Syntax Syntax checks successful. Is this happen...more >>

I am sorry for stupid question but because I did not deal with it for long time I forgot. How I know what service pack I have on SQL 7? Thank you. ...more >>

I have a backup of a Database files that its log files are damaged ( so I can't attach it to SQL Server) please tell me if there is a way to attach a database when its log files are corrupted? ( this DB has two ndf files so I couldn't use the sp_attach_single_db_file) ...more >>

Hi everyone, I'm trying to design an auction site for my company's intranet and I'm having a problem getting the right info out of my db. This is what my table and example data look like: KeyID | ItemID | ItemName | ItemPrice | ItemBriefDesc | Bidder | Bid | 1 | 2 | Small Black Bear | 50.00...more >>

I get confused with the following concepts: recursive relationship supertype-subtype relationship Can anyone provide me with a defintion for them? Thanks in advance for your help. Sandra ...more >>

Hi please see the query below. It is returning Quantity as 8 when in fact it should return 2. I want to sum the field Quantity in Disposition table where order_num and line_num matches from order_detail_org SELECT order_detail_org.order_num, order_detail_org.order_line_num, order_detai...more >>

Hello, I'm writing a cursor to create automated messages (multiple lines) and then send a copy by email using xp_sendmail. The procedure will also create a copy in a text file (currently using bcp) which will be placed in a directory and picked up immediately by another program. The email pie...more >>

I have a field, 'full_name', that concatenates the last_name and first_name fields together. When the optimization job, that was created by the maintenance plan wizard, runs it fails on the table that contains this compound field. The compound field is NOT indexed. Any ideas on what exactl...more >>

I have the following select query. I need to update table3 with the dates based on this select query. How do I do this? Select * from table1 tf where confirmation in ( select confirmation from ( select [id], max(confirmation) as confirmation from table1 group by id) as t1)and confirmation...more >>

Hi After reading the article about storing a SQL stmt in a field, I was wondering if its possible to store a calculation in a field?? The calculation would be made up of field names from a temp table that would be created in a stored proc e.g. Table is called Holds_calc 2 fiel...more >>

Hi- I have a number of codes chosen by a user in table "codes". At the users request, each code and an associated $$ amount is put into table "accounts", the table maintaining account information for clients. I would like to keep a running balance in table "accounts". table "codes" CODE code...more >>

I have a delimited list of strings, seperated by spaces. How can I loop on this list? I want that each item in the list would be replaced with '' i.e, if the list is : "hello world" then the loop would first replace hello, then world, then whatever comes next on a certain column. Is it p...more >>

Hi, I am working on sql server 2000. There are over 80 jobs created on one of our sql server. To schedule a server maintenance window, I will have to know all the current jobs schedule that's running on the server. Is there any quick way/store procedure that I generate a list of j...more >>

I have a table that has a column called cust_id in a table called Customers. The value being entered is always supposed to be 12 characters in length. The first three character are to be capitialized alpha characters. The remaining 9 characters are to be numeric characters each between 0 and 9. ...more >>