I need some help with a case expression. I need to interpert the following statements into a case expression. The statements are from Access and I'm trying to do the same in a SQL stored proc. @cboagTypeGroup And @cboagType will be the parameters passed to my procedure. Normally they would repr...more >>

I'm trying to pull a report from my SMS database that will list a yes/no type answer for each server, as to whether a file exists on that system. There are basically 3 tables involved: SoftwareInventory, which contains Columns (ClientID and FileID) System_DATA, which joins to SoftwareInventor...more >>

(I originally posted this on comp.databases.ms-sqlserver, but I have been asked to re-post to this group... Thanks!) I really hope I'm wrong about this - I can't find any way to issue a SOAP call from SQL Server 2000. I've looked at the BOL, Google, MSDN, and the SQLXML documentation and sampl...more >>

Hi All How Do I Update a table using JOIN with Other table Like the Below: UPDATE company A INNER JOIN phonelist B ON SUBSTRING(A.PHONENUM,1,3) = B.OLD_NPA AND SUBSTRING(A.PHONENUM,4,3) = B.PREFIX SET A.PHONENUM = STUFF(A.PHONENUM, 1, 3, B.NEW_NPA) The above example raises error. ...more >>

Hi Guys, I have a table which has data like this create table mytab (id int , contract_text varchar(2000)) there is no id and is a kind of temp. table id contract_text 1 this is first row 1 this is second row and is big 1 this is okay 2 this is okay too 2 this is xtra large and i dont...more >>

Hello Experts, How can I set up a automatically round for a particular row in a table? For example, if I got an insert statement: INSERT INTO table (field1, field2) VALUES ('1','3.456'). Is there an option that I can setup for "field2" which will automatically round up 3.456 to 3.47 and store...more >>

Can anyone point me towards references that give good explanations or examples of the best way to manage errors in a Transact-SQL procedure. Typically I pride myself on solid error handling, but I am having difficulty coming up with an approach for handling T-SQL errors that I am satisfied wit...more >>

Hello, I have a litle problem with a comand convert This is Ms Server: Msg 8115, Level 16, State 2, Line Arithmetic overflow error converting expression to data type int ...more >>

I've got a Sproc that totals certain values in a line item file, but I would like it to also return a few flags based on that data. Consider this table: create table litem( order_id int, type varchar(25), sales money, dropShip bit, foreignOrder bit) Let's say I want to find out the...more >>

Hi, I have a stored procedure like the following : Create Procedure sp_Main AS Set NoCount On -- Do some stuff -- Do some stuff -- Do some stuff Exec sp_InnerStored GO I also have a user, say USER_X, which belongs to a role, say ROLE_A Now, ROLE_A ...more >>

I'm wondering if there is a better way to design the following tables. There are a few things about the current design that I am unsure about or simply am not happy with. 1. In the tables related to Customer, should I make the FK:CustomerID a FK:PK instead of just a FK? The reason that I w...more >>

Hi guys, I got a column with 10 numbers, but I only need the first 5.. Which SQL-command I should use, to convert the 10 numbers into 5. I just need to cut the last 5. Thank you.. Maik ...more >>

I have auto generated statistics on a non-indexed column. Can this statistic "age out" similar to how procedure cache plans age out? I have "auto create stats" set to "on".... will that re-generate the statistic? ...more >>

CREATE TABLE #Employees (empid INTEGER PRIMARY KEY, empname VARCHAR(25) UNIQUE, salary NUMERIC(10,0) NOT NULL) INSERT INTO #Employees VALUES (1, 'Nancy Davolio', 54670) INSERT INTO #Employees VALUES (2, 'Andrew Fuller', 34570) INSERT INTO #Employees VALUES (3, 'Janet Leverling', 33550) INSERT...more >>

hi all. how can i make a query that returns true or false . true - if the query returns 1 row. false - if the query returns none. 10x . sharon. ...more >>

sql2 = "SELECT custno, DATEOR, ORDERNO, LN, custpo, ITEMNO, QTY, PRICE, UM, totprice, QTYALL, QTYBACK, SLSMNO, INIT FROM jjk044 where custno = '" & custno & "' and " + OpenType + " order by " + sortterm + "" For a local application I occasionally but not always get the below error with this SQ...more >>

I have a table that has a field populated by single character data. in it 'm' is different than 'M'. How do I differentiate this in my queries.... It works automatically on my base SQL server, but not on the replicant. Thanks for the help... ...more >>

What does the sp_sdidebug do? ...more >>

Hi, After Exec(), I try to catch @rows and @pages. But it doesn't work. Anyone can help me, how to get these two value? thanks, declare @sql varchar(256) Set @sql = 'Select ' + @rows + ' = Count(*), ' + @pages + ' = Count(*)/' + Cast(@perpage As Varchar) + ' From InventInfo WHERE snum = ...more >>

Is there a way to export data that being Inserted, Deleted, Updated realtime....more >>

Hi everyone, I have the following code which works fine in query analyzer when I execute it. But when thie code is add into a trigger, the Channel in tab1 is not update withe a value return from the recusive udf. Does anyone know why is that so? Declare @aa as varchar(50) ...more >>

Hi, In web application, if there are two visitors of a website read/write from/to a database at the same time, how does the SQL Server handle these? The read operations will be run at the same time and the write operations will be put into a queue and are run one by one? If it is right, that m...more >>

Hello, I want to use a table variable as OUTPUT parameter of a stored procedurce. Strange: I cannot find any example for this anywhere, not even in the BOL. I want to do something like this, but it gives me only "wrong syntax near table key word". --- CREATE PROCEDURE [dbo].[test_sp] @@t...more >>

Hi, I have a datatype which is used in a number of tables. How to change the owner (local - from windows) to dbo in the most simple way? Regards, Miroo ...more >>

declare @JoinToTable varchar(400) declare @joiningTable varchar(400) set @joiningTable = 'AccountsJunJournalEntry' set @JoinToTable = (select top 1 JournalEntryTypes.JoiningTable from JournalJoiningTablesTypes left outer join JournalEntryTypes on JournalEntryTypes.JETypeID = JournalJoining...more >>

Anyone know how to use a variable to set a derived column name? I need to dynamically name columns and would prefer to do this DB-side rather than in the app. e.g. DECLARE @COL AS NVARCHAR(50) SET @COL='DerivedColName' SELECT COL1 AS @COL FROM TABLE1...more >>

I have a table that contains data that is all capitalized. I would like to change the data to lowercase letters except the first letter of each word. Is there a function in SQL Server 2000 that does this? The data is countries, regions, states. Thanks. *** Sent via Developersdex http...more >>

Doing math on dates in SQL always hurts my head ;P What I would like to do is retrieve a recordset where the anniversary date is within (less than or equal to) 7 days from today's date. For example, if today is 03/22/2004, then an anniversary date of 03/18/2001 should be included in the reco...more >>

I have a web banner database which includes (among others) a Banner and a Banner_Campaign table. The ID in each table is BannerID and CampaignID. BannerID is used for the join. One banner can have more than one campaign attached to it. Here is what I do to get a list of banners with the...more >>

Hi, friends What means the following error: Operand type clash: varchar is incompatible with void type This only happen with a specific server thx Johnny Silvestre...more >>

I receive the following error when trying to use a fully qualified name in Query Analyzer: Invalid object name 'Orders.Ted.MyTable'. Here is my info: Server Name: KMC Database name: Orders Owner: Ted Table Name: MyTable My statements looks like this: INSERT INTO kmc.Orders.Ted.M...more >>

Hi all, my company recently introduced one standard format for all SQL code in the company, which I thought was long overdue (there are approx. 40 developers who contribute stored procs from all kinds of projects). However, one of the rules states that *all* code should be written exclusively in ...more >>

Hello, I am running a script to consolidate some tables. I know I will have errors such as "Violation of PRIMARY KEY constraint...". However, I do not want to see all the errors in the display because I will ignore the errors ( and will not insert the duplicated rows). How can I turn off the ...more >>

Hi, I want to create an auto-generated ID field that is alphanumeric. It should follow the pattern A000000. For example, once it approaches A999999, the next number should be B000000. Any suggestions on how to have a stored proc produce that? Khurram ...more >>

Hi all The following statement will return IsNumeric('11d111' Is this a known bug for sql server 2000 Or it is one kind of data format which I don't know J ...more >>

Hello all, I have searched for an answer to this question in the web and the usenet, but I haven't found it. I would like to create a trigger that copoes some data of a table A to another table B when new data is inserted in A. The table A is located in another database but in the same serv...more >>

Hey all, I am running SQL 2k on Win 2k Server. I get an error saying: "-2147467259 (80004005) [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt" The code ran fine before I changed the cursor location to adUseServer. Any ideas??? Code and DDL are incl...more >>

Hi All. I have some queries that take anywhere from 30 seconds to 2 minutes to execute. I would love to be able to display to the user an estimation of how long their query will take to execute. Is there a way I can get this information??? I see that it can be done through QA by displaying...more >>

I have an argument with someone about using natural key vs. surogate key. The issue is that we used to have a natural key which had a business meaning. Then the business rule changed and we had to start using a different natural key with a different business meaning. Lots of changes in multipl...more >>

I'm writing a stored procedure to return data from multiple tables in a way a that is awkward to join. The procedure will return only one record, but it needs aggregate data from lineitem and information from order. The first Idea I had was to use temporary tables- Select id, sum(field1) ...more >>

Another newbie question.... I have this table below. I would like indentify the healthplan_id column by a sequential number. For example, healthplan_id 15 would equal 1, healthplan_id = 2 etc. I will not know the number of healthplan_id's I will have, nor will I know which id's are going t...more >>

With a table that has a single-column key, you can do this: SELECT * FROM BigTable WHERE Key1 NOT IN ( SELECT Key1 FROM SmallTable ) This of course returns all records from BigTable that aren't in SmallTable, i.e. the difference of the two sets. I would like to do the same thing...more >>

Hi, In SQL 2000: is it possible define view with parameters? if yes.... why? if no... which is the solution? Stored Procedure? Thank's FILIPPO ...more >>

I use sql server 2000, and delphi 5 ado, the problem is some stored procedures are having errors(for eg that field not exisiting in table) when i run the delphi program, it runs with out giving any exception, but i want stored procedure to raise exception regardless of error type how can i ...more >>

Hello All How would I check if the contents of one table exist in another table? For example, the user uploads data into a staging table. I want to check if there are any new records that have been uploaded and flag those and display those in a special manner. please advise Thanks ...more >>

ciao i have problem puting variable in me qerystring. this is me stored proc USE PO G IF EXISTS(SELECT name FROM sysobject WHERE name = 'GetLastIssueWithYear' AND type = 'P' DROP PROCEDURE GetLastIssueWithYea G USE PO G Create procedure GetLastIssueWithYear @productNumber...more >>

Hi All, This seems simple but taking lots of my time without any luck. CREATE TABLE [dbo].[VendorCost] ( [ClaimNumber] [varchar] (10) NOT NULL , [SupplierID] [varchar] (10) NULL , [InvoiceNo] [int] NULL , [Status] [char] (1) NULL , [Rej_Code] [varchar] (3) NULL ) Insert ...more >>

Hi I have this simple query : Select value from TblName This result returns : ValueIJL ValueDSC ValueTHK ValueHMS ValuePOL How can i buld the query to receive this result : 1 ValueIJL 2 ValueDSC 3 ValueTHK 4 ValueHMS 5 ValuePOL Thank you ,Itzik ...more >>

Is it possible to index fields in a Table variables definition? If so ...How?? Thanks. ...more >>

My name is Cam. I am an SQL Server developer, noticed the following problem and need your help badly. Situation: We have 2 databases in our system; Database FASAT_PROD and FASAT_PROD_ARCHIVE; we also have 2 tables, which have the same structure; AGENT_ACTIVITY, which is in FASAT_PROD and ...more >>

can I run a query in SQL2000 (on a Win 2k or win2003 server) that lists the users contained in the Active Directory Thank Gerr ...more >>

I'm trying to use SQL92 joins after 10 years of using the old join style (= *=). Can someone tell me why the queries below don't yield the same results? Thanks in advance CREATE TABLE X x_id int IDENTITY(1,1) sex char(1) NOT NULL state char(2) NUL G INSERT INTO X (sex, state) VALUES (...more >>

I Would appreciate your help folks, wanna stop the sql agent from vb so i can copy the mdf file to another location, thx for your help... *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Hi all, CAN I USE FOLLOWING - IF YES, WHY IT DIDN'T WORK FOR ME? DO I MISS ANYTHING? CREATE PROCEDURE dbo.usp_UpdatePT @AutoKey int, @FieldName varchar(50), @FieldValue varchar(50) AS UPDATE tblMysisOnlyTOOL SET @FieldName = @FieldValue WHERE AutoKey=@AutoKey GO ...more >>

Hi I develop ASP.NET applications and use MS SQL 2000 Enterprise. Each customer has one database and one custom design application. And, there is a gateway and a central database to get data from all databases Supposed there are 10 DBs. I want the data in those 10 databases updating to the cen...more >>

Hi all, We have a database with 32 bit object identifiers. As we expect this to grow considerably we're worried that we may run out of this 'address' space, but the obvious alternative of a 64 bit bigint is not something I'm comfortable with as these identifiers are pervasive - the idea of add...more >>

Hi I have found references to this kind of thing but have been unable to adapt it to my (simple) requirement and I am hoping someone here can help. I have an existing table, called console and to make it simple, it currently only has one column, called consolename. What I would like to d...more >>

I feel that SQL can do what I want, but I cant get the syntax right I wish to append (using SHAPE) a recordset containing a series of job history records. First I need to locate the first Job_History record for the individual on or before a given date. I then need this record and subsequent record...more >>

im importing a lot of records into a table. The table has no key. Id like to make a key based on a record number. can i add a field to the table which is an 'autonumber' field and if so how would that effect my insert statement o Insert into USEDRecords ( CustNo, StartTime, FinTime) VALUES ( v1...more >>

Hi Quick Question... Can anyone tell me what N' does in a query ? ...more >>

Hi, I have an mts object written in vb, named A. Its transaction mode is 'required transaction' A calls an object named B with mode 'uses transaction'. B calls a data access component C which accesses the database. C also has a mode of 'uses transaction'. The number of calls to B from A are ...more >>

Wondered if you could help me I have a table (words) which contains a word field. This table is populated with various word records. Eg. WordID Word 1 Apple 2 Mango 3 Pear 4 Beer I want to pass a sentence to a stored procedure. Eg "I like apples If the sentence contains any of the wor...more >>

Hi I need confirmation of the fact that when you perform an operation like UNION ALL, tempdb is used as the working area for unionizing the data before returning the new result set can anybody confirm this many thanks....more >>