Hi Al I have following SP CREATE PROCEDURE Proc_Get_Ibusiness_map ( @cmr_id tex ) AS BEGIN Declare @sqlstring varchar(2000) SET @sqlstring = 'SELECT * FROM OBS_ibusiness_map WHERE month_name IN ('+@cmr_id+')' EXEC ( @sqlstring) END However this SP in not getting compiling, givin...more >>

Hopefully in the right group, otherwise sorry for that. Having the following query: SELECT fieldA, SUBSTRING (fieldB,0,10) as SubField FROM JustaTable This works fine but we want to count all the same results for SubField grouped by fieldA Is this possible with just some sql statements ...more >>

...more >>

Could anyone help me with an SQL statement in VB6 please. I have a date field in the database, after a user input a date in a text box, I want the recordset to return all records with the month and year as the textbox. Example: user input 5/13/2004 Recordset: Returns all records from May 1 to 13...more >>

Hi All Bit of confirmation needed really, as I've just come home and found that my PC is only running SQL 7.0, when I know the below code only works in SQL 2000 (I think!!!). What I want to do is put a particular DB in single user mode, do a DBCC repair_rebuild then put it back to how it wa...more >>

HEre is a not so simple example of what I am trying to accomplish. I may have this set up correctly, but I wanted to get some opinions. I am using SQL Server 2000, by the way Table: Manufacturer Fields: Manufacturer_ID, Manufacturer_Nam Sample Data: 1, HP; 2, Canon; 3, Epso Table: Equipment_Ty...more >>

Dear All, is there simple query to do the select by range, e.g. select top 10-20 * from table where fieldid>10 Thank you. regards, Angus ...more >>

I need to do some processing, and an extended stored proc will be ideal. But ideally I would like to return type sql_variant from the xp. Is this possible with SQL Server 2000 Thanks ...more >>

I have a stored procedure that is throwing an error 291 when I'm trying to convert datetime to a timestamp. The table I am inserting into needs to have a timestamp My code in question looks like this: , Convert(timestamp(8), getdate()) as upsize_ts Can any one help to see what I am doin...more >>

ISQL is very slow to run a script which contains 220 procedures, it takes 5 minutes to run. ISQL /E /S LOCALHOST /d Database1 /i C:\joe.sql BUT I run queries of this script on query analyzer, it only takes about 7 secs. I really want to know why ISQL is so slow and How can you make ISQL ...more >>

Given the following abbreviated DDL, I'd need to know the difficulty level of the most difficult class that each student is taking. Can one SELECT statement return one row per student - and that row include Students.[StudentID] and Classes.[DifficultyLevel] where [DifficultyLevel] has the highe...more >>

hi, i need help with this query: i have 3 tables 1. FEED Table columns: id_feed product_name MPN UPC Price id_merchant 2. Products Table Columns: id_products product_name MPN UPC id_Category 3. Category Table Columns: ...more >>

Hi, In an asp page that includes a transaction I call a store procedure. In the store procedure I update a record before I select all fields in it. But I find the value of all fields is the value which I updated. Why?I think it is caused by the transaction.But I don't know how to resolve ...more >>

I've got some webform data stored as varchar that is ultimately converted to int. Unfortunately, I didn't have error trapping in right away and I have some rows with columns containing fractions like '52 3/4' (Javascript for some reason recognizes this pattern as a number). I can't seem to find a...more >>

Hi! I'm trying to retrieve a rather large dataset (around 6000 rows) from a MSDE SQL Server database using the following query: SELECT [Date], [Open], [High], [Low], [Close], [Volume] FROM Data WHERE [PermNo] = 12060 AND [Date] >= '01-01-1980' AND [Date] <= '12-31-2003' When I execute th...more >>

Can I execute a stored proc from within the from clause of a transaction... Like SELECT * FROM (EXEC stored_proc) ...more >>

Hello, I need to update fldx in tblTtemp with values from fldx in tblDataSource2. tblTtemp contains a date field and an ID field with unique ID's (unique not required). tblDataSource2 also contains a date field and an ID field, but the IDs in tblDataSource2 are unique only within a mo...more >>

I have inherited a database. When I look in the list of users, I have one user whose "name" is different than the "login name." In this case, the "name" is "dbo" and the "login" is "userX" I need to copy this database to a different server. But when I run an import or an export, the ...more >>

Does anyone know how to add a column to a table using the Alter table command, reposition the column (knowing that the column position is irrelevant) without having to create another table (temp) or otherwise and re-insert data into the new re-positioned table. Bill ...more >>

Hi guys, I have a moth end strode proc which will process millions of recodes in a table. can any one advice me how to optimize it to the best. and samples that I can follow plz .. :) Thanks -Aruna ...more >>

Hi all, I have a simple delete who take 2:30 minutes to execute. My table contain 50 000 records. configuration: Sql Server 2000 SP, Computer: Dual processor Zeon 2.4GHZ(each processor), 4Gig RAM, Hard disk 60Gig. there is no foreign key referencing this table and this table does not co...more >>

I have a stored procedure created that begins with: "CREATE PROCEDURE sp_MyStoredProcedure @datetofind Int AS Select statement.... etc." The Select part of the procedure does execute successfully when run in Query Analyzer. I'm wanting to pass the following value as a variable to ...more >>

Hi All I know this probably sounds strange, but is it possible to do a reverse 'In' statement? My query, with the relevant IN statement edged out, is as follows: SELECT SP.NAME AS CENTRENAME, SC.NAME AS CATNAME, Sum (IL.QUANTITY) AS QTY FROM SALESCENTRES AS SP, SPOOLEDINVOICES AS IH, SPOO...more >>

Hi, Is there any way to get this information using a Trigger that is fired during an insert, update or delete? thanks, Ellie ...more >>

Hi ...my gurus .. I need to find view alias column and dpends column within a select , but it seems without any relationship between them ...please help .. use Northwind go CREATE VIEW dbo.testview AS SELECT TOP 5 EmployeeID, CustomerID AS aliasCustomerID,OrderID AS aliasOr...more >>

Hi, I was hoping for some suggestions on a query to show (only) duplicated rows in a table... I am importing (via DTS) from old legacy tables and need to identify which records I will need to deal with prior to importing (as they would conflict with the primary unique key obviously). The p...more >>

we had a column description set at nvarchar(4000). we didn't have enough space so I said set it to 5000, but nvarchar only goes to 4000. we then set the column to varchar(5000) then varchar(8000). each time it gave us a warning that there will be data truncated. NOW we can only put in 1023 c...more >>

I wrote a stored procedure that needs to use the Inserted and Deleted tables created by the trigger. Can a stored procedure use the deleted and inserted tables? or do you have to pass the procedure values from those tables? I am assuming the procedure doesn't recogonize the temp tables...more >>

Hi, Is there a way to get more information why this job is failing? JOB RUN: 'Log Shipping copy for SQLD.Orders_SQL_logshipping' was run on 5/10/2004 at 2:15:05 AM DURATION: 0 hours, 0 minutes, 6 seconds STATUS: Failed MESSAGES: The job failed. The Job was...more >>

Hi, I'm trying to step through a list of Database names I have in a table and do relevant selections from each database. When making use of the USE command to set the Db it throws and error stating that the database name cannot be found. Below is the t-sql I'm trying to run. I would appre...more >>

Hi I have a item scheduled to run an table update on my full text index. It fails when the schedule runs with this message "Executed as user: NT AUTHORITY\SYSTEM. Full-text change tracking must be started on table '[dbo].[JournalEvents]' before the changes can be flushed. [SQLSTATE 42000] (Error...more >>

Hey I have one system, I can't seem to see what is different then other systems I have, other than number of users... I get some strange deadlocks in a sp, using only #tmp_tables.. (selecting with (nolock) hint on) Then I also get some strange lock request time outs.... is there a way t...more >>

Greetings, In a VB/C# .NET Windows application, I need to programmatically generate = T-SQL script to create various tables, stored procedures, etc., in a = given database into a single text/sql file. In essense, I want to make = a backup of user-defined SQL Server objects. When the object i...more >>

I need to calculate a date for one year ago using this field: Armast.finvdate; so that I can sum all the invoices for the last year from today. I'm getting close with this line: WHERE Armast.finvdate>GetDate()-365 HOWEVER because of the time stamp portion of the dates in the comparison,...more >>

Can a Insert and a delete be done in the same trigger? I want to put a copy of the records that are going to be deleted into another table to hold for a time when I delete them. The old trigger code is here ---------------------------------------------------------- CREATE TRIGGER delete...more >>

I want to alter column that it's part of primary key. I tried to use script as follow. ALTER TABLE table1 ALTER COLUMN column1 varchar(30) Before i run this script, it generate error. Server: Msg 5074, Level 16, State 8, Line 1 The object 'PK_table1' is dependent on column 'column1'. ...more >>

We have some legacy code that nolonger works after we upgraded to SQL 2000 SP3a & MDAC 2.8. The Transact SQL is a stored procedure, and we bind a return parameter to the connection and call SQLExecDirect it now fails. The following is an example. sSQL = "?=sp_GetNext 'value1', 'value2', ...more >>

Hi group, As part of a project I have been doing research for, I have started to notice that in Query Analyzer, when I right-click on a stored procedure and select: "Script Object to new window as Create", the SET commands for QUOTED_IDENTIFIER and ANSI_NULLS gets inserted Looking at the script ...more >>

I have two SQL servers. one is SQL 7.0 (let's call it sql7) and another is sql 2000 (sql2000). I created linked server on sql7 for sql2000, which means I can access tables and views in sql7. If I run SQL like this, it will succeed. select .... from table (execute locally) Update remote....more >>

Is there a query I can write that will give me all of the index names on a database? I have one that I am using, but I already know the Index names. My boss has a nasty habit of adding Indexes at a customer location and then doesn't write them down, or call and tell me so I can add them to ever...more >>

Hi, I have list number from my client application: string1 = 23, or string1 = 23,24, or string1 = 23,24,344,453, I have that string1 passed to my Stored Procedure already BUT I have some trouble make it works like this: Select * From SomeTable Where PKID in (string1) Note tha...more >>

I read about a tip in SQL Server magazine that stated that large quries (greater then 100 lines) in a stored procedure can cause performance problems and that it is often better to break a single 400 line stored procedure into 4 100 line stored procedures. I have looked for any information about th...more >>

How do I develop a MS Access 2002 Multiuser application that can be used by staff who do not have MS Access installed on their computer? The database is simple design, but needs to be used by staff that are off-site and not connected to the network. Please help Thanks...more >>

I am trying to get the output from xp_cmdshell (or other stored proc for that matter of fact) in a select statement. The approach I have taken is as follows: SELECT a.* FROM OPENROWSET('SQLOLEDB','localhost';'sa';'***', 'insert into dse.dbo.tmp exec master..xp_cmdshell ''dir c: '' select...more >>

Could some one please show me how to format the GetDate Function to display just the date so it looks like this.. yymmd Thank yo Kent Prokopy...more >>

Hello everyone, We are looking for beta-testers for our new product - Scribe Report Generator. At the moment, Scribe supports 5 relational databases, including MS SQL Server 2000, Sybase and Oracle. Very briefly: Scribe is written in Java, is based on visual programming and can be extended ...more >>

Hi all, I have a PowerBuilder 6.5 application which will be run on window's platform with different locale. When I try to save some Chinese character on SQL Server 2000. I found that only computer which have same locale can view the data, otherwise some 'SPECIAL' characters will be display...more >>