Hello, Can somebody give me an example or urls of how to create an Oracle view in SQL-2000 server. I need it very quickly, Thanks in advance Rob Berghout...more >>

I have a table with a primary key column of pageIDs. I want some pages to be parents of other pages. So I have another table with two columns, parentPageID and childPageID (together forming a primary key). I wanted each of these columns to be foreign keys of pageID. However, using SQL Enterpris...more >>

What am I missing here. When there is no records I want to return a 0. It does not do that??? CREATE PROCEDURE GetMachSalesTotalsNonStock ( @SalesCookieID nvarchar(50) ) AS SELECT theSubTotalNonStock = SUM(CASE WHEN tempSalesDetail.SalesPrice Is Null THEN 0 ELSE tempSalesDetail.SalesP...more >>

I'm not sure the SQL Server newsgroup is the best place to post this, but perhaps others have run into this and can help. I am writing an app that needs to run across multiple databases and platforms, including MS SQL Server, Access, MySQL, and probably Postgres. Since these databases will perio...more >>

My users have problems with size of transaction log. They don't have sufficient technical skills so I have to do something to help them. Is it good idea to restrict file growth of transaction log and what are possible drawbacks? Maybe is better idea to do something programmatically? Do y...more >>

Hi, What's the field type for VB's long? Thanks. ...more >>

I have a table where one of the columns is Date. i.e. for e.g. 9/11/2003. Now these dates are only in multiples of weeks. i.e. given the above the next one can be 9/18/2003 or 9/25/2003 and so on. Now in the table not all of these dates are mentioned...i.e. for one year I have less than 52 entries. ...more >>

Hi SQL Gurus, I have posted this problem sometimes ago, but just found there is still problems with the solution, pls help.. reate table account( AccountNo char(4) not null primary key, debit01 decimal(15,2),debit02 decimal(15,2),debit03 decimal(15,2), debit04 decimal(15,2),debit05 decimal(1...more >>

How can I create a condition within a WHERE clause? In the following WHERE clause, Category, s_Type, Status and Manuf are provided by drop down boxes. If a user does not want to specify a selection in any or all drop downs, the variable changes from a '%' to an absolute value. The 1st WHERE claus...more >>

I have a text file I'm importing- all fields are text. There are some time fields...data looks like 023420. I need to write a stored procedure to bring it into a field (date/Time??) and convert the text...how do I do this so it looks like 02:34:20? Is this possible?? thank you....more >>

I have 3 tables: Employee, EmployeeZoneSlot, and ZoneSlot Employee (EmployeeID, FirstName, LastName) EmployeeZoneSlot (EmployeeID, ZoneID) ZoneSlot (ZoneID, Location) All I want is to display a table with columns (EmployeeID, FirstName, ZoneID, Location). Here's my attempted SQL statement: ...more >>

Hi there, Our application calls a stored procedure A in which a record is inserted into a table followed by a call to another similar stored procedure: Application calls SP_A. SP_A inserts a record and calls SP_B. SP_B inserts a record and calls SP_C etc. If *anything* goes wrong in any S...more >>

I'm using the Data Transformation Services system to pull in some data to an SQL 7 database. I'm trying to use the transformation VB script (see below) to map the fields. I'd like to set a flag in the destination table to identify the newly imported records. I tried to do this in the last line...more >>

I am currently running SQL Server 2000. My understanding is that the next version of SQL Server will be called SQL Server Yukon. Is this correct? When (approximately) might we expect a release version of SQL Server Yukon? ...more >>

Is there any tools to debug a stored procedure? Thanks! ...more >>

Hi All, I am trying to write an SQL query which will move an data from one field to another field in the same table. Say I have Table A - with col1, col2, col3, col4. I need to move the data from col1 to col4 in the same table Table A. Any help appreciated. Thanks in adv. Sunita...more >>

Hi Group, Sorry if this is in the wrong group ......... I was hoping someone might be able to help out a newbie out? I`ve installed SQL 2000 on a machine now im stuck. I`ve managed to find where to design the tables etc etc but was wondering if anybody has any how to or URL links on how t...more >>

I have created various tables in SQL and I have come to a point where I need to build a user front end for generating the tables on the fly. I have VB.Net 2003 to build the front end but need help on how to build. Can anyone point me to some examples of building a front end for an sql se...more >>

Can I use a CASE statement in a WHERE clause? I want to include certain conditions (within a stored procedure) based on the values of a paremeter. ...more >>

Trying this code in a stored proc and it says it executed successfully, but the file is not moved?? Any ideas..This is my code: EXEC master..xp_cmdshell 'move "\\datastore1.stuff.com\M:\test\test1.txt" "\\datastore1.s tuff.com\M:\test2\test1.txt"' Thanks!!! ...more >>

Hi All, I have a column with datatype binary 8, the data in the column will look like this 0x01BD9A4C0945C000, When I do a select on the column I want it to be "0x01BD9A4C0945C000", so that my result set would have a string version of the same data so that I can manipulate it. How can I ...more >>

I have installed both visual studio.net and SQL reporting service beta. My problem is that in the new project window I do not see the Business Intellingence Projects folder or templates...more >>

Is there a simple way to do a search to find any record that has any value within the string other than A-Z or 0-9 anywhere in the field?? find records that look like this: '1234/abcd' or 'abcd*' or '12#34%' but not '1234abcd' THANK YOU IN ADVANCE, I'D NEVER GET BY WIT...more >>

how I can create a trigger that deny the delete of rows in a table? I tried to place an exception within a delete trigger, but doesnt works... Tanks -- Fernando ...more >>

Hi there, I need to know how a record set generated from a SP can be passed along to another SP that calls it. I am not necessarily talking about passing the recordset as a parameter (using data type table). I need to pass a RS from a SP to another SP. Example (something like this): cr...more >>

What is uasge of "Minimum query memory" in SQL Server? Can I change the value when the server is live updating? Thanks. ...more >>

Hi, I have this query: SELECT p.c_packageid, (SELECT COUNT(*) FROM c_signature s WHERE s.seb_objectid = p.c_packageid) AS nr_of_signatures FROM c_package p P WHERE p.seb_sys_deleted='N' ORDER BY p.seb_sys_dtcre DESC Any ideas to improve performance of the query? Thx in advance Armi...more >>

I have created a user-defined_function in the SQl server. Inside this function, I try to call this function itself. But I got the error says "MyFunction is not recognized as a function". Can someone help? Thanks....more >>

I am having a problem that I am sure many people have already solved, so I was wondering if you could help me out. Background: We have an application written in VB6 that is running against the MSDE on each of the machines it is deployed on. These machines are all setup for syncrhonization ...more >>

Hello! is it possible to have SQLNS in a MSDE? The MSDE comes without the enterprise manager application, and just the database - right? No gui? -- - Lars ...more >>

First, I apologize for cross-posting this, but I was truly unsure which of these two newsgroups to post this in. I've got a stored procedure that does a bunch of things, but in essence what they all boil down to is one thing: Create a temporary (#) table, add data to that table, do a SELECT ...more >>

This question is mostly out of curiosity: On a narrow table with an identity int as a clustered primary key, how does SQL Server 2000 find a particular row? My understanding is the following steps are taken: 1. Look up root index page in sysobjects 2. Scan index entries, one at a time, sta...more >>

Here are the two options I have for a query, I'm hoping someone can tell me which is better and why: SELECT col1, col2 FROM table1 WHERE col2 IN (SELECT col1 FROM table2) OR (this one is rought, but you'll get the idea) SET @final = @final + '( DECLARE mycursor CURSOR FORWARD_ONLY FO...more >>

Hi I'm trying to use a function, returning a table in a Join like this SELCT A.Id FROM A JOIN dbo.MyFunc(@dDate) B on B.Id = A.Id My question is, will this function be called once or severel times dependant on the number of rows in table A. ? Also if I change the JOIN to this JOIN ...more >>

Hi I have this sentence to create a trigger: CREATE TRIGGER [TRIGGER_TIPS_LOG] ON dbo.Subs_Tips AFTER UPDATE,DELETE,INSERT AS SET NOCOUNT ON With the full version of SQL Server works fine, however with MSDE I get this error: Server: Msg 170, Level 15, State 1, Procedure TRIGGER...more >>

I have 2 tables in the database: Table 1 Name: EquipmentList Fields: Eq_Id, numeric(9) The ID field for the equipment Eq_Desc, varchar(50) The equipment description Table 2 Name: EquipmentSchedule Fields: Eq_ID, numeric(9) The ID field for the equipment---^ Res_Date, varchar(50) The re...more >>

What is the event sequence of Check constraints, Foreign Key, Rule, trigger and Primary key check When Inserting a record? TIA Roji ...more >>

I've been asked to provide a number of reports based on our phone logs, I am a network administrator, not a SQL Guru so I am finding myself mired down in my own code here - please help. I have a log table that in raw format might have the following data: Call_no Duration Root_C...more >>

I have a SQL statement listed below that writes the access priviledges for PUBLIC to a table. I would like to further restrict my SQL statement eliminate Microsoft's replication stored procedures (MSsin..), MSsubscription_Agent and dtproperties. Only User created tables, views, and st...more >>

Hello, Can someone please guide me on how to format a datetime field in a SQL query to retrieve the datatime field with leading zero's. For example: If the database has a datetime value like 1/7/2003 9:00:00 PM (mm/dd/yyyy hh:nn:ss format). The I would like to write a query that return ...more >>

I need to develop a database in Visual Basic/SQL server that holds word 2000 files. Is this possible?. If so, which kind of I field should I use?. Any clues/links about how to accomplish this? Thanks ...more >>

I have created a stored procedure to create a record into parent and child table. but indeed, i want to create a record into parent table, but one or many records into child table in the same transaction? How can I implement this? I don't know how to implement this. I know that there's WHILE c...more >>

hi, i've created my tables using CREATE TABLE sql statements, but now i need to create relationships between those tables. i need to do everything via SQL statements. i was wondering what the syntax is to create this and what would be the best way to go about this? thanks, mike ...more >>

Hi, I want to store the AVI files' length in a DB field (the length is in numbers of second). Can I define the field as 'datetime' field and store the length's value in it? And when I retrieve the value, can I format it directly to 'hh:mm:ss'? Thanks. ...more >>

I'm writing an application that allows users to import contacts from their outlook contacts into an aspx/C#/WebDav/SQL application. My database has a MasterCompany table (script simplified for this example); CREATE TABLE [dbo].[MasterCompany] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [Company...more >>

Can someone tell me why this code runs only one time? It should run 5 times... declare @order_counter char(1) Declare @monthyear as char(6) Declare @howmanyruns as int Declare @ordercounter as char(1) select @howmanyruns = 12 select @ordercounter = '7' select @monthyear = '20010'+@orderco...more >>

I am currently building an application that keep track all the changes in the data. If person A modified data in table 1, then my boss need to know that person did it? Any suggestion or point me to some articles, I am greatly appreciated, culam...more >>

How can I out the size of a particular table in my database? Thanks, culam...more >>

Do you know if yukon beta version of sql server is available for download, somewhere? ...more >>

Hi all, I have table name A. Basically every monthend I need to set up a sql script to run and rename that to: Let's say: Ajan03 Afeb03 Amar03 . . I know that sp_rename won't take paramaters. Is there any other way to handle this???? Thanks, tt...more >>

I have created a date filter that grabs data from the first day of the previous month to the first of the current month (i.e. 10/1/2003 - 11/1/2003). SQL: where entry_time BETWEEN CAST((MONTH(CURRENT_TIMESTAMP) - 1) AS varchar(4)) + '/' + CAST(1 AS varchar(1)) + '/' + CAST(YEAR(CURRE...more >>

Hello all, I have a non-normalized access database, and i would like to import it into SQL Server, but the table structure would be entirely different. For example, instead of First Name and Last Name, it's just Name in the .mdb. How can this be extracted as two fields when it's...more >>

Is it possible to write a stored procedure in SQL Version 7 whereby you accept a list of values? For Instance: my_stored_proc select OrderID from order_table ; Ok, the syntax is bad but you get the general idea of what I'm trying to accomplish. Andrew ...more >>

How receive events of table changes from a sql-server into my client application. ADO is unsupport this event changes. I create mssql-replication, but not understand how may make custom subscriber in my application. Please Help. Thanks. ...more >>

Help, Which feature can I use to find the size of a particular table in the database? Thanks, culam...more >>

I have a table containing "layers" of inventory purchases set up as such: PurchaseDate Qty Cost 11/07/2003 600 6.35 11/10/2003 100 6.15 11/12/2003 400 6.00 11/15/2003 500 6.25 I want to create a stored procedure query that ...more >>

I need to create a SQL Server solution that will either point to or store documents (.doc, .pdf,...) Along with the document, it should have ancilliary information like author, version, and so on. I've been told I can use BLOBs for this, but I've no experience in this. So, I'd just like to get so...more >>

Hi, --------------------------------------------- FYI: SQL Server 2000 SP2 Windows 2000 Professional SP2 Visual Basic 6 Enterprise SP2 DB Connection uses the MSDataShape Provider The recordset in question is a child Children are referenced using ADO 2.5 library ---------------------------...more >>

What should I do about the following error message.. Cannot add rows to sysdepends for the current stored procedure because it depends on the missing object 'VideoStore.jheavey.Proc_InsertCast'. The stored procedure will still be created....more >>

I would like to get your expert opinions on changing a pure Visual FoxPro (VFP) application to an application which just have VFP on the front-end side and the data will be in SQL 2K. ...more >>

Hi I have one column which is named 'name' but this includes the first name, last name and middle initial does any one know if it is even possible to extract these into separate firstname,lastname,and middle initial. thanks for any help Shaun...more >>

I have some trouble using a stored procedure. Whenever it produces an error (insert or update or such with severity no higher than 16) it will produce an exception in my calling code; making me anable to retrieve ANY return values or output parameters The proc do all the work I have assig...more >>

I'm not sure the best way to handle looping situations when creating a stored procedure. Say you want to call a Proc and pass it between 0 and 10 of a particular value. I will be calling the procedure from a VB.Net client. I setup 10 @variables and if there are only 4 values to be passed...more >>

Hi. I am using Access 2000 and SQL 2000. I have a few stored procedures that I run using Access SQL Pass-Through Query. When the result set I make a table in Access to use for reports. I find it much faster to EXECUTE the stored procedure on the SQL server and would like to store the res...more >>

Hi, All: I apologize up front for posting this long, sad story (with DDL and DML), but I could only come up with a bad way to try and solve my problem, and I can't even get that to work correctly. So maybe someone could give me a better way to do this. I have to create a flat file to be up...more >>

Hello I have a table with a column named 'References' This column includes different number of references deliminated with space -e.g Row 1: abc-123 abc-123 abc-12 Row 2: abc-12 Row 3: .. etc How can I catenate this string to execute a query with one reference per row Thanks!...more >>

I am using ADO to connect to a SQL database. I need to find a record and if it exists update it, or add one if it does not. All this is working of sorts. My question is what is the quickest way to find if the record is there as I have 11000 records to do this with each run. I am using fin...more >>

Hello, I was wondering if anyone could help me with an SQL Statement that will always return a number formatted with two decimal places. Thanks J...more >>

Hi All I have asked this question before but I seem to have lost the reply. I have field with a URL in it something like below www.mywepage.co.uk/development=150 What I want to do is grab the number after the equals sign, no matter what the URL say's as long as it has a equals sign, I w...more >>

Appreciate a suggestion for supplying date calculation eg: 'current_date minus 2' to a DTS package which extracts data from source where the date equals today's date minus two. Tx. Ola...more >>

How can i set a field as "Null" or "Not Null" after the table has been created? tanks Fernando ...more >>

Hi all, I have a stored procedure that isn't behaving as it's supposed to. I'm doing an INSERT and after that I'd like to fire another stored procedure, but that procedure never fires for some reason. I have tested the procedure I'm calling inside the SP in Query analyzer - and it works fine ...more >>

Hi, I have created a stored procedure, and can read it from VB. Now I want to add parameters to the procedure, and then pass them to SQLserver from VB before reading the result of the stored procedure. Please enlighten me. I used ADOBD methods for accessing the database. many thanks ...more >>