Hallo all, I am really breaking my head to solve this problem. In TableA I have text datatype column 'subject'.The data in this column is like ' subjt:oooeer please check the data 60 dg 0,11 € Blaue dgf 100 3fg 0,06 € Verschfg' Now I have to read this text column value and inse...more >>

Hello, After installing SQL Server 2005, I get a message stating "You must use SQL Server 2005 management tools to connect to this server", while trying to connect to my localhost using SQL Server 2000 Enterprise Manager. Regards, junior ...more >>

Hello, I need to know about the encryption types supported by MS Sql. and how can we store password in data base in encrypted form. Is there any article upon this issue. Regards, junior ...more >>

I want to create an SP that runs at night (like 11pm or so). I know I can create an SQL Server Agent Job. A few questions about that (our app is written in VB.NET): 1. If the SQL Server agent isn't running, can I turn it on via a VB.NET command or some SQL command? 2. Once the agent is runn...more >>

Hello, I would greatly appreciate any help regarding this UDF-related question: I am attempting to create an autocomputed column with persisted values. But my UDF which is returning the desired result is considered non-deterministic by SQL server 2005, although I'm sure it complies with th...more >>

Hi I was in the progress of developing a web site whereby developers can do free online tests on various programming languages. After initial feedback I managed to fix quite a few issues and changed the look and feel. I would just like some futher comments\tips\hints or feedback The site...more >>

I created a stored procedure a while back, and I am now getting around to optimizing it, or trying to. It is fairly optimized at the movement, but there are a few improvements that I am trying to implement. The first one is converting all # Tables to Table Variables. I have all the ones I can co...more >>

Hi I have a table with columns F1, F2, F3, F4, F5 etc (yes bad design - don't blame me!) I need to convert this into multiple rows like so F1 F2 F3 F4 F5 I can't think of an efficient way to do this, maybe it's late and my b...more >>

Hi all, I am sure that some one has solved this problem before. i am using RDA to do Data syncronisation from SQL CE to SQL2005. It seems that if you PULL a Table with TrackingOn and then delete the Record from the server. It does not get reflected on the PDA after next PUSH. It also see...more >>

Hi I have a mastertable that I am querying. However one of the fields that I require are often missing - due to a poor U.I and sloppy data entry. However I am able to source this data field from another table. Although this secondary table often has holes aswell, so it means I have to go...more >>

I need a way to represent the RESOURCES. For example, employee and machines are resources. I create two tables named employee (EID as PK) and machine(MID as PK). and create a view named Resource (RID as key) as followed, create view Resource as select EID RID, name from Employee union al...more >>

Hello, I have a product relationship table looking similar to this: ProductID ParentID 1 25 2 25 3 25 4 27 5 27 6 28 ..... I would like to create a query that would list parents in one ...more >>

In SQL 2K how can I change a computed column in QA? If I have a column like this: TotalCharged = NumItems * PriceEach I want to change it to this: TotalCharged = (NumItems * PriceEach) - Discount I don't see how I can use ALTER TABLE xxx ALTER COLUMN xxx to do this. Is it possible? ...more >>

Hi All I want to combine 2 or more selects so i get them on one grid. Below are the selects and a representation of the grid. select FIELD1, FIELD2, DATAFIELD TANK from mytable where analytecode = 'TANK' where FIELD1 like 'AERATION%' and FIELD2 like 'JAN%' order by uniqueno select FIELD1...more >>

Is there any way to using .net coding inside stored procedure? ...more >>

So, I am building this active script and have run into a single quote issue when I am building the body of an email that am sending thru a DTS package. Here is the code snippet sBody = "IMPORTANT" & vbcrlf & vbcrlf sBody = sBody & "If you are using PCmover to migrate to a new Microsoft ...more >>

If I reindex a whole database would there be any point in updating stats. Would you ever need to update stats if you done daily reindexing of a whole database and when would you ever need to update stats? thanks for any advice Sammy...more >>

Here is the table: idtrans idrma phmdel part qty bagqty btini btdte 211 1117 SAN2300Pink M 5 27 JP 2007-01-24 233 1117 SAN2300Pink M 3 1 JP 2007-01-26 280 1122 SAN2300Pink M 5 90 JP 2007-01-29 284 1117 SAN2300Pink M 5 17 MJH 2007-...more >>

Hello, I have a table with two columns: LevelId and LevelName. I need to check if there is a record with a given LevelName. I don't need to get that record. All I need is to know if it exists or not. What should be the right way to create this procedure? Should I use Return? Should I...more >>

Hello, I have a stored procedure that takes roughly 1 min 15 secs to complete. I have my timeout on the server set to 120 secs. I'm attempting to run this procedure from a .NET application which also has the connect timeout setting set to 120 secs. However, the call fails after 35 seconds e...more >>

We are using a concatenated key table to create a view, in which we want the non-repeating columns to be displayed on a single row output. We have created a "Full_schedule" view using a reflexive join. Here is our SQL Server statement and output: SELECT m.matchID, m.date, m.time, m.tablenum,...more >>

Dear All, Is there any easy way of capturing the details of data modifying user ( last updated user) in a table as and when the data is being modified? ( For existing Production tables) Any mechanism other than trigger? Or any generic stored procedure available which can be called in the trig...more >>

We are using a concatenated key table to create a view, in which we want the non-repeating columns to be displayed on a single row output. We have created a "Full_schedule" view using a reflexive join. Here is our SQL Server statement and output: SELECT m.matchID, m.date, m.time, m.tablenum,...more >>

Hello, I am trying to take my result set and dividing up the results into 4 equal groups I have included my code and a partial result set. All of your input and help is appreciated as I am new at this select accounts.id, sum(trans.amount) as 'income' from jom.dbo.accounts join jom...more >>

I have 2 files each with the same fields that I'm just trying to append to each other. For example: File1 id age 1 10 2 20 3 30 File2 id age 4 40 5 50 6 60 And I want: id age 1 10 2 20 3 30 4 40 5 50 6 60 Can I do this in SQL? ...more >>

Hi: I'm storing images in a Varbinary(max) column in an e-commerce site I'm developing. If an image is not available when new product is inserted, I would like it to use a default image. How do set this kind of column default? Thanks, Charlie ...more >>

The tricky part for me is accomplishing the CASE logic in the WHERE clause. I could really use some help there please. THX in advance :) -- --TEST DATA DECLARE @AirDate AS DATETIME DECLARE @TZBias AS DECIMAL(18,0) DECLARE @TZBiasDelayed AS DECIMAL(18,0) DECLARE @BroadcastZoneDesc AS...more >>

Hi I have a Stored Procedure which I am trying to get to work, what I want to do is pass in a phone number from a VB.net program and have it so that if the first SELECT statement finds no match the second SELECT statement will attempt to find a match. So far I have not been able to get this...more >>

I want to .... add a job that in essence backs up all my jobs. From what I've read, the jobs are stored in msdb. Is adding a nightly backup of msdb the best / only way to automate the backing up of my jobs? thanks in advance, bob. ...more >>

I have this script to backup our database: BACKUP DATABASE [a2101] TO DISK = N'F:\sql\sqlback2-1-07.bak' WITH NOFORMAT, NOINIT, NAME = N'a2101-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO How can I schedule this for 1:00am every night? Thanks, Trint ...more >>

I have the following query in microsoft query, SELECT v_AnalogHistory.DateTime, v_AnalogHistory.TagName, v_AnalogHistory.Value, v_AnalogHistory.wwRowCount FROM Runtime.dbo.v_AnalogHistory v_AnalogHistory WHERE (v_AnalogHistory.TagName='PS004_Pump1.Daily_Runtime') AND (v_AnalogHistory.DateTi...more >>

Hi All, I looked thru the responses to answer this question but they either didn't work or were not detailed enough. I am using SQL Server 2000. I have many databases on one server. The databases have the same structure. I want to use these stored procedures that I created on most of the ...more >>

I need to save the output of a query to a file on the database server. Our application needs to create the file and then another application sends the file to a third party. How do I do this? TIA ...more >>

Hi, I'm looking for some guidelines to design, develop and maintain a standard database. I mean standard in terms of naming conventions, schema, security, availability, maintenance and every aspect that there is standards. However I'm not sure if there can be standard rule for all of them. R...more >>

Hi group, Please someone tell me that it is possible to create custom shortcuts like ALT+F1 to run sp_help. They had this customize feature in QA, why oh why would they take it out? Thanks S ...more >>

thanks, Tibor it is possible to override that schema, that is, in order to lookup a "custom schema" insetad "dbo schema" ?? thanks -- atte, Hernán ...more >>

Hi, I have posted another question "ASPState DB error..." few minutes back. While searching via google I landed up @ http://forums.asp.net/thread/1389662.aspx Now can anyone tell me how to find out what is installed in my box :( In my naked eye i am able to see "SQL Server 2000 client" ...more >>

Hi, I need to convert a float value to varchar. Float values may vary as given below. [decimal may be in any position] I've tried many options but doesnt suit for all the below given scenarios. I need one common solution to convert all of the below given scenarios. 1.28260000000000002 12....more >>

Hi again everyone. I am trying to do some genetic algorithms for some experiments with expert systems. The problem is that i am trying to do roulette wheel selection but don't know how to do it. I have a table like this: wTable wID bigint, ID bigint pk, wrd nvarchar(100), pos_to_sh...more >>

Hi When I run InstallSqlState.sql file I am getting this error .... The specified @name ('[Uncategorized (Local)]') already exists Can anyone kindly help me quickly to resolve this? Regards Pradeep...more >>

I'm running SQL Server 2005 I executed an INSERT statement which added records to my table that were sourced from a linked server. Unfortunately I forgot to wrap it with BEGIN TRANS / COMMIT TRANS. The new rows are uneditable and the Activity Monitor still lists one runnable process wit...more >>

I'd like to create a query that will conditionally use one (or more) parameters. For example, if a query for a list of products is usually found by just a Category parameter, but sometimes in addition to the Category parameter there is a SubCategory parameter. I suppose I could do this with ...more >>

Hi, On My local SQL server I have added a linked server to another SQL server (remoteserver) in another Windows NT Domain. When I run this code select count(*) from remoteserver.mosaics.dbo.Location This works fine. However when I use begin transaction select count(*) from remoteserver...more >>

After index re-creating total fragmentation remain 83% What do I do to reduce fragmentation? Thanks ......more >>

All, I've discovered a few views in our production system that use 'Select top 100 percent'. The only reason I can see is so the original programmer could use an ORDER BY within the view itself. I'd like to change them to proper views and use the order by where the views are referenced. Howev...more >>

Hello - I have a Sql Server 2000 database in production. I am going to need to make changes to some of the stored procedures. I would like to be able to script all of the stored procedures at once and put them into Microsoft Word for searching purposes. I am somewhat reluctant because...more >>

Hi everyone, Here is a simple and quick question on queries for you all. I've got a stored procedure that uses the following: INSERT INTO MY_TABLE (SET_ID, ID, STATUS_COUNT, STATUS_CODE) select @sId, @Id, count(*), st.CODE from STATUS_TABLE as st (NOLOCK) where st.ID = @Id and (s...more >>

True story, a friend of mine is a technical writer for a semiconductor manufacturer. He has been working on a project to document an internal database application. He told me he'd spent all week working with a language called "squirrel." I asked him if he meant S-Q-L. He said, "Yeah, squirre...more >>

Hi, Scenario: Database A in Machine McA Database B in Machine McB McA.TableA has 100+ rows of (BranchNo, Col1, and Col2) - BranchNo not unique McB.TableB has 100+ rows of (BranchNo, Col1, and Col2) - BranchNo not uniqu An XLS (BranchNosToTransfer.XLS) has 50 unique rows of 'Bran...more >>

If someone could point me in the right direction I would appreciate it. Here is the situation: We are consolidating all our application databases into 1 consolidated and integrated database for an entire school district. We would like the user to be able to rebuild the databases indexes. ...more >>

How can I provide default values for parameters for a CLR stored proc written in VB? In a T-SQL stored proc, I can specify a default value. When I write a CLR stored procedure, I identify the paameters for the stored proc in the function name. In VB I can use the optional keyword, but when I...more >>

hi i need convert two rows to one row and insert into another table with the same structure. i have ID |something | P1 | P2 | P3 | P4 | date_A | date_B | 1 | abc | 1 | 0 | 0 | 0 | NULL | 1/1/2007| 2 | abc | 1 | 2 | 3 | 4 | 2/1/2007 | NULL ...more >>

Hi, I've read about covering indexes, but I'm having a bit of an issue grasping how best to create covering indexes that span multiple tables. I have a search stored procedure that uses many columns from different tables within the where clause. For example, say I have the following tables ...more >>

Hi My problem is that I would like to use an alias in a Group By clause but I think this is not allowed. I have to use the complete expression. Can someone confirm this for me? The problem is the expression itself is an agregation and hence I cannot use it in another Group by clause. Any...more >>

Hello I will be very grateful if somebody can help me. My problem I can simplify to this one: table1 have column history which is text data type. I have queary: SELECT @historyPtr = textptr(history) FROM dbo.table1 WHERE id = 1 READTEXT dbo.table1 @historyPtr 1 100 everything works f...more >>

Does anyone know why this works: SELECT [MyXmlColumn].query('/root') FROM [MyDatabase].[dbo].[MyTable] But fully qualifying the column name produces an error: SELECT [MyDatabase].[dbo].[MyTable].[MyXmlColumn].query('/root') FROM [MyDatabase].[dbo].[MyTable] System.Data.SqlClient.SqlExcep...more >>

Hello, Could someone give me a syntax to correct the last SELECT? Thx. JN. declare @Table table (CI varchar(10) COLLATE French_CI_AS, CS varchar(10) COLLATE French_CS_AS) insert into @Table (CI,CS) values ('VAL_CI','Val_CS') select CI+';'+CS From @Table...more >>

Hello, It seem's that the MAXRECURSION option is not allowed to complete a WITH in an Inline Table-valued Function, whereas it is in a Multi-statement function's code. But I can't find any MSDN article about this. Could someone help me? Thx. JN. IF EXISTS (SELECT * FROM sys.objects WHER...more >>

hi, i have table like orders o_num p_type 1 TSG 2 TSGA 3 LTS 4 LTSSAP 5 CA 6 ABC now i want to retrieve records where p_type not like 'TSG%','LTS%' for 1 order there will be only one p_type so i wrote the query like select o_num,p_type from...more >>

How to retrive from a table the rows which has common or duplicate value (other than the primary key)...... ex .... There is a table called "details" which has the following structure.... No. Name Age Country 1 dfdf 12 bvgbg 2 jjhjd 4...more >>

Hi guys. I have 2 tables: TABLE1: userid bigint FK_usertab_id, id bigint PK, s_wrd nvarchar(100) TABLE2: wrd_id bigint FK_table1_id, ExURL nvarchar(800) In TABLE1 data is like this userid id s_wrd ------------------------- 1 1 somethin1 1 2 somethin2 1 3 somethin3 2 4 somethi...more >>

Dear friends, I keep getting "The column prefix 'Item' does not match with a table name or alias name used in the query" when running this query. INSERT INTO item (Notes, [ExtendedDescription],[Description],ItemLookupCode) SELECT notes, [Ext Desc], [Desc],ILC FROM TEMP Where TEMP.ILC <>...more >>

I am writing a procedure that is supposed to eliminate (delete) redundant data from a specific table based on certain keys. The user specifies what keys are to be considered unique (for instance, any combination of X, Y, and Z) and then the procedure goes through the table to delete all "doubl...more >>