I need to get process info like process id, user, database, host, application etc from a query analizer result set. Someone knows how? Thanks...more >>

HI Is it an undocumented feature of SQL server, or just something that 'just works' that you can use a goto statement, to goto a label that's WITHIN a while loop!?? ...more >>

I have a table with a contraint to avoid duplicated keys. alter procedure AddKey @key char(10) as declare @errmsg varchar(20) insert into TheTable values ( @key ) if @@error=0 set @errmsg="" else set @errmsg="duplicated key" select @errmsg as result return @errmsg Is it possibl...more >>

Hello Experts, Assume i have a table with 100 records. How can i select all odd records (i.e. record 1, 3, 5...) or even records (i.e. 2, 4, 6)? i am using MS SQL 2000. Thanks, Benny *** Sent via Devdex http://www.devdex.com *** Don't just participate in USENET...get rewarded fo...more >>

I have a process(SP) where I use xp_cmdshell to read a directory and get a list of files. These files are named like this: 601342.pdf 601355.pdf The first 6 characters are an Invoice number. I then pull this data into a Sql table where I need to match the first 6 characters(InvNbr) to an...more >>

Hello Group, i m bit new to sql server ,programming.The issue is , i am storing html contents in a field of table , now this filed carries all html tags ( e.g. <td>,<tr>,<a> etc) as well , now i want to filter the content , is it possible through procedure that the contents of this field come out...more >>

Currently we use text field in table to store HTML and in search like to = eliminate HTML tag for example, given text - "<P><STRONG>Access High = Resorst<BR>" or <TD colspan=3D"3">Ashish</td>. I just want to search = 'Access High Resorst' or 'Ashish'.=20 How do I eliminate HTML tag using query...more >>

Hi All After insert a record in to the table, I want to update some columns in that table by using trigger. My trigger is "CREATE TRIGGER t_InsertMyDetailsUpdateCost ON MyDetails FOR INSERT AS Update Sql" Can any one help me to write the trigger? ...more >>

I would like to run a script in QA that modifies a table in one database using data from another database. How do I reference a table in this second database? What if the second database was on a different server? Thank you in advance....more >>

Not really a programming problem, but related to it: I sometimes need to move a database from my dev server to the live one. So I backup and restore it (just to a file, not tape). However, sometimes this seems to restore an *older* copy of the database that the one I backed up!!!!! Why?? I t...more >>

Has anyone seen anything or found an elegant solution to the Wheeler's RSI technical indicator for stock price movements. If so, any ideas on creating a view, SP or function would be very much appreciated. It's defined as 100 * (Mean of Price change on up days / (Mean of Price change on up day...more >>

Hi all and thanks in advance, please bear with me as I will try to explain my problem: I have a .NET dll which I have created (written in C#). which I would like to access through T-SQL's extended procs. I have successfully managed to do this on my local machine which is running SQL 2K + XP ...more >>

Hi Group, I have a master and detail table like so, Master ( ID int PK, [...] ) Detail (MasterID int PK, DetailNum int PK, [...]) The idea is that DetialNum will start at 1 and count up for each MasterID. Example: {1,1,[...]}, {1,2,[...]}, {1,3,[...]}, {2,1,[...]} I want to add records...more >>

Hello Everyone, I have a big favor to ask. I have some data that I need to take out of some tables and report them in an excel spreadsheet. I can do the export just fine but I'm having trouble formatting the data how I want it. Here's my tables: Table: Questions QuestionID | Question |...more >>

I have a stored proc to add records to a table. I would like to also add a record to a 2nd table immediately after the insert to table 1. I know how to do it in code, but thought that a trigger would work better. I have written triggers before but this one has to use the newly created identity...more >>

Hi, I hope some one can help me, I want to select from a single table the records with the highest value in two columns.=20 E.g. (MAX IssueDate and OutTime) CarNo IssueDate OutTime IssueID =20 -------- ---------- -------- -----------=20 XT-017 2004/06/04 16:38:24 298307 <--...more >>

Is it possible to use a table name as an input parameter in a stored procedure? i.e. CREATE PROCEDURE sp_Proc @var1 nvarchar(150), @tbl nvarchar(10) AS UPDATE @tbl SET VO_Bit = 0 WHERE (VO_ID = @var1) GO (The above says that @tbl must be declared) Of course, all the dynamic tables...more >>

I am trying to do my first format file. Let's say it's a fixed length field delimited text file with the following example data: 11 2982jones samual 23988 12 2911smith stan 22324 .... Please help me with the format file to import this: 8.0 5 1 SQLCHAR ...more >>

One of our software customers is getting an error from the SQL Server ODBC Driver that says: "String or binary data would be truncated." when trying to add or update a record in one of the tables using one of our programs. I don't understand why that customer is getting truncation errors when ...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. same samples that I can follow .. Thanks -Aruna ...more >>

Hi, I will later on in the year be moving an application from Unix/ISAM to Windows/SQLServer. I will need to port a simple program to Windows but I am no familiar with windows programming. My question is, what would be the prefered programming language to write a program to : Connect to...more >>

Is there a way (a relatively simple way) to search an entire database for a word or phrase in the data without searching each table, each field, etc, etc. Thanks. ...more >>

Hi All, I want to trap the errors in stored procedure(SP) while executing it. (Like VB can we use exeptional handling in SP) Can anyone help me how to trap the error in SP while executing it? Thanks and Regards, Suresh P ...more >>

Hi How data synchronization can be done between a MS SQL Server and a MS Access database. The source is a MS Access MDB which is in a remote location and it has 2 tables which have to be synchronized. Regards ...more >>

Hi how to find the number of active connections for a particular db in sqlserver in the folowing methods? 1.using stored procedures 2. using ado.net. i tried with sp_who, and sp_who2 these get me the number of connnections to whole Sqlserver not to any particuylar db.. any sp avail...more >>

I am trying to optimize the following query and would like some assistance. For one, there is an index on transactionhistory.postdate that this query is not utilizing. I have already updated statistics and still I get 'Clustered Index Scan'. Any help would be appreciated. I have also included ...more >>

I need to code a button in a .Net (windows or asp) app that will import several fixed-length-field text files into existing SQL Server tables. I've written stroed procs before but have not deralth with DTS or bcp before. Any help, links, or direction would be much appreciated! ...more >>

Hi there! I've got a problem with an ADO Command object, which is executing a long running (~5 min) stored procedure asynchronously. The problem is that the State property during the execution suddenly (after 5-15sec) evaluates to adStateClosed, even though the sp did not complete yet. The qu...more >>

Does SQL Server have the capability to declare a variable to be of particular table's column datatype? This is a featur available in Oracle. Please let me know. I am not able to find any documentation on this in my SQL Server book thank Kanan...more >>

Hi all, I try to delete about 4000 rows in a table which contains 150000. This table is referenced with DRI in several other tables (inheritance or foreign key). The firts instructions of the stored procedure delete the rows in the associated tables, and the last one is as simple as : dele...more >>

I'm creating my function but I can't use getdate() inside my function. Why? It's more complicatede but this is some example: CREATE FUNCTION Time2Date (@Time As Datetime) RETURNS Datetime AS BEGIN RETURN ( select max(date) FROM table where date<getdate() ) END Thank ...more >>

Any advice greatly appreciated. Cheers, j. ...more >>

I am try to build an SP that queries two table, Filters and Locations. Each filter is in one location only. I want to list all the locations, and for each location, list the PartNo and the number of filters in that location, eg: PartNo, Location1, Location2, Location3 ----------------------...more >>

What would be the correct syntax if I have the following scenario: Appt. Code Client_id 1/1/04 A 3 2/15/04 BF 3 3/9/04 CD 1 3/18/04 NS 1 5/25/04 NS 2 6/10/04 CD ...more >>

I am try to build an SP that queries two table, Filters and Locations. Each filter is in one location only. I want to list all the locations, and for each location, list the PartNo and the number of filters in that location, eg: PartNo, Location1, Location2, Location3 ----------------------...more >>

Hi there, just wondering if anyone is able to help. I am looking to change the name of a role I have in a database. Say the name of the database is "Customers" and the Role I want to change is "User". How would I change the name in sql to "Users"? Thanks for any help. ...more >>

Hi I am trying to write a store procedure with a query that takes a comma separated list and use it as the criteria. the code is below. ************************************************** CREATE PROCEDURE [dbo].[getPersonbyC] @varC varchar(20) AS declare @varC1 varchar(100) set @varC...more >>

here is an example.. the following stored procedure does not work it returns Server: Msg 229, Level 14, State 5, Procedure BENESP_GetJournalEntrySubTypes, Line 10 SELECT permission denied on object 'UsersPermissions', database 'bene_users', owner 'dbo'. now the database the stored proc is on...more >>

Hello database A is contacts database B is clients they both have columns "email" and "accountno". When a record goes into A, I would like the accountno field in B to be filled where A.email=B.email. Can anyone advise? Thanks Soc. ...more >>

i'm trying to figure out file sizes of files on a remote machine(doesn't have sql installed). My sql server and the remote machine are on the same network and use the same credentials. i've tried using 'net use' to map the drive of the remote machine, then using xp_getfiledetails to acces...more >>

Hey All, I am trying to figure out how to DeNormalize a table for a report. The closet thing i have been able to find is an access crosstab, but i need to do it in TSQL. TRANSFORM Avg(CH.p_Date) AS AvgOfp_Date SELECT CH.RecID, CH.p_name FROM tbl_Campaign_History CH GROUP BY CH.RecID, CH.p_...more >>

I have a cursor inside of a stored procedure. I try to use the sp's input variables after the cursor is closed and it can't recognize the variables exist. Help? SP text is below: create procedure sw_AmendmentType_Update ( @stringOfAmendTypes varchar(60), @amendid int, @lastuser...more >>

HI There, Could someone help me out with the syntax of executing a stored procedure in query analyzer. I have tried the code below but I keep getting an error, what am I doing wrong? Sean exec GetLoginForUser @Username 'test', @Password = 'test', @errmsg1 CREATE PROCEDURE GetLoginF...more >>

I have been assigned a project. I am totally new to SQL/VB6. I need to create a lookup scenario for end users to search the database by name or zipcode The name or zipcode will change each time the user makes a request from the database Since the user will have no knowledge of writting SQL querie...more >>

HI There, Could someone help me out with the syntax of returning an output parameter from a stored procedure? I have tried to use SSelect @RoleName = G.Name, but this returns an error. Could someone help me out with the syntax? Sean - thanks in advance CREATE PROCEDURE GetLoginForUs...more >>

I have been assigned a project. I am totally new to SQL/VB6. I need to create a lookup scenario for end users to search the database by name or zipcode The name or zipcode will change each time the user makes a request from the database Since the user will have no knowledge of writting SQL querie...more >>

I have SELECT statement declare @quantity int set @quantity=3 select value=case @quantity when 0 then 10 when 1 then 20 else 30 end It works. But I don't know how I can use < operator, something like this: select value=case @quantity when < 0 then 10 when <5 then 20 else 30 end ...more >>

Gurus Is there a quick way to find how may total transaction(could be remote as well) took place today, how may succeeded and how may failed(may be with the help of writing some code). My manager don't want to use auditing O whenever any thansactions fails I get a popup or something I appreciate...more >>

I would like to write a util to copy an SQL database over to another server. Can anybody see anything wrong or offer any suggestions over the sequence below? 1. Source: exec sp_detach_db SourceDB, False 2. Copy SourceDB.mdf -> DestDB.mdf 3. Copy SourceDB.ldf -> DestDB.ldf (And any othe...more >>

Problem Description: I have a Archival process in a VB.Net app that needs to kick off a stored procedure on a SQL Server 2000 box. This sproc should accept one parameter and execute 10 Select statements. These Select statements should return as XML and be output to a file. Because of the...more >>

I have inherited the following code and the looping function only give me MAX(amount_paid) for first record. I have never seen this particular looping method. Question how can I either get the existing looping to work or what should I change it to? CREATE PROCEDURE dbo.fhkivrexport AS D...more >>

Hi all - I am trying to retrieve the username of the logged on user with SELECT USER_NAME() but when it is executed by a member of the System Administrators role it returns 'dbo'. Is there a way I can retrieve the actual user and NOT 'dbo' Any help is appreciated. Thanks! ...more >>

Do Table variables have any problem with UDFs? I'm trying to re-write some temporary table code to use table variables. ---- Start Code ----- -- Create temporary table for CityStateZIP declare @tblcsz table ( kCityStateZIP int ,sCSZ varchar(255) NULL ,kCountry varchar(10) ,kAddress...more >>

I need to get process info (process id, user, database, application, host etc) as a query result set from query analizer, is it possible? Thanks...more >>

How do I generate a complex autonumber involving combination of date, text, sequenceNumber and also some logic behind the combination. Can I do it using a Trigger? In that case where do I store the Sequence Number. Any Ideas please... Thanks in Advance....more >>

Hi, I'm creating a stored procedure to compare record counts for the same table on an AS400 versus a SQL box. I've got both boxes linked to my SQL box. I've created the following sproc: CREATE PROCEDURE CompareSFM(@TBLNAME varchar(50)) AS exec(' select '''+@TBLNAME+''' as [Table Na...more >>

Is there any way to create our own constants in SQL Server 2000 which can then be used within stored procedures? ie, so that where we do an 'ISNULL (Field, Val)' we can in place of Val just insert a constant name in different SP's? TIA ...more >>

I have a problem regarding preformance when quering a linked server in SQL Server 2000 In Access I have a linked table based on a connection to an ODBC connection (Ingres ODBC driver 3.5) I'm joining 4 tables white very good performance (around 150 000 rows in each tables) Now I want to do the s...more >>

Hello, I am curious on how to eliminate the time it takes for my select statement to return. I have 9 Inner Joins and 2 Left Joins. I am in the process of optimizing the query, but I am also curious on other methods to increase the query return period, like Indexes on tables. Anyhow,...more >>

Hi, I am migrating my access database to SQL Server. I have a crosstab query in Access which is TRANSFORM Sum(tblCurrentMonth.[Total Price]) AS [SumOfTotal Price] SELECT tblCurrentMonth.[Long Code], tblExpenseCodes. [Report Client], tblCurrentMonth.[Billing Cycle Name], Sum (tblCurrent...more >>

Hi- I am having trouble with a query, perhaps you comeone can help. I am trying to insert into a table some values that are a result of a select statement. I am finding that this won't work. What is the best way to do this. I am attaching the failed query. THanks!! Insert into Xref_com...more >>

i am trying to create a trigger which when I insert a "y" on a student table in insCheck column, instructor table will create a record of the same person The reason is there are two tables are in my DB, student and instructor table. Student can be a instructor so whenever insCheck conlum in studne...more >>

I have a query that joins 2 tables and reports on those transactions in a given date range,........ Is there a way to report on the other data? as in list all parts that do not have a corresponding record in the download tabel for example? my original query looks like this, but it reports on thin...more >>

We have a table which hold three fields. HID - An Id field for hierarchal information HName - The friendly name for the field HPID - The Parent ID of this field. I need to build a query where I can input a HID value and it will return all the sub values, all the way down through the tree...more >>

Hello, when I do this SELECT names FROM users I get this results: adam joe tim mike How can I make a function (or is there any other solution?) to return only one row as delimtered like this: adam, joe, tim, mike Thanks for any help in advance Andreas ...more >>

Hi, I'm using an SQL database to track my web page's hits. I have a table to record the latest IPs to can I decide wether the hit is unique or a reload. I have tables to record different thing about the hit. I'm storing strings like path, user agent in separated tables associated with an autoi...more >>

Hello All I need to read a SQL Server table into a Web Page and within the Web Page to permit my users to make changes to the records, delete or add new records and then save the entire contents back to the SQL Server table back. The functionality I am looking is almost the same as In the S...more >>

I want to use Excel as a frontend to maintain a few SQL tables. All I want to do is read the contents of the SQL Server Table into a Excel worksheet within Excel now I want to change, add or delete entries and writeback this data into the SQL Server table. Please would you be kind enough to sh...more >>

Hi all, I used to run SQL Script using SQL Profiler found on my SQL Server Developer's Edition. Problem arise because I need to send some script to my client, and my client use MSDE as the engine. Q: Anybody knows how to run generated SQL Script using tools found in MSDE installation? I am ...more >>

I want to write a script like following select name from (exec tsp_nameview) ' tsp_nameview is name of a stored procedur I don't want to create a temporary table , like create table #nameview (...), then insert into #nameview exec tsp_nameview, because there are too many columns in the...more >>