Hi! I'll try again I want to rearrange a column in a table within a stored procedure. I got this table with this sample data: ID Text Displayorder 1 Testing 100 2 Testing2 200 3 Testing3 300 4 Testing4 400 5 ...more >>

Hi to All! I have a scenario in which i want to update specific charcater in the field i m issuing following query but update mytab set substring(R,10,1) = 'A' where substring(col049,10,1) = 'P' this query returns me error What will b the right query for performing the task Thanx...more >>

How can I use the CASE WHEN test to return a SELECT statement? CODE: declare @iType int set @iType = 1 CASE @iType WHEN 1 THEN SELECT * FROM mytable WHERE id = 1 WHEN 2 THEN SELECT * FROM mytable WHERE id = 2 END ...more >>

Hello, I have 2 tables. TableA and TableB. Both tables have the same 3 columns in them. Both tables have different sets of data loaded in them. I want to some SQL each day that moves over only the records from TableB that are not already in TableA. So basically, I want to insert new records ...more >>

hi, this is the closest newsgroup i can find that relates to my problem. please advice if there's a better one. consider the script below: create message type QueryMessage validation = none go create contract QueryContract (QueryMessage sent by initiator) go create queue SenderQueue go...more >>

I have an ID Field in a table ContactInformation. This ID field is char(4) type. And for this I want to auto generate strings starting from '0001' onwards: 0001 0002 9998 9999 A001 A002 Z999 Thanks....more >>

Hello, I have been reading various articles on way to prevent deadlock, like using locking hints, nolock etc. But I am not clear on the most general/conventional way to prevent deadlock. Like say 2 processes/procedures are trying to read/update the same record at the same time. I under...more >>

Hie there. I'm a little frenchie (froggie ? lol) and I'm just working on a Web Search engine which I would appreciate you to test... Here the URL http://erwann.conan.free.fr "Insertion d'un site" means "insert a web site" "Recherche d'un site" means "look for a web site" Thanks a lot ...more >>

I have two tables where I'm trying to get a specific conversion factor for a list of items. My items can have several conversion factors but I'm only interested in LB or PCS. If the item has the conversion factor for CS to LB I want my view to pick up that one, but if it doesn't, it should...more >>

I am trying to create a temp table using a select into query. IF @flag = 1 begin select fieldnames into #tmp from tablename where...... end else begin select fieldnames into #tmp from tablename where...... end I am getting an error "There is already an object nam...more >>

I am trying to write a stored procedure that acts like an Append query. I want to provide it with two dates, one is the date of records I want to copy and the other is the date I want to be added to the new records once they get pasted in. I am getting close with the following code: INSERT ...more >>

I've begun to experience some quirky behavior with SQL Profiler. In the past it has run just fine with no issues. Now suddenly, I'm beginning to get the following error: "Some trace events have not been reported...." because of lack of memory resources. The trace events that seem to be get...more >>

I just downloaded sql server 2005 dev edition from my msdn subscription, however, in the listing of product keys, I dont see any keys for sql server. Dont I need a product key for this when I install it? -- moondaddy@nospam.nospam ...more >>

SQL 2005 We have ~30 db's log shipped to a different server (LOGSHIP) . If any user connect to LOGSHIP \db1 then the hourly job keep failing for that db until the disconnect. That means if a connection open exits, log shipping process cannot apply the transaction log ?? Any suggestions/i...more >>

I installed a copy of sql2005 enterprise on win 2003 sp1 server and it went well. I want to manage the server thru my workstation (like EM). but there is no documentation about how to install just client tools (such as SQL Server Management Studio) on workstation. what are the requirements? I h...more >>

I have a stored procedure that attempts to process some stored data files located in one or more directories. All the procedure tries to do is walk through each directory and process (in this case, bulk insert) each file. I've coded this to use 2 cursors, one to walk the list of directories, ...more >>

It's possible that this is by design, but SQL Server Bulk Insert seems to import incorrectly formatted data without raising an error. Suppose I've defined the following table: create table test_table ( field1 varchar(255), field2 varchar(255), field3 varchar(255) ) The input data is in...more >>

Here's my dilema. I'm trying to create a SQL script that will report back to me every table that has a different row count from one database to another. I believe the best way to accomplish this is using a CURSOR to fetch through the tables in the DB and print those where the record count ...more >>

Hi all. I have tried everything I know (which isn't much) and I a all out of ideas I am trying to create a linked server from SQL Server 2000 to a DB server. I am using IBM OLE DB Provider for DB2 and I get th following error Error 7330: OLE DB Provider 'IBMDADB2' reported an error OLE DB e...more >>

I've come up with a query which allows me to perform fairly dynamic queries depending on which parameters are set when a stored procedure is called, but I'm not sure if what I'm doing is a performance no-no. Here is an example: CREATE PROCEDURE p_TradesGetForProcessing @tradeID int = null, ...more >>

DDL posted here: http://68.6.120.147:8080/DDL2.html I want to get data from two tables, but only that data that has changed in the past 90 days (addrdate and phndate specify the last change date), i.e. SELECT addrid , addrline1, addrline2 FROM address WHERE addrdate between dateadd(day,-...more >>

How do you perform 2 or more aggregate functions in a query? For example, for each distinct client I need to add two values together, then determine the overall average of all clients. SELECT AVG(SUM(leadclient.nch1 + leadclient.npts)) FROM leadclient This is the query but does not perform...more >>

Hi Everybody, I'm looking for some help putting together a stored procedure to do a report for my shipping department. What they are looking for is a report listing a SalesOrder (SO), some of its pertinent information, and then a list of the inventory we have for that part. Here's the catch...more >>

I cannot seem to get this to work... SELECT a.server,a.date FROM machines a WHERE NOT EXISTS (SELECT * FROM file_system b WHERE (file_name like '%XXX%') and (a.server = b.server)) Basically I need to find all the Servers in table B that have XXX installed, and then (ultima...more >>

I've got a big procedure written by a contractor. I'm trying to execute it (against a test db until I get it worked out) and there is one section of it that fails. I've isolated the section and run it in Query Analyzer and it still fails. Here is the SQL: DELETE FROM tblBedOccupancy WHERE (...more >>

How can I copy SQL diagram from one source to other destination? I would appreciate if you let me know. Thanks. ...more >>

I am trying to make an IN statement that has a LIKE statement in it. I want to use the following LIKE statement in an IN statement but I am not sure how to set it up. (Item.Search_Description LIKE ' % ' + @OLDID + ' % ') What I want to accomplish is for the the query to return data for the ...more >>

Hi, I have a job looping through a table containing names of databases. Based on some criterias I want to detach some of the databases. So far all is good. If for some reason the detach fails, the job fails and quits. I would like to know if I in any way can get the job to continue the loo...more >>

Hi, If a query is performing an update from one table to another, then I understand that the data is temporarily written to tempdb then committed once the update calculation is completed. I have a 2 questions....... imagine I am updating TableB with the differences between TableA and Tab...more >>

Hello... I can run: dtsrun /SMyServer /Usa /P***** /Ntesting3 from the command line on the server and it works fine... when I run it from a workstation on the lan, it says "Not associated with a trusted SQL Server connection" The security is set for both Windows and SQL authentication. ...more >>

TransactionId TransDT EntryExitId DirectionId TagHexId TrTypeId 54 2005-11-08 10:00:00.000 1 1 0FE48B54 1 55 2005-11-08 10:05:00.000 2 1 0FE48B54 1 56 2005-11-08 10:06:00.000 1 ...more >>

Hi, I call inline function in stored procedure like: SELECT tab1.Col1, tab1.Col2, fnTest.* FROM tab1, fnTest(Col1, y) WHERE tab1.Col1 = Col1 fnTest(Col1, y) returns one row and by joining I want to get row based on multiple Col1 passed as parameter in function call in FROM clause. Than...more >>

Hi! I've tried numerous solutions to this classic problem. I know how to do a pretty scalable solution, but I'd like to hear some more ideas. What I do now is something like this: 1. Declare a cursor inside an exec statement since order by doesn't support variables (I know about the ...more >>

I have a Database with 22 tables, all with a column "MyColumn" Char(40). There are 30 tables that do not have the column. I need to alter all of the tables with "MyColumn" to make the column 44 characters wide. I am struggling how to do it in a set-based operation without hardcodi...more >>

So there is no way to create a Database diagram on a SQL 2000 database using SQL Server management studio 2005 even though that database diagrams is not a new feature? Thanks...more >>

Is there a global setting to specify a business (5 day) versus a calendar (7 day) week for date calculations?...more >>

Hi, i want to know, just in general, what does 'SourceSafe integration' means in 2005. does that mean that i can use the new query analyzer (or whatever its called) to check in\checkout stuff directly from SourceSafe? anything more, anything less? thanks ...more >>

How much space do i need to have on a machine to install SQL Server 2005 Dev edition? -- moondaddy@nospam.nospam ...more >>

I have two tables, data_branchAddOns: CREATE TABLE [dbo].[data_branchAddOns] ( [id] [int] IDENTITY (1, 1) NOT NULL , [branchId] [int] NOT NULL , [name] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [displayText] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU...more >>

Hello Everyone! I'm having problems with the INNER JOIN in the subquery. If it wasn't for that, I'd be able to LEFT JOIN it and then check for nulls on the right side, but I'm not sure where I'd stick the INNER JOIN. SELECT COUNT(*) FROM FND_DONATION_MASTER F WHERE F.SHIP_CUSTOMER NOT IN ...more >>

set rowcount 100000 declare @a int while 1=1 begin begin tran del1 delete from dbo.extt_vpm_ldr_stg_2 select @a = @@rowcount print @a commit tran del1 if @a < 100000 break; end Hi TOTAL TABLE SIZE - 650 000 records I am using the above statement to delete in ...more >>

I'm looking for migration tool which can convert Access database to SQL Server. Any one which works 100%. Any suggestions would be greatly appreciated. Regards, Rao ...more >>

Im making a presumption / hoping that a few sql server programmers have, by definition of what they do, a good understanding of the hardware relationships to performance. Im concerned that a costly upgrade will yield little result, apart from a very red face from me :) Current setup: SQL ...more >>

Given the following data in table 'table1': RowID EmployeeID Amount 1 A 1000 2 B 200 3 A 350 4 C 1500 5 B 50 6 A 300 I want to extract the Maximum amount for each employee. So, for the data above, the resultset would look like this: RowID EmployeeID Amount 1 A 1000 2 B 200...more >>

I want to perform some dynamic Sql statements on inserted and deleted tables. But the sql gives me an error, that these tables does not exists. Where are inserted and deleted tables "hidden", so that I could reference on them, for example: exec('select '+@lcField+' from deleted' ) Uros...more >>

How does the update, delete or insert in a xml column work?...more >>

How can i xquery the results from multiple tables?...more >>

Hi! I want to rearrange a column in a table within a stored procedure. I got this table with this sample data: ID Text Number 1 Testing 1 2 Testing2 2 3 Testing3 3 4 Testing4 4 5 Testing5 5 I wa...more >>

Dear folks, Could you please so kind to tell me where the fixed type data stored are? I mean, in what system table are available these data? "bigint, binary, float, decimal", and so on Thanks in advance and regards, Enric...more >>

Hi How can I create multiple views in a single SQL script ? (I get an error: 'CREATE VIEW' must be the first statement in a query batch.) thanks ...more >>

SELECT TOP 1 FROM ... ORDER BY Field1, Field2, Field3 Will the statement below, always return the first record of the same stand-alone SELECT statement as below: SELECT * FROM ... ORDER BY Field1, Field2, Field3 Thanks, Jay ...more >>

Anybody using Sql Server 2005? I'm curious as to what happened to the Service Manager in the system tray, like SQL 2000 had? I know that Configuration Manager that does the same stuff (starting/pausing/stopping the Server), but it sure was convenient having that in the system tray before. Anyone...more >>

I want to write a trigger , which will delete a particular row when the system time(server time) matches with the time previously given by a user (in the front end).This deleted row should then automatically get inserted into another table. ITS REALLY URGENT, AND I WANT AN IMMEDIATE REPLY P...more >>

Hi, I often do something like this: SELECT ... FROM Table1 LEFT OUTER JOIN Table2 ON (Table2.Id = Table1.Id AND Table2.Deleted<>0) LEFT OUTER JOIN Table3 ON (Table3.Id = Table2.Id AND Table3.Deleted<>0) WHERE Table1.Deleted<>1 I've been suggested on here to break ou...more >>

Hi, How can I use newsgroups of SQL Server 2005? Thanks in advance, Leila ...more >>

Mmmm .. SQL and dates ... always fun ... I have a MS SQL database that holds bookings for various resources. Bookings are held in two tables set up as follows: ID | Schedule ID | Start time | End Time | BookedByName ScheduleID | ResourceID | ScheduleDate Both time and date fields are '...more >>