I'd like to strip various leading characters from a registration number. for example MA1234 = 1234 987651 = 987651 Q10001 = 10001 The code below works but it's gotta be inefficient(and I have to account of all lengths). I thought I could use a WHILE loop that loops through the length o...more >>

Hello, I have problems with connecting to SQL Server from ESQL/C application. Error message is: SQL Server Message 19703: Unable to connect: SQL Server is unavailable or does not exist. Whole thing worked on other machine, and other system. On this machine, where application is executing, i ...more >>

hi, Good Morning...While i am working with SQL Server with my application. That is while iam invoking more than two transaction, with payment server. i am facing the following error. Your transaction (process ID #19) was deadlocked with another process and has been chosen as the deadl...more >>

is it possible to do that except trigger? ...more >>

Hello, ..Net allows stepping through sql store proc's as part of its debugger feature; however, somehow it's no longer work for me, Visual Studio just hangs in the middle of the execution. Is there a way to step through store proc (set break point etc..) using Sql Sever Enterprise Manager or...more >>

How to use IN clause in SQL Server 2000 envolving more than 1 field? For instance: select * from mytable1 where myfield1,myfield2 in (select myfield1,myfield2 from mytable2) I already tried "where <myfield1,myfield2> in", using "<" and ">", but it generated error. ...more >>

In my SQL Server 2000 (which is installed on my PC which is in Office) there is one table with following structure: MYTABLE Id char 7 Description char 255 EntityID char 47 vdata image 16 tstamp timestamp 8 This table has more than 1000 records. I have another SQL Server 2000 (which ...more >>

Hello: I have the following trigger working fine - the problem is that I can do an insert into the table? What is the problem. CREATE TRIGGER [AuditInsert3] ON dbo.booking_up_revb FOR insert AS INSERT INTO AuditTrail2 (TableName,booking_no,actionTaken, ActionUser, ActionDa...more >>

Hi, I have a query 5 tables join as following: SELECT u.agent_name, u.telephone, u.email, a.address1, a.address2, a.city_name, a.state_cd, a.postal_cd, p.product_id, p.name, p.number, c.cc_type_cd, s.promote_rate FROM agent u, addresses a, cards c, produ...more >>

I've got a problem with a trigger I've set on a table. This is the code for the trigger: CREATE TRIGGER Update_Allowance_Rule ON [dbo].[setting_allowance_rule] FOR UPDATE AS UPDATE [dbo].[setting_allowance_condition_rule] SET [dbo].[setting_allowance_condition_rule].rule_description = del...more >>

I would like to select rows from a table where another related row in the same table doesn't exist and the relation key is more than 1 column. Example: For the table, keycol1 keycol2 datacol1 -------------------------------------- 1 1 B 1 1 ...more >>

In our application, we need read xml from our SPs, now we are using EXEC @resultcode = sp_OACreate 'MSXML2.XMLHTTP', @objRef OUT .... to access the remote url, it worked fine but recently it always causes my sql server crash, and message is SQL Server is aborting. Fatal exception c000...more >>

I need to replace the data in one column and keep the rest. I have 18,000 row. I tried various import/export procedures, but i'm not certain that the data is kept in a consistent state. I have two tables: One with working data (14 columns) and one with the new data (2 columns). each table ha...more >>

Is there a way to cast or convert a int type value to HEX in T-SQL (trigger or stored proc) ex 1000 to 03e8 THanks KT ...more >>

We are in the process of converting our Access 97 Database to SQL 2000. We will be using a Access 2002 front end. We are in the process of rewriting our DAO code to ADO. I am sure this has been asked a thousand times, but how can you create a User defined function to do the equivalent of t...more >>

I have a trigger (code below). Where do I put in IF statements to kick off the CASE? I put in a commented out example of the IF I want to do in the code. Any help would be appreciated! ALTER Trigger dbo.CreditLimit_tg on membership_detail FOR insert, update as...more >>

Hi, All, When I use the 'Union' to get result from two tables. which table's info will be shown first? Is table1 or table2? (without order by) From BOL, Table1 and Table2. Table1 Table2 ColumnA ColumnB ColumnC ColumnD char(4) int char(4) int -...more >>

does sql allow a having clause within and update statement? i have the following update statement that shows what i want to do - but i get a syntax error near the having clause - im thinking that sql must not allow this in an update clause. any ideas on another way to do this?? UPDA...more >>

I programmed an ASP web app a while ago and all worked fine. All the sudden one day I start getting the following error when trying to Insert, Update or Delete: Microsoft OLE DB Provider for SQL Server error '80040e31' Timeout expired /admin/StoreManager/OrderDetail.asp, line 142 Th...more >>

Hello everyone. I'm trying to kill process on selected SPIDs only. Select works, killing doesn't. It complains on syntax near 'kill'. Please advice on how to make it work. Appreciate it! Here's the code: -------------------------- declare @sp_id int declare @dbname varchar(50) DEC...more >>

I have 3 tables: table countryPrice: productID countryId price 1 Italy 90 1 England 110 Table country: countryId countryName 1 Italy 2 England 3 Germa...more >>

SQL 2K I am creating a table for holding data used for data wear-housing project. This table will eventually going to have millions of records. Planned to have a primary key and proper indexing in this table. This table will be queried against various columns and will be refreshed (fill data)...more >>

Dear all, How can i select only numeric data from a varchar column. I tried this simple sql: select * from test where isnumeric(name)=1 The result i get are: ID Name 1 23D02 2 23 3 33E52 4 456 Why Does it include the row "23D02" and "33E52"? Whats the problem? An...more >>

Hello, Is there any way I can compare a name column in one table to a column in another table using the soundex function. I want to loop through the names in one table and compare them with similar sounding names in the second table. Thank you, Rohith ...more >>

- Is it possible to call another stored procedure from with one? - Can you store a table (or query result set) in variable/object within an SP? - I have a few complex views that use each other as data sources, i.e. I designed one view, created a new one and used the previous view as a data ...more >>

/* I'm having trouble with a query...and was hoping that someone could shed some light on this for me. TIA~ PJ */ go create table beer ( beer_id int primary key , brand varchar(50) ) go insert beer (beer_id, brand) values (1, 'Bud') insert beer (beer_id, brand) values (2, 'Coors')...more >>

Is there a way to do this? I have tried the following but get an error as it seems I cant use a variable name as the parameter for the TRUNCATE statement: DECLARE tables_cursor CURSOR FOR select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsUserTable') = 1 DECLARE @Name nvarchar(250...more >>

Can i use Sql server with Cobol sofware program and How ? Thanks Vinz ...more >>

i have a SP that builds a t-sql script. I use a varchar variable to build the script. I have declared the variable as varchar(8000). When I run the debugger, this variable gets truncated to 255 characters. Any ideas? SQL 2000, SP2 on a Windows 2000 SP3...more >>

We have about 50 customers in hosted environment, each running databases withi identical schema but different names. Part of the application traps errors and records them in a log table. I want to consolidate these logs into a single database so we can easily view errors without having to look a...more >>

Hello, I am trying to write a query which will identify only those members who have used either one or more services in a given year. 1 MemberID can have 1 or more UnitID's. This means that if I group the report by UnitID's, I will see multiple MemberID's. I just want to see 1 MemberID b...more >>

Why are these function nondeterministic when the result is a datetime??? Brandon -- "In the beginning the universe was created. This has made a lot of people very angry, and has been widely regarded as a bad move." - Douglas Noel Adams (1952-2001) ...more >>

Is there a way to programmatically check the syntax of a dynamically generated (or user entered) SQL statement from a client application? We are developing a web-based adhoc reporting app that will allow a user to manually enter a SQL statement and I want to check the syntax of what they enter...more >>

Hi: We are using DB-Library C to insert data into SQL2K. Is there any way to insert NULL value? We are now using bcp_bind, bcp_sendrows. Thanks Sidney Zhang ...more >>

hi i have a table with 32 fields that 25 fiels of them is varchar we select quary( select * from table) in accses and sql server but the time of select in sqlserver was very larg . please help me with your segastion for decrease this time. thenks for your time. ...more >>

Hi, I read in a book which suggested to check the @@rowncount inside the trigger if it is =0 then return if @@rowncount=0 Return I am wondering if the no row is modified how come trigger is fired? Regards -- Farhan ...more >>

Hello again everyone, thanks for all your help previously. Have another trigger question... Can a trigger populate fields in another database or is it functional only in the database that it is created in? I tried looking in BOL but couldn't find anything on this. All I found was that ...more >>

Hi everyone, please i need your help in providing a quick and detailed code on how to create table using sql language. i also need to know how to create tables with multiple collumns and rows for nearly 40,000 date sets in each table. i also need to know how to extract information from thes...more >>

I am unable to detect significant performance improvements when using the following in a proc...but I'm still implementing sp_executeSQL in hopes that there is some. This is a test proc. The production proc will be executed by a dts pkg and be called anywhere from 80 to 30000 times depending ...more >>

I'm trying to convert a string to a money(or smallmoney) data type. The original string from the data source looks like this: -00000010232 (this would be -102.32 dollars), or 00000010200 (this would be 102.00 dollars. When I convert using the CAST method, the Cents drop off. The '102.00' becomes...more >>

Hi, I execute a sp that use 40 tables and other sp. If I execute this sp in the production db I have at execution time the 40 s. If I backup and restore the db in the same server but with an other db name I have execution time the 40 s, but if I execute the same sp the next day the exec...more >>

Why my message written with OE doesn't apper in the NG. Maybe that my address was banned or inserted into a black list? This situation is present in more than one microsoft NG, but not all. Why? Sincerely Andrea Moro...more >>

I am trying to execute multiple commands in a SQL Server job CmdExec step, e.g. cmd.exe param1 param2 cmd.exe param3 param4 The step runs successfully, but only the first cmd.exe executes; everything after is ignored. I have got around this by using xp_cmdshell and Transact- SQL scrip...more >>

on the following: CREATE TABLE T (id INTEGER NOT NULL PRIMARY KEY, Name varchar(50) NOT NULL); I want the following query to run very fast: SELECT id, Name FROM T ORDER BY Name so I need to index the Name column. I want all columns to be included in the index. CREATE INDEX TInd...more >>

I am running a load test on a SQL server and I want to call a stored procedure from the command prompt using ISQL. I also want to be pipe in parameters. How do I do this ? ...more >>

OK, here's my problem - I want to update a 'main' table from an 'updates' table, they have a common field which I use for a join - making the query of the format "UPDATE maintable set maintable.latestupdate = updates.update FROM maintable join updates on maintable.field1 = updates.field1" ...more >>